CREATE TABLE SAI_SUPPLIER_STAGE
(
RECORDNO NUMBER,
VENDOR_TYPE VARCHAR2(15),
RM VARCHAR2(5),
CAPITAL VARCHAR2(5),
ENGINEERING VARCHAR2(5),
SERVICES VARCHAR2(5),
VENDOR_NAME VARCHAR2(240),
SITE_NAME VARCHAR2(15) ,
INVOICE_CURRENCY_CODE VARCHAR2(15) ,
PAYMENT_CURRENCY_CODE VARCHAR2(15),
ADDRESS VARCHAR2(240) ,
CITY VARCHAR2 (25) ,
STATE VARCHAR2 (150) ,
COUNTRY VARCHAR2 (25) ,
PINCODE VARCHAR2 (20) ,
PAYMENT_METHOD VARCHAR2 (25) ,
--OPERATING_UNIT VARCHAR2 (25) ,
PAYMENT_TERM VARCHAR2 (50) ,
CST_REG_NO VARCHAR2 (50) ,
LST_REG_NO VARCHAR2 (50) ,
VAT_REG_NO VARCHAR2 (50) ,
SERVICE_TAX_REGNO VARCHAR2 (50) ,
EXCISE_RANGE VARCHAR2 (50) ,
EXCISE_ZONE VARCHAR2 (50) ,
COLLECRATE VARCHAR2 (50) ,
EXCISE_DIVISION VARCHAR2 (50) ,
EXCISE_REGION VARCHAR2 (50) ,
EXCISE_REGNO VARCHAR2 (50),
EXCISE_CIRCLE VARCHAR2 (50),
EC_CODE VARCHAR2 (50) ,
ERR_FLAG VARCHAR2(1),
ERR_MSG VARCHAR2(1000)
);
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create table sai_supplier_errors
(
recordno number,
table_name varchar2(20),
col_name varchar2(30),
err_flag varchar2(1),
err_msg varchar2(100)
);
CREATE OR REPLACE PROCEDURE SAI_LOCALIZATION(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2)
AS
CURSOR C1 IS SELECT * FROM SAI_SUPPLIER_STAGE WHERE ERR_FLAG IS NULL;
CURSOR C2(V_VENDOR VARCHAR2) IS SELECT APS.VENDOR_SITE_CODE,APS.VENDOR_SITE_ID
FROM AP_SUPPLIER_SITES_ALL APS,AP_SUPPLIERS AP
WHERE AP.VENDOR_NAME=V_VENDOR AND APS.VENDOR_ID=AP.VENDOR_ID;
V_VENDOR_ID AP_SUPPLIERS.VENDOR_ID%TYPE;
V_LOC_VENDOR_ID JAI_CMN_VENDOR_SITES.VENDOR_ID%TYPE;
V_LOC_SITE_ID AP_SUPPLIER_SITES_ALL.VENDOR_SITE_ID%TYPE;
V_COUNT NUMBER:=1;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Localization Details for the Below Vendors and Their Sites are Imported');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FOR R1 IN C1 LOOP
BEGIN
SELECT VENDOR_ID
INTO V_VENDOR_ID
FROM AP_SUPPLIERS
WHERE VENDOR_NAME=R1.VENDOR_NAME;
BEGIN
SELECT VENDOR_ID
INTO V_LOC_VENDOR_ID
FROM JAI_CMN_VENDOR_SITES
WHERE VENDOR_ID=V_VENDOR_ID
AND VENDOR_SITE_ID=0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JAI_CMN_VENDOR_SITES
(
VENDOR_ID ,
VENDOR_SITE_ID ,
EXCISE_DUTY_REGION ,
EXCISE_DUTY_ZONE ,
EXCISE_DUTY_REG_NO ,
EXCISE_DUTY_RANGE ,
EXCISE_DUTY_DIVISION ,
EXCISE_DUTY_CIRCLE,
EXCISE_DUTY_COMM ,
--TAX_CATEGORY_LIST
ST_REG_NO ,
CST_REG_NO,
EC_CODE,
--INACTIVE_FLAG
--PRICE_LIST_ID
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
APPROVED_INVOICE_FLAG ,
--VENDOR_TYPE,
--DOCUMENT_TYPE
OVERRIDE_FLAG,
VAT_REG_NO ,
SERVICE_TAX_REGNO
)
VALUES
(
V_VENDOR_ID,
0,
R1.EXCISE_REGION,
R1.EXCISE_ZONE,
R1.EXCISE_REGNO,
R1.EXCISE_RANGE,
R1.EXCISE_DIVISION,
R1.EXCISE_CIRCLE,
R1.COLLECRATE,
R1.LST_REG_NO,
R1.CST_REG_NO,
R1.EC_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.login_id,
'N',
'N',
R1.VAT_REG_NO,
R1.SERVICE_TAX_REGNO
);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,V_COUNT||'.'||R1.VENDOR_NAME);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'___________________');
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/****************** site level **************************/
FOR R2 IN C2(R1.VENDOR_NAME) LOOP
BEGIN
SELECT VENDOR_ID,VENDOR_SITE_ID
INTO V_LOC_VENDOR_ID,V_LOC_SITE_ID
FROM JAI_CMN_VENDOR_SITES
WHERE VENDOR_ID=V_VENDOR_ID
AND VENDOR_SITE_ID=R2.VENDOR_SITE_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JAI_CMN_VENDOR_SITES
(
VENDOR_ID ,
VENDOR_SITE_ID ,
EXCISE_DUTY_REGION ,
EXCISE_DUTY_ZONE ,
EXCISE_DUTY_REG_NO ,
EXCISE_DUTY_RANGE ,
EXCISE_DUTY_DIVISION ,
EXCISE_DUTY_CIRCLE,
EXCISE_DUTY_COMM ,
--TAX_CATEGORY_LIST
ST_REG_NO ,
CST_REG_NO,
EC_CODE,
--INACTIVE_FLAG
--PRICE_LIST_ID
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
APPROVED_INVOICE_FLAG ,
--VENDOR_TYPE,
--DOCUMENT_TYPE
OVERRIDE_FLAG,
VAT_REG_NO ,
SERVICE_TAX_REGNO )
VALUES
(
V_VENDOR_ID,
R2.VENDOR_SITE_ID,
R1.EXCISE_REGION,
R1.EXCISE_ZONE,
R1.EXCISE_REGNO,
R1.EXCISE_RANGE,
R1.EXCISE_DIVISION,
R1.EXCISE_CIRCLE,
R1.COLLECRATE,
R1.LST_REG_NO,
R1.CST_REG_NO,
R1.EC_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.login_id,
'N',
'N',
R1.VAT_REG_NO,
R1.SERVICE_TAX_REGNO
);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,R2.VENDOR_SITE_CODE);
END;
COMMIT;
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
COMMIT;
V_COUNT:=V_COUNT+1;
END LOOP;
COMMIT;
END SAI_LOCALIZATION;
/
/* This package is for validating the given supplier data and setting err_flag 'Y' to error records */
/***************** CONCURRENT PROGRAM NAME : CP_SAI_SUPPLIER_MAIN *****************/
CREATE OR REPLACE PACKAGE SAI_SUPPLIER_PACKAGE
AS
PROCEDURE VALID_VENDOR_NAME(PVNAME VARCHAR2,REC NUMBER);
PROCEDURE VALID_VENDOR_TYPE(PVENDOR_TYPE VARCHAR2,REC NUMBER);
PROCEDURE VALID_PAY_METHOD(PPAY_METHOD VARCHAR2,REC NUMBER);
PROCEDURE VALID_PAY_TERM(PPAY_TERM VARCHAR2,REC NUMBER);
PROCEDURE VALID_COUNTRY(PCOUNTRY VARCHAR2,REC NUMBER);
PROCEDURE VALID_INV_PAY_CURRENCY(PCURRENCY VARCHAR2,REC NUMBER);
PROCEDURE VALID_PINCODE(PPIN VARCHAR2);
--PROCEDURE VALID_TDS_VENDOR_TYPE(PTDS_VENDOR_TYPE VARCHAR2,REC NUMBER);
PROCEDURE SAI_SUPPLIER_MAIN(ERRBUF VARCHAR2,RETCODE NUMBER);
PROCEDURE UPDATE_SUP_ERRORS(ERR_F IN OUT VARCHAR2,ERR_M IN OUT VARCHAR2,REC NUMBER);
PROCEDURE INSERT_ERRORS(REC NUMBER,VTABNAME VARCHAR2,VCOLNAME VARCHAR2,ERR_F VARCHAR2,MSG VARCHAR2);
END SAI_SUPPLIER_PACKAGE;
/
/***************************** PACKAGE BODY *************************************/
CREATE OR REPLACE PACKAGE BODY SAI_SUPPLIER_PACKAGE
AS
ERR_M VARCHAR2(1000):=NULL;
ERR_F VARCHAR2(1):=NULL;
MSG VARCHAR2(200):=NULL;
V_PIN AP_SUPPLIER_SITES_ALL.ZIP%TYPE; --variable to capture the pincode.
VPAY_TERM AP_TERMS_TL.TERM_ID%TYPE;
/**************************************** CALLLING PROCEDURE ***********************************/
PROCEDURE SAI_SUPPLIER_MAIN(ERRBUF VARCHAR2,RETCODE NUMBER)
AS
CURSOR C1 IS SELECT * FROM SAI_SUPPLIER_STAGE; --Cursor to get all the supplier data from stage table.
CURSOR C2 IS SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS; --Cursor to get all the Operating Units.
VREQ_ID1 NUMBER; --variable to get the req ID of import supplier concurrent program.
VREQ_ID2 NUMBER; --variable to get the req ID of supplier site concurrent program.
VREQ_ID3 NUMBER;
FUN1 BOOLEAN;
VPHASE VARCHAR2(10):=NULL;
VSTATUS VARCHAR2(10):=NULL;
VDEV_PHASE VARCHAR2(10):=NULL;
VDEV_STATUS VARCHAR2(10):=NULL;
VMSG VARCHAR2(10):=NULL;
V_COUNTRY VARCHAR2(2);
V_SITE VARCHAR2(15);
ADDR1 AP_SUPPLIER_SITES_ALL.ADDRESS_LINE1%TYPE; --variable to capture the address_line1.
ADDR2 AP_SUPPLIER_SITES_ALL.ADDRESS_LINE2%TYPE; --variable to capture the address_line2.
ADDR3 AP_SUPPLIER_SITES_ALL.ADDRESS_LINE3%TYPE; --variable to capture the address_line3.
ADDR4 AP_SUPPLIER_SITES_ALL.ADDRESS_LINE4%TYPE; --variable to capture the address_line4.
V_LIABILITY_ACCT GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
V_PREPAY_ACCT GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
BEGIN
FOR R1 IN C1 LOOP
MSG:=NULL;
ERR_F:=NULL;
ERR_M:=NULL;
V_PIN:=NULL;
VALID_VENDOR_NAME(R1.VENDOR_NAME,R1.RECORDNO);
VALID_VENDOR_TYPE(R1.VENDOR_TYPE,R1.RECORDNO);
VALID_PAY_METHOD(R1.PAYMENT_METHOD,R1.RECORDNO);
VALID_PAY_TERM(R1.PAYMENT_TERM,R1.RECORDNO);
VALID_PAY_METHOD(R1.PAYMENT_METHOD,R1.RECORDNO);
VALID_PAY_TERM(R1.PAYMENT_TERM,R1.RECORDNO);
VALID_COUNTRY(R1.COUNTRY,R1.RECORDNO);
VALID_INV_PAY_CURRENCY(R1.INVOICE_CURRENCY_CODE,R1.RECORDNO);
VALID_INV_PAY_CURRENCY(R1.PAYMENT_CURRENCY_CODE,R1.RECORDNO);
VALID_PINCODE(R1.PINCODE);
IF R1.SITE_NAME IS NULL THEN --SITE_CODE is a mandatory column.
ERR_M:=ERR_M||'NULL AT SITE_NAME';
ERR_F:='Y';
MSG:='NULLS AT SITE_NAME';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,R1.RECORDNO);
INSERT_ERRORS(R1.RECORDNO,'SAI_SUPPLIER_STAGE','NULL AT SITE_NAME /',ERR_F,MSG);
END IF;
IF R1.ADDRESS IS NULL THEN --ADDRESS_LINE1 is a mandatory column.
ERR_M:=ERR_M||'NULLS AT ADDRESS/';
ERR_F:='Y';
MSG:='NULLS AT ADDRESS/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,R1.RECORDNO);
INSERT_ERRORS(R1.RECORDNO,'SAI_SUPPLIER_STAGE','NULL AT ADDRESS/',ERR_F,MSG);
ELSE --If the Address is NOT NULL ,Splitting the Address into 4 lines.
IF INSTR(R1.ADDRESS,',',1,1)!=0 THEN
ADDR1:=SUBSTR(R1.ADDRESS,1,INSTR(R1.ADDRESS,',',1,1)-1);
IF INSTR(R1.ADDRESS,',',1,2)!=0 THEN
ADDR2:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,1)+1,INSTR(R1.ADDRESS,',',1,2)-INSTR(R1.ADDRESS,',',1,1)-1);
IF INSTR(R1.ADDRESS,',',1,3)!=0 THEN
ADDR3:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,2)+1,INSTR(R1.ADDRESS,',',1,3)-INSTR(R1.ADDRESS,',',1,2)-1);
IF INSTR(R1.ADDRESS,',',1,4)!=0 then
ADDR4:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,3)+1);
ELSE
ADDR4:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,3)+1);
END IF;
ELSE
ADDR3:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,2)+1);
END IF;
ELSE
ADDR2:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,1)+1);
END IF;
ELSE
ADDR1:=R1.ADDRESS;
END IF;
END IF;
IF MSG IS NULL THEN --IF statement to check whether there are any errors in the current record.
INSERT INTO AP_SUPPLIERS_INT --Interface insert to import Suppliers.
(
VENDOR_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_NAME, --Mandatory
CREATION_DATE,
CREATED_BY,
VENDOR_TYPE_LOOKUP_CODE, --Mandatory
TERMS_ID,
PAYMENT_METHOD_LOOKUP_CODE,
CREATE_DEBIT_MEMO_FLAG
)
VALUES
(
AP_SUPPLIERS_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
R1.VENDOR_NAME,
SYSDATE,
FND_GLOBAL.USER_ID,
R1.VENDOR_TYPE,
VPAY_TERM ,
R1.PAYMENT_METHOD,
'Y'
);
--COMMIT;
SELECT TERRITORY_CODE
INTO V_COUNTRY FROM FND_TERRITORIES_TL
WHERE TERRITORY_SHORT_NAME=R1.COUNTRY;
FOR R2 IN C2 LOOP -- Begin the cursor to import the site n its contact to all OperatingUnits.
IF UPPER(R1.RM)='YES' THEN -- If RM is YES ,import site for Raw Material
V_SITE:=R1.SITE_NAME||'-RM';
IF R2.ORGANIZATION_ID=81 THEN --IF block to get the RM ccids
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 81 and RM
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142219' AND SEGMENT2='01';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 81 and RM
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234129' AND SEGMENT2='01';
ELSIF R2.ORGANIZATION_ID=82 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 82 and RM
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142219' AND SEGMENT2='03';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 82 and RM
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234129' AND SEGMENT2='03';
ELSIF R2.ORGANIZATION_ID=423 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and RM
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142219' AND SEGMENT2='04';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and RM
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234129' AND SEGMENT2='04';
ELSIF R2.ORGANIZATION_ID=363 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 363 and RM
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142219' AND SEGMENT2='00';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 363 and RM
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234129' AND SEGMENT2='00';
END IF;
INSERT INTO AP_SUPPLIER_SITES_INT --Interface insert to import Sites
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_CODE,
CREATION_DATE,
CREATED_BY,
ADDRESS_LINE1, --Mandatory
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY, --Mandatory
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
TERMS_ID,
ORG_ID, --Operating Unit ID
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_METHOD_LOOKUP_CODE,
ZIP,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES
(
AP_SUPPLIERS_INT_S.CURRVAL,
AP_SUPPLIER_SITES_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
V_SITE,
SYSDATE,
FND_GLOBAL.USER_ID,
ADDR1,
ADDR2,
ADDR3,
ADDR4,
R1.CITY,
R1.STATE,
v_country,
'Y',
'Y',
VPAY_TERM,
R2.ORGANIZATION_ID,
R1.INVOICE_CURRENCY_CODE,
R1.PAYMENT_CURRENCY_CODE,
R1.PAYMENT_METHOD,
V_PIN,
V_LIABILITY_ACCT,
V_PREPAY_ACCT
);
END IF;
IF UPPER(R1.CAPITAL)='YES' THEN -- If CAPITAL is YES ,import site for Capital
V_SITE:=R1.SITE_NAME||'-CAP';
IF R2.ORGANIZATION_ID=81 THEN --IF block to get the CAP ccids
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 81 and CAP
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142319' AND SEGMENT2='01';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 81 and CAP
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234199' AND SEGMENT2='01';
ELSIF R2.ORGANIZATION_ID=82 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 82 and CAP
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142319' AND SEGMENT2='03';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 82 and CAP
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234199' AND SEGMENT2='03';
ELSIF R2.ORGANIZATION_ID=423 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 423 and CAP
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142319' AND SEGMENT2='04';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and CAP
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234199' AND SEGMENT2='04';
ELSIF R2.ORGANIZATION_ID=363 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 363 and CAP
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142319' AND SEGMENT2='00';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 363 and CAP
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234199' AND SEGMENT2='00';
END IF;
INSERT INTO AP_SUPPLIER_SITES_INT --Interface insert to import Sites
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_CODE,
CREATION_DATE,
CREATED_BY,
ADDRESS_LINE1, --Mandatory
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY, --Mandatory
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
TERMS_ID,
ORG_ID, --Operating Unit ID
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_METHOD_LOOKUP_CODE,
ZIP,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES
(
AP_SUPPLIERS_INT_S.CURRVAL,
AP_SUPPLIER_SITES_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
V_SITE,
SYSDATE,
FND_GLOBAL.USER_ID,
ADDR1,
ADDR2,
ADDR3,
ADDR4,
R1.CITY,
R1.STATE,
v_country,
'Y',
'Y',
VPAY_TERM,
R2.ORGANIZATION_ID,
R1.INVOICE_CURRENCY_CODE,
R1.PAYMENT_CURRENCY_CODE,
R1.PAYMENT_METHOD,
V_PIN,
V_LIABILITY_ACCT,
V_PREPAY_ACCT
);
END IF;
IF UPPER(R1.ENGINEERING)='YES' THEN -- If ENGINEERING is YES ,import site for Engineering
V_SITE:=R1.SITE_NAME||'-ENG';
IF R2.ORGANIZATION_ID=81 THEN --IF block to get the ENG ccids
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 81 and ENG
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142619' AND SEGMENT2='01';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 81 and ENG
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='01';
ELSIF R2.ORGANIZATION_ID=82 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 82 and ENG
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142619' AND SEGMENT2='03';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 82 and ENG
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='03';
ELSIF R2.ORGANIZATION_ID=423 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 423 and ENG
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142619' AND SEGMENT2='04';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and ENG
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='04';
ELSIF R2.ORGANIZATION_ID=363 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 363 and ENG
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142619' AND SEGMENT2='00';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 363 and ENG
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='00';
END IF;
INSERT INTO AP_SUPPLIER_SITES_INT --Interface insert to import Sites
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_CODE,
CREATION_DATE,
CREATED_BY,
ADDRESS_LINE1, --Mandatory
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY, --Mandatory
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
TERMS_ID,
ORG_ID, --Operating Unit ID
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_METHOD_LOOKUP_CODE,
ZIP,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES
(
AP_SUPPLIERS_INT_S.CURRVAL,
AP_SUPPLIER_SITES_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
V_SITE,
SYSDATE,
FND_GLOBAL.USER_ID,
ADDR1,
ADDR2,
ADDR3,
ADDR4,
R1.CITY,
R1.STATE,
v_country,
'Y',
'Y',
VPAY_TERM,
R2.ORGANIZATION_ID,
R1.INVOICE_CURRENCY_CODE,
R1.PAYMENT_CURRENCY_CODE,
R1.PAYMENT_METHOD,
V_PIN,
V_LIABILITY_ACCT,
V_PREPAY_ACCT
);
END IF;
IF UPPER(R1.SERVICES)='YES' THEN -- If SERVICES is YES ,import site for Services
V_SITE:=R1.SITE_NAME||'-SER';
IF R2.ORGANIZATION_ID=81 THEN --IF block to get the SER ccids
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 81 and SER
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142419' AND SEGMENT2='01';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 81 and SER
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='01';
ELSIF R2.ORGANIZATION_ID=82 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 82 and SER
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142419' AND SEGMENT2='03';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 82 and SER
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='03';
ELSIF R2.ORGANIZATION_ID=423 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 423 and SER
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142419' AND SEGMENT2='04';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and SER
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='04';
ELSIF R2.ORGANIZATION_ID=363 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 363 and SER
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142419' AND SEGMENT2='00';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 363 and SER
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='00';
END IF;
INSERT INTO AP_SUPPLIER_SITES_INT --Interface insert to import Sites
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_CODE,
CREATION_DATE,
CREATED_BY,
ADDRESS_LINE1, --Mandatory
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY, --Mandatory
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
TERMS_ID,
ORG_ID, --Operating Unit ID
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_METHOD_LOOKUP_CODE,
ZIP,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES
(
AP_SUPPLIERS_INT_S.CURRVAL,
AP_SUPPLIER_SITES_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
V_SITE,
SYSDATE,
FND_GLOBAL.USER_ID,
ADDR1,
ADDR2,
ADDR3,
ADDR4,
R1.CITY,
R1.STATE,
v_country,
'Y',
'Y',
VPAY_TERM,
R2.ORGANIZATION_ID,
R1.INVOICE_CURRENCY_CODE,
R1.PAYMENT_CURRENCY_CODE,
R1.PAYMENT_METHOD,
V_PIN,
V_LIABILITY_ACCT,
V_PREPAY_ACCT
);
END IF;
END LOOP;
END IF;
END LOOP;
COMMIT;
/********************************** Submitting request for suppliers import *******************************************/
VREQ_ID1:=FND_REQUEST.SUBMIT_REQUEST(
'SQLAP',
'APXSUIMP',
'Submitting request for suppliers import',
NULL,
FALSE,
'NEW',
1000,
'N',
'N',
'N'
);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'IMPORTING VENDORS. REQ ID :'||VREQ_ID1);
FUN1:=FND_CONCURRENT.WAIT_FOR_REQUEST(
VREQ_ID1,
1,
0,
VPHASE,
VSTATUS,
VDEV_PHASE,
VDEV_STATUS,
VMSG
);
COMMIT;
/********************************** submitting request for sites import ***********************************************/
VREQ_ID2:=FND_REQUEST.SUBMIT_REQUEST('SQLAP',
'APXSSIMP',
'submitting request for sites import',
NULL,
FALSE,
'NEW',
1000,
'N',
'N',
'N');
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'IMPORTING VENDOR SITES. REQ ID :'||VREQ_ID2);
FUN1:=FND_CONCURRENT.WAIT_FOR_REQUEST(
VREQ_ID2,
1,
0,
VPHASE,
VSTATUS,
VDEV_PHASE,
VDEV_STATUS,
VMSG
);
COMMIT;
/********************************** submitting request for importing localization details ***********************************************/
/*
VREQ_ID3:=FND_REQUEST.SUBMIT_REQUEST(
'SQLAP',
'CP_SAI_LOCALIZATION',
'submitting request for supplier localization details import',
NULL,
FALSE
);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'IMPORTING SUPPLIER LOCALIZATION DETAILS REQ ID :'||VREQ_ID3);
*/
END SAI_SUPPLIER_MAIN;
/***************************************PROCEDURE FOR VALIDATING VENDOR_NAME ********************************************/
PROCEDURE VALID_VENDOR_NAME(PVNAME VARCHAR2,REC NUMBER)
AS
VNAME AP_SUPPLIERS.VENDOR_NAME%TYPE;
BEGIN
IF PVNAME IS NOT NULL THEN
BEGIN
SELECT VENDOR_NAME
INTO VNAME
FROM AP_SUPPLIERS
WHERE UPPER(VENDOR_NAME)=UPPER(PVNAME);
ERR_F:='Y';
ERR_M:=ERR_M||'VENDOR_NAME: SUPPLIER ALREADY EXISTS';
MSG:='SUPPLIER ALREADY EXISTS';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_NAME',ERR_F,MSG);
EXCEPTION
WHEN NO_DATA_FOUND THEN
--DBMS_OUTPUT.PUT_LINE(VNAME||' VALID SUPPLIER');
NULL;
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_NAME',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||' *NULL AT VENDOR_NAME';
MSG:='NULL AT VENDOR_NAME';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_NAME',ERR_F,MSG);
END IF;
END;
/*************************************** PROCEDURE FOR VALIDATING VENDOR TYPE ***********************************/
PROCEDURE VALID_VENDOR_TYPE(PVENDOR_TYPE VARCHAR2,REC NUMBER)
AS
VVENDOR_TYPE FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
BEGIN
IF PVENDOR_TYPE IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO VVENDOR_TYPE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='VENDOR TYPE'
AND LOOKUP_CODE=PVENDOR_TYPE
AND VIEW_APPLICATION_ID=201;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID VENDOR TYPE/';
ERR_F:='Y';
MSG:='INVALID VENDOR TYPE/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_TYPE',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_TYPE',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||' *NULL AT VENDOR_TYPE';
MSG:='NULL AT VENDOR_TYPE';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_TYPE',ERR_F,MSG);
END IF;
END;
/* ************************************ PROCEDURE FOR VALIDATING COUNTRY ************************************ */
PROCEDURE VALID_COUNTRY(PCOUNTRY VARCHAR2,REC NUMBER)
AS
VCOUNTRY FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%TYPE;
BEGIN
IF PCOUNTRY IS NOT NULL THEN
BEGIN
SELECT TERRITORY_CODE
INTO VCOUNTRY
FROM FND_TERRITORIES_TL
WHERE TERRITORY_SHORT_NAME=PCOUNTRY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID COUNTRY CODE/';
ERR_F:='Y';
MSG:='INVALID COUNTRY CODE/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','COUNTRY',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','COUNTRY',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||' *NULL AT COUNTRY';
MSG:='NULL AT COUNTRY';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','COUNTRY',ERR_F,MSG);
END IF;
END;
/**************************** PROCEDURE FOR VALIDATING INVOICE AND PAYMENT CURRENCY CODE ****************************************/
PROCEDURE VALID_INV_PAY_CURRENCY(PCURRENCY VARCHAR2,REC NUMBER)
AS
VCURRENCY FND_CURRENCIES.CURRENCY_CODE%TYPE;
BEGIN
IF PCURRENCY IS NOT NULL THEN
BEGIN
SELECT CURRENCY_CODE
INTO VCURRENCY
FROM FND_CURRENCIES
WHERE CURRENCY_CODE=PCURRENCY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID CURRENCY CODE/';
ERR_F:='Y';
MSG:='INVALID CURRENCY CODE/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','INV/PAY CURRENCY_CODE',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','INV/PAY CURRENCY_CODE',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||'*NULL AT CURRENCY_CODE';
MSG:='NULL AT CURRENCY_CODE';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','INV/PAY CURRENCY_CODE',ERR_F,MSG);
END IF;
END;
/**************************** PROCEDURE FOR VALIDATING PAYMENT METHOD****************************************/
PROCEDURE VALID_PAY_METHOD(PPAY_METHOD VARCHAR2,REC NUMBER)
AS
VPAY_METHOD FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
BEGIN
IF PPAY_METHOD IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO VPAY_METHOD
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='PAYMENT METHOD'
AND LOOKUP_CODE=PPAY_METHOD
AND VIEW_APPLICATION_ID=200;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID PAYMENT METHOD/';
ERR_F:='Y';
MSG:='INVALID PAYMENT METHOD/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PAYMENT METHOD',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PYMENT METHOD',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||'*NULL AT PAYMENT METHOD';
MSG:='NULL AT PAYMENT METHOD';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PAYMENT METHOD',ERR_F,MSG);
END IF;
END;
/**************************** PROCEDURE FOR VALIDATING PAYMENT TERM****************************************/
PROCEDURE VALID_PAY_TERM(PPAY_TERM VARCHAR2,REC NUMBER)
AS
BEGIN
IF PPAY_TERM IS NOT NULL THEN
BEGIN
SELECT TERM_ID
INTO VPAY_TERM
FROM AP_TERMS_TL
WHERE NAME=PPAY_TERM;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID PAYMENT TERM/';
ERR_F:='Y';
MSG:='INVALID PAYMENT TERM/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PAYMENT TERM',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PYMENT TERM',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||'*NULL AT PAYMENT TERM';
MSG:='NULL AT PAYMENT TERM';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PAYMENT TERM',ERR_F,MSG);
END IF;
END;
/***************************************** PROCEDURE FOR VALIDATING TDS VENDOR TYPE **********************************/
/*
PROCEDURE VALID_TDS_VENDOR_TYPE(PTDS_VENDOR_TYPE VARCHAR2,REC NUMBER)
AS
VTDS_VENDOR_TYPE FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
BEGIN
IF PTDS_VENDOR_TYPE IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO VTDS_VENDOR_TYPE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='JAI_TDS_VENDOR_TYPE'
AND LOOKUP_CODE=PTDS_VENDOR_TYPE
AND VIEW_APPLICATION_ID=7000; --APPL.NAME:Asia/Pacific Localizations,APPL.SHOT.NAME:JA
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID TDS VENDOR TYPE/';
ERR_F:='Y';
MSG:='INVALID TDS VENDOR TYPE/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','TDS_VENDOR_TYPE',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','TDS_VENDOR_TYPE',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||'*NULL AT TDS VENDOR TYPE';
MSG:='NULL AT TDS VENDOR TYPE';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','TDS_VENDOR_TYPE',ERR_F,MSG);
END IF;
END;
*/
/************************ PROCEDURE TO REMOVE SPECIAL CHARECTERS FROM PINCODE ******************************/
PROCEDURE VALID_PINCODE(PPIN VARCHAR2)
AS
V NUMBER;
BEGIN -- block to remove the spaces,hiphens and fullstops from the pincode.
IF PPIN IS NOT NULL THEN
FOR I IN 1..LENGTH(PPIN) LOOP
IF ASCII(SUBSTR(PPIN,I,1))=45 OR ASCII(SUBSTR(PPIN,I,1))=46 OR ASCII(SUBSTR(PPIN,I,1))=32 THEN
NULL;
ELSE
V_PIN:=V_PIN||SUBSTR(PPIN,I,1);
END IF;
END LOOP;
END IF;
END;
/*******************PROCEDURE FOR UPDATING ERRORS IN THE SUPPLIER STAGE TABLE *******************************/
PROCEDURE UPDATE_SUP_ERRORS(ERR_F IN OUT VARCHAR2,ERR_M IN OUT VARCHAR2,REC NUMBER)
AS
BEGIN
UPDATE SAI_SUPPLIER_STAGE
SET ERR_FLAG=ERR_F,ERR_MSG=ERR_M
WHERE RECORDNO=REC;
COMMIT;
END;
/*******************************PROCEDURE FOR INSERTING INTO ERROR TABLE****************************/
PROCEDURE INSERT_ERRORS(REC NUMBER,VTABNAME VARCHAR2,VCOLNAME VARCHAR2,ERR_F VARCHAR2,MSG VARCHAR2)
AS
BEGIN
INSERT INTO SAI_SUPPLIER_ERRORS
VALUES(REC,VTABNAME,VCOLNAME,ERR_F,MSG);
COMMIT;
END;
END SAI_SUPPLIER_PACKAGE;
/
control file
-------------------------------
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'C:\VENDOR_FINAL.txt'
INSERT
INTO TABLE SAI_SUPPLIER_STAGE
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORDNO SEQUENCE(1,1),
VENDOR_TYPE,
RM ,
CAPITAL ,
ENGINEERING ,
SERVICES ,
VENDOR_NAME "DECODE(:VENDOR_NAME,'NA',NULL,'0',NULL,:VENDOR_NAME)",
SITE_NAME "DECODE(:SITE_NAME,'NA',NULL,'0',NULL,:SITE_NAME)",
INVOICE_CURRENCY_CODE "DECODE(:INVOICE_CURRENCY_CODE,'NA',NULL,'0',NULL,:INVOICE_CURRENCY_CODE)",
PAYMENT_CURRENCY_CODE "DECODE(:PAYMENT_CURRENCY_CODE,'NA',NULL,'0',NULL,:PAYMENT_CURRENCY_CODE)",
ADDRESS "DECODE(:ADDRESS,'NA',NULL,'0',NULL,:ADDRESS)",
CITY "DECODE(:CITY,'NA',NULL,'0',NULL,:CITY)",
STATE "DECODE(:STATE,'NA',NULL,'0',NULL,:STATE)",
COUNTRY "DECODE(:COUNTRY,'NA',NULL,'0',NULL,:COUNTRY)",
PINCODE "DECODE(:PINCODE,'NA',NULL,'0',NULL,:PINCODE)",
PAYMENT_METHOD "DECODE(:PAYMENT_METHOD,'NA',NULL,'0',NULL,:PAYMENT_METHOD)",
OPERATING_UNIT FILLER ,
PAYMENT_TERM "DECODE(:PAYMENT_TERM,'NA',NULL,'0',NULL,:PAYMENT_TERM)",
CST_REG_NO "DECODE(:CST_REG_NO,'NA',NULL,'0',NULL,:CST_REG_NO)" ,
LST_REG_NO "DECODE(:LST_REG_NO,'NA',NULL,'0',NULL,:LST_REG_NO)",
VAT_REG_NO "DECODE(:VAT_REG_NO,'NA',NULL,'0',NULL,:VAT_REG_NO)",
SERVICE_TAX_REGNO "DECODE(:SERVICE_TAX_REGNO,'NA',NULL,'0',NULL,:SERVICE_TAX_REGNO)",
EXCISE_RANGE "DECODE(:EXCISE_RANGE,'NA',NULL,'0',NULL,:EXCISE_RANGE)",
EXCISE_ZONE "DECODE(:EXCISE_ZONE,'NA',NULL,'0',NULL,:EXCISE_ZONE)" ,
COLLECRATE "DECODE(:COLLECRATE,'NA',NULL,'0',NULL,:COLLECRATE)" ,
EXCISE_DIVISION "DECODE(:EXCISE_DIVISION,'NA',NULL,'0',NULL,:EXCISE_DIVISION)",
EXCISE_REGION "DECODE(:EXCISE_REGION,'NA',NULL,'0',NULL,:EXCISE_REGION)" ,
EXCISE_REGNO "DECODE(:EXCISE_REGNO,'NA',NULL,'0',NULL,:EXCISE_REGNO)",
EXCISE_CIRCLE "DECODE(:EXCISE_CIRCLE,'NA',NULL,'0',NULL,:EXCISE_CIRCLE)",
EC_CODE "DECODE(:EC_CODE,'NA',NULL,'0',NULL,:EC_CODE)",
)
(
RECORDNO NUMBER,
VENDOR_TYPE VARCHAR2(15),
RM VARCHAR2(5),
CAPITAL VARCHAR2(5),
ENGINEERING VARCHAR2(5),
SERVICES VARCHAR2(5),
VENDOR_NAME VARCHAR2(240),
SITE_NAME VARCHAR2(15) ,
INVOICE_CURRENCY_CODE VARCHAR2(15) ,
PAYMENT_CURRENCY_CODE VARCHAR2(15),
ADDRESS VARCHAR2(240) ,
CITY VARCHAR2 (25) ,
STATE VARCHAR2 (150) ,
COUNTRY VARCHAR2 (25) ,
PINCODE VARCHAR2 (20) ,
PAYMENT_METHOD VARCHAR2 (25) ,
--OPERATING_UNIT VARCHAR2 (25) ,
PAYMENT_TERM VARCHAR2 (50) ,
CST_REG_NO VARCHAR2 (50) ,
LST_REG_NO VARCHAR2 (50) ,
VAT_REG_NO VARCHAR2 (50) ,
SERVICE_TAX_REGNO VARCHAR2 (50) ,
EXCISE_RANGE VARCHAR2 (50) ,
EXCISE_ZONE VARCHAR2 (50) ,
COLLECRATE VARCHAR2 (50) ,
EXCISE_DIVISION VARCHAR2 (50) ,
EXCISE_REGION VARCHAR2 (50) ,
EXCISE_REGNO VARCHAR2 (50),
EXCISE_CIRCLE VARCHAR2 (50),
EC_CODE VARCHAR2 (50) ,
ERR_FLAG VARCHAR2(1),
ERR_MSG VARCHAR2(1000)
);
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create table sai_supplier_errors
(
recordno number,
table_name varchar2(20),
col_name varchar2(30),
err_flag varchar2(1),
err_msg varchar2(100)
);
CREATE OR REPLACE PROCEDURE SAI_LOCALIZATION(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2)
AS
CURSOR C1 IS SELECT * FROM SAI_SUPPLIER_STAGE WHERE ERR_FLAG IS NULL;
CURSOR C2(V_VENDOR VARCHAR2) IS SELECT APS.VENDOR_SITE_CODE,APS.VENDOR_SITE_ID
FROM AP_SUPPLIER_SITES_ALL APS,AP_SUPPLIERS AP
WHERE AP.VENDOR_NAME=V_VENDOR AND APS.VENDOR_ID=AP.VENDOR_ID;
V_VENDOR_ID AP_SUPPLIERS.VENDOR_ID%TYPE;
V_LOC_VENDOR_ID JAI_CMN_VENDOR_SITES.VENDOR_ID%TYPE;
V_LOC_SITE_ID AP_SUPPLIER_SITES_ALL.VENDOR_SITE_ID%TYPE;
V_COUNT NUMBER:=1;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Localization Details for the Below Vendors and Their Sites are Imported');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FOR R1 IN C1 LOOP
BEGIN
SELECT VENDOR_ID
INTO V_VENDOR_ID
FROM AP_SUPPLIERS
WHERE VENDOR_NAME=R1.VENDOR_NAME;
BEGIN
SELECT VENDOR_ID
INTO V_LOC_VENDOR_ID
FROM JAI_CMN_VENDOR_SITES
WHERE VENDOR_ID=V_VENDOR_ID
AND VENDOR_SITE_ID=0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JAI_CMN_VENDOR_SITES
(
VENDOR_ID ,
VENDOR_SITE_ID ,
EXCISE_DUTY_REGION ,
EXCISE_DUTY_ZONE ,
EXCISE_DUTY_REG_NO ,
EXCISE_DUTY_RANGE ,
EXCISE_DUTY_DIVISION ,
EXCISE_DUTY_CIRCLE,
EXCISE_DUTY_COMM ,
--TAX_CATEGORY_LIST
ST_REG_NO ,
CST_REG_NO,
EC_CODE,
--INACTIVE_FLAG
--PRICE_LIST_ID
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
APPROVED_INVOICE_FLAG ,
--VENDOR_TYPE,
--DOCUMENT_TYPE
OVERRIDE_FLAG,
VAT_REG_NO ,
SERVICE_TAX_REGNO
)
VALUES
(
V_VENDOR_ID,
0,
R1.EXCISE_REGION,
R1.EXCISE_ZONE,
R1.EXCISE_REGNO,
R1.EXCISE_RANGE,
R1.EXCISE_DIVISION,
R1.EXCISE_CIRCLE,
R1.COLLECRATE,
R1.LST_REG_NO,
R1.CST_REG_NO,
R1.EC_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.login_id,
'N',
'N',
R1.VAT_REG_NO,
R1.SERVICE_TAX_REGNO
);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,V_COUNT||'.'||R1.VENDOR_NAME);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'___________________');
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/****************** site level **************************/
FOR R2 IN C2(R1.VENDOR_NAME) LOOP
BEGIN
SELECT VENDOR_ID,VENDOR_SITE_ID
INTO V_LOC_VENDOR_ID,V_LOC_SITE_ID
FROM JAI_CMN_VENDOR_SITES
WHERE VENDOR_ID=V_VENDOR_ID
AND VENDOR_SITE_ID=R2.VENDOR_SITE_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JAI_CMN_VENDOR_SITES
(
VENDOR_ID ,
VENDOR_SITE_ID ,
EXCISE_DUTY_REGION ,
EXCISE_DUTY_ZONE ,
EXCISE_DUTY_REG_NO ,
EXCISE_DUTY_RANGE ,
EXCISE_DUTY_DIVISION ,
EXCISE_DUTY_CIRCLE,
EXCISE_DUTY_COMM ,
--TAX_CATEGORY_LIST
ST_REG_NO ,
CST_REG_NO,
EC_CODE,
--INACTIVE_FLAG
--PRICE_LIST_ID
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
APPROVED_INVOICE_FLAG ,
--VENDOR_TYPE,
--DOCUMENT_TYPE
OVERRIDE_FLAG,
VAT_REG_NO ,
SERVICE_TAX_REGNO )
VALUES
(
V_VENDOR_ID,
R2.VENDOR_SITE_ID,
R1.EXCISE_REGION,
R1.EXCISE_ZONE,
R1.EXCISE_REGNO,
R1.EXCISE_RANGE,
R1.EXCISE_DIVISION,
R1.EXCISE_CIRCLE,
R1.COLLECRATE,
R1.LST_REG_NO,
R1.CST_REG_NO,
R1.EC_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.login_id,
'N',
'N',
R1.VAT_REG_NO,
R1.SERVICE_TAX_REGNO
);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,R2.VENDOR_SITE_CODE);
END;
COMMIT;
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
COMMIT;
V_COUNT:=V_COUNT+1;
END LOOP;
COMMIT;
END SAI_LOCALIZATION;
/
/* This package is for validating the given supplier data and setting err_flag 'Y' to error records */
/***************** CONCURRENT PROGRAM NAME : CP_SAI_SUPPLIER_MAIN *****************/
CREATE OR REPLACE PACKAGE SAI_SUPPLIER_PACKAGE
AS
PROCEDURE VALID_VENDOR_NAME(PVNAME VARCHAR2,REC NUMBER);
PROCEDURE VALID_VENDOR_TYPE(PVENDOR_TYPE VARCHAR2,REC NUMBER);
PROCEDURE VALID_PAY_METHOD(PPAY_METHOD VARCHAR2,REC NUMBER);
PROCEDURE VALID_PAY_TERM(PPAY_TERM VARCHAR2,REC NUMBER);
PROCEDURE VALID_COUNTRY(PCOUNTRY VARCHAR2,REC NUMBER);
PROCEDURE VALID_INV_PAY_CURRENCY(PCURRENCY VARCHAR2,REC NUMBER);
PROCEDURE VALID_PINCODE(PPIN VARCHAR2);
--PROCEDURE VALID_TDS_VENDOR_TYPE(PTDS_VENDOR_TYPE VARCHAR2,REC NUMBER);
PROCEDURE SAI_SUPPLIER_MAIN(ERRBUF VARCHAR2,RETCODE NUMBER);
PROCEDURE UPDATE_SUP_ERRORS(ERR_F IN OUT VARCHAR2,ERR_M IN OUT VARCHAR2,REC NUMBER);
PROCEDURE INSERT_ERRORS(REC NUMBER,VTABNAME VARCHAR2,VCOLNAME VARCHAR2,ERR_F VARCHAR2,MSG VARCHAR2);
END SAI_SUPPLIER_PACKAGE;
/
/***************************** PACKAGE BODY *************************************/
CREATE OR REPLACE PACKAGE BODY SAI_SUPPLIER_PACKAGE
AS
ERR_M VARCHAR2(1000):=NULL;
ERR_F VARCHAR2(1):=NULL;
MSG VARCHAR2(200):=NULL;
V_PIN AP_SUPPLIER_SITES_ALL.ZIP%TYPE; --variable to capture the pincode.
VPAY_TERM AP_TERMS_TL.TERM_ID%TYPE;
/**************************************** CALLLING PROCEDURE ***********************************/
PROCEDURE SAI_SUPPLIER_MAIN(ERRBUF VARCHAR2,RETCODE NUMBER)
AS
CURSOR C1 IS SELECT * FROM SAI_SUPPLIER_STAGE; --Cursor to get all the supplier data from stage table.
CURSOR C2 IS SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS; --Cursor to get all the Operating Units.
VREQ_ID1 NUMBER; --variable to get the req ID of import supplier concurrent program.
VREQ_ID2 NUMBER; --variable to get the req ID of supplier site concurrent program.
VREQ_ID3 NUMBER;
FUN1 BOOLEAN;
VPHASE VARCHAR2(10):=NULL;
VSTATUS VARCHAR2(10):=NULL;
VDEV_PHASE VARCHAR2(10):=NULL;
VDEV_STATUS VARCHAR2(10):=NULL;
VMSG VARCHAR2(10):=NULL;
V_COUNTRY VARCHAR2(2);
V_SITE VARCHAR2(15);
ADDR1 AP_SUPPLIER_SITES_ALL.ADDRESS_LINE1%TYPE; --variable to capture the address_line1.
ADDR2 AP_SUPPLIER_SITES_ALL.ADDRESS_LINE2%TYPE; --variable to capture the address_line2.
ADDR3 AP_SUPPLIER_SITES_ALL.ADDRESS_LINE3%TYPE; --variable to capture the address_line3.
ADDR4 AP_SUPPLIER_SITES_ALL.ADDRESS_LINE4%TYPE; --variable to capture the address_line4.
V_LIABILITY_ACCT GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
V_PREPAY_ACCT GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
BEGIN
FOR R1 IN C1 LOOP
MSG:=NULL;
ERR_F:=NULL;
ERR_M:=NULL;
V_PIN:=NULL;
VALID_VENDOR_NAME(R1.VENDOR_NAME,R1.RECORDNO);
VALID_VENDOR_TYPE(R1.VENDOR_TYPE,R1.RECORDNO);
VALID_PAY_METHOD(R1.PAYMENT_METHOD,R1.RECORDNO);
VALID_PAY_TERM(R1.PAYMENT_TERM,R1.RECORDNO);
VALID_PAY_METHOD(R1.PAYMENT_METHOD,R1.RECORDNO);
VALID_PAY_TERM(R1.PAYMENT_TERM,R1.RECORDNO);
VALID_COUNTRY(R1.COUNTRY,R1.RECORDNO);
VALID_INV_PAY_CURRENCY(R1.INVOICE_CURRENCY_CODE,R1.RECORDNO);
VALID_INV_PAY_CURRENCY(R1.PAYMENT_CURRENCY_CODE,R1.RECORDNO);
VALID_PINCODE(R1.PINCODE);
IF R1.SITE_NAME IS NULL THEN --SITE_CODE is a mandatory column.
ERR_M:=ERR_M||'NULL AT SITE_NAME';
ERR_F:='Y';
MSG:='NULLS AT SITE_NAME';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,R1.RECORDNO);
INSERT_ERRORS(R1.RECORDNO,'SAI_SUPPLIER_STAGE','NULL AT SITE_NAME /',ERR_F,MSG);
END IF;
IF R1.ADDRESS IS NULL THEN --ADDRESS_LINE1 is a mandatory column.
ERR_M:=ERR_M||'NULLS AT ADDRESS/';
ERR_F:='Y';
MSG:='NULLS AT ADDRESS/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,R1.RECORDNO);
INSERT_ERRORS(R1.RECORDNO,'SAI_SUPPLIER_STAGE','NULL AT ADDRESS/',ERR_F,MSG);
ELSE --If the Address is NOT NULL ,Splitting the Address into 4 lines.
IF INSTR(R1.ADDRESS,',',1,1)!=0 THEN
ADDR1:=SUBSTR(R1.ADDRESS,1,INSTR(R1.ADDRESS,',',1,1)-1);
IF INSTR(R1.ADDRESS,',',1,2)!=0 THEN
ADDR2:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,1)+1,INSTR(R1.ADDRESS,',',1,2)-INSTR(R1.ADDRESS,',',1,1)-1);
IF INSTR(R1.ADDRESS,',',1,3)!=0 THEN
ADDR3:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,2)+1,INSTR(R1.ADDRESS,',',1,3)-INSTR(R1.ADDRESS,',',1,2)-1);
IF INSTR(R1.ADDRESS,',',1,4)!=0 then
ADDR4:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,3)+1);
ELSE
ADDR4:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,3)+1);
END IF;
ELSE
ADDR3:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,2)+1);
END IF;
ELSE
ADDR2:=SUBSTR(R1.ADDRESS,INSTR(R1.ADDRESS,',',1,1)+1);
END IF;
ELSE
ADDR1:=R1.ADDRESS;
END IF;
END IF;
IF MSG IS NULL THEN --IF statement to check whether there are any errors in the current record.
INSERT INTO AP_SUPPLIERS_INT --Interface insert to import Suppliers.
(
VENDOR_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_NAME, --Mandatory
CREATION_DATE,
CREATED_BY,
VENDOR_TYPE_LOOKUP_CODE, --Mandatory
TERMS_ID,
PAYMENT_METHOD_LOOKUP_CODE,
CREATE_DEBIT_MEMO_FLAG
)
VALUES
(
AP_SUPPLIERS_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
R1.VENDOR_NAME,
SYSDATE,
FND_GLOBAL.USER_ID,
R1.VENDOR_TYPE,
VPAY_TERM ,
R1.PAYMENT_METHOD,
'Y'
);
--COMMIT;
SELECT TERRITORY_CODE
INTO V_COUNTRY FROM FND_TERRITORIES_TL
WHERE TERRITORY_SHORT_NAME=R1.COUNTRY;
FOR R2 IN C2 LOOP -- Begin the cursor to import the site n its contact to all OperatingUnits.
IF UPPER(R1.RM)='YES' THEN -- If RM is YES ,import site for Raw Material
V_SITE:=R1.SITE_NAME||'-RM';
IF R2.ORGANIZATION_ID=81 THEN --IF block to get the RM ccids
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 81 and RM
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142219' AND SEGMENT2='01';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 81 and RM
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234129' AND SEGMENT2='01';
ELSIF R2.ORGANIZATION_ID=82 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 82 and RM
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142219' AND SEGMENT2='03';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 82 and RM
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234129' AND SEGMENT2='03';
ELSIF R2.ORGANIZATION_ID=423 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and RM
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142219' AND SEGMENT2='04';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and RM
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234129' AND SEGMENT2='04';
ELSIF R2.ORGANIZATION_ID=363 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 363 and RM
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142219' AND SEGMENT2='00';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 363 and RM
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234129' AND SEGMENT2='00';
END IF;
INSERT INTO AP_SUPPLIER_SITES_INT --Interface insert to import Sites
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_CODE,
CREATION_DATE,
CREATED_BY,
ADDRESS_LINE1, --Mandatory
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY, --Mandatory
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
TERMS_ID,
ORG_ID, --Operating Unit ID
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_METHOD_LOOKUP_CODE,
ZIP,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES
(
AP_SUPPLIERS_INT_S.CURRVAL,
AP_SUPPLIER_SITES_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
V_SITE,
SYSDATE,
FND_GLOBAL.USER_ID,
ADDR1,
ADDR2,
ADDR3,
ADDR4,
R1.CITY,
R1.STATE,
v_country,
'Y',
'Y',
VPAY_TERM,
R2.ORGANIZATION_ID,
R1.INVOICE_CURRENCY_CODE,
R1.PAYMENT_CURRENCY_CODE,
R1.PAYMENT_METHOD,
V_PIN,
V_LIABILITY_ACCT,
V_PREPAY_ACCT
);
END IF;
IF UPPER(R1.CAPITAL)='YES' THEN -- If CAPITAL is YES ,import site for Capital
V_SITE:=R1.SITE_NAME||'-CAP';
IF R2.ORGANIZATION_ID=81 THEN --IF block to get the CAP ccids
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 81 and CAP
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142319' AND SEGMENT2='01';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 81 and CAP
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234199' AND SEGMENT2='01';
ELSIF R2.ORGANIZATION_ID=82 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 82 and CAP
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142319' AND SEGMENT2='03';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 82 and CAP
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234199' AND SEGMENT2='03';
ELSIF R2.ORGANIZATION_ID=423 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 423 and CAP
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142319' AND SEGMENT2='04';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and CAP
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234199' AND SEGMENT2='04';
ELSIF R2.ORGANIZATION_ID=363 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 363 and CAP
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142319' AND SEGMENT2='00';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 363 and CAP
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234199' AND SEGMENT2='00';
END IF;
INSERT INTO AP_SUPPLIER_SITES_INT --Interface insert to import Sites
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_CODE,
CREATION_DATE,
CREATED_BY,
ADDRESS_LINE1, --Mandatory
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY, --Mandatory
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
TERMS_ID,
ORG_ID, --Operating Unit ID
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_METHOD_LOOKUP_CODE,
ZIP,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES
(
AP_SUPPLIERS_INT_S.CURRVAL,
AP_SUPPLIER_SITES_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
V_SITE,
SYSDATE,
FND_GLOBAL.USER_ID,
ADDR1,
ADDR2,
ADDR3,
ADDR4,
R1.CITY,
R1.STATE,
v_country,
'Y',
'Y',
VPAY_TERM,
R2.ORGANIZATION_ID,
R1.INVOICE_CURRENCY_CODE,
R1.PAYMENT_CURRENCY_CODE,
R1.PAYMENT_METHOD,
V_PIN,
V_LIABILITY_ACCT,
V_PREPAY_ACCT
);
END IF;
IF UPPER(R1.ENGINEERING)='YES' THEN -- If ENGINEERING is YES ,import site for Engineering
V_SITE:=R1.SITE_NAME||'-ENG';
IF R2.ORGANIZATION_ID=81 THEN --IF block to get the ENG ccids
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 81 and ENG
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142619' AND SEGMENT2='01';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 81 and ENG
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='01';
ELSIF R2.ORGANIZATION_ID=82 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 82 and ENG
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142619' AND SEGMENT2='03';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 82 and ENG
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='03';
ELSIF R2.ORGANIZATION_ID=423 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 423 and ENG
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142619' AND SEGMENT2='04';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and ENG
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='04';
ELSIF R2.ORGANIZATION_ID=363 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 363 and ENG
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142619' AND SEGMENT2='00';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 363 and ENG
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='00';
END IF;
INSERT INTO AP_SUPPLIER_SITES_INT --Interface insert to import Sites
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_CODE,
CREATION_DATE,
CREATED_BY,
ADDRESS_LINE1, --Mandatory
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY, --Mandatory
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
TERMS_ID,
ORG_ID, --Operating Unit ID
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_METHOD_LOOKUP_CODE,
ZIP,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES
(
AP_SUPPLIERS_INT_S.CURRVAL,
AP_SUPPLIER_SITES_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
V_SITE,
SYSDATE,
FND_GLOBAL.USER_ID,
ADDR1,
ADDR2,
ADDR3,
ADDR4,
R1.CITY,
R1.STATE,
v_country,
'Y',
'Y',
VPAY_TERM,
R2.ORGANIZATION_ID,
R1.INVOICE_CURRENCY_CODE,
R1.PAYMENT_CURRENCY_CODE,
R1.PAYMENT_METHOD,
V_PIN,
V_LIABILITY_ACCT,
V_PREPAY_ACCT
);
END IF;
IF UPPER(R1.SERVICES)='YES' THEN -- If SERVICES is YES ,import site for Services
V_SITE:=R1.SITE_NAME||'-SER';
IF R2.ORGANIZATION_ID=81 THEN --IF block to get the SER ccids
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 81 and SER
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142419' AND SEGMENT2='01';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 81 and SER
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='01';
ELSIF R2.ORGANIZATION_ID=82 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get liability ccid for the OU 82 and SER
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142419' AND SEGMENT2='03';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 82 and SER
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='03';
ELSIF R2.ORGANIZATION_ID=423 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 423 and SER
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142419' AND SEGMENT2='04';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 423 and SER
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='04';
ELSIF R2.ORGANIZATION_ID=363 THEN
SELECT CODE_COMBINATION_ID -- Stmt to get libility ccid for the OU 363 and SER
INTO V_LIABILITY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='142419' AND SEGMENT2='00';
SELECT CODE_COMBINATION_ID -- Stmt to get prepayment ccid for the OU 363 and SER
INTO V_PREPAY_ACCT
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT4='234139' AND SEGMENT2='00';
END IF;
INSERT INTO AP_SUPPLIER_SITES_INT --Interface insert to import Sites
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_CODE,
CREATION_DATE,
CREATED_BY,
ADDRESS_LINE1, --Mandatory
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
COUNTRY, --Mandatory
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
TERMS_ID,
ORG_ID, --Operating Unit ID
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_METHOD_LOOKUP_CODE,
ZIP,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES
(
AP_SUPPLIERS_INT_S.CURRVAL,
AP_SUPPLIER_SITES_INT_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
V_SITE,
SYSDATE,
FND_GLOBAL.USER_ID,
ADDR1,
ADDR2,
ADDR3,
ADDR4,
R1.CITY,
R1.STATE,
v_country,
'Y',
'Y',
VPAY_TERM,
R2.ORGANIZATION_ID,
R1.INVOICE_CURRENCY_CODE,
R1.PAYMENT_CURRENCY_CODE,
R1.PAYMENT_METHOD,
V_PIN,
V_LIABILITY_ACCT,
V_PREPAY_ACCT
);
END IF;
END LOOP;
END IF;
END LOOP;
COMMIT;
/********************************** Submitting request for suppliers import *******************************************/
VREQ_ID1:=FND_REQUEST.SUBMIT_REQUEST(
'SQLAP',
'APXSUIMP',
'Submitting request for suppliers import',
NULL,
FALSE,
'NEW',
1000,
'N',
'N',
'N'
);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'IMPORTING VENDORS. REQ ID :'||VREQ_ID1);
FUN1:=FND_CONCURRENT.WAIT_FOR_REQUEST(
VREQ_ID1,
1,
0,
VPHASE,
VSTATUS,
VDEV_PHASE,
VDEV_STATUS,
VMSG
);
COMMIT;
/********************************** submitting request for sites import ***********************************************/
VREQ_ID2:=FND_REQUEST.SUBMIT_REQUEST('SQLAP',
'APXSSIMP',
'submitting request for sites import',
NULL,
FALSE,
'NEW',
1000,
'N',
'N',
'N');
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'IMPORTING VENDOR SITES. REQ ID :'||VREQ_ID2);
FUN1:=FND_CONCURRENT.WAIT_FOR_REQUEST(
VREQ_ID2,
1,
0,
VPHASE,
VSTATUS,
VDEV_PHASE,
VDEV_STATUS,
VMSG
);
COMMIT;
/********************************** submitting request for importing localization details ***********************************************/
/*
VREQ_ID3:=FND_REQUEST.SUBMIT_REQUEST(
'SQLAP',
'CP_SAI_LOCALIZATION',
'submitting request for supplier localization details import',
NULL,
FALSE
);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'IMPORTING SUPPLIER LOCALIZATION DETAILS REQ ID :'||VREQ_ID3);
*/
END SAI_SUPPLIER_MAIN;
/***************************************PROCEDURE FOR VALIDATING VENDOR_NAME ********************************************/
PROCEDURE VALID_VENDOR_NAME(PVNAME VARCHAR2,REC NUMBER)
AS
VNAME AP_SUPPLIERS.VENDOR_NAME%TYPE;
BEGIN
IF PVNAME IS NOT NULL THEN
BEGIN
SELECT VENDOR_NAME
INTO VNAME
FROM AP_SUPPLIERS
WHERE UPPER(VENDOR_NAME)=UPPER(PVNAME);
ERR_F:='Y';
ERR_M:=ERR_M||'VENDOR_NAME: SUPPLIER ALREADY EXISTS';
MSG:='SUPPLIER ALREADY EXISTS';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_NAME',ERR_F,MSG);
EXCEPTION
WHEN NO_DATA_FOUND THEN
--DBMS_OUTPUT.PUT_LINE(VNAME||' VALID SUPPLIER');
NULL;
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_NAME',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||' *NULL AT VENDOR_NAME';
MSG:='NULL AT VENDOR_NAME';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_NAME',ERR_F,MSG);
END IF;
END;
/*************************************** PROCEDURE FOR VALIDATING VENDOR TYPE ***********************************/
PROCEDURE VALID_VENDOR_TYPE(PVENDOR_TYPE VARCHAR2,REC NUMBER)
AS
VVENDOR_TYPE FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
BEGIN
IF PVENDOR_TYPE IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO VVENDOR_TYPE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='VENDOR TYPE'
AND LOOKUP_CODE=PVENDOR_TYPE
AND VIEW_APPLICATION_ID=201;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID VENDOR TYPE/';
ERR_F:='Y';
MSG:='INVALID VENDOR TYPE/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_TYPE',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_TYPE',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||' *NULL AT VENDOR_TYPE';
MSG:='NULL AT VENDOR_TYPE';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','VENDOR_TYPE',ERR_F,MSG);
END IF;
END;
/* ************************************ PROCEDURE FOR VALIDATING COUNTRY ************************************ */
PROCEDURE VALID_COUNTRY(PCOUNTRY VARCHAR2,REC NUMBER)
AS
VCOUNTRY FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%TYPE;
BEGIN
IF PCOUNTRY IS NOT NULL THEN
BEGIN
SELECT TERRITORY_CODE
INTO VCOUNTRY
FROM FND_TERRITORIES_TL
WHERE TERRITORY_SHORT_NAME=PCOUNTRY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID COUNTRY CODE/';
ERR_F:='Y';
MSG:='INVALID COUNTRY CODE/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','COUNTRY',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','COUNTRY',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||' *NULL AT COUNTRY';
MSG:='NULL AT COUNTRY';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','COUNTRY',ERR_F,MSG);
END IF;
END;
/**************************** PROCEDURE FOR VALIDATING INVOICE AND PAYMENT CURRENCY CODE ****************************************/
PROCEDURE VALID_INV_PAY_CURRENCY(PCURRENCY VARCHAR2,REC NUMBER)
AS
VCURRENCY FND_CURRENCIES.CURRENCY_CODE%TYPE;
BEGIN
IF PCURRENCY IS NOT NULL THEN
BEGIN
SELECT CURRENCY_CODE
INTO VCURRENCY
FROM FND_CURRENCIES
WHERE CURRENCY_CODE=PCURRENCY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID CURRENCY CODE/';
ERR_F:='Y';
MSG:='INVALID CURRENCY CODE/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','INV/PAY CURRENCY_CODE',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','INV/PAY CURRENCY_CODE',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||'*NULL AT CURRENCY_CODE';
MSG:='NULL AT CURRENCY_CODE';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','INV/PAY CURRENCY_CODE',ERR_F,MSG);
END IF;
END;
/**************************** PROCEDURE FOR VALIDATING PAYMENT METHOD****************************************/
PROCEDURE VALID_PAY_METHOD(PPAY_METHOD VARCHAR2,REC NUMBER)
AS
VPAY_METHOD FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
BEGIN
IF PPAY_METHOD IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO VPAY_METHOD
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='PAYMENT METHOD'
AND LOOKUP_CODE=PPAY_METHOD
AND VIEW_APPLICATION_ID=200;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID PAYMENT METHOD/';
ERR_F:='Y';
MSG:='INVALID PAYMENT METHOD/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PAYMENT METHOD',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PYMENT METHOD',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||'*NULL AT PAYMENT METHOD';
MSG:='NULL AT PAYMENT METHOD';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PAYMENT METHOD',ERR_F,MSG);
END IF;
END;
/**************************** PROCEDURE FOR VALIDATING PAYMENT TERM****************************************/
PROCEDURE VALID_PAY_TERM(PPAY_TERM VARCHAR2,REC NUMBER)
AS
BEGIN
IF PPAY_TERM IS NOT NULL THEN
BEGIN
SELECT TERM_ID
INTO VPAY_TERM
FROM AP_TERMS_TL
WHERE NAME=PPAY_TERM;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID PAYMENT TERM/';
ERR_F:='Y';
MSG:='INVALID PAYMENT TERM/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PAYMENT TERM',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PYMENT TERM',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||'*NULL AT PAYMENT TERM';
MSG:='NULL AT PAYMENT TERM';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','PAYMENT TERM',ERR_F,MSG);
END IF;
END;
/***************************************** PROCEDURE FOR VALIDATING TDS VENDOR TYPE **********************************/
/*
PROCEDURE VALID_TDS_VENDOR_TYPE(PTDS_VENDOR_TYPE VARCHAR2,REC NUMBER)
AS
VTDS_VENDOR_TYPE FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
BEGIN
IF PTDS_VENDOR_TYPE IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO VTDS_VENDOR_TYPE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='JAI_TDS_VENDOR_TYPE'
AND LOOKUP_CODE=PTDS_VENDOR_TYPE
AND VIEW_APPLICATION_ID=7000; --APPL.NAME:Asia/Pacific Localizations,APPL.SHOT.NAME:JA
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_M:=ERR_M||'INVALID TDS VENDOR TYPE/';
ERR_F:='Y';
MSG:='INVALID TDS VENDOR TYPE/';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','TDS_VENDOR_TYPE',ERR_F,MSG);
WHEN OTHERS THEN
ERR_M:=ERR_M||SUBSTR(SQLERRM,10,30);
ERR_F:='Y';
MSG:=SUBSTR(SQLERRM,10,30);
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','TDS_VENDOR_TYPE',ERR_F,MSG);
END;
ELSE
ERR_F:='Y';
ERR_M:=ERR_M||'*NULL AT TDS VENDOR TYPE';
MSG:='NULL AT TDS VENDOR TYPE';
UPDATE_SUP_ERRORS(ERR_F,ERR_M,REC);
INSERT_ERRORS(REC,'SAI_SUPPLIER_STAGE','TDS_VENDOR_TYPE',ERR_F,MSG);
END IF;
END;
*/
/************************ PROCEDURE TO REMOVE SPECIAL CHARECTERS FROM PINCODE ******************************/
PROCEDURE VALID_PINCODE(PPIN VARCHAR2)
AS
V NUMBER;
BEGIN -- block to remove the spaces,hiphens and fullstops from the pincode.
IF PPIN IS NOT NULL THEN
FOR I IN 1..LENGTH(PPIN) LOOP
IF ASCII(SUBSTR(PPIN,I,1))=45 OR ASCII(SUBSTR(PPIN,I,1))=46 OR ASCII(SUBSTR(PPIN,I,1))=32 THEN
NULL;
ELSE
V_PIN:=V_PIN||SUBSTR(PPIN,I,1);
END IF;
END LOOP;
END IF;
END;
/*******************PROCEDURE FOR UPDATING ERRORS IN THE SUPPLIER STAGE TABLE *******************************/
PROCEDURE UPDATE_SUP_ERRORS(ERR_F IN OUT VARCHAR2,ERR_M IN OUT VARCHAR2,REC NUMBER)
AS
BEGIN
UPDATE SAI_SUPPLIER_STAGE
SET ERR_FLAG=ERR_F,ERR_MSG=ERR_M
WHERE RECORDNO=REC;
COMMIT;
END;
/*******************************PROCEDURE FOR INSERTING INTO ERROR TABLE****************************/
PROCEDURE INSERT_ERRORS(REC NUMBER,VTABNAME VARCHAR2,VCOLNAME VARCHAR2,ERR_F VARCHAR2,MSG VARCHAR2)
AS
BEGIN
INSERT INTO SAI_SUPPLIER_ERRORS
VALUES(REC,VTABNAME,VCOLNAME,ERR_F,MSG);
COMMIT;
END;
END SAI_SUPPLIER_PACKAGE;
/
control file
-------------------------------
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'C:\VENDOR_FINAL.txt'
INSERT
INTO TABLE SAI_SUPPLIER_STAGE
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORDNO SEQUENCE(1,1),
VENDOR_TYPE,
RM ,
CAPITAL ,
ENGINEERING ,
SERVICES ,
VENDOR_NAME "DECODE(:VENDOR_NAME,'NA',NULL,'0',NULL,:VENDOR_NAME)",
SITE_NAME "DECODE(:SITE_NAME,'NA',NULL,'0',NULL,:SITE_NAME)",
INVOICE_CURRENCY_CODE "DECODE(:INVOICE_CURRENCY_CODE,'NA',NULL,'0',NULL,:INVOICE_CURRENCY_CODE)",
PAYMENT_CURRENCY_CODE "DECODE(:PAYMENT_CURRENCY_CODE,'NA',NULL,'0',NULL,:PAYMENT_CURRENCY_CODE)",
ADDRESS "DECODE(:ADDRESS,'NA',NULL,'0',NULL,:ADDRESS)",
CITY "DECODE(:CITY,'NA',NULL,'0',NULL,:CITY)",
STATE "DECODE(:STATE,'NA',NULL,'0',NULL,:STATE)",
COUNTRY "DECODE(:COUNTRY,'NA',NULL,'0',NULL,:COUNTRY)",
PINCODE "DECODE(:PINCODE,'NA',NULL,'0',NULL,:PINCODE)",
PAYMENT_METHOD "DECODE(:PAYMENT_METHOD,'NA',NULL,'0',NULL,:PAYMENT_METHOD)",
OPERATING_UNIT FILLER ,
PAYMENT_TERM "DECODE(:PAYMENT_TERM,'NA',NULL,'0',NULL,:PAYMENT_TERM)",
CST_REG_NO "DECODE(:CST_REG_NO,'NA',NULL,'0',NULL,:CST_REG_NO)" ,
LST_REG_NO "DECODE(:LST_REG_NO,'NA',NULL,'0',NULL,:LST_REG_NO)",
VAT_REG_NO "DECODE(:VAT_REG_NO,'NA',NULL,'0',NULL,:VAT_REG_NO)",
SERVICE_TAX_REGNO "DECODE(:SERVICE_TAX_REGNO,'NA',NULL,'0',NULL,:SERVICE_TAX_REGNO)",
EXCISE_RANGE "DECODE(:EXCISE_RANGE,'NA',NULL,'0',NULL,:EXCISE_RANGE)",
EXCISE_ZONE "DECODE(:EXCISE_ZONE,'NA',NULL,'0',NULL,:EXCISE_ZONE)" ,
COLLECRATE "DECODE(:COLLECRATE,'NA',NULL,'0',NULL,:COLLECRATE)" ,
EXCISE_DIVISION "DECODE(:EXCISE_DIVISION,'NA',NULL,'0',NULL,:EXCISE_DIVISION)",
EXCISE_REGION "DECODE(:EXCISE_REGION,'NA',NULL,'0',NULL,:EXCISE_REGION)" ,
EXCISE_REGNO "DECODE(:EXCISE_REGNO,'NA',NULL,'0',NULL,:EXCISE_REGNO)",
EXCISE_CIRCLE "DECODE(:EXCISE_CIRCLE,'NA',NULL,'0',NULL,:EXCISE_CIRCLE)",
EC_CODE "DECODE(:EC_CODE,'NA',NULL,'0',NULL,:EC_CODE)",
)
No comments:
Post a Comment