Skip to Content
0
Jun 16, 2021 at 08:33 AM

Not getting the Result

45 Views

Dear Expert

i have a Customized Query for GL and BP

but its not Giving the Result, Kindly pls help with editing the query

Regards

Vinod

DECLARE @FD DATETIME,@TD DATETIME,@ACCT NVARCHAR(50),@TYP NVARCHAR(10)
SET @FD	= '20200101'--{?FD}
SET @TD	= '20210101'--{?TD}
SET @TYP = 'GL'--'{?Typ@SELECT 'A'Code,'GL'Name Union SELECT 'B'Code,'BP'Name}'
SET @ACCT = '2111001'--'{?AC@SELECT X.AcctCode,X.AcctName FROM (SELECT DISTINCT AcctCode,AcctName,'A' Typ FROM JDT1 A,OACT B WHERE A.Account=B.AcctCode UNION ALL SELECT CardCode,CardName,'B' FROM OCRD) X WHERE Typ='@Typ' ORDER BY AcctName}'


SELECT *,case when X.Typ ='B' then isnull(CardName,(Select CardName From OCRD where CardCode=@ACCT)) else acctName end as 'Acc/CardNAme' FROM (
SELECT 'A' Typ,NULL TransId,NULL BaseRef,NULL RefDate,NULL TaxDate,NULL DocSeries,NULL SeriesName,'OB' TransType,NULL Memo,NULL Narration
,t1.Account,T2.AcctName,NULL ContraAct,NULL CardName,0 Debit,0 Credit
,SUM(Debit-Credit) OB


FROM OJDT T0 INNER JOIN JDT1 T1 ON  T0.TransId=T1.TransId
INNER JOIN OACT T2 ON T2.ACCTCODE=T1.ACCOUNT
WHERE (T0.RefDate<@FD)
AND T1.Account=@ACCT
GROUP BY t1.Account,T2.AcctName
UNION ALL
SELECT 'A' Typ,T0.TransId,T0.BaseRef,T0.RefDate,T0.TaxDate,T0.DocSeries,T3.SeriesName
,case 
when T0.TransType=13 then 'Sales Invoice'
when T0.TransType=14 then 'Credit Note'
when T0.TransType=15 then 'Sales Delivery'
when T0.TransType=16 then 'Sales Return'
when T0.TransType=18 then 'Purchase Invoice'
when T0.TransType=19 then 'Debit Note'
when T0.TransType=20 then 'Goods Receipt PO'
when T0.TransType=21 then 'Purchase Return'
when T0.TransType=24 then 'Incoimg Payment'
when T0.TransType=30 then 'Journal Entry'
when T0.TransType=59 then 'Inventory Goods Receipt'
when T0.TransType=46 then 'Outgoing Payment'
when T0.TransType=60 then 'Inventory Goods Issue'
when T0.TransType=67 then 'Stock Transfer'
when T0.TransType=162 then 'Inventory Revaluation'
when T0.TransType=10000071 then ''
when T0.TransType=310000001 then 'Opening' ELSE T0.TransType End [TransType]
,T0.Memo
,CASE  WHEN T0.TransType=13 then T5.Comments WHEN T0.TransType=18 THEN T6.Comments
 WHEN T0.TransType=24 THEN T9.Comments
 WHEN T0.TransType=46 THEN T8.Comments
 ELSE T1.LineMemo END Narration
,T1.Account,T2.AcctName,T1.ContraAct,(SELECT CardName FROM OCRD WHERE CardCode=T1.ContraAct) CardName
--CASE T1.TransType WHEN '46' THEN ISNULL(T8.CardName,T10.CardName) WHEN '24' THEN T9.CardName ELSE T4.CardName END CardName
,T1.Debit,T1.Credit,0 OB
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId=T1.TransId
LEFT JOIN OACT T2 ON T2.AcctCode=T1.Account
LEFT JOIN NNM1 T3 ON T3.Series=T0.DocSeries
LEFT JOIN OCRD T4 ON T4.CardCode=T1.ContraAct
LEFT JOIN OPCH T5 ON T5.TransId=T1.TransId AND T5.ObjType=T0.TransType
LEFT JOIN OINV T6 ON T6.TransId=T1.TransId AND T6.ObjType=T0.TransType
LEFT JOIN OVPM T8 ON T8.TransId=T1.TransId
LEFT JOIN ORCT T9 ON T9.TransId=T1.TransId
WHERE (T0.RefDate BETWEEN @FD AND @TD)
AND T1.Account=@ACCT


UNION ALL


SELECT 'B' Typ,NULL TransId,NULL BaseRef,NULL RefDate,NULL TaxDate,NULL DocSeries,NULL SeriesName,'OB' TransType,NULL Memo,NULL Narration
,t1.Account,T2.AcctName,NULL ContraAct,NULL CardName,0 Debit,0 Credit,SUM(Debit-Credit) OB


FROM OJDT T0 INNER JOIN JDT1 T1 ON  T0.TransId=T1.TransId
INNER JOIN OACT T2 ON T2.ACCTCODE=T1.ACCOUNT
WHERE (T0.RefDate<@FD)
AND T1.ShortName=@ACCT 
GROUP BY t1.Account,T2.AcctName
UNION ALL
SELECT 'B' Typ,T0.TransId,T0.BaseRef,T0.RefDate,T0.TaxDate,T0.DocSeries,T3.SeriesName
,case 
when T0.TransType=13 then 'Sales Invoice'
when T0.TransType=14 then 'Credit Note'
when T0.TransType=15 then 'Sales Delivery'
when T0.TransType=16 then 'Sales Return'
when T0.TransType=18 then 'Purchase Invoice'
when T0.TransType=19 then 'Debit Note'
when T0.TransType=20 then 'Goods Receipt PO'
when T0.TransType=21 then 'Purchase Return'
when T0.TransType=24 then 'Incoimg Payment'
when T0.TransType=30 then 'Journal Entry'
when T0.TransType=59 then 'Inventory Goods Receipt'
when T0.TransType=46 then 'Outgoing Payment'
when T0.TransType=60 then 'Inventory Goods Issue'
when T0.TransType=67 then 'Stock Transfer'
when T0.TransType=162 then 'Inventory Revaluation'
when T0.TransType=10000071 then ''
when T0.TransType=310000001 then 'Opening' ELSE T0.TransType End [TransType]
,T0.Memo
,CASE  WHEN T0.TransType=13 then T5.Comments WHEN T0.TransType=18 THEN T6.Comments
 WHEN T0.TransType=24 THEN T9.Comments
 WHEN T0.TransType=46 THEN T8.Comments
 ELSE T1.LineMemo END Narration
,T1.Account,T2.AcctName,T1.ContraAct,(SELECT CardName FROM OCRD WHERE CardCode=T1.ShortName) CardName
--CASE T1.TransType WHEN '46' THEN ISNULL(T8.CardName,T10.CardName) WHEN '24' THEN T9.CardName ELSE T4.CardName END CardName
,T1.Debit,T1.Credit,0 OB
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId=T1.TransId
LEFT JOIN OACT T2 ON T2.AcctCode=T1.Account
LEFT JOIN NNM1 T3 ON T3.Series=T0.DocSeries
LEFT JOIN OCRD T4 ON T4.CardCode=T1.ContraAct
LEFT JOIN OPCH T5 ON T5.TransId=T1.TransId AND T5.ObjType=T0.TransType
LEFT JOIN OINV T6 ON T6.TransId=T1.TransId AND T6.ObjType=T0.TransType
LEFT JOIN OVPM T8 ON T8.TransId=T1.TransId
LEFT JOIN ORCT T9 ON T9.TransId=T1.TransId
WHERE (T0.RefDate BETWEEN @FD AND @TD)
AND T1.ShortName=@ACCT 
) X
WHERE X.Typ=@TYP
ORDER BY TransId