[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
net_rev_index BINARY_INTEGER; -- keeps track of next row to insert
insert_dist VARCHAR2(1),
insert_cma VARCHAR2(1),
insert_offset VARCHAR2(1),
check_gl_date VARCHAR2(1) -- for crediting rel9 immed invoices
);
cm_sched_index BINARY_INTEGER := 0; -- keeps track of next row to insert
TYPE cm_sched_insert_dist_tab_type IS
TABLE OF cm_schedule_rec.insert_dist%type
INDEX BY BINARY_INTEGER;
cm_sched_insert_dist_t cm_sched_insert_dist_tab_type;
null_cm_sched_insert_dist CONSTANT cm_sched_insert_dist_tab_type :=
cm_sched_insert_dist_t;
TYPE cm_sched_insert_cma_tab_type IS
TABLE OF cm_schedule_rec.insert_cma%type
INDEX BY BINARY_INTEGER;
cm_sched_insert_cma_t cm_sched_insert_cma_tab_type;
null_cm_sched_insert_cma CONSTANT cm_sched_insert_cma_tab_type :=
cm_sched_insert_cma_t;
TABLE OF cm_schedule_rec.insert_offset%type
INDEX BY BINARY_INTEGER;
cm_sched_insert_offset_t cm_sched_ins_offset_tab_type;
null_cm_sched_insert_offset CONSTANT cm_sched_ins_offset_tab_type :=
cm_sched_insert_offset_t;
TYPE select_rec_type IS RECORD
(
customer_trx_line_id BINARY_INTEGER,
prev_cust_trx_line_id BINARY_INTEGER,
allow_overapp_flag ra_cust_trx_types.allow_overapplication_flag%type,
cm_amount NUMBER,
credit_method_for_rules VARCHAR2(1),
last_period_to_credit NUMBER,
currency_code ra_customer_trx.invoice_currency_code%type,
inv_acct_rule_duration NUMBER,
allow_not_open_flag VARCHAR2(1),
partial_period_flag VARCHAR2(1),
cm_gl_date DATE,
invoice_quantity NUMBER,
cm_quantity NUMBER,
invoice_sign NUMBER, -- 3198525 from char(1) to number
cm_sign NUMBER, -- 3198525 from char(1) to number
rule_start_date DATE, -- output only
rule_end_date DATE, -- output only
cm_acct_rule_duration NUMBER, -- output only
inv_unit_price NUMBER, -- 4621029
cm_unit_price NUMBER -- 4621029
);
nonrule_insert_dist_c INTEGER;
nonrule_update_lines_c INTEGER;
nonrule_update_dist_c INTEGER;
nonrule_update_dist2_c INTEGER;
rule_select_cm_lines_c INTEGER;
rule_update_cm_lines_c INTEGER;
rule_insert_dist_c INTEGER;
rule_insert_cma_c INTEGER;
delete_header_dist_c INTEGER;
delete_line_dist_c INTEGER;
delete_header_cma_c INTEGER;
delete_line_cma_c INTEGER;
update_header_lines_c INTEGER;
update_lines_c INTEGER;
close_cursor( nonrule_insert_dist_c );
close_cursor( nonrule_update_lines_c );
close_cursor( nonrule_update_dist_c );
close_cursor( nonrule_update_dist2_c );
close_cursor( rule_select_cm_lines_c );
close_cursor( rule_update_cm_lines_c );
close_cursor( rule_insert_dist_c );
close_cursor( rule_insert_cma_c );
PROCEDURE insert_into_error_table(
p_interface_line_id binary_integer,
p_message_text varchar2,
p_invalid_value varchar2 ) IS
BEGIN
INSERT INTO ra_interface_errors
(interface_line_id,
message_text,
invalid_value,
org_id)
VALUES
(p_interface_line_id,
p_message_text,
p_invalid_value,
arp_standard.sysparm.org_id);
END insert_into_error_table;
PROCEDURE build_update_mode_sql(
p_delete_header_dist_c IN OUT NOCOPY INTEGER,
p_delete_line_dist_c IN OUT NOCOPY INTEGER,
p_delete_header_cma_c IN OUT NOCOPY INTEGER,
p_delete_line_cma_c IN OUT NOCOPY INTEGER,
p_update_header_lines_c IN OUT NOCOPY INTEGER,
p_update_lines_c IN OUT NOCOPY INTEGER ) IS
l_delete_header_dist_sql VARCHAR2(1000);
l_delete_line_dist_sql VARCHAR2(1000);
l_delete_header_cma_sql VARCHAR2(1000);
l_delete_line_cma_sql VARCHAR2(1000);
l_update_header_lines_sql VARCHAR2(1000);
l_update_lines_sql VARCHAR2(1000);
print_fcn_label( 'arp_credit_memo_module.build_update_mode_sql()+' );
l_delete_header_dist_sql :=
'DELETE from ra_cust_trx_line_gl_dist
WHERE customer_trx_id = :customer_trx_id
and account_class <> ''REC''
and account_set_flag = ''N'' ';
l_delete_header_dist_sql := l_delete_header_dist_sql ||
' RETURNING cust_trx_line_gl_dist_id INTO :gl_dist_key_value ';
debug(l_delete_header_dist_sql);
debug(' len(delete_header_dist_sql) = '||
to_char(lengthb(l_delete_header_dist_sql)));
l_delete_line_dist_sql :=
'DELETE from ra_cust_trx_line_gl_dist
WHERE customer_trx_line_id = :customer_trx_line_id
AND account_set_flag = ''N'' ';
l_delete_line_dist_sql := l_delete_line_dist_sql ||
' RETURNING cust_trx_line_gl_dist_id INTO :gl_dist_key_value ';
debug(l_delete_line_dist_sql);
debug(' len(delete_line_dist_sql) = '||
to_char(lengthb(l_delete_line_dist_sql)));
l_delete_header_cma_sql :=
'DELETE from ar_credit_memo_amounts
WHERE customer_trx_line_id in
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines
WHERE line_type = ''LINE''
and customer_trx_id = :customer_trx_id)';
debug(l_delete_header_cma_sql);
debug(' len(delete_header_cma_sql) = '||
to_char(lengthb(l_delete_header_cma_sql)));
l_delete_line_cma_sql :=
'DELETE from ar_credit_memo_amounts
WHERE customer_trx_line_id = :customer_trx_line_id';
debug(l_delete_line_cma_sql);
debug(' len(delete_line_cma_sql) = '||
to_char(lengthb(l_delete_line_cma_sql)));
l_update_header_lines_sql :=
'UPDATE ra_customer_trx_lines
SET
rule_start_date = null,
rule_end_date = null,
accounting_rule_duration = null
WHERE customer_trx_id = :customer_trx_id
and line_type = ''LINE'' ';
debug(l_update_header_lines_sql);
debug(' len(update_header_lines_sql) = '||
to_char(lengthb(l_update_header_lines_sql)));
l_update_lines_sql :=
'UPDATE ra_customer_trx_lines
SET
rule_start_date = null,
rule_end_date = null,
accounting_rule_duration = null
WHERE customer_trx_line_id = :customer_trx_line_id';
debug(l_update_lines_sql);
debug(' len(update_lines_sql) = '||
to_char(lengthb(l_update_lines_sql)));
debug( ' Parsing update mode stmts', MSG_LEVEL_DEBUG );
p_delete_header_dist_c := dbms_sql.open_cursor;
dbms_sql.parse( p_delete_header_dist_c, l_delete_header_dist_sql,
dbms_sql.v7 );
p_delete_line_dist_c := dbms_sql.open_cursor;
dbms_sql.parse( p_delete_line_dist_c, l_delete_line_dist_sql,
dbms_sql.v7 );
p_delete_header_cma_c := dbms_sql.open_cursor;
dbms_sql.parse( p_delete_header_cma_c, l_delete_header_cma_sql,
dbms_sql.v7 );
p_delete_line_cma_c := dbms_sql.open_cursor;
dbms_sql.parse( p_delete_line_cma_c, l_delete_line_cma_sql,
dbms_sql.v7 );
p_update_header_lines_c := dbms_sql.open_cursor;
dbms_sql.parse( p_update_header_lines_c, l_update_header_lines_sql,
dbms_sql.v7 );
p_update_lines_c := dbms_sql.open_cursor;
dbms_sql.parse( p_update_lines_c, l_update_lines_sql,
dbms_sql.v7 );
debug( 'EXCEPTION: Error parsing update mode stmts' );
print_fcn_label( 'arp_credit_memo_module.build_update_mode_sql()-' );
debug( 'EXCEPTION: arp_credit_memo_module.build_update_mode_sql()' );
END build_update_mode_sql;
to set IDs during insert.
*/
----------------------------------------------------------------------------
PROCEDURE build_nonrule_sql(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_cm_control IN control_rec_type,
p_nonrule_insert_dist_c IN OUT NOCOPY INTEGER,
p_nonrule_update_lines_c IN OUT NOCOPY INTEGER,
p_nonrule_update_dist_c IN OUT NOCOPY INTEGER,
p_nonrule_update_dist2_c IN OUT NOCOPY INTEGER ) IS
l_nonrule_insert_dist_sql VARCHAR2(32767);
l_nonrule_update_lines_sql VARCHAR2(1000);
l_nonrule_update_dist_sql VARCHAR2(1000);
l_nonrule_update_dist2_sql VARCHAR2(2000);
l_nonrule_insert_dist_sql :=
'INSERT into ra_cust_trx_line_gl_dist
(
/* gl_dist_id used to be here - now populated by BRI trigger */
customer_trx_id, /* credit memo customer_trx_id */
customer_trx_line_id, /* credit memo customer_trx_line_id */
cust_trx_line_salesrep_id, /* id for cm srep line credited */
request_id,
set_of_books_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
account_class, /* account class for the invoice */
/* assignment being credited */
account_set_flag,
percent,
amount ,
acctd_amount,
gl_date,
code_combination_id,
posting_control_id,
collected_tax_ccid,
ussgl_transaction_code, /*Bug 2246098*/
revenue_adjustment_id, /* Bug 2543675 - RAM id copied to CM dist */
rec_offset_flag, /* Bug 2560036 - non-collectible trans */
org_id
) ';
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
' /* Non Receivable account case */
SELECT
/* Bug 4029814 - removed gl_dist mock logic because of unique constraints */
ctl.customer_trx_id,
ctl.customer_trx_line_id,
ctls.cust_trx_line_salesrep_id,
ct.request_id,
ct.set_of_books_id,
sysdate,
ct.last_updated_by,
sysdate,
ct.created_by,
ct.last_update_login,
ct.program_application_id, /* program_appl_id */
ct.program_id, /* program_id */
sysdate, /* program_update_date */
prev_ctlgd.account_class,
''N'', /* account set for rules flag */
decode(ctl.extended_amount,
0, prev_ctlgd.percent,
round(((decode(foreign_fc.minimum_accountable_unit,
null, round( ' || CRLF ||
l_amount_fragment || CRLF ||
' , precision),
round( ' || CRLF ||
l_amount_fragment || CRLF ||
' / foreign_fc.minimum_accountable_unit) *
foreign_fc.minimum_accountable_unit
) /
decode(ctl.extended_amount, 0, 1, ctl.extended_amount)
) * decode(ctl.extended_amount, 0, 0, 1)
) * 100, 4)
), /* percent */
decode(foreign_fc.minimum_accountable_unit,
null, round( ' || CRLF ||
l_amount_fragment || CRLF ||
' , precision),
round( ' || CRLF ||
l_amount_fragment || CRLF ||
' / foreign_fc.minimum_accountable_unit) *
foreign_fc.minimum_accountable_unit
), /* amount */';
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
' round(decode(foreign_fc.minimum_accountable_unit,
null, round(' || CRLF ||
l_amount_fragment || CRLF ||
' , precision),
round( ' || CRLF ||
l_amount_fragment || CRLF ||
' / foreign_fc.minimum_accountable_unit) *
foreign_fc.minimum_accountable_unit
) * nvl(ct.exchange_rate, 1),
' || p_system_info.base_precision || CRLF ||
' ), /* acctd_amount */';
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
' round(decode(foreign_fc.minimum_accountable_unit,
null, round( ' || CRLF ||
l_amount_fragment || CRLF ||
' , precision),
round( ' || CRLF ||
l_amount_fragment || CRLF ||
' / foreign_fc.minimum_accountable_unit) *
foreign_fc.minimum_accountable_unit
) * nvl(ct.exchange_rate, 1) / ' ||
fnd_number.number_to_canonical(system_info.base_min_acc_unit) || CRLF ||
' ) * ' || fnd_number.number_to_canonical(system_info.base_min_acc_unit) || ', /* acctd_amount */' ;
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
' rec_ctlgd.gl_date,
prev_ctlgd.code_combination_id,
-3,
prev_ctlgd.collected_tax_ccid,
ct.default_ussgl_transaction_code, /*Bug 2246098*/
prev_ctlgd.revenue_adjustment_id, /* Bug 2543675 */
prev_ctlgd.rec_offset_flag, /* Bug 2560036 */
ct.org_id
FROM
fnd_currencies foreign_fc,
ar_lookups al_rules,
ra_customer_trx ct,
ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist ctlgd,
ra_cust_trx_line_gl_dist rec_ctlgd, /* cm rec dist */
ra_cust_trx_line_salesreps ctls,
ra_customer_trx prev_ct,
ra_customer_trx_lines prev_ctl,
ra_cust_trx_line_gl_dist prev_ctlgd,
ra_cust_trx_line_gl_dist prev_ctlgd2 /* inv rec dist */
WHERE ct.customer_trx_id = ctl.customer_trx_id
and ctl.customer_trx_line_id = ctlgd.customer_trx_line_id(+)
/* only look at invoices without an invoicing rule */
and al_rules.lookup_code = ''N''
/* join to the credit memo receivable account dist */
and ct.customer_trx_id = rec_ctlgd.customer_trx_id(+)
and rec_ctlgd.account_class(+) = ''REC''
and rec_ctlgd.latest_rec_flag(+) = ''Y''
/* get currency information */
and ct.invoice_currency_code = foreign_fc.currency_code
/* join to the invoice */
and ctl.previous_customer_trx_line_id
= prev_ctl.customer_trx_line_id
and prev_ctl.customer_trx_id = prev_ctlgd2.customer_trx_id
and prev_ctl.customer_trx_line_id
= prev_ctlgd.customer_trx_line_id
and prev_ctl.customer_trx_id = prev_ct.customer_trx_id
/* join for cust_trx_line_salesrep_id */';
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
'and :cm_customer_trx_id_3 = ctls.customer_trx_id(+)';
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
'and :request_id_3 = ctls.request_id(+)';
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
'and prev_ctlgd.cust_trx_line_salesrep_id
= ctls.prev_cust_trx_line_salesrep_id(+)
/* do not duplicate existing records */
and ctlgd.account_set_flag(+) = ''N''
and ctlgd.customer_trx_id is null
and ctl.previous_customer_trx_line_id is not null
and al_rules.lookup_type = ''YES/NO''
/* Use the presence of an invoicing rule to determine if the invoice has
accounting rules, not the presence of an UNEARN/UNBILL distribution */
and al_rules.lookup_code = DECODE(prev_ct.invoicing_rule_id,NULL,''N'',''Y'')
/* Do not backout account sets for rules records.
However, do use the account set record if this
is a header frt only CM against an invoice with rules. */
and prev_ctlgd.account_set_flag =
decode(prev_ct.invoicing_rule_id, '''', ''N'', decode(al_rules.lookup_code,''N'',''N'',''Y''))
/* insure that prev_ctlgd2 is the invoice rec record */
and prev_ctlgd2.customer_trx_line_id+0 is null
and prev_ctlgd2.account_class = ''REC''
and prev_ctlgd2.account_set_flag = al_rules.lookup_code
/* only reverse records in the invoice header gl date */
and (prev_ctl.accounting_rule_id is null
OR
nvl(prev_ctlgd.gl_date,
nvl(prev_ctlgd2.gl_date,
to_date(''2415386'', ''J'')) ) =
nvl(prev_ctlgd2.gl_date, to_date(''2415386'',
''J'')) )
/* Accept all distributions for tax, freight and rec AND
non revenue distributions with the same sign as the
line. This includes invoices that do not use rules and
unbilled or unearned account in the invoice GL date
from which revenue is reclassed. */
and (
prev_ctl.line_type <> ''LINE''
OR
(prev_ctl.line_type = ''LINE'' AND
prev_ctlgd.account_class = ''SUSPENSE'')
OR
( prev_ctlgd.account_class NOT IN (''REV'',''UNEARN'') AND
sign(prev_ctlgd.amount) =
sign(prev_ctl.extended_amount))
OR
( prev_ctl.accounting_rule_id is null OR
al_rules.lookup_code = ''N'')
)
and decode(prev_ctlgd.account_class,
''SUSPENSE'', ctl.revenue_amount - ctl.extended_amount,
1) <> 0 ' || CRLF || l_where_pred;
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
'and (ar_revenue_management_pvt.line_collectible(prev_ctl.customer_trx_id,
prev_ctl.customer_trx_line_id)
IN (1,2) or
prev_ctlgd.revenue_adjustment_id is null)';
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
'UNION
/* Receivable account case */
SELECT
/* Bug 4029814 - removed gl_dist mock logic because of unique constraints */
ctl.customer_trx_id,
to_number(NULL), /* customer_trx_line_id */
to_number(NULL), /* cust_trx_line_salesrep_id */
max(ctl.request_id),
max(ctl.set_of_books_id),
max(ctl.last_update_date),
max(ctl.last_updated_by),
max(ctl.creation_date),
max(ctl.created_by),
max(ctl.last_update_login),
max(ctl.program_application_id), /* program_appl_id */
max(ctl.program_id), /* program_id */
sysdate, /* program_update_date */
''REC'', /* account class */
''N'', /* account_set_flag */
100, /* percent */
sum(ctl.extended_amount), /* amount */
sum( ' ;
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
' round(ctl.extended_amount * nvl(ct.exchange_rate, 1), ' ||
p_system_info.base_precision || ')';
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
' round(ctl.extended_amount * nvl(ct.exchange_rate, 1) / ' ||
fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ') * ' ||
fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) ;
l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
' ), /* accounted amount */
cm_rec.gl_date,
inv_rec.code_combination_id,
-3,
inv_rec.collected_tax_ccid,
ct.default_ussgl_transaction_code, /*Bug 2246098*/
inv_rec.revenue_adjustment_id, /* Bug 2543675 */
null, /* Bug 2560036 */
ct.org_id
FROM
ra_customer_trx ct,
ar_lookups al_rules,
ra_cust_trx_line_gl_dist inv_rec,
ra_cust_trx_line_gl_dist cm_rec,
ra_cust_trx_line_gl_dist lgd,
ra_customer_trx_lines ctl
WHERE ct.customer_trx_id = ctl.customer_trx_id
and ctl.customer_trx_id = lgd.customer_trx_id(+)
and ''REC'' = lgd.account_class(+)
and ''N'' = lgd.account_set_flag(+)
and ctl.customer_trx_id = cm_rec.customer_trx_id
and ''REC'' = cm_rec.account_class
and ''Y'' = cm_rec.account_set_flag
and lgd.customer_trx_id is null
/* only create receivable records if the real invoice
receivable record exists. */
and ct.previous_customer_trx_id = inv_rec.customer_trx_id
and ''REC'' = inv_rec.account_class
and ''N'' = inv_rec.account_set_flag
and al_rules.lookup_type = ''YES/NO''
/* Use the presence of an invoicing rule to determine if the invoice has
accounting rules, not the presence of an UNEARN or UNBILL distribution */
and al_rules.lookup_code = DECODE(ct.invoicing_rule_id,NULL,''N'',''Y'')
and al_rules.lookup_code = ''N'' ' || CRLF ||
l_rec_where_pred || CRLF ||
'GROUP BY
ctl.customer_trx_id,
inv_rec.cust_trx_line_gl_dist_id,
inv_rec.gl_date,
cm_rec.gl_date,
inv_rec.code_combination_id,
inv_rec.collected_tax_ccid,
ct.default_ussgl_transaction_code, /*Bug 2246098*/
inv_rec.revenue_adjustment_id, /* Bug 2543675 */
ct.org_id'; /* 4156400 */
debug(l_nonrule_insert_dist_sql);
debug(' len(nonrule_insert_dist_sql) = '||
to_char(lengthb(l_nonrule_insert_dist_sql)));
l_nonrule_update_lines_sql :=
'UPDATE ra_customer_trx_lines ctl
SET autorule_complete_flag = '''',
autorule_duration_processed = accounting_rule_duration
WHERE ctl.accounting_rule_id is not null' || CRLF ||
l_where_pred || CRLF ||
'and ctl.autorule_complete_flag||'''' = ''N''
and exists
(
SELECT ''x''
FROM ra_cust_trx_line_gl_dist d
WHERE d.customer_trx_id = ctl.customer_trx_id
and d.account_class = ''REC''
and d.account_set_flag = ''N''
)';
debug(l_nonrule_update_lines_sql);
debug(' len(nonrule_update_lines_sql) = '||
to_char(lengthb(l_nonrule_update_lines_sql)));
l_nonrule_update_dist_sql :=
'UPDATE ra_cust_trx_line_gl_dist d
SET latest_rec_flag = ''N''
WHERE account_class = ''REC''
and d.latest_rec_flag = ''Y''
and d.account_set_flag = ''Y''
and d.customer_trx_id in
(
SELECT ctl.customer_trx_id
FROM ra_customer_trx_lines ctl
WHERE 1 = 1' || CRLF ||
l_where_pred || CRLF ||
')
and exists
(
SELECT 1
FROM ra_cust_trx_line_gl_dist d2
WHERE d2.account_class = ''REC''
and d2.latest_rec_flag = ''Y''
and d2.customer_trx_id = d.customer_trx_id
and d.rowid <> d2.rowid
)';
debug(l_nonrule_update_dist_sql);
debug(' len(nonrule_update_dist_sql) = '||
to_char(lengthb(l_nonrule_update_dist_sql)));
l_nonrule_update_dist2_sql :=
'UPDATE ra_cust_trx_line_gl_dist lgd
set
(
amount,
acctd_amount
) =
(
SELECT
(ctl.extended_amount - sum(lgd2.amount) ) + lgd.amount,' || CRLF ||
' (' || l_amount_fragment || CRLF ||
' - sum(lgd2.acctd_amount)) + lgd.acctd_amount
FROM
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist lgd2
WHERE ctl.customer_trx_line_id = lgd2.customer_trx_line_id
and ctl.customer_trx_line_id = lgd.customer_trx_line_id
and ct.customer_trx_id = ctl.customer_trx_id
GROUP BY
ctl.customer_trx_line_id,
ctl.line_number,
ctl.extended_amount,
ct.exchange_rate
)
WHERE lgd.cust_trx_line_gl_dist_id in
(
SELECT min(cust_trx_line_gl_dist_id)
from
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist lgd3
where ctl.customer_trx_line_id = lgd3.customer_trx_line_id';
l_nonrule_update_dist2_sql := l_nonrule_update_dist2_sql || CRLF ||
' and ctl.request_id = :request_id';
l_nonrule_update_dist2_sql := l_nonrule_update_dist2_sql || CRLF ||
' and ctl.customer_trx_id = :cm_customer_trx_id';
l_nonrule_update_dist2_sql := l_nonrule_update_dist2_sql || CRLF ||
' and ct.customer_trx_id = ctl.customer_trx_id
GROUP BY
ctl.customer_trx_line_id,
ctl.line_number,
ctl.extended_amount
HAVING
(
sum(lgd3.amount) <> ctl.extended_amount ) or
(sum(lgd3.acctd_amount) <>
sum( ' || CRLF ||
l_amount_fragment || CRLF ||
' )
)
)' ;
debug(l_nonrule_update_dist2_sql);
debug(' len(nonrule_update_dist2_sql) = '||
to_char(lengthb(l_nonrule_update_dist2_sql)));
p_nonrule_insert_dist_c := dbms_sql.open_cursor;
dbms_sql.parse( p_nonrule_insert_dist_c, l_nonrule_insert_dist_sql,
dbms_sql.v7 );
p_nonrule_update_lines_c := dbms_sql.open_cursor;
dbms_sql.parse( p_nonrule_update_lines_c, l_nonrule_update_lines_sql,
dbms_sql.v7 );
p_nonrule_update_dist_c := dbms_sql.open_cursor;
dbms_sql.parse( p_nonrule_update_dist_c, l_nonrule_update_dist_sql,
dbms_sql.v7 );
p_nonrule_update_dist2_c := dbms_sql.open_cursor;
dbms_sql.parse( p_nonrule_update_dist2_c, l_nonrule_update_dist2_sql,
dbms_sql.v7 );
p_rule_select_cm_lines_c IN OUT NOCOPY INTEGER,
p_rule_update_cm_lines_c IN OUT NOCOPY INTEGER,
p_rule_insert_dist_c IN OUT NOCOPY INTEGER,
p_rule_insert_cma_c IN OUT NOCOPY INTEGER ) IS
l_rule_select_cm_lines_sql VARCHAR2(5000);
l_rule_update_cm_lines_sql VARCHAR2(2000);
l_rule_insert_dist_sql VARCHAR2(32767);
l_rule_insert_cma_sql VARCHAR2(2000);
l_rule_select_cm_lines_sql :=
'SELECT
cm.customer_trx_line_id,
cm.previous_customer_trx_line_id,
inv_type.allow_overapplication_flag,
cm.revenue_amount,
decode(cmt.credit_method_for_rules,
''LIFO'', ''L'',
''PRORATE'', ''P'',
''UNIT'', ''U''),
decode(cmt.credit_method_for_rules,
''UNIT'', nvl(cm.last_period_to_credit,
nvl(inv.accounting_rule_duration,
rule.occurrences)),
0),
cmt.invoice_currency_code,
/* If the invoice is a Release 9 immediate invoice
with rules, set the duration to -1 so that it can
be processed specially. */
decode(inv_trx.created_from || inv_rec.gl_date ||
nvl(inv.accounting_rule_duration,
rule.occurrences),
''RAXTRX_REL9'' || inv_rev.gl_date || ''1'', ''-1'',
nvl(inv.accounting_rule_duration,
rule.occurrences)),
''Y'',
cm_rec.gl_date,
decode(sign( nvl(inv.quantity_invoiced, 0)),
sign(inv.extended_amount), nvl(inv.quantity_invoiced, 0),
nvl(inv.quantity_invoiced, 0) * -1 ),
decode(sign( nvl(cm.quantity_credited, 0)),
sign(cm.extended_amount), nvl(cm.quantity_credited, 0),
nvl(cm.quantity_credited, 0) * -1 ),
to_char(sign(inv.revenue_amount)),
to_char(sign(cm.revenue_amount)),
CASE
WHEN rule.type IN (''A'', ''ACC_DUR'') THEN
''N''
ELSE
''Y''
END partial_period_flag,
inv.unit_selling_price,
cm.unit_selling_price
FROM
ra_rules rule,
ra_cust_trx_line_gl_dist cm_rec,
ra_cust_trx_line_gl_dist inv_rec,
ra_cust_trx_line_gl_dist inv_rev,
ra_cust_trx_types inv_type,
ra_customer_trx inv_trx,
ra_customer_trx_lines inv,
ra_customer_trx cmt,
ra_customer_trx_lines cm
WHERE cm.previous_customer_trx_line_id = inv.customer_trx_line_id
and inv.customer_trx_id = inv_trx.customer_trx_id
and inv_trx.cust_trx_type_id = inv_type.cust_trx_type_id
and cm.customer_trx_id = cmt.customer_trx_id
and inv.accounting_rule_id = rule.rule_id
and cm.customer_trx_id = cm_rec.customer_trx_id
and inv_trx.customer_trx_id = inv_rec.customer_trx_id
and inv_rec.account_class = ''REC''
and inv_rec.latest_rec_flag = ''Y''
and cm_rec.account_class = ''REC''
and cm_rec.account_set_flag = ''Y''
and cm.rule_start_date is null
and cm.line_type = ''LINE''
and inv_rev.cust_trx_line_gl_dist_id =
(
SELECT nvl(min(inv_dist2.cust_trx_line_gl_dist_id),
inv_rec.cust_trx_line_gl_dist_id)
FROM ra_cust_trx_line_gl_dist inv_dist2
WHERE inv.customer_trx_line_id = inv_dist2.customer_trx_line_id
and inv_dist2.account_set_flag = ''N''
and inv_dist2.account_class IN (''REV'',''UNEARN'')
)
/* Do not create distributions for immediate lines if they
already exist. */
and not exists
(
SELECT ''dists exist''
FROM ra_cust_trx_line_gl_dist subdist
WHERE subdist.customer_trx_line_id = cm.customer_trx_line_id
and nvl(inv.accounting_rule_duration, 1) = 1
and subdist.account_class IN (''REV'',''UNEARN'')
and account_set_flag = ''N''
) ' || CRLF ||
l_where_pred || CRLF ||
'ORDER BY
cm.previous_customer_trx_line_id,
cmt.trx_date,
cm_rec.gl_date,
cm.customer_trx_line_id';
debug(l_rule_select_cm_lines_sql);
debug(' len(rule_select_cm_lines_sql) = '||
to_char(lengthb(l_rule_select_cm_lines_sql)));
l_rule_update_cm_lines_sql :=
'UPDATE ra_customer_trx_lines l
SET
l.rule_start_date = :rule_start_date,
l.rule_end_date = :rule_end_date,
l.accounting_rule_duration = :cm_acct_rule_duration,
l.last_period_to_credit = decode(:credit_method,
''U'', :last_period_to_credit,
l.last_period_to_credit)
WHERE l.customer_trx_line_id = :customer_trx_line_id
and :rule_start_date is not null
and :cm_acct_rule_duration is not null ';
debug(l_rule_update_cm_lines_sql);
debug(' len(rule_update_cm_lines_sql) = '||
to_char(lengthb(l_rule_update_cm_lines_sql)));
l_rule_insert_dist_sql :=
'INSERT INTO ra_cust_trx_line_gl_dist
(
cust_trx_line_gl_dist_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
posting_control_id,
customer_trx_id,
customer_trx_line_id,
cust_trx_line_salesrep_id,
gl_date,
original_gl_date,
set_of_books_id,
code_combination_id,
concatenated_segments,
account_class,
account_set_flag,
amount,
acctd_amount,
percent,
ussgl_transaction_code,
ussgl_transaction_code_context,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
collected_tax_ccid,
collected_tax_concat_seg,
revenue_adjustment_id, /* Bug 2543675 */
org_id
)';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
'(SELECT /*+ ORDERED
INDEX (ara.ragt ar_revenue_assignments_gt_n1) */
ra_cust_trx_line_gl_dist_s.nextval, /* cust_trx_line_dist_id */
ctl.created_by, /* created_by */
sysdate, /* creation_date */
ctl.last_updated_by, /* last_updated_by */
sysdate, /* last_update_date */
ctl.last_update_login, /* last_update_login */
ctl.program_application_id, /* program_application_id */
ctl.program_id, /* program_id */
sysdate, /* program_update_date */
ctl.request_id, /* request _id */
-3,
ctl.customer_trx_id, /* customer_trx_id */
:customer_trx_line_id, /* customer_trx_line_id */
ctls.cust_trx_line_salesrep_id, /* cust_trx_line_srep_id */
/* Bug 2142941 - use lgd.gl_date and lgd.original_gl_date instead of bind
variables :gl_date and :original_gl_date */
/* Bug 2194742 - Used bind variable for gl_date */
/* 6129294 - Honor inv gl_date for RAM dists when possible */
DECODE(lgd.revenue_adjustment_id, NULL, :gl_date,
DECODE(:gl_date_2, lgd.gl_date, :gl_date_3,
NVL(arp_credit_memo_module.get_valid_date(
lgd.gl_date,
ct.invoicing_rule_id,
lgd.set_of_books_id),
:gl_date_4))), /* gl_date */
lgd.original_gl_date, /* original_gl_date */
lgd.set_of_books_id, /* set_of_books_id */
lgd.code_combination_id, /* code_combination_id */
lgd.concatenated_segments, /* concatenated_segments */
lgd.account_class, /* account class */
''N'', /* account_set_flag */
decode( fc.minimum_accountable_unit,
NULL, round( (:amount * ' || l_temp || ' ),
fc.precision),
round( (:amount_1 * ' || l_temp || ' ) /
fc.minimum_accountable_unit ) *
fc.minimum_accountable_unit
) * decode(lgd.account_class,
''REV'', 1,
-1), /* amount */';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
'round( decode( fc.minimum_accountable_unit,
null, round((:amount_2 * ' || l_temp || ' ),
fc.precision),
round((:amount_3 * ' || l_temp || ' ) /
fc.minimum_accountable_unit)
* fc.minimum_accountable_unit )
* nvl(ct.exchange_rate, 1) , ' || p_system_info.base_precision ||
' )';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
'(round((decode( fc.minimum_accountable_unit,
null, round((:amount_2 * ' || l_temp || ' ),
fc.precision),
round((:amount_3 * ' || l_temp || ' ) /
fc.minimum_accountable_unit)
* fc.minimum_accountable_unit )
* nvl(ct.exchange_rate, 1)
) / ' || fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ' ) *' || CRLF ||
fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ')';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
' * decode(lgd.account_class,
''REV'', 1,
-1), /* acctd_amount */
decode(lgd.account_class,
''UNBILL'', -1 * round( ( (:amount_4 * ' || l_temp || ' )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4),
''UNEARN'', -1 * round( ( (:amount_5 * ' || l_temp || ' )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4),
round( ( (:amount_6 * ' || l_temp || ' )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4)
), /* percent */
ct.default_ussgl_transaction_code, /* ussgl_trx_code */
ct.default_ussgl_trx_code_context, /* ussgl_trx_code_context */
NULL, /* comments */
NULL, /* attribute_category */
NULL, /* attribute1 */
NULL, /* attribute2 */
NULL, /* attribute3 */
NULL, /* attribute4 */
NULL, /* attribute5 */
NULL, /* attribute6 */
NULL, /* attribute7 */
NULL, /* attribute8 */
NULL, /* attribute9 */
NULL, /* attribute10 */
NULL, /* attribute11 */
NULL, /* attribute12 */
NULL, /* attribute13 */
NULL, /* attribute14 */
NULL, /* attribute15 */
lgd.collected_tax_ccid, /* collected tax */
lgd.collected_tax_concat_seg, /* collected tax seg */
lgd.revenue_adjustment_id, /* revenue_adjustment_id */ /*Bug 2543675*/
ct.org_id
FROM
ra_customer_trx_lines ctl,
ra_customer_trx ct,
fnd_currencies fc,
ar_revenue_assignments_v ara /* Bug 2347001 */,
ra_cust_trx_line_gl_dist inv_rec,
ra_cust_trx_line_gl_dist lgd,
ra_cust_trx_line_salesreps ctls
WHERE lgd.customer_trx_line_id = ctl.previous_customer_trx_line_id
and ctl.previous_customer_trx_line_id = ara.customer_trx_line_id
and ara.session_id in (:session_id, -99) /**Bug 2347001 */
and ara.gl_date = nvl(lgd.original_gl_date, lgd.gl_date)
and ara.account_class = lgd.account_class
and ara.period_set_name = :period_set_name /* 4254587 */
and ct.customer_trx_id = ctl.customer_trx_id
and inv_rec.customer_trx_id = ctl.previous_customer_trx_id
and inv_rec.account_class = ''REC''
and inv_rec.latest_rec_flag = ''Y''
/* Bug 2899714 */
and lgd.cust_trx_line_salesrep_id = ctls.prev_cust_trx_line_salesrep_id(+)
/*7147479*/
and ctls.customer_trx_line_id(+) = :customer_trx_line_id_1
and ctl.customer_trx_line_id = :customer_trx_line_id_2
and fc.currency_code = ct.invoice_currency_code
and lgd.account_set_flag = ''N''
and ( (lgd.account_class in (''REV'', ''UNEARN'', ''UNBILL'') and
:insert_offset_1 = ''Y'' ) or
(lgd.account_class = ''REV'' and :insert_offset_2 = ''N'' ) )
/* inv_dist_exists is set to "F" when crediting a release 9
immediate invoice. In this case, the cm gl_date may not correspond
to any inv gl_date, so the date check cannot be done. */
/* Bug 2142941 - include join onto lgd.original_gl_date */
and (
( trunc(ara.gl_date) = lgd.original_gl_date AND
lgd.original_gl_date = :original_gl_date_1)
OR
:check_gl_date_1 = ''N''
)
/* Bug 2535023 - Revamped fixes from bugs 1936152 and 2354805
so that the insert now relies upon rec_offset_flag instead
of that and-not stuff. Forced routine to only
copy conventional distributions. */
/* Bug 2543675 - include RAM distributions */
and lgd.rec_offset_flag is null';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
'and (ar_revenue_management_pvt.line_collectible(ctl.previous_customer_trx_id,
ctl.previous_customer_trx_line_id)
IN (1,2) or
lgd.revenue_adjustment_id is null)';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF || ')';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
' (SELECT
ra_cust_trx_line_gl_dist_s.nextval, /* cust_trx_line_dist_id */
ct.created_by, /* created_by */
sysdate, /* creation_date */
ct.last_updated_by, /* last_updated_by */
sysdate, /* last_update_date */
ct.last_update_login, /* last_update_login */
ct.program_application_id, /* program_application_id */
ct.program_id, /* program_id */
sysdate, /* program_update_date */
ct.request_id, /* request _id */
-3,
ct.customer_trx_id, /* customer_trx_id */
:customer_trx_line_id, /* customer_trx_line_id */
lgd.cust_trx_line_salesrep_id, /* cust_trx_line_srep_id */
:gl_date, /* gl_date */
:original_gl_date, /* original_gl_date */
lgd.set_of_books_id, /* set_of_books_id */
lgd.code_combination_id, /* code_combination_id */
lgd.concatenated_segments, /* concatenated_segments */
lgd.account_class, /* account class */
''N'', /* account_set_flag */
decode( fc.minimum_accountable_unit,
NULL, round( (:amount * (lgd.percent / 100) ),
fc.precision),
round( (:amount_1 * (lgd.percent / 100) ) /
fc.minimum_accountable_unit ) *
fc.minimum_accountable_unit
) * decode(lgd.account_class,
''REV'', 1,
-1), /* amount */ ';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
'round( decode( fc.minimum_accountable_unit,
null, round((:amount_2 * (lgd.percent / 100) ),
fc.precision),
round((:amount_3 * (lgd.percent / 100) ) /
fc.minimum_accountable_unit)
* fc.minimum_accountable_unit )
* nvl(ct.exchange_rate, 1), ' || p_system_info.base_precision ||
' )';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
'(round((decode( fc.minimum_accountable_unit,
null, round((:amount_2 * (lgd.percent / 100) ),
fc.precision),
round((:amount_3 * (lgd.percent / 100) ) /
fc.minimum_accountable_unit)
* fc.minimum_accountable_unit )
* nvl(ct.exchange_rate, 1)
) / ' || fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ' ) *' || CRLF ||
fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ')';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
' * decode(lgd.account_class,
''REV'', 1,
-1), /* acctd_amount */
decode(lgd.account_class,
''UNBILL'', -1 * round( ( (:amount_4 * (lgd.percent / 100) )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4),
''UNEARN'', -1 * round( ( (:amount_5 * (lgd.percent / 100) )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4),
round( ( (:amount_6 * (lgd.percent / 100) )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4)
), /* percent */
ct.default_ussgl_transaction_code, /* ussgl_trx_code */
ct.default_ussgl_trx_code_context, /* ussgl_trx_code_context */
lgd.comments, /* comments */
lgd.attribute_category, /* attribute_category */
lgd.attribute1, /* attribute1 */
lgd.attribute2, /* attribute2 */
lgd.attribute3, /* attribute3 */
lgd.attribute4, /* attribute4 */
lgd.attribute5, /* attribute5 */
lgd.attribute6, /* attribute6 */
lgd.attribute7, /* attribute7 */
lgd.attribute8, /* attribute8 */
lgd.attribute9, /* attribute9 */
lgd.attribute10, /* attribute10 */
lgd.attribute11, /* attribute11 */
lgd.attribute12, /* attribute12 */
lgd.attribute13, /* attribute13 */
lgd.attribute14, /* attribute14 */
lgd.attribute15, /* attribute1 */
lgd.collected_tax_ccid, /* collected tax */
lgd.collected_tax_concat_seg, /* collected tax seg */
lgd.revenue_adjustment_id, /* revenue_adjustment_id */ /*Bug 2543675*/
ct.org_id
FROM
ra_cust_trx_line_gl_dist lgd,
fnd_currencies fc,
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_customer_trx_lines ictl,
ra_rules ir
WHERE lgd.customer_trx_id = ct.customer_trx_id
and lgd.customer_trx_line_id = :customer_trx_line_id_1
and lgd.customer_trx_line_id = ctl.customer_trx_line_id
and fc.currency_code = ct.invoice_currency_code
and account_set_flag = ''Y''
and ( (lgd.account_class in (''REV'', ''UNEARN'', ''UNBILL'') and
:insert_offset_1 = ''Y'' ) or
(lgd.account_class = ''REV'' and :insert_offset_2 = ''N'' ) )
/* Bug 2559653 - generate nothing for deferred rules
unless it is an ARREARS transaction */
and ctl.previous_customer_trx_line_id = ictl.customer_trx_line_id
and ictl.accounting_rule_id = ir.rule_id
and (nvl(ir.deferred_revenue_flag, ''N'') = ''N'' or
ct.invoicing_rule_id = -3)
/* no-effect pred, for binding purposes */
and :check_gl_date_1 = :check_gl_date_2
and :gl_date_1 is not null ';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
'and ar_revenue_management_pvt.line_collectible(ctl.previous_customer_trx_id,
ctl.previous_customer_trx_line_id) IN (1,2) ';
l_rule_insert_dist_sql := l_rule_insert_dist_sql || ')';
debug( l_rule_insert_dist_sql);
debug(' len(rule_insert_dist_sql) = '||
to_char(lengthb(l_rule_insert_dist_sql)));
l_rule_insert_cma_sql :=
'INSERT INTO ar_credit_memo_amounts
(
credit_memo_amount_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
customer_trx_line_id,
gl_date,
amount,
program_application_id,
program_id,
program_update_date,
request_id
)
SELECT
ar_credit_memo_amounts_s.nextval, /* credit_memo_amount_id */' || CRLF ||
p_profile_info.user_id || ', /* last_updated_by */
sysdate, /* last_update_date */' || CRLF ||
p_profile_info.conc_login_id || ', /* last_update_login */' || CRLF ||
p_profile_info.user_id || ', /* created_by */
sysdate, /* creation_date */
:customer_trx_line_id, /* customer_trx_line_id */
:gl_date, /* gl_date */
:amount, /* amount */' || CRLF ||
profile_info.application_id || ', /* program_application_id */'
|| CRLF || profile_info.conc_program_id || ', /* program_id */
sysdate, /* program_update_date */';
l_rule_insert_cma_sql := l_rule_insert_cma_sql || CRLF ||
'0' || ' /* request_id */';
l_rule_insert_cma_sql := l_rule_insert_cma_sql || CRLF ||
':request_id /* request_id */';
l_rule_insert_cma_sql := l_rule_insert_cma_sql || CRLF ||
'FROM dual ';
debug(l_rule_insert_cma_sql);
debug(' len(rule_insert_cma_sql) = '||
to_char(lengthb(l_rule_insert_cma_sql)));
p_rule_select_cm_lines_c := dbms_sql.open_cursor;
dbms_sql.parse( p_rule_select_cm_lines_c, l_rule_select_cm_lines_sql,
dbms_sql.v7 );
debug(' parsed p_rule_select_cm_lines_c');
p_rule_update_cm_lines_c := dbms_sql.open_cursor;
dbms_sql.parse( p_rule_update_cm_lines_c, l_rule_update_cm_lines_sql,
dbms_sql.v7 );
debug(' parsed p_rule_update_cm_lines_c');
p_rule_insert_dist_c := dbms_sql.open_cursor;
dbms_sql.parse( p_rule_insert_dist_c, l_rule_insert_dist_sql,
dbms_sql.v7 );
debug(' parsed p_rule_insert_dist_c');
p_rule_insert_cma_c := dbms_sql.open_cursor;
dbms_sql.parse( p_rule_insert_cma_c, l_rule_insert_cma_sql,
dbms_sql.v7 );
debug(' parsed p_rule_insert_cma_c');
'SELECT
distinct
cnr.previous_customer_trx_line_id,
cnr.gl_date,
cnr.amount,
cnr.net_unit_price,
decode(trx.created_from,
''RAXTRX_REL9'', ''Y'',
decode(lgd.customer_trx_id,
NULL, ''N'',
''Y'')
) /* inv dist exists */
FROM ra_cust_trx_line_gl_dist lgd,
ra_customer_trx trx,
ar_cm_net_revenue_form cnr
WHERE cnr.previous_customer_trx_line_id = :start_prev_ctlid
and cnr.previous_customer_trx_line_id = lgd.customer_trx_line_id(+)
and cnr.previous_customer_trx_id = trx.customer_trx_id
and cnr.gl_date = lgd.original_gl_date(+)
and ''N'' = lgd.account_set_flag(+)
and cnr.period_set_name = :period_set_name
ORDER BY
cnr.previous_customer_trx_line_id,
cnr.gl_date';
SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist gld
WHERE gld.customer_trx_id = p_trx_id
AND gld.customer_trx_line_id =
nvl(p_trx_line_id, gld.customer_trx_line_id);
nonrule_insert_dist_c,
nonrule_update_lines_c,
nonrule_update_dist_c,
nonrule_update_dist2_c );
/* INSERT */
dbms_sql.bind_variable( nonrule_insert_dist_c,
'cm_customer_trx_id_1',
p_cm_control.customer_trx_id );
dbms_sql.bind_variable( nonrule_insert_dist_c,
'cm_customer_trx_id_2',
p_cm_control.customer_trx_id );
/* UPDATE LINE */
dbms_sql.bind_variable( nonrule_update_lines_c,
'cm_customer_trx_id_1',
p_cm_control.customer_trx_id );
/* UPDATE DIST */
dbms_sql.bind_variable( nonrule_update_dist_c,
'cm_customer_trx_id_1',
p_cm_control.customer_trx_id );
dbms_sql.bind_variable( nonrule_insert_dist_c,
'cm_customer_trx_id_3',
p_cm_control.customer_trx_id );
debug( 'EXCEPTION: Error in binding nonrule_insert_dist_c' );
dbms_sql.bind_variable( nonrule_update_dist2_c,
'cm_customer_trx_id',
p_cm_control.customer_trx_id );
debug( 'EXCEPTION: Error in binding nonrule_update_dist2_c' );
/* INSERT */
dbms_sql.bind_variable( nonrule_insert_dist_c,
'request_id_1',
p_cm_control.request_id );
dbms_sql.bind_variable( nonrule_insert_dist_c,
'request_id_2',
p_cm_control.request_id );
/* UPDATE LINES */
dbms_sql.bind_variable( nonrule_update_lines_c,
'request_id_1',
p_cm_control.request_id );
/* UPDATE DISTS */
dbms_sql.bind_variable( nonrule_update_dist_c,
'request_id_1',
p_cm_control.request_id );
dbms_sql.bind_variable( nonrule_insert_dist_c,
'request_id_3',
p_cm_control.request_id );
debug( 'EXCEPTION: Error in binding nonrule_insert_dist_c' );
dbms_sql.bind_variable( nonrule_update_dist2_c,
'request_id',
p_cm_control.request_id );
debug( 'EXCEPTION: Error in binding nonrule_update_dist2_c' );
/* INSERT */
dbms_sql.bind_variable( nonrule_insert_dist_c,
'cm_cust_trx_line_id_1',
p_cm_control.customer_trx_line_id );
dbms_sql.bind_variable( nonrule_insert_dist_c,
'cm_cust_trx_line_id_2',
p_cm_control.customer_trx_line_id );
dbms_sql.bind_variable( nonrule_insert_dist_c,
'cm_cust_trx_line_id_3',
p_cm_control.customer_trx_line_id );
dbms_sql.bind_variable( nonrule_insert_dist_c,
'cm_cust_trx_line_id_4',
p_cm_control.customer_trx_line_id );
/* UPDATE LINES */
dbms_sql.bind_variable( nonrule_update_lines_c,
'cm_cust_trx_line_id_1',
p_cm_control.customer_trx_line_id );
dbms_sql.bind_variable( nonrule_update_lines_c,
'cm_cust_trx_line_id_2',
p_cm_control.customer_trx_line_id );
/* UPDATE DISTS */
dbms_sql.bind_variable( nonrule_update_dist_c,
'cm_cust_trx_line_id_1',
p_cm_control.customer_trx_line_id );
dbms_sql.bind_variable( nonrule_update_dist_c,
'cm_cust_trx_line_id_2',
p_cm_control.customer_trx_line_id );
debug( 'EXCEPTION: Error in binding nonrule_insert_dist_c' );
debug( ' Inserting distributions', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( nonrule_insert_dist_c );
close_cursor( nonrule_insert_dist_c );
debug( to_char(l_ignore) || ' row(s) inserted', MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing insert dist stmt' );
l_ignore := dbms_sql.execute( nonrule_update_lines_c );
close_cursor( nonrule_update_lines_c );
debug( to_char(l_ignore) || ' row(s) updated', MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing update lines stmt' );
l_ignore := dbms_sql.execute( nonrule_update_dist_c );
close_cursor( nonrule_update_dist_c );
debug( to_char(l_ignore) || ' row(s) updated', MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing update dist stmt' );
l_ignore := dbms_sql.execute( nonrule_update_dist2_c );
close_cursor( nonrule_update_dist2_c );
debug( to_char(l_ignore) || ' row(s) updated', MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing update stmt' );
PROCEDURE define_select_columns(
p_select_c IN INTEGER,
p_select_rec IN OUT NOCOPY select_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_credit_memo_module.define_select_columns()+' );
dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_line_id );
dbms_sql.define_column( p_select_c, 2,
p_select_rec.prev_cust_trx_line_id );
dbms_sql.define_column( p_select_c, 3,
p_select_rec.allow_overapp_flag, 1 );
dbms_sql.define_column( p_select_c, 4, p_select_rec.cm_amount );
dbms_sql.define_column( p_select_c, 5,
p_select_rec.credit_method_for_rules, 1 );
dbms_sql.define_column( p_select_c, 6,
p_select_rec.last_period_to_credit );
dbms_sql.define_column( p_select_c, 7, p_select_rec.currency_code, 15 );
dbms_sql.define_column( p_select_c, 8,
p_select_rec.inv_acct_rule_duration );
dbms_sql.define_column( p_select_c, 9,
p_select_rec.allow_not_open_flag, 1 );
dbms_sql.define_column( p_select_c, 10, p_select_rec.cm_gl_date );
dbms_sql.define_column( p_select_c, 11, p_select_rec.invoice_quantity );
dbms_sql.define_column( p_select_c, 12,
p_select_rec.cm_quantity );
dbms_sql.define_column( p_select_c, 13,
p_select_rec.invoice_sign);
dbms_sql.define_column( p_select_c, 14, p_select_rec.cm_sign);
dbms_sql.define_column( p_select_c, 15, p_select_rec.partial_period_flag,1);
dbms_sql.define_column( p_select_c, 16, p_select_rec.inv_unit_price);
dbms_sql.define_column( p_select_c, 17, p_select_rec.cm_unit_price);
print_fcn_label2( 'arp_credit_memo_module.define_select_columns()-' );
debug('EXCEPTION: arp_credit_memo_module.define_select_columns()');
END define_select_columns;
PROCEDURE get_select_column_values(
p_select_c IN INTEGER,
p_select_rec IN OUT NOCOPY select_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_credit_memo_module.get_select_column_values()+' );
dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_line_id );
dbms_sql.column_value( p_select_c, 2, p_select_rec.prev_cust_trx_line_id );
dbms_sql.column_value( p_select_c, 3,
p_select_rec.allow_overapp_flag );
dbms_sql.column_value( p_select_c, 4, p_select_rec.cm_amount );
dbms_sql.column_value( p_select_c, 5,
p_select_rec.credit_method_for_rules );
dbms_sql.column_value( p_select_c, 6, p_select_rec.last_period_to_credit );
dbms_sql.column_value( p_select_c, 7, p_select_rec.currency_code );
dbms_sql.column_value( p_select_c, 8,
p_select_rec.inv_acct_rule_duration );
dbms_sql.column_value( p_select_c, 9, p_select_rec.allow_not_open_flag );
dbms_sql.column_value( p_select_c, 10, p_select_rec.cm_gl_date );
dbms_sql.column_value( p_select_c, 11, p_select_rec.invoice_quantity );
dbms_sql.column_value( p_select_c, 12,
p_select_rec.cm_quantity );
dbms_sql.column_value( p_select_c, 13,
p_select_rec.invoice_sign );
dbms_sql.column_value( p_select_c, 14, p_select_rec.cm_sign );
dbms_sql.column_value( p_select_c, 15, p_select_rec.partial_period_flag);
dbms_sql.column_value( p_select_c, 16, p_select_rec.inv_unit_price);
dbms_sql.column_value( p_select_c, 17, p_select_rec.cm_unit_price);
print_fcn_label2( 'arp_credit_memo_module.get_select_column_values()-' );
debug('EXCEPTION: arp_credit_memo_module.get_select_column_values()');
END get_select_column_values;
PROCEDURE dump_select_rec( p_select_rec IN select_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_credit_memo_module.dump_select_rec()+' );
debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.customer_trx_line_id ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.prev_cust_trx_line_id ), MSG_LEVEL_DEBUG );
debug( ' allow_overapp_flag=' || p_select_rec.allow_overapp_flag ,
MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.cm_amount ), MSG_LEVEL_DEBUG );
|| p_select_rec.credit_method_for_rules, MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.last_period_to_credit ), MSG_LEVEL_DEBUG );
debug( ' currency_code=' || p_select_rec.currency_code, MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.inv_acct_rule_duration ), MSG_LEVEL_DEBUG );
p_select_rec.allow_not_open_flag, MSG_LEVEL_DEBUG );
debug( ' cm_gl_date=' || to_char( p_select_rec.cm_gl_date ),
MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.invoice_quantity ), MSG_LEVEL_DEBUG );
to_char( p_select_rec.cm_quantity ), MSG_LEVEL_DEBUG );
|| p_select_rec.invoice_sign, MSG_LEVEL_DEBUG );
|| p_select_rec.cm_sign, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_unit_price, MSG_LEVEL_DEBUG );
|| p_select_rec.cm_unit_price, MSG_LEVEL_DEBUG );
|| p_select_rec.partial_period_flag, MSG_LEVEL_DEBUG );
print_fcn_label2( 'arp_credit_memo_module.dump_select_rec()-' );
debug( 'EXCEPTION: arp_credit_memo_module.dump_select_rec()' );
END dump_select_rec;
SELECT invline.accounting_rule_id,
invline.rule_start_date,
cmline.extended_amount line_amount
FROM ra_customer_trx_lines_all cmline,
ra_customer_trx_lines_all invline
WHERE cmline.previous_customer_trx_line_id =
invline.customer_trx_line_id
AND cmline.customer_trx_line_id = p_cm_line_id;
distribution lines will be inserted in ascending order of
gl_date when the rules method is 'PRORATE'. */
/* Bug 2136455: Added 'REVERSE' for all rule methods */
FOR i in REVERSE 0..cm_sched_index - 1 LOOP
debug(' customer_trx_line_id='||cm_sched_ctlid_t( i ),
MSG_LEVEL_DEBUG);
debug(' insert_offset='||cm_sched_insert_offset_t( i ),
MSG_LEVEL_DEBUG);
IF( cm_sched_insert_dist_t( i ) = YES ) THEN
-------------------------------------------------------------
-- Insert into ra_cust_trx_line_gl_dist
-------------------------------------------------------------
-------------------------------------------------------------
-- Bind vars
-------------------------------------------------------------
BEGIN
debug( ' Binding rule_insert_dist_c', MSG_LEVEL_DEBUG );
dbms_sql.bind_variable( rule_insert_dist_c,
'customer_trx_line_id',
cm_sched_ctlid_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'customer_trx_line_id_1',
cm_sched_ctlid_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'gl_date',
cm_sched_gl_date_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'amount',
cm_sched_amount_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'amount_1',
cm_sched_amount_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'amount_2',
cm_sched_amount_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'amount_3',
cm_sched_amount_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'amount_4',
cm_sched_amount_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'amount_5',
cm_sched_amount_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'amount_6',
cm_sched_amount_t( i ) );
/*7147479 changed bind variable insert_offset_1*/
dbms_sql.bind_variable( rule_insert_dist_c,
'insert_offset_1',
cm_sched_insert_offset_t( i ) );
/*7147479 added extra bind variable insert_offset_2*/
dbms_sql.bind_variable( rule_insert_dist_c,
'insert_offset_2',
cm_sched_insert_offset_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'check_gl_date_1',
cm_sched_check_gl_date_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'period_set_name',
system_info.period_set_name );
dbms_sql.bind_variable( rule_insert_dist_c,
'session_id',
g_session_id);
dbms_sql.bind_variable( rule_insert_dist_c,
'customer_trx_line_id_2',
cm_sched_ctlid_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'original_gl_date_1',
cm_sched_orig_gl_date_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'gl_date_2',
cm_sched_gl_date_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'gl_date_3',
cm_sched_gl_date_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'gl_date_4',
cm_sched_gl_date_t( i ) );
/* in SELECT portion of statement */
dbms_sql.bind_variable( rule_insert_dist_c,
'original_gl_date',
cm_sched_orig_gl_date_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'gl_date_1',
cm_sched_gl_date_t( i ) );
dbms_sql.bind_variable( rule_insert_dist_c,
'check_gl_date_2',
cm_sched_check_gl_date_t( i ) );
debug( 'EXCEPTION: Error in binding rule_insert_dist_c' );
arp_standard.debug( ' Inserting distributions');
l_ignore := dbms_sql.execute( rule_insert_dist_c );
debug( 'EXCEPTION: Error executing insert dist stmt' );
IF( cm_sched_insert_cma_t( i ) = YES ) THEN
-------------------------------------------------------------
-- Insert into ar_credit_memo_amounts
-------------------------------------------------------------
/*--------------------------------------------------------------
| Bug # 2988282 - ORASHID: 07-07-2003
| If the credit memo line amount is zero and the corresponding
| invoice line is rule based then use the invoice line's
| rule start date as the gl date, otherwise continue with
| exisitng flow.
+--------------------------------------------------------------*/
OPEN lines(cm_sched_ctlid_t(i));
dbms_sql.bind_variable( rule_insert_cma_c,
'customer_trx_line_id',
cm_sched_ctlid_t( i ) );
dbms_sql.bind_variable( rule_insert_cma_c,
'gl_date',
l_original_gl_date);
dbms_sql.bind_variable( rule_insert_cma_c,
'amount',
cm_sched_amount_t( i ) );
debug( ' Inserting CM amounts', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( rule_insert_cma_c );
debug( to_char(l_ignore) || ' row(s) inserted',
MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing insert cma stmt' );
cm_sched_insert_dist_t:=null_cm_sched_insert_dist;
cm_sched_insert_cma_t:=null_cm_sched_insert_cma;
cm_sched_insert_offset_t:=null_cm_sched_insert_offset;
PROCEDURE insert_cm_schedule(
p_line_id IN BINARY_INTEGER,
p_gl_date IN DATE,
p_orig_gl_date IN DATE,
p_amount IN NUMBER,
p_insert_dist_flag IN VARCHAR,
p_insert_cma_flag IN VARCHAR,
p_insert_offset_flag IN VARCHAR,
p_check_gl_date_flag IN VARCHAR ) IS
l_index BINARY_INTEGER;
print_fcn_label('arp_credit_memo_module.insert_cm_schedule()+' );
debug(' p_insert_dist-flag='||p_insert_dist_flag, MSG_LEVEL_DEBUG);
debug(' p_insert_cma_flag='||p_insert_cma_flag, MSG_LEVEL_DEBUG);
debug(' p_insert_offset_flag='||p_insert_offset_flag, MSG_LEVEL_DEBUG);
cm_sched_insert_dist_t( cm_sched_index ) := p_insert_dist_flag;
cm_sched_insert_cma_t( cm_sched_index ) := p_insert_cma_flag;
cm_sched_insert_offset_t( cm_sched_index ) := p_insert_offset_flag;
print_fcn_label('arp_credit_memo_module.insert_cm_schedule()-' );
debug( 'EXCEPTION: arp_credit_memo_module.insert_cm_schedule('
|| to_char(p_line_id) || ', '
|| to_char(p_gl_date) || ', '
|| to_char(p_orig_gl_date) || ', '
|| to_char(p_amount) || ', '
|| p_insert_dist_flag || ', '
|| p_insert_cma_flag || ', '
|| p_insert_offset_flag || ', '
|| p_check_gl_date_flag || ')' );
END insert_cm_schedule;
FUNCTION update_cm_schedule(
p_line_id IN BINARY_INTEGER,
p_gl_date IN DATE,
p_amount IN NUMBER )
RETURN BOOLEAN IS
l_index BINARY_INTEGER;
print_fcn_label('arp_credit_memo_module.update_cm_schedule()+' );
print_fcn_label('arp_credit_memo_module.update_cm_schedule()-' );
print_fcn_label('arp_credit_memo_module.update_cm_schedule()-' );
debug( 'EXCEPTION: arp_credit_memo_module.update_cm_schedule('
|| to_char(p_line_id) || ', '
|| to_char(p_gl_date) || ', '
|| to_char(p_amount) || ')' );
END update_cm_schedule;
p_insert_dist_flag IN VARCHAR,
p_insert_cma_flag IN VARCHAR,
p_insert_offset_flag IN VARCHAR,
p_check_gl_date_flag IN VARCHAR )
IS
l_gl_date DATE;
debug(' p_insert_dist_flag='||p_insert_dist_flag, MSG_LEVEL_DEBUG);
debug(' p_insert_cma_flag='||p_insert_cma_flag, MSG_LEVEL_DEBUG);
SELECT invoicing_rule_id
INTO l_rule_id
FROM ra_customer_trx ct, ra_customer_trx_lines ctl
WHERE ct.customer_trx_id = ctl.customer_trx_id
AND ctl.customer_trx_line_id = p_line_id;
insert_cm_schedule( p_line_id,
l_gl_date,
p_gl_date, -- orig_gl_date
p_amount,
p_insert_dist_flag,
p_insert_cma_flag,
p_insert_offset_flag,
p_check_gl_date_flag );
l_bool := update_cm_schedule( p_line_id,
p_gl_date,
p_amount );
-- gl_date not in cm schedule tables, update the database
----------------------------------------------------------------
BEGIN
debug( ' Updating ar_credit_memo_amounts table',
MSG_LEVEL_DEBUG );
UPDATE ar_credit_memo_amounts
SET amount = amount + p_amount
WHERE customer_trx_line_id = p_line_id
and gl_date = p_gl_date;
debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
-- Update successful
--
-- Only create REV dist,
-- don't create cm amounts rec
----------------------------------------------------
debug( ' Update successful', MSG_LEVEL_DEBUG );
insert_cm_schedule( p_line_id,
l_gl_date,
p_gl_date, -- orig_gl_date
p_amount,
p_insert_dist_flag,
NO, -- p_insert_cma_flag
NO, -- p_insert_offset_flag
YES -- p_check_gl_date_flag
);
-- No cma record to update.
-- Entire cm line is an overapplication
-- Insert a cma record into array and increment
-- cm_acct_rule_duration
-- Create REV and UNEARN dist
----------------------------------------------------
debug( ' Update unsuccessful', MSG_LEVEL_DEBUG );
insert_cm_schedule( p_line_id,
l_gl_date,
p_gl_date, -- orig_gl_date
p_amount,
p_insert_dist_flag,
YES, -- p_insert_cma_flag
YES, -- p_insert_offset_flag
YES -- p_check_gl_date_flag
);
|| p_insert_dist_flag || ', '
|| p_insert_cma_flag || ', '
|| p_insert_offset_flag || ', '
|| p_check_gl_date_flag || ')' );
PROCEDURE update_net_revenue(
p_index IN BINARY_INTEGER,
p_amount IN NUMBER ) IS
BEGIN
net_rev_amount_t( p_index ) := p_amount;
debug( 'EXCEPTION: arp_credit_memo_module.update_net_revenue('
|| to_char(p_index) || ')' );
END update_net_revenue;
PROCEDURE update_net_rev_unit(
p_index IN BINARY_INTEGER,
p_amount IN NUMBER ) IS
BEGIN
net_rev_unit_t( p_index ) := p_amount;
debug( 'EXCEPTION: arp_credit_memo_module.update_net_rev_unit('
|| to_char(p_index) || ')' );
END update_net_rev_unit;
p_select_rec IN OUT NOCOPY select_rec_type,
p_start_index IN BINARY_INTEGER,
p_end_index IN BINARY_INTEGER,
p_cm_amt_remaining IN OUT NOCOPY NUMBER ) IS
l_amount NUMBER;
-- Update rule_start_date and rule_duration
-----------------------------------------------------
p_select_rec.rule_start_date :=
get_net_rev_gl_date( p_start_index );
p_select_rec.cm_acct_rule_duration := i - p_start_index + 1;
p_select_rec.cm_amount )) THEN
-----------------------------------------------------
-- cm amount < amt remaining: use total cm amount
-----------------------------------------------------
l_prorate_total_amount := p_select_rec.cm_amount;
p_select_rec.currency_code );
-- Update cm amount remaining
-------------------------------------------------------------
p_cm_amt_remaining := p_cm_amt_remaining - l_period_cm_amount;
-- Update net revenue amount
-------------------------------------------------------------
update_net_revenue( i, get_net_rev_amount(i) + l_period_cm_amount );
update_net_rev_unit( i,
get_net_rev_unit(i) +
(l_period_cm_amount /
p_select_rec.invoice_quantity) );
-- Insert into cm schedule array
-- (mode=I, array)
----------------------------------------------------------
process_cm_schedule( 'I',
p_select_rec.customer_trx_line_id,
get_net_rev_gl_date(i),
l_period_cm_amount,
p_select_rec.allow_not_open_flag,
get_net_rev_dist_exists( i ),
YES, -- insert_cma_flag
YES, -- insert_offset_flag
YES -- check_gl_date_flag
);
p_select_rec IN OUT NOCOPY select_rec_type,
p_start_index IN BINARY_INTEGER,
p_end_index IN BINARY_INTEGER,
p_cm_amt_remaining IN OUT NOCOPY NUMBER ) IS
l_amount NUMBER;
to use true sign(amt) from p_select_rec instead.
Bug 3198525 - Revised p_select_rec sign variables to be numbers
to resolve 10g certification issue. That meant that we no
longer needed to convert the char signs to numbers. */
-------------------------------------------------------------
-- If the net amount remaining in this period is zero, or
-- the invoice is aleady overapplied in this period
-- then go to previous period
-------------------------------------------------------------
IF( NOT ( get_net_rev_amount(i) = 0 OR
SIGN( get_net_rev_amount(i) ) <>
SIGN(p_select_rec.invoice_sign) ) ) THEN
IF( SIGN( get_net_rev_amount(i) ) =
SIGN( get_net_rev_amount(i) + p_cm_amt_remaining ) ) THEN
------------------------------------------------------------
-- The array amount + the cm_remaining is the same sign
-- as the array amount. Therefore, this is a partial
-- application of this period and no more processing will
-- be done for this credit memo line.
-- Set array_amount = array_amount + cm_remaining
------------------------------------------------------------
debug( ' Partial application case', MSG_LEVEL_DEBUG );
-- Update net revenue amount
------------------------------------------------------------
update_net_revenue( i, get_net_rev_amount(i) +
p_cm_amt_remaining );
-- Update rule_start_date, rule_duration
-- Only increment if not (net=0 or overapp)
------------------------------------------------------------
p_select_rec.rule_start_date := get_net_rev_gl_date( i );
p_select_rec.cm_acct_rule_duration :=
p_select_rec.cm_acct_rule_duration + 1;
-- Insert into cm schedule array
-- (mode=I, array)
----------------------------------------------------------
process_cm_schedule(
'I',
p_select_rec.customer_trx_line_id,
get_net_rev_gl_date( i ),
p_cm_amt_remaining,
p_select_rec.allow_not_open_flag,
get_net_rev_dist_exists( i ),
YES, -- insert_cma_flag
YES, -- insert_offset_flag
YES -- check_gl_date_flag
);
-- Update remaining_amount
------------------------------------------------------------
p_cm_amt_remaining := p_cm_amt_remaining +
get_net_rev_amount(i);
p_select_rec.cm_acct_rule_duration :=
p_select_rec.cm_acct_rule_duration + 1;
-- Insert into cm schedule array
-- (mode=I, array)
----------------------------------------------------------
process_cm_schedule(
'I',
p_select_rec.customer_trx_line_id,
get_net_rev_gl_date( i ),
-get_net_rev_amount( i ),
p_select_rec.allow_not_open_flag,
get_net_rev_dist_exists( i ),
YES, -- insert_cma_flag
YES, -- insert_offset_flag
YES -- check_gl_date_flag
);
-- Update net revenue amount
------------------------------------------------------------
update_net_revenue( i, 0 );
p_select_rec.rule_start_date := get_net_rev_gl_date( i );
p_select_rec IN OUT NOCOPY select_rec_type,
p_start_index IN BINARY_INTEGER,
p_end_index IN BINARY_INTEGER,
p_cm_amt_remaining IN OUT NOCOPY NUMBER ) IS
l_amount NUMBER;
to use true sign(amt) from p_select_rec instead.
Bug 3198525 - Original code converted char sign to
number. To avoid 10g cert issue, we revised sql
and structure to store sign as number.*/
IF( NOT ( get_net_rev_amount(i) = 0 OR
SIGN( get_net_rev_amount(i) ) <>
SIGN(p_select_rec.invoice_sign) ) ) THEN
l_period_cm_amount :=
arp_util.CurrRound( p_select_rec.cm_quantity *
get_net_rev_unit(i),
p_select_rec.currency_code );
p_select_rec.last_period_to_credit -
TRUNC( p_select_rec.last_period_to_credit );
p_select_rec.currency_code );
-- Update rule_start_date, rule_duration
-------------------------------------------------------------
p_select_rec.rule_start_date := get_net_rev_gl_date( i );
p_select_rec.cm_acct_rule_duration :=
p_select_rec.cm_acct_rule_duration + 1;
-- Insert into cm schedule array
-- (mode=I, array)
----------------------------------------------------------
process_cm_schedule( 'I',
p_select_rec.customer_trx_line_id,
get_net_rev_gl_date( i ),
l_period_cm_amount,
p_select_rec.allow_not_open_flag,
get_net_rev_dist_exists( i ),
YES, -- insert_cma_flag
YES, -- insert_offset_flag
YES -- check_gl_date_flag
);
-- Update net revenue amount
-------------------------------------------------------------
update_net_revenue( i, get_net_rev_amount(i) +
l_period_cm_amount );
p_select_rec IN OUT NOCOPY select_rec_type ) IS
l_start_index BINARY_INTEGER;
l_cm_amt_remaining := p_select_rec.cm_amount;
p_select_rec.cm_acct_rule_duration := 0;
p_select_rec.prev_cust_trx_line_id );
IF( p_select_rec.inv_acct_rule_duration <> -1 AND
net_rev_index <> p_select_rec.inv_acct_rule_duration AND
p_select_rec.cm_amount <> 0 ) THEN
--
-- Error: missing_periods
--
debug( ' raising missing_periods', MSG_LEVEL_DEBUG );
IF p_select_rec.partial_period_flag = 'Y' THEN
p_select_rec.rule_end_date := get_net_rev_gl_date( net_rev_index - 1);
p_select_rec.rule_end_date := null;
IF( p_select_rec.credit_method_for_rules = UNIT ) THEN
l_end_index := CEIL( p_select_rec.last_period_to_credit ) - 1;
IF ABS(p_select_rec.inv_unit_price) -
ABS(p_select_rec.cm_unit_price) < 0
THEN
debug( ' raising cm_unit_overapp, ' ||
p_select_rec.inv_unit_price || ' vs ' ||
p_select_rec.cm_unit_price, MSG_LEVEL_DEBUG );
IF( p_select_rec.invoice_sign = p_select_rec.cm_sign OR
p_select_rec.cm_sign = 0 OR
p_select_rec.invoice_sign = 0) THEN
debug( ' Overapplication case', MSG_LEVEL_DEBUG );
IF( p_select_rec.allow_overapp_flag = NO AND
p_select_rec.cm_sign <> 0 ) THEN
--
-- Error: overapplication not allowed
--
debug( ' overapp_not_allowed', MSG_LEVEL_DEBUG );
IF( p_select_rec.credit_method_for_rules = LIFO ) THEN
l_overapp_index := l_start_index;
ELSIF( p_select_rec.credit_method_for_rules = UNIT ) THEN
l_overapp_index := l_end_index;
ELSIF( p_select_rec.credit_method_for_rules = PRORATE ) THEN
-----------------------------------------------------------------
-- Find gl_date in net rev array which matches cm header gl_date
-- (search backwards)
-----------------------------------------------------------------
IF( find_net_revenue( p_select_rec.prev_cust_trx_line_id,
p_select_rec.cm_gl_date,
l_overapp_index ) = FALSE ) THEN
p_select_rec.rule_start_date := p_select_rec.cm_gl_date;
p_select_rec.cm_acct_rule_duration := 1;
p_select_rec.customer_trx_line_id,
p_select_rec.cm_gl_date,
p_select_rec.cm_amount,
p_select_rec.allow_not_open_flag,
NO, -- insert_dist_flag
YES, -- insert_cma_flag
NO, -- insert_offset_flag
NO -- check_gl_date_flag
);
-- Update net rev array: amount
---------------------------------------------------------------
update_net_revenue( l_overapp_index,
get_net_rev_amount( l_overapp_index ) +
p_select_rec.cm_amount );
-- Update rule_start_date = net rev gl_date, acct_rule_duration = 1
-------------------------------------------------------------------
p_select_rec.rule_start_date := get_net_rev_gl_date( l_overapp_index );
p_select_rec.cm_acct_rule_duration := 1;
-- Update cm schedule array: (mode=T, array)
----------------------------------------------------------
process_cm_schedule( 'U',
p_select_rec.customer_trx_line_id,
get_net_rev_gl_date( l_overapp_index ),
p_select_rec.cm_amount,
p_select_rec.allow_not_open_flag,
get_net_rev_dist_exists( l_overapp_index ),
YES, -- insert_cma_flag
YES, -- insert_offset_flag
YES -- check_gl_date_flag
);
IF( p_select_rec.inv_acct_rule_duration = -1 ) THEN
----------------------------------------------------------
-- Insert into cm schedule array: (mode=I, F)
----------------------------------------------------------
process_cm_schedule( 'I',
p_select_rec.customer_trx_line_id,
p_select_rec.cm_gl_date,
p_select_rec.cm_amount,
p_select_rec.allow_not_open_flag,
YES, -- insert_dist_flag
YES, -- insert_cma_flag
YES, -- insert_offset_flag
NO -- check_gl_date_flag
);
p_select_rec.rule_start_date := p_select_rec.cm_gl_date;
p_select_rec.cm_acct_rule_duration := 1;
IF( p_select_rec.credit_method_for_rules = PRORATE ) THEN
process_prorate_cm( p_select_rec,
l_start_index,
l_end_index,
l_cm_amt_remaining );
ELSIF( p_select_rec.credit_method_for_rules = LIFO ) THEN
process_lifo_cm( p_select_rec,
l_start_index,
l_end_index,
l_cm_amt_remaining );
ELSIF( p_select_rec.credit_method_for_rules = UNIT ) THEN
process_unit_cm( p_select_rec,
l_start_index,
l_end_index,
l_cm_amt_remaining );
IF( p_select_rec.credit_method_for_rules = LIFO ) THEN
l_overapp_index := l_start_index;
p_select_rec.rule_start_date :=
get_net_rev_gl_date( l_overapp_index );
IF( find_net_revenue( p_select_rec.prev_cust_trx_line_id,
p_select_rec.rule_start_date,
l_overapp_index ) = FALSE ) THEN
-- Bug Fix 624157
-- Do not raise an exception, instead populate rule_start_date
-- with the credit memo gl_date and rule_durration = 1
/**********************************************************
--
-- ERROR: No net revenue
--
debug( ' raising no_net_revenue',
MSG_LEVEL_DEBUG );
p_select_rec.rule_start_date := p_select_rec.cm_gl_date;
p_select_rec.cm_acct_rule_duration := 1;
-- Update net rev array
-------------------------------------------------------------
update_net_revenue( l_overapp_index,
get_net_rev_amount( l_overapp_index ) +
l_cm_amt_remaining );
-- Update cm schedule array: (mode=U, array)
----------------------------------------------------------
process_cm_schedule( 'U',
p_select_rec.customer_trx_line_id,
get_net_rev_gl_date( l_overapp_index ),
l_cm_amt_remaining,
p_select_rec.allow_not_open_flag,
get_net_rev_dist_exists( l_overapp_index ),
YES, -- insert_cma_flag
YES, -- insert_offset_flag
YES -- check_gl_date_flag
);
l_select_rec select_rec_type;
l_null_rec CONSTANT select_rec_type := l_select_rec;
rule_select_cm_lines_c,
rule_update_cm_lines_c,
rule_insert_dist_c,
rule_insert_cma_c );
define_select_columns( rule_select_cm_lines_c, l_select_rec );
dbms_sql.bind_variable( rule_select_cm_lines_c,
'cm_cust_trx_line_id',
p_cm_control.customer_trx_line_id );
debug( 'EXCEPTION: Error in binding rule_select_cm_lines_c' );
dbms_sql.bind_variable( rule_select_cm_lines_c,
'cm_customer_trx_id',
p_cm_control.customer_trx_id );
debug( 'EXCEPTION: Error in binding rule_select_cm_lines_c' );
dbms_sql.bind_variable( rule_select_cm_lines_c,
'request_id',
p_cm_control.request_id );
debug( 'EXCEPTION: Error in binding rule_select_cm_lines_c' );
dbms_sql.bind_variable( rule_insert_cma_c,
'request_id',
p_cm_control.request_id );
debug( 'EXCEPTION: Error in binding rule_insert_cma_c' );
debug( ' Executing select sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( rule_select_cm_lines_c );
debug( 'EXCEPTION: Error executing select cm lines sql' );
debug( ' Fetching select stmt', MSG_LEVEL_DEBUG );
IF dbms_sql.fetch_rows( rule_select_cm_lines_c ) > 0 THEN
debug(' fetched a row', MSG_LEVEL_DEBUG );
l_select_rec := l_null_rec;
get_select_column_values( rule_select_cm_lines_c,
l_select_rec );
dump_select_rec( l_select_rec );
-- No rows selected
--------------------------------------------------------
debug( ' raising NO_DATA_FOUND', MSG_LEVEL_DEBUG );
PROCEDURE insert_error_if_autoinv IS
BEGIN
insert_into_error_table(
l_select_rec.customer_trx_line_id,
g_error_buffer,
NULL );
IF g_prev_ctlid <> l_select_rec.prev_cust_trx_line_id
THEN
debug('write arrays to db and clear arrays...');
g_prev_ctlid := l_select_rec.prev_cust_trx_line_id;
l_select_rec );
insert_error_if_autoinv;
debug(' Binding variables for update', MSG_LEVEL_DEBUG);
l_select_rec.customer_trx_line_id,
MSG_LEVEL_DEBUG);
to_char(l_select_rec.rule_start_date),
MSG_LEVEL_DEBUG);
to_char(l_select_rec.rule_end_date),
MSG_LEVEL_DEBUG);
l_select_rec.cm_acct_rule_duration,
MSG_LEVEL_DEBUG);
dbms_sql.bind_variable( rule_update_cm_lines_c,
'rule_start_date',
l_select_rec.rule_start_date );
dbms_sql.bind_variable( rule_update_cm_lines_c,
'rule_end_date',
l_select_rec.rule_end_date );
dbms_sql.bind_variable( rule_update_cm_lines_c,
'cm_acct_rule_duration',
l_select_rec.cm_acct_rule_duration );
dbms_sql.bind_variable( rule_update_cm_lines_c,
'credit_method',
l_select_rec.credit_method_for_rules);
dbms_sql.bind_variable( rule_update_cm_lines_c,
'last_period_to_credit',
l_select_rec.last_period_to_credit );
dbms_sql.bind_variable( rule_update_cm_lines_c,
'customer_trx_line_id',
l_select_rec.customer_trx_line_id );
debug('EXCEPTION: Error in binding rule_update_cm_lines_c');
l_ignore := dbms_sql.execute( rule_update_cm_lines_c );
debug( to_char(l_ignore) || ' row(s) updated',
MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing update lines stmt' );
p_rule_start_date := l_select_rec.rule_start_date;
l_select_rec.cm_acct_rule_duration;
debug( 'EXCEPTION: Error fetching select cm lines' );
SELECT ps.payment_schedule_id,
int.previous_customer_trx_id,
int.interface_line_id,
ps.selected_for_receipt_batch_id
FROM ra_interface_lines int,
ar_payment_schedules ps
WHERE int.request_id = p_request_id
AND int.previous_customer_trx_id IS NOT NULL
AND int.previous_customer_trx_id = ps.customer_trx_id ;
PROCEDURE insert_errors(p_selected_for_rcpt_batch_id IN NUMBER) IS
l_jgzz_product_code VARCHAR2(100);
(p_selected_for_rcpt_batch_id = -999) then
g_error_buffer := MSG_INV_LOCKED_BY_JL;
insert_into_error_table( l_interface_line_id ,
g_error_buffer,
NULL ) ;
insert_into_error_table( l_interface_line_id ,
g_error_buffer,
NULL ) ;
SELECT 'Y'
INTO l_locked
FROM ar_payment_schedules
WHERE payment_schedule_id = i.payment_schedule_id
--AND selected_for_receipt_batch_id IS NULL /* Bug fix 3142217 */
--Commented The Above Line And Added The Following 3 Lines For Bug Fix 6339084
/*Bug Fix 6339084 Starts */
AND (selected_for_receipt_batch_id IS NULL
OR (selected_for_receipt_batch_id IS NOT NULL
AND NVL(global_attribute20,'~XX~X') = 'COLLECTION'))
/*Ends*/
FOR UPDATE OF payment_schedule_id NOWAIT ;
insert_errors(i.selected_for_receipt_batch_id);
insert_errors(i.selected_for_receipt_batch_id);
IF (arp_rounding.insert_round_records(
p_request_id,
p_customer_trx_id,
rows_processed,
error_message,
0,
0,
'CM', -- this doesn't matter!
arp_global.sysparam.TRX_HEADER_ROUND_CCID) <> 0)
THEN
debug('arp_rounding.insert_round_rows returned FALSE');
debug('arp_rounding.insert_round_records raised EXCEPTION');
SELECT decode( trx.invoicing_rule_id, null, 'N', 'Y')
INTO l_rule_flag
FROM ra_customer_trx trx
WHERE trx.customer_trx_id = p_customer_trx_id;
debug( 'EXCEPTION: Error in selecting into l_rule_flag' );
update the gl_dist_id this late. MRC calls will have to occur
inline */
close_cursors;
debug( ' Update mode', MSG_LEVEL_DEBUG );
IF( NOT( dbms_sql.is_open( delete_header_dist_c ) AND
dbms_sql.is_open( delete_line_dist_c ) AND
dbms_sql.is_open( delete_header_cma_c ) AND
dbms_sql.is_open( delete_line_cma_c ) AND
dbms_sql.is_open( update_header_lines_c ) AND
dbms_sql.is_open( update_lines_c ) ) ) THEN
----------------------------------------------------------------
-- Build dynamic sql
----------------------------------------------------------------
build_update_mode_sql(
delete_header_dist_c,
delete_line_dist_c,
delete_header_cma_c,
delete_line_cma_c,
update_header_lines_c,
update_lines_c );
-- Delete distributions, credit_memo_amounts
-- and update lines (rule_start_date, accounting_rule_duration)
----------------------------------------------------------------
IF( p_customer_trx_line_id IS NOT NULL ) THEN
---------------------------------------------------------------
-- Line-level processing
---------------------------------------------------------------
debug( ' Line-level processing', MSG_LEVEL_DEBUG );
delete_line_dist_c,
'customer_trx_line_id',
p_customer_trx_line_id );
delete_line_cma_c,
'customer_trx_line_id',
p_customer_trx_line_id );
update_lines_c,
'customer_trx_line_id',
p_customer_trx_line_id );
dbms_sql.bind_array(delete_line_dist_c, ':gl_dist_key_value',
gl_line_dist_array);
debug( ' Executing delete dist sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( delete_line_dist_c );
debug( to_char(l_ignore) || ' row(s) deleted',
MSG_LEVEL_DEBUG );
dbms_sql.variable_value( delete_line_dist_c,
':gl_dist_key_value',
gl_line_dist_array);
debug( 'EXCEPTION: Error executing delete dist sql' );
debug( ' Executing delete cma sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( delete_line_cma_c );
debug( to_char(l_ignore) || ' row(s) deleted',
MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing delete cma sql' );
debug( ' Executing update lines sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( update_lines_c );
debug( to_char(l_ignore) || ' row(s) updated',
MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing update lines sql' );
delete_header_dist_c,
'customer_trx_id',
p_customer_trx_id );
delete_header_cma_c,
'customer_trx_id',
p_customer_trx_id );
update_header_lines_c,
'customer_trx_id',
p_customer_trx_id );
dbms_sql.bind_array(delete_header_dist_c, ':gl_dist_key_value',
gl_header_dist_array);
debug( ' Executing delete dist sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( delete_header_dist_c );
debug( to_char(l_ignore) || ' row(s) deleted',
MSG_LEVEL_DEBUG );
dbms_sql.variable_value( delete_header_dist_c,
':gl_dist_key_value',
gl_header_dist_array);
debug( 'EXCEPTION: Error executing delete dist sql' );
debug( ' Executing delete cma sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( delete_header_cma_c );
debug( to_char(l_ignore) || ' row(s) deleted',
MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing delete cma sql' );
debug( ' Executing update lines sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( update_header_lines_c );
debug( to_char(l_ignore) || ' row(s) updated',
MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing update lines sql' );
nonrule_insert_dist_c,
nonrule_update_lines_c,
nonrule_update_dist_c,
nonrule_update_dist2_c );
nonrule_insert_dist_c,
nonrule_update_lines_c,
nonrule_update_dist_c,
nonrule_update_dist2_c );
nonrule_insert_dist_c,
nonrule_update_lines_c,
nonrule_update_dist_c,
nonrule_update_dist2_c );
rule_select_cm_lines_c,
rule_update_cm_lines_c,
rule_insert_dist_c,
rule_insert_cma_c );
rule_select_cm_lines_c,
rule_update_cm_lines_c,
rule_insert_dist_c,
rule_insert_cma_c );
rule_select_cm_lines_c,
rule_update_cm_lines_c,
rule_insert_dist_c,
rule_insert_cma_c );
PROCEDURE test_build_update_mode_sql is
l_cm_control control_rec_type;
build_update_mode_sql(
delete_header_dist_c,
delete_line_dist_c,
delete_header_cma_c,
delete_line_cma_c,
update_header_lines_c,
update_lines_c );