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
Page 1 / 1
Please replace ifsinfo with &IAL in the data source designer view and that should work.
Please replace ifsinfo with &IAL in the data source designer view and that should work.
I have IFSINFO in a couple IAL views within the Lobby Data Source Designer which work fine with Lobby Elements displaying data from them.
What is the difference between using IFSINFO and &IAL? I’m only new at working with IAL’s in IFS myself, so just curious (as I don’t know).
Hi @DevBob, I’m only newish at IAL and working with Oracle (more familiar with MS SQL) so apologies in advance if the below doesn’t work.
Is it because you’ve enclosed the Trademark field alias with double quotes?
I see in your other field alias (example 1) you haven’t used quotations, and indeed in my own IALs which are used in Lobby DS’s, I don’t use quotations. With the Trademark alias (and some of the others) I see you’ve used quotations (example 2):
Alternatively if you wanted to keep the double quotes, does it work if you updated the “Column” column in the lobby data source to have the quotations (example below)?
yes, as Garak said it should be the “” in the alias. It’s standard Oracle behavior. Try e.g.
Select trademark from (Select ‘x’ “trademark” from dual) in PL/SQL and it will run into an error.
Remove the “” and it should work.
thx all! Removed all my double-quotes from my IAL and used as and that corrected that error!
Now I’m getting a new error:
(Fyi, I took out my conditions because that was throwing another error. Do I need the conditions if they’re already in the IAL?)
So I took out my Group by field, hit Preview, it ran w/o errors but I get no results:
oh wait, an error did come back about 45 seconds later:
looks like I have to add my parms…
how do I add parameters? Looks like the control is not working. Don’t see any input fields:
when I click on Parameters it tries to run it again and I get my previous error.
Hey DevBob,
you need to remove the Parameters from the IAL and use them in the where condition like you did in the 2nd screenshot. Then they should Show up as Parameters.
/Johannes
thx.When I had conditions it errored out not liking the field.
also, it doesn’t like my file predicates on condition fields. When I remove it it still fails on that field:
I got this to work, however it doesn’t prompt me to enter the parms:
The parameters should pop up when you hit the preview button. Here for a standard lobby:
If the don’t i do not really know.
thx for your reply.
It seems that once you run it initially it does prompt, but not on successive times clicking Preview. This could be as designed but not sure. I’ve SHIFT-+F5 the screen with on luck. I’ll try ending my session...
...just closed my session, reopened a new one and it still doesn’t prompt- interesting.
Here’s my current error on another Designer, with the IAL SQL to follow:
and ( BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) > 0 and LINE_ITEM_NO <= 0 and OBJSTATE in ('Delivered', 'PartiallyDelivered', 'Picked', 'Released', 'Reserved') )) , '$99,999,999.99') as All_Orders_Gross_Amount,
sum( case when TRUNC(wanted_delivery_date) between TRUNC(sysdate - INTERVAL '2' DAY) and TRUNC(sysdate - INTERVAL '1' DAY) then 1 else 0 end ) as One_Two_Days_Past_Due,
sum( case when TRUNC(wanted_delivery_date) between TRUNC(sysdate - INTERVAL '5' DAY) and TRUNC(sysdate - INTERVAL '3' DAY) then 1 else 0 end ) as Three_Five_Days_Past_Due,
sum( case when TRUNC(wanted_delivery_date) between TRUNC(sysdate - INTERVAL '10' DAY) and TRUNC(sysdate - INTERVAL '6' DAY) then 1 else 0 end ) as Six_Ten_Days_Past_Due,
sum( case when TRUNC(wanted_delivery_date) <= TRUNC(sysdate - INTERVAL '11' DAY) then 1 else 0 end ) as Plus_Ten_Days_Past_Due,
to_char( SUM ( case when TRUNC(wanted_delivery_date) <= TRUNC(sysdate - INTERVAL '1' DAY) then ( SELECT SUM( BASE_SALE_UNIT_PRICE * (BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) )
FROM rega1app.CUSTOMER_ORDER_LINE col
WHERE col.order_no = co.order_no
and ( BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) > 0 and LINE_ITEM_NO <= 0 and OBJSTATE in ('Delivered', 'PartiallyDelivered', 'Picked', 'Released', 'Reserved') ) else 0 end ) , '$99,999,999.99') as Past_Due_Order_Gross_Amount
from rega1app.CUSTOMER_ORDER co
where state not in ('Invoiced/Closed', 'Cancelled') and contract in ('W','E22') and order_no not like 'AD%' and order_id <> 'RO'
group by ROLLUP(state)
Am i seeing it wrong or are you missing a underscore in Column All Orders?
yup; that’s it! thx! Made that correction; getting closer! …
now I get this:
it seems if the field in Conditions isn’t in the list of Columns it errors out, which shouldn’t be.
ok, this error is strange; the columns are unique:
Yeah, you don’t have a column with that Name in the ial. You Need to include each column you are using as a condition into the IAL. You are now selecting from that IAL only, not from Customer Order, so each field you are using Needs to be present.
ok, that makes sense. Kind of limits what we can do here. Thx for your reply.
I did get it working:
I also found out that you can’t enter numbers in the Name column. That was causing my unique error above.
…
