Hi everyoneA 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 RegardsSophal

Finacial postings from inventory transaction history

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

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.

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

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, 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 :)

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'

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