AR Other Applications

Description

Application: Receivables
Source: Other Applications Report (XML)
Short Name: RAXNCAR_XML
DB package: AR_RAXNCAR_XMLP_PKG

AR Other Applications and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

SELECT RACTT1.POST_TO_GL                          POST_FLAG, 
       DECODE(RACTT1.POST_TO_GL,
              'Y', :yes,
              :no)                             POSTABLE,
       RACTT1.TYPE                               FROM_TYPE,
       RACT1.CUSTOMER_TRX_ID			 FROM_TRX_ID,
       RACT1.TRX_NUMBER                          FROM_TRX_NUMBER,
       GLD1.GL_DATE                             FROM_GL_DATE,
       RACU.ACCOUNT_NUMBER                      FROM_CUST,
       RACTT2.TYPE                               TO_TYPE,
       RACT2.CUSTOMER_TRX_ID			 TO_TRX_ID,
       RACT2.TRX_NUMBER                          TO_TRX_NUMBER,
       GLD2.GL_DATE                             TO_GL_DATE,
       NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD')   TRAN_CURR,
       ARRA.GL_DATE                              TRAN_GL_DATE,
       SUM(ARRA.AMOUNT_APPLIED)                  TRAN_FOREIGN,
       SUM(ARRA.ACCTD_AMOUNT_APPLIED_FROM)   	 TRAN_FUNCT,
       AR_RAXNCAR_XMLP_PKG.D_Tran_ForeignFormula()
FROM   RA_CUSTOMER_TRX                           RACT1,
       RA_CUSTOMER_TRX                           RACT2,
       RA_CUST_TRX_TYPES                         RACTT1,
       RA_CUST_TRX_TYPES                         RACTT2,
       HZ_CUST_ACCOUNTS                            RACU,
       AR_RECEIVABLE_APPLICATIONS                ARRA,
       RA_CUST_TRX_LINE_GL_DIST                  GLD1,
       RA_CUST_TRX_LINE_GL_DIST                  GLD2
WHERE
       ARRA.GL_DATE BETWEEN :sql_start_date AND 
                            :sql_end_date
AND
       RACT1.CUSTOMER_TRX_ID = ARRA.CUSTOMER_TRX_ID  AND
       RACT1.CUSTOMER_TRX_ID = GLD1.CUSTOMER_TRX_ID  AND
       GLD1.ACCOUNT_CLASS    = 'REC'  AND
       GLD1.LATEST_REC_FLAG  = 'Y'
AND
       RACTT1.CUST_TRX_TYPE_ID = RACT1.CUST_TRX_TYPE_ID    AND
       ARRA.APPLICATION_TYPE = 'CM'
AND
       RACU.CUST_ACCOUNT_ID(+) = RACT1.BILL_TO_CUSTOMER_ID
AND
       RACT2.CUSTOMER_TRX_ID = ARRA.APPLIED_CUSTOMER_TRX_ID AND
       RACT2.CUSTOMER_TRX_ID = GLD2.CUSTOMER_TRX_ID AND
       GLD2.ACCOUNT_CLASS    = 'REC' AND
       GLD2.LATEST_REC_FLAG       = 'Y'
AND
       RACTT2.CUST_TRX_TYPE_ID = RACT2.CUST_TRX_TYPE_ID   
GROUP BY RACTT1.POST_TO_GL,
       DECODE(RACTT1.POST_TO_GL,
              'Y', :yes,
              :no),
       RACTT1.TYPE,
       RACT1.CUSTOMER_TRX_ID,
       RACT1.TRX_NUMBER,
       GLD1.GL_DATE,
       RACU.ACCOUNT_NUMBER,
       RACTT2.TYPE,
       RACT2.CUSTOMER_TRX_ID,
       RACT2.TRX_NUMBER,
       GLD2.GL_DATE,
       ARRA.GL_DATE,
       RACT1.CUSTOMER_TRX_ID,
       RACT2.CUSTOMER_TRX_ID,
       NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD')
