CREATE TABLE APPS.MPFL_XXSUPPLIER_STG
(
CODE VARCHAR2(40 BYTE),
SUPPLIER_NAME VARCHAR2(250 BYTE),
ADDRESS_LINE1 VARCHAR2(250 BYTE),
ADDRESS_LINE2 VARCHAR2(250 BYTE),
ADDRESS_LINE3 VARCHAR2(250 BYTE),
ADDRESS_LINE4 VARCHAR2(250 BYTE),
CONTACT_PERSON VARCHAR2(250 BYTE),
PHONE VARCHAR2(40 BYTE),
FAX VARCHAR2(40 BYTE),
SITE_CODE VARCHAR2(20 BYTE),
ERROR_MSG VARCHAR2(250 BYTE)
);
SET DEFINE OFF;
Insert into MPFL_XXSUPPLIER_STG
(CODE, SUPPLIER_NAME, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3,
ADDRESS_LINE4, CONTACT_PERSON, PHONE, FAX, SITE_CODE,
ERROR_MSG)
Values
('12918603', 'IOU : Md.Babul Hossain (18603)', 'Dhaka', 'HO-Badda', 'Dhaka',
'Dhaka', 'SA-ADMIN', '01912257303', NULL, 'OFFICE',
'Opened');
COMMIT;
CREATE OR REPLACE PROCEDURE APPS.MPFL_supplier_api_MA AS
--declare
--cursor c1 is select organization_id from hr_operating_units ;
cursor c2 is select * from MPFL_xxsupplier_stg where ERROR_MSG is null ;
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_vendor_contact_rec AP_VENDOR_PUB_PKG.R_VENDOR_CONTACT_REC_TYPE;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(8000);
l_data VARCHAR2 (2000);
l_msg_index NUMBER;
l_vendor_id NUMBER;
l_party_id NUMBER;
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_party_site_id NUMBER;
--l_vendor_name_alt VARCHAR2(10);--ap_supplier.VENDOR_NAME_ALT%type;
--ap_supp_rec ap.ap_suppliers_int%ROWTYPE;
BEGIN
delete from xxvendor;
commit;
FND_GLOBAL.APPS_INITIALIZE(USER_ID=>1110,RESP_ID=>NULL,RESP_APPL_ID=>NULL);
for x2 in c2 loop
/* Supplier Creation */
--Required
l_vendor_rec.vendor_name := x2.supplier_name; --Supplier Name
l_vendor_rec.segment1 := x2.code; -- supplier code
--Optional
l_vendor_rec.match_option:='R' ;
--l_venvod_rec.vendor_name_alt :=x2.supplier_name;
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 = fnd_api.g_ret_sts_success THEN
fnd_file.put_line (fnd_file.LOG,
' Vendor Id :'
|| l_vendor_id
|| ' Created .'
);
DBMS_OUTPUT.put_line ('Vendor Id :'
|| l_vendor_id
|| ' Created '
);
--fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
--fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
--DBMS_OUTPUT.put_line ('Vendor Creation: ');
--DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
--DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
-- fnd_file.put_line(fnd_file.log,'Supplier: '||x2.supplier_name);
/* Supplier Site Creation */
--Required
l_vendor_site_rec.vendor_id :=l_vendor_id ;
l_vendor_site_rec.vendor_site_code :=x2.site_code ;
l_vendor_site_rec.address_line1 :=x2.address_line1 ;
l_vendor_site_rec.country := 'BD';
l_vendor_site_rec.county := 'Bangladesh';
l_vendor_site_rec.org_id := 148;--x1.organization_id ;
--Optional
l_vendor_site_rec.address_line2 :=x2.address_line2;
l_vendor_site_rec.address_line3 :=x2.address_line3;
l_vendor_site_rec.ADDRESS_LINE4 :=x2.address_line4;
l_vendor_site_rec.purchasing_site_flag:='Y';
l_vendor_site_rec.pay_site_flag :='Y';
l_vendor_site_rec.rfq_only_site_flag :='N';
-- l_vendor_site_rec.vendor_name_alt := l_vendor_site_rec.vendor_name || ' ALT';
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 = fnd_api.g_ret_sts_success THEN
fnd_file.put_line (fnd_file.LOG,
' Vendor Site Id :'
|| l_vendor_site_id
|| ' Created .'
);
DBMS_OUTPUT.put_line ('Vendor Site Id :'
|| l_vendor_site_id
|| ' Created '
);
-- fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
--fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
--DBMS_OUTPUT.put_line ('Vendor Site Creation: ');
--DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
--DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
/* Supplier Contact Information */
--- Required
l_vendor_contact_rec.vendor_id :=l_vendor_id ;
l_vendor_contact_rec.vendor_site_id :=l_vendor_site_id;
l_vendor_contact_rec.party_site_id :=l_party_site_id;
-- l_vendor_contact_rec.person_first_name := 'B';
l_vendor_contact_rec.person_last_name :=x2.contact_person;
-- l_vendor_contact_rec.org_id := x1.organization_id ;
--optional
l_vendor_contact_rec.phone :=x2.phone ;
l_vendor_contact_rec.fax_phone :=x2.fax;
AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT
( p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_vendor_contact_rec => l_vendor_contact_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_contact_id => x_vendor_contact_id,
x_per_party_id => x_per_party_id,
x_rel_party_id => x_rel_party_id,
x_rel_id => x_rel_id,
x_org_contact_id => x_org_contact_id,
x_party_site_id => x_party_site_id
);
fnd_file.put_line (fnd_file.LOG,
' Vendor Contact Id :'
|| x_vendor_contact_id
|| ' Created .'
);
DBMS_OUTPUT.put_line ('Vendor Contact Id :'
|| x_vendor_contact_id
|| ' Created '
);
--fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
-- fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
--DBMS_OUTPUT.put_line ('Vendor Contact Creation: ');
--DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
--DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
END IF;
DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
insert into xxvendor values(l_vendor_id);
update MPFL_xxsupplier_stg set ERROR_MSG='Opened' where code = x2.code;
--delete from xxsupplier_stg where code = x2.code and CODE=123456;
END IF;
DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
DBMS_OUTPUT.put_line('Error In Supplier Creation: '||x2.code||substr(l_return_status,1,250));
IF l_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => l_data,
p_msg_index_out => l_msg_index
);
DBMS_OUTPUT.put_line ( 'Error In Supplier Creation: '
|| x2.code ||l_data
);
END LOOP;
END IF;
end loop;
COMMIT;
Exception
When OTHERS then
fnd_file.put_line(fnd_file.log,'Error In Supplier Creation: '||substr(l_return_status,1,250));
fnd_file.put_line(fnd_file.log,RPAD('*',80,'*'));
-- DBMS_OUTPUT.put_line('Error In Supplier Creation: '||x2.code||substr(l_return_status,1,250));
END;
/
update ap.ap_suppliers ss set VENDOR_NAME_ALT=VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE='EMP'
where exists
( select s.VENDOR_ID from ap.ap_suppliers s, apps.mpfl_XXSUPPLIER_STG tm where s.VENDOR_NAME=tm.SUPPLIER_NAME and s.VENDOR_ID=ss.VENDOR_ID
) ;
exec APPS.MPFL_supplier_api_MA();
(
CODE VARCHAR2(40 BYTE),
SUPPLIER_NAME VARCHAR2(250 BYTE),
ADDRESS_LINE1 VARCHAR2(250 BYTE),
ADDRESS_LINE2 VARCHAR2(250 BYTE),
ADDRESS_LINE3 VARCHAR2(250 BYTE),
ADDRESS_LINE4 VARCHAR2(250 BYTE),
CONTACT_PERSON VARCHAR2(250 BYTE),
PHONE VARCHAR2(40 BYTE),
FAX VARCHAR2(40 BYTE),
SITE_CODE VARCHAR2(20 BYTE),
ERROR_MSG VARCHAR2(250 BYTE)
);
SET DEFINE OFF;
Insert into MPFL_XXSUPPLIER_STG
(CODE, SUPPLIER_NAME, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3,
ADDRESS_LINE4, CONTACT_PERSON, PHONE, FAX, SITE_CODE,
ERROR_MSG)
Values
('12918603', 'IOU : Md.Babul Hossain (18603)', 'Dhaka', 'HO-Badda', 'Dhaka',
'Dhaka', 'SA-ADMIN', '01912257303', NULL, 'OFFICE',
'Opened');
COMMIT;
CREATE OR REPLACE PROCEDURE APPS.MPFL_supplier_api_MA AS
--declare
--cursor c1 is select organization_id from hr_operating_units ;
cursor c2 is select * from MPFL_xxsupplier_stg where ERROR_MSG is null ;
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_vendor_contact_rec AP_VENDOR_PUB_PKG.R_VENDOR_CONTACT_REC_TYPE;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(8000);
l_data VARCHAR2 (2000);
l_msg_index NUMBER;
l_vendor_id NUMBER;
l_party_id NUMBER;
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_party_site_id NUMBER;
--l_vendor_name_alt VARCHAR2(10);--ap_supplier.VENDOR_NAME_ALT%type;
--ap_supp_rec ap.ap_suppliers_int%ROWTYPE;
BEGIN
delete from xxvendor;
commit;
FND_GLOBAL.APPS_INITIALIZE(USER_ID=>1110,RESP_ID=>NULL,RESP_APPL_ID=>NULL);
for x2 in c2 loop
/* Supplier Creation */
--Required
l_vendor_rec.vendor_name := x2.supplier_name; --Supplier Name
l_vendor_rec.segment1 := x2.code; -- supplier code
--Optional
l_vendor_rec.match_option:='R' ;
--l_venvod_rec.vendor_name_alt :=x2.supplier_name;
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 = fnd_api.g_ret_sts_success THEN
fnd_file.put_line (fnd_file.LOG,
' Vendor Id :'
|| l_vendor_id
|| ' Created .'
);
DBMS_OUTPUT.put_line ('Vendor Id :'
|| l_vendor_id
|| ' Created '
);
--fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
--fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
--DBMS_OUTPUT.put_line ('Vendor Creation: ');
--DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
--DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
-- fnd_file.put_line(fnd_file.log,'Supplier: '||x2.supplier_name);
/* Supplier Site Creation */
--Required
l_vendor_site_rec.vendor_id :=l_vendor_id ;
l_vendor_site_rec.vendor_site_code :=x2.site_code ;
l_vendor_site_rec.address_line1 :=x2.address_line1 ;
l_vendor_site_rec.country := 'BD';
l_vendor_site_rec.county := 'Bangladesh';
l_vendor_site_rec.org_id := 148;--x1.organization_id ;
--Optional
l_vendor_site_rec.address_line2 :=x2.address_line2;
l_vendor_site_rec.address_line3 :=x2.address_line3;
l_vendor_site_rec.ADDRESS_LINE4 :=x2.address_line4;
l_vendor_site_rec.purchasing_site_flag:='Y';
l_vendor_site_rec.pay_site_flag :='Y';
l_vendor_site_rec.rfq_only_site_flag :='N';
-- l_vendor_site_rec.vendor_name_alt := l_vendor_site_rec.vendor_name || ' ALT';
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 = fnd_api.g_ret_sts_success THEN
fnd_file.put_line (fnd_file.LOG,
' Vendor Site Id :'
|| l_vendor_site_id
|| ' Created .'
);
DBMS_OUTPUT.put_line ('Vendor Site Id :'
|| l_vendor_site_id
|| ' Created '
);
-- fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
--fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
--DBMS_OUTPUT.put_line ('Vendor Site Creation: ');
--DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
--DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
/* Supplier Contact Information */
--- Required
l_vendor_contact_rec.vendor_id :=l_vendor_id ;
l_vendor_contact_rec.vendor_site_id :=l_vendor_site_id;
l_vendor_contact_rec.party_site_id :=l_party_site_id;
-- l_vendor_contact_rec.person_first_name := 'B';
l_vendor_contact_rec.person_last_name :=x2.contact_person;
-- l_vendor_contact_rec.org_id := x1.organization_id ;
--optional
l_vendor_contact_rec.phone :=x2.phone ;
l_vendor_contact_rec.fax_phone :=x2.fax;
AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT
( p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_vendor_contact_rec => l_vendor_contact_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_contact_id => x_vendor_contact_id,
x_per_party_id => x_per_party_id,
x_rel_party_id => x_rel_party_id,
x_rel_id => x_rel_id,
x_org_contact_id => x_org_contact_id,
x_party_site_id => x_party_site_id
);
fnd_file.put_line (fnd_file.LOG,
' Vendor Contact Id :'
|| x_vendor_contact_id
|| ' Created .'
);
DBMS_OUTPUT.put_line ('Vendor Contact Id :'
|| x_vendor_contact_id
|| ' Created '
);
--fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
-- fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
--DBMS_OUTPUT.put_line ('Vendor Contact Creation: ');
--DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
--DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
END IF;
DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
insert into xxvendor values(l_vendor_id);
update MPFL_xxsupplier_stg set ERROR_MSG='Opened' where code = x2.code;
--delete from xxsupplier_stg where code = x2.code and CODE=123456;
END IF;
DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
DBMS_OUTPUT.put_line('Error In Supplier Creation: '||x2.code||substr(l_return_status,1,250));
IF l_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => l_data,
p_msg_index_out => l_msg_index
);
DBMS_OUTPUT.put_line ( 'Error In Supplier Creation: '
|| x2.code ||l_data
);
END LOOP;
END IF;
end loop;
COMMIT;
Exception
When OTHERS then
fnd_file.put_line(fnd_file.log,'Error In Supplier Creation: '||substr(l_return_status,1,250));
fnd_file.put_line(fnd_file.log,RPAD('*',80,'*'));
-- DBMS_OUTPUT.put_line('Error In Supplier Creation: '||x2.code||substr(l_return_status,1,250));
END;
/
update ap.ap_suppliers ss set VENDOR_NAME_ALT=VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE='EMP'
where exists
( select s.VENDOR_ID from ap.ap_suppliers s, apps.mpfl_XXSUPPLIER_STG tm where s.VENDOR_NAME=tm.SUPPLIER_NAME and s.VENDOR_ID=ss.VENDOR_ID
) ;
exec APPS.MPFL_supplier_api_MA();
No comments:
Post a Comment