[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
hri_mb_rec_vacancy_ct.LAST_UPDATED_BY %TYPE
INDEX BY BINARY_INTEGER;
PROCEDURE recover_insert_rows(p_stored_rows_to_insert NUMBER) IS
BEGIN
--
-- loop through rows still to insert one at a time
--
FOR i IN 1..p_stored_rows_to_insert LOOP
--
-- Trap unique constraint errors
--
BEGIN
--
-- @@ Code specific to this view/table below
-- @@ INTRUCTION TO DEVELOPER:
-- @@ 1/ For each column in your view put a column in the insert
-- @@ statement below.
-- @@ 2/ Prefix each column in the VALUE clause with g_
-- @@ 3/ make sure (i) is at the end of each column in the value clause
--
INSERT INTO hri_mb_rec_vacancy_ct
(pos_position_fk
,rvac_vacncy_fk
,org_organztn_fk
,org_organztn_mrgd_fk
,geo_location_fk
,job_job_fk
,grd_grade_fk
,time_day_vac_end_fk
,per_person_recr_fk
,per_person_rmgr_fk
,per_person_rsed_fk
,per_person_mrgd_fk
,time_day_vac_strt_fk
,vac_strt_date
,number_of_openings
,budget_measurement_value
,adt_business_group_id
,adt_vacancy_status_code
,adt_budget_type_code
,adt_vacancy_category_code
,creation_date
,created_by
,last_updated_by
,last_update_login
,last_update_date
)
VALUES
(g_pos_position_fk(i)
,g_rvac_vacncy_fk(i)
,g_org_organztn_fk(i)
,g_org_organztn_mrgd_fk(i)
,g_geo_location_fk(i)
,g_job_job_fk(i)
,g_grd_grade_fk(i)
,g_time_day_vac_end_fk(i)
,g_per_person_recr_fk(i)
,g_per_person_rmgr_fk(i)
,g_per_person_rsed_fk(i)
,g_per_person_mrgd_fk(i)
,g_time_day_vac_strt_fk(i)
,g_time_day_vac_strt_fk(i) -- for vac_start_date
,g_number_of_openings(i)
,g_budget_measurement_value(i)
,g_adt_business_group_id(i)
,g_adt_vacancy_status_code(i)
,g_adt_budget_type_code(i)
,g_adt_vacancy_category_code(i)
,g_sysdate(i)
,g_user_id(i)
,g_user_id(i)
,g_user_id(i)
,g_sysdate(i)
);
output('Single insert error: ' || to_char(g_rvac_vacncy_fk(i)) ||
' - ' || to_char(g_pos_position_fk(i)));
END recover_insert_rows;
PROCEDURE bulk_insert_rows(p_stored_rows_to_insert NUMBER) IS
--
BEGIN
--
-- insert chunk of rows
--
-- @@ Code specific to this view/table below
-- @@ INTRUCTION TO DEVELOPER:
-- @@ 1/ For each column in your view put a column in the insert statement
-- below.
-- @@ 2/ Prefix each column in the VALUE clause with g_
-- @@ 3/ make sure (i) is at the end of each column in the value clause
--
FORALL i IN 1..p_stored_rows_to_insert
INSERT INTO hri_mb_rec_vacancy_ct
(pos_position_fk
,rvac_vacncy_fk
,org_organztn_fk
,org_organztn_mrgd_fk
,geo_location_fk
,job_job_fk
,grd_grade_fk
,time_day_vac_end_fk
,per_person_recr_fk
,per_person_rmgr_fk
,per_person_rsed_fk
,per_person_mrgd_fk
,time_day_vac_strt_fk
,vac_strt_date
,number_of_openings
,budget_measurement_value
,adt_business_group_id
,adt_vacancy_status_code
,adt_budget_type_code
,adt_vacancy_category_code
,creation_date
,created_by
,last_updated_by
,last_update_login
,last_update_date
)
VALUES
(g_pos_position_fk(i)
,g_rvac_vacncy_fk(i)
,g_org_organztn_fk(i)
,g_org_organztn_mrgd_fk(i)
,g_geo_location_fk(i)
,g_job_job_fk(i)
,g_grd_grade_fk(i)
,g_time_day_vac_end_fk(i)
,g_per_person_recr_fk(i)
,g_per_person_rmgr_fk(i)
,g_per_person_rsed_fk(i)
,g_per_person_mrgd_fk(i)
,g_time_day_vac_strt_fk(i)
,g_time_day_vac_strt_fk(i) -- for vac_start_date
,g_number_of_openings(i)
,g_budget_measurement_value(i)
,g_adt_business_group_id(i)
,g_adt_vacancy_status_code(i)
,g_adt_budget_type_code(i)
,g_adt_vacancy_category_code(i)
,g_sysdate(i)
,g_user_id(i)
,g_user_id(i)
,g_user_id(i)
,g_sysdate(i)
);
recover_insert_rows(p_stored_rows_to_insert);
END bulk_insert_rows;
PROCEDURE Incremental_Update IS
--
BEGIN
--
-- @@ Code specific to this view/table below
-- @@ INTRUCTION TO DEVELOPER:
-- @@ 1/ Change the code below to reflect the columns in your view / table
-- @@ 2/ Change the FROM, INSERT, DELETE statements to point at the relevant
-- @@ source view / table
--
-- Insert completly new rows
--
-- log('Doing insert.');
INSERT INTO hri_mb_rec_vacancy_ct
(pos_position_fk
,rvac_vacncy_fk
,org_organztn_fk
,org_organztn_mrgd_fk
,geo_location_fk
,job_job_fk
,grd_grade_fk
,time_day_vac_end_fk
,per_person_recr_fk
,per_person_rmgr_fk
,per_person_rsed_fk
,per_person_mrgd_fk
,time_day_vac_strt_fk
,vac_strt_date
,number_of_openings
,budget_measurement_value
,adt_business_group_id
,adt_vacancy_status_code
,adt_budget_type_code
,adt_vacancy_category_code
,creation_date
,created_by
,last_updated_by
,last_update_login
,last_update_date
)
SELECT
NVL(vac.position_id, -1)
,vac.vacancy_id
,NVL(vac.organization_id, -1)
,NVL(vac.organization_id, vac.business_group_id)
,NVL(vac.location_id, -1)
,NVL(vac.job_id, -1)
,NVL(vac.grade_id, -1)
,NVL(vac.date_to, g_end_of_time)
,NVL(vac.recruiter_id,-1)
,NVL(vac.manager_id, -1)
,NVL(req.person_id, -1)
,hri_opl_rec_cand_pipln.get_merged_person_fk
(NVL(vac.manager_id, -1)
,NVL(vac.recruiter_id, -1)
,NVL(req.person_id, -1)
,NVL(vac.organization_id, -1)
,vac.business_group_id)
,vac.date_from
,vac.date_from -- for vac_strt_date
,vac.number_of_openings
,vac.budget_measurement_value
,vac.business_group_id
,vac.status
,vac.budget_measurement_type
,vac.vacancy_category
,sysdate
,fnd_global.user_id
,fnd_global.user_id
,fnd_global.user_id
,sysdate
FROM
per_all_vacancies vac
,per_requisitions req
WHERE vac.last_update_date BETWEEN g_start_date AND g_end_date
AND vac.requisition_id = req.requisition_id
AND NOT EXISTS (SELECT 'x'
FROM hri_mb_rec_vacancy_ct tbl
WHERE vac.vacancy_id = tbl.rvac_vacncy_fk);
UPDATE hri_mb_rec_vacancy_ct tbl
SET (pos_position_fk
,rvac_vacncy_fk
,org_organztn_fk
,org_organztn_mrgd_fk
,geo_location_fk
,job_job_fk
,grd_grade_fk
,time_day_vac_end_fk
,per_person_recr_fk
,per_person_rmgr_fk
,per_person_rsed_fk
,per_person_mrgd_fk
,time_day_vac_strt_fk
,vac_strt_date
,number_of_openings
,budget_measurement_value
,adt_business_group_id
,adt_vacancy_status_code
,adt_budget_type_code
,adt_vacancy_category_code
,creation_date
,created_by
,last_updated_by
,last_update_login
,last_update_date
) =
(SELECT NVL(vac.position_id, -1)
,vac.vacancy_id
,NVL(vac.organization_id, -1)
,NVL(vac.organization_id, vac.business_group_id)
,NVL(vac.location_id, -1)
,NVL(vac.job_id, -1)
,NVL(vac.grade_id, -1)
,NVL(vac.date_to, g_end_of_time)
,NVL(vac.recruiter_id,-1)
,NVL(vac.manager_id, -1)
,NVL(req.person_id, -1)
,hri_opl_rec_cand_pipln.get_merged_person_fk
(NVL(vac.manager_id, -1)
,NVL(vac.recruiter_id, -1)
,NVL(req.person_id, -1)
,NVL(vac.organization_id, -1)
,vac.business_group_id)
,vac.date_from
,vac.date_from
,vac.number_of_openings
,vac.budget_measurement_value
,vac.business_group_id
,vac.status
,vac.budget_measurement_type
,vac.vacancy_category
,sysdate
,fnd_global.user_id
,fnd_global.user_id
,fnd_global.user_id
,sysdate
FROM
per_all_vacancies vac
,per_requisitions req
WHERE vac.last_update_date BETWEEN g_start_date
AND g_end_date
AND req.requisition_id = vac.requisition_id
AND vac.vacancy_id = tbl.rvac_vacncy_fk
)
WHERE (tbl.rvac_vacncy_fk)
IN
(SELECT vac.vacancy_id
FROM per_all_vacancies vac
WHERE vac.last_update_date BETWEEN g_start_date
AND g_end_date);
DELETE
FROM hri_mb_rec_vacancy_ct tbl
WHERE tbl.rvac_vacncy_fk > 0
AND NOT EXISTS
(SELECT 'x'
FROM per_all_vacancies vac
WHERE vac.vacancy_id = tbl.rvac_vacncy_fk);
Output('Failure in incremental update process.');
SELECT NVL(vac.position_id, -1)
,vac.vacancy_id
,NVL(vac.organization_id, -1)
,NVL(vac.organization_id, vac.business_group_id)
,NVL(vac.location_id, -1)
,NVL(vac.job_id, -1)
,NVL(vac.grade_id, -1)
,NVL(vac.date_to, g_end_of_time)
,NVL(vac.recruiter_id,-1)
,NVL(vac.manager_id, -1)
,NVL(req.person_id, -1)
,hri_opl_rec_cand_pipln.get_merged_person_fk
(NVL(vac.manager_id, -1)
,NVL(vac.recruiter_id, -1)
,NVL(req.person_id, -1)
,NVL(vac.organization_id, -1)
,vac.business_group_id)
,vac.date_from
,vac.number_of_openings
,vac.budget_measurement_value
,vac.business_group_id
,vac.status
,vac.budget_measurement_type
,vac.vacancy_category
,sysdate
,fnd_global.user_id
FROM
per_all_vacancies vac
,per_requisitions req
WHERE req.requisition_id = vac.requisition_id;
bulk_insert_rows (l_rows_fetched);
insert into hri_mb_rec_vacancy_ct
( time_day_vac_strt_fk
,vac_strt_date
,time_day_vac_end_fk
,per_person_recr_fk
,per_person_rmgr_fk
,per_person_rsed_fk
,per_person_mrgd_fk
,org_organztn_fk
,org_organztn_mrgd_fk
,geo_location_fk
,job_job_fk
,grd_grade_fk
,pos_position_fk
,rvac_vacncy_fk
,number_of_openings
,budget_measurement_value
,adt_business_group_id
,adt_vacancy_status_code
,adt_budget_type_code
,adt_vacancy_category_code
,creation_date
,created_by
,last_updated_by
,last_update_login
,last_update_date)
values
( hr_general.start_of_time
,to_date(null)
,hr_general.end_of_time
,-1
,-1
,-1
,-1
,-1
,-1
,-1
,-1
,-1
,-1
,-1
,NULL
,NULL
,-1
,hri_oltp_view_message.get_unassigned_msg
,hri_oltp_view_message.get_unassigned_msg
,hri_oltp_view_message.get_unassigned_msg
,sysdate
,fnd_global.user_id
,fnd_global.user_id
,fnd_global.user_id
,sysdate
);
Incremental_Update;
SELECT 'x'
FROM hri_mb_rec_vacancy_ct;