Query to show "Production Order" and related "Issue for Production" with bin locations

[azacc]

Hi, I'm trying to write a query in SAP B1 HANA to show a production order along with its Issue for Production including the Bin Location but whenever I try the bin location is blank?
Any help appreciated.
This is where I got to:
SELECT
T1."ItemCode", T1."ItemName", T3."BinCode", T0."TransNum", T0."ItemCode" AS "IssueItem", T2."OnHandQty" AS "QuantityInBin"
FROM "OINM" T0
INNER JOIN "OITM" T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN "OIBQ" T2 ON T0."ItemCode" = T2."ItemCode"
INNER JOIN "OBIN" T3 ON T2."BinAbs" = T3."AbsEntry"
WHERE T0."TransType" = '59'
But I need to start with the Production order and link in the Issue for Production.

Thanks

[azacc · Accepted solution]

Thanks for everyone's help on this, I got this sorted and fully working as needed. Posting the query here in case anyone else needs it: - One of my issues, I has the OBIN as a LEFT JOIN and it should've been an INNER JOIN.

SELECT
T0."DocEntry" AS "PO Doc Entry", T0."DocNum" AS "PO Doc No", T0."PostDate" AS "PO Doc Date", T0."ItemCode",
T0."ProdName", T0."Status", T0."Type", T0."PlannedQty", T0."CmpltQty", T0."RjctQty", T0."StartDate",
T0."DueDate", T0."OriginNum", T0."OriginType", T0."CardCode", T0."Warehouse", T0."Project", T0."Printed",
T1."LineNum"+1, T1."VisOrder", T1."ItemCode" AS "Line Item Code", T1."ItemName" AS "Line Item Description",
T1."BaseQty" AS "Line Base Qty", T1."PlannedQty" AS "Line Planned Qty", T1."IssuedQty" AS "Line Issued Qty",
T1."wareHouse", T1."UomCode", T1."AdditQty", T1."PickStatus", T1."PickQty", T1."ReqDays", T1."Status",
T3."DocEntry" AS "Issue Doc Entry", T3."DocNum" AS "Issue Doc No", T3."DocDate" AS "Issue Doc Date",
T2."Quantity" AS "Issued Qty", T2."ItemCode" AS "Issue Item Code", T2."Dscription" AS "Issue Item Description",
T2."unitMsr",
T8."ExpDate",
T7."BatchNum", T7."WhsCode",
T6."BinCode",
T2."Quantity" AS "Issued Qty",
T10."InvntryUom", T10."ItmsGrpCod",
T11."ItmsGrpNam"

FROM OWOR T0
left join WOR1 T1 ON T0."DocEntry"= T1."DocEntry"
left JOIN IGE1 T2 ON T1."DocEntry" = T2."BaseEntry" and T1."LineNum" = T2."BaseLine" and T2."BaseType"=202
left join OIGE T3 ON T3."DocEntry"=T2."DocEntry"
LEFT JOIN OILM T4 ON T2."DocEntry" = T4."DocEntry" AND T2."ObjType" = T4."TransType" AND T2."ItemCode" = T4."ItemCode" AND T4."LocCode" = T2."WhsCode"
LEFT JOIN OBTL T5 ON T4."MessageID" = T5."MessageID"
INNER JOIN OBIN T6 ON T5."BinAbs" = T6."AbsEntry"
LEFT JOIN IBT1 T7 ON T2."ObjType" = T7."BaseType" AND T2."ItemCode" = T7."ItemCode" AND T2."DocEntry" = T7."BaseEntry" AND T2."LineNum" = T7."BaseLinNum" AND T2."WhsCode" = T7."WhsCode"
LEFT JOIN OBTN T8 ON T8."DistNumber" = T7."BatchNum"
left JOIN ITL1 T9 ON T8."SysNumber" = T9."SysNumber" AND T2."ItemCode" = T9."ItemCode" AND T5."SnBMDAbs" = T9."MdAbsEntry"
LEFT JOIN OITM T10 ON T10."ItemCode" = T2."ItemCode"
LEFT JOIN OITB T11 ON T10."ItmsGrpCod" = T11."ItmsGrpCod"

[PaulB1ID]

Hi @azacc ,

Try this query, might need to be adjusted to fit your requierment

