Incoming Payment Query

[kedalenechong]

Hi all

Any way to add G/L account name for bank account?

DECLARE @REPORTBY AS NVARCHAR(1)  

/* SELECT FROM [dbo].[OFPR] T0 */    

/* WHERE */ DECLARE @DATEFROM datetime   

SET @DATEFROM =/* T0.F_RefDate */'[%0]'    

/* SELECT FROM [dbo].[OFPR] T1 */    

/* WHERE */ DECLARE @DATETO datetime    

SET @DATETO=/* T1.T_RefDate */'[%1]'    

/*  

SET @DATEFROM = '20140101'  

SET @DATETO = '20140131'  

*/  

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

  /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct, T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate 

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum 

WHERE T0.DocType = 'C'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO   

UNION ALL   

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [PAY-TO NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

  /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct, T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName  [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate 

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum 

WHERE T0.DocType = 'S'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO   

UNION ALL  

SELECT  ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , ISNULL(T0.CARDNAME,'') [CARDCODE]

  , T0.Address [NAME]

  , T0.DocCurr 

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

  /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T2.Currency

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct, T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName  [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc 

  , CASE T0.DOCTYPE WHEN 'A' THEN 0 ELSE T0.NoDocSum END [PMNT ON ACC.]

  , CASE T0.DOCTYPE WHEN 'A' THEN 0 ELSE T0.NoDocSumFC END [PMNT ON ACC. (FC)] 

  , T0.DocRate [Payment Rate]

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum 

WHERE T0.DocType = 'A'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO

[kothandaraman_nagarajan · Accepted solution]

Hi,

If you got answer, please close this thread by marking correct answer.

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Try this query to get all GL account name:

DECLARE @REPORTBY AS NVARCHAR(1)  

/* SELECT FROM [dbo].[OFPR] T0 */    

/* WHERE */ DECLARE @DATEFROM datetime   

SET @DATEFROM =/* T0.F_RefDate */'[%0]'    

/* SELECT FROM [dbo].[OFPR] T1 */    

/* WHERE */ DECLARE @DATETO datetime    

SET @DATETO=/* T1.T_RefDate */'[%1]'    

/*  

SET @DATEFROM = '20140101'  

SET @DATETO = '20140131'  

*/  

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

     /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T4.[AcctName], T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct,T4.[AcctName], T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T5.[AcctName],T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate 

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum  left join OACT t4 on T2.CheckAct = T4.AcctCode left join OACT t5 on  T5.AcctCode = t3.creditacct

WHERE T0.DocType = 'C'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO 

union all

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

     /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T4.[AcctName], T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct,T4.[AcctName], T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T5.[AcctName],T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate 

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum  left join OACT t4 on T2.CheckAct = T4.AcctCode left join OACT t5 on  T5.AcctCode = t3.creditacct

WHERE T0.DocType = 'S'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO 

union all

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

     /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T4.[AcctName], T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct,T4.[AcctName], T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T5.[AcctName],T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate 

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum  left join OACT t4 on T2.CheckAct = T4.AcctCode left join OACT t5 on  T5.AcctCode = t3.creditacct

WHERE T0.DocType = 'A'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO 

Thanks & Regards,

Nagarajan

[kedalenechong]

Hi Nagarajan

Thanks!

The Bank Transfer Account is missing?

Kedalene Chong

[kothandaraman_nagarajan]

Hi,

Try this:

DECLARE @REPORTBY AS NVARCHAR(1)  

/* SELECT FROM [dbo].[OFPR] T0 */    

/* WHERE */ DECLARE @DATEFROM datetime   

SET @DATEFROM =/* T0.F_RefDate */'[%0]'    

/* SELECT FROM [dbo].[OFPR] T1 */    

/* WHERE */ DECLARE @DATETO datetime    

SET @DATETO=/* T1.T_RefDate */'[%1]'    

/*  

SET @DATEFROM = '20140101'  

SET @DATETO = '20140131'  

*/  

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

     /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T4.[AcctName], T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct,T6.[AcctName],T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct,T4.[AcctName], T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T5.[AcctName],T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate 

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum  left join OACT t4 on T2.CheckAct = T4.AcctCode left join OACT t5 on  T5.AcctCode = t3.creditacct left join  OACT T6 on t0.CashAcct = t6.AcctCode

WHERE T0.DocType = 'C'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO 

union all

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

     /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T4.[AcctName], T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct,T6.[AcctName], T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct,T4.[AcctName], T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T5.[AcctName],T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate 

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum  left join OACT t4 on T2.CheckAct = T4.AcctCode left join OACT t5 on  T5.AcctCode = t3.creditacct left join  OACT T6 on t0.CashAcct = t6.AcctCode

WHERE T0.DocType = 'S'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO 

union all

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.] 

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

     /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct, T4.[AcctName], T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC 

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T6.[AcctName], T0.TrsfrSum, T0.TrsfrSumFC 

  , T0.CashAcct,T4.[AcctName], T0.CashSum, T0.CashSumFC 

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T5.[AcctName],T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate 

  , T0.DiffCurr 

FROM ORCT T0   

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series   

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum   

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)  

ON T0.DocNum = T3.DocNum  left join OACT t4 on T2.CheckAct = T4.AcctCode left join OACT t5 on  T5.AcctCode = t3.creditacct left join  OACT T6 on t0.CashAcct = t6.AcctCode

WHERE T0.DocType = 'A'  

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO 

Thanks & Regards,

Nagarajan

[Former Member]

HI

Check this

DECLARE @REPORTBY AS NVARCHAR(1)

/* SELECT FROM [dbo].[OFPR] T0 */  

/* WHERE */ DECLARE @DATEFROM datetime 

SET @DATEFROM =/* T0.F_RefDate */'[%0]'  

/* SELECT FROM [dbo].[OFPR] T1 */  

/* WHERE */ DECLARE @DATETO datetime  

SET @DATETO=/* T1.T_RefDate */'[%1]'  

/*

SET @DATEFROM = '20140101'

SET @DATETO = '20140131'

*/

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.]

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

  /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct,t4.AcctName, T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC

  , T0.CashAcct,t5.AcctName, T0.CashSum, T0.CashSumFC

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate

  , T0.DiffCurr