UNION
SELECT RACTT1.POST_TO_GL                          POST_FLAG,
       DECODE(RACTT1.POST_TO_GL,
              'Y', :yes,
              :no)                             POSTABLE,
       RACTT1.TYPE                               FROM_TYPE,
       RACT1.CUSTOMER_TRX_ID			 FROM_TRX_ID,
       RACT1.TRX_NUMBER                          FROM_TRX_NUMBER,
       GLD1.GL_DATE                             FROM_GL_DATE,
       RACU.ACCOUNT_NUMBER                      FROM_CUST,
       RACTT2.TYPE                               TO_TYPE,
       RACT2.CUSTOMER_TRX_ID			 TO_TRX_ID,
       RACT2.TRX_NUMBER                          TO_TRX_NUMBER,
       GLD2.GL_DATE                             TO_GL_DATE,
       NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD')   TRAN_CURR,
       ADJ.GL_DATE                              TRAN_GL_DATE,
       SUM(ADJ.AMOUNT * -1)                      TRAN_FOREIGN,
       SUM(ADJ.ACCTD_AMOUNT * -1)     		TRAN_FUNCT,
       AR_RAXNCAR_XMLP_PKG.D_Tran_ForeignFormula()
FROM   RA_CUSTOMER_TRX                           RACT1,
       RA_CUSTOMER_TRX                           RACT2,
       RA_CUST_TRX_TYPES                         RACTT1,
       RA_CUST_TRX_TYPES                         RACTT2,
       HZ_CUST_ACCOUNTS                              RACU,
       AR_ADJUSTMENTS                            ADJ,
       RA_CUST_TRX_LINE_GL_DIST                  GLD1,
       RA_CUST_TRX_LINE_GL_DIST                  GLD2
WHERE
       ADJ.GL_DATE BETWEEN :sql_start_date AND 
                           :sql_end_date
AND
       RACT2.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID AND
       RACT2.CUSTOMER_TRX_ID = GLD2.CUSTOMER_TRX_ID AND
       GLD2.ACCOUNT_CLASS    = 'REC' AND
       GLD2.LATEST_REC_FLAG       = 'Y'
AND
       RACT1.CUSTOMER_TRX_ID = RACT2.INITIAL_CUSTOMER_TRX_ID AND
       RACT1.CUSTOMER_TRX_ID = GLD1.CUSTOMER_TRX_ID  AND
       GLD1.ACCOUNT_CLASS    = 'REC'  AND
       GLD1.LATEST_REC_FLAG  = 'Y'
AND
       RACTT2.CUST_TRX_TYPE_ID = RACT2.CUST_TRX_TYPE_ID   AND
       RACTT2.TYPE = 'INV'
AND
       RACTT1.CUST_TRX_TYPE_ID = RACT1.CUST_TRX_TYPE_ID   AND
       RACTT1.TYPE = 'DEP'
AND
       RACU.CUST_ACCOUNT_ID(+) = RACT1.BILL_TO_CUSTOMER_ID
GROUP BY RACTT1.POST_TO_GL,
       DECODE(RACTT1.POST_TO_GL,
              'Y', :yes,
              :no),
       RACTT1.TYPE,
       RACT1.CUSTOMER_TRX_ID,
       RACT1.TRX_NUMBER,
       GLD1.GL_DATE,
       RACU.ACCOUNT_NUMBER,
       RACTT2.TYPE,
       RACT2.CUSTOMER_TRX_ID,
       RACT2.TRX_NUMBER,
       GLD2.GL_DATE,
       ADJ.GL_DATE,
       RACT1.CUSTOMER_TRX_ID,
       RACT2.CUSTOMER_TRX_ID,
       NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD')
