Navigation with Datasource Condition performance problem

hi Johan - I’ve run an explain plan on your code and you have 2 full table access scans which will be slowing the query down. They are also on reciept_info_tab and purchase_receipt_tab which can be very large tables.
I would look to try and optimise the query so that where ever possible these tables/views are accessed via an indexed field.

You also have 4 sub-selects within the where clause - I would try and minimise these if possible.

I’ll have a quick look and see if I can help improve the code for you.

​@NPGIFSJOMAII 

Johan

Try this as in your data source - I changed the view, and added some API calls to collect the authoriser and the current user person id

select order_no

 from purchase_receipt_info
where
delivery_date between  SYSDATE - 365 AND SYSDATE -30
and QTY_ARRIVED > QTY_INVOICED
and FINALLY_INVOICED_DATE is null
and purchase_order_api.Get_Authorize_Code(order_no) = person_info_api.Get_Id_For_Current_User()

and objstate   != 'Cancelled'

The finally invoiced date is in the UIV you used - so there is no need for the join to the RI view you used (and I’m not using that as the primary source anyway)

​@WyrDavidB  Thank you!

Unfortenately this code took longer for me to run a couple of seconds, and I could not navigate to page/PurchaseDeliveryStatistics/List with Datasource Condition because the data source had an invalid identifier. 

Anyway, I changed my query a little bit and changed navigation to page/Receipts/List instead, query + navigation took below one second :) 

Another question from a non developer person.. Is there a liberary for those API-calls i can search in?

Regards,

Johan 

​@NPGIFSJOMAII - I’m not aware of any library of API calls. 
is you use a sql developer tool, you can search for _api packages in the navigator bar, generally using the view name with _api at the end. 

Glad that you have been able to resolve your issue.

Best Regards

Dave