[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
close_cursor( arp_maintain_ps2.ips_insert_ps_c );
close_cursor( arp_maintain_ps2.ips_select_c );
close_cursor( arp_maintain_ps2.ira_insert_ps_c );
close_cursor( arp_maintain_ps2.ira_insert_ra_c );
close_cursor( arp_maintain_ps2.ira_update_ps_c );
close_cursor( arp_maintain_ps2.ira_select_c );
close_cursor( arp_maintain_ps2.ups_insert_adj_c );
close_cursor( arp_maintain_ps2.ups_update_ps_c );
close_cursor( arp_maintain_ps2.ups_select_c );
close_cursor( arp_maintain_ps2.iad_insert_adj_c );
close_cursor( arp_maintain_ps2.iad_update_ps_c );
close_cursor( arp_maintain_ps2.iad_select_c );
SELECT
ctt.type,
ctt.accounting_affect_flag,
ctt.post_to_gl,
decode(ctt.type,
'CM', decode(ct.previous_customer_trx_id,
null, 'Y','N'),
'N'), /* determine if onacct cm */
ct.previous_customer_trx_id,
ct.initial_customer_trx_id,
ctt_init.type
INTO
p_control_rec.trx_type,
l_open_rec,
l_post_to_gl,
l_onacct_cm,
p_control_rec.previous_customer_trx_id,
p_control_rec.initial_customer_trx_id,
p_control_rec.initial_trx_type
FROM
ra_cust_trx_types ctt_init,
ra_customer_trx ct_init,
ra_cust_trx_types ctt,
ra_customer_trx ct
WHERE ct.customer_trx_id = p_control_rec.customer_trx_id
and ct.cust_trx_type_id = ctt.cust_trx_type_id
and ct.initial_customer_trx_id = ct_init.customer_trx_id(+)
and ct_init.cust_trx_type_id = ctt_init.cust_trx_type_id(+);
debug( 'EXCEPTION: Error executing select stmt #1',
MSG_LEVEL_BASIC );
SELECT
ct_prev.initial_customer_trx_id,
ctt_init.type
INTO
p_control_rec.initial_customer_trx_id,
p_control_rec.initial_trx_type
FROM
ra_customer_trx ct,
ra_customer_trx ct_prev,
ra_customer_trx ct_init,
ra_cust_trx_types ctt_init
WHERE ct.customer_trx_id = p_control_rec.customer_trx_id
and ct.previous_customer_trx_id = ct_prev.customer_trx_id
and ct_prev.initial_customer_trx_id = ct_init.customer_trx_id(+)
and ct_init.cust_trx_type_id = ctt_init.cust_trx_type_id(+);
debug( 'EXCEPTION: Error executing select stmt #2',
MSG_LEVEL_BASIC );
SELECT 1
INTO l_adj_exists
FROM ar_adjustments
WHERE customer_trx_id = p_control_rec.previous_customer_trx_id
AND adjustment_type = 'C'
AND receivables_trx_id = -1
AND rownum = 1
UNION ALL
SELECT 1
FROM ar_adjustments
WHERE subsequent_trx_id = p_control_rec.previous_customer_trx_id
AND adjustment_type = 'C'
AND receivables_trx_id = -1
AND rownum = 1;
SELECT 1
INTO l_adj_exists
FROM ar_adjustments
WHERE customer_trx_id = p_control_rec.previous_customer_trx_id
AND adjustment_type = 'C'
AND receivables_trx_id = -1
AND rownum = 1;
SELECT 1
INTO l_adj_exists
FROM ar_adjustments
WHERE subsequent_trx_id = p_control_rec.previous_customer_trx_id
AND adjustment_type = 'C'
AND receivables_trx_id = -1
AND rownum = 1;
SELECT 1
INTO l_adj_exists
FROM ar_adjustments
WHERE customer_trx_id = p_control_rec.previous_customer_trx_id
AND subsequent_trx_id = p_control_rec.customer_trx_id
AND adjustment_type = 'C'
AND receivables_trx_id = -1
AND rownum = 1;
SELECT 1
INTO l_adj_exists
FROM ar_adjustments
WHERE customer_trx_id = p_control_rec.initial_customer_trx_id
AND subsequent_trx_id = p_control_rec.customer_trx_id
AND adjustment_type = 'C'
AND receivables_trx_id = -1
AND rownum = 1;
debug( 'EXCEPTION: Error executing select stmt for check for adjustments',
MSG_LEVEL_BASIC );
p_select_c IN OUT NOCOPY INTEGER ) IS
l_select_sql VARCHAR2(500);
l_select_sql :=
'SELECT
ct.set_of_books_id,
ct.trx_date,
rt.name
FROM
ra_customer_trx ct,
ar_receivables_trx rt
WHERE rt.receivables_trx_id = -1
and ct.customer_trx_id = :customer_trx_id';
p_select_c := dbms_sql.open_cursor;
dbms_sql.parse( p_select_c, l_select_sql,
dbms_sql.v7 );
PROCEDURE build_doc_insert_audit_sql(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_where_clause IN VARCHAR2,
p_insert_audit_c IN OUT NOCOPY INTEGER ) IS
l_insert_audit_sql VARCHAR2(1000);
print_fcn_label( 'arp_maintain_ps.build_doc_insert_audit_sql()+' );
l_insert_audit_sql :=
'INSERT INTO ar_doc_sequence_audit
(
doc_sequence_id,
doc_sequence_assignment_id,
doc_sequence_value,
creation_date,
created_by
)
SELECT
doc_sequence_id,
:sequence_assignment_id,
doc_sequence_value,
creation_date,
created_by
FROM AR_ADJUSTMENTS'||CRLF||p_where_clause;
p_insert_audit_c := dbms_sql.open_cursor;
dbms_sql.parse( p_insert_audit_c, l_insert_audit_sql,
dbms_sql.v7 );
print_fcn_label( 'arp_maintain_ps.build_doc_insert_audit_sql()-' );
debug( 'EXCEPTION: arp_maintain_ps.build_doc_insert_audit_sql()',
MSG_LEVEL_BASIC );
END build_doc_insert_audit_sql;
PROCEDURE build_doc_update_adj_sql(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_sequence_name IN VARCHAR2,
p_sequence_id IN BINARY_INTEGER,
p_where_clause IN VARCHAR2,
p_update_adj_c IN OUT NOCOPY INTEGER ) IS
l_update_adj_sql VARCHAR2(1000);
print_fcn_label( 'arp_maintain_ps.build_doc_update_adj_sql()+' );
l_update_adj_sql :=
'UPDATE ar_adjustments adj
SET
doc_sequence_value = ' || p_sequence_name || ',' || CRLF ||
'doc_sequence_id = ' || p_sequence_id || CRLF || p_where_clause;
p_update_adj_c := dbms_sql.open_cursor;
dbms_sql.parse( p_update_adj_c, l_update_adj_sql,
dbms_sql.v7 );
print_fcn_label( 'arp_maintain_ps.build_doc_update_adj_sql()-' );
debug( 'EXCEPTION: arp_maintain_ps.build_doc_update_adj_sql()',
MSG_LEVEL_BASIC );
END build_doc_update_adj_sql;
PROCEDURE update_adj_document_number(
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 BINARY_INTEGER,
p_bind1 BINARY_INTEGER,
p_bind2 BINARY_INTEGER,
p_bind3 BINARY_INTEGER,
p_update_where_clause VARCHAR2 ) IS
l_set_of_books_id BINARY_INTEGER;
l_update_where_clause varchar2(2000) default NULL;
select adjustment_id from ar_adjustments where customer_trx_id=cust_trx_id
UNION
select adjustment_id from ar_adjustments where subsequent_trx_id = cust_trx_id;
print_fcn_label( 'arp_maintain_ps.update_adj_document_number()+' );
IF( NOT( dbms_sql.is_open( doc_combo_select_c ) ) ) THEN
build_doc_combo_sql(
system_info,
profile_info,
doc_combo_select_c );
build_doc_insert_audit_sql(
system_info,
profile_info,
p_update_where_clause,
doc_insert_audit_c );
dbms_sql.bind_variable( doc_combo_select_c,
'customer_trx_id',
p_customer_trx_id );
dbms_sql.bind_variable( doc_insert_audit_c,':bind1',p_bind1);
dbms_sql.bind_variable( doc_insert_audit_c,':bind2',p_bind2);
dbms_sql.bind_variable( doc_insert_audit_c,':bind3',p_bind3);
dbms_sql.define_column( doc_combo_select_c, 1, l_set_of_books_id );
dbms_sql.define_column( doc_combo_select_c, 2, l_trx_date );
dbms_sql.define_column( doc_combo_select_c, 3, l_type, 30 );
debug( ' Executing select sql', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( doc_combo_select_c );
debug( 'EXCEPTION: Error executing select sql',
MSG_LEVEL_BASIC );
IF dbms_sql.fetch_rows( doc_combo_select_c ) > 0 THEN
debug(' Fetched a row', MSG_LEVEL_DEBUG );
dbms_sql.column_value( doc_combo_select_c,
1, l_set_of_books_id );
dbms_sql.column_value( doc_combo_select_c, 2, l_trx_date );
dbms_sql.column_value( doc_combo_select_c, 3, l_type );
adjustment record must not be updated */
IF ( l_dummy = -7 or l_sequence_id is NULL ) THEN
GOTO skip;
-- Update the ar_adjustments table
-----------------------------------------------------------
BEGIN
-------------------------------------------------------
-- Build update stmt
-------------------------------------------------------
--BugFix 2095183 Added the Following Statement in order to add
--an Extra condition for p_update_where_clause.
l_update_where_clause := p_update_where_clause || ' and adjustment_id = :adjustment_id ';
build_doc_update_adj_sql(
system_info,
profile_info,
l_sequence_value, --Bug 1508981 Modified to pass the value
l_sequence_id,
l_update_where_clause, --BugFix 2095183.Changed the parameter
doc_update_adj_c );
dbms_sql.bind_variable( doc_update_adj_c ,':bind1',p_bind1);
dbms_sql.bind_variable( doc_update_adj_c ,':bind2',p_bind2);
dbms_sql.bind_variable( doc_update_adj_c ,':bind3',p_bind3);
dbms_sql.bind_variable( doc_update_adj_c ,':adjustment_id',adj_rec.adjustment_id);
l_ignore := dbms_sql.execute( doc_update_adj_c );
/* MRC call not required because update only affects
doc_sequence_value and doc_sequence_id - Ie. no
accounting columns */
debug( to_char(l_ignore) || ' row(s) updated',
MSG_LEVEL_DEBUG );
-- Insert into the audit table: ar_doc_sequence_audit
-----------------------------------------------------------
BEGIN
-------------------------------------------------------
-- Bind variables
-------------------------------------------------------
dbms_sql.bind_variable(
doc_insert_audit_c,
'sequence_assignment_id',
l_sequence_assignment_id );
l_ignore := dbms_sql.execute( doc_insert_audit_c );
debug( to_char(l_ignore) || ' row(s) inserted',
MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error inserting audit table',
MSG_LEVEL_BASIC );
debug( 'EXCEPTION: Error fetching select cursor',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps.update_adj_document_number()-' );
debug( 'EXCEPTION: arp_maintain_ps.update_adj_document_number()',
MSG_LEVEL_BASIC );
END update_adj_document_number;
PROCEDURE delete_payment_schedule( p_customer_trx_id IN BINARY_INTEGER ) IS
l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
print_fcn_label( 'arp_maintain_ps.delete_payment_schedule()+' );
DELETE
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id
RETURNING ps.payment_schedule_id
BULK COLLECT INTO l_ar_ps_key_value_list;
debug( SQL%ROWCOUNT||' row(s) deleted', MSG_LEVEL_DEBUG );
p_event_mode => 'DELETE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'BATCH',
p_key_value_list => l_ar_ps_key_value_list);
debug( 'EXCEPTION: Error executing delete stmt',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps.delete_payment_schedule()-' );
debug( 'EXCEPTION: arp_maintain_ps.delete_payment_schedule()',
MSG_LEVEL_BASIC );
END delete_payment_schedule;
PROCEDURE delete_applications( p_customer_trx_id IN BINARY_INTEGER ) IS
CURSOR del_app IS
select app.receivable_application_id app_id,
app.customer_trx_id trx_id
from ar_receivable_applications app
where app.customer_trx_id = p_customer_trx_id
and nvl(app.confirmed_flag,'Y') = 'Y' --accounting exists in ar_distributions only if confirmed
and exists (select 'x'
from ar_distributions ard
where ard.source_table = 'RA'
and ard.source_id = app.receivable_application_id); --delete only necessary records
print_fcn_label( 'arp_maintain_ps.delete_applications()+' );
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
DELETE
FROM ar_receivable_applications ra
WHERE ra.customer_trx_id = p_customer_trx_id
RETURNING receivable_application_id
BULK COLLECT INTO l_rec_app_key_value_list;
debug( SQL%ROWCOUNT||' row(s) deleted', MSG_LEVEL_DEBUG );
p_event_mode => 'DELETE',
p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
p_mode => 'BATCH',
p_key_value_list => l_rec_app_key_value_list);
debug( 'EXCEPTION: Error executing delete stmt',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps.delete_applications()-' );
debug( 'EXCEPTION: arp_maintain_ps.delete_applications()',
MSG_LEVEL_BASIC );
END delete_applications;
PROCEDURE delete_adjustments(
p_customer_trx_id IN BINARY_INTEGER,
p_subsequent_trx_id IN BINARY_INTEGER
) IS
l_adj_key_value_list gl_ca_utility_pkg.r_key_value_arr;
print_fcn_label( 'arp_maintain_ps.delete_adjustments()+' );
DELETE
FROM ar_adjustments adj
WHERE adj.customer_trx_id = p_customer_trx_id
and adj.receivables_trx_id = -1
RETURNING adjustment_id
BULK COLLECT INTO l_adj_key_value_list;
p_event_mode => 'DELETE',
p_table_name => 'AR_ADJUSTMENTS',
p_mode => 'BULK',
p_key_value_list => l_adj_key_value_list);
DELETE
FROM ar_adjustments adj
WHERE adj.customer_trx_id = p_customer_trx_id
and adj.subsequent_trx_id = p_subsequent_trx_id
and adj.receivables_trx_id = -1
RETURNING adjustment_id
BULK COLLECT INTO l_adj_key_value_list;
p_event_mode => 'DELETE',
p_table_name => 'AR_ADJUSTMENTS',
p_mode => 'BULK',
p_key_value_list => l_adj_key_value_list);
debug( SQL%ROWCOUNT||' row(s) deleted', MSG_LEVEL_DEBUG );
debug( 'EXCEPTION: Error executing delete stmt',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps.delete_adjustments()-' );
debug( 'EXCEPTION: arp_maintain_ps.delete_adjustments()',
MSG_LEVEL_BASIC );
END delete_adjustments;
UPDATE ar_payment_schedules ps
SET (
ps.status,
ps.gl_date_closed,
ps.actual_date_closed,
ps.amount_adjusted,
ps.amount_due_remaining,
ps.acctd_amount_due_remaining,
ps.amount_line_items_remaining,
ps.tax_remaining,
ps.freight_remaining,
ps.receivables_charges_remaining,
last_updated_by,
last_update_date,
last_update_login) = (
SELECT
decode(ps2.amount_due_remaining - adj.amount, 0, 'CL', 'OP'),
decode(ps2.amount_due_remaining - adj.amount,
0,
greatest(nvl(max(decode(ra2.confirmed_flag,
'N', ps2.gl_date,
ra2.gl_date)),
ps2.gl_date),
max(decode(adj2.customer_trx_id,
p_customer_trx_id,
decode(adj2.subsequent_trx_id,
p_subsequent_trx_id,
decode( adj2.receivables_trx_id,
-1, ps2.gl_date,
adj2.gl_date ),
adj2.gl_date),
adj2.gl_date)
)
),
''),
decode(ps2.amount_due_remaining - adj.amount,
0,
greatest(nvl(max(decode(ra2.confirmed_flag,
'N', ps2.trx_date,
ra2.apply_date)),
ps2.trx_date),
max(decode(adj2.customer_trx_id,
p_customer_trx_id,
decode(adj2.subsequent_trx_id,
p_subsequent_trx_id,
decode(adj2.receivables_trx_id,
-1, ps2.trx_date,
adj2.apply_date),
adj2.apply_date),
adj2.apply_date)
)
),
''),
nvl(ps2.amount_adjusted, 0) - adj.amount,
ps2.amount_due_remaining - adj.amount,
ps2.acctd_amount_due_remaining - adj.acctd_amount,
nvl(ps2.amount_line_items_remaining, 0) -
nvl(adj.line_adjusted, decode(adj.type, 'LINE', adj.amount, 0)),
nvl(ps2.tax_remaining, 0) -
nvl(adj.tax_adjusted, decode(adj.type, 'TAX', adj.amount, 0)),
nvl(ps2.freight_remaining, 0) -
nvl(adj.freight_adjusted,
decode(adj.type, 'FREIGHT', adj.amount, 0)),
nvl(ps2.receivables_charges_remaining, 0) -
nvl(adj.receivables_charges_adjusted,
decode(adj.type, 'CHARGES', adj.amount, 0)),
p_profile_info.user_id,
trunc(sysdate),
p_profile_info.conc_login_id
FROM
ar_adjustments adj,
ar_payment_schedules ps2,
ar_adjustments adj2,
ar_receivable_applications ra2
WHERE adj.receivables_trx_id =-1
and adj.customer_trx_id = p_customer_trx_id
and adj.subsequent_trx_id = p_subsequent_trx_id
and adj.payment_schedule_id = ps2.payment_schedule_id
and ps2.payment_schedule_id = ps.payment_schedule_id
and ps2.payment_schedule_id = adj2.payment_schedule_id
and adj2.status = 'A'
and ps2.payment_schedule_id = ra2.applied_payment_schedule_id(+)
GROUP BY
ps2.payment_schedule_id,
ra2.applied_payment_schedule_id,
adj2.payment_schedule_id,
ps2.amount_due_remaining,
adj.amount,
ps2.gl_date,
ps2.trx_date,
ps2.amount_adjusted,
ps2.acctd_amount_due_remaining,
adj.acctd_amount,
ps2.amount_line_items_remaining,
adj.line_adjusted,
adj.type,
ps2.tax_remaining,
adj.tax_adjusted,
ps2.freight_remaining,
adj.freight_adjusted,
ps2.receivables_charges_remaining,
adj.receivables_charges_adjusted )
WHERE ps.payment_schedule_id in
(
SELECT
adj3.payment_schedule_id
FROM ar_adjustments adj3
WHERE adj3.customer_trx_id = p_customer_trx_id
and adj3.subsequent_trx_id = p_subsequent_trx_id
and adj3.receivables_trx_id = -1
)
RETURNING ps.payment_schedule_id
BULK COLLECT INTO l_ar_ps_key_value_list;
debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'BATCH',
p_key_value_list => l_ar_ps_key_value_list);
debug( 'EXCEPTION: Error executing update stmt',
MSG_LEVEL_BASIC );
/* VAT changes: delete acct entry */
/* bug 2808262. Changed the code to have a cursor */
DECLARE
CURSOR c1 IS SELECT adj.adjustment_id
FROM ar_adjustments adj
WHERE adj.customer_trx_id = p_customer_trx_id
AND adj.receivables_trx_id = -1;
delete_adjustments( p_customer_trx_id, p_subsequent_trx_id );
UPDATE ar_payment_schedules ps
SET (
status,
gl_date_closed,
actual_date_closed,
amount_credited,
amount_due_remaining,
acctd_amount_due_remaining,
amount_line_items_remaining,
tax_remaining,
freight_remaining,
receivables_charges_remaining,
last_updated_by,
last_update_date,
last_update_login) = (
SELECT
decode(ps2.amount_due_remaining + ra.amount_applied,0,'CL','OP'),
decode(ps2.amount_due_remaining + ra.amount_applied,
0,
greatest(max(decode(ra2.customer_trx_id,
p_customer_trx_id, ps2.gl_date,
ra2.gl_date)),
max(decode(adj2.status,
'A', adj2.gl_date,
ps2.gl_date))),
to_date('31-12-4712','DD-MM-YYYY')),--Added default date 31-12-4712 as per Bug:5514315
decode(ps2.amount_due_remaining + ra.amount_applied,
0,
greatest(max(decode(ra2.customer_trx_id,
p_customer_trx_id, ps2.trx_date,
ra2.apply_date)),
max(decode(adj2.status,
'A', adj2.apply_date,
ps2.trx_date))),
to_date('31-12-4712','DD-MM-YYYY')),--Added default date 31-12-4712 as per Bug:5514315
nvl(ps2.amount_credited, 0) + ra.amount_applied,
ps2.amount_due_remaining + ra.amount_applied,
ps2.acctd_amount_due_remaining + nvl(ra.acctd_amount_applied_to, 0),
nvl(ps2.amount_line_items_remaining, 0) + nvl(ra.line_applied, 0),
nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied,0),
nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0),
nvl(ps2.receivables_charges_remaining, 0) +
nvl(ra.receivables_charges_applied, 0),
p_profile_info.user_id,
trunc(sysdate),
p_profile_info.conc_login_id
FROM
ar_receivable_applications ra,
ar_payment_schedules ps2,
ar_adjustments adj2,
ar_receivable_applications ra2
WHERE ra.customer_trx_id = p_customer_trx_id
and ra.status||'' = 'APP'
and ra.applied_payment_schedule_id = ps2.payment_schedule_id
and ps2.payment_schedule_id =ps.payment_schedule_id
and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
and nvl(ra2.confirmed_flag,'Y')= 'Y'
GROUP BY
ps2.payment_schedule_id,
ra2.applied_payment_schedule_id,
adj2.payment_schedule_id,
ps2.amount_due_remaining,
ra.amount_applied,
ps2.gl_date,
ps2.trx_date,
ps2.amount_credited,
ps2.acctd_amount_due_remaining,
ra.acctd_amount_applied_to,
ps2.amount_line_items_remaining,
ra.line_applied,
ps2.tax_remaining,
ra.tax_applied,
ps2.freight_remaining,
ra.freight_applied,
ps2.receivables_charges_remaining,
ra.receivables_charges_applied)
WHERE ps.payment_schedule_id in
(
SELECT ra3.applied_payment_schedule_id
FROM ar_receivable_applications ra3
WHERE ra3.customer_trx_id = p_customer_trx_id
and ra3.status='APP'
)
RETURNING ps.payment_schedule_id
BULK COLLECT INTO l_ar_ps_key_value_list;
debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'BATCH',
p_key_value_list => l_ar_ps_key_value_list);
debug( 'EXCEPTION: Error executing update stmt',
MSG_LEVEL_BASIC );
PROCEDURE update_payment_schedule(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_control IN control_rec_type
) IS
--BUG#5324129
CURSOR del_app(p_app_id IN NUMBER) IS
select app.receivable_application_id app_id,
app.customer_trx_id trx_id
from ar_receivable_applications app
where app.applied_payment_schedule_id = p_control.payment_schedule_id --inv ps
and app.customer_trx_id = p_control.customer_trx_id --cm trx id
and nvl(app.confirmed_flag,'Y') = 'Y' --accounting exists in ar_distributions only if confirmed
and app.reversal_gl_date IS NULL
and app.receivable_application_id = p_app_id
and exists (select 'x'
from ar_distributions ard
where ard.source_table = 'RA'
and ard.source_id = app.receivable_application_id); --delete only necessary records
select app.receivable_application_id app_id,
app.customer_trx_id trx_id
from ar_receivable_applications app
where app.applied_payment_schedule_id = p_control.payment_schedule_id
and app.customer_trx_id = p_control.customer_trx_id
and nvl(app.confirmed_flag,'Y') = 'Y'
and app.reversal_gl_date IS NULL
and app.receivable_application_id = p_app_id
and not exists (select 'x'
from ar_distributions ard
where ard.source_table = 'RA'
and ard.source_id = app.receivable_application_id);
select *
from ar_receivable_applications
where applied_payment_schedule_id = p_control.payment_schedule_id
and customer_trx_id = p_control.customer_trx_id
and nvl(confirmed_flag,'Y') = 'Y'
and reversal_gl_date IS NULL;
select app.receivable_application_id,
app.amount_applied
from ar_receivable_applications app
where app.applied_payment_schedule_id = p_control.payment_schedule_id
and app.customer_trx_id = p_control.customer_trx_id
and app.receivable_application_id = p_app_id;
print_fcn_label( 'arp_maintain_ps.update_payment_schedule()+' );
SELECT
ps_cm.amount_due_remaining - ra.amount_applied,
ps_cm.acctd_amount_due_remaining - ra.acctd_amount_applied_from,
ps_cm.exchange_rate,
-( p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
ps_inv.amount_due_remaining + ra.amount_applied,
ps_inv.acctd_amount_due_remaining + ra.acctd_amount_applied_to,
ps_inv.exchange_rate
INTO
l_cm_adr,
l_cm_acctd_adr,
l_cm_rate,
l_new_amount_applied,
l_inv_adr,
l_inv_acctd_adr,
l_inv_rate
FROM
ar_payment_schedules ps_cm,
ar_payment_schedules ps_inv,
ar_receivable_applications ra
WHERE p_system_info.base_currency <> ps_inv.invoice_currency_code
and ra.applied_payment_schedule_id = ps_inv.payment_schedule_id
and ps_inv.payment_schedule_id = p_control.payment_schedule_id
and ra.payment_schedule_id = ps_cm.payment_schedule_id
and ps_cm.customer_trx_id = p_control.customer_trx_id
and ra.reversal_gl_date IS NULL
and ra.receivable_application_id = old_rec_app.receivable_application_id;
debug( 'EXCEPTION: Error executing select stmt',
MSG_LEVEL_BASIC );
UPDATE ar_payment_schedules ps
SET (
ps.status,
ps.gl_date_closed,
ps.actual_date_closed,
ps.amount_credited,
ps.amount_due_remaining,
ps.acctd_amount_due_remaining,
ps.amount_line_items_remaining,
ps.tax_remaining,
ps.freight_remaining,
ps.receivables_charges_remaining,
ps.last_updated_by,
ps.last_update_date,
ps.last_update_login) = (
SELECT
decode( ps2.amount_due_remaining + ra.amount_applied +
(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
0, 'CL', 'OP'),
decode( ps2.amount_due_remaining + ra.amount_applied +
(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
0,
greatest(max(ra2.gl_date), max(decode(adj2.status,
'A', adj2.gl_date,
ps2.gl_date))),
''),
decode(ps2.amount_due_remaining + ra.amount_applied +
(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
0, greatest(max(ra2.apply_date),
max(decode(adj2.status,
'A', adj2.apply_date,
ps2.trx_date))),
''),
nvl(ps2.amount_credited, 0) + ra.amount_applied +
(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
ps2.amount_due_remaining + ra.amount_applied +
(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
decode(l_foreign_transaction,
'N',
ps2.amount_due_remaining + ra.amount_applied +
(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
'Y', to_number(nvl(l_new_inv_acctd_adr, 0))),
nvl(ps2.amount_line_items_remaining, 0) + nvl(ra.line_applied, 0) +
p_control.line_amount,
nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied, 0) +
p_control.tax_amount,
nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0) +
p_control.freight_amount,
nvl(ps2.receivables_charges_remaining, 0) +
nvl(ra.receivables_charges_applied, 0) +
p_control.charge_amount,
p_profile_info.user_id,
trunc(sysdate),
p_profile_info.conc_login_id
FROM
ar_receivable_applications ra,
ar_payment_schedules ps2,
ar_receivable_applications ra2,
ar_adjustments adj2
WHERE ra.customer_trx_id = p_control.customer_trx_id
and ra.status||'' = 'APP'
and ra.reversal_gl_date IS NULL
and ra.applied_payment_schedule_id = ps2.payment_schedule_id
and ps.payment_schedule_id = ps2.payment_schedule_id
and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
and ra2.reversal_gl_date IS NULL
and nvl(ra2.confirmed_flag, 'Y') = 'Y'
and ra.receivable_application_id = old_rec_app.receivable_application_id
GROUP BY
ps2.payment_schedule_id,
ra2.applied_payment_schedule_id,
adj2.payment_schedule_id,
ps2.amount_due_remaining,
ra.amount_applied,
ps2.gl_date,
ps2.trx_date,
ps2.amount_credited,
ps2.acctd_amount_due_remaining,
ra.acctd_amount_applied_to,
ps2.amount_line_items_remaining,
ra.line_applied,
ps2.tax_remaining,
ra.tax_applied,
ps2.freight_remaining,
ra.freight_applied,
ps2.receivables_charges_remaining,
ra.receivables_charges_applied,
ps2.exchange_rate)
WHERE ps.payment_schedule_id in
(
SELECT ra3.applied_payment_schedule_id
FROM ar_receivable_applications ra3
WHERE ra3.customer_trx_id = p_control.customer_trx_id
and ra3.status = 'APP'
and ra3.applied_payment_schedule_id = p_control.payment_schedule_id
and ra3.reversal_gl_date IS NULL
)
RETURNING payment_schedule_id
BULK COLLECT INTO l_ar_ps_key_value_list;
debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'BATCH',
p_key_value_list => l_ar_ps_key_value_list);
debug( 'EXCEPTION: Error executing update stmt #1',
MSG_LEVEL_BASIC );
arp_standard.debug('Path Update CM RA and recreate distributions');
arp_standard.debug('1 Delete current RA distributions');
arp_standard.debug(' Current distributions exist delete distributions +');
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
arp_standard.debug(' delete distributions -');
arp_standard.debug('Update the CM app ra_id '|| old_rec_app.receivable_application_id || '+');
UPDATE ar_receivable_applications ra
SET
acctd_amount_applied_from =
decode(l_foreign_transaction,
'N',
-( p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
'Y', to_number( nvl(l_new_acctd_amt_applied_from, 0) ) ),
acctd_amount_applied_to =
decode(l_foreign_transaction,
'N',
-(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount),
'Y', to_number(nvl(l_new_acctd_amt_applied_to, 0))),
amount_applied =
-(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount),
line_applied = -p_control.line_amount,
tax_applied = -p_control.tax_amount,
freight_applied = -p_control.freight_amount,
receivables_charges_applied = -p_control.charge_amount,
last_updated_by = p_profile_info.user_id,
last_update_date = trunc(sysdate),
last_update_login = p_profile_info.conc_login_id
WHERE ra.applied_payment_schedule_id = p_control.payment_schedule_id
and ra.customer_trx_id = p_control.customer_trx_id
and ra.reversal_gl_date IS NULL
and ra.receivable_application_id = old_rec_app.receivable_application_id;
debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
ar_mrc_engine3.update_cm_application(
l_app_id,
p_control.payment_schedule_id, /* p_app_ps_id */
p_control.customer_trx_id, /* p_ct_id */
l_amount_applied);
arp_standard.debug('End update the CM app ra_id');
arp_standard.debug('End Path Update CM RA and recreate distributions');
ins_ra_rec.program_update_date := NULL;
arp_app_pkg.insert_p( ins_ra_rec, l_ra_id );
arp_standard.debug('Reverse application inserted ra_id :'||l_ra_id);
UPDATE ar_receivable_applications
SET reversal_gl_date = TRUNC(SYSDATE),
display = 'N'
WHERE receivable_application_id = old_rec_app.receivable_application_id;
arp_standard.debug('The old ra record '|| old_rec_app.receivable_application_id ||' reversal_gl_date updated ');
ar_mrc_engine3.reversal_insert_oppos_ra_recs(
ins_ra_rec,
old_rec_app.receivable_application_id,
l_ra_id);
arp_app_pkg.insert_p( ins_ra_rec, l_ra_id );
debug( 'EXCEPTION: Error executing update stmt #2',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps.update_payment_schedule()-' );
debug( 'EXCEPTION: arp_maintain_ps.update_payment_schedule()',
MSG_LEVEL_BASIC );
END update_payment_schedule;
PROCEDURE update_adjustments(
p_system_info IN arp_trx_global.system_info_rec_type,
p_profile_info IN arp_trx_global.profile_rec_type,
p_control IN control_rec_type
) IS
CURSOR del_app IS
select app.receivable_application_id app_id,
app.customer_trx_id trx_id
from ar_receivable_applications app
where app.applied_payment_schedule_id = p_control.payment_schedule_id
and app.customer_trx_id = p_control.customer_trx_id
and nvl(app.confirmed_flag,'Y') = 'Y' --accounting exists in ar_distributions only if confirmed
and app.status = 'APP'
and exists (select 'x'
from ar_distributions ard
where ard.source_table = 'RA'
and ard.source_id = app.receivable_application_id); --delete only necessary records
l_update_inv_adr NUMBER;
l_update_inv_acctd_adr NUMBER;
l_update_new_adj_amount NUMBER;
print_fcn_label( 'arp_maintain_ps.update_adjustments()+' );
SELECT adj.adjustment_id
INTO l_dummy
FROM ar_adjustments adj
WHERE adj.receivables_trx_id = -1
and adj.customer_trx_id = p_control.previous_customer_trx_id
and adj.subsequent_trx_id = p_control.customer_trx_id
and rownum = 1;
debug( 'EXCEPTION: Error executing select stmt #1',
MSG_LEVEL_BASIC );
update_payment_schedule(
p_system_info,
p_profile_info,
p_control );
SELECT
/* reverse old cm app */
ps_cm.amount_due_remaining - ra.amount_applied,
ps_cm.acctd_amount_due_remaining - ra.acctd_amount_applied_from,
ps_cm.exchange_rate,
-( p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount ),
/* reverse old cm app */
ps_inv.amount_due_remaining + ra.amount_applied,
ps_inv.acctd_amount_due_remaining + ra.acctd_amount_applied_to,
ps_inv.exchange_rate,
(-ra.line_applied - p_control.line_amount)
INTO
l_cm_adr,
l_cm_acctd_adr,
l_cm_rate,
l_new_amount_applied,
l_inv_adr,
l_inv_acctd_adr,
l_inv_rate,
l_new_adj_amount
FROM
ar_payment_schedules ps_cm,
ar_payment_schedules ps_inv,
ar_receivable_applications ra
WHERE p_system_info.base_currency <> ps_inv.invoice_currency_code
and ra.applied_payment_schedule_id = ps_inv.payment_schedule_id
and ps_inv.payment_schedule_id = p_control.payment_schedule_id
and ra.payment_schedule_id = ps_cm.payment_schedule_id
and ps_cm.customer_trx_id = p_control.customer_trx_id;
debug( 'EXCEPTION: Error executing select stmt #2',
MSG_LEVEL_BASIC );
SELECT
/* reverse adj effect */
to_number(nvl(l_new_inv_adr, 0)) - adj.amount,
to_number(nvl(l_new_inv_acctd_adr, 0)) - adj.acctd_amount,
adj.amount + to_number(nvl(l_new_adj_amount, 0))
INTO
l_update_inv_adr,
l_update_inv_acctd_adr,
l_update_new_adj_amount
FROM ar_adjustments adj
WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
and adj.subsequent_trx_id = p_control.customer_trx_id
and adj.receivables_trx_id = -1
and adj.payment_schedule_id = p_control.payment_schedule_id;
debug( 'EXCEPTION: Error executing select stmt #3',
MSG_LEVEL_BASIC );
select ar_adjustments_s.nextval into l_adjustment_id
from dual;
INSERT INTO ar_adjustments
(
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
set_of_books_id,
receivables_trx_id,
automatically_generated,
type,
adjustment_type,
status,
apply_date,
adjustment_id,
amount,
gl_date,
code_combination_id,
customer_trx_id,
payment_schedule_id,
subsequent_trx_id,
postable,
acctd_amount,
adjustment_number,
created_from,
posting_control_id
,org_id
)
SELECT
p_profile_info.user_id,
trunc(sysdate),
p_profile_info.user_id,
trunc(sysdate),
p_profile_info.conc_login_id,
adj2.set_of_books_id,
-1,
'Y',
'LINE',
'C',
'A',
adj2.apply_date,
l_adjustment_id,
nvl(-ra.line_applied, 0) - p_control.line_amount,
adj2.gl_date,
adj2.code_combination_id,
p_control.previous_customer_trx_id,
p_control.payment_schedule_id,
p_control.customer_trx_id,
adj2.postable,
decode(l_foreign_transaction,
'N', nvl(-ra.line_applied, 0) - p_control.line_amount,
'Y', to_number(nvl(l_new_adj_acctd_amount, 0))),
to_char(ar_adjustment_number_s.nextval),
'ARAPSI',
-3
,arp_standard.sysparm.org_id /* SSA changes anuj */
FROM
ar_adjustments adj2,
ar_receivable_applications ra,
ra_customer_trx ct
WHERE adj2.receivables_trx_id= -1
and adj2.customer_trx_id = p_control.previous_customer_trx_id
and adj2.subsequent_trx_id = p_control.customer_trx_id
and ra.customer_trx_id = adj2.subsequent_trx_id
and ra.applied_payment_schedule_id = p_control.payment_schedule_id
and ct.customer_trx_id = ra.applied_customer_trx_id
and adj2.payment_schedule_id =
(
/* find an adjustment against the invoice by the CM */
SELECT max(payment_schedule_id)
FROM ar_adjustments adj3
WHERE adj3.receivables_trx_id=-1
and adj3.customer_trx_id = p_control.previous_customer_trx_id
and adj3.subsequent_trx_id = p_control.customer_trx_id
);
debug( SQL%ROWCOUNT||' row(s) inserted', MSG_LEVEL_DEBUG );
p_event_mode => 'INSERT',
p_table_name => 'AR_ADJUSTMENTS',
p_mode => 'SINGLE',
p_key_value => l_adjustment_id
);
debug( 'EXCEPTION: Error executing insert stmt',
MSG_LEVEL_BASIC );
update_adj_document_number(
p_system_info,
p_profile_info,
p_control.customer_trx_id,
p_control.previous_customer_trx_id,
p_control.customer_trx_id,
p_control.payment_schedule_id,
l_doc_where_clause );
l_update_inv_adr, -- master_from
l_update_inv_acctd_adr, -- acctd_master_from
l_update_new_adj_amount, -- detail
l_dummy, -- master_to
l_new2_inv_acctd_adr, -- acctd_master_to
l_new_adj_acctd_amount -- acctd_detail
);
-- do the update
DECLARE
l_adj_key_value_list gl_ca_utility_pkg.r_key_value_arr;
UPDATE ar_adjustments adj
SET
(
amount,
acctd_amount,
line_adjusted,
last_updated_by,
last_update_date,
last_update_login
) =
(
SELECT
nvl(adj.amount, 0) - ra.line_applied - p_control.line_amount,
decode(l_foreign_transaction,
'N', nvl(adj.amount, 0) - ra.line_applied -
p_control.line_amount,
'Y', to_number(nvl(l_new_adj_acctd_amount, 0))),
nvl(adj.amount, 0) - ra.line_applied - p_control.line_amount,
p_profile_info.user_id,
trunc(sysdate),
p_profile_info.conc_login_id
FROM
ar_receivable_applications ra,
ra_customer_trx ct
WHERE ra.customer_trx_id = p_control.customer_trx_id
and ra.status||'' = 'APP'
and ra.applied_payment_schedule_id = p_control.payment_schedule_id
and ct.customer_trx_id = ra.applied_customer_trx_id
)
WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
and adj.subsequent_trx_id = p_control.customer_trx_id
and adj.receivables_trx_id = -1
and adj.payment_schedule_id = p_control.payment_schedule_id
RETURNING adj.adjustment_id
BULK COLLECT INTO l_adj_key_value_list;
debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
p_event_mode => 'UPDATE',
p_table_name => 'AR_ADJUSTMENTS',
p_mode => 'BATCH',
p_key_value_list => l_adj_key_value_list);
debug( 'EXCEPTION: Error executing update stmt',
MSG_LEVEL_BASIC );
/* VAT changes: update accounting entry */
SELECT adjustment_id
INTO l_adjustment_id
FROM ar_adjustments adj
WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
and adj.subsequent_trx_id = p_control.customer_trx_id
and adj.receivables_trx_id = -1
and adj.payment_schedule_id = p_control.payment_schedule_id;
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
UPDATE ar_payment_schedules ps
SET (
ps.status,
ps.gl_date_closed,
ps.actual_date_closed,
ps.amount_credited,
ps.amount_adjusted,
ps.amount_due_remaining,
ps.acctd_amount_due_remaining,
ps.tax_remaining,
ps.freight_remaining,
ps.receivables_charges_remaining,
ps.last_updated_by,
ps.last_update_date,
ps.last_update_login) = (
SELECT
decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
nvl(ra.freight_applied, 0) +
nvl(ra.receivables_charges_applied, 0) +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount,
0, 'CL', 'OP'),
decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
nvl(ra.freight_applied,0) +
nvl(ra.receivables_charges_applied, 0) +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount,
0, greatest(max(ra2.gl_date),
max(decode(adj2.status,
'A', adj2.gl_date,
ps2.gl_date))),
''),
decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
nvl(ra.freight_applied, 0) +
nvl(ra.receivables_charges_applied,0) +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount,
0, greatest(max(ra2.apply_date),
max(decode(adj2.status,
'A', adj2.apply_date,
ps2.trx_date))),
''),
nvl(ps2.amount_credited, 0) + ra.amount_applied +
(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount),
nvl(ps2.amount_adjusted, 0) - ra.line_applied - p_control.line_amount,
ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
nvl(ra.freight_applied, 0) +
nvl(ra.receivables_charges_applied, 0) +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount,
decode(l_foreign_transaction,
'N',
ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
nvl(ra.freight_applied, 0) +
nvl(ra.receivables_charges_applied, 0) +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount,
'Y', to_number(nvl(l_new2_inv_acctd_adr, 0))),
nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied, 0) +
p_control.tax_amount,
nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0) +
p_control.freight_amount,
nvl(ps2.receivables_charges_remaining, 0) +
nvl(ra.receivables_charges_applied,0) + p_control.charge_amount,
p_profile_info.user_id,
trunc(sysdate),
p_profile_info.conc_login_id
FROM
ar_receivable_applications ra,
ar_payment_schedules ps2,
ar_receivable_applications ra2,
ar_adjustments adj2
WHERE ra.customer_trx_id = p_control.customer_trx_id
and ra.status||'' = 'APP'
and ra.applied_payment_schedule_id = ps2.payment_schedule_id
and ps.payment_schedule_id = ps2.payment_schedule_id
and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
and nvl(ra2.confirmed_flag,'Y')='Y'
GROUP BY
ps2.payment_schedule_id,
ra2.applied_payment_schedule_id,
adj2.payment_schedule_id,
ps2.amount_due_remaining,
ra.amount_applied,
ps2.gl_date,
ps2.trx_date,
ps2.amount_credited,
ps2.amount_adjusted,
ps2.acctd_amount_due_remaining,
ra.acctd_amount_applied_to,
ps2.amount_line_items_remaining,
ra.line_applied,
ps2.tax_remaining,
ra.tax_applied,
ps2.freight_remaining,
ra.freight_applied,
ps2.receivables_charges_remaining,
ra.receivables_charges_applied,
ps2.exchange_rate)
WHERE ps.payment_schedule_id = p_control.payment_schedule_id
RETURNING ps.payment_schedule_id
BULK COLLECT INTO l_ar_ps_key_value_list;
debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'BATCH',
p_key_value_list => l_ar_ps_key_value_list);
debug( 'EXCEPTION: Error executing update stmt',
MSG_LEVEL_BASIC );
select app.receivable_application_id,
app.amount_applied
from ar_receivable_applications app
where app.applied_payment_schedule_id = p_control.payment_schedule_id
and app.customer_trx_id = p_control.customer_trx_id
and app.status = 'APP';
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
UPDATE ar_receivable_applications ra
SET
acctd_amount_applied_from =
decode(l_foreign_transaction,
'N',
-(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount),
'Y', to_number(nvl(l_new_acctd_amt_applied_from, 0))),
acctd_amount_applied_to =
decode(l_foreign_transaction,
'N',
-(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount),
'Y', to_number(nvl(l_new_acctd_amt_applied_to, 0))),
amount_applied =
-(p_control.line_amount +
p_control.tax_amount +
p_control.freight_amount +
p_control.charge_amount),
line_applied = -to_number(p_control.line_amount),
tax_applied = -to_number(p_control.tax_amount),
freight_applied = -to_number(p_control.freight_amount),
receivables_charges_applied = -to_number(p_control.charge_amount),
last_updated_by = p_profile_info.user_id,
last_update_date = trunc(sysdate),
last_update_login = p_profile_info.conc_login_id
WHERE ra.applied_payment_schedule_id = p_control.payment_schedule_id
and ra.status||'' = 'APP'
and ra.customer_trx_id = p_control.customer_trx_id;
debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
ar_mrc_engine3.update_cm_application(
l_app_id.receivable_application_id,
p_control.payment_schedule_id, /* p_app_ps_id */
p_control.customer_trx_id, /* p_ct_id */
l_app_id.amount_applied);
debug( 'EXCEPTION: Error executing update stmt',
MSG_LEVEL_BASIC );
print_fcn_label( 'arp_maintain_ps.update_adjustments()-' );
debug( 'EXCEPTION: arp_maintain_ps.update_adjustments()',
MSG_LEVEL_BASIC );
END update_adjustments;
SELECT
nvl( sum( nvl(-adj.amount, 0) ), 0 )
INTO l_temp
FROM ar_adjustments adj
WHERE adj.customer_trx_id =
decode( p_control.initial_trx_type,
'DEP', p_control.customer_trx_id,
'GUAR', p_control.initial_customer_trx_id )
and (
( p_control.initial_trx_type = 'DEP'
and
adj.subsequent_trx_id is null )
or
( p_control.initial_trx_type = 'GUAR'
and
adj.subsequent_trx_id = p_control.customer_trx_id ) )
and adj.receivables_trx_id = -1;
debug( 'EXCEPTION: Error executing select stmt',
MSG_LEVEL_BASIC );
SELECT 1
INTO l_temp
FROM ar_payment_schedules
WHERE customer_trx_id = p_customer_trx_id;
-- Update, Delete case
----------------------------------------------------------------
IF( l_control_rec.process_mode in ( U, D ) ) THEN
debug( ' Update, Delete mode', MSG_LEVEL_DEBUG );
/* VAT changes: delete accounting entry for adjustment */
SELECT adj.adjustment_id into l_adjustment_id
FROM ar_adjustments adj
WHERE adj.customer_trx_id = l_control_rec.customer_trx_id
and adj.receivables_trx_id = -1;
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
-- arabdep: delete invoice adjustments
delete_adjustments( l_control_rec.customer_trx_id, NULL );
-- arabaj: delete guar adj, update guar ps created by child
reverse_adjustments(
system_info,
profile_info,
l_control_rec.initial_customer_trx_id,
l_control_rec.customer_trx_id );
-- aradps: delete old ps
delete_payment_schedule( l_control_rec.customer_trx_id );
-- Insert, Update case
----------------------------------------------------------------
IF( p_mode in ( I, U ) ) THEN
debug( ' Insert, Update mode', MSG_LEVEL_DEBUG );
arp_maintain_ps2.insert_inv_ps_private(
system_info,
profile_info,
l_control_rec.customer_trx_id,
l_control_rec.reversed_cash_receipt_id );
arp_maintain_ps2.insert_child_adj_private(
system_info,
profile_info,
l_control_rec.customer_trx_id );
update_adj_document_number(
system_info,
profile_info,
l_control_rec.customer_trx_id,
l_control_rec.customer_trx_id,
null,
null,
l_doc_where_clause );
update_adj_document_number(
system_info,
profile_info,
l_control_rec.customer_trx_id,
l_control_rec.initial_customer_trx_id,
l_control_rec.customer_trx_id,
null,
l_doc_where_clause );
-- Update, Delete case
--
IF( l_control_rec.process_mode in ( U, D ) ) THEN
debug( ' Update, Delete mode', MSG_LEVEL_DEBUG );
-- arabaj: delete invoice adj (created by cm), update inv ps
reverse_adjustments(
system_info,
profile_info,
l_control_rec.previous_customer_trx_id,
l_control_rec.customer_trx_id );
-- arabaj: delete guar adj (created by cm), update guar ps
reverse_adjustments(
system_info,
profile_info,
l_control_rec.initial_customer_trx_id,
l_control_rec.customer_trx_id );
-- arabcm: update inv ps (reverse cm effect)
arp_standard.debug(' reverse_cm_effect+');
-- aradra: delete cm app recs
arp_standard.debug(' delete_applications+');
delete_applications( l_control_rec.customer_trx_id );
arp_standard.debug(' delete_applications-');
-- aradps: delete cm ps
arp_standard.debug(' delete_payment_schedule+');
delete_payment_schedule( l_control_rec.customer_trx_id );
arp_standard.debug(' delete_payment_schedule-');
debug( ' Insert, Update mode', MSG_LEVEL_DEBUG );
-- araiad: create adj, update ps
----------------------------------------------------------
arp_standard.debug(' arp_maintain_ps2.insert_cm_child_adj_private+');
arp_maintain_ps2.insert_cm_child_adj_private(
system_info,
profile_info,
l_control_rec.customer_trx_id );
arp_standard.debug(' arp_maintain_ps2.insert_cm_child_adj_private-');
update_adj_document_number(
system_info,
profile_info,
l_control_rec.customer_trx_id,
l_control_rec.previous_customer_trx_id,
l_control_rec.customer_trx_id,
null,
l_doc_where_clause );
arp_standard.debug(' update_adj_document_number+');
update_adj_document_number(
system_info,
profile_info,
l_control_rec.customer_trx_id,
l_control_rec.initial_customer_trx_id,
l_control_rec.customer_trx_id,
null,
l_doc_where_clause );
arp_standard.debug(' update_adj_document_number-');
arp_standard.debug(' arp_maintain_ps2.insert_cm_ps_private+');
arp_maintain_ps2.insert_cm_ps_private(
system_info,
profile_info,
l_control_rec.customer_trx_id );
arp_standard.debug(' arp_maintain_ps2.insert_cm_ps_private-');
-- araudps: insert dep adj, if not exists, else update adj
update_adjustments( system_info, profile_info, l_control_rec );
-- araups: correct round error, update inv ps, update cm app
arp_standard.debug(' update_payment_schedule+');
update_payment_schedule(
system_info,
profile_info,
l_control_rec );
arp_standard.debug(' update_payment_schedule-');
doc_combo_select_c );
build_doc_insert_audit_sql(
system_info,
profile_info,
p_where_clause,
doc_insert_audit_c );
PROCEDURE test_build_doc_update_adj_sql( p_where_clause VARCHAR2 )
IS
BEGIN
enable_debug( 1000000 );
build_doc_update_adj_sql(
system_info,
profile_info,
'my_seq', -- seq name
1, -- seq id
p_where_clause,
doc_update_adj_c );
PROCEDURE test_update_adj_doc_number(
p_customer_trx_id BINARY_INTEGER,
p_update_where_clause VARCHAR2 ) IS
BEGIN
enable_debug( 1000000 );
update_adj_document_number(
system_info,
profile_info,
p_customer_trx_id,
null,
null,
null,
p_update_where_clause );