AR_ASSGN_STD_CONT_INARREAR

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 PERCENT ACCOUNT_CLASS PERIOD_SET_NAME

Name

CUSTOMER_TRX_LINE_ID

CUSTOMER_TRX_ID

PREVIOUS_CUSTOMER_TRX_LINE_ID

REQUEST_ID

QUANTITY

GL_DATE

AMOUNT

PERCENT

ACCOUNT_CLASS

PERIOD_SET_NAME

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) QUANTITY , 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)))) GL_DATE, / 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, 'UNEARN', 1, - 1) ) ) AMOUNT, /* AMOUNT / rrs.percent PERCENT, /PERCENT/ RAL.LOOKUP_CODE ACCOUNT_CLASS, / ACCOUNT TYPE / GPS1.PERIOD_SET_NAME / PERIOD_SET NAME / FROM ra_customer_trx_lines_all ctl, gl_sets_of_books sob, ra_customer_trx_all 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_all 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 'UNEARN' = 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 ( 'UNEARN' , 'UNBILL') 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 = 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') AND ctl.previous_customer_trx_line_id IS NULL

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) QUANTITY ,

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)))) GL_DATE,

/* 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, 'UNEARN', 1, - 1) ) ) AMOUNT,

/* AMOUNT */

rrs.percent PERCENT,

/*PERCENT*/

RAL.LOOKUP_CODE ACCOUNT_CLASS,

/* ACCOUNT TYPE */

GPS1.PERIOD_SET_NAME

/* PERIOD_SET NAME */

FROM ra_customer_trx_lines_all ctl,

gl_sets_of_books sob,

ra_customer_trx_all 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_all 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 'UNEARN' = 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 ( 'UNEARN' , 'UNBILL')

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 = 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')

AND ctl.previous_customer_trx_line_id IS NULL