[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select rowid from IGS_EN_SPLACE_SUPS where Splacement_id=c_splacement_id
and supervisor_id=c_supervisor_id;
Cursor cur_c1(c_person_id IN NUMBER, c_emp_hist_id IN NUMBER) IS SELECT * FROM IGS_AD_EMP_DTL
WHERE person_id=c_person_id
AND employment_history_id=c_emp_hist_id;
Cursor cur_c2 IS SELECT *
FROM IGS_PE_CONTACTS_V
WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
AND OWNER_TABLE_ID = P_PERSON_ID
AND CONTACT_POINT_ID = P_PHONE_ID
AND CONTACT_POINT_TYPE = 'PHONE'
FOR UPDATE NOWAIT;
Cursor cur_c3 IS SELECT *
FROM IGS_PE_CONTACTS_V
WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
AND OWNER_TABLE_ID = P_PERSON_ID
AND CONTACT_POINT_ID = P_EMAIL_ID
AND CONTACT_POINT_TYPE = 'EMAIL'
FOR UPDATE NOWAIT;
Cursor cur_c4(c_person_id IN NUMBER) IS SELECT party_number
FROM HZ_PARTIES
WHERE HZ_PARTIES.PARTY_ID=c_person_id;
l_last_update_date HZ_CONTACT_POINTS.LAST_UPDATE_DATE%TYPE :=NULL;
v_phone_update_rec cur_c2%ROWTYPE;
v_email_update_rec cur_c3%ROWTYPE;
IGS_PE_PERSON_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_MSG_DATA => L_MSG_DATA,
X_MSG_COUNT => L_MSG_COUNT,
X_RETURN_STATUS => L_RETURN_STATUS,
X_PERSON_ID => L_PERSON_ID,
X_PERSON_NUMBER => L_PERSON_NUMBER,
X_SURNAME => P_LAST_NAME,
X_MIDDLE_NAME => NULL,
X_GIVEN_NAMES => P_FIRST_NAME,
X_SEX => NULL,
X_TITLE => NULL,
X_STAFF_MEMBER_IND => NULL,
X_DECEASED_IND => NULL,
X_SUFFIX => NULL,
X_PRE_NAME_ADJUNCT => NULL,
X_ARCHIVE_EXCLUSION_IND=> NULL,
X_ARCHIVE_DT => NULL,
X_PURGE_EXCLUSION_IND => NULL,
X_PURGE_DT => NULL,
X_DECEASED_DATE => NULL,
X_PROOF_OF_INS => NULL,
X_PROOF_OF_IMMU => NULL,
X_BIRTH_DT => NULL,
X_SALUTATION => NULL,
X_ORACLE_USERNAME => NULL,
X_PREFERRED_GIVEN_NAME => NULL,
X_EMAIL_ADDR => NULL,
X_LEVEL_OF_QUAL_ID => NULL,
X_MILITARY_SERVICE_REG => NULL,
X_VETERAN => NULL,
X_HZ_PARTIES_OVN => l_hz_parties_ovn,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_PERSON_ID_TYPE => NULL,
X_API_PERSON_ID => NULL,
X_MODE => 'S',
X_ATTRIBUTE21 => NULL,
X_ATTRIBUTE22 => NULL,
X_ATTRIBUTE23 => NULL,
X_ATTRIBUTE24 => NULL);
P_ACTION => 'INSERT',
P_ROWID => l_rowid,
P_STATUS => 'A',
P_OWNER_TABLE_NAME => 'HZ_PARTIES',
P_OWNER_TABLE_ID => L_PERSON_ID,
P_PRIMARY_FLAG => 'Y',
P_PHONE_COUNTRY_CODE => NULL,
P_PHONE_AREA_CODE => NULL,
P_PHONE_NUMBER => P_PHONE,
P_PHONE_EXTENSION => NULL,
P_PHONE_LINE_TYPE => 'GEN',
P_RETURN_STATUS => l_return_status,
P_MSG_DATA => l_msg_data,
P_LAST_UPDATE_DATE => l_last_update_date,
P_CONTACT_POINT_ID => L_CONTACT_POINT_ID,
P_CONTACT_POINT_OVN => L_CONTACT_POINT_OVN,
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);
ELSE -- if phone id is not null, it is an existing record, so update
--Select all the details of the PHONE record using a cursor based on the PERSON_ID, OBJECT VERSION NUMBER AND PHONE_ID
OPEN cur_c2;
FETCH cur_c2 INTO v_phone_update_rec;
IF v_phone_update_rec.OBJECT_VERSION_NUMBER <> P_PHONE_OVN THEN
-- the record was updated by some other user hence the object version number is not matching.
CLOSE cur_c2;
FND_MESSAGE.SET_NAME ('FND',' FORM_RECORD_DELETED');
IF cur_c2%FOUND AND (nvl(v_phone_update_rec.phone_number,'null')<>nvl(p_phone,'null')) THEN
IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKP (
P_ACTION =>'UPDATE',
P_ROWID => V_PHONE_UPDATE_REC.ROW_ID,
P_STATUS => 'A',
P_OWNER_TABLE_NAME => 'HZ_PARTIES',
P_OWNER_TABLE_ID => P_PERSON_ID,
P_PRIMARY_FLAG => 'Y',
P_PHONE_COUNTRY_CODE => V_PHONE_UPDATE_REC.PHONE_COUNTRY_CODE,
P_PHONE_AREA_CODE => V_PHONE_UPDATE_REC.PHONE_AREA_CODE,
P_PHONE_NUMBER => P_PHONE,
P_PHONE_EXTENSION => V_PHONE_UPDATE_REC.PHONE_EXTENSION,
P_PHONE_LINE_TYPE => V_PHONE_UPDATE_REC.PHONE_LINE_TYPE,
P_RETURN_STATUS => l_return_status,
P_MSG_DATA => l_msg_data,
P_LAST_UPDATE_DATE => V_PHONE_UPDATE_REC.LAST_UPDATE_DATE,
P_CONTACT_POINT_ID => l_phone_id,
P_CONTACT_POINT_OVN => l_phone_ovn,
P_ATTRIBUTE_CATEGORY=> V_PHONE_UPDATE_REC. ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => V_PHONE_UPDATE_REC.ATTRIBUTE1,
P_ATTRIBUTE2 => V_PHONE_UPDATE_REC.ATTRIBUTE2,
P_ATTRIBUTE3 => V_PHONE_UPDATE_REC.ATTRIBUTE3,
P_ATTRIBUTE4 => V_PHONE_UPDATE_REC.ATTRIBUTE4,
P_ATTRIBUTE5 => V_PHONE_UPDATE_REC.ATTRIBUTE5,
P_ATTRIBUTE6 => V_PHONE_UPDATE_REC.ATTRIBUTE6,
P_ATTRIBUTE7 => V_PHONE_UPDATE_REC.ATTRIBUTE7,
P_ATTRIBUTE8 => V_PHONE_UPDATE_REC.ATTRIBUTE8,
P_ATTRIBUTE9 => V_PHONE_UPDATE_REC.ATTRIBUTE9,
P_ATTRIBUTE10 => V_PHONE_UPDATE_REC.ATTRIBUTE10,
P_ATTRIBUTE12 => V_PHONE_UPDATE_REC.ATTRIBUTE12,
P_ATTRIBUTE13 => V_PHONE_UPDATE_REC.ATTRIBUTE13,
P_ATTRIBUTE14 => V_PHONE_UPDATE_REC.ATTRIBUTE14,
P_ATTRIBUTE15 => V_PHONE_UPDATE_REC.ATTRIBUTE15,
P_ATTRIBUTE16 => V_PHONE_UPDATE_REC.ATTRIBUTE16,
P_ATTRIBUTE17 => V_PHONE_UPDATE_REC.ATTRIBUTE17,
P_ATTRIBUTE18 => V_PHONE_UPDATE_REC.ATTRIBUTE18,
P_ATTRIBUTE19 => V_PHONE_UPDATE_REC.ATTRIBUTE19,
P_ATTRIBUTE20 => V_PHONE_UPDATE_REC.ATTRIBUTE20);
P_ACTION =>'INSERT',
P_ROWID => l_rowid,
P_STATUS => 'A',
P_OWNER_TABLE_NAME => 'HZ_PARTIES',
P_OWNER_TABLE_ID => L_PERSON_ID,
P_PRIMARY_FLAG => 'Y',
P_EMAIL_FORMAT => 'MAILTEXT',
P_EMAIL_ADDRESS => P_EMAIL_ADDRESS,
P_RETURN_STATUS => l_return_status,
P_MSG_DATA => l_msg_data,
P_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
P_CONTACT_POINT_ID => l_email_id,
P_CONTACT_POINT_OVN => L_CONTACT_POINT_OVN,
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);
ELSE --if email id is not null, existing record - so update email details
--Select all the details of the email record using a cursor based on the PERSON_ID, OBJECT VERSION NUMBER AND EMAIL_ID
OPEN cur_c3;
FETCH cur_c3 INTO v_email_update_rec;
IF V_EMAIL_UPDATE_REC.OBJECT_VERSION_NUMBER <> l_EMAIL_OVN THEN
-- the record was updated by some other user hence the object version number is not matching.
CLOSE cur_c3;
FND_MESSAGE.SET_NAME('FND',' FORM_RECORD_DELETED');
IF cur_c3%FOUND AND (nvl(v_email_update_rec.email_address,'null')<>nvl(l_email_address,'null')) THEN
IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKE(
P_ACTION => 'UPDATE',
P_ROWID => V_EMAIL_UPDATE_REC.ROW_ID,
P_STATUS => 'A',
P_OWNER_TABLE_NAME => 'HZ_PARTIES',
P_OWNER_TABLE_ID => P_PERSON_ID,
P_PRIMARY_FLAG => 'Y',
P_EMAIL_FORMAT => V_EMAIL_UPDATE_REC.EMAIL_FORMAT,
P_EMAIL_ADDRESS => l_EMAIL_ADDRESS,
P_RETURN_STATUS => l_RETURN_STATUS,
P_MSG_DATA => l_MSG_DATA,
P_LAST_UPDATE_DATE => V_EMAIL_UPDATE_REC.LAST_UPDATE_DATE,
P_CONTACT_POINT_ID => L_EMAIL_ID,
P_CONTACT_POINT_OVN => L_EMAIL_OVN,
P_ATTRIBUTE_CATEGORY=> V_EMAIL_UPDATE_REC.ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => V_EMAIL_UPDATE_REC.ATTRIBUTE1,
P_ATTRIBUTE2 => V_EMAIL_UPDATE_REC.ATTRIBUTE2,
P_ATTRIBUTE3 => V_EMAIL_UPDATE_REC.ATTRIBUTE3,
P_ATTRIBUTE4 => V_EMAIL_UPDATE_REC.ATTRIBUTE4,
P_ATTRIBUTE5 => V_EMAIL_UPDATE_REC.ATTRIBUTE5,
P_ATTRIBUTE6 => V_EMAIL_UPDATE_REC.ATTRIBUTE6,
P_ATTRIBUTE7 => V_EMAIL_UPDATE_REC.ATTRIBUTE7,
P_ATTRIBUTE8 => V_EMAIL_UPDATE_REC.ATTRIBUTE8,
P_ATTRIBUTE9 => V_EMAIL_UPDATE_REC.ATTRIBUTE9,
P_ATTRIBUTE10 => V_EMAIL_UPDATE_REC.ATTRIBUTE10,
P_ATTRIBUTE12 => V_EMAIL_UPDATE_REC.ATTRIBUTE12,
P_ATTRIBUTE13 => V_EMAIL_UPDATE_REC.ATTRIBUTE13,
P_ATTRIBUTE14 => V_EMAIL_UPDATE_REC.ATTRIBUTE14,
P_ATTRIBUTE15 => V_EMAIL_UPDATE_REC.ATTRIBUTE15,
P_ATTRIBUTE16 => V_EMAIL_UPDATE_REC.ATTRIBUTE16,
P_ATTRIBUTE17 => V_EMAIL_UPDATE_REC.ATTRIBUTE17,
P_ATTRIBUTE18 => V_EMAIL_UPDATE_REC.ATTRIBUTE18,
P_ATTRIBUTE19 => V_EMAIL_UPDATE_REC.ATTRIBUTE19,
P_ATTRIBUTE20 => V_EMAIL_UPDATE_REC.ATTRIBUTE20);
IGS_AD_EMP_DTL_PKG.UPDATE_ROW (
X_ROWID => V_EMP_HIST_REC.ROW_ID,
X_EMPLOYMENT_HISTORY_ID => V_EMP_HIST_REC.EMPLOYMENT_HISTORY_ID,
X_PERSON_ID => l_PERSON_ID,
X_START_DT => V_EMP_HIST_REC.START_DT,
X_END_DT => V_EMP_HIST_REC.END_DT,
X_TYPE_OF_EMPLOYMENT => V_EMP_HIST_REC.TYPE_OF_EMPLOYMENT,
X_FRACTION_OF_EMPLOYMENT => V_EMP_HIST_REC.FRACTION_OF_EMPLOYMENT,
X_TENURE_OF_EMPLOYMENT => V_EMP_HIST_REC.TENURE_OF_EMPLOYMENT,
X_POSITION => p_title,
X_OCCUPATIONAL_TITLE_CODE => V_EMP_HIST_REC.OCCUPATIONAL_TITLE_CODE,
X_OCCUPATIONAL_TITLE => V_EMP_HIST_REC.OCCUPATIONAL_TITLE,
X_WEEKLY_WORK_HOURS => V_EMP_HIST_REC.WEEKLY_WORK_HOURS,
X_COMMENTS => V_EMP_HIST_REC.COMMENTS,
X_EMPLOYER => V_EMP_HIST_REC.EMPLOYER,
X_EMPLOYED_BY_DIVISION_NAME => V_EMP_HIST_REC.EMPLOYED_BY_DIVISION_NAME,
X_BRANCH => V_EMP_HIST_REC.BRANCH,
X_MILITARY_RANK => V_EMP_HIST_REC.MILITARY_RANK,
X_SERVED => V_EMP_HIST_REC.SERVED,
X_STATION => V_EMP_HIST_REC.STATION,
X_CONTACT => V_EMP_HIST_REC.CONTACT,
X_MSG_DATA => L_MSG_DATA,
X_RETURN_STATUS => L_RETURN_STATUS,
X_OBJECT_VERSION_NUMBER => p_empl_ovn,
X_EMPLOYED_BY_PARTY_ID => V_EMP_HIST_REC.EMPLOYED_BY_PARTY_ID,
X_REASON_FOR_LEAVING => V_EMP_HIST_REC.REASON_FOR_LEAVING
);
IGS_AD_EMP_DTL_PKG.INSERT_ROW (
X_ROWID => L_ROWID,
X_EMPLOYMENT_HISTORY_ID => l_EMPLOYMENT_HISTORY_ID,
X_PERSON_ID => l_PERSON_ID,
X_START_DT => SYSDATE,
X_END_DT => NULL,
X_TYPE_OF_EMPLOYMENT => NULL,
X_FRACTION_OF_EMPLOYMENT => NULL,
X_TENURE_OF_EMPLOYMENT => NULL,
X_POSITION => P_TITLE,
X_OCCUPATIONAL_TITLE_CODE => NULL,
X_OCCUPATIONAL_TITLE => NULL,
X_WEEKLY_WORK_HOURS => NULL,
X_COMMENTS => NULL,
X_EMPLOYER => NULL,
X_EMPLOYED_BY_DIVISION_NAME => NULL,
X_BRANCH => NULL,
X_MILITARY_RANK => NULL,
X_SERVED => NULL,
X_STATION => NULL,
X_CONTACT => NULL,
X_MSG_DATA => L_MSG_DATA,
X_RETURN_STATUS => L_RETURN_STATUS,
X_OBJECT_VERSION_NUMBER => L_EMP_OBJ_VER_NO,
X_EMPLOYED_BY_PARTY_ID => NULL,
X_REASON_FOR_LEAVING => NULL
);
Cursor cur_s1 IS Select IGS_EN_Splacements_S.nextVal from dual;
PROCEDURE delete_supervisor_info(p_splacement_id IN NUMBER, p_supervisor_id IN NUMBER)
IS
/*--------------------------------------------------------------
-- rvangala 01-NOV-2003 Created
-- Deletes supervisor from IGS_EN_SPLACE_SUPS
--
--
----------------------------------------------------------------
*/
l_row_id VARCHAR2(30);
igs_en_splace_sups_pkg.DELETE_ROW(l_row_id);
END delete_supervisor_info;
igs_en_splace_sups_pkg.INSERT_ROW(
x_rowid => l_rowid,
x_splacement_id => p_splacement_id,
x_supervisor_id => l_person_id,
x_mode => l_mode);