FROM ORCT T0 

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series 

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum

left outer join OACT T4 on t2.CheckAct = t4.AcctCode

left outer join OACT T5 on t0.CashAcct = t5.AcctCode

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)

ON T0.DocNum = T3.DocNum

WHERE T0.DocType = 'C'

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO 

UNION ALL 

SELECT   ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.]

  , T0.DocDate [DATE]

  , T0.CardCode [CARDCODE]

  , T0.CardName [PAY-TO NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

  /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct,t4.AcctName, T2.Currency [Check Curr.]

  , T2.CheckSum, T0.CheckSumFC

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC

  , T0.CashAcct,t5.AcctName, T0.CashSum, T0.CashSumFC

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName  [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc, T0.NoDocSum, T0.NoDocSumFC

  , T0.DocRate

  , T0.DiffCurr

FROM ORCT T0 

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series 

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum

left outer join OACT T4 on t2.CheckAct = T4.AcctCode

left outer join OACT T5 on t0.CashAcct = t5.AcctCode

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)

ON T0.DocNum = T3.DocNum

WHERE T0.DocType = 'S'

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO 

UNION ALL

SELECT  ISNULL(T01.BeginStr,'') + ' ' + CONVERT(NVARCHAR(20), T0.DOCNUM) [DOCUMENT NO.]

  , T0.DocDate [DATE]

  , ISNULL(T0.CARDNAME,'') [CARDCODE]

  , T0.Address [NAME]

  , T0.DocCurr

  , T0.DocTotal [TOTAL incl. GST]

  , T0.DocTotalFC  [TOTAL (FC) incl. GST]

  , T0.Comments [Remarks]

  , T0.JrnlMemo

  , T0.CounterRef [Ref 2]

  /*BANK CHARGES*/

  , T0.BcgSum [Bank Charges], T0.BcgSumFC [Bank Charges (FC)]

  /*CHECK PAYMENT*/

  , T2.CheckAct,t4.AcctName, T2.Currency

  , T2.CheckSum, T0.CheckSumFC

  , T2.AcctNum, T2.BankCode

  /*BANK TRANSFER*/

  , T0.TrsfrAcct, T0.TrsfrSum, T0.TrsfrSumFC

  , T0.CashAcct,t5.AcctName, T0.CashSum, T0.CashSumFC

  /*CREDIT CARD/ADJUSTMENT PAYMENT*/

  , T03.CardName  [ADJ. TYPE], T3.CreditAcct [ADJ. ACC.], T0.CreditSum, T0.CredSumFC

  /*PAYMENT ON ACCOUNT*/

  , T0.PayNoDoc

  , CASE T0.DOCTYPE WHEN 'A' THEN 0 ELSE T0.NoDocSum END [PMNT ON ACC.]

  , CASE T0.DOCTYPE WHEN 'A' THEN 0 ELSE T0.NoDocSumFC END [PMNT ON ACC. (FC)]

  , T0.DocRate [Payment Rate]

  , T0.DiffCurr

FROM ORCT T0 

LEFT OUTER JOIN NNM1 T01 ON T0.ObjType = T01.ObjectCode AND T0.Series = T01.Series 

LEFT OUTER JOIN RCT1 T2 ON T0.DocNum = T2.DocNum

left outer join OACT T4 on t2.CheckAct = t4.AcctCode

left outer join OACT T5 on t0.CashAcct = t5.AcctCode

LEFT OUTER JOIN (RCT3 T3 LEFT OUTER JOIN OCRC T03 ON T3.CreditCard = T03.CreditCard)

ON T0.DocNum = T3.DocNum

WHERE T0.DocType = 'A'

AND T0.DOCDATE BETWEEN @DATEFROM AND @DATETO

Thanks

Mohammad Imran

[kedalenechong]

Hi Mohammed Imran

Thanks so much!

But is it possible to have account name for each payment mode type?  example

CheckAct NAME

TrsfrAcct   NAME

CashAcct NAME

ADJ. ACC.   NAME

[Former Member]

Hi

This query providing the same.

Do one thing copy again this query and paste then run it will give you desired result.

because i have updated this query.

Thanks

Mohammad Imran

[kedalenechong]

Hi Imran

Thanks!

The Bank Transfer Account is missing?

Kedalene