Oracle EBS (ERP): Supplier Bank Interface at Site Level Oracle Apps

#Table Structure as per data template

CREATE TABLE XXJG_AP_EMP_SUPL_BANK_STG
(
SUPPLIER_NAME VARCHAR2(300 BYTE),
SUPPLIER_SITE_NAME VARCHAR2(100 BYTE),
BANK_NAME VARCHAR2(200 BYTE),
BRANCH_NAME VARCHAR2(200 BYTE),
BANK_ACCOUNT_NUM_ELECTRONIC VARCHAR2(100 BYTE),
ACCOUNT_NAME VARCHAR2(200 BYTE),
ACCOUNT_NUMBER VARCHAR2(100 BYTE),
COUNTRY VARCHAR2(100 BYTE),
CURRENCY VARCHAR2(100 BYTE),
ALLOW_MULTI_CUR VARCHAR2(10 BYTE),
ATTRIBUTE1 VARCHAR2(200 BYTE),
ATTRIBUTE2 VARCHAR2(200 BYTE),
ATTRIBUTE3 VARCHAR2(200 BYTE),
ATTRIBUTE4 VARCHAR2(200 BYTE),
ATTRIBUTE5 VARCHAR2(200 BYTE),
STATUS VARCHAR2(100 BYTE),
ERROR_FLAG NUMBER,
ERR_MSG VARCHAR2(4000 BYTE),
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
REQUEST_ID NUMBER,
OPERATING_UNIT VARCHAR2(240 BYTE),
BANK_NAME_ALT VARCHAR2(500 BYTE),
BANK_NUM NUMBER,
BRANCH_NAME_ALT VARCHAR2(500 BYTE),
BRANCH_NUM NUMBER,
ACCT_TYPE VARCHAR2(100 BYTE),
EMPLOYEE_NUM VARCHAR2(10 BYTE)
)
/

CREATE SYNONYM APPS.XXJG_AP_EMP_SUPL_BANK_STG FOR JG.XXJG_AP_EMP_SUPL_BANK_STG
/

#Procedure

CREATE OR REPLACE PROCEDURE APPS.JXXG_VEN_BANK_CREATE (

ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2)

GN_USER_ID NUMBER := APPS.FND_PROFILE.VALUE ('USER_ID');

GN_LOGIN_ID NUMBER := APPS.FND_PROFILE.VALUE ('LOGIN_ID');

CURSOR lcu_sup_bank

SELECT supplier_name,

supplier_site_name,

bank_name,

branch_name,

bank_account_num_electronic -- added

account_name,

account_number,

country,

currency,

allow_multi_cur,

attribute1,

attribute2,

attribute3,

attribute4,

attribute5,

status,

error_flag,

err_msg,

creation_date,

created_by,

last_update_date,

last_updated_by,

last_update_login,

request_id,

operating_unit,

bank_name_alt,

bank_num,

branch_name_alt,

branch_num,

acct_type,

employee_num

FROM APPS.XXJG_AP_EMP_SUPL_BANK_STG xab

WHERE xab.status = 'NEW' AND request_id IS NULL;

CURSOR lcu_bank_account (lc_bank_account IN VARCHAR2)

SELECT *

FROM apps.IBY_EXT_BANK_ACCOUNTS

WHERE bank_account_name = lc_bank_account AND ROWNUM = 1;

--Local Variables used for Validations

lc_country_code VARCHAR2 (50);

lc_bank_name VARCHAR2 (360);

lc_bank_number VARCHAR2 (500);

ln_bank_exist_id NUMBER;

ln_branch_exist_id NUMBER;

ln_account_exist_id NUMBER;

ln_ca_object_version_number NUMBER;

ln_branch_object_number NUMBER;

ln_bank_branch_count NUMBER;

ln_bank_account_count NUMBER;

lc_currency_code VARCHAR2 (50);

ln_bank_cnt NUMBER;

ln_vendor_id NUMBER;

ln_party_id NUMBER;

ln_party_site_id NUMBER;

ln_vendor_site_id NUMBER;

