Oracle EBS (ERP): AP

Showing posts with label AP. Show all posts

#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

Oracle R12 Vendor Details Query

----Oracle R12 Supplier name and Address

SELECT     (Select Name from hr_operating_units where organization_id=aps.ORG_ID) OU_NAME,
           ap.segment1 vendor_code,
           ap.vendor_name,
           aps.vendor_site_code vendor_Site_Code,
           ap.VENDOR_TYPE_LOOKUP_CODE Type_Code,
           aps.address_line1,
           aps.address_line2,
           aps.address_line3,
           aps.city,
           aps.state,
           aps.zip,
           aps.Country,
           apc.AREA_CODE,
           apc.PHONE,
           apc.FAX_AREA_CODE,
           apc.FAX,
           apc.EMAIL_ADDRESS
    FROM   ap_suppliers ap,
           ap_supplier_sites_all aps,
           ap_supplier_contacts apc
--           hz_party_sites hps,
--           zx_party_tax_profile zp
--           zx_registrations zr
   WHERE       ap.vendor_id = aps.vendor_id
           AND apc.VENDOR_CONTACT_ID(+) = ap.VENDOR_ID
AND NVL(ap.VENDOR_TYPE_LOOKUP_CODE,'NA') <> 'EMPLOYEE'
           AND aps.vendor_site_code NOT IN 'OFFICE'
           AND ap.enabled_flag ='Y'
--           AND hps.PARTY_SITE_ID = aps.PARTY_SITE_ID
--           AND hps.party_id = zp.party_id
--           AND zp.PARTY_TYPE_CODE = 'THIRD_PARTY'
--           AND PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
--           AND zr.party_tax_profile_id(+)= zp.party_tax_profile_id
--           AND ap.segment1='2406'
ORDER BY   ap.segment1, ap.vendor_name

Query of Calculate Balance Owed Oracle Payables

SELECT   (SELECT NAME
            FROM hr_operating_units
           WHERE organization_id = aia.org_id) operating_unit_name,
         (SELECT segment1
            FROM ap_suppliers
           WHERE vendor_id = aia.vendor_id) vendor_code,
         (SELECT vendor_name
            FROM ap_suppliers
           WHERE vendor_id = aia.vendor_id) vendor_name,
         SUM (NVL (ps.gross_amount, 0)) balance,
         SUM (NVL (ps.amount_remaining, 0)) unpaid_amount,
         COUNT (aia.invoice_num) number_of_invoices
    FROM ap_invoices_all aia,
         ap_payment_schedules_all ps,
         ap_supplier_sites_all vs
   WHERE 1 = 1
     AND ps.amount_remaining != 0
     AND ps.invoice_id = aia.invoice_id
     AND NVL (aia.payment_status_flag, 'N') != 'Y'
     AND aia.cancelled_date IS NULL
     AND vs.vendor_site_id = aia.vendor_site_id
     AND NOT (    aia.invoice_type_lookup_code = 'PREPAYMENT'
              AND NVL (aia.payment_status_flag, 'N') = 'N'
             )
     AND aia.org_id = NVL (:p_org_id, aia.org_id)
     AND aia.vendor_id = NVL (:p_vendor_id, aia.vendor_id)
     AND aia.vendor_site_id = NVL (:p_vendor_site_id, aia.vendor_site_id)
GROUP BY aia.org_id, aia.vendor_id
ORDER BY vendor_code

Find the Operating Unit with AR and AP and Company Code and Location

---Find the Operating Unit with AR and AP and Company code and Location

SELECT DISTINCT hrl.country,
                hroutl_bg.NAME bg,
                hroutl_bg.organization_id,
                lep.legal_entity_id,
                lep.NAME legal_entity,
                hroutl_ou.NAME ou_name,
                hroutl_ou.organization_id org_id,
                hrl.location_id,
                hrl.location_code,
                glev.flex_segment_value company_code,
                (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = arsp.org_id) ar_ou,
               (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = apsp.org_id) ap_ou
FROM xle_entity_profiles          lep,
       xle_registrations            reg,
       hr_locations_all             hrl,
       hz_parties                   hzp,
       fnd_territories_vl           ter,
       hr_operating_units           hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units        gloperatingunitseo,
       gl_legal_entities_bsvs       glev,
       ar_system_parameters_all     arsp,
       ap_system_parameters_all     apsp
