[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update boolean := true ;
if p_dml_operation = 'INSERT' then
--
l_datetrack_mode := hr_api.g_update;
l_update := true;
l_update := true;
elsif l_datetrack_mode in ('UPDATE_OVERRIDE','UPDATE' ) then
--
if p_effective_date = p_effective_start_date then
l_datetrack_mode := hr_api.g_correction ;
l_update := true;
if l_datetrack_mode in ('UPDATE_OVERRIDE') then
--
l_datetrack_mode := hr_api.g_update_override ;
l_update := false ;
elsif l_datetrack_mode in ('UPDATE') then
--
l_datetrack_mode := hr_api.g_update;
elsif l_datetrack_mode in ('UPDATE_CHANGE_INSERT') then
--
if p_effective_date = p_effective_start_date then
l_datetrack_mode := hr_api.g_correction ;
l_update := true;
l_datetrack_mode := hr_api.g_update_change_insert ;
l_update := true;
l_datetrack_mode := hr_api.g_update;
l_update := false;
if p_dml_operation = 'INSERT' then
--
l_datetrack_mode := hr_api.g_update;
l_update := false;
l_update := false;
elsif l_datetrack_mode in ('UPDATE_OVERRIDE','UPDATE' ) then
--
if p_effective_date = p_effective_start_date then
l_datetrack_mode := hr_api.g_correction ;
l_update := true;
l_datetrack_mode := hr_api.g_update ;
l_update := false ;
elsif l_datetrack_mode in ('UPDATE_CHANGE_INSERT') then
--
if p_effective_date = p_effective_start_date then
l_datetrack_mode := hr_api.g_correction ;
l_update := false;
l_datetrack_mode := hr_api.g_update ;
l_update := false;
l_datetrack_mode := hr_api.g_update;
l_update := false;
select tr.table_alias, tr.table_route_id
from pqh_table_route tr
where tr.table_route_id in(
select table_route_id from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.number_of_copies = 1);
g_table_data_in_cer.delete;
if p_dml_operation = 'UPDATE' then
--
l_ret_id := p_old_val ;
procedure update_cer_with_target(c_pk_rec g_pk_rec_type, p_copy_entity_txn_id in number) is
begin
hr_utility.set_location('Inside update_cer_with_target ',233);
update ben_copy_entity_results
set information9 = c_pk_rec.copy_reuse_type||'-'||c_pk_rec.new_value
where copy_entity_txn_id = p_copy_entity_txn_id
and table_route_id = c_pk_rec.table_route_id
and information1 = c_pk_rec.old_value ;
end update_cer_with_target ;
select display_name
from pqh_table_route_vl tr
where tr.table_alias = p_table_alias
and tr.from_clause = 'OAB' ;
g_pgm_tbl_copied.delete;
g_pgm_tbl_reused.delete;
g_pln_tbl_copied.delete;
g_pln_tbl_reused.delete;
g_opt_tbl_copied.delete;
g_opt_tbl_reused.delete;
g_ptp_tbl_copied.delete;
g_ptp_tbl_reused.delete;
g_eat_tbl_copied.delete;
g_eat_tbl_reused.delete;
g_bnb_tbl_copied.delete;
g_bnb_tbl_reused.delete;
g_clf_tbl_copied.delete;
g_clf_tbl_reused.delete;
g_hwf_tbl_copied.delete;
g_hwf_tbl_reused.delete;
g_agf_tbl_copied.delete;
g_agf_tbl_reused.delete;
g_lsf_tbl_copied.delete;
g_lsf_tbl_reused.delete;
g_pff_tbl_copied.delete;
g_pff_tbl_reused.delete;
g_cla_tbl_copied.delete;
g_cla_tbl_reused.delete;
g_reg_tbl_copied.delete;
g_reg_tbl_reused.delete;
g_bnr_tbl_copied.delete;
g_bnr_tbl_reused.delete;
g_bpp_tbl_copied.delete;
g_bpp_tbl_reused.delete;
g_ler_tbl_copied.delete;
g_ler_tbl_reused.delete;
g_elp_tbl_copied.delete;
g_elp_tbl_reused.delete;
g_dce_tbl_copied.delete;
g_dce_tbl_reused.delete;
g_gos_tbl_copied.delete;
g_gos_tbl_reused.delete;
g_bng_tbl_copied.delete;
g_bng_tbl_reused.delete;
g_pdl_tbl_copied.delete;
g_pdl_tbl_reused.delete;
g_sva_tbl_copied.delete;
g_sva_tbl_reused.delete;
g_cpl_tbl_copied.delete;
g_cpl_tbl_reused.delete;
g_cbp_tbl_copied.delete;
g_cbp_tbl_reused.delete;
g_cpt_tbl_copied.delete;
g_cpt_tbl_reused.delete;
g_fff_tbl_copied.delete;
g_fff_tbl_reused.delete;
g_abr_tbl_copied.delete;
g_abr_tbl_reused.delete;
g_apr_tbl_copied.delete;
g_apr_tbl_reused.delete;
g_vpf_tbl_copied.delete;
g_vpf_tbl_reused.delete;
g_ccm_tbl_copied.delete;
g_ccm_tbl_reused.delete;
g_acp_tbl_copied.delete;
g_acp_tbl_reused.delete;
g_psl_tbl_copied.delete;
g_psl_tbl_reused.delete;
g_egl_tbl_copied.delete;
g_egl_tbl_reused.delete;
BEN_PD_COPY_TO_BEN_three.g_not_copied_tbl.delete;
PROCEDURE create_or_update_ff (
p_formula_id IN NUMBER,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_business_group_id IN NUMBER,
p_legislation_code IN VARCHAR,
p_formula_type_id IN NUMBER,
p_formula_name IN VARCHAR,
p_description IN VARCHAR,
p_formula_text IN LONG,
p_sticky_flag IN VARCHAR,
p_compile_flag IN VARCHAR,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_process_date IN DATE,
p_dml_operation IN VARCHAR,
p_datetrack_mode IN VARCHAR
)
IS
BEGIN
hr_utility.set_location ('Entering procedure create_or_update_ff creation ',10);
IF p_dml_operation = hr_api.g_insert
THEN
hr_utility.set_location (' p_dml_operation' || p_dml_operation, 10);
INSERT INTO ff_formulas_f
(formula_id, effective_start_date, effective_end_date,
legislation_code, formula_type_id, formula_name,
description, formula_text, sticky_flag,
compile_flag, business_group_id, last_update_date,
last_updated_by, last_update_login, created_by,
creation_date
)
VALUES (p_formula_id, p_process_date, p_effective_end_date,
p_legislation_code, p_formula_type_id, p_formula_name,
p_description, p_formula_text, p_sticky_flag,
p_compile_flag, p_business_group_id, p_last_update_date,
p_last_updated_by, p_last_update_login, p_created_by,
p_creation_date
);
IF p_dml_operation = hr_api.g_update
THEN
hr_utility.set_location (' p_dml_operation' || p_dml_operation, 10);
IF p_datetrack_mode = hr_api.g_update
THEN
UPDATE ff_formulas_f
SET effective_end_date = (p_process_date - 1),
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
created_by = p_created_by,
creation_date = p_creation_date
WHERE formula_id = p_formula_id
AND effective_start_date = p_effective_start_date
AND effective_end_date = p_effective_end_date
AND business_group_id = p_business_group_id;
INSERT INTO ff_formulas_f
(formula_id, effective_start_date, effective_end_date,
legislation_code, formula_type_id, formula_name,
description, formula_text, sticky_flag,
compile_flag, business_group_id,
last_update_date, last_updated_by,
last_update_login, created_by, creation_date
)
VALUES (p_formula_id, p_process_date, p_effective_end_date,
p_legislation_code, p_formula_type_id, p_formula_name,
p_description, p_formula_text, p_sticky_flag,
p_compile_flag, p_business_group_id,
p_last_update_date, p_last_updated_by,
p_last_update_login, p_created_by, p_creation_date
);
UPDATE ff_formulas_f
SET legislation_code = p_legislation_code,
formula_type_id = p_formula_type_id,
formula_name = p_formula_name,
description = p_description,
formula_text = p_formula_text,
sticky_flag = p_sticky_flag,
compile_flag = p_compile_flag,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
created_by = p_created_by,
creation_date = p_creation_date
WHERE formula_id = p_formula_id
AND effective_start_date = p_effective_start_date
AND effective_end_date = p_effective_end_date
AND business_group_id = p_business_group_id;
IF p_datetrack_mode = hr_api.g_update_override
THEN
UPDATE ff_formulas_f
SET effective_end_date = (p_process_date - 1),
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
created_by = p_created_by,
creation_date = p_creation_date
WHERE formula_id = p_formula_id
AND effective_start_date = p_effective_start_date
AND effective_end_date = p_effective_end_date
AND business_group_id = p_business_group_id;
DELETE FROM ff_formulas_f
WHERE formula_id = p_formula_id
AND business_group_id = p_business_group_id
AND effective_start_date >= p_process_date;
INSERT INTO ff_formulas_f
(formula_id, effective_start_date, effective_end_date,
legislation_code, formula_type_id, formula_name,
description, formula_text, sticky_flag,
compile_flag, business_group_id,
last_update_date, last_updated_by,
last_update_login, created_by, creation_date
)
VALUES (p_formula_id, p_process_date, -- l_min_esd,
hr_api.g_eot, -- CHANGED FROM HARD-CODED DATE TO HR_API VARIABLE..
p_legislation_code, p_formula_type_id, p_formula_name,
p_description, p_formula_text, p_sticky_flag,
p_compile_flag, p_business_group_id,
p_last_update_date, p_last_updated_by,
p_last_update_login, p_created_by, p_creation_date
);
IF p_datetrack_mode = hr_api.g_update_change_insert
THEN
UPDATE ff_formulas_f
SET effective_end_date = (p_process_date - 1),
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
created_by = p_created_by,
creation_date = p_creation_date
WHERE formula_id = p_formula_id
AND business_group_id = p_business_group_id
AND effective_start_date = p_effective_start_date
AND effective_start_date = p_effective_end_date;
INSERT INTO ff_formulas_f
(formula_id, effective_start_date, effective_end_date,
legislation_code, formula_type_id, formula_name,
description, formula_text, sticky_flag,
compile_flag, business_group_id,
last_update_date, last_updated_by,
last_update_login, created_by, creation_date
)
VALUES (p_formula_id, p_process_date, -- l_min_esd,
p_effective_end_date,
p_legislation_code, p_formula_type_id, p_formula_name,
p_description, p_formula_text, p_sticky_flag,
p_compile_flag, p_business_group_id,
p_last_update_date, p_last_updated_by,
p_last_update_login, p_created_by, p_creation_date
);
hr_utility.set_location ('Leaving procedure create_or_update_ff creation ',10);
END create_or_update_ff;
SELECT DISTINCT cpe.information1, cpe.information2, cpe.information3,
cpe.information112 NAME, -- 5250824, 5253679 (FF Case Sensitive)
cpe.table_route_id,
cpe.dml_operation, cpe.datetrack_mode
--RKG Modfied for PDW FF Enhancements
FROM ben_copy_entity_results cpe, pqh_table_route tr
WHERE cpe.copy_entity_txn_id = p_copy_entity_txn_id
AND cpe.table_route_id = tr.table_route_id
AND tr.table_alias = l_table_alias
AND cpe.number_of_copies = 1 --ADDITION
GROUP BY cpe.information1,
cpe.information2,
cpe.information3,
cpe.information112,
cpe.table_route_id,
cpe.dml_operation,
cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
SELECT MIN (cpe.information2) min_esd, MAX (cpe.information3) min_eed
FROM ben_copy_entity_results cpe
WHERE cpe.copy_entity_txn_id = p_copy_entity_txn_id
AND cpe.table_route_id = c_table_route_id
AND cpe.information1 = c_information1;
SELECT cpe.*
FROM ben_copy_entity_results cpe
WHERE cpe.copy_entity_txn_id = p_copy_entity_txn_id
AND cpe.table_route_id = c_table_route_id
AND cpe.information1 = c_information1
AND cpe.information2 = c_information2
AND cpe.information3 = c_information3
AND ROWNUM = 1;
SELECT fff.formula_id new_value
FROM ff_formulas_f fff
WHERE fff.formula_name = c_fff_name -- 5250824, 5253679 (FF Case Sensitive)
AND fff.business_group_id = c_business_group_id
AND fff.formula_id <> c_new_pk_id
AND EXISTS (
SELECT NULL
FROM ff_formulas_f fff1
WHERE fff1.formula_name = c_fff_name -- 5250824 , 5253679 (FF Case Sensitive)
AND fff1.business_group_id = c_business_group_id
AND fff1.effective_start_date <= c_effective_start_date)
AND EXISTS (
SELECT NULL
FROM ff_formulas_f fff2
WHERE fff2.formula_name = c_fff_name -- 5250824, 5253679 (FF Case Sensitive)
AND fff2.business_group_id = c_business_group_id
AND fff2.effective_end_date >= c_effective_end_date);
l_update BOOLEAN := FALSE;
l_datetrack_mode VARCHAR2 (80) := hr_api.g_update;
l_update := FALSE;
IF l_dml_operation = 'UPDATE'
THEN
--
l_object_found_in_target := TRUE;
l_update := TRUE;
hr_utility.set_location ('found record for update', 10);
l_update := FALSE;
IF NOT l_object_found_in_target OR l_update
THEN
--
hr_utility.set_location (' l_object_found_in_target ', 60);
IF l_first_rec AND NOT l_update
THEN
-- Call Create routine.
--
hr_utility.set_location (' In the Insert FF_FORMULAS_F ', 70);
SELECT ff_formulas_s.NEXTVAL
INTO l_formula_id
FROM SYS.DUAL;
create_or_update_ff (p_formula_id => l_formula_id,
p_effective_start_date => r_fff.information2,
p_effective_end_date => r_fff.information3,
p_business_group_id => p_target_business_group_id,
p_legislation_code => r_fff.information13,
p_formula_type_id => r_fff.information160,
p_formula_name => l_formula_name, -- :FORMULA_NAME, 5253679 (FF Case Sensitive)
p_description => r_fff.information151,
p_formula_text => r_fff.information323,
p_sticky_flag => r_fff.information11,
p_compile_flag => r_fff.information12,
p_last_update_date => SYSDATE,
p_last_updated_by => 1,
p_last_update_login => 0,
p_created_by => 1,
p_creation_date => SYSDATE,
p_process_date => l_effective_date,
p_dml_operation => 'INSERT',
p_datetrack_mode => NULL
);
IF l_update
THEN
--
l_datetrack_mode := r_fff.datetrack_mode;
create_or_update_ff (p_formula_id => l_formula_id,
p_effective_start_date => r_fff.information2,
p_effective_end_date => r_fff.information3,
p_business_group_id => p_target_business_group_id,
p_legislation_code => r_fff.information13,
p_formula_type_id => r_fff.information160,
p_formula_name => l_formula_name, -- :FORMULA_NAME, 5253679 (FF Case Sensitive)
p_description => r_fff.information151,
p_formula_text => r_fff.information323,
p_sticky_flag => r_fff.information11,
p_compile_flag => r_fff.information12,
p_last_update_date => SYSDATE,
p_last_updated_by => 1,
p_last_update_login => 0,
p_created_by => 1,
p_creation_date => SYSDATE,
p_process_date => l_effective_date,
p_dml_operation => 'UPDATE',
p_datetrack_mode => l_datetrack_mode
);
IF l_dml_operation <> 'UPDATE'
THEN
--
hr_utility.set_location (' l_formula_id ' || l_formula_id,90);
create_or_update_ff (p_formula_id => l_formula_id,
p_effective_start_date => r_fff.information2,
p_effective_end_date => r_fff.information3,
p_business_group_id => p_target_business_group_id,
p_legislation_code => r_fff.information13,
p_formula_type_id => r_fff.information160,
p_formula_name => l_formula_name, -- :FORMULA_NAME, 5253679 (FF Case Sensitive)
p_description => r_fff.information151,
p_formula_text => r_fff.information323,
p_sticky_flag => r_fff.information11,
p_compile_flag => r_fff.information12,
p_last_update_date => SYSDATE,
p_last_updated_by => 1,
p_last_update_login => 0,
p_created_by => 1,
p_creation_date => SYSDATE,
p_process_date => l_effective_date,
p_dml_operation => 'INSERT',
p_datetrack_mode => NULL
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.information170 name,
cpe.table_route_id
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_ACTN_TYP
and tr.table_alias = l_table_alias
group by cpe.information1,cpe.information2,cpe.information3,
cpe.information170, cpe.table_route_id
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
EAT.actn_typ_id new_value
from BEN_ACTN_TYP EAT
where
EAT.name = c_EAT_name and
EAT.business_group_id = c_business_group_id
and EAT.actn_typ_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,
cpe.dml_operation,
cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_CMBN_PLIP_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 -- ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.information170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
CPL.cmbn_plip_id new_value
from BEN_CMBN_PLIP_F CPL
where CPL.name = c_CPL_name and
nvl(CPL.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CPL.business_group_id = c_business_group_id
and CPL.cmbn_plip_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_CMBN_PLIP_F CPL1
where CPL1.name = c_CPL_name and
nvl(CPL1.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CPL1.business_group_id = c_business_group_id
and CPL1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_CMBN_PLIP_F CPL2
where CPL2.name = c_CPL_name and
nvl(CPL2.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CPL2.business_group_id = c_business_group_id
and CPL2.effective_end_date >= c_effective_end_date )
;
select
CPL.cmbn_plip_id new_value
from BEN_CMBN_PLIP_F CPL
where CPL.name = c_CPL_name and
CPL.business_group_id = c_business_group_id
and CPL.cmbn_plip_id <> c_new_pk_id
and exists ( select null
from BEN_CMBN_PLIP_F CPL1
where CPL1.name = c_CPL_name and
CPL1.business_group_id = c_business_group_id
and CPL1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_CMBN_PLIP_F CPL2
where CPL2.name = c_CPL_name and
CPL2.business_group_id = c_business_group_id
and CPL2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target OR l_update then
--
--UPD END
--
l_current_pk_id := r_CPL.information1;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_CMBN_PLIP_F CREATE_CMBN_PLIP ',20);
if l_update then
--
l_datetrack_mode := r_CPL.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
hr_utility.set_location(' BEN_CMBN_PLIP_F UPDATE_CMBN_PLIP ',30);
BEN_CMBN_PLIP_API.UPDATE_CMBN_PLIP(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_CMBN_PLIP_ID => l_cmbn_plip_id
,P_CPL_ATTRIBUTE1 => r_CPL.INFORMATION111
,P_CPL_ATTRIBUTE10 => r_CPL.INFORMATION120
,P_CPL_ATTRIBUTE11 => r_CPL.INFORMATION121
,P_CPL_ATTRIBUTE12 => r_CPL.INFORMATION122
,P_CPL_ATTRIBUTE13 => r_CPL.INFORMATION123
,P_CPL_ATTRIBUTE14 => r_CPL.INFORMATION124
,P_CPL_ATTRIBUTE15 => r_CPL.INFORMATION125
,P_CPL_ATTRIBUTE16 => r_CPL.INFORMATION126
,P_CPL_ATTRIBUTE17 => r_CPL.INFORMATION127
,P_CPL_ATTRIBUTE18 => r_CPL.INFORMATION128
,P_CPL_ATTRIBUTE19 => r_CPL.INFORMATION129
,P_CPL_ATTRIBUTE2 => r_CPL.INFORMATION112
,P_CPL_ATTRIBUTE20 => r_CPL.INFORMATION130
,P_CPL_ATTRIBUTE21 => r_CPL.INFORMATION131
,P_CPL_ATTRIBUTE22 => r_CPL.INFORMATION132
,P_CPL_ATTRIBUTE23 => r_CPL.INFORMATION133
,P_CPL_ATTRIBUTE24 => r_CPL.INFORMATION134
,P_CPL_ATTRIBUTE25 => r_CPL.INFORMATION135
,P_CPL_ATTRIBUTE26 => r_CPL.INFORMATION136
,P_CPL_ATTRIBUTE27 => r_CPL.INFORMATION137
,P_CPL_ATTRIBUTE28 => r_CPL.INFORMATION138
,P_CPL_ATTRIBUTE29 => r_CPL.INFORMATION139
,P_CPL_ATTRIBUTE3 => r_CPL.INFORMATION113
,P_CPL_ATTRIBUTE30 => r_CPL.INFORMATION140
,P_CPL_ATTRIBUTE4 => r_CPL.INFORMATION114
,P_CPL_ATTRIBUTE5 => r_CPL.INFORMATION115
,P_CPL_ATTRIBUTE6 => r_CPL.INFORMATION116
,P_CPL_ATTRIBUTE7 => r_CPL.INFORMATION117
,P_CPL_ATTRIBUTE8 => r_CPL.INFORMATION118
,P_CPL_ATTRIBUTE9 => r_CPL.INFORMATION119
,P_CPL_ATTRIBUTE_CATEGORY => r_CPL.INFORMATION110
,P_NAME => l_prefix || r_CPL.INFORMATION170 || l_suffix
,P_PGM_ID => l_PGM_ID
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_CMBN_PLIP_API.delete_CMBN_PLIP(
--
p_validate => false
,p_cmbn_plip_id => l_cmbn_plip_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_CMBN_PTIP_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 -- ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
CBP.cmbn_ptip_id new_value
from BEN_CMBN_PTIP_F CBP
where CBP.name = c_CBP_name and
nvl(CBP.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CBP.business_group_id = c_business_group_id
and CBP.cmbn_ptip_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_CMBN_PTIP_F CBP1
where CBP1.name = c_CBP_name and
nvl(CBP1.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CBP1.business_group_id = c_business_group_id
and CBP1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_CMBN_PTIP_F CBP2
where CBP2.name = c_CBP_name and
nvl(CBP2.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CBP2.business_group_id = c_business_group_id
and CBP2.effective_end_date >= c_effective_end_date )
;
select
CBP.cmbn_ptip_id new_value
from BEN_CMBN_PTIP_F CBP
where CBP.name = c_CBP_name and
CBP.business_group_id = c_business_group_id
and CBP.cmbn_ptip_id <> c_new_pk_id
and exists ( select null
from BEN_CMBN_PTIP_F CBP1
where CBP1.name = c_CBP_name and
CBP1.business_group_id = c_business_group_id
and CBP1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_CMBN_PTIP_F CBP2
where CBP2.name = c_CBP_name and
CBP2.business_group_id = c_business_group_id
and CBP2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
end if ; --UPDATE
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_CMBN_PTIP_F CREATE_CMBN_PTIP ',20);
hr_utility.set_location(' BEN_CMBN_PTIP_F UPDATE_CMBN_PTIP ',30);
BEN_CMBN_PTIP_API.UPDATE_CMBN_PTIP(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_CBP_ATTRIBUTE1 => r_CBP.INFORMATION111
,P_CBP_ATTRIBUTE10 => r_CBP.INFORMATION120
,P_CBP_ATTRIBUTE11 => r_CBP.INFORMATION121
,P_CBP_ATTRIBUTE12 => r_CBP.INFORMATION122
,P_CBP_ATTRIBUTE13 => r_CBP.INFORMATION123
,P_CBP_ATTRIBUTE14 => r_CBP.INFORMATION124
,P_CBP_ATTRIBUTE15 => r_CBP.INFORMATION125
,P_CBP_ATTRIBUTE16 => r_CBP.INFORMATION126
,P_CBP_ATTRIBUTE17 => r_CBP.INFORMATION127
,P_CBP_ATTRIBUTE18 => r_CBP.INFORMATION128
,P_CBP_ATTRIBUTE19 => r_CBP.INFORMATION129
,P_CBP_ATTRIBUTE2 => r_CBP.INFORMATION112
,P_CBP_ATTRIBUTE20 => r_CBP.INFORMATION130
,P_CBP_ATTRIBUTE21 => r_CBP.INFORMATION131
,P_CBP_ATTRIBUTE22 => r_CBP.INFORMATION132
,P_CBP_ATTRIBUTE23 => r_CBP.INFORMATION133
,P_CBP_ATTRIBUTE24 => r_CBP.INFORMATION134
,P_CBP_ATTRIBUTE25 => r_CBP.INFORMATION135
,P_CBP_ATTRIBUTE26 => r_CBP.INFORMATION136
,P_CBP_ATTRIBUTE27 => r_CBP.INFORMATION137
,P_CBP_ATTRIBUTE28 => r_CBP.INFORMATION138
,P_CBP_ATTRIBUTE29 => r_CBP.INFORMATION139
,P_CBP_ATTRIBUTE3 => r_CBP.INFORMATION113
,P_CBP_ATTRIBUTE30 => r_CBP.INFORMATION140
,P_CBP_ATTRIBUTE4 => r_CBP.INFORMATION114
,P_CBP_ATTRIBUTE5 => r_CBP.INFORMATION115
,P_CBP_ATTRIBUTE6 => r_CBP.INFORMATION116
,P_CBP_ATTRIBUTE7 => r_CBP.INFORMATION117
,P_CBP_ATTRIBUTE8 => r_CBP.INFORMATION118
,P_CBP_ATTRIBUTE9 => r_CBP.INFORMATION119
,P_CBP_ATTRIBUTE_CATEGORY => r_CBP.INFORMATION110
,P_CMBN_PTIP_ID => l_cmbn_ptip_id
,P_NAME => l_prefix || r_CBP.INFORMATION170 || l_suffix
,P_PGM_ID => l_PGM_ID
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_CMBN_PTIP_API.delete_CMBN_PTIP(
--
p_validate => false
,p_cmbn_ptip_id => l_cmbn_ptip_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,
cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_CMBN_PTIP_OPT_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 -- ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
CPT.cmbn_ptip_opt_id new_value
from BEN_CMBN_PTIP_OPT_F CPT
where CPT.name = c_CPT_name and
nvl(CPT.OPT_ID,-999) = nvl(l_OPT_ID,-999) and
nvl(CPT.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CPT.business_group_id = c_business_group_id
and CPT.cmbn_ptip_opt_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_CMBN_PTIP_OPT_F CPT1
where CPT1.name = c_CPT_name and
nvl(CPT1.OPT_ID,-999) = nvl(l_OPT_ID,-999) and
nvl(CPT1.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CPT1.business_group_id = c_business_group_id
and CPT1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_CMBN_PTIP_OPT_F CPT2
where CPT2.name = c_CPT_name and
nvl(CPT2.OPT_ID,-999) = nvl(l_OPT_ID,-999) and
nvl(CPT2.PGM_ID,-999) = nvl(l_PGM_ID,-999) and
CPT2.business_group_id = c_business_group_id
and CPT2.effective_end_date >= c_effective_end_date )
;
select
CPT.cmbn_ptip_opt_id new_value
from BEN_CMBN_PTIP_OPT_F CPT
where CPT.name = c_CPT_name and
CPT.business_group_id = c_business_group_id
and CPT.cmbn_ptip_opt_id <> c_new_pk_id
and exists ( select null
from BEN_CMBN_PTIP_OPT_F CPT1
where CPT1.name = c_CPT_name and
CPT1.business_group_id = c_business_group_id
and CPT1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_CMBN_PTIP_OPT_F CPT2
where CPT2.name = c_CPT_name and
CPT2.business_group_id = c_business_group_id
and CPT2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
end if; --UPDATE
if not l_object_found_in_target OR l_update then
--
l_current_pk_id := r_CPT.information1;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_CMBN_PTIP_OPT_F CREATE_CMBN_PTIP_OPT ',20);
if l_update then
--
l_datetrack_mode := r_CPT.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_CMBN_PTIP_OPT_F UPDATE_CMBN_PTIP_OPT ',30);
BEN_CMBN_PTIP_OPT_API.UPDATE_CMBN_PTIP_OPT(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_CMBN_PTIP_OPT_ID => l_cmbn_ptip_opt_id
,P_CPT_ATTRIBUTE1 => r_CPT.INFORMATION111
,P_CPT_ATTRIBUTE10 => r_CPT.INFORMATION120
,P_CPT_ATTRIBUTE11 => r_CPT.INFORMATION121
,P_CPT_ATTRIBUTE12 => r_CPT.INFORMATION122
,P_CPT_ATTRIBUTE13 => r_CPT.INFORMATION123
,P_CPT_ATTRIBUTE14 => r_CPT.INFORMATION124
,P_CPT_ATTRIBUTE15 => r_CPT.INFORMATION125
,P_CPT_ATTRIBUTE16 => r_CPT.INFORMATION126
,P_CPT_ATTRIBUTE17 => r_CPT.INFORMATION127
,P_CPT_ATTRIBUTE18 => r_CPT.INFORMATION128
,P_CPT_ATTRIBUTE19 => r_CPT.INFORMATION129
,P_CPT_ATTRIBUTE2 => r_CPT.INFORMATION112
,P_CPT_ATTRIBUTE20 => r_CPT.INFORMATION130
,P_CPT_ATTRIBUTE21 => r_CPT.INFORMATION131
,P_CPT_ATTRIBUTE22 => r_CPT.INFORMATION132
,P_CPT_ATTRIBUTE23 => r_CPT.INFORMATION133
,P_CPT_ATTRIBUTE24 => r_CPT.INFORMATION134
,P_CPT_ATTRIBUTE25 => r_CPT.INFORMATION135
,P_CPT_ATTRIBUTE26 => r_CPT.INFORMATION136
,P_CPT_ATTRIBUTE27 => r_CPT.INFORMATION137
,P_CPT_ATTRIBUTE28 => r_CPT.INFORMATION138
,P_CPT_ATTRIBUTE29 => r_CPT.INFORMATION139
,P_CPT_ATTRIBUTE3 => r_CPT.INFORMATION113
,P_CPT_ATTRIBUTE30 => r_CPT.INFORMATION140
,P_CPT_ATTRIBUTE4 => r_CPT.INFORMATION114
,P_CPT_ATTRIBUTE5 => r_CPT.INFORMATION115
,P_CPT_ATTRIBUTE6 => r_CPT.INFORMATION116
,P_CPT_ATTRIBUTE7 => r_CPT.INFORMATION117
,P_CPT_ATTRIBUTE8 => r_CPT.INFORMATION118
,P_CPT_ATTRIBUTE9 => r_CPT.INFORMATION119
,P_CPT_ATTRIBUTE_CATEGORY => r_CPT.INFORMATION110
,P_NAME => l_prefix || r_CPT.INFORMATION170 || l_suffix
,P_OPT_ID => l_OPT_ID
,P_PGM_ID => l_PGM_ID
,P_PTIP_ID => l_PTIP_ID
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
end if; --l_update
BEN_CMBN_PTIP_OPT_API.delete_CMBN_PTIP_OPT(
--
p_validate => false
,p_cmbn_ptip_opt_id => l_cmbn_ptip_opt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,
cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_BNFTS_BAL_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode ;
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,
cpe.dml_operation,
cpe.datetrack_mode
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170,
cpe.table_route_id,
cpe.dml_operation,
cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
BNB.bnfts_bal_id new_value
from BEN_BNFTS_BAL_F BNB
where BNB.name = c_BNB_name
and BNB.business_group_id = c_business_group_id
and BNB.bnfts_bal_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_BNFTS_BAL_F BNB1
where BNB1.name = c_BNB_name
and BNB1.business_group_id = c_business_group_id
and BNB1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_BNFTS_BAL_F BNB2
where BNB2.name = c_BNB_name
and BNB2.business_group_id = c_business_group_id
and BNB2.effective_end_date >= c_effective_end_date ) ;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target OR l_update then
--
--UPD END
open c_BNB(r_BNB_unique.table_route_id,
r_BNB_unique.information1,
r_BNB_unique.information2,
r_BNB_unique.information3 ) ;
if l_first_rec and not l_update then
--UPD END
-- Call Create routine.
hr_utility.set_location(' BEN_BNFTS_BAL_F CREATE_BENEFITS_BALANCE ',20);
if l_update then
--
l_datetrack_mode := r_BNB.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
--
hr_utility.set_location(' BEN_BNFTS_BAL_F UPDATE_BENEFITS_BALANCE ',30);
BEN_BENEFITS_BALANCE_API.UPDATE_BENEFITS_BALANCE(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_BNB_ATTRIBUTE1 => r_BNB.INFORMATION111
,P_BNB_ATTRIBUTE10 => r_BNB.INFORMATION120
,P_BNB_ATTRIBUTE11 => r_BNB.INFORMATION121
,P_BNB_ATTRIBUTE12 => r_BNB.INFORMATION122
,P_BNB_ATTRIBUTE13 => r_BNB.INFORMATION123
,P_BNB_ATTRIBUTE14 => r_BNB.INFORMATION124
,P_BNB_ATTRIBUTE15 => r_BNB.INFORMATION125
,P_BNB_ATTRIBUTE16 => r_BNB.INFORMATION126
,P_BNB_ATTRIBUTE17 => r_BNB.INFORMATION127
,P_BNB_ATTRIBUTE18 => r_BNB.INFORMATION128
,P_BNB_ATTRIBUTE19 => r_BNB.INFORMATION129
,P_BNB_ATTRIBUTE2 => r_BNB.INFORMATION112
,P_BNB_ATTRIBUTE20 => r_BNB.INFORMATION130
,P_BNB_ATTRIBUTE21 => r_BNB.INFORMATION131
,P_BNB_ATTRIBUTE22 => r_BNB.INFORMATION132
,P_BNB_ATTRIBUTE23 => r_BNB.INFORMATION133
,P_BNB_ATTRIBUTE24 => r_BNB.INFORMATION134
,P_BNB_ATTRIBUTE25 => r_BNB.INFORMATION135
,P_BNB_ATTRIBUTE26 => r_BNB.INFORMATION136
,P_BNB_ATTRIBUTE27 => r_BNB.INFORMATION137
,P_BNB_ATTRIBUTE28 => r_BNB.INFORMATION138
,P_BNB_ATTRIBUTE29 => r_BNB.INFORMATION139
,P_BNB_ATTRIBUTE3 => r_BNB.INFORMATION113
,P_BNB_ATTRIBUTE30 => r_BNB.INFORMATION140
,P_BNB_ATTRIBUTE4 => r_BNB.INFORMATION114
,P_BNB_ATTRIBUTE5 => r_BNB.INFORMATION115
,P_BNB_ATTRIBUTE6 => r_BNB.INFORMATION116
,P_BNB_ATTRIBUTE7 => r_BNB.INFORMATION117
,P_BNB_ATTRIBUTE8 => r_BNB.INFORMATION118
,P_BNB_ATTRIBUTE9 => r_BNB.INFORMATION119
,P_BNB_ATTRIBUTE_CATEGORY => r_BNB.INFORMATION110
,P_BNFTS_BAL_DESC => r_BNB.INFORMATION185
,P_BNFTS_BAL_ID => l_bnfts_bal_id
,P_BNFTS_BAL_USG_CD => r_BNB.INFORMATION11
,P_NAME => l_prefix || r_BNB.INFORMATION170 || l_suffix
,P_NNMNTRY_UOM => r_BNB.INFORMATION13
,P_UOM => r_BNB.INFORMATION12
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
--UPD START
-- ,P_DATETRACK_MODE => l_datetrack_mode
,P_DATETRACK_MODE => l_datetrack_mode
--UPD END
);
end if; -- l_update
BEN_BENEFITS_BALANCE_API.delete_BENEFITS_BALANCE(
--
p_validate => false
,p_bnfts_bal_id => l_bnfts_bal_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,
cpe.dml_operation --UPD
from ben_copy_entity_results cpe
-- pqh_table_route tr UPD
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
-- and cpe.table_route_id = tr.table_route_id UPD
-- and tr.where_clause = l_BEN_COMP_LVL_FCTR
and cpe.table_alias = l_table_alias --UPD
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,
cpe.dml_operation
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
CLF.comp_lvl_fctr_id new_value
from BEN_COMP_LVL_FCTR CLF
where CLF.name = c_CLF_name
and CLF.business_group_id = c_business_group_id
and CLF.comp_lvl_fctr_id <> c_new_pk_id;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target OR l_update then
--
--UPD END
--
l_current_pk_id := r_CLF.information1;
if l_first_rec and not l_update then
--UPD END
-- Call Create routine.
hr_utility.set_location(' BEN_COMP_LVL_FCTR CREATE_COMP_LEVEL_FACTORS ',20);
elsif l_update THEN
--
hr_utility.set_location(' BEN_COMP_LVL_FCTR UPDATE_COMP_LEVEL_FACTORS ',20);
BEN_COMP_LEVEL_FACTORS_API.UPDATE_COMP_LEVEL_FACTORS(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(l_parent_effective_start_date,p_effective_date)
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_BNFTS_BAL_ID => l_BNFTS_BAL_ID
,P_CLF_ATTRIBUTE1 => r_CLF.INFORMATION111
,P_CLF_ATTRIBUTE10 => r_CLF.INFORMATION120
,P_CLF_ATTRIBUTE11 => r_CLF.INFORMATION121
,P_CLF_ATTRIBUTE12 => r_CLF.INFORMATION122
,P_CLF_ATTRIBUTE13 => r_CLF.INFORMATION123
,P_CLF_ATTRIBUTE14 => r_CLF.INFORMATION124
,P_CLF_ATTRIBUTE15 => r_CLF.INFORMATION125
,P_CLF_ATTRIBUTE16 => r_CLF.INFORMATION126
,P_CLF_ATTRIBUTE17 => r_CLF.INFORMATION127
,P_CLF_ATTRIBUTE18 => r_CLF.INFORMATION128
,P_CLF_ATTRIBUTE19 => r_CLF.INFORMATION129
,P_CLF_ATTRIBUTE2 => r_CLF.INFORMATION112
,P_CLF_ATTRIBUTE20 => r_CLF.INFORMATION130
,P_CLF_ATTRIBUTE21 => r_CLF.INFORMATION131
,P_CLF_ATTRIBUTE22 => r_CLF.INFORMATION132
,P_CLF_ATTRIBUTE23 => r_CLF.INFORMATION133
,P_CLF_ATTRIBUTE24 => r_CLF.INFORMATION134
,P_CLF_ATTRIBUTE25 => r_CLF.INFORMATION135
,P_CLF_ATTRIBUTE26 => r_CLF.INFORMATION136
,P_CLF_ATTRIBUTE27 => r_CLF.INFORMATION137
,P_CLF_ATTRIBUTE28 => r_CLF.INFORMATION138
,P_CLF_ATTRIBUTE29 => r_CLF.INFORMATION139
,P_CLF_ATTRIBUTE3 => r_CLF.INFORMATION113
,P_CLF_ATTRIBUTE30 => r_CLF.INFORMATION140
,P_CLF_ATTRIBUTE4 => r_CLF.INFORMATION114
,P_CLF_ATTRIBUTE5 => r_CLF.INFORMATION115
,P_CLF_ATTRIBUTE6 => r_CLF.INFORMATION116
,P_CLF_ATTRIBUTE7 => r_CLF.INFORMATION117
,P_CLF_ATTRIBUTE8 => r_CLF.INFORMATION118
,P_CLF_ATTRIBUTE9 => r_CLF.INFORMATION119
,P_CLF_ATTRIBUTE_CATEGORY => r_CLF.INFORMATION110
,P_COMP_ALT_VAL_TO_USE_CD => r_CLF.INFORMATION11
,P_COMP_CALC_RL => l_COMP_CALC_RL
,P_COMP_LVL_DET_CD => r_CLF.INFORMATION18
,P_COMP_LVL_DET_RL => l_COMP_LVL_DET_RL
,P_COMP_LVL_FCTR_ID => l_comp_lvl_fctr_id
,P_COMP_LVL_UOM => r_CLF.INFORMATION15
,P_COMP_SRC_CD => r_CLF.INFORMATION16
,P_DEFINED_BALANCE_ID => l_DEFINED_BALANCE_ID
,P_MN_COMP_VAL => r_CLF.INFORMATION294
,P_MX_COMP_VAL => r_CLF.INFORMATION293
,P_NAME => l_prefix || r_CLF.INFORMATION170 || l_suffix
,P_NO_MN_COMP_FLAG => r_CLF.INFORMATION13
,P_NO_MX_COMP_FLAG => r_CLF.INFORMATION12
,P_RNDG_CD => r_CLF.INFORMATION14
,P_RNDG_RL => l_RNDG_RL
,P_STTD_SAL_PRDCTY_CD => r_CLF.INFORMATION17
,p_proration_flag => r_CLF.INFORMATION20
,p_start_day_mo => r_CLF.INFORMATION21
,p_end_day_mo => r_CLF.INFORMATION22
,p_start_year => r_CLF.INFORMATION23
,p_end_year => r_CLF.INFORMATION24
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_HRS_WKD_IN_PERD_FCTR
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
HWF.hrs_wkd_in_perd_fctr_id new_value
from BEN_HRS_WKD_IN_PERD_FCTR HWF
where HWF.name = c_HWF_name
and HWF.business_group_id = c_business_group_id
and HWF.hrs_wkd_in_perd_fctr_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target or l_update then
--
l_current_pk_id := r_HWF.information1;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_HRS_WKD_IN_PERD_FCTR CREATE_HRS_WKD_IN_PERD_FCTR ',20);
ELSIF l_update THEN
--
BEN_HRS_WKD_IN_PERD_FCTR_API.UPDATE_HRS_WKD_IN_PERD_FCTR(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(l_parent_effective_start_date,p_effective_date)
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_BNFTS_BAL_ID => l_BNFTS_BAL_ID
,P_DEFINED_BALANCE_ID => l_DEFINED_BALANCE_ID
,P_HRS_ALT_VAL_TO_USE_CD => r_HWF.INFORMATION18
,P_HRS_SRC_CD => r_HWF.INFORMATION13
,P_HRS_WKD_CALC_RL => l_HRS_WKD_CALC_RL
,P_HRS_WKD_DET_CD => r_HWF.INFORMATION15
,P_HRS_WKD_DET_RL => l_HRS_WKD_DET_RL
,P_HRS_WKD_IN_PERD_FCTR_ID => l_hrs_wkd_in_perd_fctr_id
,P_HWF_ATTRIBUTE1 => r_HWF.INFORMATION111
,P_HWF_ATTRIBUTE10 => r_HWF.INFORMATION120
,P_HWF_ATTRIBUTE11 => r_HWF.INFORMATION121
,P_HWF_ATTRIBUTE12 => r_HWF.INFORMATION122
,P_HWF_ATTRIBUTE13 => r_HWF.INFORMATION123
,P_HWF_ATTRIBUTE14 => r_HWF.INFORMATION124
,P_HWF_ATTRIBUTE15 => r_HWF.INFORMATION125
,P_HWF_ATTRIBUTE16 => r_HWF.INFORMATION126
,P_HWF_ATTRIBUTE17 => r_HWF.INFORMATION127
,P_HWF_ATTRIBUTE18 => r_HWF.INFORMATION128
,P_HWF_ATTRIBUTE19 => r_HWF.INFORMATION129
,P_HWF_ATTRIBUTE2 => r_HWF.INFORMATION112
,P_HWF_ATTRIBUTE20 => r_HWF.INFORMATION130
,P_HWF_ATTRIBUTE21 => r_HWF.INFORMATION131
,P_HWF_ATTRIBUTE22 => r_HWF.INFORMATION132
,P_HWF_ATTRIBUTE23 => r_HWF.INFORMATION133
,P_HWF_ATTRIBUTE24 => r_HWF.INFORMATION134
,P_HWF_ATTRIBUTE25 => r_HWF.INFORMATION135
,P_HWF_ATTRIBUTE26 => r_HWF.INFORMATION136
,P_HWF_ATTRIBUTE27 => r_HWF.INFORMATION137
,P_HWF_ATTRIBUTE28 => r_HWF.INFORMATION138
,P_HWF_ATTRIBUTE29 => r_HWF.INFORMATION139
,P_HWF_ATTRIBUTE3 => r_HWF.INFORMATION113
,P_HWF_ATTRIBUTE30 => r_HWF.INFORMATION140
,P_HWF_ATTRIBUTE4 => r_HWF.INFORMATION114
,P_HWF_ATTRIBUTE5 => r_HWF.INFORMATION115
,P_HWF_ATTRIBUTE6 => r_HWF.INFORMATION116
,P_HWF_ATTRIBUTE7 => r_HWF.INFORMATION117
,P_HWF_ATTRIBUTE8 => r_HWF.INFORMATION118
,P_HWF_ATTRIBUTE9 => r_HWF.INFORMATION119
,P_HWF_ATTRIBUTE_CATEGORY => r_HWF.INFORMATION110
,P_MN_HRS_NUM => r_HWF.INFORMATION293
,P_MX_HRS_NUM => r_HWF.INFORMATION294
,P_NAME => l_prefix || r_HWF.INFORMATION170 || l_suffix
,P_NO_MN_HRS_WKD_FLAG => r_HWF.INFORMATION11
,P_NO_MX_HRS_WKD_FLAG => r_HWF.INFORMATION19
,P_ONCE_R_CNTUG_CD => r_HWF.INFORMATION16
,P_PYRL_FREQ_CD => r_HWF.INFORMATION12
,P_RNDG_CD => r_HWF.INFORMATION14
,P_RNDG_RL => l_RNDG_RL
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_AGE_FCTR
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
AGF.age_fctr_id new_value
from BEN_AGE_FCTR AGF
where AGF.name = c_AGF_name
and AGF.business_group_id = c_business_group_id
and AGF.age_fctr_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target OR l_update then
--
open c_AGF(r_AGF_unique.table_route_id,
r_AGF_unique.information1,
r_AGF_unique.information2,
r_AGF_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_AGE_FCTR CREATE_AGE_FACTOR ',20);
elsif l_update then
hr_utility.set_location(' BEN_AGE_FCTR UPDATE_AGE_FACTOR ',20);
BEN_AGE_FACTOR_API.UPDATE_AGE_FACTOR(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(ben_pd_copy_to_ben_one.g_copy_effective_date ,p_effective_date)
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_AGE_CALC_RL => l_AGE_CALC_RL
,P_AGE_DET_CD => r_AGF.INFORMATION16
,P_AGE_DET_RL => l_AGE_DET_RL
,P_AGE_FCTR_ID => l_age_fctr_id
,P_AGE_TO_USE_CD => r_AGF.INFORMATION14
,P_AGE_UOM => r_AGF.INFORMATION15
,P_AGF_ATTRIBUTE1 => r_AGF.INFORMATION111
,P_AGF_ATTRIBUTE10 => r_AGF.INFORMATION120
,P_AGF_ATTRIBUTE11 => r_AGF.INFORMATION121
,P_AGF_ATTRIBUTE12 => r_AGF.INFORMATION122
,P_AGF_ATTRIBUTE13 => r_AGF.INFORMATION123
,P_AGF_ATTRIBUTE14 => r_AGF.INFORMATION124
,P_AGF_ATTRIBUTE15 => r_AGF.INFORMATION125
,P_AGF_ATTRIBUTE16 => r_AGF.INFORMATION126
,P_AGF_ATTRIBUTE17 => r_AGF.INFORMATION127
,P_AGF_ATTRIBUTE18 => r_AGF.INFORMATION128
,P_AGF_ATTRIBUTE19 => r_AGF.INFORMATION129
,P_AGF_ATTRIBUTE2 => r_AGF.INFORMATION112
,P_AGF_ATTRIBUTE20 => r_AGF.INFORMATION130
,P_AGF_ATTRIBUTE21 => r_AGF.INFORMATION131
,P_AGF_ATTRIBUTE22 => r_AGF.INFORMATION132
,P_AGF_ATTRIBUTE23 => r_AGF.INFORMATION133
,P_AGF_ATTRIBUTE24 => r_AGF.INFORMATION134
,P_AGF_ATTRIBUTE25 => r_AGF.INFORMATION135
,P_AGF_ATTRIBUTE26 => r_AGF.INFORMATION136
,P_AGF_ATTRIBUTE27 => r_AGF.INFORMATION137
,P_AGF_ATTRIBUTE28 => r_AGF.INFORMATION138
,P_AGF_ATTRIBUTE29 => r_AGF.INFORMATION139
,P_AGF_ATTRIBUTE3 => r_AGF.INFORMATION113
,P_AGF_ATTRIBUTE30 => r_AGF.INFORMATION140
,P_AGF_ATTRIBUTE4 => r_AGF.INFORMATION114
,P_AGF_ATTRIBUTE5 => r_AGF.INFORMATION115
,P_AGF_ATTRIBUTE6 => r_AGF.INFORMATION116
,P_AGF_ATTRIBUTE7 => r_AGF.INFORMATION117
,P_AGF_ATTRIBUTE8 => r_AGF.INFORMATION118
,P_AGF_ATTRIBUTE9 => r_AGF.INFORMATION119
,P_AGF_ATTRIBUTE_CATEGORY => r_AGF.INFORMATION110
,P_MN_AGE_NUM => r_AGF.INFORMATION294
,P_MX_AGE_NUM => r_AGF.INFORMATION293
,P_NAME => l_prefix || r_AGF.INFORMATION170 || l_suffix
,P_NO_MN_AGE_FLAG => r_AGF.INFORMATION11
,P_NO_MX_AGE_FLAG => r_AGF.INFORMATION12
,P_RNDG_CD => r_AGF.INFORMATION13
,P_RNDG_RL => l_RNDG_RL
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_LOS_FCTR
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
LSF.los_fctr_id new_value
from BEN_LOS_FCTR LSF
where LSF.name = c_LSF_name
and LSF.business_group_id = c_business_group_id
and LSF.los_fctr_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target or l_update then
--
open c_LSF(r_LSF_unique.table_route_id,
r_LSF_unique.information1,
r_LSF_unique.information2,
r_LSF_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_LOS_FCTR CREATE_LOS_FACTORS ',20);
elsif l_update then
--
BEN_LOS_FACTORS_API.UPDATE_LOS_FACTORS(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(ben_pd_copy_to_ben_one.g_copy_effective_date,p_effective_date)
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
-- ,P_hrs_ALT_VAL_TO_USE_CD => r_LSF.information11
-- ,P_LOS_ALT_VAL_TO_USE_CD => r_LSF.INFORMATION19
,P_LOS_CALC_RL => l_LOS_CALC_RL
,P_LOS_DET_CD => r_LSF.INFORMATION15
,P_LOS_DET_RL => l_LOS_DET_RL
,P_LOS_DT_TO_USE_CD => r_LSF.INFORMATION14
,P_LOS_DT_TO_USE_RL => l_LOS_DT_TO_USE_RL
,P_LOS_FCTR_ID => l_los_fctr_id
,P_LOS_UOM => r_LSF.INFORMATION17
,P_LSF_ATTRIBUTE1 => r_LSF.INFORMATION111
,P_LSF_ATTRIBUTE10 => r_LSF.INFORMATION120
,P_LSF_ATTRIBUTE11 => r_LSF.INFORMATION121
,P_LSF_ATTRIBUTE12 => r_LSF.INFORMATION122
,P_LSF_ATTRIBUTE13 => r_LSF.INFORMATION123
,P_LSF_ATTRIBUTE14 => r_LSF.INFORMATION124
,P_LSF_ATTRIBUTE15 => r_LSF.INFORMATION125
,P_LSF_ATTRIBUTE16 => r_LSF.INFORMATION126
,P_LSF_ATTRIBUTE17 => r_LSF.INFORMATION127
,P_LSF_ATTRIBUTE18 => r_LSF.INFORMATION128
,P_LSF_ATTRIBUTE19 => r_LSF.INFORMATION129
,P_LSF_ATTRIBUTE2 => r_LSF.INFORMATION112
,P_LSF_ATTRIBUTE20 => r_LSF.INFORMATION130
,P_LSF_ATTRIBUTE21 => r_LSF.INFORMATION131
,P_LSF_ATTRIBUTE22 => r_LSF.INFORMATION132
,P_LSF_ATTRIBUTE23 => r_LSF.INFORMATION133
,P_LSF_ATTRIBUTE24 => r_LSF.INFORMATION134
,P_LSF_ATTRIBUTE25 => r_LSF.INFORMATION135
,P_LSF_ATTRIBUTE26 => r_LSF.INFORMATION136
,P_LSF_ATTRIBUTE27 => r_LSF.INFORMATION137
,P_LSF_ATTRIBUTE28 => r_LSF.INFORMATION138
,P_LSF_ATTRIBUTE29 => r_LSF.INFORMATION139
,P_LSF_ATTRIBUTE3 => r_LSF.INFORMATION113
,P_LSF_ATTRIBUTE30 => r_LSF.INFORMATION140
,P_LSF_ATTRIBUTE4 => r_LSF.INFORMATION114
,P_LSF_ATTRIBUTE5 => r_LSF.INFORMATION115
,P_LSF_ATTRIBUTE6 => r_LSF.INFORMATION116
,P_LSF_ATTRIBUTE7 => r_LSF.INFORMATION117
,P_LSF_ATTRIBUTE8 => r_LSF.INFORMATION118
,P_LSF_ATTRIBUTE9 => r_LSF.INFORMATION119
,P_LSF_ATTRIBUTE_CATEGORY => r_LSF.INFORMATION110
,P_MN_LOS_NUM => r_LSF.INFORMATION293
,P_MX_LOS_NUM => r_LSF.INFORMATION294
,P_NAME => l_prefix || r_LSF.INFORMATION170 || l_suffix
,P_NO_MN_LOS_NUM_APLS_FLAG => r_LSF.INFORMATION13
,P_NO_MX_LOS_NUM_APLS_FLAG => r_LSF.INFORMATION12
,P_RNDG_CD => r_LSF.INFORMATION16
,P_RNDG_RL => l_RNDG_RL
,P_USE_OVERID_SVC_DT_FLAG => r_LSF.INFORMATION11
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION218 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_PCT_FL_TM_FCTR
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION218, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
PFF.pct_fl_tm_fctr_id new_value
from BEN_PCT_FL_TM_FCTR PFF
where PFF.name = c_PFF_name
and PFF.business_group_id = c_business_group_id
and PFF.pct_fl_tm_fctr_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
end if; --l_update
if not l_object_found_in_target or l_update then
--
open c_PFF(r_PFF_unique.table_route_id,
r_PFF_unique.information1,
r_PFF_unique.information2,
r_PFF_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_PCT_FL_TM_FCTR CREATE_PERCENT_FT_FACTORS ',20);
elsif l_update then
--
hr_utility.set_location(' BEN_PCT_FL_TM_FCTR UPDATE_PERCENT_FT_FACTORS ',20);
BEN_PERCENT_FT_FACTORS_API.UPDATE_PERCENT_FT_FACTORS(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(ben_pd_copy_to_ben_one.g_copy_effective_date ,p_effective_date)
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_MN_PCT_VAL => r_PFF.INFORMATION294
,P_MX_PCT_VAL => r_PFF.INFORMATION293
,P_NAME => l_prefix || r_PFF.INFORMATION218 || l_suffix
,P_NO_MN_PCT_VAL_FLAG => r_PFF.INFORMATION11
,P_NO_MX_PCT_VAL_FLAG => r_PFF.INFORMATION12
,P_PCT_FL_TM_FCTR_ID => l_pct_fl_tm_fctr_id
,P_PFF_ATTRIBUTE1 => r_PFF.INFORMATION111
,P_PFF_ATTRIBUTE10 => r_PFF.INFORMATION120
,P_PFF_ATTRIBUTE11 => r_PFF.INFORMATION121
,P_PFF_ATTRIBUTE12 => r_PFF.INFORMATION122
,P_PFF_ATTRIBUTE13 => r_PFF.INFORMATION123
,P_PFF_ATTRIBUTE14 => r_PFF.INFORMATION124
,P_PFF_ATTRIBUTE15 => r_PFF.INFORMATION125
,P_PFF_ATTRIBUTE16 => r_PFF.INFORMATION126
,P_PFF_ATTRIBUTE17 => r_PFF.INFORMATION127
,P_PFF_ATTRIBUTE18 => r_PFF.INFORMATION128
,P_PFF_ATTRIBUTE19 => r_PFF.INFORMATION129
,P_PFF_ATTRIBUTE2 => r_PFF.INFORMATION112
,P_PFF_ATTRIBUTE20 => r_PFF.INFORMATION130
,P_PFF_ATTRIBUTE21 => r_PFF.INFORMATION131
,P_PFF_ATTRIBUTE22 => r_PFF.INFORMATION132
,P_PFF_ATTRIBUTE23 => r_PFF.INFORMATION133
,P_PFF_ATTRIBUTE24 => r_PFF.INFORMATION134
,P_PFF_ATTRIBUTE25 => r_PFF.INFORMATION135
,P_PFF_ATTRIBUTE26 => r_PFF.INFORMATION136
,P_PFF_ATTRIBUTE27 => r_PFF.INFORMATION137
,P_PFF_ATTRIBUTE28 => r_PFF.INFORMATION138
,P_PFF_ATTRIBUTE29 => r_PFF.INFORMATION139
,P_PFF_ATTRIBUTE3 => r_PFF.INFORMATION113
,P_PFF_ATTRIBUTE30 => r_PFF.INFORMATION140
,P_PFF_ATTRIBUTE4 => r_PFF.INFORMATION114
,P_PFF_ATTRIBUTE5 => r_PFF.INFORMATION115
,P_PFF_ATTRIBUTE6 => r_PFF.INFORMATION116
,P_PFF_ATTRIBUTE7 => r_PFF.INFORMATION117
,P_PFF_ATTRIBUTE8 => r_PFF.INFORMATION118
,P_PFF_ATTRIBUTE9 => r_PFF.INFORMATION119
,P_PFF_ATTRIBUTE_CATEGORY => r_PFF.INFORMATION110
,P_RNDG_CD => r_PFF.INFORMATION15
,P_RNDG_RL => l_RNDG_RL
,P_USE_PRMRY_ASNT_ONLY_FLAG => r_PFF.INFORMATION13
,P_USE_SUM_OF_ALL_ASNTS_FLAG => r_PFF.INFORMATION14
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_CMBN_AGE_LOS_FCTR
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
CLA.cmbn_age_los_fctr_id new_value
from BEN_CMBN_AGE_LOS_FCTR CLA
where CLA.name = c_CLA_name
and CLA.business_group_id = c_business_group_id
and CLA.cmbn_age_los_fctr_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target or l_update then
--
open c_CLA(r_CLA_unique.table_route_id,
r_CLA_unique.information1,
r_CLA_unique.information2,
r_CLA_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_CMBN_AGE_LOS_FCTR CREATE_CMBN_AGE_LOS_FCTR ',20);
elsif l_update then
hr_utility.set_location(' BEN_CMBN_AGE_LOS_FCTR UPDATE_CMBN_AGE_LOS_FCTR ',20);
BEN_CMBN_AGE_LOS_FCTR_API.UPDATE_CMBN_AGE_LOS_FCTR(
--
P_VALIDATE => false
-- ,P_EFFECTIVE_DATE => p_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_AGE_FCTR_ID => l_AGE_FCTR_ID
,P_CLA_ATTRIBUTE1 => r_CLA.INFORMATION111
,P_CLA_ATTRIBUTE10 => r_CLA.INFORMATION120
,P_CLA_ATTRIBUTE11 => r_CLA.INFORMATION121
,P_CLA_ATTRIBUTE12 => r_CLA.INFORMATION122
,P_CLA_ATTRIBUTE13 => r_CLA.INFORMATION123
,P_CLA_ATTRIBUTE14 => r_CLA.INFORMATION124
,P_CLA_ATTRIBUTE15 => r_CLA.INFORMATION125
,P_CLA_ATTRIBUTE16 => r_CLA.INFORMATION126
,P_CLA_ATTRIBUTE17 => r_CLA.INFORMATION127
,P_CLA_ATTRIBUTE18 => r_CLA.INFORMATION128
,P_CLA_ATTRIBUTE19 => r_CLA.INFORMATION129
,P_CLA_ATTRIBUTE2 => r_CLA.INFORMATION112
,P_CLA_ATTRIBUTE20 => r_CLA.INFORMATION130
,P_CLA_ATTRIBUTE21 => r_CLA.INFORMATION131
,P_CLA_ATTRIBUTE22 => r_CLA.INFORMATION132
,P_CLA_ATTRIBUTE23 => r_CLA.INFORMATION133
,P_CLA_ATTRIBUTE24 => r_CLA.INFORMATION134
,P_CLA_ATTRIBUTE25 => r_CLA.INFORMATION135
,P_CLA_ATTRIBUTE26 => r_CLA.INFORMATION136
,P_CLA_ATTRIBUTE27 => r_CLA.INFORMATION137
,P_CLA_ATTRIBUTE28 => r_CLA.INFORMATION138
,P_CLA_ATTRIBUTE29 => r_CLA.INFORMATION139
,P_CLA_ATTRIBUTE3 => r_CLA.INFORMATION113
,P_CLA_ATTRIBUTE30 => r_CLA.INFORMATION140
,P_CLA_ATTRIBUTE4 => r_CLA.INFORMATION114
,P_CLA_ATTRIBUTE5 => r_CLA.INFORMATION115
,P_CLA_ATTRIBUTE6 => r_CLA.INFORMATION116
,P_CLA_ATTRIBUTE7 => r_CLA.INFORMATION117
,P_CLA_ATTRIBUTE8 => r_CLA.INFORMATION118
,P_CLA_ATTRIBUTE9 => r_CLA.INFORMATION119
,P_CLA_ATTRIBUTE_CATEGORY => r_CLA.INFORMATION110
,P_CMBND_MAX_VAL => r_CLA.INFORMATION294
,P_CMBND_MIN_VAL => r_CLA.INFORMATION293
,P_CMBN_AGE_LOS_FCTR_ID => l_cmbn_age_los_fctr_id
,P_LOS_FCTR_ID => l_LOS_FCTR_ID
,P_NAME => l_prefix || r_CLA.INFORMATION170 || l_suffix
,P_ORDR_NUM => r_CLA.INFORMATION260
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_PL_TYP_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
PTP.pl_typ_id new_value
from BEN_PL_TYP_F PTP
where PTP.name = c_PTP_name
and PTP.business_group_id = c_business_group_id
and PTP.pl_typ_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_PL_TYP_F PTP1
where PTP1.name = c_PTP_name
and PTP1.business_group_id = c_business_group_id
and PTP1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_PL_TYP_F PTP2
where PTP2.name = c_PTP_name
and PTP2.business_group_id = c_business_group_id
and PTP2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
end if ; --l_update
if not l_object_found_in_target or l_update then
--
open c_PTP(r_PTP_unique.table_route_id,
r_PTP_unique.information1,
r_PTP_unique.information2,
r_PTP_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_PL_TYP_F CREATE_PLAN_TYPE ',20);
if l_update then
--
l_datetrack_mode := r_PTP.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
hr_utility.set_location(' BEN_PL_TYP_F UPDATE_PLAN_TYPE ',30);
BEN_PLAN_TYPE_API.UPDATE_PLAN_TYPE(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_COMP_TYP_CD => r_PTP.INFORMATION16
,P_IVR_IDENT => r_PTP.INFORMATION141
,P_MN_ENRL_RQD_NUM => r_PTP.INFORMATION261
,P_MX_ENRL_ALWD_NUM => r_PTP.INFORMATION260
,P_NAME => l_prefix || r_PTP.INFORMATION170 || l_suffix
,P_NO_MN_ENRL_NUM_DFND_FLAG => r_PTP.INFORMATION14
,P_NO_MX_ENRL_NUM_DFND_FLAG => r_PTP.INFORMATION13
,P_OPT_DSPLY_FMT_CD => r_PTP.INFORMATION15
,P_OPT_TYP_CD => r_PTP.INFORMATION18
,P_PL_TYP_ID => l_pl_typ_id
,P_PL_TYP_STAT_CD => r_PTP.INFORMATION17
,P_PTP_ATTRIBUTE1 => r_PTP.INFORMATION111
,P_PTP_ATTRIBUTE10 => r_PTP.INFORMATION120
,P_PTP_ATTRIBUTE11 => r_PTP.INFORMATION121
,P_PTP_ATTRIBUTE12 => r_PTP.INFORMATION122
,P_PTP_ATTRIBUTE13 => r_PTP.INFORMATION123
,P_PTP_ATTRIBUTE14 => r_PTP.INFORMATION124
,P_PTP_ATTRIBUTE15 => r_PTP.INFORMATION125
,P_PTP_ATTRIBUTE16 => r_PTP.INFORMATION126
,P_PTP_ATTRIBUTE17 => r_PTP.INFORMATION127
,P_PTP_ATTRIBUTE18 => r_PTP.INFORMATION128
,P_PTP_ATTRIBUTE19 => r_PTP.INFORMATION129
,P_PTP_ATTRIBUTE2 => r_PTP.INFORMATION112
,P_PTP_ATTRIBUTE20 => r_PTP.INFORMATION130
,P_PTP_ATTRIBUTE21 => r_PTP.INFORMATION131
,P_PTP_ATTRIBUTE22 => r_PTP.INFORMATION132
,P_PTP_ATTRIBUTE23 => r_PTP.INFORMATION133
,P_PTP_ATTRIBUTE24 => r_PTP.INFORMATION134
,P_PTP_ATTRIBUTE25 => r_PTP.INFORMATION135
,P_PTP_ATTRIBUTE26 => r_PTP.INFORMATION136
,P_PTP_ATTRIBUTE27 => r_PTP.INFORMATION137
,P_PTP_ATTRIBUTE28 => r_PTP.INFORMATION138
,P_PTP_ATTRIBUTE29 => r_PTP.INFORMATION139
,P_PTP_ATTRIBUTE3 => r_PTP.INFORMATION113
,P_PTP_ATTRIBUTE30 => r_PTP.INFORMATION140
,P_PTP_ATTRIBUTE4 => r_PTP.INFORMATION114
,P_PTP_ATTRIBUTE5 => r_PTP.INFORMATION115
,P_PTP_ATTRIBUTE6 => r_PTP.INFORMATION116
,P_PTP_ATTRIBUTE7 => r_PTP.INFORMATION117
,P_PTP_ATTRIBUTE8 => r_PTP.INFORMATION118
,P_PTP_ATTRIBUTE9 => r_PTP.INFORMATION119
,P_PTP_ATTRIBUTE_CATEGORY => r_PTP.INFORMATION110
,P_SHORT_CODE => r_PTP.INFORMATION11
,P_SHORT_NAME => r_PTP.INFORMATION12
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_PLAN_TYPE_API.delete_PLAN_TYPE(
--
p_validate => false
,p_pl_typ_id => l_pl_typ_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_PGM_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by cpe.information1, cpe.information2 /* Bug 5076363 */;
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
PGM.pgm_id new_value
from BEN_PGM_F PGM
where PGM.name = c_PGM_name
and PGM.business_group_id = c_business_group_id
and PGM.pgm_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_PGM_F PGM1
where PGM1.name = c_PGM_name
and PGM1.business_group_id = c_business_group_id
and PGM1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_PGM_F PGM2
where PGM2.name = c_PGM_name
and PGM2.business_group_id = c_business_group_id
and PGM2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
open c_PGM(r_PGM_unique.table_route_id,
r_PGM_unique.information1,
r_PGM_unique.information2,
r_PGM_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_PGM_F CREATE_PROGRAM ',20);
,P_UPDATE_SALARY_CD => r_PGM.INFORMATION16
,P_URL_REF_NAME => r_PGM.INFORMATION185
,P_USES_ALL_ASMTS_FOR_RTS_FLAG => r_PGM.INFORMATION27
,P_USE_MULTI_PAY_RATES_FLAG => NVL(r_PGM.INFORMATION17, 'N')
,P_USE_PROG_POINTS_FLAG => NVL(r_PGM.INFORMATION18, 'N')
,P_USE_SCORES_CD => r_PGM.INFORMATION19
,P_VRFY_FMLY_MMBR_CD => r_PGM.INFORMATION54
,P_VRFY_FMLY_MMBR_RL => l_VRFY_FMLY_MMBR_RL
,P_USE_VARIABLE_RATES_FLAG => NVL(r_PGM.INFORMATION69,'N')
,P_SALARY_CALC_MTHD_CD => r_PGM.INFORMATION70
,P_GSP_ALLOW_OVERRIDE_FLAG => NVL(r_PGM.INFORMATION72,'N')
,P_SALARY_CALC_MTHD_RL => l_SALARY_CALC_MTHD_RL
--
--ML
,p_SUSP_IF_DPNT_SSN_NT_PRV_CD =>l_susp_if_dpnt_ssn_nt_prv_cd
,p_SUSP_IF_DPNT_DOB_NT_PRV_CD =>l_susp_if_dpnt_dob_nt_prv_cd
,p_SUSP_IF_DPNT_ADR_NT_PRV_CD =>l_susp_if_dpnt_adr_nt_prv_cd
,p_SUSP_IF_CTFN_NOT_DPNT_FLAG =>nvl(r_pgm.INFORMATION192,'Y')
,p_DPNT_CTFN_DETERMINE_CD =>r_pgm.INFORMATION193
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
if l_update then
--
l_datetrack_mode := r_PGM.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
hr_utility.set_location(' BEN_PGM_F UPDATE_PROGRAM ',30);
BEN_PROGRAM_API.UPDATE_PROGRAM(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_ACTY_REF_PERD_CD => r_PGM.INFORMATION41
,P_ALWS_UNRSTRCTD_ENRT_FLAG => r_PGM.INFORMATION36
,P_AUTO_ENRT_MTHD_RL => l_AUTO_ENRT_MTHD_RL
,P_COORD_CVG_FOR_ALL_PLS_FLG => r_PGM.INFORMATION30
,P_DFLT_ELEMENT_TYPE_ID => l_DFLT_ELEMENT_TYPE_ID
,P_DFLT_INPUT_VALUE_ID => l_DFLT_INPUT_VALUE_ID
,P_DFLT_PGM_FLAG => NVL(r_PGM.INFORMATION13, 'N')
,P_DFLT_STEP_CD => r_PGM.INFORMATION14
,P_DFLT_STEP_RL => l_DFLT_STEP_RL
,P_DPNT_ADRS_RQD_FLAG => r_PGM.INFORMATION21
,P_DPNT_CVG_END_DT_CD => r_PGM.INFORMATION43
,P_DPNT_CVG_END_DT_RL => l_DPNT_CVG_END_DT_RL
,P_DPNT_CVG_STRT_DT_CD => r_PGM.INFORMATION44
,P_DPNT_CVG_STRT_DT_RL => l_DPNT_CVG_STRT_DT_RL
,P_DPNT_DOB_RQD_FLAG => r_PGM.INFORMATION23
,P_DPNT_DSGN_CD => r_PGM.INFORMATION40
,P_DPNT_DSGN_LVL_CD => r_PGM.INFORMATION37
,P_DPNT_DSGN_NO_CTFN_RQD_FLAG => r_PGM.INFORMATION31
,P_DPNT_LEGV_ID_RQD_FLAG => r_PGM.INFORMATION25
,P_DRVBL_FCTR_APLS_RTS_FLAG => r_PGM.INFORMATION34
,P_DRVBL_FCTR_DPNT_ELIG_FLAG => r_PGM.INFORMATION32
,P_DRVBL_FCTR_PRTN_ELIG_FLAG => r_PGM.INFORMATION33
,P_ELIG_APLS_FLAG => r_PGM.INFORMATION26
,P_ENRT_CD => r_PGM.INFORMATION51
,P_ENRT_CVG_END_DT_CD => r_PGM.INFORMATION42
,P_ENRT_CVG_END_DT_RL => l_ENRT_CVG_END_DT_RL
,P_ENRT_CVG_STRT_DT_CD => r_PGM.INFORMATION45
,P_ENRT_CVG_STRT_DT_RL => l_ENRT_CVG_STRT_DT_RL
,P_ENRT_INFO_RT_FREQ_CD => r_PGM.INFORMATION46
,P_ENRT_MTHD_CD => r_PGM.INFORMATION52
,P_ENRT_RL => l_ENRT_RL
,P_IVR_IDENT => r_PGM.INFORMATION141
,P_MX_DPNT_PCT_PRTT_LF_AMT => r_PGM.INFORMATION287
,P_MX_SPS_PCT_PRTT_LF_AMT => r_PGM.INFORMATION288
,P_NAME => l_prefix || r_PGM.INFORMATION170 || l_suffix
,P_PER_CVRD_CD => r_PGM.INFORMATION20
,P_PGM_ATTRIBUTE1 => r_PGM.INFORMATION111
,P_PGM_ATTRIBUTE10 => r_PGM.INFORMATION120
,P_PGM_ATTRIBUTE11 => r_PGM.INFORMATION121
,P_PGM_ATTRIBUTE12 => r_PGM.INFORMATION122
,P_PGM_ATTRIBUTE13 => r_PGM.INFORMATION123
,P_PGM_ATTRIBUTE14 => r_PGM.INFORMATION124
,P_PGM_ATTRIBUTE15 => r_PGM.INFORMATION125
,P_PGM_ATTRIBUTE16 => r_PGM.INFORMATION126
,P_PGM_ATTRIBUTE17 => r_PGM.INFORMATION127
,P_PGM_ATTRIBUTE18 => r_PGM.INFORMATION128
,P_PGM_ATTRIBUTE19 => r_PGM.INFORMATION129
,P_PGM_ATTRIBUTE2 => r_PGM.INFORMATION112
,P_PGM_ATTRIBUTE20 => r_PGM.INFORMATION130
,P_PGM_ATTRIBUTE21 => r_PGM.INFORMATION131
,P_PGM_ATTRIBUTE22 => r_PGM.INFORMATION132
,P_PGM_ATTRIBUTE23 => r_PGM.INFORMATION133
,P_PGM_ATTRIBUTE24 => r_PGM.INFORMATION134
,P_PGM_ATTRIBUTE25 => r_PGM.INFORMATION135
,P_PGM_ATTRIBUTE26 => r_PGM.INFORMATION136
,P_PGM_ATTRIBUTE27 => r_PGM.INFORMATION137
,P_PGM_ATTRIBUTE28 => r_PGM.INFORMATION138
,P_PGM_ATTRIBUTE29 => r_PGM.INFORMATION139
,P_PGM_ATTRIBUTE3 => r_PGM.INFORMATION113
,P_PGM_ATTRIBUTE30 => r_PGM.INFORMATION140
,P_PGM_ATTRIBUTE4 => r_PGM.INFORMATION114
,P_PGM_ATTRIBUTE5 => r_PGM.INFORMATION115
,P_PGM_ATTRIBUTE6 => r_PGM.INFORMATION116
,P_PGM_ATTRIBUTE7 => r_PGM.INFORMATION117
,P_PGM_ATTRIBUTE8 => r_PGM.INFORMATION118
,P_PGM_ATTRIBUTE9 => r_PGM.INFORMATION119
,P_PGM_ATTRIBUTE_CATEGORY => r_PGM.INFORMATION110
,P_PGM_DESC => r_PGM.INFORMATION219
,P_PGM_GRP_CD => r_PGM.INFORMATION49
,P_PGM_ID => l_pgm_id
,P_PGM_PRVDS_NO_AUTO_ENRT_FLAG => r_PGM.INFORMATION22
,P_PGM_PRVDS_NO_DFLT_ENRT_FLAG => r_PGM.INFORMATION24
,P_PGM_STAT_CD => l_status_cd
,P_PGM_TYP_CD => r_PGM.INFORMATION39
,P_PGM_UOM => r_PGM.INFORMATION50
,P_PGM_USE_ALL_ASNTS_ELIG_FLAG => r_PGM.INFORMATION29
,P_POE_LVL_CD => r_PGM.INFORMATION53
,P_PRTN_ELIG_OVRID_ALWD_FLAG => r_PGM.INFORMATION28
,P_RT_END_DT_CD => r_PGM.INFORMATION48
,P_RT_END_DT_RL => l_RT_END_DT_RL
,P_RT_STRT_DT_CD => r_PGM.INFORMATION47
,P_RT_STRT_DT_RL => l_RT_STRT_DT_RL
,P_SCORES_CALC_MTHD_CD => r_PGM.INFORMATION15
,P_SCORES_CALC_RL => l_SCORES_CALC_RL
,P_SHORT_CODE => r_PGM.INFORMATION11
,P_SHORT_NAME => r_PGM.INFORMATION12
,P_TRK_INELIG_PER_FLAG => r_PGM.INFORMATION35
,P_UPDATE_SALARY_CD => r_PGM.INFORMATION16
,P_URL_REF_NAME => r_PGM.INFORMATION185
,P_USES_ALL_ASMTS_FOR_RTS_FLAG => r_PGM.INFORMATION27
,P_USE_MULTI_PAY_RATES_FLAG => NVL(r_PGM.INFORMATION17,'N')
,P_USE_PROG_POINTS_FLAG => NVL(r_PGM.INFORMATION18,'N')
,P_USE_SCORES_CD => r_PGM.INFORMATION19
,P_VRFY_FMLY_MMBR_CD => r_PGM.INFORMATION54
,P_VRFY_FMLY_MMBR_RL => l_VRFY_FMLY_MMBR_RL
,P_USE_VARIABLE_RATES_FLAG => NVL(r_PGM.INFORMATION69,'N')
,P_SALARY_CALC_MTHD_CD => r_PGM.INFORMATION70
,P_GSP_ALLOW_OVERRIDE_FLAG => NVL(r_PGM.INFORMATION72,'N')
,P_SALARY_CALC_MTHD_RL => l_SALARY_CALC_MTHD_RL
--ML
,p_SUSP_IF_DPNT_SSN_NT_PRV_CD =>l_susp_if_dpnt_ssn_nt_prv_cd
,p_SUSP_IF_DPNT_DOB_NT_PRV_CD =>l_susp_if_dpnt_dob_nt_prv_cd
,p_SUSP_IF_DPNT_ADR_NT_PRV_CD =>l_susp_if_dpnt_adr_nt_prv_cd
,p_SUSP_IF_CTFN_NOT_DPNT_FLAG =>nvl(r_pgm.INFORMATION192,'Y')
,p_DPNT_CTFN_DETERMINE_CD =>r_pgm.INFORMATION193
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_PROGRAM_API.delete_PROGRAM(
--
p_validate => false
,p_pgm_id => l_pgm_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_REGN_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
REG.regn_id new_value
from BEN_REGN_F REG
where REG.name = c_REG_name
and REG.business_group_id = c_business_group_id
and REG.regn_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_REGN_F REG1
where REG1.name = c_REG_name
and REG1.business_group_id = c_business_group_id
and REG1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_REGN_F REG2
where REG2.name = c_REG_name
and REG2.business_group_id = c_business_group_id
and REG2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_REGN_F CREATE_REGULATIONS ',20);
hr_utility.set_location(' BEN_REGN_F UPDATE_REGULATIONS ',30);
BEN_REGULATIONS_API.UPDATE_REGULATIONS(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_NAME => l_prefix || r_REG.INFORMATION170 || l_suffix
,P_ORGANIZATION_ID => l_ORGANIZATION_ID
,P_REGN_ID => l_regn_id
,P_REG_ATTRIBUTE1 => r_REG.INFORMATION111
,P_REG_ATTRIBUTE10 => r_REG.INFORMATION120
,P_REG_ATTRIBUTE11 => r_REG.INFORMATION121
,P_REG_ATTRIBUTE12 => r_REG.INFORMATION122
,P_REG_ATTRIBUTE13 => r_REG.INFORMATION123
,P_REG_ATTRIBUTE14 => r_REG.INFORMATION124
,P_REG_ATTRIBUTE15 => r_REG.INFORMATION125
,P_REG_ATTRIBUTE16 => r_REG.INFORMATION126
,P_REG_ATTRIBUTE17 => r_REG.INFORMATION127
,P_REG_ATTRIBUTE18 => r_REG.INFORMATION128
,P_REG_ATTRIBUTE19 => r_REG.INFORMATION129
,P_REG_ATTRIBUTE2 => r_REG.INFORMATION112
,P_REG_ATTRIBUTE20 => r_REG.INFORMATION130
,P_REG_ATTRIBUTE21 => r_REG.INFORMATION131
,P_REG_ATTRIBUTE22 => r_REG.INFORMATION132
,P_REG_ATTRIBUTE23 => r_REG.INFORMATION133
,P_REG_ATTRIBUTE24 => r_REG.INFORMATION134
,P_REG_ATTRIBUTE25 => r_REG.INFORMATION135
,P_REG_ATTRIBUTE26 => r_REG.INFORMATION136
,P_REG_ATTRIBUTE27 => r_REG.INFORMATION137
,P_REG_ATTRIBUTE28 => r_REG.INFORMATION138
,P_REG_ATTRIBUTE29 => r_REG.INFORMATION139
,P_REG_ATTRIBUTE3 => r_REG.INFORMATION113
,P_REG_ATTRIBUTE30 => r_REG.INFORMATION140
,P_REG_ATTRIBUTE4 => r_REG.INFORMATION114
,P_REG_ATTRIBUTE5 => r_REG.INFORMATION115
,P_REG_ATTRIBUTE6 => r_REG.INFORMATION116
,P_REG_ATTRIBUTE7 => r_REG.INFORMATION117
,P_REG_ATTRIBUTE8 => r_REG.INFORMATION118
,P_REG_ATTRIBUTE9 => r_REG.INFORMATION119
,P_REG_ATTRIBUTE_CATEGORY => r_REG.INFORMATION110
,P_STTRY_CITN_NAME => r_REG.INFORMATION185
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_REGULATIONS_API.delete_REGULATIONS(
--
p_validate => false
,p_regn_id => l_regn_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_PSTL_ZIP_RNG_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945 --
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
RZR.pstl_zip_rng_id new_value
from BEN_PSTL_ZIP_RNG_F RZR
where
RZR.business_group_id = c_business_group_id
and RZR.from_value = r_RZR.information142 --r_RZR.from_value
and nvl(RZR.to_value,-999) = nvl(r_RZR.information141,-999) --r_RZR.to_value
and RZR.pstl_zip_rng_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_PSTL_ZIP_RNG_F RZR1
where RZR1.business_group_id = c_business_group_id
and RZR1.from_value = r_RZR.information142
and nvl(RZR1.to_value,-999) = nvl(r_RZR.information141,-999)
and RZR1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_PSTL_ZIP_RNG_F RZR2
where RZR2.business_group_id = c_business_group_id
and RZR2.from_value = r_RZR.information142
and nvl(RZR2.to_value,-999) = nvl(r_RZR.information141,-999)
and RZR2.effective_end_date >= c_effective_end_date ) ;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
/*
open c_RZR(r_RZR_unique.table_route_id,
r_RZR_unique.information1,
r_RZR_unique.information2,
r_RZR_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_PSTL_ZIP_RNG_F CREATE_POSTAL_ZIP_RANGE ',20);
if l_update then
--
l_datetrack_mode := r_RZR.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_PSTL_ZIP_RNG_F UPDATE_POSTAL_ZIP_RANGE ',30);
BEN_POSTAL_ZIP_RANGE_API.UPDATE_POSTAL_ZIP_RANGE(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_FROM_VALUE => r_RZR.INFORMATION142
,P_PSTL_ZIP_RNG_ID => l_pstl_zip_rng_id
,P_RZR_ATTRIBUTE1 => r_RZR.INFORMATION111
,P_RZR_ATTRIBUTE10 => r_RZR.INFORMATION120
,P_RZR_ATTRIBUTE11 => r_RZR.INFORMATION121
,P_RZR_ATTRIBUTE12 => r_RZR.INFORMATION122
,P_RZR_ATTRIBUTE13 => r_RZR.INFORMATION123
,P_RZR_ATTRIBUTE14 => r_RZR.INFORMATION124
,P_RZR_ATTRIBUTE15 => r_RZR.INFORMATION125
,P_RZR_ATTRIBUTE16 => r_RZR.INFORMATION126
,P_RZR_ATTRIBUTE17 => r_RZR.INFORMATION127
,P_RZR_ATTRIBUTE18 => r_RZR.INFORMATION128
,P_RZR_ATTRIBUTE19 => r_RZR.INFORMATION129
,P_RZR_ATTRIBUTE2 => r_RZR.INFORMATION112
,P_RZR_ATTRIBUTE20 => r_RZR.INFORMATION130
,P_RZR_ATTRIBUTE21 => r_RZR.INFORMATION131
,P_RZR_ATTRIBUTE22 => r_RZR.INFORMATION132
,P_RZR_ATTRIBUTE23 => r_RZR.INFORMATION133
,P_RZR_ATTRIBUTE24 => r_RZR.INFORMATION134
,P_RZR_ATTRIBUTE25 => r_RZR.INFORMATION135
,P_RZR_ATTRIBUTE26 => r_RZR.INFORMATION136
,P_RZR_ATTRIBUTE27 => r_RZR.INFORMATION137
,P_RZR_ATTRIBUTE28 => r_RZR.INFORMATION138
,P_RZR_ATTRIBUTE29 => r_RZR.INFORMATION139
,P_RZR_ATTRIBUTE3 => r_RZR.INFORMATION113
,P_RZR_ATTRIBUTE30 => r_RZR.INFORMATION140
,P_RZR_ATTRIBUTE4 => r_RZR.INFORMATION114
,P_RZR_ATTRIBUTE5 => r_RZR.INFORMATION115
,P_RZR_ATTRIBUTE6 => r_RZR.INFORMATION116
,P_RZR_ATTRIBUTE7 => r_RZR.INFORMATION117
,P_RZR_ATTRIBUTE8 => r_RZR.INFORMATION118
,P_RZR_ATTRIBUTE9 => r_RZR.INFORMATION119
,P_RZR_ATTRIBUTE_CATEGORY => r_RZR.INFORMATION110
,P_TO_VALUE => r_RZR.INFORMATION141
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_POSTAL_ZIP_RANGE_API.delete_POSTAL_ZIP_RANGE(
--
p_validate => false
,p_pstl_zip_rng_id => l_pstl_zip_rng_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_RPTG_GRP
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
BNR.rptg_grp_id new_value
from BEN_RPTG_GRP BNR
where BNR.name = c_BNR_name
and (BNR.business_group_id = c_business_group_id
or BNR.business_group_id is null) -- Bug 2907912
and BNR.rptg_grp_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target or l_update then
--
open c_BNR(r_BNR_unique.table_route_id,
r_BNR_unique.information1,
r_BNR_unique.information2,
r_BNR_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_RPTG_GRP CREATE_REPORTING_GROUP ',20);
elsif l_update then
hr_utility.set_location(' BEN_RPTG_GRP UPDATE_REPORTING_GROUP ',20);
BEN_REPORTING_GROUP_API.UPDATE_REPORTING_GROUP(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(ben_pd_copy_to_ben_one.g_copy_effective_date ,p_effective_date)
,P_BUSINESS_GROUP_ID => l_business_group_id
--
,P_BNR_ATTRIBUTE1 => r_BNR.INFORMATION111
,P_BNR_ATTRIBUTE10 => r_BNR.INFORMATION120
,P_BNR_ATTRIBUTE11 => r_BNR.INFORMATION121
,P_BNR_ATTRIBUTE12 => r_BNR.INFORMATION122
,P_BNR_ATTRIBUTE13 => r_BNR.INFORMATION123
,P_BNR_ATTRIBUTE14 => r_BNR.INFORMATION124
,P_BNR_ATTRIBUTE15 => r_BNR.INFORMATION125
,P_BNR_ATTRIBUTE16 => r_BNR.INFORMATION126
,P_BNR_ATTRIBUTE17 => r_BNR.INFORMATION127
,P_BNR_ATTRIBUTE18 => r_BNR.INFORMATION128
,P_BNR_ATTRIBUTE19 => r_BNR.INFORMATION129
,P_BNR_ATTRIBUTE2 => r_BNR.INFORMATION112
,P_BNR_ATTRIBUTE20 => r_BNR.INFORMATION130
,P_BNR_ATTRIBUTE21 => r_BNR.INFORMATION131
,P_BNR_ATTRIBUTE22 => r_BNR.INFORMATION132
,P_BNR_ATTRIBUTE23 => r_BNR.INFORMATION133
,P_BNR_ATTRIBUTE24 => r_BNR.INFORMATION134
,P_BNR_ATTRIBUTE25 => r_BNR.INFORMATION135
,P_BNR_ATTRIBUTE26 => r_BNR.INFORMATION136
,P_BNR_ATTRIBUTE27 => r_BNR.INFORMATION137
,P_BNR_ATTRIBUTE28 => r_BNR.INFORMATION138
,P_BNR_ATTRIBUTE29 => r_BNR.INFORMATION139
,P_BNR_ATTRIBUTE3 => r_BNR.INFORMATION113
,P_BNR_ATTRIBUTE30 => r_BNR.INFORMATION140
,P_BNR_ATTRIBUTE4 => r_BNR.INFORMATION114
,P_BNR_ATTRIBUTE5 => r_BNR.INFORMATION115
,P_BNR_ATTRIBUTE6 => r_BNR.INFORMATION116
,P_BNR_ATTRIBUTE7 => r_BNR.INFORMATION117
,P_BNR_ATTRIBUTE8 => r_BNR.INFORMATION118
,P_BNR_ATTRIBUTE9 => r_BNR.INFORMATION119
,P_BNR_ATTRIBUTE_CATEGORY => r_BNR.INFORMATION110
,P_FUNCTION_CODE => r_BNR.INFORMATION11
,P_LEGISLATION_CODE => r_BNR.INFORMATION12
,P_NAME => l_prefix || r_BNR.INFORMATION170 || l_suffix
,P_RPG_DESC => r_BNR.INFORMATION185
,P_RPTG_GRP_ID => l_rptg_grp_id
,P_RPTG_PRPS_CD => r_BNR.INFORMATION13
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION218 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_RLTD_PER_CHG_CS_LER_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION218, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
RCL.rltd_per_chg_cs_ler_id new_value
from BEN_RLTD_PER_CHG_CS_LER_F RCL
where RCL.name = c_RCL_name
and RCL.business_group_id = c_business_group_id
and RCL.rltd_per_chg_cs_ler_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_RLTD_PER_CHG_CS_LER_F RCL1
where RCL1.name = c_RCL_name
and RCL1.business_group_id = c_business_group_id
and RCL1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_RLTD_PER_CHG_CS_LER_F RCL2
where RCL2.name = c_RCL_name
and RCL2.business_group_id = c_business_group_id
and RCL2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
open c_RCL(r_RCL_unique.table_route_id,
r_RCL_unique.information1,
r_RCL_unique.information2,
r_RCL_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_RLTD_PER_CHG_CS_LER_F CREATE_RLTD_PER_CHG_CS_LER ',20);
if l_update then
--
l_datetrack_mode := r_RCL.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_RLTD_PER_CHG_CS_LER_F UPDATE_RLTD_PER_CHG_CS_LER ',30);
BEN_RLTD_PER_CHG_CS_LER_API.UPDATE_RLTD_PER_CHG_CS_LER(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_NAME => l_prefix || r_RCL.INFORMATION218 || l_suffix
,P_NEW_VAL => r_RCL.INFORMATION186
,P_OLD_VAL => r_RCL.INFORMATION185
,P_RCL_ATTRIBUTE1 => r_RCL.INFORMATION111
,P_RCL_ATTRIBUTE10 => r_RCL.INFORMATION120
,P_RCL_ATTRIBUTE11 => r_RCL.INFORMATION121
,P_RCL_ATTRIBUTE12 => r_RCL.INFORMATION122
,P_RCL_ATTRIBUTE13 => r_RCL.INFORMATION123
,P_RCL_ATTRIBUTE14 => r_RCL.INFORMATION124
,P_RCL_ATTRIBUTE15 => r_RCL.INFORMATION125
,P_RCL_ATTRIBUTE16 => r_RCL.INFORMATION126
,P_RCL_ATTRIBUTE17 => r_RCL.INFORMATION127
,P_RCL_ATTRIBUTE18 => r_RCL.INFORMATION128
,P_RCL_ATTRIBUTE19 => r_RCL.INFORMATION129
,P_RCL_ATTRIBUTE2 => r_RCL.INFORMATION112
,P_RCL_ATTRIBUTE20 => r_RCL.INFORMATION130
,P_RCL_ATTRIBUTE21 => r_RCL.INFORMATION131
,P_RCL_ATTRIBUTE22 => r_RCL.INFORMATION132
,P_RCL_ATTRIBUTE23 => r_RCL.INFORMATION133
,P_RCL_ATTRIBUTE24 => r_RCL.INFORMATION134
,P_RCL_ATTRIBUTE25 => r_RCL.INFORMATION135
,P_RCL_ATTRIBUTE26 => r_RCL.INFORMATION136
,P_RCL_ATTRIBUTE27 => r_RCL.INFORMATION137
,P_RCL_ATTRIBUTE28 => r_RCL.INFORMATION138
,P_RCL_ATTRIBUTE29 => r_RCL.INFORMATION139
,P_RCL_ATTRIBUTE3 => r_RCL.INFORMATION113
,P_RCL_ATTRIBUTE30 => r_RCL.INFORMATION140
,P_RCL_ATTRIBUTE4 => r_RCL.INFORMATION114
,P_RCL_ATTRIBUTE5 => r_RCL.INFORMATION115
,P_RCL_ATTRIBUTE6 => r_RCL.INFORMATION116
,P_RCL_ATTRIBUTE7 => r_RCL.INFORMATION117
,P_RCL_ATTRIBUTE8 => r_RCL.INFORMATION118
,P_RCL_ATTRIBUTE9 => r_RCL.INFORMATION119
,P_RCL_ATTRIBUTE_CATEGORY => r_RCL.INFORMATION110
,P_RLTD_PER_CHG_CS_LER_ID => l_rltd_per_chg_cs_ler_id
,P_RLTD_PER_CHG_CS_LER_RL => l_RLTD_PER_CHG_CS_LER_RL
,P_SOURCE_COLUMN => r_RCL.INFORMATION141
,P_SOURCE_TABLE => r_RCL.INFORMATION142
,P_WHATIF_LBL_TXT => r_RCL.INFORMATION219
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_RLTD_PER_CHG_CS_LER_API.delete_RLTD_PER_CHG_CS_LER(
--
p_validate => false
,p_rltd_per_chg_cs_ler_id => l_rltd_per_chg_cs_ler_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct decode(cpe.information264,cpe.information1,1,2), cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_OPT_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by decode(cpe.information264,cpe.information1,1,2),
cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170,
cpe.table_route_id,
cpe.dml_operation,
cpe.datetrack_mode
ORDER BY 1,2,3;
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
OPT.opt_id new_value
from BEN_OPT_F OPT
where OPT.name = c_OPT_name
and OPT.business_group_id = c_business_group_id
and OPT.opt_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_OPT_F OPT1
where OPT1.name = c_OPT_name
and OPT1.business_group_id = c_business_group_id
and OPT1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_OPT_F OPT2
where OPT2.name = c_OPT_name
and OPT2.business_group_id = c_business_group_id
and OPT2.effective_end_date >= c_effective_end_date )
;
select name, opt_id
from ben_opt_f
where name = p_grp_opt_name
and p_effective_date between effective_start_date and effective_end_date;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
open c_OPT(r_OPT_unique.table_route_id,
r_OPT_unique.information1,
r_OPT_unique.information2,
r_OPT_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_OPT_F CREATE_OPTION_DEFINITION ',20);
if l_update then
--
l_datetrack_mode := r_OPT.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_OPT_F UPDATE_OPTION_DEFINITION ',30);
BEN_OPTION_DEFINITION_API.UPDATE_OPTION_DEFINITION(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_CMBN_PTIP_OPT_ID => l_CMBN_PTIP_OPT_ID
,P_COMPONENT_REASON => r_OPT.INFORMATION13
,P_INVK_WV_OPT_FLAG => r_OPT.INFORMATION14
,P_MAPPING_TABLE_NAME => r_OPT.INFORMATION141
,P_MAPPING_TABLE_PK_ID => l_MAPPING_TABLE_PK_ID
,P_NAME => l_prefix || r_OPT.INFORMATION170 || l_suffix
,P_OPT_ATTRIBUTE1 => r_OPT.INFORMATION111
,P_OPT_ATTRIBUTE10 => r_OPT.INFORMATION120
,P_OPT_ATTRIBUTE11 => r_OPT.INFORMATION121
,P_OPT_ATTRIBUTE12 => r_OPT.INFORMATION122
,P_OPT_ATTRIBUTE13 => r_OPT.INFORMATION123
,P_OPT_ATTRIBUTE14 => r_OPT.INFORMATION124
,P_OPT_ATTRIBUTE15 => r_OPT.INFORMATION125
,P_OPT_ATTRIBUTE16 => r_OPT.INFORMATION126
,P_OPT_ATTRIBUTE17 => r_OPT.INFORMATION127
,P_OPT_ATTRIBUTE18 => r_OPT.INFORMATION128
,P_OPT_ATTRIBUTE19 => r_OPT.INFORMATION129
,P_OPT_ATTRIBUTE2 => r_OPT.INFORMATION112
,P_OPT_ATTRIBUTE20 => r_OPT.INFORMATION130
,P_OPT_ATTRIBUTE21 => r_OPT.INFORMATION131
,P_OPT_ATTRIBUTE22 => r_OPT.INFORMATION132
,P_OPT_ATTRIBUTE23 => r_OPT.INFORMATION133
,P_OPT_ATTRIBUTE24 => r_OPT.INFORMATION134
,P_OPT_ATTRIBUTE25 => r_OPT.INFORMATION135
,P_OPT_ATTRIBUTE26 => r_OPT.INFORMATION136
,P_OPT_ATTRIBUTE27 => r_OPT.INFORMATION137
,P_OPT_ATTRIBUTE28 => r_OPT.INFORMATION138
,P_OPT_ATTRIBUTE29 => r_OPT.INFORMATION139
,P_OPT_ATTRIBUTE3 => r_OPT.INFORMATION113
,P_OPT_ATTRIBUTE30 => r_OPT.INFORMATION140
,P_OPT_ATTRIBUTE4 => r_OPT.INFORMATION114
,P_OPT_ATTRIBUTE5 => r_OPT.INFORMATION115
,P_OPT_ATTRIBUTE6 => r_OPT.INFORMATION116
,P_OPT_ATTRIBUTE7 => r_OPT.INFORMATION117
,P_OPT_ATTRIBUTE8 => r_OPT.INFORMATION118
,P_OPT_ATTRIBUTE9 => r_OPT.INFORMATION119
,P_OPT_ATTRIBUTE_CATEGORY => r_OPT.INFORMATION110
,P_OPT_ID => l_opt_id
,P_RQD_PERD_ENRT_NENRT_RL => l_RQD_PERD_ENRT_NENRT_RL
,P_RQD_PERD_ENRT_NENRT_UOM => r_OPT.INFORMATION15
,P_RQD_PERD_ENRT_NENRT_VAL => r_OPT.INFORMATION259
-- Bug 3939490
,p_legislation_code => r_opt.information16
,p_legislation_subgroup => r_opt.information17
-- Bug 3939490
,P_SHORT_CODE => r_OPT.INFORMATION11
,P_SHORT_NAME => r_OPT.INFORMATION12
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
--cwbglobal -- cwb tilak
,P_GROUP_OPT_ID => r_OPT.INFORMATION264
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_OPTION_DEFINITION_API.delete_OPTION_DEFINITION(
--
p_validate => false
,p_opt_id => l_opt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.information16 typ_cd, -- Required for Absences
cpe.information13 lf_evt_oper_cd, -- Required for Absences
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_LER_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170,
cpe.information16, cpe.information13,cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
LER.ler_id new_value
from BEN_LER_F LER
where LER.name = c_LER_name
and LER.business_group_id = c_business_group_id
and LER.ler_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_LER_F LER1
where LER1.name = c_LER_name
and LER1.business_group_id = c_business_group_id
and LER1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_LER_F LER2
where LER2.name = c_LER_name
and LER2.business_group_id = c_business_group_id
and LER2.effective_end_date >= c_effective_end_date )
;
select
LER.ler_id new_value
from BEN_LER_F LER
where LER.typ_cd = c_LER_TYP_cd
-- 3508427: Including LE's of type Derived and Scheduled along with Abscences.
-- and LER.lf_evt_oper_cd = c_LF_EVT_OPER_cd
and ((LER.typ_cd = 'ABS' and LER.lf_evt_oper_cd = c_LF_EVT_OPER_cd )
or ( LER.typ_cd in ('DRVDAGE', 'DRVDLOS', 'DRVDCAL',
'DRVDHRW', 'DRVDCMP', 'DRVDTPF', 'SCHEDDO','SCHEDDA','SCHEDDU',
'QMSCOCO', 'QDROCOU', 'QUAINGR', 'GSP','IREC') )
)
-- 3508427 Ends ---------
and LER.business_group_id = c_business_group_id
and LER.ler_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_LER_F LER1
where LER1.typ_cd = c_LER_TYP_cd
and LER1.lf_evt_oper_cd = c_LF_EVT_OPER_cd
and LER1.ler_id = LER.ler_id
and LER1.business_group_id = c_business_group_id
and LER1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_LER_F LER2
where LER2.typ_cd = c_LER_TYP_cd
and LER2.lf_evt_oper_cd = c_LF_EVT_OPER_cd
and LER2.ler_id = LER.ler_id
and LER2.business_group_id = c_business_group_id
and LER2.effective_end_date >= c_effective_end_date )
;
select
LER.name
from BEN_LER_F LER
where LER.typ_cd = c_LER_TYP_cd
and LER.lf_evt_oper_cd = c_LF_EVT_OPER_cd
and LER.business_group_id = c_business_group_id
and LER.ler_id <> c_new_pk_id
and ((LER.effective_start_date between c_effective_start_date
and c_effective_end_date)
or
(LER.effective_end_date between c_effective_start_date
and c_effective_end_date))
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
open c_LER(r_LER_unique.table_route_id,
r_LER_unique.information1,
r_LER_unique.information2,
r_LER_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_LER_F CREATE_LIFE_EVENT_REASON ',20);
if l_update then
--
l_datetrack_mode := r_LER.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_LER_F UPDATE_LIFE_EVENT_REASON ',30);
BEN_LIFE_EVENT_REASON_API.UPDATE_LIFE_EVENT_REASON(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_CK_RLTD_PER_ELIG_FLAG => r_LER.INFORMATION22
,P_CM_APLY_FLAG => r_LER.INFORMATION23
,P_DESC_TXT => r_LER.INFORMATION219
,P_LER_ATTRIBUTE1 => r_LER.INFORMATION111
,P_LER_ATTRIBUTE10 => r_LER.INFORMATION120
,P_LER_ATTRIBUTE11 => r_LER.INFORMATION121
,P_LER_ATTRIBUTE12 => r_LER.INFORMATION122
,P_LER_ATTRIBUTE13 => r_LER.INFORMATION123
,P_LER_ATTRIBUTE14 => r_LER.INFORMATION124
,P_LER_ATTRIBUTE15 => r_LER.INFORMATION125
,P_LER_ATTRIBUTE16 => r_LER.INFORMATION126
,P_LER_ATTRIBUTE17 => r_LER.INFORMATION127
,P_LER_ATTRIBUTE18 => r_LER.INFORMATION128
,P_LER_ATTRIBUTE19 => r_LER.INFORMATION129
,P_LER_ATTRIBUTE2 => r_LER.INFORMATION112
,P_LER_ATTRIBUTE20 => r_LER.INFORMATION130
,P_LER_ATTRIBUTE21 => r_LER.INFORMATION131
,P_LER_ATTRIBUTE22 => r_LER.INFORMATION132
,P_LER_ATTRIBUTE23 => r_LER.INFORMATION133
,P_LER_ATTRIBUTE24 => r_LER.INFORMATION134
,P_LER_ATTRIBUTE25 => r_LER.INFORMATION135
,P_LER_ATTRIBUTE26 => r_LER.INFORMATION136
,P_LER_ATTRIBUTE27 => r_LER.INFORMATION137
,P_LER_ATTRIBUTE28 => r_LER.INFORMATION138
,P_LER_ATTRIBUTE29 => r_LER.INFORMATION139
,P_LER_ATTRIBUTE3 => r_LER.INFORMATION113
,P_LER_ATTRIBUTE30 => r_LER.INFORMATION140
,P_LER_ATTRIBUTE4 => r_LER.INFORMATION114
,P_LER_ATTRIBUTE5 => r_LER.INFORMATION115
,P_LER_ATTRIBUTE6 => r_LER.INFORMATION116
,P_LER_ATTRIBUTE7 => r_LER.INFORMATION117
,P_LER_ATTRIBUTE8 => r_LER.INFORMATION118
,P_LER_ATTRIBUTE9 => r_LER.INFORMATION119
,P_LER_ATTRIBUTE_CATEGORY => r_LER.INFORMATION110
,P_LER_EVAL_RL => l_LER_EVAL_RL
,P_LER_ID => l_ler_id
,P_LER_STAT_CD => r_LER.INFORMATION15
,P_LF_EVT_OPER_CD => r_LER.INFORMATION13
,P_NAME => l_prefix || r_LER.INFORMATION170 || l_suffix
,P_OCRD_DT_DET_CD => r_LER.INFORMATION21
,P_OVRIDG_LE_FLAG => r_LER.INFORMATION24
,P_PTNL_LER_TRTMT_CD => r_LER.INFORMATION17
,P_QUALG_EVT_FLAG => r_LER.INFORMATION25
,P_SS_PCP_DISP_CD => r_LER.INFORMATION26 --4301332
,P_SHORT_CODE => r_LER.INFORMATION11
,P_SHORT_NAME => r_LER.INFORMATION12
,P_SLCTBL_SLF_SVC_CD => r_LER.INFORMATION14
,P_TMLNS_DYS_NUM => r_LER.INFORMATION263
,P_TMLNS_EVAL_CD => r_LER.INFORMATION20
,P_TMLNS_PERD_CD => r_LER.INFORMATION19
,P_TMLNS_PERD_RL => l_TMLNS_PERD_RL
,P_TYP_CD => r_LER.INFORMATION16
,P_WHN_TO_PRCS_CD => r_LER.INFORMATION18
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_LIFE_EVENT_REASON_API.delete_LIFE_EVENT_REASON(
--
p_validate => false
,p_ler_id => l_ler_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cer.information1,
cer.information2,
cer.information3,
cer.table_route_id,cer.dml_operation,cer.datetrack_mode
from ben_copy_entity_results cer,
pqh_table_route tr
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and cer.table_route_id = tr.table_route_id
and tr.table_alias = l_table_alias
and cer.number_of_copies = 1
group by cer.information1,cer.information2,cer.information3, cer.table_route_id,cer.dml_operation,cer.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cer.information2) min_esd,
max(cer.information3) min_eed
from ben_copy_entity_results cer
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and cer.table_route_id = c_table_route_id
and cer.information1 = c_information1 ;
select
cer.*
from ben_copy_entity_results cer
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and cer.table_route_id = c_table_route_id
and cer.information1 = c_information1
and cer.information2 = c_information2
and cer.information3 = c_information3
and rownum = 1 ;
select
LPL.ler_per_info_cs_ler_id new_value
from BEN_LER_PER_INFO_CS_LER_F LPL
where
LPL.LER_ID = l_LER_ID and
LPL.PER_INFO_CHG_CS_LER_ID = l_PER_INFO_CHG_CS_LER_ID and
LPL.business_group_id = c_business_group_id
and LPL.ler_per_info_cs_ler_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_LER_PER_INFO_CS_LER_F LPL1
where
LPL1.LER_ID = l_LER_ID and
LPL1.PER_INFO_CHG_CS_LER_ID = l_PER_INFO_CHG_CS_LER_ID and
LPL1.business_group_id = c_business_group_id
and LPL1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_LER_PER_INFO_CS_LER_F LPL2
where
LPL2.LER_ID = l_LER_ID and
LPL2.PER_INFO_CHG_CS_LER_ID = l_PER_INFO_CHG_CS_LER_ID and
LPL2.business_group_id = c_business_group_id
and LPL2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
update_cer_with_target( BEN_PD_COPY_TO_BEN_ONE.g_pk_tbl(BEN_PD_COPY_TO_BEN_ONE.g_count) , p_copy_entity_txn_id) ;
if not l_object_found_in_target or l_update then
--
l_current_pk_id := r_LPL.information1;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_LER_PER_INFO_CS_LER_F CREATE_LER_PER_INFO_CS_LER ',20);
if l_update then
--
l_datetrack_mode := r_LPL.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_LER_PER_INFO_CS_LER_F UPDATE_LER_PER_INFO_CS_LER ',30);
BEN_LER_PER_INFO_CS_LER_API.UPDATE_LER_PER_INFO_CS_LER(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_LER_ID => l_LER_ID
,P_LER_PER_INFO_CS_LER_ID => l_ler_per_info_cs_ler_id
,P_LER_PER_INFO_CS_LER_RL => l_LER_PER_INFO_CS_LER_RL
,P_LPL_ATTRIBUTE1 => r_LPL.information14
,P_LPL_ATTRIBUTE10 => r_LPL.information15
,P_LPL_ATTRIBUTE11 => r_LPL.information16
,P_LPL_ATTRIBUTE12 => r_LPL.information17
,P_LPL_ATTRIBUTE13 => r_LPL.information18
,P_LPL_ATTRIBUTE14 => r_LPL.information19
,P_LPL_ATTRIBUTE15 => r_LPL.information20
,P_LPL_ATTRIBUTE16 => r_LPL.information21
,P_LPL_ATTRIBUTE17 => r_LPL.information22
,P_LPL_ATTRIBUTE18 => r_LPL.information23
,P_LPL_ATTRIBUTE19 => r_LPL.information24
,P_LPL_ATTRIBUTE2 => r_LPL.information25
,P_LPL_ATTRIBUTE20 => r_LPL.information26
,P_LPL_ATTRIBUTE21 => r_LPL.information27
,P_LPL_ATTRIBUTE22 => r_LPL.information28
,P_LPL_ATTRIBUTE23 => r_LPL.information29
,P_LPL_ATTRIBUTE24 => r_LPL.information30
,P_LPL_ATTRIBUTE25 => r_LPL.information31
,P_LPL_ATTRIBUTE26 => r_LPL.information32
,P_LPL_ATTRIBUTE27 => r_LPL.information33
,P_LPL_ATTRIBUTE28 => r_LPL.information34
,P_LPL_ATTRIBUTE29 => r_LPL.information35
,P_LPL_ATTRIBUTE3 => r_LPL.information36
,P_LPL_ATTRIBUTE30 => r_LPL.information37
,P_LPL_ATTRIBUTE4 => r_LPL.information38
,P_LPL_ATTRIBUTE5 => r_LPL.information39
,P_LPL_ATTRIBUTE6 => r_LPL.information40
,P_LPL_ATTRIBUTE7 => r_LPL.information41
,P_LPL_ATTRIBUTE8 => r_LPL.information42
,P_LPL_ATTRIBUTE9 => r_LPL.information43
,P_LPL_ATTRIBUTE_CATEGORY => r_LPL.information44
,P_PER_INFO_CHG_CS_LER_ID => l_PER_INFO_CHG_CS_LER_ID
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_LER_PER_INFO_CS_LER_API.delete_LER_PER_INFO_CS_LER(
--
p_validate => false
,p_ler_per_info_cs_ler_id => l_ler_per_info_cs_ler_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_ELIGY_PRFL_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3,
cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
ELP.eligy_prfl_id new_value
from BEN_ELIGY_PRFL_F ELP
where ELP.name = c_ELP_name
and ELP.business_group_id = c_business_group_id
and ELP.eligy_prfl_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_ELIGY_PRFL_F ELP1
where ELP1.name = c_ELP_name
and ELP1.business_group_id = c_business_group_id
and ELP1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_ELIGY_PRFL_F ELP2
where ELP2.name = c_ELP_name
and ELP2.business_group_id = c_business_group_id
and ELP2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
open c_ELP(r_ELP_unique.table_route_id,
r_ELP_unique.information1,
r_ELP_unique.information2,
r_ELP_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_ELIGY_PRFL_F CREATE_ELIGY_PROFILE ',20);
if l_update then
--
l_datetrack_mode := r_ELP.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_ELIGY_PRFL_F UPDATE_ELIGY_PROFILE ',30);
BEN_ELIGY_PROFILE_API.UPDATE_ELIGY_PROFILE(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
-- ,p_asg_typ_cd => r_elp.information11
,P_ASMT_TO_USE_CD => r_ELP.INFORMATION76
,P_BNFT_CAGR_PRTN_CD => r_ELP.INFORMATION20
,P_CNTNG_PRTN_ELIG_PRFL_FLAG => r_ELP.INFORMATION62
,P_DESCRIPTION => r_ELP.INFORMATION219
,P_ELIGY_PRFL_ID => l_eligy_prfl_id
,P_ELIGY_PRFL_RL_FLAG => r_ELP.INFORMATION60
,P_ELIG_AGE_FLAG => r_ELP.INFORMATION45
,P_ELIG_ANTHR_PL_FLAG => r_ELP.INFORMATION16
,P_ELIG_ASNT_SET_FLAG => r_ELP.INFORMATION52
,P_ELIG_BENFTS_GRP_FLAG => r_ELP.INFORMATION42
,P_ELIG_BRGNG_UNIT_FLAG => r_ELP.INFORMATION44
,P_ELIG_CBR_QUALD_BNF_FLAG => r_ELP.INFORMATION32
,P_ELIG_CMBN_AGE_LOS_FLAG => r_ELP.INFORMATION61
,P_ELIG_COMPTNCY_FLAG => r_ELP.INFORMATION14
,P_ELIG_COMP_LVL_FLAG => r_ELP.INFORMATION54
,P_ELIG_DPNT_CVRD_PGM_FLAG => r_ELP.INFORMATION36
,P_ELIG_DPNT_CVRD_PLIP_FLAG => r_ELP.INFORMATION34
,P_ELIG_DPNT_CVRD_PL_FLAG => r_ELP.INFORMATION71
,P_ELIG_DPNT_CVRD_PTIP_FLAG => r_ELP.INFORMATION35
,P_ELIG_DPNT_OTHR_PTIP_FLAG => r_ELP.INFORMATION77
,P_ELIG_DSBLD_FLAG => r_ELP.INFORMATION11
,P_ELIG_DSBLTY_CTG_FLAG => r_ELP.INFORMATION25
,P_ELIG_DSBLTY_DGR_FLAG => r_ELP.INFORMATION26
,P_ELIG_DSBLTY_RSN_FLAG => r_ELP.INFORMATION27
,P_ELIG_EE_STAT_FLAG => r_ELP.INFORMATION49
,P_ELIG_ENRLD_OIPL_FLAG => r_ELP.INFORMATION69
,P_ELIG_ENRLD_PGM_FLAG => r_ELP.INFORMATION70
,P_ELIG_ENRLD_PLIP_FLAG => r_ELP.INFORMATION31
,P_ELIG_ENRLD_PL_FLAG => r_ELP.INFORMATION68
,P_ELIG_ENRLD_PTIP_FLAG => r_ELP.INFORMATION33
,P_ELIG_FL_TM_PT_TM_FLAG => r_ELP.INFORMATION48
,P_ELIG_GNDR_FLAG => r_ELP.INFORMATION24
,P_ELIG_GRD_FLAG => r_ELP.INFORMATION50
,P_ELIG_HLTH_CVG_FLAG => r_ELP.INFORMATION15
,P_ELIG_HRLY_SLRD_FLAG => r_ELP.INFORMATION38
,P_ELIG_HRS_WKD_FLAG => r_ELP.INFORMATION53
,P_ELIG_JOB_FLAG => r_ELP.INFORMATION37
,P_ELIG_LBR_MMBR_FLAG => r_ELP.INFORMATION40
,P_ELIG_LGL_ENTY_FLAG => r_ELP.INFORMATION41
,P_ELIG_LOA_RSN_FLAG => r_ELP.INFORMATION56
,P_ELIG_LOS_FLAG => r_ELP.INFORMATION46
,P_ELIG_LVG_RSN_FLAG => r_ELP.INFORMATION72
,P_ELIG_MRTL_STS_FLAG => r_ELP.INFORMATION28
,P_ELIG_NO_OTHR_CVG_FLAG => r_ELP.INFORMATION67
,P_ELIG_OPTD_MDCR_FLAG => r_ELP.INFORMATION73
,P_ELIG_ORG_UNIT_FLAG => r_ELP.INFORMATION55
,P_ELIG_PCT_FL_TM_FLAG => r_ELP.INFORMATION51
,P_ELIG_PERF_RTNG_FLAG => r_ELP.INFORMATION17
,P_ELIG_PER_TYP_FLAG => r_ELP.INFORMATION47
,P_ELIG_PPL_GRP_FLAG => r_ELP.INFORMATION64
,P_ELIG_PRBTN_PERD_FLAG => r_ELP.INFORMATION29
,P_ELIG_PRTT_PL_FLAG => r_ELP.INFORMATION63
,P_ELIG_PSTL_CD_FLAG => r_ELP.INFORMATION39
,P_ELIG_PSTN_FLAG => r_ELP.INFORMATION19
,P_ELIG_PTIP_PRTE_FLAG => r_ELP.INFORMATION66
,P_ELIG_PYRL_FLAG => r_ELP.INFORMATION57
,P_ELIG_PY_BSS_FLAG => r_ELP.INFORMATION59
,P_ELIG_QUAL_TITL_FLAG => r_ELP.INFORMATION21
,P_ELIG_QUA_IN_GR_FLAG => r_ELP.INFORMATION18
,P_ELIG_SCHEDD_HRS_FLAG => r_ELP.INFORMATION58
,P_ELIG_SP_CLNG_PRG_PT_FLAG => r_ELP.INFORMATION22
,P_ELIG_SUPPL_ROLE_FLAG => r_ELP.INFORMATION23
,P_ELIG_SVC_AREA_FLAG => r_ELP.INFORMATION65
,P_ELIG_TBCO_USE_FLAG => r_ELP.INFORMATION74
,P_ELIG_TTL_CVG_VOL_FLAG => r_ELP.INFORMATION12
,P_ELIG_TTL_PRTT_FLAG => r_ELP.INFORMATION13
,P_ELIG_WK_LOC_FLAG => r_ELP.INFORMATION43
,p_ELIG_CRIT_VALUES_FLAG => nvl(r_ELP.INFORMATION78, 'N') /* Bug 4169120 : Rate By Criteria */
,P_ELP_ATTRIBUTE1 => r_ELP.INFORMATION111
,P_ELP_ATTRIBUTE10 => r_ELP.INFORMATION120
,P_ELP_ATTRIBUTE11 => r_ELP.INFORMATION121
,P_ELP_ATTRIBUTE12 => r_ELP.INFORMATION122
,P_ELP_ATTRIBUTE13 => r_ELP.INFORMATION123
,P_ELP_ATTRIBUTE14 => r_ELP.INFORMATION124
,P_ELP_ATTRIBUTE15 => r_ELP.INFORMATION125
,P_ELP_ATTRIBUTE16 => r_ELP.INFORMATION126
,P_ELP_ATTRIBUTE17 => r_ELP.INFORMATION127
,P_ELP_ATTRIBUTE18 => r_ELP.INFORMATION128
,P_ELP_ATTRIBUTE19 => r_ELP.INFORMATION129
,P_ELP_ATTRIBUTE2 => r_ELP.INFORMATION112
,P_ELP_ATTRIBUTE20 => r_ELP.INFORMATION130
,P_ELP_ATTRIBUTE21 => r_ELP.INFORMATION131
,P_ELP_ATTRIBUTE22 => r_ELP.INFORMATION132
,P_ELP_ATTRIBUTE23 => r_ELP.INFORMATION133
,P_ELP_ATTRIBUTE24 => r_ELP.INFORMATION134
,P_ELP_ATTRIBUTE25 => r_ELP.INFORMATION135
,P_ELP_ATTRIBUTE26 => r_ELP.INFORMATION136
,P_ELP_ATTRIBUTE27 => r_ELP.INFORMATION137
,P_ELP_ATTRIBUTE28 => r_ELP.INFORMATION138
,P_ELP_ATTRIBUTE29 => r_ELP.INFORMATION139
,P_ELP_ATTRIBUTE3 => r_ELP.INFORMATION113
,P_ELP_ATTRIBUTE30 => r_ELP.INFORMATION140
,P_ELP_ATTRIBUTE4 => r_ELP.INFORMATION114
,P_ELP_ATTRIBUTE5 => r_ELP.INFORMATION115
,P_ELP_ATTRIBUTE6 => r_ELP.INFORMATION116
,P_ELP_ATTRIBUTE7 => r_ELP.INFORMATION117
,P_ELP_ATTRIBUTE8 => r_ELP.INFORMATION118
,P_ELP_ATTRIBUTE9 => r_ELP.INFORMATION119
,P_ELP_ATTRIBUTE_CATEGORY => r_ELP.INFORMATION110
,P_NAME => l_prefix || r_ELP.INFORMATION170 || l_suffix
,P_STAT_CD => r_ELP.INFORMATION30
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_ELIGY_PROFILE_API.delete_ELIGY_PROFILE(
--
p_validate => false
,p_eligy_prfl_id => l_eligy_prfl_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_DPNT_CVG_ELIGY_PRFL_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
DCE.dpnt_cvg_eligy_prfl_id new_value
from BEN_DPNT_CVG_ELIGY_PRFL_F DCE
where DCE.name = c_DCE_name
and DCE.business_group_id = c_business_group_id
and DCE.dpnt_cvg_eligy_prfl_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_DPNT_CVG_ELIGY_PRFL_F DCE1
where DCE1.name = c_DCE_name
and DCE1.business_group_id = c_business_group_id
and DCE1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_DPNT_CVG_ELIGY_PRFL_F DCE2
where DCE2.name = c_DCE_name
and DCE2.business_group_id = c_business_group_id
and DCE2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
open c_DCE(r_DCE_unique.table_route_id,
r_DCE_unique.information1,
r_DCE_unique.information2,
r_DCE_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_DPNT_CVG_ELIGY_PRFL_F CREATE_DPNT_CVG_ELIG_PRFL ',20);
if l_update then
--
l_datetrack_mode := r_DCE.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_DPNT_CVG_ELIGY_PRFL_F UPDATE_DPNT_CVG_ELIG_PRFL ',30);
BEN_DPNT_CVG_ELIG_PRFL_API.UPDATE_DPNT_CVG_ELIG_PRFL(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_DCE_ATTRIBUTE1 => r_DCE.INFORMATION111
,P_DCE_ATTRIBUTE10 => r_DCE.INFORMATION120
,P_DCE_ATTRIBUTE11 => r_DCE.INFORMATION121
,P_DCE_ATTRIBUTE12 => r_DCE.INFORMATION122
,P_DCE_ATTRIBUTE13 => r_DCE.INFORMATION123
,P_DCE_ATTRIBUTE14 => r_DCE.INFORMATION124
,P_DCE_ATTRIBUTE15 => r_DCE.INFORMATION125
,P_DCE_ATTRIBUTE16 => r_DCE.INFORMATION126
,P_DCE_ATTRIBUTE17 => r_DCE.INFORMATION127
,P_DCE_ATTRIBUTE18 => r_DCE.INFORMATION128
,P_DCE_ATTRIBUTE19 => r_DCE.INFORMATION129
,P_DCE_ATTRIBUTE2 => r_DCE.INFORMATION112
,P_DCE_ATTRIBUTE20 => r_DCE.INFORMATION130
,P_DCE_ATTRIBUTE21 => r_DCE.INFORMATION131
,P_DCE_ATTRIBUTE22 => r_DCE.INFORMATION132
,P_DCE_ATTRIBUTE23 => r_DCE.INFORMATION133
,P_DCE_ATTRIBUTE24 => r_DCE.INFORMATION134
,P_DCE_ATTRIBUTE25 => r_DCE.INFORMATION135
,P_DCE_ATTRIBUTE26 => r_DCE.INFORMATION136
,P_DCE_ATTRIBUTE27 => r_DCE.INFORMATION137
,P_DCE_ATTRIBUTE28 => r_DCE.INFORMATION138
,P_DCE_ATTRIBUTE29 => r_DCE.INFORMATION139
,P_DCE_ATTRIBUTE3 => r_DCE.INFORMATION113
,P_DCE_ATTRIBUTE30 => r_DCE.INFORMATION140
,P_DCE_ATTRIBUTE4 => r_DCE.INFORMATION114
,P_DCE_ATTRIBUTE5 => r_DCE.INFORMATION115
,P_DCE_ATTRIBUTE6 => r_DCE.INFORMATION116
,P_DCE_ATTRIBUTE7 => r_DCE.INFORMATION117
,P_DCE_ATTRIBUTE8 => r_DCE.INFORMATION118
,P_DCE_ATTRIBUTE9 => r_DCE.INFORMATION119
,P_DCE_ATTRIBUTE_CATEGORY => r_DCE.INFORMATION110
,P_DCE_DESC => r_DCE.INFORMATION185
,P_DPNT_AGE_FLAG => r_DCE.INFORMATION13
,P_DPNT_CVG_ELIGY_PRFL_ID => l_dpnt_cvg_eligy_prfl_id
,P_DPNT_CVG_ELIGY_PRFL_STAT_CD => r_DCE.INFORMATION11
,P_DPNT_CVG_ELIG_DET_RL => l_DPNT_CVG_ELIG_DET_RL
,P_DPNT_CVRD_IN_ANTHR_PL_FLAG => r_DCE.INFORMATION19
,P_DPNT_DSBLD_FLAG => r_DCE.INFORMATION15
,P_DPNT_DSGNT_CRNTLY_ENRLD_FLAG => r_DCE.INFORMATION20
,P_DPNT_MLTRY_FLAG => r_DCE.INFORMATION17
,P_DPNT_MRTL_FLAG => r_DCE.INFORMATION16
,P_DPNT_PSTL_FLAG => r_DCE.INFORMATION18
,P_DPNT_RLSHP_FLAG => r_DCE.INFORMATION12
,P_DPNT_STUD_FLAG => r_DCE.INFORMATION14
,P_NAME => l_prefix || r_DCE.INFORMATION170 || l_suffix
,P_REGN_ID => l_REGN_ID
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_DPNT_CVG_ELIG_PRFL_API.delete_DPNT_CVG_ELIG_PRFL(
--
p_validate => false
,p_dpnt_cvg_eligy_prfl_id => l_dpnt_cvg_eligy_prfl_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_GD_OR_SVC_TYP
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3,
cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
GOS.gd_or_svc_typ_id new_value
from BEN_GD_OR_SVC_TYP GOS
where GOS.name = c_GOS_name
and GOS.business_group_id = c_business_group_id
and GOS.gd_or_svc_typ_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target or l_update then
--
open c_GOS(r_GOS_unique.table_route_id,
r_GOS_unique.information1,
r_GOS_unique.information2,
r_GOS_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_GD_OR_SVC_TYP CREATE_GOOD_SVC_TYPE ',20);
elsif l_update then
--
hr_utility.set_location(' BEN_GD_OR_SVC_TYP UPDATE_GOOD_SVC_TYPE ',20);
BEN_GOOD_SVC_TYPE_API.UPDATE_GOOD_SVC_TYPE(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(ben_pd_copy_to_ben_one.g_copy_effective_date ,p_effective_date)
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_DESCRIPTION => r_GOS.INFORMATION185
,P_GD_OR_SVC_TYP_ID => l_gd_or_svc_typ_id
,P_GOS_ATTRIBUTE1 => r_GOS.INFORMATION111
,P_GOS_ATTRIBUTE10 => r_GOS.INFORMATION120
,P_GOS_ATTRIBUTE11 => r_GOS.INFORMATION121
,P_GOS_ATTRIBUTE12 => r_GOS.INFORMATION122
,P_GOS_ATTRIBUTE13 => r_GOS.INFORMATION123
,P_GOS_ATTRIBUTE14 => r_GOS.INFORMATION124
,P_GOS_ATTRIBUTE15 => r_GOS.INFORMATION125
,P_GOS_ATTRIBUTE16 => r_GOS.INFORMATION126
,P_GOS_ATTRIBUTE17 => r_GOS.INFORMATION127
,P_GOS_ATTRIBUTE18 => r_GOS.INFORMATION128
,P_GOS_ATTRIBUTE19 => r_GOS.INFORMATION129
,P_GOS_ATTRIBUTE2 => r_GOS.INFORMATION112
,P_GOS_ATTRIBUTE20 => r_GOS.INFORMATION130
,P_GOS_ATTRIBUTE21 => r_GOS.INFORMATION131
,P_GOS_ATTRIBUTE22 => r_GOS.INFORMATION132
,P_GOS_ATTRIBUTE23 => r_GOS.INFORMATION133
,P_GOS_ATTRIBUTE24 => r_GOS.INFORMATION134
,P_GOS_ATTRIBUTE25 => r_GOS.INFORMATION135
,P_GOS_ATTRIBUTE26 => r_GOS.INFORMATION136
,P_GOS_ATTRIBUTE27 => r_GOS.INFORMATION137
,P_GOS_ATTRIBUTE28 => r_GOS.INFORMATION138
,P_GOS_ATTRIBUTE29 => r_GOS.INFORMATION139
,P_GOS_ATTRIBUTE3 => r_GOS.INFORMATION113
,P_GOS_ATTRIBUTE30 => r_GOS.INFORMATION140
,P_GOS_ATTRIBUTE4 => r_GOS.INFORMATION114
,P_GOS_ATTRIBUTE5 => r_GOS.INFORMATION115
,P_GOS_ATTRIBUTE6 => r_GOS.INFORMATION116
,P_GOS_ATTRIBUTE7 => r_GOS.INFORMATION117
,P_GOS_ATTRIBUTE8 => r_GOS.INFORMATION118
,P_GOS_ATTRIBUTE9 => r_GOS.INFORMATION119
,P_GOS_ATTRIBUTE_CATEGORY => r_GOS.INFORMATION110
,P_NAME => l_prefix || r_GOS.INFORMATION170 || l_suffix
,P_TYP_CD => r_GOS.INFORMATION11
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_BENFTS_GRP
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3,
cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
BNG.benfts_grp_id new_value
from BEN_BENFTS_GRP BNG
where BNG.name = c_BNG_name
and BNG.business_group_id = c_business_group_id
and BNG.benfts_grp_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target or l_update then
--
open c_BNG(r_BNG_unique.table_route_id,
r_BNG_unique.information1,
r_BNG_unique.information2,
r_BNG_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_BENFTS_GRP CREATE_BENEFITS_GROUP ',20);
elsif l_update then
hr_utility.set_location(' BEN_BENFTS_GRP UPDATE_BENEFITS_GROUP ',20);
BEN_BENEFITS_GROUP_API.UPDATE_BENEFITS_GROUP(
--
P_VALIDATE => false
-- ,P_EFFECTIVE_DATE => p_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_BENFTS_GRP_ID => l_benfts_grp_id
,P_BNG_ATTRIBUTE1 => r_BNG.INFORMATION111
,P_BNG_ATTRIBUTE10 => r_BNG.INFORMATION120
,P_BNG_ATTRIBUTE11 => r_BNG.INFORMATION121
,P_BNG_ATTRIBUTE12 => r_BNG.INFORMATION122
,P_BNG_ATTRIBUTE13 => r_BNG.INFORMATION123
,P_BNG_ATTRIBUTE14 => r_BNG.INFORMATION124
,P_BNG_ATTRIBUTE15 => r_BNG.INFORMATION125
,P_BNG_ATTRIBUTE16 => r_BNG.INFORMATION126
,P_BNG_ATTRIBUTE17 => r_BNG.INFORMATION127
,P_BNG_ATTRIBUTE18 => r_BNG.INFORMATION128
,P_BNG_ATTRIBUTE19 => r_BNG.INFORMATION129
,P_BNG_ATTRIBUTE2 => r_BNG.INFORMATION112
,P_BNG_ATTRIBUTE20 => r_BNG.INFORMATION130
,P_BNG_ATTRIBUTE21 => r_BNG.INFORMATION131
,P_BNG_ATTRIBUTE22 => r_BNG.INFORMATION132
,P_BNG_ATTRIBUTE23 => r_BNG.INFORMATION133
,P_BNG_ATTRIBUTE24 => r_BNG.INFORMATION134
,P_BNG_ATTRIBUTE25 => r_BNG.INFORMATION135
,P_BNG_ATTRIBUTE26 => r_BNG.INFORMATION136
,P_BNG_ATTRIBUTE27 => r_BNG.INFORMATION137
,P_BNG_ATTRIBUTE28 => r_BNG.INFORMATION138
,P_BNG_ATTRIBUTE29 => r_BNG.INFORMATION139
,P_BNG_ATTRIBUTE3 => r_BNG.INFORMATION113
,P_BNG_ATTRIBUTE30 => r_BNG.INFORMATION140
,P_BNG_ATTRIBUTE4 => r_BNG.INFORMATION114
,P_BNG_ATTRIBUTE5 => r_BNG.INFORMATION115
,P_BNG_ATTRIBUTE6 => r_BNG.INFORMATION116
,P_BNG_ATTRIBUTE7 => r_BNG.INFORMATION117
,P_BNG_ATTRIBUTE8 => r_BNG.INFORMATION118
,P_BNG_ATTRIBUTE9 => r_BNG.INFORMATION119
,P_BNG_ATTRIBUTE_CATEGORY => r_BNG.INFORMATION110
,P_BNG_DESC => r_BNG.INFORMATION185
,P_NAME => l_prefix || r_BNG.INFORMATION170 || l_suffix
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION218 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_PER_INFO_CHG_CS_LER_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION218, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
PSL.per_info_chg_cs_ler_id new_value
from BEN_PER_INFO_CHG_CS_LER_F PSL
where ( PSL.name = c_PSL_name OR
( PSL.source_table = l_source_table
and PSL.source_column = l_source_column
and PSL.new_val = nvl(l_new_val,hr_api.g_varchar2)
and PSL.old_val = nvl(l_old_val,hr_api.g_varchar2) ))
and PSL.business_group_id = c_business_group_id
and PSL.per_info_chg_cs_ler_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_PER_INFO_CHG_CS_LER_F PSL1
where (PSL1.name = c_PSL_name OR
( PSL1.source_table = l_source_table
and PSL1.source_column = l_source_column
--and PSL1.new_val = nvl(l_new_val,hr_api.g_varchar2)
--and PSL1.old_val = nvl(l_old_val,hr_api.g_varchar2)
))
and PSL1.business_group_id = c_business_group_id
and PSL1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_PER_INFO_CHG_CS_LER_F PSL2
where ( PSL2.name = c_PSL_name OR
( PSL2.source_table = l_source_table
and PSL2.source_column = l_source_column
--and PSL2.new_val = nvl(l_new_val,hr_api.g_varchar2)
--and PSL2.old_val = nvl(l_old_val,hr_api.g_varchar2)
))
and PSL2.business_group_id = c_business_group_id
and PSL2.effective_end_date >= c_effective_end_date )
;
SELECT hl.lookup_code
FROM hr_lookups hl,
per_abs_attendance_reasons abs
WHERE hl.lookup_type = 'ABSENCE_REASON'
AND hl.lookup_code = abs.name
AND hl.lookup_code = cv_lookup_code
AND hl.enabled_flag = 'Y'
AND abs.business_group_id = p_target_business_group_id
AND cv_effective_date between nvl(start_date_active,cv_effective_date)
and nvl(end_date_active,cv_effective_date);
SELECT absence_attendance_type_id
from PER_ABSENCE_ATTENDANCE_TYPES
WHERE business_group_id = p_target_business_group_id
and name = cv_val;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
l_current_pk_id := r_PSL.information1;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_PER_INFO_CHG_CS_LER_F CREATE_PERSON_CHANGE_CS_LER ',20);
if l_update then
--
l_datetrack_mode := r_PSL.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_PER_INFO_CHG_CS_LER_F UPDATE_PERSON_CHANGE_CS_LER ',30);
BEN_PERSON_CHANGE_CS_LER_API.UPDATE_PERSON_CHANGE_CS_LER(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_NAME => l_prefix || r_PSL.INFORMATION218 || l_suffix
--
-- Bug No: 3907710
--
,P_RULE_OVERRIDES_FLAG => r_PSL.INFORMATION11
--
,P_NEW_VAL => l_abs_new_name
,P_OLD_VAL => l_abs_old_name
,P_PER_INFO_CHG_CS_LER_ID => l_per_info_chg_cs_ler_id
,P_PER_INFO_CHG_CS_LER_RL => l_PER_INFO_CHG_CS_LER_RL
,P_PSL_ATTRIBUTE1 => r_PSL.INFORMATION111
,P_PSL_ATTRIBUTE10 => r_PSL.INFORMATION120
,P_PSL_ATTRIBUTE11 => r_PSL.INFORMATION121
,P_PSL_ATTRIBUTE12 => r_PSL.INFORMATION122
,P_PSL_ATTRIBUTE13 => r_PSL.INFORMATION123
,P_PSL_ATTRIBUTE14 => r_PSL.INFORMATION124
,P_PSL_ATTRIBUTE15 => r_PSL.INFORMATION125
,P_PSL_ATTRIBUTE16 => r_PSL.INFORMATION126
,P_PSL_ATTRIBUTE17 => r_PSL.INFORMATION127
,P_PSL_ATTRIBUTE18 => r_PSL.INFORMATION128
,P_PSL_ATTRIBUTE19 => r_PSL.INFORMATION129
,P_PSL_ATTRIBUTE2 => r_PSL.INFORMATION112
,P_PSL_ATTRIBUTE20 => r_PSL.INFORMATION130
,P_PSL_ATTRIBUTE21 => r_PSL.INFORMATION131
,P_PSL_ATTRIBUTE22 => r_PSL.INFORMATION132
,P_PSL_ATTRIBUTE23 => r_PSL.INFORMATION133
,P_PSL_ATTRIBUTE24 => r_PSL.INFORMATION134
,P_PSL_ATTRIBUTE25 => r_PSL.INFORMATION135
,P_PSL_ATTRIBUTE26 => r_PSL.INFORMATION136
,P_PSL_ATTRIBUTE27 => r_PSL.INFORMATION137
,P_PSL_ATTRIBUTE28 => r_PSL.INFORMATION138
,P_PSL_ATTRIBUTE29 => r_PSL.INFORMATION139
,P_PSL_ATTRIBUTE3 => r_PSL.INFORMATION113
,P_PSL_ATTRIBUTE30 => r_PSL.INFORMATION140
,P_PSL_ATTRIBUTE4 => r_PSL.INFORMATION114
,P_PSL_ATTRIBUTE5 => r_PSL.INFORMATION115
,P_PSL_ATTRIBUTE6 => r_PSL.INFORMATION116
,P_PSL_ATTRIBUTE7 => r_PSL.INFORMATION117
,P_PSL_ATTRIBUTE8 => r_PSL.INFORMATION118
,P_PSL_ATTRIBUTE9 => r_PSL.INFORMATION119
,P_PSL_ATTRIBUTE_CATEGORY => r_PSL.INFORMATION110
,P_SOURCE_COLUMN => r_PSL.INFORMATION141
,P_SOURCE_TABLE => r_PSL.INFORMATION142
,P_WHATIF_LBL_TXT => r_PSL.INFORMATION219
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_PERSON_CHANGE_CS_LER_API.delete_PERSON_CHANGE_CS_LER(
--
p_validate => false
,p_per_info_chg_cs_ler_id => l_per_info_chg_cs_ler_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_CM_TYP_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
CCT.cm_typ_id new_value
from BEN_CM_TYP_F CCT
where CCT.name = c_CCT_name
and CCT.business_group_id = c_business_group_id
and CCT.cm_typ_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_CM_TYP_F CCT1
where CCT1.name = c_CCT_name
and CCT1.business_group_id = c_business_group_id
and CCT1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_CM_TYP_F CCT2
where CCT2.name = c_CCT_name
and CCT2.business_group_id = c_business_group_id
and CCT2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
open c_CCT(r_CCT_unique.table_route_id,
r_CCT_unique.information1,
r_CCT_unique.information2,
r_CCT_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_CM_TYP_F CREATE_COMP_COMM_TYPES ',20);
if l_update then
--
l_datetrack_mode := r_CCT.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_CM_TYP_F UPDATE_COMP_COMM_TYPES ',30);
BEN_COMP_COMM_TYPES_API.UPDATE_COMP_COMM_TYPES(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_CCT_ATTRIBUTE1 => r_CCT.INFORMATION111
,P_CCT_ATTRIBUTE10 => r_CCT.INFORMATION120
,P_CCT_ATTRIBUTE11 => r_CCT.INFORMATION121
,P_CCT_ATTRIBUTE12 => r_CCT.INFORMATION122
,P_CCT_ATTRIBUTE13 => r_CCT.INFORMATION123
,P_CCT_ATTRIBUTE14 => r_CCT.INFORMATION124
,P_CCT_ATTRIBUTE15 => r_CCT.INFORMATION125
,P_CCT_ATTRIBUTE16 => r_CCT.INFORMATION126
,P_CCT_ATTRIBUTE17 => r_CCT.INFORMATION127
,P_CCT_ATTRIBUTE18 => r_CCT.INFORMATION128
,P_CCT_ATTRIBUTE19 => r_CCT.INFORMATION129
,P_CCT_ATTRIBUTE2 => r_CCT.INFORMATION112
,P_CCT_ATTRIBUTE20 => r_CCT.INFORMATION130
,P_CCT_ATTRIBUTE21 => r_CCT.INFORMATION131
,P_CCT_ATTRIBUTE22 => r_CCT.INFORMATION132
,P_CCT_ATTRIBUTE23 => r_CCT.INFORMATION133
,P_CCT_ATTRIBUTE24 => r_CCT.INFORMATION134
,P_CCT_ATTRIBUTE25 => r_CCT.INFORMATION135
,P_CCT_ATTRIBUTE26 => r_CCT.INFORMATION136
,P_CCT_ATTRIBUTE27 => r_CCT.INFORMATION137
,P_CCT_ATTRIBUTE28 => r_CCT.INFORMATION138
,P_CCT_ATTRIBUTE29 => r_CCT.INFORMATION139
,P_CCT_ATTRIBUTE3 => r_CCT.INFORMATION113
,P_CCT_ATTRIBUTE30 => r_CCT.INFORMATION140
,P_CCT_ATTRIBUTE4 => r_CCT.INFORMATION114
,P_CCT_ATTRIBUTE5 => r_CCT.INFORMATION115
,P_CCT_ATTRIBUTE6 => r_CCT.INFORMATION116
,P_CCT_ATTRIBUTE7 => r_CCT.INFORMATION117
,P_CCT_ATTRIBUTE8 => r_CCT.INFORMATION118
,P_CCT_ATTRIBUTE9 => r_CCT.INFORMATION119
,P_CCT_ATTRIBUTE_CATEGORY => r_CCT.INFORMATION110
,P_CM_TYP_ID => l_cm_typ_id
,P_CM_TYP_RL => l_CM_TYP_RL
,P_CM_USG_CD => r_CCT.INFORMATION16
,P_DESC_TXT => r_CCT.INFORMATION185
,P_INSPN_RQD_FLAG => r_CCT.INFORMATION14
,P_INSPN_RQD_RL => l_INSPN_RQD_RL
,P_MX_NUM_AVLBL_VAL => r_CCT.INFORMATION262
,P_NAME => l_prefix || r_CCT.INFORMATION170 || l_suffix
,P_PARNT_CM_TYP_ID => l_PARNT_CM_TYP_ID
,P_PC_KIT_CD => r_CCT.INFORMATION17
,P_RCPENT_CD => r_CCT.INFORMATION15
,P_SHRT_NAME => r_CCT.INFORMATION141
,P_TO_BE_SENT_DT_CD => r_CCT.INFORMATION13
,P_TO_BE_SENT_DT_RL => l_TO_BE_SENT_DT_RL
,P_TRK_MLG_FLAG => r_CCT.INFORMATION12
,P_WHNVR_TRGRD_FLAG => r_CCT.INFORMATION11
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_COMP_COMM_TYPES_API.delete_COMP_COMM_TYPES(
--
p_validate => false
,p_cm_typ_id => l_cm_typ_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_PTD_LMT_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.INFORMATION170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
PDL.ptd_lmt_id new_value
from BEN_PTD_LMT_F PDL
where PDL.name = c_PDL_name
and PDL.business_group_id = c_business_group_id
and PDL.ptd_lmt_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_PTD_LMT_F PDL1
where PDL1.name = c_PDL_name
and PDL1.business_group_id = c_business_group_id
and PDL1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_PTD_LMT_F PDL2
where PDL2.name = c_PDL_name
and PDL2.business_group_id = c_business_group_id
and PDL2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target or l_update then
--
open c_PDL(r_PDL_unique.table_route_id,
r_PDL_unique.information1,
r_PDL_unique.information2,
r_PDL_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_PTD_LMT_F CREATE_PERIOD_LIMIT ',20);
if l_update then
--
l_datetrack_mode := r_PDL.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
-- Call Update routine for the pk_id created in prev run .
-- insert the table_name,old_pk_id,new_pk_id into a plsql record
hr_utility.set_location(' BEN_PTD_LMT_F UPDATE_PERIOD_LIMIT ',30);
BEN_PERIOD_LIMIT_API.UPDATE_PERIOD_LIMIT(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_BALANCE_TYPE_ID => l_BALANCE_TYPE_ID
,P_COMP_LVL_FCTR_ID => l_COMP_LVL_FCTR_ID
,P_LMT_DET_CD => r_PDL.INFORMATION11
,P_MX_COMP_TO_CNSDR => r_PDL.INFORMATION293
,P_MX_PCT_VAL => r_PDL.INFORMATION295
,P_MX_VAL => r_PDL.INFORMATION294
,P_NAME => l_prefix || r_PDL.INFORMATION170 || l_suffix
,P_PDL_ATTRIBUTE1 => r_PDL.INFORMATION111
,P_PDL_ATTRIBUTE10 => r_PDL.INFORMATION120
,P_PDL_ATTRIBUTE11 => r_PDL.INFORMATION121
,P_PDL_ATTRIBUTE12 => r_PDL.INFORMATION122
,P_PDL_ATTRIBUTE13 => r_PDL.INFORMATION123
,P_PDL_ATTRIBUTE14 => r_PDL.INFORMATION124
,P_PDL_ATTRIBUTE15 => r_PDL.INFORMATION125
,P_PDL_ATTRIBUTE16 => r_PDL.INFORMATION126
,P_PDL_ATTRIBUTE17 => r_PDL.INFORMATION127
,P_PDL_ATTRIBUTE18 => r_PDL.INFORMATION128
,P_PDL_ATTRIBUTE19 => r_PDL.INFORMATION129
,P_PDL_ATTRIBUTE2 => r_PDL.INFORMATION112
,P_PDL_ATTRIBUTE20 => r_PDL.INFORMATION130
,P_PDL_ATTRIBUTE21 => r_PDL.INFORMATION131
,P_PDL_ATTRIBUTE22 => r_PDL.INFORMATION132
,P_PDL_ATTRIBUTE23 => r_PDL.INFORMATION133
,P_PDL_ATTRIBUTE24 => r_PDL.INFORMATION134
,P_PDL_ATTRIBUTE25 => r_PDL.INFORMATION135
,P_PDL_ATTRIBUTE26 => r_PDL.INFORMATION136
,P_PDL_ATTRIBUTE27 => r_PDL.INFORMATION137
,P_PDL_ATTRIBUTE28 => r_PDL.INFORMATION138
,P_PDL_ATTRIBUTE29 => r_PDL.INFORMATION139
,P_PDL_ATTRIBUTE3 => r_PDL.INFORMATION113
,P_PDL_ATTRIBUTE30 => r_PDL.INFORMATION140
,P_PDL_ATTRIBUTE4 => r_PDL.INFORMATION114
,P_PDL_ATTRIBUTE5 => r_PDL.INFORMATION115
,P_PDL_ATTRIBUTE6 => r_PDL.INFORMATION116
,P_PDL_ATTRIBUTE7 => r_PDL.INFORMATION117
,P_PDL_ATTRIBUTE8 => r_PDL.INFORMATION118
,P_PDL_ATTRIBUTE9 => r_PDL.INFORMATION119
,P_PDL_ATTRIBUTE_CATEGORY => r_PDL.INFORMATION110
,P_PTD_LMT_CALC_RL => l_PTD_LMT_CALC_RL
,P_PTD_LMT_ID => l_ptd_lmt_id
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_PERIOD_LIMIT_API.delete_PERIOD_LIMIT(
--
p_validate => false
,p_ptd_lmt_id => l_ptd_lmt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_YR_PERD
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select
YRP.yr_perd_id new_value
from BEN_YR_PERD YRP
where
YRP.business_group_id = c_business_group_id
and YRP.start_date = c_start_date
and YRP.end_date = c_end_date
and YRP.yr_perd_id <> c_new_pk_id
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target or l_update then
--
l_current_pk_id := r_YRP.information1;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_YR_PERD CREATE_PGM_OR_PL_YR_PERD ',20);
elsif l_update then
hr_utility.set_location(' BEN_YR_PERD UPDATE_PGM_OR_PL_YR_PERD ',20);
BEN_PGM_OR_PL_YR_PERD_API.UPDATE_PGM_OR_PL_YR_PERD(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(ben_pd_copy_to_ben_one.g_copy_effective_date ,p_effective_date)
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_END_DATE => r_YRP.INFORMATION308
,P_LMTN_YR_END_DT => r_YRP.INFORMATION311
,P_LMTN_YR_STRT_DT => r_YRP.INFORMATION310
,P_PERDS_IN_YR_NUM => r_YRP.INFORMATION260
,P_PERD_TM_UOM_CD => r_YRP.INFORMATION11
,P_PERD_TYP_CD => r_YRP.INFORMATION12
,P_START_DATE => r_YRP.INFORMATION309
,P_YRP_ATTRIBUTE1 => r_YRP.INFORMATION111
,P_YRP_ATTRIBUTE10 => r_YRP.INFORMATION120
,P_YRP_ATTRIBUTE11 => r_YRP.INFORMATION121
,P_YRP_ATTRIBUTE12 => r_YRP.INFORMATION122
,P_YRP_ATTRIBUTE13 => r_YRP.INFORMATION123
,P_YRP_ATTRIBUTE14 => r_YRP.INFORMATION124
,P_YRP_ATTRIBUTE15 => r_YRP.INFORMATION125
,P_YRP_ATTRIBUTE16 => r_YRP.INFORMATION126
,P_YRP_ATTRIBUTE17 => r_YRP.INFORMATION127
,P_YRP_ATTRIBUTE18 => r_YRP.INFORMATION128
,P_YRP_ATTRIBUTE19 => r_YRP.INFORMATION129
,P_YRP_ATTRIBUTE2 => r_YRP.INFORMATION112
,P_YRP_ATTRIBUTE20 => r_YRP.INFORMATION130
,P_YRP_ATTRIBUTE21 => r_YRP.INFORMATION131
,P_YRP_ATTRIBUTE22 => r_YRP.INFORMATION132
,P_YRP_ATTRIBUTE23 => r_YRP.INFORMATION133
,P_YRP_ATTRIBUTE24 => r_YRP.INFORMATION134
,P_YRP_ATTRIBUTE25 => r_YRP.INFORMATION135
,P_YRP_ATTRIBUTE26 => r_YRP.INFORMATION136
,P_YRP_ATTRIBUTE27 => r_YRP.INFORMATION137
,P_YRP_ATTRIBUTE28 => r_YRP.INFORMATION138
,P_YRP_ATTRIBUTE29 => r_YRP.INFORMATION139
,P_YRP_ATTRIBUTE3 => r_YRP.INFORMATION113
,P_YRP_ATTRIBUTE30 => r_YRP.INFORMATION140
,P_YRP_ATTRIBUTE4 => r_YRP.INFORMATION114
,P_YRP_ATTRIBUTE5 => r_YRP.INFORMATION115
,P_YRP_ATTRIBUTE6 => r_YRP.INFORMATION116
,P_YRP_ATTRIBUTE7 => r_YRP.INFORMATION117
,P_YRP_ATTRIBUTE8 => r_YRP.INFORMATION118
,P_YRP_ATTRIBUTE9 => r_YRP.INFORMATION119
,P_YRP_ATTRIBUTE_CATEGORY => r_YRP.INFORMATION110
,P_YR_PERD_ID => l_yr_perd_id
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_WTHN_YR_PERD
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and rownum = 1 ;
select WYP.wthn_yr_perd_id new_value
from BEN_WTHN_YR_PERD WYP
where WYP.business_group_id = c_business_group_id
and WYP.strt_day = r_WYP.information293
and WYP.strt_mo = r_WYP.information295
and WYP.end_day = r_WYP.information294
and WYP.end_mo = r_WYP.information296
and WYP.tm_uom = r_WYP.information11
and WYP.yr_perd_id = l_yr_perd_id
and WYP.wthn_yr_perd_id <> c_new_pk_id ;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_update := true;
hr_utility.set_location( 'found record for update',10);
if not l_object_found_in_target or l_update then
l_current_pk_id := r_WYP.information1;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_WTHN_YR_PERD CREATE_WITHIN_YEAR_PERD ',20);
elsif l_update then
hr_utility.set_location(' BEN_WTHN_YR_PERD UPDATE_WITHIN_YEAR_PERD ',20);
BEN_WITHIN_YEAR_PERD_API.UPDATE_WITHIN_YEAR_PERD(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => NVL(ben_pd_copy_to_ben_one.g_copy_effective_date ,p_effective_date)
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_END_DAY => r_WYP.INFORMATION294
,P_END_MO => r_WYP.INFORMATION296
,P_STRT_DAY => r_WYP.INFORMATION293
,P_STRT_MO => r_WYP.INFORMATION295
,P_TM_UOM => r_WYP.INFORMATION11
,P_WTHN_YR_PERD_ID => l_wthn_yr_perd_id
,P_WYP_ATTRIBUTE1 => r_WYP.INFORMATION111
,P_WYP_ATTRIBUTE10 => r_WYP.INFORMATION120
,P_WYP_ATTRIBUTE11 => r_WYP.INFORMATION121
,P_WYP_ATTRIBUTE12 => r_WYP.INFORMATION122
,P_WYP_ATTRIBUTE13 => r_WYP.INFORMATION123
,P_WYP_ATTRIBUTE14 => r_WYP.INFORMATION124
,P_WYP_ATTRIBUTE15 => r_WYP.INFORMATION125
,P_WYP_ATTRIBUTE16 => r_WYP.INFORMATION126
,P_WYP_ATTRIBUTE17 => r_WYP.INFORMATION127
,P_WYP_ATTRIBUTE18 => r_WYP.INFORMATION128
,P_WYP_ATTRIBUTE19 => r_WYP.INFORMATION129
,P_WYP_ATTRIBUTE2 => r_WYP.INFORMATION112
,P_WYP_ATTRIBUTE20 => r_WYP.INFORMATION130
,P_WYP_ATTRIBUTE21 => r_WYP.INFORMATION131
,P_WYP_ATTRIBUTE22 => r_WYP.INFORMATION132
,P_WYP_ATTRIBUTE23 => r_WYP.INFORMATION133
,P_WYP_ATTRIBUTE24 => r_WYP.INFORMATION134
,P_WYP_ATTRIBUTE25 => r_WYP.INFORMATION135
,P_WYP_ATTRIBUTE26 => r_WYP.INFORMATION136
,P_WYP_ATTRIBUTE27 => r_WYP.INFORMATION137
,P_WYP_ATTRIBUTE28 => r_WYP.INFORMATION138
,P_WYP_ATTRIBUTE29 => r_WYP.INFORMATION139
,P_WYP_ATTRIBUTE3 => r_WYP.INFORMATION113
,P_WYP_ATTRIBUTE30 => r_WYP.INFORMATION140
,P_WYP_ATTRIBUTE4 => r_WYP.INFORMATION114
,P_WYP_ATTRIBUTE5 => r_WYP.INFORMATION115
,P_WYP_ATTRIBUTE6 => r_WYP.INFORMATION116
,P_WYP_ATTRIBUTE7 => r_WYP.INFORMATION117
,P_WYP_ATTRIBUTE8 => r_WYP.INFORMATION118
,P_WYP_ATTRIBUTE9 => r_WYP.INFORMATION119
,P_WYP_ATTRIBUTE_CATEGORY => r_WYP.INFORMATION110
,P_YR_PERD_ID => l_YR_PERD_ID
--
,P_OBJECT_VERSION_NUMBER => l_object_version_number
);
select distinct decode(cpe.information160,cpe.information1,1,2), cpe.information1,
cpe.information2,
cpe.information3,
cpe.INFORMATION170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode,
cpe.information8
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_PL_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by decode(cpe.information160,cpe.information1,1,2), cpe.information1,cpe.information2,cpe.information3,
cpe.information170, cpe.table_route_id ,cpe.dml_operation,cpe.datetrack_mode, cpe.information8
order by 1,2,3;
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
PLN.pl_id new_value
from BEN_PL_F PLN
where PLN.name = c_PLN_name
and PLN.business_group_id = c_business_group_id
and PLN.pl_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_PL_F PLN1
where PLN1.name = c_PLN_name
and PLN1.business_group_id = c_business_group_id
and PLN1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_PL_F PLN2
where PLN2.name = c_PLN_name
and PLN2.business_group_id = c_business_group_id
and PLN2.effective_end_date >= c_effective_end_date )
;
select name, pl_id
from ben_pl_f
where name = p_grp_pl_name
and p_effective_date between effective_start_date and effective_end_date;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target OR l_update then
--
open c_PLN(r_PLN_unique.table_route_id,
r_PLN_unique.information1,
r_PLN_unique.information2,
r_PLN_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_PL_F CREATE_PLAN ',20);
if l_update then
--
l_datetrack_mode := r_PLN.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
hr_utility.set_location(' BEN_PL_F UPDATE_PLAN ',30);
BEN_PLAN_API.UPDATE_PLAN(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_ACTL_PREM_ID => l_ACTL_PREM_ID
,P_ALWS_QDRO_FLAG => r_PLN.INFORMATION36
,P_ALWS_QMCSO_FLAG => r_PLN.INFORMATION37
,P_ALWS_REIMBMTS_FLAG => r_PLN.INFORMATION51
,P_ALWS_TMPRY_ID_CRD_FLAG => r_PLN.INFORMATION24
,P_ALWS_UNRSTRCTD_ENRT_FLAG => r_PLN.INFORMATION52
,P_AUTO_ENRT_MTHD_RL => l_AUTO_ENRT_MTHD_RL
,P_BNDRY_PERD_CD => r_PLN.INFORMATION101
,P_BNFT_OR_OPTION_RSTRCTN_CD => r_PLN.INFORMATION77
,P_BNFT_PRVDR_POOL_ID => l_BNFT_PRVDR_POOL_ID
,P_BNF_ADDL_INSTN_TXT_ALWD_FLAG => r_PLN.INFORMATION53
,P_BNF_ADRS_RQD_FLAG => r_PLN.INFORMATION54
,P_BNF_CNTNGT_BNFS_ALWD_FLAG => r_PLN.INFORMATION56
,P_BNF_CTFN_RQD_FLAG => r_PLN.INFORMATION55
,P_BNF_DFLT_BNF_CD => r_PLN.INFORMATION82
,P_BNF_DOB_RQD_FLAG => r_PLN.INFORMATION66
,P_BNF_DSGE_MNR_TTEE_RQD_FLAG => r_PLN.INFORMATION60
,P_BNF_DSGN_CD => r_PLN.INFORMATION89
,P_BNF_INCRMT_AMT => r_PLN.INFORMATION302
,P_BNF_LEGV_ID_RQD_FLAG => r_PLN.INFORMATION57
,P_BNF_MAY_DSGT_ORG_FLAG => r_PLN.INFORMATION58
,P_BNF_MN_DSGNTBL_AMT => r_PLN.INFORMATION303
,P_BNF_MN_DSGNTBL_PCT_VAL => r_PLN.INFORMATION290
,P_BNF_PCT_AMT_ALWD_CD => r_PLN.INFORMATION83
,P_BNF_PCT_INCRMT_VAL => r_PLN.INFORMATION293
,P_BNF_QDRO_RL_APLS_FLAG => r_PLN.INFORMATION59
,P_CMPR_CLMS_TO_CVG_OR_BAL_CD => r_PLN.INFORMATION84
,P_COBRA_PYMT_DUE_DY_NUM => r_PLN.INFORMATION285
,P_COST_ALLOC_KEYFLEX_1_ID => l_COST_ALLOC_KEYFLEX_1_ID
,P_COST_ALLOC_KEYFLEX_2_ID => l_COST_ALLOC_KEYFLEX_2_ID
,P_CVG_INCR_R_DECR_ONLY_CD => r_PLN.INFORMATION68
,P_DFLT_TO_ASN_PNDG_CTFN_CD => r_PLN.INFORMATION91
,P_DFLT_TO_ASN_PNDG_CTFN_RL => l_DFLT_TO_ASN_PNDG_CTFN_RL
,P_DPNT_ADRS_RQD_FLAG => r_PLN.INFORMATION30
,P_DPNT_CVD_BY_OTHR_APLS_FLAG => r_PLN.INFORMATION29
,P_DPNT_CVG_END_DT_CD => r_PLN.INFORMATION85
,P_DPNT_CVG_END_DT_RL => l_DPNT_CVG_END_DT_RL
,P_DPNT_CVG_STRT_DT_CD => r_PLN.INFORMATION86
,P_DPNT_CVG_STRT_DT_RL => l_DPNT_CVG_STRT_DT_RL
,P_DPNT_DOB_RQD_FLAG => r_PLN.INFORMATION32
,P_DPNT_DSGN_CD => r_PLN.INFORMATION87
,P_DPNT_LEG_ID_RQD_FLAG => r_PLN.INFORMATION31
,P_DPNT_NO_CTFN_RQD_FLAG => r_PLN.INFORMATION27
,P_DRVBL_DPNT_ELIG_FLAG => r_PLN.INFORMATION25
,P_DRVBL_FCTR_APLS_RTS_FLAG => r_PLN.INFORMATION33
,P_DRVBL_FCTR_PRTN_ELIG_FLAG => r_PLN.INFORMATION26
,P_ELIG_APLS_FLAG => r_PLN.INFORMATION34
,P_ENRT_CD => r_PLN.INFORMATION17
,P_ENRT_CVG_END_DT_CD => r_PLN.INFORMATION21
,P_ENRT_CVG_END_DT_RL => l_ENRT_CVG_END_DT_RL
,P_ENRT_CVG_STRT_DT_CD => r_PLN.INFORMATION20
,P_ENRT_CVG_STRT_DT_RL => l_ENRT_CVG_STRT_DT_RL
,P_ENRT_MTHD_CD => r_PLN.INFORMATION92
,P_ENRT_PL_OPT_FLAG => r_PLN.INFORMATION39
,P_ENRT_RL => l_ENRT_RL
,P_FRFS_APLY_FLAG => r_PLN.INFORMATION40
,P_FRFS_CNTR_DET_CD => r_PLN.INFORMATION96
,P_FRFS_DISTR_DET_CD => r_PLN.INFORMATION97
,P_FRFS_DISTR_MTHD_CD => r_PLN.INFORMATION13
,P_FRFS_DISTR_MTHD_RL => l_FRFS_DISTR_MTHD_RL
,P_FRFS_MX_CRYFWD_VAL => r_PLN.INFORMATION304
,P_FRFS_PORTION_DET_CD => r_PLN.INFORMATION100
,P_FRFS_VAL_DET_CD => r_PLN.INFORMATION99
,P_FUNCTION_CODE => r_PLN.INFORMATION95
,P_HC_PL_SUBJ_HCFA_APRVL_FLAG => r_PLN.INFORMATION47
,P_HC_SVC_TYP_CD => r_PLN.INFORMATION15
,P_HGHLY_CMPD_RL_APLS_FLAG => r_PLN.INFORMATION38
,P_IMPTD_INCM_CALC_CD => r_PLN.INFORMATION73
,P_INCPTN_DT => r_PLN.INFORMATION306
,P_INVK_DCLN_PRTN_PL_FLAG => r_PLN.INFORMATION50
,P_INVK_FLX_CR_PL_FLAG => r_PLN.INFORMATION49
,P_IVR_IDENT => r_PLN.INFORMATION142
,P_MAPPING_TABLE_NAME => r_PLN.INFORMATION141
,P_MAPPING_TABLE_PK_ID => l_MAPPING_TABLE_PK_ID
,P_MAY_ENRL_PL_N_OIPL_FLAG => r_PLN.INFORMATION28
,P_MN_CVG_RL => l_MN_CVG_RL
,P_MN_CVG_RQD_AMT => r_PLN.INFORMATION300
,P_MN_OPTS_RQD_NUM => r_PLN.INFORMATION269
,P_MX_CVG_ALWD_AMT => r_PLN.INFORMATION299
,P_MX_CVG_INCR_ALWD_AMT => r_PLN.INFORMATION297
,P_MX_CVG_INCR_WCF_ALWD_AMT => r_PLN.INFORMATION298
,P_MX_CVG_MLT_INCR_NUM => r_PLN.INFORMATION271
,P_MX_CVG_MLT_INCR_WCF_NUM => r_PLN.INFORMATION273
,P_MX_CVG_RL => l_MX_CVG_RL
,P_MX_CVG_WCFN_AMT => r_PLN.INFORMATION295
,P_MX_CVG_WCFN_MLT_NUM => r_PLN.INFORMATION267
,P_MX_OPTS_ALWD_NUM => r_PLN.INFORMATION270
,P_MX_WTG_DT_TO_USE_CD => r_PLN.INFORMATION80
,P_MX_WTG_DT_TO_USE_RL => l_MX_WTG_DT_TO_USE_RL
,P_MX_WTG_PERD_PRTE_UOM => r_PLN.INFORMATION79
,P_MX_WTG_PERD_PRTE_VAL => r_PLN.INFORMATION289
,P_MX_WTG_PERD_RL => l_MX_WTG_PERD_RL
,P_NAME => l_prefix || r_PLN.INFORMATION170 || l_suffix
,P_NIP_ACTY_REF_PERD_CD => r_PLN.INFORMATION16
,P_NIP_DFLT_ENRT_CD => r_PLN.INFORMATION88
,P_NIP_DFLT_ENRT_DET_RL => l_NIP_DFLT_ENRT_DET_RL
,P_NIP_DFLT_FLAG => r_PLN.INFORMATION12
,P_NIP_ENRT_INFO_RT_FREQ_CD => r_PLN.INFORMATION22
,P_NIP_PL_UOM => r_PLN.INFORMATION81
,P_NO_MN_CVG_AMT_APLS_FLAG => r_PLN.INFORMATION61
,P_NO_MN_CVG_INCR_APLS_FLAG => r_PLN.INFORMATION63
,P_NO_MN_OPTS_NUM_APLS_FLAG => r_PLN.INFORMATION65
,P_NO_MX_CVG_AMT_APLS_FLAG => r_PLN.INFORMATION62
,P_NO_MX_CVG_INCR_APLS_FLAG => r_PLN.INFORMATION64
,P_NO_MX_OPTS_NUM_APLS_FLAG => r_PLN.INFORMATION35
,P_ORDR_NUM => r_PLN.INFORMATION266
,P_PER_CVRD_CD => r_PLN.INFORMATION76
,P_PLN_ATTRIBUTE1 => r_PLN.INFORMATION111
,P_PLN_ATTRIBUTE10 => r_PLN.INFORMATION120
,P_PLN_ATTRIBUTE11 => r_PLN.INFORMATION121
,P_PLN_ATTRIBUTE12 => r_PLN.INFORMATION122
,P_PLN_ATTRIBUTE13 => r_PLN.INFORMATION123
,P_PLN_ATTRIBUTE14 => r_PLN.INFORMATION124
,P_PLN_ATTRIBUTE15 => r_PLN.INFORMATION125
,P_PLN_ATTRIBUTE16 => r_PLN.INFORMATION126
,P_PLN_ATTRIBUTE17 => r_PLN.INFORMATION127
,P_PLN_ATTRIBUTE18 => r_PLN.INFORMATION128
,P_PLN_ATTRIBUTE19 => r_PLN.INFORMATION129
,P_PLN_ATTRIBUTE2 => r_PLN.INFORMATION112
,P_PLN_ATTRIBUTE20 => r_PLN.INFORMATION130
,P_PLN_ATTRIBUTE21 => r_PLN.INFORMATION131
,P_PLN_ATTRIBUTE22 => r_PLN.INFORMATION132
,P_PLN_ATTRIBUTE23 => r_PLN.INFORMATION133
,P_PLN_ATTRIBUTE24 => r_PLN.INFORMATION134
,P_PLN_ATTRIBUTE25 => r_PLN.INFORMATION135
,P_PLN_ATTRIBUTE26 => r_PLN.INFORMATION136
,P_PLN_ATTRIBUTE27 => r_PLN.INFORMATION137
,P_PLN_ATTRIBUTE28 => r_PLN.INFORMATION138
,P_PLN_ATTRIBUTE29 => r_PLN.INFORMATION139
,P_PLN_ATTRIBUTE3 => r_PLN.INFORMATION113
,P_PLN_ATTRIBUTE30 => r_PLN.INFORMATION140
,P_PLN_ATTRIBUTE4 => r_PLN.INFORMATION114
,P_PLN_ATTRIBUTE5 => r_PLN.INFORMATION115
,P_PLN_ATTRIBUTE6 => r_PLN.INFORMATION116
,P_PLN_ATTRIBUTE7 => r_PLN.INFORMATION117
,P_PLN_ATTRIBUTE8 => r_PLN.INFORMATION118
,P_PLN_ATTRIBUTE9 => r_PLN.INFORMATION119
,P_PLN_ATTRIBUTE_CATEGORY => r_PLN.INFORMATION110
,P_PL_CD => r_PLN.INFORMATION67
,P_PL_ID => l_pl_id
,P_PL_STAT_CD => l_status_cd
,P_PL_TYP_ID => l_PL_TYP_ID
,P_PL_YR_NOT_APPLCBL_FLAG => NVL(r_PLN.INFORMATION14, 'N') -- BUG: 3502032
,P_POSTELCN_EDIT_RL => l_POSTELCN_EDIT_RL
,P_POST_TO_GL_FLAG => r_PLN.INFORMATION98
,P_PRMRY_FNDG_MTHD_CD => r_PLN.INFORMATION90
,P_PRORT_PRTL_YR_CVG_RSTRN_CD => r_PLN.INFORMATION18
,P_PRORT_PRTL_YR_CVG_RSTRN_RL => l_PRORT_PRTL_YR_CVG_RSTRN_RL
,P_PRTN_ELIG_OVRID_ALWD_FLAG => r_PLN.INFORMATION46
,P_RQD_PERD_ENRT_NENRT_RL => l_RQD_PERD_ENRT_NENRT_RL
,P_RQD_PERD_ENRT_NENRT_UOM => r_PLN.INFORMATION69
,P_RQD_PERD_ENRT_NENRT_VAL => r_PLN.INFORMATION301
,P_RT_END_DT_CD => r_PLN.INFORMATION74
,P_RT_END_DT_RL => l_RT_END_DT_RL
,P_RT_STRT_DT_CD => r_PLN.INFORMATION75
,P_RT_STRT_DT_RL => l_RT_STRT_DT_RL
,P_SHORT_CODE => r_PLN.INFORMATION93
,P_SHORT_NAME => r_PLN.INFORMATION94
-- cwb tilakn
,P_GROUP_PL_ID => r_PLN.INFORMATION160
,P_SUBJ_TO_IMPTD_INCM_TYP_CD => r_PLN.INFORMATION71
,P_SVGS_PL_FLAG => r_PLN.INFORMATION41
,P_TRK_INELIG_PER_FLAG => r_PLN.INFORMATION42
,P_UNSSPND_ENRT_CD => r_PLN.INFORMATION72
,P_URL_REF_NAME => r_PLN.INFORMATION185
,P_USE_ALL_ASNTS_ELIG_FLAG => r_PLN.INFORMATION43
,P_USE_ALL_ASNTS_FOR_RT_FLAG => r_PLN.INFORMATION44
,P_VRFY_FMLY_MMBR_CD => r_PLN.INFORMATION23
,P_VRFY_FMLY_MMBR_RL => l_VRFY_FMLY_MMBR_RL
,P_VSTG_APLS_FLAG => r_PLN.INFORMATION45
,P_WVBL_FLAG => r_PLN.INFORMATION48
--ML
,p_SUSP_IF_CTFN_NOT_PRVD_FLAG => nvl(r_PLN.INFORMATION198,'Y')
,p_CTFN_DETERMINE_CD => r_PLN.INFORMATION197
,p_SUSP_IF_DPNT_SSN_NT_PRV_CD => l_SUSP_IF_DPNT_SSN_NT_PRV_CD
,p_SUSP_IF_DPNT_DOB_NT_PRV_CD => l_SUSP_IF_DPNT_DOB_NT_PRV_CD
,p_SUSP_IF_DPNT_ADR_NT_PRV_CD => l_SUSP_IF_DPNT_ADR_NT_PRV_CD
,p_SUSP_IF_CTFN_NOT_DPNT_FLAG => nvl(r_PLN.INFORMATION192,'Y')
,p_DPNT_CTFN_DETERMINE_CD => r_PLN.INFORMATION193
,p_SUSP_IF_BNF_SSN_NT_PRV_CD => l_SUSP_IF_BNF_SSN_NT_PRV_CD
,p_SUSP_IF_BNF_DOB_NT_PRV_CD => l_SUSP_IF_BNF_DOB_NT_PRV_CD
,p_BNF_CTFN_DETERMINE_CD => r_PLN.INFORMATION104
-- Bug 3939490
,p_legislation_code => r_pln.information107
,p_legislation_subgroup => r_pln.information108
,p_use_csd_rsd_prccng_cd => r_pln.information109
-- Bug 3939490
,p_SUSP_IF_CTFN_NOT_BNF_FLAG => nvl(r_PLN.INFORMATION105,'N')
,p_SUSP_IF_BNF_ADR_NT_PRV_CD => l_SUSP_IF_BNF_ADR_NT_PRV_CD
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_PLAN_API.delete_PLAN(
--
p_validate => false
,p_pl_id => l_pl_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.information170 name,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_SVC_AREA_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.information170, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
SVA.svc_area_id new_value
from BEN_SVC_AREA_F SVA
where SVA.name = c_SVA_name
and SVA.business_group_id = c_business_group_id
and SVA.svc_area_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_SVC_AREA_F SVA1
where SVA1.name = c_SVA_name
and SVA1.business_group_id = c_business_group_id
and SVA1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_SVC_AREA_F SVA2
where SVA2.name = c_SVA_name
and SVA2.business_group_id = c_business_group_id
and SVA2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target OR l_update then
--
open c_SVA(r_SVA_unique.table_route_id,
r_SVA_unique.information1,
r_SVA_unique.information2,
r_SVA_unique.information3 ) ;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_SVC_AREA_F CREATE_SERVICE_AREA ',20);
if l_update then
--
l_datetrack_mode := r_SVA.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
hr_utility.set_location(' BEN_SVC_AREA_F UPDATE_SERVICE_AREA ',30);
BEN_SERVICE_AREA_API.UPDATE_SERVICE_AREA(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_NAME => l_prefix || r_SVA.INFORMATION170 || l_suffix
,P_ORG_UNIT_PRDCT => r_SVA.INFORMATION141
,P_SVA_ATTRIBUTE1 => r_SVA.INFORMATION111
,P_SVA_ATTRIBUTE10 => r_SVA.INFORMATION120
,P_SVA_ATTRIBUTE11 => r_SVA.INFORMATION121
,P_SVA_ATTRIBUTE12 => r_SVA.INFORMATION122
,P_SVA_ATTRIBUTE13 => r_SVA.INFORMATION123
,P_SVA_ATTRIBUTE14 => r_SVA.INFORMATION124
,P_SVA_ATTRIBUTE15 => r_SVA.INFORMATION125
,P_SVA_ATTRIBUTE16 => r_SVA.INFORMATION126
,P_SVA_ATTRIBUTE17 => r_SVA.INFORMATION127
,P_SVA_ATTRIBUTE18 => r_SVA.INFORMATION128
,P_SVA_ATTRIBUTE19 => r_SVA.INFORMATION129
,P_SVA_ATTRIBUTE2 => r_SVA.INFORMATION112
,P_SVA_ATTRIBUTE20 => r_SVA.INFORMATION130
,P_SVA_ATTRIBUTE21 => r_SVA.INFORMATION131
,P_SVA_ATTRIBUTE22 => r_SVA.INFORMATION132
,P_SVA_ATTRIBUTE23 => r_SVA.INFORMATION133
,P_SVA_ATTRIBUTE24 => r_SVA.INFORMATION134
,P_SVA_ATTRIBUTE25 => r_SVA.INFORMATION135
,P_SVA_ATTRIBUTE26 => r_SVA.INFORMATION136
,P_SVA_ATTRIBUTE27 => r_SVA.INFORMATION137
,P_SVA_ATTRIBUTE28 => r_SVA.INFORMATION138
,P_SVA_ATTRIBUTE29 => r_SVA.INFORMATION139
,P_SVA_ATTRIBUTE3 => r_SVA.INFORMATION113
,P_SVA_ATTRIBUTE30 => r_SVA.INFORMATION140
,P_SVA_ATTRIBUTE4 => r_SVA.INFORMATION114
,P_SVA_ATTRIBUTE5 => r_SVA.INFORMATION115
,P_SVA_ATTRIBUTE6 => r_SVA.INFORMATION116
,P_SVA_ATTRIBUTE7 => r_SVA.INFORMATION117
,P_SVA_ATTRIBUTE8 => r_SVA.INFORMATION118
,P_SVA_ATTRIBUTE9 => r_SVA.INFORMATION119
,P_SVA_ATTRIBUTE_CATEGORY => r_SVA.INFORMATION110
,P_SVC_AREA_ID => l_svc_area_id
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_SERVICE_AREA_API.delete_SERVICE_AREA(
--
p_validate => false
,p_svc_area_id => l_svc_area_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
select distinct cpe.information1,
cpe.information2,
cpe.information3,
cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
from ben_copy_entity_results cpe,
pqh_table_route tr
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = tr.table_route_id
-- and tr.where_clause = l_BEN_PL_TYP_OPT_TYP_F
and tr.table_alias = l_table_alias
and cpe.number_of_copies = 1 --ADDITION
group by cpe.information1,cpe.information2,cpe.information3, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
select
min(cpe.information2) min_esd,
max(cpe.information3) min_eed
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1 ;
select
cpe.*
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_route_id = c_table_route_id
and cpe.information1 = c_information1
and cpe.information2 = c_information2
and cpe.information3 = c_information3
and rownum = 1 ;
select
PON.pl_typ_opt_typ_id new_value
from BEN_PL_TYP_OPT_TYP_F PON
where
PON.OPT_ID = l_OPT_ID and
PON.PL_TYP_ID = l_PL_TYP_ID and
PON.business_group_id = c_business_group_id
and PON.pl_typ_opt_typ_id <> c_new_pk_id
--TEMPIK
and c_effective_start_date between effective_start_date
and effective_end_date ;
and exists ( select null
from BEN_PL_TYP_OPT_TYP_F PON1
where
PON1.OPT_ID = l_OPT_ID and
PON1.PL_TYP_ID = l_PL_TYP_ID and
PON1.business_group_id = c_business_group_id
and PON1.effective_start_date <= c_effective_start_date )
and exists ( select null
from BEN_PL_TYP_OPT_TYP_F PON2
where
PON2.OPT_ID = l_OPT_ID and
PON2.PL_TYP_ID = l_PL_TYP_ID and
PON2.business_group_id = c_business_group_id
and PON2.effective_end_date >= c_effective_end_date )
;
l_update boolean := false ;
l_datetrack_mode varchar2(80) := hr_api.g_update;
l_update := false;
if l_dml_operation = 'UPDATE' then
--
l_object_found_in_target := TRUE;
l_update := true;
hr_utility.set_location( 'found record for update',10);
l_update := false;
if not l_object_found_in_target OR l_update then
--
l_current_pk_id := r_PON.information1;
if l_first_rec and not l_update then
-- Call Create routine.
hr_utility.set_location(' BEN_PL_TYP_OPT_TYP_F CREATE_PLAN_TYPE_OPTION_TYPE ',20);
if l_update then
--
l_datetrack_mode := r_PON.datetrack_mode ;
IF l_update OR l_dml_operation <> 'UPDATE' THEN
--UPD END
hr_utility.set_location(' BEN_PL_TYP_OPT_TYP_F UPDATE_PLAN_TYPE_OPTION_TYPE ',30);
BEN_PLAN_TYPE_OPTION_TYPE_API.UPDATE_PLAN_TYPE_OPTION_TYPE(
--
P_VALIDATE => false
,P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_target_business_group_id
--
,P_OPT_ID => l_OPT_ID
,P_PL_TYP_ID => l_PL_TYP_ID
,P_PL_TYP_OPT_TYP_CD => r_PON.INFORMATION11
,P_PL_TYP_OPT_TYP_ID => l_pl_typ_opt_typ_id
,P_PON_ATTRIBUTE1 => r_PON.INFORMATION111
,P_PON_ATTRIBUTE10 => r_PON.INFORMATION120
,P_PON_ATTRIBUTE11 => r_PON.INFORMATION121
,P_PON_ATTRIBUTE12 => r_PON.INFORMATION122
,P_PON_ATTRIBUTE13 => r_PON.INFORMATION123
,P_PON_ATTRIBUTE14 => r_PON.INFORMATION124
,P_PON_ATTRIBUTE15 => r_PON.INFORMATION125
,P_PON_ATTRIBUTE16 => r_PON.INFORMATION126
,P_PON_ATTRIBUTE17 => r_PON.INFORMATION127
,P_PON_ATTRIBUTE18 => r_PON.INFORMATION128
,P_PON_ATTRIBUTE19 => r_PON.INFORMATION129
,P_PON_ATTRIBUTE2 => r_PON.INFORMATION112
,P_PON_ATTRIBUTE20 => r_PON.INFORMATION130
,P_PON_ATTRIBUTE21 => r_PON.INFORMATION131
,P_PON_ATTRIBUTE22 => r_PON.INFORMATION132
,P_PON_ATTRIBUTE23 => r_PON.INFORMATION133
,P_PON_ATTRIBUTE24 => r_PON.INFORMATION134
,P_PON_ATTRIBUTE25 => r_PON.INFORMATION135
,P_PON_ATTRIBUTE26 => r_PON.INFORMATION136
,P_PON_ATTRIBUTE27 => r_PON.INFORMATION137
,P_PON_ATTRIBUTE28 => r_PON.INFORMATION138
,P_PON_ATTRIBUTE29 => r_PON.INFORMATION139
,P_PON_ATTRIBUTE3 => r_PON.INFORMATION113
,P_PON_ATTRIBUTE30 => r_PON.INFORMATION140
,P_PON_ATTRIBUTE4 => r_PON.INFORMATION114
,P_PON_ATTRIBUTE5 => r_PON.INFORMATION115
,P_PON_ATTRIBUTE6 => r_PON.INFORMATION116
,P_PON_ATTRIBUTE7 => r_PON.INFORMATION117
,P_PON_ATTRIBUTE8 => r_PON.INFORMATION118
,P_PON_ATTRIBUTE9 => r_PON.INFORMATION119
,P_PON_ATTRIBUTE_CATEGORY => r_PON.INFORMATION110
--
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_DATETRACK_MODE => l_datetrack_mode
);
BEN_PLAN_TYPE_OPTION_TYPE_API.delete_PLAN_TYPE_OPTION_TYPE(
--
p_validate => false
,p_pl_typ_opt_typ_id => l_pl_typ_opt_typ_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_max_eed
,p_datetrack_mode => hr_api.g_delete
--
);
SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_valueset_name
/* AND ( NVL (security_group_id, 0) = 0 -- Bug 4351143 Commented as
OR security_group_id IN ( -- security_group_id is not in
SELECT security_group_id -- table fnd_flex_value_sets
FROM fnd_security_groups
WHERE security_group_key = TO_CHAR (p_target_business_group_id)
)
)*/
;
SELECT NULL
FROM fnd_lookup_types_vl flv
WHERE lookup_type = cv_lookup_type
AND ( customization_level IN ('E', 'S')
OR ( customization_level = 'U'
AND ( security_group_id = 0
OR security_group_id IN (
SELECT security_group_id
FROM fnd_security_groups
WHERE security_group_key =
TO_CHAR (cv_target_business_group_id))
)
)
);
SELECT DISTINCT cpe.information1, cpe.information2, cpe.information3,
cpe.information170 NAME, cpe.table_route_id,
cpe.dml_operation, cpe.datetrack_mode
FROM ben_copy_entity_results cpe, pqh_table_route tr
WHERE cpe.copy_entity_txn_id = p_copy_entity_txn_id
AND cpe.table_route_id = tr.table_route_id
AND tr.table_alias = l_table_alias
AND cpe.number_of_copies = 1
GROUP BY cpe.information1,
cpe.information2,
cpe.information3,
cpe.information170,
cpe.table_route_id,
cpe.dml_operation,
cpe.datetrack_mode
order by information1, information2; --added for bug: 5151945
SELECT MIN (cpe.information2) min_esd, MAX (cpe.information3) min_eed
FROM ben_copy_entity_results cpe
WHERE cpe.copy_entity_txn_id = p_copy_entity_txn_id
AND cpe.table_route_id = c_table_route_id
AND cpe.information1 = c_information1;
SELECT cpe.*
FROM ben_copy_entity_results cpe
WHERE cpe.copy_entity_txn_id = p_copy_entity_txn_id
AND cpe.table_route_id = c_table_route_id
AND cpe.information1 = c_information1
AND ROWNUM = 1;
SELECT egl.eligy_criteria_id new_value
FROM ben_eligy_criteria egl
WHERE egl.NAME = c_egl_name
AND egl.business_group_id = c_business_group_id
AND egl.eligy_criteria_id <> c_new_pk_id;
SELECT information3
FROM pqh_copy_entity_attribs
WHERE copy_entity_Txn_id = p_copy_entity_txn_id;
l_update BOOLEAN := FALSE;
l_datetrack_mode VARCHAR2 (80) := hr_api.g_update;
l_update := FALSE;
IF l_dml_operation = 'UPDATE'
THEN
--
l_update := TRUE;
hr_utility.set_location ('found record for update', 10);
IF NOT l_object_found_in_target OR l_update
THEN
--
OPEN c_egl (r_egl_unique.table_route_id,
r_egl_unique.information1
);
IF l_first_rec AND NOT l_update
THEN
-- Call Create routine.
hr_utility.set_location ('BEN_ELIGY_CRITERIA_API.CREATE_ELIGY_CRITERIA ', 20 );
ELSIF l_update
THEN
--
-- Bug: 4372345 Fetch OVN. EGL being non-date-tracked,
-- create-api will not be called before update in PDW/PDC flow.
l_object_version_number := r_egl.information265;
hr_utility.set_location ('BEN_ELIGY_CRITERIA_API.UPDATE_ELIGY_CRITERIA ', 20 );
ben_eligy_criteria_api.update_eligy_criteria
(
p_validate => FALSE,
p_eligy_criteria_id => l_eligy_criteria_id,
p_name => l_prefix || r_egl.information170 || l_suffix,
p_short_code => r_egl.information11,
p_description => r_egl.information219,
p_criteria_type => r_egl.information12,
p_crit_col1_val_type_cd => r_egl.information13,
p_crit_col1_datatype => r_egl.information14,
p_col1_lookup_type => r_egl.information15,
p_col1_value_set_id => l_col1_value_set_id,
p_access_table_name1 => r_egl.information16,
p_access_column_name1 => r_egl.information17,
p_time_entry_access_tab_nam1 => r_egl.information18,
p_time_entry_access_col_nam1 => r_egl.information19,
p_crit_col2_val_type_cd => r_egl.information20,
p_crit_col2_datatype => r_egl.information21,
p_col2_lookup_type => r_egl.information22,
p_col2_value_set_id => r_egl.information267,
p_access_table_name2 => r_egl.information23,
p_access_column_name2 => r_egl.information24,
p_time_entry_access_tab_nam2 => r_egl.information25,
p_time_entry_access_col_nam2 => r_egl.information26,
p_access_calc_rule => l_access_calc_rule,
p_allow_range_validation_flg => r_egl.information27,
p_user_defined_flag => r_egl.information28,
p_business_group_id => p_target_business_group_id,
p_legislation_code => r_egl.information29,
--Bug 4592554
p_allow_range_validation_flag2 => r_egl.information30,
p_access_calc_rule2 => r_egl.information269,
p_time_access_calc_rule1 => r_egl.information270,
p_time_access_calc_rule2 => r_egl.information271,
--End Bug 4592554
p_egl_attribute_category => r_egl.information110,
p_egl_attribute1 => r_egl.information111,
p_egl_attribute2 => r_egl.information112,
p_egl_attribute3 => r_egl.information113,
p_egl_attribute4 => r_egl.information114,
p_egl_attribute5 => r_egl.information115,
p_egl_attribute6 => r_egl.information116,
p_egl_attribute7 => r_egl.information117,
p_egl_attribute8 => r_egl.information118,
p_egl_attribute9 => r_egl.information119,
p_egl_attribute10 => r_egl.information120,
p_egl_attribute11 => r_egl.information121,
p_egl_attribute12 => r_egl.information122,
p_egl_attribute13 => r_egl.information123,
p_egl_attribute14 => r_egl.information124,
p_egl_attribute15 => r_egl.information125,
p_egl_attribute16 => r_egl.information126,
p_egl_attribute17 => r_egl.information127,
p_egl_attribute18 => r_egl.information128,
p_egl_attribute19 => r_egl.information129,
p_egl_attribute20 => r_egl.information130,
p_egl_attribute21 => r_egl.information131,
p_egl_attribute22 => r_egl.information132,
p_egl_attribute23 => r_egl.information133,
p_egl_attribute24 => r_egl.information134,
p_egl_attribute25 => r_egl.information135,
p_egl_attribute26 => r_egl.information136,
p_egl_attribute27 => r_egl.information137,
p_egl_attribute28 => r_egl.information138,
p_egl_attribute29 => r_egl.information139,
p_egl_attribute30 => r_egl.information140,
p_object_version_number => l_object_version_number,
p_effective_date => nvl(l_effective_date,p_effective_date)
);
select information30, context_business_group_id
from ben_copy_entity_txns_vw
where copy_entity_txn_id = p_copy_entity_txn_id;