[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_cs_per_person_ct(
per_work_phone
,per_work_location
,adt_ppf_person_id
,adt_ppf_effctv_start_date
,adt_ppf_effctv_end_date
,per_buyer_flag_code
,per_date_of_birth
,per_middle_names
,per_known_as
,per_honors
,per_pre_name_adjunct
,per_apl_number
,per_emp_number
,per_cwk_number
,per_apl_flag_crnt_code
,per_emp_flag_crnt_code
,per_cwk_flag_crnt_code
,per_person_name_lcl
,per_first_name
,per_place_of_birth
,per_last_name_prev
,per_order_by
,per_person_name_gbl
,per_last_name
,per_worker_crnt_flag_code
,per_country_of_birth
,per_date_of_death
,per_work_email
,per_title
,per_suffix
,per_person_name
,per_person_pk
,per_marital_status_crnt
,per_gender_crnt
)
VALUES( g_per_work_phone(i)
,g_per_work_location(i)
,g_adt_ppf_person_id(i)
,g_adt_ppf_effctv_start_date(i)
,g_adt_ppf_effctv_end_date(i)
,g_per_buyer_flag_code(i)
,g_per_date_of_birth(i)
,g_per_middle_names(i)
,g_per_known_as(i)
,g_per_honors(i)
,g_per_pre_name_adjunct(i)
,g_per_apl_number(i)
,g_per_emp_number(i)
,g_per_cwk_number(i)
,g_per_apl_flag_crnt_code(i)
,g_per_emp_flag_crnt_code(i)
,g_per_cwk_flag_crnt_code(i)
,g_per_person_name_lcl(i)
,g_per_first_name(i)
,g_per_place_of_birth(i)
,g_per_last_name_prev(i)
,g_per_order_by(i)
,g_per_person_name_gbl(i)
,g_per_last_name(i)
,g_per_worker_crnt_flag_code(i)
,g_per_country_of_birth(i)
,g_per_date_of_death(i)
,g_per_work_email(i)
,g_per_title(i)
,g_per_suffix(i)
,g_per_person_name(i)
,g_per_person_pk(i)
,g_per_marital_status_crnt(i)
,g_per_gender_crnt(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_cs_per_person_ct(
per_work_phone
,per_work_location
,adt_ppf_person_id
,adt_ppf_effctv_start_date
,adt_ppf_effctv_end_date
,per_buyer_flag_code
,per_date_of_birth
,per_middle_names
,per_known_as
,per_honors
,per_pre_name_adjunct
,per_apl_number
,per_emp_number
,per_cwk_number
,per_apl_flag_crnt_code
,per_emp_flag_crnt_code
,per_cwk_flag_crnt_code
,per_person_name_lcl
,per_first_name
,per_place_of_birth
,per_last_name_prev
,per_order_by
,per_person_name_gbl
,per_last_name
,per_worker_crnt_flag_code
,per_country_of_birth
,per_date_of_death
,per_work_email
,per_title
,per_suffix
,per_person_name
,per_person_pk
,per_marital_status_crnt
,per_gender_crnt
)
VALUES(g_per_work_phone(i)
,g_per_work_location(i)
,g_adt_ppf_person_id(i)
,g_adt_ppf_effctv_start_date(i)
,g_adt_ppf_effctv_end_date(i)
,g_per_buyer_flag_code(i)
,g_per_date_of_birth(i)
,g_per_middle_names(i)
,g_per_known_as(i)
,g_per_honors(i)
,g_per_pre_name_adjunct(i)
,g_per_apl_number(i)
,g_per_emp_number(i)
,g_per_cwk_number(i)
,g_per_apl_flag_crnt_code(i)
,g_per_emp_flag_crnt_code(i)
,g_per_cwk_flag_crnt_code(i)
,g_per_person_name_lcl(i)
,g_per_first_name(i)
,g_per_place_of_birth(i)
,g_per_last_name_prev(i)
,g_per_order_by(i)
,g_per_person_name_gbl(i)
,g_per_last_name(i)
,g_per_worker_crnt_flag_code(i)
,g_per_country_of_birth(i)
,g_per_date_of_death(i)
,g_per_work_email(i)
,g_per_title(i)
,g_per_suffix(i)
,g_per_person_name(i)
,g_per_person_pk(i)
,g_per_marital_status_crnt(i)
,g_per_gender_crnt(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_cs_per_person_ct(
per_work_phone
,per_work_location
,adt_ppf_person_id
,adt_ppf_effctv_start_date
,adt_ppf_effctv_end_date
,per_buyer_flag_code
,per_date_of_birth
,per_middle_names
,per_known_as
,per_honors
,per_pre_name_adjunct
,per_apl_number
,per_emp_number
,per_cwk_number
,per_apl_flag_crnt_code
,per_emp_flag_crnt_code
,per_cwk_flag_crnt_code
,per_person_name_lcl
,per_first_name
,per_place_of_birth
,per_last_name_prev
,per_order_by
,per_person_name_gbl
,per_last_name
,per_worker_crnt_flag_code
,per_country_of_birth
,per_date_of_death
,per_work_email
,per_title
,per_suffix
,per_person_name
,per_person_pk
,per_marital_status_crnt
,per_gender_crnt
)
SELECT NVL(per.work_telephone,'NA_EDW')
,NVL(per.internal_location,'NA_EDW')
,per.person_id
,per.effective_start_date
,per.effective_end_date
,case
when poa.agent_id = per.person_id
and (
poa.end_date_active is null
or
trunc(sysdate) between poa.start_date_active
and poa.end_date_active
)
then 'Y'
else 'N'
end
,NVL(per.date_of_birth,hr_general.start_of_time)
,per.middle_names
,per.known_as
,per.honors
,per.pre_name_adjunct
,NVL(per.applicant_number,'NA_EDW')
,NVL(per.employee_number,'NA_EDW')
,NVL(per.npw_number,'NA_EDW')
,per.current_applicant_flag
,per.current_employee_flag
,per.current_npw_flag
,per.local_name
,per.first_name
,NVL(per.town_of_birth,'NA_EDW')
,per.previous_last_name
,per.order_name
,per.global_name
,per.last_name
,decode(nvl(per.current_employee_flag,'N'),'Y','Y',current_npw_flag)
,NVL(per.country_of_birth,'NA_EDW')
,NVL(per.date_of_death,hr_general.end_of_time)
,NVL(per.email_address,'NA_EDW')
,per.title
,per.suffix
,per.first_name
,per.person_id
,NVL(per.marital_status,'NA_EDW')
,NVL(per.sex,'NA_EDW')
FROM per_all_people_f per
,po_agents poa
WHERE per.person_id = poa.agent_id(+)
AND TRUNC(sysdate) between per.effective_start_date and per.effective_end_date
AND NOT EXISTS (SELECT 'x'
FROM hri_cs_per_person_ct tbl
WHERE per.person_id = tbl.per_person_pk
);
UPDATE hri_cs_per_person_ct tbl
SET (per_buyer_flag_code) = (decode(tbl.per_buyer_flag_code, 'Y', 'N', 'Y'))
WHERE tbl.per_person_pk in
(SELECT ct.per_person_pk person_id
FROM (SELECT tbl.per_person_pk,
tbl.per_buyer_flag_code collected_flag,
case
when poa.agent_id = tbl.per_person_pk
and (
poa.end_date_active is null
or
trunc(sysdate) between poa.start_date_active
and poa.end_date_active
)
then 'Y'
else 'N'
end buyer_flag
FROM hri_cs_per_person_ct tbl, po_agents poa
WHERE tbl.per_person_pk = poa.agent_id(+)
)ct
WHERE ct.buyer_flag <> ct.collected_flag
);
UPDATE hri_cs_per_person_ct tbl
SET ( per_work_phone
,per_work_location
,adt_ppf_person_id
,adt_ppf_effctv_start_date
,adt_ppf_effctv_end_date
,per_date_of_birth
,per_middle_names
,per_known_as
,per_honors
,per_pre_name_adjunct
,per_apl_number
,per_emp_number
,per_cwk_number
,per_apl_flag_crnt_code
,per_emp_flag_crnt_code
,per_cwk_flag_crnt_code
,per_person_name_lcl
,per_first_name
,per_place_of_birth
,per_last_name_prev
,per_order_by
,per_person_name_gbl
,per_last_name
,per_worker_crnt_flag_code
,per_country_of_birth
,per_date_of_death
,per_work_email
,per_title
,per_suffix
,per_person_name
,per_person_pk
,per_marital_status_crnt
,per_gender_crnt
)=
(SELECT per.work_telephone
,per.internal_location
,per.person_id
,per.effective_start_date
,per.effective_end_date
,per.date_of_birth
,per.middle_names
,per.known_as
,per.honors
,per.pre_name_adjunct
,per.applicant_number
,per.employee_number
,per.npw_number
,per.current_applicant_flag
,per.current_employee_flag
,per.current_npw_flag
,per.global_name
,per.first_name
,per.town_of_birth
,per.previous_last_name
,per.order_name
,per.global_name
,per.last_name
,DECODE(nvl(per.current_employee_flag,'N'),'Y','Y',current_npw_flag)
,per.country_of_birth
,per.date_of_death
,per.email_address
,per.title
,per.suffix
,per.first_name
,per.person_id
,per.marital_status
,per.sex
FROM per_all_people_f per
WHERE per.person_id = tbl.per_person_pk
AND TRUNC(sysdate)
between per.effective_start_date and per.effective_end_date
)
WHERE tbl.per_person_pk in
(SELECT per1.person_id
from per_all_people_f per1
where per1.last_update_date
between g_start_date and g_end_date
);
DELETE
FROM hri_cs_per_person_ct tbl
WHERE NOT EXISTS (SELECT 'x'
FROM per_all_people_f per
WHERE per.person_id = tbl.per_person_pk
AND TRUNC(sysdate) BETWEEN
per.effective_start_date AND per.effective_end_date )
and tbl.per_person_pk <> -1;
Output('Failure in incremental update process.');
SELECT NVL(per.work_telephone,'NA_EDW')
,NVL(per.internal_location,'NA_EDW')
,per.person_id
,per.effective_start_date
,per.effective_end_date
,case
when poa.agent_id = per.person_id
and (
poa.end_date_active is null
or
trunc(sysdate) between poa.start_date_active
and poa.end_date_active
)
then 'Y'
else 'N'
end
,NVL(per.date_of_birth,hr_general.start_of_time)
,per.middle_names
,per.known_as
,per.honors
,per.pre_name_adjunct
,NVL(per.applicant_number,'NA_EDW')
,NVL(per.employee_number,'NA_EDW')
,NVL(per.npw_number,'NA_EDW')
,per.current_applicant_flag
,per.current_employee_flag
,per.current_npw_flag
,per.local_name
,per.first_name
,NVL(per.town_of_birth,'NA_EDW')
,per.previous_last_name
,per.order_name
,per.global_name
,per.last_name
,decode(nvl(per.current_employee_flag,'N'),'Y','Y',current_npw_flag)
,NVL(per.country_of_birth,'NA_EDW')
,NVL(per.date_of_death,hr_general.end_of_time)
,NVL(per.email_address,'NA_EDW')
,per.title
,per.suffix
,per.first_name
,per.person_id
,NVL(per.marital_status,'NA_EDW')
,NVL(per.sex,'NA_EDW')
FROM per_all_people_f per,
po_agents poa
WHERE TRUNC(sysdate) between per.effective_start_date and per.effective_end_date
AND per.person_id = poa.agent_id(+);
insert into hri_cs_per_person_ct(
PER_PERSON_PK
,PER_PERSON_NAME
,PER_ORDER_BY
,PER_PERSON_NAME_GBL
,PER_PERSON_NAME_LCL
,PER_FIRST_NAME
,PER_LAST_NAME
,PER_LAST_NAME_PREV
,PER_MIDDLE_NAMES
,PER_KNOWN_AS
,PER_HONORS
,PER_TITLE
,PER_SUFFIX
,PER_PRE_NAME_ADJUNCT
,PER_APL_NUMBER
,PER_EMP_NUMBER
,PER_CWK_NUMBER
,PER_APL_FLAG_CRNT_CODE
,PER_EMP_FLAG_CRNT_CODE
,PER_CWK_FLAG_CRNT_CODE
,PER_WORKER_CRNT_FLAG_CODE
,PER_BUYER_FLAG_CODE
,PER_DATE_OF_BIRTH
,PER_PLACE_OF_BIRTH
,PER_COUNTRY_OF_BIRTH
,PER_DATE_OF_DEATH
,PER_WORK_EMAIL
,PER_WORK_PHONE
,PER_WORK_LOCATION
,ADT_PPF_PERSON_ID
,ADT_PPF_EFFCTV_START_DATE
,ADT_PPF_EFFCTV_END_DATE
,PER_MARITAL_STATUS_CRNT
,PER_GENDER_CRNT )
select
id
,id_char
,NULL
,id_char
,id_char
,id_char
,id_char
,id_char
,id_char
,NULL
,NULL
,NULL
,NULL
,NULL
,id_char
,id_char
,id_char
,id_char
,id_char
,id_char
,id_char
,id_char
,hr_general.end_of_time
,id_char
,id_char
,hr_general.end_of_time
,id_char
,id_char
,id_char
,id
,hr_general.end_of_time
,hr_general.end_of_time
,id_char
,id_char
from hri_unassigned ;
bulk_insert_rows (l_rows_fetched);
Incremental_Update;
SELECT 'x'
FROM hri_cs_per_person_ct;