[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| Added the Procedure update_org. Modified orgp_upd_ins_ou_sts AND
|| orgp_upd_ou_sts to propagate the STATUS through Organizational Structure.
|| ssawhney 11-jun-2002 BUG : 2408794
|| ORGP_GET_WITHIN_OU, c_our cursor added check for LOGICAL_DELETE_DT IS NULL;
|| mmkumar 18-Jul-2005 Party_Number impact, inside update_org , modified cursor hz_parties_cur,
|| igs_or_unit_hist_pkg.insert_row call and call to igs_pe_hz_parties_pkg.update_row
*/
PROCEDURE orgp_del_instn_hist(
p_institution_cd IN VARCHAR2 )
IS
v_other_detail VARCHAR(255);
DELETE
FROM IGS_OR_INST_HIST
WHERE institution_cd = p_institution_cd;
DELETE
FROM IGS_OR_UNIT_HIST
WHERE org_unit_cd = p_org_unit_cd
AND ou_start_dt = p_start_dt;
SELECT lot.s_location_type
FROM IGS_AD_LOCATION_TYPE lot,
IGS_AD_LOCATION loc
WHERE lot.location_type = loc.location_type AND
loc.location_cd = p_location_cd;
SELECT s_location_type
FROM IGS_AD_LOCATION_TYPE
WHERE location_type = p_location_type;
SELECT our.parent_org_unit_cd,
our.parent_start_dt
FROM IGS_OR_UNIT_REL our
WHERE our.child_org_unit_cd = cp_org_unit_cd and
our.child_start_dt = cp_start_dt and
our.logical_delete_dt IS NULL ; -- new validation as part of bug 2408794
SELECT name
INTO v_name
FROM IGS_OR_INSTITUTION
WHERE institution_cd = p_institution_cd;
-- Insert the IGS_OR_UNIT_HIST record.
INSERT INTO IGS_OR_UNIT_HIST
(org_unit_cd,
ou_start_dt,
hist_start_dt,
hist_end_dt,
hist_who,
ou_end_dt,
description,
org_status,
org_type,
member_type,
institution_cd,
name,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES ( p_org_unit_cd,
p_ou_start_dt,
p_hist_start_dt,
p_hist_end_dt,
p_hist_who,
p_ou_end_dt,
p_description,
p_org_status,
p_org_type,
p_member_type,
p_institution_cd,
v_name,
1,
SYSDATE,
1,
SYSDATE);
|| Purpose : This procedure updates the INST record while status is made ACTIVE to INACTIVE
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
v_complete BOOLEAN;
SELECT IGS_OR_UNIT.org_unit_cd,
IGS_OR_UNIT.start_dt
FROM IGS_OR_UNIT,
IGS_OR_STATUS
WHERE IGS_OR_UNIT.institution_cd = p_institution_cd
AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
AND IGS_OR_STATUS.s_org_status = 'ACTIVE'
ORDER BY IGS_OR_UNIT.institution_cd;
SELECT parent_org_unit_cd,
parent_start_dt
FROM IGS_OR_UNIT_REL,
IGS_OR_UNIT,
IGS_OR_STATUS
WHERE child_org_unit_cd = cp_org_unit_cd
AND child_start_dt = cp_start_dt
AND logical_delete_dt IS NULL
AND org_unit_cd = parent_org_unit_cd
AND start_dt = parent_start_dt
AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
v_update_ou BOOLEAN;
SELECT IGS_OR_UNIT_REL.child_org_unit_cd,
IGS_OR_UNIT_REL.child_start_dt,
IGS_OR_UNIT.institution_cd
FROM IGS_OR_UNIT_REL,
IGS_OR_UNIT,
IGS_OR_STATUS
WHERE IGS_OR_UNIT_REL.parent_org_unit_cd = p_org_unit_cd
AND IGS_OR_UNIT_REL.parent_start_dt = p_start_dt
AND IGS_OR_UNIT_REL.logical_delete_dt IS NULL
AND IGS_OR_UNIT.org_unit_cd = IGS_OR_UNIT_REL.child_org_unit_cd
AND IGS_OR_UNIT.start_dt = IGS_OR_UNIT_REL.child_start_dt
AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
SELECT parent_org_unit_cd,
parent_start_dt
FROM IGS_OR_UNIT_REL,
IGS_OR_UNIT,
IGS_OR_STATUS
WHERE child_org_unit_cd = cp_child_org_unit_cd
AND child_start_dt = cp_child_start_dt
AND NOT (
parent_org_unit_cd = cp_parent_org_unit_cd AND
parent_start_dt = cp_parent_start_dt)
AND logical_delete_dt IS NULL
AND org_unit_cd = parent_org_unit_cd
AND start_dt = parent_start_dt
AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
-- Default v_update_ou to true so that if no children exist
-- then possible to update the current OU.
v_update_ou := TRUE;
-- have a cursor to select all active children(Org Unit attached with the Parent Org Unit)
FOR our_child IN c_our_child LOOP
-- Validate if child has other active parents.
FOR our_ou_os_parent IN c_our_ou_os_parent (
p_org_unit_cd,
p_start_dt,
our_child.child_org_unit_cd,
our_child.child_start_dt) LOOP
v_other_active_parent := TRUE;
-- If child has other active parent then don't update.
IF v_other_active_parent = TRUE THEN
v_update_ou := FALSE;
-- indicates that the current OU is not to be updated as
-- it has active children.
-- set a flag to indicate this.
v_update_ou := FALSE;
-- check the results of processing as to whether to update current Org Unit
IF v_update_ou AND
(p_institution_cd = p_org_instn_cd) THEN
-- If no active children, and
-- IGS_OR_INSTITUTION codes match then update the org IGS_OR_UNIT
-- and return true to indicate to the calling parent
-- that there is no active child.
IF TRUNC(SYSDATE) >= p_start_dt THEN
-- Check whether the Org Unit is already processed. If it is already processed once
-- then do not update it again
FOR l_count IN 1..l_index LOOP
IF temp_table(l_count) = p_org_unit_cd THEN
l_check := l_check +1;
igs_or_gen_001.update_org(p_org_unit_cd,
p_new_org_status,
TRUNC(SYSDATE));
-- Select all active org units with the IGS_OR_INSTITUTION whose status has changed to inactive.
SAVEPOINT do_propagation;
|| Purpose : This procedure updates the ORG record while status is made ACTIVE to INACTIVE
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
v_complete BOOLEAN;
SELECT child_org_unit_cd,
child_start_dt
FROM IGS_OR_UNIT_REL,
IGS_OR_UNIT,
IGS_OR_STATUS
WHERE parent_org_unit_cd = p_org_unit_cd
AND parent_start_dt = p_start_dt
AND logical_delete_dt IS NULL
AND org_unit_cd = child_org_unit_cd
AND start_dt = child_start_dt
AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
SELECT parent_org_unit_cd,
parent_start_dt
FROM IGS_OR_UNIT_REL,
IGS_OR_UNIT,
IGS_OR_STATUS
WHERE child_org_unit_cd = cp_child_org_unit_cd
AND child_start_dt = cp_child_start_dt
AND NOT (
parent_org_unit_cd = cp_parent_org_unit_cd AND
parent_start_dt = cp_parent_start_dt)
AND logical_delete_dt IS NULL
AND org_unit_cd = parent_org_unit_cd
AND start_dt = parent_start_dt
AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
v_update_ou BOOLEAN;
v_update_ou := TRUE;
-- If child has other active parent then don't update.
IF v_other_active_parent = TRUE THEN
v_update_ou := FALSE;
v_update_ou := FALSE;
IF v_update_ou = TRUE THEN
IF p_end_dt >= p_start_dt THEN
-- The Updation of the Most Parent Org Unit should be prevented. Th Updation this Org Unit should
-- happen in the Form.
IF g_org_unit_cd <> p_org_unit_cd THEN
igs_or_gen_001.update_org(p_org_unit_cd,
p_org_status,
p_end_dt);
PROCEDURE update_org(p_org_unit_cd hz_parties.party_number%TYPE,
p_org_status igs_pe_hz_parties.ou_org_status%TYPE,
p_end_date igs_pe_hz_parties.ou_end_dt%TYPE)
IS
/*
|| Created By : pkpatel
|| Created On : 10-DEC-2001
|| Purpose : This procedure updates the ORG record and creates its history.
|| The TCA API is being called to refresh the last_update_date. So that the History would be created properly
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| skpandey 27-SEP-2005 Bug: 3663505
|| Description: Added ATTRIBUTES 21 TO 24 to store additional information in IGS_OR_GEN_012_PKG call
|| pkpatel 25-OCT-2002 Bug No: 2613704
|| Modified signature of igs_pe_hz_parties_pkg to refer inst_priority_cd instead of inst_priority_code_id
|| mmkumar 18-Jul-2005 Party_Number impact, modified cursor hz_parties_cur, igs_or_unit_hist_pkg.insert_row call
|| and call to igs_pe_hz_parties_pkg.update_row
|| (reverse chronological order - newest change first)
*/
l_return_status VARCHAR2(1);
SELECT hp.*, ihp.oss_org_unit_cd
FROM hz_parties hp, igs_pe_hz_parties ihp
WHERE ihp.oss_org_unit_cd = p_org_unit_cd and
ihp.party_id = hp.party_id;
SELECT rowid, pe.*
FROM igs_pe_hz_parties pe
WHERE party_id = cp_party_id
FOR UPDATE OF ou_end_dt, ou_org_status NOWAIT;
-- This call is made so that the last update date is refreshed and the History would show the proper Start and End Date.
igs_or_gen_012_pkg.update_organization (
p_party_id => hz_parties_rec.party_id,
p_institution_cd => hz_parties_rec.party_number,
p_name => hz_parties_rec.party_name,
p_status => 'A',
p_last_update => hz_parties_rec.last_update_date,
p_attribute_category => hz_parties_rec.attribute_category,
p_attribute1 => hz_parties_rec.attribute1,
p_attribute2 => hz_parties_rec.attribute2,
p_attribute3 => hz_parties_rec.attribute3,
p_attribute4 => hz_parties_rec.attribute4,
p_attribute5 => hz_parties_rec.attribute5,
p_attribute6 => hz_parties_rec.attribute6,
p_attribute7 => hz_parties_rec.attribute7,
p_attribute8 => hz_parties_rec.attribute8,
p_attribute9 => hz_parties_rec.attribute9,
p_attribute10 => hz_parties_rec.attribute10,
p_attribute11 => hz_parties_rec.attribute11,
p_attribute12 => hz_parties_rec.attribute12,
p_attribute13 => hz_parties_rec.attribute13,
p_attribute14 => hz_parties_rec.attribute14,
p_attribute15 => hz_parties_rec.attribute15,
p_attribute16 => hz_parties_rec.attribute16,
p_attribute17 => hz_parties_rec.attribute17,
p_attribute18 => hz_parties_rec.attribute18,
p_attribute19 => hz_parties_rec.attribute19,
p_attribute20 => hz_parties_rec.attribute20,
p_return_status => l_return_status,
p_msg_data => l_msg_data,
p_object_version_number => hz_parties_rec.object_version_number,
p_attribute21 => hz_parties_rec.attribute21,
p_attribute22 => hz_parties_rec.attribute22,
p_attribute23 => hz_parties_rec.attribute23,
p_attribute24 => hz_parties_rec.attribute24
) ;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
igs_pe_hz_parties_pkg.update_row (
x_mode => 'R',
x_rowid => igs_org_rec.rowid,
x_party_id => igs_org_rec.party_id,
x_deceased_ind => igs_org_rec.deceased_ind,
x_archive_exclusion_ind => igs_org_rec.archive_exclusion_ind,
x_archive_dt => igs_org_rec.archive_dt,
x_purge_exclusion_ind => igs_org_rec.purge_exclusion_ind,
x_purge_dt => igs_org_rec.purge_dt,
x_oracle_username => igs_org_rec.oracle_username,
x_proof_of_ins => igs_org_rec.proof_of_ins,
x_proof_of_immu => igs_org_rec.proof_of_immu,
x_level_of_qual => igs_org_rec.level_of_qual,
x_military_service_reg => igs_org_rec.military_service_reg,
x_veteran => igs_org_rec.veteran,
x_institution_cd => igs_org_rec.institution_cd,
x_oi_local_institution_ind => igs_org_rec.oi_local_institution_ind,
x_oi_os_ind => igs_org_rec.oi_os_ind,
x_oi_govt_institution_cd => igs_org_rec.oi_govt_institution_cd,
x_oi_inst_control_type => igs_org_rec.oi_inst_control_type,
x_oi_institution_type => igs_org_rec.oi_institution_type,
x_oi_institution_status => igs_org_rec.oi_institution_status,
x_ou_start_dt => igs_org_rec.ou_start_dt,
x_ou_end_dt => p_end_date,
x_ou_member_type => igs_org_rec.ou_member_type,
x_ou_org_status => p_org_status,
x_ou_org_type => igs_org_rec.ou_org_type,
x_inst_org_ind => igs_org_rec.inst_org_ind,
x_inst_priority_cd => igs_org_rec.inst_priority_cd,
x_inst_eps_code => igs_org_rec.inst_eps_code,
x_inst_phone_country_code => igs_org_rec.inst_phone_country_code,
x_inst_phone_area_code => igs_org_rec.inst_phone_area_code,
x_inst_phone_number => igs_org_rec.inst_phone_number,
x_adv_studies_classes => igs_org_rec.adv_studies_classes,
x_honors_classes => igs_org_rec.honors_classes,
x_class_size => igs_org_rec.class_size,
x_sec_school_location_id => igs_org_rec.sec_school_location_id,
x_percent_plan_higher_edu => igs_org_rec.percent_plan_higher_edu,
x_fund_authorization => igs_org_rec.fund_authorization,
x_pe_info_verify_time => igs_org_rec.pe_info_verify_time,
x_birth_city => igs_org_rec.birth_city,
x_birth_country => igs_org_rec.birth_country,
x_oss_org_unit_cd => hz_parties_rec.oss_org_unit_cd --mmkumar , party_number impact
);
l_hist_start_dt := hz_parties_rec.last_update_date - 1/(60*24*60);
igs_or_unit_hist_pkg.insert_row (
X_ROWID => lv_rowid,
X_ORG_UNIT_CD => hz_parties_rec.oss_org_unit_cd,
X_OU_START_DT => igs_org_rec.ou_start_dt,
X_HIST_START_DT => l_hist_start_dt,
X_HIST_END_DT => l_hist_end_dt,
X_HIST_WHO => hz_parties_rec.last_updated_by,
X_OU_END_DT => igs_org_rec.ou_end_dt,
X_DESCRIPTION => hz_parties_rec.party_name,
X_ORG_STATUS => igs_org_rec.ou_org_status,
X_ORG_TYPE => igs_org_rec.ou_org_type,
X_MEMBER_TYPE => igs_org_rec.ou_member_type,
X_INSTITUTION_CD => igs_org_rec.institution_cd,
X_NAME => NULL,
X_MODE => 'R' ,
X_ORG_ID => l_org_id
);
END update_org;