UNION
SELECT RACTT1.POST_TO_GL                          POST_FLAG,
       DECODE(RACTT1.POST_TO_GL,
              'Y', :yes,
              :no)                             POSTABLE,
       RACTT1.TYPE                               FROM_TYPE,
       RACT1.CUSTOMER_TRX_ID			 FROM_TRX_ID,
       RACT1.TRX_NUMBER                          FROM_TRX_NUMBER,
       GLD1.GL_DATE                             FROM_GL_DATE,
       RACU.ACCOUNT_NUMBER                      FROM_CUST,
       RACTT2.TYPE                               TO_TYPE,
       RACT2.CUSTOMER_TRX_ID			 TO_TRX_ID,
       RACT2.TRX_NUMBER                          TO_TRX_NUMBER,
       GLD2.GL_DATE                             TO_GL_DATE,
       NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD')   TRAN_CURR,
       ADJ.GL_DATE                              TRAN_GL_DATE,
       SUM(ADJ.AMOUNT * -1)                      TRAN_FOREIGN,
       SUM(ADJ.ACCTD_AMOUNT * -1)     		TRAN_FUNCT,
       AR_RAXNCAR_XMLP_PKG.D_Tran_ForeignFormula()
FROM   RA_CUSTOMER_TRX                           RACT1,
       RA_CUSTOMER_TRX                           RACT2,
       RA_CUST_TRX_TYPES                         RACTT1,
       RA_CUST_TRX_TYPES                         RACTT2,
       HZ_CUST_ACCOUNTS                             RACU,
       AR_ADJUSTMENTS                            ADJ,
       RA_CUST_TRX_LINE_GL_DIST                  GLD1,
       RA_CUST_TRX_LINE_GL_DIST                  GLD2
WHERE
       ADJ.GL_DATE BETWEEN :sql_start_date AND 
                           :sql_end_date
AND
       RACT2.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID
AND
       RACT2.CUSTOMER_TRX_ID = RACT1.INITIAL_CUSTOMER_TRX_ID AND
       RACT2.CUSTOMER_TRX_ID = GLD2.CUSTOMER_TRX_ID AND
       GLD2.ACCOUNT_CLASS    = 'REC' AND
       GLD2.LATEST_REC_FLAG       = 'Y'
AND
       RACTT2.CUST_TRX_TYPE_ID = RACT2.CUST_TRX_TYPE_ID   AND
       RACTT2.TYPE = 'GUAR'
AND
       RACTT1.CUST_TRX_TYPE_ID = RACT1.CUST_TRX_TYPE_ID   AND
       RACTT1.TYPE = 'INV' AND
       RACT1.CUSTOMER_TRX_ID = GLD1.CUSTOMER_TRX_ID  AND
       GLD1.ACCOUNT_CLASS    = 'REC'  AND
       GLD1.LATEST_REC_FLAG  = 'Y'
AND
       RACU.CUST_ACCOUNT_ID(+) = RACT1.BILL_TO_CUSTOMER_ID
GROUP BY RACTT1.POST_TO_GL,
       DECODE(RACTT1.POST_TO_GL,
              'Y', :yes,
              :no),
       RACTT1.TYPE,
       RACT1.CUSTOMER_TRX_ID,
       RACT1.TRX_NUMBER,
       GLD1.GL_DATE,
       RACU.ACCOUNT_NUMBER,
       RACTT2.TYPE,
       RACT2.CUSTOMER_TRX_ID,
       RACT2.TRX_NUMBER,
       GLD2.GL_DATE,
       ADJ.GL_DATE,
       RACT1.CUSTOMER_TRX_ID,
       RACT2.CUSTOMER_TRX_ID,
       NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD')
UNION
SELECT RACTT1.POST_TO_GL                          POST_FLAG, 
       DECODE(RACTT1.POST_TO_GL,
              'Y', :yes,
              :no)                             POSTABLE,
       RACTT1.TYPE                               FROM_TYPE,
       RACT1.CUSTOMER_TRX_ID			 FROM_TRX_ID,
       RACT1.TRX_NUMBER                          FROM_TRX_NUMBER,
       GLD1.GL_DATE                             FROM_GL_DATE,
       RACU.ACCOUNT_NUMBER                      FROM_CUST,
       NULL                                        TO_TYPE,
       PS.CUSTOMER_TRX_ID			 TO_TRX_ID,
       PS.TRX_NUMBER                          TO_TRX_NUMBER,
       DECODE(PS.TRX_NUMBER,'Refund',TO_DATE(NULL),
                      PS.GL_DATE )                            TO_GL_DATE,
       NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD')   TRAN_CURR,
       ARRA.GL_DATE                              TRAN_GL_DATE,
       SUM(ARRA.AMOUNT_APPLIED)                  TRAN_FOREIGN,
       SUM(ARRA.ACCTD_AMOUNT_APPLIED_FROM)   	 TRAN_FUNCT,
       AR_RAXNCAR_XMLP_PKG.D_Tran_ForeignFormula()
