ITEM WISE BIN LOCATION & STOCK

[former_member431018]

Hi Experts,

Actually I wrote one query for "Item wise Bin location" but while I am executing the query,For some items Bin Location displays NULL values, but some more items it displays duplicate bin locations.

QUERY:

SELECT DISTINCT T0.ItemCode, T7.BinCode , isnull(T3.OnHandQty,0) as 'On Hand', T6.Price as 'LP Rate', (T3.OnHandQty * T6.Price) as 'Total Value', T4.[ItmsGrpNam] as 'Item Category', T1.ItemName, T5.FirmName as 'Net / Dis. Price',T1.U_Brand as 'Brand',T1.U_U_Model as 'Model',T1.U_ItemType as 'ItemType',T1.U_Moving,T1.QryGroup1 as 'Equipments',T1.QryGroup2 as 'Accessories',T1.QryGroup3 as 'Spares',T1.QryGroup4 as 'GardenTools',T1.QryGroup5 as 'Non Trading',T1.QryGroup6 as 'Other Equipments',T1.QryGroup7 as 'R&D Equipments', T1.QryGroup8 as 'Equipment Asper Price List', T1.QryGroup10 as 'Asset Items',

T0.WhsCode as 'Warehouse'

FROM OITW T0

INNER JOIN OITM T1 on T0.ItemCode = T1.ItemCode

LEFT JOIN OBIN T7 ON T7.AbsEntry = T0.DftBinAbs

LEFT OUTER JOIN OIBQ T3 on T0.ItemCode=T3.ItemCode and T0.WhsCode = T3.WhsCode

LEFT JOIN OITB T4 ON T4.ItmsGrpCod = T1.ItmsGrpCod

LEFT JOIN OMRC T5 ON T5.FirmCode = T1.FirmCode

INNER JOIN ITM1 T6 ON T1.ItemCode = T6.ItemCode

WHERE T3.OnHandQty <>'0.00' and T1.ItemType <> 'F' and T1.QryGroup8 ='Y' and T6.PriceList = '7'

GROUP BY T0.ItemCode, T1.ItemName, T1.U_Brand ,T0.WhsCode,T6.Price ,T7.BinCode,T1.U_U_Model,T1.U_ItemType,T1.U_Moving,T1.QryGroup1,T1.QryGroup2,T1.QryGroup3,T1.QryGroup4,T4.[ItmsGrpNam],T5.FirmName,T3.OnHandQty,T1.QryGroup5,T1.QryGroup6,T1.QryGroup7,T1.QryGroup8,T1.QryGroup10

ORDER BY T4.[ItmsGrpNam]

Screenshot:

Experts for your reference I am attaching screenshot,In that query is displaying duplicate and null bin locations that i have selected.

Hope all are understood my requirement.

Please help me out to make this Query(Bin Location)

Hope for the best reply ASAP.

Thank You,

Regards,

Ramya.

[agustin_marcoscividanes · Accepted solution]

Hi

I think you have to add a new condition in left outer join with bin location:

LEFT OUTER JOIN OIBQ T3 on T0.ItemCode=T3.ItemCode and T0.WhsCode = T3.WhsCode and T7.AbsEntry = T3.BinAbs

Kind regards

Agustín Marcos Cividanes

[former_member431018 · Accepted solution]

Hi Mr.Marcos,

Thanks for your reply,

I given the condition like this:

LEFT OUTER JOIN OIBQ T3 on T0.ItemCode=T3.ItemCode and T0.WhsCode = T3.WhsCode LEFT JOIN  OBIN T7 ON  T7.AbsEntry = T3.BinAbs

Now query executing successfully with out duplicate and null records.

Thank You,

Regards,

Ramya.

[former_member212181 · Accepted solution]

Hi ramya,

Please try below query

Changes done

1) change the link of OBIN table from OITW to OIBQ table

2) remove where condition of Pricelist Num = 7 and use the same condition on ITM1 Join

3) remove distinct as you already using group by function.

----Query Starts

SELECT

T0.ItemCode, T7.BinCode , isnull(T3.OnHandQty,0) as 'On Hand', T6.Price as 'LP Rate', (T3.OnHandQty * T6.Price) as 'Total Value', T4.[ItmsGrpNam] as 'Item Category', T1.ItemName, T5.FirmName as 'Net / Dis. Price',T1.U_Brand as 'Brand',T1.U_U_Model as 'Model',T1.U_ItemType as 'ItemType',T1.U_Moving,T1.QryGroup1 as 'Equipments',T1.QryGroup2 as 'Accessories',T1.QryGroup3 as 'Spares',T1.QryGroup4 as 'GardenTools',T1.QryGroup5 as 'Non Trading',T1.QryGroup6 as 'Other Equipments',T1.QryGroup7 as 'R&D Equipments', T1.QryGroup8 as 'Equipment Asper Price List', T1.QryGroup10 as 'Asset Items',

T0.WhsCode as 'Warehouse'

FROM OITW T0

INNER JOIN OITM T1 on T0.ItemCode = T1.ItemCode

--LEFT JOIN OBIN T7 ON T7.AbsEntry = T0.DftBinAbs

LEFT OUTER JOIN OIBQ T3 on T0.ItemCode=T3.ItemCode and T0.WhsCode = T3.WhsCode

LEFT JOIN OBIN T7 ON T7.AbsEntry = T3.BinAbs

LEFT JOIN OITB T4 ON T4.ItmsGrpCod = T1.ItmsGrpCod

LEFT JOIN OMRC T5 ON T5.FirmCode = T1.FirmCode

INNER JOIN ITM1 T6 ON T1.ItemCode = T6.ItemCode and T6.PriceList = '7'

WHERE T3.OnHandQty <>'0.00'

  and T1.ItemType <> 'F'

  and T1.QryGroup8 ='Y'

GROUP BY T0.ItemCode, T1.ItemName, T1.U_Brand ,T0.WhsCode,T6.Price ,T7.BinCode,T1.U_U_Model,T1.U_ItemType,T1.U_Moving,T1.QryGroup1,T1.QryGroup2,T1.QryGroup3,T1.QryGroup4,T4.[ItmsGrpNam],T5.FirmName,T3.OnHandQty,T1.QryGroup5,T1.QryGroup6,T1.QryGroup7,T1.QryGroup8,T1.QryGroup10

ORDER BY T4.[ItmsGrpNam]

-----Query Ends