[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Binary Integer to Number(15) for all record types select_ips_rec_type,
select_ira_rec_type,select_ups_rec_type, select_iad_rec_type.
*/
TYPE select_ips_rec_type IS RECORD
(
customer_trx_id NUMBER(15),
trx_number ra_customer_trx.trx_number%type,
cust_trx_type_id NUMBER(15),
trx_type ra_cust_trx_types.type%type,
trx_date DATE,
gl_date DATE,
customer_id NUMBER(15),
site_use_id NUMBER(15),
reversed_cash_receipt_id NUMBER(15),
currency_code ra_customer_trx.invoice_currency_code%type,
precision NUMBER,
min_acc_unit NUMBER,
exchange_rate_type ra_customer_trx.exchange_rate_type%type,
exchange_rate NUMBER,
exchange_date DATE,
term_id NUMBER(15),
first_installment_code ra_terms.first_installment_code%type,
rec_acctd_amount NUMBER,
total_line_amount NUMBER,
total_tax_amount NUMBER,
total_freight_amount NUMBER,
total_charges_amount NUMBER,
term_sequence_num NUMBER,
percent NUMBER,
due_date DATE
);
TYPE select_ira_rec_type IS RECORD
(
customer_trx_id NUMBER(15),
trx_number ra_customer_trx.trx_number%type,
cust_trx_type_id NUMBER(15),
post_to_gl_flag ra_cust_trx_types.post_to_gl%type,
credit_method ra_customer_trx.credit_method_for_installments%type,
trx_date DATE,
gl_date DATE,
customer_id NUMBER(15),
site_use_id NUMBER(15),
currency_code ra_customer_trx.invoice_currency_code%type,
precision NUMBER,
min_acc_unit NUMBER,
exchange_rate_type ra_customer_trx.exchange_rate_type%type,
exchange_rate NUMBER,
exchange_date DATE,
rec_acctd_amount NUMBER,
total_cm_line_amount NUMBER,
total_cm_tax_amount NUMBER,
total_cm_freight_amount NUMBER,
total_cm_charges_amount NUMBER,
code_combination_id NUMBER(15),
gl_date_closed DATE,
actual_date_closed DATE,
inv_customer_trx_id NUMBER(15),
inv_precision NUMBER,
inv_min_acc_unit NUMBER,
inv_exchange_rate NUMBER,
inv_payment_schedule_id NUMBER(15),
inv_amount_due_remaining NUMBER,
inv_acctd_amt_due_rem NUMBER,
inv_line_remaining NUMBER,
inv_tax_remaining NUMBER,
inv_freight_remaining NUMBER,
inv_charges_remaining NUMBER,
inv_amount_credited NUMBER
);
TYPE select_ups_rec_type IS RECORD
(
set_of_books_id NUMBER(15),
customer_trx_id NUMBER(15),
post_to_gl_flag ra_cust_trx_types.post_to_gl%type,
trx_date DATE,
gl_date DATE,
precision NUMBER,
min_acc_unit NUMBER,
adjusted_trx_id NUMBER(15),
subsequent_trx_id NUMBER(15),
commitment_trx_id NUMBER(15),
commitment_type ra_cust_trx_types.type%type,
ps_currency_code ra_customer_trx.invoice_currency_code%type,
ps_exchange_rate NUMBER,
ps_precision NUMBER,
ps_min_acc_unit NUMBER,
code_combination_id NUMBER(15),
gl_date_closed DATE,
actual_date_closed DATE,
total_line_amount NUMBER,
payment_schedule_id NUMBER(15),
amount_due_remaining NUMBER,
acctd_amt_due_rem NUMBER,
line_remaining NUMBER,
amount_adjusted NUMBER,
percent NUMBER,
allocate_tax_freight ra_cust_trx_types_all.allocate_tax_freight%type,
adjustment_type ar_adjustments_all.type%type,
tax_remaining NUMBER,
freight_remaining NUMBER,
total_tax_amount NUMBER,
total_freight_amount NUMBER
);
TYPE select_iad_rec_type IS RECORD
(
set_of_books_id NUMBER(15),
customer_trx_id NUMBER(15),
post_to_gl_flag ra_cust_trx_types.post_to_gl%type,
trx_date DATE,
gl_date DATE,
precision NUMBER,
min_acc_unit NUMBER,
adjusted_trx_id NUMBER(15),
invoice_trx_id NUMBER(15),
ps_currency_code ra_customer_trx.invoice_currency_code%type,
ps_exchange_rate NUMBER,
ps_precision NUMBER,
ps_min_acc_unit NUMBER,
commitment_code NUMBER(15),
code_combination_id NUMBER(15),
gl_date_closed DATE,
actual_date_closed DATE,
total_cm_line_amount NUMBER,
total_inv_adj_amount NUMBER,
total_inv_line_remaining NUMBER,
payment_schedule_id NUMBER(15),
ps_amount_due_remaining NUMBER,
ps_acctd_amt_due_rem NUMBER,
ps_line_original NUMBER,
ps_line_remaining NUMBER,
ps_tax_original NUMBER,
ps_tax_remaining NUMBER,
ps_freight_original NUMBER,
ps_freight_remaining NUMBER,
ps_amount_adjusted NUMBER,
allocate_tax_freight ra_cust_trx_types.allocate_tax_freight%type,
adjustment_type ar_adjustments.type%type,
total_cm_tax_amount NUMBER,
total_cm_frt_amount NUMBER,
-- total_inv_line_adj NUMBER,
-- total_inv_tax_adj NUMBER,
-- total_inv_frt_adj NUMBER,
inv_line_adj NUMBER,
inv_tax_adj NUMBER,
inv_frt_adj NUMBER
);
p_select_c IN OUT NOCOPY INTEGER,
p_insert_ps_c IN OUT NOCOPY INTEGER ) IS
l_insert_ps_sql VARCHAR2(5000);
l_select_sql VARCHAR2(5000);
l_select_sql :=
'SELECT
/* :user_id, */
ctl.customer_trx_id,
/* null, */
sum(decode(ctl.line_type, ''LINE'', ctl.extended_amount,
''CB'', ctl.extended_amount, 0)),
sum(decode(ctl.line_type, ''TAX'', ctl.extended_amount, 0)),
sum(decode(ctl.line_type, ''FREIGHT'', ctl.extended_amount, 0)),
t.term_id,
tl.sequence_num,
/* Bug fix 5589303, If billing_date is not null, billing_date shall be used to calculate due date */
decode(ctt.type, ''CM'', ct.trx_date, nvl(tl.due_date,
decode(tl.due_days,
null,
decode(least(to_number(substrb(nvl(ct.billing_date, ct.trx_date),1,2)),
nvl(t.due_cutoff_day,32)),
t.due_cutoff_day,
last_day(add_months(nvl(ct.billing_date, ct.trx_date),
tl.due_months_forward)) +
least(tl.due_day_of_month,
to_number(substrb(last_day(add_months(
nvl(ct.billing_date, ct.trx_date),
tl.due_months_forward + 1)),1,2))),
last_day(add_months(nvl(ct.billing_date, ct.trx_date),
(tl.due_months_forward - 1)))
+ least(tl.due_day_of_month,
to_number(substrb(last_day(add_months(
nvl(ct.billing_date, ct.trx_date),
tl.due_months_forward)),1,2)))),
nvl(ct.billing_date, ct.trx_date) + tl.due_days))),
ct.bill_to_customer_id,
ctt.type,
ct.bill_to_site_use_id,
ct.cust_trx_type_id,
ct.invoice_currency_code,
/* null, */
ct.exchange_rate_type,
ct.exchange_rate,
ct.exchange_date,
ct.trx_number,
ct.trx_date,
tl.relative_amount / t.base_amount,
c.precision,
/* 1, */
t.first_installment_code,
c.minimum_accountable_unit,
nvl(ctlgd.gl_date, ct.trx_date),
nvl(ctlgd.acctd_amount, 0),
/* null, */
/* :raagixlul, */
sum(decode(ctl.line_type, ''CHARGES'', ctl.extended_amount, 0)),
ct.reversed_cash_receipt_id /* Bug3328690 */
FROM
ra_terms t,
ra_terms_lines tl,
ra_cust_trx_types ctt,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist ctlgd,
fnd_currencies c,
ra_customer_trx_lines ctl
WHERE ct.customer_trx_id = :customer_trx_id
AND ctl.customer_trx_id = ctlgd.customer_trx_id
AND ctlgd.account_class = ''REC''
AND ctlgd.latest_rec_flag = ''Y''
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.invoice_currency_code = c.currency_code
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.accounting_affect_flag = ''Y'' /* Open Receivables = Y */
AND ct.term_id = t.term_id(+)
AND t.term_id = tl.term_id(+)
and not (ctt.type = ''CM'' and ct.previous_customer_trx_id is not NULL)
GROUP BY
ctl.customer_trx_id,
t.term_id,
tl.relative_amount,
t.base_amount,
tl.sequence_num,
tl.due_date,
tl.due_days,
ct.trx_date,
t.due_cutoff_day,
tl.due_months_forward,
tl.due_day_of_month,
ct.bill_to_customer_id,
ctt.type,
ct.bill_to_site_use_id,
ct.cust_trx_type_id,
ct.invoice_currency_code,
ct.exchange_rate_type,
ct.exchange_rate,
ct.exchange_date,
ct.trx_number,
c.minimum_accountable_unit,
t.first_installment_code,
ctlgd.gl_date,
ctlgd.acctd_amount,
c.precision,
ct.reversed_cash_receipt_id, /*Bug3328690 */
ct.billing_date
ORDER BY
ctl.customer_trx_id,
tl.sequence_num';
debug(' select_sql = ' || CRLF ||
l_select_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(select_sql) = '||
to_char(length(l_select_sql)) || CRLF,
MSG_LEVEL_DEBUG);
l_insert_ps_sql :=
'INSERT INTO AR_PAYMENT_SCHEDULES
(
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
payment_schedule_id,
customer_trx_id,
amount_due_original,
amount_due_remaining,
acctd_amount_due_remaining,
amount_line_items_original,
amount_line_items_remaining,
tax_original,
tax_remaining,
freight_original,
freight_remaining,
receivables_charges_charged,
receivables_charges_remaining,
amount_credited,
amount_applied,
term_id,
terms_sequence_number,
due_date,
customer_id,
class,
customer_site_use_id,
cust_trx_type_id,
number_of_due_dates,
status,
invoice_currency_code,
actual_date_closed,
exchange_rate_type,
exchange_rate,
exchange_date,
trx_number,
trx_date,
gl_date_closed,
gl_date,
reversed_cash_receipt_id
,org_id
)
VALUES
(
:user_id, /* created_by */
sysdate, /* creation_date */
:user_id, /* last_updated_by */
sysdate, /* last_update_date */
:login_id, /* last_update_login */
:request_id, /* request_id */
decode(:application_id,
-1, null, :application_id), /* program_application_id */
decode(:program_id, -1, null, :program_id), /* program_id */
sysdate, /* program_update_date */
:payment_schedule_id,
:customer_trx_id, /* customer_trx_id */
:line_amt + nvl(:tax_amt, 0) + nvl(:frt_amt, 0) +
nvl(:charge_amt, 0), /* ado */
:line_amt + nvl(:tax_amt, 0) + nvl(:frt_amt, 0) +
nvl(:charge_amt, 0), /* adr */
:acctd_adr, /* acctd_amount_due_remaining */
:line_amt, /* alio */
:line_amt, /* alir */
nvl(:tax_amt, 0), /* tax_original */
nvl(:tax_amt, 0), /* tax_remaining */
nvl(:frt_amt, 0), /* freight_original */
nvl(:frt_amt, 0), /* freight_remaining */
nvl(:charge_amt, 0), /* receivables_charges_charged */
nvl(:charge_amt, 0), /* receivables_charges_remaining */
null, /* amount_credited */
null, /* amount_applied */
:term_id, /* term_id */
nvl(:terms_sequence_number,1), /* terms_sequence_number */
:due_date,
:customer_id,
:type, /* class */
:site_use_id, /* customer_site_use_id */
:cust_trx_type_id, /* cust_trx_type_id */
:number_of_due_dates, /* number_of_due_dates */
decode(:line_amt + nvl(:tax_amt, 0) + nvl(:frt_amt, 0)+ nvl(:charge_amt, 0),
0, ''CL'', ''OP''), /* status */
:currency_code,
nvl(decode(:line_amt + nvl(:tax_amt,0) + nvl(:frt_amt,0)+
nvl(:charge_amt,0),
0, :trx_date, null), to_date(''12/31/4712'',''MM/DD/YYYY'')),/* bug#2678029 lgandhi actual_date_closed */
:exchange_rate_type,
:exchange_rate,
:exchange_date,
:trx_number,
:trx_date,
nvl(decode(:line_amt + nvl(:tax_amt, 0) + nvl(:frt_amt, 0) +
nvl(:charge_amt, 0),
0, nvl(:gl_date, :trx_date), null),to_date(''12/31/4712'',''MM/DD/YYYY'')), /* gl_date_closed */
nvl(:gl_date, :trx_date),
:reversed_cash_receipt_id
, :org_id--arp_standard.sysparm.org_id /* SSA changes anuj */
)' ;
debug(' insert_ps_sql = ' || CRLF ||
l_insert_ps_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(insert_ps_sql) = '||
to_char(length(l_insert_ps_sql)) || CRLF,
MSG_LEVEL_DEBUG);
p_insert_ps_c := dbms_sql.open_cursor;
dbms_sql.parse( p_insert_ps_c, l_insert_ps_sql,
dbms_sql.v7 );
p_select_c := dbms_sql.open_cursor;
dbms_sql.parse( p_select_c, l_select_sql,
dbms_sql.v7 );
PROCEDURE define_ips_select_columns(
p_select_c IN INTEGER,
p_select_rec IN select_ips_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()+' );
dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_id );
dbms_sql.define_column( p_select_c, 2, p_select_rec.total_line_amount );
dbms_sql.define_column( p_select_c, 3, p_select_rec.total_tax_amount );
dbms_sql.define_column( p_select_c, 4, p_select_rec.total_freight_amount );
dbms_sql.define_column( p_select_c, 5, p_select_rec.term_id );
dbms_sql.define_column( p_select_c, 6, p_select_rec.term_sequence_num );
dbms_sql.define_column( p_select_c, 7, p_select_rec.due_date );
dbms_sql.define_column( p_select_c, 8, p_select_rec.customer_id );
dbms_sql.define_column( p_select_c, 9, p_select_rec.trx_type, 20 );
dbms_sql.define_column( p_select_c, 10, p_select_rec.site_use_id );
dbms_sql.define_column( p_select_c, 11, p_select_rec.cust_trx_type_id );
dbms_sql.define_column( p_select_c, 12,
p_select_rec.currency_code, 15 );
dbms_sql.define_column( p_select_c, 13,
p_select_rec.exchange_rate_type, 30 );
dbms_sql.define_column( p_select_c, 14, p_select_rec.exchange_rate );
dbms_sql.define_column( p_select_c, 15, p_select_rec.exchange_date );
dbms_sql.define_column( p_select_c, 16, p_select_rec.trx_number, 20 );
dbms_sql.define_column( p_select_c, 17, p_select_rec.trx_date );
dbms_sql.define_column( p_select_c, 18, p_select_rec.percent );
dbms_sql.define_column( p_select_c, 19, p_select_rec.precision );
dbms_sql.define_column( p_select_c, 20,
p_select_rec.first_installment_code, 12 );
dbms_sql.define_column( p_select_c, 21,
p_select_rec.min_acc_unit );
dbms_sql.define_column( p_select_c, 22, p_select_rec.gl_date );
dbms_sql.define_column( p_select_c, 23, p_select_rec.rec_acctd_amount );
dbms_sql.define_column( p_select_c, 24,
p_select_rec.total_charges_amount );
dbms_sql.define_column( p_select_c, 25,p_select_rec.reversed_cash_receipt_id); /*Bug3328690 */
print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()-' );
debug('EXCEPTION: arp_maintain_ps2.define_ips_select_columns()',
MSG_LEVEL_BASIC);
END define_ips_select_columns;
PROCEDURE get_ips_column_values( p_select_c IN INTEGER,
p_select_rec IN OUT NOCOPY select_ips_rec_type ) IS
/* Bug 460927 - Modified IN to IN OUT in the above line - Oracle 8 */
BEGIN
print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()+' );
dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_id );
dbms_sql.column_value( p_select_c, 2, p_select_rec.total_line_amount );
dbms_sql.column_value( p_select_c, 3, p_select_rec.total_tax_amount );
dbms_sql.column_value( p_select_c, 4, p_select_rec.total_freight_amount );
dbms_sql.column_value( p_select_c, 5, p_select_rec.term_id );
dbms_sql.column_value( p_select_c, 6, p_select_rec.term_sequence_num );
dbms_sql.column_value( p_select_c, 7, p_select_rec.due_date );
dbms_sql.column_value( p_select_c, 8, p_select_rec.customer_id );
dbms_sql.column_value( p_select_c, 9, p_select_rec.trx_type );
dbms_sql.column_value( p_select_c, 10, p_select_rec.site_use_id );
dbms_sql.column_value( p_select_c, 11, p_select_rec.cust_trx_type_id );
dbms_sql.column_value( p_select_c, 12,
p_select_rec.currency_code );
dbms_sql.column_value( p_select_c, 13,
p_select_rec.exchange_rate_type );
dbms_sql.column_value( p_select_c, 14, p_select_rec.exchange_rate );
dbms_sql.column_value( p_select_c, 15, p_select_rec.exchange_date );
dbms_sql.column_value( p_select_c, 16, p_select_rec.trx_number );
dbms_sql.column_value( p_select_c, 17, p_select_rec.trx_date );
dbms_sql.column_value( p_select_c, 18, p_select_rec.percent );
dbms_sql.column_value( p_select_c, 19, p_select_rec.precision );
dbms_sql.column_value( p_select_c, 20,
p_select_rec.first_installment_code );
dbms_sql.column_value( p_select_c, 21,
p_select_rec.min_acc_unit );
dbms_sql.column_value( p_select_c, 22, p_select_rec.gl_date );
dbms_sql.column_value( p_select_c, 23, p_select_rec.rec_acctd_amount );
dbms_sql.column_value( p_select_c, 24, p_select_rec.total_charges_amount );
dbms_sql.column_value( p_select_c, 25, p_select_rec.reversed_cash_receipt_id); /*Bug3328690 */
PROCEDURE dump_ips_select_rec( p_select_rec IN select_ips_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()+' );
debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
|| p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.trx_number, MSG_LEVEL_DEBUG );
|| p_select_rec.cust_trx_type_id, MSG_LEVEL_DEBUG );
|| p_select_rec.trx_type, MSG_LEVEL_DEBUG );
|| p_select_rec.trx_date, MSG_LEVEL_DEBUG );
|| p_select_rec.gl_date, MSG_LEVEL_DEBUG );
|| p_select_rec.customer_id, MSG_LEVEL_DEBUG );
|| p_select_rec.site_use_id, MSG_LEVEL_DEBUG );
|| p_select_rec.reversed_cash_receipt_id, MSG_LEVEL_DEBUG );
|| p_select_rec.currency_code, MSG_LEVEL_DEBUG );
|| p_select_rec.precision, MSG_LEVEL_DEBUG );
|| p_select_rec.min_acc_unit, MSG_LEVEL_DEBUG );
|| p_select_rec.exchange_rate_type, MSG_LEVEL_DEBUG );
|| p_select_rec.exchange_rate, MSG_LEVEL_DEBUG );
|| p_select_rec.exchange_date, MSG_LEVEL_DEBUG );
|| p_select_rec.term_id, MSG_LEVEL_DEBUG );
|| p_select_rec.first_installment_code, MSG_LEVEL_DEBUG );
|| p_select_rec.rec_acctd_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_line_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_tax_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_freight_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_charges_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.term_sequence_num, MSG_LEVEL_DEBUG );
|| p_select_rec.percent, MSG_LEVEL_DEBUG );
|| p_select_rec.due_date, MSG_LEVEL_DEBUG );
print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()-' );
debug( 'EXCEPTION: arp_maintain_ps2.dump_ips_select_rec()',
MSG_LEVEL_BASIC );
END dump_ips_select_rec;
p_insert_ps_c IN INTEGER,
p_select_rec IN select_ips_rec_type,
p_number_of_due_dates IN NUMBER,
p_percent_t IN number_table_type,
p_terms_sequence_num_t IN number_table_type,
p_due_date_t IN date_table_type,
p_line_amount_t IN OUT NOCOPY number_table_type,
p_tax_amount_t IN OUT NOCOPY number_table_type,
p_freight_amount_t IN OUT NOCOPY number_table_type,
p_charges_amount_t IN OUT NOCOPY number_table_type,
p_acctd_amt_due_rem_t IN OUT NOCOPY number_table_type ) IS
l_ignore INTEGER;
p_select_rec.currency_code,
p_select_rec.total_line_amount,
p_percent_t,
p_line_amount_t );
p_select_rec.currency_code,
p_select_rec.total_charges_amount,
p_percent_t,
p_charges_amount_t );
IF( p_select_rec.first_installment_code = 'INCLUDE' ) THEN
--------------------------------------------------------------------
-- Put tax in 1st installment
--------------------------------------------------------------------
p_tax_amount_t( 0 ) := p_select_rec.total_tax_amount;
p_freight_amount_t( 0 ) := p_select_rec.total_freight_amount;
p_select_rec.currency_code,
p_select_rec.total_tax_amount,
p_percent_t,
p_tax_amount_t );
p_select_rec.currency_code,
p_select_rec.total_freight_amount,
p_percent_t,
p_freight_amount_t );
p_select_rec.exchange_rate,
p_line_amount_t,
p_tax_amount_t,
p_freight_amount_t,
p_charges_amount_t,
p_acctd_amt_due_rem_t,
p_select_rec.rec_acctd_amount );
debug( ' Binding insert_ps_c', MSG_LEVEL_DEBUG );
dbms_sql.bind_variable( p_insert_ps_c,
'user_id',
p_profile_info.user_id );
dbms_sql.bind_variable( p_insert_ps_c,
'login_id',
p_profile_info.conc_login_id );
dbms_sql.bind_variable( p_insert_ps_c,
'request_id',
p_profile_info.request_id );
dbms_sql.bind_variable( p_insert_ps_c,
'application_id',
p_profile_info.application_id );
dbms_sql.bind_variable( p_insert_ps_c,
'program_id',
p_profile_info.conc_program_id );
SELECT ar_payment_schedules_s.nextval
INTO l_ps_id
FROM dual;
dbms_sql.bind_variable( p_insert_ps_c,
'payment_schedule_id',
l_ps_id );
dbms_sql.bind_variable( p_insert_ps_c,
'customer_trx_id',
p_select_rec.customer_trx_id );
dbms_sql.bind_variable( p_insert_ps_c,
'line_amt',
p_line_amount_t(i) );
dbms_sql.bind_variable( p_insert_ps_c,
'tax_amt',
p_tax_amount_t(i) );
dbms_sql.bind_variable( p_insert_ps_c,
'frt_amt',
p_freight_amount_t(i) );
dbms_sql.bind_variable( p_insert_ps_c,
'charge_amt',
p_charges_amount_t(i) );
dbms_sql.bind_variable( p_insert_ps_c,
'acctd_adr',
p_acctd_amt_due_rem_t(i) );
dbms_sql.bind_variable( p_insert_ps_c,
'terms_sequence_number',
p_terms_sequence_num_t(i) );
dbms_sql.bind_variable( p_insert_ps_c,
'due_date',
p_due_date_t(i) );
dbms_sql.bind_variable( p_insert_ps_c,
'term_id',
p_select_rec.term_id );
dbms_sql.bind_variable( p_insert_ps_c,
'customer_id',
p_select_rec.customer_id );
dbms_sql.bind_variable( p_insert_ps_c,
'type',
p_select_rec.trx_type );
dbms_sql.bind_variable( p_insert_ps_c,
'site_use_id',
p_select_rec.site_use_id );
dbms_sql.bind_variable( p_insert_ps_c,
'cust_trx_type_id',
p_select_rec.cust_trx_type_id );
dbms_sql.bind_variable( p_insert_ps_c,
'currency_code',
p_select_rec.currency_code );
dbms_sql.bind_variable( p_insert_ps_c,
'trx_date',
p_select_rec.trx_date );
dbms_sql.bind_variable( p_insert_ps_c,
'gl_date',
p_select_rec.gl_date );
dbms_sql.bind_variable( p_insert_ps_c,
'trx_number',
p_select_rec.trx_number );
dbms_sql.bind_variable( p_insert_ps_c,
'exchange_rate_type',
p_select_rec.exchange_rate_type );
dbms_sql.bind_variable( p_insert_ps_c,
'exchange_rate',
p_select_rec.exchange_rate );
dbms_sql.bind_variable( p_insert_ps_c,
'exchange_date',
p_select_rec.exchange_date );
dbms_sql.bind_variable( p_insert_ps_c,
'number_of_due_dates',
p_number_of_due_dates );
dbms_sql.bind_variable( p_insert_ps_c,
'reversed_cash_receipt_id',
p_select_rec.reversed_cash_receipt_id );
dbms_sql.bind_variable( p_insert_ps_c,
'org_id',
arp_standard.sysparm.org_id /* SSA changes anuj */ );
debug( 'EXCEPTION: Error in binding rule_insert_dist_c',
MSG_LEVEL_BASIC );
debug( ' Inserting payment schedules', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( p_insert_ps_c );
debug( to_char(l_ignore) || ' row(s) inserted',
MSG_LEVEL_DEBUG );
JL_BR_SPED_PKG.SET_TRX_LOCK_STATUS(p_select_rec.customer_trx_id);
| Call central MRC library for insertion |
| into MRC tables |
+-------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'INSERT',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'SINGLE',
p_key_value => l_ps_id);
debug( 'EXCEPTION: Error executing insert ps stmt',
MSG_LEVEL_BASIC );
PROCEDURE insert_inv_ps_private(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_customer_trx_id IN BINARY_INTEGER,
p_reversed_cash_receipt_id IN BINARY_INTEGER ) IS
l_ignore INTEGER;
l_select_rec select_ips_rec_type;
PROCEDURE load_tables( p_select_rec IN select_ips_rec_type ) IS
BEGIN
print_fcn_label2('arp_maintain_ps2.load_tables()+' );
p_select_rec.term_sequence_num;
l_percent_t( l_table_index ) := p_select_rec.percent;
l_due_date_t( l_table_index ) := p_select_rec.due_date;
print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()+' );
IF( NOT( dbms_sql.is_open( ips_select_c ) AND
dbms_sql.is_open( ips_insert_ps_c ) )) THEN
build_ips_sql( system_info,
profile_info,
ips_select_c,
ips_insert_ps_c );
define_ips_select_columns( ips_select_c, l_select_rec );
dbms_sql.bind_variable( ips_select_c,
'customer_trx_id',
p_customer_trx_id );
debug( ' Executing select sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( ips_select_c );
debug( 'EXCEPTION: Error executing select sql',
MSG_LEVEL_BASIC );
IF dbms_sql.fetch_rows( ips_select_c ) > 0 THEN
debug(' Fetched a row', MSG_LEVEL_DEBUG );
dbms_sql.column_value( ips_select_c, 1, l_customer_trx_id );
ips_insert_ps_c,
l_select_rec,
l_table_index,
l_percent_t,
l_terms_sequence_num_t,
l_due_date_t,
l_line_amount_t,
l_tax_amount_t,
l_freight_amount_t,
l_charges_amount_t,
l_acctd_amt_due_rem_t );
get_ips_column_values( ips_select_c, l_select_rec );
l_select_rec.reversed_cash_receipt_id :=
p_reversed_cash_receipt_id;
dump_ips_select_rec( l_select_rec );
load_tables( l_select_rec );
ips_insert_ps_c,
l_select_rec,
l_table_index,
l_percent_t,
l_terms_sequence_num_t,
l_due_date_t,
l_line_amount_t,
l_tax_amount_t,
l_freight_amount_t,
l_charges_amount_t,
l_acctd_amt_due_rem_t );
debug( 'EXCEPTION: Error fetching select cursor',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()-' );
debug( 'EXCEPTION: arp_maintain_ps2.insert_inv_ps_private()',
MSG_LEVEL_BASIC );
END insert_inv_ps_private;
p_select_c IN OUT NOCOPY INTEGER,
p_insert_ps_c IN OUT NOCOPY INTEGER,
p_insert_ra_c IN OUT NOCOPY INTEGER,
p_update_ps_c IN OUT NOCOPY INTEGER ) IS
l_insert_ps_sql VARCHAR2(3000);
l_insert_ra_sql VARCHAR2(3000);
l_update_ps_sql VARCHAR2(2000);
l_select_sql VARCHAR2(6000);
l_select_sql :=
'SELECT
/* null, */
nvl(ct.trx_date, sysdate),
ct.bill_to_customer_id,
ct.cust_trx_type_id,
ctl.customer_trx_id,
ct.invoice_currency_code,
sum(decode(ctl.line_type,
''LINE'', ctl.extended_amount, 0)) /
(count(distinct nvl(ra.receivable_application_id, -9.9)) *
count(distinct nvl(adj.adjustment_id, -9.9))),
sum(decode(ctl.line_type, ''FREIGHT'', ctl.extended_amount, 0)) /
(count(distinct nvl(ra.receivable_application_id, -9.9)) *
count(distinct nvl(adj.adjustment_id, -9.9))),
sum(decode(ctl.line_type, ''TAX'', ctl.extended_amount, 0)) /
(count(distinct nvl(ra.receivable_application_id, -9.9)) *
count(distinct nvl(adj.adjustment_id, -9.9))),
/* null, */
ct.exchange_rate_type,
ct.exchange_rate,
ct.exchange_date,
ct.trx_number,
nvl(ctlgd.gl_date, ct.trx_date),
ctlgd_inv.code_combination_id,
ps.customer_trx_id,
ps.payment_schedule_id,
greatest(nvl(max(decode(ra.confirmed_flag,
''Y'', ra.gl_date,
null, decode(ra.receivable_application_id,
null, nvl(ctlgd.gl_date,
ct.trx_date),
ra.gl_date),
nvl(ctlgd.gl_date, ct.trx_date))),
nvl(ctlgd.gl_date, ct.trx_date)),
nvl(max(decode(adj.status,
''A'',adj.gl_date,
nvl(ctlgd.gl_date,ct.trx_date))),
nvl(ctlgd.gl_date,ct.trx_date)),
nvl(ctlgd.gl_date, ct.trx_date)),
greatest(nvl(max(decode(ra.confirmed_flag,
''Y'', ra.apply_date,
null, decode(ra.receivable_application_id,
null, ct.trx_date,
ra.apply_date),
ct.trx_date)),
ct.trx_date),
nvl(max(decode(adj.status,
''A'',adj.apply_date,
ct.trx_date)),
ct.trx_date),
ct.trx_date),
c.precision,
nvl(ps.amount_line_items_remaining,0),
nvl(ps.freight_remaining,0),
nvl(ps.tax_remaining,0),
ct.bill_to_site_use_id,
/* 0, */
/* 0, */
/* 0, */
c.minimum_accountable_unit,
ctt.post_to_gl,
/* nvl(ctlgd.gl_date,ct.trx_date), */
ct.credit_method_for_installments,
nvl(ps.amount_credited,0),
ps.amount_due_remaining,
ps.acctd_amount_due_remaining,
/* null, */
/* null, */
c_inv.precision,
c_inv.minimum_accountable_unit,
ct_inv.exchange_rate,
ctlgd.acctd_amount,
/* null, */
/* null, */
/* null, */
/* null, */
sum(decode(ctl.line_type, ''CHARGES'', ctl.extended_amount, 0)) /
(count(distinct nvl(ra.receivable_application_id, -9.9)) *
count(distinct nvl(adj.adjustment_id, -9.9))),
/* null, */
nvl(ps.receivables_charges_remaining,0)
/* , 0 */
FROM
ar_receivable_applications ra,
ar_payment_schedules ps,
ar_adjustments adj,
ra_cust_trx_types ctt,
ra_cust_trx_line_gl_dist ctlgd,
ra_customer_trx ct,
fnd_currencies c,
fnd_currencies c_inv,
ra_customer_trx ct_inv,
ra_cust_trx_line_gl_dist ctlgd_inv,
ra_customer_trx_lines ctl
WHERE ct.customer_trx_id = :customer_trx_id
and ctl.customer_trx_id = ct.customer_trx_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.type = ''CM''
AND ctt.accounting_affect_flag = ''Y''
AND ctl.previous_customer_trx_id = ps.customer_trx_id
AND ps.customer_trx_id = ra.applied_customer_trx_id (+)
AND ps.customer_trx_id = adj.customer_trx_id (+)
AND c.currency_code = ct.invoice_currency_code
AND ct.customer_trx_id = ctlgd.customer_trx_id
AND ctlgd.account_class = ''REC''
AND ctlgd.latest_rec_flag = ''Y''
AND ps.customer_trx_id = ct_inv.customer_trx_id
AND ct_inv.customer_trx_id = ctlgd_inv.customer_trx_id
AND ctlgd_inv.account_class = ''REC''
AND ctlgd_inv.latest_rec_flag= ''Y''
AND ct_inv.invoice_currency_code = c_inv.currency_code
GROUP BY
ctl.customer_trx_id,
ct.trx_date,
ct.bill_to_customer_id,
ct.cust_trx_type_id,
ct.invoice_currency_code,
ct.exchange_rate_type,
ct.exchange_rate,
ct.exchange_date,
ct.trx_number,
nvl(ctlgd.gl_date, ct.trx_date),
ctlgd_inv.code_combination_id,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.freight_remaining,
ps.tax_remaining,
ps.amount_line_items_remaining,
ps.gl_date,
ra.applied_customer_trx_id,
adj.customer_trx_id,
ct.bill_to_site_use_id,
c.precision,
c.minimum_accountable_unit,
ctlgd.gl_date,
ct.trx_date,
ct.credit_method_for_installments,
ctt.post_to_gl,
ps.amount_credited,
c_inv.precision,
c_inv.minimum_accountable_unit,
ct_inv.exchange_rate,
ctlgd.acctd_amount,
ps.terms_sequence_number,
ps.amount_due_remaining,
ps.receivables_charges_remaining,
ps.acctd_amount_due_remaining
ORDER BY
ps.customer_trx_id asc,
ctl.customer_trx_id asc,
ps.terms_sequence_number';
debug(' select_sql = ' || CRLF ||
l_select_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(select_sql) = '||
to_char(length(l_select_sql)) || CRLF,
MSG_LEVEL_DEBUG);
l_insert_ps_sql :=
'INSERT INTO AR_PAYMENT_SCHEDULES
(
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
payment_schedule_id,
customer_trx_id,
amount_due_original,
amount_due_remaining,
acctd_amount_due_remaining,
amount_line_items_original,
amount_line_items_remaining,
tax_original,
tax_remaining,
freight_original,
freight_remaining,
receivables_charges_charged,
receivables_charges_remaining,
amount_credited,
amount_applied,
term_id,
terms_sequence_number,
due_date,
customer_id,
class,
customer_site_use_id,
cust_trx_type_id,
number_of_due_dates,
status,
invoice_currency_code,
actual_date_closed,
exchange_rate_type,
exchange_rate,
exchange_date,
trx_number,
trx_date,
gl_date_closed,
gl_date
,org_id
)
VALUES
(
:user_id, /* created_by */
sysdate, /* creation_date */
:user_id, /* last_updated_by */
sysdate, /* last_update_date */
:login_id, /* last_update_login */
:request_id,
decode(:application_id,
-1, null, :application_id), /* program_application_id */
decode(:program_id, -1, null, :program_id), /* program_id */
sysdate, /* program_update_date */
:payment_schedule_id, /* payment_schedule_id */
:customer_trx_id,
:amount_due_original,
0, /* amount_due_remaining */
0, /* acctd_amount_due_remaining */
:amount_line_items_original,
0, /* amount_line_items_remaining */
:tax_original,
0, /* tax_remaining */
:freight_original,
0, /* freight_remaining */
:receivables_charges_charged,
0, /* receivables_charges_remaining */
0, /* amount_credited */
:amount_applied,
null, /* term_id */
1, /* terms_sequence_number */
:trx_date,
:customer_id,
''CM'', /* class */
:site_use_id,
:cust_trx_type_id,
1, /* number_of_due_dates */
''CL'', /* status */
:currency_code,
nvl(:trx_date, to_date(''12/31/4712'',''MM/DD/YYYY'')),
:exchange_rate_type,
:exchange_rate,
:exchange_date,
:trx_number,
:trx_date,
nvl(:gl_date, to_date(''12/31/4712'',''MM/DD/YYYY'')), /* gl_date_closed */
:gl_date
,:org_id --arp_standard.sysparm.org_id /* SSA changes anuj */
)' ;
debug(' insert_ps_sql = ' || CRLF ||
l_insert_ps_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(insert_ps_sql) = '||
to_char(length(l_insert_ps_sql)) || CRLF,
MSG_LEVEL_DEBUG);
l_insert_ra_sql :=
'INSERT INTO AR_RECEIVABLE_APPLICATIONS
(
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login,
receivable_application_id,
customer_trx_id,
payment_schedule_id,
gl_date,
code_combination_id,
set_of_books_id,
display,
application_type,
apply_date,
applied_customer_trx_id,
applied_payment_schedule_id,
status,
amount_applied,
acctd_amount_applied_from,
acctd_amount_applied_to,
line_applied,
tax_applied,
freight_applied,
receivables_charges_applied,
application_rule,
postable,
posting_control_id,
cash_receipt_history_id,
ussgl_transaction_code
,org_id
)
SELECT
:user_id, /* created_by */
sysdate, /* creation_date */
:user_id, /* last_updated_by */
sysdate, /* last_update_date */
:request_id,
:application_id,
:program_id,
sysdate, /* program_update_date */
:login_id, /* last_update_login */
:receivable_application_id,
:customer_trx_id,
ps.payment_schedule_id,
:gl_date,
:code_combination_id,
:set_of_books_id,
''Y'', /* display */
''CM'', /* application_type */
:trx_date,
:applied_customer_trx_id,
:applied_payment_schedule_id,
''APP'', /* status */
-:amount_applied,
-:acctd_amount_applied_from,
-:acctd_amount_applied_to,
-:line_applied,
-:tax_applied,
-:freight_applied,
-:receivables_charges_applied,
67, /* application_rule */
:post_to_gl_flag,
-3, /* posting_control_id */
null, /* cash_receipt_history_id */
:ussgl_transaction_code /*Transaction code*/
,ps.org_id /* SSA changes anuj */
FROM AR_PAYMENT_SCHEDULES ps
/* assumes only one ps line exists for CM */
WHERE :customer_trx_id = ps.customer_trx_id';
debug(' insert_ra_sql = ' || CRLF ||
l_insert_ra_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(insert_ra_sql) = '||
to_char(length(l_insert_ra_sql)) || CRLF,
MSG_LEVEL_DEBUG);
l_update_ps_sql :=
'UPDATE AR_PAYMENT_SCHEDULES
SET
last_update_date = sysdate,
last_updated_by = :user_id,
last_update_login = :login_id,
status = decode(:amount_due_remaining, 0, ''CL'', ''OP''),
gl_date_closed = decode(:amount_due_remaining, 0, :gl_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
actual_date_closed =
decode(:amount_due_remaining, 0, :actual_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
amount_due_remaining = :amount_due_remaining,
acctd_amount_due_remaining = :acctd_amount_due_remaining,
amount_line_items_remaining = :amount_line_items_remaining,
freight_remaining = :freight_remaining,
tax_remaining = :tax_remaining,
receivables_charges_remaining = :receivables_charges_remaining,
amount_credited = :amount_credited
WHERE payment_schedule_id = :applied_payment_schedule_id';
debug(' update_ps_sql = ' || CRLF ||
l_update_ps_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(update_ps_sql) = '||
to_char(length(l_update_ps_sql)) || CRLF,
MSG_LEVEL_DEBUG);
debug( ' Parsing insert_ps_c', MSG_LEVEL_DEBUG );
p_insert_ps_c := dbms_sql.open_cursor;
dbms_sql.parse( p_insert_ps_c, l_insert_ps_sql,
dbms_sql.v7 );
debug( ' Parsing insert_ra_c', MSG_LEVEL_DEBUG );
p_insert_ra_c := dbms_sql.open_cursor;
dbms_sql.parse( p_insert_ra_c, l_insert_ra_sql,
dbms_sql.v7 );
debug( ' Parsing update_ps_c', MSG_LEVEL_DEBUG );
p_update_ps_c := dbms_sql.open_cursor;
dbms_sql.parse( p_update_ps_c, l_update_ps_sql,
dbms_sql.v7 );
debug( ' Parsing select_c', MSG_LEVEL_DEBUG );
p_select_c := dbms_sql.open_cursor;
dbms_sql.parse( p_select_c, l_select_sql,
dbms_sql.v7 );
PROCEDURE define_ira_select_columns(
p_select_c IN INTEGER,
p_select_rec IN select_ira_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()+' );
dbms_sql.define_column( p_select_c, 1, p_select_rec.trx_date );
dbms_sql.define_column( p_select_c, 2, p_select_rec.customer_id );
dbms_sql.define_column( p_select_c, 3, p_select_rec.cust_trx_type_id );
dbms_sql.define_column( p_select_c, 4, p_select_rec.customer_trx_id );
dbms_sql.define_column( p_select_c, 5, p_select_rec.currency_code, 15 );
dbms_sql.define_column( p_select_c, 6, p_select_rec.total_cm_line_amount );
dbms_sql.define_column( p_select_c, 7,
p_select_rec.total_cm_freight_amount );
dbms_sql.define_column( p_select_c, 8, p_select_rec.total_cm_tax_amount );
dbms_sql.define_column( p_select_c, 9,
p_select_rec.exchange_rate_type, 30 );
dbms_sql.define_column( p_select_c, 10, p_select_rec.exchange_rate );
dbms_sql.define_column( p_select_c, 11, p_select_rec.exchange_date );
dbms_sql.define_column( p_select_c, 12, p_select_rec.trx_number, 20 );
dbms_sql.define_column( p_select_c, 13, p_select_rec.gl_date );
dbms_sql.define_column( p_select_c, 14, p_select_rec.code_combination_id );
dbms_sql.define_column( p_select_c, 15, p_select_rec.inv_customer_trx_id );
dbms_sql.define_column( p_select_c, 16,
p_select_rec.inv_payment_schedule_id );
dbms_sql.define_column( p_select_c, 17, p_select_rec.gl_date_closed );
dbms_sql.define_column( p_select_c, 18, p_select_rec.actual_date_closed );
dbms_sql.define_column( p_select_c, 19, p_select_rec.precision );
dbms_sql.define_column( p_select_c, 20, p_select_rec.inv_line_remaining );
dbms_sql.define_column( p_select_c, 21,
p_select_rec.inv_freight_remaining );
dbms_sql.define_column( p_select_c, 22, p_select_rec.inv_tax_remaining );
dbms_sql.define_column( p_select_c, 23, p_select_rec.site_use_id );
dbms_sql.define_column( p_select_c, 24,
p_select_rec.min_acc_unit );
dbms_sql.define_column( p_select_c, 25,
p_select_rec.post_to_gl_flag, 1 );
dbms_sql.define_column( p_select_c, 26,
p_select_rec.credit_method, 30 );
dbms_sql.define_column( p_select_c, 27,
p_select_rec.inv_amount_credited );
dbms_sql.define_column( p_select_c, 28,
p_select_rec.inv_amount_due_remaining );
dbms_sql.define_column( p_select_c, 29,
p_select_rec.inv_acctd_amt_due_rem );
dbms_sql.define_column( p_select_c, 30, p_select_rec.inv_precision );
dbms_sql.define_column( p_select_c, 31,
p_select_rec.inv_min_acc_unit );
dbms_sql.define_column( p_select_c, 32, p_select_rec.inv_exchange_rate );
dbms_sql.define_column( p_select_c, 33, p_select_rec.rec_acctd_amount );
dbms_sql.define_column( p_select_c, 34,
p_select_rec.total_cm_charges_amount );
dbms_sql.define_column( p_select_c, 35,
p_select_rec.inv_charges_remaining );
print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()-' );
debug('EXCEPTION: arp_maintain_ps2.define_ira_select_columns()',
MSG_LEVEL_BASIC);
END define_ira_select_columns;
PROCEDURE get_ira_column_values( p_select_c IN INTEGER,
p_select_rec IN OUT NOCOPY select_ira_rec_type ) IS
/* Bug 460927 - Modified IN to IN OUT oin the above line - oracle 8 */
BEGIN
print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()+' );
dbms_sql.column_value( p_select_c, 1, p_select_rec.trx_date );
dbms_sql.column_value( p_select_c, 2, p_select_rec.customer_id );
dbms_sql.column_value( p_select_c, 3, p_select_rec.cust_trx_type_id );
dbms_sql.column_value( p_select_c, 4, p_select_rec.customer_trx_id );
dbms_sql.column_value( p_select_c, 5, p_select_rec.currency_code );
dbms_sql.column_value( p_select_c, 6, p_select_rec.total_cm_line_amount );
dbms_sql.column_value( p_select_c, 7,
p_select_rec.total_cm_freight_amount );
dbms_sql.column_value( p_select_c, 8, p_select_rec.total_cm_tax_amount );
dbms_sql.column_value( p_select_c, 9,
p_select_rec.exchange_rate_type );
dbms_sql.column_value( p_select_c, 10, p_select_rec.exchange_rate );
dbms_sql.column_value( p_select_c, 11, p_select_rec.exchange_date );
dbms_sql.column_value( p_select_c, 12, p_select_rec.trx_number );
dbms_sql.column_value( p_select_c, 13, p_select_rec.gl_date );
dbms_sql.column_value( p_select_c, 14, p_select_rec.code_combination_id );
dbms_sql.column_value( p_select_c, 15, p_select_rec.inv_customer_trx_id );
dbms_sql.column_value( p_select_c, 16,
p_select_rec.inv_payment_schedule_id );
dbms_sql.column_value( p_select_c, 17, p_select_rec.gl_date_closed );
dbms_sql.column_value( p_select_c, 18, p_select_rec.actual_date_closed );
dbms_sql.column_value( p_select_c, 19, p_select_rec.precision );
dbms_sql.column_value( p_select_c, 20, p_select_rec.inv_line_remaining );
dbms_sql.column_value( p_select_c, 21,
p_select_rec.inv_freight_remaining );
dbms_sql.column_value( p_select_c, 22, p_select_rec.inv_tax_remaining );
dbms_sql.column_value( p_select_c, 23, p_select_rec.site_use_id );
dbms_sql.column_value( p_select_c, 24,
p_select_rec.min_acc_unit );
dbms_sql.column_value( p_select_c, 25,
p_select_rec.post_to_gl_flag );
dbms_sql.column_value( p_select_c, 26,
p_select_rec.credit_method );
dbms_sql.column_value( p_select_c, 27,
p_select_rec.inv_amount_credited );
dbms_sql.column_value( p_select_c, 28,
p_select_rec.inv_amount_due_remaining );
dbms_sql.column_value( p_select_c, 29,
p_select_rec.inv_acctd_amt_due_rem );
dbms_sql.column_value( p_select_c, 30, p_select_rec.inv_precision );
dbms_sql.column_value( p_select_c, 31,
p_select_rec.inv_min_acc_unit );
dbms_sql.column_value( p_select_c, 32, p_select_rec.inv_exchange_rate );
dbms_sql.column_value( p_select_c, 33, p_select_rec.rec_acctd_amount );
dbms_sql.column_value( p_select_c, 34,
p_select_rec.total_cm_charges_amount );
dbms_sql.column_value( p_select_c, 35,
p_select_rec.inv_charges_remaining );
PROCEDURE dump_ira_select_rec( p_select_rec IN select_ira_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()+' );
debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
|| p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.trx_number, MSG_LEVEL_DEBUG );
|| p_select_rec.cust_trx_type_id, MSG_LEVEL_DEBUG );
|| p_select_rec.post_to_gl_flag, MSG_LEVEL_DEBUG );
|| p_select_rec.credit_method, MSG_LEVEL_DEBUG );
|| p_select_rec.trx_date, MSG_LEVEL_DEBUG );
|| p_select_rec.gl_date, MSG_LEVEL_DEBUG );
|| p_select_rec.customer_id, MSG_LEVEL_DEBUG );
|| p_select_rec.site_use_id, MSG_LEVEL_DEBUG );
|| p_select_rec.currency_code, MSG_LEVEL_DEBUG );
|| p_select_rec.precision, MSG_LEVEL_DEBUG );
|| p_select_rec.min_acc_unit, MSG_LEVEL_DEBUG );
|| p_select_rec.exchange_rate_type, MSG_LEVEL_DEBUG );
|| p_select_rec.exchange_rate, MSG_LEVEL_DEBUG );
|| p_select_rec.exchange_date, MSG_LEVEL_DEBUG );
|| p_select_rec.rec_acctd_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_cm_line_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_cm_tax_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_cm_freight_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_cm_charges_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.code_combination_id, MSG_LEVEL_DEBUG );
|| p_select_rec.gl_date_closed, MSG_LEVEL_DEBUG );
|| p_select_rec.actual_date_closed, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_customer_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_precision, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_min_acc_unit, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_exchange_rate, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_payment_schedule_id, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_amount_due_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_acctd_amt_due_rem, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_line_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_tax_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_freight_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_charges_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.inv_amount_credited, MSG_LEVEL_DEBUG );
print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()-' );
debug( 'EXCEPTION: arp_maintain_ps2.dump_ira_select_rec()',
MSG_LEVEL_BASIC );
END dump_ira_select_rec;
p_select_rec IN select_ira_rec_type,
p_count IN NUMBER,
p_cm_amount IN NUMBER,
p_inv_rem_t IN OUT NOCOPY number_table_type,
p_cm_applied_amt_t IN OUT NOCOPY number_table_type ) IS
l_cm_amount_sign NUMBER;
p_select_rec.currency_code,
l_apply_amount,
l_percent_t,
p_cm_applied_amt_t );
p_insert_ps_c IN INTEGER,
p_insert_ra_c IN INTEGER,
p_update_ps_c IN INTEGER,
p_select_rec IN select_ira_rec_type,
p_number_records IN NUMBER,
p_inv_ps_id_t IN id_table_type,
p_inv_amount_due_rem_t IN OUT NOCOPY number_table_type,
p_inv_acctd_amt_due_rem_t IN OUT NOCOPY number_table_type,
p_inv_line_rem_t IN OUT NOCOPY number_table_type,
p_inv_tax_rem_t IN OUT NOCOPY number_table_type,
p_inv_freight_rem_t IN OUT NOCOPY number_table_type,
p_inv_charges_rem_t IN OUT NOCOPY number_table_type,
p_inv_amount_credited_t IN OUT NOCOPY number_table_type,
p_line_applied_t IN OUT NOCOPY number_table_type,
p_tax_applied_t IN OUT NOCOPY number_table_type,
p_freight_applied_t IN OUT NOCOPY number_table_type,
p_charges_applied_t IN OUT NOCOPY number_table_type,
p_acctd_amt_applied_from_t IN OUT NOCOPY number_table_type,
p_acctd_amt_applied_to_t IN OUT NOCOPY number_table_type ) IS
CURSOR get_appl_info (p_rec_app_id NUMBER) IS
SELECT payment_schedule_id,
applied_payment_schedule_id
from ar_receivable_applications
where receivable_application_id = p_rec_app_id;
IF( p_select_rec.credit_method in (FIFO, LIFO) ) THEN
IF( p_select_rec.credit_method = FIFO ) THEN
debug( ' FIFO processing', MSG_LEVEL_DEBUG );
p_select_rec.total_cm_line_amount,
p_inv_line_rem_t,
p_line_applied_t );
p_select_rec.total_cm_tax_amount,
p_inv_tax_rem_t,
p_tax_applied_t );
p_select_rec.total_cm_freight_amount,
p_inv_freight_rem_t,
p_freight_applied_t );
p_select_rec.total_cm_charges_amount,
p_inv_charges_rem_t,
p_charges_applied_t );
p_select_rec,
p_number_records,
p_select_rec.total_cm_line_amount,
p_inv_line_rem_t,
p_line_applied_t );
p_select_rec,
p_number_records,
p_select_rec.total_cm_tax_amount,
p_inv_tax_rem_t,
p_tax_applied_t );
p_select_rec,
p_number_records,
p_select_rec.total_cm_freight_amount,
p_inv_freight_rem_t,
p_freight_applied_t );
p_select_rec,
p_number_records,
p_select_rec.total_cm_charges_amount,
p_inv_charges_rem_t,
p_charges_applied_t );
-- Update amount_credited
--------------------------------------------------------------------
p_inv_amount_credited_t( i ) :=
p_inv_amount_credited_t( i ) + l_amount_applied ;
p_select_rec.inv_exchange_rate,
'+', -- type
p_inv_amount_due_rem_t( i ), -- master_from
p_inv_acctd_amt_due_rem_t( i ), -- acctd_master_from
l_amount_applied, -- detail
l_new_inv_adr, -- master_to
l_new_inv_acctd_adr, -- acctd_master_to
l_new_acctd_amt_applied_to -- acctd_detail
);
-- Update amounts
--------------------------------------------------------------------
p_inv_amount_due_rem_t( i ) := l_new_inv_adr;
p_select_rec.exchange_rate,
p_line_applied_t,
p_tax_applied_t,
p_freight_applied_t,
p_charges_applied_t,
p_acctd_amt_applied_from_t,
p_select_rec.rec_acctd_amount );
debug( ' Binding insert_ps_c', MSG_LEVEL_DEBUG );
dbms_sql.bind_variable( p_insert_ps_c,
'user_id',
p_profile_info.user_id );
dbms_sql.bind_variable( p_insert_ps_c,
'login_id',
p_profile_info.conc_login_id );
dbms_sql.bind_variable( p_insert_ps_c,
'request_id',
p_profile_info.request_id );
dbms_sql.bind_variable( p_insert_ps_c,
'application_id',
p_profile_info.application_id );
dbms_sql.bind_variable( p_insert_ps_c,
'program_id',
p_profile_info.conc_program_id );
SELECT ar_payment_schedules_s.nextval
INTO l_ps_id
FROM dual;
dbms_sql.bind_variable( p_insert_ps_c,
'payment_schedule_id',
l_ps_id );
dbms_sql.bind_variable( p_insert_ps_c,
'customer_trx_id',
p_select_rec.customer_trx_id );
dbms_sql.bind_variable( p_insert_ps_c,
'amount_due_original',
p_select_rec.total_cm_line_amount +
p_select_rec.total_cm_tax_amount +
p_select_rec.total_cm_freight_amount +
p_select_rec.total_cm_charges_amount );
dbms_sql.bind_variable( p_insert_ps_c,
'amount_line_items_original',
p_select_rec.total_cm_line_amount );
dbms_sql.bind_variable( p_insert_ps_c,
'tax_original',
p_select_rec.total_cm_tax_amount );
dbms_sql.bind_variable( p_insert_ps_c,
'freight_original',
p_select_rec.total_cm_freight_amount );
dbms_sql.bind_variable( p_insert_ps_c,
'receivables_charges_charged',
p_select_rec.total_cm_charges_amount );
dbms_sql.bind_variable( p_insert_ps_c,
'amount_applied',
p_select_rec.total_cm_line_amount +
p_select_rec.total_cm_tax_amount +
p_select_rec.total_cm_freight_amount +
p_select_rec.total_cm_charges_amount );
dbms_sql.bind_variable( p_insert_ps_c,
'trx_date',
p_select_rec.trx_date );
dbms_sql.bind_variable( p_insert_ps_c,
'customer_id',
p_select_rec.customer_id );
dbms_sql.bind_variable( p_insert_ps_c,
'site_use_id',
p_select_rec.site_use_id );
dbms_sql.bind_variable( p_insert_ps_c,
'cust_trx_type_id',
p_select_rec.cust_trx_type_id );
dbms_sql.bind_variable( p_insert_ps_c,
'currency_code',
p_select_rec.currency_code );
dbms_sql.bind_variable( p_insert_ps_c,
'exchange_rate_type',
p_select_rec.exchange_rate_type );
dbms_sql.bind_variable( p_insert_ps_c,
'exchange_rate',
p_select_rec.exchange_rate );
dbms_sql.bind_variable( p_insert_ps_c,
'exchange_date',
p_select_rec.exchange_date );
dbms_sql.bind_variable( p_insert_ps_c,
'gl_date',
p_select_rec.gl_date );
dbms_sql.bind_variable( p_insert_ps_c,
'trx_number',
p_select_rec.trx_number );
dbms_sql.bind_variable( p_insert_ps_c,
'org_id',
arp_standard.sysparm.org_id /* SSA changes anuj */
);
debug( 'EXCEPTION: Error in binding insert_ps',
MSG_LEVEL_BASIC );
debug( ' Inserting CM payment schedules', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( p_insert_ps_c );
debug( to_char(l_ignore) || ' row(s) inserted',
MSG_LEVEL_DEBUG );
| Call central MRC library for insertion |
| into MRC tables |
+-------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'INSERT',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'SINGLE',
p_key_value => l_ps_id);
debug( 'EXCEPTION: Error executing insert ps stmt',
MSG_LEVEL_BASIC );
debug( ' Binding insert_ra_c', MSG_LEVEL_DEBUG );
/*Bug :2246098-Used a bind var for insert*/
select ct.default_ussgl_transaction_code into l_ussgl_transaction_code
from ra_customer_trx ct
WHERE ct.customer_trx_id=p_select_rec.customer_trx_id;
dbms_sql.bind_variable( p_insert_ra_c,
'user_id',
p_profile_info.user_id );
dbms_sql.bind_variable( p_insert_ra_c,
'login_id',
p_profile_info.conc_login_id );
dbms_sql.bind_variable( p_insert_ra_c,
'request_id',
p_profile_info.request_id );
dbms_sql.bind_variable( p_insert_ra_c,
'application_id',
p_profile_info.application_id );
dbms_sql.bind_variable( p_insert_ra_c,
'program_id',
p_profile_info.conc_program_id );
dbms_sql.bind_variable( p_insert_ra_c,
'customer_trx_id',
p_select_rec.customer_trx_id );
dbms_sql.bind_variable( p_insert_ra_c,
'gl_date',
p_select_rec.gl_date );
dbms_sql.bind_variable( p_insert_ra_c,
'code_combination_id',
p_select_rec.code_combination_id );
p_insert_ra_c,
'set_of_books_id',
arp_standard.sysparm.set_of_books_id );
dbms_sql.bind_variable( p_insert_ra_c,
'trx_date',
p_select_rec.trx_date );
dbms_sql.bind_variable( p_insert_ra_c,
'applied_customer_trx_id',
p_select_rec.inv_customer_trx_id );
dbms_sql.bind_variable( p_insert_ra_c,
'applied_payment_schedule_id',
p_inv_ps_id_t( i ) );
dbms_sql.bind_variable( p_insert_ra_c,
'amount_applied',
p_line_applied_t( i ) +
p_tax_applied_t( i ) +
p_freight_applied_t( i ) +
p_charges_applied_t( i ) );
dbms_sql.bind_variable( p_insert_ra_c,
'acctd_amount_applied_from',
p_acctd_amt_applied_from_t( i ) );
dbms_sql.bind_variable( p_insert_ra_c,
'acctd_amount_applied_to',
p_acctd_amt_applied_to_t( i ) );
dbms_sql.bind_variable( p_insert_ra_c,
'line_applied',
p_line_applied_t( i ) );
dbms_sql.bind_variable( p_insert_ra_c,
'tax_applied',
p_tax_applied_t( i ) );
dbms_sql.bind_variable( p_insert_ra_c,
'freight_applied',
p_freight_applied_t( i ) );
dbms_sql.bind_variable( p_insert_ra_c,
'receivables_charges_applied',
p_charges_applied_t( i ) );
dbms_sql.bind_variable( p_insert_ra_c,
'post_to_gl_flag',
p_select_rec.post_to_gl_flag );
select ar_receivable_applications_s.nextval
into l_receivable_application_id
from dual;
dbms_sql.bind_variable( p_insert_ra_c,
'receivable_application_id',
l_receivable_application_id );
dbms_sql.bind_variable( p_insert_ra_c,
'ussgl_transaction_code',
l_ussgl_transaction_code );
debug( 'EXCEPTION: Error in selecting sequence nextval',
MSG_LEVEL_BASIC );
debug( 'EXCEPTION: Error in binding insert_ra_c',
MSG_LEVEL_BASIC );
debug( ' Inserting applications', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( p_insert_ra_c );
debug( to_char(l_ignore) || ' row(s) inserted',
MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing insert ra stmt',
MSG_LEVEL_BASIC );
l_ae_doc_rec.document_id := p_select_rec.customer_trx_id;
SELECT class INTO l_ps_class
FROM ar_payment_schedules
WHERE customer_trx_id = p_select_rec.customer_trx_id;
debug( ' Binding update_ps_c', MSG_LEVEL_DEBUG );
dbms_sql.bind_variable( p_update_ps_c,
'user_id',
p_profile_info.user_id );
dbms_sql.bind_variable( p_update_ps_c,
'login_id',
p_profile_info.conc_login_id );
dbms_sql.bind_variable( p_update_ps_c,
'amount_due_remaining',
p_inv_amount_due_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'gl_date_closed',
p_select_rec.gl_date_closed );
dbms_sql.bind_variable( p_update_ps_c,
'actual_date_closed',
p_select_rec.actual_date_closed );
dbms_sql.bind_variable( p_update_ps_c,
'amount_line_items_remaining',
p_inv_line_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'amount_credited',
p_inv_amount_credited_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'freight_remaining',
p_inv_freight_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'tax_remaining',
p_inv_tax_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'acctd_amount_due_remaining',
p_inv_acctd_amt_due_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'receivables_charges_remaining',
p_inv_charges_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'applied_payment_schedule_id',
p_inv_ps_id_t( i ) );
debug( 'EXCEPTION: Error in binding update_ps_c',
MSG_LEVEL_BASIC );
l_ignore := dbms_sql.execute( p_update_ps_c );
debug( to_char(l_ignore) || ' row(s) updated',
MSG_LEVEL_DEBUG );
| Call central MRC library for update |
| of AR_PAYMENT_SCHEDULES |
+-------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'SINGLE',
p_key_value => p_inv_ps_id_t( i ));
debug( 'EXCEPTION: Error executing update ps stmt',
MSG_LEVEL_BASIC );
debug('EXCEPTION: dynamic sql that got executed '||p_update_ps_c,
MSG_LEVEL_BASIC);
PROCEDURE insert_cm_ps_private(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_customer_trx_id IN BINARY_INTEGER ) IS
l_ignore INTEGER;
l_select_rec select_ira_rec_type;
PROCEDURE load_tables( p_select_rec IN select_ira_rec_type ) IS
BEGIN
print_fcn_label2('arp_maintain_ps2.load_tables()+' );
p_select_rec.inv_payment_schedule_id;
p_select_rec.inv_amount_due_remaining;
p_select_rec.inv_acctd_amt_due_rem;
p_select_rec.inv_line_remaining;
p_select_rec.inv_tax_remaining;
p_select_rec.inv_freight_remaining;
p_select_rec.inv_charges_remaining;
p_select_rec.inv_amount_credited;
print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()+' );
IF( NOT( dbms_sql.is_open( ira_select_c ) AND
dbms_sql.is_open( ira_insert_ps_c ) AND
dbms_sql.is_open( ira_insert_ra_c ) AND
dbms_sql.is_open( ira_update_ps_c ) )) THEN
build_ira_sql(
system_info,
profile_info,
ira_select_c,
ira_insert_ps_c,
ira_insert_ra_c,
ira_update_ps_c );
define_ira_select_columns( ira_select_c, l_select_rec );
dbms_sql.bind_variable( ira_select_c,
'customer_trx_id',
p_customer_trx_id );
debug( ' Executing select sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( ira_select_c );
debug( 'EXCEPTION: Error executing select sql',
MSG_LEVEL_BASIC );
IF dbms_sql.fetch_rows( ira_select_c ) > 0 THEN
debug(' Fetched a row', MSG_LEVEL_DEBUG );
dbms_sql.column_value( ira_select_c, 4, l_customer_trx_id );
dbms_sql.column_value( ira_select_c, 15,
l_inv_customer_trx_id );
ira_insert_ps_c,
ira_insert_ra_c,
ira_update_ps_c,
l_select_rec,
l_table_index,
l_inv_ps_id_t,
l_inv_amount_due_rem_t,
l_inv_acctd_amt_due_rem_t,
l_inv_line_rem_t,
l_inv_tax_rem_t,
l_inv_freight_rem_t,
l_inv_charges_rem_t,
l_inv_amount_credited_t,
l_line_applied_t,
l_tax_applied_t,
l_freight_applied_t,
l_charges_applied_t,
l_acctd_amt_applied_from_t,
l_acctd_amt_applied_to_t );
get_ira_column_values( ira_select_c, l_select_rec );
dump_ira_select_rec( l_select_rec );
load_tables( l_select_rec );
ira_insert_ps_c,
ira_insert_ra_c,
ira_update_ps_c,
l_select_rec,
l_table_index,
l_inv_ps_id_t,
l_inv_amount_due_rem_t,
l_inv_acctd_amt_due_rem_t,
l_inv_line_rem_t,
l_inv_tax_rem_t,
l_inv_freight_rem_t,
l_inv_charges_rem_t,
l_inv_amount_credited_t,
l_line_applied_t,
l_tax_applied_t,
l_freight_applied_t,
l_charges_applied_t,
l_acctd_amt_applied_from_t,
l_acctd_amt_applied_to_t );
debug( 'EXCEPTION: Error fetching select cursor',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()-' );
debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_ps_private()',
MSG_LEVEL_BASIC );
END insert_cm_ps_private;
p_select_c IN OUT NOCOPY INTEGER,
p_insert_adj_c IN OUT NOCOPY INTEGER,
p_update_ps_c IN OUT NOCOPY INTEGER ) IS
l_insert_adj_sql VARCHAR2(2000);
l_update_ps_sql VARCHAR2(2000);
l_select_sql VARCHAR2(6000);
l_select_sql :=
'SELECT
/* :raagixuix, */
ctl.set_of_books_id,
/* -1, */
/* -''Y'', */
/* ''LINE'', overridden below */
/* -''C'', */
/* -''A'', */
sum(ctl.extended_amount) /
(count(distinct ps.payment_schedule_id) *
count(distinct nvl(ra.receivable_application_id, -9.9)) *
count(distinct nvl(adj.adjustment_id, -9.9))),
nvl(:gl_date, nvl(ctlgd.gl_date, ct.trx_date)),
ctlgd_com.code_combination_id,
decode(ctt_com.type,
''DEP'', ctl.customer_trx_id,
ctl_com.customer_trx_id),
ps.payment_schedule_id,
decode(ctt_com.type,
''DEP'', null,
ctl.customer_trx_id),
''Y'', /* bugfix 2614759. Instead of ctt.post_to_gl, pass y always. */
ct_com.customer_trx_id,
tl.relative_amount / t.base_amount,
c.precision,
c.minimum_accountable_unit,
greatest(nvl(max(decode(ra.confirmed_flag,
''Y'', ra.gl_date,
null,
decode(ra.receivable_application_id,
null, nvl(ctlgd.gl_date,
ct.trx_date),
ra.gl_date),
nvl(ctlgd.gl_date,
ct.trx_date))),
nvl(ctlgd.gl_date,ct.trx_date)),
nvl(max(decode(adj.status,
''A'',adj.gl_date,
nvl(ctlgd.gl_date,
ct.trx_date))),
nvl(ctlgd.gl_date,ct.trx_date)),
nvl(:gl_date, nvl(ctlgd.gl_date, ct.trx_date))),
greatest(nvl(max(decode(ra.confirmed_flag,
''Y'', ra.apply_date,
null,
decode(ra.receivable_application_id,
null, ct.trx_date,
ra.apply_date),
ct.trx_date)),
ct.trx_date),
nvl(max(decode(adj.status,
''A'',adj.apply_date,
ct.trx_date)),
ct.trx_date),
nvl(:apply_date, ct.trx_date),
ct.trx_date),
nvl(:apply_date, ct.trx_date),
ctt_com.type,
/* :raagixlul, */
/* null, */
nvl(ps.amount_line_items_remaining,0),
ps.amount_due_remaining,
ps.acctd_amount_due_remaining,
nvl(ps.amount_adjusted,0),
c_ps.precision,
c_ps.minimum_accountable_unit,
ct_ps.exchange_rate,
ctl.customer_trx_id,
ct_ps.invoice_currency_code,
ctt_com.allocate_tax_freight,
DECODE(ctt_com.allocate_tax_freight, ''Y'', ''INVOICE'',''LINE''), /*1483656 - LINE or INVOICE */
nvl(ps.tax_remaining, 0),
nvl(ps.freight_remaining, 0),
ARPT_SQL_FUNC_UTIL.get_sum_of_trx_lines(ctl.customer_trx_id, ''TAX''),
ARPT_SQL_FUNC_UTIL.get_sum_of_trx_lines(ctl.customer_trx_id, ''FREIGHT'')
FROM
ra_cust_trx_types ctt,
ra_cust_trx_types ctt_com,
ra_cust_trx_line_gl_dist ctlgd_com,
ar_payment_schedules ps,
ar_receivable_applications ra,
ar_adjustments adj,
fnd_currencies c,
ra_terms t,
ra_terms_lines tl,
ra_customer_trx ct_com,
ra_customer_trx_lines ctl_com,
ra_customer_trx ct_ps,
fnd_currencies c_ps,
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist ctlgd
WHERE ct.customer_trx_id = :customer_trx_id
and ctl.customer_trx_id = ct.customer_trx_id
and ctlgd.customer_trx_id = ct.customer_trx_id
and ctlgd.account_class = ''REC''
and ctlgd.latest_rec_flag = ''Y''
and ctl.line_type = ''LINE''
and exists
(select ''x''
from ra_customer_trx trx
where trx.customer_trx_id = ctl.customer_trx_id)
and ctl.initial_customer_trx_line_id is not null
and ct.invoice_currency_code = c.currency_code
and ct.cust_trx_type_id = ctt.cust_trx_type_id
and ctt.type = ''INV''
and ctl.initial_customer_trx_line_id = ctl_com.customer_trx_line_id
and ctl_com.customer_trx_id = ct_com.customer_trx_id
and ctl_com.customer_trx_line_id = ctlgd_com.customer_trx_line_id
and ctlgd_com.account_class = ''REV''
and ct_com.cust_trx_type_id = ctt_com.cust_trx_type_id
and ps.customer_trx_id =
decode(ctt_com.type,
''DEP'', ctl.customer_trx_id,
ctl_com.customer_trx_id)
and ps.customer_trx_id = ct_ps.customer_trx_id
and ct_ps.invoice_currency_code = c_ps.currency_code
and ps.term_id = t.term_id
and ps.term_id = tl.term_id
and ps.terms_sequence_number = tl.sequence_num
and ps.customer_trx_id = ra.applied_customer_trx_id (+)
and ps.customer_trx_id = adj.customer_trx_id (+)
GROUP BY
ctlgd_com.code_combination_id,
ctl.customer_trx_id,
ctl_com.customer_trx_id,
ps.payment_schedule_id,
/* bugfix 2614759. comment out ctt.post_to_gl, */
ctt_com.type,
ctt_com.allocate_tax_freight, /*1483656*/
ct_com.customer_trx_id,
tl.relative_amount / t.base_amount,
c.precision,
c.minimum_accountable_unit,
ra.applied_customer_trx_id,
adj.customer_trx_id,
ctlgd.gl_date,
ct.trx_date,
ctlgd_com.gl_date,
ct_com.trx_date,
ctl.set_of_books_id,
c_ps.precision,
c_ps.minimum_accountable_unit,
ct_ps.exchange_rate,
ps.amount_line_items_remaining,
ps.tax_remaining,
ps.freight_remaining,
ps.amount_due_remaining,
ps.amount_adjusted,
ps.acctd_amount_due_remaining,
ps.terms_sequence_number,
ct_ps.invoice_currency_code
ORDER BY
ct_com.customer_trx_id,
ctl.customer_trx_id,
ps.terms_sequence_number';
debug(' select_sql = ' || CRLF ||
l_select_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(select_sql) = '||
to_char(length(l_select_sql)) || CRLF,
MSG_LEVEL_DEBUG);
l_insert_adj_sql :=
'INSERT INTO AR_ADJUSTMENTS
(
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
receivables_trx_id,
automatically_generated,
type,
adjustment_type,
status,
apply_date,
adjustment_id,
gl_date,
code_combination_id,
customer_trx_id,
payment_schedule_id,
subsequent_trx_id,
postable,
adjustment_number,
created_from,
posting_control_id,
amount,
acctd_amount,
line_adjusted,
tax_adjusted,
freight_adjusted
,org_id
)
VALUES
(
:user_id,
sysdate,
:user_id,
sysdate,
:login_id,
:request_id,
:application_id,
:program_id,
sysdate,
:set_of_books_id,
-1,
''Y'',
:adjust_type,
''C'',
''A'',
:trx_date,
:adjustment_id,
:gl_date,
:code_combination_id,
:adjusted_trx_id,
:payment_schedule_id,
:subsequent_trx_id,
:post_to_gl_flag,
to_char(ar_adjustment_number_s.nextval),
''RAXTRX'',
-3,
-1 * :adj_amount,
-1 * :acctd_adj_amount,
-1 * :line_adj_amount,
-1 * :tax_adj_amount,
-1 * :frt_adj_amount
,:org_id --arp_standard.sysparm.org_id /* SSA changes anuj */
)';
debug(' insert_adj_sql = ' || CRLF ||
l_insert_adj_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(insert_adj_sql) = '||
to_char(length(l_insert_adj_sql)) || CRLF,
MSG_LEVEL_DEBUG);
l_update_ps_sql :=
'UPDATE AR_PAYMENT_SCHEDULES
SET last_update_date = sysdate,
last_updated_by = :user_id,
last_update_login = :login_id,
status = decode(:amount_due_remaining, 0, ''CL'', ''OP''),
gl_date_closed =
decode(:amount_due_remaining, 0, :gl_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
actual_date_closed =
decode(:amount_due_remaining, 0, :actual_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
amount_due_remaining = :amount_due_remaining,
acctd_amount_due_remaining = :acctd_amount_due_remaining,
amount_line_items_remaining = :amount_line_items_remaining,
amount_adjusted = :amount_adjusted,
tax_remaining = :tax_remaining,
freight_remaining = :freight_remaining
WHERE payment_schedule_id = :payment_schedule_id';
debug(' update_ps_sql = ' || CRLF ||
l_update_ps_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(update_ps_sql) = '||
to_char(length(l_update_ps_sql)) || CRLF,
MSG_LEVEL_DEBUG);
debug( ' Parsing insert_adj_c', MSG_LEVEL_DEBUG );
p_insert_adj_c := dbms_sql.open_cursor;
dbms_sql.parse( p_insert_adj_c, l_insert_adj_sql,
dbms_sql.v7 );
debug( ' Parsing update_ps_c', MSG_LEVEL_DEBUG );
p_update_ps_c := dbms_sql.open_cursor;
dbms_sql.parse( p_update_ps_c, l_update_ps_sql,
dbms_sql.v7 );
debug( ' Parsing select_c', MSG_LEVEL_DEBUG );
p_select_c := dbms_sql.open_cursor;
dbms_sql.parse( p_select_c, l_select_sql,
dbms_sql.v7 );
PROCEDURE define_ups_select_columns(
p_select_c IN INTEGER,
p_select_rec IN select_ups_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()+' );
dbms_sql.define_column( p_select_c, 1, p_select_rec.set_of_books_id );
dbms_sql.define_column( p_select_c, 2, p_select_rec.total_line_amount );
dbms_sql.define_column( p_select_c, 3, p_select_rec.gl_date );
dbms_sql.define_column( p_select_c, 4, p_select_rec.code_combination_id );
dbms_sql.define_column( p_select_c, 5,
p_select_rec.adjusted_trx_id );
dbms_sql.define_column( p_select_c, 6,
p_select_rec.payment_schedule_id );
dbms_sql.define_column( p_select_c, 7,
p_select_rec.subsequent_trx_id );
dbms_sql.define_column( p_select_c, 8,
p_select_rec.post_to_gl_flag, 1 );
dbms_sql.define_column( p_select_c, 9,
p_select_rec.commitment_trx_id );
dbms_sql.define_column( p_select_c, 10, p_select_rec.percent );
dbms_sql.define_column( p_select_c, 11, p_select_rec.precision );
dbms_sql.define_column( p_select_c, 12, p_select_rec.min_acc_unit );
dbms_sql.define_column( p_select_c, 13, p_select_rec.gl_date_closed );
dbms_sql.define_column( p_select_c, 14, p_select_rec.actual_date_closed );
dbms_sql.define_column( p_select_c, 15, p_select_rec.trx_date );
dbms_sql.define_column( p_select_c, 16, p_select_rec.commitment_type, 20 );
dbms_sql.define_column( p_select_c, 17, p_select_rec.line_remaining );
dbms_sql.define_column( p_select_c, 18,
p_select_rec.amount_due_remaining );
dbms_sql.define_column( p_select_c, 19,
p_select_rec.acctd_amt_due_rem );
dbms_sql.define_column( p_select_c, 20,
p_select_rec.amount_adjusted );
dbms_sql.define_column( p_select_c, 21, p_select_rec.ps_precision );
dbms_sql.define_column( p_select_c, 22, p_select_rec.ps_min_acc_unit );
dbms_sql.define_column( p_select_c, 23, p_select_rec.ps_exchange_rate );
dbms_sql.define_column( p_select_c, 24, p_select_rec.customer_trx_id );
dbms_sql.define_column( p_select_c, 25,
p_select_rec.ps_currency_code, 15 );
dbms_sql.define_column( p_select_c, 26, p_select_rec.allocate_tax_freight,1 );
dbms_sql.define_column( p_select_c, 27, p_select_rec.adjustment_type,8 );
dbms_sql.define_column( p_select_c, 28, p_select_rec.tax_remaining );
dbms_sql.define_column( p_select_c, 29, p_select_rec.freight_remaining );
dbms_sql.define_column( p_select_c, 30, p_select_rec.total_tax_amount );
dbms_sql.define_column( p_select_c, 31, p_select_rec.total_freight_amount );
print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()-' );
debug('EXCEPTION: arp_maintain_ps2.define_ups_select_columns()',
MSG_LEVEL_BASIC);
END define_ups_select_columns;
PROCEDURE get_ups_column_values( p_select_c IN INTEGER,
p_select_rec IN OUT NOCOPY select_ups_rec_type ) IS
/* Bug 460927 - Changed IN to IN OUT in the above line - Oracle 8 */
BEGIN
print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()+' );
dbms_sql.column_value( p_select_c, 1, p_select_rec.set_of_books_id );
dbms_sql.column_value( p_select_c, 2, p_select_rec.total_line_amount );
dbms_sql.column_value( p_select_c, 3, p_select_rec.gl_date );
dbms_sql.column_value( p_select_c, 4, p_select_rec.code_combination_id );
dbms_sql.column_value( p_select_c, 5,
p_select_rec.adjusted_trx_id );
dbms_sql.column_value( p_select_c, 6, p_select_rec.payment_schedule_id );
dbms_sql.column_value( p_select_c, 7, p_select_rec.subsequent_trx_id );
dbms_sql.column_value( p_select_c, 8, p_select_rec.post_to_gl_flag );
dbms_sql.column_value( p_select_c, 9, p_select_rec.commitment_trx_id );
dbms_sql.column_value( p_select_c, 10, p_select_rec.percent );
dbms_sql.column_value( p_select_c, 11, p_select_rec.precision );
dbms_sql.column_value( p_select_c, 12, p_select_rec.min_acc_unit );
dbms_sql.column_value( p_select_c, 13, p_select_rec.gl_date_closed );
dbms_sql.column_value( p_select_c, 14, p_select_rec.actual_date_closed );
dbms_sql.column_value( p_select_c, 15, p_select_rec.trx_date );
dbms_sql.column_value( p_select_c, 16, p_select_rec.commitment_type );
dbms_sql.column_value( p_select_c, 17, p_select_rec.line_remaining );
dbms_sql.column_value( p_select_c, 18,
p_select_rec.amount_due_remaining );
dbms_sql.column_value( p_select_c, 19, p_select_rec.acctd_amt_due_rem );
dbms_sql.column_value( p_select_c, 20, p_select_rec.amount_adjusted );
dbms_sql.column_value( p_select_c, 21, p_select_rec.ps_precision );
dbms_sql.column_value( p_select_c, 22, p_select_rec.ps_min_acc_unit );
dbms_sql.column_value( p_select_c, 23, p_select_rec.ps_exchange_rate );
dbms_sql.column_value( p_select_c, 24, p_select_rec.customer_trx_id );
dbms_sql.column_value( p_select_c, 25, p_select_rec.ps_currency_code );
dbms_sql.column_value( p_select_c, 26, p_select_rec.allocate_tax_freight );
dbms_sql.column_value( p_select_c, 27, p_select_rec.adjustment_type );
dbms_sql.column_value( p_select_c, 28, p_select_rec.tax_remaining );
dbms_sql.column_value( p_select_c, 29, p_select_rec.freight_remaining );
dbms_sql.column_value( p_select_c, 30, p_select_rec.total_tax_amount );
dbms_sql.column_value( p_select_c, 31, p_select_rec.total_freight_amount );
PROCEDURE dump_ups_select_rec( p_select_rec IN select_ups_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()+' );
debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
|| p_select_rec.set_of_books_id, MSG_LEVEL_DEBUG );
|| p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.post_to_gl_flag, MSG_LEVEL_DEBUG );
|| p_select_rec.trx_date, MSG_LEVEL_DEBUG );
|| p_select_rec.gl_date, MSG_LEVEL_DEBUG );
|| p_select_rec.precision, MSG_LEVEL_DEBUG );
|| p_select_rec.min_acc_unit, MSG_LEVEL_DEBUG );
|| p_select_rec.adjusted_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.subsequent_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.commitment_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.commitment_type, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_currency_code, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_exchange_rate, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_precision, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_min_acc_unit, MSG_LEVEL_DEBUG );
|| p_select_rec.code_combination_id, MSG_LEVEL_DEBUG );
|| p_select_rec.gl_date_closed, MSG_LEVEL_DEBUG );
|| p_select_rec.actual_date_closed, MSG_LEVEL_DEBUG );
|| p_select_rec.total_line_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.payment_schedule_id, MSG_LEVEL_DEBUG );
|| p_select_rec.amount_due_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.acctd_amt_due_rem, MSG_LEVEL_DEBUG );
|| p_select_rec.line_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.amount_adjusted, MSG_LEVEL_DEBUG );
|| p_select_rec.percent, MSG_LEVEL_DEBUG );
|| p_select_rec.tax_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.freight_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.adjustment_type, MSG_LEVEL_DEBUG );
print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()-' );
debug( 'EXCEPTION: arp_maintain_ps2.dump_ups_select_rec()',
MSG_LEVEL_BASIC );
END dump_ups_select_rec;
p_insert_adj_c IN INTEGER,
p_update_ps_c IN INTEGER,
p_select_rec IN select_ups_rec_type,
p_number_records IN NUMBER,
p_ps_id_t IN id_table_type,
p_ps_amount_due_rem_t IN OUT NOCOPY number_table_type,
p_ps_acctd_amt_due_rem_t IN OUT NOCOPY number_table_type,
p_ps_line_rem_t IN OUT NOCOPY number_table_type,
p_ps_amount_adjusted_t IN OUT NOCOPY number_table_type,
p_adj_amount_t IN OUT NOCOPY number_table_type,
p_acctd_adj_amount_t IN OUT NOCOPY number_table_type,
p_percent_t IN number_table_type,
p_ps_tax_rem_t IN OUT NOCOPY number_table_type,
p_ps_freight_rem_t IN OUT NOCOPY number_table_type,
p_line_adj_t IN OUT NOCOPY number_table_type,
p_tax_adj_t IN OUT NOCOPY number_table_type,
p_frt_adj_t IN OUT NOCOPY number_table_type) IS
l_ignore INTEGER;
p_select_rec.commitment_trx_id,
null, -- class
g_oe_install_flag, -- oe_installed_flag
g_so_source_code -- so_source_code
);
IF (p_select_rec.commitment_type = 'DEP')
THEN
/* Bug 3431804, 3537233
Figure total amount remaining (L, T, and F) based on
amounts for each PS row (installment) */
FOR i IN p_ps_line_rem_t.FIRST..p_ps_line_rem_t.LAST LOOP
l_line_remaining := l_line_remaining + nvl(p_ps_line_rem_t(i),0);
Guarantees use SELECT in build_ups_sql differently.
The joins to PS are for the GUAR rather than the target
invoice. This means that we have to use the line totals
from ra_customer_trx_lines instead. */
l_line_remaining := p_select_rec.total_line_amount;
IF( p_select_rec.allocate_tax_freight = 'Y') THEN
/* Only DEPs can allocate tax and freight */
debug( ' allocating tax and freight (new logic)');
debug( ' total_invoice_amount='||p_select_rec.total_line_amount,
MSG_LEVEL_DEBUG );
debug( ' total lines = '|| p_select_rec.total_line_amount, MSG_LEVEL_DEBUG );
debug( ' total tax lines = '|| p_select_rec.total_tax_amount, MSG_LEVEL_DEBUG );
debug( ' total freight lines = '|| p_select_rec.total_freight_amount, MSG_LEVEL_DEBUG );
p_select_rec.ps_currency_code);
p_select_rec.ps_currency_code);
debug( ' total_line_amount='||p_select_rec.total_line_amount,
MSG_LEVEL_DEBUG );
IF (p_select_rec.commitment_type = 'DEP')
THEN
distribute_amount(
p_number_records,
p_select_rec.ps_currency_code,
l_total_line_adj,
l_line_percent_t,
p_line_adj_t);
p_select_rec.ps_currency_code,
l_total_line_adj,
p_percent_t,
p_line_adj_t);
IF( p_select_rec.allocate_tax_freight = 'Y') THEN
distribute_amount(
p_number_records,
p_select_rec.ps_currency_code,
l_total_tax_adj,
l_tax_percent_t,
p_tax_adj_t);
p_select_rec.ps_currency_code,
l_total_frt_adj,
l_frt_percent_t,
p_frt_adj_t);
IF (p_select_rec.allocate_tax_freight = 'Y') THEN
p_adj_amount_t(i) := p_adj_amount_t(i) + p_tax_adj_t(i)
+ p_frt_adj_t(i);
to prevent errors during insert of ADJ row */
p_tax_adj_t(i) := NULL;
p_select_rec.ps_exchange_rate,
'-', -- type
p_ps_amount_due_rem_t( i ), -- master_from
p_ps_acctd_amt_due_rem_t( i ), -- acctd_master_from
p_adj_amount_t( i ), -- detail
l_new_ps_adr, -- master_to
l_new_ps_acctd_adr, -- acctd_master_to
l_new_acctd_adj_amount -- acctd_detail
);
-- Update amt_due_rem, acctd_amt_due_rem
--------------------------------------------------------------------
p_ps_amount_due_rem_t( i ) := l_new_ps_adr;
debug( ' Binding insert_adj_c', MSG_LEVEL_DEBUG );
dbms_sql.bind_variable( p_insert_adj_c,
'user_id',
p_profile_info.user_id );
dbms_sql.bind_variable( p_insert_adj_c,
'login_id',
p_profile_info.conc_login_id );
dbms_sql.bind_variable( p_insert_adj_c,
'request_id',
p_profile_info.request_id );
dbms_sql.bind_variable( p_insert_adj_c,
'application_id',
p_profile_info.application_id );
dbms_sql.bind_variable( p_insert_adj_c,
'program_id',
p_profile_info.conc_program_id );
p_insert_adj_c,
'set_of_books_id',
p_select_rec.set_of_books_id );
dbms_sql.bind_variable( p_insert_adj_c,
'trx_date',
p_select_rec.trx_date );
dbms_sql.bind_variable( p_insert_adj_c,
'gl_date',
p_select_rec.gl_date );
dbms_sql.bind_variable( p_insert_adj_c,
'code_combination_id',
p_select_rec.code_combination_id );
dbms_sql.bind_variable( p_insert_adj_c,
'adjusted_trx_id',
p_select_rec.adjusted_trx_id );
dbms_sql.bind_variable( p_insert_adj_c,
'payment_schedule_id',
p_ps_id_t( i ) );
dbms_sql.bind_variable( p_insert_adj_c,
'subsequent_trx_id',
p_select_rec.subsequent_trx_id );
dbms_sql.bind_variable( p_insert_adj_c,
'post_to_gl_flag',
p_select_rec.post_to_gl_flag );
dbms_sql.bind_variable( p_insert_adj_c,
'adj_amount',
p_adj_amount_t( i ) );
dbms_sql.bind_variable( p_insert_adj_c,
'acctd_adj_amount',
p_acctd_adj_amount_t( i ) );
SELECT ar_adjustments_s.nextval
INTO l_adjustment_id
FROM dual;
dbms_sql.bind_variable( p_insert_adj_c,
'adjustment_id',
l_adjustment_id );
dbms_sql.bind_variable (p_insert_adj_c,
'tax_adj_amount',
p_tax_adj_t(i) );
dbms_sql.bind_variable (p_insert_adj_c,
'frt_adj_amount',
p_frt_adj_t(i) );
dbms_sql.bind_variable (p_insert_adj_c,
'line_adj_amount',
p_line_adj_t(i) );
dbms_sql.bind_variable (p_insert_adj_c,
'adjust_type',
p_select_rec.adjustment_type);
dbms_sql.bind_variable (p_insert_adj_c,
'org_id',
arp_standard.sysparm.org_id /* SSA changes anuj */);
debug( 'EXCEPTION: Error in binding insert_adj_c',
MSG_LEVEL_BASIC );
debug( ' Inserting adjustments', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( p_insert_adj_c );
debug( to_char(l_ignore) || ' row(s) inserted',
MSG_LEVEL_DEBUG );
| Call central MRC library for insertion |
| into MRC tables |
+-------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'INSERT',
p_table_name => 'AR_ADJUSTMENTS',
p_mode => 'SINGLE',
p_key_value => l_adjustment_id);
debug( 'EXCEPTION: Error executing insert ra stmt',
MSG_LEVEL_BASIC );
debug( ' Binding update_ps_c', MSG_LEVEL_DEBUG );
dbms_sql.bind_variable( p_update_ps_c,
'user_id',
p_profile_info.user_id );
dbms_sql.bind_variable( p_update_ps_c,
'login_id',
p_profile_info.conc_login_id );
dbms_sql.bind_variable( p_update_ps_c,
'amount_due_remaining',
p_ps_amount_due_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'gl_date_closed',
p_select_rec.gl_date_closed );
dbms_sql.bind_variable( p_update_ps_c,
'actual_date_closed',
p_select_rec.actual_date_closed );
dbms_sql.bind_variable( p_update_ps_c,
'amount_line_items_remaining',
p_ps_line_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'amount_adjusted',
p_ps_amount_adjusted_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'acctd_amount_due_remaining',
p_ps_acctd_amt_due_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'payment_schedule_id',
p_ps_id_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'tax_remaining',
p_ps_tax_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'freight_remaining',
p_ps_freight_rem_t( i ) );
debug( 'EXCEPTION: Error in binding update_ps_c',
MSG_LEVEL_BASIC );
l_ignore := dbms_sql.execute( p_update_ps_c );
debug( to_char(l_ignore) || ' row(s) updated',
MSG_LEVEL_DEBUG );
| Call central MRC library for update |
| of AR_PAYMENT_SCHEDULES |
+-------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'SINGLE',
p_key_value => p_ps_id_t( i ));
debug( 'EXCEPTION: Error executing update ps stmt',
MSG_LEVEL_BASIC );
l_ae_doc_rec.source_id_old := p_select_rec.code_combination_id;
PROCEDURE insert_child_adj_private(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_customer_trx_id IN BINARY_INTEGER,
p_adj_date IN DATE,
p_gl_date IN DATE) IS
l_ignore INTEGER;
l_select_rec select_ups_rec_type;
PROCEDURE load_tables( p_select_rec IN select_ups_rec_type ) IS
BEGIN
print_fcn_label2('arp_maintain_ps2.load_tables()+' );
l_ps_id_t( l_table_index ) := p_select_rec.payment_schedule_id;
p_select_rec.amount_due_remaining;
p_select_rec.acctd_amt_due_rem;
p_select_rec.line_remaining;
p_select_rec.tax_remaining;
p_select_rec.freight_remaining;
p_select_rec.amount_adjusted;
l_percent_t( l_table_index ) := p_select_rec.percent;
print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()+' );
IF( NOT( dbms_sql.is_open( ups_select_c ) AND
dbms_sql.is_open( ups_insert_adj_c ) AND
dbms_sql.is_open( ups_update_ps_c ) )) THEN
build_ups_sql(
system_info,
profile_info,
ups_select_c,
ups_insert_adj_c,
ups_update_ps_c );
define_ups_select_columns( ups_select_c, l_select_rec );
dbms_sql.bind_variable( ups_select_c,
'customer_trx_id',
p_customer_trx_id );
dbms_sql.bind_variable( ups_select_c,
'gl_date',
p_gl_date);
dbms_sql.bind_variable( ups_select_c,
'apply_date',
p_adj_date);
debug( ' Executing select sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( ups_select_c );
debug( 'EXCEPTION: Error executing select sql',
MSG_LEVEL_BASIC );
IF dbms_sql.fetch_rows( ups_select_c ) > 0 THEN
debug(' Fetched a row', MSG_LEVEL_DEBUG );
dbms_sql.column_value( ups_select_c, 24, l_customer_trx_id );
dbms_sql.column_value( ups_select_c, 5, l_adjusted_trx_id );
ups_insert_adj_c,
ups_update_ps_c,
l_select_rec,
l_table_index,
l_ps_id_t,
l_ps_amount_due_rem_t,
l_ps_acctd_amt_due_rem_t,
l_ps_line_rem_t,
l_ps_amount_adjusted_t,
l_adj_amount_t,
l_acctd_adj_amount_t,
l_percent_t,
l_ps_tax_rem_t,
l_ps_freight_rem_t,
l_line_adj_t,
l_tax_adj_t,
l_frt_adj_t);
get_ups_column_values( ups_select_c, l_select_rec );
dump_ups_select_rec( l_select_rec );
load_tables( l_select_rec );
ups_insert_adj_c,
ups_update_ps_c,
l_select_rec,
l_table_index,
l_ps_id_t,
l_ps_amount_due_rem_t,
l_ps_acctd_amt_due_rem_t,
l_ps_line_rem_t,
l_ps_amount_adjusted_t,
l_adj_amount_t,
l_acctd_adj_amount_t,
l_percent_t,
l_ps_tax_rem_t,
l_ps_freight_rem_t,
l_line_adj_t,
l_tax_adj_t,
l_frt_adj_t);
debug( 'EXCEPTION: Error fetching select cursor',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()-' );
debug( 'EXCEPTION: arp_maintain_ps2.insert_child_adj_private()',
MSG_LEVEL_BASIC );
END insert_child_adj_private;
/* Bug 4642526 - new wrapper for insert_child_adj_private */
PROCEDURE insert_child_adj_private(p_customer_trx_id IN BINARY_INTEGER,
p_adj_date IN DATE DEFAULT NULL,
p_gl_date IN DATE DEFAULT NULL )
IS
x_system_info arp_trx_global.system_info_rec_type;
insert_child_adj_private(x_system_info, x_profile_info,
p_customer_trx_id, p_adj_date, p_gl_date);
p_select_c IN OUT NOCOPY INTEGER,
p_insert_adj_c IN OUT NOCOPY INTEGER,
p_update_ps_c IN OUT NOCOPY INTEGER ) IS
l_insert_adj_sql VARCHAR2(2000);
l_update_ps_sql VARCHAR2(2000);
l_select_sql VARCHAR2(8000);
l_select_sql :=
'SELECT
ctl.set_of_books_id,
ct.trx_date,
/* nvl(ps.amount_line_items_remaining, 0), */
nvl(ctlgd.gl_date, ct.trx_date),
ctlgd_com.code_combination_id,
decode(ctt_com.type,
''DEP'', ct_inv.customer_trx_id,
ct_com.customer_trx_id),
ps.payment_schedule_id,
ctl.customer_trx_id,
''Y'', /* bugfix 2614759. ctt.post_to_gl */
greatest(nvl(max(decode(ra.confirmed_flag,
''Y'', ra.apply_date,
null, decode(ra.receivable_application_id,
null, ct.trx_date,
ra.apply_date),
ct.trx_date)),
ct.trx_date),
nvl(max(decode(adj.status,
''A'',adj.apply_date,
ct.trx_date)),
ct.trx_date),
ct.trx_date),
greatest(nvl(max(decode(ra.confirmed_flag,
''Y'', ra.gl_date,
null, decode(ra.receivable_application_id,
null, nvl(ctlgd.gl_date,
ct.trx_date),
ra.gl_date),
nvl(ctlgd.gl_date, ct.trx_date))),
nvl(ctlgd.gl_date,ct.trx_date)),
nvl(max(decode(adj.status,
''A'',adj.gl_date,
nvl(ctlgd.gl_date,
ct.trx_date))),
nvl(ctlgd.gl_date,ct.trx_date)),
nvl(ctlgd.gl_date, ct.trx_date)),
c.minimum_accountable_unit,
c.precision,
sum(ctl.extended_amount) /
(count(distinct ps.payment_schedule_id) *
count(distinct ps_inv.payment_schedule_id) *
count(distinct nvl(adj.adjustment_id, -9.9)) *
count(distinct nvl(adjd.adjustment_id, -9.9)) *
count(distinct nvl(ra.receivable_application_id, -9.9))),
nvl(sum(decode(adj.adjustment_type, ''C'', adj.amount, 0)),0) /
(count(distinct ps.payment_schedule_id) *
count(distinct ps_inv.payment_schedule_id) *
count(distinct ctl.customer_trx_line_id) *
count(distinct nvl(ra.receivable_application_id, -9.9))),
decode(ctt_com.type, ''DEP'', 1, 0),
/* nvl(ps.amount_line_items_remaining, 0), */
/* 0, */
ct_inv.customer_trx_id,
/* null, */
nvl(ps.amount_line_items_remaining, 0),
ps.amount_due_remaining,
ps.acctd_amount_due_remaining,
nvl(ps.amount_adjusted, 0),
c_ps.precision,
c_ps.minimum_accountable_unit,
decode(ctt_com.type, ''DEP'', ct_inv.exchange_rate, ct_com.exchange_rate),
sum(nvl(ps_inv.amount_line_items_remaining, 0))/
(count(distinct ps.payment_schedule_id) *
count(distinct ctl.customer_trx_line_id) *
count(distinct nvl(adj.adjustment_id, -9.9)) *
count(distinct nvl(ra.receivable_application_id, -9.9)) ),
nvl(ps.amount_line_items_original, 0),
/* 0 */
decode(ctt_com.type,
''DEP'', ct_inv.invoice_currency_code,
ct_com.invoice_currency_code),
/* 1483656 */
ctt_com.allocate_tax_freight,
/* adj_type */
DECODE(ctt_com.allocate_tax_freight, ''Y'', ''INVOICE'', ''LINE''),
/* CM TAX and CM FREIGHT totals */
ARPT_SQL_FUNC_UTIL.get_sum_of_trx_lines(ctl.customer_trx_id, ''TAX''),
ARPT_SQL_FUNC_UTIL.get_sum_of_trx_lines(ctl.customer_trx_id, ''FREIGHT''),
/* inv_line_adj, inv_tax_adj, inv_frt_adj */
sum(nvl(decode(adjd.adjustment_type, ''C'', adjd.line_adjusted, 0),0)) /
(count(distinct ps.payment_schedule_id) *
count(distinct ps_inv.payment_schedule_id) *
count(distinct ctl.customer_trx_line_id) *
count(distinct adj.adjustment_id) *
count(distinct nvl(ra.receivable_application_id, -9.9))),
sum(nvl(decode(adjd.adjustment_type, ''C'', adjd.tax_adjusted, 0),0)) /
(count(distinct ps.payment_schedule_id) *
count(distinct ps_inv.payment_schedule_id) *
count(distinct ctl.customer_trx_line_id) *
count(distinct adj.adjustment_id) *
count(distinct nvl(ra.receivable_application_id, -9.9))),
sum(nvl(decode(adjd.adjustment_type, ''C'', adjd.freight_adjusted, 0),0)) /
(count(distinct ps.payment_schedule_id) *
count(distinct ps_inv.payment_schedule_id) *
count(distinct ctl.customer_trx_line_id) *
count(distinct adj.adjustment_id) *
count(distinct nvl(ra.receivable_application_id, -9.9))),
NVL(ps.tax_remaining, 0),
NVL(ps.freight_remaining, 0),
NVL(ps.tax_original, 0),
NVL(ps.freight_original, 0)
FROM
ra_cust_trx_types ctt_com,
ra_cust_trx_types ctt,
ra_cust_trx_line_gl_dist ctlgd_com,
ar_payment_schedules ps,
ar_payment_schedules ps_inv,
ar_receivable_applications ra,
ar_adjustments adj,
ar_adjustments adjd,
fnd_currencies c,
fnd_currencies c_ps,
ra_customer_trx ct_com,
ra_customer_trx ct_inv,
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist ctlgd
WHERE ct.customer_trx_id = :customer_trx_id
and ctl.customer_trx_id = ct.customer_trx_id
and ctlgd.customer_trx_id = ct.customer_trx_id
and ctlgd.account_class = ''REC''
and ctlgd.latest_rec_flag = ''Y''
and ctl.line_type = ''LINE''
and exists
(select ''x''
from ra_customer_trx h
where h.customer_trx_id = ctl.customer_trx_id)
and ct.invoice_currency_code = c.currency_code
and ct.cust_trx_type_id = ctt.cust_trx_type_id
and ctt.type = ''CM''
and ctl.previous_customer_trx_id = ct_inv.customer_trx_id
and ct_inv.initial_customer_trx_id = ct_com.customer_trx_id
and ct_com.cust_trx_type_id = ctt_com.cust_trx_type_id
and ct_com.customer_trx_id = ctlgd_com.customer_trx_id
and ctlgd_com.account_class = ''REV''
and ps.customer_trx_id =
decode(ctt_com.type,
''DEP'', ct_inv.customer_trx_id,
ct_com.customer_trx_id)
and decode(ctt_com.type, ''DEP'', ct_inv.invoice_currency_code,
ct_com.invoice_currency_code)
= c_ps.currency_code
and ps.customer_trx_id = ra.applied_customer_trx_id (+)
and ps.customer_trx_id = adj.customer_trx_id (+)
and ct_inv.customer_trx_id = ps_inv.customer_trx_id
and decode(adj.subsequent_trx_id, null, ct_inv.customer_trx_id,
adj.subsequent_trx_id) = ct_inv.customer_trx_id
and ps.payment_schedule_id = adjd.payment_schedule_id (+)
and adjd.adjustment_type (+) = ''C''
and decode(ctt_com.type,''GUAR'', adjd.subsequent_trx_id,1)
= decode(ctt_com.type,''GUAR'',ctl.previous_customer_trx_id,1)
GROUP BY
ctl.set_of_books_id,
ctt_com.type,
ctt_com.allocate_tax_freight,
ctlgd.gl_date,
ct.trx_date,
ps.amount_line_items_remaining,
ctlgd_com.code_combination_id,
ct_inv.customer_trx_id,
ct_com.customer_trx_id,
ps.payment_schedule_id,
ctl.customer_trx_id,
/* Bugfix 2614759. comment out ctt.post_to_gl, */
c.minimum_accountable_unit,
c.precision,
ps.amount_due_remaining,
ps.amount_due_original,
ps.acctd_amount_due_remaining,
ps.amount_adjusted,
ps.tax_original,
ps.tax_remaining,
ps.freight_original,
ps.freight_remaining,
c_ps.precision,
c_ps.minimum_accountable_unit,
ct_inv.exchange_rate,
ct_com.exchange_rate,
ps.terms_sequence_number,
ps.amount_line_items_original,
ct_inv.invoice_currency_code,
ct_com.invoice_currency_code
ORDER BY
5 asc, /* adjusted_trx_id */
ct_inv.customer_trx_id,
ctl.customer_trx_id,
ps.terms_sequence_number';
debug(' select_sql = ' || CRLF ||
l_select_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(select_sql) = '||
to_char(length(l_select_sql)) || CRLF,
MSG_LEVEL_DEBUG);
l_insert_adj_sql :=
'INSERT INTO AR_ADJUSTMENTS
(
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
set_of_books_id,
receivables_trx_id,
automatically_generated,
type,
adjustment_type,
status,
apply_date,
adjustment_id,
gl_date,
code_combination_id,
customer_trx_id,
payment_schedule_id,
subsequent_trx_id,
postable,
adjustment_number,
created_from,
posting_control_id,
amount,
acctd_amount,
line_adjusted,
tax_adjusted,
freight_adjusted
,org_id
)
VALUES
(
:user_id,
sysdate,
:user_id,
sysdate,
:login_id,
:request_id,
:application_id,
:program_id,
sysdate,
:set_of_books_id,
-1,
''Y'',
:adj_type,
''C'',
''A'',
:trx_date,
:adjustment_id,
:gl_date,
:code_combination_id,
:adjusted_trx_id,
:payment_schedule_id,
:subsequent_trx_id,
:post_to_gl_flag,
to_char(ar_adjustment_number_s.nextval),
''RAXTRX'',
-3,
-1 * :adj_amount,
-1 * :acctd_adj_amount,
-1 * :line_adjusted,
-1 * :tax_adjusted,
-1 * :freight_adjusted
,:org_id --arp_standard.sysparm.org_id /* SSA changes anuj */
)';
debug(' insert_adj_sql = ' || CRLF ||
l_insert_adj_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(insert_adj_sql) = '||
to_char(length(l_insert_adj_sql)) || CRLF,
MSG_LEVEL_DEBUG);
l_update_ps_sql :=
'UPDATE AR_PAYMENT_SCHEDULES
SET last_update_date = sysdate,
last_updated_by = :user_id,
last_update_login = :login_id,
status = decode(:amount_due_remaining, 0, ''CL'', ''OP''),
gl_date_closed =
decode(:amount_due_remaining, 0, :gl_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
actual_date_closed =
decode(:amount_due_remaining, 0, :actual_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
amount_due_remaining = :amount_due_remaining,
acctd_amount_due_remaining = :acctd_amount_due_remaining,
amount_line_items_remaining = :amount_line_items_remaining,
amount_adjusted = :amount_adjusted,
tax_remaining = :tax_remaining,
freight_remaining = :freight_remaining
WHERE payment_schedule_id = :payment_schedule_id';
debug(' update_ps_sql = ' || CRLF ||
l_update_ps_sql || CRLF,
MSG_LEVEL_DEBUG);
debug(' len(update_ps_sql) = '||
to_char(length(l_update_ps_sql)) || CRLF,
MSG_LEVEL_DEBUG);
debug( ' Parsing insert_adj_c', MSG_LEVEL_DEBUG );
p_insert_adj_c := dbms_sql.open_cursor;
dbms_sql.parse( p_insert_adj_c, l_insert_adj_sql,
dbms_sql.v7 );
debug( ' Parsing update_ps_c', MSG_LEVEL_DEBUG );
p_update_ps_c := dbms_sql.open_cursor;
dbms_sql.parse( p_update_ps_c, l_update_ps_sql,
dbms_sql.v7 );
debug( ' Parsing select_c', MSG_LEVEL_DEBUG );
p_select_c := dbms_sql.open_cursor;
dbms_sql.parse( p_select_c, l_select_sql,
dbms_sql.v7 );
PROCEDURE define_iad_select_columns(
p_select_c IN INTEGER,
p_select_rec IN select_iad_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()+' );
dbms_sql.define_column( p_select_c, 1, p_select_rec.set_of_books_id );
dbms_sql.define_column( p_select_c, 2, p_select_rec.trx_date );
dbms_sql.define_column( p_select_c, 3, p_select_rec.gl_date );
dbms_sql.define_column( p_select_c, 4, p_select_rec.code_combination_id );
dbms_sql.define_column( p_select_c, 5, p_select_rec.adjusted_trx_id );
dbms_sql.define_column( p_select_c, 6, p_select_rec.payment_schedule_id );
dbms_sql.define_column( p_select_c, 7, p_select_rec.customer_trx_id );
dbms_sql.define_column( p_select_c, 8,
p_select_rec.post_to_gl_flag, 1 );
dbms_sql.define_column( p_select_c, 9, p_select_rec.actual_date_closed );
dbms_sql.define_column( p_select_c, 10, p_select_rec.gl_date_closed );
dbms_sql.define_column( p_select_c, 11, p_select_rec.min_acc_unit );
dbms_sql.define_column( p_select_c, 12, p_select_rec.precision );
dbms_sql.define_column( p_select_c, 13,
p_select_rec.total_cm_line_amount );
dbms_sql.define_column( p_select_c, 14,
p_select_rec.total_inv_adj_amount );
dbms_sql.define_column( p_select_c, 15, p_select_rec.commitment_code );
dbms_sql.define_column( p_select_c, 16, p_select_rec.invoice_trx_id );
dbms_sql.define_column( p_select_c, 17, p_select_rec.ps_line_remaining );
dbms_sql.define_column( p_select_c, 18,
p_select_rec.ps_amount_due_remaining );
dbms_sql.define_column( p_select_c, 19,
p_select_rec.ps_acctd_amt_due_rem );
dbms_sql.define_column( p_select_c, 20,
p_select_rec.ps_amount_adjusted );
dbms_sql.define_column( p_select_c, 21, p_select_rec.ps_precision );
dbms_sql.define_column( p_select_c, 22, p_select_rec.ps_min_acc_unit );
dbms_sql.define_column( p_select_c, 23, p_select_rec.ps_exchange_rate );
dbms_sql.define_column( p_select_c, 24,
p_select_rec.total_inv_line_remaining );
dbms_sql.define_column( p_select_c, 25, p_select_rec.ps_line_original );
dbms_sql.define_column( p_select_c, 26,
p_select_rec.ps_currency_code, 15 );
dbms_sql.define_column( p_select_c, 27, p_select_rec.allocate_tax_freight, 1);
dbms_sql.define_column( p_select_c, 28, p_select_rec.adjustment_type, 8);
dbms_sql.define_column( p_select_c, 29, p_select_rec.total_cm_tax_amount);
dbms_sql.define_column( p_select_c, 30, p_select_rec.total_cm_frt_amount);
dbms_sql.define_column( p_select_c, 31, p_select_rec.inv_line_adj);
dbms_sql.define_column( p_select_c, 32, p_select_rec.inv_tax_adj);
dbms_sql.define_column( p_select_c, 33, p_select_rec.inv_frt_adj);
dbms_sql.define_column( p_select_c, 34, p_select_rec.ps_tax_remaining);
dbms_sql.define_column( p_select_c, 35, p_select_rec.ps_freight_remaining);
dbms_sql.define_column( p_select_c, 36, p_select_rec.ps_tax_original);
dbms_sql.define_column( p_select_c, 37, p_select_rec.ps_freight_remaining);
print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()-' );
debug('EXCEPTION: arp_maintain_ps2.define_iad_select_columns()',
MSG_LEVEL_BASIC);
END define_iad_select_columns;
PROCEDURE get_iad_column_values( p_select_c IN INTEGER,
p_select_rec IN OUT NOCOPY select_iad_rec_type ) IS
/* Bug 460927 - Changed IN to IN OUT in the above line - Oracle 8 */
BEGIN
print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()+' );
dbms_sql.column_value( p_select_c, 1, p_select_rec.set_of_books_id );
dbms_sql.column_value( p_select_c, 2, p_select_rec.trx_date );
dbms_sql.column_value( p_select_c, 3, p_select_rec.gl_date );
dbms_sql.column_value( p_select_c, 4, p_select_rec.code_combination_id );
dbms_sql.column_value( p_select_c, 5, p_select_rec.adjusted_trx_id );
dbms_sql.column_value( p_select_c, 6, p_select_rec.payment_schedule_id );
dbms_sql.column_value( p_select_c, 7, p_select_rec.customer_trx_id );
dbms_sql.column_value( p_select_c, 8, p_select_rec.post_to_gl_flag );
dbms_sql.column_value( p_select_c, 9, p_select_rec.actual_date_closed );
dbms_sql.column_value( p_select_c, 10, p_select_rec.gl_date_closed );
dbms_sql.column_value( p_select_c, 11, p_select_rec.min_acc_unit );
dbms_sql.column_value( p_select_c, 12, p_select_rec.precision );
dbms_sql.column_value( p_select_c, 13, p_select_rec.total_cm_line_amount );
dbms_sql.column_value( p_select_c, 14, p_select_rec.total_inv_adj_amount );
dbms_sql.column_value( p_select_c, 15, p_select_rec.commitment_code );
dbms_sql.column_value( p_select_c, 16, p_select_rec.invoice_trx_id );
dbms_sql.column_value( p_select_c, 17, p_select_rec.ps_line_remaining );
dbms_sql.column_value( p_select_c, 18,
p_select_rec.ps_amount_due_remaining );
dbms_sql.column_value( p_select_c, 19,
p_select_rec.ps_acctd_amt_due_rem );
dbms_sql.column_value( p_select_c, 20,
p_select_rec.ps_amount_adjusted );
dbms_sql.column_value( p_select_c, 21, p_select_rec.ps_precision );
dbms_sql.column_value( p_select_c, 22, p_select_rec.ps_min_acc_unit );
dbms_sql.column_value( p_select_c, 23, p_select_rec.ps_exchange_rate );
dbms_sql.column_value( p_select_c, 24,
p_select_rec.total_inv_line_remaining );
dbms_sql.column_value( p_select_c, 25, p_select_rec.ps_line_original );
dbms_sql.column_value( p_select_c, 26, p_select_rec.ps_currency_code );
dbms_sql.column_value( p_select_c, 27, p_select_rec.allocate_tax_freight);
dbms_sql.column_value( p_select_c, 28, p_select_rec.adjustment_type);
dbms_sql.column_value( p_select_c, 29, p_select_rec.total_cm_tax_amount);
dbms_sql.column_value( p_select_c, 30, p_select_rec.total_cm_frt_amount);
dbms_sql.column_value( p_select_c, 31, p_select_rec.inv_line_adj);
dbms_sql.column_value( p_select_c, 32, p_select_rec.inv_tax_adj);
dbms_sql.column_value( p_select_c, 33, p_select_rec.inv_frt_adj);
dbms_sql.column_value( p_select_c, 34, p_select_rec.ps_tax_remaining);
dbms_sql.column_value( p_select_c, 35, p_select_rec.ps_freight_remaining);
dbms_sql.column_value( p_select_c, 36, p_select_rec.ps_tax_original);
dbms_sql.column_value( p_select_c, 37, p_select_rec.ps_freight_original);
PROCEDURE dump_iad_select_rec( p_select_rec IN select_iad_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()+' );
debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
|| p_select_rec.set_of_books_id, MSG_LEVEL_DEBUG );
|| p_select_rec.customer_trx_id , MSG_LEVEL_DEBUG );
|| p_select_rec.post_to_gl_flag, MSG_LEVEL_DEBUG );
|| p_select_rec.trx_date, MSG_LEVEL_DEBUG );
|| p_select_rec.gl_date, MSG_LEVEL_DEBUG );
|| p_select_rec.precision, MSG_LEVEL_DEBUG );
|| p_select_rec.min_acc_unit, MSG_LEVEL_DEBUG );
|| p_select_rec.adjusted_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.invoice_trx_id, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_currency_code, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_exchange_rate, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_precision, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_min_acc_unit, MSG_LEVEL_DEBUG );
|| p_select_rec.commitment_code, MSG_LEVEL_DEBUG );
|| p_select_rec.code_combination_id, MSG_LEVEL_DEBUG );
|| p_select_rec.gl_date_closed, MSG_LEVEL_DEBUG );
|| p_select_rec.actual_date_closed, MSG_LEVEL_DEBUG );
|| p_select_rec.total_cm_line_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_inv_adj_amount, MSG_LEVEL_DEBUG );
|| p_select_rec.total_inv_line_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.payment_schedule_id, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_amount_due_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_acctd_amt_due_rem, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_line_original, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_line_remaining, MSG_LEVEL_DEBUG );
|| p_select_rec.ps_amount_adjusted, MSG_LEVEL_DEBUG );
|| p_select_rec.allocate_tax_freight, MSG_LEVEL_DEBUG);
print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()-' );
debug( 'EXCEPTION: arp_maintain_ps2.dump_iad_select_rec()',
MSG_LEVEL_BASIC );
END dump_iad_select_rec;
p_insert_adj_c IN INTEGER,
p_update_ps_c IN INTEGER,
p_select_rec IN select_iad_rec_type,
p_number_records IN NUMBER,
p_ps_id_t IN id_table_type,
p_ps_amount_due_rem_t IN OUT NOCOPY number_table_type,
p_ps_acctd_amt_due_rem_t IN OUT NOCOPY number_table_type,
p_ps_line_orig_t IN number_table_type,
p_ps_line_rem_t IN OUT NOCOPY number_table_type,
p_ps_amount_adjusted_t IN OUT NOCOPY number_table_type,
p_adj_amount_t IN OUT NOCOPY number_table_type,
p_acctd_adj_amount_t IN OUT NOCOPY number_table_type,
p_eff_adj_line_total IN OUT NOCOPY NUMBER,
p_eff_adj_tax_total IN OUT NOCOPY NUMBER,
p_eff_adj_frt_total IN OUT NOCOPY NUMBER,
p_eff_line_bal IN OUT NOCOPY NUMBER,
p_eff_tax_bal IN OUT NOCOPY NUMBER,
p_eff_frt_bal IN OUT NOCOPY NUMBER,
p_line_adj_t IN OUT NOCOPY number_table_type,
p_tax_adj_t IN OUT NOCOPY number_table_type,
p_frt_adj_t IN OUT NOCOPY number_table_type,
p_ps_tax_orig_t IN OUT NOCOPY number_table_type,
p_ps_tax_rem_t IN OUT NOCOPY number_table_type,
p_ps_frt_orig_t IN OUT NOCOPY number_table_type,
p_ps_frt_rem_t IN OUT NOCOPY number_table_type,
p_inv_line_adj_t IN OUT NOCOPY number_table_type,
p_inv_tax_adj_t IN OUT NOCOPY number_table_type,
p_inv_frt_adj_t IN OUT NOCOPY number_table_type,
p_is_new_adj_trx IN boolean
) IS
l_ignore INTEGER;
d. INSERT adjustments
e. UPDATE payment schedules
f. INSERT distributions
*/
--------------------------------------------------------------------
-- Get total line orig/rem for adjusted ps
--------------------------------------------------------------------
FOR i IN 0..p_number_records - 1 LOOP
l_total_ps_line_rem := l_total_ps_line_rem + p_ps_line_rem_t( i );
IF (p_select_rec.allocate_tax_freight = 'Y') THEN
l_total_ps_orig := l_total_ps_orig + p_ps_tax_orig_t(i) +
p_ps_frt_orig_t(i);
IF (p_select_rec.allocate_tax_freight = 'Y') THEN
l_percent_t( i ) := (p_ps_line_rem_t( i ) + p_ps_tax_rem_t( i ) +
p_ps_frt_rem_t( i )) / l_total_ps_rem;
IF (p_select_rec.allocate_tax_freight = 'Y') THEN
l_percent_t( i ) := (p_ps_line_orig_t( i ) + p_ps_tax_orig_t( i ) +
p_ps_frt_orig_t( i )) / l_total_ps_orig;
debug( ' commitment_code='||p_select_rec.commitment_code,
MSG_LEVEL_DEBUG );
debug( ' allocate_tax_freight=' || p_select_rec.allocate_tax_freight,
MSG_LEVEL_DEBUG );
IF( p_select_rec.commitment_code = 0 ) THEN
----------------------------------------------------------------
-- GUAR case
----------------------------------------------------------------
debug( ' GUAR case', MSG_LEVEL_DEBUG );
p_select_rec.total_inv_line_remaining,
MSG_LEVEL_DEBUG );
debug( ' adj_trx_id='||p_select_rec.adjusted_trx_id,
MSG_LEVEL_DEBUG );
debug( ' invoice_trx_id=' || p_select_rec.invoice_trx_id,
MSG_LEVEL_DEBUG );
select nvl(sum(adj.line_adjusted),0)
into l_cm_adjustment_total
from ar_adjustments adj,
ra_customer_trx ocm
where adj.customer_trx_id = p_select_rec.adjusted_trx_id
and adj.subsequent_trx_id = ocm.customer_trx_id
and ocm.previous_customer_trx_id = p_select_rec.invoice_trx_id;
l_reversal_adj := GREATEST(p_select_rec.total_cm_line_amount,
l_max_curr_adj);
l_reversal_adj := LEAST(p_select_rec.total_cm_line_amount,
l_max_curr_adj);
-- Update invoice line balance
----------------------------------------------------------------
p_eff_line_bal := p_eff_line_bal +
p_select_rec.total_cm_line_amount;
p_select_rec.ps_currency_code,
l_reversal_adj,
l_percent_t,
p_line_adj_t );
ELSIF( p_select_rec.commitment_code = 1 ) THEN
----------------------------------------------------------------
-- DEP case
-- 1483656 - modified to allocate tax and freight
----------------------------------------------------------------
debug( ' DEP case', MSG_LEVEL_DEBUG );
p_select_rec.total_cm_line_amount +
(l_total_line_adj * -1) <= 0)
THEN
debug( ' l_total_ps_line_rem = '||
l_total_ps_line_rem, MSG_LEVEL_DEBUG );
debug( ' p_select_rec.total_cm_line = '||
p_select_rec.total_cm_line_amount, MSG_LEVEL_DEBUG );
DEBUG(' p_select_rec.total_cm_line_amount = ' ||
p_select_rec.total_cm_line_amount,
MSG_LEVEL_DEBUG);
IF( p_eff_line_bal + p_select_rec.total_cm_line_amount < 0 ) THEN
l_reversal_adj :=
GREATEST( p_eff_adj_line_total,
p_eff_line_bal +
p_select_rec.total_cm_line_amount );
p_select_rec.ps_currency_code,
l_reversal_adj,
l_percent_t,
p_line_adj_t );
p_select_rec.total_cm_line_amount;
IF (p_select_rec.allocate_tax_freight = 'Y') THEN
debug( ' ALLOCATING TAX AND FREIGHT', MSG_LEVEL_DEBUG);
debug( ' p_select_rec.total_cm_tax = '||p_select_rec.total_cm_tax_amount,
MSG_LEVEL_DEBUG );
p_select_rec.total_cm_tax_amount +
(l_total_tax_adj * -1) <= 0)
THEN
debug( ' REVERSING TAX', MSG_LEVEL_DEBUG );
DEBUG(' p_select_rec.total_cm_tax_amount = ' ||
p_select_rec.total_cm_tax_amount,
MSG_LEVEL_DEBUG);
IF( p_eff_tax_bal + p_select_rec.total_cm_tax_amount < 0 ) THEN
l_reversal_adj :=
GREATEST( p_eff_adj_tax_total,
p_eff_tax_bal +
p_select_rec.total_cm_tax_amount );
p_select_rec.ps_currency_code,
l_reversal_adj,
l_percent_t,
p_tax_adj_t );
p_select_rec.total_cm_tax_amount;
debug( ' p_select_rec.total_cm_frt = '||p_select_rec.total_cm_frt_amount,
MSG_LEVEL_DEBUG );
p_select_rec.total_cm_frt_amount +
(l_total_frt_adj * -1) <= 0 )
THEN
debug( ' REVERSING FREIGHT', MSG_LEVEL_DEBUG );
IF( p_eff_frt_bal + p_select_rec.total_cm_frt_amount < 0 ) THEN
l_reversal_adj :=
GREATEST( p_eff_adj_frt_total,
p_eff_frt_bal +
p_select_rec.total_cm_frt_amount );
p_select_rec.ps_currency_code,
l_reversal_adj,
l_percent_t,
p_frt_adj_t );
p_select_rec.total_cm_frt_amount;
IF (p_select_rec.allocate_tax_freight = 'Y') THEN
p_ps_tax_rem_t( i ) := p_ps_tax_rem_t( i ) - p_tax_adj_t( i );
p_select_rec.ps_exchange_rate,
'-', -- type
p_ps_amount_due_rem_t( i ), -- master_from
p_ps_acctd_amt_due_rem_t( i ), -- acctd_master_from
p_adj_amount_t( i ), -- detail
l_new_ps_adr, -- master_to
l_new_ps_acctd_adr, -- acctd_master_to
l_new_acctd_adj_amount -- acctd_detail
);
-- Update amt_due_rem, acctd_amt_due_rem
--------------------------------------------------------------------
p_ps_amount_due_rem_t( i ) := l_new_ps_adr;
debug( ' Binding insert_adj_c', MSG_LEVEL_DEBUG );
dbms_sql.bind_variable( p_insert_adj_c,
'user_id',
p_profile_info.user_id );
dbms_sql.bind_variable( p_insert_adj_c,
'login_id',
p_profile_info.conc_login_id );
dbms_sql.bind_variable( p_insert_adj_c,
'request_id',
p_profile_info.request_id );
dbms_sql.bind_variable( p_insert_adj_c,
'application_id',
p_profile_info.application_id );
dbms_sql.bind_variable( p_insert_adj_c,
'program_id',
p_profile_info.conc_program_id );
p_insert_adj_c,
'set_of_books_id',
p_select_rec.set_of_books_id );
dbms_sql.bind_variable( p_insert_adj_c,
'trx_date',
p_select_rec.trx_date );
dbms_sql.bind_variable( p_insert_adj_c,
'gl_date',
p_select_rec.gl_date );
dbms_sql.bind_variable( p_insert_adj_c,
'code_combination_id',
p_select_rec.code_combination_id );
dbms_sql.bind_variable( p_insert_adj_c,
'adjusted_trx_id',
p_select_rec.adjusted_trx_id );
dbms_sql.bind_variable( p_insert_adj_c,
'payment_schedule_id',
p_ps_id_t( i ) );
dbms_sql.bind_variable( p_insert_adj_c,
'subsequent_trx_id',
p_select_rec.customer_trx_id );
dbms_sql.bind_variable( p_insert_adj_c,
'post_to_gl_flag',
p_select_rec.post_to_gl_flag );
dbms_sql.bind_variable( p_insert_adj_c,
'adj_amount',
p_adj_amount_t( i ) );
dbms_sql.bind_variable( p_insert_adj_c,
'acctd_adj_amount',
p_acctd_adj_amount_t( i ) );
SELECT ar_adjustments_s.nextval
INTO l_adjustment_id
FROM dual;
dbms_sql.bind_variable( p_insert_adj_c,
'adjustment_id',
l_adjustment_id );
dbms_sql.bind_variable( p_insert_adj_c,
'adj_type',
p_select_rec.adjustment_type);
dbms_sql.bind_variable( p_insert_adj_c,
'line_adjusted',
p_line_adj_t(i));
dbms_sql.bind_variable( p_insert_adj_c,
'tax_adjusted',
p_tax_adj_t(i));
dbms_sql.bind_variable( p_insert_adj_c,
'freight_adjusted',
p_frt_adj_t(i));
dbms_sql.bind_variable( p_insert_adj_c,
'org_id',
arp_standard.sysparm.org_id);
debug( 'EXCEPTION: Error in binding insert_adj_c',
MSG_LEVEL_BASIC );
debug( ' Inserting adjustments', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( p_insert_adj_c );
debug( to_char(l_ignore) || ' row(s) inserted',
MSG_LEVEL_DEBUG );
| Call central MRC library for insertion |
| into MRC tables |
+-------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'INSERT',
p_table_name => 'AR_ADJUSTMENTS',
p_mode => 'SINGLE',
p_key_value => l_adjustment_id);
debug( 'EXCEPTION: Error executing insert ra stmt',
MSG_LEVEL_BASIC );
debug( ' Binding update_ps_c', MSG_LEVEL_DEBUG );
dbms_sql.bind_variable( p_update_ps_c,
'user_id',
p_profile_info.user_id );
dbms_sql.bind_variable( p_update_ps_c,
'login_id',
p_profile_info.conc_login_id );
dbms_sql.bind_variable( p_update_ps_c,
'amount_due_remaining',
p_ps_amount_due_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'gl_date_closed',
p_select_rec.gl_date_closed );
dbms_sql.bind_variable( p_update_ps_c,
'actual_date_closed',
p_select_rec.actual_date_closed );
dbms_sql.bind_variable( p_update_ps_c,
'amount_line_items_remaining',
p_ps_line_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'amount_adjusted',
p_ps_amount_adjusted_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'acctd_amount_due_remaining',
p_ps_acctd_amt_due_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'payment_schedule_id',
p_ps_id_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'tax_remaining',
p_ps_tax_rem_t( i ) );
dbms_sql.bind_variable( p_update_ps_c,
'freight_remaining',
p_ps_frt_rem_t( i ) );
debug( 'EXCEPTION: Error in binding update_ps_c',
MSG_LEVEL_BASIC );
l_ignore := dbms_sql.execute( p_update_ps_c );
debug( to_char(l_ignore) || ' row(s) updated',
MSG_LEVEL_DEBUG );
| Call central MRC library for update |
| of AR_PAYMENT_SCHEDULES |
+-------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'SINGLE',
p_key_value => p_ps_id_t( i ));
debug( 'EXCEPTION: Error executing update ps stmt',
MSG_LEVEL_BASIC );
l_ae_doc_rec.source_id_old := p_select_rec.code_combination_id;
PROCEDURE insert_cm_child_adj_private(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_customer_trx_id IN BINARY_INTEGER ) IS
l_ignore INTEGER;
l_select_rec select_iad_rec_type;
PROCEDURE load_tables( p_select_rec IN select_iad_rec_type ) IS
BEGIN
print_fcn_label2('arp_maintain_ps2.load_tables()+' );
l_ps_id_t( l_table_index ) := p_select_rec.payment_schedule_id;
p_select_rec.ps_amount_due_remaining;
p_select_rec.ps_acctd_amt_due_rem;
p_select_rec.ps_line_original;
p_select_rec.ps_line_remaining;
p_select_rec.ps_amount_adjusted;
p_select_rec.ps_tax_original;
p_select_rec.ps_tax_remaining;
p_select_rec.ps_freight_original;
p_select_rec.ps_freight_remaining;
p_select_rec.inv_line_adj;
p_select_rec.inv_tax_adj;
p_select_rec.inv_frt_adj;
print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()+' );
IF( NOT( dbms_sql.is_open( iad_select_c ) AND
dbms_sql.is_open( iad_insert_adj_c ) AND
dbms_sql.is_open( iad_update_ps_c ) )) THEN
build_iad_sql(
system_info,
profile_info,
iad_select_c,
iad_insert_adj_c,
iad_update_ps_c );
define_iad_select_columns( iad_select_c, l_select_rec );
dbms_sql.bind_variable( iad_select_c,
'customer_trx_id',
p_customer_trx_id );
debug( ' Executing select sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( iad_select_c );
debug( 'EXCEPTION: Error executing select sql',
MSG_LEVEL_BASIC );
IF dbms_sql.fetch_rows( iad_select_c ) > 0 THEN
debug(' Fetched a row', MSG_LEVEL_DEBUG );
dbms_sql.column_value( iad_select_c, 7, l_customer_trx_id );
dbms_sql.column_value( iad_select_c, 5, l_adjusted_trx_id );
dbms_sql.column_value( iad_select_c, 16, l_inv_trx_id );
iad_insert_adj_c,
iad_update_ps_c,
l_select_rec,
l_table_index,
l_ps_id_t,
l_ps_amount_due_rem_t,
l_ps_acctd_amt_due_rem_t,
l_ps_line_orig_t,
l_ps_line_rem_t,
l_ps_amount_adjusted_t,
l_adj_amount_t,
l_acctd_adj_amount_t,
l_eff_adj_line_total,
l_eff_adj_tax_total,
l_eff_adj_frt_total,
l_eff_line_bal,
l_eff_tax_bal,
l_eff_frt_bal,
l_line_adj_t,
l_tax_adj_t,
l_frt_adj_t,
l_ps_tax_orig_t,
l_ps_tax_rem_t,
l_ps_frt_orig_t,
l_ps_frt_rem_t,
l_inv_line_adj_t,
l_inv_tax_adj_t,
l_inv_frt_adj_t,
l_is_new_adj_trx );
/* dbms_sql.column_value( iad_select_c, 31,
l_eff_adj_line_total );
dbms_sql.column_value( iad_select_c, 32,
l_eff_adj_tax_total );
dbms_sql.column_value( iad_select_c, 33,
l_eff_adj_frt_total );
dbms_sql.column_value( iad_select_c, 24 ,
l_eff_line_bal );
dbms_sql.column_value( iad_select_c, 34 ,
l_eff_tax_bal );
dbms_sql.column_value( iad_select_c, 35,
l_eff_frt_bal );
get_iad_column_values( iad_select_c, l_select_rec );
dump_iad_select_rec( l_select_rec );
load_tables( l_select_rec );
iad_insert_adj_c,
iad_update_ps_c,
l_select_rec,
l_table_index,
l_ps_id_t,
l_ps_amount_due_rem_t,
l_ps_acctd_amt_due_rem_t,
l_ps_line_orig_t,
l_ps_line_rem_t,
l_ps_amount_adjusted_t,
l_adj_amount_t,
l_acctd_adj_amount_t,
l_eff_adj_line_total,
l_eff_adj_tax_total,
l_eff_adj_frt_total,
l_eff_line_bal,
l_eff_tax_bal,
l_eff_frt_bal,
l_line_adj_t,
l_tax_adj_t,
l_frt_adj_t,
l_ps_tax_orig_t,
l_ps_tax_rem_t,
l_ps_frt_orig_t,
l_ps_frt_rem_t,
l_inv_line_adj_t,
l_inv_tax_adj_t,
l_inv_frt_adj_t,
l_is_new_adj_trx );
debug( 'EXCEPTION: Error fetching select cursor',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()-' );
debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_child_adj_private()',
MSG_LEVEL_BASIC );
END insert_cm_child_adj_private;
build_ips_sql(system_info, profile_info, ips_select_c, ips_insert_ps_c);
ira_select_c,
ira_insert_ps_c,
ira_insert_ra_c,
ira_update_ps_c);
ups_select_c,
ups_insert_adj_c,
ups_update_ps_c);
iad_select_c,
iad_insert_adj_c,
iad_update_ps_c);
PROCEDURE test_insert_inv_ps(
p_customer_trx_id BINARY_INTEGER,
p_reversed_cash_receipt_id IN BINARY_INTEGER )
IS
BEGIN
insert_inv_ps_private(
system_info,
profile_info,
p_customer_trx_id,
p_reversed_cash_receipt_id
);
PROCEDURE test_ai_insert_inv_ps(
p_request_id BINARY_INTEGER,
p_select_sql VARCHAR2 )
IS
BEGIN
print_fcn_label( 'arp_maintain_ps2.test_ai_insert_inv_ps()+' );
ips_select_c,
ips_insert_ps_c );
dbms_sql.close_cursor( ips_select_c );
ips_select_c := dbms_sql.open_cursor;
debug(' select_sql='||p_select_sql );
debug(' parsing new select sql');
dbms_sql.parse( ips_select_c, p_select_sql, dbms_sql.v7 );
insert_inv_ps_private(
system_info,
profile_info,
p_request_id,
null);
print_fcn_label( 'arp_maintain_ps2.test_ai_insert_inv_ps()-' );
PROCEDURE test_insert_cm_ps( p_customer_trx_id BINARY_INTEGER )
IS
BEGIN
insert_cm_ps_private(system_info, profile_info, p_customer_trx_id);
PROCEDURE test_ai_insert_cm_ps(
p_request_id BINARY_INTEGER,
p_select_sql VARCHAR2 )
IS
BEGIN
print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_ps()+' );
ira_select_c,
ira_insert_ps_c,
ira_insert_ra_c,
ira_update_ps_c );
dbms_sql.close_cursor( ira_select_c );
ira_select_c := dbms_sql.open_cursor;
debug(' select_sql='||p_select_sql );
debug(' parsing new select sql');
dbms_sql.parse( ira_select_c, p_select_sql, dbms_sql.v7 );
insert_cm_ps_private(system_info, profile_info, p_request_id);
print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_ps()-' );
PROCEDURE test_insert_child_adj( p_customer_trx_id BINARY_INTEGER )
IS
BEGIN
insert_child_adj_private(system_info, profile_info, p_customer_trx_id);
PROCEDURE test_ai_insert_child_adj(
p_request_id BINARY_INTEGER,
p_select_sql VARCHAR2 )
IS
BEGIN
print_fcn_label( 'arp_maintain_ps2.test_ai_insert_child_adj()+' );
ups_select_c,
ups_insert_adj_c,
ups_update_ps_c );
dbms_sql.close_cursor( ups_select_c );
ups_select_c := dbms_sql.open_cursor;
debug(' select_sql='||p_select_sql );
debug(' parsing new select sql');
dbms_sql.parse( ups_select_c, p_select_sql, dbms_sql.v7 );
insert_child_adj_private(system_info, profile_info, p_request_id);
print_fcn_label( 'arp_maintain_ps2.test_ai_insert_child_adj()-' );
PROCEDURE test_insert_cm_child_adj( p_customer_trx_id BINARY_INTEGER )
IS
BEGIN
insert_cm_child_adj_private(system_info, profile_info, p_customer_trx_id);
PROCEDURE test_ai_insert_cm_child_adj(
p_request_id BINARY_INTEGER,
p_select_sql VARCHAR2 )
IS
BEGIN
print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_child_adj()+' );
iad_select_c,
iad_insert_adj_c,
iad_update_ps_c );
dbms_sql.close_cursor( iad_select_c );
iad_select_c := dbms_sql.open_cursor;
debug(' select_sql='||p_select_sql );
debug(' parsing new select sql');
dbms_sql.parse( iad_select_c, p_select_sql, dbms_sql.v7 );
insert_cm_child_adj_private(system_info, profile_info, p_request_id);
print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_child_adj()-' );