Skip to Content

Add field to Inventory audit report

Hello All,

I would like to pull up the document name instead of the document codes in the inventory audit report.

Can anyone help me with this please ?

Thank you

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Oct 12, 2017 at 02:28 PM

    Here you go Deepika. Give this SQL a try. It might not contain all of documents you need so some modifications might be in order the Trans Type.

    COPY WHAT IS UNDER THIS LINE DOWNWARDS TO USE AS SQL


    --H-IN Item History File Recon for IAR and TB Ver 1 ZP 2016 02 17

    --DESCRIPTION: SQL provides detail required to research inventory transactions in the OINM History File. This SQL can also be used to reconcile the Inventory General Ledger Accounts - the Inventory Audit Report - Trial Balance.

    --USAGE: Finance and Warehouse Departments

    --AUTHOR(s):

    --Version 1 Zal Parchem 17 February 2016

    SELECT

    'SQL' AS 'Source',

    T0.DocDate AS 'Posting Date',

    T0.BASE_REF AS 'Origin No.',

    CASE

    WHEN T0.TransType = 13 THEN 'A/R Invoices'

    WHEN T0.TransType = 14 THEN 'A/R Credit Memos'

    WHEN T0.TransType = 15 THEN 'Deliveries'

    WHEN T0.TransType = 16 THEN 'Returns'

    WHEN T0.TransType = 18 THEN 'A/P Invoices'

    WHEN T0.TransType = 19 THEN 'A/P Credit Memos'

    WHEN T0.TransType = 20 THEN 'Goods Receipt PO'

    WHEN T0.TransType = 21 THEN 'A/P Returns'

    WHEN T0.TransType = 59 THEN 'Receipt frm Production'

    WHEN T0.TransType = 60 THEN 'Goods Issue'

    WHEN T0.TransType = 67 THEN 'Inventory Transfers'

    WHEN T0.TransType = 162 THEN 'Inventory Revaluations'

    WHEN T0.TransType = 202 THEN 'Production Order'

    ELSE 'Research'

    END AS 'Details',

    SUM(T0.TransValue) AS 'C/D (SC)',

    T0.ItemCode AS 'Item Number',

    T3.FormatCode

    FROM OINM T0

    LEFT OUTER JOIN OITM T1

    ON T0.ItemCode = T1.ItemCode

    LEFT OUTER JOIN OACT T3

    ON T0.[InvntAct] = T3.[AcctCode]

    WHERE

    T0.DocDate >='[%0]'

    AND T0.DocDate <= '[%1]'

    AND T3.FormatCode = '[%2]'

    GROUP BY

    T0.DocDate,

    T0.BASE_REF,

    T0.ItemCode,

    T3.FormatCode,

    T0.TransType

    ORDER BY

    T0.DocDate,

    T0.BASE_REF

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 05, 2017 at 11:48 AM

    Only possible by creating customized query. By standard it is not possible to change.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 04, 2017 at 12:38 PM

    Hi,

    What you mean by document codes?

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 07, 2017 at 08:37 AM

    By querying OINM table you can design your own query wherein you can use transaction types.

    Regards

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 07, 2017 at 08:37 AM

    By querying OINM table you can design your own query wherein you can use transaction types.

    Regards

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 12, 2017 at 02:45 PM

    You can do it by creating customized query using OINM table, but by standard it is not possible to change.

    Add comment
    10|10000 characters needed characters exceeded