Oracle Applications & Oracle Cloud Fusion HCM

Tuesday 20 December 2016

Direct Debit Authorization (Inbound)

CREATE OR REPLACE PACKAGE bolinf.xxeq_inbound_auth_pkg AUTHID CURRENT_USER
AS
PROCEDURE update_dd_status (
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_file_name IN VARCHAR2
);
END xxeq_inbound_auth_pkg;
/

CREATE OR REPLACE PACKAGE BODY bolinf.xxeq_inbound_auth_pkg
AS
/*********************************************************************************************************
Version |Name |Date |Change Description
---------------------------------------------------------------------------------------------------------
1.0 |Madhu Dhare |15-Jun-2014 |Initial Version for Italy Inbound Authorization Package for SEPA
*********************************************************************************************************/
PROCEDURE update_dd_status (
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_file_name IN VARCHAR2
)
AS
---Local Variables Declaration
v_proxy_number VARCHAR2 (30) := NULL;
v_iban_number VARCHAR2 (30) := NULL;
v_acpt_result VARCHAR2 (30) := NULL;
v_reason_code VARCHAR2 (30) := NULL;
v_add_rej_info VARCHAR2 (100) := NULL;
v_dir_path VARCHAR2 (250) := NULL;
f_handle UTL_FILE.file_type;
v_line VARCHAR2 (2000);
v_1st_comma NUMBER;
v_2nd_comma NUMBER;
v_3rd_comma NUMBER;
v_4th_comma NUMBER;
v_5th_comma NUMBER;
v_user_name VARCHAR2 (240) := fnd_global.user_name;
v_file_name VARCHAR2 (100) := p_file_name;
v_error_desc VARCHAR2 (2000);
v_sqlcode VARCHAR2 (200);
v_rec_cnt NUMBER := 0;
v_updt_cnt NUMBER := 0;
v_attribute14 VARCHAR2 (30) := NULL;
v_abi VARCHAR2 (5) := NULL;
v_cab VARCHAR2 (5) := NULL;
v_conto VARCHAR2 (12) := NULL;
err_generic EXCEPTION;
err_empty_file EXCEPTION;
err_empty_file1 EXCEPTION;
separatore VARCHAR2 (120) := RPAD ('*'
,120
,'*'
);
l_bank_acct_uses_rec apps.hz_cust_acct_info_pub.bank_acct_uses_rec_type;
l_last_update_date DATE;
l_new_req NUMBER;
x_return_status VARCHAR2 (50);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
v_bank_account_num ap_bank_accounts_all.bank_account_num%TYPE;
v_bank_number ap_bank_branches.bank_number%TYPE;
v_bank_num ap_bank_branches.bank_num%TYPE;
v_customer_number ra_customers.customer_number%TYPE;
v_site_num hz_cust_site_uses_all.LOCATION%TYPE;
v_arbor_ba hz_cust_site_uses_all.attribute5%TYPE;
v_gfp_dd_status ap_bank_account_uses_all.attribute14%TYPE;
v_mismatch_reason VARCHAR2 (100);

CURSOR bank_acc_uses_cur (
v_proxy_number VARCHAR2
)
IS
SELECT abau.bank_account_uses_id
,abau.customer_id
,abau.customer_site_use_id
,abau.vendor_id
,abau.vendor_site_id
,abau.external_bank_account_id
,abau.start_date
,abau.end_date
,abau.primary_flag
,abau.last_update_date
,abau.attribute_category
,abau.attribute1
,abau.attribute2
,abau.attribute3
,abau.attribute4
,abau.attribute5
,abau.attribute6
,abau.attribute7
,abau.attribute8
,abau.attribute9
,abau.attribute10
,abau.attribute11
,abau.attribute12
,abau.attribute13
,abau.attribute14
,aba.bank_account_num
,abb.bank_number
,abb.bank_num
,rc.customer_number
,hcsu.LOCATION
,hcsu.attribute5 arbor_ba
FROM ra_customers rc
,hz_cust_acct_sites_all hcas
,hz_cust_site_uses_all hcsu
,ap_bank_account_uses_all abau
,ap_bank_accounts_all aba
,ap_bank_branches abb
WHERE hcas.cust_account_id = rc.customer_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_code = 'BILL_TO'
AND hcsu.status = 'A'
AND abau.customer_site_use_id = hcsu.site_use_id
AND abau.customer_id = rc.customer_id
AND NVL (abau.end_date, SYSDATE + 1) >= SYSDATE
AND aba.bank_account_id = abau.external_bank_account_id
AND abb.bank_branch_id = aba.bank_branch_id
AND abau.attribute6 = v_proxy_number
AND EXISTS (
SELECT 1
FROM ar_cust_receipt_methods_v acrm
,fnd_lookup_values flv1
WHERE acrm.customer_id = rc.customer_id
AND acrm.site_use_id = abau.customer_site_use_id
AND flv1.lookup_type = 'XXEQ_DIRECT_DEBIT_PAYMENT_MTH'
AND flv1.LANGUAGE = USERENV ('LANG')
AND flv1.enabled_flag = 'Y'
AND NVL (flv1.end_date_active, SYSDATE + 1) >= SYSDATE
AND flv1.lookup_code = acrm.receipt_method_name);

CURSOR log_msg_cur
IS
SELECT *
FROM xxeq_inb_auth_log
ORDER BY log_type
,rec_num
,dds_status;

CURSOR mismatch_msg_cur
IS
SELECT *
FROM xxeq_inb_auth_mismatch
ORDER BY rec_num;

BEGIN
-- derive file name
IF UPPER (v_user_name) = 'BPEL_INTERFACE' THEN
v_file_name := p_file_name;
ELSE
v_file_name := fnd_global.user_id || '-' || p_file_name;
END IF;

-- derive file dir
BEGIN
SELECT fnd_profile.VALUE ('XXEQ_FILE_UPLOAD_INCOMING_DIR')
INTO v_dir_path
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
v_error_desc := 'Unable to retrieve incoming file location ' || SQLERRM;
RAISE;
END;

fnd_file.put_line (fnd_file.LOG, 'File name : ' || v_file_name);
fnd_file.put_line (fnd_file.LOG, 'Directory path : ' || v_dir_path);

-- open file
BEGIN
f_handle := UTL_FILE.fopen (v_dir_path
,v_file_name
,'r'
);
EXCEPTION
WHEN OTHERS THEN
v_error_desc := 'Error in FOPEN instruction of the input file (' || v_file_name || ')';
RAISE;
END;

-- fetch record in loop
/* ----------------------------------------------------------
Looping and reading line using GET_LINE which will
raise NO_DATA_FOUND when last line is read and exit the loop.
---------------------------------------------------------- */
DELETE FROM xxeq_inb_auth_log;

DELETE FROM xxeq_inb_auth_mismatch;

COMMIT;

LOOP
BEGIN
BEGIN
UTL_FILE.get_line (f_handle, v_line);
v_rec_cnt := v_rec_cnt + 1;
v_proxy_number := NULL;
v_iban_number := NULL;
v_acpt_result := NULL;
v_reason_code := NULL;
v_add_rej_info := NULL;
v_dir_path := NULL;
v_1st_comma := NULL;
v_2nd_comma := NULL;
v_3rd_comma := NULL;
v_4th_comma := NULL;
v_5th_comma := NULL;
v_attribute14 := NULL;
v_abi := NULL;
v_cab := NULL;
v_conto := NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF v_rec_cnt = 0 THEN
v_sqlcode := SQLCODE;
v_error_desc := v_file_name || ' is an empty file.';
RAISE err_empty_file;
END IF;

EXIT;
END;

-- separate different fields
/* ----------------------------------------------------------
Fields in the input record are delimited by commas.
Separating the fields from v_line and storing in variables.
---------------------------------------------------------- */
v_1st_comma := INSTR (v_line
,','
,1
,1
);
v_2nd_comma := INSTR (v_line
,','
,1
,2
);
v_3rd_comma := INSTR (v_line
,','
,1
,3
);
v_4th_comma := INSTR (v_line
,','
,1
,4
);
v_5th_comma := INSTR (v_line
,','
,1
,5
);
-- proxy = mandate ID
v_proxy_number := SUBSTR (v_line
,1
, v_1st_comma - 1
);
v_iban_number := SUBSTR (v_line
, v_1st_comma + 1
, v_2nd_comma - v_1st_comma - 1
);
v_acpt_result := SUBSTR (v_line
, v_2nd_comma + 1
, v_3rd_comma - v_2nd_comma - 1
);
v_reason_code := SUBSTR (v_line
, v_3rd_comma + 1
, v_4th_comma - v_3rd_comma - 1
);
v_add_rej_info := SUBSTR (v_line
, v_4th_comma + 1
, v_5th_comma - v_4th_comma - 1
);

-- validate above fields if NULL
IF v_proxy_number = NULL
OR v_iban_number = NULL
OR v_acpt_result = NULL
OR v_reason_code = NULL THEN
v_sqlcode := '';
v_error_desc := 'For record number ' || v_rec_cnt || ' either Proxy, IBAN, Acceptance Result or Reason Code is NULL';
RAISE err_generic;
END IF;

-- derive bank account attribute14
BEGIN
SELECT meaning
,description
INTO v_add_rej_info
,v_attribute14
FROM fnd_lookup_values
WHERE lookup_type = 'XXEQ_REJECTION_REASONS_SEPA'
AND lookup_code = v_reason_code
AND TRUNC (SYSDATE) BETWEEN TRUNC (start_date_active) AND NVL (TRUNC (end_date_active), TO_DATE ('31-DEC-4712', 'DD-MON-RRRR'))
AND enabled_flag = 'Y'
AND LANGUAGE = 'US';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_sqlcode := SQLCODE;
v_error_desc := 'For record number ' || v_rec_cnt || ' Reason code ' || v_reason_code || ' is not present in lookup XXEQ_REJECTION_REASONS_SEPA';
RAISE err_generic;
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_error_desc := 'For record number ' || v_rec_cnt || ' Unexpected error while fetching reason code details from lookup XXEQ_REJECTION_REASONS_SEPA ';
RAISE err_generic;
END;

