AR_REVENUE_ASSIGNMENTS

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 RULE_TYPE

Name

CUSTOMER_TRX_LINE_ID

CUSTOMER_TRX_ID

PREVIOUS_CUSTOMER_TRX_LINE_ID

REQUEST_ID

QUANTITY

GL_DATE

AMOUNT

ACCOUNT_CLASS

PERIOD_SET_NAME

RULE_TYPE

Query

SQL_Statement
SELECT CTL.CUSTOMER_TRX_LINE_ID , /* 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(RRS.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 / / USE THE AR_CREDIT_MEMO_AMOUNT VALUE IF IT IS NOT NULL. OR USE THE LUMP SUM IF THIS IS A VARIABLE DURATION RULE WITH A LUMP SUM. / TO_NUMBER(DECODE(CT.COMPLETE_FLAG, 'N', 0, NVL(LGD.AMOUNT, NVL(CMA.AMOUNT, DECODE(RRS_LUMP.PERCENT + DECODE(RRS.PERIOD_NUMBER, 1, 1, NULL), NULL, DECODE(RR.TYPE, 'A', CTL.REVENUE_AMOUNT (RRS.PERCENT / 100), /* PRORATE THE AMOUNT EXCEPT OVER FIRST / / PERIOD IF THERE IS A LUMP SUM. / ( CTL.REVENUE_AMOUNT - / LUMP SUM CASE / NVL( ( CTL.REVENUE_AMOUNT (RRS_LUMP.PERCENT / 100) ), 0 ) ) / DECODE(RRS_LUMP.PERCENT, NULL, CTL.ACCOUNTING_RULE_DURATION, CTL.ACCOUNTING_RULE_DURATION -1) ), /* END NOT LUMP SUM CASE / / USE THE LUMP SUM IF IT IS NOT NULL AND THIS IS THE FIRST PERIOD. / CTL.REVENUE_AMOUNT (RRS_LUMP.PERCENT / 100) /* INSURE THAT THE SIGN OF THE ROW REFLECTS THE ACCOUNT TYPE AND INVOICING RULE. / ) / END CMA.AMOUNT NOT SPECIFIED CASE / ) / END CMA.AMOUNT NVL / ) / END LGD.AMOUNT NVL / DECODE(RAL.LOOKUP_CODE, 'REV', 1, - 1) ) ) , /* AMOUNT / RAL.LOOKUP_CODE ACCOUNT_CLASS , / ACCOUNT TYPE / GPS1.PERIOD_SET_NAME , / PERIOD_SET NAME / rr.type rule_type / RULE_TYPE / FROM ra_customer_trx_lines ctl , gl_sets_of_books sob , ra_customer_trx ct , ra_rules rr , ra_rule_schedules rrs , ar_period_types gpt , ar_credit_memo_amounts cma , ra_rule_schedules rrs_lump , ra_cust_trx_line_gl_dist lgd, ar_periods gps1 , ar_periods gps2 , ar_lookups ral WHERE sob.set_of_books_id = ctl.set_of_books_id AND ct.customer_trx_id = ctl.customer_trx_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 / Doubles the amount of rows returned for each GL date. / AND ral.lookup_type = 'AUTOGL_TYPE' AND ral.lookup_code IN ( 'REV', 'UNEARN' ) AND DECODE(rr.type, 'A', rr.rule_id, -1) = rrs.rule_id / Join to rrs_lump if the rule is variable duration with a lump sum / AND rrs_lump.rule_id(+) = DECODE(rr.type, 'ACC_DUR', rr.rule_id, -10) AND rrs_lump.period_number(+) = 1 AND rr.rule_id = ctl.accounting_rule_id AND rrs.period_number <= DECODE(ctl.accounting_rule_duration , NULL, rr.occurrences, ctl.accounting_rule_duration) AND gps2.period_set_name = gps1.period_set_name AND gpt.period_type = gps1.period_type AND gpt.period_type = DECODE(rr.frequency, NULL,accounted_period_type, 'SPECIFIC', accounted_period_type, DECODE(ctl.previous_customer_trx_line_id, NULL, rr.frequency, accounted_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 + gpt.max_regular_period_length AND gps2.new_period_num = gps1.new_period_num + (rrs.period_number - 1) AND (rr.type IN ('A', 'ACC_DUR') OR ctl.previous_customer_trx_line_id IS NOT NULL) /* Handle only Fixed and Variable rules */ UNION ALL 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, main_qry.rule_type 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 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 ( 'REV', 'UNEARN' ) AND main_qry.customer_trx_line_id = cma.customer_trx_line_id (+) AND main_qry.gl_date = cma.gl_date (+)

