Skip to Content
0
Nov 28, 2009 at 01:47 PM

Query PLD

26 Views

Hi All

Iam designing a PLD for Query Report

*In the PLD iam designing the Report for Open Purchase Order Status Report Query run fine but the problem came under designing the Document.My Query as above.

My Client have requirement not repeat the PO No, PO Date,Due Date and Vendor Name that's row level record not repeated.(Under that row level record repeat (For Eg : Item Code,Item Description,Order Qty,PO Amount etc) But it is possible from me in Query PLD.

SELECT

T1.DocNum,T1.CardName as "Vendor Name",t0.[Itemcode],t0.[dscription],T0.[Project] as "Project Name",

T1.[DocDate] as "PO Date",

T0.[ShipDate] as "Due Date",

Sum(T0.[Quantity]) as "Order Qty",

Sum(T0.[LineTotal]) as "PO Amount",

Sum(T0.[OpenQty]) as "Pending Qty",

Sum(((T0.[OpenQty]) * (T0.[Price]))) as "Pending Amount"

FROM

[dbo].[POR1] T0 INNER JOIN [dbo].[OPOR] T1 ON T0.DocEntry = T1.DocEntry left join OPDN T2 ON T2.DocEntry = T0.TrgetEntry

WHERE

T1.[DocDate] >=[%0] and T1.[DocDate] <= [%1] and t1.[Docstatus] = 'O' and t0.[linestatus] = 'O'

Group By

t0.[Itemcode],t0.[dscription],T1.[CardName], T1.[DocNum], T1.[DocDate], T0.[ShipDate],

T0.[Project], T2.[DocDate]

order by

T1.DocDate

Regards

Amol