-- derive ABI, CAB and CONTO from IBAN
v_abi := SUBSTR (v_iban_number
,6
,5
);
v_cab := SUBSTR (v_iban_number
,11
,5
);
v_conto := SUBSTR (v_iban_number
,16
,12
);

FOR c_bank_acc_uses IN bank_acc_uses_cur (v_proxy_number)
LOOP
-- derive mandatory fields to be passed to the API
l_bank_acct_uses_rec := NULL;
l_bank_acct_uses_rec.bank_account_uses_id := c_bank_acc_uses.bank_account_uses_id;
l_bank_acct_uses_rec.customer_id := c_bank_acc_uses.customer_id;
l_bank_acct_uses_rec.customer_site_use_id := c_bank_acc_uses.customer_site_use_id;
l_bank_acct_uses_rec.vendor_id := c_bank_acc_uses.vendor_id;
l_bank_acct_uses_rec.vendor_site_id := c_bank_acc_uses.vendor_site_id;
l_bank_acct_uses_rec.external_bank_account_id := c_bank_acc_uses.external_bank_account_id;
l_bank_acct_uses_rec.start_date := c_bank_acc_uses.start_date;
l_bank_acct_uses_rec.end_date := c_bank_acc_uses.end_date;
l_bank_acct_uses_rec.primary_flag := c_bank_acc_uses.primary_flag;
l_last_update_date := c_bank_acc_uses.last_update_date;
l_bank_acct_uses_rec.attribute_category := c_bank_acc_uses.attribute_category;
l_bank_acct_uses_rec.attribute1 := c_bank_acc_uses.attribute1;
l_bank_acct_uses_rec.attribute2 := c_bank_acc_uses.attribute2;
l_bank_acct_uses_rec.attribute3 := c_bank_acc_uses.attribute3;
l_bank_acct_uses_rec.attribute4 := c_bank_acc_uses.attribute4;
l_bank_acct_uses_rec.attribute5 := c_bank_acc_uses.attribute5;
l_bank_acct_uses_rec.attribute6 := c_bank_acc_uses.attribute6;
l_bank_acct_uses_rec.attribute7 := c_bank_acc_uses.attribute7;
l_bank_acct_uses_rec.attribute8 := c_bank_acc_uses.attribute8;
l_bank_acct_uses_rec.attribute9 := c_bank_acc_uses.attribute9;
l_bank_acct_uses_rec.attribute10 := c_bank_acc_uses.attribute10;
l_bank_acct_uses_rec.attribute11 := c_bank_acc_uses.attribute11;
l_bank_acct_uses_rec.attribute12 := c_bank_acc_uses.attribute12;
l_bank_acct_uses_rec.attribute13 := c_bank_acc_uses.attribute13;
l_bank_acct_uses_rec.attribute14 := c_bank_acc_uses.attribute14;
v_bank_account_num := c_bank_acc_uses.bank_account_num;
v_bank_number := c_bank_acc_uses.bank_number;
v_bank_num := c_bank_acc_uses.bank_num;
v_customer_number := c_bank_acc_uses.customer_number;
v_site_num := c_bank_acc_uses.LOCATION;
v_arbor_ba := c_bank_acc_uses.arbor_ba;
v_gfp_dd_status := c_bank_acc_uses.attribute14;

IF ( LTRIM (v_bank_account_num, '0') = LTRIM (v_conto, '0')
AND v_bank_number = v_abi
AND v_bank_num = v_cab)
OR (v_reason_code IN ('MD09', 'MD16')) THEN
-- update the bank account's attributes using API
BEGIN
l_bank_acct_uses_rec.attribute14 := v_attribute14;
l_bank_acct_uses_rec.attribute15 := TO_CHAR (SYSDATE, 'DD-MON-YYYY');
apps.hz_cust_acct_info_pub.update_bank_acct_uses (p_api_version => 1.0
,p_init_msg_list => apps.fnd_api.g_true
,p_commit => fnd_api.g_false
,p_bank_account_uses_rec => l_bank_acct_uses_rec
,p_last_update_date => l_last_update_date
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_validation_level => fnd_api.g_valid_level_full
);

IF x_return_status = fnd_api.g_ret_sts_success THEN
v_updt_cnt := v_updt_cnt + 1;

INSERT INTO xxeq_inb_auth_log
VALUES (1
,v_rec_cnt
,v_attribute14
, 'Cliente: ' || v_proxy_number || ' Direct Debit status updated to ' || v_attribute14 || ' : ' || v_add_rej_info
);

COMMIT;
ELSE
IF x_msg_count > 0 THEN
fnd_file.put_line (fnd_file.LOG, 'Update API FAILED ERROR(1), x_msg_data : ' || x_msg_data);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SUBSTR (SQLERRM
,1
,200
);
v_error_desc := 'Error in Bank Account Uses UPDATE API';
RAISE err_generic;
END;
ELSE
IF LTRIM (v_bank_account_num, '0') <> LTRIM (v_conto, '0') THEN
v_mismatch_reason := 'Conto is not matching';
END IF;

IF v_bank_number <> v_abi THEN
v_mismatch_reason := v_mismatch_reason || 'ABI is not matching';
END IF;

IF v_bank_num <> v_cab THEN
v_mismatch_reason := v_mismatch_reason || 'CAB is not matching';
END IF;

-- print log for other IBANs against that customer proxy
INSERT INTO xxeq_inb_auth_mismatch
VALUES (v_rec_cnt
,v_customer_number
,v_site_num
,v_arbor_ba
,v_proxy_number
,v_bank_number
,v_bank_num
,v_bank_account_num
,v_gfp_dd_status
,v_reason_code
,v_add_rej_info
,v_abi
,v_cab
,v_conto
,v_mismatch_reason
);

COMMIT;
END IF;
END LOOP;

fnd_file.put_line (fnd_file.output, separatore);
fnd_file.put_line (fnd_file.output, 'Successfully updated records ');
fnd_file.put_line (fnd_file.output, separatore);

FOR c_log_msg IN log_msg_cur
LOOP
fnd_file.put_line (fnd_file.output, c_log_msg.MESSAGE);
END LOOP;

fnd_file.put_line (fnd_file.output, separatore);
fnd_file.put_line (fnd_file.output, 'Records not updated due to Abi-Cab-Conto mismatch');
fnd_file.put_line (fnd_file.output, separatore);
fnd_file.put_line
(fnd_file.output
,'Customer Number Site Number Arbor Billing Account Proxy Number GFP ABI GFP CAB GFP CONTO Direct Debit Status Causale Causale-Description Response ABI Response CAB Response Conto Reason For Not Updating DDS');
fnd_file.put_line
(fnd_file.output
,'------------- -------------- -------------- -------------- ------------ ---------- -------------- -------------- ---------- -------------- -------------- -------------- -------------- --------------');