ln_org_id NUMBER;

ln_loc_count NUMBER;

ln_bank_loc_id NUMBER;

ln_branch_loc_id NUMBER;

ln_bank_loc_count NUMBER;

ln_branch_loc_count NUMBER;

ln_request_id NUMBER;

ln_ledger_id NUMBER;

ln_err_flag NUMBER; ----- ln_err_flag is having initial value = 1

-- and have value=2 IF any validation failed,

-- data will be insrted only IF this flag=1

lc_status VARCHAR2 (100); ----- IF any validation fails THEN lc_status value ='ERROR-V'

-- and updates the staging table and insert the error status into

-- error table

lc_msg VARCHAR2 (4000); ----- lc_msg shows the error message when any validation fails

-- and updates the staging table and insert the error message into

-- error table

--- API VARIABLES ------

lnx_bank_id NUMBER;

lnx_bank_id1 NUMBER;

lnx_branch_id NUMBER;

lnx_bank_acct_id NUMBER;

lcx_status VARCHAR2 (10);

lnx_msg_cnt NUMBER;

lcx_msg_data VARCHAR2 (1000);

lnx_msg_index_out NUMBER;

ldx_date DATE;

lnx_assign_id NUMBER;

lrx_response apps.IBY_FNDCPT_COMMON_PUB.Result_rec_type;

lcx_commit VARCHAR2 (1) := apps.fnd_api.g_false;

lnx_location_id NUMBER;

lnx_party_site_id NUMBER;

lnx_party_site_number NUMBER;

lnx_br_location_id NUMBER;

lnx_br_party_site_id NUMBER;

lnx_br_party_site_number NUMBER;

ln_ou_id NUMBER;

--- API RECORD TYPES ----

lr_bank_type_rec apps.iby_ext_bankacct_pub.extbank_rec_type; -- for bank creation ---

LR_BANK_BRANCH_TYPE_REC APPS.IBY_EXT_BANKACCT_PUB.EXTBANKBRANCH_REC_TYPE; -- for bank branch creation --

LR_BANK_ACCOUNT_TYPE_REC APPS.IBY_EXT_BANKACCT_PUB.EXTBANKACCT_REC_TYPE; -- for Bank Account Creation ---

lr_payee apps.iby_disbursement_setup_pub.payeecontext_rec_type;

LR_INSTRUMENT APPS.IBY_FNDCPT_SETUP_PUB.PMTINSTRUMENT_REC_TYPE; -- for Supplier to Bank Pay Instruction creation --

LR_ASSIGNMENT_ATTRIBS APPS.IBY_FNDCPT_SETUP_PUB.PMTINSTRASSIGNMENT_REC_TYPE;

LR_LOCATION_REC APPS.HZ_LOCATION_V2PUB.LOCATION_REC_TYPE; -- for bank and branch location creation --

lr_party_site_rec apps.hz_party_site_v2pub.party_site_rec_type; -- for bank and branch site creation --

BEGIN

FOR lr_bank IN lcu_sup_bank

LOOP

lnx_bank_id := NULL;

lnx_bank_id1 := NULL;

lnx_branch_id := NULL;

lnx_bank_acct_id := NULL;

lnx_party_site_id := NULL;

lnx_br_location_id := NULL;

lnx_br_party_site_id := NULL;

lnx_br_party_site_number := NULL;

ln_org_id := NULL;

ln_loc_count := NULL;

ln_bank_loc_count := NULL;

ln_branch_loc_count := NULL;

ln_bank_loc_id := NULL;

ln_branch_loc_id := NULL;

lc_country_code := NULL;

lc_bank_name := NULL;

ln_bank_exist_id := NULL;

ln_branch_exist_id := NULL;

ln_account_exist_id := NULL;

ln_ca_object_version_number := NULL;

ln_branch_object_number := NULL;

ln_bank_branch_count := NULL;

ln_bank_account_count := NULL;

lc_currency_code := NULL;

lcx_status := NULL;

lcx_msg_data := NULL;

lcx_status := NULL;

