[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_ia_recs
(
inv_id NUMBER ,
inv_num VARCHAR2 ,
ven_id NUMBER ,
ven_name VARCHAR2
)
IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'insert_ia_recs';
INSERT INTO fv_interagency_funds
(
interagency_fund_id,
set_of_books_id,
processed_flag,
chargeback_flag,
last_update_date,
last_updated_by,
created_by,
creation_date,
vendor_id,
vendor_name,
invoice_id,
invoice_number
)
VALUES
(
fv_interagency_funds_s.nextval,
g_set_of_books_id,
'N',
'N',
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
SYSDATE,
ven_id,
ven_name,
inv_id,
inv_num
);
END insert_ia_recs;
CURSOR ia_trx_select_csr
(
p_sob_id NUMBER
) IS
SELECT ai.invoice_id ,
ai.invoice_num,
ai.vendor_id ,
pv.vendor_name,
ai.creation_date
FROM ap_invoices ai , po_vendors pv
WHERE ai.vendor_id = pv.vendor_id
AND ai.invoice_num LIKE 'IPAC%'
AND ai.payment_method_lookup_code = 'CLEARING'
AND EXISTS (SELECT 'X'
FROM fv_operating_units
WHERE set_of_books_id = p_sob_id
AND default_alc = 'Y'
AND payables_ia_paygroup = ai.pay_group_lookup_code)
AND TO_DATE(ai.creation_date,'DD-MM-YYYY') BETWEEN
TO_DATE(parm_inv_creation_date_low,'DD-MM-YYYY') AND
TO_DATE(parm_inv_creation_date_high,'DD-MM-YYYY')
AND NOT EXISTS (SELECT 'X'
FROM fv_interagency_funds
WHERE set_of_books_id = p_sob_id
AND invoice_id IS NOT NULL
AND invoice_id = ai.invoice_id);
FOR trx_select IN ia_trx_select_csr(g_set_of_books_id) LOOP
l_count := l_count+1;
insert_ia_recs(trx_select.invoice_id,
trx_select.invoice_num,
trx_select.vendor_id,
trx_select.vendor_name);
PROCEDURE insert_error
(
p_ipac_import_id IN fv_ipac_import_errors.ipac_import_id%TYPE,
p_validation_code IN fv_ipac_import_errors.error_code%TYPE,
p_validation_err IN fv_ipac_import_errors.error_desc%TYPE,
p_error_code OUT NOCOPY NUMBER,
p_error_desc OUT NOCOPY VARCHAR2
)
IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'insert_error';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into fv_ipac_import_errors');
INSERT INTO fv_ipac_import_errors
(
ipac_import_id,
error_code,
error_desc
)
VALUES
(
p_ipac_import_id,
p_validation_code,
p_validation_err
);
PROCEDURE insert_invoice_hdr
(
p_invoice_hdr_rec IN ap_invoices_interface%ROWTYPE,
p_error_code OUT NOCOPY NUMBER,
p_error_desc OUT NOCOPY VARCHAR2
)
IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'insert_invoice_hdr';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into ap_invoices_interface');
INSERT INTO ap_invoices_interface
(
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,
description,
awt_group_id,
awt_group_name,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
status,
source,
group_id,
request_id,
payment_cross_rate_type,
payment_cross_rate_date,
payment_cross_rate,
payment_currency_code,
workflow_flag,
doc_category_code,
voucher_num,
payment_method_lookup_code,
pay_group_lookup_code,
goods_received_date,
invoice_received_date,
gl_date,
accts_pay_code_combination_id,
-- ussgl_transaction_code,
exclusive_payment_flag,
org_id,
amount_applicable_to_discount,
prepay_num,
prepay_dist_num,
prepay_apply_amount,
prepay_gl_date,
invoice_includes_prepay_flag,
no_xrate_base_amount,
vendor_email_address,
terms_date,
requester_id,
ship_to_location,
external_doc_ref,
payment_method_code
)
VALUES
(
p_invoice_hdr_rec.invoice_id,
p_invoice_hdr_rec.invoice_num,
p_invoice_hdr_rec.invoice_type_lookup_code,
p_invoice_hdr_rec.invoice_date,
p_invoice_hdr_rec.po_number,
p_invoice_hdr_rec.vendor_id,
p_invoice_hdr_rec.vendor_num,
p_invoice_hdr_rec.vendor_name,
p_invoice_hdr_rec.vendor_site_id,
p_invoice_hdr_rec.vendor_site_code,
p_invoice_hdr_rec.invoice_amount,
p_invoice_hdr_rec.invoice_currency_code,
p_invoice_hdr_rec.exchange_rate,
p_invoice_hdr_rec.exchange_rate_type,
p_invoice_hdr_rec.exchange_date,
p_invoice_hdr_rec.terms_id,
p_invoice_hdr_rec.terms_name,
p_invoice_hdr_rec.description,
p_invoice_hdr_rec.awt_group_id,
p_invoice_hdr_rec.awt_group_name,
p_invoice_hdr_rec.last_update_date,
p_invoice_hdr_rec.last_updated_by,
p_invoice_hdr_rec.last_update_login,
p_invoice_hdr_rec.creation_date,
p_invoice_hdr_rec.created_by,
p_invoice_hdr_rec.attribute_category,
p_invoice_hdr_rec.attribute1,
p_invoice_hdr_rec.attribute2,
p_invoice_hdr_rec.attribute3,
p_invoice_hdr_rec.attribute4,
p_invoice_hdr_rec.attribute5,
p_invoice_hdr_rec.attribute6,
p_invoice_hdr_rec.attribute7,
p_invoice_hdr_rec.attribute8,
p_invoice_hdr_rec.attribute9,
p_invoice_hdr_rec.attribute10,
p_invoice_hdr_rec.attribute11,
p_invoice_hdr_rec.attribute12,
p_invoice_hdr_rec.attribute13,
p_invoice_hdr_rec.attribute14,
p_invoice_hdr_rec.attribute15,
p_invoice_hdr_rec.global_attribute_category,
p_invoice_hdr_rec.global_attribute1,
p_invoice_hdr_rec.global_attribute2,
p_invoice_hdr_rec.global_attribute3,
p_invoice_hdr_rec.global_attribute4,
p_invoice_hdr_rec.global_attribute5,
p_invoice_hdr_rec.global_attribute6,
p_invoice_hdr_rec.global_attribute7,
p_invoice_hdr_rec.global_attribute8,
p_invoice_hdr_rec.global_attribute9,
p_invoice_hdr_rec.global_attribute10,
p_invoice_hdr_rec.global_attribute11,
p_invoice_hdr_rec.global_attribute12,
p_invoice_hdr_rec.global_attribute13,
p_invoice_hdr_rec.global_attribute14,
p_invoice_hdr_rec.global_attribute15,
p_invoice_hdr_rec.global_attribute16,
p_invoice_hdr_rec.global_attribute17,
p_invoice_hdr_rec.global_attribute18,
p_invoice_hdr_rec.global_attribute19,
p_invoice_hdr_rec.global_attribute20,
p_invoice_hdr_rec.status,
p_invoice_hdr_rec.source,
p_invoice_hdr_rec.group_id,
p_invoice_hdr_rec.request_id,
p_invoice_hdr_rec.payment_cross_rate_type,
p_invoice_hdr_rec.payment_cross_rate_date,
p_invoice_hdr_rec.payment_cross_rate,
p_invoice_hdr_rec.payment_currency_code,
p_invoice_hdr_rec.workflow_flag,
p_invoice_hdr_rec.doc_category_code,
p_invoice_hdr_rec.voucher_num,
p_invoice_hdr_rec.payment_method_lookup_code,
p_invoice_hdr_rec.pay_group_lookup_code,
p_invoice_hdr_rec.goods_received_date,
p_invoice_hdr_rec.invoice_received_date,
p_invoice_hdr_rec.gl_date,
p_invoice_hdr_rec.accts_pay_code_combination_id,
-- p_invoice_hdr_rec.ussgl_transaction_code,
p_invoice_hdr_rec.exclusive_payment_flag,
p_invoice_hdr_rec.org_id,
p_invoice_hdr_rec.amount_applicable_to_discount,
p_invoice_hdr_rec.prepay_num,
p_invoice_hdr_rec.prepay_dist_num,
p_invoice_hdr_rec.prepay_apply_amount,
p_invoice_hdr_rec.prepay_gl_date,
p_invoice_hdr_rec.invoice_includes_prepay_flag,
p_invoice_hdr_rec.no_xrate_base_amount,
p_invoice_hdr_rec.vendor_email_address,
p_invoice_hdr_rec.terms_date,
p_invoice_hdr_rec.requester_id,
p_invoice_hdr_rec.ship_to_location,
p_invoice_hdr_rec.external_doc_ref,
p_invoice_hdr_rec.payment_method_code
);
PROCEDURE insert_invoice_line
(
p_invoice_lines_rec IN OUT NOCOPY ap_invoice_lines_interface%ROWTYPE,
p_error_code OUT NOCOPY NUMBER,
p_error_desc OUT NOCOPY VARCHAR2
)
IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'insert_invoice_line';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into ap_invoice_lines_interface');
INSERT INTO ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
line_group_number,
amount,
accounting_date,
description,
amount_includes_tax_flag,
prorate_across_flag,
tax_code,
final_match_flag,
po_header_id,
po_number,
po_line_id,
po_line_number,
po_line_location_id,
po_shipment_num,
po_distribution_id,
po_distribution_num,
po_unit_of_measure,
inventory_item_id,
item_description,
quantity_invoiced,
ship_to_location_code,
unit_price,
distribution_set_id,
distribution_set_name,
dist_code_concatenated,
dist_code_combination_id,
awt_group_id,
awt_group_name,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
po_release_id,
release_num,
account_segment,
balancing_segment,
cost_center_segment,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
project_accounting_context,
pa_addition_flag,
pa_quantity,
-- ussgl_transaction_code,
stat_amount,
type_1099,
income_tax_region,
assets_tracking_flag,
price_correction_flag,
org_id,
receipt_number,
receipt_line_number,
match_option,
packing_slip,
rcv_transaction_id,
pa_cc_ar_invoice_id,
pa_cc_ar_invoice_line_num,
reference_1,
reference_2,
pa_cc_processed_code,
tax_recovery_rate,
tax_recovery_override_flag,
tax_recoverable_flag,
tax_code_override_flag,
tax_code_id,
credit_card_trx_id,
award_id,
vendor_item_num,
taxable_flag,
price_correct_inv_num,
external_doc_line_ref
)
VALUES
(
p_invoice_lines_rec.invoice_id,
ap_invoice_lines_interface_s.NEXTVAL,
p_invoice_lines_rec.line_number,
p_invoice_lines_rec.line_type_lookup_code,
p_invoice_lines_rec.line_group_number,
p_invoice_lines_rec.amount,
p_invoice_lines_rec.accounting_date,
p_invoice_lines_rec.description,
p_invoice_lines_rec.amount_includes_tax_flag,
p_invoice_lines_rec.prorate_across_flag,
p_invoice_lines_rec.tax_code,
p_invoice_lines_rec.final_match_flag,
p_invoice_lines_rec.po_header_id,
p_invoice_lines_rec.po_number,
p_invoice_lines_rec.po_line_id,
p_invoice_lines_rec.po_line_number,
p_invoice_lines_rec.po_line_location_id,
p_invoice_lines_rec.po_shipment_num,
p_invoice_lines_rec.po_distribution_id,
p_invoice_lines_rec.po_distribution_num,
p_invoice_lines_rec.po_unit_of_measure,
p_invoice_lines_rec.inventory_item_id,
p_invoice_lines_rec.item_description,
p_invoice_lines_rec.quantity_invoiced,
p_invoice_lines_rec.ship_to_location_code,
p_invoice_lines_rec.unit_price,
p_invoice_lines_rec.distribution_set_id,
p_invoice_lines_rec.distribution_set_name,
p_invoice_lines_rec.dist_code_concatenated,
p_invoice_lines_rec.dist_code_combination_id,
p_invoice_lines_rec.awt_group_id,
p_invoice_lines_rec.awt_group_name,
p_invoice_lines_rec.last_updated_by,
p_invoice_lines_rec.last_update_date,
p_invoice_lines_rec.last_update_login,
p_invoice_lines_rec.created_by,
p_invoice_lines_rec.creation_date,
p_invoice_lines_rec.attribute_category,
p_invoice_lines_rec.attribute1,
p_invoice_lines_rec.attribute2,
p_invoice_lines_rec.attribute3,
p_invoice_lines_rec.attribute4,
p_invoice_lines_rec.attribute5,
p_invoice_lines_rec.attribute6,
p_invoice_lines_rec.attribute7,
p_invoice_lines_rec.attribute8,
p_invoice_lines_rec.attribute9,
p_invoice_lines_rec.attribute10,
p_invoice_lines_rec.attribute11,
p_invoice_lines_rec.attribute12,
p_invoice_lines_rec.attribute13,
p_invoice_lines_rec.attribute14,
p_invoice_lines_rec.attribute15,
p_invoice_lines_rec.global_attribute_category,
p_invoice_lines_rec.global_attribute1,
p_invoice_lines_rec.global_attribute2,
p_invoice_lines_rec.global_attribute3,
p_invoice_lines_rec.global_attribute4,
p_invoice_lines_rec.global_attribute5,
p_invoice_lines_rec.global_attribute6,
p_invoice_lines_rec.global_attribute7,
p_invoice_lines_rec.global_attribute8,
p_invoice_lines_rec.global_attribute9,
p_invoice_lines_rec.global_attribute10,
p_invoice_lines_rec.global_attribute11,
p_invoice_lines_rec.global_attribute12,
p_invoice_lines_rec.global_attribute13,
p_invoice_lines_rec.global_attribute14,
p_invoice_lines_rec.global_attribute15,
p_invoice_lines_rec.global_attribute16,
p_invoice_lines_rec.global_attribute17,
p_invoice_lines_rec.global_attribute18,
p_invoice_lines_rec.global_attribute19,
p_invoice_lines_rec.global_attribute20,
p_invoice_lines_rec.po_release_id,
p_invoice_lines_rec.release_num,
p_invoice_lines_rec.account_segment,
p_invoice_lines_rec.balancing_segment,
p_invoice_lines_rec.cost_center_segment,
p_invoice_lines_rec.project_id,
p_invoice_lines_rec.task_id,
p_invoice_lines_rec.expenditure_type,
p_invoice_lines_rec.expenditure_item_date,
p_invoice_lines_rec.expenditure_organization_id,
p_invoice_lines_rec.project_accounting_context,
p_invoice_lines_rec.pa_addition_flag,
p_invoice_lines_rec.pa_quantity,
-- p_invoice_lines_rec.ussgl_transaction_code,
p_invoice_lines_rec.stat_amount,
p_invoice_lines_rec.type_1099,
p_invoice_lines_rec.income_tax_region,
p_invoice_lines_rec.assets_tracking_flag,
p_invoice_lines_rec.price_correction_flag,
p_invoice_lines_rec.org_id,
p_invoice_lines_rec.receipt_number,
p_invoice_lines_rec.receipt_line_number,
p_invoice_lines_rec.match_option,
p_invoice_lines_rec.packing_slip,
p_invoice_lines_rec.rcv_transaction_id,
p_invoice_lines_rec.pa_cc_ar_invoice_id,
p_invoice_lines_rec.pa_cc_ar_invoice_line_num,
p_invoice_lines_rec.reference_1,
p_invoice_lines_rec.reference_2,
p_invoice_lines_rec.pa_cc_processed_code,
p_invoice_lines_rec.tax_recovery_rate,
p_invoice_lines_rec.tax_recovery_override_flag,
p_invoice_lines_rec.tax_recoverable_flag,
p_invoice_lines_rec.tax_code_override_flag,
p_invoice_lines_rec.tax_code_id,
p_invoice_lines_rec.credit_card_trx_id,
p_invoice_lines_rec.award_id,
p_invoice_lines_rec.vendor_item_num,
p_invoice_lines_rec.taxable_flag,
p_invoice_lines_rec.price_correct_inv_num,
p_invoice_lines_rec.external_doc_line_ref
) RETURNING invoice_line_id INTO p_invoice_lines_rec.invoice_line_id;
FOR invoices_rec IN (SELECT aii.invoice_id,
air.reject_lookup_code,
fii.ipac_import_id,
alc.description
FROM ap_invoices_interface aii,
ap_interface_rejections air,
fv_ipac_import fii,
ap_lookup_codes alc
WHERE aii.group_id = p_group_id
AND aii.status = 'REJECTED'
AND aii.invoice_id = air.parent_id
AND air.parent_table = 'AP_INVOICES_INTERFACE'
AND fii.group_id = p_group_id
AND fii.int_invoice_id = aii.invoice_id
AND alc.lookup_type = 'REJECT CODE'
AND alc.lookup_code = air.reject_lookup_code) LOOP
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id='||invoices_rec.invoice_id);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
insert_error
(
p_ipac_import_id => invoices_rec.ipac_import_id,
p_validation_code => invoices_rec.reject_lookup_code,
p_validation_err => invoices_rec.description,
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'insert_error returned with code'||p_error_code);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'UPDATE fv_ipac_import1');
UPDATE fv_ipac_import
SET record_status = g_status_error
WHERE ipac_import_id = invoices_rec.ipac_import_id;
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'UPDATED '||l_rowcount||' rows.');
l_location := l_module_name||'.update_fv_ipac_import1';
FOR invoice_lines_rec IN (SELECT aii.invoice_id,
air.reject_lookup_code,
fii.ipac_import_id,
alc.description
FROM ap_invoices_interface aii,
ap_invoice_lines_interface aili,
ap_interface_rejections air,
fv_ipac_import fii,
ap_lookup_codes alc
WHERE aii.group_id = p_group_id
AND aii.status = 'REJECTED'
AND aili.invoice_line_id = air.parent_id
AND aii.invoice_id = aili.invoice_id
AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND fii.group_id = p_group_id
AND fii.int_invoice_id = aii.invoice_id
AND fii.int_invoice_line_id = aili.invoice_line_id
AND alc.lookup_type = 'REJECT CODE'
AND alc.lookup_code = air.reject_lookup_code) LOOP
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id='||invoice_lines_rec.invoice_id);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
insert_error
(
p_ipac_import_id => invoice_lines_rec.ipac_import_id,
p_validation_code => invoice_lines_rec.reject_lookup_code,
p_validation_err => invoice_lines_rec.description,
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'UPDATE fv_ipac_import2');
UPDATE fv_ipac_import
SET record_status = g_status_error
WHERE ipac_import_id = invoice_lines_rec.ipac_import_id;
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'UPDATED '||l_rowcount||' rows.');
l_location := l_module_name||'.update_fv_ipac_import2';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting ap_interface_groups_s');
SELECT 'IPAC'||TO_CHAR(fv_ipac_batch_s.NEXTVAL),
ap_interface_groups_s.NEXTVAL
INTO p_batch_name,
p_group_id
FROM dual;
l_location := l_module_name||'.select_fv_ipac_batch_s_nextval';
UPDATE fv_ipac_import
SET request_id = g_request_id,
record_status = g_status_preprocessed,
created_by = g_user_id,
last_updated_by = g_user_id,
last_update_date = SYSDATE,
batch_name = p_batch_name,
org_id = g_org_id,
set_of_books_id = g_set_of_books_id,
group_id = p_group_id,
data_file = p_data_file_name
WHERE request_id = -1
AND record_status = g_status_imported;
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updated '||l_rowcount||' rows.');
l_location := l_module_name||'.update_fv_ipac_import';
DELETE fv_ipac_import
WHERE group_id = p_group_id
AND record_status = g_status_preprocessed
AND (transaction_type IN ('A', 'P')
OR customer_alc <> p_Agency_Location_Code);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleted '||l_rowcount||' rows.');
fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.update_fv_ipac_import',p_error_desc) ;
FOR del_rec IN (SELECT ipac_import_id
FROM fv_ipac_import
WHERE group_id = p_group_id
AND record_status = g_status_preprocessed
AND transaction_type IN ('A', 'P')) LOOP
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
insert_error
(
p_ipac_import_id => del_rec.ipac_import_id,
p_validation_code => 'INVALID_TXN_TYPE',
p_validation_err => 'Transaction type is A or P.',
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
UPDATE fv_ipac_import
SET record_status = g_status_error
WHERE ipac_import_id = del_rec.ipac_import_id;
l_location := l_module_name||'.update_fv_ipac_import1';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Rejecting not selected ALC records');
FOR del_rec IN (SELECT ipac_import_id
FROM fv_ipac_import
WHERE group_id = p_group_id
AND record_status = g_status_preprocessed
AND customer_alc <> p_Agency_Location_Code) LOOP
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
insert_error
(
p_ipac_import_id => del_rec.ipac_import_id,
p_validation_code => 'INVALID_ALC',
p_validation_err => 'Customer ALC is not the selected one.',
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
UPDATE fv_ipac_import
SET record_status = g_status_error
WHERE ipac_import_id = del_rec.ipac_import_id;
l_location := l_module_name||'.update_fv_ipac_import1';
SELECT ai.invoice_id,
ai.approval_status_lookup_code
FROM ap_invoices_v ai,
ap_recurring_payments_v arp
WHERE arp.recurring_payment_id = ai.recurring_payment_id
AND arp.set_of_books_id = ai.set_of_books_id
AND arp.po_number = c_po_num
AND arp.line_num = NVL(c_po_line_num, 1)
AND ai.source = 'RECURRING INVOICE'
AND ai.invoice_amount = c_amount
AND ai.payment_status_flag = 'N'
AND ai.set_of_books_id = g_set_of_books_id
AND ai.invoice_date = (SELECT MIN(invoice_date)
FROM ap_invoices aib,
ap_recurring_payments_v arpb
WHERE aib.source = 'RECURRING INVOICE'
AND aib.set_of_books_id = g_set_of_books_id
AND aib.payment_status_flag = 'N'
AND aib.invoice_amount = c_amount
AND aib.recurring_payment_id = arpb.recurring_payment_id
AND aib.set_of_books_id = arpb.set_of_books_id
AND arpb.po_number = c_po_num
AND arp.line_num = NVL(c_po_line_num,1))
UNION
SELECT DISTINCT ai.invoice_id,
ai.approval_status_lookup_code
FROM ap_invoices_v ai,
ap_invoice_distributions_v ali
WHERE ali.po_number = c_po_num
AND ai.invoice_id = ali.invoice_id
AND ali.po_line_number = NVL(c_po_line_num,1)
AND ai.invoice_amount = c_amount
AND ai.payment_status_flag = 'N'
AND ai.set_of_books_id = g_set_of_books_id
AND ai.source <> 'RECURRING INVOICE'
AND ai.invoice_type_lookup_code = 'STANDARD'
AND ai.invoice_date = (SELECT MIN(invoice_date)
FROM ap_invoices_v ai1,
ap_invoice_distributions_v ali1
WHERE ali1.po_number = c_po_num
AND ai1.invoice_id = ali1.invoice_id
AND ali1.po_line_number = NVL(c_po_line_num,1)
AND ai1.invoice_amount = c_amount
AND ai1.payment_status_flag = 'N'
AND ai1.set_of_books_id = g_set_of_books_id
AND ai1.source <> 'RECURRING INVOICE'
AND ai1.invoice_type_lookup_code = 'STANDARD');
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into fv_ipac_recurring_inv');
INSERT INTO fv_ipac_recurring_inv
(
batch_name,
invoice_id,
invoice_action,
accomplish_date,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by
)
SELECT p_batch_name,
l_invoice_id,
l_code,
p_accomplished_date, --using accomplish date (attribute11)of po
SYSDATE,
g_user_id,
SYSDATE,
g_login_id,
g_user_id
FROM dual
WHERE NOT EXISTS (SELECT invoice_id
FROM fv_ipac_recurring_inv
WHERE invoice_id = l_invoice_id
AND batch_name = p_batch_name);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from mtl_units_of_measure_vl1');
SELECT unit_of_measure
INTO l_unit_of_measure
FROM mtl_units_of_measure_vl
WHERE uom_code = p_uom_code;
l_location := l_module_name||'.select_mtl_units_of_measure_vl1';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from mtl_units_of_measure_vl1');
SELECT unit_of_measure
INTO l_unit_of_measure
FROM mtl_units_of_measure_vl
WHERE unit_of_measure = p_unit_of_measure;
l_location := l_module_name||'.select_mtl_units_of_measure_vl2';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
insert_error
(
p_ipac_import_id => p_ipac_import_id,
p_validation_code => 'INVALID_UOM',
p_validation_err => 'Invalid Unit of Measure',
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Select fv_ccr_vendors');
SELECT 'X'
INTO l_dummy
FROM fv_ccr_vendors fcv
WHERE fcv.vendor_id = p_ipac_import_rec.vendor_id
AND fcv.duns = p_ipac_import_rec.receiver_duns
AND NVL(fcv.plus_four, '-1') = NVL(p_ipac_import_rec.receiver_duns_4, '-1');
l_location := l_module_name||'.select_po_headers';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Select po_vendor_sites');
SELECT vendor_site_id
INTO l_vendor_site_id
FROM po_vendor_sites pvs
WHERE pvs.vendor_id = p_ipac_import_rec.vendor_id
AND pvs.vendor_site_code = p_ipac_import_rec.receiver_duns||p_ipac_import_rec.receiver_duns_4;
l_location := l_module_name||'.select_po_headers';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
insert_error
(
p_ipac_import_id => p_ipac_import_rec.ipac_import_id,
p_validation_code => l_validation_code,
p_validation_err => l_validation_err,
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_headers');
SELECT po_header_id,
closed_code,
vendor_id,
vendor_site_id
INTO p_ap_inv_lines_rec.po_header_id,
l_hdr_closed_code,
p_ipac_import_rec.vendor_id,
p_ipac_import_rec.vendor_site_id
FROM po_headers
WHERE segment1 = p_ap_inv_lines_rec.po_number;
l_location := l_module_name||'.select_po_headers';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_distributions and lines');
SELECT COUNT(*)
INTO l_count_po_disb_lines
FROM po_distributions pd,
po_lines pl
WHERE pd.po_header_id = p_ap_inv_lines_rec.po_header_id
AND pl.po_line_id = pd.po_line_id
AND pd.set_of_books_id = g_set_of_books_id;
l_location := l_module_name||'.select_po_distributions1';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_distributions, lines and ap_invoice_distributions');
SELECT COUNT(*)
INTO l_count_matched_disb_lines
FROM ap_invoice_distributions aid ,
po_distributions pd ,
po_lines pl
WHERE aid.po_distribution_id = pd.po_distribution_id
AND pd.po_header_id = p_ap_inv_lines_rec.po_header_id
AND pl.po_line_id = pd.po_line_id
AND aid.final_match_flag = 'D'
AND aid.set_of_books_id = pd.set_of_books_id
AND aid.set_of_books_id = g_set_of_books_id ;
l_location := l_module_name||'.select_po_distributions2';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_lines');
SELECT pl.po_line_id,
pl.closed_code,
pl.unit_meas_lookup_code
INTO p_ap_inv_lines_rec.po_line_id,
l_line_closed_code,
p_ap_inv_lines_rec.po_unit_of_measure
FROM po_lines pl
WHERE po_header_id = p_ap_inv_lines_rec.po_header_id
AND line_num = NVL(p_ap_inv_lines_rec.po_line_number, 1);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_lines');
l_location := l_module_name||'.select_po_lines';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_distributions');
SELECT COUNT(*)
INTO l_count_po_disb_lines
FROM po_distributions pd
WHERE pd.po_line_id = p_ap_inv_lines_rec.po_line_id
AND pd.set_of_books_id = g_set_of_books_id;
l_location := l_module_name||'.select_po_distributions3';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_distributions and ap_invoice_distributions');
SELECT COUNT(*)
INTO l_count_matched_disb_lines
FROM ap_invoice_distributions aid ,
po_distributions pd
WHERE aid.po_distribution_id = pd.po_distribution_id
AND pd.po_line_id = p_ap_inv_lines_rec.po_line_id
AND aid.final_match_flag = 'D'
AND aid.set_of_books_id = pd.set_of_books_id
AND aid.set_of_books_id = g_set_of_books_id;
l_location := l_module_name||'.select_po_distributions4';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from ap_recurring_payments_v');
SELECT COUNT(*)
INTO l_exists
FROM ap_recurring_payments_v aprpv
WHERE aprpv.po_header_id = p_ap_inv_lines_rec.po_header_id
AND aprpv.po_line_id = p_ap_inv_lines_rec.po_line_id;
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
insert_error
(
p_ipac_import_id => p_ipac_import_rec.ipac_import_id,
p_validation_code => l_validation_code,
p_validation_err => l_validation_err,
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'insert_error returned');
p_okay_to_insert_inv IN VARCHAR2,
p_total_invoice_lines IN NUMBER,
p_total_invoices IN OUT NOCOPY NUMBER,
p_error_code OUT NOCOPY NUMBER,
p_error_desc OUT NOCOPY VARCHAR2
)
IS
l_module_name VARCHAR2(200);
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'p_okay_to_insert_inv = '||p_okay_to_insert_inv);
IF ((p_previous_inv_number IS NOT NULL) AND (p_okay_to_insert_inv = 'Y')) THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_invoice_hdr');
insert_invoice_hdr
(
p_invoice_hdr_rec => p_ap_inv_hdr_rec,
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'insert_invoice_hdr returned');
IF ((p_previous_inv_number IS NOT NULL) AND (p_okay_to_insert_inv = 'N')) THEN
IF (p_total_invoice_lines > 0) THEN
BEGIN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoice_lines_interface');
DELETE ap_invoice_lines_interface
WHERE invoice_id = p_ap_inv_hdr_rec.invoice_id;
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoice_lines_interface.');
l_location := l_module_name||'delete_ap_invoice_lines_interface';
l_ok_to_insert_inv VARCHAR2(1);
l_no_of_invoices_inserted NUMBER;
SELECT *
FROM fv_ipac_import fii
WHERE fii.group_id = c_group_id
AND fii.record_status = g_status_preprocessed
ORDER BY fii.invoice_number;
l_ok_to_insert_inv := 'Y';
l_no_of_invoices_inserted := 0;
l_inv_lines_interface_rec_null.last_updated_by := g_user_id;
l_inv_lines_interface_rec_null.last_update_date := SYSDATE;
l_inv_hdrs_interface_rec_null.last_updated_by := g_user_id;
l_inv_hdrs_interface_rec_null.last_update_date := SYSDATE;
p_okay_to_insert_inv => l_ok_to_insert_inv,
p_total_invoice_lines => l_current_inv_lines,
p_total_invoices => l_no_of_invoices_inserted,
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_invoices_inserted ='||l_no_of_invoices_inserted);
l_ok_to_insert_inv := 'Y';
SELECT ap_invoices_interface_s.NEXTVAL
INTO l_inv_hdrs_interface_rec.invoice_id
FROM DUAL;
l_location := l_module_name||'select_ap_invoices_interface_s';
l_ok_to_insert_inv := 'N';
l_ok_to_insert_inv := 'N';
IF (p_error_code = g_SUCCESS AND l_ok_to_insert_inv = 'Y') THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling resolve_uom');
l_ok_to_insert_inv := 'N';
IF (p_error_code = g_SUCCESS AND l_ok_to_insert_inv = 'Y') THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_invoice_line');
insert_invoice_line
(
p_invoice_lines_rec => l_inv_lines_interface_rec,
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_invoice_line returned');
UPDATE fv_ipac_import fii
SET record_status = l_ipac_import_record.record_status,
invoice_id = l_ipac_import_record.invoice_id,
int_invoice_id = l_inv_lines_interface_rec.invoice_id,
int_invoice_line_id = l_inv_lines_interface_rec.invoice_line_id,
actual_po_number = l_inv_lines_interface_rec.po_number,
actual_po_line_number = l_inv_lines_interface_rec.po_line_number,
vendor_id = l_inv_hdrs_interface_rec.vendor_id,
vendor_site_id = l_inv_hdrs_interface_rec.vendor_site_id
WHERE fii.ipac_import_id = l_ipac_import_record.ipac_import_id;
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Updated '||l_rowcount||' rows in fv_ipac_import.');
l_location := l_module_name||'update_fv_ipac_import';
p_okay_to_insert_inv => l_ok_to_insert_inv,
p_total_invoice_lines => l_current_inv_lines,
p_total_invoices => l_no_of_invoices_inserted,
p_error_code => p_error_code,
p_error_desc => p_error_desc
);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_invoices_inserted ='||l_no_of_invoices_inserted);
IF (l_no_of_invoices_inserted > 0) THEN
p_ok_to_import := 'Y';
DELETE fv_ipac_import
WHERE record_status = 'IMPORTED';
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from fv_ipac_import.');
l_location := l_module_name||'.delete_fv_ipac_import';
FOR import_rec IN (SELECT invoice_number
FROM fv_ipac_import
WHERE group_id = p_group_id
AND record_status = g_status_error) LOOP
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Currently Processing Invoice Number = '||import_rec.invoice_number);
DELETE ap_interface_rejections
WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND parent_id IN (SELECT invoice_line_id
FROM ap_invoice_lines_interface aili,
ap_invoices_interface aii
WHERE aii.invoice_num = import_rec.invoice_number
AND aii.group_id = p_group_id
AND aii.invoice_id = aili.invoice_id);
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_interface_rejections1.');
l_location := l_module_name||'.delete_ap_interface_rejections1';
DELETE ap_interface_rejections
WHERE parent_table = 'AP_INVOICES_INTERFACE'
AND parent_id IN (SELECT invoice_id
FROM ap_invoices_interface aii
WHERE aii.invoice_num = import_rec.invoice_number
AND aii.group_id = p_group_id);
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_interface_rejections2.');
l_location := l_module_name||'.delete_ap_interface_rejections2';
DELETE ap_invoice_lines_interface
WHERE invoice_id IN (SELECT invoice_id
FROM ap_invoices_interface
WHERE invoice_num = import_rec.invoice_number
AND group_id = p_group_id);
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoice_lines_interface.');
l_location := l_module_name||'.delete_ap_invoice_lines_interface';
DELETE ap_invoices_interface
WHERE invoice_num = import_rec.invoice_number
AND group_id = p_group_id;
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoices_interface.');
l_location := l_module_name||'.delete_ap_invoices_interface';
UPDATE fv_ipac_import
SET record_status = g_status_other_error
WHERE group_id = p_group_id
AND invoice_number = import_rec.invoice_number
AND record_status <> g_status_error;
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Updated '||l_rowcount||' rows in fv_ipac_import.');
l_location := l_module_name||'.update_fv_ipac_import';
INSERT INTO fv_ipac_import_history
(
ipac_import_id,
batch_name,
transaction_id,
submitter_alc,
originating_alc,
customer_alc,
contact_name,
contact_email_address,
contact_phone_number,
summary_amount,
numer_of_detail_lines,
accomplished_date,
accounting_date,
detail_line_number,
contract_number,
purchase_order_number,
clin,
invoice_number,
requisition_number,
quantity,
unit_of_issue,
unit_price,
detail_amount,
pay_flag,
fy_obligation_id,
receiver_tres_acct_symbol,
receiver_betc,
receiver_duns,
receiver_duns_4,
sender_tres_acct_symbol,
sender_betc,
sender_duns,
sender_duns_4,
receiver_department_code,
accounting_class_code,
acrn,
job_project_number,
jas_number,
fsn_aaa_adsn,
obligating_doc_number,
act_trace_number,
description,
misc_information,
transaction_type,
ipac_doc_ref_number,
sender_do_symbol,
dodacc,
transaction_contact,
transcation_contact_phone,
voucher_number,
original_do_symbol,
orig_accomplished_date,
orig_accounting_date,
orig_doc_ref_number,
orig_transaction_type,
sender_sgl_comment,
receiver_sgl_comment,
sgl_number1,
sgl_sender_receiver_flag1,
sgl_federal_flag1,
sgl_debit_credit_flag1,
sgl_amount1,
sgl_number2,
sgl_sender_receiver_flag2,
sgl_federal_flag2,
sgl_debit_credit_flag2,
sgl_amount2,
sgl_number3,
sgl_sender_receiver_flag3,
sgl_federal_flag3,
sgl_debit_credit_flag3,
sgl_amount3,
sgl_number4,
sgl_sender_receiver_flag4,
sgl_federal_flag4,
sgl_debit_credit_flag4,
sgl_amount4,
sgl_number5,
sgl_sender_receiver_flag5,
sgl_federal_flag5,
sgl_debit_credit_flag5,
sgl_amount5,
sgl_number6,
sgl_sender_receiver_flag6,
sgl_federal_flag6,
sgl_debit_credit_flag6,
sgl_amount6,
sgl_number7,
sgl_sender_receiver_flag7,
sgl_federal_flag7,
sgl_debit_credit_flag7,
sgl_amount7,
sgl_number8,
sgl_sender_receiver_flag8,
sgl_federal_flag8,
sgl_debit_credit_flag8,
sgl_amount8,
sgl_number9,
sgl_sender_receiver_flag9,
sgl_federal_flag9,
sgl_debit_credit_flag9,
sgl_amount9,
sgl_number10,
sgl_sender_receiver_flag10,
sgl_federal_flag10,
sgl_debit_credit_flag10,
sgl_amount10,
sgl_number11,
sgl_sender_receiver_flag11,
sgl_federal_flag11,
sgl_debit_credit_flag11,
sgl_amount11,
sgl_number12,
sgl_sender_receiver_flag12,
sgl_federal_flag12,
sgl_debit_credit_flag12,
sgl_amount12,
sgl_number13,
sgl_sender_receiver_flag13,
sgl_federal_flag13,
sgl_debit_credit_flag13,
sgl_amount13,
sgl_number14,
sgl_sender_receiver_flag14,
sgl_federal_flag14,
sgl_debit_credit_flag14,
sgl_amount14,
sgl_number15,
sgl_sender_receiver_flag15,
sgl_federal_flag15,
sgl_debit_credit_flag15,
sgl_amount15,
sgl_number16,
sgl_sender_receiver_flag16,
sgl_federal_flag16,
sgl_debit_credit_flag16,
sgl_amount16,
record_status,
org_id,
set_of_books_id,
invoice_id,
invoice_line_id,
actual_po_number,
actual_po_line_number,
vendor_id,
vendor_site_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
group_id,
data_file,
int_invoice_id,
int_invoice_line_id
)
SELECT ipac_import_id,
batch_name,
transaction_id,
submitter_alc,
originating_alc,
customer_alc,
contact_name,
contact_email_address,
contact_phone_number,
summary_amount,
numer_of_detail_lines,
accomplished_date,
accounting_date,
detail_line_number,
contract_number,
purchase_order_number,
clin,
invoice_number,
requisition_number,
quantity,
unit_of_issue,
unit_price,
detail_amount,
pay_flag,
fy_obligation_id,
receiver_tres_acct_symbol,
receiver_betc,
receiver_duns,
receiver_duns_4,
sender_tres_acct_symbol,
sender_betc,
sender_duns,
sender_duns_4,
receiver_department_code,
accounting_class_code,
acrn,
job_project_number,
jas_number,
fsn_aaa_adsn,
obligating_doc_number,
act_trace_number,
description,
misc_information,
transaction_type,
ipac_doc_ref_number,
sender_do_symbol,
dodacc,
transaction_contact,
transcation_contact_phone,
voucher_number,
original_do_symbol,
orig_accomplished_date,
orig_accounting_date,
orig_doc_ref_number,
orig_transaction_type,
sender_sgl_comment,
receiver_sgl_comment,
sgl_number1,
sgl_sender_receiver_flag1,
sgl_federal_flag1,
sgl_debit_credit_flag1,
sgl_amount1,
sgl_number2,
sgl_sender_receiver_flag2,
sgl_federal_flag2,
sgl_debit_credit_flag2,
sgl_amount2,
sgl_number3,
sgl_sender_receiver_flag3,
sgl_federal_flag3,
sgl_debit_credit_flag3,
sgl_amount3,
sgl_number4,
sgl_sender_receiver_flag4,
sgl_federal_flag4,
sgl_debit_credit_flag4,
sgl_amount4,
sgl_number5,
sgl_sender_receiver_flag5,
sgl_federal_flag5,
sgl_debit_credit_flag5,
sgl_amount5,
sgl_number6,
sgl_sender_receiver_flag6,
sgl_federal_flag6,
sgl_debit_credit_flag6,
sgl_amount6,
sgl_number7,
sgl_sender_receiver_flag7,
sgl_federal_flag7,
sgl_debit_credit_flag7,
sgl_amount7,
sgl_number8,
sgl_sender_receiver_flag8,
sgl_federal_flag8,
sgl_debit_credit_flag8,
sgl_amount8,
sgl_number9,
sgl_sender_receiver_flag9,
sgl_federal_flag9,
sgl_debit_credit_flag9,
sgl_amount9,
sgl_number10,
sgl_sender_receiver_flag10,
sgl_federal_flag10,
sgl_debit_credit_flag10,
sgl_amount10,
sgl_number11,
sgl_sender_receiver_flag11,
sgl_federal_flag11,
sgl_debit_credit_flag11,
sgl_amount11,
sgl_number12,
sgl_sender_receiver_flag12,
sgl_federal_flag12,
sgl_debit_credit_flag12,
sgl_amount12,
sgl_number13,
sgl_sender_receiver_flag13,
sgl_federal_flag13,
sgl_debit_credit_flag13,
sgl_amount13,
sgl_number14,
sgl_sender_receiver_flag14,
sgl_federal_flag14,
sgl_debit_credit_flag14,
sgl_amount14,
sgl_number15,
sgl_sender_receiver_flag15,
sgl_federal_flag15,
sgl_debit_credit_flag15,
sgl_amount15,
sgl_number16,
sgl_sender_receiver_flag16,
sgl_federal_flag16,
sgl_debit_credit_flag16,
sgl_amount16,
record_status,
org_id,
set_of_books_id,
invoice_id,
invoice_line_id,
actual_po_number,
actual_po_line_number,
vendor_id,
vendor_site_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
group_id,
data_file,
int_invoice_id,
int_invoice_line_id
FROM fv_ipac_import
WHERE group_id = p_group_id
AND record_status = g_status_processed;
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Inserted '||l_rowcount||' rows into fv_ipac_import_history.');
l_location := l_module_name||'.insert_fv_ipac_import_history';
DELETE FROM fv_ipac_import
WHERE group_id = p_group_id
AND record_status = g_status_processed;
fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from fv_ipac_import.');
l_location := l_module_name||'.delete_fv_ipac_import';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from fv_operating_units');
SELECT payables_ia_paygroup
INTO g_ia_paygroup
FROM fv_operating_units
WHERE set_of_books_id = g_set_of_books_id;