Does anyone know of any IFS Documentation on how to map IAL columns to the Designer ?
Here’s what we’re trying and we get an error:
IAL:
select
col.COMPANY as Company,
col.CONTRACT as Site,
col.customer_no || ' - ' || max(rega1app.CUST_ORD_CUSTOMER_API.Get_Name(col.CUSTOMER_NO)) as Customer_Name,
pm.Part_Main_Group "Trademark",
ROUND(SUM( col.revised_qty_due * col.sale_unit_price), 2) "Total_Revenue",
ROUND(SUM( col.revised_qty_due * col.cost ), 2) "Total_Sales_Cost",
ROUND( SUM( ((col.revised_qty_due * col.sale_unit_price) - (col. revised_qty_due * col.cost))), 2) "Margin",
ROUND(AVG(
case when col.sale_unit_price <> 0 then
(((col.revised_qty_due * col.sale_unit_price) - (col.revised_qty_due * col.cost)) / (col.revised_qty_due * col.sale_unit_price)) * 100
else 0 end
) , 2) "Margin_%"
from
REGA1APP.CUSTOMER_ORDER_LINE col
left join REGA1APP.CUSTOMER_ORDER co
on col.ORDER_NO = co.ORDER_NO
left join REGA1APP.PART_CATALOG pm on col.part_no = pm.part_no
left join REGA1APP.INVENTORY_PART ip on col.part_no = ip.part_no and col.contract = ip.contract
left join REGA1APP.CUST_ORDER_INV_ITEM_UIV_ALL coii on col.order_no = coii.order_no and col.line_no = coii.line_no
left join REGA1APP.CUST_ORDER_INV_HEAD_UIV_ALL coih on coii.invoice_id = coih.invoice_id
where col.company like nvl('&Company_Equal','%')
and col.contract like nvl('&Site_Equal','%')
and col.DISTRICT_CODE between nvl('&BusinessUnit_From','%') and nvl('&BusinessUnit_End','%')
and coih.invoice_date between to_date('&Date_Applied_Start','MM/DD/YYYY') AND to_date('&Date_Applied_End','MM/DD/YYYY')
and col.sale_unit_price <> 0 and col.revised_qty_due <> 0
group by col.COMPANY, col.CONTRACT, col.customer_no, pm.Part_Main_Group
Best answer by Novacura_jst
View original