[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
{ 18-SEP-2005 M Raymond 4602892 Added 'distinct' to inserts to prevent the
| creation of multiple rows in _GT table and, ultimately
| the cartesian insert of extra gl_dist rows.
*===================================================================================*/
PROCEDURE build_for_credit(
p_session_id IN number,
p_period_set_name IN gl_periods.period_set_name%TYPE,
p_request_id IN ra_customer_trx_all.request_id%TYPE,
p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
p_customer_trx_line_id IN ra_customer_trx_lines_all.customer_trx_line_id%TYPE)
IS
BEGIN
arp_standard.debug('arp_revenue_assignments.build_for_credit()+');
INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
(SESSION_ID,
CUSTOMER_TRX_LINE_ID,
COMPLETE_FLAG,
ACCOUNT_CLASS,
LUMP_SUM_FLAG,
RULE_TYPE,
PERIOD_NUMBER,
PERCENT,
RULE_DATE,
SET_OF_BOOKS_ID,
PERIOD_TYPE,
MAX_REGULAR_PERIOD_LENGTH)
select distinct p_session_id,
tl.previous_customer_trx_line_id,
t.complete_flag,
ral.lookup_code account_class,
decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
rr.type,
rrs.period_number,
decode(rr.type, 'ACC_DUR',
decode(rrs_lump.rule_id, null,
(1/nvl(itl.accounting_rule_duration, 1)) ,
decode(rrs.period_number, 1, rrs_lump.percent / 100,
(1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
itl.accounting_rule_duration - 1)) *
(1 - rrs_lump.percent/100))) * 100,
rrs.percent) percent,
rrs.rule_date,
tl.set_of_books_id,
decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
gsb.accounted_period_type)) period_type,
apt.max_regular_period_length
from
ra_customer_trx_lines tl,
ra_customer_trx_lines itl,
ra_customer_trx t,
ra_rules rr,
ra_rule_schedules rrs,
ra_rule_schedules rrs_lump,
ar_lookups ral,
gl_sets_of_books gsb,
ar_period_types apt
where
tl.customer_trx_id = t.customer_trx_id
and tl.accounting_rule_id = rr.rule_id
and tl.set_of_books_id = gsb.set_of_books_id
and tl.previous_customer_trx_line_id =
itl.customer_trx_line_id (+)
and gsb.accounted_period_type = apt.period_type
and ral.lookup_type = 'AUTOGL_TYPE'
and (ral.lookup_code = 'REV' or
ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
-3, 'UNBILL'))
and rrs.period_number <= DECODE(rr.type, 'PP_DR_PP', 1,
'PP_DR_ALL', 1,
nvl(itl.accounting_rule_duration, rr.occurrences))
and rrs_lump.rule_id (+) = rr.rule_id
and rrs_lump.period_number (+) = 1
and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id
and t.request_id = p_request_id
and t.previous_customer_trx_id is not null;
INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
(SESSION_ID,
CUSTOMER_TRX_LINE_ID,
COMPLETE_FLAG,
ACCOUNT_CLASS,
LUMP_SUM_FLAG,
RULE_TYPE,
PERIOD_NUMBER,
PERCENT,
RULE_DATE,
SET_OF_BOOKS_ID,
PERIOD_TYPE,
MAX_REGULAR_PERIOD_LENGTH)
select distinct p_session_id,
tl.previous_customer_trx_line_id,
t.complete_flag,
ral.lookup_code account_class,
decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
rr.type,
rrs.period_number,
decode(rr.type, 'ACC_DUR',
decode(rrs_lump.rule_id, null, 1/nvl(itl.accounting_rule_duration, 1),
decode(rrs.period_number, 1, rrs_lump.percent / 100,
(1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
itl.accounting_rule_duration - 1)) *
(1 - rrs_lump.percent/100))) * 100,
rrs.percent) percent,
rrs.rule_date,
tl.set_of_books_id,
decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
gsb.accounted_period_type)) period_type,
apt.max_regular_period_length
from
ra_customer_trx_lines tl,
ra_customer_trx_lines itl,
ra_customer_trx t,
ra_rules rr,
ra_rule_schedules rrs,
ra_rule_schedules rrs_lump,
ar_lookups ral,
gl_sets_of_books gsb,
ar_period_types apt
where
tl.customer_trx_line_id = p_customer_trx_line_id
and tl.customer_trx_id = t.customer_trx_id
and tl.accounting_rule_id = rr.rule_id
and tl.set_of_books_id = gsb.set_of_books_id
and tl.previous_customer_trx_line_id =
itl.customer_trx_line_id (+)
and gsb.accounted_period_type = apt.period_type
and ral.lookup_type = 'AUTOGL_TYPE'
and (ral.lookup_code = 'REV' or
ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
-3, 'UNBILL'))
and rrs.period_number <= DECODE(rr.type, 'PP_DR_PP', 1,
'PP_DR_ALL', 1,
nvl(itl.accounting_rule_duration, rr.occurrences))
and rrs_lump.rule_id (+) = rr.rule_id
and rrs_lump.period_number (+) = 1
and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id;
INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
(SESSION_ID,
CUSTOMER_TRX_LINE_ID,
COMPLETE_FLAG,
ACCOUNT_CLASS,
LUMP_SUM_FLAG,
RULE_TYPE,
PERIOD_NUMBER,
PERCENT,
RULE_DATE,
SET_OF_BOOKS_ID,
PERIOD_TYPE,
MAX_REGULAR_PERIOD_LENGTH)
select distinct p_session_id,
tl.previous_customer_trx_line_id,
t.complete_flag,
ral.lookup_code account_class,
decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
rr.type,
rrs.period_number,
decode(rr.type, 'ACC_DUR',
decode(rrs_lump.rule_id, null, 1/nvl(itl.accounting_rule_duration, 1),
decode(rrs.period_number, 1, rrs_lump.percent / 100,
(1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
itl.accounting_rule_duration - 1)) *
(1 - rrs_lump.percent/100))) * 100,
rrs.percent) percent,
rrs.rule_date,
tl.set_of_books_id,
decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
gsb.accounted_period_type)) period_type,
apt.max_regular_period_length
from
ra_customer_trx_lines tl,
ra_customer_trx_lines itl,
ra_customer_trx t,
ra_rules rr,
ra_rule_schedules rrs,
ra_rule_schedules rrs_lump,
ar_lookups ral,
gl_sets_of_books gsb,
ar_period_types apt
where
t.customer_trx_id = p_customer_trx_id
and tl.customer_trx_id = t.customer_trx_id
and tl.accounting_rule_id = rr.rule_id
and tl.set_of_books_id = gsb.set_of_books_id
and tl.previous_customer_trx_line_id =
itl.customer_trx_line_id (+)
and gsb.accounted_period_type = apt.period_type
and ral.lookup_type = 'AUTOGL_TYPE'
and (ral.lookup_code = 'REV' or
ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
-3, 'UNBILL'))
and rrs.period_number <= DECODE(rr.type, 'PP_DR_PP', 1,
'PP_DR_ALL', 1,
nvl(itl.accounting_rule_duration, rr.occurrences))
and rrs_lump.rule_id (+) = rr.rule_id
and rrs_lump.period_number (+) = 1
and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id;
SELECT
SESSION_ID,
CUSTOMER_TRX_LINE_ID,
COMPLETE_FLAG,
ACCOUNT_CLASS,
LUMP_SUM_FLAG,
RULE_TYPE,
PERIOD_NUMBER,
PERCENT,
RULE_DATE,
SET_OF_BOOKS_ID,
PERIOD_TYPE,
MAX_REGULAR_PERIOD_LENGTH
FROM AR_REVENUE_ASSIGNMENTS_GT
WHERE session_id = p_session_id
ORDER BY CUSTOMER_TRX_LINE_ID, RULE_date;