VIEW: APPS.AR_REV_ASSIGN_FOR_STD_V

Source

View Text - Preformatted

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)))) , /* 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 TYPE */ GPS1.PERIOD_SET_NAME /* PERIOD_SET NAME */ 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 = 'REV' OR ral.lookup_code = DECODE(ct.invoicing_rule_id, -2, 'UNEARN', -3, '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 = DECODE(rr.frequency, '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)

View Text - HTML Formatted

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))))
, /* 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 TYPE */ GPS1.PERIOD_SET_NAME /* PERIOD_SET NAME */
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 = 'REV' OR RAL.LOOKUP_CODE = DECODE(CT.INVOICING_RULE_ID
, -2
, 'UNEARN'
, -3
, '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 = DECODE(RR.FREQUENCY
, '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)