Skip to Content
0

Add field to Inventory audit report

Oct 04, 2017 at 09:59 AM

100

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Zal Parchem
Oct 12, 2017 at 02:28 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thank you very much.

It works for me :)

0

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

Regards, Zal

1
Nagarajan K Oct 05, 2017 at 11:48 AM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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

0

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'

1

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

0
Nagarajan K Oct 04, 2017 at 12:38 PM
0

Hi,

What you mean by document codes?

Regards,

Nagarajan

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

doc-code.png (19.9 kB)
0
avatar image
Former Member Oct 07, 2017 at 08:37 AM
0

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

Regards

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Oct 07, 2017 at 08:37 AM
0

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

Regards

Share
10 |10000 characters needed characters left characters exceeded
Bhupendra Jadhav Oct 12, 2017 at 02:45 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded