CREATE OR REPLACE procedure APPS.xx_phone_upload as
p_date_from DATE;
p_date_to DATE;
p_phone_type VARCHAR2 (200);
p_phone_number VARCHAR2 (200);
p_parent_id NUMBER;
p_parent_table VARCHAR2 (200);
p_attribute_category VARCHAR2 (200);
p_attribute1 VARCHAR2 (200);
p_attribute2 VARCHAR2 (200);
p_attribute3 VARCHAR2 (200);
p_attribute4 VARCHAR2 (200);
p_attribute5 VARCHAR2 (200);
p_attribute6 VARCHAR2 (200);
p_attribute7 VARCHAR2 (200);
p_attribute8 VARCHAR2 (200);
p_attribute9 VARCHAR2 (200);
p_attribute10 VARCHAR2 (200);
p_attribute11 VARCHAR2 (200);
p_attribute12 VARCHAR2 (200);
p_attribute13 VARCHAR2 (200);
p_attribute14 VARCHAR2 (200);
p_attribute15 VARCHAR2 (200);
p_attribute16 VARCHAR2 (200);
p_attribute17 VARCHAR2 (200);
p_attribute18 VARCHAR2 (200);
p_attribute19 VARCHAR2 (200);
p_attribute20 VARCHAR2 (200);
p_attribute21 VARCHAR2 (200);
p_attribute22 VARCHAR2 (200);
p_attribute23 VARCHAR2 (200);
p_attribute24 VARCHAR2 (200);
p_attribute25 VARCHAR2 (200);
p_attribute26 VARCHAR2 (200);
p_attribute27 VARCHAR2 (200);
p_attribute28 VARCHAR2 (200);
p_attribute29 VARCHAR2 (200);
p_attribute30 VARCHAR2 (200);
p_validate BOOLEAN;
p_effective_date DATE;
p_party_id NUMBER;
p_validity VARCHAR2 (200);
p_object_version_number NUMBER;
p_phone_id NUMBER;
v_pid NUMBER;
v_partyid NUMBER;
v_count NUMBER;
x_msg_count NUMBER;
v_status VARCHAR2 (100);
v_ph_type VARCHAR2 (100);
errm VARCHAR2 (4000);
CURSOR c1
IS
SELECT *
FROM xx_phone
WHERE phone_number IS NOT NULL and STATUS is null ;
--- WHERE phone_type <> 'PERM';
BEGIN
fnd_global.apps_initialize (0, 50637, 800);
FOR i IN c1
LOOP
BEGIN
errm := '';
SELECT DISTINCT person_id
INTO v_pid
FROM per_all_people_f
WHERE employee_number = i.employee_code;
v_status := 'S';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_status := 'E';
errm := 'Person_id Err:' || SQLERRM || SQLCODE;
DBMS_OUTPUT.put_line ('Employee Number Not Exist ' || errm);
END;
IF v_status = 'S'
THEN
BEGIN
SELECT DISTINCT party_id
INTO v_partyid
FROM per_all_people_f
WHERE employee_number = i.employee_code;
v_status := 'S';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_status := 'E';
errm := 'party_id :' || SQLERRM || SQLCODE || errm;
DBMS_OUTPUT.put_line ('Error in Party ' || errm);
END;
END IF;
IF v_status = 'S'
THEN
BEGIN
SELECT lookup_code
INTO v_ph_type
FROM fnd_lookups
WHERE lookup_type LIKE 'JTA_EC_PHONE_TYPE'
AND meaning = i.phone_type;
v_status := 'S';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_status := 'E';
errm := 'Phone Type :' || SQLERRM || SQLCODE || errm;
DBMS_OUTPUT.put_line ('Phone Type' || errm);
END;
END IF;
SELECT COUNT (1)
INTO v_count
FROM per_phones
WHERE parent_id = v_pid
AND phone_number = i.phone_number
AND phone_type = i.phone_type;
IF (v_count > 0)
THEN
DBMS_OUTPUT.put_line ('Already exist');
errm := 'Already exists' || errm;
v_status := 'E';
END IF;
IF v_count = 0 AND v_status = 'S'
THEN
DBMS_OUTPUT.put_line ('inside block ---');
---v_status := 'S';
p_date_from := i.date_from;
p_date_to :=null;
p_phone_type := v_ph_type; -- i.phone_type;
p_phone_number := i.phone_number;
p_parent_id := v_pid;
p_parent_table := 'PER_ALL_PEOPLE_F';
p_attribute_category := NULL;
p_attribute1 := NULL;
p_attribute2 := NULL;
p_attribute3 := NULL;
p_attribute4 := NULL;
p_attribute5 := NULL;
p_attribute6 := NULL;
p_attribute7 := NULL;
p_attribute8 := NULL;
p_attribute9 := NULL;
p_attribute10 := NULL;
p_attribute11 := NULL;
p_attribute12 := NULL;
p_attribute13 := NULL;
p_attribute14 := NULL;
p_attribute15 := NULL;
p_attribute16 := NULL;
p_attribute17 := NULL;
p_attribute18 := NULL;
p_attribute19 := NULL;
p_attribute20 := NULL;
p_attribute21 := NULL;
p_attribute22 := NULL;
p_attribute23 := NULL;
p_attribute24 := NULL;
p_attribute25 := NULL;
p_attribute26 := NULL;
p_attribute27 := NULL;
p_attribute28 := NULL;
p_attribute29 := NULL;
p_attribute30 := NULL;
p_validate := FALSE;
p_effective_date := SYSDATE;
p_party_id := v_partyid;
p_validity := NULL;
p_object_version_number := NULL;
p_phone_id := NULL;
BEGIN
apps.hr_phone_api.create_phone (p_date_from,
p_date_to,
p_phone_type,
p_phone_number,
p_parent_id,
p_parent_table,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_attribute21,
p_attribute22,
p_attribute23,
p_attribute24,
p_attribute25,
p_attribute26,
p_attribute27,
p_attribute28,
p_attribute29,
p_attribute30,
p_validate,
p_effective_date,
p_party_id,
p_validity,
p_object_version_number,
p_phone_id
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'The phone number Not Loaded For '
|| 'employee number - '
|| i.employee_code
);
v_status := 'E';
errm := SQLERRM || SQLCODE || errm;
END;
-- ROLLBACK;
END IF;
DBMS_OUTPUT.put_line ('v_status -->' || v_status);
IF (v_status != 'S')
THEN
errm := errm || '-' || i.employee_code || '-' || i.phone_type;
END IF;
DBMS_OUTPUT.put_line ('errm -->' || errm);
UPDATE xx_phone
SET status = v_status,
err_msg = errm
WHERE employee_code = i.employee_code
AND phone_type = i.phone_type
AND phone_number = i.phone_number
AND date_from = i.date_from;
COMMIT;
END LOOP;
END;
No comments:
Post a Comment