WHERE lep.transacting_entity_flag        = 'Y'
   AND lep.party_id                       = hzp.party_id
   AND lep.legal_entity_id                = reg.source_id
   AND reg.source_table                   = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id                    = reg.location_id
   AND reg.identifying_flag               = 'Y'
   AND ter.territory_code                 = hrl.country
   AND lep.legal_entity_id                = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id          = hro.business_group_id
   AND hroutl_ou.organization_id          = hro.organization_id
   AND glev.legal_entity_id               = lep.legal_entity_id
   AND hro.set_of_books_id = arsp.set_of_books_id(+)
   AND hro.organization_id = arsp.org_id(+)
   AND hro.set_of_books_id = apsp.set_of_books_id(+)
   AND hro.organization_id = apsp.org_id(+)
Order by company_code

How to Link between RTN to Credit Memo in Payable

SELECT jat.dm_invoice_num
FROM jai_ap_tds_invoices jat,
       ap_invoices_all aia,
       ap_invoice_distributions_all aida
WHERE aia.invoice_id = aida.invoice_id
   AND jat.invoice_id = aia.invoice_id
   AND aida.invoice_distribution_id IN (
          SELECT aid.prepay_distribution_id
            FROM jai_ap_tds_thhold_trxs jtx, ap_invoice_distributions_all aid
           WHERE jtx.invoice_distribution_id = aid.invoice_distribution_id
             AND invoice_to_vendor_id = 516912) --- RTN Invoice ID

How To Upload Supplier Additional Information

--------------------------------1. Data Template for Supplier Additional Info :---------------------------

SL_NO

VENDOR_NAME

VENDOR_SITE_CODE

EXCISE_DUTY_REG_NO

EXCISE_DUTY_RANGE

EXCISE_DUTY_DIVISION

EXCISE_DUTY_COMM

EC_CODE

TAX_NAME

ST_REG_NO

CST_REG_NO

VAT_REG_NO

SERVICE_TAX_REGNO

PAN_NO

TAN_NO

WARD_NO

SECTION_CODE

TDS_VENDOR_TYPE_LOOKUP_CODE

VERIFY_FLAG

NVL_SITE_VERIFY_FLAG

ERROR_MESSAGE

---------------------------2. Create staging table :------------------------------------

CREATE TABLE XX_SUPPLIERS_ADDINFO
(
SL_NO NUMBER NOT NULL,
VENDOR_NAME VARCHAR2(240 BYTE) NOT NULL,
VENDOR_SITE_CODE VARCHAR2(15 BYTE) NOT NULL,
EXCISE_DUTY_REG_NO VARCHAR2(50 BYTE),
EXCISE_DUTY_RANGE VARCHAR2(50 BYTE),
EXCISE_DUTY_DIVISION VARCHAR2(50 BYTE),
EXCISE_DUTY_COMM VARCHAR2(50 BYTE),
EC_CODE VARCHAR2(50 BYTE),
TAX_NAME VARCHAR2(50 BYTE),
ST_REG_NO VARCHAR2(50 BYTE),
CST_REG_NO VARCHAR2(50 BYTE),
VAT_REG_NO VARCHAR2(50 BYTE),
SERVICE_TAX_REGNO VARCHAR2(50 BYTE),
PAN_NO VARCHAR2(30 BYTE),
TAN_NO VARCHAR2(30 BYTE),
WARD_NO VARCHAR2(30 BYTE),
SECTION_CODE VARCHAR2(30 BYTE),
TDS_VENDOR_TYPE_LOOKUP_CODE VARCHAR2(30 BYTE),
VERIFY_FLAG VARCHAR2(1 BYTE),
NVL_SITE_VERIFY_FLAG VARCHAR2(1 BYTE),
ERROR_MESSAGE VARCHAR2(100 BYTE)
)

---------------------------3. Create Procedure :------------------------------------

CREATE OR REPLACE PROCEDURE APPS.XXCONA_SUPPLER_ADDINFO(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
P_ORG_ID IN NUMBER
)
AS

/********************Suppliers Additional Information ***********************/

L_VERIFY_FLAG VARCHAR2(1);
L_ERROR_MESSAGE VARCHAR2(2500);
L_VENDOR_ID NUMBER(10);
L_VENDOR_SITE_ID NUMBER(10);
L_ORG_ID NUMBER(10);
L_USER_ID NUMBER(10);
L_VENDOR_TYPE VARCHAR2(50);
L_TAX_ID NUMBER(10);
L_SECTION_TYPE VARCHAR2(25);
L_SECTION_CODE VARCHAR2(30);

L_CNT_NVL_SITE NUMBER(3);
L_CNT_SITE NUMBER(3);

CURSOR C_ADDINFO IS
SELECT *
FROM XX_SUPPLIERS_ADDINFO
WHERE VERIFY_FLAG = 'N' ;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Upload Program Start');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Checking Start-----');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit-----'||P_ORG_ID);
FOR c1 IN C_ADDINFO
LOOP