lnx_msg_cnt := NULL;

lnx_party_site_number := NULL;

ldx_date := NULL;

lnx_msg_index_out := NULL;

lrx_response := NULL;

lnx_assign_id := NULL;

ln_vendor_id := NULL;

ln_party_id := NULL;

ln_party_site_id := NULL;

ln_vendor_site_id := NULL;

ln_err_flag := 1;

-- lc_status := 'NEW' ;

lc_msg := NULL;

ln_request_id := apps.FND_GLOBAL.CONC_REQUEST_ID;

ln_ledger_id := apps.FND_PROFILE.VALUE ('GL_SET_OF_BKS_ID');

-- apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'Bank Details Loading Started for Supplier : '||lr_bank.supplier_name||'.......' ) ;

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'*************************************NEXT RECORD******************************************');

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'******************************************************************************************');

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank Details Loading Started for Supplier : '

|| lr_bank.supplier_name

|| '.......');

/******* Checking Country Code **********/

BEGIN

SELECT territory_code

INTO lc_country_code

FROM apps.fnd_territories_vl

WHERE territory_code = LTRIM (RTRIM (lr_bank.country))

AND ROWNUM = 1;

/* SELECT country_code

INTO lc_country_code

FROM apps.PA_COUNTRY_V

WHERE country_code = LTRIM (RTRIM (lr_bank.country )) --condition modified by EY

AND ROWNUM = 1 ; */

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Country Code is Validated : ' || lc_country_code);

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK';

lc_msg := 'Country is not Valid ';

/*UPDATE XXJG_AP_SUPL_BANK_STG

SET status = lc_status

, err_msg = lc_msg

, request_id = ln_request_id

WHERE supplier_name = lr_bank.supplier_name

AND supplier_site_name = lr_bank.supplier_site_name

AND OPERATING_UNIT = lr_bank.OPERATING_UNIT;*/

-- AND branch_name = lr_bank.branch_name ;

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Country is not Valid ');

END;

/******* Checking Currency Code **********/

BEGIN

SELECT fs.currency_code

INTO lc_currency_code

FROM apps.FND_CURRENCIES fs

WHERE fs.currency_code = lr_bank.currency

AND NVL (fs.enabled_flag, 'N') = 'Y';

-- apps.FND_FILE.PUT_LINE(apps.FND_FILE.LOG,'Currency Code Is Validated : '||lc_currency_code ) ;

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK';

lc_msg := lc_msg || ',Currency is not Valid ';

/*UPDATE XXJG_AP_SUPL_BANK_STG

SET status = lc_status

, err_msg = lc_msg

, request_id = ln_request_id

WHERE supplier_name = lr_bank.supplier_name

AND supplier_site_name = lr_bank.supplier_site_name

AND OPERATING_UNIT = lr_bank.OPERATING_UNIT; */

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Currency is not Valid ');

END;

/******* Capturing Bank Name Into a Variable **********/

lc_bank_name := LTRIM (RTRIM (lr_bank.bank_name));

/******* Capturing Bank Id if already existed into system **********/

BEGIN

SELECT MAX (bank_party_id)

INTO ln_bank_exist_id

FROM apps.ce_banks_v

WHERE bank_name = lr_bank.bank_name AND ROWNUM = 1;

IF ln_bank_exist_id IS NOT NULL

THEN

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank ID '

|| ln_bank_exist_id

|| 'Already Exists for supplier '

|| lr_bank.supplier_name

|| ' and supplier site '

|| lr_bank.supplier_site_name);

END IF;

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK';

lc_msg := lc_msg || ',Bank Name is not Valid ';

/*UPDATE XXJG_AP_SUPL_BANK_STG

SET status = lc_status

, err_msg = lc_msg

, request_id = ln_request_id

WHERE supplier_name = lr_bank.supplier_name

AND supplier_site_name = lr_bank.supplier_site_name

AND OPERATING_UNIT = lr_bank.OPERATING_UNIT ; */

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Bank Name does not exists ');

END;

