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

#Table Structure

CREATE TABLE XXCUSTOM.XX_AP_EMP_SUPL_BANK_STG

(

SUPPLIER_NAME VARCHAR2(300 BYTE),

SUPPLIER_NUMBER VARCHAR2(30 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),

ALLOW_INTERNATION_PAY_FLAG VARCHAR2(100 BYTE),

EMPLOYEE_NUM VARCHAR2(10 BYTE)

)

/

CREATE OR REPLACE PUBLIC SYNONYM XX_AP_EMP_SUPL_BANK_STG FOR XXCUSTOM.XX_AP_EMP_SUPL_BANK_STG

/

CREATE OR REPLACE SYNONYM APPS.XX_AP_EMP_SUPL_BANK_STG FOR XXCUSTOM.XX_AP_EMP_SUPL_BANK_STG

/

#Procedure

CREATE OR REPLACE PROCEDURE APPS.XX_SUPP_BANK_UPLOAD_NEW (

ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2)

IS

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

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

CURSOR LCU_SUP_BANK

IS

SELECT TRIM (XAB.SUPPLIER_NAME) SUPPLIER_NAME,

TRIM (XAB.SUPPLIER_NUMBER) SUPPLIER_NUMBER,

TRIM (ASSA.VENDOR_SITE_CODE) SUPPLIER_SITE_NAME,

TRIM (XAB.BANK_NAME) BANK_NAME,

TRIM (XAB.BRANCH_NAME) BRANCH_NAME,

TRIM (XAB.BANK_ACCOUNT_NUM_ELECTRONIC)

BANK_ACCOUNT_NUM_ELECTRONIC,

TRIM (XAB.ACCOUNT_NAME) ACCOUNT_NAME,

TRIM (XAB.ACCOUNT_NUMBER) ACCOUNT_NUMBER,

TRIM (XAB.COUNTRY) COUNTRY,

TRIM (XAB.CURRENCY) CURRENCY,

TRIM (XAB.ALLOW_MULTI_CUR) ALLOW_MULTI_CUR,

TRIM (XAB.ATTRIBUTE1) ATTRIBUTE1,

TRIM (XAB.ATTRIBUTE2) ATTRIBUTE2,

TRIM (XAB.ATTRIBUTE3) ATTRIBUTE3,

TRIM (XAB.ATTRIBUTE4) ATTRIBUTE4,

TRIM (XAB.ATTRIBUTE5) ATTRIBUTE5,

TRIM (XAB.STATUS) STATUS,

TRIM (XAB.ERROR_FLAG) ERROR_FLAG,

TRIM (XAB.ERR_MSG) ERR_MSG,

TRIM (XAB.CREATION_DATE) CREATION_DATE,

TRIM (XAB.CREATED_BY) CREATED_BY,

TRIM (XAB.LAST_UPDATE_DATE) LAST_UPDATE_DATE,

TRIM (XAB.LAST_UPDATED_BY) LAST_UPDATED_BY,

TRIM (XAB.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN,

TRIM (XAB.REQUEST_ID) REQUEST_ID,

TRIM (XAB.OPERATING_UNIT) OPERATING_UNIT,

TRIM (XAB.BANK_NAME_ALT) BANK_NAME_ALT,

TRIM (XAB.BANK_NUM) BANK_NUM,

TRIM (XAB.BRANCH_NAME_ALT) BRANCH_NAME_ALT,

TRIM (XAB.BRANCH_NUM) BRANCH_NUM,

TRIM (XAB.ACCT_TYPE) ACCT_TYPE,

TRIM (XAB.ALLOW_INTERNATION_PAY_FLAG) ALLOW_INTERNATION_PAY_FLAG,

TRIM (XAB.EMPLOYEE_NUM) EMPLOYEE_NUM

FROM XX_AP_EMP_SUPL_BANK_STG XAB,

APPS.AP_SUPPLIERS APS,

APPS.AP_SUPPLIER_SITES_ALL ASSA

WHERE 1 = 1

AND TRIM (XAB.SUPPLIER_NUMBER) = APS.SEGMENT1

AND APS.VENDOR_ID = ASSA.VENDOR_ID

AND XAB.STATUS = 'NEW'

AND XAB.ACCOUNT_NUMBER IS NOT NULL

AND XAB.REQUEST_ID IS NULL

-- AND SUPPLIER_NUMBER IN ('319600')

ORDER BY XAB.SUPPLIER_NAME;

CURSOR LCU_BANK_ACCOUNT (LC_BANK_ACCOUNT IN VARCHAR2)

IS

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;

ROW_PROCESSED NUMBER := 0;

--- 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_SUP 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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'*****New Code is running now*****');

FOR LR_BANK IN LCU_SUP_BANK

LOOP

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 1);

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');

ROW_PROCESSED := ROW_PROCESSED + 1;

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'ROW_PROCESSED : ' || ROW_PROCESSED);

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

|| ' - '

|| LR_BANK.SUPPLIER_NUMBER

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

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 2);

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 ))

AND ROWNUM = 1 ; */

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 3);

EXCEPTION

WHEN OTHERS

THEN

LN_ERR_FLAG := 2;

RETCODE := 1;

LC_STATUS := 'ERROR-BANK';

LC_MSG := 'Country is not Valid ';

/*UPDATE XX_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 ');

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 4);

END;

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

BEGIN

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 5);

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 XX_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 ');

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 6);

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 7);

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, 'MESSAGE - ' || 8);

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 XX_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 ');

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 9);

END;

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 10);

-- 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);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 11);

IF NVL (LN_BANK_EXIST_ID, 0) = 0

THEN

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 12);

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, 'MESSAGE - ' || 13);

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, 'MESSAGE - ' || 14);

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 XX_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 ');

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 15);

END;

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

BEGIN

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 16);

SELECT MAX (EXT_BANK_ACCOUNT_ID)

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, 'MESSAGE - ' || 17);

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, 'MESSAGE - ' || 18);

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'Bank A/c ID '

|| LN_ACCOUNT_EXIST_ID

|| ' Does Not Exist for supplier '

|| LR_BANK.SUPPLIER_NAME

|| ' and supplier site '

|| LR_BANK.SUPPLIER_SITE_NAME);

END IF;

EXCEPTION

WHEN OTHERS

THEN

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 19);

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'LN_ERR_FLAG - ' || LN_ERR_FLAG);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'LN_BANK_EXIST_ID - ' || LN_BANK_EXIST_ID);

IF NVL (LN_BANK_EXIST_ID, 0) = 0

THEN

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'LN_BANK_EXIST_ID - ' || LN_BANK_EXIST_ID);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 20);

-- IF ln_bank_cnt = 0 AND ln_err_flag = 1 THEN --- IF Bank not exists into System ,then bank 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;

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.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 21);

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);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 22);

IF (LCX_STATUS <> 'S')

THEN

LN_ERR_FLAG := 2;

FOR L_INDEX IN 1 .. LNX_MSG_CNT

LOOP

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'MESSAGE - ' || 23);

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, 'MESSAGE - ' || 24);

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;

END IF;

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 25);

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

-- IF ln_bank_branch_count = 0 AND ln_err_flag = 1 THEN

IF LN_ERR_FLAG = 1

THEN

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'LN_ERR_FLAG.2 - ' || LN_ERR_FLAG);

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'LN_BRANCH_EXIST_ID.2 - ' || LN_BRANCH_EXIST_ID);

-- 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,

'LN_BRANCH_EXIST_ID.2 - ' || LN_BRANCH_EXIST_ID);

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 :=

SUBSTR (LR_BANK.BANK_ACCOUNT_NUM_ELECTRONIC, -4);

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.BANK_ACCOUNT_NUM_ELECTRONIC; -- ifsc code -- lr_bank.branch_num;

LR_BANK_BRANCH_TYPE_REC.BRANCH_TYPE := 'SWIFT';

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.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 26);

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 27);

LN_ERR_FLAG := 2;

FOR L_INDEX IN 1 .. LNX_MSG_CNT

LOOP

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'MESSAGE - ' || 28);

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, 'MESSAGE - ' || 29);

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;

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);*/

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 30);

SELECT ASA.PARTY_ID

INTO LN_PARTY_ID

FROM APPS.AP_SUPPLIERS ASA

WHERE UPPER (ASA.SEGMENT1) = UPPER (LR_BANK.SUPPLIER_NUMBER);

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 31);

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.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 32);

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 33);

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, 'MESSAGE - ' || 34);

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'Bank Account Created and Bank Account Id is : '

|| LNX_BANK_ACCT_ID);

IF LNX_BANK_ACCT_ID IS NOT NULL

AND UPPER (LR_BANK.ALLOW_INTERNATION_PAY_FLAG) IN

('N', 'NO')

THEN

BEGIN

UPDATE APPS.IBY_EXT_BANK_ACCOUNTS

SET FOREIGN_PAYMENT_USE_FLAG = NULL

