Hi Matthew,

Are you able to share a sample report showing the current logic that you’re applying to your report?

I have built some work in progress reports in the Construction space but would like to see if any of it would be relevant for you. 
 

Jason

The below is the code from our report - it was written by a Finance user- so looks at code part values to group. It has a number of little foibles that make it far from ideal - but there is nothing out of the Box. We are open to any and all suggestions - the report on a day-to-day is used to make sure WO are processed correctly and on a monthly basis for reporting of financial figures.

Select 
WI.Wo_NO, 
WO.state, 
Trunc (Reg_date) As "Entry Date", 
(Select c.name from customer_info c where c.customer_id=wo.Customer_No) as "Customer",
wo.work_type_id as "Category", 
wo.Contract_id as "Contract",
wo.Line_No as "Contract line",
Wi.Profit_Centre,
Sum (WI.Amount) as "Amount",
Case when sysdate-reg_date<=28 then Sum (WI.Amount) end as "<4 WK",
Case when sysdate-reg_date>28 and sysdate-reg_date<=56 then Sum (WI.Amount) end as "< 8 WK",
Case when sysdate-reg_date>56 and sysdate-reg_date<=84 then Sum (WI.Amount) end as "< 12 WK",
Case when sysdate-reg_date>84 then Sum (WI.Amount) end as ">12 WK"


From (Select Gl.Trans_code, GL.Reference_number, GL.Amount, Gl.Voucher_type, gl.Reference_Serie, GL.Code_c as Profit_Centre, GL.Mpccom_accounting_id,GL.Voucher_No,GL.Row_No, PH.order_no, 

CASE when GL.Reference_serie ='PUR ORDER' then NVL((select distinct PO.Demand_Order_No from PURCHASE_REQ_LINE_all PO where PO.demand_code_db='WO' and PO.Order_no=GL.Reference_number and PO.assg_line_no=PH.release_no ),GL.Reference_number) when

 GL.Reference_serie ='SI' Then NVL((Select Max(PO.Demand_Order_No) from PURCHASE_REQ_LINE_ALL PO where PO.Order_No= (select Max(PP.PO_REF_Number) From POSTING_PROPOSAL_INVOICE_QRY PP where PP.Invoice_No=GL.Reference_Number)), GL.Reference_number) 

ELSE GL.Reference_Number END as WO_NO from GEN_LED_VOUCHER_ROW_UNION_QRY GL left outer join PURCHASE_TRANSACTION_HIST PH on To_Char(PH.Transaction_id)=To_Char(GL.Mpccom_accounting_id) where GL.account='1629' and trans_code<>'TX6' 

Union Select Null,Null, Wc.Amount, Ver_Type, 'WOCODE', CODE_C, Transaction_id, Ver_No, null, null,To_Char(WO_no) From WORK_ORDER_CODING_UIV WC where Ver_type='E' and code_A='1629' and CSS_TYPE<>'Posted' ) WI left outer join Work_order wo on WI.WO_no=to_char(wo.WO_No) group by WI.WO_No, Wi.Profit_Centre, Reg_date,Customer_No, WO.state, wo.contract_id, wo.Work_type_id,wo.line_no having Sum(Amount)<>0 order by WI.WO_No

I have attached some lines from an old report.

Hi Matthew,

Thanks for sharing the code and example sheet. Is there a suitable time that we can connect / share screens to talk through the transactions on the screen? I’m based in Victoria, Australia so my time zone is AEST (but don’t let that be a lmitting factor, I’d be OK with a late night or early morning chat, whatever suits you). If you have MS Teams (or similar), you can send an invite to jasondereuck@hotmail.com

I do think there are alternative options through IFS Business Reporter but it would be good to walk through some of the entries and get more understanding of the report’s intended use I.e. Is it to drive operations to complete their work or to substantiate financial entries at the end of the month (costs / revenue adjustments).

Thanks

Jason

Matthew,

You have not stated that your question has been answered. So I add an alternative.

Prepare an IAL using the IAL Object Developer. Dependent on how frequent the information is requested you will have to decide how up to date the data is. In this case I would say that the information is rebuild every night.

Steve

In Apps 10 they introduced Work Task Cost Codes which will book your WIP for work orders and based on the rules/event you define in the system, IFS will rebook your WIP to it’s final expense account in the G/L.  I don’t think there is a “report” for this, but you could simply run a G/L Balance Analysis on your WIP accounts to see what is remaining in WIP at the end of the month.