FOR c_mismatch_msg IN mismatch_msg_cur
LOOP
fnd_file.put_line (fnd_file.output
, c_mismatch_msg.customer_number
|| ' '
|| c_mismatch_msg.site_number
|| ' '
|| c_mismatch_msg.arbor_ba
|| ' '
|| c_mismatch_msg.proxy
|| ' '
|| c_mismatch_msg.gfp_abi
|| ' '
|| c_mismatch_msg.gfp_cab
|| ' '
|| c_mismatch_msg.gfp_conto
|| ' '
|| c_mismatch_msg.gfp_dd_status
|| ' '
|| c_mismatch_msg.resp_rej_code
|| ' '
|| c_mismatch_msg.resp_rej_desc
|| ' '
|| c_mismatch_msg.resp_abi
|| ' '
|| c_mismatch_msg.resp_cab
|| ' '
|| c_mismatch_msg.resp_conto
|| ' '
|| c_mismatch_msg.reason);
END LOOP;
EXCEPTION
WHEN err_generic THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, 'Message: ' || v_error_desc);
fnd_file.put_line (fnd_file.LOG, 'SQLCode : ' || v_sqlcode);
fnd_file.put_line (fnd_file.LOG, 'Record : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, ' ');
WHEN err_empty_file THEN
RAISE err_empty_file1;
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, 'Message: ' || SQLERRM);
fnd_file.put_line (fnd_file.LOG, 'SQLCode: ' || SQLCODE);
fnd_file.put_line (fnd_file.LOG, 'Record : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, ' ');
END;
END LOOP;

-- close file
BEGIN
UTL_FILE.fclose (f_handle);
fnd_file.put_line (fnd_file.LOG, 'Records read : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, 'Records updated : ' || v_updt_cnt);
fnd_file.put_line (fnd_file.LOG, 'Calling File Archive program');
l_new_req :=
fnd_request.submit_request ('XXEQ'
,'XXEQDDAARCH'
,' '
, SYSDATE + (1 / 8640)
,FALSE
,v_file_name
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
);

IF (l_new_req <> 0) THEN
fnd_file.put_line (fnd_file.LOG, 'Request_id: ' || l_new_req || ' submitted for file archive');
ELSE
fnd_file.put_line (fnd_file.LOG, 'Request not submitted for file archive');
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_desc := 'Error in instruction of the input file fclose(' || v_file_name || ')';
RAISE;
END;
EXCEPTION
WHEN err_empty_file1 THEN
retcode := 1;
fnd_file.put_line (fnd_file.LOG, 'Message: ' || v_error_desc);
WHEN UTL_FILE.invalid_path THEN
v_sqlcode := 'UTL_FILE.INVALID_PATH';
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, 'Message: ' || v_error_desc);
fnd_file.put_line (fnd_file.LOG, 'Error : ' || v_sqlcode);
fnd_file.put_line (fnd_file.LOG, 'Record : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, separatore);
retcode := 1;
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, ' ');
WHEN UTL_FILE.invalid_operation THEN
v_sqlcode := 'UTL_FILE.INVALID_OPERATION';
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, 'Message: ' || v_error_desc);
fnd_file.put_line (fnd_file.LOG, 'Error : ' || v_sqlcode);
fnd_file.put_line (fnd_file.LOG, 'Record : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, separatore);
retcode := 1;
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, ' ');
WHEN UTL_FILE.invalid_mode THEN
v_sqlcode := 'UTL_FILE.INVALID_MODE';
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, 'Messagr: ' || v_error_desc);
fnd_file.put_line (fnd_file.LOG, 'Error : ' || v_sqlcode);
fnd_file.put_line (fnd_file.LOG, 'Record : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, separatore);
retcode := 1;
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, ' ');
WHEN UTL_FILE.invalid_filehandle THEN
v_sqlcode := 'UTL_FILE.INVALID_FILEHANDLE';
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, 'Message: ' || v_error_desc);
fnd_file.put_line (fnd_file.LOG, 'Error : ' || v_sqlcode);
fnd_file.put_line (fnd_file.LOG, 'Record : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, separatore);
retcode := 1;
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, ' ');
WHEN UTL_FILE.read_error THEN
v_sqlcode := 'UTL_FILE.READ_ERROR';
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, 'Message: ' || v_error_desc);
fnd_file.put_line (fnd_file.LOG, 'Error : ' || v_sqlcode);
fnd_file.put_line (fnd_file.LOG, 'Record : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, separatore);
retcode := 1;
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, ' ');
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, separatore);
fnd_file.put_line (fnd_file.LOG, 'Message: ' || SQLERRM);
fnd_file.put_line (fnd_file.LOG, 'SQLCode: ' || SQLCODE);
fnd_file.put_line (fnd_file.LOG, 'Record : ' || v_rec_cnt);
fnd_file.put_line (fnd_file.LOG, separatore);
retcode := 1;
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, ' ');
END update_dd_status;
END xxeq_inbound_auth_pkg;
/

Supplier Conversion

CREATE OR REPLACE PACKAGE BODY XXCSC_AP_SUPPLIERS_CONV_PKG
AS

/* $Header: XXCSC_AP_SUPPLIERS_CONV_PKG.pkb version date time object_type module $
===========================================================================================

===========================================================================================

History:

Name Version Reason for Change Date
===========================================================================================
Madhu Dhare 1.0 Original 12-JUN-2014

===========================================================================================
*/

---------------------------------------------------------------------------------------------
-- Variable Declaration
---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPPLIERS_MAPPING_PROC
-- DESCRIPTION : Supplier Mapping Activity in Staging Table
---------------------------------------------------------------------------------------------

PROCEDURE XXCSC_AP_SUPP_MAPPING_PROC (x_object_name IN VARCHAR2
,x_stage IN VARCHAR2
)
IS

x_query VARCHAR2(4000);

CURSOR CUR_MAP IS
SELECT table_name
,column_name
,legacy_code
,legacy_description
,target_code
,target_description
FROM XXHR.xxcsc_cmn_mapping_all mt
,XXHR.xxcsc_cmn_metadata_all st
WHERE mt.object_code = st.object_code
AND mt.object_dep_seq = st.object_dep_seq
AND st.object_name = x_object_name
AND mt.enable_flag = x_yes
AND st.enable_flag = x_yes;

BEGIN

XXCSC_AP_SUPPLIERS_CONV_PKG.x_object := x_object_name;

--- Update the PROCESS FLAG AS M
XXCSC_AP_SUPP_STATUS_PROC (x_mapping_flag
,NULL -- x_status
);

FOR rec_map IN cur_map LOOP
x_query := 'UPDATE XXHR.' || rec_map.table_name ||' SET ' ||rec_map.column_name || ' = '''|| rec_map.target_code ||''' , status_flag = ''' ||x_mapped_flag || ''' WHERE EXISTS (SELECT 1 FROM XXHR.xxcsc_cmn_mapping_all b WHERE b.enable_flag = ''Y'' AND b.legacy_code = '|| rec_map.column_name || ' )' ;
EXECUTE IMMEDIATE (x_query);
COMMIT;
END LOOP;

--- Update the STATUS FLAG AS NM
XXCSC_AP_SUPP_STATUS_PROC (NULL
,x_notmapped_flag -- x_status
);

IF x_stage = x_mapping_stg THEN
XXCSC_AP_SUPP_ERROR_PROC (x_object_name
,x_stage
);
END IF;

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC('ERROR : '|| SQLCODE);
XXCSC_AP_SUPP_PRINT_PROC('ERROR DESCRIPTION - ' || SQLERRM);
ROLLBACK;

END XXCSC_AP_SUPP_MAPPING_PROC;

---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPPLIERS_VALIDATION_PROC
-- DESCRIPTION : Supplier Validation Activity in Staging table
---------------------------------------------------------------------------------------------

PROCEDURE XXCSC_AP_SUPP_VALIDATION_PROC (x_object_name IN VARCHAR2
,x_stage IN VARCHAR2
)
IS

-- Local Variable Declaration
x_cnt NUMBER ;
x_query VARCHAR2(4000);
x_select VARCHAR2(2000);
x_str VARCHAR2(100);
x_arg1 VARCHAR2(100);
x_arg2 VARCHAR2(100);
x_arg3 VARCHAR2(100);
x_arg4 VARCHAR2(100);
x_pr_flag VARCHAR2(20);
x_st_flag VARCHAR2(20);
x_val NUMBER;
x_val1 NUMBER;

-- Ref Cursor Declaration
TYPE x_ref_cur IS REF CURSOR;
x_dyn_cur1 x_ref_cur;
x_dyn_cur2 x_ref_cur;

-- Cursor Declaration
CURSOR cur_tbl IS
SELECT DISTINCT
st.table_name
,st.key_column1 key_column1
,NVL(st.key_column2,'1') key_column2
,NVL(st.key_column3,'1') key_column3
,st.object_dep_seq
FROM XXHR.xxcsc_cmn_metadata_all st
WHERE st.object_name = x_object_name
AND st.enable_flag = x_yes
ORDER BY object_dep_seq;

CURSOR cur_valid IS
SELECT st.table_name
,st.key_column1
,NVL(st.key_column2,'1') key_column2
,NVL(st.key_column3, '1') key_column3
,cv1.valid_code
,cv1.valid_message
,cv1.sql_validation
,cv1.valid_type
FROM XXHR.xxcsc_cmn_validations_all cv1
,XXHR.xxcsc_cmn_metadata_all st
WHERE cv1.object_code = st.object_code
AND cv1.object_dep_seq = st.object_dep_seq
AND st.object_name = x_object_name
AND st.enable_flag = x_yes
AND cv1.enable_flag = x_yes
AND cv1.execution_seq <1000
ORDER BY st.object_dep_seq
,cv1.execution_seq;

CURSOR cur_valid1 IS
SELECT st.table_name
,st.key_column1
,NVL(st.key_column2,'1') key_column2
,NVL(st.key_column3, '1') key_column3
,cv1.valid_code
,cv1.valid_message
,cv1.sql_validation
,cv1.valid_type
FROM XXHR.xxcsc_cmn_validations_all cv1
,XXHR.xxcsc_cmn_metadata_all st
WHERE cv1.object_code = st.object_code
AND cv1.object_dep_seq = st.object_dep_seq
AND st.object_name = x_object_name
AND st.enable_flag = x_yes
AND cv1.enable_flag = x_yes
AND cv1.execution_seq >=1000
ORDER BY st.object_dep_seq
,cv1.execution_seq;

BEGIN
XXCSC_AP_SUPPLIERS_CONV_PKG.x_object := x_object_name;

XXCSC_AP_SUPP_STATUS_PROC (x_validate_flag
,NULL -- x_status
);

FOR rec_tbl IN cur_tbl LOOP
x_select := 'SELECT RECORD_ID, '|| rec_tbl.key_column1 ||' ,'||rec_tbl.key_column2 || ' ,'|| rec_tbl.key_column3|| ' FROM XXHR.'|| TRIM(rec_tbl.table_name);
x_select := REPLACE(x_select,'1','DECODE(1,1,'' '')');

OPEN x_dyn_cur1 FOR x_select;
LOOP
FETCH x_dyn_cur1 INTO x_arg1,x_arg2,x_arg3,x_arg4 ;

EXIT WHEN x_dyn_cur1%NOTFOUND;

FOR rec_valid IN cur_valid LOOP
IF (rec_valid.table_name=rec_tbl.table_name) THEN
BEGIN
x_query := rec_valid.sql_validation;
x_str := ' AND MT.RECORD_ID='||x_arg1;
x_query := CONCAT(x_query,x_str);

EXECUTE IMMEDIATE (x_query) INTO x_cnt;

IF x_cnt >0 THEN
x_query := 'UPDATE XXHR.'|| TRIM(rec_valid.TABLE_NAME) ||' SET error_code = error_code ||'','|| rec_valid.VALID_CODE || ''', status_flag = ''' || rec_valid.valid_type || ''' WHERE record_id = '||x_arg1 ;
EXECUTE IMMEDIATE (x_query) ;

COMMIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED: ' || SQLCODE);
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED IN VALIDATION STAGE - ' || SQLERRM);
ROLLBACK;

END;

END IF;
END LOOP;
END LOOP;
END LOOP;

--Orphan Record Validate Start
FOR rec_tbl IN cur_tbl LOOP
x_select := 'SELECT RECORD_ID, '|| rec_tbl.key_column1 ||' ,'||rec_tbl.key_column2 || ' ,'|| rec_tbl.key_column3|| ' FROM XXHR.'|| TRIM(rec_tbl.table_name);
x_select := REPLACE(x_select,'1','DECODE(1,1,'' '')');

OPEN x_dyn_cur1 FOR x_select;
LOOP
FETCH x_dyn_cur1 INTO x_arg1,x_arg2,x_arg3,x_arg4 ;

EXIT WHEN x_dyn_cur1%NOTFOUND;

FOR rec_valid IN cur_valid1 LOOP
IF (rec_valid.table_name=rec_tbl.table_name) THEN
BEGIN
x_query := rec_valid.sql_validation;
x_str := ' AND MT.RECORD_ID='||x_arg1;
x_query := CONCAT(x_query,x_str);

EXECUTE IMMEDIATE (x_query) INTO x_cnt;

IF x_cnt >0 THEN
x_query := 'UPDATE XXHR.'|| TRIM(rec_valid.TABLE_NAME) ||' SET error_code = error_code ||'','|| rec_valid.VALID_CODE || ''', status_flag = ''' || rec_valid.valid_type || ''' WHERE record_id = '||x_arg1 ;
EXECUTE IMMEDIATE (x_query) ;

COMMIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED: ' || SQLCODE);
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED IN VALIDATION STAGE - ' || SQLERRM);
ROLLBACK;

END;

END IF;
END LOOP;
END LOOP;
END LOOP;
--Orphan Record Validate End

--- Update the STATUS FLAG AS V
XXCSC_AP_SUPP_STATUS_PROC (NULL
,x_validate_flag -- x_status
);

IF x_stage = x_validate_stg THEN
XXCSC_AP_SUPP_ERROR_PROC (x_object_name
,x_stage
);
END IF;

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED: ' || SQLCODE);
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED IN VALIDATION STAGE - ' || SQLERRM);
ROLLBACK;

END XXCSC_AP_SUPP_VALIDATION_PROC;

---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPPLIERS_TRANSFER_PROC
-- DESCRIPTION : Supplier Data Transfer from Staging Table into Interface Table
---------------------------------------------------------------------------------------------

PROCEDURE XXCSC_AP_SUPP_TRANSFER_PROC (x_object_name IN VARCHAR2
,x_stage IN VARCHAR2
)
IS
x_pr_flag VARCHAR2(20);
x_st_flag VARCHAR2(20);

BEGIN
XXCSC_AP_SUPPLIERS_CONV_PKG.x_object := x_object_name;

FOR rec_hr IN cur_hr LOOP
UPDATE XXHR.xxcsc_ap_supplier_headers
SET process_flag = x_transfer_flag;

COMMIT;
END LOOP;

--- Update the PROCESS FLAG
FOR rec_st IN cur_st LOOP
UPDATE XXHR.xxcsc_ap_supplier_sites
SET process_flag = x_transfer_flag;

COMMIT;
END LOOP;

--- Update the PROCESS FLAG
FOR rec_cn IN cur_cn LOOP
UPDATE XXHR.xxcsc_ap_supplier_contacts
SET process_flag = x_transfer_flag;

COMMIT;
END LOOP;

-- Purge Supplier Interface Table

DELETE FROM APPS.ap_suppliers_int;
DELETE FROM APPS.ap_supplier_sites_int;
DELETE FROM APPS.ap_sup_site_contact_int;
COMMIT;

FOR rec_hdr IN cur_hdr (x_validate_flag) LOOP
BEGIN
-- SUPPLIER HEADER INSERT INTO INTERFACE TABLE
INSERT INTO APPS.ap_suppliers_int
(
vendor_interface_id
,last_update_date
,last_updated_by
,vendor_name
,vendor_name_alt
,segment1
,summary_flag
,enabled_flag
,last_update_login
,creation_date
,created_by
,employee_id
,vendor_type_lookup_code
,customer_num
,one_time_flag
,min_order_amount
,ship_to_location_id
,ship_to_location_code
,bill_to_location_id
,bill_to_location_code
,ship_via_lookup_code
,freight_terms_lookup_code
,fob_lookup_code
,terms_id
,terms_name
,set_of_books_id
,always_take_disc_flag
,pay_date_basis_lookup_code
,pay_group_lookup_code
,payment_priority
,invoice_currency_code
,payment_currency_code
,invoice_amount_limit
,hold_all_payments_flag
,hold_future_payments_flag
,hold_reason
,distribution_set_id
,distribution_set_name
,accts_pay_code_combination_id
,prepay_code_combination_id
,num_1099
,type_1099
,organization_type_lookup_code
,vat_code
,start_date_active
,end_date_active
,minority_group_lookup_code
,payment_method_lookup_code
,women_owned_flag
,small_business_flag
,standard_industry_class
,hold_flag
,purchasing_hold_reason
,hold_by
,hold_date
,terms_date_basis
,inspection_required_flag
,receipt_required_flag
,qty_rcv_tolerance
,qty_rcv_exception_code
,enforce_ship_to_location_code
,days_early_receipt_allowed
,days_late_receipt_allowed
,receipt_days_exception_code
,receiving_routing_id
,allow_substitute_receipts_flag
,allow_unordered_receipts_flag
,hold_unmatched_invoices_flag
,exclusive_payment_flag
,ap_tax_rounding_rule
,auto_tax_calc_flag
,auto_tax_calc_override
,amount_includes_tax_flag
,tax_verification_date
,name_control
,state_reportable_flag
,federal_reportable_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,request_id
,program_application_id
,program_id
,program_update_date
,vat_registration_num
,auto_calculate_interest_flag
,exclude_freight_from_discount
,tax_reporting_name
,allow_awt_flag
,awt_group_id
,awt_group_name
,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
,global_attribute_category
,edi_transaction_handling
,edi_payment_method
,edi_payment_format
,edi_remittance_method
,edi_remittance_instruction
,bank_charge_bearer
,match_option
,future_dated_payment_ccid
,create_debit_memo_flag
,offset_tax_flag
,import_request_id
,status
,reject_code
,ece_tp_location_code
,iby_bank_charge_bearer
,bank_instruction1_code
,bank_instruction2_code
,bank_instruction_details
,payment_reason_code
,payment_reason_comments
,payment_text_message1
,payment_method_code
)
VALUES
(
rec_hdr.record_id
,SYSDATE --last_update_date
,APPS.FND_GLOBAL.USER_ID --last_updated_by
,rec_hdr.vendor_name
,rec_hdr.vendor_name_alt
,rec_hdr.vendor_number
,rec_hdr.summary_flag
,rec_hdr.enabled_flag
,APPS.FND_GLOBAL.USER_ID --last_update_login
,SYSDATE --creation_date
,APPS.FND_GLOBAL.USER_ID --created_by
,rec_hdr.employee_id
,rec_hdr.vendor_type_lookup_code
,rec_hdr.customer_num
,rec_hdr.one_time_flag
,rec_hdr.min_order_amount
,rec_hdr.ship_to_location_id
,rec_hdr.ship_to_location_code
,rec_hdr.bill_to_location_id
,rec_hdr.bill_to_location_code
,rec_hdr.ship_via_lookup_code
,rec_hdr.freight_terms_lookup_code
,rec_hdr.fob_lookup_code
,rec_hdr.terms_id
,rec_hdr.terms_name
,rec_hdr.set_of_books_id
,rec_hdr.always_take_disc_flag
,rec_hdr.pay_date_basis_lookup_code
,rec_hdr.pay_group_lookup_code
,rec_hdr.payment_priority
,rec_hdr.invoice_currency_code
,rec_hdr.payment_currency_code
,rec_hdr.invoice_amount_limit
,rec_hdr.hold_all_payments_flag
,rec_hdr.hold_future_payments_flag
,rec_hdr.hold_reason
,rec_hdr.distribution_set_id
,rec_hdr.distribution_set_name
,rec_hdr.accts_pay_code_combination_id
,rec_hdr.prepay_code_combination_id
,rec_hdr.num_1099
,rec_hdr.type_1099
,rec_hdr.organization_type_lookup_code
,rec_hdr.vat_code
,rec_hdr.start_date_active
,rec_hdr.end_date_active
,rec_hdr.minority_group_lookup_code
,rec_hdr.payment_method_lookup_code
,rec_hdr.women_owned_flag
,rec_hdr.small_business_flag
,rec_hdr.standard_industry_class
,rec_hdr.hold_flag
,rec_hdr.purchasing_hold_reason
,rec_hdr.hold_by
,rec_hdr.hold_date
,rec_hdr.terms_date_basis
,rec_hdr.inspection_required_flag
,rec_hdr.receipt_required_flag
,rec_hdr.qty_rcv_tolerance
,rec_hdr.qty_rcv_exception_code
,rec_hdr.enforce_ship_to_location_code
,rec_hdr.days_early_receipt_allowed
,rec_hdr.days_late_receipt_allowed
,rec_hdr.receipt_days_exception_code
,rec_hdr.receiving_routing_id
,rec_hdr.allow_substitute_receipts_flag
,rec_hdr.allow_unordered_receipts_flag
,rec_hdr.hold_unmatched_invoices_flag
,rec_hdr.exclusive_payment_flag
,rec_hdr.ap_tax_rounding_rule
,rec_hdr.auto_tax_calc_flag
,rec_hdr.auto_tax_calc_override
,rec_hdr.amount_includes_tax_flag
,rec_hdr.tax_verification_date
,rec_hdr.name_control
,rec_hdr.state_reportable_flag
,rec_hdr.federal_reportable_flag
,rec_hdr.attribute_category
,rec_hdr.attribute1
,rec_hdr.attribute2
,rec_hdr.attribute3
,rec_hdr.attribute4
,rec_hdr.attribute5
,rec_hdr.attribute6
,rec_hdr.attribute7
,rec_hdr.attribute8
,rec_hdr.attribute9
,rec_hdr.attribute10
,rec_hdr.attribute11
,rec_hdr.attribute12
,rec_hdr.attribute13
,rec_hdr.attribute14
,rec_hdr.attribute15
,APPS.FND_GLOBAL.CONC_REQUEST_ID -- request_id
,NULL -- program_application_id
,NULL -- program_id
,NULL -- program_update_date
,rec_hdr.vat_registration_num
,rec_hdr.auto_calculate_interest_flag
,rec_hdr.exclude_freight_from_discount
,rec_hdr.tax_reporting_name
,rec_hdr.allow_awt_flag
,rec_hdr.awt_group_id
,rec_hdr.awt_group_name
,rec_hdr.global_attribute1
,rec_hdr.global_attribute2
,rec_hdr.global_attribute3
,rec_hdr.global_attribute4
,rec_hdr.global_attribute5
,rec_hdr.global_attribute6
,rec_hdr.global_attribute7
,rec_hdr.global_attribute8
,rec_hdr.global_attribute9
,rec_hdr.global_attribute10
,rec_hdr.global_attribute11
,rec_hdr.global_attribute12
,rec_hdr.global_attribute13
,rec_hdr.global_attribute14
,rec_hdr.global_attribute15
,rec_hdr.global_attribute16
,rec_hdr.global_attribute17
,rec_hdr.global_attribute18
,rec_hdr.global_attribute19
,rec_hdr.global_attribute20
,rec_hdr.global_attribute_category
,rec_hdr.edi_transaction_handling
,rec_hdr.edi_payment_method
,rec_hdr.edi_payment_format
,rec_hdr.edi_remittance_method
,rec_hdr.edi_remittance_instruction
,rec_hdr.bank_charge_bearer
,rec_hdr.match_option
,rec_hdr.future_dated_payment_ccid
,rec_hdr.create_debit_memo_flag
,rec_hdr.offset_tax_flag
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL);

UPDATE XXHR.xxcsc_ap_supplier_headers hdr
SET hdr.status_flag = x_transfered_flag
WHERE hdr.vendor_number = rec_hdr.vendor_number
AND hdr.record_id = rec_hdr.record_id;

FOR rec_sit IN cur_sit (x_validate_flag,rec_hdr.vendor_number) LOOP
BEGIN

INSERT INTO APPS.ap_supplier_sites_int
(
vendor_interface_id
,last_update_date
,last_updated_by
,vendor_id
,vendor_site_code
,vendor_site_code_alt
,last_update_login
,creation_date
,created_by
,purchasing_site_flag
,rfq_only_site_flag
,pay_site_flag
,attention_ar_flag
,address_line1
,address_lines_alt
,address_line2
,address_line3
,city
,state
,zip
,province
,country
,area_code
,phone
,customer_num
,ship_to_location_id
,ship_to_location_code
,bill_to_location_id
,bill_to_location_code
,ship_via_lookup_code
,freight_terms_lookup_code
,fob_lookup_code
,inactive_date
,fax
,fax_area_code
,telex
,payment_method_lookup_code
,terms_date_basis
,vat_code
,distribution_set_id
,distribution_set_name
,accts_pay_code_combination_id
,prepay_code_combination_id
,pay_group_lookup_code
,payment_priority
,terms_id
,terms_name
,invoice_amount_limit
,pay_date_basis_lookup_code
,always_take_disc_flag
,invoice_currency_code
,payment_currency_code
,hold_all_payments_flag
,hold_future_payments_flag
,hold_reason
,hold_unmatched_invoices_flag
,ap_tax_rounding_rule
,auto_tax_calc_flag
,auto_tax_calc_override
,amount_includes_tax_flag
,exclusive_payment_flag
,tax_reporting_site_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,request_id
,program_application_id
,program_id
,program_update_date
,exclude_freight_from_discount
,vat_registration_num
,org_id
,operating_unit_name
,address_line4
,county
,address_style
,language
,allow_awt_flag
,awt_group_id
,awt_group_name
,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
,global_attribute_category
,edi_transaction_handling
,edi_id_number
,edi_payment_method
,edi_payment_format
,edi_remittance_method
,bank_charge_bearer
,edi_remittance_instruction
,pay_on_code
,default_pay_site_id
,pay_on_receipt_summary_code
,tp_header_id
,ece_tp_location_code
,pcard_site_flag
,match_option
,country_of_origin_code
,future_dated_payment_ccid
,create_debit_memo_flag
,offset_tax_flag
,supplier_notif_method
,email_address
,remittance_email
,primary_pay_site_flag
,import_request_id
,status
,reject_code
,shipping_control
,duns_number
,tolerance_id
,tolerance_name
,iby_bank_charge_bearer
,bank_instruction1_code
,bank_instruction2_code
,bank_instruction_details
,payment_reason_code
,payment_reason_comments
,delivery_channel_code
,payment_format_code
,settlement_priority
,payment_text_message1
,payment_text_message2
,payment_text_message3
,vendor_site_interface_id
,payment_method_code
,retainage_rate
,gapless_inv_num_flag
,selling_company_identifier
)
VALUES
(
rec_hdr.record_id
,SYSDATE -- last_update_date
,APPS.FND_GLOBAL.USER_ID --last_updated_by
,NULL -- rec_sit.vendor_id
,rec_sit.vendor_site_code
,rec_sit.vendor_site_code_alt
,APPS.FND_GLOBAL.USER_ID -- last_update_login
,SYSDATE -- creation_date
,APPS.FND_GLOBAL.USER_ID -- created_by
,rec_sit.purchasing_site_flag
,rec_sit.rfq_only_site_flag
,rec_sit.pay_site_flag
,rec_sit.attention_ar_flag
,rec_sit.address_line1
,rec_sit.address_lines_alt
,rec_sit.address_line2
,rec_sit.address_line3
,rec_sit.city
,rec_sit.state
,rec_sit.zip
,rec_sit.province
,rec_sit.country
,rec_sit.area_code
,rec_sit.phone
,rec_sit.customer_num
,rec_sit.ship_to_location_id
,rec_sit.ship_to_location_code
,rec_sit.bill_to_location_id
,rec_sit.bill_to_location_code
,rec_sit.ship_via_lookup_code
,rec_sit.freight_terms_lookup_code
,rec_sit.fob_lookup_code
,rec_sit.inactive_date
,rec_sit.fax
,rec_sit.fax_area_code
,rec_sit.telex
,rec_sit.payment_method_lookup_code
,rec_sit.terms_date_basis
,rec_sit.vat_code
,rec_sit.distribution_set_id
,rec_sit.distribution_set_name
,rec_sit.accts_pay_code_combination_id
,rec_sit.prepay_code_combination_id
,rec_sit.pay_group_lookup_code
,rec_sit.payment_priority
,rec_sit.terms_id
,rec_sit.terms_name
,rec_sit.invoice_amount_limit
,rec_sit.pay_date_basis_lookup_code
,rec_sit.always_take_disc_flag
,rec_sit.invoice_currency_code
,rec_sit.payment_currency_code
,rec_sit.hold_all_payments_flag
,rec_sit.hold_future_payments_flag
,rec_sit.hold_reason
,rec_sit.hold_unmatched_invoices_flag
,rec_sit.ap_tax_rounding_rule
,rec_sit.auto_tax_calc_flag
,rec_sit.auto_tax_calc_override
,rec_sit.amount_includes_tax_flag
,rec_sit.exclusive_payment_flag
,rec_sit.tax_reporting_site_flag
,rec_sit.attribute_category
,rec_sit.attribute1
,rec_sit.attribute2
,rec_sit.attribute3
,rec_sit.attribute4
,rec_sit.attribute5
,rec_sit.attribute6
,rec_sit.attribute7
,rec_sit.attribute8
,rec_sit.attribute9
,rec_sit.attribute10
,rec_sit.attribute11
,rec_sit.attribute12
,rec_sit.attribute13
,rec_sit.attribute14
,rec_sit.attribute15
,rec_sit.request_id
,NULL -- program_application_id
,NULL -- program_id
,NULL -- program_update_date
,rec_sit.exclude_freight_from_discount
,rec_sit.vat_registration_num
,rec_sit.org_id
,rec_sit.org_name
,rec_sit.address_line4
,rec_sit.county
,rec_sit.address_style
,rec_sit.language
,rec_sit.allow_awt_flag
,rec_sit.awt_group_id
,rec_sit.awt_group_name
,rec_sit.global_attribute1
,rec_sit.global_attribute2
,rec_sit.global_attribute3
,rec_sit.global_attribute4
,rec_sit.global_attribute5
,rec_sit.global_attribute6
,rec_sit.global_attribute7
,rec_sit.global_attribute8
,rec_sit.global_attribute9
,rec_sit.global_attribute10
,rec_sit.global_attribute11
,rec_sit.global_attribute12
,rec_sit.global_attribute13
,rec_sit.global_attribute14
,rec_sit.global_attribute15
,rec_sit.global_attribute16
,rec_sit.global_attribute17
,rec_sit.global_attribute18
,rec_sit.global_attribute19
,rec_sit.global_attribute20
,rec_sit.global_attribute_category
,rec_sit.edi_transaction_handling
,rec_sit.edi_id_number
,rec_sit.edi_payment_method
,rec_sit.edi_payment_format
,rec_sit.edi_remittance_method
,rec_sit.bank_charge_bearer
,rec_sit.edi_remittance_instruction
,rec_sit.pay_on_code
,rec_sit.default_pay_site_id
,rec_sit.pay_on_receipt_summary_code
,rec_sit.tp_header_id
,rec_sit.ece_tp_location_code
,rec_sit.pcard_site_flag
,rec_sit.match_option
,rec_sit.country_of_origin_code
,rec_sit.future_dated_payment_ccid
,rec_sit.create_debit_memo_flag
,rec_sit.offset_tax_flag
,rec_sit.supplier_notif_method
,rec_sit.email_address
,rec_sit.remittance_email
,rec_sit.primary_pay_site_flag
,NULL -- import_request_id
,NULL -- status
,NULL -- reject_code
,rec_sit.shipping_control
,rec_sit.duns_number
,rec_sit.tolerance_id
,rec_sit.tolerance_name
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,rec_sit.record_id
,NULL
,rec_sit.retainage_rate
,rec_sit.gapless_inv_num_flag
,rec_sit.selling_company_identifier
);

UPDATE XXHR.xxcsc_ap_supplier_sites sit
SET sit.status_flag = x_transfered_flag
WHERE sit.vendor_number = rec_hdr.vendor_number
AND sit.vendor_site_code = rec_sit.vendor_site_code
AND sit.record_id = rec_sit.record_id;

FOR rec_con IN cur_con (x_validate_flag,rec_hdr.vendor_number,rec_sit.vendor_site_code) LOOP
BEGIN
INSERT INTO APPS.ap_sup_site_contact_int
(
last_update_date
,last_updated_by
,vendor_site_id
,vendor_site_code
,org_id
,operating_unit_name
,last_update_login
,creation_date
,created_by
,inactive_date
,first_name
,middle_name
,last_name
,prefix
,title
,mail_stop
,area_code
,phone
,program_application_id
,program_id
,program_update_date
,request_id
,contact_name_alt
,first_name_alt
,last_name_alt
,department
,import_request_id
,status
,reject_code
,email_address
,url
,alt_area_code
,alt_phone
,fax_area_code
,fax
,vendor_interface_id
,vendor_id
,vendor_contact_interface_id
)
VALUES
(
SYSDATE -- last_update_date
,APPS.FND_GLOBAL.USER_ID -- last_updated_by
,rec_sit.vendor_site_id
,rec_con.vendor_site_code
,rec_con.org_id -- org_id
,rec_con.org_name -- operating_unit_name
,APPS.FND_GLOBAL.USER_ID -- last_update_login
,SYSDATE -- creation_date
,APPS.FND_GLOBAL.USER_ID -- created_by
,rec_con.inactive_date
,rec_con.first_name
,rec_con.middle_name
,rec_con.last_name
,rec_con.prefix
,rec_con.title
,rec_con.mail_stop
,rec_con.area_code
,rec_con.phone
,NULL -- program_application_id
,NULL -- program_id
,NULL -- program_update_date
,rec_con.request_id
,rec_con.contact_name_alt
,rec_con.first_name_alt
,rec_con.last_name_alt
,rec_con.department
,NULL -- import_request_id
,NULL -- status
,NULL -- reject_code
,rec_con.email_address
,rec_con.url
,rec_con.alt_area_code
,rec_con.alt_phone
,rec_con.fax_area_code
,rec_con.fax
,rec_hdr.record_id
,NULL -- rec_hdr.record_id
,rec_con.record_id
);

UPDATE XXHR.xxcsc_ap_supplier_contacts con
SET con.status_flag = x_transfered_flag
WHERE con.vendor_number = rec_hdr.vendor_number
AND con.vendor_site_code = rec_sit.vendor_site_code
AND con.record_id = rec_con.record_id;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC ('Contact Error: Error occured while SUPPLIER CONATCS data transfer from Staging table into Interface Table ');
ROLLBACK;
END;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC ('Site Error: Error occured while SUPPLIER SITES data transfer from Staging table into Interface Table ');
ROLLBACK;
END;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC ('Header Error: Error occured while SUPPLIER HEADERS data transfer from Staging table into Interface Table ');
ROLLBACK;
END;
END LOOP;

XXCSC_AP_SUPP_STATUS_PROC (NULL
,x_nottransfer_flag -- x_status
);

IF x_stage IN (x_transfer_stg, x_all_stg) THEN
XXCSC_AP_SUPP_ERROR_PROC (x_object_name
,x_stage
);
END IF;

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR OCCURED : '|| SQLCODE );
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR DESCRIPTION - ' || SQLERRM );
ROLLBACK;

END XXCSC_AP_SUPP_TRANSFER_PROC;

---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPP_ERROR_PROC
-- DESCRIPTION : Error Report Generation
---------------------------------------------------------------------------------------------

PROCEDURE XXCSC_AP_SUPP_ERROR_PROC (x_object_name IN VARCHAR2
,x_stage IN VARCHAR2
)
IS
-- Variable Declaration
x_title VARCHAR2(100);
x_status VARCHAR2(100);
x_req_id NUMBER;
x_req_id_1 NUMBER;
x_start_dt VARCHAR2(100);
x_end_dt VARCHAR2(100);
x_exec_time VARCHAR2(100);
x_conc_prog_name VARCHAR2(100);
x_param1 VARCHAR2(100);
x_param2 VARCHAR2(100);
x_param3 VARCHAR2(100);
x_sqlqry VARCHAR2(2000);
x_sqlqry1 VARCHAR2(2000);
x_err_desc VARCHAR2(2000);
x_cnt NUMBER := 0;
x_key2 NUMBER;
x_key3 NUMBER;
x_arg1 VARCHAR2(300);
x_arg2 VARCHAR2(300);
x_arg3 VARCHAR2(300);
x_arg4 VARCHAR2(300);
x_arg5 VARCHAR2(300);
x_flag VARCHAR2(1):= 'Y';
x_str VARCHAR2(200):=' ';

-- Ref Cursor Delaration

TYPE x_ref_cur IS ref cursor;
x_dyn_cur x_ref_cur;
x_dyn_cur1 x_ref_cur;

CURSOR cur_tbl IS
SELECT st.table_name
,st.table_description
FROM XXHR.xxcsc_cmn_metadata_all st
WHERE st.object_name = TRIM(x_object_name)
AND st.enable_flag = x_yes
ORDER BY st.object_dep_seq;

CURSOR cur_det IS
SELECT st.table_name
,st.table_description
,st.key_column1
,NVL(st.key_column2,1) AS key_column2
,NVL(st.key_column3,1) AS key_column3
FROM XXHR.xxcsc_cmn_metadata_all st
WHERE st.object_name = TRIM(x_object_name)
AND st.enable_flag = x_yes
ORDER BY st.object_dep_seq;

BEGIN
XXCSC_AP_SUPPLIERS_CONV_PKG.x_object := x_object_name;

SELECT MAX(REQUEST_ID)
INTO x_req_id_1
FROM APPS.FND_CONCURRENT_REQUESTS CR
,APPS.FND_CONCURRENT_PROGRAMS_TL B
WHERE CR.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND ARGUMENT1 = x_object_name;

SELECT
' CONVERSION SUMMARY DETAILS' AS Title
,DECODE(mt.status_code,'C','COMPLETED','E','ERROR') AS status_code
,mt.request_id AS request_id
,TO_CHAR(mt.actual_start_date,'DD-MON-YYYY HH:MI:SS') AS actual_start_date
,TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS') AS actual_completion_date
,FLOOR(((SYSDATE - mt.actual_start_date)*24*60*60)/3600)|| ' Hours ' ||
FLOOR((((SYSDATE - mt.actual_start_date)*24*60*60) -
FLOOR(((SYSDATE - mt.actual_start_date)*24*60*60)/3600)*3600)/60)|| ' Minutes ' ||
ROUND((((SYSDATE - mt.actual_start_date)*24*60*60) -
FLOOR(((SYSDATE - mt.actual_start_date)*24*60*60)/3600)*3600 -
(FLOOR((((SYSDATE - mt.actual_start_date)*24*60*60) -
FLOOR(((SYSDATE - mt.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) || ' Secs '
AS Duration
,b.user_concurrent_program_name AS user_concurrent_program_name
,mt.argument1 AS argument1
,mt.argument2 AS argument2
,mt.argument3 AS argument3
INTO x_title
,x_status
,x_req_id
,x_start_dt
,x_end_dt
,x_exec_time
,x_conc_prog_name
,x_param1
,x_param2
,x_param3
FROM APPS.fnd_concurrent_requests mt
,APPS.fnd_concurrent_programs a
,APPS.fnd_concurrent_programs_TL b
WHERE a.concurrent_program_id = b.concurrent_program_id
AND mt.concurrent_program_id = a.concurrent_program_id
AND mt.concurrent_program_id = b.concurrent_program_id
AND mt.argument1 = TRIM(x_object_name)
AND status_code != 'E'
AND ROWNUM < 2
AND mt. request_id = x_req_id_1
ORDER BY request_date DESC;

-- Print Heading

XXCSC_AP_SUPP_PRINT_PROC ( ' ');
XXCSC_AP_SUPP_PRINT_PROC ( ' ');
XXCSC_AP_SUPP_PRINT_PROC ('**************************************************************************');
XXCSC_AP_SUPP_PRINT_PROC ( UPPER(x_object_name) ||' CONVERSION SUMMARY AND ERROR REPORT ');
XXCSC_AP_SUPP_PRINT_PROC ('**************************************************************************');
XXCSC_AP_SUPP_PRINT_PROC ( ' ');
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Object Name',30,' ') || ':'||x_object_name);
--S XXCSC_AP_SUPP_PRINT_PROC (RPAD('Load Status',30,' ') || ':'||x_status);
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Concurrent Request ID',30,' ') || ':'||x_req_id );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Program Start Date',30,' ') || ':'||x_start_dt );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Program End Date',30,' ') || ':'||x_end_dt );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Execution time',30,' ') || ':'||x_exec_time );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Concurrent Program Name',30,' ') || ':'||x_conc_prog_name );
XXCSC_AP_SUPP_PRINT_PROC ('Parameters:' );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Object Name',30,' ') || ':'||x_param1 );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Stage',30,' ') || ':'||x_param2 );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Forcefully Submit',30,' ') || ':'||x_param3 );
XXCSC_AP_SUPP_PRINT_PROC (' ' );
XXCSC_AP_SUPP_PRINT_PROC ('**************************************************************************' );
XXCSC_AP_SUPP_PRINT_PROC (' ' );

-- Summary Report

FOR rec_tbl IN cur_tbl LOOP
XXCSC_AP_SUPP_PRINT_PROC(' ' );
XXCSC_AP_SUPP_PRINT_PROC( RPAD(rec_tbl.table_description ||' Conversion Summary Details',50,' ') );
XXCSC_AP_SUPP_PRINT_PROC( '**********************************************' );
XXCSC_AP_SUPP_PRINT_PROC(' ' );

x_sqlqry := 'SELECT COUNT(1) FROM XXHR.'||rec_tbl.table_name;
EXECUTE IMMEDIATE x_sqlqry INTO x_cnt;

XXCSC_AP_SUPP_PRINT_PROC( RPAD('Total Records in '|| rec_tbl.table_description ,50,' ') || ': ' ||x_cnt );

IF x_stage IN (x_mapping_stg, x_all_stg) THEN
x_sqlqry1 := x_sqlqry || ' WHERE status_flag = ''' || x_mapped_flag||'''';
EXECUTE IMMEDIATE x_sqlqry1 INTO x_cnt;
XXCSC_AP_SUPP_PRINT_PROC( RPAD('Total Records Mapped in '|| rec_tbl.table_description ,50,' ') || ': ' ||x_cnt );
END IF;

x_sqlqry1 := ' ';

IF x_stage IN (x_validate_stg, x_all_stg) THEN
x_sqlqry1 := x_sqlqry || ' WHERE (status_flag IN ('''|| x_error_flag ||''' , '''|| x_warning_flag || ''') OR error_code IS NOT NULL)';
EXECUTE IMMEDIATE x_sqlqry1 INTO x_cnt;
XXCSC_AP_SUPP_PRINT_PROC( RPAD('Total Records Error in '|| rec_tbl.table_description ,50,' ') || ': ' ||x_cnt );
END IF;

x_sqlqry1 := ' ';

IF x_stage IN (x_transfer_stg, x_all_stg) THEN
x_sqlqry1 := x_sqlqry || ' WHERE status_flag = '''|| x_transfered_flag||'''';
EXECUTE IMMEDIATE x_sqlqry1 INTO x_cnt;
XXCSC_AP_SUPP_PRINT_PROC( RPAD('Total Records Transfered in '|| rec_tbl.table_description ,50,' ') || ': ' ||x_cnt );
END IF;

END LOOP;

IF x_stage IN (x_all_stg, x_validate_stg) THEN

XXCSC_AP_SUPP_PRINT_PROC( ' ' );
XXCSC_AP_SUPP_PRINT_PROC( ' ' );
XXCSC_AP_SUPP_PRINT_PROC( 'SUMMARY ERROR DETAILS' );
XXCSC_AP_SUPP_PRINT_PROC( '*********************' );
XXCSC_AP_SUPP_PRINT_PROC( ' ' );

FOR REC_DET IN CUR_DET LOOP
x_sqlqry1 := 'SELECT count(mt.record_id),st.valid_code,st.valid_message
FROM XXHR.' ||REC_DET.TABLE_NAME || ' MT
,XXHR.xxcsc_cmn_validations_all st
WHERE INSTR(mt.error_code,st.valid_code)>0
AND mt.error_code IS NOT NULL
GROUP BY st.valid_code,st.valid_message
ORDER BY COUNT(valid_code) DESC';

XXCSC_AP_SUPP_PRINT_PROC(' ');
XXCSC_AP_SUPP_PRINT_PROC('Summary of Error for '||REC_DET.TABLE_DESCRIPTION );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('*',50,'*') );

x_flag:='Y';
OPEN x_dyn_cur1 for x_sqlqry1;
LOOP
FETCH x_dyn_cur1 INTO x_arg1,x_arg2,x_arg3 ;
EXIT WHEN x_dyn_cur1%NOTFOUND;

IF (x_flag='Y') THEN
x_flag:='N';
XXCSC_AP_SUPP_PRINT_PROC(RPAD('COUNT',10,' ')||RPAD('ERROR_CODE',30,' ')||RPAD('ERROR_MESSAGE',100,' ') );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('-',10,'-')||RPAD('-',30,'-')||RPAD('-',100,'-') );
END IF;

XXCSC_AP_SUPP_PRINT_PROC(RPAD(x_arg1,10,' ')||RPAD(x_arg2,30,' ')||RPAD(x_arg3,100,' ') );

END LOOP;
CLOSE x_dyn_cur1;
END LOOP;

-- Detail Report
XXCSC_AP_SUPP_PRINT_PROC (' ' );
XXCSC_AP_SUPP_PRINT_PROC (' ' );
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR WITH DETAILS' );
XXCSC_AP_SUPP_PRINT_PROC( '******************' );
XXCSC_AP_SUPP_PRINT_PROC (' ' );

FOR REC_DET IN CUR_DET LOOP
x_sqlqry1 := 'SELECT MT.'||rec_det.key_column1 ||'
,' ||rec_det.key_column2 ||'
,' ||rec_det.key_column3 ||'
,st.valid_code
,' || 'st.valid_message
FROM XXHR.' ||rec_det.table_name || ' mt
,XXHR.xxcsc_cmn_validations_all st
WHERE INSTR(mt.error_code,st.valid_code)>0
AND mt.error_code IS NOT NULL';

IF TRIM(rec_det.key_column2) = '1' THEN
x_key2 := 0;
ELSE
x_key2 := 20;
END IF;

IF TRIM(rec_det.key_column3) = '1' THEN
x_key3 := 0;
ELSE
x_key3 := 20;
END IF;

XXCSC_AP_SUPP_PRINT_PROC( ' ' );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('Error Details For '||rec_det.table_description,50,' ') );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('*',50,'*') );
XXCSC_AP_SUPP_PRINT_PROC( ' ' );
XXCSC_AP_SUPP_PRINT_PROC(RPAD(REPLACE(TO_CHAR(rec_det.key_column1),'1',' '),20,' ')||RPAD(REPLACE(TO_CHAR(rec_det.key_column2),'1',' '),x_key2,' ')||RPAD(REPLACE(TO_CHAR(rec_det.key_column3),'1',' '),x_key3,' ')||RPAD('VALIDATION CODE ',25,' ')||RPAD('VALIDATION DESCRIPTION',75,' ') );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('-',20,'-')||RPAD('-',20,'-')||RPAD('-',20,'-')||RPAD('-',25,'-')||RPAD('-',75,'-') );

x_sqlqry1 := REPLACE(x_sqlqry1,'1','DECODE(1,1,NULL)' );

x_flag:= 'Y';

OPEN x_dyn_cur FOR x_sqlqry1;
LOOP
FETCH x_dyn_cur INTO x_arg1,x_arg2,x_arg3,x_arg4,x_arg5 ;

EXIT WHEN x_dyn_cur%NOTFOUND;

XXCSC_AP_SUPP_PRINT_PROC(RPAD(x_arg1,20,' ')||RPAD(x_arg2,x_key2,' ')||RPAD(x_arg3,x_key3,' ')||RPAD(x_arg4,25,' ')||RPAD(x_arg5,75,' ') );
END LOOP;

CLOSE x_dyn_cur;
END LOOP;
END IF;

XXCSC_AP_SUPP_PRINT_PROC (' ');
XXCSC_AP_SUPP_PRINT_PROC(RPAD('*',140,'*') );
XXCSC_AP_SUPP_PRINT_PROC (RPAD(' ',66,' ')||'END OF REPORT' || RPAD(' ',66,' '));
XXCSC_AP_SUPP_PRINT_PROC(RPAD('*',140,'*') );

EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR CODE : ' || SQLCODE );
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR DESCRIPTION : ' || SQLERRM );
ROLLBACK;

END XXCSC_AP_SUPP_ERROR_PROC;

---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPP_PRINT_PROC
-- DESCRIPTION : Error Report Design
---------------------------------------------------------------------------------------------

PROCEDURE XXCSC_AP_SUPP_PRINT_PROC (x_print IN VARCHAR2
)
IS
file_handle UTL_FILE.FILE_TYPE;
x_directory VARCHAR2(50) := '/usr/tmp';
x_reid NUMBER ;
x_file_name VARCHAR2(100);
x_flag VARCHAR2(1);
x_accept VARCHAR2(1);

BEGIN

x_file_name := 'XXCSC_AP_SUPPLIER_ERROR.dat';

IF TRIM(XXCSC_AP_SUPPLIERS_CONV_PKG.x_flag) = 'Y' THEN
x_accept := 'W';
XXCSC_AP_SUPPLIERS_CONV_PKG.x_flag := 'N';
ELSE
x_accept := 'A' ;
END IF;

file_handle := UTL_FILE.FOPEN(x_directory, x_file_name ,x_accept);

UTL_FILE.PUT_LINE(file_handle,x_print);

UTL_FILE.FCLOSE(file_handle);

EXCEPTION
WHEN Utl_File.INVALID_OPERATION THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The file could not be opened or operated on as requested. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The file could not be opened or operated on as requested. ');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.INVALID_PATH THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'File location or filename was invalid.');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'File location or filename was invalid.');
utl_file.fclose_all;
ROLLBACK;
WHEN Utl_File.INVALID_MODE THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The open_mode parameter in FOPEN was invalid. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The open_mode parameter in FOPEN was invalid. ');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.INVALID_FILEHANDLE THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The file handle was invalid. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The file handle was invalid. ');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.READ_ERROR THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An operating system error occurred during the read operation.');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An operating system error occurred during the read operation.');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.WRITE_ERROR THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An operating system error occurred during the write operation. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An operating system error occurred during the write operation. ');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.INTERNAL_ERROR THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An unspecified error in PL/SQL. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An unspecified error in PL/SQL. ');
utl_file.fclose_all;
ROLLBACK;
WHEN OTHERS THEN
-- fnd_file.new_line(APPS.FND_FILE.LOG,1);
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'error in generating output file '||'$'||x_directory||'$'||'/'||'$'||x_file_name);
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,SQLERRM);
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'*----*----**----*----**----*----**----*----**----*');
END XXCSC_AP_SUPP_PRINT_PROC;

---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPP_STATUS_PROC
-- DESCRIPTION : Update the PROCESS_FLAG and STATUS_FLAG
---------------------------------------------------------------------------------------------

PROCEDURE XXCSC_AP_SUPP_STATUS_PROC (x_process IN VARCHAR2
,x_status IN VARCHAR2
)
IS

CURSOR cur_hr IS
SELECT hdr.*
FROM XXHR.xxcsc_ap_supplier_headers hdr;

CURSOR cur_st IS
SELECT sit.*
FROM XXHR.xxcsc_ap_supplier_sites sit;

CURSOR cur_cn IS
SELECT con.*
FROM XXHR.xxcsc_ap_supplier_contacts con;

BEGIN

---------------------------------------------------------------------------------------------
-- UPDATE PROCESS FLAG
---------------------------------------------------------------------------------------------
IF NVL(x_process,'$') != '$' THEN
--- Update the PROCESS FLAG
FOR rec_hr IN cur_hr LOOP
UPDATE XXHR.xxcsc_ap_supplier_headers
SET process_flag = x_process
,status_flag = NULL
,error_code = NULL;

COMMIT;
END LOOP;

--- Update the PROCESS FLAG
FOR rec_st IN cur_st LOOP
UPDATE XXHR.xxcsc_ap_supplier_sites
SET process_flag = x_process
,status_flag = NULL
,error_code = NULL;

COMMIT;
END LOOP;

--- Update the PROCESS FLAG
FOR rec_cn IN cur_cn LOOP
UPDATE XXHR.xxcsc_ap_supplier_contacts
SET process_flag = x_process
,status_flag = NULL
,error_code = NULL;

COMMIT;
END LOOP;

END IF;
---------------------------------------------------------------------------------------------
-- UPDATE STATUS FLAG
---------------------------------------------------------------------------------------------

IF NVL(x_status,'$') != '$' THEN
--- Update the STATUS FLAG
FOR rec_hr IN cur_hr LOOP
UPDATE XXHR.xxcsc_ap_supplier_headers
SET status_flag = x_status
WHERE status_flag IS NULL;

COMMIT;
END LOOP;

--- Update the STATUS FLAG
FOR rec_st IN cur_st LOOP
UPDATE XXHR.xxcsc_ap_supplier_sites
SET status_flag = x_status
WHERE status_flag IS NULL;

COMMIT;
END LOOP;

--- Update the STATUS FLAG
FOR rec_cn IN cur_cn LOOP
UPDATE XXHR.xxcsc_ap_supplier_contacts
SET status_flag = x_status
WHERE status_flag IS NULL;

COMMIT;
END LOOP;

END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( 'ERROR CODE : ' || SQLCODE );
DBMS_OUTPUT.PUT_LINE( 'ERROR DESCRIPTION : ' || SQLERRM );
ROLLBACK;

END XXCSC_AP_SUPP_STATUS_PROC;

---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPP_RECON_PROC
-- DESCRIPTION : RECON Report Design
---------------------------------------------------------------------------------------------

PROCEDURE XXCSC_AP_SUPP_RECON_PROC (x_object IN VARCHAR2
)
IS
file_handle UTL_FILE.FILE_TYPE;
x_directory VARCHAR2(50) := '/usr/tmp';
x_file_name VARCHAR2(100) := 'XXCSC_AP_SUPPLIER_ERROR.dat';

CURSOR cur_recon IS
SELECT 'SUPPLIER HEADERS RECONCILIATION REPORT' data FROM DUAL
UNION ALL
SELECT '*************************************' FROM DUAL
UNION ALL
SELECT RPAD('Total Record Count in Staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_headers
UNION ALL
SELECT RPAD('Total Record Errored in staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_headers WHERE error_code IS NOT NULL
UNION ALL
SELECT RPAD('Total Record transfered from staging into Interface Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_headers WHERE status_flag = 'T' AND process_flag = 'T'
UNION ALL
SELECT RPAD('Total Record in Interface Table',80,' ') || COUNT(1) FROM APPS.ap_suppliers_int
UNION ALL
SELECT RPAD('Total Record Processed from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_suppliers_int WHERE status ='PROCESSED'
UNION ALL
SELECT RPAD('Total Record Rejected while transfering from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_suppliers_int WHERE status ='REJECTED'
UNION ALL
SELECT ' ' FROM DUAL
UNION ALL
SELECT 'SUPPLIER SITES RECONCILIATION REPORT' FROM DUAL
UNION ALL
SELECT '*************************************' FROM DUAL
UNION ALL
SELECT RPAD('Total Record Count in Staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_sites
UNION ALL
SELECT RPAD('Total Record Errored in staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_sites WHERE error_code IS NOT NULL
UNION ALL
SELECT RPAD('Total Record transfered from staging into Interface Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_sites WHERE status_flag = 'T' AND process_flag = 'T'
UNION ALL
SELECT RPAD('Total Record in Interface Table',80,' ') || COUNT(1) FROM APPS.ap_supplier_sites_int
UNION ALL
SELECT RPAD('Total Record Processed from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_supplier_sites_int WHERE status ='PROCESSED'
UNION ALL
SELECT RPAD('Total Record Rejected while transfering from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_supplier_sites_int WHERE status ='REJECTED'
UNION ALL
SELECT ' ' FROM DUAL
UNION ALL
SELECT 'SUPPLIER CONTACTS RECONCILIATION REPORT' FROM DUAL
UNION ALL
SELECT '**************************************' FROM DUAL
UNION ALL
SELECT RPAD('Total Record Count in Staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_contacts
UNION ALL
SELECT RPAD('Total Record Errored in Staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_contacts WHERE error_code IS NOT NULL
UNION ALL
SELECT RPAD('Total Record transfered from staging into Interface Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_contacts WHERE status_flag = 'T' AND process_flag = 'T'
UNION ALL
SELECT RPAD('Total Record in Interface Table:',80,' ') || COUNT(1) FROM APPS.ap_sup_site_contact_int
UNION ALL
SELECT RPAD('Total Record Processed from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_sup_site_contact_int WHERE status ='PROCESSED'
UNION ALL
SELECT RPAD('Total Record Rejected while transfering from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_sup_site_contact_int WHERE status ='REJECTED'
UNION ALL
SELECT ' ' FROM DUAL;

BEGIN
file_handle := UTL_FILE.FOPEN(x_directory, x_file_name ,'W');

file_handle := UTL_FILE.FOPEN(x_directory, x_file_name ,'A');

UTL_FILE.PUT_LINE(file_handle, RPAD('*',85,'*'));
UTL_FILE.PUT_LINE(file_handle, RPAD(' ',35,' ')|| x_object ||' CONVERSION RECON REPORT' || RPAD(' ',35, ' '));
UTL_FILE.PUT_LINE(file_handle, RPAD('*',85,'*'));
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, ' ');

FOR rec_recon IN cur_recon LOOP
UTL_FILE.PUT_LINE(file_handle, rec_recon.data);
END LOOP;

UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, RPAD('*',85,'*'));
UTL_FILE.PUT_LINE(file_handle, RPAD(' ',35,' ')|| 'END OF THE REPORT' || RPAD(' ',35, ' '));
UTL_FILE.PUT_LINE(file_handle, RPAD('*',85,'*'));
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, ' ');

UTL_FILE.FCLOSE(file_handle);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( 'ERROR CODE : ' || SQLCODE );
DBMS_OUTPUT.PUT_LINE( 'ERROR DESCRIPTION : ' || SQLERRM );
DBMS_OUTPUT.PUT_LINE( 'ERROR OCCURED WHILE PRINTING THE RECON REPORT');
ROLLBACK;

END XXCSC_AP_SUPP_RECON_PROC;

END XXCSC_AP_SUPPLIERS_CONV_PKG;

/

Subscribe to: Posts ( Atom )

Oracle Applications & Oracle Cloud Fusion HCM

Oracle Application and Cloud HCM

Tuesday 20 December 2016

Direct Debit Authorization (Inbound)

Supplier Conversion

Contact us