[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_BATCH_SOURCE(p_invoice_date_from IN DATE,
p_invoice_date_to IN DATE,
p_gl_date_from IN DATE,
p_gl_date_to IN DATE,
p_ship_date_from IN DATE,
p_ship_date_to IN DATE,
p_default_date IN DATE) IS
l_contributor_class_code VARCHAR2(150);
SELECT rowid,
interface_line_attribute1,
cust_trx_type_id,
nvl(orig_system_ship_address_id,
orig_system_bill_address_id) orig_system_address_id,
batch_source_name,
trx_date,
org_id
FROM ra_interface_lines
WHERE nvl(interface_status, '~') <> 'P'
AND ((nvl(trx_date,sysdate-1) BETWEEN
nvl(p_invoice_date_from,nvl(trx_date,sysdate)) AND
nvl(p_invoice_date_to, nvl(trx_date,sysdate)))
OR (p_invoice_date_from is null and p_invoice_date_to is null
and trx_date is null))
AND ((nvl(gl_date,sysdate-1) BETWEEN
nvl(p_gl_date_from, nvl(gl_date,sysdate)) AND
nvl(p_gl_date_to, nvl(gl_date,sysdate)))
OR (p_gl_date_from is null and p_gl_date_to is null
and gl_date is null))
AND ((nvl(ship_date_actual,sysdate-1) BETWEEN
nvl(p_ship_date_from,nvl(ship_date_actual,sysdate)) AND
nvl(p_ship_date_to,nvl(ship_date_actual,sysdate)))
OR (p_ship_date_from is null and p_ship_date_to is null
and ship_date_actual is null));
arp_file.write_log('inside update_batch_source',0);
SELECT ra.global_attribute8
INTO l_contributor_class_code
FROM hz_cust_acct_sites ra
WHERE ra.cust_acct_site_id = trx_lines_rec.orig_system_address_id;
SELECT hrl.global_attribute1
INTO l_organization_class_code
FROM hr_locations hrl,
hr_organization_units hrou
WHERE hrou.organization_id = l_so_org_id
AND hrl.location_id = hrou.location_id;
SELECT distinct tax_category_id
INTO l_tax_category_id
FROM jl_ar_ar_doc_letter;
SELECT tax_attribute_name, tax_attribute_value
INTO l_org_attribute_name, l_org_attribute_value
FROM jl_zz_ar_tx_att_cls cls, jl_zz_ar_tx_categ_all cat
WHERE cls.tax_attr_class_type = 'ORGANIZATION_CLASS'
AND cls.tax_attr_class_code = l_organization_class_code
AND cls.tax_category_id = l_tax_category_id
AND cls.tax_category_id = cat.tax_category_id
AND cls.tax_attribute_name = cat.org_tax_attribute;
SELECT nvl(cas.global_attribute9,'N')
INTO l_cus_cls_flag
FROM hz_cust_acct_sites cas
WHERE cas.cust_acct_site_id = trx_lines_rec.orig_system_address_id;
SELECT tax_attribute_name, tax_attribute_value
INTO l_cust_attribute_name, l_cust_attribute_value
FROM jl_zz_ar_tx_cus_cls cus, jl_zz_ar_tx_categ_all cat
WHERE cus.tax_attr_class_code = l_contributor_class_code
AND cus.address_id = trx_lines_rec.orig_system_address_id
AND cus.tax_category_id = l_tax_category_id
AND cus.tax_category_id = cat.tax_category_id
AND cus.tax_attribute_name = cat.cus_tax_attribute;
SELECT tax_attribute_name, tax_attribute_value
INTO l_cust_attribute_name, l_cust_attribute_value
FROM jl_zz_ar_tx_att_cls att, jl_zz_ar_tx_categ_all cat
WHERE att.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
AND att.tax_attr_class_code = l_contributor_class_code
AND att.tax_category_id = l_tax_category_id
AND att.tax_attribute_name = cat.cus_tax_attribute
AND att.tax_category_id = cat.tax_category_id;
SELECT document_letter
INTO l_document_letter
FROM jl_ar_ar_doc_letter
WHERE tax_category_id = l_tax_category_id
AND org_tax_attribute_name = l_org_attribute_name
AND org_tax_attribute_value = l_org_attribute_value
AND con_tax_attribute_name = l_cust_attribute_name
AND con_tax_attribute_value = l_cust_attribute_value
AND nvl(trx_lines_rec.trx_date, p_default_date) BETWEEN start_date_active AND end_date_active;
SELECT ty.batch_source_id
INTO l_batch_source_id
FROM jg_zz_ar_src_trx_ty ty, ra_batch_sources_all src
WHERE ty.cust_trx_type_id = trx_lines_rec.cust_trx_type_id
AND ty.batch_source_id = src.batch_source_id
AND src.global_attribute3 = l_document_letter
AND ty.enable_flag = 'Y';
SELECT name
INTO l_new_batch_source_name
FROM ra_batch_sources
WHERE batch_source_id = l_batch_source_id;
UPDATE ra_interface_lines
SET batch_source_name = l_new_batch_source_name
WHERE rowid = trx_lines_rec.rowid;
INSERT INTO RA_INTERFACE_ERRORS(INTERFACE_LINE_ID, MESSAGE_TEXT, INVALID_VALUE, ORG_ID)
VALUES(trx_lines_rec.interface_line_attribute1,
'Original batch source : '||trx_lines_rec.batch_source_name||
'is updated with new batch source :'||l_new_batch_source_name,
'JLUPDUBS',
trx_lines_rec.org_id);
arp_file.write_log('Updated old batch source '||trx_lines_rec.batch_source_name ||
'to '|| l_new_batch_source_name,0);
PROCEDURE JL_AR_AR_UPDATE_BATCH_SOURCE (
errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY number,
p_low_gl_date IN VARCHAR2 ,
p_high_gl_date IN VARCHAR2 ,
p_low_ship_date IN VARCHAR2 ,
p_high_ship_date IN VARCHAR2,
p_low_invoice_date IN VARCHAR2 ,
p_high_invoice_date IN VARCHAR2,
p_default_date IN VARCHAR2) IS
X_req_id NUMBER(38);
arp_file.write_log('Calling Update Batch source',0);
UPDATE_BATCH_SOURCE(p_low_invoice_dt, p_high_invoice_dt,
p_low_gl_dt, p_high_gl_dt,
p_low_ship_dt, p_high_ship_dt, p_default_dt);
arp_file.write_log('After update batch source');
'Argentine Autoinvoice Batch Source Update Error Report',
SYSDATE,
FALSE);
END JL_AR_AR_UPDATE_BATCH_SOURCE;