Question

Lobby Element for Late PO's?

  • 1 October 2020
  • 1 reply
  • 171 views
B
Rank
Badge +2

I’m working on setting up a lobby element to show late PO’s per vendor. This is what I have as of currently (mind you i’m relatively new to IFS and SQL);

 

This is the request for the element;


I wanted to post and see if anyone had the ability to help me with getting this in order. Thank you in advance. We have an element that does this currently, but with the PO lines rather than the PO as a whole. And if it helps, we are on APPS9, UPD16 I believe. 

 

1 reply

paul harland
Rank
Userlevel 7
Badge +24

Hi @BaileyGross12 

Firstly, this is very difficult; not exactly a basic SQL requirement!  Anybody, regardless of skill would find this difficult.

 

You are trying to do an aggregation of data - there are 2 ways to do this.  You can either do it in the lobby data source, or in the element.

In this case, I think you need to do it inside the data source.

You need to add 2 columns.  The first is just a ‘1’; representing 1 towards the total of purchase orders that meet the condition.  When summed up, this will give you the total.

The second column will be “IsLate”; either 1 (late) or 0 (not late).  

To do that, you will probably need a subquery, embedded in the column, something like the following.  But please note these are not real column names.  Basically this is saying “if there exists an order line on this order which is late, then “1”, else “0”.)

 

(Case when exists (select 1 from purchase_order_line L where L.order_no = purchase_order.order_no L.wanted_delivery_date<sysdate and status = ‘Released’) then 1 else 0 end)

 

If you can get that working, then you will add a third column that combines these in a calculation (100 * Late / Total).  When you do that, you need to be wary of the divide by zero error in Oracle.  You can use a NULLIF command to prevent that error occurring. 

Good luck!

Reply


Terms & ConditionsCookie settings