/******* Checking Bank Exists or Not SSB_PR1 **********/

-- apps.FND_FILE.PUT_LINE(apps.FND_FILE.LOG,'Bank Existence Checking : ') ;

apps.iby_ext_bankacct_pub.check_bank_exist (

p_api_version => '1.0',

p_init_msg_list => apps.FND_API.G_FALSE,

p_country_code => lc_country_code,

p_bank_name => lc_bank_name,

p_bank_number => lr_bank.BANK_NUM,

x_return_status => lcx_status,

x_msg_count => lnx_msg_cnt,

x_msg_data => lcx_msg_data,

x_bank_id => lnx_bank_id1,

x_end_date => ldx_date,

x_response => lrx_response);

IF NVL (ln_bank_exist_id, 0) = 0

THEN

ln_bank_exist_id := lnx_bank_id1;

END IF;

/******* Capturing Branch Id if already existed into system **********/

BEGIN

SELECT MAX (branch_party_id)

INTO ln_branch_exist_id

FROM apps.IBY_EXT_BANK_BRANCHES_V

WHERE bank_branch_name = lr_bank.branch_name

AND bank_party_id = ln_bank_exist_id

AND TRUNC (SYSDATE) BETWEEN TRUNC (START_DATE)

AND TRUNC (NVL (END_DATE, SYSDATE));

--IF ln_branch_exist_id IS NOT NULL THEN

IF ln_branch_exist_id > 0

THEN

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Branch ID '

|| ln_branch_exist_id

|| ' Already Exists for supplier '

|| lr_bank.supplier_name

|| ' and supplier site '

|| lr_bank.supplier_site_name);

ELSE

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Branch ID '

|| ln_branch_exist_id

|| ' Already Does Not Exist for supplier '

|| lr_bank.supplier_name

|| ' and supplier site '

|| lr_bank.supplier_site_name);

END IF;

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK';

lc_msg := lc_msg || ',Bank Branch Name is not Valid ';

/*UPDATE XXJG_AP_SUPL_BANK_STG

SET status = lc_status

, err_msg = lc_msg

, request_id = ln_request_id

WHERE supplier_name = lr_bank.supplier_name

AND supplier_site_name = lr_bank.supplier_site_name

AND OPERATING_UNIT = lr_bank.OPERATING_UNIT ; */

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Branch does not exists ');

END;

/******* Capturing Bank Account Id if already existed into system **********/

BEGIN

SELECT MAX (ext_bank_account_id) -- Modified by EY on 22nd Nov 2014

INTO ln_account_exist_id

FROM apps.IBY_EXT_BANK_ACCOUNTS

WHERE bank_account_num = lr_bank.account_number

AND country_code = lc_country_code

AND currency_code = lc_currency_code

AND bank_id = ln_bank_exist_id

AND branch_id = ln_branch_exist_id

AND TRUNC (SYSDATE) BETWEEN TRUNC (START_DATE)

AND TRUNC (NVL (END_DATE, SYSDATE));

IF ln_account_exist_id > 0

THEN

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank A/c ID '

|| ln_account_exist_id

|| ' Already Exists for supplier '

|| lr_bank.supplier_name

|| ' and supplier site '

|| lr_bank.supplier_site_name);

ELSE

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank A/c ID '

|| ln_account_exist_id

|| ' Already Does Not Exist for supplier '

|| lr_bank.supplier_name

|| ' and supplier site '

|| lr_bank.supplier_site_name);

END IF;

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK';

lc_msg := lc_msg || ',Bank Account is not Valid ';

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Bank Account is not valid ');

END;

IF ln_err_flag = 1

THEN

IF NVL (ln_bank_exist_id, 0) = 0

THEN

-- IF ln_bank_cnt = 0 AND ln_err_flag = 1 THEN --- IF Bank not exists into System ,then bankn has to be created into the system

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Bank Creating.. for: ' || lc_bank_name);

lr_bank_type_rec.bank_id := NULL;

