Add field to Inventory audit report

[former_member280936]

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

[zal_parchem2 · Accepted solution]

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

[former_member280936 · Accepted solution]

Thank you very much.

It works for me 🙂

[zal_parchem2 · Accepted solution]

More than welcome Deepika - and thanks for marking this as "Answered".

Regards, Zal

[former_member429505]

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

[narayanis]

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

Regards

[narayanis]

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

Regards

[kothandaraman_nagarajan]

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

[former_member280936]

Yes I know. Can you please tell me the table name and field name for the document names ?

[kothandaraman_nagarajan]

Use OINM table and transaction types for Document names. You have to use case statement to get document name.

For example, when Transtype = 15 then 'Delivery'

[kothandaraman_nagarajan]

Sorry, I don't have time to create such query

[kothandaraman_nagarajan]

Hi,

What you mean by document codes?

Regards,

Nagarajan

[former_member280936]

Hello, Please see attached, I would like to see the names instead of these codes.doc-code.png