API to create employee as supplier in r12
In this post , We will discuss about the API to create employee as supplier in r12.This API helps to create Employees as a Supplier in oracle apps. In this post , I will share the sample script using API to create employee as supplier in r12. We will be use default Site ' Office' for all the Employees created as a Supplier. Please find below the complete source code of API to create employee as supplier in r12.
Sample Script of API to create employee as supplier in r12.
create or replace PACKAGE BODY XX_SUPPLIER_CREATION_PKG AS
PROCEDURE main(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_emp_id IN number,
p_org_id IN number
) AS
CURSOR c_emp_info(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) 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';
apps.fnd_file.put_line(apps.fnd_file.output,
'***************Employee Type Supplier Creation***********************');
apps.fnd_file.put_line(apps.fnd_file.output, ' ');
OPEN c_hou;
FETCH c_hou
INTO v_org_name;
CLOSE c_hou;
apps.fnd_file.put_line(apps.fnd_file.output,
' Organization : ' || v_org_name);
apps.fnd_file.put_line(apps.fnd_file.output, ' ');
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
apps.fnd_file.put_line(apps.fnd_file.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';
apps.fnd_file.put_line(apps.fnd_file.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
apps.fnd_file.put_line(apps.fnd_file.output,
' Creating Employee Type Supplier for :' ||
v_empname);
apps.fnd_file.put_line(apps.fnd_file.output, ' ');
l_vendor_rec.vendor_name := v_empname;
l_vendor_rec.VENDOR_TYPE_LOOKUP_CODE := 'EMPLOYEE';
l_vendor_rec.employee_id := v_empid;
apps.fnd_file.put_line(apps.fnd_file.output,
' Calling CREATE_VENDOR 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
apps.fnd_file.put_line(apps.fnd_file.output,
' Error in Supplier..');
IF (l_msg_count = 1) THEN
apps.fnd_file.put_line(apps.fnd_file.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;
apps.fnd_file.put_line(apps.fnd_file.output,
' Error Message' || p_count || ' ---' ||
l_msg_data);
END LOOP;
END IF;
ELSE
apps.fnd_file.put_line(apps.fnd_file.output, ' ');
apps.fnd_file.put_line(apps.fnd_file.output,
' Successfully Cretaed Supplier ');
END IF;
END IF;
IF l_vendor_id IS NOT NULL AND NVL(ln_sup_site_cnt,0) <> 3 THEN
BEGIN
IF v_error <> 'E' THEN
l_vendor_site_rec.vendor_id := l_vendor_id;
l_vendor_site_rec.vendor_site_code := 'OFFICE';
l_vendor_site_rec.org_id := p_org_id;
l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID := l_liability_id;
l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID := 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';
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
apps.fnd_file.put_line(apps.fnd_file.output,
' Error in Creating Employee Type Supplier..');
retcode := '1';
errbuf := ' Error in Supplier Site Creation-';
IF (l_msg_count = 1) THEN
apps.fnd_file.put_line(apps.fnd_file.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;
apps.fnd_file.put_line(apps.fnd_file.output,
' Error Message' || p_count ||
' ---' || l_msg_data);
END LOOP;
END IF;
ELSE
apps.fnd_file.put_line(apps.fnd_file.output,
' Successfully Cretaed Supplier Site');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.output,
' Error - ' || SQLERRM);
END;
END IF;
END IF;
apps.fnd_file.put_line(apps.fnd_file.output,
'*************** END ***********************');
EXCEPTION
WHEN OTHERS THEN
retcode := '2';
errbuf := ' Error in Main- ' || SQLERRM;
apps.fnd_file.put_line(apps.fnd_file.output,
' Error in Main- ' || SQLERRM);
END main;
END XX_SUPPLIER_CREATION_PKG;
0 comments:
Post a Comment