Vendor Liability Query

46 Views

Hi,

Actually i have created a query for Vendor liability Ageing report.

Which is showing little bit wrong result.

The query is

WITH JV AS

(SELECT ALL 'Jv' AS Doc, J1.ShortName AS CardCode, J1.TransID, J1.Line_ID, J1.TransType AS objType, J1.CreatedBy AS DocEntry, (J1.Debit-J1.Credit) AS DocAmt

FROM JDT1 J1

INNER JOIN OCRD ON J1.ShortName = OCRD.CardCode

WHERE OCRD.CardType = 'S' AND J1.RefDate <= '05-Nov-2014' AND OCRD.CardCode = 'SG903'

),

ITR AS (

Select ALL 'ITR' AS Doc, OCRD.CardCode,

CASE IsNull(C1.InconsType, '') WHEN '' THEN T1.TransID WHEN '9' THEN T1.TransID ELSE C1.OrigTrnsId END AS TransId,

CASE IsNull(C1.InconsType, '') WHEN '' THEN T1.TransRowId WHEN '9' THEN T1.TransRowId ELSE C1.OrigTrnsLn END AS Line_ID,

CASE IsNull(C1.InconsType, '') WHEN '' THEN T1.SrcObjTyp WHEN '9' THEN T1.SrcObjTyp ELSE C1.OrigObjTyp END AS objType,

CASE IsNull(C1.InconsType, '') WHEN '' THEN T1.SrcObjAbs WHEN '9' THEN T1.SrcObjAbs ELSE C1.OrigAbsEnt END AS DocEntry,

CASE IsNull(C1.InconsType, '') WHEN '8' THEN C1.Y ELSE T1.ReconSum END *

CASE T1.IsCredit WHEN 'D' THEN -1 ELSE 1 END AS DocAmt

FROM OITR T0

INNER JOIN ITR1 T1 ON T0.ReconNum = T1.ReconNum

INNER JOIN OCRD ON T1.ShortName = OCRD.CardCode

LEFT OUTER JOIN CASE1 C1 ON T1.TransId = C1.TransId AND T1.TransRowId = C1.TransLine

WHERE OCRD.CardType = 'S' AND T0.ReconDate <= '05-Nov-2014' AND OCRD.CardCode = 'SG903'

AND CASE IsNull(C1.InconsType, '') WHEN '' THEN T1.TransID WHEN '9' THEN T1.TransID ELSE C1.OrigTrnsId END = 5665

)

SELECT * FROM JV UNION ALL SELECT * FROM ITR

Result of CardCode 'SG903' is as

Doc CardCode TransID Line_ID objType DocEntry DocAmt Jv SG903 5665 1 46 10259 1537599 ITR SG903 5665 1 46 10259 -1053052.2 ITR SG903 5665 1 46 10259 -424417.8 ITR SG903 5665 -1 46 10259 -28381.53 ITR SG903 5665 -1 46 10259 -31747.51

Can you tell me Why there is -1 in line_id for row 4, 5 ????

This is internal reconcile entry, for Partial Exchange Rate Difference Recognition.

For detail actual entry for

data in table OITR is

ReconNum IsCard ReconType ReconDate Total ReconCurr Canceled CancelAbs IsSystem InitObjTyp InitObjAbs CreateDate CreateTime DataSource UserSign ReconRule1 ReconRule2 ReconRule3 IsMultiBP VersionNum OldMatNum ReconJEId BuildDesc BPLId BPLName VATRegNum 24738 C 0 6/10/2013 0:00 12214092 INR N 0 N 6/10/2013 0:00 1438 N 15 N 8.82.073.10 0 0 24738 C 0 6/10/2013 0:00 12214092 INR N 0 N 6/10/2013 0:00 1438 N 15 N 8.82.073.10 0 0 19596 C 3 6/26/2008 0:00 1053052 INR N 0 Y 46 10259 6/26/2008 0:00 N N 0 24738 C 0 6/10/2013 0:00 12214092 INR N 0 N 6/10/2013 0:00 1438 N 15 N 8.82.073.10 0 0

data in table ITR1 is

ReconNum LineSeq ShortName TransId TransRowId SrcObjTyp SrcObjAbs ReconSum ReconSumFC ReconSumSC FrgnCurr SumMthCurr IsCredit Account CashDisSum WTSum WTSumFC WTSumSC ExpSum ExpSumFC ExpSumSC netBefDisc MIEntry MIType 24738 3 SG903 176566 0 -5 176566 28381.53 0 28381.53 USD 28381.53 D _SYS00000000309 0 0 0 0 0 0 0 0 0 24738 4 SG903 176567 0 -5 176567 31747.51 0 31747.51 USD 31747.51 D _SYS00000000309 0 0 0 0 0 0 0 0 0 19596 0 SG903 5665 1 46 10259 1053052 0 0 1053052 D _SYS00000000309 0 0 0 0 0 0 0 0 0 24738 5 SG903 5665 1 46 10259 424417.8 0 424417.8 424417.8 D _SYS00000000309 0 0 0 0 0 0 0 0 0

data in table Case1 is

AbsEntry LineId ShortName Account OrigAbsEnt OrigObjTyp OrigDocNum OrigInsNum OrigTrnsId OrigTrnsLn LnkRecnAbs LnkRecnObj LnkRecnNum LnkRecnIns LnkRecnTrn LnkRecnLn InconsType ReconNum NewRcnNum CredDeb Amount AmountFC AmountSC TransId TransLine X Y Z LnkObjAbsE LinkObjTyp LinkDocNum LinkInsNum LinkTrnsId LinkTrnsLn TrnsTtlAmt TrnsTtlFc 1 141 SG903 _SYS00000000309 10259 46 10259 1 5665 -1 -1 -1 -1 -1 -1 -1 4 0 0 D 28381.53 0 0 176566 0 433219.9 461601.4 0 153 18 113 -1 3893 0 28381.53 0 1 142 SG903 _SYS00000000309 10259 46 10259 1 5665 -1 -1 -1 -1 -1 -1 -1 4 0 0 D 31747.51 0 0 176567 0 619832.3 651579.8 0 154 18 114 -1 3895 0 31747.51 0

After running above query i get original transaction Entry number ie OrigTransId but not get Original Transaction Line Id (getting -1 instead of 1)

So i cann't joined it again JV data to get further information because of wrong Transcation Id.

Can give any suggestion for above problem.

if any query available for getting Vendor liability Ageing and Customer receivable ageing report

Please send it.

Thanks and regards

Narendra Dashpute