Dear Kallicharan,
Please find the following query
SELECT T0.[DocNum] as 'PO NO', T0.[DocDate] as 'PO DATE',
T0.[CardCode] as 'Party Code', T0. [CardName] as 'Name Of Party',T0.[DocStatus] as 'PO Status',
T0.[DocTotal] as 'Total Amount' ,
IsNull((Select Sum(IsNull(DocTotal,0)) From OPDN Where DocEntry In
(Select Distinct DocEntry from PDN1 Where BaseEntry=T0.DocEntry And
BaseType=T0.ObjType)),0) +
IsNull((Select Sum(IsNull(DocTotal,0)) From OPCH Where DocEntry In
(Select Distinct DocEntry from PCH1 Where BaseEntry=T0.DocEntry And
BaseType=T0.ObjType)),0) 'GR/INV Amt'
FROM OPOR T0 WHERE T0.[DocNum]>='[%0]' AND T0.[DocNum]<='[%0]'
Regards,
Kamlesh
Hi Kalli........
Please try this........
Select * From OPOR T0 Inner Join POR1 T1 On T0.DocEntry=T1.DocEntry LEFT JOIN PDN1 T2 On T2.BaseEntry=T1.DocEntry LEFT Join OPDN T3 On T2.DocEntry=T3.DocEntry LEFT JOIN PCH1 T4 On T0.BaseEntry=T3.DocEntry LEFT Join OPCH T5 On T4.DocEntry=T5.DocEntry
Regards,
Rahul
Hi Kalli........
Please try this........
SELECT
T0.[DocNum] as 'PO NO', T0.[DocDate] as 'PO DATE',
P1.[GRPODocNum] as 'GRPO NO',
P1.[GRPODocDate] as 'GRPO DATE',
P1.[INVOICENO] as 'INVOICE NO',
P1.[INVOICEDATE] as 'INVOICE DATE',
T0.[CardCode] as 'Party Code',
T0.[CardName] as 'Name Of Party',T0.[DocStatus] as 'PO Status', T0.[VatSum],
T0.[DocTotal] as 'Total Amount' , T0.[Comments] as 'Remarks',
IsNull(
(
Select Sum(IsNull(VatSum,0))
From OPDN
Where DocEntry In (
Select Distinct DocEntry
from PDN1
Where BaseEntry=T0.DocEntry
And BaseType=T0.ObjType
)
),0) +
IsNull(
(
Select Sum(IsNull(VatSum,0))
From OPCH T3
Where DocEntry In (
Select Distinct DocEntry
from PCH1
Where BaseEntry=T0.DocEntry And BaseType=T0.ObjType
)
),0) 'GR/INV TaxAmt',
IsNull(
(
Select Sum(IsNull(DocTotal,0))
From OPDN
Where DocEntry In (
Select Distinct DocEntry
from PDN1
Where BaseEntry=T0.DocEntry And BaseType=T0.ObjType
)
),0) +
IsNull(
(
Select Sum(IsNull(T3.DocTotal,0))
From OPCH T3
Where DocEntry In (
Select Distinct DocEntry
from PCH1
Where BaseEntry=T0.DocEntry And BaseType=T0.ObjType
)
),0) 'GR/INV Amt' ,
(select DocTotal from OPCH where DOCENTRY= INVENTRY) 'INV Amt'
FROM OPOR T0
inner join (SELECT DISTINCT
T2.DOCENTRY PODocEntry,
T3.DOCENTRY INVEntry,
(select DocNum from OPCH where DocEntry = T3.DOCENTRY) INVOICENO,
(select DocDate from OPCH where DocEntry = T3.DOCENTRY) INVOICEDATE,
T1.DocEntry GRPOEntry,
T0.DocNum GRPODocNum,
T0.DocDate GRPODocDate
FROM OPDN T0
INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN POR1 T2 ON (T2.DOCENTRY = T1.BASEENTRY AND T2.LINENUM = T1.BASELINE)
INNER JOIN PCH1 T3 ON (T1.DOCENTRY = T3.BASEENTRY AND T1.LINENUM = T3.BASELINE)
) P1 on T0.docentry = P1.PODocEntry
WHERE (T0.[Series] ='10')
and (T0.[DocDate]>='[%0]' AND T0.[DocDate]<='[%1]')
order by T0.[DocNum]
Regards
Kamlesh
Hi Kalli,
Try:
SELECT Distinct
T0.DocNum as 'PO NO',
T0.DocDate as 'PO DATE',
T0.Cardname as 'Vendor Name',
T0.NumAtCard as 'Vendor Ref',
T2.DocNum as 'GRPO NO',
T2.DocDate as 'GRPO DATE',
T4.DocNum as 'AP Invoice NO',
T4.DocDate as 'AP Invoice DATE',
T4.DocTotal as 'AP Invoice Amount'
FROM OPOR T0
LEFT JOIN PDN1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType=22
LEFT JOIN OPDN T2 ON T2.DocEntry=T1.DocEntry
LEFT JOIN PCH1 T3 ON T3.BaseEntry=T2.DocEntry AND T3.BaseType=20
LEFT JOIN OPCH T4 ON T4.DocEntry=T3.DocEntry
WHERE T0.DocDate Between [%0\] AND [%1\]
Thanks,
Gordon
Add a comment