Reconciling Received Not Invoiced Report to GL

I was struggling with reconciliation of this report multiple times, but I did not get satisfactory results for accounting services. Major issue was that Received Not Invoiced Report does not have cut-off date parameter, so it cannot produce picture at the end of accounting period - it shows current view instead, including not transferred transactions and preliminary posted invoices.

Finally, I prepared sql query comparing distribution accounting postings (only transferred to GL and with voucher date prior to cut-off date parameter) with invoice postings (taken from MAN_SUPP_INVOICE_POSTINGS, because only there I can find reference to purchase order receipt/stage payment/subcontract etc.). With this report I managed to provide “accurate” list of not invoiced receipts at period end, at least justifying accounting balance.

Detailed list of posting types to be included in such report depends on the scope of processes active (in mpccom_accounting look for M10/M14/M189 in case of inventory, M91 in case of non-inventory purchase, M200 for stage payments, from invoice postings take M18 and M91). And subcontracts are additional level of problem - SCV4 postings are tricky to get from subcontract module.

SQL query I prepared finally was too long to store as permanent quick report, I can share it as an example, but without any responsibility taken. I am rather sharing an idea here.

Hi Adam,

I would love to see your SQL query as we’ve struggled too with reconciling  Received Not Invoiced Report to GL.  I’ve created my own version of Received Not Invoiced Report to help accounting but maybe your SQL query could provide them a better solution.

Thank you,

William Klotz

Hello,

File attached (*BCC) is sql query I created to reconcile with GL non-inventory purchase (company I am working for uses IFS version 7.5 still, and creates postings for non-inventory procurement).

In case of inventory purchase, M91/M200 posting controls should be replaced/extended by  M10, M14 and M189 (if arrival charges are used). You may in fact check in GL balance analysis, what posting types are directed to account you want to analyse, and on which voucher function group.

And of course, in case any code part value is used on GRNI account (like project or cost center) you may add it to grouping and to subquery columns. 

Report simply groups records from union of procurement and invoice postings and lists all non-zero cases. It relies on posted vouchers only and uses voucher date as criteria, in my case it perfectly matches GL balance. I hope you will find it useful, as a basis for further development. 

Please note subcontracts are not covered here.

Thank you,  I’ll review the query and see if it will help us.  William

Hi Adam,

Thanks for you quick response and the query.

I am also struggling with a Apps 7.5 customer.

By the way I checked in Apps 10, to get an understating of the standard report. As I understood, we can provide a date as a parameter and it works ok, even with the transactions with Date Applied modified.

Only issue I saw was, Arrival charges are not included in the  standard report.

Cheers

Lahiru

Hi Adam,

I was able to reconcile after few attempts. I did small changes in the query as some methods did not exists.

Thanks for you support

Cheers

Lahiru

Hi, Lahiru,

You are welcome. 

You may want to share updated query here for others to use. 

I hope I could help. 

@Adam Bereda , Could you kindly let me know Posting type M187 too consider to Received not yet invoiced report comparison  ?

Hi, Theja

Yes: M10 from MPCCOM_Accounting is closed by M18 on the invoice, similarly M189 is closed by M187.

In general, idea is to fetch from  MPCCOM_Accounting and inv_accounting_row transactions on specific accounts, build a common reference for them (PO,  line, release, receipt) and list references with non-zero balances.

And LahiruD shared the query in previous post, I think it’s working

@Adam Bereda , Thanks a lot for the quick response.

Hello,

We have the type of issue with IFS Cloud, have you got an SQL script for cloud.

The one we picked from the conversationis not working.

Thak you

Christophe

Hello,
Even in Cloud the design of accounting entries for procurement did not change significantly. 
If the query from Lahiru do not work, what is the issue? 

Can you tell me what are you buying, inventory parts or non-inventory items? 
How are your parts valuated? 

Hi Thank you for your reply !

I got the message “SQL Expression is wrong” when I am trying to save the quick report with the query from Lahiru.

Kind regards.

Christophe.

I think query from ​@LahiruD does not specify application owner as prefix for table name:
e.g instead of MAN_SUPP_INVOICE_POSTINGS we should use IFSAPP.MAN_SUPP_INVOICE_POSTINGS, if IFSAPP is userid of installation owner.

similarly, appowner should be prefix of api function calls like  purchase_order_api..get_vendor_no.

Assuming that IFSAPP is an owner, attached is the updated SQL