cancel
Showing results for 
Search instead for 
Did you mean: 

Draft Report with Bin Locations - Goods Receipt PO

bradderz
Explorer
0 Kudos

Hi Experts,

I am trying to develop a query to display all open draft documents with the line details for goods receipt PO. this includes Bin locations however ive hit a wall as incorperating ODRF and DRF1 into OPDN, PDN1 has thrown my results fully. can any advise where im going wrong on the below:

SELECT
OPDN.DocNum,
pdn1.ItemCode,
OBTL.Quantity AS Quantity,
OBin.BinCode,
Ousr.U_NAME
FROM
opdn
INNER JOIN
pdn1 ON opdn.docentry = pdn1.docentry
INNER JOIN
OITM ON pdn1.ItemCode = OITM.ItemCode
INNER JOIN
oivl ON oivl.ItemCode = pdn1.ItemCode
AND oivl.TransType = 20
AND pdn1.LineNum = oivl.DocLineNum
LEFT JOIN
OBTL ON oivl.MessageID = obtl.MessageID
LEFT JOIN
OBIN ON obin.AbsEntry = obtl.BinAbs
LEFT JOIN
POR1 ON pdn1.BaseType = 22
AND PDN1.BaseEntry = POR1.DocEntry
AND PDN1.BaseLine = POR1.LineNum
LEFT JOIN
OPOR ON OPOR.DocEntry = POR1.DocEntry
LEFT JOIN
OUSR ON OPDN.UserSign = OUSR.USERID
WHERE
opdn.DocStatus = 'O' AND opdn.draftkey IS NOT NULL

View Entire Topic
BattleshipCobra
Contributor
0 Kudos

Hello!

You don't necessarily have to integrate the tables into a single query.  You can use a technique called UNION ALL.

 

SELECT 'Invoice' AS "Type",T0."DocNum", T0."DocDate", T0."CardCode", T0."CardName", T0."DocTotal" FROM OINV T0
UNION ALL
SELECT 'Credit' AS "Type",T0."DocNum", T0."DocDate", T0."CardCode", T0."CardName", T0."DocTotal" * -1 AS "DocTotal" FROM ORIN T0

 

You can see that I have effectively two independent queries (but you can run the entire thing in a query window as a single query).  One from OINV and one from ORIN and I have even manipulated the DocTotal so that in the credit side of the query it is negative (scroll right).  As long as all of the columns are the same datatype and there are the same number of them then you can join the queries this way.

You can then extend this and sort them by turning the whole mess into a derived table:

 

SELECT X."Type", X."DocNum", X."DocDate", X."CardCode", X."CardName", X."DocTotal" 

FROM
(
SELECT 'Invoice' AS "Type",T0."DocNum", T0."DocDate", T0."CardCode", T0."CardName", T0."DocTotal" FROM OINV T0
UNION ALL
SELECT 'Credit' AS "Type",T0."DocNum", T0."DocDate", T0."CardCode", T0."CardName", T0."DocTotal" * -1 AS "DocTotal" FROM ORIN T0
) X

ORDER BY X."DocDate"

 

So I just put parenthesis around the UNION ALL query and labelled it as "X" and then outside of the parenthesis you can refer to the columns with the X alias.  In my example I wanted it to sort by "DocDate".

This is identical in SQL Server / HANA.

With your example you can just do a query for the GRPOs and then stack it on a query for the drafts you want.  With BIN locations and stuff it gets too complex to unify it the way you are trying to do it.

Let me know if this makes sense,

Mike
Check out my YouTube SAP Business One channel: http://youtube.battleshipcobra.com/ 

bradderz
Explorer
0 Kudos

Hi, sorry im not sure im quite understanding how to work this based on the unified logic would i need to replicate like this:

-- Query for Goods Receipt PO Drafts
SELECT
ODRF.DocNum,
DRF1.ItemCode,
DRF1.Quantity,
OBIN.BinCode,
OUSR.U_NAME
FROM
ODRF
INNER JOIN
DRF1 ON ODRF.DocEntry = DRF1.DocEntry
LEFT JOIN
OITM ON DRF1.ItemCode = OITM.ItemCode
LEFT JOIN
OBTL ON DRF1.DocEntry = OBTL.DocEntry
AND DRF1.LineNum = OBTL.LineNum
LEFT JOIN
OBIN ON OBIN.AbsEntry = OBTL.BinAbs
LEFT JOIN
POR1 ON DRF1.BaseType = 22
AND DRF1.BaseEntry = POR1.DocEntry
AND DRF1.BaseLine = POR1.LineNum
LEFT JOIN
OPOR ON OPOR.DocEntry = POR1.DocEntry
LEFT JOIN
OUSR ON ODRF.UserSign = OUSR.USERID
WHERE
ODRF.ObjType = 20 -- Goods Receipt PO
AND ODRF.DocStatus = 'O'

UNION ALL

-- Query for Regular Goods Receipt POs
SELECT
OPDN.DocNum,
PDN1.ItemCode,
OBTL.Quantity,
OBIN.BinCode,
OUSR.U_NAME
FROM
OPDN
INNER JOIN
PDN1 ON OPDN.DocEntry = PDN1.DocEntry
LEFT JOIN
OITM ON PDN1.ItemCode = OITM.ItemCode
LEFT JOIN
OBTL ON PDN1.DocEntry = OBTL.DocEntry
AND PDN1.LineNum = OBTL.LineNum
LEFT JOIN
OBIN ON OBIN.AbsEntry = OBTL.BinAbs
LEFT JOIN
POR1 ON PDN1.BaseType = 22
AND PDN1.BaseEntry = POR1.DocEntry
AND PDN1.BaseLine = POR1.LineNum
LEFT JOIN
OPOR ON OPOR.DocEntry = POR1.DocEntry
LEFT JOIN
OUSR ON OPDN.UserSign = OUSR.USERID
WHERE
OPDN.DocStatus = 'O' 


thanks 

Brad

BattleshipCobra
Contributor
0 Kudos
Basically yeah. I have not looked specifically at your query but what I'm seeing in your example is the UNION ALL. Trying to merge the real and draft documents into a single table structure is confusing (to me) so my approach would be a UNION. Does this query give you the information you need? Or are you still having issues with the OPDN / BIN links?
bradderz
Explorer
0 Kudos
Unfortunately no this still only shows GRN posted not saved as drafts
BattleshipCobra
Contributor
0 Kudos
So when you run your draft side of the query it returns no results? If it returns no results then the UNION won't fix that part.
bradderz
Explorer
0 Kudos
Same results as before. Only showing goods receipt PO's that have been posted and not saved as draft.