Skip to Content
0
Jan 05, 2023 at 05:06 PM

Historical BIN table

60 Views

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"