SQL_Statement

SELECT CTL.CUSTOMER_TRX_LINE_ID ,

/* 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(RRS.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 */

/* USE THE AR_CREDIT_MEMO_AMOUNT VALUE IF IT IS NOT NULL. OR USE THE LUMP SUM IF THIS IS A VARIABLE DURATION RULE WITH A LUMP SUM. */

TO_NUMBER(DECODE(CT.COMPLETE_FLAG, 'N', 0, NVL(LGD.AMOUNT, NVL(CMA.AMOUNT, DECODE(RRS_LUMP.PERCENT + DECODE(RRS.PERIOD_NUMBER, 1, 1, NULL), NULL, DECODE(RR.TYPE, 'A', CTL.REVENUE_AMOUNT * (RRS.PERCENT / 100),

/* PRORATE THE AMOUNT EXCEPT OVER FIRST */

/* PERIOD IF THERE IS A LUMP SUM. */

( CTL.REVENUE_AMOUNT -

/* LUMP SUM CASE */

NVL( ( CTL.REVENUE_AMOUNT * (RRS_LUMP.PERCENT / 100) ), 0 ) ) / DECODE(RRS_LUMP.PERCENT, NULL, CTL.ACCOUNTING_RULE_DURATION, CTL.ACCOUNTING_RULE_DURATION -1) ),

/* END NOT LUMP SUM CASE */

/* USE THE LUMP SUM IF IT IS NOT NULL AND THIS IS THE FIRST PERIOD. */

CTL.REVENUE_AMOUNT * (RRS_LUMP.PERCENT / 100)

/* INSURE THAT THE SIGN OF THE ROW REFLECTS THE ACCOUNT TYPE AND INVOICING RULE. */

)

/* END CMA.AMOUNT NOT SPECIFIED CASE */

)

/* END CMA.AMOUNT NVL */

)

/* END LGD.AMOUNT NVL */

* DECODE(RAL.LOOKUP_CODE, 'REV', 1, - 1) ) ) ,

/* AMOUNT */

RAL.LOOKUP_CODE ACCOUNT_CLASS ,

/* ACCOUNT TYPE */

GPS1.PERIOD_SET_NAME ,

/* PERIOD_SET NAME */

rr.type rule_type

/* RULE_TYPE */

FROM ra_customer_trx_lines ctl ,

gl_sets_of_books sob ,

ra_customer_trx ct ,

ra_rules rr ,

ra_rule_schedules rrs ,

ar_period_types gpt ,

ar_credit_memo_amounts cma ,

ra_rule_schedules rrs_lump ,

ra_cust_trx_line_gl_dist lgd,

ar_periods gps1 ,

ar_periods gps2 ,

ar_lookups ral

WHERE sob.set_of_books_id = ctl.set_of_books_id

AND ct.customer_trx_id = ctl.customer_trx_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

/* Doubles the amount of rows returned for each GL date. */

AND ral.lookup_type = 'AUTOGL_TYPE'

AND ral.lookup_code IN (

'REV',

'UNEARN'

)

AND DECODE(rr.type, 'A', rr.rule_id, -1) = rrs.rule_id

/* Join to rrs_lump if the rule is variable duration with a lump sum */

AND rrs_lump.rule_id(+) = DECODE(rr.type, 'ACC_DUR', rr.rule_id, -10)

AND rrs_lump.period_number(+) = 1

AND rr.rule_id = ctl.accounting_rule_id

AND rrs.period_number <= DECODE(ctl.accounting_rule_duration , NULL, rr.occurrences, ctl.accounting_rule_duration)

AND gps2.period_set_name = gps1.period_set_name

AND gpt.period_type = gps1.period_type

AND gpt.period_type = DECODE(rr.frequency, NULL,accounted_period_type, 'SPECIFIC', accounted_period_type, DECODE(ctl.previous_customer_trx_line_id, NULL, rr.frequency, accounted_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 + gpt.max_regular_period_length

AND gps2.new_period_num = gps1.new_period_num + (rrs.period_number - 1)

AND (rr.type IN ('A', 'ACC_DUR')

OR ctl.previous_customer_trx_line_id IS NOT NULL)

/** Handle only Fixed and Variable rules **/

UNION ALL

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,

main_qry.rule_type

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

'REV',

'UNEARN'

)

AND main_qry.customer_trx_line_id = cma.customer_trx_line_id (+)

AND main_qry.gl_date = cma.gl_date (+)