SELECT DISTINCT T1."DocEntry", T0."DocNum", T0."DocDate", 
T0."CardName", T1."ItemCode", T1."Dscription",  T1."unitMsr", T6."ExpDate",  T5."BatchNum", 
T5."WhsCode", T4."BinCode",T4."SL1Code",
T3."Quantity",T9."InvntryUom"
FROM OWOR A
left join WOR1 B on A."DocEntry"=B."DocEntry"
left JOIN IGE1 T1 ON B."DocEntry" = T1."BaseEntry" and B."LineNum" = T1."BaseLine" and T1."BaseType"=202
left join OIGE T0 on T0."DocEntry"=T1."DocEntry"
LEFT JOIN OILM T2 ON T1."DocEntry" = T2."DocEntry" AND T1."ObjType" = T2."TransType" AND T1."ItemCode" = T2."ItemCode" AND T2."LocCode" = T1."WhsCode"
LEFT JOIN OBTL T3 ON T2."MessageID" = T3."MessageID"
LEFT JOIN OBIN T4 ON T3."BinAbs" = T4."AbsEntry"
LEFT JOIN IBT1 T5 ON T1."ObjType" = T5."BaseType" AND T1."ItemCode" = T5."ItemCode" AND T1."DocEntry" = T5."BaseEntry" AND T1."LineNum" = T5."BaseLinNum" AND T1."WhsCode" = T5."WhsCode"
LEFT JOIN OBTN T6 ON T6."DistNumber" = T5."BatchNum" 
left JOIN ITL1 T8 ON T6."SysNumber" = T8."SysNumber" AND T1."ItemCode" = T8."ItemCode" AND T3."SnBMDAbs" = T8."MdAbsEntry"

INNER JOIN OITM T9 ON T9."ItemCode" = T1."ItemCode"

Regards,

Paul

[azacc]

Thanks Pau,

[azacc]

Thanks Paul, This is where I'm at so far, it seems to be working but to get it to not duplicate lines I'm using Distinct and a dodgy Where clause to block out lines where the bin location is blank, not ideal. If anyone can take a look and see how I can improve this to not need the where clause that would be great:

[azacc]

Sorry, it keeps posting before I finish typing! Code here: SELECT DISTINCT T0."DocNum" AS "PO No.", T0."ItemCode", T0."ProdName" AS "Description", T0."Status", T0."Type", T0."PlannedQty", T0."CmpltQty", T0."RjctQty", T0."PostDate" AS "DocDate", T0."StartDate", T0."DueDate", T0."CardCode", T0."Warehouse" AS "Header Warehouse", T0."Project", T0."Printed", T1."LineNum" + 1 AS "LineNum", T1."VisOrder"+1 AS "VisOrder", T1."ItemCode" AS "Line ItemCode", T1."ItemName" AS "Line Description", T1."BaseQty", T1."PlannedQty" AS "Line Planned Qty", T1."IssuedQty", T1."wareHouse" AS "Line Warehouse", T10."DocNum" AS "Issue No.", T10."DocDate" AS "Issue Doc Date", T14."BinCode" FROM OWOR T0 INNER JOIN WOR1 T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode" LEFT JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" LEFT OUTER JOIN OCRD T4 ON T0."CardCode" = T4."CardCode" LEFT OUTER JOIN ORDR T5 ON T0."OriginAbs" = T5."DocEntry" AND T0."LinkToObj" = '17' LEFT OUTER JOIN RDR1 T6 ON T5."DocEntry" = T6."DocEntry" AND T6."ItemCode" = T0."ItemCode" LEFT OUTER JOIN OITM T7 ON T1."ItemCode" = T7."ItemCode" AND T1."ItemType" = '4' -- For Component items LEFT OUTER JOIN ORSC T8 ON T1."ItemCode" = T8."ResCode" AND T1."ItemType" = '290' -- For Resource items --LEFT OUTER JOIN WOR4 T9 ON T1."StageId" = T9."StageId" LEFT JOIN IGE1 T11 ON T1."DocEntry" = T11."BaseEntry" and T1."LineNum" = T11."BaseLine" --and T11."BaseType"=202 LEFT JOIN OIGE T10 on T1."DocEntry"=T10."BaseEntry" LEFT JOIN OILM T12 ON T11."DocEntry" = T12."DocEntry" AND T11."ObjType" = T12."TransType" AND T11."ItemCode" = T12."ItemCode" --AND T12."LocCode" = T11."WhsCode" LEFT JOIN OBTL T13 ON T12."MessageID" = T13."MessageID" LEFT JOIN OBIN T14 ON T13."BinAbs" = T14."AbsEntry" LEFT JOIN IBT1 T15 ON T11."ObjType" = T15."BaseType" AND T11."ItemCode" = T15."ItemCode" AND T11."DocEntry" = T15."BaseEntry" AND T11."LineNum" = T15."BaseLinNum" AND T11."WhsCode" = T15."WhsCode" LEFT JOIN OBTN T16 ON T16."DistNumber" = T15."BatchNum" LEFT JOIN ITL1 T17 ON T16."SysNumber" = T17."SysNumber" AND T11."ItemCode" = T17."ItemCode" AND T13."SnBMDAbs" = T17."MdAbsEntry" WHERE T0."DocNum" = '1014' AND T14."BinCode" > ''