l_verify_flag := 'Y';
l_error_message := null;
l_cnt_nvl_site := 0;
l_cnt_site := 0;

BEGIN
SELECT ORGANIZATION_ID INTO l_org_id
FROM HR_OPERATING_UNITS
WHERE ORGANIZATION_ID = P_ORG_ID; -- NAME like P_ORG_CODE;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Operating Unit is not valid...';
DBMS_OUTPUT.PUT_LINE ('Operating Unit is '||l_org_id);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit is '||l_org_id);
END;

l_user_id := APPS.FND_PROFILE.VALUE ('user_id');

BEGIN
SELECT VENDOR_ID,VENDOR_TYPE_LOOKUP_CODE
INTO l_vendor_id,L_VENDOR_TYPE
FROM APPS.AP_SUPPLIERS
WHERE TRIM(upper(vendor_name)) = trim(upper(c1.vendor_name));
EXCEPTION
WHEN OTHERS Then
l_verify_flag := 'N';
l_error_message := l_error_message||'Vendor Name is not valid...';
DBMS_OUTPUT.PUT_LINE ('Vendor ID '||l_vendor_id ||' Vendor Type '||l_vendor_type);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor ID '||l_vendor_id ||' Vendor Type '||l_vendor_type);
END;

BEGIN
SELECT VENDOR_SITE_ID INTO l_vendor_site_id
FROM AP_SUPPLIER_SITES_ALL
WHERE TRIM(upper(vendor_site_code)) = trim(upper(c1.vendor_site_code))
AND VENDOR_ID = l_vendor_id
AND ORG_ID = l_org_id;
EXCEPTION
WHEN OTHERS Then
l_verify_flag := 'N';
l_error_message := l_error_message||'Vendor Site not valid...';
DBMS_OUTPUT.PUT_LINE ('Vendor Site ID '||l_vendor_site_id );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Site ID '||l_vendor_site_id);
END;

BEGIN
SELECT TAX_ID INTO l_tax_id
FROM JAI_CMN_TAXES_ALL
WHERE upper(tax_name) = trim(upper(c1.tax_name))
AND org_id = l_org_id
AND nvl(end_date,sysdate) >= sysdate;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'TDS Tax is not Valid...';
DBMS_OUTPUT.PUT_LINE ('TAX ID '||l_tax_id );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'TAX ID '||l_tax_id);
END;

BEGIN
SELECT DISTINCT SECTION_TYPE, SECTION_CODE, VENDOR_TYPE_LOOKUP_CODE
INTO l_section_type, l_section_code, l_vendor_type
FROM JAI_AP_TDS_THHOLD_HDRS
WHERE TRIM(UPPER(SECTION_CODE)) = UPPER(TRIM(C1.SECTION_CODE))
AND TRIM(UPPER(VENDOR_TYPE_LOOKUP_CODE)) = UPPER(TRIM(C1.TDS_VENDOR_TYPE_LOOKUP_CODE))
AND EXCEPTION_SETUP_FLAG = 'N';
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Tds Section or TDS Vendor Type is not valid...';
DBMS_OUTPUT.PUT_LINE ('VENDOR_TYPE_LOOKUP_CODE '||l_vendor_type );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'VENDOR_TYPE_LOOKUP_CODE '||l_vendor_type);
END;

BEGIN
SELECT COUNT(*) INTO l_cnt_site
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_site_id = l_vendor_site_id
AND vendor_id = l_vendor_id ;
IF l_cnt_site > 0 then
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Site already existing in JAI_CMN_VENDOR_SITES...';
l_cnt_site := 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
DBMS_OUTPUT.PUT_LINE ('NO of Site already existing '||l_cnt_site||l_error_message );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Site already existing '||l_cnt_site||l_error_message );
END;

BEGIN
SELECT COUNT(*) INTO L_CNT_SITE
FROM JAI_AP_TDS_VENDOR_HDRS
WHERE VENDOR_SITE_ID = l_vendor_site_id
AND VENDOR_ID = l_vendor_id ;
IF L_CNT_SITE > 0 THEN
L_VERIFY_FLAG := 'N' ;
l_error_message := l_error_message||'Site already existing in JAI_AP_TDS_VENDOR_HDRS...';
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
DBMS_OUTPUT.PUT_LINE ('NO of Site already existing '||l_cnt_site||l_error_message );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Site already existing '||l_cnt_site||l_error_message );
END;

IF l_verify_flag <> 'N' THEN

BEGIN
SELECT COUNT(*) INTO L_CNT_NVL_SITE
FROM JAI_CMN_VENDOR_SITES
WHERE VENDOR_SITE_ID = 0
AND VENDOR_ID = l_vendor_id ;
EXCEPTION
WHEN OTHERS THEN
l_cnt_nvl_site := 0;

