CREATE OR REPLACE PROCEDURE create_invoice (errcode OUT VARCHAR2,
errmsg OUT VARCHAR2)
IS
l_err_msg VARCHAR2 (1000);
l_err_code VARCHAR2 (1000);
l_invoice_num VARCHAR2 (100);
l_invoice_type_lookup_code VARCHAR2 (100);
l_vendor_id NUMBER;
l_vendor_site_id NUMBER;
l_invoice_amount NUMBER;
l_invoice_currency_code VARCHAR2 (10);
l_source VARCHAR2 (240);
l_org_id NUMBER;
l_orgn_id NUMBER;
l_entry_date DATE;
l_form_number NUMBER;
l_group_id NUMBER;
l_goods_received_date DATE;
l_currency_code VARCHAR2 (10);
l_po_number VARCHAR2 (40);
l_amount NUMBER;
counter NUMBER;
l_ship_to_location_id NUMBER;
l_code_combination_id NUMBER;
l_term_id NUMBER;
l_dist_code_comb_id NUMBER;
v_count NUMBER;
l_error_flag VARCHAR2 (1);
l_error_details VARCHAR2 (250);
CURSOR headers
IS
SELECT * FROM xx_temp_inv_hdr;
CURSOR lines (p_invoice_id NUMBER)
IS
SELECT *
FROM xx_temp_inv_line
WHERE invoice_number = p_invoice_id;
BEGIN
-- mo_global.init('PO');
-- mo_global.SET_POLICY_CONTEXT('',88);
l_err_msg := NULL;
l_amount := 0;
v_count := 0;
FOR hdr_rec IN headers
LOOP
BEGIN
SELECT COUNT ( * )
INTO v_count
FROM ap_invoices_all
WHERE invoice_num = hdr_rec.invoice_number;
IF v_count > 0
THEN
DBMS_OUTPUT.put_line('Invoice No already exists -->'
|| v_count
|| SQLERRM);
ELSE
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM ap_suppliers
WHERE vendor_name = hdr_rec.supplier_name; --'LOGWELL FORGE LTD.';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Vendor id is not found' || SQLERRM);
END;
IF l_vendor_id IS NOT NULL
THEN
DBMS_OUTPUT.put_line ('Vendor id is not found' || SQLERRM);
BEGIN
SELECT vendor_site_id
INTO l_vendor_site_id
FROM ap_supplier_sites_all
WHERE vendor_site_code = hdr_rec.supplier_site_name
AND vendor_id = l_vendor_id;
IF l_vendor_site_id IS NOT NULL
THEN
DBMS_OUTPUT.put_line (
'Vendor id is not found' || SQLERRM
);
BEGIN
SELECT code_combination_id
INTO l_code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments =
hdr_rec.liability_account;
IF l_code_combination_id IS NOT NULL
THEN
DBMS_OUTPUT.put_line (
'Vendor id is not found' || SQLERRM
);
BEGIN
SELECT term_id
INTO l_term_id
FROM ap_terms_tl
WHERE NAME =
'60 DAYS AFTER RECEIPT OF MATERIAL';
IF l_term_id IS NOT NULL
THEN
DBMS_OUTPUT.put_line (
'Vendor id is not found' || SQLERRM
);
DBMS_OUTPUT.put_line (
'start header loop found'
);
INSERT INTO ap_invoices_interface (
invoice_id,
invoice_type_lookup_code,
invoice_num,
invoice_date,
gl_date,
vendor_id,
vendor_num,
vendor_site_id,
invoice_amount,
invoice_currency_code,
description,
terms_id,
payment_method_code,
doc_category_code,
accts_pay_code_combination_id,
goods_received_date,
org_id,
SOURCE,
last_update_date,
last_updated_by,
creation_date,
created_by
)
VALUES (ap_invoices_interface_s.NEXTVAL, -- invoice_id
hdr_rec.invoice_type,
-- type
hdr_rec.invoice_number,
hdr_rec.invoice_date,
hdr_rec.gl_date,
l_vendor_id,
hdr_rec.supplier_number,
l_vendor_site_id,
hdr_rec.invoice_amount,
hdr_rec.invoice_currency,
hdr_rec.invoice_description,
l_term_id,
hdr_rec.payment_method,
hdr_rec.document_category,
l_code_combination_id,
hdr_rec.good_receiving_date,
246,
'OPEN BALANCE',
SYSDATE,
fnd_profile.VALUE ('USER_ID'),
SYSDATE,
fnd_profile.VALUE ('USER_ID'));
fnd_file.put_line (
fnd_file.LOG,
'Invoice Num ' || hdr_rec.invoice_number
);
counter := 1;
DBMS_OUTPUT.put_line (
'end header loop found'
);
FOR rec IN lines (hdr_rec.invoice_number)
LOOP
DBMS_OUTPUT.put_line (
'start line loop found' || SQLERRM
);
BEGIN
SELECT code_combination_id
INTO l_dist_code_comb_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments =
rec.ACCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line('dist code combination_id is not found'
|| SQLERRM);
END;
DBMS_OUTPUT.put_line('start insert line loop found'
|| SQLERRM);
INSERT INTO ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
dist_code_combination_id,
description,
org_id,
last_update_date,
last_updated_by,
creation_date,
created_by
)
VALUES (
ap_invoices_interface_s.CURRVAL,
ap_invoice_lines_interface_s.NEXTVAL,
rec.line_number,
rec.TYPE,
rec.amount,
l_dist_code_comb_id,
rec.description,
246,
SYSDATE,
fnd_profile.VALUE (
'USER_ID'
),
SYSDATE,
fnd_profile.VALUE (
'USER_ID'
)
);
DBMS_OUTPUT.put_line (
'end line loop found' || SQLERRM
);
DBMS_OUTPUT.put_line('Invoice Line Num '
|| rec.line_number
|| ' Line Amount: '
|| rec.amount);
fnd_file.put_line (
fnd_file.LOG,
'Invoice Line Num '
|| rec.line_number
|| ' Line Amount: '
|| rec.amount
);
counter := counter + 1;
END LOOP;
ELSE
DBMS_OUTPUT.put_line (
'Vendor id is not found' || SQLERRM
);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (
'Term name is not found' || SQLERRM
);
END;
ELSE
DBMS_OUTPUT.put_line (
'Vendor id is not found' || SQLERRM
);
END IF;
--'01-SSC-001-24106-0000-000-0000-000';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (
'Account Number is not found' || SQLERRM
);
END;
ELSE
DBMS_OUTPUT.put_line (
'Vendor id is not found' || SQLERRM
);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (
'Vendor site id is not found' || SQLERRM
);
END;
ELSE
DBMS_OUTPUT.put_line ('Vendor id is not found' || SQLERRM);
END IF;
END IF;
END;
--DBMS_OUTPUT.put_line ('INVOICE NUM v_count' || v_count);
END LOOP;
fnd_file.put_line (fnd_file.LOG, lines%ROWCOUNT || ' Rows Inserted');
fnd_file.put_line (fnd_file.output, 'ap invoice lines are valid');
fnd_file.put_line (fnd_file.LOG, headers%ROWCOUNT || ' Rows Inserted');
fnd_file.put_line (fnd_file.output, 'ap invoice headers are valid');
COMMIT;
END create_invoice;
/
