Oracle EBS (ERP): AP

Showing posts with label AP. Show all posts

Oracle R12 Vendor Invoice TDS with GST and PAN Query

SELECT

aia.invoice_num,

aia.creation_date invoice_creation_date,

jai.amount base_amount,

(

SELECT

vendor_name

FROM

ap_suppliers asa

WHERE

asa.vendor_id = aia.vendor_id

) vendor_name,

jai.tax_amount,

jai.actual_section_code tds_section_code--,jai.TAX_CATEGORY_ID,jai.INVOICE_ID,jai.INVOICE_DISTRIBUTION_ID,jai.ACTUAL_TAX_ID

jai.tax_type,

(

SELECT

rate_percentage

FROM

jai_tax_rate_details_v

WHERE

effective_to IS NULL

AND tax_rate_id = jai.actual_tax_id

) tax_rate,

(

SELECT

registration_number

FROM

jai_party_reg_lines jprl,

jai_party_regs jpr

WHERE

registration_type_code = 'PAN'

AND jprl.party_reg_id = jpr.party_reg_id

AND party_id = aia.vendor_id

AND jprl.effective_to IS NULL

AND party_site_id = aia.vendor_site_id

) pan_number,

(

SELECT

registration_number

FROM

jai_party_reg_lines jprl,

jai_party_regs jpr

WHERE

jprl.registration_type_code IN ( 'GSTN', 'GSTIN' )

AND jprl.party_reg_id = jpr.party_reg_id

AND party_id = aia.vendor_id

AND effective_to IS NULL

AND party_site_id = aia.vendor_site_id

) gstin_number

FROM

jai_ap_wthld_inv_taxes jai,

ap_invoices_all aia

--,apps.ap_invoice_distributions_all aid

WHERE

jai.tax_line_no > 0

AND jai.invoice_id = aia.invoice_id

AND aia.cancelled_date IS NULL

AND apps.AP_INVOICES_PKG.GET_POSTING_STATUS( aia.invoice_id ) ='Y' --Accounted

AND trunc(aia.creation_date) BETWEEN '01-OCT-2023' AND '30-NOV-2023'

-- AND jai.INVOICE_DISTRIBUTION_ID = aid.INVOICE_DISTRIBUTION_ID

ORDER BY

aia.creation_date;

Oracle R12 Supplier Update API

R12: AP: New Supplier Update API's in Oracle Payables (Doc ID 1618099.1)

SET SERVEROUTPUT ON

DECLARE

lc_return_status VARCHAR2(2000);

ln_msg_count NUMBER;

ll_msg_data LONG;

Ln_Vendor_Id NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int NUMBER;

Ln_Party_Id NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

Ln_Vendor_Id := 7474;

Lrec_Vendor_Rec.vendor_name := 'JG_SUPP_DNU';

Lrec_Vendor_Rec.vendor_name_alt := 'JG_SUPP_DoNotUse';

Lrec_Vendor_Rec.end_date_active := '1-Oct-20';

Lrec_Vendor_Rec.enabled_flag := 'N';

ap_vendor_pub_pkg.update_vendor_public( p_api_version => 1,

x_return_status => lc_return_status,

x_msg_count => ln_msg_count,

x_msg_data => ll_msg_data,

p_vendor_rec => Lrec_Vendor_Rec,

p_Vendor_Id => Ln_Vendor_Id);

IF (lc_return_status <> 'S') THEN

IF ln_msg_count >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => ll_msg_data, p_msg_index_out => ln_message_int );

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

End If;

END IF;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

END;

COMMIT;

EXIT;

Oracle R12 Suppler Extract Query with Bank PAN GST details

--Oracle R12 Vendor Extract with Bank PAN GST

SELECT

(

SELECT

hou.name

FROM

apps.hr_operating_units hou

WHERE

1 = 1

AND hou.organization_id = aps.org_id

) ou_name,

ass.segment1 supplier_number,

ass.vendor_type_lookup_code supplier_type,

ass.vendor_name supplier_name,

ass.vendor_name_alt supplier_name_alt,

decode(ass.end_date_active, NULL, 'ACTIVE', 'IN ACTIVE') "Supplier active code",

aps.vendor_site_code supplier_site_code,

aps.address_line1,

aps.address_line2,

aps.address_line3,

aps.address_line4,

aps.city,

aps.state,

aps.zip post_code,

aps.pay_group_lookup_code,

iepa.default_payment_method_code site_payment_method,

t.name site_terms_name,

branch.bank_name "bank_name",

branch.bank_branch_name bank_branch_name,

accts.bank_account_name bank_account_name,

accts.bank_account_num bank_account_number,

branch.eft_swift_code ifsc_code,

(

SELECT

registration_number

FROM

jai_party_reg_lines jprl,

jai_party_regs jpr

WHERE jprl.EFFECTIVE_TO is null

AND registration_type_code = 'PAN'

AND jprl.party_reg_id = jpr.party_reg_id

AND party_id = aps.vendor_id

AND party_site_id = aps.vendor_site_id

) pan_number,

(

SELECT

registration_number

FROM

jai_party_reg_lines jprl,

jai_party_regs jpr

WHERE jprl.EFFECTIVE_TO is null

AND jprl.registration_type_code IN ( 'GSTN', 'GSTIN' )

AND jprl.party_reg_id = jpr.party_reg_id

AND party_id = aps.vendor_id

AND party_site_id = aps.vendor_site_id

) gstin_number,

uses.order_of_preference,

uses.end_date bank_end_date

FROM

apps.ap_supplier_sites_all aps,

apps.ap_suppliers ass,

apps.ap_terms t,

apps.iby_external_payees_all iepa,

apps.iby_pmt_instr_uses_all uses,

apps.iby_account_owners owners,

apps.iby_ext_bank_accounts accts,

apps.ce_bank_branches_v branch

WHERE

1 = 1--Aps.ORG_ID=82

AND ass.end_date_active IS NULL

AND aps.inactive_date IS NULL

AND aps.terms_id = t.term_id

--AND ass.creation_date like sysdate

AND ass.vendor_id = aps.vendor_id

AND owners.ext_bank_account_id = accts.ext_bank_account_id (+)

AND owners.ext_bank_account_id (+) = uses.instrument_id

AND iepa.ext_payee_id = uses.ext_pmt_party_id (+)

AND iepa.payee_party_id = owners.account_owner_party_id (+)

--and uses.end_date is null

--AND uses.END_DATE is not null

AND iepa.supplier_site_id (+) = aps.vendor_site_id

AND branch.branch_party_id (+) = accts.branch_id

AND ass.vendor_type_lookup_code = 'VENDOR' --IN ('VENDOR', 'LANDLORD')

Oracle R12 AP Invoice Cancel List with PO GRN

--AP Invoice Cancel List with PO number and GRN

SELECT

aps.vendor_name,

assa.vendor_site_code,

aia.invoice_num,

aia.invoice_currency_code,

aia.invoice_date,

aia.invoice_type_lookup_code,

aia.description,

aia.invoice_amount,

pha.segment1 po_number,

rsh.receipt_num,

rt.primary_quantity rcv_qty,

rt.transaction_type,

fu.user_name "Cancelled By"

FROM

ap_invoices_all aia,

ap_invoice_lines_all ail,

rcv_transactions rt,

rcv_shipment_headers rsh,

po_distributions_all pda,

po_headers_all pha,

ap_suppliers aps,

ap_supplier_sites_all assa,

fnd_user fu

WHERE 1=1

AND aia.cancelled_date IS NOT NULL

AND aia.cancelled_by IS NOT NULL

AND ail.invoice_id = aia.invoice_id

AND ail.rcv_transaction_id (+) = rt.transaction_id

AND rt.shipment_header_id = rsh.shipment_header_id

AND pda.po_header_id = pha.po_header_id

AND pda.po_distribution_id = rt.po_distribution_id

AND aia.vendor_id = aps.vendor_id

AND aps.vendor_id = assa.vendor_id

AND aia.vendor_site_id = assa.vendor_site_id

AND aia.cancelled_by = fu.user_id

ORDER BY

aps.vendor_name

Oracle R12 AP Payment status Transmitte script

-- Find the payment_instruction_id form below script

SELECT

payment_instruction_id PaymentInstructionID,

payment_instruction_status,

payments_complete_code

FROM

iby_pay_instructions_all

WHERE

pay_admin_assigned_ref_code = &PPR_Number;

----Payment Transmitted script below

SET SERVEROUTPUT ON SIZE 1000000;

SET LINESIZE 2000;

DECLARE

l_return_status VARCHAR2(1000);

l_instr_id NUMBER;

l_instr_status VARCHAR2(240);

l_completion_code VARCHAR2(240);

l_msg_index_out NUMBER;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

l_instr_id := &PaymentInstructionID;

DBMS_OUTPUT.PUT_LINE('Payment instruction ID : '|| l_instr_id);

SELECT payment_instruction_status,

payments_complete_code

INTO l_instr_status,

l_completion_code

FROM iby_pay_instructions_all

WHERE payment_instruction_id = l_instr_id;

IF (NVL(l_completion_code,'NO') <> 'YES') THEN

DBMS_OUTPUT.PUT_LINE('Payment instruction status : '|| l_instr_status);

DBMS_OUTPUT.PUT_LINE('Payment instruction completion code : '|| l_completion_code);

DBMS_OUTPUT.PUT_LINE('Attempting to unlock pmt instruction id: '|| l_instr_id);

IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity( l_instr_id, 'PAYMENT_INSTRUCTION', l_return_status );

DBMS_OUTPUT.PUT_LINE('Finished unlocking pmt instruction id: '|| l_instr_id);

DBMS_OUTPUT.PUT_LINE('Attempting to terminate pmt instruction id: '|| l_instr_id);

IBY_DISBURSE_UI_API_PUB_PKG.terminate_pmt_instruction( l_instr_id, l_instr_status, l_return_status );

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN

DBMS_OUTPUT.PUT_LINE('ERROR: API return status: ' || l_return_status );

DBMS_OUTPUT.PUT_LINE('ERROR: API call failed');

DBMS_OUTPUT.PUT_LINE('Rolling back changes ...');

ROLLBACK;

ELSE

DBMS_OUTPUT.PUT_LINE('INFO: API return status: ' || l_return_status );

DBMS_OUTPUT.PUT_LINE('INFO: API call success');

DBMS_OUTPUT.PUT_LINE('Committing ...');

COMMIT;

END IF;

ELSE

DBMS_OUTPUT.PUT_LINE('Payment instruction status : '|| l_instr_status);

DBMS_OUTPUT.PUT_LINE('Payment instruction completion code : '|| l_completion_code);

DBMS_OUTPUT.PUT_LINE('ERROR: Payment instruction cannot be terminated since it is already complete');

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('ERROR: Payment instruction not found !');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR: Exception occured when executing termination script !');

DBMS_OUTPUT.PUT_LINE('SQLCODE is: ' || SQLCODE);

DBMS_OUTPUT.PUT_LINE('SQLERRM is: ' || SQLERRM);

END;

EXIT;

Error: One or More Transformations Used by the Matching Rule Have Not Been Staged

Issue is not create a Supplier

Error: One or More Transformations Used by the Matching Rule Have Not Been Staged

Reslove:

Run Concurrent Program: DQM Staging Program

From this Responsibility: Trading Community Manager

Parameter on Staging Command: STAGE_ALL_DATA

Oracle R12 Supplier Site update API

---Supplier Site update API

DECLARE

p_api_version NUMBER;

p_init_msg_list VARCHAR2 (200);

p_commit VARCHAR2 (200);

p_validation_level NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

lr_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

lr_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;

p_vendor_site_id NUMBER;

p_calling_prog VARCHAR2 (200);

BEGIN

-- Initialize apps session

--user_id, resp_id, resp_app_id

fnd_global.apps_initialize (0, 7000, 200);

mo_global.init ('SQLAP');

fnd_client_info.set_org_context (261);

-- Assign Basic Values

p_api_version := 1.0;

p_init_msg_list := fnd_api.g_true;

p_commit := fnd_api.g_true;

p_validation_level := fnd_api.g_valid_level_full;

p_vendor_site_id := 121039; -- to be end dated

p_calling_prog := 'XXCUSTOM';

BEGIN

SELECT *

INTO lr_existing_vendor_site_rec

FROM ap_supplier_sites_all assa

WHERE assa.vendor_site_id = p_vendor_site_id;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'Unable to derive the supplier site information for site id:'

|| p_vendor_site_id);

END;

-- Assign Vendor Site Details

lr_vendor_site_rec.vendor_site_id := lr_existing_vendor_site_rec.vendor_site_id;

lr_vendor_site_rec.last_update_date := SYSDATE;

lr_vendor_site_rec.last_updated_by := 0;

lr_vendor_site_rec.vendor_id := lr_existing_vendor_site_rec.vendor_id;

lr_vendor_site_rec.org_id := lr_existing_vendor_site_rec.org_id;

lr_vendor_site_rec.vendor_site_code := 'Sec. 194(Q)';

--lr_vendor_site_rec.inactive_date := SYSDATE;

AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE (

p_api_version => p_api_version,

p_init_msg_list => p_init_msg_list,

p_commit => p_commit,

p_validation_level => p_validation_level,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_vendor_site_rec => lr_vendor_site_rec,

p_vendor_site_id => p_vendor_site_id,

p_calling_prog => p_calling_prog);

DBMS_OUTPUT.put_line ('RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line ('MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line ('MSG_DATA = ' || x_msg_data);

END;

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