CREATE OR REPLACE PACKAGE BODY xxte_apinvoice_pay_intf
AS
PROCEDURE xxte_apinvoice_main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
lv_errbuff VARCHAR2 (1000);
lv_retcode NUMBER;
BEGIN
xxte_apinvoice_ins (lv_errbuff, lv_retcode);
xxte_apinvoice_comm (lv_errbuff, lv_retcode);
EXCEPTION
WHEN OTHERS
THEN
lv_retcode := 1;
lv_errbuff := 'Error: In Main Procudure.' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, lv_errbuff);
END;
PROCEDURE xxte_apinvoice_comm (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
CURSOR stg_cur
IS
SELECT a.*, a.ROWID
FROM xxte_apinv_comm_st a
WHERE process_flag = 'N';
-- declare local variables.
lv_user_id NUMBER;
lv_resp_id NUMBER;
lv_resp_appl_id NUMBER;
lv_vendor_id NUMBER;
lv_vendor_no NUMBER;
lv_error_message VARCHAR2 (2400);
lv_error_code NUMBER := 0;
lv_vendor_site_id NUMBER;
lv_invoice_date VARCHAR2 (1);
lv_gl_date VARCHAR2 (1);
lv_exist_rec NUMBER;
lv_invoice_amt NUMBER;
lv_acc NUMBER;
lv_source VARCHAR2 (30);
lv_invoice_type VARCHAR2 (50);
lv_dist_ccid NUMBER;
BEGIN
lv_user_id := fnd_profile.VALUE ('USER_ID');
lv_resp_id := fnd_profile.VALUE ('RESP_ID');
lv_resp_appl_id := fnd_profile.VALUE ('RESP_APPL_ID');
BEGIN
fnd_global.apps_initialize (lv_user_id, lv_resp_id, lv_resp_appl_id);
mo_global.init ('AP');
END;
-- Purge the previous processed data in the stage/interface table
BEGIN
DELETE FROM ap_invoice_lines_interface apil
WHERE apil.invoice_id IN (SELECT invoice_id
FROM ap_invoices_interface
WHERE status = 'PROCESSED');
DELETE FROM ap_invoices_interface
WHERE status = 'PROCESSED';
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Delete The Invoice Stage/Interface Table.'
|| SQLERRM;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
fnd_file.put_line(2, '****************************Agent Commission Invoice***************************');
fnd_file.put_line(2, '------------------------------------------------------------------------------- ');
FOR i IN stg_cur
LOOP
lv_error_message := NULL;
lv_error_code := 0;
lv_source := 'TE INSIS';
lv_dist_ccid := 2106;
-- Validation for Invoice Type
BEGIN
SELECT invoice_type
INTO lv_invoice_type
FROM xxte_apinv_comm_st
WHERE ROWID = i.ROWID;
IF lv_invoice_type IS NULL
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Invoice Type Is Null';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Invalid Invoice Type';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for vendor name
BEGIN
SELECT vendor_id
INTO lv_vendor_id
FROM po_vendors
WHERE vendor_name = i.vendor_name;
IF lv_vendor_id IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor Name '
|| ' '
|| i.vendor_name
|| ' '
|| 'is Not Found';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor Name '
|| ' '
|| i.vendor_name
|| ' '
|| 'is Not Found';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for vendor no
BEGIN
SELECT segment1
INTO lv_vendor_no
FROM po_vendors
WHERE segment1 = i.vendor_no AND vendor_name = i.vendor_name;
IF lv_vendor_no IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor No '
|| ' '
|| i.vendor_no
|| ' '
|| ' is Not Found for the vendor'
|| ' '
|| i.vendor_name;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor No '
|| ' '
|| i.vendor_no
|| ' '
|| ' is Not Found for the vendor'
|| ' '
|| i.vendor_name;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for vendor site id
BEGIN
SELECT vendor_site_id
INTO lv_vendor_site_id
FROM po_vendor_sites_all
WHERE UPPER (vendor_site_code) = UPPER (i.vendor_site)
AND vendor_id IN (SELECT vendor_id
FROM po_vendors
WHERE vendor_name = i.vendor_name);
IF lv_vendor_site_id IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor Site Name '
|| ' '
|| i.vendor_site
|| ' '
|| 'is Not found for the Vendor'
|| ' '
|| i.vendor_name;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor Site Name '
|| ' '
|| i.vendor_site
|| ' '
|| 'is Not found for the Vendor'
|| ' '
|| i.vendor_name;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for invoice date
BEGIN
SELECT 'x'
INTO lv_invoice_date
FROM gl_period_statuses
WHERE application_id = 200 ---For Oracle Payables.
AND set_of_books_id = 2022
AND (i.invoice_date >= start_date
AND i.invoice_date <= end_date
)
AND closing_status = 'O';
IF lv_invoice_date IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Invoice Date Is NULL ';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Period is Not Open for the Invoice Date '
|| ' '
|| i.invoice_date;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for GL date
BEGIN
SELECT 'x'
INTO lv_gl_date
FROM gl_period_statuses
WHERE application_id = 200 ---For Oracle Payables.
AND set_of_books_id = 2022
AND (i.gl_date >= start_date AND i.gl_date <= end_date)
AND closing_status = 'O';
IF lv_gl_date IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'GL Date Is NULL ';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Period is Not Open for the GL Date '
|| ' '
|| i.gl_date;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for currency code
BEGIN
IF i.invoice_currency != 'AED'
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Invalid Currency Code '
|| ' '
|| i.invoice_currency;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
END;
-- Validation for Invoice amount
BEGIN
SELECT invoice_amount
INTO lv_invoice_amt
FROM xxte_apinv_comm_st
WHERE ROWID = i.ROWID;
IF lv_invoice_amt IS NULL
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Invoice Amount Is Null';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Invoice Amount Is Null';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for Account
BEGIN
SELECT dist_account
INTO lv_acc
FROM xxte_apinv_comm_st
WHERE ROWID = i.ROWID;
IF lv_acc IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: ' || 'Distribution Account Is Null';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: ' || 'Distribution Account Is Null';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
--Validation for excisting record in base table
BEGIN
SELECT COUNT (*)
INTO lv_exist_rec
FROM ap_invoices_all
WHERE invoice_amount = i.invoice_amount
AND invoice_date = i.invoice_date
AND vendor_id IN (SELECT vendor_id
FROM po_vendors
WHERE vendor_name = i.vendor_name);
IF lv_exist_rec > 0
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Record Already Exists';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Record Already Exists';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
IF lv_invoice_type IN
('PAYABLE', 'COMMISION ADV', 'CLAIM', 'SURRENDER',
'MATURITY', 'RETAKAFUL EXP', 'RETAKAFUL PAYABLE',
'CLAIM PAYABLE', 'DEATH CLAIM EXP')
AND lv_invoice_amt > 0
THEN
lv_invoice_type := 'STANDARD';
ELSIF lv_invoice_type IN
('PAYABLE', 'COMMISION ADV', 'REFUND', 'RETAKAFUL EXP',
'RETAKAFUL PAYABLE', 'RETAFUL COMMISION',
'RETAKAFUL SHARE IN EXP', 'RECEIVABLE FROM RETAKAFUL')
AND lv_invoice_amt < 0
THEN
lv_invoice_type := 'DEBIT';
ELSE
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Invalid Invoice Type';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
IF lv_error_code = 0
THEN
INSERT INTO ap_invoices_interface
(invoice_id,
invoice_num,
gl_date, invoice_type_lookup_code, invoice_date,
vendor_id, vendor_site_id, invoice_amount,
invoice_currency_code, description, SOURCE, org_id
)
VALUES (ap_invoices_interface_s.NEXTVAL,
'INSIS_'
|| i.vendor_site
|| '_'
|| ap_invoices_interface_s.CURRVAL,
i.gl_date, lv_invoice_type, -- will change
i.invoice_date,
lv_vendor_id, lv_vendor_site_id, i.invoice_amount,
i.invoice_currency, i.description, -- will change
lv_source, 81
);
INSERT INTO ap_invoice_lines_interface
(invoice_id, line_number, line_type_lookup_code,
amount, dist_code_combination_id,
-- if value dist id is entered here no need to enter value in dist_code_concatenated
org_id
)
VALUES (ap_invoices_interface_s.CURRVAL, i.line_no, 'ITEM',
i.amount, lv_dist_ccid, -- will update
81
);
fnd_file.put_line (2, 'Payable Invoice Is Created');
UPDATE xxte_apinv_comm_st
SET process_flag = 'S'
WHERE ROWID = i.ROWID;
ELSE
fnd_file.put_line (2, 'Please Check The Log File For Error(s)');
UPDATE xxte_apinv_comm_st
SET process_flag = 'E',
error_message = lv_error_message
WHERE ROWID = i.ROWID;
END IF;
END LOOP;
COMMIT;
fnd_file.put_line(2,'**************************** END ***************************');
fnd_file.put_line(2,'------------------------------------------------------------------------------- ');
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
fnd_file.put_line (fnd_file.LOG, 'Error :' || SQLERRM);
COMMIT;
END;
PROCEDURE xxte_apinvoice_ins (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
CURSOR ins_c
IS
SELECT 'PAYABLE' invoice_type, a.vendor_name, a.vendor_no,
a.vendor_site,
(SELECT invoice_date
FROM xxte_apinv_pay_temp
WHERE vendor_name = a.vendor_name
AND vendor_site = a.vendor_site
AND ROWNUM = 1) invoice_date,
(SELECT invoice_currency
FROM xxte_apinv_pay_temp
WHERE vendor_name = a.vendor_name
AND vendor_site = a.vendor_site
AND ROWNUM = 1) invoice_currency,
SUM (a.invoice_amount) invoice_amount,
(SELECT gl_date
FROM xxte_apinv_pay_temp
WHERE vendor_name = a.vendor_name
AND vendor_site = a.vendor_site
AND ROWNUM = 1) gl_date,
(SELECT description
FROM xxte_apinv_pay_temp
WHERE vendor_name = a.vendor_name
AND vendor_site = a.vendor_site
AND ROWNUM = 1) description,
SUM (a.amount) amount,
(SELECT dist_account
FROM xxte_apinv_pay_temp
WHERE vendor_name = a.vendor_name
AND vendor_site = a.vendor_site
AND ROWNUM = 1) dist_account
FROM xxte_apinv_pay_temp a
WHERE a.invoice_type IN ('COMMISION ADV', 'PAYABLE')
GROUP BY a.vendor_name, a.vendor_site, a.vendor_no
HAVING SUM (a.invoice_amount) != 0
UNION
SELECT *
FROM xxte_apinv_pay_temp
WHERE invoice_type NOT IN ('COMMISION ADV', 'PAYABLE');
lv_error_message VARCHAR2 (2400);
lv_error_code NUMBER := 0;
BEGIN
BEGIN
DELETE FROM xxte_apinv_comm_st
WHERE process_flag != 'N';
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Delete Record(s) In Staging Table.'
|| SQLERRM;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
FOR i IN ins_c
LOOP
INSERT INTO xxte_apinv_comm_st
(invoice_type, vendor_name, vendor_no,
vendor_site, invoice_date, invoice_currency,
invoice_amount, gl_date, description, amount,
dist_account
)
VALUES (i.invoice_type, i.vendor_name, i.vendor_no,
i.vendor_site, i.invoice_date, i.invoice_currency,
i.invoice_amount, i.gl_date, i.description, i.amount,
i.dist_account
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Insert Data In Staging Table.' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
END xxte_apinvoice_pay_intf;
/
