Finacial postings from inventory transaction history | IFS Community
Solved

Finacial postings from inventory transaction history

  • 13 April 2021
  • 8 replies
  • 353 views

Userlevel 6
Badge +8

Hi everyone

A customer uses IFS app10. I try to finde a Report or an analysing window which shows you the Postings of a certain inventory part. Right now i am only able to go into the inventory transaction history, go into the details, go into the TAB accounting and get the financial posting for it. This way i need to open every inventory transaction and write down the voucher number.

In the window Dinstribution and manufacturing posting analysis I cannot search by inventory part.

Does anyone knows the query for a quick report how to get the Inventory postings from the inventory transaction history, searching by inventory part number?

 

Best Regards

Sophal

icon

Best answer by Dedi Tjong 13 April 2021, 12:44

View original

This topic has been closed for comments

8 replies

Userlevel 6
Badge +8

Hi Sophal,

 

By any chance does the customer use Inventory Part as a pre-posting in inventory-related transactions? If so user would be able to query specific part-related financial postings from the GL Voucher Rows Analysis window. 

 

Thanks & best regards,

Buddhika 

Userlevel 7
Badge +15

Hi Sophal,

You can use following query ;

SELECT x.*
FROM mpccom_accounting_tab x, inventory_transaction_hist_tab y
WHERE x.accounting_id=y.accounting_id AND y.part_no='&part_no'

when you use above query/When you ran the quick report with above query, system will pop-up a dialog to enter the  part No.

Hope above will help you.

Userlevel 4
Badge +6

Hi, you can write below query in quick report. Basically you join the inventory transaction history (inventory_transaction_hist2) and the query posting (mpccom_accounting).

You can put this in quick report.

SELECT <include column you want here are some examples>

ith.part_no, ith.contract COM,
                               ITH.CONTRACT CON,
                               MA.STR_CODE TRANS,
                               ITH.TRANSACTION_CODE,
                               ITH.source_ref1 OD,
                               ITH.source_ref2 LN,
                               ITH.source_ref3 RL,
                               ITH.source_ref4 RC,
                               MA.DATE_APPLIED DATED,
                               DECODE(DIRECTION, '-', -QUANTITY, QUANTITY) *
                               DECODE(MA.EVENT_CODE,
                                      'RETREVAL+',
                                      0,
                                      'RETREVAL-',
                                      0,
                                      1) QTY,
                               NVL(DECODE(DEBIT_CREDIT, 'D', MA.VALUE, 'C', -MA.VALUE),
                                   0) VAL,
                                ma.voucher_type,
                                ma.voucher_no,
                                ma.accounting_id,ma.seq
                          FROM MPCCOM_ACCOUNTING          MA,
                                INVENTORY_TRANSACTION_HIST ITH
                         WHERE ma.accounting_id = ith.accounting_id

and <your additional>);

Userlevel 5
Badge +9

Hello,

 

You can also add a new custom field with Part No to the view MPCCOM_ACCOUNTING.

In the Distribution and Manufacturing Postings Analysis window Distribution and Manufacturing Postings Analysis choose the Custom Object > Fields > Create New

 

Then Approve it and Publish.

Go back to the Distribution and Manufacturing Postings Analysis window and add this new column.

First you have to choose Reload Configuration option available under RBM Custom Objects

Then choose the Column Chooser option and add a new column to the window.

Userlevel 6
Badge +8

Hi, you can write below query in quick report. Basically you join the inventory transaction history (inventory_transaction_hist2) and the query posting (mpccom_accounting).

You can put this in quick report.

SELECT <include column you want here are some examples>

ith.part_no, ith.contract COM,
                               ITH.CONTRACT CON,
                               MA.STR_CODE TRANS,
                               ITH.TRANSACTION_CODE,
                               ITH.source_ref1 OD,
                               ITH.source_ref2 LN,
                               ITH.source_ref3 RL,
                               ITH.source_ref4 RC,
                               MA.DATE_APPLIED DATED,
                               DECODE(DIRECTION, '-', -QUANTITY, QUANTITY) *
                               DECODE(MA.EVENT_CODE,
                                      'RETREVAL+',
                                      0,
                                      'RETREVAL-',
                                      0,
                                      1) QTY,
                               NVL(DECODE(DEBIT_CREDIT, 'D', MA.VALUE, 'C', -MA.VALUE),
                                   0) VAL,
                                ma.voucher_type,
                                ma.voucher_no,
                                ma.accounting_id,ma.seq
                          FROM MPCCOM_ACCOUNTING          MA,
                                INVENTORY_TRANSACTION_HIST ITH
                         WHERE ma.accounting_id = ith.accounting_id

and <your additional>);

Perfect, this worked as explained :)

Userlevel 6
Badge +8

Here is the copy paste version including the option to choose the inventory part

 

SELECT ith.part_no, ith.contract COM,
                               ITH.CONTRACT CON,
                               MA.STR_CODE TRANS,
                               ITH.TRANSACTION_CODE,
                               ITH.source_ref1 OD,
                               ITH.source_ref2 LN,
                               ITH.source_ref3 RL,
                               ITH.source_ref4 RC,
                               MA.DATE_APPLIED DATED,
                               DECODE(DIRECTION, '-', -QUANTITY, QUANTITY) *
                               DECODE(MA.EVENT_CODE,
                                      'RETREVAL+',
                                      0,
                                      'RETREVAL-',
                                      0,
                                      1) QTY,
                               NVL(DECODE(DEBIT_CREDIT, 'D', MA.VALUE, 'C', -MA.VALUE),
                                   0) VAL,
                                ma.voucher_type,
                                ma.voucher_no,
                                ma.accounting_id,ma.seq
                          FROM MPCCOM_ACCOUNTING          MA,
                                INVENTORY_TRANSACTION_HIST ITH
                         WHERE ma.accounting_id = ith.accounting_id
AND ith.part_no = '&part_no'

Userlevel 6
Badge +8

Hello,

 

You can also add a new custom field with Part No to the view MPCCOM_ACCOUNTING.

In the Distribution and Manufacturing Postings Analysis window Distribution and Manufacturing Postings Analysis choose the Custom Object > Fields > Create New

 

Then Approve it and Publish.

Go back to the Distribution and Manufacturing Postings Analysis window and add this new column.

First you have to choose Reload Configuration option available under RBM Custom Objects

Then choose the Column Chooser option and add a new column to the window.

Hi

Can you add the pictures again they are very small even when i open it. i cannot read what is in there

 

Best Regards

Sophal

Userlevel 5
Badge +9

Hi Sophal,

 

of course.
Here you have screen shots:
 

 

Best regards,

Małgorzata