lr_bank_type_rec.bank_number := lr_bank.BANK_NUM; --NULL; -- Commented by EY --lr_bank.bank_number ; --- NULL ; --- On 23-Jul-10

lr_bank_type_rec.bank_alt_name := lr_bank.BANK_NAME_ALT;

lr_bank_type_rec.institution_type := 'BANK';

lr_bank_type_rec.bank_name := lc_bank_name;

lr_bank_type_rec.country_code := lc_country_code;

lnx_bank_id := NULL;

lcx_status := NULL;

lcx_msg_data := NULL;

lcx_status := NULL;

ldx_date := NULL;

lnx_msg_cnt := NULL;

lnx_msg_index_out := NULL;

lrx_response := NULL;

apps.iby_ext_bankacct_pub.create_ext_bank (

p_api_version => 1.0,

p_init_msg_list => apps.FND_API.G_TRUE,

p_ext_bank_rec => lr_bank_type_rec,

x_bank_id => lnx_bank_id,

x_return_status => lcx_status,

x_msg_count => lnx_msg_cnt,

x_msg_data => lcx_msg_data,

x_response => lrx_response);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'BANK CREATED : ' || lnx_bank_id);

IF (lcx_status <> 'S')

THEN

ln_err_flag := 2;

FOR l_index IN 1 .. lnx_msg_cnt

LOOP

apps.fnd_msg_pub.get (

p_msg_index => apps.fnd_msg_pub.g_last,

p_encoded => 'F',

p_data => lcx_msg_data,

p_msg_index_out => lnx_msg_index_out);

retcode := 1;

ln_err_flag := 2;

lc_msg := lc_msg || ',' || lcx_msg_data;

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Create Bank Error ' || lcx_msg_data);

END LOOP;

ELSE

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank Created : '

|| lr_bank.bank_name

|| ' and Bank Id is : '

|| lnx_bank_id);

lc_status := 'BANK-CREATED :: ';

END IF;

ELSE

lc_status := 'BANK ALREADY EXISTS';

END IF;

/******** Creating Bank Branch ****************/

-- IF ln_bank_branch_count = 0 AND ln_err_flag = 1 THEN

IF ln_err_flag = 1

THEN

-- IF ln_bank_branch_count = 0 THEN

IF NVL (ln_branch_exist_id, 0) = 0

THEN

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Bank Branch Creating........ ');

lr_bank_branch_type_rec.branch_name := lr_bank.branch_name;

lr_bank_branch_type_rec.branch_number :=

lr_bank.bank_account_num_electronic;

lr_bank_branch_type_rec.alternate_branch_name :=

lr_bank.BRANCH_NAME_ALT;

lr_bank_branch_type_rec.bank_party_id :=

NVL (lnx_bank_id, ln_bank_exist_id);

lr_bank_branch_type_rec.bic := lr_bank.branch_num; --Commented by EY

lr_bank_branch_type_rec.branch_type := 'OTHER';

lcx_status := NULL;

lcx_msg_data := NULL;

lnx_msg_cnt := NULL;

lnx_msg_index_out := NULL;

ln_branch_object_number := NULL;

--ln_bank_branch_count := NULL ;

apps.iby_ext_bankacct_pub.create_ext_bank_branch (

p_api_version => 1.0,

p_init_msg_list => 'T',

p_ext_bank_branch_rec => lr_bank_branch_type_rec,

x_branch_id => lnx_branch_id,

x_return_status => lcx_status,

x_msg_count => lnx_msg_cnt,

x_msg_data => lcx_msg_data,

x_response => lrx_response);

IF (lcx_status <> 'S')

THEN

ln_err_flag := 2;

FOR l_index IN 1 .. lnx_msg_cnt

LOOP

apps.fnd_msg_pub.get (

p_msg_index => apps.fnd_msg_pub.g_last,

p_encoded => 'F',

p_data => lcx_msg_data,

p_msg_index_out => lnx_msg_index_out);

retcode := 1;

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Create Bank Branch Error ' || lcx_msg_data);

lc_msg := lc_msg || ',' || lcx_msg_data;

