AR_REV_ASSIGN_FOR_PPRR

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_ID PREV_CUSTOMER_TRX_LINE_ID REQUEST_ID QUANTITY GL_DATE AMOUNT ACCOUNT_CLASS PERIOD_SET_NAME

Name

CUSTOMER_TRX_LINE_ID

CUSTOMER_TRX_ID

PREV_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.prev_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 prev_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 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 = '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 (+)

SQL_Statement

SELECT

main_qry.customer_trx_line_id,

main_qry.customer_trx_id,

main_qry.prev_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 prev_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 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 = '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 (+)