DECLARE
CURSOR c_hdr
IS
SELECT *
FROM APINV_HDR_STG
WHERE 1=1
and STATUS ='STAGED'
;
CURSOR c_line ( ln_inv_id NUMBER )
IS
SELECT *
FROM APINV_LINES_STG
WHERE invoice_id = ln_inv_id;
CURSOR get_org_id IS
SELECT DISTINCT org_id
FROM apinv_hdr_stg hdr
WHERE 1 = 1
AND hdr.status = 'INTERFACED';
l_errbuf
VARCHAR2(2000);
l_retcode
VARCHAR2(2000);
lc_final_flag
VARCHAR2 (1)
:= 'Y';
lc_header_flag
VARCHAR2 (1)
:= 'Y';
lc_line_flag
VARCHAR2 (1)
:= 'Y';
ln_count
NUMBER
:= 0;
l_conc_request_id
NUMBER
:= 0;
l_batch_name
VARCHAR2(100);
l_phase
VARCHAR2(25);
l_status
VARCHAR2(25);
l_dev_phase
VARCHAR2(25);
l_dev_status
VARCHAR2(25);
l_message
VARCHAR2(500);
l_request_status
BOOLEAN;
l_error_message
VARCHAR2(2000);
l_int_status
VARCHAR2(100);
l_int_error
VARCHAR2(4000);
l_invoice_id
NUMBER;
l_invoice_line_id
NUMBER;
l_line_number
NUMBER := 0;
--l_int_cnt number;
BEGIN
fnd_file.put_line (fnd_file.log ,'LOAD_INT_DATA start: ');
FOR i IN c_hdr
LOOP
fnd_file.put_line (fnd_file.log ,'c_hdr Loop Start');
lc_header_flag
:='Y';
ln_count
:= ln_count + 1;
l_invoice_id := ap_invoices_interface_s.NEXTVAL;
fnd_file.put_line (fnd_file.log ,'Interface Invoice Number: ' || i.invoice_num);
BEGIN
INSERT INTO ap_invoices_interface
(invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, vendor_id
, vendor_num
, vendor_name
, vendor_site_id
, vendor_site_code
, invoice_amount
, invoice_currency_code
, description
, last_update_date
, last_updated_by
, creation_date
, created_by
, SOURCE
, request_id
, gl_date
, org_id
, pay_group_lookup_code
, payment_method_lookup_code
, external_bank_account_id
, reference_key2
, requester_id
, terms_date
, attribute2
)
VALUES (
l_invoice_id
, i.invoice_num
, i.invoice_type_lookup_code
, i.invoice_date
, i.vendor_id
, i.vendor_num
, i.vendor_name
, i.vendor_site_id
, i.vendor_site_code
, i.invoice_amount
, i.invoice_currency_code
, i.description
, i.last_update_date
, i.last_updated_by
, i.creation_date
, i.created_by
, i.SOURCE
, i.request_id
, i.gl_date
, i.org_id
, i.pay_group_lookup_code
, i.payment_method_lookup_code
, i.external_bank_account_id
, i.report_id
, i.requester_id
, i.invoice_date
, 'No'
);
EXCEPTION WHEN OTHERS THEN
--lc_sucss_flag := 'N';
lc_header_flag := 'N';
lc_final_flag := 'N';
l_errbuf := SQLERRM;
l_error_message := 'Error loading header data into AP Interface : ' || l_errbuf;
fnd_file.put_line (fnd_file.log ,l_error_message);
ROLLBACK;
END;
-- Insert Lines only if Header inserted successfully
IF lc_header_flag = 'Y' THEN
lc_line_flag := 'Y';
l_line_number
:= 0;
FOR j IN c_line (i.invoice_id)
LOOP
l_invoice_line_id
:= ap_invoice_lines_interface_s.NEXTVAL;
l_line_number
:= l_line_number + 1;
fnd_file.put_line (fnd_file.log ,'Inside AP_interface_line for Invoice Number: ' || i.invoice_num);
BEGIN
INSERT INTO ap_invoice_lines_interface
(invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, description
, amount
, accounting_date
, dist_code_concatenated
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, quantity_invoiced
, last_updated_by
, last_update_date
, created_by
, creation_date
, org_id
, attribute15
, dist_code_combination_id
)
VALUES (
l_invoice_id
, l_invoice_line_id
, l_line_number
, j.line_type_lookup_code
, j.description
, j.amount
, j.accounting_date
, j.dist_code_concatenated
, j.project_id
, j.task_id
, j.expenditure_type
, j.expenditure_item_date
, j.expenditure_organization_id
, j.quantity_invoiced
, j.last_updated_by
, j.last_update_date
, j.created_by
, j.creation_date
, j.org_id
, j.attribute1
, j.dist_code_combination_id
);
--lc_sucss_flag := 'Y';
--lc_final_flag := 'Y';
EXCEPTION WHEN OTHERS THEN
--lc_sucss_flag := 'N';
lc_line_flag := 'N';
lc_final_flag := 'N';
l_errbuf := SQLERRM;
l_error_message := 'Error While loading Line data into AP Interface : '|| l_errbuf;
fnd_file.put_line (fnd_file.log ,l_error_message);
ROLLBACK;
END;
END LOOP;
IF lc_line_flag = 'Y' THEN
UPDATE apinv_hdr_stg
SET status
= 'INTERFACED'
WHERE invoice_id = i.invoice_id;
COMMIT;
END IF;
END IF;
END LOOP;
IF ln_count = 0 THEN
fnd_file.put_line (fnd_file.log ,'No records to be loaded into AP Invoice Interface Tables');
END IF;
IF ln_count > 0 THEN
FOR get_org_id_rec IN get_org_id
LOOP
mo_global.set_policy_context('S', get_org_id_rec.org_id);
fnd_global.apps_initialize(fnd_global.user_id, fnd_global.resp_id, fnd_global.resp_appl_id);
l_batch_name := 'TEST'||to_char(sysdate,'DD-MON-RRRR HH24:MI:SS');
fnd_file.put_line (fnd_file.log ,'get_org_id_rec.org_id: '||get_org_id_rec.org_id);
fnd_file.put_line (fnd_file.log ,'l_batch_name: '||l_batch_name);
l_phase := NULL;
l_status := NULL;
l_dev_phase := NULL;
l_dev_status := NULL;
l_message
:= NULL;
l_conc_request_id := NULL;
l_conc_request_id := fnd_request.submit_request ( application => 'SQLAP'
, program => 'APXIIMPT'
, argument1 => get_org_id_rec.org_id
, argument2 => 'MANUAL INVOICE ENTRY'
, argument3 => NULL
, argument4 => l_batch_name
);
COMMIT;
fnd_file.put_line (fnd_file.log ,'l_conc_request_id : ' || l_conc_request_id);
l_request_status := fnd_concurrent.wait_for_request ( l_conc_request_id
, 10
, 0
, l_phase
, l_status
, l_dev_phase
, l_dev_status
, l_message
);
fnd_file.put_line (fnd_file.log ,'l_conc_request_id1 : ' || l_conc_request_id);
END LOOP;
END IF;
fnd_file.put_line (fnd_file.log ,'LOAD_INT_DATA end: ');
EXCEPTION WHEN OTHERS THEN
l_errbuf := SQLERRM;
l_retcode := 1;
fnd_file.put_line (fnd_file.log ,'Unhandled Exception While loading data into AP Interface : ' || l_errbuf);
END load_stg_data_test;
/
