[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pathipat 18-JUL-2002 Validation for Date Overlap included before Updation also (previously present for Insertion only)
npalanis 16-JUN-2002 Bug - 2409967
Level of interest lookup type not present in fnd_lookup_values
Level of interest validation removed
npalanis 14-JUN-2002 Bug - 2409967
the cursor check is put inside the check for error code.
gmaheswa 10-NOV-2003 Bug - 3223043 HZ.K impact changes
gmaheswa 15-DEC-2003 Bug 3316838 Removed code related to date overlap under same employer or employer party number.
pkpatel 23-Feb-2006 Bug 4937960 (Used the table HZ_EMPLOYMENT_HISTORY directly instead of the view IGS_AD_EMP_DTL)
skpandey 16-May-2006 Bug - 5205911 added comments column to IGS_AD_EMP_INT_ALL
*/
--1
cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
SELECT hii.*, i.person_id
FROM igs_ad_alias_int_all hii, igs_ad_interface_all i
WHERE hii.interface_run_id = cp_interface_run_id
AND i.interface_id = hii.interface_id
AND i.interface_run_id = cp_interface_run_id
AND hii.status = '2';
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id= cp_person_id;
UPDATE igs_ad_alias_int_all
SET STATUS = '3',
ERROR_CODE = l_error_code
WHERE interface_alias_id = person_alias_rec.interface_alias_id;
UPDATE IGS_AD_ALIAS_INT_ALL
SET STATUS = '3',
ERROR_CODE = l_error_code
WHERE INTERFACE_ALIAS_ID = PERSON_ALIAS_REC.INTERFACE_ALIAS_ID;
UPDATE IGS_AD_ALIAS_INT_ALL
SET STATUS = '3',
ERROR_CODE = l_error_code
WHERE INTERFACE_ALIAS_ID = PERSON_ALIAS_REC.INTERFACE_ALIAS_ID;
SELECT IGS_PE_PERSON_ALIAS_SEQ_NUM_S.NEXTVAL FROM dual;
Igs_Pe_Person_Alias_Pkg.INSERT_ROW (
X_ROWID => l_rowid,
X_PERSON_ID => PERSON_ALIAS_REC.PERSON_ID,
X_ALIAS_TYPE => PERSON_ALIAS_REC.ALIAS_TYPE,
-- X_SEQUENCE_NUMBER => NULL,--PERSON_ALIAS_REC.SEQUENCE_NUMBER,--commented by nshee
X_SEQUENCE_NUMBER => l_person_alias_seq_number,
X_TITLE => PERSON_ALIAS_REC.TITLE,
X_ALIAS_COMMENT => PERSON_ALIAS_REC.ALIAS_COMMENT,
X_START_DT => PERSON_ALIAS_REC.START_DT,
X_END_DT => PERSON_ALIAS_REC.END_DT,
X_SURNAME => PERSON_ALIAS_REC.SURNAME,
X_GIVEN_NAMES => PERSON_ALIAS_REC.GIVEN_NAMES,
X_MODE => 'R'
);
UPDATE IGS_AD_ALIAS_INT_ALL
SET STATUS = '1',
ERROR_CODE = NULL
WHERE INTERFACE_ALIAS_ID = person_alias_rec.INTERFACE_ALIAS_ID;
UPDATE IGS_AD_ALIAS_INT_ALL
SET ERROR_CODE = 'E322',
STATUS = '3'
WHERE INTERFACE_ALIAS_ID = person_alias_rec.INTERFACE_ALIAS_ID;
SELECT hii.*, i.person_id
FROM igs_ad_emp_int_all hii, igs_ad_interface_all i
WHERE hii.interface_run_id = cp_interface_run_id
AND i.interface_id = hii.interface_id
AND i.interface_run_id = cp_interface_run_id
AND hii.status = '2';
SELECT 'Y'
FROM igs_ps_dic_occ_titls
WHERE occupational_title_code = cp_occ_t_code;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT PARTY_ID
FROM HZ_PARTIES
WHERE party_type = 'ORGANIZATION' AND
party_number = cp_employer_party_number AND
status <> 'M';
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '3'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
UPDATE IGS_AD_EMP_INT_ALL
SET Error_Code = p_error_code,
Status = '1'
WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
l_last_update_date DATE;
Igs_Ad_Emp_Dtl_Pkg.INSERT_ROW (
X_ROWID => l_RowId,
x_employment_history_id => l_Employment_History_Id,
x_PERSON_ID => PERSON_EMP_REC.person_id,
x_START_DT => PERSON_EMP_REC.Start_Date,
x_END_DT => PERSON_EMP_REC.End_Date,
x_TYPE_OF_EMPLOYMENT => PERSON_EMP_REC.Type_Of_Employment,
x_FRACTION_OF_EMPLOYMENT => PERSON_EMP_REC.Fraction_Of_Employment,
x_TENURE_OF_EMPLOYMENT => PERSON_EMP_REC.Tenure_Of_Employment,
x_POSITION => PERSON_EMP_REC.Position,
x_OCCUPATIONAL_TITLE_CODE => PERSON_EMP_REC.OCCUPATIONAL_TITLE_CODE,
x_OCCUPATIONAL_TITLE => NULL, --PERSON_EMP_REC.TITLE,
x_WEEKLY_WORK_HOURS => PERSON_EMP_REC.WEEKLY_WORK_HRS,
x_COMMENTS => PERSON_EMP_REC.Comments,
x_EMPLOYER => PERSON_EMP_REC.Employer,
x_EMPLOYED_BY_DIVISION_NAME => PERSON_EMP_REC.Employed_by_division_name,
x_BRANCH => null,
x_MILITARY_RANK => null,
x_SERVED => null,
x_STATION => null,
x_CONTACT => PERSON_EMP_REC.Contact, --Bug : 2037512
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_object_version_number => l_object_version_number,
x_employed_by_party_id => l_Employer_party_id,
x_reason_for_leaving => PERSON_EMP_REC.Reason_for_leaving,
X_MODE => 'R'
);
UPDATE IGS_AD_EMP_INT_all
SET status = '3',
error_code = 'E322'
WHERE INTERFACE_EMP_ID = PERSON_EMP_REC.INTERFACE_EMP_ID;
UPDATE IGS_AD_EMP_INT_all
SET status = '1'
WHERE INTERFACE_EMP_ID = PERSON_EMP_REC.INTERFACE_EMP_ID;
UPDATE igs_ad_emp_int_all
SET status = '3',
ERROR_CODE = 'E695' -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND interface_run_id = l_interface_run_id
AND status = '2';
UPDATE igs_ad_emp_int_all mi
SET status = '1',
match_ind = '19'
WHERE mi.interface_run_id = l_interface_run_id
AND mi.status = '2'
AND EXISTS ( SELECT '1'
FROM hz_employment_history pe, igs_ad_interface_all ii, hz_parties hz
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND pe.employed_by_party_id = hz.party_id(+)
AND ii.person_id = pe.party_id
AND (( NVL(UPPER(mi.employer),'*!') = NVL(UPPER(pe.employed_by_name_company),'*'))
OR (NVL(mi.employer_party_number,'*!') = NVL(hz.party_number,'*')))
AND pe.begin_date = TRUNC(mi.start_date) );
UPDATE igs_ad_emp_int_all
SET status = '1'
WHERE interface_run_id = l_interface_run_id
AND match_ind IN ('18','19','22','23')
AND status='2';
UPDATE igs_ad_emp_int_all
SET status = '3',
ERROR_CODE = 'E695'
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN ('21','25'))
AND status='2';
UPDATE igs_ad_emp_int_all mi
SET status = '1',
match_ind = '23'
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS ( SELECT '1'
FROM hz_employment_history pe, igs_ad_interface_all ii, igs_ad_hz_emp_dtl ahed, hz_parties hz
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.party_id
AND pe.employment_history_id = ahed.employment_history_id (+)
AND pe.employed_by_party_id = hz.party_id (+)
AND ((NVL(UPPER(mi.employer),'*!') = NVL(UPPER(pe.employed_by_name_company),'*'))
OR (NVL(hz.party_number,'*!') = NVL(mi.employer_party_number,'*')))
AND pe.begin_date = TRUNC(mi.start_date)
AND NVL(pe.end_date,igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
AND NVL(pe.supervisor_name,'*') = NVL(mi.contact,'*')
AND NVL(pe.employment_type_code,'*') = NVL(UPPER(mi.type_of_employment),'*')
AND NVL(pe.fraction_of_tenure,0) = NVL(mi.fraction_of_employment,0)
AND NVL(pe.tenure_code,'*') = NVL(UPPER(mi.tenure_of_employment),'*')
AND NVL(pe.employed_as_title,'*') = NVL(mi.position,'*')
AND NVL(ahed.occupational_title_code,'*') = NVL(mi.occupational_title_code,'*')
AND NVL(pe.weekly_work_hours,0) = NVL(mi.weekly_work_hrs,0)
AND NVL(pe.employed_by_division_name,'*') = NVL(mi.employed_by_division_name,'*')
AND NVL(pe.reason_for_leaving,'*') = NVL(mi.reason_for_leaving,'*')
AND NVL(pe.comments,'*') = NVL(mi.comments,'*')
);
UPDATE igs_ad_emp_int_all mi
SET status = '3',
match_ind = '20',
dup_employment_number = (SELECT employment_history_id
FROM hz_employment_history pe, igs_ad_interface_all ii, hz_parties hz
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.party_id
AND pe.employed_by_party_id = hz.party_id (+)
AND ((NVL(UPPER(mi.employer),'*!') = NVL(UPPER(pe.employed_by_name_company),'*'))
OR (NVL(mi.employer_party_number,'*!') = NVL(hz.party_number,'*')))
AND pe.begin_date = TRUNC(mi.start_date)
AND ROWNUM = 1)
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS (SELECT '1'
FROM hz_employment_history pe, igs_ad_interface_all ii, hz_parties hz
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.party_id
AND pe.employed_by_party_id = hz.party_id (+)
AND ((NVL(UPPER(mi.employer),'*!') = NVL(UPPER(pe.employed_by_name_company),'*'))
OR (NVL(mi.employer_party_number,'*!') = NVL(hz.party_number,'*')))
AND pe.begin_date = TRUNC(mi.start_date));
SELECT heh.rowid row_id,
heh.employment_history_id,
heh.party_id person_id,
heh.begin_date start_dt,
heh.end_date end_dt,
heh.supervisor_name contact,
heh.employment_type_code type_of_employment,
heh.fraction_of_tenure fraction_of_employment,
heh.tenure_code tenure_of_employment,
heh.employed_as_title position,
ahed.occupational_title_code,
heh.weekly_work_hours,
heh.comments,
heh.employed_by_name_company employer,
heh.employed_by_division_name,
heh.branch,
heh.military_rank,
heh.served,
heh.station,
heh.object_version_number,
heh.employed_by_party_id,
heh.reason_for_leaving reason_for_leaving,
null occupational_title
FROM hz_employment_history heh, igs_ad_hz_emp_dtl ahed, hz_parties hz
WHERE heh.party_id = cp_person_id
AND heh.employment_history_id = ahed.employment_history_id (+)
AND heh.employed_by_party_id = hz.party_id (+)
AND ( NVL(UPPER(heh.employed_by_name_company),'!*!') = NVL(UPPER(cp_employer),'!*!')
OR
NVL(hz.party_number,'!*!') = NVL(cp_employer_party_number,'!*!'))
AND
TRUNC(heh.begin_date) = TRUNC(cp_start_date);
igs_ad_emp_dtl_pkg.update_row (
x_rowid => dup_emp_dtlsc_rec.row_id,
x_employment_history_id => dup_emp_dtlsc_rec.employment_history_id,
x_person_id => NVL(person_emp_rec.person_id,dup_emp_dtlsc_rec.person_id),
x_start_dt => NVL(person_emp_rec.start_date,dup_emp_dtlsc_rec.start_dt),
x_end_dt => NVL(person_emp_rec.end_date,dup_emp_dtlsc_rec.end_dt),
x_type_of_employment => NVL(person_emp_rec.type_of_employment,dup_emp_dtlsc_rec.type_of_employment),
x_fraction_of_employment => NVL(person_emp_rec.fraction_of_employment,dup_emp_dtlsc_rec.fraction_of_employment),
x_tenure_of_employment => NVL(person_emp_rec.tenure_of_employment,dup_emp_dtlsc_rec.tenure_of_employment),
x_position => NVL(person_emp_rec.position,dup_emp_dtlsc_rec.position),
x_occupational_title_code => NVL(person_emp_rec.occupational_title_code,dup_emp_dtlsc_rec.occupational_title_code),
x_occupational_title => dup_emp_dtlsc_rec.occupational_title,
x_weekly_work_hours => NVL(person_emp_rec.weekly_work_hrs,dup_emp_dtlsc_rec.weekly_work_hours),
x_comments => NVL(person_emp_rec.comments,dup_emp_dtlsc_rec.comments),
x_employer => NVL(person_emp_rec.employer,dup_emp_dtlsc_rec.employer),
x_employed_by_division_name => NVL(person_emp_rec.employed_by_division_name,dup_emp_dtlsc_rec.employed_by_division_name),
x_branch => NVL(person_emp_rec.branch,dup_emp_dtlsc_rec.branch),
x_military_rank => NVL(person_emp_rec.military_rank,dup_emp_dtlsc_rec.military_rank),
x_served => NVL(person_emp_rec.served,dup_emp_dtlsc_rec.served),
x_station => NVL(person_emp_rec.station,dup_emp_dtlsc_rec.station),
x_contact => NVL(person_emp_rec.contact,dup_emp_dtlsc_rec.contact), -- Bug : 2037512
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_object_version_number => dup_emp_dtlsc_rec.object_version_number,
x_employed_by_party_id => NVL(l_employer_party_id,dup_emp_dtlsc_rec.employed_by_party_id),
x_reason_for_leaving => NVL(person_emp_rec.reason_for_leaving,dup_emp_dtlsc_rec.reason_for_leaving),
x_mode => 'R'
);
UPDATE IGS_AD_EMP_INT_all
SET error_code = 'E014',
status = '3'
WHERE interface_emp_id = person_emp_rec.interface_emp_id;
UPDATE igs_ad_emp_int_all
SET match_ind = cst_mi_val_18 ,
STATUS = cst_stat_val_1, ERROR_CODE = NULL
WHERE interface_emp_id = person_emp_rec.interface_emp_id;
UPDATE igs_ad_emp_int_all
SET ERROR_CODE = 'E014',
STATUS = '3'
WHERE interface_emp_id = person_emp_rec.interface_emp_id;
igs_ad_emp_dtl_pkg.update_row (
x_rowid => dup_emp_dtlsc_rec.row_id,
x_employment_history_id => dup_emp_dtlsc_rec.employment_history_id,
x_person_id => NVL(person_emp_rec.person_id,dup_emp_dtlsc_rec.person_id),
x_start_dt => NVL(person_emp_rec.start_date,dup_emp_dtlsc_rec.start_dt),
x_end_dt => NVL(person_emp_rec.end_date,dup_emp_dtlsc_rec.end_dt),
x_type_of_employment => NVL(person_emp_rec.type_of_employment,dup_emp_dtlsc_rec.type_of_employment),
x_fraction_of_employment => NVL(person_emp_rec.fraction_of_employment,dup_emp_dtlsc_rec.fraction_of_employment),
x_tenure_of_employment => NVL(person_emp_rec.tenure_of_employment,dup_emp_dtlsc_rec.tenure_of_employment),
x_position => NVL(person_emp_rec.position,dup_emp_dtlsc_rec.position),
x_occupational_title_code => NVL(person_emp_rec.occupational_title_code,dup_emp_dtlsc_rec.occupational_title_code),
x_occupational_title => dup_emp_dtlsc_rec.occupational_title,
x_weekly_work_hours => NVL(person_emp_rec.weekly_work_hrs,dup_emp_dtlsc_rec.weekly_work_hours),
x_comments => NVL(person_emp_rec.comments,dup_emp_dtlsc_rec.comments),
x_employer => NVL(person_emp_rec.employer,dup_emp_dtlsc_rec.employer),
x_employed_by_division_name => NVL(person_emp_rec.employed_by_division_name,dup_emp_dtlsc_rec.employed_by_division_name),
x_branch => NVL(person_emp_rec.branch,dup_emp_dtlsc_rec.branch),
x_military_rank => NVL(person_emp_rec.military_rank,dup_emp_dtlsc_rec.military_rank),
x_served => NVL(person_emp_rec.served,dup_emp_dtlsc_rec.served),
x_station => NVL(person_emp_rec.station,dup_emp_dtlsc_rec.station),
x_contact => NVL(person_emp_rec.contact,dup_emp_dtlsc_rec.contact), ---Bug : 2037512
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_object_version_number => dup_emp_dtlsc_rec.object_version_number,
x_employed_by_party_id => NVL(l_employer_party_id,dup_emp_dtlsc_rec.employed_by_party_id),
x_reason_for_leaving => NVL(person_emp_rec.reason_for_leaving,dup_emp_dtlsc_rec.reason_for_leaving),
x_mode => 'R'
);
UPDATE IGS_AD_EMP_INT_all
SET error_code = 'E014',
status = '3'
WHERE interface_emp_id = person_emp_rec.interface_emp_id;
UPDATE igs_ad_emp_int_all
SET match_ind = cst_mi_val_18 ,
STATUS = cst_stat_val_1, ERROR_CODE = NULL
WHERE interface_emp_id = person_emp_rec.interface_emp_id;
l_last_update_date IGS_AD_EXTRACURR_ACT_V.LAST_UPDATE_DATE%TYPE;
SELECT hii.*, i.person_id
FROM igs_ad_excurr_int_all hii, igs_ad_interface_all i
WHERE hii.interface_run_id = cp_interface_run_id
AND i.interface_id = hii.interface_id
AND i.interface_run_id = cp_interface_run_id
AND hii.status = '2';
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
UPDATE igs_ad_excurr_int_all
SET STATUS = '1'
WHERE INTERFACE_EXCURR_ID = EXTRACURR_REC.INTERFACE_EXCURR_ID;
UPDATE igs_ad_excurr_int_all
SET STATUS = '3',
ERROR_CODE = p_error_code
WHERE INTERFACE_EXCURR_ID = EXTRACURR_REC.INTERFACE_EXCURR_ID;
Igs_Ad_Extracurr_Act_Pkg.Insert_Row(
x_rowid => l_RowId,
x_person_interest_id => l_Person_Interest_Id,
x_person_id => extracurr_rec.person_id,
x_interest_type_code => extracurr_rec.interest_type_code,
x_comments => extracurr_rec.comments,
x_start_date => EXTRACURR_REC.Start_Date,
x_end_date => EXTRACURR_REC.End_Date,
x_hours_per_week => EXTRACURR_REC.hours_per_week,
x_weeks_per_year => EXTRACURR_REC.weeks_per_year,
x_level_of_interest => EXTRACURR_REC.level_of_interest,
x_level_of_participation => EXTRACURR_REC.level_Of_Participation,
x_sport_indicator => EXTRACURR_REC.sport_indicator,
x_sub_interest_type_code => l_sub_interest_type_code,
x_interest_name => EXTRACURR_REC.Interest_name,
x_team => EXTRACURR_REC.team,
x_wh_update_date => NULL,
-- added Activity Source CD as part of ID prospective applicant part 2 of 1
X_ACTIVITY_SOURCE_CD => extracurr_rec.activity_source_cd,
x_last_update_date => l_last_update_date,
x_msg_Data=> l_msg_Data,
x_return_Status => l_return_status,
x_object_version_number => l_object_version_number,
x_mode => 'R');
UPDATE
igs_ad_excurr_int_all
SET
ERROR_CODE = 'E322',
STATUS = '3'
WHERE
INTERFACE_EXCURR_ID = extracurr_rec.INTERFACE_EXCURR_ID;
UPDATE
igs_ad_excurr_int_all
SET
STATUS = '1'
WHERE
INTERFACE_EXCURR_ID = extracurr_rec.INTERFACE_EXCURR_ID;
UPDATE igs_ad_excurr_int_all
SET STATUS = '3',
ERROR_CODE = 'E322'
WHERE INTERFACE_EXCURR_ID = EXTRACURR_REC.INTERFACE_EXCURR_ID;