eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_REVENUE_ASSIGNMENTS_V

Source

View Text - Preformatted


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 (+)

View Text - HTML Formatted

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 (+)