FROM   RA_CUSTOMER_TRX                           RACT1,
       AR_PAYMENT_SCHEDULES                  PS,
       RA_CUST_TRX_TYPES                         RACTT1,
       HZ_CUST_ACCOUNTS                            RACU,
       AR_RECEIVABLE_APPLICATIONS                ARRA,
       RA_CUST_TRX_LINE_GL_DIST                  GLD1
WHERE
       ARRA.GL_DATE BETWEEN :sql_start_date AND 
                            :sql_end_date
AND
       RACT1.CUSTOMER_TRX_ID = ARRA.CUSTOMER_TRX_ID  AND
       RACT1.CUSTOMER_TRX_ID = GLD1.CUSTOMER_TRX_ID  AND
       GLD1.ACCOUNT_CLASS    = 'REC'  AND
       GLD1.LATEST_REC_FLAG  = 'Y'
AND
       RACTT1.CUST_TRX_TYPE_ID = RACT1.CUST_TRX_TYPE_ID    AND
       ARRA.APPLICATION_TYPE = 'CM' AND
       ARRA.STATUS = 'ACTIVITY'
AND
       RACU.CUST_ACCOUNT_ID(+) = RACT1.BILL_TO_CUSTOMER_ID
AND
       PS.PAYMENT_SCHEDULE_ID = ARRA.APPLIED_PAYMENT_SCHEDULE_ID
GROUP BY RACTT1.POST_TO_GL,
       DECODE(RACTT1.POST_TO_GL,
              'Y', :yes,
              :no),
       RACTT1.TYPE,
       RACT1.CUSTOMER_TRX_ID,
       RACT1.TRX_NUMBER,
       GLD1.GL_DATE,
       RACU.ACCOUNT_NUMBER,
       PS.CUSTOMER_TRX_ID,
       PS.TRX_NUMBER,
       PS.GL_DATE,
       ARRA.GL_DATE,
       RACT1.CUSTOMER_TRX_ID,
       PS.CUSTOMER_TRX_ID,
       NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD')
/*ORDER BY 1 DESC,
         3 ASC,
         5 ASC,
         6 ASC,
         8 ASC,
        10 ASC,
        11 ASC,
        13 ASC*/
order by 2 desc,3,5,4,6,8,10,11,13,1,14,15

