Qpld for GRPO

[Former Member]

Hi All,

I need to make a QPLD for GRPO. I have made a query but its getting list of all the GRPO's made in the system when i check i tried the query in SQL.

SELECT T0.[CardCode]'Cust Code', T0.[CardName]'Cust. Name' , T0.[DocNum]'Doc Number', T0.[DocDate] ,

T1.[ItemCode], T1.[Dscription]'Description', T1.[Quantity], T1.[Price], T2.[BuyUnitMsr],

T2.[SalUnitMsr],T1.[LineTotal] FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

Can this query be used for QPLD??

Thanks in advance,

Joseph

[Former Member]

Hi Joseph,

Query generator help you a lot to write the sql queries to a level .

Just select the tables ,system will show the link between the tables if exist .

I suggest you to if u r new in sql just go for Crystal report for such reports , In rystal erport you vcan learn more of coding skills .

However the query is ok.

rgds,

Premraj

[Former Member]

Thanks Gordon , Thanks Premraj.... Will work on the suggestions

Best Regards,

Joseph

[Former Member]

Welcome,

[Former Member]

A part of query is still missing!!

[Former Member]

If you only need print one GRPO, you have to define condition. Such as:

SELECT T0.CardCode'Cust Code', T0.CardName'Cust. Name' , T0.DocNum 'Doc Number', T0.DocDate ,

T1.ItemCode, T1.Dscription'Description', T1.Quantity, T1.Price, T2.BuyUnitMsr,

T2.SalUnitMsr,T1.LineTotal FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T0.DocNum = '[%0\]'

Thanks,

Gordon

[Former Member]

Hi Gordon,

Thanks for the SQL. Actually, i'm not very comfortable with SQL. I'm just a beginner so i'm trying to learn how to use the parameters!! Thanks again.

Regards,

Joseph

[Former Member]

Hi Gordon,

Sorry, i forgot to mention one point in the query. I would also like to get the sum of Purchase Uom and the sales Uom before line total. CAn you please help me on that as well?

Thanks,

Joseph

[Former Member]

Try this one:

SELECT T0.CardCode'Cust Code', T0.CardName'Cust. Name' , T0.DocNum 'Doc Number', T0.DocDate ,

T1.ItemCode, T1.Dscription'Description', T1.Quantity, T1.Price, T2.BuyUnitMsr,T1.Quantity * T2.NumInBuy 'Purchasing QTY', T2.SalUnitMsr, T1.Quantity * T2.NumInSale 'Sales QTY'

T1.LineTotal

FROM DBO.OPDN T0 INNER JOIN DBO.PDN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN DBO.OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T0.DocNum = '[%0\]'