[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Update rows of consolidated billing invoice or rows associated with |
| specified concurrent request id to print status of 'PENDING' so report |
| ARXCBI will print them. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS : IN: |
| P_consinv_id - consolidated billing invoice |
| P_request_id - concurrent request id |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
*----------------------------------------------------------------------------*/
PROCEDURE reprint (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
BEGIN
UPDATE ar_cons_inv
SET print_status = 'PENDING',
last_update_date = arp_global.last_update_date,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
AND concurrent_request_id = DECODE(P_consinv_id,
NULL, P_request_id,
concurrent_request_id);
| Updates rows for draft versions of consolidated billing invoices to |
| status of 'PRINTED', from a prior status of 'DRAFT' |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS : IN: |
| P_consinv_id - Consolidated Billing Invoice id |
| P_request_id - Concurrent Request Id associated with |
| rows that are to be accepted. |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
*----------------------------------------------------------------------------*/
PROCEDURE accept( P_cust_num_low IN VARCHAR2,
P_cust_num_high IN VARCHAR2,
P_bill_site_low IN NUMBER,
P_bill_site_high IN NUMBER,
P_bill_date_low IN DATE,
P_bill_date_high IN DATE,
P_consinv_num_low IN NUMBER,
P_consinv_num_high IN NUMBER,
P_request_id IN NUMBER) IS
BEGIN
write_debug_and_log ( ' Parameters into accept:' );
UPDATE ar_cons_inv
SET status = 'ACCEPTED',
last_update_date = arp_global.last_update_date,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
WHERE customer_id in (select cust_account_id
from hz_cust_accounts c
where c.account_number
between nvl(P_cust_num_low, c.account_number)
and nvl(P_cust_num_high, c.account_number))
AND site_use_id between nvl(P_bill_site_low, site_use_id) and
nvl(P_bill_site_high, site_use_id)
AND billing_date between nvl(P_bill_date_low, billing_date) and
nvl(P_bill_date_high, billing_date)
AND cons_billing_number between nvl(P_consinv_num_low, cons_billing_number) and
nvl(P_consinv_num_high, cons_billing_number)
AND concurrent_request_id = nvl(P_request_id, concurrent_request_id)
AND status = 'DRAFT';
| Will delete the consolidated billing invoice or all consolidated |
| billing invoices associated with the specified concurrent request id. |
| All of the AR tables that have been updated with these consolidated |
| billing invoice id's will be updated so that these deleted id's are |
| no longer referenced. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS : IN: |
| P_consinv_id - Consolidated Billing Invoice id |
| P_request_id - Concurrent Request Id |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| C M Clyde 28 Aug 97 Modified to include transaction types of |
| 'XSITE XCURR RECAPP', 'XSITE XCURR RECREV', |
| 'XCURR RECAPP', 'XCURR RECREV'. |
| |
*----------------------------------------------------------------------------*/
PROCEDURE reject( P_cust_num_low IN VARCHAR2,
P_cust_num_high IN VARCHAR2,
P_bill_site_low IN NUMBER,
P_bill_site_high IN NUMBER,
P_bill_date_low IN DATE,
P_bill_date_high IN DATE,
P_consinv_num_low IN NUMBER,
P_consinv_num_high IN NUMBER,
P_request_id IN NUMBER) IS
BEGIN
write_debug_and_log ( ' Parameters into Reject:');
UPDATE ar_cons_inv
SET status = 'PRE_REJECTED',
print_status = 'PRINTED',
last_update_date = arp_global.last_update_date,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
WHERE customer_id in (select cust_account_id
from hz_cust_accounts c
where c.account_number
between nvl(P_cust_num_low, c.account_number)
and nvl(P_cust_num_high, c.account_number))
AND site_use_id between nvl(P_bill_site_low, site_use_id) and
nvl(P_bill_site_high, site_use_id)
AND billing_date between nvl(P_bill_date_low, billing_date) and
nvl(P_bill_date_high, billing_date)
AND cons_billing_number between nvl(P_consinv_num_low, cons_billing_number) and
nvl(P_consinv_num_high, cons_billing_number)
AND concurrent_request_id = nvl(P_request_id, concurrent_request_id)
AND status = 'DRAFT';
UPDATE ra_customer_trx
SET printing_original_date =
DECODE(printing_count,
1, NULL,
printing_original_date),
printing_last_printed =
DECODE(printing_count,
1, NULL,
printing_last_printed),
printing_count = DECODE(printing_count,
1, NULL,
printing_count - 1)
WHERE customer_trx_id IN
(SELECT PS.customer_trx_id
FROM ar_payment_schedules PS,
ar_cons_inv_trx IT,
ar_cons_inv CI
WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
AND CI.cons_inv_id = IT.cons_inv_id
AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED')
AND PS.payment_schedule_id = IT.adj_ps_id);
UPDATE ar_payment_schedules
SET cons_inv_id = NULL
WHERE payment_schedule_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv CI,
ar_cons_inv_trx IT
WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
'RECEIPT')
AND CI.cons_inv_id = IT.cons_inv_id
AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED'));
UPDATE ar_payment_schedules
SET cons_inv_id_rev = NULL
WHERE payment_schedule_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv CI,
ar_cons_inv_trx IT
WHERE IT.transaction_type = 'RECEIPT REV'
AND CI.cons_inv_id = IT.cons_inv_id
AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED'));
UPDATE ar_receivable_applications
SET cons_inv_id = NULL
WHERE receivable_application_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv CI,
ar_cons_inv_trx IT
WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
'XCURR RECREV', 'XSITE XCURR RECREV',
'EXCLUDE RECREV', 'EXCLUDE_CMREV')
AND CI.cons_inv_id = IT.cons_inv_id
AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED'));
UPDATE ar_receivable_applications
SET cons_inv_id_to = NULL
WHERE receivable_application_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv CI,
ar_cons_inv_trx IT
WHERE IT.transaction_type IN ('XSITE RECAPP','XSITE_CMAPP',
'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
'EXCLUDE RECAPP', 'EXCLUDE_CMAPP',
'DELAY_CMAPP')
AND CI.cons_inv_id = IT.cons_inv_id
AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED'));
UPDATE ar_adjustments
SET cons_inv_id = NULL
WHERE adjustment_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv CI,
ar_cons_inv_trx IT
WHERE IT.transaction_type = 'ADJUSTMENT'
AND CI.cons_inv_id = IT.cons_inv_id
AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED'));
DELETE FROM ar_cons_inv_trx_lines
WHERE cons_inv_id IN
(SELECT CI.cons_inv_id
FROM ar_cons_inv CI
WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED'));
DELETE FROM ar_cons_inv_trx
WHERE cons_inv_id IN
(SELECT CI.cons_inv_id
FROM ar_cons_inv CI
WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED'));
UPDATE ar_cons_inv CI
SET status = 'REJECTED'
WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
where i.status = 'PRE_REJECTED');
UPDATE RA_CUSTOMER_TRX
SET term_id = new_term_id,
billing_date = new_bill_date,
term_due_date = new_due_date
WHERE customer_trx_id = P_trx_id;
UPDATE AR_PAYMENT_SCHEDULES
SET due_date = new_due_date
WHERE customer_trx_id = P_trx_id;
SELECT acct.cust_account_id customer_id,
nvl(cp1.cons_bill_level, cp2.cons_bill_level) site_bill_level,
cp2.cons_bill_level acct_bill_level,
nvl(cp1.standard_terms, cp2.standard_terms) site_term,
cp2.standard_terms acct_term
FROM hz_cust_accounts acct,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses,
hz_customer_profiles cp1,
hz_customer_profiles cp2
WHERE acct.account_number between P_cust_num_low and P_cust_num_high
AND acct_site.cust_account_id = acct.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_id between P_bill_site_low and P_bill_site_high
AND cp1.cust_account_id = acct.cust_account_id
AND cp1.site_use_id(+) = site_uses.site_use_id
AND cp2.cust_account_id = acct.cust_account_id
AND cp2.site_use_id IS NULL;
SELECT T.term_id term_id,
TL.due_days due_day,
TL.due_day_of_month due_dom,
TL.due_months_forward due_mf
FROM ra_terms T,
ra_terms_lines TL
WHERE TL.term_id = T.term_id
AND T.billing_cycle_id = C_billing_cycle_id
AND T.term_id = nvl(C_term_id, T.term_id)
order by 1;
SELECT CT.customer_trx_id trx_id,
CT.trx_date trx_date,
CT.trx_number trx_number,
PS.class class,
PS.payment_schedule_id schedule_id,
PS.amount_due_original amount_due,
PS.tax_original tax,
PS.invoice_currency_code currency,
CT.term_id term_id,
CT.billing_date billing_date,
CT.initial_customer_trx_id init_trx_id,
CT.previous_customer_trx_id prev_trx_id,
CT.interface_header_attribute1 trx_desc,
CT.ship_to_site_use_id ship_id,
CT.term_due_date due_date
FROM ra_customer_trx CT,
ar_payment_schedules PS
WHERE PS.customer_site_use_id = C_site_use_id
AND PS.cons_inv_id IS NULL
AND PS.invoice_currency_code = nvl(C_use_currency, PS.invoice_currency_code)
AND CT.customer_trx_id = PS.customer_trx_id
AND CT.printing_option = 'PRI'
AND PS.class IN ('INV', 'DM', 'DEP', 'CB','CM')
AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
AND nvl(CT.billing_date, CT.trx_date) <= C_billing_date
ORDER BY 10, 1;
SELECT payment_schedule_id
FROM ar_payment_schedules
WHERE customer_trx_id = C_trx_id
ORDER BY creation_date DESC;
C_site_select VARCHAR2(5000);
SELECT link_to_cust_trx_line_id,
sum(nvl(CTL.extended_amount,0)),
sum(decode(amount_includes_tax_flag, 'Y', nvl(CTL.extended_amount,0),0))
FROM ra_customer_trx_lines CTL
WHERE CTL.customer_trx_id = l_trx_id
AND CTL.line_type = 'TAX'
GROUP BY link_to_cust_trx_line_id;
select start_date
into l_cycle_start_date
from ar_cons_bill_cycles_b
where billing_cycle_id = p_billing_cycle_id;
SELECT bucket_name
INTO l_bucket_name
FROM ar_aging_buckets
WHERE aging_bucket_id = 2;
c_site_select :=
'SELECT /*+ ORDERED */ ' ||
'acct_site.cust_account_id customer_id, ' ||
'site_uses.site_use_id site_id, ' ||
'ar_bfb_utils_pvt.get_bill_level(acct_site.cust_account_id) bill_level, ' ||
'decode(ar_bfb_utils_pvt.get_bill_level(acct_site.cust_account_id), ' ||
' ''A'', CP.override_terms, ' ||
' ''S'', SP.override_terms) override_terms, ' ||
'decode(ar_bfb_utils_pvt.get_bill_level(acct_site.cust_account_id), ' ||
' ''A'', CP.cons_inv_type, ' ||
' ''S'', SP.cons_inv_type) cons_inv_type ';
' (SELECT NULL ' ||
' FROM ar_cons_inv CI ' ||
' WHERE CI.site_use_id = site_uses.site_use_id ' ||
' AND CI.billing_date >= :billing_date ' ||
' AND CI.currency_code = :currency ' ||
' AND CI.status <> ''REJECTED'') ' ||
'AND NOT EXISTS ' ||
' (SELECT NULL ' ||
' FROM ar_cons_inv CI2 ' ||
' WHERE CI2.site_use_id = site_uses.site_use_id ' ||
' AND CI2.currency_code = :currency ' ||
' AND CI2.status = ''DRAFT'') ';
c_site_stmt := c_site_select || c_site_from || c_site_where;
SELECT ar_cons_inv_s.NEXTVAL
INTO l_consinv_id
FROM dual;
select p.party_id
into l_party_id
from hz_parties p, hz_cust_accounts c
where c.cust_account_id = L_sites.customer_id
and c.party_id = p.party_id;
SELECT sum(ending_balance), max(billing_date)
INTO l_beginning_balance, l_last_bill_date
FROM ar_cons_inv CI1
WHERE CI1.site_use_id = L_sites.site_id
AND CI1.currency_code = P_currency
AND (CI1.status IN ('ACCEPTED', 'FINAL')
AND CI1.billing_date =
(SELECT max(CI2.billing_date)
FROM ar_cons_inv CI2
WHERE CI2.site_use_id = L_sites.site_id
AND CI2.currency_code = P_currency
AND CI2.status IN ('ACCEPTED', 'FINAL')));
write_debug_and_log('......insert to ar_cons_inv, ID: ' || TO_CHAR(l_consinv_id) ||
' number: ' || l_cons_billno);
INSERT INTO ar_cons_inv (cons_inv_id,
cons_billing_number,
customer_id,
site_use_id,
concurrent_request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
cons_inv_type,
status,
print_status,
term_id,
issue_date,
due_date,
currency_code,
beginning_balance,
ending_balance,
org_id,
billing_date,
bill_level_flag,
last_billing_date,
billing_cycle_id,
remit_to_address_id)
VALUES (l_consinv_id,
l_cons_billno,
L_sites.customer_id,
L_sites.site_id,
arp_standard.profile.request_id,
arp_global.last_update_date,
arp_global.last_updated_by,
arp_global.creation_date,
arp_global.created_by,
arp_global.last_update_login,
--Bug 5203710 get the cons_inv_type from customer setup
L_sites.cons_inv_type,
P_print_option,
P_print_status,
L_terms.term_id,
sysdate,
l_due_date,
P_currency,
nvl(l_beginning_balance,0),
0,
arp_standard.sysparm.org_id,
l_billing_date,
L_sites.bill_level,
l_last_bill_date,
P_billing_cycle_id,
l_remit_to_address_id);
write_debug_and_log('insert to ar_cons_inv_trx for ' || L_inv_trx.trx_id);
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id,
justification,
trx_description,
customer_trx_id,
ship_to_site_id)
VALUES (l_consinv_id,
DECODE(L_inv_trx.class,
'CM','CREDIT_MEMO',
'INVOICE'),
L_inv_trx.trx_number,
L_inv_trx.trx_date,
L_inv_trx.amount_due,
L_inv_trx.tax,
l_new_schedule_id, -- bug 6488683
l_consinv_lineno,
arp_standard.sysparm.org_id,
l_comments,
L_inv_trx.trx_desc,
L_inv_trx.trx_id,
L_inv_trx.ship_id);
/** For audit purposes, insert detail line information even if
reporting in summary. Also note that cons_inv_line_number
is one value for detail lines for a specific invoice. **/
write_debug_and_log('insert to ar_cons_inv_trx_lines for ' || L_inv_trx.trx_id);
INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
cons_inv_line_number,
customer_trx_id,
customer_trx_line_id,
line_number,
inventory_item_id,
description,
uom_code,
quantity_invoiced,
unit_selling_price,
extended_amount,
tax_amount,
org_id)
SELECT l_consinv_id,
l_consinv_lineno,
customer_trx_id,
customer_trx_line_id,
line_number,
inventory_item_id,
description,
uom_code,
decode(L_inv_trx.class,'CM', quantity_credited,quantity_invoiced),
nvl (gross_unit_selling_price, unit_selling_price),
nvl (gross_extended_amount, extended_amount),
0,
org_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = L_inv_trx.trx_id
AND line_type NOT IN ('TAX', 'FREIGHT');
/** now update lines with associated tax line **/
write_debug_and_log('update ar_cons_inv_trx_lines for TAX');
UPDATE ar_cons_inv_trx_lines
SET tax_amount = l_tax_sum(i),
extended_amount = extended_amount - l_include_tax_sum(i)
WHERE customer_trx_id = L_inv_trx.trx_id
AND customer_trx_line_id = l_line_id(i) ;
write_debug_and_log('insert to ar_cons_inv_trx_lines for FREIGHT');
INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
cons_inv_line_number,
customer_trx_id,
customer_trx_line_id,
line_number,
inventory_item_id,
description,
uom_code,
quantity_invoiced,
unit_selling_price,
extended_amount,
tax_amount,
org_id)
SELECT
l_consinv_id,
l_consinv_lineno,
max(customer_trx_id),
max(customer_trx_line_id),
max(line_number),
NULL,
'Freight',
NULL,
1,
sum (nvl (gross_extended_amount, extended_amount)),
sum (nvl (gross_extended_amount, extended_amount)),
0,
org_id
FROM
ra_customer_trx_lines
WHERE
customer_trx_id = L_inv_trx.trx_id
AND line_type = 'FREIGHT'
GROUP BY line_type,org_id;
(fin charge is in next select ACTIVITY 1A) */
write_debug_and_log('.........ACTIVITY 1');
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT /*+ index (PS AR_PAYMENT_SCHEDULES_N5) */
l_consinv_id,
'ADJUSTMENT',
PS.trx_number,
ADJ.gl_date,
ADJ.amount,
NVL(ADJ.tax_adjusted, 0),
ADJ.adjustment_id,
NULL,
ps.org_id
FROM
ar_adjustments ADJ,
ar_payment_schedules PS
WHERE
ADJ.cons_inv_id is NULL
AND ADJ.gl_date <= l_billing_date
AND ADJ.type in ('CHARGES','FREIGHT','INVOICE','LINE','TAX')
AND ADJ.created_from <> 'ARFCCF' -- exclude auto-generated finance charges
AND ADJ.status = 'A'
AND PS.payment_schedule_id = ADJ.payment_schedule_id
AND PS.customer_site_use_id = L_sites.site_id
AND PS.invoice_currency_code = P_currency
AND PS.class||'' <> 'GUAR'
AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT /*+ index (PS AR_PAYMENT_SCHEDULES_N5) */
l_consinv_id,
'FINANCE CHARGE',
PS.trx_number,
ADJ.gl_date,
ADJ.amount,
NVL(ADJ.tax_adjusted, 0),
ADJ.adjustment_id,
NULL,
ps.org_id
FROM
ar_adjustments ADJ,
ar_payment_schedules PS
WHERE
ADJ.cons_inv_id is NULL
AND ADJ.gl_date <= l_billing_date
AND ADJ.type = 'CHARGES'
AND ADJ.created_from = 'ARFCCF'
AND ADJ.status = 'A'
AND PS.payment_schedule_id = ADJ.payment_schedule_id
AND PS.customer_site_use_id = L_sites.site_id
AND PS.invoice_currency_code = P_currency
AND PS.class||'' <> 'GUAR'
AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
'RECEIPT',
PS.trx_number,
CR.receipt_date,
PS.amount_due_original,
NULL,
PS.payment_schedule_id,
NULL,
PS.org_id
FROM
ar_payment_schedules PS,
ar_cash_receipts CR
WHERE
PS.customer_site_use_id = L_sites.site_id
AND PS.cons_inv_id IS NULL
AND PS.class = 'PMT'
AND PS.invoice_currency_code = P_currency
AND CR.cash_receipt_id = PS.cash_receipt_id
AND CR.receipt_date <= l_billing_date
AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
'RECEIPT REV',
PS.trx_number,
CR.reversal_date,
(-1)*PS.amount_due_original,
NULL,
PS.payment_schedule_id,
NULL,
CR.org_id
FROM
ar_payment_schedules PS,
ar_cash_receipts CR
WHERE
PS.customer_site_use_id = L_sites.site_id
AND PS.cons_inv_id_rev IS NULL
AND PS.invoice_currency_code = P_currency
AND PS.class = 'PMT'
AND CR.cash_receipt_id = PS.cash_receipt_id
AND CR.reversal_date <= l_billing_date
AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
DECODE (nvl(ps_inv.exclude_from_cons_bill_flag, 'N'), 'Y','EXCLUDE RECREV',
DECODE (nvl (ps_cash.customer_site_use_id, -1), ps_inv.customer_site_use_id,
DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
'XXXXXXXXXX', 'XCURR RECREV'),
DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
'XSITE RECREV', 'XSITE XCURR RECREV')) ),
ps_cash.trx_number,
RA.apply_date,
nvl (ra.amount_applied_from, RA.amount_applied),
NULL,
RA.receivable_application_id,
NULL,
ps_cash.org_id
FROM
ar_receivable_applications RA,
ar_payment_schedules ps_cash,
ar_payment_schedules ps_inv
WHERE
RA.cons_inv_id IS NULL
AND RA.status = 'APP'
AND RA.application_type = 'CASH'
AND RA.apply_date <= l_billing_date
AND ps_cash.payment_schedule_id = RA.payment_schedule_id
AND ps_cash.customer_site_use_id = L_sites.site_id
AND ps_cash.invoice_currency_code = P_currency
AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
AND nvl(ps_cash.exclude_from_cons_bill_flag, 'N') <> 'Y'
AND ( ps_cash.customer_site_use_id <> ps_inv.customer_site_use_id
OR RA.amount_applied IS NOT NULL
OR nvl(ps_inv.exclude_from_cons_bill_flag, 'N') = 'Y');
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
DECODE (nvl(ps_cash.exclude_from_cons_bill_flag, 'N'),'Y','EXCLUDE RECAPP',
DECODE (nvl (ps_cash.customer_site_use_id, -1), ps_inv.customer_site_use_id,
DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
'XXXXXXXXXX', 'XCURR RECAPP'),
DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
'XSITE RECAPP', 'XSITE XCURR RECAPP')) ),
ps_cash.trx_number,
RA.apply_date,
(-1)*RA.amount_applied,
NULL,
RA.receivable_application_id,
NULL,
ps_cash.org_id
FROM
ar_receivable_applications RA,
ar_payment_schedules ps_cash,
ar_payment_schedules ps_inv
WHERE
RA.cons_inv_id_to IS NULL
AND RA.status = 'APP'
AND RA.application_type = 'CASH'
AND RA.apply_date <= l_billing_date
AND ps_cash.payment_schedule_id = RA.payment_schedule_id
AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
AND ps_inv.customer_site_use_id = L_sites.site_id
AND ps_inv.invoice_currency_code = P_currency
AND nvl(ps_inv.exclude_from_cons_bill_flag, 'N') <> 'Y'
AND ( nvl(ps_cash.customer_site_use_id, -1) <> ps_inv.customer_site_use_id
OR ra.amount_applied_from IS NOT NULL
OR nvl(ps_cash.exclude_from_cons_bill_flag, 'N') = 'Y');
Now if later, that receipt is updated with a Location = this BFB site the
receipt will now be picked up in ACTIVITY 2.
The following select is necessary to counter what was previously picked up
in ACTIVITY 5, otherwise the receipt application is recorded twice */
write_debug_and_log('.........ACTIVITY 6');
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
'XSITE RECREV', 'XSITE XCURR RECREV'),
ps_cash.trx_number,
RA.apply_date,
nvl (ra.amount_applied_from, RA.amount_applied),
NULL,
RA.receivable_application_id,
NULL,
ps_cash.org_id
FROM
ar_cons_inv_trx inv_trx,
ar_receivable_applications ra,
ar_payment_schedules ps_cash,
ar_payment_schedules ps_inv
WHERE ra.cons_inv_id_to is not null
AND ra.cons_inv_id is null
AND ra.status = 'APP'
AND ra.application_type = 'CASH'
AND ra.apply_date <= l_billing_date
AND ps_cash.payment_schedule_id = ra.payment_schedule_id
AND ps_cash.customer_site_use_id = L_sites.site_id
AND ps_cash.invoice_currency_code = P_currency
AND ps_inv.payment_schedule_id = ra.applied_payment_schedule_id
AND ps_cash.customer_site_use_id = ps_inv.customer_site_use_id
AND ra.receivable_application_id = inv_trx.adj_ps_id
AND inv_trx.transaction_type IN ('XSITE RECAPP','XSITE XCURR RECAPP');
Now if later, that receipt is updated with a Location different from this BFB site
we need to exclude it. */
write_debug_and_log('.........ACTIVITY 7');
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
'XSITE RECAPP', 'XSITE XCURR RECAPP'),
ps_cash.trx_number,
RA.apply_date,
(-1)*RA.amount_applied,
NULL,
RA.receivable_application_id,
NULL,
ps_cash.org_id
FROM
ar_cons_inv_trx inv_trx,
ar_receivable_applications ra,
ar_payment_schedules ps_cash,
ar_payment_schedules ps_inv
WHERE ra.cons_inv_id_to is null
AND ra.cons_inv_id is not null
AND ra.status = 'APP'
AND ra.application_type = 'CASH'
AND ra.apply_date <= l_billing_date
AND ps_cash.payment_schedule_id = RA.payment_schedule_id
AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
AND ps_inv.customer_site_use_id = L_sites.site_id
AND ps_inv.invoice_currency_code = P_currency
AND ps_cash.customer_site_use_id = ps_inv.customer_site_use_id
AND ra.receivable_application_id = inv_trx.adj_ps_id
AND inv_trx.transaction_type IN ('XSITE RECREV','XSITE XCURR RECREV');
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
DECODE ( nvl(PS_INV.exclude_from_cons_bill_flag, 'N'), 'Y', 'EXCLUDE_CMREV', 'XSITE_CMREV'),
PS_CM.trx_number,
RA.apply_date,
RA.amount_applied,
NULL,
RA.receivable_application_id,
NULL,
PS_CM.org_id
FROM
ar_receivable_applications RA,
ar_payment_schedules PS_CM,
ar_payment_schedules PS_INV
WHERE
RA.cons_inv_id IS NULL
AND RA.status = 'APP'
AND RA.application_type = 'CM'
AND RA.apply_date <= l_billing_date
AND PS_CM.payment_schedule_id = RA.payment_schedule_id
AND PS_CM.customer_site_use_id = L_sites.site_id
AND PS_CM.invoice_currency_code = P_currency
AND nvl(PS_CM.exclude_from_cons_bill_flag, 'N') <> 'Y'
AND PS_INV.payment_schedule_id = RA.applied_payment_schedule_id
AND ( PS_INV.customer_site_use_id <> PS_CM.customer_site_use_id
or nvl(PS_INV.exclude_from_cons_bill_flag, 'N') = 'Y' ) ;
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
'DELAY_CMAPP',
PS_CM.trx_number,
RA.apply_date,
RA.amount_applied,
NULL,
RA.receivable_application_id,
NULL,
PS_CM.org_id
FROM
ar_receivable_applications RA,
ar_payment_schedules PS_CM,
ar_payment_schedules PS_INV
WHERE
RA.cons_inv_id IS NULL
AND RA.status = 'APP'
AND RA.application_type = 'CM'
AND RA.apply_date <= l_billing_date
AND PS_CM.payment_schedule_id = RA.payment_schedule_id
AND PS_CM.customer_site_use_id = L_sites.site_id
AND PS_CM.invoice_currency_code = P_currency
AND nvl(PS_CM.exclude_from_cons_bill_flag, 'N') <> 'Y'
AND PS_INV.payment_schedule_id = RA.applied_payment_schedule_id
AND PS_INV.customer_site_use_id = L_sites.site_id
AND NOT EXISTS
(SELECT '*'
FROM ar_cons_inv c,
ar_cons_inv_trx ctrx
WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
AND c.cons_inv_id = ctrx.cons_inv_id
AND c.status <> 'REJECTED');
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
DECODE( nvl(PS_CM.exclude_from_cons_bill_flag, 'N') , 'Y', 'EXCLUDE_CMAPP','XSITE_CMAPP') ,
PS_INV.trx_number,
RA.apply_date,
(-1)*RA.amount_applied,
NULL,
RA.receivable_application_id,
NULL,
PS_INV.org_id
FROM
ar_receivable_applications RA,
ar_payment_schedules PS_INV, -- INV
ar_payment_schedules PS_CM -- CM
WHERE
RA.cons_inv_id_to IS NULL
AND RA.status = 'APP'
AND RA.application_type = 'CM'
AND RA.apply_date <= l_billing_date
AND PS_INV.payment_schedule_id = RA.applied_payment_schedule_id
AND PS_INV.customer_site_use_id = L_sites.site_id
AND PS_INV.invoice_currency_code = P_currency
AND nvl(PS_INV.exclude_from_cons_bill_flag, 'N') <> 'Y'
AND PS_CM.payment_schedule_id = RA.payment_schedule_id
AND ( PS_CM.customer_site_use_id <> PS_INV.customer_site_use_id
or nvl(PS_CM.exclude_from_cons_bill_flag, 'N') = 'Y')
AND EXISTS (SELECT '*'
FROM ar_cons_inv c,
ar_cons_inv_trx ctrx
WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
AND c.cons_inv_id = ctrx.cons_inv_id
AND c.status <> 'REJECTED');
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
'RECEIPT',
PS.trx_number,
CR.receipt_date,
PS.amount_due_original,
NULL,
PS.payment_schedule_id,
NULL,
PS.org_id
FROM
ar_payment_schedules PS,
ar_cash_receipts CR
WHERE
PS.customer_id = L_sites.customer_id
AND PS.customer_site_use_id IS NULL
AND PS.cons_inv_id IS NULL
AND PS.class = 'PMT'
AND PS.invoice_currency_code = P_currency
AND CR.cash_receipt_id = PS.cash_receipt_id
AND CR.receipt_date <= l_billing_date
AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
INSERT INTO ar_cons_inv_trx (cons_inv_id,
transaction_type,
trx_number,
transaction_date,
amount_original,
tax_original,
adj_ps_id,
cons_inv_line_number,
org_id)
SELECT
l_consinv_id,
'RECEIPT REV',
PS.trx_number,
CR.reversal_date,
(-1)*PS.amount_due_original,
NULL,
PS.payment_schedule_id,
NULL,
CR.org_id
FROM
ar_payment_schedules PS,
ar_cash_receipts CR
WHERE
PS.customer_id =L_sites.customer_id
AND PS.customer_site_use_id IS NULL
AND PS.cons_inv_id_rev IS NULL
AND PS.invoice_currency_code = P_currency
AND PS.class = 'PMT'
AND CR.cash_receipt_id = PS.cash_receipt_id
AND CR.reversal_date <= l_billing_date
AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
SELECT nvl(sum(amount_original),0)
INTO l_period_trx
FROM ar_cons_inv_trx
WHERE cons_inv_id = l_consinv_id
AND transaction_type IN ('INVOICE');
SELECT nvl(sum(amount_original),0)
INTO l_period_receipts
FROM ar_cons_inv_trx
WHERE cons_inv_id = l_consinv_id
AND transaction_type IN ('RECEIPT','RECEIPT REV','XSITE RECREV',
'XSITE RECAPP',
'XSITE XCURR RECAPP','XSITE XCURR RECREV',
'EXCLUDE RECREV', 'EXCLUDE RECAPP');
SELECT nvl(sum(amount_original),0)
INTO l_period_finchrg
FROM ar_cons_inv_trx
WHERE cons_inv_id = l_consinv_id
AND transaction_type IN ('FINANCE CHARGE');
SELECT nvl(sum(amount_original),0)
INTO l_period_adj
FROM ar_cons_inv_trx
WHERE cons_inv_id = l_consinv_id
AND transaction_type = 'ADJUSTMENT';
SELECT nvl(sum(amount_original),0)
INTO l_period_credits
FROM ar_cons_inv_trx
WHERE cons_inv_id = l_consinv_id
AND transaction_type IN ('CREDIT_MEMO',
'XSITE_CMREV','XSITE_CMAPP',
'EXCLUDE_CMREV', 'EXCLUDE_CMAPP',
'DELAY_CMAPP');
SELECT nvl(sum(tax_amount),0)
INTO l_period_tax
FROM ar_cons_inv_trx_lines
WHERE cons_inv_id = l_consinv_id;
UPDATE ar_cons_inv
SET total_receipts_amt = l_period_receipts,
total_adjustments_amt = l_period_adj,
total_credits_amt = l_period_credits,
total_finance_charges_amt = l_period_finchrg,
total_trx_amt = l_period_trx,
total_tax_amt = l_period_tax,
ending_balance = beginning_balance + l_period_trx + l_period_receipts +
l_period_adj + l_period_credits + l_period_finchrg
WHERE cons_inv_id = l_consinv_id;
/** For Site: update ar_payment_schedules, ar_receivable_applications
and ar_adjustments **/
write_debug_and_log('Updating AR_PAYMENT_SCHEDULES');
UPDATE ar_payment_schedules PS
SET PS.cons_inv_id = l_consinv_id
WHERE PS.payment_schedule_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv_trx IT
WHERE IT.cons_inv_id = l_consinv_id
AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
'RECEIPT'));
UPDATE ar_payment_schedules PS
SET PS.cons_inv_id_rev = l_consinv_id
WHERE PS.payment_schedule_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv_trx IT
WHERE IT.cons_inv_id = l_consinv_id
AND IT.transaction_type = 'RECEIPT REV');
UPDATE ar_receivable_applications RA
SET RA.cons_inv_id = l_consinv_id
WHERE RA.receivable_application_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv_trx IT
WHERE IT.cons_inv_id = l_consinv_id
AND IT.transaction_type IN ('XSITE RECREV',
'XSITE_CMREV',
'XCURR RECREV',
'XSITE XCURR RECREV',
'EXCLUDE RECREV',
'EXCLUDE_CMREV'));
UPDATE ar_receivable_applications RA
SET RA.cons_inv_id_to = l_consinv_id
WHERE RA.receivable_application_id IN
(SELECT IT.adj_ps_id
FROM ar_cons_inv_trx IT
WHERE IT.cons_inv_id = l_consinv_id
AND IT.transaction_type IN ('XSITE RECAPP',
'XSITE_CMAPP',
'XCURR RECAPP',
'XSITE XCURR RECAPP',
'EXCLUDE RECAPP',
'EXCLUDE_CMAPP'));
UPDATE ar_adjustments RA
SET RA.cons_inv_id = l_consinv_id
WHERE RA.adjustment_id IN
(SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
IT.adj_ps_id
FROM ar_cons_inv_trx IT
WHERE IT.cons_inv_id = l_consinv_id
AND IT.transaction_type = 'ADJUSTMENT');
UPDATE ra_customer_trx trx
SET printing_original_date = nvl(printing_original_date, SYSDATE),
printing_last_printed = nvl(printing_last_printed, SYSDATE)
WHERE trx.trx_number IN
(SELECT trx_number
FROM ar_cons_inv_trx IT
WHERE IT.cons_inv_id = l_consinv_id );
UPDATE ar_cons_inv
SET aging_bucket1_amt = l_bucket_amount_0,
aging_bucket2_amt = l_bucket_amount_1,
aging_bucket3_amt = l_bucket_amount_2,
aging_bucket4_amt = l_bucket_amount_3,
aging_bucket5_amt = l_bucket_amount_4,
aging_bucket6_amt = l_bucket_amount_5,
aging_bucket7_amt = l_bucket_amount_6
WHERE cons_inv_id = l_consinv_id;
UPDATE RA_CUSTOMER_TRX
SET term_id = l_tab_term_id(i),
billing_date = l_tab_billing_date(i),
term_due_date = l_tab_due_date(i)
WHERE customer_trx_id = l_tab_trx_id(i);
UPDATE AR_PAYMENT_SCHEDULES
SET due_date = l_tab_due_date(i)
WHERE customer_trx_id = l_tab_trx_id(i);
| update_status |
| |
| DESCRIPTION |
| After Consolidated Billing Invoices are printed successfully, update |
| status of the billing invoices from 'PENDING' to 'PRINTED'. |
| For NEW or DRAFT, parameters P_consinv_id and P_request_id are NULL. |
| These parameters are specified by the user for a REPRINT only. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS : IN: |
| P_print_option - print option |
| P_consinv_id - consolidated billing invoice |
| P_request_id - concurrent request id |
| |
| OUT: |
| None |
| RETURNS : None |
| |
| MODIFICATION HISTORY |
| 26-MAY-2005 MRAYMOND 4188835 - Added freeze call related to
| etax. When a invoice is printed, we need
| to notify etax that it will not change.
*----------------------------------------------------------------------------*/
PROCEDURE update_status (P_print_option IN VARCHAR,
P_consinv_id IN NUMBER,
P_request_id IN NUMBER) IS
CURSOR c_pending_trx IS
SELECT PS.customer_trx_id
FROM ar_payment_schedules PS,
ar_cons_inv_trx IT,
ar_cons_inv CI
WHERE
CI.print_status = 'PENDING'
AND IT.cons_inv_id = CI.cons_inv_id
AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
AND PS.payment_schedule_id = IT.adj_ps_id;
/* bug3604391 Changed the sequence of following update stmts.
Because ra_customer_trx was not updated after
ar_cons_inv.print_status was changed.
*/
UPDATE ra_customer_trx CT
SET CT.printing_original_date =
nvl(CT.printing_original_date,sysdate),
CT.printing_last_printed = sysdate,
CT.printing_count = nvl(CT.printing_count,0) +
DECODE(P_print_option,
'REPRINT', 0,
1)
WHERE CT.customer_trx_id IN
(SELECT PS.customer_trx_id
FROM ar_payment_schedules PS,
ar_cons_inv_trx IT,
ar_cons_inv CI
WHERE (
(P_print_option = 'REPRINT'
AND CI.cons_inv_id=nvl(P_consinv_id,CI.cons_inv_id)
AND CI.concurrent_request_id =
nvl(P_request_id, CI.concurrent_request_id))
OR
(P_print_option IN ('DRAFT', 'PRINT')
AND CI.print_status = 'PENDING')
)
AND IT.cons_inv_id = CI.cons_inv_id
AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
AND PS.payment_schedule_id = IT.adj_ps_id);
arp_etax_util.global_document_update(trx.customer_trx_id,
null,
'PRINT');
UPDATE ar_cons_inv
SET print_status = 'PRINTED',
last_update_date = arp_global.last_update_date,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
WHERE (P_print_option = 'REPRINT'
AND cons_inv_id = nvl(P_consinv_id,cons_inv_id)
AND concurrent_request_id = DECODE (P_consinv_id,
NULL, P_request_id,
concurrent_request_id))
OR (P_print_option IN ('DRAFT', 'PRINT')
AND print_status = 'PENDING');
write_debug_and_log( ' Exception: update_status: ');
update_status(P_report.print_option,
P_report.consinv_id_low,
P_report.request_id);
select org_id
from ar_system_parameters
where org_id = nvl(p_org_id,org_id);
select org_id
from ar_system_parameters
where org_id = nvl(p_org_id,org_id);
SELECT sob.currency_code
INTO l_use_currency
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
SELECT sob.currency_code
INTO l_use_currency
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;