AR_ASSGN_PPRR_CONT_INARREAR_V

Details

• Schema: FUSION

• Object owner: AR

• Object type: VIEW

Columns

Name
CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_ID PREVIOUS_CUSTOMER_TRX_LINE_ID REQUEST_ID QUANTITY GL_DATE AMOUNT ACCOUNT_CLASS PERIOD_SET_NAME

Query

SQL_Statement

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, 'UNEARN', 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_all ctl, ra_customer_trx_all 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 NVL(rr.frequency,accounted_period_type) = 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 in ('UNEARN','UNBILL') AND main_qry.customer_trx_line_id = cma.customer_trx_line_id (+) AND main_qry.gl_date = cma.gl_date (+)