Hi,
I want to create a Lobby Data Source which shows the sum of values stored in 3 views. Therefore, I need to create an IAL. But I struggle to get the desired result. I created the SQL Statement in the SQL Query Tool and it gives the result I want. But how do I implement the statement in an IAL?
Your help is appreciated.
The SQL Statement:
select OPPORTUNITY_NO "ID", DESCRIPTION as Description, 'BO' "Type", EST_OPPORTUNITY_VALUE as Value
from BUSINESS_OPPORTUNITY
WHERE OBJSTATE <> (select ifsapp.BUSINESS_OPPORTUNITY_API.FINITE_STATE_ENCODE__('Cancelled') from dual) and OBJSTATE <> (select ifsapp.BUSINESS_OPPORTUNITY_API.FINITE_STATE_ENCODE__('Closed') from dual)
UNION
select QUOTATION_NO "ID", CATALOG_DESC as Description, 'SQ' "Type", ifsapp.Order_Quotation_Line_API.Get_Base_Sale_Price_Total(QUOTATION_NO, LINE_NO, REL_NO, LINE_ITEM_NO) as Value
from Order_Quotation_Line
WHERE OBJSTATE = (select ifsapp.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Planned') from dual) or OBJSTATE = (select ifsapp.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Rejected') from dual) or OBJSTATE = (select ifsapp.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Released') from dual) or OBJSTATE = (select ifsapp.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Revised') from dual)
UNION
select contract_NO "ID", cf$_description as Description, 'SC' "Type", ifsapp.CONTRACT_REVISION_UTIL_API.Get_Contract_Sales_Value_Cc(CONTRACT_NO, REV_SEQ) as Value
from CONTRACT_REVISION_cfv
WHERE OBJSTATE <> (select ifsapp.SALES_CONTRACT_API.FINITE_STATE_ENCODE__('Cancelled') from dual)
AND (CF$_CONTRACT_STATUS = 'Enquiry' or CF$_CONTRACT_STATUS = 'Estimated' or CF$_CONTRACT_STATUS = 'Tendered')