Solved

Mapping IAL to Lobby Data Source Designer


Userlevel 4
Badge +10
  • Sidekick (Customer)
  • 119 replies

Does anyone know of any IFS Documentation on how to map IAL columns to the Designer ?

Here’s what we’re trying and we get an error:

 

 

 

 

IAL:

select
col.COMPANY as Company,
col.CONTRACT as Site,
col.customer_no || ' - ' || max(rega1app.CUST_ORD_CUSTOMER_API.Get_Name(col.CUSTOMER_NO)) as Customer_Name,
pm.Part_Main_Group "Trademark",

ROUND(SUM( col.revised_qty_due * col.sale_unit_price), 2) "Total_Revenue",
ROUND(SUM( col.revised_qty_due * col.cost ), 2) "Total_Sales_Cost",

ROUND( SUM( ((col.revised_qty_due * col.sale_unit_price) - (col. revised_qty_due * col.cost))), 2) "Margin",

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

icon

Best answer by Novacura_jst 24 June 2020, 15:50

View original

21 replies

Userlevel 1
Badge +6

Please replace ifsinfo with &IAL in the data source designer view and that should work.

Userlevel 5
Badge +9

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

 

Userlevel 5
Badge +9

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

 

Userlevel 4
Badge +7

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. 

Userlevel 4
Badge +10

thx all! Removed all my double-quotes from my IAL and used as and that corrected that error!  :grinning:

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

 

 

Userlevel 4
Badge +10

So I took out my Group by field, hit Preview, it ran w/o errors but I get no results:  :zipper_mouth:

 

 

Userlevel 4
Badge +10

oh wait, an error did come back about 45 seconds later:

looks like I have to add my parms…

 

 

Userlevel 4
Badge +10

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.

 

 

Userlevel 4
Badge +7

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

Userlevel 4
Badge +10

thx.When I had conditions it errored out not liking the field.

 

 

Userlevel 4
Badge +10

also, it doesn’t like my file predicates on condition fields. When I remove it it still fails on that field:

 

 

 

 

 

Userlevel 4
Badge +10

I got this to work, however it doesn’t prompt me to enter the parms:

 

 

Userlevel 4
Badge +7

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.

Userlevel 4
Badge +10

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...

Userlevel 4
Badge +10

...just closed my session, reopened a new one and it still doesn’t prompt- interesting.

Userlevel 4
Badge +10

Here’s my current error on another Designer, with the IAL SQL to follow:

 

 

IAL:

select
state as Status,
count(*) as All_Orders,

to_char( SUM (( 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')
)) , '$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)

Userlevel 4
Badge +7

Am i seeing it wrong or are you missing a underscore in Column All Orders?

Userlevel 4
Badge +10

yup; that’s it! thx! Made that correction; getting closer! :smiley:  …

 

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.

 

 

Userlevel 4
Badge +10

ok, this error is strange; the columns are unique:

 

 

Userlevel 4
Badge +7

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.

Userlevel 4
Badge +10

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.

bad:  1-2 Days Past Due

good: On Two Days Past Due

 

 

Reply