SELECT RACTT1.POST_TO_GL POST_FLAG, DECODE(RACTT1.POST_TO_GL, 'Y', :yes, :no) POSTABLE, RACTT1.TYPE FROM_TYPE, RACT1.CUSTOMER_TRX_ID FROM_TRX_ID, RACT1.TRX_NUMBER FROM_TRX_NUMBER, GLD1.GL_DATE FROM_GL_DATE, RACU.ACCOUNT_NUMBER FROM_CUST, RACTT2.TYPE TO_TYPE, RACT2.CUSTOMER_TRX_ID TO_TRX_ID, RACT2.TRX_NUMBER TO_TRX_NUMBER, GLD2.GL_DATE TO_GL_DATE, NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD') TRAN_CURR, ARRA.GL_DATE TRAN_GL_DATE, SUM(ARRA.AMOUNT_APPLIED) TRAN_FOREIGN, SUM(ARRA.ACCTD_AMOUNT_APPLIED_FROM) TRAN_FUNCT, AR_RAXNCAR_XMLP_PKG.D_Tran_ForeignFormula() FROM RA_CUSTOMER_TRX RACT1, RA_CUSTOMER_TRX RACT2, RA_CUST_TRX_TYPES RACTT1, RA_CUST_TRX_TYPES RACTT2, HZ_CUST_ACCOUNTS RACU, AR_RECEIVABLE_APPLICATIONS ARRA, RA_CUST_TRX_LINE_GL_DIST GLD1, RA_CUST_TRX_LINE_GL_DIST GLD2 WHERE ARRA.GL_DATE BETWEEN :sql_start_date AND :sql_end_date AND RACT1.CUSTOMER_TRX_ID = ARRA.CUSTOMER_TRX_ID AND RACT1.CUSTOMER_TRX_ID = GLD1.CUSTOMER_TRX_ID AND GLD1.ACCOUNT_CLASS = 'REC' AND GLD1.LATEST_REC_FLAG = 'Y' AND RACTT1.CUST_TRX_TYPE_ID = RACT1.CUST_TRX_TYPE_ID AND ARRA.APPLICATION_TYPE = 'CM' AND RACU.CUST_ACCOUNT_ID(+) = RACT1.BILL_TO_CUSTOMER_ID AND RACT2.CUSTOMER_TRX_ID = ARRA.APPLIED_CUSTOMER_TRX_ID AND RACT2.CUSTOMER_TRX_ID = GLD2.CUSTOMER_TRX_ID AND GLD2.ACCOUNT_CLASS = 'REC' AND GLD2.LATEST_REC_FLAG = 'Y' AND RACTT2.CUST_TRX_TYPE_ID = RACT2.CUST_TRX_TYPE_ID GROUP BY RACTT1.POST_TO_GL, DECODE(RACTT1.POST_TO_GL, 'Y', :yes, :no), RACTT1.TYPE, RACT1.CUSTOMER_TRX_ID, RACT1.TRX_NUMBER, GLD1.GL_DATE, RACU.ACCOUNT_NUMBER, RACTT2.TYPE, RACT2.CUSTOMER_TRX_ID, RACT2.TRX_NUMBER, GLD2.GL_DATE, ARRA.GL_DATE, RACT1.CUSTOMER_TRX_ID, RACT2.CUSTOMER_TRX_ID, NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD') UNION SELECT RACTT1.POST_TO_GL POST_FLAG, DECODE(RACTT1.POST_TO_GL, 'Y', :yes, :no) POSTABLE, RACTT1.TYPE FROM_TYPE, RACT1.CUSTOMER_TRX_ID FROM_TRX_ID, RACT1.TRX_NUMBER FROM_TRX_NUMBER, GLD1.GL_DATE FROM_GL_DATE, RACU.ACCOUNT_NUMBER FROM_CUST, RACTT2.TYPE TO_TYPE, RACT2.CUSTOMER_TRX_ID TO_TRX_ID, RACT2.TRX_NUMBER TO_TRX_NUMBER, GLD2.GL_DATE TO_GL_DATE, NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD') TRAN_CURR, ADJ.GL_DATE TRAN_GL_DATE, SUM(ADJ.AMOUNT * -1) TRAN_FOREIGN, SUM(ADJ.ACCTD_AMOUNT * -1) TRAN_FUNCT, AR_RAXNCAR_XMLP_PKG.D_Tran_ForeignFormula() FROM RA_CUSTOMER_TRX RACT1, RA_CUSTOMER_TRX RACT2, RA_CUST_TRX_TYPES RACTT1, RA_CUST_TRX_TYPES RACTT2, HZ_CUST_ACCOUNTS RACU, AR_ADJUSTMENTS ADJ, RA_CUST_TRX_LINE_GL_DIST GLD1, RA_CUST_TRX_LINE_GL_DIST GLD2 WHERE ADJ.GL_DATE BETWEEN :sql_start_date AND :sql_end_date AND RACT2.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID AND RACT2.CUSTOMER_TRX_ID = GLD2.CUSTOMER_TRX_ID AND GLD2.ACCOUNT_CLASS = 'REC' AND GLD2.LATEST_REC_FLAG = 'Y' AND RACT1.CUSTOMER_TRX_ID = RACT2.INITIAL_CUSTOMER_TRX_ID AND RACT1.CUSTOMER_TRX_ID = GLD1.CUSTOMER_TRX_ID AND GLD1.ACCOUNT_CLASS = 'REC' AND GLD1.LATEST_REC_FLAG = 'Y' AND RACTT2.CUST_TRX_TYPE_ID = RACT2.CUST_TRX_TYPE_ID AND RACTT2.TYPE = 'INV' AND RACTT1.CUST_TRX_TYPE_ID = RACT1.CUST_TRX_TYPE_ID AND RACTT1.TYPE = 'DEP' AND RACU.CUST_ACCOUNT_ID(+) = RACT1.BILL_TO_CUSTOMER_ID GROUP BY RACTT1.POST_TO_GL, DECODE(RACTT1.POST_TO_GL, 'Y', :yes, :no), RACTT1.TYPE, RACT1.CUSTOMER_TRX_ID, RACT1.TRX_NUMBER, GLD1.GL_DATE, RACU.ACCOUNT_NUMBER, RACTT2.TYPE, RACT2.CUSTOMER_TRX_ID, RACT2.TRX_NUMBER, GLD2.GL_DATE, ADJ.GL_DATE, RACT1.CUSTOMER_TRX_ID, RACT2.CUSTOMER_TRX_ID, NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD') UNION SELECT RACTT1.POST_TO_GL POST_FLAG, DECODE(RACTT1.POST_TO_GL, 'Y', :yes, :no) POSTABLE, RACTT1.TYPE FROM_TYPE, RACT1.CUSTOMER_TRX_ID FROM_TRX_ID, RACT1.TRX_NUMBER FROM_TRX_NUMBER, GLD1.GL_DATE FROM_GL_DATE, RACU.ACCOUNT_NUMBER FROM_CUST, RACTT2.TYPE TO_TYPE, RACT2.CUSTOMER_TRX_ID TO_TRX_ID, RACT2.TRX_NUMBER TO_TRX_NUMBER, GLD2.GL_DATE TO_GL_DATE, NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD') TRAN_CURR, ADJ.GL_DATE TRAN_GL_DATE, SUM(ADJ.AMOUNT * -1) TRAN_FOREIGN, SUM(ADJ.ACCTD_AMOUNT * -1) TRAN_FUNCT, AR_RAXNCAR_XMLP_PKG.D_Tran_ForeignFormula() FROM RA_CUSTOMER_TRX RACT1, RA_CUSTOMER_TRX RACT2, RA_CUST_TRX_TYPES RACTT1, RA_CUST_TRX_TYPES RACTT2, HZ_CUST_ACCOUNTS RACU, AR_ADJUSTMENTS ADJ, RA_CUST_TRX_LINE_GL_DIST GLD1, RA_CUST_TRX_LINE_GL_DIST GLD2 WHERE ADJ.GL_DATE BETWEEN :sql_start_date AND :sql_end_date AND RACT2.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID AND RACT2.CUSTOMER_TRX_ID = RACT1.INITIAL_CUSTOMER_TRX_ID AND RACT2.CUSTOMER_TRX_ID = GLD2.CUSTOMER_TRX_ID AND GLD2.ACCOUNT_CLASS = 'REC' AND GLD2.LATEST_REC_FLAG = 'Y' AND RACTT2.CUST_TRX_TYPE_ID = RACT2.CUST_TRX_TYPE_ID AND RACTT2.TYPE = 'GUAR' AND RACTT1.CUST_TRX_TYPE_ID = RACT1.CUST_TRX_TYPE_ID AND RACTT1.TYPE = 'INV' AND RACT1.CUSTOMER_TRX_ID = GLD1.CUSTOMER_TRX_ID AND GLD1.ACCOUNT_CLASS = 'REC' AND GLD1.LATEST_REC_FLAG = 'Y' AND RACU.CUST_ACCOUNT_ID(+) = RACT1.BILL_TO_CUSTOMER_ID GROUP BY RACTT1.POST_TO_GL, DECODE(RACTT1.POST_TO_GL, 'Y', :yes, :no), RACTT1.TYPE, RACT1.CUSTOMER_TRX_ID, RACT1.TRX_NUMBER, GLD1.GL_DATE, RACU.ACCOUNT_NUMBER, RACTT2.TYPE, RACT2.CUSTOMER_TRX_ID, RACT2.TRX_NUMBER, GLD2.GL_DATE, ADJ.GL_DATE, RACT1.CUSTOMER_TRX_ID, RACT2.CUSTOMER_TRX_ID, NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD') UNION SELECT RACTT1.POST_TO_GL POST_FLAG, DECODE(RACTT1.POST_TO_GL, 'Y', :yes, :no) POSTABLE, RACTT1.TYPE FROM_TYPE, RACT1.CUSTOMER_TRX_ID FROM_TRX_ID, RACT1.TRX_NUMBER FROM_TRX_NUMBER, GLD1.GL_DATE FROM_GL_DATE, RACU.ACCOUNT_NUMBER FROM_CUST, NULL TO_TYPE, PS.CUSTOMER_TRX_ID TO_TRX_ID, PS.TRX_NUMBER TO_TRX_NUMBER, DECODE(PS.TRX_NUMBER,'Refund',TO_DATE(NULL), PS.GL_DATE ) TO_GL_DATE, NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD') TRAN_CURR, ARRA.GL_DATE TRAN_GL_DATE, SUM(ARRA.AMOUNT_APPLIED) TRAN_FOREIGN, SUM(ARRA.ACCTD_AMOUNT_APPLIED_FROM) TRAN_FUNCT, AR_RAXNCAR_XMLP_PKG.D_Tran_ForeignFormula() FROM RA_CUSTOMER_TRX RACT1, AR_PAYMENT_SCHEDULES PS, RA_CUST_TRX_TYPES RACTT1, HZ_CUST_ACCOUNTS RACU, AR_RECEIVABLE_APPLICATIONS ARRA, RA_CUST_TRX_LINE_GL_DIST GLD1 WHERE ARRA.GL_DATE BETWEEN :sql_start_date AND :sql_end_date AND RACT1.CUSTOMER_TRX_ID = ARRA.CUSTOMER_TRX_ID AND RACT1.CUSTOMER_TRX_ID = GLD1.CUSTOMER_TRX_ID AND GLD1.ACCOUNT_CLASS = 'REC' AND GLD1.LATEST_REC_FLAG = 'Y' AND RACTT1.CUST_TRX_TYPE_ID = RACT1.CUST_TRX_TYPE_ID AND ARRA.APPLICATION_TYPE = 'CM' AND ARRA.STATUS = 'ACTIVITY' AND RACU.CUST_ACCOUNT_ID(+) = RACT1.BILL_TO_CUSTOMER_ID AND PS.PAYMENT_SCHEDULE_ID = ARRA.APPLIED_PAYMENT_SCHEDULE_ID GROUP BY RACTT1.POST_TO_GL, DECODE(RACTT1.POST_TO_GL, 'Y', :yes, :no), RACTT1.TYPE, RACT1.CUSTOMER_TRX_ID, RACT1.TRX_NUMBER, GLD1.GL_DATE, RACU.ACCOUNT_NUMBER, PS.CUSTOMER_TRX_ID, PS.TRX_NUMBER, PS.GL_DATE, ARRA.GL_DATE, RACT1.CUSTOMER_TRX_ID, PS.CUSTOMER_TRX_ID, NVL(RACT1.INVOICE_CURRENCY_CODE, 'USD') /*ORDER BY 1 DESC, 3 ASC, 5 ASC, 6 ASC, 8 ASC, 10 ASC, 11 ASC, 13 ASC*/ order by 2 desc,3,5,4,6,8,10,11,13,1,14,15

Parameter Name

SQL text

Validation

Start GL Date

Date

End GL Date

Date

Download

Related Blitz Reports

AR Aging - 7 Buckets - By Salesperson/Agent AR Customer Open Balances Period Lookback AR Customer Statement AR Open Balances Revaluation JA India - Debtors Ledger Report- Not Supported: Reserved For Future Use AR Potential Reconciling Items GL Account Analysis AR Open Items Revaluation GL Account Analysis (Distributions) 11g

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Find out more

Features

Download

FAQ