Query Help

[former_member224367]

Dear All,

I created query to extract all Open PO and link to Open GRN, filter by PO document date. Why my filter doesn't work. Below are my query

Select  A.DocNum as POSO#, A.Project,A.DocDate as POdate, A.CardCode, A.CardName,B.ItemCode,B.AcctCode as POAcctCode,B.Dscription, B.text as ItemDetails,B.Quantity as POQty, B.UnitMsr as POUOM,B.Price as POItemPrice,B.Currency as POCurrency,B.LineTotal as POLineTotal, B.OpenQty  as RemainingQtyforGRN

,D.DocNum as GRNNum,D.DocDate as GRNDate,C.Quantity AS GRNQty,C.Price as GRNprice,C.LineTotal as GRNvalue From OPOR A LEFT OUTER JOIN POR1 B ON A.DocEntry=B.DocEntry

LEFT OUTER JOIN PDN1 C ON A.DocNum=C.BaseRef LEFT OUTER JOIN OPDN D ON

C.DocEntry=D.DocEntry LEFT OUTER JOIN PCH1 E ON D.DocNum=E.BaseRef

LEFT OUTER JOIN OPCH F ON E.DocEntry=F.DocEntry

Where A.DocDate>='[%1]' and A.DocDate<='[%2]' and A.DocStatus = 'O' and B.LineStatus = 'O' and C.LineStatus = 'O'

order By A.DocNum

Thanks,

Saufil

[kothandaraman_nagarajan · Accepted solution]

Have you checked my reply?

Try this query:

Select  A.DocNum as POSO#, A.Project,A.DocDate as POdate, A.CardCode, A.CardName,B.ItemCode,B.AcctCode as POAcctCode,B.Dscription, B.text as ItemDetails,B.Quantity as POQty, B.UnitMsr as POUOM,B.Price as POItemPrice,B.Currency as POCurrency,B.LineTotal as POLineTotal, B.OpenQty  as RemainingQtyforGRN

,D.DocNum as GRNNum,D.DocDate as GRNDate,C.Quantity AS GRNQty,C.Price as GRNprice,C.LineTotal as GRNvalue

From OPOR A

JOIN POR1 B ON A.DocEntry=B.DocEntry and B.LineStatus = 'O'

LEFT JOIN PDN1 C ON C.BaseEntry=A.DocEntry AND C.BaseLine=B.LineNum and C.LineStatus = 'O'

LEFT JOIN OPDN D ON D.DocEntry=C.DocEntry

LEFT JOIN PCH1 E ON E.BaseEntry=D.DocEntry AND E.BaseLine=C.LineNum

LEFT JOIN OPCH F ON E.DocEntry=F.DocEntry

Where A.DocDate>='[%1]' and A.DocDate<='[%2]'

order By A.DocNum

[former_member224367 · Accepted solution]

Hi Naga,

Yes, my problem resolved.

Thanks,

Saufil

[KennedyT21]

Hi

Try this

Select  A.DocNum as POSO#, A.Project,A.DocDate as POdate, A.CardCode, A.CardName,B.ItemCode,B.AcctCode as POAcctCode,B.Dscription, B.text as ItemDetails,B.Quantity as POQty, B.UnitMsr as POUOM,B.Price as POItemPrice,B.Currency as POCurrency,B.LineTotal as POLineTotal, B.OpenQty  as RemainingQtyforGRN

,D.DocNum as GRNNum,D.DocDate as GRNDate,C.Quantity AS GRNQty,C.Price as GRNprice,C.LineTotal as GRNvalue From OPOR A LEFT OUTER JOIN POR1 B ON A.DocEntry=B.DocEntry

LEFT OUTER JOIN PDN1 C ON A.DocNum=C.BaseRef LEFT OUTER JOIN OPDN D ON

C.DocEntry=D.DocEntry LEFT OUTER JOIN PCH1 E ON D.DocNum=E.BaseRef

LEFT OUTER JOIN OPCH F ON E.DocEntry=F.DocEntry

Where A.DocDate>='[%1]' and A.DocDate<='[%2]' and A.DocStatus = 'O'  or B.LineStatus = 'O'  or C.LineStatus = 'O'

order By A.DocNum

[former_member224367]

Hi Kennedy,

I just tried and system comes out message out of memory.

Thanks,

Saufil

[Former Member]

Hi Saudi,

Try:

Select  A.DocNum as POSO#, A.Project,A.DocDate as POdate, A.CardCode, A.CardName,B.ItemCode,B.AcctCode as POAcctCode,B.Dscription, B.text as ItemDetails,B.Quantity as POQty, B.UnitMsr as POUOM,B.Price as POItemPrice,B.Currency as POCurrency,B.LineTotal as POLineTotal, B.OpenQty  as RemainingQtyforGRN

,D.DocNum as GRNNum,D.DocDate as GRNDate,C.Quantity AS GRNQty,C.Price as GRNprice,C.LineTotal as GRNvalue

From OPOR A

JOIN POR1 B ON A.DocEntry=B.DocEntry and B.LineStatus = 'O'

LEFT JOIN PDN1 C ON C.BaseEntry=A.DocEntry AND C.BaseLine=B.LineNum and C.LineStatus = 'O'

LEFT JOIN OPDN D ON D.DocEntry=C.DocEntry

LEFT JOIN PCH1 E ON E.BaseEntry=D.DocEntry AND E.BaseLine=D.LineNum

LEFT JOIN OPCH F ON E.DocEntry=F.DocEntry

Where A.DocDate>='[%1]' and A.DocDate<='[%2]'

order By A.DocNum

Thanks,

Gordon

[former_member224367]

Hi Gordon,

I got error message invalid columns name LineNum.

Thanks,

Saufil

[former_member224367]

Hi Naga,

Actually like this, i want the list of open PO with and open GRN and without GRN combined in same query.

Thanks

[kothandaraman_nagarajan]

Hi,

Are you looking for this query?

SELECT T0.[CardCode], T0.[CardName],T0.docnum, T1.[ItemCode], T1.[Dscription] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocStatus] = 'o' and  T0.[DocDate] between [%0] and [%1] and  T0.[DocType]  = 'i'

union all

SELECT T0.[CardCode], T0.[CardName], T0.docnum, T1.[ItemCode], T1.[Dscription] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocStatus] = 'o' and  T0.[DocDate] between [%0] and [%1] and  T0.[DocType]  = 'i'

Thanks & Regards,

Nagarajan

[former_member224367]

Hi,

Let me try again and update you the status.

Thanks,

[kothandaraman_nagarajan]

Hi,

Try this query for PO and GRPO. Your requirement and query is different.

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T3.[DocNum] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry left join  PDN1 T2 on  T2.[BaseEntry]  = T1.docentry and  T2.[BaseLine]  =  T1.[LineNum] left JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry where t0.docstatus = 'o' and T3.[DocStatus] = 'o'

Thanks & Regards,

Nagarajan

[former_member224367]

Hi,

Thanks for your feedback, i think  can't use because i need per line item and sometime user did partial GRN.

Thanks

[kothandaraman_nagarajan]

Have you checked above query? Why does not meet your requirement?