END LOOP;

ELSE

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank Branch Created '

|| lr_bank.branch_name

|| ' and Branch Id Is : '

|| lnx_branch_id);

lc_status := 'BANK BRANCH CREATED';

END IF;

ELSE

lc_status := 'BANK BRANCH ALREADY EXISTS';

END IF;

/******* Validation of Party Name SSB_PR3 **********/

BEGIN

/*SELECT asa.party_id

INTO ln_party_id

FROM apps.AP_SUPPLIERS asa

WHERE upper(asa.vendor_name )= upper(lr_bank.supplier_name);*/

SELECT asa.party_id

INTO ln_party_id

FROM apps.AP_SUPPLIERS asa

WHERE UPPER (asa.vendor_name) = UPPER (lr_bank.supplier_name);

IF ln_party_id IS NOT NULL

THEN

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Supplier Party Validated : ' || ln_party_id);

END IF;

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK';

lc_msg := lc_msg || ',Party Name Validation Failed ';

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Party Name Validation Failed ');

END;

IF ln_err_flag = 1

THEN

-- IF ln_bank_account_count = 0 THEN

IF NVL (ln_account_exist_id, 0) = 0

THEN

lr_bank_account_type_rec.acct_owner_party_id := ln_party_id;

lr_bank_account_type_rec.bank_id :=

NVL (lnx_bank_id, ln_bank_exist_id);

lr_bank_account_type_rec.branch_id :=

NVL (lnx_branch_id, ln_branch_exist_id);

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'before bank_account_num : ' || lr_bank.account_number);

lr_bank_account_type_rec.bank_account_num :=

SUBSTR (lr_bank.account_number, 1, 100);

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'before bank_account_name : ' || lr_bank.account_name);

lr_bank_account_type_rec.bank_account_name :=

SUBSTR (lr_bank.account_name, 1, 80);

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'After bank_account_name : ' || lr_bank.account_name);

lr_bank_account_type_rec.acct_type := lr_bank.acct_type;

-- lr_bank_account_type_rec.bank_account_name := NVL(SUBSTR(lr_bank.account_name,1,80),SUBSTR(lr_bank.account_number,1,100));

lr_bank_account_type_rec.country_code := lc_country_code;

lr_bank_account_type_rec.currency := lc_currency_code;

-- lr_bank_account_type_rec.iban := lr_bank.iban ;

-- lr_bank_account_type_rec.check_digits := lr_bank.check_digits ;

lr_bank_account_type_rec.object_version_number := 1.0;

lr_bank_account_type_rec.foreign_payment_use_flag :=

lr_bank.allow_multi_cur; --'Y';

lr_bank_account_type_rec.attribute1 := lr_bank.attribute1; -- added

apps.iby_ext_bankacct_pub.create_ext_bank_acct (

p_api_version => 1.0,

p_init_msg_list => 'T',

p_ext_bank_acct_rec => lr_bank_account_type_rec,

x_acct_id => lnx_bank_acct_id,

x_return_status => lcx_status,

x_msg_count => lnx_msg_cnt,

x_msg_data => lcx_msg_data,

x_response => lrx_response);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'ACCOUNT : ' || lnx_bank_acct_id);

IF (lcx_status <> 'S')

THEN

ln_err_flag := 2;

FOR l_index IN 1 .. lnx_msg_cnt

LOOP

apps.fnd_msg_pub.get (

p_msg_index => apps.fnd_msg_pub.g_last,

p_encoded => 'F',

p_data => lcx_msg_data,

p_msg_index_out => lnx_msg_index_out);

retcode := 1;

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Create Bank Brance Error ' || lcx_msg_data);

lc_status := 'ERROR-BANK ACCOUNT CREATION';

lc_msg := lc_msg || ',' || lcx_msg_data;

END LOOP;

ELSE

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank Account Created and Bank Account Id is : '

|| lnx_bank_acct_id);

lc_status := 'BANK ACCOUNT CREATED';

END IF;

ELSE

lc_status := 'BANK ACCOUNT ALREADY EXISTS';

