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_REV_ASSIGN_FOR_PPRR_V

Source

View Text - Preformatted


SELECT 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(ral.lookup_code, 'REV', 1, -1) amount, ral.lookup_code account_class, period_set_name FROM (SELECT /* */ 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, rr.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, ctl.CUSTOMER_TRX_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,ctl.CUSTOMER_TRX_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, ctl.CUSTOMER_TRX_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,ctl.CUSTOMER_TRX_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,ctl.CUSTOMER_TRX_ID ) days_in_partial_periods, /** Revenue Amount for line item ***/ ctl.revenue_amount FROM ar_periods gps, ar_system_parameters_all SYS, gl_sets_of_books gls, ar_period_types gpt, ra_rules rr, ra_customer_trx_lines ctl, ra_customer_trx ct WHERE sys.set_of_books_id = gls.set_of_books_id AND sys.org_id = ctl.org_id AND gls.period_set_name = gps.period_set_name AND gps.period_type = gpt.period_type AND ctl.accounting_rule_id = rr.rule_id AND rr.type NOT IN ('A', 'ACC_DUR') AND ctl.previous_customer_trx_line_id IS NULL /* no regular credit memos */ AND rr.frequency = gpt.period_type AND ct.customer_trx_id = ctl.customer_trx_id 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_lookups ral, ar_credit_memo_amounts cma WHERE ral.lookup_type = 'AUTOGL_TYPE' AND (ral.lookup_code = 'REV' OR ral.lookup_code = 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 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(RAL.LOOKUP_CODE
, 'REV'
, 1
, -1) AMOUNT
, RAL.LOOKUP_CODE ACCOUNT_CLASS
, PERIOD_SET_NAME
FROM (SELECT /* */ 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
, RR.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
, CTL.CUSTOMER_TRX_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
, CTL.CUSTOMER_TRX_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
, CTL.CUSTOMER_TRX_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
, CTL.CUSTOMER_TRX_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
, CTL.CUSTOMER_TRX_ID ) DAYS_IN_PARTIAL_PERIODS
, /** REVENUE AMOUNT FOR LINE ITEM ***/ CTL.REVENUE_AMOUNT
FROM AR_PERIODS GPS
, AR_SYSTEM_PARAMETERS_ALL SYS
, GL_SETS_OF_BOOKS GLS
, AR_PERIOD_TYPES GPT
, RA_RULES RR
, RA_CUSTOMER_TRX_LINES CTL
, RA_CUSTOMER_TRX CT
WHERE SYS.SET_OF_BOOKS_ID = GLS.SET_OF_BOOKS_ID
AND SYS.ORG_ID = CTL.ORG_ID
AND GLS.PERIOD_SET_NAME = GPS.PERIOD_SET_NAME
AND GPS.PERIOD_TYPE = GPT.PERIOD_TYPE
AND CTL.ACCOUNTING_RULE_ID = RR.RULE_ID
AND RR.TYPE NOT IN ('A'
, 'ACC_DUR')
AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID IS NULL /* NO REGULAR CREDIT MEMOS */
AND RR.FREQUENCY = GPT.PERIOD_TYPE
AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
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_LOOKUPS RAL
, AR_CREDIT_MEMO_AMOUNTS CMA
WHERE RAL.LOOKUP_TYPE = 'AUTOGL_TYPE'
AND (RAL.LOOKUP_CODE = 'REV' OR RAL.LOOKUP_CODE = 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 (+)