Dear Experts,
I need to write a Custom query similar to the Inventory Audit Report and pull in the Bin Locations per transaction.
I was already able to start this with the OINM table, but do not know where to find a similar table for historical Bin movements. You assistance in pointing me in the right direction will be very helpful.
Regards,
Marli
-----------
Here is what I have so far for the query:
SELECT T0."ItemCode", T0."CreateDate", T0."TaxDate", T0."DocDate", T0."DocDueDate", CASE WHEN T0."TransType" = 13 then 'AR' WHEN T0."TransType" = 14 then 'ARCredit' WHEN T0."TransType" = 15 then 'Delivery' WHEN T0."TransType" = 16 then 'SalesReturn' WHEN T0."TransType" = 203 then 'ARDown' WHEN T0."TransType" = 20 then 'Goods Receipt' WHEN T0."TransType" = 21 then 'Goods Return' WHEN T0."TransType" = 204 then 'APDown' WHEN T0."TransType" = 18 then 'APInvoice' WHEN T0."TransType" = 13 then 'AP Credit Memo' WHEN T0."TransType" = 69 then 'LandedCost' WHEN T0."TransType" = 24 then 'Incoming Payment' WHEN T0."TransType" = 25 then 'Deposit' WHEN T0."TransType" = 46 then 'VendorPayment' WHEN T0."TransType" = 57 then 'Check for Payment' WHEN T0."TransType" = 76 then 'Postdated Check' WHEN T0."TransType" = 58 then 'Inventory list' WHEN T0."TransType" = 59 then 'Good Receipt' WHEN T0."TransType" = 60 then 'Good Issue' WHEN T0."TransType" = 67 then 'Inventory Transfer' WHEN T0."TransType" = 68 then 'Work Instruction' WHEN T0."TransType" = 162 then 'Inventory Valuation' WHEN T0."TransType" = 202 then 'Production order' WHEN T0."TransType" = -2 then 'Opening Balance' WHEN T0."TransType" = -3 then 'Closing Balance' WHEN T0."TransType" = 30 then 'Journal Entry' WHEN T0."TransType" = 321 then 'Internal Reconilation' WHEN T0."TransType" = 10000046 then 'Data archive' WHEN T0."TransType" = 310000001 then 'Initial Qty' WHEN T0."TransType" = 10000071 then 'Inventory posting' END AS "Doc", T0."BASE_REF", T0."Warehouse", T0."Price", sum(T0."InQty" - T0."OutQty") as "InQTY-OutQTY", T0."Balance", T0."CalcPrice" AS "Cost", T0."CostMethod" FROM OINM T0 WHERE T0."ItemCode" = 'ELEC00039' GROUP BY T0."ItemCode", T0."CreateDate", T0."TaxDate", T0."DocDate", T0."DocDueDate", T0."TransType", T0."BASE_REF", T0."Warehouse", T0."Price", T0."Balance", T0."CalcPrice", T0."CostMethod"