CREATE TABLE XXC_SUPP_STAGE
(
VENDOR_NUMBER VARCHAR2(30),
VENDOR_NAME VARCHAR2(240),
VENDOR_TYPE VARCHAR2(300),
ORGANIZATION_TYPE VARCHAR2(300),
VENDOR_SITE VARCHAR2(300),
VENDOR_PAY_GROUP VARCHAR2(300),
VENDOR_PAYMENT_TERMS VARCHAR2(300),
ADDRESS_LINE1 VARCHAR2(300),
ADDRESS_LINE2 VARCHAR2(300),
ADDRESS_LINE3 VARCHAR2(300),
ADDRESS_LINE4 VARCHAR2(300),
CITY VARCHAR2(25),
STATE VARCHAR2(15),
COUNTRY VARCHAR2(25),
ZIP_CODE VARCHAR2(20),
VENDOR_SITES_PAY_GROUP VARCHAR2(300),
VENDOR_SITES_PAYMENT_TERMS VARCHAR2(300),
OPERATING_UNIT_NAME VARCHAR2(500),
LIABILITY_ACCOUNT VARCHAR2(300),
PREPAY_ACCOUNT VARCHAR2(300),
BANK_NAME VARCHAR2(300),
BANK_BRANCH_NAME VARCHAR2(300),
BANK_ACCOUNT_NAME VARCHAR2(300),
BANK_ACCOUNT_NUMBER VARCHAR2(300),
CURRENCY_CODE VARCHAR2(50),
CONTACT_FIRST_NAME VARCHAR2(100),
CONTACT_LAST_NAME VARCHAR2(150),
CONTACT_PHONE VARCHAR2(100),
CONTACT_EMAIL VARCHAR2(300),
CONTACT_FAX VARCHAR2(100),
SET_OF_BOOKS_ID NUMBER,
V_SUPP_FLAG VARCHAR2(10),
V_SITE_FLAG VARCHAR2(10),
V_CONTACT_FLAG VARCHAR2(10),
SUPPLIER_ERROR_MESSAGE VARCHAR2(2000),
SITE_ERROR_MESSAGE VARCHAR2(2000),
CONTACT_ERROR_MESSAGE VARCHAR2(200),
CREATED_BY NUMBER,
CREATION_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATE_LOGIN NUMBER
)
-----------------End Of Staging Table Creation---------------------------------------
---------------------Loader File To load Flat File Data Into Staging Table------------------------------
OPTIONS (SKIP=1)
LOAD DATA
INFILE '/u02/appltest/apps/apps_st/appl/ap/12.0.0/bin/SAMPLEDATA.txt'
INSERT
into table XXC_SUPP_STAGE
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
VENDOR_NAME,
VENDOR_NUMBER,
VENDOR_TYPE,
ORGANIZATION_TYPE,
VENDOR_SITE,
VENDOR_PAY_GROUP,
VENDOR_PAYMENT_TERMS,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY,
ZIP_CODE,
VENDOR_SITES_PAY_GROUP,
VENDOR_SITES_PAYMENT_TERMS,
OPERATING_UNIT_NAME,
LIABILITY_ACCOUNT,
PREPAY_ACCOUNT,
BANK_NAME,
BANK_BRANCH_NAME,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUMBER,
CURRENCY_CODE,
CONTACT_FIRST_NAME,
CONTACT_LAST_NAME,
CONTACT_PHONE,
CONTACT_EMAIL,
CONTACT_FAX,
SET_OF_BOOKS_ID CONSTANT "1",
V_SUPP_FLAG CONSTANT "N",
V_SITE_FLAG CONSTANT "N",
V_CONTACT_FLAG CONSTANT "N",
SUPPLIER_ERROR_MESSAGE CONSTANT "",
SITE_ERROR_MESSAGE CONSTANT "",
CONTACT_ERROR_MESSAGE CONSTANT "",
CREATED_BY CONSTANT "-1",
CREATION_DATE SYSDATE,
LAST_UPDATED_BY CONSTANT "-1",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATE_LOGIN CONSTANT "-1"
)
----------------------------------End Of Loader File--------------------------------------------
CREATE OR REPLACE PACKAGE XXC_SUP_CREATION_PKG
/*
*/
IS
---THIS PROCEDURE IS TO VALIDATE SUPPLERS
PROCEDURE XXC_SUPPLIERS_VALIDATE(p_vendor_number varchar2 default null);
--This Procedure is used to Create Vendor.
PROCEDURE XXC_CREATE_SUPPLIERS(p_vendor_number varchar2 default null);
--This Procedure is used to create Vendor sites.
PROCEDURE XXC_CREATE_SUPPLIER_SITES( p_vendor_number varchar2 default null,
p_vendor_site varchar2 default null);
--This Procedure is used to create vendor contact information.
PROCEDURE XXC_CREATE_SUPPLIERS_CONTACTS( p_vendor_number varchar2 default null,
p_vendor_site varchar2 default null);
---This PROCEDURE is used to create supplier information Totally
PROCEDURE XXC_MAIN_SUPPLIER(
errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2,
p_vendor_number varchar2 default null,
p_vendor_site varchar2 default null);
---This PROCEDURE is used for display purpose----------------
Procedure XXC_DISPLAY_MESSAGE(p_mode Varchar2, p_message Varchar2);
-----------CURSORs----------------------------------
----This Cursor is used for validating the stagingTable Data
CURSOR C_STAGE(p_vendor_number varchar2)
IS SELECT * FROM XXC_SUPP_STAGE
WHERE vendor_number=nvl(p_vendor_number,vendor_number);
---------------This Cursor is used For Inserting Valid Suppliers Data Into BaseTables
CURSOR c_supplier(p_vendor_number varchar2)
IS SELECT * FROM XXC_SUPP_STAGE WHERE
v_supp_flag='V' AND
vendor_number=nvl(p_vendor_number,vendor_number);
-- --------This Cursor Is used to For Inserting Valid SupplierSites for Suppliers into the Base Tables
CURSOR c_site(p_vendor_number varchar2,p_vendor_site varchar2)
IS SELECT * FROM XXC_SUPP_STAGE WHERE v_supp_flag='V' and v_site_flag='V'
and vendor_number=nvl(p_vendor_number,vendor_number)
and vendor_site=nvl(p_vendor_site,vendor_site);
--------------This Cursor is Used For Inserting Valid Suppliers Contacts Data Into the Base tables
CURSOR c_contact(p_vendor_number varchar2,p_vendor_site varchar2)
IS SELECT * FROM XXC_SUPP_STAGE
WHERE v_supp_flag='V' and v_contact_flag='V' and v_site_flag='V'
and vendor_number=nvl(p_vendor_number,vendor_number)
and vendor_site=nvl(p_vendor_site,vendor_site);
-----------END OF Cursors------------------------------------
END XXC_SUP_CREATION_PKG;
/
CREATE OR REPLACE PACKAGE BODY XXC_SUP_CREATION_PKG
/*
Author : SAI GOWTHAM .V
File Name : XXC_SUPP_CREATION_PKG.pkb
Created Date : AUG 2011
Modified By : SAI GOWTHAM.V
Modified Date : AUG 2011
Package Name : XXC_SUP_CREATION_PKG
Description : This package is used to create New Supplier, supplier sites and Contact information.
Comments :
*/
IS
-----------PROCEDURE for display message--------------
PROCEDURE XXC_DISPLAY_MESSAGE(p_mode VARCHAR2, p_message VARCHAR2) is
BEGIN
IF UPPER(p_mode) = 'output' THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
ELSIF UPPER(p_mode) = 'log' THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
dbms_output.put_line(p_message);
END IF;
dbms_output.put_line(substr(p_message,1,80));
END XXC_DISPLAY_MESSAGE;
-----------END of display message--------------------
--------start of validate suppliers--------------
procedure xxc_suppliers_validate(p_vendor_number VARCHAR2 default null)
is
-------Local Variables Declaration
lv_vendor_num VARCHAR2(50);
lv_vendor_name VARCHAR2(100);
lv_organization_type VARCHAR2(200);
lv_vendor_pay_group VARCHAR2(200);
lv_organization_id VARCHAR2(250);
lv_liability_account VARCHAR2(300);
lv_prepay_account VARCHAR2(300);
lv_terms_id VARCHAR2(100);
lv_supp_errmsg VARCHAR2(2000);
lv_site_errmsg VARCHAR2(2000);
lv_contact_errmsg VARCHAR2(2000);
lv_vendor_site_code VARCHAR2(200);
lv_vendor_sites_pay_group VARCHAR2(300);
lv_vendor_type VARCHAR2(100);
lv_vendor_number VARCHAR2(250);
lv_currency_code VARCHAR2(50);
lv_country_code VARCHAR2(10);
lv_sites_terms_id VARCHAR2(100);
lv_supp_flag VARCHAR2(10);
lv_site_flag VARCHAR2(10);
lv_contact_flag VARCHAR2(10);
BEGIN
XXC_DISPLAY_MESSAGE('log','Start VALIDATE_SUPLIERS ');
------Opening The loop from Spec For Validating Data
FOR CP_STAGE IN C_STAGE(p_vendor_number) LOOP
------Assinging values To lical Varibles to handle Errors
lv_supp_flag :='N';
lv_site_flag :='N';
lv_contact_flag :='N';
lv_supp_errmsg :='';
lv_site_errmsg :='';
lv_contact_errmsg :='';
------------------START OF VENDOR NUMBERAND VENDORNAME VALIDATION -----------------
IF CP_STAGE.VENDOR_NUMBER IS NOT NULL THEN
BEGIN
SELECT SEGMENT1,VENDOR_NAME
INTO lv_vendor_num,LV_VENDOR_NAME
FROM AP_SUPPLIERS
WHERE TRIM(SEGMENT1)=TRIM(CP_STAGE.VENDOR_NUMBER);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN TOO_MANY_ROWS THEN
XXC_DISPLAY_MESSAGE('log',CP_STAGE.VENDOR_NUMBER||' - '||'THERE ARE MORE THAN ONE VENDOR NUMBER ');
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
IF lv_vendor_num IS NOT NULL AND lv_vendor_name IS NOT NULL THEN
lv_supp_flag := 'Y';
lv_supp_errmsg:=CP_STAGE.VENDOR_NUMBER||' '||CP_STAGE.VENDOR_NAME||' - '||'VENDOR NUMBER OR VENDOR_NAME IS ALREADY EXIST NOT VALID';
lv_supp_errmsg:=lv_supp_errmsg||' ; ';
XXC_DISPLAY_MESSAGE('log',CP_STAGE.VENDOR_NUMBER||' '||CP_STAGE.VENDOR_NAME||' - '||'VENDOR NUMBER OR VENDOR_NAME ALREADY EXIST NOT VALID');
END IF;
END IF;
------------------------------END OF VENDOR_ NUMBERAND VENDORNAME VALIDATION------------------
---------------------------START OF ORGANIZATION_TYPE --------------------
IF CP_STAGE.ORGANIZATION_TYPE IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO lv_organization_type
FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE='ORGANIZATION TYPE'
AND TRIM(LOOKUP_CODE)=TRIM(CP_STAGE.ORGANIZATION_TYPE);
EXCEPTION WHEN NO_DATA_FOUND THEN
lv_supp_flag := 'Y';
lv_supp_errmsg:= lv_supp_errmsg||CP_STAGE.ORGANIZATION_TYPE||' - '||'ORGANIZATION_TYPE INVALID';
XXC_DISPLAY_MESSAGE('log',lv_supp_errmsg);
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
----------------------------END OF ORGANIZATION_TYPE---------------
------START OF VENDOR_TYPE VALIDATION -----------------
IF CP_STAGE.VENDOR_TYPE IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO lv_vendor_type
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE='VENDOR TYPE' AND
TRIM(LOOKUP_CODE)=TRIM(CP_STAGE.VENDOR_TYPE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_supp_flag := 'Y';
lv_supp_errmsg:=lv_supp_errmsg||CP_STAGE.VENDOR_TYPE||' - '||'VENDOR TYPE IS NOT VALID';
XXC_DISPLAY_MESSAGE('log',CP_STAGE.VENDOR_TYPE||' - '||'VENDOR TYPE IS NOT VALID');
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
----------END OF VENDOR_TYPE----------------------
----------------START OF VENDOR_TERMS_NAME ------------------
IF CP_STAGE.VENDOR_PAYMENT_TERMS IS NOT NULL THEN
BEGIN
SELECT TERM_ID
INTO lv_terms_id
FROM AP_TERMS_TL
WHERE UPPER(TRIM(NAME))= UPPER(TRIM(CP_STAGE.VENDOR_PAYMENT_TERMS));
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_supp_flag := 'Y';
lv_supp_errmsg:=lv_supp_errmsg||CP_STAGE.VENDOR_PAYMENT_TERMS||' - '||'INVALID PAYMENT TERMS';
lv_supp_errmsg:=lv_supp_errmsg||' ; ';
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
----------------------END OF TERMS_NAME--------------------------------
------------START OF VENDOR_PAY_GROUP -------------------
IF CP_STAGE.VENDOR_PAY_GROUP IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE INTO lv_vendor_pay_group
FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE='PAY GROUP'
AND LOOKUP_CODE=CP_STAGE.VENDOR_PAY_GROUP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_supp_flag := 'Y';
lv_supp_errmsg:=lv_supp_errmsg||CP_STAGE.VENDOR_PAY_GROUP||' - '||'INVALID VENDOR PAY GROUP';
lv_supp_errmsg:=lv_supp_errmsg||' ; ';
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
---------------END OF VENDOR_PAY_GROUP--------------------
-----------------START OF OPERATING UNIT NAME ----------------
IF CP_STAGE.OPERATING_UNIT_NAME IS NOT NULL THEN
BEGIN
SELECT ORGANIZATION_ID
INTO lv_organization_id
FROM HR_OPERATING_UNITS
WHERE UPPER(NAME)=UPPER(CP_STAGE.OPERATING_UNIT_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_site_flag := 'Y';
lv_site_errmsg:=lv_site_errmsg||CP_STAGE.OPERATING_UNIT_NAME||' - '||'INVALID OPERATING UNIT NAME';
lv_site_errmsg:=lv_site_errmsg||' ; ';
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
---------------------END OF OPERATING_UNIT---------------
--------------------START OF CURRENCY_CODE ----------------
IF CP_STAGE.CURRENCY_CODE IS NOT NULL THEN
BEGIN
SELECT CURRENCY_CODE
INTO lv_currency_code
FROM FND_CURRENCIES
WHERE UPPER(CURRENCY_CODE)=UPPER(CP_STAGE.CURRENCY_CODE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_site_flag := 'Y';
lv_site_errmsg:=lv_site_errmsg||CP_STAGE.CURRENCY_CODE||' - '||'INVALID CURRENCY';
lv_site_errmsg:=lv_site_errmsg||' ; ';
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
--------------------END OF CURRENCY_CODE-------------
------------------------START OF COUNTRY CODE -----------------
IF CP_STAGE.COUNTRY IS NOT NULL THEN
BEGIN
SELECT TERRITORY_CODE
INTO lv_country_code FROM FND_TERRITORIES_VL
WHERE TRIM(TERRITORY_CODE)=TRIM(CP_STAGE.COUNTRY);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_site_flag :='Y';
lv_site_errmsg:=lv_site_errmsg||CP_STAGE.COUNTRY||' - '||'COUNTRY IS NOT VALID';
lv_site_errmsg:=lv_site_errmsg||' ; ';
XXC_DISPLAY_MESSAGE('log',CP_STAGE.COUNTRY||' - '||'COUNTRY IS NOT VALID');
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
---------------------END OF COUNTRY CODE-----------------------
----------------START OF VENDOR_SITES_TERMS_NAME ------------------
IF CP_STAGE.VENDOR_SITES_PAYMENT_TERMS IS NOT NULL THEN
BEGIN
SELECT TERM_ID
INTO lv_sites_terms_id
FROM AP_TERMS_TL
WHERE UPPER(TRIM(NAME))= UPPER(TRIM(CP_STAGE.VENDOR_SITES_PAYMENT_TERMS ));
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_site_flag := 'Y';
lv_site_errmsg:=lv_site_errmsg||CP_STAGE.VENDOR_SITES_PAYMENT_TERMS ||' - '||'INVALID SITES PAYMENT TERMS';
lv_site_errmsg:=lv_site_errmsg||' ; ';
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
----------------------END OF VENDOR SITES TERMS_NAME--------------------------------
------------START OF VENDOR_SITES_PAY_GROUP -------------------
IF CP_STAGE.VENDOR_SITES_PAY_GROUP IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE INTO lv_vendor_sites_pay_group
FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE='PAY GROUP'
AND LOOKUP_CODE=CP_STAGE.VENDOR_SITES_PAY_GROUP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_site_flag := 'Y';
lv_site_errmsg:=lv_site_errmsg||CP_STAGE.VENDOR_SITES_PAY_GROUP||' - '||'INVALID VENDOR SITES PAY GROUP';
lv_site_errmsg:=lv_site_errmsg||' ; ';
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
---------------END OF VENDOR_SITES_PAY_GROUP--------------------
----------------START OF LIABILITY_ACCOUNT ----------------
IF CP_STAGE.LIABILITY_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CONCATENATED_SEGMENTS INTO
lv_liability_account
FROM GL_CODE_COMBINATIONS_KFV WHERE TRIM(CONCATENATED_SEGMENTS)=TRIM(CP_STAGE.LIABILITY_ACCOUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_site_flag := 'Y';
lv_site_errmsg:=lv_site_errmsg||CP_STAGE.LIABILITY_ACCOUNT||' - '||'INVALID LIABILITY ACCOUNT';
lv_site_errmsg:=lv_site_errmsg||' ; ';
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
---------------END OF LIABILITY_ACCOUNT---------------------------
----------------START OF PREPAY_ACCOUNT ----------------
IF CP_STAGE.PREPAY_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CONCATENATED_SEGMENTS INTO
lv_prepay_account
FROM GL_CODE_COMBINATIONS_KFV WHERE TRIM(CONCATENATED_SEGMENTS)=TRIM(CP_STAGE.LIABILITY_ACCOUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_site_flag := 'Y';
lv_site_errmsg:=lv_site_errmsg||CP_STAGE.PREPAY_ACCOUNT||' - '||'INVALID PREPAY ACCOUNT';
lv_site_errmsg:=lv_site_errmsg||' ; ';
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
END IF;
---------------END OF LIABILITY_ACCOUNT---------------------------
--------------------START OF VENDOR_SITE_CODE ------------------
IF CP_STAGE.VENDOR_SITE IS NOT NULL THEN
BEGIN
SELECT A.SEGMENT1,
B.VENDOR_SITE_CODE
INTO LV_VENDOR_NUMBER,
lv_vendor_site_code
FROM AP_SUPPLIERS A,
AP_SUPPLIER_SITES_ALL B
WHERE A.VENDOR_ID=B.VENDOR_ID
AND TRIM(B.VENDOR_SITE_CODE)=TRIM(CP_STAGE.VENDOR_SITE)
AND A.SEGMENT1=CP_STAGE.VENDOR_NUMBER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN TOO_MANY_ROWS THEN
XXC_DISPLAY_MESSAGE('log',CP_STAGE.VENDOR_SITE||' - '||'THERE ARE MORE THAN ONE VENDOR SITES ');
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
IF lv_vendor_site_code IS NOT NULL THEN
lv_site_flag := 'Y';
lv_contact_flag:='Y';
lv_site_errmsg:=CP_STAGE.VENDOR_SITE||' - '||' VENDOR_SITE IS ALREADY EXIST NOT VALID';
lv_contact_errmsg:=CP_STAGE.VENDOR_SITE||' - '||' VENDOR_SITE IS ALREADY EXIST NOT VALID';
--LV_ERRMSG:=LV_ERRMSG||' ; ';
XXC_DISPLAY_MESSAGE('log',CP_STAGE.VENDOR_NUMBER||' '||CP_STAGE.VENDOR_SITE||' - '||' VENDOR_SITE ALREADY EXIST NOT VALID');
END IF;
END IF;
--------------------END OF VENDOR_SITE_CODE--------------------------------------
-- Changing the STAGEing table flag
------------------------------------
--
IF lv_supp_flag ='N' THEN
BEGIN
UPDATE XXC_SUPP_STAGE
SET v_supp_flag ='V'
,supplier_error_message = null
where vendor_number =cp_stage.vendor_number;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','Error Occured During Updation of Valid Records in Update in xxc_supp_staging');
END;
END IF;
--
IF lv_supp_flag = 'Y' THEN
BEGIN
UPDATE XXC_SUPP_STAGE
SET v_supp_flag ='E'
,supplier_error_message =lv_supp_errmsg
where vendor_number = cp_stage.vendor_number;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','Error Occured During Updation of Error Records in xxc_supp_staging');
END;
END IF;
--
--
IF lv_site_flag = 'N' THEN
BEGIN
UPDATE XXC_SUPP_STAGE
SET
v_site_flag ='V'
,site_error_message = null
where vendor_number = cp_stage.vendor_number;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','Error Occured During Updation of Valid Records in Update in xxc_supp_staging');
END;
END IF;
--
IF lv_site_flag='Y' THEN
BEGIN
UPDATE XXC_SUPP_STAGE
SET
v_site_flag ='E'
,site_error_message = lv_site_errmsg
where vendor_number =cp_stage.vendor_number;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','Error Occured During Updation of Error Records in xxc_supp_staging');
END;
END IF;
--
IF lv_contact_flag ='N' THEN
BEGIN
UPDATE XXC_SUPP_STAGE
SET
v_contact_flag='V'
,contact_error_message = null
where vendor_number = cp_stage.vendor_number;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','Error Occured During Updation of Valid Records in Update in xxc_supp_staging');
END;
END IF;
--
IF lv_contact_flag = 'Y' THEN
BEGIN
UPDATE XXC_SUPP_STAGE
SET
v_contact_flag='E'
,contact_error_message = lv_contact_errmsg
where vendor_number =cp_stage.vendor_number;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','Error Occured During Updation of Error Records in xxc_supp_staging');
END;
END IF;
XXC_DISPLAY_MESSAGE('log','End the VALIDATE_SUPLIERS ');
END LOOP;
END xxc_suppliers_validate;--------end of validate suppliers-----------------------
----------start of xxc_create_suppliers----------
PROCEDURE XXC_CREATE_SUPPLIERS(p_vendor_number VARCHAR2 default null)
is
lv_vendor_rec AP_VENDOR_PUB_PKG.R_VENDOR_REC_TYPE;
lv_return_status VARCHAR2(10);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2(1000);
lv_vendor_id NUMBER;
lv_party_id NUMBER;
BEGIN
fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPl_ID);
--fnd_global.apps_initialize(0,20707,201);
FOR cp_supplier in c_supplier(p_vendor_number) LOOP
--Assingning Staging Table Data To Record Type
lv_vendor_rec.vendor_name := cp_supplier.vendor_name; --Supplier Name
lv_vendor_rec.segment1 := cp_supplier.vendor_number;
lv_vendor_rec.organization_type_lookup_code := cp_supplier.organization_type;
lv_vendor_rec.vendor_type_lookup_code := cp_supplier.vendor_type;
lv_vendor_rec.pay_group_lookup_code :=cp_supplier.vendor_pay_group;
lv_vendor_rec.terms_name :=cp_supplier.vendor_payment_terms;
lv_vendor_rec.set_of_books_id :=cp_supplier.set_of_books_id;
lv_vendor_rec.summary_flag := 'N';
lv_vendor_rec.enabled_flag := 'Y';
--lv_vendor_rec.match_option:='P' ; --Match Option
----Api To Create Vendor
AP_VENDOR_PUB_PKG.CREATE_VENDOR
( p_api_version => 1,
p_vendor_rec => lv_vendor_rec,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data,
x_vendor_id => lv_vendor_id,
x_party_id => lv_party_id
);
IF (lv_return_status <> 'S') THEN
XXC_DISPLAY_MESSAGE('log','Encountered ERROR in supplier creation!!!');
XXC_DISPLAY_MESSAGE('log','--------------------------------------');
XXC_DISPLAY_MESSAGE('log',lv_msg_data);
IF lv_msg_count > 1 THEN
FOR i IN 1..lv_msg_count LOOP
XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
END IF;
COMMIT;
XXC_DISPLAY_MESSAGE('log','return_status: '||lv_return_status);
XXC_DISPLAY_MESSAGE('log','msg_data: '||lv_msg_data);
XXC_DISPLAY_MESSAGE('log','vendor_id: '||lv_vendor_id);
XXC_DISPLAY_MESSAGE('log','party_id: '||lv_party_id);
end LOOP; --------End Of Supppliers Loop
END XXC_CREATE_SUPPLIERS;
---------end of create suppliers------------------------
-------------------start of create supplier site------------------
PROCEDURE XXC_CREATE_SUPPLIER_SITES( p_vendor_number VARCHAR2 default null,
p_vendor_site VARCHAR2 default null)
is
lv_vendor_site_rec AP_VENDOR_PUB_PKG.R_VENDOR_SITE_REC_TYPE;
lv_return_status VARCHAR2(10);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2(1000);
lv_vendor_site_id NUMBER;
lv_party_site_id NUMBER;
lv_location_id NUMBER;
BEGIN
fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPL_ID);
--fnd_global.apps_initialize(0,20707,201);
---opening loop valid site data
FOR cp_site in c_site(p_vendor_number,p_vendor_site) LOOP
---QUERY TO GET VENDOR_ID
BEGIN
SELECT vendor_id
INTO lv_vendor_site_rec.vendor_id
FROM ap_suppliers
WHERE segment1 = cp_site.vendor_number ; --Vendor Number
EXCEPTION
WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','no supplier created'||sqlcode||' - '||sqlerrm);
END;
--QUERY TO GET ACCT_PAY_CODE_COMBINATION_ID
BEGIN
SELECT code_combination_id INTO
lv_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS_KFV
WHERE concatenated_segments=cp_site.liability_account;
EXCEPTION WHEN OTHERS
THEN
XXC_DISPLAY_MESSAGE('log','accts'||SQLCODE||SQLERRM);
END;
----
--QUERY TO GET ACCT_PAY_CODE_COMBINATION_ID
BEGIN
SELECT code_combination_id INTO
lv_vendor_site_rec.prepay_code_combination_id
FROM GL_CODE_COMBINATIONS_KFV
WHERE concatenated_segments=cp_site.prepay_account;
EXCEPTION WHEN OTHERS
THEN
XXC_DISPLAY_MESSAGE('log','accts1'||SQLCODE||SQLERRM);
END;
----
---QUERY TO GET ORG_ID
BEGIN
SELECT
ORGANIZATION_ID INTO
lv_vendor_site_rec.org_id FROM
HR_ALL_ORGANIZATION_UNITS where name =cp_site.operating_unit_name;
EXCEPTION WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','org_id'||SQLCODE||SQLERRM);
END;
-----------------
----------Assigning Staging Table Data To Reocrd Type :R_VENDOR_SITE_REC_TYPE
XXC_DISPLAY_MESSAGE('log','Vendor id is :'||lv_vendor_site_rec.vendor_id);
lv_vendor_site_rec.vendor_site_code :=cp_site.vendor_site; --Vendor Site Code
lv_vendor_site_rec.address_line1 := cp_site.address_line1; --Address line1
lv_vendor_site_rec.address_line2 :=cp_site.address_line2; --Address line2
lv_vendor_site_rec.address_line3 := cp_site.address_line3; --Address line3
lv_vendor_site_rec.address_line4 := cp_site.address_line4; --Address line4
lv_vendor_site_rec.city := cp_site.city; -----city
lv_vendor_site_rec.state := cp_site.state; ----state
lv_vendor_site_rec.zip := cp_site.zip_code; ------zip_code
lv_vendor_site_rec.country_of_origin_code := cp_site.country; --Country
lv_vendor_site_rec.country :=cp_site.country;
lv_vendor_site_rec.pay_group_lookup_code := cp_site.vendor_sites_pay_group;--pay group
lv_vendor_site_rec.terms_name := cp_site.vendor_sites_payment_terms;---terms
lv_vendor_site_rec.org_name := cp_site.operating_unit_name; -- operating unit name
lv_vendor_site_rec.payment_currency_code := cp_site.CURRENCY_CODE; ---.payment_currency_code
lv_vendor_site_rec.invoice_currency_code := cp_site.CURRENCY_CODE; ---invoice currency code
lv_vendor_site_rec.purchasing_site_flag :='Y';
lv_vendor_site_rec.pay_site_flag :='Y';
--Optional
--lv_vendor_site_rec.rfq_only_site_flag :='N';
------Api to create Venodr sites
AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE ( p_api_version => 1,
p_vendor_site_rec => lv_vendor_site_rec,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data,
x_vendor_site_id => lv_vendor_site_id,
x_party_site_id => lv_party_site_id,
x_location_id => lv_location_id
);
IF (lv_return_status <> 'S') THEN
XXC_DISPLAY_MESSAGE('log','Encountered ERROR in supplier site creation!!!');
XXC_DISPLAY_MESSAGE('log','--------------------------------------');
XXC_DISPLAY_MESSAGE('log',lv_msg_data);
IF lv_msg_count > 1 THEN
FOR i IN 1..lv_msg_count LOOP
XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
END IF;
COMMIT;
XXC_DISPLAY_MESSAGE('log','return_status: '||lv_return_status);
XXC_DISPLAY_MESSAGE('log','msg_data: '||lv_msg_data);
XXC_DISPLAY_MESSAGE('log','msg_data: '||lv_msg_count);
XXC_DISPLAY_MESSAGE('log','vendor_site_id: '||lv_vendor_site_id);
XXC_DISPLAY_MESSAGE('log','party_site_id: '||lv_party_site_id);
XXC_DISPLAY_MESSAGE('log','location_id: '||lv_location_id);
END LOOP; ------------------End of sites loop
END XXC_CREATE_SUPPLIER_SITES ;
-------------END of create supplier site--------------
----------------start of supplier contact--------------------
PROCEDURE XXC_CREATE_SUPPLIERS_CONTACTS( p_vendor_number VARCHAR2 default null,
p_vendor_site VARCHAR2 default null )
IS
lv_vendor_contact_rec AP_VENDOR_PUB_PKG.R_VENDOR_CONTACT_REC_TYPE;
lv_return_status VARCHAR2(10);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2(1000);
lv_vendor_id NUMBER;
lv_party_id NUMBER;
lv_vendor_contact_id NUMBER;
lv_per_party_id NUMBER;
lv_rel_party_id NUMBER;
lv_rel_id NUMBER;
lv_org_contact_id NUMBER;
lv_party_site_id NUMBER;
BEGIN
fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPL_ID);
--fnd_global.apps_initialize(0,20707,200);
--- opening loop for valid contacts
FOR cp_contact in c_contact(p_vendor_number,p_vendor_site) LOOP
--Query to get Vendor Id
BEGIN
SELECT vendor_id
INTO lv_vendor_contact_rec.vendor_id
FROM ap_suppliers
WHERE segment1 =cp_contact.vendor_number ; --Vendor Number
EXCEPTION WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','ERROR OCCURED DURING FETCHING VENDOR_ID'||' '||SQLCODE||' - '||SQLERRM);
END;
--XXC_DISPLAY_MESSAGE('log','Vendor id is :'||lv_vendor_contact_rec.vendor_id);
--Query to get Party Site Id
BEGIN
SELECT party_site_id
INTO lv_vendor_contact_rec.org_party_site_id
FROM ap_supplier_sites_all
WHERE vendor_site_code =cp_contact.vendor_site
AND vendor_id = lv_vendor_contact_rec.vendor_id ;
EXCEPTION WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','ERROR OCCURED DURING FETCHING OF VENDOR_SITE_CODE'||' '||SQLCODE||' - '||SQLERRM);
END;
---------QUERY TO GET ORG_ID
BEGIN
SELECT
ORGANIZATION_ID INTO
lv_vendor_contact_rec.org_id FROM
HR_ALL_ORGANIZATION_UNITS where name =cp_contact.operating_unit_name;
EXCEPTION WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
-----
XXC_DISPLAY_MESSAGE('log','Party site id is :'||lv_vendor_contact_rec.org_party_site_id);
----Assigning Values to Contacts record type from staging Table
lv_vendor_contact_rec.PERSON_FIRST_NAME := cp_contact.contact_first_name; --First Name
lv_vendor_contact_rec.PERSON_LAST_NAME := cp_contact.contact_last_name; --Last Name
lv_vendor_contact_rec.phone := cp_contact.contact_phone; --Phone Number
lv_vendor_contact_rec.email_address := cp_contact.contact_email ; --Email Address
lv_vendor_contact_rec.fax_phone := cp_contact.contact_fax; -----fax number
-------Api To Create Vendor Contacts
AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT
( p_api_version => 1,
p_vendor_contact_rec => lv_vendor_contact_rec,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data,
x_vendor_contact_id => lv_vendor_contact_id,
x_per_party_id => lv_per_party_id,
x_rel_party_id => lv_rel_party_id,
x_rel_id => lv_rel_id,
x_org_contact_id => lv_org_contact_id,
x_party_site_id => lv_party_site_id
);
IF (lv_return_status <> 'S') THEN
XXC_DISPLAY_MESSAGE('log','Encountered ERROR in supplier contact creation!!!');
XXC_DISPLAY_MESSAGE('log','--------------------------------------');
XXC_DISPLAY_MESSAGE('log',lv_msg_data);
IF lv_msg_count > 1 THEN
FOR i IN 1..lv_msg_count LOOP
XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
END IF;
COMMIT;
XXC_DISPLAY_MESSAGE('log','return_status: '||lv_return_status);
XXC_DISPLAY_MESSAGE('log','msg_data: '||lv_msg_data);
XXC_DISPLAY_MESSAGE('log','msg_data_count: '||lv_msg_count);
XXC_DISPLAY_MESSAGE('log','vendor Contact Id: '||lv_vendor_contact_id);
XXC_DISPLAY_MESSAGE('log','per_party_id: '||lv_per_party_id);
XXC_DISPLAY_MESSAGE('log','rel_party_id: '||lv_rel_party_id);
XXC_DISPLAY_MESSAGE('log','rel_id: '||lv_rel_id);
XXC_DISPLAY_MESSAGE('log','org_contact_id: '||lv_org_contact_id);
XXC_DISPLAY_MESSAGE('log','party_site_id: '||lv_party_site_id);
END LOOP;------------End of Contacts Loop
End XXC_CREATE_SUPPLIERS_CONTACTS;
------------------end of supplier contact-------------------------
----This is The main Procedure it will Create Records for All valid Suppliers,Sites and Contacts Information
-----start of main PROCEDURE -------------------------
PROCEDURE XXC_MAIN_SUPPLIER(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
p_vendor_number VARCHAR2 DEFAULT NULL,
p_vendor_site VARCHAR2 DEFAULT NULL)
is
BEGIN
fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPL_ID);
XXC_SUPPLIERS_VALIDATE(p_vendor_number);
XXC_CREATE_SUPPLIERS(p_vendor_number);
XXC_CREATE_SUPPLIER_SITES(p_vendor_number,p_vendor_site);
XXC_CREATE_SUPPLIERS_CONTACTS(p_vendor_number,p_vendor_site);
END xxc_main_supplier;
------------END of main supplier-------------
END XXC_SUP_CREATION_PKG;
/
Oracle Application and Cloud HCM
Tuesday 20 December 2016
Create New Supplier, Supplier Sites and Contact Information
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment