Skip to main content
Solved

Finacial postings from inventory transaction history

B
Hero (Partner)
Forum|alt.badge.img+10

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

Best answer by Dedi Tjong

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

View original
    IFS-SachinBhardwaj
    1 person likes this
    • IFS-SachinBhardwaj
      IFS-SachinBhardwaj
    Did this topic help you find an answer to your question?
    This topic has been closed for comments

    8 replies

    Buddhika Nanayakkara
    Hero (Partner)
    Forum|alt.badge.img+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 

    LinkedIn - https://www.linkedin.com/in/buddhikamn/
    Vimukthi Mahakumbura
    1 person likes this
    • Vimukthi Mahakumbura
      Vimukthi Mahakumbura
    X
    Superhero (Employee)
    Forum|alt.badge.img+20

    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.

    Yoshini Silva
    Dharshankumaar Mahendran
    D
    6 people like this
    • Yoshini Silva
      Yoshini Silva
    • Dharshankumaar Mahendran
      Dharshankumaar Mahendran
    • D
      Dedi Tjong
    • Vimukthi Mahakumbura
      Vimukthi Mahakumbura
    • Malgorzata Palatynska
      Malgorzata Palatynska
    • Chanu_Yazi
      Chanu_Yazi
    D
    Hero (Employee)
    Forum|alt.badge.img+7
    • Dedi Tjong
    • Hero (Employee)
    • 35 replies
    • Answer
    • April 13, 2021

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

    #ProudlyIFS #forthechallengers
    Yasas Kasthuriarachchi
    Mohamed Infaz
    Vimukthi Mahakumbura
    5 people like this
    • Yasas Kasthuriarachchi
      Yasas Kasthuriarachchi
    • Mohamed Infaz
      Mohamed Infaz
    • Vimukthi Mahakumbura
      Vimukthi Mahakumbura
    • Malgorzata Palatynska
      Malgorzata Palatynska
    • B
      bytsophan
    Malgorzata Palatynska
    Hero (Employee)
    Forum|alt.badge.img+10

    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.

    X
    Yoshini Silva
    A
    4 people like this
    • X
      Xavier Fernando
    • Yoshini Silva
      Yoshini Silva
    • A
      Aneta Kuzora
    • B
      bytsophan
    B
    Hero (Partner)
    Forum|alt.badge.img+10
    • bytsophan
    • Author
    • Hero (Partner)
    • 122 replies
    • April 13, 2021
    Dedi Tjong wrote:

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

    Yasas Kasthuriarachchi
    1 person likes this
    • Yasas Kasthuriarachchi
      Yasas Kasthuriarachchi
    B
    Hero (Partner)
    Forum|alt.badge.img+10
    • bytsophan
    • Author
    • Hero (Partner)
    • 122 replies
    • April 14, 2021

    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'

    B
    Hero (Partner)
    Forum|alt.badge.img+10
    • bytsophan
    • Author
    • Hero (Partner)
    • 122 replies
    • April 14, 2021
    malcpl wrote:

    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

    Malgorzata Palatynska
    Hero (Employee)
    Forum|alt.badge.img+10

    Hi Sophal,

     

    of course.
    Here you have screen shots:
     

     

    Best regards,

    Małgorzata

    X
    Yoshini Silva
    A
    3 people like this
    • X
      Xavier Fernando
    • Yoshini Silva
      Yoshini Silva
    • A
      Aneta Kuzora
    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings