Question

Accounts Payable Aging Detail List for Audit

  • 3 November 2023
  • 9 replies
  • 178 views
L
Rank
Badge +1

Hello, one of the items we need to provide to our external auditors every year is a detailed listing of our Accounts Payable aging.  Basically, a listing at the invoice level of the invoices that make up our AP.  I cannot find any report or query in IFS that would give me an accurate list.  Does anyone know of anything?  

We have created a report with this information, but there is no logic and it is a point in time snapshot. Therefore, the problem is that we keep AP open at year end and post invoices into year-end after new invoice for the new fiscal year have started to flow in as well as payment runs in the new year.  We need something with logic like the AP Summary level report out of IFS that would help give accurate details a week or two into the next month.

Let me know if anyone has suggestions!

9 replies

G
Rank
Userlevel 6
Badge +19

Hello @larissawillett 

Go to order report and run MC Supplier Ledger Detailed Age Analysis. This should give you full list of open transactions for all suppliers. The report is in PDF but you can convert to excel by a quick report or by excel plugin (report layout templates).

Other method is using Supplier Open Balance History. 1st create a category and then add balance date manually. System will generate open balances for the balance date. 

 

Another option is to create BR report in excel. There you can create a report for yourself with selected columns and layout. See one of sample I created below. 

 

 

Hope this helps

https://superp.solutions/
L
Rank
Badge +1

Thank you, @gumabs !  That report is exactly what I need.  I’m struggling to get it into Excel though.  Can you provide a few more details of this sentence “you can convert to excel by a quick report or by excel plugin (report layout templates).”  What do you mean by report layout templates?  Here is what I see for output options.

 

G
Rank
Userlevel 6
Badge +19

Hello,

If you are on IFS cloud, excel layout functionality is very limited since it is planned to be removed. 

 

I created one nice quick report for one of my customer. Add the query as quick report and see if it works for you.

 

select mc.result_key,
       mc.analysis_date,
       mc.identity,
       mc.party_name,
       mc.invoice_date,
       mc.due_date,
       mc.series_id,
       mc.invoice_no,
       li.ncf_reference,
       ifsapp.invoice_api.Get_Pay_Term_Id(li.company,
                                          ifsapp.invoice_api.Get_Invoice_Id(li.company,
                                                                            li.identity,
                                                                            li.party_type,
                                                                            li.ledger_item_series_id,
                                                                            li.ledger_item_id)) as "Payment Term",
       li.currency,
       li.curr_rate,
       ifsapp.ledger_item_api.Get_Rest_Curr_Amt_At_Date(li.company,
                                                        li.identity,
                                                        li.party_type,
                                                        li.ledger_item_series_id,
                                                        li.ledger_item_id,li.ledger_item_version,mc.analysis_date) as "Open Curr Amount",

       li.code_a as "GL Account",
       mc.col1_amt as "Not Overdue",
       mc.col2_amt as "Inverval 1",
       mc.text2 as "Interval 1 Detail",
       mc.col3_amt as "Inverval 2",
       mc.text3 as "Interval 2 Detail",
       mc.col4_amt as "Inverval 3",
       mc.text4 as "Interval 3 Detail",
       mc.col5_amt as "Inverval 4",
       mc.text5 as "Interval 4 Detail"
  from MC_SUPP_INV_DUE_DET_REP  mc, ledger_item li
 where mc.result_key like NVL('&RESULT_KEY', (SELECT MAX(A.RESULT_KEY)
                       FROM IFSAPP.ARCHIVE A
                      WHERE A.REPORT_ID = 'MC_SUPP_INV_DUE_DET_REP' AND A.SENDER=IFSAPP.FND_SESSION_API.GET_FND_USER()))

   and mc.company = li.company
   and mc.sort_column = '11'
   and li.party_type_db = 'SUPPLIER'
   and li.identity = mc.identity
   and li.ledger_item_series_id = mc.series_id
   and li.ledger_item_id = mc.invoice_no

 

Result key is report ID from IFS. In your example it is 4641101 

 

Hope this helps

https://superp.solutions/
L
Rank
Badge +1

@gumabs thanks again!  I am going to see if our IT team can help me publish this quick report.

P
Rank
Badge +3

I would be careful with the MC report. MC is the multicompany version of AP balances and you typically run audit for a single company. Of course if there is only one company in the installation then it’s not an issue ...yet. 

P
Rank
Badge +3

Also it’s worth asking why you think you cannot get any accurate report from IFS. It’s quite typical that invoices are booked to the previous year/period when the new one has been already opened.

Supplier Ledger Open Items and Supplier Ledger Analysis are absolutely correct reports to use for audit purposes as they provide a balance at the end of chosen period (or day even). Similar reports on the AR side.

You may also check whether you can exchange the data with your Auditor using the Audit Interface functionality.

G
Rank
Userlevel 6
Badge +19

After Piotr’s input, I would go for Supplier Open Balance History. Which is a IFS window and result can be easily exported to excel.

Rest is excel skills.

Thanks for input @piswpl 

https://superp.solutions/
P
Rank
Badge +3

I am not 100% sure whether it contains exactly the same data and uses the same algorithm as the Supplier Ledger Open Item report.  

Ralph Gericke
Rank
Userlevel 5
Badge +11

You might also consider to use the audit functionality. Set it up for your country using report type gdpdu.

And use the Assistant to export the data (online) and choose as a data Source CUST_AGE_ANALYSIS_AV and define a output path:

System will ask you to enter the report parameters:

And the files will be written to the defined output path:

The without *.XML contains the data.

There more Data Sources available that might be interesting.

When it comes to mass data the Background Option might be interesting as well. To use that a server path needs to be defined together with one of your admins.

Reply


Terms & ConditionsCookie settings