END;
DBMS_OUTPUT.PUT_LINE ('NO of Supplier upload '||L_CNT_NVL_SITE );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Supplier upload '||L_CNT_NVL_SITE);

IF l_cnt_nvl_site = 0 then

BEGIN
DBMS_OUTPUT.PUT_LINE ('Null Supplier site insert JAI_CMN_VENDOR_SITES ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Null Supplier site insert JAI_CMN_VENDOR_SITES');
INSERT INTO JAI_CMN_VENDOR_SITES
(
VENDOR_ID
,VENDOR_SITE_ID
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,approved_invoice_flag
,EXCISE_DUTY_RANGE
,EXCISE_DUTY_DIVISION
,EXCISE_DUTY_REG_NO
,cst_reg_no
,st_reg_no
,vat_reg_no
,SERVICE_TAX_REGNO
,EXCISE_DUTY_COMM
,EC_CODE
)
VALUES
(
l_vendor_id
,0
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,'Y'
,trim(c1.excise_duty_range)
,trim(c1.excise_duty_division)
,trim(c1.excise_duty_reg_no)
,trim(c1.CST_REG_NO)
,trim(c1.ST_REG_NO)
,trim(c1.VAT_REG_NO)
,trim(c1.SERVICE_TAX_REGNO)
,trim(c1.EXCISE_DUTY_COMM)
,NULL
) ;
UPDATE XX_SUPPLIERS_ADDINFO
SET nvl_site_verify_flag = 'Y'
WHERE vendor_name = c1.vendor_name
AND VENDOR_SITE_CODE = c1.VENDOR_SITE_CODE;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM ;
l_verify_flag := 'N';
DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is '||l_error_message);
UPDATE XX_SUPPLIERS_ADDINFO
SET nvl_site_verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = c1.vendor_name
AND vendor_site_code = c1.vendor_site_code;
END;

IF l_verify_flag <> 'N' THEN

BEGIN

INSERT INTO JAI_AP_TDS_VENDOR_HDRS
(
vendor_id
,vendor_site_id
,pan_no
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,section_type
,section_code
,confirm_pan_flag
,tds_vendor_type_lookup_code
,TAN_NO
)
VALUES
(
l_vendor_id
,0
,trim(c1.pan_no)
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,''
,''
,'Y'
,l_vendor_type
,trim(c1.TAN_NO)
) ;

UPDATE XX_SUPPLIERS_ADDINFO
set nvl_site_verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is '||l_error_message);
UPDATE XX_SUPPLIERS_ADDINFO
set nvl_site_verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;

END;

END IF;

END IF;

BEGIN

INSERT INTO JAI_CMN_VENDOR_SITES J
(
vendor_id
,vendor_site_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,approved_invoice_flag
,excise_duty_range
,excise_duty_division
,excise_duty_reg_no
,cst_reg_no
,st_reg_no
,vat_reg_no
,service_tax_regno
,EXCISE_DUTY_COMM
,EC_CODE
)
values
(
l_vendor_id
,l_vendor_site_id
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,'Y'
,trim(c1.excise_duty_range)
,trim(c1.excise_duty_division)
,trim(c1.excise_duty_reg_no)
,trim(c1.cst_reg_no)
,trim(c1.st_reg_no)
,trim(c1.vat_reg_no)
,trim(c1.service_tax_regno)
,trim(c1.EXCISE_DUTY_COMM)
,trim(c1.EC_CODE)
) ;

UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is '||l_error_message);
UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;

END;

BEGIN

insert into JAI_AP_TDS_VENDOR_HDRS
(
tax_id
,vendor_id
,vendor_site_id
,pan_no
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,section_type
,section_code
,confirm_pan_flag
,tds_vendor_type_lookup_code
,TAN_NO
)
values
(
l_tax_id
,l_vendor_id
,l_vendor_site_id
,trim(c1.pan_no)
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,l_section_type
,l_section_code
,'Y'
,l_vendor_type
,trim(c1.TAN_NO)
) ;

UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is '||l_error_message);
UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;

END;

ELSE

DBMS_OUTPUT.PUT_LINE ('NO of Supplier upload '||L_CNT_NVL_SITE );
UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;

END IF;

Commit;

END LOOP;
DBMS_OUTPUT.PUT_LINE ('Upload Program End');

END XXCONA_SUPPLER_ADDINFO;
/

Supplier Bank Interface at Site Level Oracle Apps

Oracle R12 Vendor Details Query

Query of Calculate Balance Owed Oracle Payables

Find the Operating Unit with AR and AP and Company Code and Location

How to Link between RTN to Credit Memo in Payable

How To Upload Supplier Additional Information