AR Other Applications

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Other Applications Report (XML)
Short Name: RAXNCAR_XML
DB package: AR_RAXNCAR_XMLP_PKG
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
Ask a question
Parameter Name SQL text Validation
End GL Date
 
Date
Start GL Date
 
Date