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 , RAGT.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(CMA.GL_DATE, NVL(RAGT.RULE_DATE, LEAST( (CTL.RULE_START_DATE - GPS1.START_DATE) + GPS2.START_DATE, GPS2.END_DATE, NVL(CTL.RULE_END_DATE, TO_DATE('31-12-4712', 'DD-MM-RRRR')) ) ) ), /* GL DATE */ CASE SUBSTR(RAGT.RULE_TYPE, 1, 1) WHEN 'A' THEN /* normal fixed or variable duration rule */ TO_NUMBER( NVL(CMA.AMOUNT, CTL.REVENUE_AMOUNT * (RAGT.PERCENT / 100)) * DECODE(RAGT.ACCOUNT_CLASS, 'REV', 1, - 1)) WHEN 'P' THEN /* PPRR rule */ RAGT.PPRR_AMOUNT END AMOUNT, /* AMOUNT */ RAGT.ACCOUNT_CLASS, /* ACCOUNT CLASS */ GPS1.PERIOD_SET_NAME /* PERIOD_SET NAME */ FROM ar_revenue_assignments_gt ragt, ra_customer_trx_lines ctl, 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, 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)

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(RAGT
, CTL
, LGD
, GPS1
, GPS2
, CMA) INDEX RAGT (AR_REVENUE_ASSIGNMENTS_GT_N1) */ RAGT.SESSION_ID
, RAGT.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(CMA.GL_DATE
, NVL(RAGT.RULE_DATE
, LEAST( (CTL.RULE_START_DATE - GPS1.START_DATE) + GPS2.START_DATE
, GPS2.END_DATE
, NVL(CTL.RULE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-RRRR')) ) ) )
, /* GL DATE */ CASE SUBSTR(RAGT.RULE_TYPE
, 1
, 1) WHEN 'A' THEN /* NORMAL FIXED OR VARIABLE DURATION RULE */ TO_NUMBER( NVL(CMA.AMOUNT
, CTL.REVENUE_AMOUNT * (RAGT.PERCENT / 100)) * DECODE(RAGT.ACCOUNT_CLASS
, 'REV'
, 1
, - 1)) WHEN 'P' THEN /* PPRR RULE */ RAGT.PPRR_AMOUNT END AMOUNT
, /* AMOUNT */ RAGT.ACCOUNT_CLASS
, /* ACCOUNT CLASS */ GPS1.PERIOD_SET_NAME /* PERIOD_SET NAME */
FROM AR_REVENUE_ASSIGNMENTS_GT RAGT
, RA_CUSTOMER_TRX_LINES CTL
, 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
, 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)