WHERE 1 = 1 AND EXT_BANK_ACCOUNT_ID = LNX_BANK_ACCT_ID;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

END IF;

LC_STATUS := 'BANK ACCOUNT CREATED';

END IF;

ELSE

LC_STATUS := 'BANK ACCOUNT ALREADY EXISTS';

END IF;

END IF;

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 35);

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

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 36);

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 **********/

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 37);

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 (SUP.SEGMENT1) = (LR_BANK.SUPPLIER_NUMBER)

AND ASSA.VENDOR_SITE_CODE = LR_BANK.SUPPLIER_SITE_NAME

AND ASSA.ORG_ID = 1891;

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 38);

IF LN_PARTY_SITE_ID IS NOT NULL

THEN

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 39);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'Supplier Party Site Validated');

END IF;

EXCEPTION

WHEN OTHERS

THEN

LN_ERR_FLAG := 2;

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 40);

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 (APS.SEGMENT1) = (LR_BANK.SUPPLIER_NUMBER);

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 doesn't 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 ');

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 41);

END;

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

--Supplier Level bank creation

IF LN_ERR_FLAG = 1

THEN

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 42);

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';

--added by Dharmendra for SupplierLevel Record of Payee--

--lr_sup.supplier_site_id := ln_vendor_site_id;

LR_SUP.PARTY_ID := LN_PARTY_ID;

--lr_sup.party_site_id := ln_party_site_id;

LR_SUP.PAYMENT_FUNCTION := 'PAYABLES_DISB';

--lr_sup.org_id := ln_org_id;

--lr_sup.org_type := 'OPERATING_UNIT';

----end code of Dharmendra---

--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;

LR_ASSIGNMENT_ATTRIBS.INSTRUMENT := LR_INSTRUMENT;

--

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'ln_vendor_site_id - ' || LN_VENDOR_SITE_ID);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'ln_party_id - ' || LN_PARTY_ID);

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'ln_party_site_id - ' || LN_PARTY_SITE_ID);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'ln_org_id - ' || 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.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, 25);

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'******************************************************** Supplier SIte Level Assignment Starting******');

/* 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 - '||lcx_status);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, 'lnx_msg_cnt - '||lnx_msg_cnt);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, 'lnx_assign_id - '||lnx_assign_id);

apps.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, 'lcx_msg_data - '||lcx_msg_data);*/

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'******************************************************** Supplier SIte Level Assignment Ending******');

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'******************************************************** Supplier Level Assignment STarting******');

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 43);

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_SUP,

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,

'******************************************************** Supplier Level Assignment Ending******');

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 44);

--end last

IF (LCX_STATUS <> 'S')

THEN

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, 'MESSAGE - ' || 45);

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 XX_AP_EMP_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 IF;

COMMIT;

--********************************************************************

--Supplier Level bank creation

IF 1 = 2 -- ln_err_flag = 1

THEN

APPS.FND_FILE.PUT_LINE (

APPS.FND_FILE.LOG,

'*********Supplier Level Assignment*********');

-- 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 ; old comment no touch.

LR_INSTRUMENT.INSTRUMENT_ID :=

NVL (LN_ACCOUNT_EXIST_ID, LNX_BANK_ACCT_ID);

LR_INSTRUMENT.INSTRUMENT_TYPE := 'BANKACCOUNT';

LR_ASSIGNMENT_ATTRIBS.PRIORITY := 1;

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.FND_FILE.PUT_LINE (apps.FND_FILE.LOG, 25);

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 - ' || LCX_STATUS);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'lnx_msg_cnt - ' || LNX_MSG_CNT);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'lnx_assign_id - ' || LNX_ASSIGN_ID);

APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,

'lcx_msg_data - ' || 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 XX_AP_EMP_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 IF; -- 1=2

--********************************************************************

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 XX_AP_EMP_SUPL_BANK_STG

SET STATUS = LC_STATUS,

ERR_MSG = LC_MSG,

REQUEST_ID = LN_REQUEST_ID

WHERE SUPPLIER_NUMBER = LR_BANK.SUPPLIER_NUMBER

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 XX_AP_EMP_SUPL_BANK_STG

SET STATUS = LC_STATUS,

ERR_MSG = LC_MSG,

REQUEST_ID = LN_REQUEST_ID

WHERE SUPPLIER_NUMBER = LR_BANK.SUPPLIER_NUMBER

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 XX_SUPP_BANK_UPLOAD_NEW;

/

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

Manish Kumar