CREATE OR REPLACE PACKAGE BODY XXTE_AR_INVOICE_INTF
AS
PROCEDURE XXTE_ARINVOICE_MAIN (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
lv_errbuff VARCHAR2 (1000);
lv_retcode NUMBER;
BEGIN
xxte_arinvoice_ins (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_arinvoice_ins (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
CURSOR stg_cur
IS
SELECT a.*, a.ROWID
FROM xxte_ar_invoice_st a
WHERE process_flag = 'N';
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
p_batch_source_rec apps.ar_invoice_api_pub.batch_source_rec_type;
p_trx_header_tbl apps.ar_invoice_api_pub.trx_header_tbl_type;
p_trx_lines_tbl apps.ar_invoice_api_pub.trx_line_tbl_type;
p_trx_dist_tbl apps.ar_invoice_api_pub.trx_dist_tbl_type;
p_trx_salescredits_tbl apps.ar_invoice_api_pub.trx_salescredits_tbl_type;
p_trx_contingencies_tbl apps.ar_invoice_api_pub.trx_contingencies_tbl_type;
x_customer_trx_id NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
h_trx_header_id ra_customer_trx_all.customer_trx_id%TYPE;
l_trx_ln_hdr_id ra_customer_trx_all.customer_trx_id%TYPE;
l_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE;
lv_errbuff VARCHAR2 (1000);
lv_retcode NUMBER;
lv_user_id NUMBER;
lv_resp_id NUMBER;
lv_resp_appl_id NUMBER;
lv_source NUMBER;
lv_gl_date VARCHAR2(1);
lv_trx_date VARCHAR2(1);
lv_class NUMBER;
lv_customer_id NUMBER;
lv_error_message VARCHAR2 (2400);
lv_error_code NUMBER := 0;
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 ('AR');
END;
-- Purge the previous processed data in the stage table
BEGIN
DELETE FROM xxte_ar_invoice_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;
fnd_file.put_line(2,'****************************AR Invoice Process***************************');
fnd_file.put_line(2,'------------------------------------------------------------------------------- ');
FOR i IN stg_cur
LOOP
lv_error_message := NULL;
lv_error_code := 0;
lv_source := 1002;--'TE INSIS';
--lv_class := 1080;
-- Validation for GL date
BEGIN
SELECT 'X'
INTO lv_gl_date
FROM gl_period_statuses
WHERE application_id = 222 ---For Oracle Receivable
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 or Period is Not Open for the GL Date ';
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 or GL Date IS Null'
|| ' '
|| i.gl_date;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for Trx date
BEGIN
SELECT 'X'
INTO lv_trx_date
FROM gl_period_statuses
WHERE application_id = 222 ---For Oracle Receivable
AND set_of_books_id = 2022
AND (i.trx_date >= start_date AND i.trx_date <= end_date)
AND closing_status = 'O';
IF lv_trx_date IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_error_code := 1;
lv_error_message := 'Error: ' || 'Transaction Date Is NULL or Period is Not Open for the Transaction Date ';
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 Transaction Date or Transaction Date IS Null'
|| ' '
|| i.trx_date;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
-- Validation for customer name
BEGIN
SELECT customer_id
INTO lv_customer_id
FROM ar_customers
WHERE customer_name = i.bill_to_name;
IF lv_customer_id IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Customer Name '
|| ' '
|| i.bill_to_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: '
|| 'Customer Name '
|| ' '
|| i.bill_to_name
|| ' '
|| 'is Not Found';
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
--- Validation for description
BEGIN
IF i.description IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Description is null' ;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
END;
--- Validation for unit price
BEGIN
IF i.unit_price IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Unit Price is null' ;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
END;
--- Validation for invoicing rule
BEGIN
IF i.class = '1020' THEN
IF i.rule_end_date IS NULL and i.rule_start_date IS NULL THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Rule Start Date/Rule End Date Is Null' ;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
ELSIF i.rule_start_date > i.rule_end_date THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Rule End Date Is Greater Then Rule Start Date' ;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
ELSIF i.rule_end_date <> i.gl_date and i.rule_end_date <> i.trx_date THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'GL Date, Transaction Date and Rule End Date Must Be Same' ;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END IF;
END IF;
END;
IF lv_error_code = 0 THEN
/* API initialize */
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_false;
p_commit := fnd_api.g_true;
p_batch_source_rec.batch_source_id := lv_source;
/*Header Part*/
SELECT ra_customer_trx_s.NEXTVAL
INTO h_trx_header_id
FROM DUAL;
p_trx_header_tbl (1).trx_header_id := h_trx_header_id;
p_trx_header_tbl (1).trx_date := i.trx_date;
-- p_trx_header_tbl (1).trx_currency := 'AED';
-- p_trx_header_tbl (1).trx_class := 'INV';
p_trx_header_tbl (1).cust_trx_type_id := i.class;
p_trx_header_tbl (1).gl_date := i.gl_date;
p_trx_header_tbl (1).bill_to_customer_id := lv_customer_id;
IF i.class = '1020' THEN
p_trx_header_tbl (1).invoicing_rule_id := -2;
END IF;
--p_trx_header_tbl (1).term_id := 5;
/*Line Part*/
SELECT ra_customer_trx_s.CURRVAL
INTO l_trx_ln_hdr_id
FROM DUAL;
p_trx_lines_tbl (1).trx_header_id := l_trx_ln_hdr_id;
SELECT ra_customer_trx_lines_s.NEXTVAL
INTO l_trx_line_id
FROM DUAL;
p_trx_lines_tbl (1).trx_line_id := l_trx_line_id;
p_trx_lines_tbl (1).line_number := 1;
p_trx_lines_tbl (1).description := i.description;
p_trx_lines_tbl (1).quantity_invoiced := 1;
p_trx_lines_tbl (1).unit_selling_price := i.unit_price;
p_trx_lines_tbl (1).line_type := 'LINE';
IF i.class = '1020' THEN
p_trx_lines_tbl (1).accounting_rule_id := 2000;
p_trx_lines_tbl (1).rule_start_date := i.rule_start_date;
p_trx_lines_tbl (1).rule_end_date := i.rule_end_date;
END IF;
x_customer_trx_id := NULL;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
BEGIN
apps.ar_invoice_api_pub.create_single_invoice (p_api_version,
p_init_msg_list,
p_commit,
p_batch_source_rec,
p_trx_header_tbl,
p_trx_lines_tbl,
p_trx_dist_tbl,
p_trx_salescredits_tbl,
p_trx_contingencies_tbl,
x_customer_trx_id,
x_return_status,
x_msg_count,
x_msg_data
);
fnd_file.put_line (2, 'Receivable Invoice Is Created. Customer Trx ID:'|| x_customer_trx_id);
UPDATE xxte_ar_invoice_st SET process_flag = 'S' WHERE ROWID = i.ROWID;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Insert Data In Base Table.' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
ELSE
fnd_file.put_line (2, 'Please Check The Log File For Error(s)');
UPDATE xxte_ar_invoice_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;
lv_error_message :=
'Error: Unable To Insert Data In Base Table.' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, lv_error_message);
END;
END xxte_ar_invoice_intf;