END IF;

--apps.FND_FILE.PUT_LINE(apps.FND_FILE.LOG,'Ledger Id:: ' || ln_ledger_id) ;

BEGIN

SELECT ORGANIZATION_ID

INTO ln_ou_id

FROM apps.HR_OPERATING_UNITS

WHERE name = lr_bank.operating_unit AND ROWNUM = 1;

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-OU';

lc_msg := lc_msg || ',Operating Unit is not present ';

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Operating unit is not present: ' || lr_bank.operating_unit);

END;

FOR lr_org IN (SELECT DISTINCT ORGANIZATION_ID

FROM apps.HR_OPERATING_UNITS

WHERE name = lr_bank.operating_unit)

LOOP

/******* Validation of Address Name SSB_PR4 **********/

BEGIN

/* SELECT assa.party_site_id

INTO ln_party_site_id

FROM --apps.HZ_PARTY_SITES hps,

apps.AP_SUPPLIER_SITES_ALL assa,

apps.ap_suppliers sup

WHERE sup.vendor_id = assa.vendor_id

AND upper(sup.vendor_name) = upper(lr_bank.supplier_name)

AND assa.vendor_site_code = lr_bank.supplier_site_name

AND assa.org_id = lr_org.organization_id;*/

SELECT assa.party_site_id

INTO ln_party_site_id

FROM --apps.HZ_PARTY_SITES hps,

apps.AP_SUPPLIER_SITES_ALL assa,

apps.ap_suppliers sup

WHERE sup.vendor_id = assa.vendor_id

AND UPPER (sup.vendor_name) =

UPPER (lr_bank.supplier_name)

AND assa.vendor_site_code = lr_bank.supplier_site_name

AND assa.org_id = 1142;

IF ln_party_site_id IS NOT NULL

THEN

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Supplier Party Site Validated');

END IF;

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK';

lc_msg := lc_msg || ',Party Address Validation Failed ';

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Party Address Validation Failed ');

END;

/******* Validation for Supplier Name SSIL_PR1 ******/

BEGIN

SELECT aps.vendor_id

INTO ln_vendor_id

FROM apps.ap_suppliers aps

WHERE LOWER (aps.vendor_name) =

LOWER (lr_bank.supplier_name);

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK_SUPPLIER';

lc_msg :=

lc_msg || ',Bank Vendor Does Not Exist : Validation Failed';

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank Vendor Does Not Exists : Validation Failed');

END;

/******* Validation of Supplier Address Name SSB_PR5 **********/

BEGIN

SELECT ass.vendor_site_id, ass.org_id

INTO ln_vendor_site_id, ln_org_id

FROM apps.AP_SUPPLIER_SITES_ALL ass

WHERE ass.vendor_id = ln_vendor_id /*OPEN ISSUE : ORG_ID dsnt EXISTS in HZ_PARTY_SITES */

AND LOWER (ass.vendor_site_code) =

LOWER (lr_bank.supplier_site_name)

AND ass.org_id = lr_org.organization_id; --(SELECT DISTINCT org_id FROM ORG_ORGANIZATION_DEFINITIONS)

IF ln_vendor_site_id IS NOT NULL

THEN

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Supplier Address Validated ');

END IF;

EXCEPTION

WHEN OTHERS

THEN

ln_err_flag := 2;

retcode := 1;

lc_status := 'ERROR-BANK';

lc_msg := lc_msg || ',Supplier Address Validation Failed ';

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Supplier Address Validation Failed ');

END;

/*******************Create Supplier to Bank Pay Instruction ********************************/

IF ln_err_flag = 1

THEN

lr_payee.supplier_site_id := ln_vendor_site_id;

lr_payee.party_id := ln_party_id;

lr_payee.party_site_id := ln_party_site_id;

lr_payee.payment_function := 'PAYABLES_DISB';

lr_payee.org_id := ln_org_id;

lr_payee.org_type := 'OPERATING_UNIT';

--lr_instrument.instrument_id := lnx_bank_acct_id ;

