/*********************** formating here ***********************/
drop trigger XXXX_SUP_SITE_CONT_T
drop sequence XXXX_SUP_SITE_CONT_S
drop table XXXX_SUP_SITE_CONT_STG
/******************** STAGING TABLE Definition HERE *************************/
CREATE TABLE XXXX_SUP_SITE_CONT_STG
(
TRANSACTION_ID NUMBER,
OVER_ALL_STATUS_FLAG VARCHAR2(2),
VENDOR_STATUS_FLAG VARCHAR2(2 BYTE),
VENDOR_SITE_STATUS_FLAG VARCHAR2(2 BYTE),
VENDOR_CONTACT_STATUS_FLAG VARCHAR2(2),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
VENDOR_NAME_LS VARCHAR2(240 BYTE),
SEGMENT1_LS VARCHAR2(30 BYTE),
VENDOR_SITE_CODE_LS VARCHAR2(15 BYTE),
VENDOR_SITE_COUNTRY VARCHAR2(40 BYTE),
VENDOR_SITE_ADDRESS_LINE1 VARCHAR2(40 BYTE),
VENDOR_SITE_CITY VARCHAR2(40 BYTE),
VENDOR_SITE_STATE VARCHAR2(40 BYTE),
VENDOR_TYPE_NAME_LS VARCHAR2(40 BYTE),
VENDOR_TYPE_LOOKUP_CODE VARCHAR2(30 BYTE),
PAYMENT_TERMS_LS VARCHAR2(50 BYTE),
TERMS_ID NUMBER,
PAY_GROUP_NAME_LS VARCHAR2(40 BYTE),
PAY_GROUP_LOOKUP_CODE VARCHAR2(25 BYTE),
INVOICE_CURRENCY_CODE_LS VARCHAR2(15 BYTE),
PAYMENT_CURRENCY_CODE_LS VARCHAR2(15 BYTE),
PAYMENT_METHOD_LOOKUP_NAME_LS VARCHAR2(40 BYTE),
PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(25 BYTE),
SHIP_TO_ADDRESS_LINE_1_LS VARCHAR2(240 BYTE),
SHIP_TO_ADDRESS_LINE_2_LS VARCHAR2(240 BYTE),
SHIP_TO_ADDRESS_LINE_3_LS VARCHAR2(240 BYTE),
SHIP_TO_TOWN_OR_CITY_LS VARCHAR2(60 BYTE),
SHIP_TO_COUNTRY_LS VARCHAR2(30 BYTE),
SHIP_TO_POSTAL_CODE_LS VARCHAR2(60 BYTE),
SHIP_TO_LOCATION_ID NUMBER,
BILL_TO_ADDRESS_LINE_1_LS VARCHAR2(240 BYTE),
BILL_TO_ADDRESS_LINE_2_LS VARCHAR2(240 BYTE),
BILL_TO_ADDRESS_LINE_3_LS VARCHAR2(240 BYTE),
BILL_TO_TOWN_OR_CITY_LS VARCHAR2(60 BYTE),
BILL_TO_COUNTRY_LS VARCHAR2(30 BYTE),
BILL_TO_POSTAL_CODE_LS VARCHAR2(60 BYTE),
BILL_TO_LOCATION_ID NUMBER,
SHIP_VIA_LOOKUP_NAME_LS VARCHAR2(40 BYTE),
SHIP_VIA_LOOKUP_CODE VARCHAR2(25 BYTE),
FREIGHT_TERMS_LOOKUP_NAME_LS VARCHAR2(40 BYTE),
FREIGHT_TERMS_LOOKUP_CODE VARCHAR2(25 BYTE),
CONTACT_PERSON_NAME_LS VARCHAR2(240),
VENDOR_INTERFACE_ID NUMBER,
VENDOR_SITE_INTERFACE_ID NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER
)
/******************** SEQUENCE Definition HERE *************************/
CREATE SEQUENCE XXXX_SUP_SITE_CONT_S
/******************** TRIGGER Definition HERE *************************/
CREATE OR REPLACE TRIGGER XXXX_SUP_SITE_CONT_T
BEFORE INSERT ON APPS.XXXX_SUP_SITE_CONT_STG FOR EACH ROW
BEGIN
SELECT XXXX_SUP_SITE_CONT_S.NEXTVAL INTO :NEW.TRANSACTION_ID FROM DUAL;
END XXXX_SUP_SITE_CONT_T;
/
SELECT * FROM XXXX_SUP_SITE_CONT_STG
insert into XXXX_SUP_SITE_CONT_STG(vendor_name_ls,segment1_ls,VENDOR_SITE_CODE_LS,CONTACT_PERSON_NAME_LS)
values('xyzvenr4',2201234,'SIT423','SRIN11234')
insert into XXXX_SUP_SITE_CONT_STG(vendor_name_ls,segment1_ls,VENDOR_SITE_CODE_LS,CONTACT_PERSON_NAME_LS)
values('abcvenr4',8801234,'SIT423','SRIN21234')
insert into XXXX_SUP_SITE_CONT_STG(vendor_name_ls,segment1_ls,VENDOR_SITE_CODE_LS,CONTACT_PERSON_NAME_LS)
values('vvvend4',8511234,'SITE23','SRINA31234')
insert into XXXX_SUP_SITE_CONT_STG(vendor_name_ls,segment1_ls,VENDOR_SITE_CODE_LS,CONTACT_PERSON_NAME_LS)
values('vvvr34',851234,'SIT4123','SRINIVA234')
COMMIT;
SELECT * FROM XXXX_SUP_SITE_CONT_STG
declare
v1 varchar2(90);
v2 number;
begin
XXXX_SUPP_SITE_CONT_PKG.map(v1,v2);
XXXX_supp_site_cont_pkg.validate(v1,v2);
end;
--ALL PRODS --
CALL_ALLPROD
declare
v1 varchar2(90);
v2 number;
begin
XXXX_SUPP_SITE_CONT_PKG.CALL_ALLPROD(v1,v2);
end;
select * from ap_SUPPLIERS WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)
SELECT * FROM AP_SUPPLIER_SITES_ALL WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)
SELECT * FROM AP_SUPPLIER_CONTACTS WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)
----------------------------------------------------------------------------------------------------------
/* Formatted on 2013/08/19 17:51 (Formatter Plus v4.8.8) */
/************************ PACKAGE *****************/
CREATE OR REPLACE PACKAGE XXXX_supp_site_cont_pkg
IS
PROCEDURE MAP (errbuf OUT VARCHAR2, retcode OUT NUMBER);
PROCEDURE VALIDATE (errbuf OUT VARCHAR2, retcode OUT NUMBER);
PROCEDURE call_api (errbuf OUT VARCHAR2, retcode OUT NUMBER);
PROCEDURE error_report (errbuf OUT VARCHAR2, retcode OUT NUMBER);
PROCEDURE call_allprod (errbuf OUT VARCHAR2, retcode OUT NUMBER);
END XXXX_supp_site_cont_pkg;
/
/******************* VENDOR & VENDOR SITE PACKAGE ENDS HERE *****************/
/******************* VENDOR & VENDOR SITE PACKAGE BODY STARTS HERE *****************/
CREATE OR REPLACE PACKAGE BODY XXXX_supp_site_cont_pkg
IS
PROCEDURE MAP (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
BEGIN
/*************************** UPDATE STAGING TABLE STARTS HERE **************/
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'N',
vendor_status_flag = 'N',
vendor_site_status_flag = 'N',
vendor_contact_status_flag = 'N',
error_message = NULL,
terms_id = NULL,
vendor_type_lookup_code = NULL,
pay_group_lookup_code = NULL,
payment_method_lookup_code = NULL,
ship_to_location_id = NULL,
bill_to_location_id = NULL,
ship_via_lookup_code = NULL,
freight_terms_lookup_code = NULL,
vendor_interface_id = 102,
vendor_site_interface_id = 102,
creation_date = TRUNC (SYSDATE),
created_by = 1318,
last_update_date = TRUNC (SYSDATE),
last_updated_by = 1318;
UPDATE XXXX_sup_site_cont_stg
SET invoice_currency_code_ls = 'USD'
WHERE invoice_currency_code_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET payment_currency_code_ls = 'USD'
WHERE payment_currency_code_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET vendor_type_name_ls = 'VENDOR'
WHERE vendor_type_name_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'MM',
error_message = 'Mandatory Column is missing'
WHERE vendor_name_ls IS NULL OR segment1_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET vendor_site_status_flag = 'MM',
error_message = 'Mandatory Column is missing'
WHERE vendor_site_code_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET vendor_contact_status_flag = 'MM',
error_message = 'Mandatory Column is missing'
WHERE contact_person_name_ls IS NULL;
/*************************** UPDATE STAGING TABLE ENDS HERE **************/
COMMIT;
END MAP;
PROCEDURE VALIDATE (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v_count NUMBER;
v_term_id NUMBER;
v_location_id NUMBER;
v_ven_lcode VARCHAR2 (40);
v_payg_lcode VARCHAR2 (40);
v_paym_lcode VARCHAR2 (40);
v_shipv_lcode VARCHAR2 (40);
v_freight_lcode VARCHAR2 (40);
CURSOR ven_cur
IS
SELECT vendor_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM');
CURSOR ven_no_cur
IS
SELECT vendor_name_ls, segment1_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM');
CURSOR ven_site_cur (p_vendor_name_ls VARCHAR2)
IS
SELECT vendor_name_ls, vendor_site_code_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_site_status_flag NOT IN ('X', 'MM')
AND vendor_name_ls IS NOT NULL
AND vendor_name_ls = p_vendor_name_ls;
CURSOR ven_type_cur
IS
SELECT vendor_type_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND vendor_type_name_ls IS NOT NULL;
CURSOR pay_term_cur
IS
SELECT payment_terms_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND payment_terms_ls IS NOT NULL;
CURSOR pay_group_cur
IS
SELECT pay_group_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND pay_group_name_ls IS NOT NULL;
CURSOR inv_curr_cur
IS
SELECT invoice_currency_code_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND invoice_currency_code_ls IS NOT NULL;
CURSOR pay_curr_cur
IS
SELECT payment_currency_code_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND payment_currency_code_ls IS NOT NULL;
CURSOR pay_meth_cur
IS
SELECT payment_method_lookup_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND payment_method_lookup_name_ls IS NOT NULL;
CURSOR ship_cur
IS
SELECT ship_to_address_line_1_ls, ship_to_address_line_2_ls,
ship_to_address_line_3_ls, ship_to_town_or_city_ls,
ship_to_country_ls, ship_to_postal_code_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND ship_to_address_line_1_ls IS NOT NULL;
CURSOR ship_via_cur
IS
SELECT ship_via_lookup_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND ship_via_lookup_name_ls IS NOT NULL;
CURSOR freight_cur
IS
SELECT freight_terms_lookup_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND freight_terms_lookup_name_ls IS NOT NULL;
BEGIN
/**********************DUPLICATE RECORD ELIMNATION STARTS HERE**********************************/
BEGIN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'X',
error_message = 'Duplicate Record'
WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM XXXX_sup_site_cont_stg
GROUP BY vendor_name_ls);
UPDATE XXXX_sup_site_cont_stg
SET vendor_site_status_flag = 'X',
error_message = 'Duplicate Record'
WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM XXXX_sup_site_cont_stg
GROUP BY vendor_site_code_ls);
UPDATE XXXX_sup_site_cont_stg
SET vendor_contact_status_flag = 'X',
error_message = 'Duplicate Record'
WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM XXXX_sup_site_cont_stg
GROUP BY contact_person_name_ls);
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'MM',
error_message = 'Mandatory Column Missing'
WHERE vendor_name_ls IS NULL
OR segment1_ls IS NULL
OR vendor_site_code_ls IS NULL;
END;
/**********************DUPLICATE RECORD ELIMNATION ENDS HERE**********************************/
/*************************** VENDOR NAME VALIDATION SARTS HERE ******************/
FOR ven_rec IN ven_cur
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM ap_suppliers
WHERE UPPER (vendor_name) = UPPER (ven_rec.vendor_name_ls);
-- here count >0 means the vendor is present in base table then error out other wise it is new vendor.
IF v_count > 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message = 'Vendor alredy Present'
WHERE transaction_id = ven_rec.transaction_id;
END IF;
END;
END LOOP;
/*************************** VENDOR NAME VALIDATION SARTS HERE ******************/
/*************************** VENDOR NUMBER (SEGMENT1) VALIDATION SARTS HERE ******************/
FOR ven_no_rec IN ven_no_cur
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM ap_suppliers
WHERE UPPER (segment1) = UPPER (ven_no_rec.segment1_ls);
IF v_count > 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| ', Vendor Number esist in base table '
WHERE transaction_id = ven_no_rec.transaction_id;
END IF;
END;
END LOOP;
/*************************** VENDOR NUMBER (SEGMENT1) VALIDATION ENDS HERE ******************/
/*************************** VENDOR SITE CODE VALIDATION STARTS HERE ******************/
/*
for VEN_SITE_REC in VEN_SITE_CUR
loop
begin
select VENDOR_SITE_ID into V_VENDOR_SITE_ID from AP_SUPPLIER_SITES_ALL
where UPPER(vendor_site_code)=upper(VEN_SITE_REC.vendor_site_code_ls)
and vendor_id=VEN_SITE_REC.vendor_id;
update XXXX_SUP_SITE_CONT_STG set vendor_site_id=v_vendor_site_id where
transaction_id=VEN_SITE_REC.transaction_id;
exception
when no_data_found then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||', No Site found :'||vendor_site_code_ls
where transaction_id=VEN_SITE_REC. transaction_id;
when too_many_rows then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||', No many Sites found :'||vendor_site_code_ls
where transaction_id=VEN_SITE_REC. transaction_id;
when others then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||', UnExcepted error at :'||vendor_site_code_ls
where transaction_id=VEN_SITE_REC. transaction_id;
end;
end loop;
*/
/************* site is dependacy validation ***************/
FOR ven_rec IN ven_cur
LOOP
BEGIN
FOR ven_site_rec IN ven_site_cur (ven_rec.vendor_name_ls)
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM ap_suppliers as1, ap_supplier_sites_all assa
WHERE as1.vendor_id = assa.vendor_id
AND as1.vendor_name = ven_site_rec.vendor_name_ls
AND assa.vendor_site_code =
ven_site_rec.vendor_site_code_ls;
IF v_count > 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_site_status_flag = 'VE',
error_message =
error_message
|| ',Same SITE is exists for this Vendor'
WHERE transaction_id = ven_site_rec.transaction_id;
END IF;
END;
END LOOP;
END;
END LOOP;
/*************************** VENDOR SITE CODE VALIDATION ENDS HERE ******************/
/*************************** VENDOR TYPE LOOKUP CODE VALIDATION STARTS HERE ******************/
FOR ven_type_rec IN ven_type_cur
LOOP
BEGIN
SELECT lookup_code
INTO v_ven_lcode
FROM fnd_lookup_values
WHERE 1 = 1
AND UPPER (lookup_type) =
UPPER ('vendor type')
--must be upper case
AND LANGUAGE = USERENV ('lang')
AND UPPER (meaning) = UPPER (ven_type_rec.vendor_type_name_ls);
UPDATE XXXX_sup_site_cont_stg
SET vendor_type_lookup_code = v_ven_lcode
WHERE transaction_id = ven_type_rec.transaction_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message || 'vendor type name is not exist '
WHERE transaction_id = ven_type_rec.transaction_id;
WHEN TOO_MANY_ROWS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message || 'many vendor type name '
WHERE transaction_id = ven_type_rec.transaction_id;
WHEN OTHERS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message || 'vendor type name is invalid '
WHERE transaction_id = ven_type_rec.transaction_id;
END;
END LOOP;
/*************************** VENDOR TYPE LOOKUP CODE VALIDATION ENDS HERE ******************/
/*************************** PAYMENT TERMS VLIDATION STARTS HERE ******************/
FOR pay_term_rec IN pay_term_cur
LOOP
BEGIN
SELECT term_id
INTO v_term_id
FROM ap_terms_tl
WHERE 1 = 1
AND LANGUAGE = USERENV ('lang')
AND UPPER (NAME) = UPPER (pay_term_rec.payment_terms_ls);
UPDATE XXXX_sup_site_cont_stg
SET terms_id = v_term_id
WHERE transaction_id = pay_term_rec.transaction_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| payment_terms_ls
|| ' does not exsist. '
WHERE transaction_id = pay_term_rec.transaction_id;
WHEN TOO_MANY_ROWS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| payment_terms_ls
|| ' multiple are exsist. '
WHERE transaction_id = pay_term_rec.transaction_id;
WHEN OTHERS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| payment_terms_ls
|| ' is Unexpected error. '
WHERE transaction_id = pay_term_rec.transaction_id;
END;
END LOOP;
/*************************** PAYMENT TERMS VLIDATION ENDS HERE ******************/
/*************************** PAYMENT GROUP LOOKUP CODE VLIDATION STARTS HERE ******************/
FOR pay_group_rec IN pay_group_cur
LOOP
BEGIN
SELECT lookup_code
INTO v_payg_lcode
FROM fnd_lookup_values
WHERE 1 = 1
AND UPPER (lookup_type) =
UPPER ('pay group')
--must be upper case
AND LANGUAGE = USERENV ('lang')
AND UPPER (meaning) = UPPER (pay_group_rec.pay_group_name_ls);
UPDATE XXXX_sup_site_cont_stg
SET pay_group_lookup_code = v_payg_lcode
WHERE transaction_id = pay_group_rec.transaction_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message = error_message || 'invalied pay group '
WHERE transaction_id = pay_group_rec.transaction_id;
WHEN TOO_MANY_ROWS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message = error_message || 'too many pay group '
WHERE transaction_id = pay_group_rec.transaction_id;
WHEN OTHERS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message || 'un expected error at pay group '
WHERE transaction_id = pay_group_rec.transaction_id;
END;
END LOOP;
/*************************** PAYMENT GROUP LOOKUP CODE VLIDATION ENDS HERE ******************/
/*************************** INVOICE CURRENCY CODE VLIDATION STARTS HERE ******************/
FOR inv_curr_rec IN inv_curr_cur
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM fnd_currencies
WHERE UPPER (currency_code) =
UPPER (inv_curr_rec.invoice_currency_code_ls);
IF v_count = 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| ' , invoice currency code is not present '
WHERE transaction_id = inv_curr_rec.transaction_id;
END IF;
END;
END LOOP;
/*************************** INVOICE CURRENCY CODE VLIDATION ENDS HERE ******************/
/*************************** payment CURRENCY CODE VLIDATION STARTS HERE ******************/
FOR pay_curr_rec IN pay_curr_cur
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM fnd_currencies
WHERE UPPER (currency_code) =
UPPER (pay_curr_rec.payment_currency_code_ls);
IF v_count = 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| ' , payment currency code is not present '
WHERE transaction_id = pay_curr_rec.transaction_id;
END IF;
END;
END LOOP;
/*************************** payment CURRENCY CODE VLIDATION ENDS HERE ******************/
/*************************** payment method loop CODE VLIDATION STARTS HERE ******************/
/*
-- payment method shown as showtopper error by ORACLE FOUNDATION DEVELOPERS.
for PAY_METH_REC in PAY_METH_CUR
loop
begin
select LOOKUP_CODE INTO V_payM_LCODE from fnd_lookup_values where 1=1
and upper(lookup_type)=upper('PAYMENT METHOD')
and upper(meaning)=upper(PAY_METH_REC.PAYMENT_METHOD__NAME_LS);
update XXXX_SUP_SITE_CONT_STG set payment_mehthod_lookup_
execption
---PAYMENT_METHOD__NAME_LS
if v_count=0 then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||' , payment method lookup is not present '
where transaction_id=PAY_METH_REC.transaction_id;
end if;
end;
end loop;
*/
/*************************** payment method loop CODE VLIDATION ENDS HERE ******************/
/*************************** ship to location VLIDATION STARTS HERE ******************/
/*
for SHIP_REC in SHIP_CUR
loop
begin
select LOCATION_ID into V_LOCATION_ID from HR_LOCATIONS where 1=1
and address_line_1=SHIP_REC.ship_to_address_line_1_ls
and address_line_2=SHIP_REC.ship_to_address_line_2_ls
and address_line_3=SHIP_REC.ship_to_address_line_3_ls
and town_or_city=SHIP_REC.ship_to_town_or_city_ls
and country=SHIP_REC.ship_to_country_ls
and postal_code=SHIP_REC.ship_to_postal_code_ls;
update XXXX_SUP_SITE_CONT_STG set SHIP_TO_LOCATION_ID=v_location_id
where transaction_id=SHIP_REC.transaction_id;
exception
when no_data_found then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE', error_message=error_message||ship_to_address_line_1_ls|| ' address may not exsist. '
where transaction_id=SHIP_REC.transaction_id;
when too_many_rows then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE', error_message=error_message||ship_to_address_line_1_ls|| ' address may be multiple are exsist. '
where transaction_id=SHIP_REC.transaction_id;
when others then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE', error_message=error_message||ship_to_address_line_1_ls|| ' is Unexpected error. '
where transaction_id=SHIP_REC.transaction_id;
end;
end loop;
*/
/*************************** ship to location VLIDATION ENDS HERE ******************/
/*************************** ship via LOOKUP CODE VLIDATION STARTS HERE ******************/
/*
for SHIP_VIA_REC in SHIP_VIA_CUR
loop
begin
select LOOKUP_CODE into V_shipV_Lcode from fnd_lookup_values where 1=1
and upper(lookup_type)=upper('SHIP_VIA')
and upper(meaning)=upper(SHIP_VIA_REC.SHIP_VIA_LOOKUP_NAME_LS);
update XXXX_SUP_SITE_CONT_STG set SHIP_VIA_LOOKUP_CODE=V_shipV_Lcode
where transaction_id=SHIP_VIA_REC.transaction_id;
exception
when NO_DATA_FOUND then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||'No ship via lookup matching'
where transaction_id=SHIP_VIA_REC.transaction_id;
when TOO_MANY_ROWS then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||'TOO MANY ship via lookup'
where transaction_id=SHIP_VIA_REC.transaction_id;
when OTHERS then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||'UnExpected ship via lookup'
where transaction_id=SHIP_VIA_REC.transaction_id;
end;
end loop;
*/
/*************************** ship via LOOKUP CODE VLIDATION ENDS HERE ******************/
/*************************** FREIGHT TERMS LOOKUP CODE VLIDATION STARTS HERE ******************/
/*
for FREIGHT_REC in FREIGHT_CUR
loop
begin
select LOOKUP_CODE into V_FREIGHT_Lcode from fnd_lookup_values where 1=1
and upper(lookup_type)=upper('FREIGHT TERMS')
and upper(meaning)=upper(FREIGHT_REC.FREIGHT_TERMS_LOOKUP_NAME_LS);
update XXXX_SUP_SITE_CONT_STG set FREIGHT_TERMS_LOOKUP_CODE=V_FREIGHT_Lcode
where transaction_id=FREIGHT_REC.transaction_id;
EXCEPTION
when NO_DATA_FOUND then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||',No freight code present'
where transaction_id=FREIGHT_REC.transaction_id;
when TOO_MANY_ROWS then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||', too many freight codes '
where transaction_id=FREIGHT_REC.transaction_id;
when OTHERS then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||'unexpected error at freight code'
where transaction_id=FREIGHT_REC.transaction_id;
end;
end loop;
*/
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'V'
WHERE over_all_status_flag = 'N';
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'V'
WHERE vendor_status_flag = 'N';
UPDATE XXXX_sup_site_cont_stg
SET vendor_site_status_flag = 'V'
WHERE vendor_site_status_flag = 'N';
UPDATE XXXX_sup_site_cont_stg
SET vendor_contact_status_flag = 'V'
WHERE vendor_contact_status_flag = 'N';
/*************************** FREIGHT TERMS LOOKUP CODE VLIDATION ENDS HERE ******************/
COMMIT;
END VALIDATE;
PROCEDURE call_api (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
x_return_status VARCHAR2 (90);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (90);
p_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
x_vendor_id ap_suppliers.vendor_id%TYPE;
x_party_id hz_parties.party_id%TYPE;
p_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
x_vendor_site_id NUMBER;
x_party_site_id NUMBER;
x_location_id NUMBER;
p_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_msg_index_out NUMBER;
CURSOR sup_cur
IS
SELECT *
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag = 'V';
CURSOR supp_site_cur (p_vendor_name_ls VARCHAR2)
IS
SELECT *
FROM XXXX_sup_site_cont_stg
WHERE vendor_name_ls = p_vendor_name_ls
AND vendor_site_status_flag = 'V';
CURSOR supp_cont_cur (
p_vendor_name_ls VARCHAR2,
p_vendor_site_code_ls VARCHAR2
)
IS
SELECT *
FROM XXXX_sup_site_cont_stg
WHERE vendor_name_ls = p_vendor_name_ls
AND vendor_site_code_ls = p_vendor_site_code_ls
AND vendor_contact_status_flag = 'V';
BEGIN
/******************* VENDOR LOOP STARTS FROM HERE ***************************************/
FOR sup_rec IN sup_cur
LOOP
BEGIN
p_vendor_rec.vendor_name := sup_rec.vendor_name_ls;
p_vendor_rec.segment1 := sup_rec.segment1_ls;
p_vendor_rec.vendor_type_lookup_code :=
UPPER (sup_rec.vendor_type_lookup_code);
p_vendor_rec.terms_id := sup_rec.terms_id;
-- P_vendor_rec.PAY_GROUP_LOOKUP_CODE:=upper(SUP_REC.PAY_GROUP_LOOKUP_CODE);
p_vendor_rec.invoice_currency_code :=
UPPER (sup_rec.invoice_currency_code_ls);
p_vendor_rec.payment_currency_code :=
UPPER (sup_rec.payment_currency_code_ls);
-- P_vendor_rec.<NO PARAMETER>:=SUP_REC.PAYMENT_METHOD_LOOKUP_CODE;
-- show topper error (BUG.) but mandatory in MD 5O DOCUMENT
-- P_vendor_rec.SHIP_VIA_LOOKUP_CODE:=UPPER(SUP_REC.SHIP_VIA_LOOKUP_CODE);
--SITE LEVEL
-- P_vendor_rec.FREIGHT_TERMS_LOOKUP_CODE:=UPPER(SUP_REC.FREIGHT_TERMS_LOOKUP_CODE);
--SITE LEVEL
mo_global.set_policy_context ('S', 204);
ap_vendor_pub_pkg.create_vendor
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => p_vendor_rec,
x_vendor_id => x_vendor_id,
x_party_id => x_party_id
);
fnd_file.put_line
(fnd_file.output,
' vendor_NAME '
);
fnd_file.put_line
(fnd_file.output,
' ---------------- '
);
fnd_file.put_line (fnd_file.output,
' x_vendor_id : ' || x_vendor_id
);
fnd_file.put_line (fnd_file.output,
' x_msg_count : ' || x_msg_count
);
fnd_file.put_line (fnd_file.output,
' x_return_status :' || x_return_status
);
fnd_file.put_line (fnd_file.output,
' x_msg_data : ' || x_msg_data
);
/************************** vendor site loop starts form here ***************************/
FOR supp_site_rec IN supp_site_cur (sup_rec.vendor_name_ls)
LOOP
BEGIN
p_vendor_site_rec.vendor_id := x_vendor_id; --
p_vendor_site_rec.org_id := 204;
p_vendor_site_rec.vendor_site_code :=
supp_site_rec.vendor_site_code_ls;
p_vendor_site_rec.country :=
supp_site_rec.vendor_site_country;
p_vendor_site_rec.address_line1 :=
supp_site_rec.vendor_site_address_line1;
p_vendor_site_rec.city := supp_site_rec.vendor_site_city;
p_vendor_site_rec.state := supp_site_rec.vendor_site_state;
mo_global.set_policy_context ('S', 204);
ap_vendor_pub_pkg.create_vendor_site
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => p_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => x_party_site_id,
x_location_id => x_location_id
);
/*************** ERROR AT SITE LEVEL ***************/
fnd_msg_pub.initialize;
FOR i IN 1 .. x_msg_count
LOOP
BEGIN
fnd_msg_pub.get (p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => x_msg_index_out
);
fnd_file.put_line (fnd_file.output,
'X_MSG_DATA : ' || x_msg_data
);
END;
END LOOP;
fnd_file.put_line
(fnd_file.output,
' vendor_site '
);
fnd_file.put_line
(fnd_file.output,
' ------------- '
);
fnd_file.put_line (fnd_file.output,
' x_msg_count : ' || x_msg_count
);
fnd_file.put_line (fnd_file.output,
' x_return_status :' || x_return_status
);
fnd_file.put_line (fnd_file.output,
' x_msg_data : ' || x_msg_data
);
fnd_file.put_line (fnd_file.output,
' x_party_site_id : '
|| x_party_site_id
);
fnd_file.put_line (fnd_file.output,
' x_location_id : ' || x_location_id
);
/************************** vendor site CONTACT loop starts form here ***************************/
FOR supp_cont_rec IN
supp_cont_cur (sup_rec.vendor_name_ls,
sup_rec.vendor_site_code_ls
)
LOOP
BEGIN
p_vendor_contact_rec.vendor_id := x_vendor_id; --
p_vendor_contact_rec.org_id := 204;
p_vendor_contact_rec.org_party_site_id :=
x_party_site_id;
p_vendor_contact_rec.person_last_name :=
supp_cont_rec.contact_person_name_ls;
mo_global.set_policy_context ('S', 204);
ap_vendor_pub_pkg.create_vendor_contact
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_contact_rec => p_vendor_contact_rec,
x_vendor_contact_id => x_vendor_contact_id,
x_per_party_id => x_per_party_id,
x_rel_party_id => x_rel_party_id,
x_rel_id => x_rel_id,
x_org_contact_id => x_org_contact_id,
x_party_site_id => x_party_site_id
);
fnd_file.put_line
(fnd_file.output,
' vendor CONTACTS.... '
);
fnd_file.put_line
(fnd_file.output,
' ----------***---------- '
);
fnd_file.put_line (fnd_file.output,
' x_msg_count : '
|| x_msg_count
);
fnd_file.put_line (fnd_file.output,
' x_return_status :'
|| x_return_status
);
fnd_file.put_line (fnd_file.output,
' x_msg_data : '
|| x_msg_data
);
fnd_file.put_line (fnd_file.output,
' x_org_contact_id : '
|| x_org_contact_id
);
fnd_file.put_line (fnd_file.output,
' x_rel_party_id : '
|| x_rel_party_id
);
fnd_file.put_line (fnd_file.output,
' x_vendor_contact_id : '
|| x_vendor_contact_id
);
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
END;
END LOOP;
/******************* VENDOR contacts loop ENDS FROM HERE ***************************************/
END;
END LOOP;
/************************** vendor site loop ends form here ***************************/
EXCEPTION
WHEN OTHERS
THEN
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'LE',
error_message =
error_message || 'Rejected By API..........'
WHERE transaction_id = sup_rec.transaction_id;
END;
END LOOP;
/******************* VENDOR LOOP STARTS FROM HERE ***************************************/
END call_api;
/******************* ERROR_REPORT STARTS HERE ***************************************/
PROCEDURE error_report (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v_count NUMBER;
v_valied NUMBER;
v_failed NUMBER;
v_duplicate NUMBER;
CURSOR err_cur
IS
SELECT *
FROM XXXX_sup_site_cont_stg
WHERE error_message IS NOT NULL;
BEGIN
fnd_file.put_line (fnd_file.output,
' XXXX SUPPLIER REPORT'
);
fnd_file.put_line
(fnd_file.output,
' ------------------------'
);
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output,
RPAD ('Supplier No', 25, ' ')
|| RPAD ('Supplier Name', 30, ' ')
|| RPAD ('Supplier Site', 20, ' ')
|| RPAD ('Supplier Contact', 40, ' ')
|| RPAD ('Error', 450, ' ')
);
fnd_file.put_line (fnd_file.output,
RPAD ('-----------', 25, ' ')
|| RPAD ('--------------', 30, ' ')
|| RPAD ('----------------', 20, ' ')
|| RPAD ('---------------------', 40, ' ')
|| RPAD ('------', 40, ' ')
);
FOR err_rec IN err_cur
LOOP
BEGIN
fnd_file.put_line (fnd_file.output,
RPAD (err_rec.segment1_ls, 25, ' ')
|| RPAD (err_rec.vendor_name_ls, 30, ' ')
|| RPAD (err_rec.vendor_site_code_ls, 20, ' ')
|| RPAD (err_rec.contact_person_name_ls,
40,
' '
)
|| RPAD (err_rec.error_message, 450, ' ')
);
END;
END LOOP;
fnd_file.put_line
(fnd_file.output,
' ----------'
);
fnd_file.put_line
(fnd_file.output,
' END OF DATA'
);
fnd_file.put_line
(fnd_file.output,
' ----------'
);
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, ' -------');
fnd_file.put_line (fnd_file.output, ' SUMMARY');
fnd_file.put_line (fnd_file.output, ' -------');
SELECT COUNT (*)
INTO v_count
FROM XXXX_sup_site_cont_stg;
fnd_file.put_line
(fnd_file.output,
' Total Number of Records : '
|| v_count
);
SELECT COUNT (*)
INTO v_failed
FROM XXXX_sup_site_cont_stg
WHERE error_message IS NOT NULL;
fnd_file.put_line
(fnd_file.output,
' Total Number of invalid Records : '
|| v_failed
);
SELECT COUNT (*)
INTO v_duplicate
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag = 'X'
AND error_message IS NOT NULL;
fnd_file.put_line
(fnd_file.output,
' Total Number of duplicate Records : '
|| v_duplicate
);
END error_report;
/******************* ERROR_REPORT ENDS HERE ***************************************/
PROCEDURE call_allprod (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v1 VARCHAR2 (240);
v2 NUMBER;
BEGIN
XXXX_supp_site_cont_pkg.MAP (v1, v2);
XXXX_supp_site_cont_pkg.VALIDATE (v1, v2);
XXXX_supp_site_cont_pkg.call_api (v1, v2);
XXXX_supp_site_cont_pkg.error_report (v1, v2);
END call_allprod;
END XXXX_supp_site_cont_pkg;
/
/******************* VENDOR & VENDOR SITE PACKAGE BODY ENDS HERE *****************/
drop trigger XXXX_SUP_SITE_CONT_T
drop sequence XXXX_SUP_SITE_CONT_S
drop table XXXX_SUP_SITE_CONT_STG
/******************** STAGING TABLE Definition HERE *************************/
CREATE TABLE XXXX_SUP_SITE_CONT_STG
(
TRANSACTION_ID NUMBER,
OVER_ALL_STATUS_FLAG VARCHAR2(2),
VENDOR_STATUS_FLAG VARCHAR2(2 BYTE),
VENDOR_SITE_STATUS_FLAG VARCHAR2(2 BYTE),
VENDOR_CONTACT_STATUS_FLAG VARCHAR2(2),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
VENDOR_NAME_LS VARCHAR2(240 BYTE),
SEGMENT1_LS VARCHAR2(30 BYTE),
VENDOR_SITE_CODE_LS VARCHAR2(15 BYTE),
VENDOR_SITE_COUNTRY VARCHAR2(40 BYTE),
VENDOR_SITE_ADDRESS_LINE1 VARCHAR2(40 BYTE),
VENDOR_SITE_CITY VARCHAR2(40 BYTE),
VENDOR_SITE_STATE VARCHAR2(40 BYTE),
VENDOR_TYPE_NAME_LS VARCHAR2(40 BYTE),
VENDOR_TYPE_LOOKUP_CODE VARCHAR2(30 BYTE),
PAYMENT_TERMS_LS VARCHAR2(50 BYTE),
TERMS_ID NUMBER,
PAY_GROUP_NAME_LS VARCHAR2(40 BYTE),
PAY_GROUP_LOOKUP_CODE VARCHAR2(25 BYTE),
INVOICE_CURRENCY_CODE_LS VARCHAR2(15 BYTE),
PAYMENT_CURRENCY_CODE_LS VARCHAR2(15 BYTE),
PAYMENT_METHOD_LOOKUP_NAME_LS VARCHAR2(40 BYTE),
PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(25 BYTE),
SHIP_TO_ADDRESS_LINE_1_LS VARCHAR2(240 BYTE),
SHIP_TO_ADDRESS_LINE_2_LS VARCHAR2(240 BYTE),
SHIP_TO_ADDRESS_LINE_3_LS VARCHAR2(240 BYTE),
SHIP_TO_TOWN_OR_CITY_LS VARCHAR2(60 BYTE),
SHIP_TO_COUNTRY_LS VARCHAR2(30 BYTE),
SHIP_TO_POSTAL_CODE_LS VARCHAR2(60 BYTE),
SHIP_TO_LOCATION_ID NUMBER,
BILL_TO_ADDRESS_LINE_1_LS VARCHAR2(240 BYTE),
BILL_TO_ADDRESS_LINE_2_LS VARCHAR2(240 BYTE),
BILL_TO_ADDRESS_LINE_3_LS VARCHAR2(240 BYTE),
BILL_TO_TOWN_OR_CITY_LS VARCHAR2(60 BYTE),
BILL_TO_COUNTRY_LS VARCHAR2(30 BYTE),
BILL_TO_POSTAL_CODE_LS VARCHAR2(60 BYTE),
BILL_TO_LOCATION_ID NUMBER,
SHIP_VIA_LOOKUP_NAME_LS VARCHAR2(40 BYTE),
SHIP_VIA_LOOKUP_CODE VARCHAR2(25 BYTE),
FREIGHT_TERMS_LOOKUP_NAME_LS VARCHAR2(40 BYTE),
FREIGHT_TERMS_LOOKUP_CODE VARCHAR2(25 BYTE),
CONTACT_PERSON_NAME_LS VARCHAR2(240),
VENDOR_INTERFACE_ID NUMBER,
VENDOR_SITE_INTERFACE_ID NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER
)
/******************** SEQUENCE Definition HERE *************************/
CREATE SEQUENCE XXXX_SUP_SITE_CONT_S
/******************** TRIGGER Definition HERE *************************/
CREATE OR REPLACE TRIGGER XXXX_SUP_SITE_CONT_T
BEFORE INSERT ON APPS.XXXX_SUP_SITE_CONT_STG FOR EACH ROW
BEGIN
SELECT XXXX_SUP_SITE_CONT_S.NEXTVAL INTO :NEW.TRANSACTION_ID FROM DUAL;
END XXXX_SUP_SITE_CONT_T;
/
SELECT * FROM XXXX_SUP_SITE_CONT_STG
insert into XXXX_SUP_SITE_CONT_STG(vendor_name_ls,segment1_ls,VENDOR_SITE_CODE_LS,CONTACT_PERSON_NAME_LS)
values('xyzvenr4',2201234,'SIT423','SRIN11234')
insert into XXXX_SUP_SITE_CONT_STG(vendor_name_ls,segment1_ls,VENDOR_SITE_CODE_LS,CONTACT_PERSON_NAME_LS)
values('abcvenr4',8801234,'SIT423','SRIN21234')
insert into XXXX_SUP_SITE_CONT_STG(vendor_name_ls,segment1_ls,VENDOR_SITE_CODE_LS,CONTACT_PERSON_NAME_LS)
values('vvvend4',8511234,'SITE23','SRINA31234')
insert into XXXX_SUP_SITE_CONT_STG(vendor_name_ls,segment1_ls,VENDOR_SITE_CODE_LS,CONTACT_PERSON_NAME_LS)
values('vvvr34',851234,'SIT4123','SRINIVA234')
COMMIT;
SELECT * FROM XXXX_SUP_SITE_CONT_STG
declare
v1 varchar2(90);
v2 number;
begin
XXXX_SUPP_SITE_CONT_PKG.map(v1,v2);
XXXX_supp_site_cont_pkg.validate(v1,v2);
end;
--ALL PRODS --
CALL_ALLPROD
declare
v1 varchar2(90);
v2 number;
begin
XXXX_SUPP_SITE_CONT_PKG.CALL_ALLPROD(v1,v2);
end;
select * from ap_SUPPLIERS WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)
SELECT * FROM AP_SUPPLIER_SITES_ALL WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)
SELECT * FROM AP_SUPPLIER_CONTACTS WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)
----------------------------------------------------------------------------------------------------------
/* Formatted on 2013/08/19 17:51 (Formatter Plus v4.8.8) */
/************************ PACKAGE *****************/
CREATE OR REPLACE PACKAGE XXXX_supp_site_cont_pkg
IS
PROCEDURE MAP (errbuf OUT VARCHAR2, retcode OUT NUMBER);
PROCEDURE VALIDATE (errbuf OUT VARCHAR2, retcode OUT NUMBER);
PROCEDURE call_api (errbuf OUT VARCHAR2, retcode OUT NUMBER);
PROCEDURE error_report (errbuf OUT VARCHAR2, retcode OUT NUMBER);
PROCEDURE call_allprod (errbuf OUT VARCHAR2, retcode OUT NUMBER);
END XXXX_supp_site_cont_pkg;
/
/******************* VENDOR & VENDOR SITE PACKAGE ENDS HERE *****************/
/******************* VENDOR & VENDOR SITE PACKAGE BODY STARTS HERE *****************/
CREATE OR REPLACE PACKAGE BODY XXXX_supp_site_cont_pkg
IS
PROCEDURE MAP (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
BEGIN
/*************************** UPDATE STAGING TABLE STARTS HERE **************/
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'N',
vendor_status_flag = 'N',
vendor_site_status_flag = 'N',
vendor_contact_status_flag = 'N',
error_message = NULL,
terms_id = NULL,
vendor_type_lookup_code = NULL,
pay_group_lookup_code = NULL,
payment_method_lookup_code = NULL,
ship_to_location_id = NULL,
bill_to_location_id = NULL,
ship_via_lookup_code = NULL,
freight_terms_lookup_code = NULL,
vendor_interface_id = 102,
vendor_site_interface_id = 102,
creation_date = TRUNC (SYSDATE),
created_by = 1318,
last_update_date = TRUNC (SYSDATE),
last_updated_by = 1318;
UPDATE XXXX_sup_site_cont_stg
SET invoice_currency_code_ls = 'USD'
WHERE invoice_currency_code_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET payment_currency_code_ls = 'USD'
WHERE payment_currency_code_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET vendor_type_name_ls = 'VENDOR'
WHERE vendor_type_name_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'MM',
error_message = 'Mandatory Column is missing'
WHERE vendor_name_ls IS NULL OR segment1_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET vendor_site_status_flag = 'MM',
error_message = 'Mandatory Column is missing'
WHERE vendor_site_code_ls IS NULL;
UPDATE XXXX_sup_site_cont_stg
SET vendor_contact_status_flag = 'MM',
error_message = 'Mandatory Column is missing'
WHERE contact_person_name_ls IS NULL;
/*************************** UPDATE STAGING TABLE ENDS HERE **************/
COMMIT;
END MAP;
PROCEDURE VALIDATE (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v_count NUMBER;
v_term_id NUMBER;
v_location_id NUMBER;
v_ven_lcode VARCHAR2 (40);
v_payg_lcode VARCHAR2 (40);
v_paym_lcode VARCHAR2 (40);
v_shipv_lcode VARCHAR2 (40);
v_freight_lcode VARCHAR2 (40);
CURSOR ven_cur
IS
SELECT vendor_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM');
CURSOR ven_no_cur
IS
SELECT vendor_name_ls, segment1_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM');
CURSOR ven_site_cur (p_vendor_name_ls VARCHAR2)
IS
SELECT vendor_name_ls, vendor_site_code_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_site_status_flag NOT IN ('X', 'MM')
AND vendor_name_ls IS NOT NULL
AND vendor_name_ls = p_vendor_name_ls;
CURSOR ven_type_cur
IS
SELECT vendor_type_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND vendor_type_name_ls IS NOT NULL;
CURSOR pay_term_cur
IS
SELECT payment_terms_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND payment_terms_ls IS NOT NULL;
CURSOR pay_group_cur
IS
SELECT pay_group_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND pay_group_name_ls IS NOT NULL;
CURSOR inv_curr_cur
IS
SELECT invoice_currency_code_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND invoice_currency_code_ls IS NOT NULL;
CURSOR pay_curr_cur
IS
SELECT payment_currency_code_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND payment_currency_code_ls IS NOT NULL;
CURSOR pay_meth_cur
IS
SELECT payment_method_lookup_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND payment_method_lookup_name_ls IS NOT NULL;
CURSOR ship_cur
IS
SELECT ship_to_address_line_1_ls, ship_to_address_line_2_ls,
ship_to_address_line_3_ls, ship_to_town_or_city_ls,
ship_to_country_ls, ship_to_postal_code_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND ship_to_address_line_1_ls IS NOT NULL;
CURSOR ship_via_cur
IS
SELECT ship_via_lookup_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND ship_via_lookup_name_ls IS NOT NULL;
CURSOR freight_cur
IS
SELECT freight_terms_lookup_name_ls, transaction_id
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag NOT IN ('X', 'MM')
AND freight_terms_lookup_name_ls IS NOT NULL;
BEGIN
/**********************DUPLICATE RECORD ELIMNATION STARTS HERE**********************************/
BEGIN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'X',
error_message = 'Duplicate Record'
WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM XXXX_sup_site_cont_stg
GROUP BY vendor_name_ls);
UPDATE XXXX_sup_site_cont_stg
SET vendor_site_status_flag = 'X',
error_message = 'Duplicate Record'
WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM XXXX_sup_site_cont_stg
GROUP BY vendor_site_code_ls);
UPDATE XXXX_sup_site_cont_stg
SET vendor_contact_status_flag = 'X',
error_message = 'Duplicate Record'
WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM XXXX_sup_site_cont_stg
GROUP BY contact_person_name_ls);
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'MM',
error_message = 'Mandatory Column Missing'
WHERE vendor_name_ls IS NULL
OR segment1_ls IS NULL
OR vendor_site_code_ls IS NULL;
END;
/**********************DUPLICATE RECORD ELIMNATION ENDS HERE**********************************/
/*************************** VENDOR NAME VALIDATION SARTS HERE ******************/
FOR ven_rec IN ven_cur
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM ap_suppliers
WHERE UPPER (vendor_name) = UPPER (ven_rec.vendor_name_ls);
-- here count >0 means the vendor is present in base table then error out other wise it is new vendor.
IF v_count > 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message = 'Vendor alredy Present'
WHERE transaction_id = ven_rec.transaction_id;
END IF;
END;
END LOOP;
/*************************** VENDOR NAME VALIDATION SARTS HERE ******************/
/*************************** VENDOR NUMBER (SEGMENT1) VALIDATION SARTS HERE ******************/
FOR ven_no_rec IN ven_no_cur
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM ap_suppliers
WHERE UPPER (segment1) = UPPER (ven_no_rec.segment1_ls);
IF v_count > 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| ', Vendor Number esist in base table '
WHERE transaction_id = ven_no_rec.transaction_id;
END IF;
END;
END LOOP;
/*************************** VENDOR NUMBER (SEGMENT1) VALIDATION ENDS HERE ******************/
/*************************** VENDOR SITE CODE VALIDATION STARTS HERE ******************/
/*
for VEN_SITE_REC in VEN_SITE_CUR
loop
begin
select VENDOR_SITE_ID into V_VENDOR_SITE_ID from AP_SUPPLIER_SITES_ALL
where UPPER(vendor_site_code)=upper(VEN_SITE_REC.vendor_site_code_ls)
and vendor_id=VEN_SITE_REC.vendor_id;
update XXXX_SUP_SITE_CONT_STG set vendor_site_id=v_vendor_site_id where
transaction_id=VEN_SITE_REC.transaction_id;
exception
when no_data_found then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||', No Site found :'||vendor_site_code_ls
where transaction_id=VEN_SITE_REC. transaction_id;
when too_many_rows then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||', No many Sites found :'||vendor_site_code_ls
where transaction_id=VEN_SITE_REC. transaction_id;
when others then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||', UnExcepted error at :'||vendor_site_code_ls
where transaction_id=VEN_SITE_REC. transaction_id;
end;
end loop;
*/
/************* site is dependacy validation ***************/
FOR ven_rec IN ven_cur
LOOP
BEGIN
FOR ven_site_rec IN ven_site_cur (ven_rec.vendor_name_ls)
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM ap_suppliers as1, ap_supplier_sites_all assa
WHERE as1.vendor_id = assa.vendor_id
AND as1.vendor_name = ven_site_rec.vendor_name_ls
AND assa.vendor_site_code =
ven_site_rec.vendor_site_code_ls;
IF v_count > 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_site_status_flag = 'VE',
error_message =
error_message
|| ',Same SITE is exists for this Vendor'
WHERE transaction_id = ven_site_rec.transaction_id;
END IF;
END;
END LOOP;
END;
END LOOP;
/*************************** VENDOR SITE CODE VALIDATION ENDS HERE ******************/
/*************************** VENDOR TYPE LOOKUP CODE VALIDATION STARTS HERE ******************/
FOR ven_type_rec IN ven_type_cur
LOOP
BEGIN
SELECT lookup_code
INTO v_ven_lcode
FROM fnd_lookup_values
WHERE 1 = 1
AND UPPER (lookup_type) =
UPPER ('vendor type')
--must be upper case
AND LANGUAGE = USERENV ('lang')
AND UPPER (meaning) = UPPER (ven_type_rec.vendor_type_name_ls);
UPDATE XXXX_sup_site_cont_stg
SET vendor_type_lookup_code = v_ven_lcode
WHERE transaction_id = ven_type_rec.transaction_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message || 'vendor type name is not exist '
WHERE transaction_id = ven_type_rec.transaction_id;
WHEN TOO_MANY_ROWS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message || 'many vendor type name '
WHERE transaction_id = ven_type_rec.transaction_id;
WHEN OTHERS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message || 'vendor type name is invalid '
WHERE transaction_id = ven_type_rec.transaction_id;
END;
END LOOP;
/*************************** VENDOR TYPE LOOKUP CODE VALIDATION ENDS HERE ******************/
/*************************** PAYMENT TERMS VLIDATION STARTS HERE ******************/
FOR pay_term_rec IN pay_term_cur
LOOP
BEGIN
SELECT term_id
INTO v_term_id
FROM ap_terms_tl
WHERE 1 = 1
AND LANGUAGE = USERENV ('lang')
AND UPPER (NAME) = UPPER (pay_term_rec.payment_terms_ls);
UPDATE XXXX_sup_site_cont_stg
SET terms_id = v_term_id
WHERE transaction_id = pay_term_rec.transaction_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| payment_terms_ls
|| ' does not exsist. '
WHERE transaction_id = pay_term_rec.transaction_id;
WHEN TOO_MANY_ROWS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| payment_terms_ls
|| ' multiple are exsist. '
WHERE transaction_id = pay_term_rec.transaction_id;
WHEN OTHERS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| payment_terms_ls
|| ' is Unexpected error. '
WHERE transaction_id = pay_term_rec.transaction_id;
END;
END LOOP;
/*************************** PAYMENT TERMS VLIDATION ENDS HERE ******************/
/*************************** PAYMENT GROUP LOOKUP CODE VLIDATION STARTS HERE ******************/
FOR pay_group_rec IN pay_group_cur
LOOP
BEGIN
SELECT lookup_code
INTO v_payg_lcode
FROM fnd_lookup_values
WHERE 1 = 1
AND UPPER (lookup_type) =
UPPER ('pay group')
--must be upper case
AND LANGUAGE = USERENV ('lang')
AND UPPER (meaning) = UPPER (pay_group_rec.pay_group_name_ls);
UPDATE XXXX_sup_site_cont_stg
SET pay_group_lookup_code = v_payg_lcode
WHERE transaction_id = pay_group_rec.transaction_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message = error_message || 'invalied pay group '
WHERE transaction_id = pay_group_rec.transaction_id;
WHEN TOO_MANY_ROWS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message = error_message || 'too many pay group '
WHERE transaction_id = pay_group_rec.transaction_id;
WHEN OTHERS
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message || 'un expected error at pay group '
WHERE transaction_id = pay_group_rec.transaction_id;
END;
END LOOP;
/*************************** PAYMENT GROUP LOOKUP CODE VLIDATION ENDS HERE ******************/
/*************************** INVOICE CURRENCY CODE VLIDATION STARTS HERE ******************/
FOR inv_curr_rec IN inv_curr_cur
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM fnd_currencies
WHERE UPPER (currency_code) =
UPPER (inv_curr_rec.invoice_currency_code_ls);
IF v_count = 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| ' , invoice currency code is not present '
WHERE transaction_id = inv_curr_rec.transaction_id;
END IF;
END;
END LOOP;
/*************************** INVOICE CURRENCY CODE VLIDATION ENDS HERE ******************/
/*************************** payment CURRENCY CODE VLIDATION STARTS HERE ******************/
FOR pay_curr_rec IN pay_curr_cur
LOOP
BEGIN
SELECT COUNT (*)
INTO v_count
FROM fnd_currencies
WHERE UPPER (currency_code) =
UPPER (pay_curr_rec.payment_currency_code_ls);
IF v_count = 0
THEN
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'VE',
error_message =
error_message
|| ' , payment currency code is not present '
WHERE transaction_id = pay_curr_rec.transaction_id;
END IF;
END;
END LOOP;
/*************************** payment CURRENCY CODE VLIDATION ENDS HERE ******************/
/*************************** payment method loop CODE VLIDATION STARTS HERE ******************/
/*
-- payment method shown as showtopper error by ORACLE FOUNDATION DEVELOPERS.
for PAY_METH_REC in PAY_METH_CUR
loop
begin
select LOOKUP_CODE INTO V_payM_LCODE from fnd_lookup_values where 1=1
and upper(lookup_type)=upper('PAYMENT METHOD')
and upper(meaning)=upper(PAY_METH_REC.PAYMENT_METHOD__NAME_LS);
update XXXX_SUP_SITE_CONT_STG set payment_mehthod_lookup_
execption
---PAYMENT_METHOD__NAME_LS
if v_count=0 then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||' , payment method lookup is not present '
where transaction_id=PAY_METH_REC.transaction_id;
end if;
end;
end loop;
*/
/*************************** payment method loop CODE VLIDATION ENDS HERE ******************/
/*************************** ship to location VLIDATION STARTS HERE ******************/
/*
for SHIP_REC in SHIP_CUR
loop
begin
select LOCATION_ID into V_LOCATION_ID from HR_LOCATIONS where 1=1
and address_line_1=SHIP_REC.ship_to_address_line_1_ls
and address_line_2=SHIP_REC.ship_to_address_line_2_ls
and address_line_3=SHIP_REC.ship_to_address_line_3_ls
and town_or_city=SHIP_REC.ship_to_town_or_city_ls
and country=SHIP_REC.ship_to_country_ls
and postal_code=SHIP_REC.ship_to_postal_code_ls;
update XXXX_SUP_SITE_CONT_STG set SHIP_TO_LOCATION_ID=v_location_id
where transaction_id=SHIP_REC.transaction_id;
exception
when no_data_found then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE', error_message=error_message||ship_to_address_line_1_ls|| ' address may not exsist. '
where transaction_id=SHIP_REC.transaction_id;
when too_many_rows then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE', error_message=error_message||ship_to_address_line_1_ls|| ' address may be multiple are exsist. '
where transaction_id=SHIP_REC.transaction_id;
when others then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE', error_message=error_message||ship_to_address_line_1_ls|| ' is Unexpected error. '
where transaction_id=SHIP_REC.transaction_id;
end;
end loop;
*/
/*************************** ship to location VLIDATION ENDS HERE ******************/
/*************************** ship via LOOKUP CODE VLIDATION STARTS HERE ******************/
/*
for SHIP_VIA_REC in SHIP_VIA_CUR
loop
begin
select LOOKUP_CODE into V_shipV_Lcode from fnd_lookup_values where 1=1
and upper(lookup_type)=upper('SHIP_VIA')
and upper(meaning)=upper(SHIP_VIA_REC.SHIP_VIA_LOOKUP_NAME_LS);
update XXXX_SUP_SITE_CONT_STG set SHIP_VIA_LOOKUP_CODE=V_shipV_Lcode
where transaction_id=SHIP_VIA_REC.transaction_id;
exception
when NO_DATA_FOUND then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||'No ship via lookup matching'
where transaction_id=SHIP_VIA_REC.transaction_id;
when TOO_MANY_ROWS then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||'TOO MANY ship via lookup'
where transaction_id=SHIP_VIA_REC.transaction_id;
when OTHERS then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||'UnExpected ship via lookup'
where transaction_id=SHIP_VIA_REC.transaction_id;
end;
end loop;
*/
/*************************** ship via LOOKUP CODE VLIDATION ENDS HERE ******************/
/*************************** FREIGHT TERMS LOOKUP CODE VLIDATION STARTS HERE ******************/
/*
for FREIGHT_REC in FREIGHT_CUR
loop
begin
select LOOKUP_CODE into V_FREIGHT_Lcode from fnd_lookup_values where 1=1
and upper(lookup_type)=upper('FREIGHT TERMS')
and upper(meaning)=upper(FREIGHT_REC.FREIGHT_TERMS_LOOKUP_NAME_LS);
update XXXX_SUP_SITE_CONT_STG set FREIGHT_TERMS_LOOKUP_CODE=V_FREIGHT_Lcode
where transaction_id=FREIGHT_REC.transaction_id;
EXCEPTION
when NO_DATA_FOUND then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||',No freight code present'
where transaction_id=FREIGHT_REC.transaction_id;
when TOO_MANY_ROWS then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||', too many freight codes '
where transaction_id=FREIGHT_REC.transaction_id;
when OTHERS then
update XXXX_SUP_SITE_CONT_STG set VENDOR_STATUS_FLAG='VE',error_message=error_message||'unexpected error at freight code'
where transaction_id=FREIGHT_REC.transaction_id;
end;
end loop;
*/
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'V'
WHERE over_all_status_flag = 'N';
UPDATE XXXX_sup_site_cont_stg
SET vendor_status_flag = 'V'
WHERE vendor_status_flag = 'N';
UPDATE XXXX_sup_site_cont_stg
SET vendor_site_status_flag = 'V'
WHERE vendor_site_status_flag = 'N';
UPDATE XXXX_sup_site_cont_stg
SET vendor_contact_status_flag = 'V'
WHERE vendor_contact_status_flag = 'N';
/*************************** FREIGHT TERMS LOOKUP CODE VLIDATION ENDS HERE ******************/
COMMIT;
END VALIDATE;
PROCEDURE call_api (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
x_return_status VARCHAR2 (90);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (90);
p_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
x_vendor_id ap_suppliers.vendor_id%TYPE;
x_party_id hz_parties.party_id%TYPE;
p_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
x_vendor_site_id NUMBER;
x_party_site_id NUMBER;
x_location_id NUMBER;
p_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_msg_index_out NUMBER;
CURSOR sup_cur
IS
SELECT *
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag = 'V';
CURSOR supp_site_cur (p_vendor_name_ls VARCHAR2)
IS
SELECT *
FROM XXXX_sup_site_cont_stg
WHERE vendor_name_ls = p_vendor_name_ls
AND vendor_site_status_flag = 'V';
CURSOR supp_cont_cur (
p_vendor_name_ls VARCHAR2,
p_vendor_site_code_ls VARCHAR2
)
IS
SELECT *
FROM XXXX_sup_site_cont_stg
WHERE vendor_name_ls = p_vendor_name_ls
AND vendor_site_code_ls = p_vendor_site_code_ls
AND vendor_contact_status_flag = 'V';
BEGIN
/******************* VENDOR LOOP STARTS FROM HERE ***************************************/
FOR sup_rec IN sup_cur
LOOP
BEGIN
p_vendor_rec.vendor_name := sup_rec.vendor_name_ls;
p_vendor_rec.segment1 := sup_rec.segment1_ls;
p_vendor_rec.vendor_type_lookup_code :=
UPPER (sup_rec.vendor_type_lookup_code);
p_vendor_rec.terms_id := sup_rec.terms_id;
-- P_vendor_rec.PAY_GROUP_LOOKUP_CODE:=upper(SUP_REC.PAY_GROUP_LOOKUP_CODE);
p_vendor_rec.invoice_currency_code :=
UPPER (sup_rec.invoice_currency_code_ls);
p_vendor_rec.payment_currency_code :=
UPPER (sup_rec.payment_currency_code_ls);
-- P_vendor_rec.<NO PARAMETER>:=SUP_REC.PAYMENT_METHOD_LOOKUP_CODE;
-- show topper error (BUG.) but mandatory in MD 5O DOCUMENT
-- P_vendor_rec.SHIP_VIA_LOOKUP_CODE:=UPPER(SUP_REC.SHIP_VIA_LOOKUP_CODE);
--SITE LEVEL
-- P_vendor_rec.FREIGHT_TERMS_LOOKUP_CODE:=UPPER(SUP_REC.FREIGHT_TERMS_LOOKUP_CODE);
--SITE LEVEL
mo_global.set_policy_context ('S', 204);
ap_vendor_pub_pkg.create_vendor
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => p_vendor_rec,
x_vendor_id => x_vendor_id,
x_party_id => x_party_id
);
fnd_file.put_line
(fnd_file.output,
' vendor_NAME '
);
fnd_file.put_line
(fnd_file.output,
' ---------------- '
);
fnd_file.put_line (fnd_file.output,
' x_vendor_id : ' || x_vendor_id
);
fnd_file.put_line (fnd_file.output,
' x_msg_count : ' || x_msg_count
);
fnd_file.put_line (fnd_file.output,
' x_return_status :' || x_return_status
);
fnd_file.put_line (fnd_file.output,
' x_msg_data : ' || x_msg_data
);
/************************** vendor site loop starts form here ***************************/
FOR supp_site_rec IN supp_site_cur (sup_rec.vendor_name_ls)
LOOP
BEGIN
p_vendor_site_rec.vendor_id := x_vendor_id; --
p_vendor_site_rec.org_id := 204;
p_vendor_site_rec.vendor_site_code :=
supp_site_rec.vendor_site_code_ls;
p_vendor_site_rec.country :=
supp_site_rec.vendor_site_country;
p_vendor_site_rec.address_line1 :=
supp_site_rec.vendor_site_address_line1;
p_vendor_site_rec.city := supp_site_rec.vendor_site_city;
p_vendor_site_rec.state := supp_site_rec.vendor_site_state;
mo_global.set_policy_context ('S', 204);
ap_vendor_pub_pkg.create_vendor_site
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => p_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => x_party_site_id,
x_location_id => x_location_id
);
/*************** ERROR AT SITE LEVEL ***************/
fnd_msg_pub.initialize;
FOR i IN 1 .. x_msg_count
LOOP
BEGIN
fnd_msg_pub.get (p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => x_msg_index_out
);
fnd_file.put_line (fnd_file.output,
'X_MSG_DATA : ' || x_msg_data
);
END;
END LOOP;
fnd_file.put_line
(fnd_file.output,
' vendor_site '
);
fnd_file.put_line
(fnd_file.output,
' ------------- '
);
fnd_file.put_line (fnd_file.output,
' x_msg_count : ' || x_msg_count
);
fnd_file.put_line (fnd_file.output,
' x_return_status :' || x_return_status
);
fnd_file.put_line (fnd_file.output,
' x_msg_data : ' || x_msg_data
);
fnd_file.put_line (fnd_file.output,
' x_party_site_id : '
|| x_party_site_id
);
fnd_file.put_line (fnd_file.output,
' x_location_id : ' || x_location_id
);
/************************** vendor site CONTACT loop starts form here ***************************/
FOR supp_cont_rec IN
supp_cont_cur (sup_rec.vendor_name_ls,
sup_rec.vendor_site_code_ls
)
LOOP
BEGIN
p_vendor_contact_rec.vendor_id := x_vendor_id; --
p_vendor_contact_rec.org_id := 204;
p_vendor_contact_rec.org_party_site_id :=
x_party_site_id;
p_vendor_contact_rec.person_last_name :=
supp_cont_rec.contact_person_name_ls;
mo_global.set_policy_context ('S', 204);
ap_vendor_pub_pkg.create_vendor_contact
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_contact_rec => p_vendor_contact_rec,
x_vendor_contact_id => x_vendor_contact_id,
x_per_party_id => x_per_party_id,
x_rel_party_id => x_rel_party_id,
x_rel_id => x_rel_id,
x_org_contact_id => x_org_contact_id,
x_party_site_id => x_party_site_id
);
fnd_file.put_line
(fnd_file.output,
' vendor CONTACTS.... '
);
fnd_file.put_line
(fnd_file.output,
' ----------***---------- '
);
fnd_file.put_line (fnd_file.output,
' x_msg_count : '
|| x_msg_count
);
fnd_file.put_line (fnd_file.output,
' x_return_status :'
|| x_return_status
);
fnd_file.put_line (fnd_file.output,
' x_msg_data : '
|| x_msg_data
);
fnd_file.put_line (fnd_file.output,
' x_org_contact_id : '
|| x_org_contact_id
);
fnd_file.put_line (fnd_file.output,
' x_rel_party_id : '
|| x_rel_party_id
);
fnd_file.put_line (fnd_file.output,
' x_vendor_contact_id : '
|| x_vendor_contact_id
);
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
END;
END LOOP;
/******************* VENDOR contacts loop ENDS FROM HERE ***************************************/
END;
END LOOP;
/************************** vendor site loop ends form here ***************************/
EXCEPTION
WHEN OTHERS
THEN
UPDATE XXXX_sup_site_cont_stg
SET over_all_status_flag = 'LE',
error_message =
error_message || 'Rejected By API..........'
WHERE transaction_id = sup_rec.transaction_id;
END;
END LOOP;
/******************* VENDOR LOOP STARTS FROM HERE ***************************************/
END call_api;
/******************* ERROR_REPORT STARTS HERE ***************************************/
PROCEDURE error_report (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v_count NUMBER;
v_valied NUMBER;
v_failed NUMBER;
v_duplicate NUMBER;
CURSOR err_cur
IS
SELECT *
FROM XXXX_sup_site_cont_stg
WHERE error_message IS NOT NULL;
BEGIN
fnd_file.put_line (fnd_file.output,
' XXXX SUPPLIER REPORT'
);
fnd_file.put_line
(fnd_file.output,
' ------------------------'
);
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output,
RPAD ('Supplier No', 25, ' ')
|| RPAD ('Supplier Name', 30, ' ')
|| RPAD ('Supplier Site', 20, ' ')
|| RPAD ('Supplier Contact', 40, ' ')
|| RPAD ('Error', 450, ' ')
);
fnd_file.put_line (fnd_file.output,
RPAD ('-----------', 25, ' ')
|| RPAD ('--------------', 30, ' ')
|| RPAD ('----------------', 20, ' ')
|| RPAD ('---------------------', 40, ' ')
|| RPAD ('------', 40, ' ')
);
FOR err_rec IN err_cur
LOOP
BEGIN
fnd_file.put_line (fnd_file.output,
RPAD (err_rec.segment1_ls, 25, ' ')
|| RPAD (err_rec.vendor_name_ls, 30, ' ')
|| RPAD (err_rec.vendor_site_code_ls, 20, ' ')
|| RPAD (err_rec.contact_person_name_ls,
40,
' '
)
|| RPAD (err_rec.error_message, 450, ' ')
);
END;
END LOOP;
fnd_file.put_line
(fnd_file.output,
' ----------'
);
fnd_file.put_line
(fnd_file.output,
' END OF DATA'
);
fnd_file.put_line
(fnd_file.output,
' ----------'
);
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, ' -------');
fnd_file.put_line (fnd_file.output, ' SUMMARY');
fnd_file.put_line (fnd_file.output, ' -------');
SELECT COUNT (*)
INTO v_count
FROM XXXX_sup_site_cont_stg;
fnd_file.put_line
(fnd_file.output,
' Total Number of Records : '
|| v_count
);
SELECT COUNT (*)
INTO v_failed
FROM XXXX_sup_site_cont_stg
WHERE error_message IS NOT NULL;
fnd_file.put_line
(fnd_file.output,
' Total Number of invalid Records : '
|| v_failed
);
SELECT COUNT (*)
INTO v_duplicate
FROM XXXX_sup_site_cont_stg
WHERE vendor_status_flag = 'X'
AND error_message IS NOT NULL;
fnd_file.put_line
(fnd_file.output,
' Total Number of duplicate Records : '
|| v_duplicate
);
END error_report;
/******************* ERROR_REPORT ENDS HERE ***************************************/
PROCEDURE call_allprod (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v1 VARCHAR2 (240);
v2 NUMBER;
BEGIN
XXXX_supp_site_cont_pkg.MAP (v1, v2);
XXXX_supp_site_cont_pkg.VALIDATE (v1, v2);
XXXX_supp_site_cont_pkg.call_api (v1, v2);
XXXX_supp_site_cont_pkg.error_report (v1, v2);
END call_allprod;
END XXXX_supp_site_cont_pkg;
/
/******************* VENDOR & VENDOR SITE PACKAGE BODY ENDS HERE *****************/