[PaulB1ID]

Hi azacc, kinda hard to read your query. I had to paste it to chatgpt to make it readable. what happen if you take out comment on AND T12."LocCode" = T11."WhsCode"

[williamwachholz]

Hello,

Try to search the OILM table. Something like this:

SELECT *

FROM OWOR

INNER JOIN OILM ON OILM."TransType" = OWOR."ObjType" AND OILM."DocEntry" = OWOR."DocEntry"

From there, you can access the OBTL table.

Reference: https://biuan.com/OILM

Best regards

[azacc]

@Paul: Putting AND T12."LocCode" = T11."WhsCode" back in doesn't make a difference unfortunately, I still get duplicate lines. I may be able to work with this for what I need but would like it to work properly.

[azacc]

@PaulB1ID @williamwachholz  I've tidied it up and got it bringing up the correct number of lines, no Distinct, no dodgy where clause. Unfortunately the bin location is still blank so that's next to work on.  

(Copied into notepad before putting here to keep it legible) 

SELECT
T0."DocNum", T0."DocEntry", T0."ItemCode", T1."ItemName" AS "ItemDesc",T2."ItmsGrpCod", T2."ItmsGrpNam",
CASE WHEN T0."Status"='P' THEN 'Planned' WHEN T0."Status"='R' THEN 'Released' END AS "Status",
CASE WHEN T0."Type"='S' THEN 'Standard' WHEN T0."Type"='P' THEN 'Special' WHEN T0."Type"='D' THEN 'Disassenbly' END AS "Type" ,
T0."PlannedQty", T0."CmpltQty", T0."RjctQty", T0."PostDate" AS "DocDate", T0."StartDate", T0."DueDate", T0."OriginNum",
T0."OriginType", T0."CardCode", T3."CardName", T0."Warehouse", T0."Project", T0."Printed",
T6."DocEntry", T6."LineNum"+1, T6."VisOrder",
CASE WHEN T6."ItemType"='4' THEN 'Component' WHEN T6."ItemType"='290' THEN 'Resource' WHEN T6."ItemType"='-18' THEN 'Text' END AS "LineType" ,
T6."ItemCode" AS "Code",
CASE WHEN T6."ItemType"='4' THEN T1."ItemName" WHEN T6."ItemType"='290' THEN T7."ResName" WHEN T6."ItemType"='-18' THEN T6."LineText" END AS "Description",
T6."BaseQty", T6."PlannedQty", T6."IssuedQty",
CASE WHEN T6."IssueType"='M' THEN 'Manual' WHEN T6."IssueType"='B' THEN 'Backfllush' END AS "IssueType",
T6."wareHouse", T6."UomCode", T6."AdditQty", T6."PickStatus", T6."PickQty",
T8."SeqNum", T8."Name", T8."ReqDays", T8."WaitDays", T8."Status",
T11."BinCode"

FROM OWOR T0
INNER JOIN OITM T1 ON T0."ItemCode"=T1."ItemCode"
INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod"
LEFT OUTER JOIN OCRD T3 ON T0."CardCode" = T3."CardCode"
LEFT OUTER JOIN ORDR T4 ON T0."OriginAbs" = T4."DocEntry" AND T0."LinkToObj"='17'
LEFT OUTER JOIN RDR1 T5 ON T4."DocEntry" = T5."DocEntry" AND T5."ItemCode"=T0."ItemCode"
INNER JOIN WOR1 T6 ON T0."DocEntry" = T6."DocEntry"
LEFT OUTER JOIN ORSC T7 ON T0."ItemCode" = T7."ResCode" AND T6."ItemType"='290'
LEFT OUTER JOIN WOR4 T8 ON T8."StageId"= T6."StageId"
LEFT JOIN OILM T9 ON T9."TransType" = T0."ObjType" AND T0."ItemCode" = T9."ItemCode" AND T6."LineNum" = T9."BaseLine" AND T9."DocEntry" = T0."DocEntry"
LEFT JOIN OBTL T10 ON T9."MessageID" = T10."MessageID"
LEFT JOIN OBIN T11 ON T10."BinAbs" = T11."AbsEntry"

WHERE T0."DocNum" = '1014'

[PaulB1ID]

Hi Azacc, try to join to OIGN first from OWOR because the bin location is tied to receipt from production

[PaulB1ID]

HI Azacc, if you want to get bin location for issue for production, join from owor to oign.