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 |