--------------------------------1. Data Template for Supplier Info :---------------------------
SL_NO
VENDOR_TYPE
VENDOR_NAME
INVOICE_CURRENCY_CODE
PAYMENT_CURRENCY_CODE
OPERATING_UNIT
BILL_TO_LOCATION_CODE
SHIP_TO_LOCATION_CODE
INSPECTION_REQUIRED_FLAG
RECEIVING_ROUTING_ID
RECEIPT_REQUIRED_FLAG
MATCH_OPTION
PAYMENT_TERMS
COUNTRY
VENDOR_SITE_CODE
PAY_ON_RECEIPT_SUMMARY_CODE
PAY_ON_CODE
PRIMARY_PAY_SITE_FLAG
PAY_SITE_FLAG
PURCHASING_SITE_FLAG
CONTACT_LAST_NAME
CONTACT_FIRST_NAME
ADDRESS1
ADDRESS2
ADDRESS3
CITY
STATE
ZIP
PHONE
FAX
EMAIL
---------------------------2. Create staging table :------------------------------------
CREATE TABLE XX_SUPPLIERS_INT
(
SL_NO NUMBER NOT NULL,
VENDOR_TYPE VARCHAR2(30 BYTE) NOT NULL,
VENDOR_NAME VARCHAR2(240 BYTE) NOT NULL,
INVOICE_CURRENCY_CODE VARCHAR2(15 BYTE),
PAYMENT_CURRENCY_CODE VARCHAR2(15 BYTE),
OPERATING_UNIT VARCHAR2(240 BYTE) NOT NULL,
BILL_TO_LOCATION_CODE VARCHAR2(60 BYTE) NOT NULL,
SHIP_TO_LOCATION_CODE VARCHAR2(60 BYTE) NOT NULL,
INSPECTION_REQUIRED_FLAG VARCHAR2(1 BYTE),
RECEIVING_ROUTING_ID NUMBER,
RECEIPT_REQUIRED_FLAG VARCHAR2(1 BYTE),
MATCH_OPTION VARCHAR2(25 BYTE),
PAYMENT_TERMS VARCHAR2(50 BYTE) NOT NULL,
COUNTRY VARCHAR2(30 BYTE) NOT NULL,
VENDOR_SITE_CODE VARCHAR2(15 BYTE) NOT NULL,
PAY_ON_RECEIPT_SUMMARY_CODE VARCHAR2(25 BYTE),
PAY_ON_CODE VARCHAR2(25 BYTE),
PRIMARY_PAY_SITE_FLAG VARCHAR2(1 BYTE),
PAY_SITE_FLAG VARCHAR2(1 BYTE),
PURCHASING_SITE_FLAG VARCHAR2(1 BYTE),
CONTACT_LAST_NAME VARCHAR2(60 BYTE) NOT NULL,
CONTACT_FIRST_NAME VARCHAR2(15 BYTE),
ADDRESS1 VARCHAR2(240 BYTE) NOT NULL,
ADDRESS2 VARCHAR2(240 BYTE),
ADDRESS3 VARCHAR2(240 BYTE),
CITY VARCHAR2(60 BYTE),
STATE VARCHAR2(150 BYTE) NOT NULL,
ZIP VARCHAR2(60 BYTE),
PHONE VARCHAR2(15 BYTE),
FAX VARCHAR2(15 BYTE),
EMAIL VARCHAR2(2000 BYTE),
VERIFY_FLAG VARCHAR2(1 BYTE) DEFAULT 'N',
ERROR_MESSAGE VARCHAR2(100 BYTE)
)
---------------------------3. Create Procedure :------------------------------------
CREATE OR REPLACE PROCEDURE APPS.XXCONA_SUPPLIER_UPLOAD
(errbuf OUT VARCHAR2,
retcode OUT NUMBER)
-- using Request Set
AS
L_VENDOR_TYPE VARCHAR2 (30);
L_VERIFY_FLAG VARCHAR (1);
L_ERROR_MESSAGE VARCHAR2 (2500);
L_INVOICE_CURRENCY VARCHAR2 (10);
L_PAYMENT_CURRENCY VARCHAR2 (10);
L_TERM_ID NUMBER (10);
L_VENDOR_SITE_ID NUMBER (10);
L_PAY_CODE_COMBINATION_ID NUMBER (10);
L_PREPAY_CODE_COMBINATION_ID NUMBER (10);
L_ORG_ID NUMBER (10);
--R_ORG_ID NUMBER(10);
L_TERRITORY_CODE VARCHAR2 (10);
L_CNT NUMBER (3);
L_LOCATION_ID_B NUMBER (10);
L_LOCATION_ID_S NUMBER (10);
R_LOCATION_ID_B NUMBER(10);
R_LOCATION_ID_S NUMBER(10);
L_VENDOR_NAME VARCHAR2 (150);
L_VENDOR_SITE_CODE VARCHAR2 (100);
l_return_status VARCHAR2(50);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
ln_request_id Number;
P_CONC_REQUEST_ID Number;
CURSOR C_SUPP IS
SELECT DISTINCT VENDOR_TYPE,
PAYMENT_TERMS,
VENDOR_NAME,
--VENDOR_ALTERNATIVE_NAME,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
--ACCTS_PAY_CODE_COMBINATION,
-- PREPAY_CODE_COMBINATION,
OPERATING_UNIT,
BILL_TO_LOCATION_CODE,
SHIP_TO_LOCATION_CODE,
INSPECTION_REQUIRED_FLAG,
RECEIVING_ROUTING_ID,
RECEIPT_REQUIRED_FLAG,
MATCH_OPTION
FROM XX_SUPPLIERS_INT
WHERE NVL(VERIFY_FLAG, 'N') = 'N';
CURSOR C_SUPP_SITE (P_SUPP_NAME VARCHAR2) IS
SELECT *
FROM XX_SUPPLIERS_INT
WHERE VENDOR_NAME = P_SUPP_NAME;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Delete the supplier Interface Table');
DELETE FROM AP_SUPPLIERS_INT;
DELETE FROM AP_SUPPLIER_SITES_INT;
DELETE FROM AP_SUP_SITE_CONTACT_INT;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'--- START CHECKING ---');
FOR H1 IN C_SUPP LOOP
L_VERIFY_FLAG := 'Y';
L_ERROR_MESSAGE := NULL;
L_CNT := 0;
L_VENDOR_NAME := NULL;
BEGIN
SELECT COUNT (*)
INTO L_CNT
FROM APPS.AP_SUPPLIERS
WHERE TRIM (UPPER (VENDOR_NAME)) = TRIM (UPPER (H1.VENDOR_NAME));
DBMS_OUTPUT.PUT_LINE('Check with AP_SUPPLIERS -'||L_CNT);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Check with AP_SUPPLIERS - '||L_CNT);
IF L_CNT > 0
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := 'Vendor is already existing';
DBMS_OUTPUT.PUT_LINE('Vendor is already existing');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor is already existing');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION occured in Vendor checking');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'EXCEPTION occured in Vendor checking');
NULL;
END;
/********************* Find OPERATING UNIT'****************************/
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORG_ID
FROM HR_OPERATING_UNITS
WHERE NAME = H1.OPERATING_UNIT; --'XXX OPERATING UNIT';
DBMS_OUTPUT.PUT_LINE('Organization_id - '||L_ORG_ID);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Organization_ID - '||L_ORG_ID);
EXCEPTION
WHEN OTHERS
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE || 'Operating Unit is Invalid';
DBMS_OUTPUT.PUT_LINE('Operating Unit is Invalid');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit is Invalid');
END;
/********************* Find Location ID ****************************/
BEGIN
SELECT LOCATION_ID
INTO L_LOCATION_ID_B
FROM HR_LOCATIONS
WHERE LOCATION_CODE = H1.BILL_TO_LOCATION_CODE ; --'XXX MAIN STORE LOCATION';
-- WHERE LOCATION_CODE ='Karam Chand Thapar (Coal Sales) Limited - ICD';
DBMS_OUTPUT.PUT_LINE('LOC CODE Bill'|| L_LOCATION_ID_B);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'LOC CODE Bill '|| L_LOCATION_ID_B);
EXCEPTION
WHEN OTHERS
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE || 'Location is Not Valid';
DBMS_OUTPUT.PUT_LINE('Location is Not Valid');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Billing Location is Not Valid');
END;
BEGIN
SELECT LOCATION_ID
INTO L_LOCATION_ID_S
FROM HR_LOCATIONS
WHERE LOCATION_CODE = H1.SHIP_TO_LOCATION_CODE ;--'XXX MAIN STORE LOCATION';
-- WHERE LOCATION_CODE ='Karam Chand Thapar (Coal Sales) Limited - ICD';
DBMS_OUTPUT.PUT_LINE('LOC CODE Ship '|| L_LOCATION_ID_S);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'LOC CODE Ship '|| L_LOCATION_ID_S);
EXCEPTION
WHEN OTHERS
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE || 'Location is Not Valid';
DBMS_OUTPUT.PUT_LINE('Shiping Location is Not Valid');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Shiping Location is Not Valid');
END;
/********************* Find Vendor Type ****************************/
BEGIN
SELECT LOOKUP_CODE
INTO L_VENDOR_TYPE
FROM APPS.PO_LOOKUP_CODES
WHERE LOOKUP_TYPE(+) = 'VENDOR TYPE'
AND UPPER (LOOKUP_CODE) = UPPER (TRIM (H1.VENDOR_TYPE));
DBMS_OUTPUT.PUT_LINE('Vendor Type'||L_VENDOR_TYPE);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Type '||L_VENDOR_TYPE);
EXCEPTION
WHEN OTHERS
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE || 'Vendor Type Lookup Code not existing';
DBMS_OUTPUT.PUT_LINE('Vendor Type Lookup Code not existing');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Type Lookup Code not existing');
END;
/********************* Find Invoice Currency ****************************/
BEGIN
SELECT CURRENCY_CODE
INTO L_INVOICE_CURRENCY
FROM APPS.FND_CURRENCIES
WHERE CURRENCY_CODE = TRIM (H1.INVOICE_CURRENCY_CODE);
DBMS_OUTPUT.PUT_LINE('CURRENCY CODE for Invoice '||L_INVOICE_CURRENCY);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'CURRENCY CODE for Invoice '||L_INVOICE_CURRENCY);
EXCEPTION
WHEN OTHERS
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE || 'Invoice Currency Code is not Valid';
DBMS_OUTPUT.PUT_LINE('Invoice Currency Code is not Valid');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Invoice Currency Code is not Valid');
END;
/********************* Find Payment Currency ****************************/
BEGIN
SELECT CURRENCY_CODE
INTO L_PAYMENT_CURRENCY
FROM APPS.FND_CURRENCIES
WHERE CURRENCY_CODE = TRIM (H1.PAYMENT_CURRENCY_CODE);
DBMS_OUTPUT.PUT_LINE('Payament Currency Cocde '||L_PAYMENT_CURRENCY);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Payament Currency Cocde '||L_PAYMENT_CURRENCY);
EXCEPTION
WHEN OTHERS
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE :=
L_ERROR_MESSAGE || 'Payament Currency Cocde is not valid';
DBMS_OUTPUT.PUT_LINE('Payament Currency Cocde is not valid');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Payament Currency Cocde is not valid');
END;
/********************* Find Payment Term ID **************************/
BEGIN
SELECT TERM_ID
INTO L_TERM_ID
FROM APPS.AP_TERMS
WHERE UPPER (NAME) = UPPER (TRIM (H1.PAYMENT_TERMS));
DBMS_OUTPUT.PUT_LINE('Payament Term'||L_TERM_ID);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Payament Term - '||L_TERM_ID);
EXCEPTION
WHEN OTHERS
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE || 'Payment Term is not valid';
DBMS_OUTPUT.PUT_LINE ('Payment Term is not valid');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Payment Term is not valid');
END;
/********************* Find Supplier Name is Not Null******************/
IF H1.VENDOR_NAME IS NULL
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE || 'Supplier Name is not existing';
DBMS_OUTPUT.PUT_LINE('Vendor Name is not existing');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Supplier Name is not existing');
END IF;
SAVEPOINT A;
IF L_VERIFY_FLAG <> 'N'
THEN
BEGIN
INSERT INTO AP.AP_SUPPLIERS_INT
(VENDOR_INTERFACE_ID,
VENDOR_NAME,
-- VENDOR_NAME_ALT,
VENDOR_TYPE_LOOKUP_CODE,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
TERMS_ID,
-- ACCTS_PAY_CODE_COMBINATION_ID,
-- PREPAY_CODE_COMBINATION_ID,
-- BILL_TO_LOCATION_CODE,
-- SHIP_TO_LOCATION_CODE,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID,
RECEIVING_ROUTING_ID,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
PAYMENT_METHOD_LOOKUP_CODE,
CREATE_DEBIT_MEMO_FLAG,
PAY_DATE_BASIS_LOOKUP_CODE,
-- PAY_ON_RECEIPT_SUMMARY_CODE,
MATCH_OPTION
)
VALUES (APPS.AP_SUPPLIERS_INT_S.NEXTVAL,
TRIM (H1.VENDOR_NAME),
-- TRIM (H1.VENDOR_ALTERNATIVE_NAME),
L_VENDOR_TYPE,
L_INVOICE_CURRENCY,
L_PAYMENT_CURRENCY,
L_TERM_ID,
-- L_PAY_CODE_COMBINATION_ID,
-- L_PREPAY_CODE_COMBINATION_ID,
-- H1.BILL_TO_LOC,
-- H1.SHIP_TO_LOC,
L_LOCATION_ID_S,
L_LOCATION_ID_B,
NVL(H1.RECEIVING_ROUTING_ID,2),
NVL(H1.INSPECTION_REQUIRED_FLAG,'Y'),
NVL(H1.RECEIPT_REQUIRED_FLAG,'Y'),
'CHECK',
'Y',
'DUE',
-- 'RECEIPT',
NVL(H1.MATCH_OPTION,'R')
);
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'Y'
WHERE VENDOR_NAME = H1.VENDOR_NAME;
DBMS_OUTPUT.PUT_LINE('DATA INSERT INTO AP.ap_suppliers_int');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DATA INSERT INTO AP.ap_suppliers_int');
EXCEPTION
WHEN OTHERS
THEN
L_ERROR_MESSAGE := SQLERRM;
L_VERIFY_FLAG := 'N';
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE VENDOR_NAME = H1.VENDOR_NAME;
DBMS_OUTPUT.PUT_LINE('DATA NOT INSERT INTO ap_suppliers_int');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DATA NOT INSERT INTO ap_suppliers_int');
COMMIT;
--GOTO NEXT_SUPP;
END;
FOR L1 IN C_SUPP_SITE (H1.VENDOR_NAME)
LOOP
L_VENDOR_SITE_CODE := NULL;
BEGIN
/********************* Find Territory Code *********************/
SELECT TERRITORY_CODE
INTO L_TERRITORY_CODE
FROM APPS.FND_TERRITORIES
WHERE NLS_TERRITORY = UPPER(TRIM (L1.COUNTRY));
DBMS_OUTPUT.PUT_LINE('Country Territory code - '||L_TERRITORY_CODE);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Country Territory Code - '||L_TERRITORY_CODE);
EXCEPTION
WHEN OTHERS
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE || 'Invalid Country';
DBMS_OUTPUT.PUT_LINE('Invalid Country');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Invalid Country');
NULL;
END;
BEGIN
/********************* Find Supplier Site ***********************/
SELECT VENDOR_SITE_CODE
INTO L_VENDOR_SITE_CODE
FROM APPS.AP_SUPPLIER_SITES_ALL A, APPS.AP_SUPPLIERS B
WHERE ORG_ID = L_ORG_ID
AND UPPER (VENDOR_SITE_CODE) = TRIM (UPPER (L1.VENDOR_SITE_CODE))
AND A.VENDOR_ID = B.VENDOR_ID
AND UPPER (B.VENDOR_NAME) = TRIM (UPPER (L1.VENDOR_NAME));
DBMS_OUTPUT.PUT_LINE('Vendor Site Code'||L_VENDOR_SITE_CODE);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Site Code'||L_VENDOR_SITE_CODE);
IF L_VENDOR_SITE_CODE IS NOT NULL
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE :=
L_ERROR_MESSAGE || 'Vendor Site is already existing';
DBMS_OUTPUT.PUT_LINE('Vendor Site is already existing');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Site is already existing');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Exception : Vendor Site ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Exception : Vendor Site ');
NULL;
END;
/********************* Supplier Site Upload ******************/
IF L_VERIFY_FLAG <> 'N'
THEN
BEGIN
INSERT INTO AP.AP_SUPPLIER_SITES_INT
(VENDOR_INTERFACE_ID,
VENDOR_SITE_CODE,
-- VENDOR_SITE_CODE_ALT,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
COUNTRY,
ZIP,
PHONE,
FAX,
-- ACCTS_PAY_CODE_COMBINATION_ID,
-- PREPAY_CODE_COMBINATION_ID,
ORG_ID,
TERMS_ID,
ATTRIBUTE_CATEGORY,
--ATTRIBUTE10,
-- SHIP_TO_LOCATION_CODE,
-- BILL_TO_LOCATION_CODE,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID,
HOLD_UNMATCHED_INVOICES_FLAG,
VENDOR_SITE_INTERFACE_ID,
PURCHASING_SITE_FLAG,
PAY_SITE_FLAG,
PRIMARY_PAY_SITE_FLAG,
PAYMENT_METHOD_LOOKUP_CODE,
PAY_ON_CODE,
PAY_DATE_BASIS_LOOKUP_CODE,
CREATE_DEBIT_MEMO_FLAG,
PAY_ON_RECEIPT_SUMMARY_CODE,
MATCH_OPTION
)
VALUES (APPS.AP_SUPPLIERS_INT_S.CURRVAL,
UPPER(TRIM(L1.VENDOR_SITE_CODE)),
-- INITCAP(TRIM (L1.VENDOR_SITE_CODE)),
TRIM (L1.ADDRESS1),
TRIM (L1.ADDRESS2),
TRIM (L1.ADDRESS3),
TRIM (L1.CITY),
TRIM (L1.STATE),
L_TERRITORY_CODE,
TRIM (L1.ZIP),
TRIM (L1.PHONE),
TRIM (L1.FAX),
-- L_PAY_CODE_COMBINATION_ID,
-- L_PREPAY_CODE_COMBINATION_ID,
L_ORG_ID,
L_TERM_ID,
'Legacy Supplier Code',
-- TRIM(L1.BAAN_SUPP_CODE),
--L1.SHIP_TO_LOC,
--L1.BILL_TO_LOC,
L_LOCATION_ID_S,
L_LOCATION_ID_B,
'N',
APPS.AP_SUPPLIER_SITES_INT_S.NEXTVAL,
NVL(L1.PURCHASING_SITE_FLAG,'Y'),
NVL(L1.PAY_SITE_FLAG,'Y'),
NVL(L1.PRIMARY_PAY_SITE_FLAG,'N'),--CHNG
'CHECK',
L1.PAY_ON_CODE,
'DUE',
'Y',
L1.PAY_ON_RECEIPT_SUMMARY_CODE,
NVL(L1.MATCH_OPTION,'R')
);
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'Y'
WHERE VENDOR_NAME = L1.VENDOR_NAME
AND VENDOR_SITE_CODE = L1.VENDOR_SITE_CODE;
DBMS_OUTPUT.PUT_LINE('DATA INSERT INTO AP.AP_SUPPLIER_SITES_INT ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DATA INSERT INTO AP.AP_SUPPLIER_SITES_INT OF -'||L_CNT);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO SAVEPOINT A;
L_ERROR_MESSAGE := SQLERRM;
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE VENDOR_NAME = L1.VENDOR_NAME
AND VENDOR_SITE_CODE = L1.VENDOR_SITE_CODE;
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'N'
WHERE VENDOR_NAME = L1.VENDOR_NAME;
--GOTO NEXT_SUPP;
DBMS_OUTPUT.PUT_LINE('DATA NOT INSERT INTO AP.AP_SUPPLIER_SITES_INT ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DATA NOT INSERT INTO AP.AP_SUPPLIER_SITES_INT ');
COMMIT;
END;
BEGIN
/********************* Find Supplier Site ID ***********************/
SELECT VENDOR_SITE_ID
INTO L_VENDOR_SITE_ID
FROM APPS.AP_SUPPLIER_SITES_ALL A, APPS.AP_SUPPLIERS B
WHERE ORG_ID = L_ORG_ID
AND UPPER (VENDOR_SITE_CODE) = TRIM (UPPER (L1.VENDOR_SITE_CODE))
AND A.VENDOR_ID = B.VENDOR_ID
AND UPPER (B.VENDOR_NAME) = TRIM (UPPER (L1.VENDOR_NAME));
DBMS_OUTPUT.PUT_LINE('Vendor Site ID'||L_VENDOR_SITE_ID);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Site ID'||L_VENDOR_SITE_ID);
IF L_VENDOR_SITE_CODE IS NOT NULL
THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE :=
L_ERROR_MESSAGE || 'Vendor Site is already existing';
DBMS_OUTPUT.PUT_LINE('Vendor Site is already existing');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Site is already existing');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Exception : Vendor Site ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Exception : Vendor Site ');
NULL;
END;
/**************** Supplier Site Contact Upload ******************/
BEGIN
INSERT INTO AP.AP_SUP_SITE_CONTACT_INT
(
VENDOR_INTERFACE_ID,
VENDOR_CONTACT_INTERFACE_ID,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
ORG_ID,
OPERATING_UNIT_NAME,
-- TITLE,
FIRST_NAME,
LAST_NAME,
-- AREA_CODE,
PHONE,
-- FAX_AREA_CODE,
FAX,
EMAIL_ADDRESS
)
VALUES
(
APPS.AP_SUPPLIERS_INT_S.CURRVAL ,
APPS.AP_SUP_SITE_CONTACT_INT_S.NEXTVAL,
L_VENDOR_SITE_ID,
TRIM (L1.VENDOR_SITE_CODE),
L_ORG_ID,
TRIM(L1.OPERATING_UNIT), --'KMPL-PHASEVI',
-- L1.TITLE ,
L1.CONTACT_FIRST_NAME,
L1.CONTACT_LAST_NAME,
-- TRIM(L1.AREA_CODE),
TRIM (L1.PHONE),
-- TRIM(L1.FAX_AREA_CODE),
TRIM(L1.FAX),
TRIM(L1.EMAIL)
);
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'Y'
WHERE VENDOR_NAME = L1.VENDOR_NAME
AND VENDOR_SITE_CODE = L1.VENDOR_SITE_CODE;
SELECT COUNT(*) INTO L_CNT FROM XX_SUPPLIERS_INT
WHERE VERIFY_FLAG = 'Y'
AND VENDOR_NAME = L1.VENDOR_NAME
AND VENDOR_SITE_CODE = L1.VENDOR_SITE_CODE;
DBMS_OUTPUT.PUT_LINE('DATA INSERT INTO AP.AP_SUP_SITE_CONTACT_INT');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DATA INSERT INTO AP.AP_SUP_SITE_CONTACT_INT');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT A;
L_ERROR_MESSAGE := SQLERRM;
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE VENDOR_NAME = L1.VENDOR_NAME
AND VENDOR_SITE_CODE = L1.VENDOR_SITE_CODE;
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'N'
WHERE VENDOR_NAME = L1.VENDOR_NAME;
--GOTO NEXT_SUPP;
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'N'
WHERE VENDOR_NAME = L1.VENDOR_NAME
AND VENDOR_SITE_CODE = L1.VENDOR_SITE_CODE;
DBMS_OUTPUT.PUT_LINE('DATA NOT INSERT INTO AP.AP_SUP_SITE_CONTACT_INT ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DATA NOT INSERT INTO AP.AP_SUP_SITE_CONTACT_INT ');
END;
ELSE
ROLLBACK TO SAVEPOINT A;
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE VENDOR_NAME = L1.VENDOR_NAME
AND VENDOR_SITE_CODE = L1.VENDOR_SITE_CODE;
UPDATE XX_SUPPLIERS_INT
SET VERIFY_FLAG = 'N'
WHERE VENDOR_NAME = L1.VENDOR_NAME;
DBMS_OUTPUT.PUT_LINE('ELSE DATA NOT INSERT INTO ap.ap_supplier_sites_int ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'ELSE DATA NOT INSERT INTO ap.ap_supplier_sites_int ');
COMMIT;
--GOTO NEXT_SUPP;
END IF;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'VERIFY_FLAG - Y');
END LOOP;
ELSE
UPDATE XX_SUPPLIERS_INT
SET ERROR_MESSAGE = L_ERROR_MESSAGE,
VERIFY_FLAG = 'N'
WHERE VENDOR_NAME = H1.vendor_name;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'VERIFY_FLAG - N');
END IF;
COMMIT;
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'--- END CHECKING ---');
END XXCONA_SUPPLIER_UPLOAD;
/
--------------------------------4. Create Concurrent Request Set-----------------------------
