CREATE OR REPLACE PACKAGE BODY APPS.xx_emp_as_spplr_crtn_pkg
AS
/*
API to create employee as supplier in R12
Created By Pavankumar M. Bondage bondagepm.erp@gmail.com
*/
g_business_group_id NUMBER := 81;
g_conc_request_id number :=fnd_global.conc_request_id;
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_emp_id IN NUMBER,
p_org_id IN NUMBER,
p_vendor_site_code IN VARCHAR2
)
AS
--CURSOR c_emp_info(p_emp_id IN VARCHAR2) IS
CURSOR c_empdet (p_emp_id IN VARCHAR2)
IS
SELECT papf.full_name, papf.person_id, papf.employee_number
FROM per_all_people_f papf
WHERE papf.business_group_id = g_business_group_id
AND papf.person_id = p_emp_id;
CURSOR c_hou
IS
SELECT hou.NAME
FROM hr_operating_units hou
WHERE hou.organization_id = p_org_id;
CURSOR c_vendor (v_vendor_name IN VARCHAR2)
IS
SELECT pv.vendor_id, pv.vendor_type_lookup_code
FROM po_vendors pv
WHERE UPPER (vendor_name) = UPPER (v_vendor_name);
CURSOR c_vensite (p_vendor_id IN NUMBER, p_org_id IN NUMBER)
IS
SELECT COUNT (1)
FROM ap_supplier_sites_all
WHERE vendor_id = p_vendor_id
AND UPPER (vendor_site_code) =
UPPER
(p_vendor_site_code)
-- IN ('HOME', 'OFFICE', 'PROVISIONAL')
AND org_id = p_org_id;
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
ext_payee_rec iby_disbursement_setup_pub.external_payee_rec_type;
l_return_status VARCHAR2 (10);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
v_empname VARCHAR2 (50);
v_empid NUMBER;
p_count NUMBER;
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
l_liability_id NUMBER;
l_prepay_id NUMBER;
v_error VARCHAR2 (1000);
v_org_name VARCHAR2 (50);
l_vendor_type_code VARCHAR2 (20);
v_employee_number VARCHAR2 (20);
ln_sup_site_cnt NUMBER;
BEGIN
v_error := 'S';
xx_emp_as_spplr_crtn_pkg.output ('***************Employee Type Supplier Creation request Log Starts***********************');
OPEN c_hou;
FETCH c_hou
INTO v_org_name;
CLOSE c_hou;
xx_emp_as_spplr_crtn_pkg.output (' Organization : ' || v_org_name);
OPEN c_empdet (p_emp_id);
FETCH c_empdet
INTO v_empname, v_empid, v_employee_number;
CLOSE c_empdet;
IF v_empname IS NULL
THEN
xx_emp_as_spplr_crtn_pkg.output (' Employee does not exist for Employee Number#' || v_employee_number);
ELSE
OPEN c_vendor (v_empname);
FETCH c_vendor
INTO l_vendor_id, l_vendor_type_code;
CLOSE c_vendor;
IF l_vendor_id IS NOT NULL
THEN
v_error := 'Supp_Exists';
xx_emp_as_spplr_crtn_pkg.output ( ' Supplier Already Exists with Vendor Id#'|| l_vendor_id|| ' and Type '|| l_vendor_type_code);
OPEN c_vensite (l_vendor_id, p_org_id);
FETCH c_vensite
INTO ln_sup_site_cnt;
CLOSE c_vensite;
/* IF ln_sup_site_cnt = 3
THEN
retcode := '2';
errbuf :=
' Error - Supplier and Sites have already been created. ';
END IF;*/
END IF;
IF v_error <> 'Supp_Exists'
THEN
xx_emp_as_spplr_crtn_pkg.output
(' Creating Employee Type Supplier for :'
|| v_empname
);
l_vendor_rec.vendor_name := v_empname;
l_vendor_rec.vendor_type_lookup_code := 'EMPLOYEE';
l_vendor_rec.employee_id := v_empid;
xx_emp_as_spplr_crtn_pkg.output ('Calling Vendor Creation API...');
pos_vendor_pub_pkg.create_vendor
(p_vendor_rec => l_vendor_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_id => l_vendor_id,
x_party_id => l_party_id
);
IF (l_return_status <> 'S')
THEN
xx_emp_as_spplr_crtn_pkg.output ('Error in Supplier Creation API...');
IF (l_msg_count = 1)
THEN
xx_emp_as_spplr_crtn_pkg.output ('x_msg_data '|| l_msg_data);
ELSIF (l_msg_count > 1)
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF (l_msg_data IS NULL)
THEN
EXIT;
END IF;
xx_emp_as_spplr_crtn_pkg.output (' Error Message'|| p_count
|| ' ---'
|| l_msg_data);
END LOOP;
END IF;
ELSE
xx_emp_as_spplr_crtn_pkg.output(' Successfully Cretaed Supplier ');
END IF;
END IF;
IF l_vendor_id IS NOT NULL AND NVL (ln_sup_site_cnt, 0) =0
THEN
BEGIN
IF v_error <> 'E'
THEN
l_vendor_site_rec.vendor_id := l_vendor_id;
l_vendor_site_rec.vendor_site_code :=
UPPER (p_vendor_site_code);
--'OFFICE';
l_vendor_site_rec.org_id := p_org_id;
l_vendor_site_rec.accts_pay_code_combination_id := 1016;
--l_liability_id;
l_vendor_site_rec.prepay_code_combination_id := 1017;
--l_prepay_id;
l_vendor_site_rec.terms_date_basis := 'Invoice';
ext_payee_rec.default_pmt_method := 'CHECK';
l_vendor_site_rec.ext_payee_rec.default_pmt_method :=
'CHECK';
xx_emp_as_spplr_crtn_pkg.output ('Calling Vendor Site Creation API...');
pos_vendor_pub_pkg.create_vendor_site
(p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id
);
IF (l_return_status <> 'S')
THEN
xx_emp_as_spplr_crtn_pkg.output( ' Error in Creating Vendor Site..');
retcode := '1';
errbuf := ' Error in Supplier Site Creation-';
IF (l_msg_count = 1)
THEN
xx_emp_as_spplr_crtn_pkg.output( ' x_msg_data '||l_msg_data);
ELSIF (l_msg_count > 1)
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next,
fnd_api.g_false
);
IF (l_msg_data IS NULL)
THEN
EXIT;
END IF;
xx_emp_as_spplr_crtn_pkg.output(' Error Message'|| p_count|| ' ---'|| l_msg_data);
END LOOP;
END IF;
ELSE
xx_emp_as_spplr_crtn_pkg.output(' Successfully Created Supplier Site');
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
xx_emp_as_spplr_crtn_pkg.output('SQL Error :'||dbms_utility.format_error_backtrace||SQLERRM) ;
END;
END IF;
END IF;
--=== adding bank details--@@ 03-12-19/--
DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_ext_bank_acct_rec apps.iby_ext_bankacct_pub.extbankacct_rec_type;
x_acct_id NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
x_response apps.iby_fndcpt_common_pub.result_rec_type;
lv_bank_name VARCHAR2 (100);
lv_bank_branch VARCHAR2 (100);
LV_IBAN_NUM varchar2(150);
--lv_bank_acct_num VARCHAR2 (100);
lv_bank_acct_name VARCHAR2 (100);
lv_vendor_name VARCHAR2 (100);
--ln_bank_id NUMBER;
ln_branch_id NUMBER;
lv_bank_home_country_code VARCHAR2 (100);
ln_acct_owner_party_id NUMBER;
l_msg VARCHAR2 (200);
p_person_id NUMBER := p_emp_id;--76526;
lv_bank_crt_error_msg varchar2(2000):=null;
l_lngth_of_iban number:=0;
lv_bank_party_id number :=0;
BEGIN
xx_emp_as_spplr_crtn_pkg.output('Bank Creation API code Begins...');
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'Starts','Bank Creation API code Begins...',substr(SQLERRM,1,399));
-- Initialize apps session
fnd_global.apps_initialize (0, 50744, 200);
mo_global.init ('SQLAP');
fnd_client_info.set_org_context (81);
/*
lv_bank_name := 'Doha Bank'; --'TEST SUPPLIER BANK';
lv_bank_branch := 'Main Branch'; --'TEST BRANCH';
lv_bank_acct_num := '123456789';
lv_bank_acct_name := 'TEST SUPP BANK ACCT';
lv_vendor_name := 'Ajay G Singh'; --'TEST SUPPLIER';
-- Nullify temp variables
ln_bank_id := NULL; */
ln_branch_id := NULL;
lv_bank_home_country_code := NULL;
ln_acct_owner_party_id := NULL;
xx_emp_as_spplr_crtn_pkg.output
('..rssd');
begin
SELECT DISTINCT DECODE
(hl.meaning,
'HSBC, Qatar', '4152',
'Qatar National Bank (QNB)', '25494',
'Commercial Bank of Qatar (CBQ)', '4048',
'Doha Bank', '4052',
'International Bank of Qatar (IBQ)', '4055',
'Qatar International Islamic Bank (QIIB)', '4356',
'NOT_DEFINED'
) bank_party_id, --bank_name,
-- pea.segment2 branch_name,
-- NVL (pea.segment3, 'Blank') account_type,
pea.segment4 IBAN_NO
into lv_bank_party_id, LV_IBAN_NUM
FROM per_all_people_f papf,
per_all_assignments_f paaf,
hr_lookups hl,
pay_external_accounts pea,
pay_personal_payment_methods_f pp
WHERE 1 = 1
AND pp.external_account_id = pea.external_account_id
AND papf.person_id = paaf.person_id
AND pp.assignment_id = paaf.assignment_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
AND pp.effective_end_date
AND papf.current_employee_flag = 'Y'
AND paaf.primary_flag = 'Y'
AND papf.person_id = p_emp_id
AND hl.lookup_type = 'XXNEBRAS_BANK_SWIFT_CODE'
AND SUBSTR (hl.lookup_code, 1, 4) = SUBSTR (pea.segment4, 5, 4)
AND pea.segment4 IS NOT NULL;
exception when others then
lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Not found Employees bank account details in Employee-Assignment-PayMethod';
end;
--Get person_id
BEGIN
select aps.party_id into ln_acct_owner_party_id from ap_suppliers aps
where aps.vendor_id=l_vendor_id
and rownum <2;
exception when no_data_found then
ln_acct_owner_party_id:=l_party_id;
when OTHERS then
-- ln_acct_owner_party_id := null;
lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Not found The Suppliers PARTY_ID';
END;
begin
SELECT papf.full_name into lv_bank_acct_name
FROM per_all_people_f papf
WHERE papf.business_group_id = g_business_group_id
AND papf.person_id = p_emp_id
and rownum <2;
exception when no_data_found then
lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Not found lv_bank_acct_name';
end;
-- Get bank details
/* BEGIN
SELECT distinct bank_home_country, bank_party_id --, pk_id
INTO lv_bank_home_country_code, ln_bank_id--, ln_branch_id
FROM ce_bank_branches_v
WHERE bank_name like '%' || substr(lv_bank_name,1,10) || '%'; --lv_bank_name
-- AND bank_branch_name = lv_bank_branch;
EXCEPTION
WHEN OTHERS
THEN
lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Not found Bank and Branch details w.r.t. employees bank details in Employee-Master';
xx_emp_as_spplr_crtn_pkg.output
( 'Unable to derive Bank and Branch details'
|| SQLERRM
);
END;*/
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'lv_bank_party_id - LV_IBAN_NUM - ln_acct_owner_party_id - lv_bank_home_country_code :=>',
to_char(lv_bank_party_id)||' - '||LV_IBAN_NUM||' - '||to_char(ln_acct_owner_party_id)||' - '||to_char(lv_bank_home_country_code),substr(SQLERRM,1,399));
-- Get account owner id
/* BEGIN
SELECT party_id
INTO ln_acct_owner_party_id
FROM hz_parties
WHERE
(party_name = lv_vendor_name or party_id=76526)
;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Unable to derive Account Owner Infor' ||
SQLERRM);
END;*/
--Bank Branch Validation
BEGIN
select length(trim(LV_IBAN_NUM)) into l_lngth_of_iban
from dual;
if l_lngth_of_iban <> 29 --QA--Qatar
then
lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- IBAN No not valid for QATAR';
end if;
if nvl(lv_bank_party_id,0)>0 then
begin
SELECT cbbv.branch_party_id--, cbbv.bank_branch_name
into ln_branch_id
FROM ce_bank_branches_v cbbv
WHERE cbbv.bank_party_id = lv_bank_party_id AND ROWNUM < 2;
exception when others then
ln_branch_id:=0;
lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Bank Branch Not Found';
end;
end if;
END;
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'BeforeBankAPI Call lv_bank_crt_error_msg',lv_bank_crt_error_msg,substr(SQLERRM,1,399));
if lv_bank_crt_error_msg is null then
-- Assign API parameters
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_ext_bank_acct_rec.country_code := 'QA';--lv_bank_home_country_code;
p_ext_bank_acct_rec.bank_id := lv_bank_party_id;-- ln_bank_id; -- bank_id
p_ext_bank_acct_rec.branch_id := ln_branch_id; -- branch_id--@@ commented
p_ext_bank_acct_rec.acct_owner_party_id := NVL(ln_acct_owner_party_id,l_party_id);--party_id in ap_suupliers;
p_ext_bank_acct_rec.bank_account_name :=lv_bank_acct_name;--'Employees Bank';-- lv_bank_acct_name; --@@ commented
p_ext_bank_acct_rec.bank_account_num := LV_IBAN_NUM;
p_ext_bank_acct_rec.currency := 'QAR';-- bank_acct_currency
p_ext_bank_acct_rec.start_date := SYSDATE;--Employee Effective Start date
p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';
p_ext_bank_acct_rec.iban:=LV_IBAN_NUM;
-- p_ext_bank_acct_rec.PAYMENT_FACTOR_FLAG:='N';
xx_emp_as_spplr_crtn_pkg.output('calling Bank creation API');
iby_ext_bankacct_pub.create_ext_bank_acct
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_ext_bank_acct_rec => p_ext_bank_acct_rec,
x_acct_id => x_acct_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response
);
xx_emp_as_spplr_crtn_pkg.output ('X_ACCT_ID = ' || x_acct_id);
xx_emp_as_spplr_crtn_pkg.output ('X_RETURN_STATUS = ' || x_return_status);
xx_emp_as_spplr_crtn_pkg.output ('X_MSG_COUNT = ' || x_msg_count);
xx_emp_as_spplr_crtn_pkg.output ('X_MSG_DATA = ' || x_msg_data);
xx_emp_as_spplr_crtn_pkg.output ( 'X_RESPONSE.Result_Code = '
|| x_response.result_code
);
xx_emp_as_spplr_crtn_pkg.output ( 'X_RESPONSE.Result_Category = '
|| x_response.result_category
);
xx_emp_as_spplr_crtn_pkg.output ( 'X_RESPONSE.Result_Message = '
|| x_response.result_message
);
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'01','X_ACCT_ID - X_RETURN_STATUS - '||to_char(X_ACCT_ID)||' - '||X_RETURN_STATUS,substr(SQLERRM,1,399));
IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
l_msg :=
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false
);
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'BankAPIError',l_msg,substr(SQLERRM,1,399));
xx_emp_as_spplr_crtn_pkg.output ('The Bank Creation API call failed with error '
|| l_msg
);
END LOOP;
ELSE
IF x_acct_id is not null then
xx_emp_as_spplr_crtn_pkg.SET_PAYEE_INSTR_ASSIGNMENT_P(l_vendor_id,l_vendor_site_id,p_org_id,x_acct_id);
end if;
xx_emp_as_spplr_crtn_pkg.output ('The Bank Creation API call ended with SUCESSS status');
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'Ends','The Bank Creation API call ended with SUCESSS status',substr(SQLERRM,1,399));
END IF;
COMMIT;
end if;--lv_bank_crt_error_msg is null then Ends
EXCEPTION
WHEN OTHERS
THEN
-- ROLLBACK;
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'Ends-Bank-Exception','Bank Creation API excception',substr(SQLERRM,1,399));
xx_emp_as_spplr_crtn_pkg.output ('Bank Creation API excception ' || SQLERRM);
END;
--=====end adding bank details
xx_emp_as_spplr_crtn_pkg.output ('*************** END ***********************');
EXCEPTION
WHEN OTHERS
THEN
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'Ends-Main-Exception','Main excception',substr(SQLERRM,1,399));
xx_emp_as_spplr_crtn_pkg.output (' Error in Main- '||dbms_utility.format_error_backtrace||SQLERRM );
retcode := '2';
errbuf := ' Error in Main- ' || SQLERRM;
END main;
PROCEDURE LOG (p_msg VARCHAR2)
IS
BEGIN
IF fnd_global.conc_login_id = -1
THEN
-- NULL;
dbms_output.put_line(p_msg);
ELSE
fnd_file.put_line (fnd_file.LOG, p_msg);
END IF;
END LOG;
PROCEDURE output (p_msg VARCHAR2)
IS
BEGIN
IF fnd_global.conc_login_id = -1
THEN
dbms_output.put_line(p_msg);
-- NULL;
ELSE
fnd_file.put_line (fnd_file.output, p_msg);
END IF;
END output;
procedure SET_PAYEE_INSTR_ASSIGNMENT_P
(P_vendor_id in number,
P_vendor_site_id in number,
p_org_id in number,
P_ext_bank_account_id in NUMBER
)IS
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
p_payee apps.iby_disbursement_setup_pub.payeecontext_rec_type;
p_assignment_attribs apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
lr_ext_bank_acct_dtl iby_ext_bank_accounts%ROWTYPE;
x_assign_id NUMBER;
x_response apps.iby_fndcpt_common_pub.result_rec_type;
lv_vendor_site_code VARCHAR2 (100);
lv_vendor_name VARCHAR2 (100);
lv_bank_acct_name VARCHAR2 (100);
lv_supp_site_id VARCHAR2 (100);
lv_supp_party_site_id VARCHAR2 (100);
lv_acct_owner_party_id VARCHAR2 (100);
lv_org_id VARCHAR2 (100);
l_msg VARCHAR2 (200);
lv_error_msg varchar2(2500):=NULL;
BEGIN
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.SET_PAYEE_INSTR_ASSIGNMENT_P',g_conc_request_id,'Enterred','P_vendor_id - P_vendor_site_id - p_org_id - P_ext_bank_account_id'
||P_vendor_id||' - '||P_vendor_site_id||' - '||p_org_id||' - '||P_ext_bank_account_id,substr(SQLERRM,1,399));
-- Initialize apps session
fnd_global.apps_initialize (0, 50744, 200);
mo_global.init ('SQLAP');
fnd_client_info.set_org_context (81);
-- Input values
-- lv_vendor_site_code := 'TEST SUPPLIER SITE';
-- lv_vendor_name := 'TEST SUPPLIER';
-- lv_bank_acct_name := 'TEST SUPP BANK ACCT';
-- Assign API parameters
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_true;
-- get ext bank account details
BEGIN
DBMS_OUTPUT.put_line('******************Starts*******************');
SELECT *
INTO lr_ext_bank_acct_dtl
FROM iby_ext_bank_accounts
WHERE 1=1
-- AND bank_account_name = lv_bank_acct_name
and ext_bank_account_id=P_ext_bank_account_id
and rownum <2;
EXCEPTION
WHEN OTHERS
THEN
lv_error_msg:=lv_error_msg||' - Unable to derive the external bank details';
DBMS_OUTPUT.put_line
( 'Unable to derive the external bank details:'
|| SQLERRM
);
END;
-- get supplier details
BEGIN
SELECT assa.vendor_site_id, assa.party_site_id, aps.party_id,
assa.org_id
INTO lv_supp_site_id, lv_supp_party_site_id, lv_acct_owner_party_id,
lv_org_id
FROM ap_suppliers aps, ap_supplier_sites_all assa
WHERE aps.vendor_id = assa.vendor_id
-- AND aps.vendor_name = lv_vendor_name
-- AND assa.vendor_site_code = lv_vendor_site_code
and ( assa.VENDOR_SITE_ID=P_vendor_site_id OR aps.vendor_id= P_vendor_id)
and rownum <2;
EXCEPTION
WHEN OTHERS
THEN
lv_error_msg:=lv_error_msg||' - Error at Get supplier party_id';
DBMS_OUTPUT.put_line
( 'Error- Get supp_site_id and supp_party_site_id'
|| SQLCODE
|| SQLERRM
);
END;
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.SET_PAYEE_INSTR_ASSIGNMENT_P',g_conc_request_id,'lv_error_msg :=> ',substr(lv_error_msg,1,399),substr(SQLERRM,1,399));
if lv_error_msg is null then
-- Assign payee values
p_payee.supplier_site_id := null;--lv_supp_site_id;
p_payee.party_id := lv_acct_owner_party_id;
p_payee.party_site_id :=null;-- lv_supp_party_site_id;
p_payee.payment_function := 'PAYABLES_DISB';
p_payee.org_id := null;--lv_org_id;
p_payee.org_type :=null;-- 'OPERATING_UNIT';
-- Assignment Values
p_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';
p_assignment_attribs.instrument.instrument_id :=
lr_ext_bank_acct_dtl.ext_bank_account_id;
-- External Bank Account ID
p_assignment_attribs.priority := 1;
p_assignment_attribs.start_date := SYSDATE;
iby_disbursement_setup_pub.set_payee_instr_assignment
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payee => p_payee,
p_assignment_attribs => p_assignment_attribs,
x_assign_id => x_assign_id,
x_response => x_response
);
DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
DBMS_OUTPUT.put_line ('X_ASSIGN_ID = ' || x_assign_id);
DBMS_OUTPUT.put_line ('X_RESPONSE.Result_Code = ' || x_response.result_code);
DBMS_OUTPUT.put_line ( 'X_RESPONSE.Result_Category = '
|| x_response.result_category
);
DBMS_OUTPUT.put_line ( 'X_RESPONSE.Result_Message = '
|| x_response.result_message
);
IF (x_return_status <> fnd_api.g_ret_sts_success --or x_response.result_message is not null
)
THEN
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
l_msg :=
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false);
DBMS_OUTPUT.put_line ('The API call failed with error ' || l_msg);
END LOOP;
ELSE
DBMS_OUTPUT.put_line ('The API call ended with SUCESSS status');
END IF;
end if;
exception when others then
xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.SET_PAYEE_INSTR_ASSIGNMENT_P',g_conc_request_id,'Ends-Main-Exception','Main excception',substr(SQLERRM,1,399));
xx_emp_as_spplr_crtn_pkg.output (' Error in SET_PAYEE_INSTR_ASSIGNMENT_P- '||dbms_utility.format_error_backtrace||SQLERRM );
-- retcode := '2';
-- errbuf := ' Error in SET_PAYEE_INSTR_ASSIGNMENT_P- ' || SQLERRM;
END set_payee_instr_assignment_p;
procedure create_external_payee_P
(
P_VENDOR_ID in number,
p_org_id in number,
P_ext_payee_id in number -- iby_pmt_instr_uses_all.ext_pmt_party_id
)IS
BEGIN
NULL;
END create_external_payee_P;
PROCEDURE create_ce_bank_branch (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_bank_party_id IN NUMBER,
p_branch_name IN VARCHAR2,
p_branch_type IN VARCHAR2,
p_bank_home_country_code IN VARCHAR2
)
IS
p_init_msg_list VARCHAR2 (200);
-- p_bank_id NUMBER;
-- p_branch_name VARCHAR2 (200);
p_branch_number VARCHAR2 (200);
-- p_branch_type VARCHAR2 (200);
p_alternate_branch_name VARCHAR2 (200);
p_description VARCHAR2 (200);
p_rfc_identifier VARCHAR2 (200);
x_branch_id NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
p_count NUMBER;
BEGIN
p_init_msg_list := fnd_api.g_true;
-- p_branch_name := 'XXPKTest Branch';
-- p_alternate_branch_name := 'XXPKTestBranch Name';
-- p_description := 'XXPKTest Branch description';
-- branch number/Routing Transit Number is subjected to
-- the check digit validation
-- p_branch_number := 111000025;
-- Validated against AR_LOOKUPS with lookup_type = BANK_BRANCH_TYPE
-- p_branch_type := 'ABA';
-- Validated against CE_LOOKUPS with lookup_type = RFC_IDENTIFIER
p_rfc_identifier := 'AFC';
CE_BANK_PUB.CREATE_BANK_BRANCH
(p_init_msg_list => p_init_msg_list,
p_bank_id => p_bank_party_id,
p_branch_name => p_branch_name,
p_branch_number => p_branch_number,
p_branch_type => p_branch_type,
-- p_alternate_branch_name => p_alternate_branch_name,
-- p_description => p_description,
p_rfc_identifier => p_rfc_identifier,
x_branch_id => x_branch_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ('BRANCH_ID/PARTY_ID = ' || x_branch_id);
DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
IF x_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
ELSIF x_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF x_msg_data IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
END LOOP;
END IF;
commit;
exception when others then
rollback;
END create_ce_bank_branch;
END xx_emp_as_spplr_crtn_pkg;
/
