[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| kumma 21-OCT-2002 Added one more parameter for disp_action_info to the Igs_Pe_Felony_Dtls_Pkg.insert_row
|| and update_row in PROCEDURE crt_pe_felony_dtls , #2608360
|| npalanis 30_OCT-2002 Bug : 2608360
|| Trunc function added to crime date and nvl added for
|| disp_action_info in prc_pe_felony_dtls.
|| gmaheswa 1-Nov-2004 Bug : 3770362 removed code related to the effective dates(start_date , end_date)of housing status as they are obsoleted
|| pkpatel 29-Nov-204 Bug : 3770362 In the Load Cal validation of Housing status modified to TRUNC of sysdate
|| (reverse chronological order - newest change first)
|| skpandey 08-JUL-2005 Bug : 4327807
|| Added a condition in exception section of crt_pe_felony_dtls after calling igs_pe_felony_dtls_pkg.update_row
|| and igs_pe_felony_dtls_pkg.insert_row to set status and error code
*/
--
-- Starts procedure PRC_PE_HOUSE_STATUS
--
l_interface_run_id NUMBER;
SELECT ai.*, i.person_id
FROM igs_pe_housing_int ai, igs_ad_interface_all i
WHERE ai.interface_id = i.interface_id AND
ai.status = '2' AND
i.interface_run_id = cp_interface_run_id AND
ai.interface_run_id = cp_interface_run_id;
SELECT p.rowid,p.* -- selecting all fields of the interface table...
FROM igs_pe_teach_periods_all p
WHERE p.person_id = cp_person_id AND
p.cal_type = cp_cal_type AND
p.sequence_number = cp_sequence_number;
igs_pe_teach_periods_pkg.insert_row (
x_rowid => l_rowid,
x_teaching_period_id => l_teaching_period_id,
x_person_id => p_housing_rec.person_id,
x_teach_period_resid_stat_cd => p_housing_rec.teach_period_resid_stat_cd,
x_cal_type => p_housing_rec.cal_type,
x_sequence_number => p_housing_rec.sequence_number,
x_mode => 'R',
x_org_id => l_org_id
);
UPDATE igs_pe_housing_int
SET status = '1',
error_code = l_error_code
WHERE interface_housing_id = p_housing_rec.interface_housing_id;
l_error_code := 'E109'; -- Person Housing Status Insertion Failed
UPDATE igs_pe_housing_int
SET status = '3',
error_code = l_error_code
WHERE interface_housing_id = p_housing_rec.interface_housing_id;
-- Local procedure to update a record in the OSS table.
PROCEDURE upd_pe_house_status(p_dup_rec IN dup_chk_housing_cur%ROWTYPE,
p_housing_rec IN housing_cur%ROWTYPE)
/*
|| Created By : gmaheswa
|| Created On : 2/11/2004
|| Purpose : Local procedure to update an existing housing record.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| gmaheswa 2/11/2004 Created
*/
AS
BEGIN
igs_pe_teach_periods_pkg.update_row (
x_rowid => p_dup_rec.rowid,
x_teaching_period_id => p_dup_rec.teaching_period_id,
x_person_id => NVL(p_dup_rec.person_id,housing_rec.person_id),
x_teach_period_resid_stat_cd => NVL(p_housing_rec.teach_period_resid_stat_cd,p_dup_rec.teach_period_resid_stat_cd),
x_cal_type => NVL(p_dup_rec.cal_type,housing_rec.cal_type),
x_sequence_number => NVL(p_dup_rec.sequence_number,housing_rec.sequence_number),
x_mode => 'R'
);
UPDATE igs_pe_housing_int
SET status = '1',
error_code = NULL,
match_ind = '18' -- '18' Match occured and used import values
WHERE interface_housing_id = p_housing_rec.interface_housing_id;
UPDATE igs_pe_housing_int
SET status = '3',
error_code = 'E114'
WHERE interface_housing_id = p_housing_rec.interface_housing_id;
SELECT 'X'
FROM igs_en_su_attempt_all sa
WHERE sa.person_id = c_person_id AND
sa.unit_attempt_status IN ('ENROLLED','UNCONFIRM') AND
sa.cal_type = c_cal_type AND
sa.ci_sequence_number = c_seq_number;
SELECT 'X'
FROM IGS_CA_INST_ALL CA,
IGS_CA_TYPE TYP,
IGS_CA_STAT STAT
WHERE
TYP.CAL_TYPE = CA.CAL_TYPE AND
TYP.S_CAL_CAT = 'LOAD' AND
CA.END_DT >= TRUNC(SYSDATE) AND
CA.CAL_STATUS = STAT.CAL_STATUS AND
STAT.S_CAL_STATUS = 'ACTIVE' AND
CA.CAL_TYPE = c_cal_type AND
CA.SEQUENCE_NUMBER = c_seq_number;
UPDATE igs_pe_housing_int
SET status = '3',
error_code = l_error_code
WHERE interface_housing_id = p_housing_rec.interface_housing_id;
UPDATE igs_pe_housing_int phi
SET status = '3',
error_code = 'E695'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
phi.match_ind IS NOT NULL;
UPDATE igs_pe_housing_int phi
SET status = '1',
match_ind = '19'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
EXISTS
(SELECT 1
FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai
WHERE phi.interface_id = ai.interface_id AND
ai.interface_run_id = l_interface_run_id AND
ai.person_id = pi.person_id AND
UPPER(phi.cal_type) = pi.cal_type AND
phi.sequence_number = pi.sequence_number);
UPDATE igs_pe_housing_int phi
SET status = '1'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
phi.match_ind IN ('18','19','22','23');
UPDATE igs_pe_housing_int phi
SET status = '3', error_code = 'E695'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
(phi.match_ind IS NOT NULL AND phi.match_ind NOT IN ('21','25'));
UPDATE igs_pe_housing_int phi
SET status = '1', match_ind = '23'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
phi.match_ind IS NULL AND
EXISTS
(SELECT 1
FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai
WHERE phi.interface_id = ai.interface_id AND
ai.interface_run_id = l_interface_run_id AND
pi.person_id = ai.person_id AND
pi.TEACH_PERIOD_RESID_STAT_CD = UPPER(phi.TEACH_PERIOD_RESID_STAT_CD) AND
pi.cal_type = UPPER(phi.cal_type) AND
pi.sequence_number = phi.sequence_number
) ;
UPDATE igs_pe_housing_int phi
SET status = 3,
match_ind = 20,
dup_teaching_period_id = (SELECT pi.teaching_period_id
FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai
WHERE ai.interface_id = phi.interface_id AND
ai.interface_run_id = l_interface_run_id AND
ai.person_id = pi.person_id AND
UPPER(phi.cal_type) = pi.cal_type AND
phi.sequence_number = pi.sequence_number AND
ROWNUM < 2)
WHERE phi.status='2' AND
phi.interface_run_id = l_interface_run_id AND
phi.match_ind IS NULL AND
EXISTS
(SELECT 1
FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai
WHERE ai.interface_run_id = l_interface_run_id AND
ai.interface_id = phi.interface_id AND
ai.person_id = pi.person_id AND
UPPER(phi.cal_type) = pi.cal_type AND
phi.sequence_number = pi.sequence_number
);
SELECT ai.*, i.person_id
FROM igs_pe_flny_dtl_int ai,
igs_ad_interface_all i
WHERE ai.interface_id = i.interface_id
AND ai.status = '2'
AND ai.interface_run_id=cp_interface_run_id
AND i.interface_run_id = cp_interface_run_id;
SELECT pf.rowid,pf.* -- select all the feilds from the OSS table to avoid opening the cursor below.
FROM igs_pe_felony_dtls pf
WHERE person_id = felony_dtls_rec.person_id
AND UPPER(crime_nature) = UPPER(felony_dtls_rec.crime_nature)
AND TRUNC(crime_date) = TRUNC(felony_dtls_rec.crime_date);
UPDATE igs_pe_flny_dtl_int
SET status = '3',
error_code = l_error_code
WHERE interface_felony_dtls_id = p_felony_dtls_cur.interface_felony_dtls_id;
-- kumma, added one more parameter to the Igs_Pe_Felony_Dtls_Pkg.insert_row, #2608360
PROCEDURE crt_pe_felony_dtls( p_felony_dtls_rec felony_dtls_cur%ROWTYPE,
p_status OUT NOCOPY VARCHAR2,
p_error_code OUT NOCOPY VARCHAR2) AS
l_rowid VARCHAR2(25);
Igs_Pe_Felony_Dtls_Pkg.insert_row (
x_rowid => l_rowid ,
x_felony_details_id => l_felony_dtls_id,
x_person_id => p_felony_dtls_rec.person_id,
x_crime_nature => p_felony_dtls_rec.crime_nature,
x_crime_date => p_felony_dtls_rec.crime_date,
x_convict_ind => p_felony_dtls_rec.convict_ind,
x_disp_action_info => p_felony_dtls_rec.disp_action_info,
x_mode => 'R');
ELSIF l_message_name = 'IGS_PE_SS_FLNY_CANT_INSERT' THEN
p_status :='3';
UPDATE igs_pe_flny_dtl_int pfi
SET status = '3',
error_code = 'E695'
WHERE pfi.status = '2' AND
pfi.interface_run_id = l_interface_run_id AND
pfi.match_ind IS NOT NULL;
UPDATE igs_pe_flny_dtl_int pfi
SET status = '1',
match_ind = '19'
WHERE pfi.status = '2' AND
pfi.interface_run_id = l_interface_run_id AND
pfi.match_ind IS NULL AND
EXISTS (SELECT 1
FROM igs_pe_felony_dtls pi,
igs_ad_interface_all aii
WHERE pfi.interface_id = aii.interface_id
AND aii.interface_run_id = l_interface_run_id
AND aii.person_id = pi.person_id
AND UPPER(pfi.crime_nature) = UPPER(pi.crime_nature)
AND TRUNC(pfi.crime_date) = TRUNC(pi.crime_date));
UPDATE igs_pe_flny_dtl_int pfi
SET status = 1
WHERE pfi.status = '2' AND
pfi.interface_run_id = l_interface_run_id AND
pfi.match_ind IN ('18','19','22','23');
UPDATE igs_pe_flny_dtl_int pfi
SET status = 3,
error_code = 'E695'
WHERE pfi.status = '2' AND
pfi.interface_run_id = l_interface_run_id AND
(pfi.match_ind IS NOT NULL AND pfi.match_ind NOT IN ('21','25'));
UPDATE igs_pe_flny_dtl_int pfi
SET status = '1',
match_ind = '23'
WHERE pfi.status = '2' AND
pfi.interface_run_id = l_interface_run_id AND
pfi.match_ind IS NULL AND
EXISTS
(SELECT 1
FROM igs_pe_felony_dtls pi,
igs_ad_interface_all aii
WHERE pfi.interface_id = aii.interface_id
AND aii.interface_run_id = l_interface_run_id
AND NVL(aii.person_id,-99)= NVL(pi.person_id,-99)
AND UPPER(pfi.crime_nature) = UPPER(pi.crime_nature)
AND TRUNC(pfi.crime_date) = TRUNC(pi.crime_date)
AND UPPER(pfi.convict_ind) = UPPER(pi.convict_ind)
AND NVL(UPPER(pfi.disp_action_info),'*!*') = NVL(UPPER(pi.disp_action_info),'*!*')
);
UPDATE igs_pe_flny_dtl_int pfi
SET status='3',
match_ind='20',
dup_felony_details_id=(SELECT pi.FELONY_DETAILS_ID
FROM igs_pe_felony_dtls pi,
igs_ad_interface_all aii
WHERE pfi.interface_id = aii.interface_id
AND aii.interface_run_id = l_interface_run_id
AND aii.person_id = pi.person_id
AND UPPER(pfi.crime_nature) = UPPER(pi.crime_nature)
AND TRUNC(pfi.crime_date) = TRUNC(pi.crime_date))
WHERE pfi.status='2' AND
pfi.interface_run_id = l_interface_run_id AND
pfi.match_ind IS NULL AND
EXISTS
(SELECT 1
FROM igs_pe_felony_dtls pi,
igs_ad_interface_all aii
WHERE pfi.interface_id = aii.interface_id
AND aii.interface_run_id = l_interface_run_id
AND aii.person_id = pi.person_id
AND UPPER(pfi.crime_nature) = UPPER(pi.crime_nature)
AND TRUNC(pfi.crime_date) = TRUNC(pi.crime_date));
igs_pe_felony_dtls_pkg.update_row(
x_rowid => dup_chk_rec.rowid,
x_felony_details_id=> dup_chk_rec.felony_details_id,
x_person_id =>NVL( felony_dtls_rec.person_id,dup_chk_rec.person_id),
x_crime_nature => dup_chk_rec.crime_nature,
x_crime_date => dup_chk_rec.crime_date,
x_convict_ind => felony_dtls_rec.convict_ind,
x_disp_action_info => nvl(felony_dtls_rec.disp_action_info,dup_chk_rec.disp_action_info),
x_mode => 'R'
);
UPDATE igs_pe_flny_dtl_int
SET status = '1',
error_code=NULL,
match_ind='18'
WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
UPDATE igs_pe_flny_dtl_int
SET status = l_status ,
error_code = l_error_code
WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
igs_pe_felony_dtls_pkg.update_row(
x_rowid => dup_chk_rec.rowid,
x_felony_details_id=> dup_chk_rec.felony_details_id,
x_person_id =>NVL( dup_chk_rec.person_id,dup_chk_rec.person_id),
x_crime_nature =>felony_dtls_rec.crime_nature,
x_crime_date => felony_dtls_rec.crime_date,
x_convict_ind => felony_dtls_rec.convict_ind,
x_disp_action_info => NVL(felony_dtls_rec.disp_action_info,dup_chk_rec.disp_action_info),
x_mode => 'R'
);
UPDATE igs_pe_flny_dtl_int
SET status = '1',
match_ind='18',
error_code=NULL
WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
UPDATE igs_pe_flny_dtl_int
SET status = l_status ,
error_code = l_error_code
WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
UPDATE igs_pe_flny_dtl_int
SET status = l_status,
error_code = l_error_code
WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
SELECT ai.*, i.person_id
FROM igs_pe_hear_dtl_int ai,
igs_ad_interface_all i
WHERE ai.interface_id = i.interface_id
AND ai.status = '2'
AND ai.interface_run_id=cp_interface_run_id
AND i.interface_run_id = cp_interface_run_id;
SELECT pd.rowid,pd.* --
FROM igs_pe_hearing_dtls pd
WHERE person_id = hearing_dtls_rec.person_id
AND UPPER(description) = UPPER(hearing_dtls_rec.description)
AND NVL(TRUNC(start_date),l_default_date)= NVL(TRUNC(hearing_dtls_rec.start_date),l_default_date);
UPDATE igs_pe_hear_dtl_int
SET status = '3',
error_code = l_error_code
WHERE interface_hearing_dtls_id = p_hearing_dtls_cur.interface_hearing_dtls_id;
Igs_Pe_Hearing_Dtls_Pkg.insert_row (
x_rowid => l_rowid ,
x_hearing_details_id=> l_hearing_details_id,
x_person_id => p_hearing_dtls_rec.person_id,
x_description => p_hearing_dtls_rec.description,
x_start_date => p_hearing_dtls_rec.start_date,
x_end_date => p_hearing_dtls_rec.end_date,
x_dspl_file_ind => p_hearing_dtls_rec.dspl_file_ind,
x_acad_dism_ind => p_hearing_dtls_rec.acad_dism_ind,
x_non_acad_dism_ind => p_hearing_dtls_rec.non_acad_dism_ind,
x_mode => 'R'
);
UPDATE igs_pe_hear_dtl_int pdi
SET status = '3',
error_code = 'E695'
WHERE pdi.status = '2' AND
pdi.interface_run_id = l_interface_run_id AND
pdi.match_ind IS NOT NULL;
UPDATE igs_pe_hear_dtl_int pdi
SET status = '1',
match_ind = '19'
WHERE pdi.status = '2' AND
pdi.interface_run_id = l_interface_run_id AND
EXISTS (SELECT 1
FROM igs_pe_hearing_dtls pi,
igs_ad_interface_all aiii
WHERE pdi.interface_id = aiii.interface_id
AND aiii.interface_run_id = l_interface_run_id
AND aiii.person_id = pi.person_id
AND UPPER(pdi.description) = UPPER(pi.description)
AND NVL(TRUNC(pdi.start_date),l_default_date) = NVL(TRUNC(pi.start_date),l_default_date)
);
UPDATE igs_pe_hear_dtl_int pdi
SET status = '1'
WHERE pdi.status = '2' AND
pdi.interface_run_id = l_interface_run_id AND
pdi.match_ind IN ('18','19','22','23');
UPDATE igs_pe_hear_dtl_int pdi
SET status = '3',
error_code = 'E695'
WHERE pdi.status = '2' AND
pdi.interface_run_id = l_interface_run_id AND
(pdi.match_ind IS NOT NULL AND pdi.match_ind NOT IN ('21','25'));
UPDATE igs_pe_hear_dtl_int pdi
SET status = '1',
match_ind = '23'
WHERE pdi.status = '2' AND
pdi.interface_run_id = l_interface_run_id AND
pdi.match_ind IS NULL AND
EXISTS (SELECT 1
FROM igs_pe_hearing_dtls pi,
igs_ad_interface_all aiii
WHERE NVL(pi.person_id,-99) = NVL(aiii.person_id,-99)
AND pdi.interface_id = aiii.interface_id
AND aiii.interface_run_id = l_interface_run_id
AND UPPER(pi.description) = UPPER(pdi.description)
AND NVL(TRUNC(pi.start_date),l_default_date)= NVL(TRUNC(pdi.start_date),l_default_date)
AND NVL(TRUNC(pi.end_date),l_default_date) = NVL(TRUNC(pdi.end_date),l_default_date)
AND UPPER(pi.dspl_file_ind) = UPPER(pdi.dspl_file_ind)
AND UPPER(pi.acad_dism_ind) = UPPER(pdi.acad_dism_ind)
AND UPPER(pi.non_acad_dism_ind) = UPPER(pdi.non_acad_dism_ind));
UPDATE igs_pe_hear_dtl_int pdi
SET status = '3',
match_ind = '20',
dup_hearing_details_id=(SELECT pi.hearing_details_id
FROM igs_pe_hearing_dtls pi,
igs_ad_interface_all aiii
WHERE pdi.interface_id = aiii.interface_id
AND aiii.interface_run_id = l_interface_run_id
AND aiii.person_id = pi.person_id
AND UPPER(pdi.description) = UPPER(pi.description)
AND NVL(TRUNC(pdi.start_date),l_default_date)= NVL(TRUNC(pi.start_date),l_default_date)
AND ROWNUM = 1)
WHERE
pdi.status='2' AND
pdi.interface_run_id = l_interface_run_id AND
pdi.match_ind IS NULL AND
EXISTS (SELECT 1
FROM igs_pe_hearing_dtls pi,
igs_ad_interface_all aiii
WHERE pdi.interface_id = aiii.interface_id
AND aiii.interface_run_id = l_interface_run_id
AND aiii.person_id = pi.person_id
AND UPPER(pdi.description) = UPPER(pi.description)
AND NVL(TRUNC(pdi.start_date),l_default_date)
= NVL(TRUNC(pi.start_date),l_default_date));
igs_pe_hearing_dtls_pkg.update_row(
x_rowid => dup_chk_rec.rowid,
x_hearing_details_id=>dup_chk_rec.hearing_details_id,
x_person_id => NVL(hearing_dtls_rec.person_id,dup_chk_rec.person_id),
x_description => hearing_dtls_rec.description,
x_start_date =>NVL( hearing_dtls_rec.start_date,dup_chk_rec.start_date),
x_end_date => NVL(hearing_dtls_rec.end_date,dup_chk_rec.end_date),
x_dspl_file_ind => hearing_dtls_rec.dspl_file_ind,
x_acad_dism_ind => hearing_dtls_rec.acad_dism_ind,
x_non_acad_dism_ind => hearing_dtls_rec.non_acad_dism_ind,
x_mode => 'R'
);
UPDATE igs_pe_hear_dtl_int
SET status = '1',
error_code=NULL,
match_ind='18'
WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
UPDATE igs_pe_hear_dtl_int
SET status = l_status,
error_code = l_error_code
WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
igs_pe_hearing_dtls_pkg.update_row(
x_rowid => dup_chk_rec.rowid,
x_hearing_details_id=>dup_chk_rec.hearing_details_id,
x_person_id => NVL(hearing_dtls_rec.person_id,dup_chk_rec.person_id),
x_description => hearing_dtls_rec.description,
x_start_date =>NVL( hearing_dtls_rec.start_date,dup_chk_rec.start_date),
x_end_date => NVL(hearing_dtls_rec.end_date,dup_chk_rec.end_date),
x_dspl_file_ind => hearing_dtls_rec.dspl_file_ind,
x_acad_dism_ind => hearing_dtls_rec.acad_dism_ind,
x_non_acad_dism_ind => hearing_dtls_rec.non_acad_dism_ind,
x_mode => 'R'
);
UPDATE igs_pe_hear_dtl_int
SET status = '1',
match_ind = '18',
error_code = NULL
WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
UPDATE igs_pe_hear_dtl_int
SET status = l_status,
error_code = l_error_code
WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
UPDATE igs_pe_hear_dtl_int
SET status = l_status,
error_code = l_error_code
WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
SELECT ai.*,i.person_id
FROM igs_pe_race_int ai, igs_ad_interface_all i
WHERE ai.interface_id = i.interface_id AND
ai.status = '2' AND
ai.interface_run_id=cp_interface_run_id AND
i.interface_run_id = cp_interface_run_id;
SELECT pr.race_cd
FROM igs_pe_race pr
WHERE pr.person_id = cp_person_id AND
pr.race_cd = cp_race_cd;
igs_pe_race_pkg.insert_row (
x_rowid => l_rowid,
x_person_id => p_race_rec.person_id,
x_race_cd => p_race_rec.race_cd,
x_mode => 'R'
);
UPDATE igs_pe_race_int
SET status = '1',
error_code = NULL
WHERE interface_race_id = p_race_rec.interface_race_id;
-- Person Race Insertion Failed
UPDATE igs_pe_race_int
SET status = '3',
error_code = 'E322'
WHERE interface_race_id = p_race_rec.interface_race_id;
UPDATE igs_pe_race_int
SET status = '3',
error_code = l_error_code
WHERE interface_race_id = p_race_rec.interface_race_id;
UPDATE igs_pe_race_int pri
SET status='1',
match_ind='18'
WHERE pri.status='2'
AND pri.interface_run_id = l_interface_run_id
AND EXISTS
(SELECT 1
FROM igs_pe_race pr,
igs_ad_interface_all ai
WHERE pri.interface_id=ai.interface_id
AND ai.interface_run_id = l_interface_run_id
AND pr.person_id=ai.person_id
AND UPPER(pri.race_cd)=UPPER(pr.race_cd)
);
-- the interface table donot get inserted into the OSS table
l_dup_race_cd := NULL;
UPDATE igs_pe_race_int
SET status = '1',
match_ind = '18'
WHERE interface_race_id = race_rec.interface_race_id;
SELECT ai.*, i.person_id
FROM igs_pe_privacy_int ai,
igs_ad_interface_all i
WHERE ai.interface_id = i.interface_id AND
ai.status = '2' AND
i.interface_run_id = cp_interface_run_id AND
ai.interface_run_id = cp_interface_run_id;
SELECT p.rowid, p.*
FROM igs_pe_priv_level p
WHERE p.person_id = cp_person_id AND
p.data_group = cp_data_group AND
p.start_date = cp_start_dt;
-- Local procedure crt_pe_priv_dtls for inserting new records in the OSS table.
PROCEDURE crt_pe_priv_dtls(p_priv_rec IN privacy_cur%ROWTYPE,
p_grp_id IN igs_pe_priv_level.data_group_id%TYPE)
/*
|| Created By : nsidana
|| Created On : 9/7/2004
|| Purpose : Local procedure to create a new privacy record.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| nsidana 9/7/2004 Created
*/
AS
l_rowid VARCHAR2(25);
SAVEPOINT before_insert;
igs_pe_priv_level_pkg.insert_row(
x_rowid => l_rowid,
x_privacy_level_id => l_privacy_level_id,
x_person_id => p_priv_rec.person_id,
x_data_group => p_priv_rec.data_group,
x_data_group_id => p_grp_id,
x_lvl => null,
x_action => p_priv_rec.action_code,
x_whom => p_priv_rec.to_whom_code,
x_ref_notes_id => null,
x_start_date => p_priv_rec.start_date,
x_end_date => p_priv_rec.end_date,
x_mode => 'R'
);
UPDATE igs_pe_privacy_int
SET status = '1',
error_code = l_error_code
WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
ROLLBACK TO before_insert;
l_error_code := 'E322'; -- Person privacy details record insertion failed.
UPDATE igs_pe_privacy_int
SET status = '3',
error_code = l_error_code
WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
|| Purpose : Local procedure to update an existing privacy record.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| nsidana 9/7/2004 Created
*/
AS
BEGIN
SAVEPOINT before_update;
igs_pe_priv_level_pkg.update_row (x_rowid => p_oss_rec.rowid,
x_privacy_level_id => p_oss_rec.privacy_level_id,
x_person_id => p_oss_rec.person_id,
x_data_group => p_oss_rec.data_group,
x_data_group_id => p_oss_rec.data_group_id,
x_lvl => p_oss_rec.lvl,
x_action => p_int_rec.action_code,
x_whom => p_int_rec.to_whom_code,
x_ref_notes_id => p_oss_rec.ref_notes_id,
x_start_date => p_oss_rec.start_date,
x_end_date => NVL(p_int_rec.end_date,p_oss_rec.end_date),
x_mode => 'R'
);
UPDATE igs_pe_privacy_int
SET status = l_status,
error_code = l_error_code,
match_ind = '18' -- '18' Match occured and used import values
WHERE interface_privacy_id = p_int_rec.interface_privacy_id;
ROLLBACK TO before_update;
UPDATE igs_pe_privacy_int
SET status = '3',
error_code = l_error_code
WHERE interface_id = p_int_rec.interface_privacy_id;
SELECT lvl, data_group_id
FROM igs_pe_data_groups
WHERE data_group = cp_data_group AND
closed_ind = 'N';
SELECT lvl, data_group_id
FROM igs_pe_data_groups
WHERE data_group = cp_data_group;
SELECT 1
FROM FND_LOOKUP_VALUES L,
HZ_RELATIONSHIPS R
WHERE L.LOOKUP_CODE = R.RELATIONSHIP_CODE AND
L.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE' AND
L.LANGUAGE = USERENV('LANG') AND
L.VIEW_APPLICATION_ID = 222 AND
L.SECURITY_GROUP_ID = 0 AND
R.STATUS ='A' AND
R.RELATIONSHIP_CODE = cp_to_whom_code AND
R.SUBJECT_ID = cp_person_id;
--1.) Check a valid data group. Consider closed ones as invalid for Insert mode and valid for update mode.
IF ( p_mode = 'I') THEN
OPEN chk_data_group_for_ins(p_priv_rec.data_group);
--4.) Need to handle the following in the EXCPETION section of insert_row and update_row. These will be caught in the exception secion of the insert_row and update_row calls.
--E406 : IGS_PE_FROM_DT_GRT_TO_DATE :Start date not greater than end date.
--E352 : IGS_FI_ST_NOT_LT_CURRDT : Start date not less than current date.
--E228 : IGS_PE_PRIV_DT_OVERLAP : Overlap validation.
RETURN TRUE;
UPDATE igs_pe_privacy_int
SET status = '3',
error_code = l_error_code
WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
UPDATE igs_pe_privacy_int phi
SET status = '3',
error_code = 'E695'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
phi.match_ind IS NOT NULL;
-- 2 . If rule is E and the match ind is null, we update the interface table for all duplicate records with status 1 and match ind 19.
IF (l_rule = 'E') THEN
UPDATE igs_pe_privacy_int phi
SET status = '1',
match_ind = '19'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
EXISTS
(SELECT 1
FROM igs_pe_priv_level pi, igs_ad_interface_all ai
WHERE phi.interface_id = ai.interface_id AND
ai.interface_run_id = l_interface_run_id AND
ai.person_id = pi.person_id AND
pi.data_group = UPPER(phi.data_group) AND
TRUNC(phi.start_date) = pi.start_date);
-- 3. If rule is R and the record status is 18,19,22,23 these records have been processed, but didn't get updated. Update them to 1
IF (l_rule='R') THEN
UPDATE igs_pe_privacy_int phi
SET status = '1'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
phi.match_ind IN ('18','19','22','23');
-- 4. If rule is R and the match ind is not null and is neither 21 nor 25, update it to errored record.
IF (l_rule = 'R') THEN
UPDATE igs_pe_privacy_int phi
SET status = '3', error_code = 'E695'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
(phi.match_ind IS NOT NULL AND phi.match_ind NOT IN ('21','25'));
-- 5. If rule = 'R' and there is no discprepency in duplicate records, update them to status 1 and match ind 23.
IF (l_rule ='R') THEN
UPDATE igs_pe_privacy_int phi
SET status = '1', match_ind = '23'
WHERE phi.status = '2' AND
phi.interface_run_id = l_interface_run_id AND
phi.match_ind IS NULL AND
EXISTS
(SELECT 1
FROM igs_pe_priv_level pi, igs_ad_interface_all ai
WHERE phi.interface_id = ai.interface_id AND
ai.interface_run_id = l_interface_run_id AND
pi.person_id = ai.person_id AND
pi.data_group = UPPER(phi.data_group) AND
pi.action = UPPER(phi.action_code) AND
pi.whom = UPPER(phi.to_whom_code) AND
pi.start_date = TRUNC(phi.start_date) AND
NVL(TRUNC(pi.end_date), l_default_date) = NVL(TRUNC(phi.end_date),l_default_date)
) ;
-- 6. If rule is R and there are still some records, they are the ones for which there is some discrepency existing. Update them to status 3
-- and value from the OSS table.
IF (l_rule ='R') THEN
UPDATE igs_pe_privacy_int phi
SET status = 3,
match_ind = 20,
dup_privacy_level_id = (SELECT pi.privacy_level_id
FROM igs_pe_priv_level pi, igs_ad_interface_all ai
WHERE ai.interface_id = phi.interface_id AND
ai.interface_run_id = l_interface_run_id AND
ai.person_id = pi.person_id AND
UPPER(phi.data_group) = pi.data_group AND
TRUNC(phi.start_date) = pi.start_date)
WHERE phi.status='2' AND
phi.interface_run_id = l_interface_run_id AND
phi.match_ind IS NULL AND
EXISTS
(SELECT 1
FROM igs_pe_priv_level pi, igs_ad_interface_all ai
WHERE ai.interface_run_id = l_interface_run_id AND
ai.interface_id = phi.interface_id AND
ai.person_id = pi.person_id AND
UPPER(phi.data_group) = pi.data_group AND
TRUNC(phi.start_date) = pi.start_date
);
END IF; -- end for validate for update
-- validate and insert new.
IF validate_record(privacy_cur_rec,'I',l_grp_id) THEN
crt_pe_priv_dtls(p_priv_rec => privacy_cur_rec, p_grp_id => l_grp_id);