View: AR_REVENUE_ASSIGNMENTS_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.AR_REVENUE_ASSIGNMENTS_V

View Text

SELECT /*+ ORDERED USE_NL(RAGT
, CTL
, LGD
, GPS1
, GPS2
, CMA) INDEX RAGT (AR_REVENUE_ASSIGNMENTS_GT_N1) */ RAGT.SESSION_ID
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.CUSTOMER_TRX_ID
, CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID
, CTL.REQUEST_ID
, /* REQUEST_ID */ NVL(CTL.QUANTITY_CREDITED
, CTL.QUANTITY_INVOICED)
, NVL(LGD.ORIGINAL_GL_DATE
, NVL(CMA.GL_DATE
, NVL(RAGT.RULE_DATE
, LEAST( (CTL.RULE_START_DATE - GPS1.START_DATE) + GPS2.START_DATE
, GPS2.END_DATE ) ) ) )
, /* GL DATE */ TO_NUMBER(NVL(LGD.AMOUNT
, NVL(CMA.AMOUNT
, CTL.REVENUE_AMOUNT * (RAGT.PERCENT / 100))) * DECODE(RAGT.ACCOUNT_CLASS
, 'REV'
, 1
, - 1))
, /* AMOUNT */ RAGT.ACCOUNT_CLASS
, /* ACCOUNT CLASS */ GPS1.PERIOD_SET_NAME /* PERIOD_SET NAME */
FROM AR_REVENUE_ASSIGNMENTS_GT RAGT
, RA_CUSTOMER_TRX_LINES CTL
, RA_CUST_TRX_LINE_GL_DIST LGD
, AR_PERIODS GPS1
, AR_PERIODS GPS2
, AR_CREDIT_MEMO_AMOUNTS CMA
WHERE CTL.CUSTOMER_TRX_LINE_ID = RAGT.CUSTOMER_TRX_LINE_ID
AND CTL.CUSTOMER_TRX_LINE_ID = CMA.CUSTOMER_TRX_LINE_ID(+)
AND DECODE(CTL.AUTORULE_COMPLETE_FLAG
, NULL
, DECODE(CTL.AUTORULE_DURATION_PROCESSED
, -1
, CTL.CUSTOMER_TRX_LINE_ID
, NULL)
, 'Y'
, DECODE(CTL.AUTORULE_DURATION_PROCESSED
, -1
, CTL.CUSTOMER_TRX_LINE_ID
, NULL)
, NULL) = LGD.CUSTOMER_TRX_LINE_ID(+)
AND 'N' = LGD.ACCOUNT_SET_FLAG(+)
AND 'REV' = LGD.ACCOUNT_CLASS(+)
AND DECODE(CTL.AUTORULE_COMPLETE_FLAG
, NULL
, DECODE(CTL.AUTORULE_DURATION_PROCESSED
, -1
, GPS1.ROWID
, GPS2.ROWID)
, GPS2.ROWID) = GPS2.ROWID
AND DECODE(CMA.GL_DATE
, NULL
, GPS2.ROWID
, GPS1.ROWID) = GPS2.ROWID
AND GPS2.PERIOD_SET_NAME = GPS1.PERIOD_SET_NAME
AND RAGT.PERIOD_TYPE = GPS1.PERIOD_TYPE
AND GPS2.PERIOD_TYPE = GPS1.PERIOD_TYPE
AND CTL.RULE_START_DATE BETWEEN GPS1.START_DATE
AND GPS1.END_DATE
AND GPS1.END_DATE BETWEEN CTL.RULE_START_DATE
AND CTL.RULE_START_DATE + RAGT.MAX_REGULAR_PERIOD_LENGTH
AND GPS2.NEW_PERIOD_NUM = GPS1.NEW_PERIOD_NUM + (RAGT.PERIOD_NUMBER - 1) UNION ALL SELECT MAIN_QRY.SESSION_ID
, MAIN_QRY.CUSTOMER_TRX_LINE_ID
, MAIN_QRY.CUSTOMER_TRX_ID
, MAIN_QRY.PREVIOUS_CUSTOMER_TRX_LINE_ID
, MAIN_QRY.REQUEST_ID
, MAIN_QRY.QUANTITY
, MAIN_QRY.GL_DATE
, NVL(CMA.AMOUNT
, CASE MAIN_QRY.RULE_TYPE WHEN 'PP_DR_ALL' THEN MAIN_QRY.DAILY_RATE * DAYS_PER_GL_PERIOD WHEN 'PP_DR_PP' THEN CASE FULL_OR_PARTIAL WHEN 'F' THEN /** FULL PERIODS **/ (REVENUE_AMOUNT - ( DAYS_IN_PARTIAL_PERIODS * MAIN_QRY.DAILY_RATE ))/ (CNT_OF_FULL_PERIODS) ELSE /** PARTIAL PERIODS **/ MAIN_QRY.DAILY_RATE * DAYS_PER_GL_PERIOD END ELSE (REVENUE_AMOUNT * PERIOD_FACTOR)/ (NO_OF_FULL_PERIODS) END ) * DECODE(MAIN_QRY.ACCOUNT_CLASS
, 'REV'
, 1
, -1) AMOUNT
, MAIN_QRY.ACCOUNT_CLASS
, MAIN_QRY.PERIOD_SET_NAME
FROM ( SELECT RAGT.SESSION_ID
, CTL.CUSTOMER_TRX_ID
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID
, CTL.REQUEST_ID
, CTL.RULE_START_DATE
, CTL.RULE_END_DATE
, CT.INVOICING_RULE_ID
, NVL(CTL.QUANTITY_CREDITED
, CTL.QUANTITY_INVOICED) QUANTITY
, GPS.START_DATE
, GPS.END_DATE
, GPS.PERIOD_TYPE
, GPS.PERIOD_SET_NAME
, RAGT.RULE_TYPE RULE_TYPE
, /** GL DATE **/ LEAST((CTL.RULE_START_DATE - FIRST_VALUE(START_DATE) OVER (PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID ORDER BY GPS.START_DATE ) ) + GPS.START_DATE
, GPS.END_DATE
, CTL.RULE_END_DATE ) GL_DATE
, /** PERIOD SEQ NUMBER **/ ROW_NUMBER() OVER( PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID ORDER BY GPS.START_DATE ) PERIOD_SEQ_NUM
, /** PARTIAL OR FULL ***/ DECODE(LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE) )
, (GPS.END_DATE - GPS.START_DATE)
, 'F'
, 'P' ) FULL_OR_PARTIAL
, /** TOTAL NUMBER OF DAYS IN REVREC SCHEDULE **/ (CTL.RULE_END_DATE - CTL.RULE_START_DATE) + 1 TOTAL_DAYS_IN_SCHEDULE
, /** DAILY RATE **/ (CTL.REVENUE_AMOUNT) /((CTL.RULE_END_DATE - CTL.RULE_START_DATE) + 1) DAILY_RATE
, /** DAYS PER GIVEN GL PERIOD **/ LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE) ) + 1 DAYS_PER_GL_PERIOD
, /** MAXIMUM DAYS PER GIVEN GL PERIOD ***/ (GPS.END_DATE - GPS.START_DATE) + 1 MAX_DAYS_PER_GL_PERIOD
, /** PERIOD FACTOR **/ (LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE) ) + 1 )/ ((GPS.END_DATE - GPS.START_DATE) + 1) PERIOD_FACTOR
, /** TOTAL FULL PERIODS (INCLUDES PARTIAL PERIODS) ***/ SUM((LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE) ) + 1 )/ ((GPS.END_DATE - GPS.START_DATE) + 1) ) OVER (PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID ) NO_OF_FULL_PERIODS
, /** COUNT OF ONLY FULL PERIODS ***/ SUM( CASE /** COUNT ONLY FULL PERIODS **/ WHEN LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE)) = (GPS.END_DATE - GPS.START_DATE) THEN 1 ELSE /** DO NOT COUNT PARTIAL PERIODS */ 0 END ) OVER (PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID ) CNT_OF_FULL_PERIODS
, /**DAYS IN PARTIAL PERIODS ***/ SUM( CASE WHEN LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE)) = (GPS.END_DATE - GPS.START_DATE) THEN /** FULL PERIODS **/ 0 ELSE /** PARTIAL PERIODS */ LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE)) + 1 END ) OVER (PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID ) DAYS_IN_PARTIAL_PERIODS
, /** REVENUE AMOUNT FOR LINE ITEM ***/ CTL.REVENUE_AMOUNT
, RAGT.ACCOUNT_CLASS
FROM AR_REVENUE_ASSIGNMENTS_GT RAGT
, AR_PERIODS GPS
, RA_CUSTOMER_TRX_LINES CTL
, RA_CUSTOMER_TRX CT
WHERE RAGT.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID
AND CTL.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND RAGT.RULE_TYPE NOT IN ('A'
, 'ACC_DUR')
AND RAGT.PERIOD_TYPE = GPS.PERIOD_TYPE
AND (CTL.RULE_START_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE OR CTL.RULE_END_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE OR GPS.START_DATE BETWEEN CTL.RULE_START_DATE
AND CTL.RULE_END_DATE ) ) MAIN_QRY
, AR_CREDIT_MEMO_AMOUNTS CMA
WHERE (MAIN_QRY.ACCOUNT_CLASS = 'REV' OR MAIN_QRY.ACCOUNT_CLASS = DECODE(MAIN_QRY.INVOICING_RULE_ID
, -2
, 'UNEARN'
, -3
, 'UNBILL'))
AND MAIN_QRY.CUSTOMER_TRX_LINE_ID = CMA.CUSTOMER_TRX_LINE_ID (+)
AND MAIN_QRY.GL_DATE = CMA.GL_DATE (+)

Columns