[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
There are separate INSERT statements for Invoice API,
Autoinvoice, and ARXTWMAI */
PROCEDURE insert_term_contingencies (
p_request_id NUMBER,
p_customer_trx_line_id NUMBER) IS
l_user_id NUMBER;
debug('insert_term_contingencies()+');
INSERT INTO ar_line_conts_all
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
max(ctl.customer_trx_line_id),
5,
'5',
NULL,
NULL,
NULL,
NULL,
'N',
'Y',
max(ctl.request_id),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
max(ctl.org_id)
FROM ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_terms_lines tl,
ra_cust_trx_types_all ctt,
pa_implementations pa
WHERE ctl.request_id = p_request_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.org_id = ctt.org_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND ct.term_id = tl.term_id
AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'errors'
FROM ar_trx_errors_gt teg,
ar_trx_lines_gt tlg
WHERE teg.trx_header_id = tlg.trx_header_id
AND teg.trx_line_id = tlg.trx_line_id
AND tlg.customer_trx_line_id = ctl.customer_trx_line_id)
AND NOT EXISTS
(SELECT 'prevent duplicate contingency'
FROM ar_line_conts_all alc
WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
AND alc.contingency_id = 5)
GROUP BY ctl.customer_trx_line_id, tl.term_id
HAVING max(due_days) > arp_standard.sysparm.payment_threshold;
INSERT INTO ar_line_conts_all
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
max(ctl.customer_trx_line_id),
5,
'5',
NULL,
NULL,
NULL,
NULL,
'N',
'Y',
max(ctl.request_id),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
max(ctl.org_id)
FROM ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_terms_lines tl,
ra_cust_trx_types_all ctt,
pa_implementations pa
WHERE ctl.request_id = p_request_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.org_id = ctt.org_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND ct.term_id = tl.term_id
AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'errors'
FROM ra_interface_errors_all ie
WHERE ie.interface_line_id = ctl.customer_trx_line_id)
AND NOT EXISTS
(SELECT 'prevent duplicate contingency'
FROM ar_line_conts_all alc
WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
AND alc.contingency_id = 5)
GROUP BY ctl.customer_trx_line_id, tl.term_id
HAVING max(due_days) > arp_standard.sysparm.payment_threshold;
INSERT INTO ar_line_conts_all
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
ctl.customer_trx_line_id,
5,
'5',
NULL,
NULL,
NULL,
NULL,
'N',
'Y',
NULL,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
ctl.org_id
FROM ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_terms_lines tl,
ra_cust_trx_types_all ctt,
pa_implementations pa
WHERE ctl.customer_trx_line_id = p_customer_trx_line_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.org_id = ctt.org_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND ct.term_id = tl.term_id
AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'prevent duplicate contingency'
FROM ar_line_conts_all alc
WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
AND alc.contingency_id = 5)
GROUP BY ctl.customer_trx_line_id, ctl.org_id,tl.term_id
HAVING max(tl.due_days) > arp_standard.sysparm.payment_threshold;
debug('term contingencies inserted: ' || l_rows);
debug('insert_term_contingencies()-');
END insert_term_contingencies;
There are separate INSERT statements for Invoice API,
Autoinvoice, and ARXTWMAI */
PROCEDURE insert_credit_contingencies (
p_request_id NUMBER,
p_customer_trx_line_id NUMBER) IS
l_user_id NUMBER;
debug('insert_credit_contingencies()+');
INSERT INTO ar_line_conts_all
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
ctl.customer_trx_line_id,
3,
'3',
NULL,
NULL,
NULL,
NULL,
'N',
'Y',
ctl.request_id,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
ctl.org_id
FROM ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_cust_trx_types_all ctt,
pa_implementations pa
WHERE ctl.request_id = p_request_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.org_id = ctt.org_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
AND ar_revenue_management_pvt.creditworthy
(ct.bill_to_customer_id, ct.bill_to_site_use_id)= 0
AND NOT EXISTS
(SELECT 'errors'
FROM ar_trx_errors_gt teg,
ar_trx_lines_gt tlg
WHERE teg.trx_header_id = tlg.trx_header_id
AND teg.trx_line_id = tlg.trx_line_id
AND tlg.customer_trx_line_id = ctl.customer_trx_line_id)
AND NOT EXISTS
(SELECT 'prevent duplicate contingency'
FROM ar_line_conts_all alc
WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
AND alc.contingency_id = 3);
INSERT INTO ar_line_conts_all
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
ctl.customer_trx_line_id,
3,
'3',
NULL,
NULL,
NULL,
NULL,
'N',
'Y',
ctl.request_id,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
ctl.org_id
FROM ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_cust_trx_types_all ctt,
pa_implementations pa
WHERE ctl.request_id = p_request_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.org_id = ctt.org_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
AND ar_revenue_management_pvt.creditworthy
(ct.bill_to_customer_id, ct.bill_to_site_use_id)= 0
AND NOT EXISTS
(SELECT 'errors'
FROM ra_interface_errors_all ie
WHERE ie.interface_line_id = ctl.customer_trx_line_id)
AND NOT EXISTS
(SELECT 'prevent duplicate contingency'
FROM ar_line_conts_all alc
WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
AND alc.contingency_id = 3);
INSERT INTO ar_line_conts_all
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
ctl.customer_trx_line_id,
3,
'3',
NULL,
NULL,
NULL,
NULL,
'N',
'Y',
NULL,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
ctl.org_id
FROM ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_cust_trx_types_all ctt,
pa_implementations pa
WHERE ctl.customer_trx_line_id = p_customer_trx_line_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.org_id = ctt.org_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
AND ar_revenue_management_pvt.creditworthy
(ct.bill_to_customer_id, ct.bill_to_site_use_id)= 0
AND NOT EXISTS
(SELECT 'prevent duplicate contingency'
FROM ar_line_conts_all alc
WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
AND alc.contingency_id = 3);
debug('credit contingencies inserted: ' || l_rows);
debug('insert_credit_contingencies()-');
END insert_credit_contingencies;
INSERT INTO ar_reviewed_lines_gt
(
customer_trx_line_id,
customer_trx_id,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
line_type,
so_line_id,
request_id
)
SELECT
dl.customer_trx_line_id line_id,
max(dl.customer_trx_id) trx_id,
max(dl.amount_due_original),
max(dl.acctd_amount_due_original),
max(dl.amount_recognized),
max(dl.acctd_amount_recognized),
max(dl.amount_pending),
max(dl.acctd_amount_pending),
'PARENT',
max(interface_line_attribute6),
l_request_id
FROM ar_deferred_lines dl,
ar_line_conts lc,
ar_deferral_reasons dr,
ra_customer_trx_lines ctl
WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
AND lc.contingency_id = dr.contingency_id
AND ctl.customer_trx_line_id = lc.customer_trx_line_id
AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE'
AND lc.completed_flag = 'N'
AND line_collectible_flag = 'N' -- not collectilbe
AND manual_override_flag = 'N' -- not manually overridden in
-- RAM wizards
AND dl.customer_trx_id = nvl(p_customer_trx_id,
dl.customer_trx_id)
AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
dl.customer_trx_line_id)
GROUP BY dl.customer_trx_line_id;
debug('acceptance rows inserted: ' || SQL%ROWCOUNT);
INSERT INTO ar_reviewed_lines_gt
(
customer_trx_line_id,
customer_trx_id,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
line_type,
so_line_id,
request_id
)
SELECT
dl.customer_trx_line_id line_id,
max(dl.customer_trx_id) trx_id,
max(dl.amount_due_original),
max(dl.acctd_amount_due_original),
max(dl.amount_recognized),
max(dl.acctd_amount_recognized),
max(dl.amount_pending),
max(dl.acctd_amount_pending),
'PARENT',
max(interface_line_attribute6),
l_request_id
FROM ar_deferred_lines dl,
ar_line_conts lc,
ar_deferral_reasons dr,
ra_customer_trx_lines ctl
WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
AND lc.contingency_id = dr.contingency_id
AND ctl.customer_trx_line_id = lc.customer_trx_line_id
AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE'
AND lc.completed_flag = 'N'
AND line_collectible_flag = 'N' -- not collectilbe
AND manual_override_flag = 'N' -- not manually overridden in
-- RAM wizards
AND dl.customer_trx_id = nvl(p_customer_trx_id,
dl.customer_trx_id)
AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
dl.customer_trx_line_id)
AND trunc(lc.expiration_date) <= trunc(sysdate)
GROUP BY dl.customer_trx_line_id;
debug('acceptance rows inserted: ' || SQL%ROWCOUNT);
DELETE FROM AR_REVIEWED_LINES_GT
WHERE (customer_trx_id, customer_trx_line_id) IN
(select gt.customer_trx_id, gt.customer_trx_line_id
FROM AR_REVIEWED_LINES_GT gt,
RA_CUSTOMER_TRX_LINES ctl,
OE_ORDER_LINES oel
WHERE gt.customer_trx_id = ctl.customer_trx_id
AND gt.customer_trx_line_id = ctl.customer_trx_line_id
AND ctl.interface_line_context = g_om_context
AND to_number(ctl.interface_line_attribute6) =
oel.line_id
AND oel.flow_status_code <> 'POST-BILLING_ACCEPTANCE'
AND oel.CONTINGENCY_ID IS NOT NULL); --BUG 13482797
INSERT INTO ar_reviewed_lines_gt
(
customer_trx_line_id,
customer_trx_id,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
line_type,
so_line_id,
request_id
)
SELECT
dl.customer_trx_line_id,
dl.customer_trx_id,
dl.amount_due_original,
dl.acctd_amount_due_original,
dl.amount_recognized,
dl.acctd_amount_recognized,
dl.amount_pending,
dl.acctd_amount_pending,
'PARENT',
interface_line_attribute6,
l_request_id
FROM ar_deferred_lines dl,
ra_customer_trx_lines ctl
WHERE dl.customer_trx_line_id = ctl.customer_trx_line_id
AND dl.customer_trx_id = nvl(p_customer_trx_id, dl.customer_trx_id)
AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
dl.customer_trx_line_id)
AND NOT EXISTS
( SELECT 'already inserted'
FROM ar_reviewed_lines_gt rl
WHERE rl.customer_trx_line_id = dl.customer_trx_line_id);
debug('no contingency rows inserted: ' || SQL%ROWCOUNT);
and results in no lines being inserted when the interface_line_context
does not match the ONT_SOURCE_CODE profile */
/* 5229211 - Added code to populate so_line_id */
INSERT INTO ar_reviewed_lines_gt
(
customer_trx_line_id,
customer_trx_id,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
line_type,
request_id,
so_line_id,
expiration_date
)
SELECT /*+ ORDERED */
child.customer_trx_line_id line_id,
max(child.customer_trx_id) trx_id,
max(child.amount_due_original),
max(child.acctd_amount_due_original),
max(child.amount_recognized),
max(child.acctd_amount_recognized),
max(child.amount_pending),
max(child.acctd_amount_pending),
'CHILD',
l_request_id,
child_line.interface_line_attribute6,
max(lc.expiration_date)
FROM ar_reviewed_lines_gt parent,
ra_customer_trx_lines parent_line,
ar_deferred_lines child,
ra_customer_trx_lines child_line,
ar_line_conts lc,
ar_deferral_reasons dr
WHERE parent.customer_trx_id = parent_line.customer_trx_id
AND parent.customer_trx_line_id = parent_line.customer_trx_line_id
AND parent_line.interface_line_context = g_om_context
AND to_char(child.parent_line_id) = parent.so_line_id
AND child.customer_trx_line_id = child_line.customer_trx_line_id
AND child_line.customer_trx_line_id = lc.customer_trx_line_id
AND lc.contingency_id = dr.contingency_id
AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE'
AND lc.completed_flag = 'N'
AND line_collectible_flag = 'N' -- not collectilbe
AND manual_override_flag = 'N' -- not manually overridden in
-- RAM wizards
AND parent.customer_trx_id = nvl(p_customer_trx_id,
parent.customer_trx_id)
AND parent.customer_trx_line_id = nvl(p_customer_trx_line_id,
parent.customer_trx_line_id)
AND trunc(lc.expiration_date) <= trunc(sysdate)
GROUP BY child.customer_trx_line_id, child_line.interface_line_attribute6;
debug(' inserted ' || l_rows || ' row(s)');
INSERT INTO ar_reviewed_lines_gt
(
customer_trx_line_id,
customer_trx_id,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
line_type,
request_id,
expiration_date
)
SELECT
dl.customer_trx_line_id line_id,
max(customer_trx_id) trx_id,
max(amount_due_original),
max(acctd_amount_due_original),
max(amount_recognized),
max(acctd_amount_recognized),
max(amount_pending),
max(acctd_amount_pending),
'OTHERS',
l_request_id,
max(lc.expiration_date)
FROM ar_deferred_lines dl,
ar_line_conts lc,
ar_deferral_reasons dr
WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
AND lc.contingency_id = dr.contingency_id
AND lc.completed_flag = 'N'
AND line_collectible_flag = 'N' -- not collectilbe
AND manual_override_flag = 'N' -- not manually overridden in
-- RAM wizards
AND dr.revrec_event_code <> 'CUSTOMER_ACCEPTANCE'
AND trunc(lc.expiration_date) <= trunc(sysdate)
AND dl.customer_trx_id = nvl(p_customer_trx_id, dl.customer_trx_id)
AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
dl.customer_trx_line_id)
AND NOT EXISTS
( SELECT 'already inserted'
FROM ar_reviewed_lines_gt rl
WHERE rl.customer_trx_line_id = dl.customer_trx_line_id)
GROUP BY dl.customer_trx_line_id;
INSERT INTO ar_reviewed_lines_gt
(
customer_trx_line_id,
customer_trx_id,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
line_type,
request_id
)
SELECT
dl.customer_trx_line_id line_id,
max(customer_trx_id) trx_id,
max(amount_due_original),
max(acctd_amount_due_original),
max(amount_recognized),
max(acctd_amount_recognized),
max(amount_pending),
max(acctd_amount_pending),
'UPDATE',
l_request_id
FROM ar_deferred_lines dl,
ar_line_conts lc,
ar_deferral_reasons dr
WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
AND lc.contingency_id = dr.contingency_id
AND line_collectible_flag = 'N' -- not collectilbe
AND manual_override_flag = 'N' -- not manually overridden in
-- RAM wizards
AND dl.customer_trx_id = nvl(p_customer_trx_id, dl.customer_trx_id)
AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
dl.customer_trx_line_id)
GROUP BY dl.customer_trx_line_id;
debug(' Other row(s) inserted : ' || l_count);
SELECT rl.customer_trx_line_id,
rl.customer_trx_id,
rl.so_line_id
FROM ar_reviewed_lines_gt rl,
ra_customer_trx_lines tl
WHERE rl.request_id = p_req_id
AND tl.customer_trx_line_id = rl.customer_trx_line_id
AND tl.customer_trx_id = rl.customer_trx_id
AND tl.interface_line_context = g_om_context;
PROCEDURE delete_unwanted_contingencies (p_request_id NUMBER
,p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE) IS
/* debug cursor */
CURSOR alc (p_req_id NUMBER, p_line_id NUMBER) IS
select lc.customer_trx_line_id, lc.contingency_id,
dr.policy_attached
from ar_line_conts lc,
ar_deferral_reasons dr
where lc.contingency_id = dr.contingency_id
and ((p_req_id IS NULL and p_line_id IS NOT NULL AND
lc.customer_trx_line_id = p_line_id) OR
(p_req_id IS NOT NULL AND lc.request_id = p_req_id));
debug('delete_unwanted_contingencies()+');
DELETE
FROM ar_line_conts lrc
WHERE customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines ctl
WHERE ctl.request_id = p_request_id)
AND trunc(expiration_date) - trunc(sysdate) <
NVL(arp_standard.sysparm.standard_refund,0)
AND EXISTS
(SELECT 'its a refund contingency'
FROM ar_deferral_reasons dr
WHERE dr.contingency_id = lrc.contingency_id
AND dr.policy_attached in ('REFUND','REFUND_POLICY'));
debug('refund contingencies deleted: ' || SQL%ROWCOUNT);
DELETE
FROM ar_line_conts lc
WHERE lc.customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines ctl,
ra_customer_trx ct
WHERE ctl.customer_trx_id = ct.customer_trx_id
AND ctl.request_id = p_request_id
AND ar_revenue_management_pvt.creditworthy
(ct.bill_to_customer_id, ct.bill_to_site_use_id)= 1)
AND EXISTS
(SELECT 'its a CREDIT_CLASSIFICATION'
FROM ar_deferral_reasons dr
WHERE dr.contingency_id = lc.contingency_id
AND dr.policy_attached = 'CREDIT_CLASSIFICATION');
debug('customer credit contingencies deleted: ' || SQL%ROWCOUNT);
DELETE
FROM ar_line_conts lc
WHERE lc.customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_terms_lines tl
WHERE ctl.customer_trx_id = ct.customer_trx_id
AND ct.term_id = tl.term_id
AND ctl.request_id = p_request_id
GROUP BY ctl.customer_trx_line_id, tl.term_id
HAVING NVL(max(due_days),0) <=
NVL(arp_standard.sysparm.payment_threshold,0))
AND EXISTS
(SELECT 'its a PAYMENT_TERM'
FROM ar_deferral_reasons dr
WHERE dr.policy_attached = 'PAYMENT_TERM'
AND dr.contingency_id = lc.contingency_id);
debug('payment term contingencies deleted: ' || SQL%ROWCOUNT);
DELETE from ar_line_conts
WHERE customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines ctl,
ra_rules r
WHERE ctl.request_id = p_request_id
AND ctl.accounting_rule_id IS NOT NULL
AND ctl.accounting_rule_id = r.rule_id
AND r.deferred_revenue_flag = 'Y');
debug('contingencies for lines with deferred rule deleted: ' ||
SQL%ROWCOUNT);
DELETE
FROM ar_line_conts lrc
WHERE trunc(expiration_date) - trunc(sysdate) <
NVL(arp_standard.sysparm.standard_refund,0)
AND lrc.customer_trx_line_id = p_customer_trx_line_id
AND EXISTS
(SELECT 'a refund contingency'
FROM ar_deferral_reasons dr
WHERE dr.policy_attached in ('REFUND','REFUND_POLICY')
AND dr.contingency_id = lrc.contingency_id);
debug('refund contingencies deleted: ' || SQL%ROWCOUNT);
DELETE
FROM ar_line_conts lc
WHERE lc.customer_trx_line_id = p_customer_trx_line_id
AND EXISTS
(SELECT 'its a credit_classification contingency'
FROM ar_deferral_reasons dr
WHERE dr.contingency_id = lc.contingency_id
AND dr.policy_attached = 'CREDIT_CLASSIFICATION')
AND EXISTS
(SELECT 'customer is not credit worthy'
FROM ra_customer_trx_lines ctl,
ra_customer_trx ct
WHERE ctl.customer_trx_id = ct.customer_trx_id
AND ctl.customer_trx_line_id = p_customer_trx_line_id
AND ar_revenue_management_pvt.creditworthy
(ct.bill_to_customer_id, ct.bill_to_site_use_id)= 1);
debug('customer credit contingencies deleted: ' || SQL%ROWCOUNT);
DELETE
FROM ar_line_conts lc
WHERE lc.customer_trx_line_id = p_customer_trx_line_id
AND EXISTS
(SELECT 'it is a term contingency'
FROM ar_deferral_reasons dr
WHERE dr.policy_attached = 'PAYMENT_TERM'
AND dr.contingency_id = lc.contingency_id)
AND EXISTS
(SELECT 'term exceeds threshold'
FROM ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_terms_lines tl
WHERE ctl.customer_trx_id = ct.customer_trx_id
AND ct.term_id = tl.term_id
AND ctl.customer_trx_line_id = lc.customer_trx_line_id
GROUP BY ctl.customer_trx_line_id, tl.term_id
HAVING NVL(max(due_days),0) <=
NVL(arp_standard.sysparm.payment_threshold,0));
debug('payment term contingencies deleted: ' || SQL%ROWCOUNT);
DELETE FROM AR_LINE_CONTS A
WHERE A.customer_trx_line_id = p_customer_trx_line_id
AND EXISTS (SELECT 'DEFERRED RULE'
FROM ra_customer_trx_lines ctl,
ra_rules r
WHERE ctl.customer_trx_line_id = A.customer_trx_line_id
AND ctl.accounting_rule_id = r.rule_id
AND r.deferred_revenue_flag = 'Y');
debug('contingencies for lines with deferred rule deleted: ' ||
SQL%ROWCOUNT);
DELETE
FROM ar_line_conts
WHERE customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ar_trx_errors_gt teg,
ar_trx_lines_gt tlg
WHERE teg.trx_header_id = tlg.trx_header_id
AND teg.trx_line_id = tlg.trx_line_id
AND request_id = p_request_id);
DELETE
FROM ar_line_conts
WHERE customer_trx_line_id IN
(SELECT ie.interface_line_id
FROM ra_interface_errors ie
WHERE request_id = p_request_id);
debug('delete_unwanted_contingencies()-');
debug('NO_DATA_FOUND: delete_unwanted_contingencies');
debug('OTHERS: delete_unwanted_contingencies');
END delete_unwanted_contingencies;
SELECT MIN(customer_trx_line_id)
INTO l_customer_trx_line_id
FROM RA_CUSTOMER_TRX_LINES
WHERE interface_line_context = l_line_flex_rec.interface_line_context
AND interface_line_attribute1 = l_line_flex_rec.interface_line_attribute1
AND interface_line_attribute2 = l_line_flex_rec.interface_line_attribute2
AND interface_line_attribute3 = l_line_flex_rec.interface_line_attribute3
AND interface_line_attribute4 = l_line_flex_rec.interface_line_attribute4
AND interface_line_attribute5 = l_line_flex_rec.interface_line_attribute5
AND interface_line_attribute6 = l_line_flex_rec.interface_line_attribute6
AND ltrim(interface_line_attribute11) = '0' --13018057
AND ltrim(interface_line_attribute14) = '0'; --13018057
executing the INSERT. */
SELECT 1
INTO l_exists
FROM dual
WHERE EXISTS (select 'at least one child'
from RA_INTERFACE_LINES il
where il.request_id = p_request_id
and il.parent_line_id is not null);
INSERT INTO ar_line_conts
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
ctl.customer_trx_line_id,
plc.contingency_id,
plc.contingency_id,
plc.expiration_date,
plc.expiration_days,
plc.expiration_event_date,
plc.reason_removal_date,
plc.completed_flag,
'C', -- indicates it was copied, not defaulted or imported
p_request_id,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
plc.org_id
FROM ra_customer_trx ct,
ra_customer_trx_lines ctl,
ra_cust_trx_types ctt,
ra_interface_lines il,
ar_line_conts plc
WHERE ct.request_id = p_request_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND ctt.type = 'INV'
AND ct.customer_trx_id = ctl.customer_trx_id
AND ctl.line_type = 'LINE'
AND il.interface_line_id = ctl.customer_trx_line_id
AND il.parent_line_id IS NOT NULL
AND plc.customer_trx_line_id = get_line_id(il.parent_line_id)
AND NOT EXISTS (
SELECT 'contingency already applied'
FROM ar_line_conts clc
WHERE clc.customer_trx_line_id = ctl.customer_trx_line_id
AND clc.contingency_code = plc.contingency_id);
insert_term_contingencies(p_request_id, p_customer_trx_line_id);
insert_credit_contingencies(p_request_id, p_customer_trx_line_id);
INSERT INTO ar_rdr_parameters_gt
(
source_line_id,
batch_source_id,
profile_class_id,
cust_account_id,
cust_acct_site_id,
cust_trx_type_id,
-- item_category_id, (xportal issue logged)
inventory_item_id,
memo_line_id,
org_id,
accounting_rule_id,
ship_to_cust_acct_id,
ship_to_site_use_id
)
SELECT -- Removed the hint that was added as part of bug 13828621
ctl.customer_trx_line_id,
ct.batch_source_id,
decode(ctl.deferral_exclusion_flag, 'Y','',
decode(hcp.cust_account_id,'','',
decode(hcp.site_use_id,'',hcp.profile_class_id,
hcp.profile_class_id))),--For 9855526
ct.bill_to_customer_id,
ct.bill_to_site_use_id,
ctt.cust_trx_type_id,
-- item_category_id
ctl.inventory_item_id,
ctl.memo_line_id,
ct.org_id,
ctl.accounting_rule_id,
NVL(ctl.ship_to_customer_id,ct.ship_to_customer_id),
NVL(ctl.ship_to_site_use_id,ct.ship_to_site_use_id)
FROM
ra_customer_trx ct,
ra_customer_trx_lines ctl,
hz_customer_profiles hcp,
ra_cust_trx_types ctt
WHERE (ctl.customer_trx_line_id = p_customer_trx_line_id)
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.type = 'INV'
AND ct.customer_trx_id = ctl.customer_trx_id
AND ctl.line_type = 'LINE'
AND ct.bill_to_customer_id = hcp.cust_account_id (+)
AND ct.bill_to_site_use_id = NVL(hcp.site_use_id, ct.bill_to_site_use_id )
AND nvl(ctl.deferral_exclusion_flag, 'N') <> 'Y'
AND nvl(ct.invoicing_rule_id, 0) <> -3; /* 11711172 */
INSERT INTO ar_rdr_parameters_gt
(
source_line_id,
batch_source_id,
profile_class_id,
cust_account_id,
cust_acct_site_id,
cust_trx_type_id,
-- item_category_id, (xportal issue logged)
inventory_item_id,
memo_line_id,
org_id,
accounting_rule_id,
ship_to_cust_acct_id,
ship_to_site_use_id
)
SELECT /*+ index(ctl ra_customer_trx_lines_n2) */
ctl.customer_trx_line_id,
ct.batch_source_id,
decode(ctl.deferral_exclusion_flag, 'Y','',
decode(hcp.cust_account_id,'','',
decode(hcp.site_use_id,'',hcp.profile_class_id,
hcp.profile_class_id))),--For 9855526
ct.bill_to_customer_id,
ct.bill_to_site_use_id,
ctt.cust_trx_type_id,
-- item_category_id
ctl.inventory_item_id,
ctl.memo_line_id,
ct.org_id,
ctl.accounting_rule_id,
NVL(ctl.ship_to_customer_id,ct.ship_to_customer_id),
NVL(ctl.ship_to_site_use_id,ct.ship_to_site_use_id)
FROM
ra_customer_trx ct,
ra_customer_trx_lines ctl,
hz_customer_profiles hcp,
ra_cust_trx_types ctt
WHERE ct.request_id = p_request_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.type = 'INV'
AND ct.customer_trx_id = ctl.customer_trx_id
AND ctl.line_type = 'LINE'
AND ct.bill_to_customer_id = hcp.cust_account_id (+)
AND ct.bill_to_site_use_id = nvl(hcp.site_use_id, ct.bill_to_site_use_id)
AND nvl(ctl.deferral_exclusion_flag, 'N') <> 'Y'
AND nvl(ct.invoicing_rule_id, 0) <> -3; /* 11711172 */
debug('rows inserted in rule gt: ' || SQL%ROWCOUNT);
exclude the insert if the interface_line_context = g_om_context
and the contingency revrec_event_code in (INVOICING or CUSTOMER_ACCE.)
INVOICING is really 'pre-billing customer acceptance' and
CUSTOMER_ACCEPTANCE is 'post-billing customer acceptance'.
*/
/* 5222197 - Fix from 5236506 caused problems when transactions had
no context specified. Need to NVL that column to insure that
the condition defaults to false */
/* 5201842 - Added code to populate expiration_date, and
expiration_event_date */
/* 7039838 - conditionally call insert based on parameters */
IF p_request_id IS NOT NULL
THEN
/* Modified logic for autoinvoice */
INSERT INTO ar_line_conts
(
customer_trx_line_id,
contingency_code,
contingency_id,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT /*+ leading(rbr,ctl) use_hash(ctl)
index(ctl,RA_CUSTOMER_TRX_LINES_N4) */
rbr.id,
dr.contingency_id,
dr.contingency_id,
decode(dr.expiration_event_code,
'TRANSACTION_DATE', trunc(ct.trx_date)
+ nvl(dr.expiration_days, 0),
'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual)
+ nvl(dr.expiration_days, 0), NULL),
MAX(expiration_days),
decode(dr.expiration_event_code,
'TRANSACTION_DATE', trunc(ct.trx_date),
'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual), NULL),
decode(MAX(dr.expiration_event_code), 'INVOICING', sysdate, NULL)
reason_removal_date,
decode(MAX(dr.expiration_event_code), 'INVOICING', 'Y', 'N')
completed_flag,
'Y',
p_request_id,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
ct.org_id
FROM fun_rule_bulk_result_gt rbr,
ar_deferral_reasons dr,
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_types ctt,
pa_implementations pa
WHERE rbr.result_value = dr.contingency_id
AND rbr.id = ctl.customer_trx_line_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ctl.request_id = p_request_id -- 7039838
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND sysdate BETWEEN NVL(dr.start_date,SYSDATE) AND
NVL(dr.end_date,SYSDATE)
AND NOT (NVL(ctl.interface_line_context,'##NOT_MATCH##') = g_om_context AND
dr.revrec_event_code in ('INVOICING','CUSTOMER_ACCEPTANCE'))
AND NOT EXISTS
( SELECT 'contingency exists'
FROM ar_line_conts lc
WHERE lc.customer_trx_line_id = rbr.id
AND lc.contingency_id = rbr.result_value
)
GROUP BY rbr.id, dr.contingency_id, dr.expiration_event_code,
dr.expiration_days, ct.org_id, ct.trx_date, ct.ship_date_actual;
INSERT INTO ar_line_conts
(
customer_trx_line_id,
contingency_code,
contingency_id,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
defaulted_in_ar_flag,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
rbr.id,
dr.contingency_id,
dr.contingency_id,
decode(dr.expiration_event_code,
'TRANSACTION_DATE', trunc(ct.trx_date)
+ nvl(dr.expiration_days, 0),
'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual)
+ nvl(dr.expiration_days, 0), NULL),
MAX(expiration_days),
decode(dr.expiration_event_code,
'TRANSACTION_DATE', trunc(ct.trx_date),
'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual), NULL),
decode(MAX(dr.expiration_event_code), 'INVOICING', sysdate, NULL)
reason_removal_date,
decode(MAX(dr.expiration_event_code), 'INVOICING', 'Y', 'N')
completed_flag,
'Y',
p_request_id,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
ct.org_id
FROM fun_rule_bulk_result_gt rbr,
ar_deferral_reasons dr,
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_types ctt,
pa_implementations pa
WHERE rbr.result_value = dr.contingency_id
AND rbr.id = ctl.customer_trx_line_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND sysdate BETWEEN NVL(dr.start_date,SYSDATE) AND
NVL(dr.end_date,SYSDATE)
AND NOT (NVL(ctl.interface_line_context,'##NOT_MATCH##') = g_om_context AND
dr.revrec_event_code in ('INVOICING','CUSTOMER_ACCEPTANCE'))
AND NOT EXISTS
( SELECT 'contingency exists'
FROM ar_line_conts lc
WHERE lc.customer_trx_line_id = rbr.id
AND lc.contingency_id = rbr.result_value
)
AND
(DECODE(g_source,null,decode(ct.created_from,'ARXTWMAI',decode(dr.revrec_event_code,'INVOICING',1,0),0),0)
<> 1)
GROUP BY rbr.id, dr.contingency_id, dr.expiration_event_code,
dr.expiration_days, ct.org_id, ct.trx_date, ct.ship_date_actual;
debug('rows inserted ar_line_conts: ' || SQL%ROWCOUNT);
PROCEDURE insert_contingencies_from_gt (p_request_id NUMBER) IS
l_user_id NUMBER;
debug('insert_contingencies_from_gt()+');
INSERT INTO ar_line_conts
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
completed_by,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
SELECT
tlg.customer_trx_line_id,
tcg.contingency_id,
tcg.contingency_id,
nvl(trunc(tcg.expiration_date), decode(dr.expiration_event_code,
'TRANSACTION_DATE', trunc(thg.trx_date)
+ nvl(tcg.expiration_days, nvl(dr.expiration_days, 0)),
'SHIP_CONFIRM_DATE', trunc(thg.ship_date_actual)
+ nvl(tcg.expiration_days, nvl(dr.expiration_days, 0)), NULL))
expiration_date,
nvl(tcg.expiration_days, dr.expiration_days) expiration_days,
decode( dr.expiration_event_code,
'TRANSACTION_DATE', trunc(thg.trx_date),
'SHIP_CONFIRM_DATE', trunc(thg.ship_date_actual), NULL)
expiration_event_date,
decode(revrec_event_code, 'INVOICING',
NVL(expiration_date, sysdate), NULL) reason_removal_date,
decode(revrec_event_code, 'INVOICING', 'Y',nvl(completed_flag, 'N'))
completed_flag,
decode(revrec_event_code, 'INVOICING', completed_by, NULL)
completed_by,
tlg.request_id,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
thg.org_id
FROM ar_trx_lines_gt tlg,
ar_trx_header_gt thg,
ra_cust_trx_types ctt,
ar_trx_contingencies_gt tcg,
ar_deferral_reasons dr,
pa_implementations pa
WHERE tlg.request_id = p_request_id
AND tlg.trx_header_id = thg.trx_header_id
AND thg.batch_source_id NOT IN (20, 21)
AND thg.org_id = pa.org_id (+)
AND thg.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND thg.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(thg.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND thg.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.type = 'INV'
AND tlg.line_type = 'LINE'
AND tlg.trx_line_id = tcg.trx_line_id
AND tcg.contingency_id = dr.contingency_id
AND NOT EXISTS
( SELECT 'errors exist'
FROM ar_trx_errors_gt err
WHERE err.trx_header_id = tlg.trx_header_id
AND err.trx_line_id = tlg.trx_line_id
);
debug('gt contingencies inserted: ' || SQL%ROWCOUNT);
debug('insert_contingencies_from_gt()-');
debug('NO_DATA_FOUND: insert_contingencies_from_gt');
debug('OTHERS: insert_contingencies_from_gt');
END insert_contingencies_from_gt;
PROCEDURE insert_contingencies_from_itf (p_request_id NUMBER) IS
l_user_id NUMBER;
debug('insert_contingencies_from_itf()+');
INSERT INTO ar_line_conts
(
customer_trx_line_id,
contingency_id,
contingency_code,
expiration_date,
expiration_days,
expiration_event_date,
reason_removal_date,
completed_flag,
completed_by,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
org_id
)
SELECT
ctl.customer_trx_line_id,
ic.contingency_id,
ic.contingency_id,
nvl(trunc(expiration_date), decode(dr.expiration_event_code,
'TRANSACTION_DATE', trunc(ct.trx_date)
+ nvl(ic.expiration_days, nvl(dr.expiration_days, 0)),
'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual)
+ nvl(ic.expiration_days, nvl(dr.expiration_days, 0)), NULL))
expiration_date,
nvl(ic.expiration_days, dr.expiration_days) expiration_days,
decode( dr.expiration_event_code,
'TRANSACTION_DATE', trunc(ct.trx_date),
'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual), NULL)
expiration_event_date,
decode(revrec_event_code, 'INVOICING',
nvl(expiration_date, sysdate),
DECODE(NVL(completed_flag, 'N'),'Y',
NVL(expiration_date,sysdate), NULL))
reason_removal_date,
decode(revrec_event_code, 'INVOICING', 'Y',nvl(completed_flag, 'N'))
completed_flag,
decode(revrec_event_code, 'INVOICING', completed_by, NULL)
completed_by,
ctl.request_id,
l_user_id,
sysdate,
l_user_id,
sysdate,
ct.org_id
FROM ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_types ctt,
ar_interface_conts ic,
ar_deferral_reasons dr,
pa_implementations pa
WHERE ctl.request_id = p_request_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.batch_source_id NOT IN (20, 21)
AND ct.org_id = pa.org_id (+)
AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.type = 'INV'
AND ctl.line_type = 'LINE'
AND ctl.customer_trx_line_id = ic.interface_line_id
AND ic.contingency_id = dr.contingency_id
AND NOT EXISTS
(SELECT 'errors'
FROM ra_interface_errors ie
WHERE ie.interface_line_id = ctl.customer_trx_line_id);
debug('itf contingencies inserted: ' || SQL%ROWCOUNT);
debug('insert_contingencies_from_itf()-');
debug('NO_DATA_FOUND: insert_contingencies_from_itf');
debug('OTHERS: insert_contingencies_from_itf');
END insert_contingencies_from_itf;
PROCEDURE insert_deferred_lines (p_request_id NUMBER
,p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE) IS
l_user_id NUMBER;
l_insert_stmt VARCHAR2(4000);
debug('insert_deferred_lines()+');
INSERT INTO ar_deferred_lines
(
customer_trx_line_id,
customer_trx_id,
original_collectibility_flag,
line_collectible_flag,
manual_override_flag,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
parent_line_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
org_id
)
SELECT
tlg.customer_trx_line_id,
MAX(thg.customer_trx_id),
'N',
'N',
'N',
MAX(tlg.extended_amount),
MAX(decode(g_minimum_accountable_unit_f, NULL,
ROUND( tlg.extended_amount * nvl(thg.exchange_rate, 1),
g_precision_f),
ROUND((tlg.extended_amount * nvl(thg.exchange_rate, 1))
/ g_minimum_accountable_unit_f) * g_minimum_accountable_unit_f)),
0,
0,
0,
0,
MAX(tlg.parent_line_id),
MAX(tcg.attribute_category),
MAX(tcg.attribute1),
MAX(tcg.attribute2),
MAX(tcg.attribute3),
MAX(tcg.attribute4),
MAX(tcg.attribute5),
MAX(tcg.attribute6),
MAX(tcg.attribute7),
MAX(tcg.attribute8),
MAX(tcg.attribute9),
MAX(tcg.attribute10),
MAX(tcg.attribute11),
MAX(tcg.attribute12),
MAX(tcg.attribute13),
MAX(tcg.attribute14),
MAX(tcg.attribute15),
MAX(tlg.request_id),
l_user_id,
sysdate,
l_user_id,
sysdate,
thg.org_id
FROM ar_trx_header_gt thg,
ar_trx_lines_gt tlg,
ar_trx_contingencies_gt tcg,
ar_line_conts lrc
WHERE tlg.request_id = p_request_id
AND tlg.customer_trx_id = thg.customer_trx_id
AND tlg.customer_trx_line_id = lrc.customer_trx_line_id
AND tlg.trx_header_id = tcg.trx_header_id
AND tlg.trx_line_id = tcg.trx_line_id
GROUP BY tlg.customer_trx_line_id, thg.org_id;
INSERT INTO ar_deferred_lines
(
customer_trx_line_id,
customer_trx_id,
original_collectibility_flag,
line_collectible_flag,
manual_override_flag,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
parent_line_id,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
org_id
)
SELECT
tlg.customer_trx_line_id,
MAX(thg.customer_trx_id),
'N',
'N',
'N',
MAX(tlg.extended_amount),
MAX(decode(g_minimum_accountable_unit_f, NULL,
ROUND( tlg.extended_amount * nvl(thg.exchange_rate, 1),
g_precision_f),
ROUND((tlg.extended_amount * nvl(thg.exchange_rate, 1))
/ g_minimum_accountable_unit_f) * g_minimum_accountable_unit_f)),
0,
0,
0,
0,
MAX(tlg.parent_line_id),
MAX(tlg.request_id),
l_user_id,
sysdate,
l_user_id,
sysdate,
thg.org_id
FROM ar_trx_header_gt thg,
ar_trx_lines_gt tlg,
ar_line_conts lrc
WHERE tlg.request_id = p_request_id
AND tlg.customer_trx_id = thg.customer_trx_id
AND tlg.customer_trx_line_id = lrc.customer_trx_line_id
AND NOT EXISTS
(SELECT 'line already inserted'
FROM ar_deferred_lines dl
WHERE dl.customer_trx_line_id = lrc.customer_trx_line_id)
GROUP BY tlg.customer_trx_line_id, thg.org_id;
INSERT INTO ar_deferred_lines
(
customer_trx_line_id,
customer_trx_id,
original_collectibility_flag,
line_collectible_flag,
manual_override_flag,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
org_id,
parent_line_id
)
SELECT
ctl.customer_trx_line_id,
MAX(ct.customer_trx_id),
'N',
'N',
'N',
MAX(ctl.extended_amount),
MAX(decode(g_minimum_accountable_unit_f, NULL,
ROUND( ctl.extended_amount * nvl(ct.exchange_rate, 1),
g_precision_f),
ROUND((ctl.extended_amount * nvl(ct.exchange_rate, 1))
/ g_minimum_accountable_unit_f) * g_minimum_accountable_unit_f)),
0,
0,
0,
0,
MAX(ic.attribute_category),
MAX(ic.attribute1),
MAX(ic.attribute2),
MAX(ic.attribute3),
MAX(ic.attribute4),
MAX(ic.attribute5),
MAX(ic.attribute6),
MAX(ic.attribute7),
MAX(ic.attribute8),
MAX(ic.attribute9),
MAX(ic.attribute10),
MAX(ic.attribute11),
MAX(ic.attribute12),
MAX(ic.attribute13),
MAX(ic.attribute14),
MAX(ic.attribute15),
MAX(ctl.request_id),
l_user_id,
sysdate,
l_user_id,
sysdate,
ct.org_id,
MAX(il.parent_line_id)
FROM ra_customer_trx ct,
ra_customer_trx_lines ctl,
ar_line_conts lrc,
ar_interface_conts ic,
ra_interface_lines il
WHERE ctl.request_id = p_request_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ctl.customer_trx_line_id = lrc.customer_trx_line_id
AND ctl.customer_trx_line_id = ic.interface_line_id
AND ctl.customer_trx_line_id = il.interface_line_id
GROUP BY ctl.customer_trx_line_id, ct.org_id;
INSERT INTO ar_deferred_lines
(
customer_trx_line_id,
customer_trx_id,
original_collectibility_flag,
line_collectible_flag,
manual_override_flag,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
org_id,
parent_line_id
)
SELECT
ctl.customer_trx_line_id,
MAX(ct.customer_trx_id),
'N',
'N',
'N',
MAX(ctl.extended_amount),
MAX(decode(g_minimum_accountable_unit_f, NULL,
ROUND( ctl.extended_amount * nvl(ct.exchange_rate, 1),
g_precision_f),
ROUND((ctl.extended_amount * nvl(ct.exchange_rate, 1))
/ g_minimum_accountable_unit_f) * g_minimum_accountable_unit_f)),
0,
0,
0,
0,
MAX(ctl.request_id),
l_user_id,
sysdate,
l_user_id,
sysdate,
ct.org_id,
MAX(il.parent_line_id)
FROM ra_customer_trx ct,
ra_customer_trx_lines ctl,
ar_line_conts lrc,
ra_interface_lines il
WHERE ((p_request_id IS NULL AND p_customer_trx_line_id IS NOT NULL AND
ctl.customer_trx_line_id = p_customer_trx_line_id) OR
(p_request_id IS NOT NULL AND ctl.request_id = p_request_id))
AND ctl.customer_trx_id = ct.customer_trx_id
AND ctl.customer_trx_line_id = lrc.customer_trx_line_id
AND ctl.customer_trx_line_id = il.interface_line_id (+)
AND NOT EXISTS
(SELECT 'line already inserted'
FROM ar_deferred_lines dl
WHERE dl.customer_trx_line_id = lrc.customer_trx_line_id)
GROUP BY ctl.customer_trx_line_id, ct.org_id;
debug('deferred lines inserted: ' || SQL%ROWCOUNT);
UPDATE ar_deferred_lines dl
SET line_collectible_flag = 'Y'
WHERE dl.request_id = p_request_id
AND NOT EXISTS
(SELECT 'incomplete contingency'
FROM ar_line_conts_all lc
WHERE request_id = p_request_id
AND lc.customer_trx_line_id = dl.customer_trx_line_id
AND lc.completed_flag = 'N');
update ar_deferred_lines dl
set original_collectibility_flag = 'Y'
WHERE dl.request_id = p_request_id
and dl.customer_trx_id in
(SELECT ctl.customer_trx_id /* its a pre-billing contingency */
FROM ra_customer_trx_lines_all ctl,
ar_line_conts_all lrc,
ar_deferral_reasons dr
WHERE ctl.customer_trx_line_id = dl.customer_trx_line_id
and ctl.customer_trx_line_id=lrc.customer_trx_line_id
and trunc(nvl(lrc.expiration_date,sysdate)) - trunc(sysdate) <= 0
and dr.contingency_id = lrc.contingency_id
AND dr.REVREC_EVENT_CODE = 'INVOICING');
UPDATE ar_deferred_lines dl
SET line_collectible_flag = 'Y'
WHERE dl.customer_trx_line_id = p_customer_trx_line_id
AND NOT EXISTS
(SELECT 'incomplete contingency'
FROM ar_line_conts_all lc
WHERE customer_trx_line_id = p_customer_trx_line_id
AND lc.customer_trx_line_id = dl.customer_trx_line_id
AND lc.completed_flag = 'N');
update ar_deferred_lines dl
set original_collectibility_flag = 'Y'
WHERE dl.customer_trx_line_id = p_customer_trx_line_id
and dl.customer_trx_id in
(SELECT ctl.customer_trx_id /* its a pre-billing contingency */
FROM ra_customer_trx_lines_all ctl,
ar_line_conts_all lrc,
ar_deferral_reasons dr
WHERE ctl.customer_trx_line_id = dl.customer_trx_line_id
and ctl.customer_trx_line_id=lrc.customer_trx_line_id
and trunc(nvl(lrc.expiration_date,sysdate)) - trunc(sysdate) <= 0
and dr.contingency_id = lrc.contingency_id
AND dr.REVREC_EVENT_CODE = 'INVOICING');
debug('deferred lines updated: ' || SQL%ROWCOUNT);
debug('insert_deferred_lines()-');
debug('NO_DATA_FOUND: insert_deferred_lines');
debug('OTHERS: insert_deferred_lines');
END insert_deferred_lines;
INSERT INTO ar_trx_errors_gt
(
trx_header_id,
trx_line_id,
trx_contingency_id,
error_message,
invalid_value
)
SELECT
lgt.trx_header_id,
lgt.trx_line_id,
cgt.trx_contingency_id,
arp_standard.fnd_message('AR_RVMG_INVALID_CONTINGENCY'),
cgt.contingency_id
FROM ar_trx_lines_gt lgt,
ar_trx_header_gt hgt,
ar_trx_contingencies_gt cgt
WHERE lgt.trx_header_id = hgt.trx_header_id
AND cgt.trx_line_id = lgt.trx_line_id
AND NOT EXISTS
(
SELECT 'valid lookup code'
FROM ar_deferral_reasons l
WHERE l.contingency_id = cgt.contingency_id
);
debug('contingency validation errors inserted: ' || l_error_count);
INSERT INTO ar_trx_errors_gt
(
trx_header_id,
trx_line_id,
trx_contingency_id,
error_message,
invalid_value
)
SELECT
lgt.trx_header_id,
lgt.trx_line_id,
cgt.trx_contingency_id,
arp_standard.fnd_message('AR_RVMG_NO_EXP_DATE'),
cgt.contingency_id
FROM ar_trx_lines_gt lgt,
ar_trx_header_gt hgt,
ar_trx_contingencies_gt cgt,
ar_deferral_reasons dr
WHERE lgt.trx_header_id = hgt.trx_header_id
AND cgt.trx_line_id = lgt.trx_line_id
AND cgt.contingency_id = dr.contingency_id
AND cgt.expiration_date IS NOT NULL
AND dr.expiration_event_code IS NOT NULL
AND NVL(cgt.completed_flag, 'N') = 'N';
debug('contingency validation errors inserted: ' || l_error_count);
INSERT INTO ra_interface_errors
(
interface_line_id,
interface_contingency_id,
message_text,
invalid_value,
org_id
)
SELECT
l.interface_line_id,
c.interface_contingency_id,
arp_standard.fnd_message('AR_RVMG_INVALID_CONTINGENCY'),
c.contingency_id,
l.org_id
FROM ra_interface_lines l,
ar_interface_conts c
WHERE l.request_id = p_request_id
AND c.interface_line_id = l.interface_line_id
AND NOT EXISTS
(
SELECT 'valid lookup code'
FROM ar_deferral_reasons l
WHERE l.contingency_id = c.contingency_id
);
debug('validation errors inserted: ' || l_error_count);
INSERT INTO ra_interface_errors
(
interface_line_id,
interface_contingency_id,
message_text,
invalid_value,
org_id
)
SELECT
l.interface_line_id,
c.interface_contingency_id,
arp_standard.fnd_message('AR_RVMG_NO_EXP_DATE'),
c.contingency_id,
l.org_id
FROM ra_interface_lines l,
ar_interface_conts c,
ar_deferral_reasons dr
WHERE l.request_id = p_request_id
AND c.interface_line_id = l.interface_line_id
AND dr.contingency_id = c.contingency_id
AND c.expiration_date IS NOT NULL
AND dr.expiration_event_code IS NOT NULL
AND NVL(c.completed_flag, 'N') = 'N';
debug('contingency validation errors inserted: ' || l_error_count);
SELECT c.currency_code,
c.precision,
c.minimum_accountable_unit
FROM ar_system_parameters sysp,
gl_sets_of_books sob,
fnd_currencies c
WHERE sob.set_of_books_id = sysp.set_of_books_id
AND sob.currency_code = c.currency_code;
PROCEDURE update_deferred_lines (
p_customer_trx_id NUMBER DEFAULT NULL,
p_customer_trx_line_id NUMBER DEFAULT NULL,
p_line_status NUMBER DEFAULT NULL,
p_manual_override VARCHAR2 DEFAULT NULL,
p_amount_recognized NUMBER DEFAULT NULL,
p_acctd_amount_recognized NUMBER DEFAULT NULL,
p_amount_pending NUMBER DEFAULT NULL,
p_acctd_amount_pending NUMBER DEFAULT NULL) IS
l_sysdate DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
debug('update_deferred_lines()+');
debug('** update_deferred_lines parameters **');
l_last_updated_by := arp_global.user_id;
l_last_update_login := arp_global.last_update_login;
UPDATE ar_deferred_lines
SET line_collectible_flag = nvl(l_line_collectible,
line_collectible_flag),
manual_override_flag = nvl(p_manual_override,
manual_override_flag),
last_updated_by = l_last_updated_by,
last_update_date = l_sysdate,
last_update_login = l_last_update_login
WHERE customer_trx_id = p_customer_trx_id;
UPDATE ar_deferred_lines
SET line_collectible_flag = nvl(l_line_collectible,
line_collectible_flag),
manual_override_flag = nvl(p_manual_override,
manual_override_flag),
amount_recognized = nvl(p_amount_recognized,
amount_recognized),
acctd_amount_recognized = nvl(p_acctd_amount_recognized,
acctd_amount_recognized),
amount_pending = nvl(p_amount_pending, amount_pending),
acctd_amount_pending = nvl(p_acctd_amount_pending,
acctd_amount_pending),
last_updated_by = l_last_updated_by,
last_update_date = l_sysdate,
last_update_login = l_last_update_login
WHERE customer_trx_line_id = p_customer_trx_line_id;
debug('update_deferred_lines()-');
debug('NO_DATA_FOUND: update_deferred_lines');
debug('OTHERS: update_deferred_lines');
END update_deferred_lines;
SELECT 1
FROM ra_customer_trx rctl
WHERE rctl.customer_trx_id = p_customer_trx_id
AND invoicing_rule_id IS NOT NULL;
SELECT 1
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id
AND account_set_flag = 'N'
AND rownum = 1;
SELECT 1
FROM ar_deferred_lines
WHERE customer_trx_id = p_customer_trx_id
AND manual_override_flag = 'N'
AND line_collectible_flag = 'N';
update_deferred_lines (
p_customer_trx_id => p_customer_trx_id,
p_manual_override => 'Y');
update_deferred_lines (
p_customer_trx_line_id => p_customer_trx_line_id,
p_manual_override => 'Y');
PROCEDURE update_for_event (
p_cust_trx_line_id IN NUMBER,
p_event_date IN DATE,
p_event_code IN VARCHAR2) IS
l_user_id NUMBER;
SELECT dr.contingency_id, revrec_event_code, expiration_event_code
FROM ar_line_conts lc,
ar_deferral_reasons dr
WHERE lc.contingency_id = dr.contingency_id
AND lc.customer_trx_line_id = p_cust_trx_line_id
AND (dr.revrec_event_code = p_event_code OR
dr.expiration_event_code = p_event_code);
debug('update_for_event()+');
debug('** update_for_event parameters **');
UPDATE ar_line_conts
SET expiration_date =
DECODE(cont_rec.expiration_event_code, p_event_code,
NVL(p_event_date + expiration_days,expiration_date),
expiration_date),
expiration_event_date =
DECODE(cont_rec.expiration_event_code, p_event_code,
NVL(p_event_date,
NVL(expiration_date - expiration_days,
expiration_date))),
completed_flag =
DECODE(cont_rec.revrec_event_code, p_event_code,'Y',
completed_flag),
completed_by =
DECODE(cont_rec.revrec_event_code, p_event_code,
fnd_global.user_id, completed_by),
reason_removal_date =
DECODE(cont_rec.revrec_event_code, p_event_code,
sysdate, reason_removal_date),
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_user_id
WHERE customer_trx_line_id = p_cust_trx_line_id
AND contingency_id = cont_rec.contingency_id;
debug('update_for_event()-');
debug('NO_DATA_FOUND: update_for_event');
debug('OTHERS: update_for_event');
END update_for_event;
UPDATE ar_deferred_lines
SET amount_recognized = amount_recognized + p_delta_amount -
l_rev_adj_rec.amount +
l_adjustable_amount,
acctd_amount_recognized = acctd_amount_recognized + p_acctd_delta_amount -
p_acctd_amount +
l_acctd_adjustable_amount
WHERE customer_trx_line_id = p_customer_trx_line_id;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_customer_account_id;
SELECT credit_classification
FROM hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_account_id
AND site_use_id = p_site_use_id;
SELECT credit_classification
FROM hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_account_id
AND site_use_id IS NULL;
SELECT credit_classification
FROM hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = -1;
SELECT parent_id
FROM hz_hierarchy_nodes
WHERE child_id = p_child_id
AND parent_table_name = 'HZ_PARTIES'
AND parent_object_type = 'ORGANIZATION'
AND hierarchy_type = 'CREDIT'
AND level_number > 0
AND effective_start_date <= trunc(sysdate)
AND effective_end_date >= trunc(sysdate);
SELECT credit_classification
FROM hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = -1
AND site_use_id IS NULL;
SELECT sum(amount_recognized) + sum(amount_pending)
FROM ar_deferred_lines
WHERE customer_trx_id = p_customer_trx_id;
SELECT sum(acctd_amount_recognized) + sum(acctd_amount_pending)
FROM ar_deferred_lines
WHERE customer_trx_id = p_customer_trx_id;
SELECT applied_customer_trx_id,
acctd_amount_applied_to,
tax_applied,
receivables_charges_applied,
line_applied,
freight_applied,
gl_date
FROM ar_receivable_applications
WHERE receivable_application_id = p_receivable_application_id;
SELECT invoice_currency_code,
exchange_rate
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id;
SELECT lc.customer_trx_line_id,
lc.contingency_id,
lc.expiration_date
FROM ar_line_conts lc,
ar_deferral_reasons dr
WHERE lc.customer_trx_line_id = p_customer_trx_line_id
AND lc.contingency_id = dr.contingency_id
AND lc.completed_flag = 'N'
AND lc.expiration_date IS NOT NULL
AND trunc(lc.expiration_date) <= trunc(sysdate);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.user_id;
UPDATE ar_line_conts
SET completed_flag = 'Y',
reason_removal_date = sysdate,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login
WHERE customer_trx_line_id = l_cust_trx_line_id_tbl(i)
AND contingency_id = l_contingency_id_tbl(i);
SELECT 1
FROM ar_line_conts lc,
ar_deferral_reasons dr
WHERE lc.contingency_id = dr.contingency_id
AND lc.customer_trx_line_id = p_cust_trx_line_id
AND lc.completed_flag = 'N'
AND dr.revrec_event_code = 'RECEIPT_APPLICATION';
SELECT 1
FROM ar_line_conts lc,
ar_deferral_reasons dr
WHERE lc.contingency_id = dr.contingency_id
AND lc.customer_trx_line_id = p_cust_trx_line_id
AND lc.completed_flag = 'N'
AND dr.revrec_event_code <> 'RECEIPT_APPLICATION';
| PUBLIC PROCEDURE update_line_conts
|
| DESCRIPTION
| This procedures lets calling programs update contingencies
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| RAM Wizard
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| None.
|
| PARAMETERS
| None.
|
| NOTES
| None.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 28-APR-2005 ORASHID Subroutine Created
| 29-JUN-2006 MRAYMOND 5201842 - Fixed expiration_event_date
| to use correct parameter.
*===========================================================================*/
PROCEDURE update_line_conts (
p_customer_trx_line_id NUMBER,
p_contingency_id NUMBER,
p_expiration_date DATE DEFAULT NULL,
p_expiration_event_date DATE DEFAULT NULL,
p_expiration_days NUMBER DEFAULT NULL,
p_completed_flag VARCHAR2 DEFAULT NULL,
p_reason_removal_date DATE DEFAULT NULL) IS
l_sysdate DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
debug('update_line_conts()+');
l_last_updated_by := arp_global.user_id;
l_last_update_login := arp_global.last_update_login;
UPDATE ar_line_conts
SET expiration_date = nvl(p_expiration_date, expiration_date),
expiration_event_date = nvl(p_expiration_event_date, expiration_event_date),
expiration_days = nvl(p_expiration_days, expiration_days),
completed_flag = nvl(p_completed_flag, completed_flag),
reason_removal_date = nvl(p_reason_removal_date, reason_removal_date),
last_updated_by = l_last_updated_by,
last_update_date = l_sysdate,
last_update_login = l_last_update_login
WHERE customer_trx_line_id = p_customer_trx_line_id
AND contingency_id = p_contingency_id;
debug('update_line_conts()-');
debug('NO_DATA_FOUND: update_line_conts');
debug('OTHERS: update_line_conts');
END update_line_conts;
| PUBLIC PROCEDURE delete_line_conts
|
| DESCRIPTION
| This procedures lets calling programs delete contingencies
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| RAM Wizard
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| None.
|
| PARAMETERS
| None.
|
| NOTES
| None.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 18-MAY-2005 ORASHID Subroutine Created
|
*===========================================================================*/
PROCEDURE delete_line_conts (
p_customer_trx_line_id NUMBER,
p_contingency_id NUMBER) IS
BEGIN
-- This procedure simply deletes a row of data in the
-- ar_deferred_lines table. It will only delete columns
-- for which data is provided, the rest will retain their
-- original values.
IF pg_debug IN ('Y', 'C') THEN
debug('delete_line_conts()+');
debug('** delete_line_conts parameters **');
DELETE
FROM ar_line_conts
WHERE customer_trx_line_id = p_customer_trx_line_id
AND contingency_id = p_contingency_id;
debug('delete_line_conts()-');
debug('NO_DATA_FOUND: delete_line_conts');
debug('OTHERS: delete_line_conts');
END delete_line_conts;
SELECT line_collectible_flag
FROM ar_deferred_lines
WHERE customer_trx_id = p_customer_trx_id
AND customer_trx_line_id = p_customer_trx_line_id;
SELECT 1
FROM ar_deferred_lines
WHERE customer_trx_id = p_customer_trx_id
AND original_collectibility_flag = 'N'
AND manual_override_flag = 'N'
AND rownum = 1;
SELECT 'its a pre-billing contingency'
FROM ra_customer_trx_lines_all ctl,
ar_line_conts_all lrc,
ar_deferral_reasons dr
WHERE ctl.customer_trx_id = p_customer_trx_id
and ctl.customer_trx_line_id=lrc.customer_trx_line_id
and trunc(nvl(lrc.expiration_date,sysdate)) - trunc(sysdate) <= 0
and dr.contingency_id = lrc.contingency_id
AND dr.REVREC_EVENT_CODE = 'INVOICING' ;
Update ar_deferred_lines dl
set original_collectibility_flag = 'Y'
WHERE dl.customer_trx_id = p_customer_trx_id;
| PUBLIC PROCEDURE delete_failed_rows
|
| DESCRIPTION
|
| This procedure deletes rows from the revenue management tables for
| a failed auto invoice run.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Auto Invoice.
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| None.
|
| PARAMETERS
| p_request_id
|
| NOTES
| None.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 24-SEP-2002 ORASHID Subroutine Created
|
*===========================================================================*/
PROCEDURE delete_failed_rows (p_request_id IN NUMBER) IS
BEGIN
IF pg_debug IN ('Y', 'C') THEN
debug('ar_revenue_management_pvt.delete_failed_rows()+');
debug('** delete_failed_rows parameters **');
DELETE FROM ar_deferred_lines
WHERE request_id = p_request_id;
DELETE FROM ar_line_conts
WHERE request_id = p_request_id;
debug('ar_revenue_management_pvt.delete_failed_rows()-');
debug('NO_DATA_FOUND: delete_failed_rows');
debug('OTHERS: delete_failed_rows');
END delete_failed_rows;
| PUBLIC PROCEDURE delete_rejected_rows
|
| DESCRIPTION
|
| This procedure deletes rows those are rejected by auto invoice
| from the revenue management tables.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Auto Invoice.
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| None.
|
| PARAMETERS
| p_request_id
|
| NOTES
| None.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 23-OCT-2002 ORASHID Subroutine Created
| 06-APR-2004 MRAYMOND Modified sql to remove all
| rows for a specific transaction
| when any one is in error.
*===========================================================================*/
PROCEDURE delete_rejected_rows (p_request_id IN NUMBER) IS
BEGIN
debug('ar_revenue_management_pvt.delete_rejected_rows()+');
debug('** delete_rejected_rows parameters **');
DELETE
FROM ar_line_conts
WHERE customer_trx_line_id IN
(
SELECT customer_trx_line_id
FROM ar_deferred_lines
WHERE customer_trx_id IN
(
SELECT DISTINCT il.customer_trx_id
FROM ra_interface_errors ie,
ra_interface_lines il
WHERE ie.interface_line_id = il.interface_line_id
AND il.request_id = p_request_id
)
);
debug('contingencies deleted : ' || SQL%ROWCOUNT);
DELETE
FROM ar_deferred_lines
WHERE customer_trx_id IN
(
SELECT DISTINCT il.customer_trx_id
FROM ra_interface_errors ie,
ra_interface_lines il
WHERE ie.interface_line_id = il.interface_line_id
AND il.request_id = p_request_id
);
debug('lines deleted : ' || SQL%ROWCOUNT);
debug('ar_revenue_management_pvt.delete_rejected_rows()-');
debug('NO_DATA_FOUND: delete_rejected_rows');
debug('OTHERS: delete_rejected_rows');
END delete_rejected_rows;
SELECT 1
FROM ar_deferred_lines dl,
ar_line_conts lc,
ar_deferral_reasons dl
WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
AND lc.contingency_id = dl.contingency_id
AND lc.completed_flag = 'N'
AND dl.customer_trx_id = p_customer_trx_id
AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
dl.customer_trx_line_id)
AND dl.revrec_event_code = 'CUSTOMER_ACCEPTANCE';
SELECT 1
FROM ar_deferred_lines dl,
ar_line_conts lc,
ar_deferral_reasons dr
WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
AND lc.contingency_id = dr.contingency_id
AND lc.completed_flag = 'N'
AND dr.revrec_event_code = 'RECEIPT_APPLICATION'
AND dl.customer_trx_id = p_customer_trx_id;
SELECT customer_trx_id,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending
FROM ar_deferred_lines
WHERE customer_trx_line_id = p_cust_trx_line_id;
update_for_event(
p_cust_trx_line_id => p_cust_trx_line_id,
p_event_date => p_event_date,
p_event_code => p_event_code);
l_rev_adj_rec.line_selection_mode := 'S';
update_deferred_lines (
p_customer_trx_line_id => p_cust_trx_line_id,
p_line_status => l_line_status,
p_amount_recognized => l_amount_due_original,
p_acctd_amount_recognized => l_acctd_amount_due_orig,
p_amount_pending => 0,
p_acctd_amount_pending => 0);
l_rev_adj_rec.line_selection_mode := 'S';
update_deferred_lines (
p_customer_trx_line_id => p_cust_trx_line_id,
p_line_status => l_line_status,
p_amount_recognized => l_amount_pending,
p_acctd_amount_recognized => l_acctd_amount_pending,
p_amount_pending => 0,
p_acctd_amount_pending => 0);
| update_deferred_lines
|
| PARAMETERS
| p_mode
| p_customer_trx_id
| p_customer_trx_line_id
| p_gl_date
| p_comments
| p_ram_desc_flexfield
|
| NOTES
| This procedure will be called for any RAM adjustments done any where in
| the system. A new field has been added to RAM record structure called
| source. If the source is not this package
| (c_source_revenue_management_source) then a call will be placed here
| to indicate manual override.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 26-JUL-2002 ORASHID Subroutine Created
| 26-APR-2006 MRAYMOND 5043785 - Added logic to prevent
| child insert and OM call when
| acceptance contingency is not
| sourced from OM
|
*=======================================================================*/
PROCEDURE revenue_synchronizer (
p_mode IN NUMBER,
p_customer_trx_id IN NUMBER,
p_customer_trx_line_id IN NUMBER,
p_gl_date IN DATE,
p_comments IN VARCHAR2,
p_ram_desc_flexfield IN desc_flexfield,
x_scenario OUT NOCOPY NUMBER,
x_first_adjustment_number OUT NOCOPY NUMBER,
x_last_adjustment_number OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
/*-----------------------------------------------------------------------+
| Cursor Declarations |
+-----------------------------------------------------------------------*/
-- This cursor retrieves all the valid rows from the revenue
-- management tables.
CURSOR rev_lines IS
SELECT customer_trx_line_id,
customer_trx_id,
amount_due_original,
acctd_amount_due_original,
amount_recognized,
acctd_amount_recognized,
amount_pending,
acctd_amount_pending
FROM ar_reviewed_lines_gt
WHERE request_id = nvl(p_customer_trx_line_id, -- 7328069
nvl(p_customer_trx_id,
request_id));
p_mode => 'UPDATE');
lr_customer_trx_line_id_tbl.delete;
lr_customer_trx_id_tbl.delete;
lr_amount_due_original_tbl.delete;
lr_acctd_amount_due_orig_tbl.delete;
lr_amount_recognized_tbl.delete;
lr_acctd_amt_recognized_tbl.delete;
lr_amount_pending_tbl.delete;
lr_acctd_amount_pending_tbl.delete;
update_for_event(
p_cust_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_event_date => sysdate,
p_event_code => 'CUSTOMER_ACCEPTANCE');
l_rev_adj_rec.line_selection_mode := 'S';
update_deferred_lines (
p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_line_status => l_line_status,
p_amount_recognized => lr_amount_due_original_tbl(i),
p_acctd_amount_recognized => lr_acctd_amount_due_orig_tbl(i),
p_amount_pending => 0,
p_acctd_amount_pending => 0);
l_rev_adj_rec.line_selection_mode := 'S';
update_deferred_lines (
p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_line_status => l_line_status,
p_amount_recognized => lr_amount_pending_tbl(i),
p_acctd_amount_recognized => lr_acctd_amount_pending_tbl(i),
p_amount_pending => 0,
p_acctd_amount_pending => 0);
| update_deferred_lines
|
| PARAMETERS
| None.
|
| NOTES
| Note that creditworthiness of a customer will be never be checked again.
| This function only checks for expiration.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 26-JUL-2002 ORASHID Subroutine Created
|
| 31-OCT-2005 APANDIT Enabling this conc program to
| be run as multi-org
| 03-FEB-2006 JBECKETT Bug 4757939 - Added org_id parameter
*=======================================================================*/
PROCEDURE periodic_sweeper (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_org_id IN NUMBER) IS
/*-----------------------------------------------------------------------+
| Cursor Declarations |
+-----------------------------------------------------------------------*/
-- This cursor retrieves all the lines which are contingency based
CURSOR expiring_lines (p_request_id NUMBER) IS
SELECT customer_trx_line_id,
MAX(customer_trx_id),
MAX(amount_due_original),
MAX(acctd_amount_due_original),
MAX(amount_recognized),
MAX(acctd_amount_recognized),
MAX(amount_pending),
MAX(acctd_amount_pending),
MAX(expiration_date)
FROM ar_reviewed_lines_gt
WHERE request_id = p_request_id
GROUP BY customer_trx_line_id;
SELECT org_id FROM ar_system_parameters
WHERE org_id = NVL(p_org_id,org_id);
Delete the data in the global temporary tables */
delete from ar_rdr_parameters_gt;
delete from ar_trx_errors_gt;
delete from ar_trx_header_gt;
delete from ar_trx_lines_gt;
delete from ar_reviewed_lines_gt;
delete from fun_rule_bulk_result_gt;
delete from ar_trx_contingencies_gt;
lr_customer_trx_line_id_tbl.delete;
lr_customer_trx_id_tbl.delete;
lr_amount_due_original_tbl.delete;
lr_acctd_amount_due_orig_tbl.delete;
lr_amount_recognized_tbl.delete;
lr_acctd_amt_recognized_tbl.delete;
lr_amount_pending_tbl.delete;
lr_acctd_amount_pending_tbl.delete;
lr_expiration_date_tbl.delete;
l_rev_adj_rec.line_selection_mode := 'S';
select trx_number
into l_trx_number
from ra_customer_trx
where customer_trx_id = lr_customer_trx_id_tbl(i);
select trx_number
into l_trx_number
from ra_customer_trx
where customer_trx_id = lr_customer_trx_id_tbl(i);
debug('update rvmg table');
update_deferred_lines (
p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_line_status => l_line_status,
p_amount_recognized => lr_amount_recognized_tbl(i),
p_acctd_amount_recognized => lr_acctd_amt_recognized_tbl(i),
p_amount_pending => lr_amount_pending_tbl(i),
p_acctd_amount_pending => lr_acctd_amount_pending_tbl(i));
| update_deferred_lines
| adjust_revenue
|
| PARAMETERS
|
| p_mode
| p_customer_trx_id
| p_acctd_amount_applied
| p_exchange_rate
| p_invoice_currency_code
| p_tax_applied
| p_charges_applied
| p_freight_applied
| p_line_applied
| p_receivable_application_id
|
| KNOWN ISSUES
| Enter business functionality which was de-scoped as part of the
| implementation. Ideally this should never be used.
|
| NOTES
| The receipt analyzer does a variety of things depending on what
| is the scenation it is handling. Below, I give a matrix of what
| it does for future use.
|
| SCENARIO ACTION
|
| Cash Based Recognize Up To The Receipt Amount
| Combination Put In The Pending Column Up to The
| Receipt Amount
| Contingency Based No action, it will be recognized by the
| Peridioc Sweeper.
| Recognize Recognize Fully.
|
| Let me try to give the functional reasoning behind each one of the
| scenario above. The first case is where only problem is a credit
| problem and/or payment term problem. In both cases, the we are
| doubtfult that we may not collect money for it. So, when money
| arrives, we can immediately recognize it.
|
| In the second scneario, it is the very similar to the first one
| however, there may be a non-standard refund clause so we can not
| recognize any revenue until that has expired. At the same time,
| we do not lose track of this receipt. So, we put in pending, and
| as soon as the expiration happens this pending amount will be
| recognized.
|
| Third scenario is the simplest, we do not do anything. Simply because
| when all expire the periodic sweeper is smart enough to recognize the
| entire amount.
|
| The fourth scenario happens when intially there was a non-standard
| refund policy for this line. And just before this receipt arrived,
| this expired, so now we should do the periodc sweeper's job and
| recognize all revenue.
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 26-JUL-2002 ORASHID Subroutine Created
|
*=======================================================================*/
PROCEDURE receipt_analyzer (
p_mode IN VARCHAR2 DEFAULT NULL,
p_customer_trx_id IN NUMBER DEFAULT NULL,
p_acctd_amount_applied IN NUMBER DEFAULT NULL,
p_exchange_rate IN NUMBER DEFAULT NULL,
p_invoice_currency_code IN VARCHAR2 DEFAULT NULL,
p_tax_applied IN NUMBER DEFAULT NULL,
p_charges_applied IN NUMBER DEFAULT NULL,
p_freight_applied IN NUMBER DEFAULT NULL,
p_line_applied IN NUMBER DEFAULT NULL,
p_receivable_application_id IN NUMBER DEFAULT NULL,
p_gl_date IN DATE DEFAULT NULL) IS
/*-----------------------------------------------------------------------+
| Cursor Declarations |
+-----------------------------------------------------------------------*/
-- This cursor retrieves all the deferred lines
/* 9320279 - added CM amounts to cursor */
CURSOR rev_lines (p_trx_id NUMBER) IS
SELECT adl.customer_trx_line_id,
adl.customer_trx_id,
adl.line_collectible_flag,
adl.amount_due_original,
adl.acctd_amount_due_original,
adl.amount_recognized,
adl.acctd_amount_recognized,
adl.amount_pending,
adl.acctd_amount_pending,
SUM(NVL(gld.amount,0)), SUM(NVL(gld.acctd_amount,0)),
SUM(decode(ctrl.customer_trx_id, NULL, 0, 1))
FROM ar_deferred_lines adl,
ra_customer_trx_lines ctrl,
ra_cust_trx_line_gl_dist gld
WHERE adl.customer_trx_id = p_trx_id
AND adl.customer_trx_id = ctrl.previous_customer_trx_id (+)
AND adl.customer_trx_line_id = ctrl.previous_customer_trx_line_id (+)
AND ctrl.customer_trx_line_id = gld.customer_trx_line_id (+)
GROUP BY adl.customer_trx_line_id, adl.customer_trx_id,
adl.line_collectible_flag, adl.amount_due_original,
adl.acctd_amount_due_original, adl.amount_recognized,
adl.acctd_amount_recognized, adl.amount_pending,
adl.acctd_amount_pending;
SELECT sum(amount_due_original),
sum(acctd_amount_due_original)
FROM ar_deferred_lines
WHERE customer_trx_id = p_trx_id;
SELECT sum(nvl(amount,0)), sum(nvl(acctd_amount,0))
FROM ra_cust_trx_line_gl_dist gld,
ra_customer_trx ctrx
WHERE ctrx.previous_customer_trx_id = p_trx_id
AND ctrx.customer_trx_id = gld.customer_trx_id
AND account_class in ('REV','UNEARN');
SELECT nvl(allow_overapplication_flag,'N')
FROM ra_cust_trx_types rtt, ra_customer_trx trx
WHERE customer_trx_id = p_trx_id
AND trx.cust_trx_type_id = rtt.cust_trx_type_id;
lr_customer_trx_line_id_tbl.delete;
lr_customer_trx_id_tbl.delete;
lr_line_collectible_tbl.delete;
lr_amount_due_original_tbl.delete;
lr_acctd_amount_due_orig_tbl.delete;
lr_amount_recognized_tbl.delete;
lr_acctd_amt_recognized_tbl.delete;
lr_amount_pending_tbl.delete;
lr_acctd_amount_pending_tbl.delete;
lr_cm_amount_tbl.delete;
lr_cm_acctd_amount_tbl.delete;
lr_cm_exists_tbl.delete;
l_rev_adj_rec.line_selection_mode := 'S';
update_deferred_lines (
p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_line_status => l_line_status,
p_amount_recognized => l_amount_adjusted,
p_acctd_amount_recognized => l_acctd_amount_adjusted,
p_amount_pending => 0,
p_acctd_amount_pending => 0);
update_deferred_lines (
p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_line_status => l_line_status,
p_amount_recognized => l_amount_adjusted,
p_acctd_amount_recognized => l_acctd_amount_adjusted,
p_amount_pending => 0,
p_acctd_amount_pending => 0);
update_deferred_lines (
p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_line_status => l_line_status,
p_amount_recognized => l_amount_adjusted,
p_acctd_amount_recognized => l_acctd_amount_adjusted);
update_deferred_lines (
p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_amount_pending => l_amount_adjusted,
p_acctd_amount_pending => l_acctd_amount_adjusted);
update_deferred_lines (
p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
p_amount_pending => l_amount_adjusted,
p_acctd_amount_pending => l_acctd_amount_adjusted);
SELECT ara.rowid,
ara.applied_customer_trx_id,
ara.acctd_amount_applied_to,
ara.tax_applied,
ara.receivables_charges_applied,
ara.line_applied,
ara.freight_applied,
rct.invoice_currency_code,
rct.exchange_rate,
ara.gl_date
FROM ar_receivable_applications ara,
ra_customer_trx rct
WHERE ara.request_id = p_request_id
AND ara.applied_customer_trx_id = rct.customer_trx_id;
l_rowid_tbl.delete;
SELECT ctl.customer_trx_line_id,
decode(lrs.line_collectible_flag, NULL, 1, 'Y', 1, 'N', 0) verdict
FROM ra_customer_trx_lines ctl,
ar_deferred_lines lrs
WHERE ((p_request_id IS NULL AND
p_customer_trx_line_id IS NOT NULL AND
ctl.customer_trx_line_id = p_customer_trx_line_id) OR
(p_request_id IS NOT NULL AND
ctl.request_id = p_request_id))
AND ctl.line_type = 'LINE'
AND ctl.customer_trx_line_id = lrs.customer_trx_line_id (+)
ORDER BY ctl.customer_trx_line_id;
insert_contingencies_from_gt(p_request_id => p_request_id);
insert_contingencies_from_itf(p_request_id => p_request_id);
debug('contingency rows inserted: ' || SQL%ROWCOUNT);
delete_unwanted_contingencies (p_request_id => p_request_id
,p_customer_trx_line_id => p_customer_trx_line_id);
insert_deferred_lines (p_request_id => p_request_id
,p_customer_trx_line_id => p_customer_trx_line_id);
debug('deferred rows inserted: ' || SQL%ROWCOUNT);