[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ps.cons_inv_id,
bill_to_customer_id customer_id,
bill_to_site_use_id site_use_id,
ct.invoice_currency_code currency_code,
decode(tt.type, 'CM', 'CREDIT MEMO', 'INVOICE') transaction_type,
ct.trx_number,
ct.trx_date,
ct.customer_trx_id,
ct.org_id
FROM
ar_payment_schedules ps,
ra_customer_trx ct,
ra_cust_trx_types tt
WHERE
ct.cust_trx_type_id = tt.cust_trx_type_id
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.cons_inv_id > 0
AND ps.terms_sequence_number = 1
AND ct.customer_trx_id in ( SELECT customer_trx_id
FROM ra_interface_lines il
WHERE il.request_id = C_request_id
AND customer_trx_id IS NOT NULL
AND cons_billing_number IS NOT NULL
AND NVL(il.interface_status, '~') <> 'P'
)
ORDER BY
ps.cons_inv_id,
ct.customer_trx_id;
update_ps(p_request_id=>p_request_id);
new_cons_billing_number := g_cons_billing_number(new_cons_inv_id); -- From update_ps
SELECT nvl(max(cons_inv_line_number),0) + 1
INTO l_group_inv_line_number
FROM ar_cons_inv_trx
WHERE cons_inv_id = new_cons_inv_id ;
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,
issue_date,
cut_off_date,
due_date,
org_id)
VALUES
(new_cons_inv_id, -- Cons Inv Id
new_cons_billing_number, -- Cons Billing Number
c_group_inv_rec.customer_id, -- Customer Id
c_group_inv_rec.site_use_id, -- Site Use Id
arp_standard.profile.request_id, -- Request Id
arp_global.last_update_date, -- Last Update Date
arp_global.last_updated_by, -- Last Updated By
arp_global.creation_date, -- Creation Date
arp_global.created_by, -- Created By
arp_global.last_update_login, -- Last Update Login
'MINV', -- Cons Inv Type
'IMPORTED', -- Status
'PRINTED', -- Print Status
TRUNC(sysdate), -- Issue Date
NULL, -- Cutoff Date
NULL, -- Due Date
arp_standard.sysparm.org_id);
arp_standard.debug('Inserted cons :' || SQL%ROWCOUNT);
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,
customer_trx_id,
org_id)
VALUES
(new_cons_inv_id, -- Cons Inv Id
c_group_inv_rec.transaction_type, -- Transaction Type
c_group_inv_rec.trx_number, -- Transaction Number
c_group_inv_rec.trx_date, -- Transaction Date
NULL, -- Amount original
NULL, -- Tax Original
NULL, -- PS Id
l_group_inv_line_number, -- Cons Inv Line Number
c_group_inv_rec.customer_trx_id, -- Customer Trx Id
c_group_inv_rec.org_id);
arp_standard.debug('Inserted ['|| l_group_inv_line_number||'] :' ||
SQL%ROWCOUNT);
INSERT INTO ra_interface_errors
(interface_line_id,
message_text,
invalid_value,
org_id)
SELECT l.interface_line_id,
l_message_text,
l.cons_billing_number,
l.org_id
FROM ra_interface_lines l
WHERE l.request_id = p_request_id
AND l.cons_billing_number IS NOT NULL
AND l.link_to_line_id IS NULL
AND l.orig_system_bill_customer_id IS NOT NULL
AND EXISTS (SELECT 'x'
FROM ra_interface_lines l2
WHERE l2.request_id = l.request_id
AND l2.cons_billing_number = l.cons_billing_number
AND l2.orig_system_bill_customer_id <> l.orig_system_bill_customer_id);
arp_standard.debug('Inserted [1]:' || SQL%ROWCOUNT);
INSERT INTO RA_INTERFACE_ERRORS
(interface_line_id,
message_text,
invalid_value,
org_id)
SELECT l.interface_line_id,
l_message_text,
l.cons_billing_number,
l.org_id
FROM ra_interface_lines l
WHERE l.request_id = p_request_id
AND l.cons_billing_number IS NOT NULL
AND (EXISTS (SELECT 'X'
FROM ar_cons_inv
WHERE cons_billing_number = l.cons_billing_number
AND cons_inv_type = 'MINV' )
OR
EXISTS (SELECT 'X'
FROM ra_interface_lines l2
WHERE l2.request_id > 0
AND l2.request_id <> l.request_iD
AND l2.cons_billing_number = l.cons_billing_number));
arp_standard.debug('Inserted [2]:' || SQL%ROWCOUNT);
INSERT INTO RA_INTERFACE_ERRORS
(interface_line_id,
message_text,
invalid_value,
org_id)
SELECT l.interface_line_id,
l_message_text,
l.cons_billing_number,
l.org_id
FROM ra_interface_lines l
WHERE l.request_id = p_request_id
AND l.cons_billing_number IS NOT NULL
AND l.link_to_line_id IS NULL
AND l.orig_system_bill_address_id IS NOT NULL
AND EXISTS (SELECT 'X'
FROM ra_interface_lines l2
WHERE l2.request_id = l.request_id
AND l2.cons_billing_number = l.cons_billing_number
AND l2.orig_system_bill_address_id <> l.orig_system_bill_address_id);
arp_standard.debug('Inserted [3]:' || SQL%ROWCOUNT);
INSERT INTO RA_INTERFACE_ERRORS
(interface_line_id,
message_text,
invalid_value,
org_id)
SELECT l.interface_line_id,
l_message_text,
l.cons_billing_number,
l.org_id
FROM ra_interface_lines l
WHERE l.request_id = p_request_id
AND l.cons_billing_number IS NOT NULL
AND l.link_to_line_id IS NULL
AND l.orig_system_bill_customer_id IS NOT NULL
AND l.orig_system_bill_address_id IS NOT NULL
AND EXISTS (SELECT /*+ no_unnest */'X'
FROM
hz_cust_site_uses su,
hz_customer_profiles cp,
hz_customer_profiles sp,
hz_cust_acct_sites ac
WHERE su.cust_acct_site_id = l.orig_system_bill_address_id
AND su.site_use_code = 'BILL_TO'
AND su.status = 'A'
AND cp.cust_account_id = l.orig_system_bill_customer_id
AND cp.site_use_id IS NULL
AND ac.cust_acct_site_id = su.cust_acct_site_id
AND ac.cust_account_id = cp.cust_account_id
AND su.site_use_id = sp.site_use_id (+)
AND NVL(NVL(sp.cons_inv_flag, cp.cons_inv_flag), 'N') = 'N'
);
arp_standard.debug('Inserted [4]:' || SQL%ROWCOUNT);
INSERT INTO RA_INTERFACE_ERRORS
(interface_line_id,
message_text,
invalid_value,
org_id)
SELECT l.interface_line_id,
l_message_text,
l.cons_billing_number,
l.org_id
FROM ra_interface_lines l
WHERE l.request_id = p_request_id
AND l.cons_billing_number IS NOT NULL
AND l.link_to_line_id IS NULL
AND l.orig_system_bill_customer_id IS NOT NULL
AND l.orig_system_bill_address_id IS NOT NULL
AND EXISTS (SELECT /*+ no_unnest */'X'
FROM
hz_cust_site_uses su,
hz_customer_profiles cp,
hz_customer_profiles sp,
hz_cust_acct_sites ac
WHERE su.cust_acct_site_id = l.orig_system_bill_address_id
AND su.site_use_code = 'BILL_TO'
AND su.status = 'A'
AND cp.cust_account_id = l.orig_system_bill_customer_id
AND cp.site_use_id IS NULL
AND ac.cust_acct_site_id = su.cust_acct_site_id
AND ac.cust_account_id = cp.cust_account_id
AND su.site_use_id = sp.site_use_id (+)
AND NVL(sp.cons_inv_flag, cp.cons_inv_flag) = 'Y'
AND NVL(sp.cons_inv_type, cp.cons_inv_type) <> 'IMPORTED'
);
arp_standard.debug('Inserted [5]:' || SQL%ROWCOUNT);
| update_ps |
| |
| DESCRIPTION |
| Update ar_payment_schedules |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS : IN: |
| P_request_id - concurrent request id |
| |
| OUT: |
| none - |
| RETURNS : None |
| |
| MODIFICATION HISTORY |
| 26-JUL-2000 Ramakant Alat Created |
| |
*----------------------------------------------------------------------------*/
PROCEDURE update_ps (p_request_id IN NUMBER) IS
CURSOR c01 IS
SELECT DISTINCT cons_billing_number, customer_trx_id
FROM ra_interface_lines li
WHERE request_id = p_request_id
and li.customer_trx_id IS NOT NULL
AND cons_billing_number IS NOT NULL
AND NVL(interface_status, '~') <> 'P' -- Only consider unprocessed Transactions
AND EXISTS (SELECT 1
FROM ra_customer_trx ct
WHERE ct.customer_trx_id = li.customer_trx_id)
ORDER BY cons_billing_number, customer_trx_id;
SELECT cons_inv_id
FROM ar_cons_inv
WHERE cons_billing_number = l_cbi_number
AND cons_inv_type = 'MINV';
l_tot_rec_updated NUMBER:=0;
arp_standard.debug('arp_group_inv.update_ps()+');
SELECT ar_cons_inv_s.NEXTVAL INTO l_cons_inv_id FROM dual;
UPDATE ar_payment_schedules
SET cons_inv_id = l_cons_inv_id
WHERE customer_trx_id = c01_rec.customer_trx_id;
l_tot_rec_updated := l_tot_rec_updated + SQL%ROWCOUNT;
arp_standard.debug('Updated :' || l_tot_rec_updated);
arp_standard.debug('arp_group_inv.update_ps()-');
arp_standard.debug( ' Exception: arp_group_inv.update_ps()');
END update_ps;
INSERT INTO RA_INTERFACE_ERRORS
(interface_line_id,
message_text,
invalid_value,
org_id)
SELECT l.interface_line_id,
l_message_text,
l.cons_billing_number,
l.org_id
FROM ra_interface_lines_gt l
WHERE l.request_id = p_request_id
AND l.cons_billing_number IS NOT NULL
AND nvl(l.interface_status,'~') <> 'P'
AND l.link_to_line_id is null
AND l.customer_trx_id is not null
AND EXISTS
( SELECT /*+ leading(L2) use_nl_with_index(E, RA_INTERFACE_ERRORS_N1) */ 'x'
FROM ra_interface_errors e, ra_interface_lines_gt l2
WHERE e.INTERFACE_LINE_ID = l2.INTERFACE_LINE_ID
AND l2.cons_billing_number = l.cons_billing_number
AND l2.request_id = l.request_id );