Solved

How to create an IAL from more than one view?

  • 20 July 2021
  • 4 replies
  • 525 views

Userlevel 4
Badge +9

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')

icon

Best answer by djay 20 July 2021, 15:45

View original

This topic has been closed for comments

4 replies

Userlevel 4
Badge +6

Hi Simon,

 

Please refer the below link.

https://docs.ifs.com/techdocs/Foundation1/040_administration/255_br_and_a/060_information_access_layer/020_create_ial/

 

Regards,
Oshada

Userlevel 4
Badge +9

Hi @Oshada Samarasinghe , 

Thank you for the link. But I receive an error message when I want to deploy the IAL:

bind variables not allowed for data definition operations

Failed executing statement (ORA-01027: bind variables not allowed for data definition operations

 

My Select Statement: 

select OPPORTUNITY_NO "ID", DESCRIPTION as Description, 'BO' "Type", EST_OPPORTUNITY_VALUE as Value
from BUSINESS_OPPORTUNITY
WHERE OBJSTATE <> (select &AO.BUSINESS_OPPORTUNITY_API.FINITE_STATE_ENCODE__('Cancelled') from dual) and OBJSTATE <> (select &AO.BUSINESS_OPPORTUNITY_API.FINITE_STATE_ENCODE__('Closed') from dual)
UNION
select QUOTATION_NO "ID", CATALOG_DESC as Description, 'SQ' "Type", &AO.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 &AO.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Planned') from dual) or OBJSTATE = (select &AO.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Rejected') from dual) or OBJSTATE = (select &AO.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Released') from dual)
or OBJSTATE = (select &AO.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Revised') from dual)
 UNION
select contract_NO "ID", cf$_description as Description, 'SC' "Type", &AO.CONTRACT_REVISION_UTIL_API.Get_Contract_Sales_Value_Cc(CONTRACT_NO, REV_SEQ) as Value
from CONTRACT_REVISION_cfv
WHERE OBJSTATE <> (select &AO.SALES_CONTRACT_API.FINITE_STATE_ENCODE__('Cancelled') from dual)
AND (CF$_CONTRACT_STATUS = 'Enquiry' or CF$_CONTRACT_STATUS = 'Estimated' or CF$_CONTRACT_STATUS = 'Tendered')

Userlevel 3
Badge +7

Please use &AO..(double dot) instead of &AO. 

And the view CONTRACT_REVISION_cfv is missing &AO..

Userlevel 4
Badge +9

Thank you @djay

That did the job.