lr_instrument.instrument_id :=

NVL (ln_account_exist_id, lnx_bank_acct_id);

lr_instrument.instrument_type := 'BANKACCOUNT';

lr_assignment_attribs.priority := 1; --2 ;

lr_assignment_attribs.instrument := lr_instrument;

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, ln_vendor_site_id);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, ln_party_id);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, ln_party_site_id);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, ln_org_id);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

lr_instrument.instrument_id);

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'ln_account_exist_id::' || ln_account_exist_id || '::');

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'lnx_bank_acct_id::' || lnx_bank_acct_id || '::');

-- IF ln_err_flag =1 THEN

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Inside Set Payee Instr Assigment');

apps.iby_disbursement_setup_pub.set_payee_instr_assignment (

p_api_version => 1.0,

p_init_msg_list => 'T',

p_commit => lcx_commit,

x_return_status => lcx_status,

x_msg_count => lnx_msg_cnt,

x_msg_data => lcx_msg_data,

p_payee => lr_payee,

p_assignment_attribs => lr_assignment_attribs,

x_assign_id => lnx_assign_id,

x_response => lrx_response);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, lcx_status);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, lnx_msg_cnt);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, lnx_assign_id);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, lcx_msg_data);

--end last

IF (lcx_status <> 'S')

THEN

ln_err_flag := 2;

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'Before loop' || lcx_status);

FOR l_index IN 1 .. lnx_msg_cnt

LOOP

apps.fnd_msg_pub.get (

p_msg_index => apps.fnd_msg_pub.g_last,

p_encoded => 'F',

p_data => lcx_msg_data,

p_msg_index_out => lnx_msg_index_out);

retcode := 1;

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Payment Instru error ' || lcx_status);

lc_status := 'ERROR-SUPPLIER BANK ASSOCIATION';

lc_msg := lc_msg || ',' || lcx_msg_data;

END LOOP;

ELSE

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank Conversion Completed for Supplier : '

|| lr_bank.supplier_name

|| ' for Org :'

|| ln_org_id);

lc_status := 'BANK CONVERSION COMPLETED';

/*UPDATE XXJG_AP_SUPL_BANK_STG

SET STATUS = 'BANK CONVERSION COMPLETED'

, last_update_date = sysdate

, request_id = ln_request_id

-- , assign_id = lnx_assign_id

WHERE supplier_name = lr_bank.supplier_name

AND supplier_site_name = lr_bank.supplier_site_name

AND OPERATING_UNIT = lr_bank.OPERATING_UNIT ;*/

END IF;

END LOOP; --- organization_id loop

apps.FND_FILE.PUT_LINE (

apps.FND_FILE.LOG,

'Bank Details Loaded Successfully for Supplier : '

|| lr_bank.supplier_name

|| '.......');

-- apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'Bank Details Loaded Successfully for Supplier : '||lr_bank.supplier_name||'.......' ) ;

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG,

'ln_err_flag: ' || ln_err_flag);

IF ln_err_flag = 1

THEN

UPDATE APPS.XXJG_AP_EMP_SUPL_BANK_STG

SET status = lc_status,

err_msg = lc_msg,

request_id = ln_request_id

WHERE supplier_name = lr_bank.supplier_name

AND supplier_site_name = lr_bank.supplier_site_name

AND OPERATING_UNIT = lr_bank.OPERATING_UNIT

AND status = 'NEW'

AND request_id IS NULL;

ELSE

UPDATE APPS.XXJG_AP_EMP_SUPL_BANK_STG

SET status = lc_status,

err_msg = lc_msg,

request_id = ln_request_id

WHERE supplier_name = lr_bank.supplier_name

AND supplier_site_name = lr_bank.supplier_site_name

AND OPERATING_UNIT = lr_bank.OPERATING_UNIT

AND status = 'NEW'

AND request_id IS NULL;

END IF;

COMMIT;

END LOOP;

END XXJG_VEN_BANK_CREATE;

-----------------------------------------------------------------------------------------------------------

Manish Kumar