[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_transfers_mgrh(p_start_object_id IN NUMBER,
p_end_object_id IN NUMBER) IS
BEGIN
-- Delete primary assignment changes
DELETE FROM hri_mdp_mgrh_transfers_ct mgrh_trn
WHERE mgrh_trn.rowid IN
(SELECT mgrh_trn2.rowid
FROM
hri_mdp_mgrh_transfers_ct mgrh_trn2
,hri_eq_sprvsr_hrchy_chgs eq
WHERE mgrh_trn2.asg_assgnmnt_fk = eq.assignment_id
AND eq.person_id BETWEEN p_start_object_id AND p_end_object_id
AND mgrh_trn2.time_day_evt_fk >= eq.erlst_evnt_effective_date);
DELETE FROM hri_mdp_mgrh_transfers_ct mgrh_trn
WHERE mgrh_trn.rowid IN
(SELECT mgrh_trn2.rowid
FROM
hri_mdp_mgrh_transfers_ct mgrh_trn2
,hri_eq_sprvsr_hrchy_chgs eq
WHERE mgrh_trn2.per_person_trn_fk = eq.person_id
AND eq.person_id BETWEEN p_start_object_id AND p_end_object_id
AND mgrh_trn2.time_day_evt_fk >= eq.erlst_evnt_effective_date);
END delete_transfers_mgrh;
PROCEDURE delete_transfers(p_start_object_id IN NUMBER,
p_end_object_id IN NUMBER) IS
BEGIN
DELETE FROM hri_mdp_mgrh_transfers_ct mgrh_trn
WHERE mgrh_trn.rowid IN
(SELECT mgrh_trn2.rowid
FROM
hri_mdp_mgrh_transfers_ct mgrh_trn2
,hri_eq_asgn_evnts eq
WHERE mgrh_trn2.asg_assgnmnt_fk = eq.assignment_id
AND eq.assignment_id BETWEEN p_start_object_id AND p_end_object_id
AND mgrh_trn2.time_day_evt_fk >= eq.erlst_evnt_effective_date
AND mgrh_trn2.sec_asg_ind IS NULL);
DELETE FROM hri_mdp_orgh_transfers_ct orgh_trn
WHERE orgh_trn.rowid IN
(SELECT orgh_trn2.rowid
FROM
hri_mdp_orgh_transfers_ct orgh_trn2
,hri_eq_asgn_evnts eq
WHERE orgh_trn2.asg_assgnmnt_fk = eq.assignment_id
AND eq.assignment_id BETWEEN p_start_object_id AND p_end_object_id
AND orgh_trn2.time_day_evt_fk >= eq.erlst_evnt_effective_date);
END delete_transfers;
PROCEDURE bulk_insert_transfers IS
l_user_id NUMBER;
INSERT INTO hri_mdp_mgrh_transfers_ct
(mgr_sup_person_fk
,mgr_sup_mngrsc_fk
,per_person_fk
,per_person_trn_fk
,asg_assgnmnt_fk
,ptyp_wrktyp_fk
,time_day_evt_fk
,transfer_in_ind
,transfer_out_ind
,direct_ind
,direct_record_ind
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_mgrh_sup_psn_id(i)
,g_mgrh_sup_sc_fk(i)
,g_mgrh_psn_id(i)
,g_mgrh_trn_id(i)
,g_mgrh_asg_id(i)
,g_mgrh_wty_fk(i)
,g_mgrh_date(i)
,g_mgrh_in_ind(i)
,g_mgrh_out_ind(i)
,g_mgrh_dir_ind(i)
,g_mgrh_dir_rec(i)
,l_current_time
,l_user_id
,l_user_id
,l_user_id
,l_current_time);
INSERT INTO hri_mdp_mgrh_transfers_ct
(mgr_sup_person_fk
,mgr_sup_mngrsc_fk
,per_person_fk
,per_person_trn_fk
,asg_assgnmnt_fk
,ptyp_wrktyp_fk
,time_day_evt_fk
,transfer_in_ind
,transfer_out_ind
,direct_ind
,direct_record_ind
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_mgrh_sup_psn_id(i)
,g_mgrh_sup_sc_fk(i)
,g_mgrh_psn_id(i)
,g_mgrh_trn_id(i)
,g_mgrh_asg_id(i)
,g_mgrh_wty_fk(i)
,g_mgrh_date(i)
,g_mgrh_in_ind(i)
,g_mgrh_out_ind(i)
,g_mgrh_dir_ind(i)
,g_mgrh_dir_rec(i)
,l_current_time
,l_user_id
,l_user_id
,l_user_id
,l_current_time);
INSERT INTO hri_mdp_orgh_transfers_ct
(org_sup_organztn_fk
,per_person_fk
,asg_assgnmnt_fk
,ptyp_wrktyp_fk
,time_day_evt_fk
,transfer_in_ind
,transfer_out_ind
,direct_ind
,headcount_trn
,fte_trn
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_orgh_sup_org_id(i)
,g_orgh_psn_id(i)
,g_orgh_asg_id(i)
,g_orgh_wty_fk(i)
,g_orgh_date(i)
,g_orgh_in_ind(i)
,g_orgh_out_ind(i)
,g_orgh_dir_ind(i)
,g_orgh_hdc_trn(i)
,g_orgh_fte_trn(i)
,l_current_time
,l_user_id
,l_user_id
,l_user_id
,l_current_time);
END bulk_insert_transfers;
PROCEDURE insert_mgrh_transfer_row(p_sup_person_id IN NUMBER
,p_sup_mngrsc_fk IN NUMBER
,p_trn_person_id IN NUMBER
,p_transferee_id IN NUMBER
,p_trn_assignment_id IN NUMBER
,p_trn_wrktyp_fk IN VARCHAR2
,p_transfer_date IN DATE
,p_transfer_in_ind IN NUMBER
,p_transfer_out_ind IN NUMBER
,p_direct_ind IN NUMBER
,p_direct_rec IN NUMBER) IS
BEGIN
-- Add row
g_mgrh_row_count := g_mgrh_row_count + 1;
END insert_mgrh_transfer_row;
PROCEDURE insert_orgh_transfer_row(p_sup_organization_id IN NUMBER
,p_trn_person_id IN NUMBER
,p_trn_assignment_id IN NUMBER
,p_trn_wrktyp_fk IN VARCHAR2
,p_transfer_date IN DATE
,p_transfer_in_ind IN NUMBER
,p_transfer_out_ind IN NUMBER
,p_direct_ind IN NUMBER
,p_hdc_trn IN NUMBER
,p_fte_trn IN NUMBER) IS
BEGIN
-- Add row
g_orgh_row_count := g_orgh_row_count + 1;
END insert_orgh_transfer_row;
SELECT
suph.sup_person_id
,chn.mgrs_mngrsc_pk
FROM
hri_cs_suph suph
,hri_cs_mngrsc_ct chn
WHERE suph.sub_person_id = v_person_id
AND suph.sub_person_id = chn.mgrs_person_fk
AND v_date BETWEEN suph.effective_start_date
AND suph.effective_end_date
AND v_date BETWEEN chn.mgrs_date_start
AND chn.mgrs_date_end;
insert_mgrh_transfer_row
(p_sup_person_id => l_index
,p_sup_mngrsc_fk => l_node_sk
,p_trn_person_id => p_transfer_psn_id
,p_transferee_id => p_transfer_psn_id
,p_trn_assignment_id => p_transfer_asg_id
,p_trn_wrktyp_fk => p_transfer_wty_fk
,p_transfer_date => p_transfer_date
,p_transfer_in_ind => l_transfer_in_ind
,p_transfer_out_ind => l_transfer_out_ind
,p_direct_ind => l_direct_ind
,p_direct_rec => 0);
insert_mgrh_transfer_row
(p_sup_person_id => l_index
,p_sup_mngrsc_fk => l_node_sk
,p_trn_person_id => p_transfer_psn_id
,p_transferee_id => p_transfer_psn_id
,p_trn_assignment_id => p_transfer_asg_id
,p_trn_wrktyp_fk => p_transfer_wty_fk
,p_transfer_date => p_transfer_date
,p_transfer_in_ind => 0
,p_transfer_out_ind => 1
,p_direct_ind => 1
,p_direct_rec => 1);
insert_mgrh_transfer_row
(p_sup_person_id => l_index
,p_sup_mngrsc_fk => l_node_sk
,p_trn_person_id => p_transfer_psn_id
,p_transferee_id => p_transfer_psn_id
,p_trn_assignment_id => p_transfer_asg_id
,p_trn_wrktyp_fk => p_transfer_wty_fk
,p_transfer_date => p_transfer_date
,p_transfer_in_ind => 1
,p_transfer_out_ind => 0
,p_direct_ind => 1
,p_direct_rec => 1);
SELECT
orgh_sup_organztn_fk
,orgh_relative_level
FROM hri_cs_orgh_ct
WHERE orgh_organztn_fk = v_organization_id;
insert_orgh_transfer_row
(p_sup_organization_id => l_index
,p_trn_person_id => p_transfer_psn_id
,p_trn_assignment_id => p_transfer_asg_id
,p_trn_wrktyp_fk => p_transfer_wty_fk
,p_transfer_date => p_transfer_date
,p_transfer_in_ind => 0
,p_transfer_out_ind => 1
,p_direct_ind => l_chain_cache(l_index).direct_node_before
,p_hdc_trn => p_transfer_hdc
,p_fte_trn => p_transfer_fte);
insert_orgh_transfer_row
(p_sup_organization_id => l_index
,p_trn_person_id => p_transfer_psn_id
,p_trn_assignment_id => p_transfer_asg_id
,p_trn_wrktyp_fk => p_transfer_wty_fk
,p_transfer_date => p_transfer_date
,p_transfer_in_ind => 1
,p_transfer_out_ind => 0
,p_direct_ind => l_chain_cache(l_index).direct_node_after
,p_hdc_trn => p_transfer_hdc
,p_fte_trn => p_transfer_fte);