[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION delete_attachments(p_invoice_id IN NUMBER)
RETURN NUMBER IS
l_attachments_count NUMBER := 0;
select count(1)
into l_attachments_count
from fnd_attached_documents
where entity_name = 'AP_INVOICES_INTERFACE'
and pk1_value = p_invoice_id;
fnd_attached_documents2_pkg.delete_attachments(
X_entity_name => 'AP_INVOICES_INTERFACE',
X_pk1_value => p_invoice_id,
X_delete_document_flag => 'N' );
END delete_attachments;
SELECT invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
po_number,
vendor_id,
vendor_num,
vendor_name,
vendor_site_id,
vendor_site_code,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
terms_name,
terms_date,
trim(description) description,
awt_group_id,
awt_group_name,
pay_awt_group_id,--bug6639866
pay_awt_group_name,--bug6639866
amount_applicable_to_discount,
sysdate,
last_updated_by,
last_update_login,
sysdate,
created_by,
status,
rtrim(attribute_category) attribute_category,
rtrim(attribute1) attribute1,
rtrim(attribute2) attribute2,
rtrim(attribute3) attribute3,
rtrim(attribute4) attribute4,
rtrim(attribute5) attribute5,
rtrim(attribute6) attribute6,
rtrim(attribute7) attribute7,
rtrim(attribute8) attribute8,
rtrim(attribute9) attribute9,
rtrim(attribute10) attribute10,
rtrim(attribute11) attribute11,
rtrim(attribute12) attribute12,
rtrim(attribute13) attribute13,
rtrim(attribute14) attribute14,
rtrim(attribute15) attribute15,
rtrim(global_attribute_category) global_attribute_category,
rtrim(global_attribute1) global_attribute1,
rtrim(global_attribute2) global_attribute2,
rtrim(global_attribute3) global_attribute3,
rtrim(global_attribute4) global_attribute4,
rtrim(global_attribute5) global_attribute5,
rtrim(global_attribute6) global_attribute6,
rtrim(global_attribute7) global_attribute7,
rtrim(global_attribute8) global_attribute8,
rtrim(global_attribute9) global_attribute9,
rtrim(global_attribute10) global_attribute10,
rtrim(global_attribute11) global_attribute11,
rtrim(global_attribute12) global_attribute12,
rtrim(global_attribute13) global_attribute13,
rtrim(global_attribute14) global_attribute14,
rtrim(global_attribute15) global_attribute15,
rtrim(global_attribute16) global_attribute16,
rtrim(global_attribute17) global_attribute17,
rtrim(global_attribute18) global_attribute18,
rtrim(global_attribute19) global_attribute19,
rtrim(global_attribute20) global_attribute20,
payment_currency_code,
payment_cross_rate,
payment_cross_rate_type,
payment_cross_rate_date,
doc_category_code,
voucher_num,
payment_method_code,
pay_group_lookup_code,
goods_received_date,
invoice_received_date,
gl_date,
accts_pay_code_combination_id,
-- bug 6509776
RTRIM(accts_pay_code_concatenated,'-'),
-- ussgl_transaction_code, - Bug 4277744
UPPER(exclusive_payment_flag),
prepay_num,
prepay_line_num,
prepay_apply_amount,
prepay_gl_date,
UPPER(invoice_includes_prepay_flag),
no_xrate_base_amount,
requester_id,
org_id,
operating_unit,
source,
group_id,
request_id,
workflow_flag,
vendor_email_address,
NVL(calc_tax_during_import_flag, 'N'), -- bug 6349739,bug6328293
control_amount,
add_tax_to_inv_amt_flag,
tax_related_invoice_id,
taxation_country,
document_sub_type,
supplier_tax_invoice_number,
supplier_tax_invoice_date,
supplier_tax_exchange_rate,
tax_invoice_recording_date,
tax_invoice_internal_seq,
legal_entity_id,
null,
ap_import_utilities_pkg.get_tax_only_rcv_matched_flag(invoice_id),
ap_import_utilities_pkg.get_tax_only_flag(invoice_id),
apply_advances_flag,
application_id,
product_table,
reference_key1,
reference_key2,
reference_key3,
reference_key4,
reference_key5,
reference_1,
reference_2,
net_of_retainage_flag,
cust_registration_code,
cust_registration_number,
paid_on_behalf_employee_id,
party_id, -- Added for Payment Requests
party_site_id,
pay_proc_trxn_type_code,
payment_function,
payment_priority,
BANK_CHARGE_BEARER,
REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3,
UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT,
SETTLEMENT_PRIORITY,
PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS,
DELIVERY_CHANNEL_CODE,
EXTERNAL_BANK_ACCOUNT_ID,
--Bug 7357218 Quick Pay and Dispute Resolution Project
ORIGINAL_INVOICE_AMOUNT ,
DISPUTE_REASON,
--Third Party Payments
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID ,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID,
REMIT_TO_SUPPLIER_NUM
FROM ap_invoices_interface
WHERE ((status is NULL) OR (status = 'REJECTED'))
AND source = p_source
AND ((p_invoice_interface_id IS NULL AND
NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
OR (invoice_id = p_invoice_interface_id))
AND NVL(workflow_flag,'D') = 'D'
AND ( (p_commit_cycles IS NULL)
OR (rownum <= p_commit_cycles))
AND ( (org_id IS NOT NULL AND
p_org_id IS NOT NULL AND
org_id = p_org_id)
OR (p_org_id IS NULL AND
org_id IS NOT NULL AND
(mo_global.check_access(org_id)= 'Y'))
OR (p_org_id is NOT NULL AND org_id IS NULL)
OR (p_org_id is NULL AND org_id IS NULL))
ORDER BY org_id,
invoice_id,
vendor_id,
vendor_num,
vendor_name,
vendor_site_id,
vendor_site_code,
invoice_num
For UPDATE of invoice_id NOWAIT;
SELECT invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
po_number,
vendor_id,
vendor_num,
vendor_name,
vendor_site_id,
vendor_site_code,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
terms_name,
terms_date,
trim(description) description,
awt_group_id,
awt_group_name,
pay_awt_group_id,--bug6639866
pay_awt_group_name,--bug6639866
amount_applicable_to_discount,
sysdate,
last_updated_by,
last_update_login,
sysdate,
created_by,
status,
rtrim(attribute_category) attribute_category,
rtrim(attribute1) attribute1,
rtrim(attribute2) attribute2,
rtrim(attribute3) attribute3,
rtrim(attribute4) attribute4,
rtrim(attribute5) attribute5,
rtrim(attribute6) attribute6,
rtrim(attribute7) attribute7,
rtrim(attribute8) attribute8,
rtrim(attribute9) attribute9,
rtrim(attribute10) attribute10,
rtrim(attribute11) attribute11,
rtrim(attribute12) attribute12,
rtrim(attribute13) attribute13,
rtrim(attribute14) attribute14,
rtrim(attribute15) attribute15,
rtrim(global_attribute_category) global_attribute_category,
rtrim(global_attribute1) global_attribute1,
rtrim(global_attribute2) global_attribute2,
rtrim(global_attribute3) global_attribute3,
rtrim(global_attribute4) global_attribute4,
rtrim(global_attribute5) global_attribute5,
rtrim(global_attribute6) global_attribute6,
rtrim(global_attribute7) global_attribute7,
rtrim(global_attribute8) global_attribute8,
rtrim(global_attribute9) global_attribute9,
rtrim(global_attribute10) global_attribute10,
rtrim(global_attribute11) global_attribute11,
rtrim(global_attribute12) global_attribute12,
rtrim(global_attribute13) global_attribute13,
rtrim(global_attribute14) global_attribute14,
rtrim(global_attribute15) global_attribute15,
rtrim(global_attribute16) global_attribute16,
rtrim(global_attribute17) global_attribute17,
rtrim(global_attribute18) global_attribute18,
rtrim(global_attribute19) global_attribute19,
rtrim(global_attribute20) global_attribute20,
payment_currency_code,
payment_cross_rate,
payment_cross_rate_type,
payment_cross_rate_date,
doc_category_code,
voucher_num,
payment_method_code,
pay_group_lookup_code,
goods_received_date,
invoice_received_date,
gl_date,
accts_pay_code_combination_id,
-- bug 6509776
RTRIM(accts_pay_code_concatenated,'-'),
-- ussgl_transaction_code, - Bug 4277744
UPPER(exclusive_payment_flag),
prepay_num,
prepay_line_num,
prepay_apply_amount,
prepay_gl_date,
UPPER(invoice_includes_prepay_flag),
no_xrate_base_amount,
requester_id,
org_id,
operating_unit,
source,
group_id,
request_id,
workflow_flag,
vendor_email_address,
NVL(calc_tax_during_import_flag, 'N'), -- bug 6349739,bug6328293
control_amount,
add_tax_to_inv_amt_flag,
tax_related_invoice_id,
taxation_country,
document_sub_type,
supplier_tax_invoice_number,
supplier_tax_invoice_date,
supplier_tax_exchange_rate,
tax_invoice_recording_date,
tax_invoice_internal_seq,
legal_entity_id,
null,
ap_import_utilities_pkg.get_tax_only_rcv_matched_flag(invoice_id),
ap_import_utilities_pkg.get_tax_only_flag(invoice_id),
apply_advances_flag,
application_id,
product_table,
reference_key1,
reference_key2,
reference_key3,
reference_key4,
reference_key5,
reference_1,
reference_2,
net_of_retainage_flag,
cust_registration_code,
cust_registration_number,
paid_on_behalf_employee_id,
party_id, -- Added for Payment Requests
party_site_id,
pay_proc_trxn_type_code,
payment_function,
payment_priority,
BANK_CHARGE_BEARER,
REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3,
UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT,
SETTLEMENT_PRIORITY,
PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS,
DELIVERY_CHANNEL_CODE,
EXTERNAL_BANK_ACCOUNT_ID,
--Bug 7357218 Quick Pay and Dispute Resolution Project
ORIGINAL_INVOICE_AMOUNT,
DISPUTE_REASON,
--Third Party Payments
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID ,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID,
REMIT_TO_SUPPLIER_NUM
FROM ap_invoices_interface
WHERE ((status is NULL) OR (status = 'REJECTED'))
AND source = p_source
AND group_id = p_group_id
AND ((p_invoice_interface_id IS NULL AND
NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
OR (invoice_id = p_invoice_interface_id))
AND NVL(workflow_flag,'D') = 'D'
AND ( (p_commit_cycles IS NULL)
OR (rownum <= p_commit_cycles))
AND ( (org_id IS NOT NULL AND
p_org_id IS NOT NULL AND
org_id = p_org_id)
OR (p_org_id IS NULL AND
org_id IS NOT NULL AND
(mo_global.check_access(org_id)= 'Y'))
OR (p_org_id is NOT NULL AND org_id IS NULL)
OR (p_org_id is NULL AND org_id IS NULL))
ORDER BY org_id,
invoice_id,
vendor_id,
vendor_num,
vendor_name,
vendor_site_id,
vendor_site_code,
invoice_num
For UPDATE of invoice_id NOWAIT;
l_default_last_updated_by NUMBER;
l_default_last_update_login NUMBER;
SELECT structure_id
INTO AP_IMPORT_INVOICES_PKG.g_structure_id
FROM mtl_default_sets_view
WHERE functional_area_id = 2;
l_default_last_updated_by := to_number(FND_GLOBAL.USER_ID);
l_default_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
debug_info := '(Import Invoice 3) Delete Rejections from previous failed '||
'imports';
debug_info := '(Check_lines 3a) Select all the Rejected Invoices';
SELECT invoice_id
BULK COLLECT INTO enums
FROM ap_invoices_interface
WHERE ((status is NULL) or (status = 'REJECTED'))
AND source = p_source
AND ((p_invoice_interface_id IS NULL AND
NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
OR (invoice_id = p_invoice_interface_id))
AND nvl(workflow_flag,'D') = 'D'
AND ((org_id is NOT NULL AND
p_org_id is NOT NULL AND
org_id = p_org_id)
or (p_org_id is NULL AND
org_id is NOT NULL AND
(mo_global.check_access(org_id)= 'Y'))
or (p_org_id is NOT NULL AND
org_id is NULL)
or (p_org_id is NULL AND
org_id is NULL));
SELECT invoice_id
BULK COLLECT INTO enums
FROM ap_invoices_interface
WHERE ((status is NULL) or (status = 'REJECTED'))
AND source = p_source
AND group_id = p_group_id
AND ((p_invoice_interface_id IS NULL AND
NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
OR (invoice_id = p_invoice_interface_id))
AND nvl(workflow_flag,'D') = 'D'
AND ((org_id is NOT NULL AND
p_org_id is NOT NULL AND
org_id = p_org_id)
or (p_org_id is NULL AND
org_id is NOT NULL AND
(mo_global.check_access(org_id)= 'Y'))
or (p_org_id is NOT NULL AND
org_id is NULL)
or (p_org_id is NULL AND
org_id is NULL));
debug_info := '(Check_lines 3b) Delete invoices from ap_interface_rejections';
DELETE FROM ap_interface_rejections
WHERE parent_table = 'AP_INVOICES_INTERFACE'
AND parent_id = enums(i);
DELETE FROM ap_interface_rejections
WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND parent_id IN (SELECT invoice_line_id
FROM ap_invoice_lines_interface
WHERE invoice_id = enums(i));
debug_info := '(Check_lines 3c) Update requestid on the Selected Invoices';
UPDATE AP_INVOICES_INTERFACE
SET request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id
WHERE invoice_id = enums(i);
debug_info := '(Import Invoice 4) Update the org_id';
UPDATE ap_invoices_interface i
SET org_id = (SELECT hr.organization_id org_id
FROM hr_operating_units hr,
per_business_groups per
WHERE hr.business_group_id = per.business_group_id
AND mo_global.check_access(hr.organization_id) = 'Y'
AND hr.name = i.operating_unit)
WHERE i.org_id is null
AND i.operating_unit is not null
AND ((status is NULL) OR (status = 'REJECTED'))
AND source = p_source
AND ((p_invoice_interface_id IS NULL AND
NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
OR (invoice_id = p_invoice_interface_id))
AND NVL(workflow_flag,'D') = 'D' ;
UPDATE ap_invoices_interface i
SET org_id = (SELECT hr.organization_id org_id
FROM hr_operating_units hr,
per_business_groups per
WHERE hr.business_group_id = per.business_group_id
AND mo_global.check_access(hr.organization_id) = 'Y'
AND hr.name = i.operating_unit)
WHERE i.org_id is null
AND i.operating_unit is not null
AND ((status is NULL) OR (status = 'REJECTED'))
AND source = p_source
AND group_id = p_group_id
AND ((p_invoice_interface_id IS NULL AND
NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
OR (invoice_id = p_invoice_interface_id))
AND NVL(workflow_flag,'D') = 'D' ;
SELECT count(*)
INTO l_total_count
FROM ap_invoices_interface
WHERE ((status is NULL) or (status = 'REJECTED'))
AND source = p_source
AND ((p_invoice_interface_id IS NULL AND
NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
OR (invoice_id = p_invoice_interface_id))
AND nvl(workflow_flag,'D') = 'D'
AND ((org_id is NOT NULL AND
p_org_id is NOT NULL AND
org_id = p_org_id)
or (p_org_id is NULL AND
org_id is NOT NULL AND
(mo_global.check_access(org_id)= 'Y'))
or (p_org_id is NOT NULL AND
org_id is NULL)
or (p_org_id is NULL AND
org_id is NULL))
AND ROWNUM = 1;
SELECT count(*)
INTO l_total_count
FROM ap_invoices_interface
WHERE ((status is NULL) or (status = 'REJECTED'))
AND source = p_source
AND group_id = p_group_id
AND ((p_invoice_interface_id IS NULL AND
NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
OR (invoice_id = p_invoice_interface_id))
AND nvl(workflow_flag,'D') = 'D'
AND ((org_id is NOT NULL AND
p_org_id is NOT NULL AND
org_id = p_org_id)
or (p_org_id is NULL AND
org_id is NOT NULL AND
(mo_global.check_access(org_id)= 'Y'))
or (p_org_id is NOT NULL AND
org_id is NULL)
or (p_org_id is NULL AND
org_id is NULL))
AND ROWNUM = 1;
||' last_update_date = ' ||to_char(l_invoice_rec.last_update_date)
||' last_updated_by = ' ||to_char(l_invoice_rec.last_updated_by)
||' last_update_login = '||to_char(l_invoice_rec.last_update_login)
||' creation_date = ' ||to_char(l_invoice_rec.creation_date)
||' attribute_category = '||l_invoice_rec.attribute_category
||' attribute1 = ' ||l_invoice_rec.attribute1
||' attribute2 = ' ||l_invoice_rec.attribute2
||' attribute3 = ' ||l_invoice_rec.attribute3
||' attribute4 = ' ||l_invoice_rec.attribute4
||' attribute5 = ' ||l_invoice_rec.attribute5
||' attribute6 = ' ||l_invoice_rec.attribute6
||' attribute7 = ' ||l_invoice_rec.attribute7
||' attribute8 = ' ||l_invoice_rec.attribute8
||' attribute9 = ' ||l_invoice_rec.attribute9
||' attribute10 = ' ||l_invoice_rec.attribute10
||' attribute11 = ' ||l_invoice_rec.attribute11
||' attribute12 = ' ||l_invoice_rec.attribute12
||' attribute13 = ' ||l_invoice_rec.attribute13
||' attribute14 = ' ||l_invoice_rec.attribute14
||' attribute15 = ' ||l_invoice_rec.attribute15
||' global_attribute_category = '
|| l_invoice_rec.global_attribute_category
||' global_attribute1 = ' ||l_invoice_rec.global_attribute1
||' global_attribute2 = ' ||l_invoice_rec.global_attribute2
||' global_attribute3 = ' ||l_invoice_rec.global_attribute3
||' global_attribute4 = ' ||l_invoice_rec.global_attribute4
||' global_attribute5 = ' ||l_invoice_rec.global_attribute5
||' global_attribute6 = ' ||l_invoice_rec.global_attribute6
||' global_attribute7 = ' ||l_invoice_rec.global_attribute7
||' global_attribute8 = ' ||l_invoice_rec.global_attribute8
||' global_attribute9 = ' ||l_invoice_rec.global_attribute9
||' global_attribute10 = '||l_invoice_rec.global_attribute10
||' global_attribute11 = '||l_invoice_rec.global_attribute11
||' global_attribute12 = '||l_invoice_rec.global_attribute12
||' global_attribute13 = '||l_invoice_rec.global_attribute13
||' global_attribute14 = '||l_invoice_rec.global_attribute14
||' global_attribute15 = '||l_invoice_rec.global_attribute15
||' global_attribute16 = '||l_invoice_rec.global_attribute16
||' global_attribute17 = '||l_invoice_rec.global_attribute17
||' global_attribute18 = '||l_invoice_rec.global_attribute18
||' global_attribute19 = '||l_invoice_rec.global_attribute19
||' global_attribute20 = '||l_invoice_rec.global_attribute20
||' doc_category_code = '||l_invoice_rec.doc_category_code
||' voucher_num = ' ||l_invoice_rec.voucher_num
||' payment_method_code = '
|| l_invoice_rec.payment_method_code
||' pay_group_lookup_code = '||l_invoice_rec.pay_group_lookup_code
||' goods_received_date = '
|| to_char(l_invoice_rec.goods_received_date)
||' invoice_received_date = '
|| to_char(l_invoice_rec.invoice_received_date)
||' exclusive_payment_flag = '
|| l_invoice_rec.exclusive_payment_flag
||' prepay_num = ' ||l_invoice_rec.prepay_num
||' prepay_line_num = ' ||l_invoice_rec.prepay_line_num
||' prepay_apply_amount = '||l_invoice_rec.prepay_apply_amount
||' prepay_gl_date = ' ||l_invoice_rec.prepay_gl_date
||' set_of_books_id = '||l_invoice_rec.set_of_books_id
||' legal_entity_id = '||l_invoice_rec.legal_entity_id
||' tax_only_flag = '||l_invoice_rec.tax_only_flag
||' tax_only_rcv_matched_flag = '||l_invoice_rec.tax_only_rcv_matched_flag
--Third Party Payments
||' remit_to_supplier_name = '||l_invoice_rec.remit_to_supplier_name
||' remit_to_supplier_id = '||l_invoice_rec.remit_to_supplier_id
||' remit_to_supplier_site = '||l_invoice_rec.remit_to_supplier_site
||' remit_to_supplier_site_id = '||l_invoice_rec.remit_to_supplier_site_id
||' relationship_id = '||l_invoice_rec.relationship_id
||' remit_to_supplier_num = '||l_invoice_rec.remit_to_supplier_num
);
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
(AP_IMPORT_INVOICES_PKG.g_invoices_table,
l_invoice_rec.invoice_id,
'NO OPERATING UNIT',
l_default_last_updated_by,
l_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
END IF; --Insert rejections
UPDATE ap_invoices_interface
SET org_id = l_default_org_id
WHERE invoice_id = l_invoice_rec.invoice_id ;
UPDATE ap_invoices_interface
SET org_id = p_org_id
WHERE invoice_id = l_invoice_rec.invoice_id ;
SELECT org_id
INTO l_option_defined_org
FROM financials_system_parameters
WHERE org_id = l_invoice_rec.org_id;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
(AP_IMPORT_INVOICES_PKG.g_invoices_table,
l_invoice_rec.invoice_id,
'UNDEFINED OPERATING UNIT',
l_default_last_updated_by,
l_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
END IF; -- Insert rejections
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
(AP_IMPORT_INVOICES_PKG.g_invoices_table,
l_invoice_rec.invoice_id,
'UNDEFINED OPERATING UNIT',
l_default_last_updated_by,
l_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
END IF; -- Insert rejections
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
(AP_IMPORT_INVOICES_PKG.g_invoices_table,
l_invoice_rec.invoice_id,
'INCONSISTENT OPERATING UNITS',
l_default_last_updated_by,
l_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
END IF; -- Insert rejections
l_default_last_updated_by, -- IN
l_default_last_update_login, -- IN
l_fatal_error_flag, -- OUT
l_invoice_status, -- OUT
l_calc_user_xrate, -- IN
l_prepay_period_name, -- IN OUT
l_prepay_invoice_id, -- OUT --Contract Payments
l_prepay_case_name, -- OUT --Contract Payments
p_conc_request_id,
l_allow_interest_invoices, -- IN
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'v_check_invoice_validation<-'||current_calling_sequence);
l_default_last_updated_by, -- IN
l_default_last_update_login, -- IN
l_invoice_status, -- OUT NOCOPY
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'v_check_lines_validation<-'||current_calling_sequence);
l_default_last_updated_by, -- IN
l_default_last_update_login, -- IN
l_seqval, -- OUT NOCOPY
l_dbseqnm, -- OUT NOCOPY
l_dbseqid, -- OUT NOCOPY
l_invoice_status, -- OUT NOCOPY
current_calling_sequence)<> TRUE) THEN
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'get_doc_sequence<-'||current_calling_sequence);
l_default_last_updated_by, -- IN
l_default_last_update_login, -- IN
l_pay_curr_invoice_amount, -- OUT NOCOPY
l_payment_priority, -- OUT NOCOPY
l_invoice_amount_limit, -- OUT NOCOPY
l_hold_future_payments_flag, -- OUT NOCOPY
l_supplier_hold_reason, -- OUT NOCOPY
l_exclude_freight_from_disc, -- OUT NOCOPY /* bug 4931755 */
current_calling_sequence ) <> TRUE) THEN
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'get_invoice_info<-'||current_calling_sequence);
debug_info := '(Import_invoice 15.1b) Update global context code';
debug_info := '(Import_invoice 15.2) Insert record INTO ap_invoices';
IF (AP_IMPORT_UTILITIES_PKG.insert_ap_invoices(
l_invoice_rec, -- IN OUT
l_base_invoice_id, -- OUT NOCOPY
l_set_of_books_id, -- IN
l_dbseqid, -- IN
l_seqval, -- IN
l_batch_id, -- IN
l_pay_curr_invoice_amount, -- IN
l_approval_workflow_flag, -- IN
p_needs_invoice_approval,
l_add_days_settlement_date, -- IN --bug 4930111
l_disc_is_inv_less_tax_flag, -- IN --bug 4931755
l_exclude_freight_from_disc, -- IN --bug 4931755
current_calling_sequence) <> TRUE) THEN
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'<-'||current_calling_sequence);
debug_info := '(Import_invoice 15.3) Insert payment schedules '||
'from terms';
p_last_updated_by =>l_invoice_rec.last_updated_by,
p_created_by =>l_invoice_rec.created_by,
p_payment_priority =>l_payment_priority,
p_batch_id =>l_batch_id,
p_terms_date =>l_invoice_rec.terms_date,
p_invoice_amount =>l_invoice_rec.invoice_amount,
p_pay_curr_invoice_amount =>l_pay_curr_invoice_amount,
p_payment_cross_rate =>l_invoice_rec.payment_cross_rate,
p_amount_for_discount =>
l_invoice_rec.amount_applicable_to_discount,
p_payment_method =>l_invoice_rec.payment_method_code,
p_invoice_currency =>l_invoice_rec.invoice_currency_code,
p_payment_currency =>l_invoice_rec.payment_currency_code,
p_calling_sequence =>current_calling_sequence);
debug_info := '(Import_invoice 15.4) Insert holds for this invoice';
IF (AP_IMPORT_UTILITIES_PKG.insert_holds(
l_base_invoice_id,
p_hold_code,
p_hold_reason,
l_hold_future_payments_flag,
l_supplier_hold_reason,
l_invoice_amount_limit,
/*bug fix:3022381 Added the NVL condition*/
nvl(l_invoice_rec.no_xrate_base_amount, -- Bug 4692091. Added ap_round_currency
ap_utilities_pkg.ap_round_currency(
l_invoice_rec.invoice_amount*nvl(l_invoice_rec.exchange_rate,1),
l_invoice_rec.invoice_currency_code)),
l_invoice_rec.last_updated_by,
current_calling_sequence ) <> TRUE) THEN
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,'<-'||
current_calling_sequence);
l_default_last_updated_by,
l_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'create_lines<-'||current_calling_sequence);
p_line_number_to_delete => NULL,
P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
p_all_error_messages => 'Y',
p_error_code => l_error_code,
p_calling_sequence => current_calling_sequence)) THEN
-- If the call to calculate fails, the import process will
-- fail. In this case the invoice cannot be imported since
-- user is trying to import tax lines
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'ap_etax_pkg.calling_etax(CALCULATE IMPORT)<-'||current_calling_sequence);
p_line_number_to_delete => NULL,
P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
p_all_error_messages => 'Y',
p_error_code => l_error_code,
p_calling_sequence => current_calling_sequence)) THEN
-- If the import of tax fails, the import process will fail.
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'ap_etax_pkg.calling_etax(IMPORT INTERFACE)<-'||current_calling_sequence);
p_line_number_to_delete => NULL,
P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
p_all_error_messages => 'Y',
p_error_code => l_error_code,
p_calling_sequence => current_calling_sequence)) THEN
-- If the call to determine recovery fails, the import process
-- will fail. In this case the invoice cannot be imported since
-- user is trying to import tax lines
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'ap_etax_pkg.calling_etax(DISTRIBUTE IMPORT)<-'||current_calling_sequence);
p_line_number_to_delete => NULL,
P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
p_all_error_messages => 'Y',
p_error_code => l_error_code,
p_calling_sequence => current_calling_sequence)) THEN
-- If the calculation of tax fails the invoice will be imported
-- anyway, and the error(s) will be included in the log file.
-- Tax can be later be calculated from the invoice workbench or
-- during the validation of the invoice.
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'ap_etax_pkg.calling_etax(CALCULATE IMPORT)<-'||current_calling_sequence);
SELECT sum(nvl(amount_remaining,0))
INTO l_inv_amount_unpaid
FROM ap_payment_schedules
WHERE invoice_id = l_base_invoice_id;
p_user_id => l_default_last_updated_by,
p_last_update_login => l_default_last_update_login,
p_calling_sequence => current_calling_sequence,
p_prepay_appl_log => l_prepay_appl_log);
debug_info := '(Import_invoice 15.9) Update the invoice amount '||
'if flag add_tax_to_inv_amt_flag is set with the '||
'total of the exclusive tax lines created for the '||
'invoice';
SELECT ai.invoice_amount,
(SELECT NVL(SUM(NVL(ail.amount, 0)), 0)
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = l_base_invoice_id
AND ail.line_type_lookup_code = 'TAX')
INTO l_inv_hdr_amount, l_exclusive_tax_amount
FROM ap_invoices_all ai
WHERE ai.invoice_id = l_base_invoice_id;
X_last_updated_by => l_default_last_updated_by,
X_message1 => l_message1,
X_message2 => l_message2,
X_reset_match_status => l_reset_match_status,
X_liability_adjusted_flag => l_liability_adjusted_flag,
X_calling_sequence => 'APXIIMPT',
X_calling_mode => 'APXIIMPT',
X_revalidate_ps => l_revalidate_ps);
UPDATE ap_invoices_all ai
SET ai.invoice_amount = ai.invoice_amount + l_exclusive_tax_amount,
ai.amount_applicable_to_discount = ai.amount_applicable_to_discount + l_exclusive_tax_amount
WHERE ai.invoice_id = l_base_invoice_id;
UPDATE ap_invoices_all ai
SET ai.pay_curr_invoice_amount = ai.pay_curr_invoice_amount +
gl_currency_api.convert_amount(
ai.invoice_currency_code,
ai.payment_currency_code,
ai.payment_cross_rate_date,
ai.payment_cross_rate_type,
l_exclusive_tax_amount)
WHERE ai.invoice_id = l_base_invoice_id;
debug_info := '(Import_invoice 15.10) Delete the contents of '||
'the l_invoice_lines_tab Lines Table';
l_invoice_lines_tab.DELETE;
debug_info := '(Import_invoice 15.11) Delete the contents of '||
'the eTax global temp tables';
BEGIN DELETE zx_trx_headers_gt;
BEGIN DELETE zx_transaction_lines_gt;
BEGIN DELETE zx_import_tax_lines_gt;
BEGIN DELETE zx_trx_tax_link_gt;
l_default_last_updated_by, -- IN
l_default_last_update_login, -- IN
l_invoice_status, -- OUT instr_status_flag
l_valid_invoices_count, -- OUT p_invoices_count
l_total_invoice_amount, -- OUT p_invoices_total
current_calling_sequence) <> TRUE) THEN
--
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'Import_Retroprice_Adjustments<-'||current_calling_sequence);
IF (AP_IMPORT_UTILITIES_PKG.Insert_ap_batches(
l_batch_id,
l_batch_name,
l_invoice_rec.invoice_currency_code,
l_invoice_rec.payment_currency_code,
l_valid_invoices_count,-- bug1721820
l_actual_invoice_total,-- bug1721820
l_default_last_updated_by,
current_calling_sequence) <> TRUE) THEN
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'Insert_ap_batches<-'||current_calling_sequence);
IF (AP_IMPORT_UTILITIES_PKG.Update_Ap_Batches(
l_batch_id,
p_batch_name,
l_valid_invoices_count,
l_actual_invoice_total,
l_default_last_updated_by,
current_calling_sequence) <> TRUE) THEN
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'Update_Ap_Batches<-'||current_calling_sequence);
debug_info := '(Import_invoice 21) Update temporary status';
IF (AP_IMPORT_UTILITIES_PKG.Update_temp_invoice_status(
p_source,
p_group_id,
current_calling_sequence) <> TRUE) THEN
IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
AP_IMPORT_UTILITIES_PKG.Print(
AP_IMPORT_INVOICES_PKG.g_debug_switch,
'Update_temp_invoice_statu<-'||current_calling_sequence);
SELECT to_char(ap_invoice_import_wfevent_s.nextval)
INTO l_event_key
FROM dual;
SELECT to_char(AP_INV_IMPORT_EVENT_S.nextval)
INTO l_event_key
FROM dual;
'(Import_invoice:EXCEPTION) The invoices to be SELECTed by this ' ||
'process are locked');
SELECT invoice_id
FROM ap_invoices_interface
WHERE source = p_source
AND status = 'PROCESSED'
AND ((p_commit_cycles IS NULL) OR
(ROWNUM <= p_commit_cycles))
AND ((org_id IS NOT NULL and p_org_id IS NOT NULL and
org_id = p_org_id)
OR (p_org_id IS NULL and org_id is NOT NULL and
(mo_global.check_access(org_id)= 'Y'))
OR (p_org_id IS NOT NULL and org_id IS NULL)
OR (p_org_id IS NULL and org_id IS NULL))
ORDER BY vendor_id,
vendor_num,
vendor_name,
vendor_site_id,
vendor_site_code,
invoice_num;
SELECT invoice_id
FROM ap_invoices_interface
WHERE source = p_source
AND group_id = p_group_id
AND status = 'PROCESSED'
AND ((p_commit_cycles IS NULL) OR
(ROWNUM <= p_commit_cycles))
AND ((org_id IS NOT NULL and p_org_id IS NOT NULL and
org_id = p_org_id)
OR (p_org_id IS NULL and org_id is NOT NULL and
(mo_global.check_access(org_id)= 'Y'))
OR (p_org_id IS NOT NULL and org_id IS NULL)
OR (p_org_id IS NULL and org_id IS NULL))
ORDER BY vendor_id,
vendor_num,
vendor_name,
vendor_site_id,
vendor_site_code,
invoice_num;
SELECT count(*)
INTO l_total_count
FROM ap_invoices_interface
WHERE source = p_source
AND status = 'PROCESSED'
AND ( (org_id IS NOT NULL AND
p_org_id IS NOT NULL AND
org_id = p_org_id)
OR (p_org_id IS NULL AND
org_id is NOT NULL and
(mo_global.check_access(org_id)= 'Y'))
OR (p_org_id IS NOT NULL and org_id IS NULL)
OR (p_org_id IS NULL and org_id IS NULL));
SELECT count(*)
INTO l_total_count
FROM ap_invoices_interface
WHERE source = p_source
AND group_id = p_group_id
AND status = 'PROCESSED'
AND ( (org_id IS NOT NULL AND
p_org_id IS NOT NULL AND
org_id = p_org_id)
OR (p_org_id IS NULL AND
org_id is NOT NULL and
(mo_global.check_access(org_id)= 'Y'))
OR (p_org_id IS NOT NULL and org_id IS NULL)
OR (p_org_id IS NULL and org_id IS NULL));
debug_info := '(Import_purge 3) Delete records in ' ||
'ap_invoice_lines_interface...';
DELETE FROM AP_INVOICE_LINES_INTERFACE
WHERE invoice_id = l_invoice_id;
debug_info := '(Import_purge 4.1) Delete attachments if any...';
debug_info := '(Import_purge 4.2) before delete attachments: '||
'source = ' || p_source || ', invoice_id = ' || l_invoice_id;
l_attachments_count := delete_attachments(l_invoice_id);
debug_info := '(Import_purge 4.2) delete attachments done: '||
l_attachments_count;
debug_info := '(Import_purge 4) Delete records in ' ||
'ap_invoices_interface...';
DELETE FROM AP_INVOICES_INTERFACE
WHERE invoice_id = l_invoice_id
AND ( (org_id IS NOT NULL AND
p_org_id IS NOT NULL AND
org_id = p_org_id)
OR (p_org_id IS NULL AND
org_id is NOT NULL AND
(mo_global.check_access(org_id)= 'Y'))
OR (p_org_id IS NOT NULL AND
org_id IS NULL)
OR (p_org_id IS NULL AND
org_id IS NULL));
SELECT h.invoice_id BULK COLLECT
INTO h_list
FROM ap_invoices_interface h,
ap_invoice_lines_interface l,
ap_interface_rejections r
WHERE DECODE(r.parent_table, 'AP_INVOICES_INTERFACE',
h.invoice_id,
'AP_INVOICE_LINES_INTERFACE', l.invoice_line_id)
= r.parent_id
AND h.invoice_id = l.invoice_id
AND nvl(r.notify_vendor_flag,'N') = 'Y'
AND h.status = 'REJECTED'
AND h.source = 'XML GATEWAY'
AND nvl(h.ORG_ID,
to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')) )
= to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))
GROUP BY h.invoice_id;
SELECT h.invoice_id BULK COLLECT
INTO h_list
FROM ap_invoices_interface h,
ap_invoice_lines_interface l,
ap_interface_rejections r
WHERE DECODE(r.parent_table, 'AP_INVOICES_INTERFACE',
h.invoice_id,
'AP_INVOICE_LINES_INTERFACE', l.invoice_line_id)
= r.parent_id
AND h.invoice_id = l.invoice_id
AND nvl(r.notify_vendor_flag,'N') = 'Y'
AND h.status = 'REJECTED'
AND h.source = 'XML GATEWAY'
AND h.group_id = p_group_id
AND nvl(h.ORG_ID,
to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')) )
= to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))
GROUP BY h.invoice_id;
debug_info := '(XML Import Purge 1.1) number of invoices to delete: '
|| nvl(h_list.count,0);
DELETE FROM ap_interface_rejections r
WHERE r.parent_id = h_list(i)
AND r.parent_table = 'AP_INVOICES_INTERFACE';
DELETE FROM ap_interface_rejections r
WHERE r.parent_id IN (SELECT l.invoice_line_id
FROM ap_invoice_lines_interface l
WHERE l.invoice_id = h_list(i) )
AND r.parent_table = 'AP_INVOICE_LINES_INTERFACE';
DELETE FROM ap_invoice_lines_interface l
WHERE l.invoice_id = h_list(i);
DELETE FROM ap_invoices_interface h
WHERE h.invoice_id = h_list(i);
SELECT parent_table,
parent_id,
reject_lookup_code
FROM ap_interface_rejections
WHERE parent_table = 'AP_INVOICES_INTERFACE'
AND parent_id = p_invoice_interface_id
UNION
SELECT parent_table,
parent_id,
reject_lookup_code
FROM ap_interface_rejections
WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND parent_id IN (SELECT invoice_line_id
FROM ap_invoice_lines_interface
WHERE invoice_id = p_invoice_interface_id);
SELECT source
INTO l_source
FROM ap_invoices_interface
WHERE invoice_id = p_invoice_interface_id;