[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select SRC_EFFECTIVE_DATE,CONTEXT_BUSINESS_GROUP_ID
From pqh_copy_entity_txns
Where copy_entity_txn_id=p_copy_entity_txn_id;
select ptp.*
from BEN_PL_TYP_F ptp
where ptp.pl_typ_id = p_pl_typ_id
-- and p_effective_date between effective_start_date and effective_end_date
and NOT EXISTS (SELECT information1
FROM BEN_COPY_ENTITY_RESULTS cer
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'PTP'
AND information1 = p_pl_typ_id
AND dml_operation = 'REUSE');
select table_route_id
from pqh_table_route
WHERE table_alias = c_table_alias;
select
information5 overview_Name,
table_alias,
information174,
information178,
information185,
information228, -- pl_typ_opt_typ_id
information258 -- oipl id
from
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and parent_entity_result_id = p_parent_entity_result_id
-- Inorder to populate mappings for rows outside effective date removing date track where clause
-- and p_effective_date between information2 and information3
and information170 is null
for update of
information170, information185;
select
context_business_group_id into l_business_group_id
from
pqh_copy_entity_txns
where
copy_entity_txn_id = p_copy_entity_txn_id;
select
copy_entity_result_id into l_parent_entity_result_id
from
ben_copy_entity_results
where
table_alias = 'ELP'
and copy_entity_txn_id = p_copy_entity_txn_id
and information1 = p_elig_prfl_id
and p_effective_date between information2 and information3;
select
name into l_name
from
per_absence_attendance_types
where
absence_attendance_type_id = l_crit.information174
and business_group_id = l_business_group_id
and date_effective <= p_effective_date
and (date_end is null or date_end >= p_effective_date);
select
meaning into l_information185
from
per_abs_attendance_reasons
,hr_leg_lookups
where
business_group_id = l_business_group_id
and abs_attendance_reason_id = l_crit.information178
and name = lookup_code
and lookup_type = 'ABSENCE_REASON'
and (start_date_active is null or start_date_active <= p_effective_date)
and (end_date_active is null or end_date_active >= p_effective_date);
update
ben_copy_entity_results
set
information170 = l_name,
information185 = l_information185
where current of c_crit;
select
name into l_name
from
per_competences_vl
where
(business_group_id is null or business_group_id = l_business_group_id)
and competence_id = l_crit.information174
and (date_from is null or date_from <= p_effective_date)
and (date_to is null or date_to >= p_effective_date);
select
rtl.name into l_information185
from
per_rating_levels_vl rtl
where
(rtl.business_group_id is null or rtl.business_group_id=l_business_group_id)
and rtl.rating_level_id = l_crit.information178;
update
ben_copy_entity_results
set
information170 = l_name,
information185 = l_information185
where current of c_crit;
select
ptp.name || ' - ' || opt.name name into l_name
from
ben_pl_typ_opt_typ_f pto, ben_pl_typ_f ptp, ben_opt_f opt
where
pto.business_group_id = l_business_group_id
and pto.pl_typ_opt_typ_id = l_crit.information228
and p_effective_date between pto.effective_start_date and pto.effective_end_date
and pto.pl_typ_id = ptp.pl_typ_id
and pto.business_group_id = ptp.business_group_id
and p_effective_date between ptp.effective_start_date and ptp.effective_end_date
and pto.opt_id = opt.opt_id
and pto.business_group_id = opt.business_group_id
and p_effective_date between opt.effective_start_date and opt.effective_end_date;
select
pln.name name into l_information185
from
ben_pl_typ_opt_typ_f pto, ben_oipl_f oipl, ben_pl_f pln
where
pto.business_group_id = l_business_group_id
and p_effective_date between pto.effective_start_date and pto.effective_end_date
and oipl.oipl_id = l_crit.information258
and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
and pto.pl_typ_opt_typ_id = l_crit.information228
and pto.opt_id = oipl.opt_id
and pto.business_group_id = oipl.business_group_id
and oipl.pl_id = pln.pl_id
and oipl.business_group_id = pln.business_group_id
and p_effective_date between pln.effective_start_date and pln.effective_end_date;
update
ben_copy_entity_results
set
information185 = l_information185
where current of c_crit;
update
ben_copy_entity_results
set
information170 = l_name
where current of c_crit;
select
information1 elig_prfl_id
from
ben_copy_entity_results
where
table_alias = 'ELP'
and copy_entity_txn_id = p_copy_entity_txn_id
and p_effective_date between information2 and information3 ;
Select
ptp.Information18 into l_opt_Typ_Cd
From
Ben_copy_entity_results ptp,
Ben_copy_entity_results pln
Where
ptp.copy_entity_txn_id = p_copy_entity_txn_id
And pln.copy_entity_txn_id = ptp.copy_entity_txn_id
And p_effective_date between ptp.information2 and ptp.information3
And p_effective_date between pln.information2 and pln.information3
And pln.table_alias='PLN'
And ptp.table_alias='PTP'
And ptp.information1=pln.Information248
And pln.status <>'DELETE'
And ptp.status <>'DELETE'
And pln.copy_entity_result_id = p_copy_entity_result_id;
Update
Ben_copy_entity_results pln
Set
Information11 = l_opt_Typ_Cd
Where
copy_entity_result_id = p_copy_entity_result_id;
select
cpy.*
from
BEN_COPY_ENTITY_RESULTS cpy
where
cpy.information4 = p_business_group_id
And cpy.copy_entity_txn_id = p_copy_entity_txn_id
And cpy.table_alias='CPY'
--And cpy.information260 is not null
and cpy.dml_operation <>'INSERT'
and cpy.information311 is null
for update ;
Select
Information309 ,
Information308 ,
Information12
into l_start_dt , l_end_dt , l_type
From
Ben_copy_entity_results yrp
Where
yrp.copy_entity_txn_id = p_copy_entity_txn_id
And yrp.table_alias='YRP'
And yrp.information1 = l_cpy_rec.Information240 ;
Update
Ben_copy_entity_results cpy
set
information311 = l_start_dt,
information310 = l_end_dt,
information12 = l_type
where
current of c_CPY;
select EPA.copy_entity_result_id,
EPA.information1 prtn_elig_id,
EPA.information260 PGM,
EPA.information259 CTP,
EPA.information256 CPP,
EPA.information261 PLN,
EPA.information258 COP
from ben_copy_entity_results EPA
where copy_entity_txn_id = c_copy_entity_txn_id
and table_alias = 'EPA'
and c_effective_date between information2 and information3
for update of information20, information272;
select CEP.copy_entity_result_id
from ben_copy_entity_results CEP
where copy_entity_txn_id = c_copy_entity_txn_id
and table_alias = 'CEP'
and c_effective_date between information2 and information3
and information229 = c_prtn_elig_id
for update of information20, information272;
update
ben_copy_entity_results
set
information20 = l_compobj_type,
information272 = l_compobj_id
Where Current Of c_epa ;
update
ben_copy_entity_results
set
information20 = l_compobj_type,
information272 = l_compobj_id
where current of c_cep;
select
copy_entity_result_id,
information1 vrbl_rt_prfl_id,
information266,
information186,
information2 effective_date
from
ben_copy_entity_results
where
table_alias = 'VPF'
and copy_entity_txn_id = p_copy_entity_txn_id
-- Inorder to populate mappings for rows outside effective date removing date track where clause
-- and p_effective_date between information2 and information3
and (information186 is null or information266 is null)
for update of information266, information186;
select
elp.information1 , elp.information170
into l_elig_prfl_id, l_elig_prfl_name
from
ben_copy_entity_Results elp,
ben_copy_entity_results vep
where
elp.table_alias = 'ELP'
and elp.copy_entity_txn_id = p_copy_entity_Txn_id
and l_vpf.effective_date between elp.information2 and elp.information3
and vep.table_alias = 'VEP'
and vep.copy_entity_txn_id = elp.copy_entity_txn_id
and l_vpf.effective_date between vep.information2 and vep.information3
and vep.information263 = elp.information1
and vep.information262 = l_vpf.vrbl_rt_prfl_id;
update
ben_copy_entity_results
set
information266 = l_elig_prfl_id,
information186 = l_elig_prfl_name
where current of c_vpf;
Update
Ben_copy_entity_results len1
Set
Information170 = (
Select
ler.information170 Name
From
Ben_copy_entity_results len,
Ben_copy_entity_results ler
Where
len.copy_entity_result_id = p_copy_entity_result_id
And ler.copy_entity_txn_id = len.copy_entity_txn_id
And p_effective_date between len.information2 and len.information3
And p_effective_date between ler.information2 and ler.information3
and ler.table_alias='LER'
and len.table_alias='LEN'
and len.information257 = ler.information1
)
Where
len1.copy_entity_result_id = p_copy_entity_result_id ;
Select
len.copy_entity_result_id
From
Ben_copy_entity_results len,
Ben_copy_entity_results pet
Where
len.copy_entity_txn_id = p_copy_entity_txn_id
And len.copy_entity_txn_id = pet.copy_entity_txn_id
And p_effective_date between len.information2 and len.information3
And p_effective_date between pet.information2 and pet.information3
and pet.table_alias='PET'
and len.table_alias='LEN'
and pet.information11='L'
and len.information232 = pet.information1
and pet.information260= p_pgm_id;
Select
LPR.copy_entity_result_id,
LPR.information15,
LPR.information13,
LPR.information261,
LPR.information256,
LPR.information257 ler_id,
LPR.information2 effective_date
From
Ben_copy_entity_results LPR
Where
LPR.copy_entity_txn_id = p_copy_entity_txn_id
And LPR.copy_entity_txn_id = LPR.copy_entity_txn_id
--And p_effective_date between LPR.information2 and LPR.information3
And LPR.table_alias='LPR1'
--And LPR.information260= p_pgm_id
And LPR.information15 is not null
And LPR.information103 is null -- not populated already
And LPR.dml_operation <>'DELETE'
For Update of LPR.Information103,LPR.information104;
select
information16 ENRT_CD,
information101 NEW_ENRT_CD,
information102 CUR_ENRT_CD
from
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'LPR1'
and information16 is not null
-- and p_effective_date between information2 and information3
and dml_operation <> 'DELETE'
for update of information101, information102;
Select
copy_entity_result_id ,information261 into plipCopyEntityResultId,l_pl_id
From
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and l_LPR.effective_date between information2 and information3
and table_alias='CPP'
and information1 = l_LPR.information256;
Select
cop.copy_entity_result_id into l_default_object_id
From
Ben_copy_entity_results lop,
Ben_copy_entity_results cop
Where
lop.copy_entity_txn_id = p_copy_entity_txn_id
And lop.copy_entity_txn_id = cop.copy_entity_txn_id
And l_LPR.effective_date between lop.information2 and lop.information3
And l_LPR.effective_date between cop.information2 and cop.information3
And lop.table_alias ='LOP'
And cop.table_alias = 'COP'
And cop.information1 = lop.information258
And cop.information261 = l_pl_id
And lop.information12 ='Y'
And lop.information257 = l_LPR.ler_id
And lop.dml_operation <>'DELETE'
and rownum=1;
Update
Ben_copy_entity_results LPR1
Set
LPR1.information103 =l_new_dflt_enrt_cd,
LPR1.information104 =l_old_dflt_enrt_cd,
LPR1.information160 = l_default_object_id,
lpr1.information161 = lpr1.copy_entity_result_id,
lpr1.information162 = plipCopyEntityResultId
Where current of c_LPR;
update
ben_copy_entity_results
set
information101 = l_new_enrt_cd,
information102 = l_cur_enrt_cd
where current of c_lpr1;
Select
LOP.copy_entity_result_id,
LOP.information16,
LOP.information258,
LOP.information2 effective_date
From
Ben_copy_entity_results LOP
Where
LOP.copy_entity_txn_id = p_copy_entity_txn_id
And LOP.copy_entity_txn_id = LOP.copy_entity_txn_id
--And p_effective_date between LOP.information2 and LOP.information3
And LOP.table_alias='LOP'
And LOP.information16 is not null
And LOP.information103 is null
And LOP.dml_operation <>'DELETE'
For Update of LOP.Information103,LOP.information104;
Select
copy_entity_result_id into oiplCopyEntityResultId
From
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and l_LOP.effective_date between information2 and information3
and table_alias='COP'
and information1 = l_LOP.information258;
Update
Ben_copy_entity_results LOP1
Set
LOP1.information103 =l_new_dflt_enrt_cd,
LOP1.information104 =l_old_dflt_enrt_cd,
-- If OIpl has the defaults flag set then make this the default object for this level
LOP1.Information160= decode(LOP1.Information12,'Y',oiplCopyEntityResultId,null),
lOP1.information161 = lOP1.copy_entity_result_id,
lop1.information162 = oiplCopyEntityResultId
Where current of c_LOP;
Select
COP.copy_entity_result_id,
COP.information26
From
Ben_copy_entity_results COP
Where
COP.copy_entity_txn_id = p_copy_entity_txn_id
And COP.copy_entity_txn_id = COP.copy_entity_txn_id
--And p_effective_date between COP.information2 and COP.information3
And COP.table_alias='COP'
And COP.information26 is not null
And COP.information106 is null
And COP.dml_operation <>'DELETE'
For Update of COP.Information106,COP.information107;
Update
Ben_copy_entity_results COP1
Set
COP1.information106 =l_new_dflt_enrt_cd,
COP1.information107 =l_old_dflt_enrt_cd,
-- If OIpl has the defaults flag set then make this the default object for this level
COP1.Information160= decode(COP1.Information18,'Y',COP1.copy_entity_result_id,null)
Where current of c_COP;
SELECT cpp.information261 pl_id
FROM ben_copy_entity_results cpp
WHERE cpp.copy_entity_txn_id = p_copy_entity_txn_id
AND cpp.table_alias = TABLE_ALIAS_CPP
AND cpp.information261 NOT IN
(SELECT pln.information1
FROM ben_copy_entity_results pln
WHERE pln.copy_entity_txn_id = p_copy_entity_txn_id
AND pln.table_alias = TABLE_ALIAS_PLN );
hr_utility.set_location('delete duplicate rows ',30);
other pages.Hence this delete.
get_txn_details (
p_copy_entity_txn_id
,l_business_group_id
,l_effective_date
);
DELETE FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND copy_entity_result_id NOT IN
( SELECT MIN(copy_entity_result_id)
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND NVL(dml_operation, DML_OPER_REUSE) = DML_OPER_REUSE
AND ( result_type_cd='DISPLAY' or
l_effective_date between information2 and information3
)
GROUP BY table_alias, information1)
AND NVL(DML_OPERATION, DML_OPER_REUSE) = DML_OPER_REUSE and
TABLE_ALIAS <> 'BEN_PDW_TASK_LIST';
DELETE
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND information256 = p_id
AND table_alias in (TABLE_ALIAS_LPR);
select pln.*
from BEN_PL_F pln
where pln.pl_id = p_pl_id
and p_effective_date between effective_start_date and effective_end_date
and NOT EXISTS (SELECT information1
FROM BEN_COPY_ENTITY_RESULTS cer
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'PLN'
AND information1 = p_pl_id
AND dml_operation = 'REUSE');
select table_route_id
from pqh_table_route
WHERE table_alias = c_table_alias;
Select information170 name
from ben_copy_entity_results
where table_alias='ELP'
and copy_entity_txn_id=c_copy_entity_txn_id
and information1=c_eligy_prfl_id;
Select cep.information263 ELIGY_PRFL_ID,
cep.information12 mndtry_flag ,
cep.copy_entity_result_id
From ben_copy_entity_results cep
Where cep.copy_entity_txn_id=p_copy_entity_txn_id
and cep.table_alias='CEP'
and cep.INFORMATION229=c_prtn_elig_id;
Select 1
From ben_copy_entity_results
Where table_alias='ELP'
and copy_entity_txn_id=c_copy_txn_id
and information1=c_eligy_prfl_id;
Select eligy_prfl_id
From ben_eligy_prfl_f
where business_group_id =l_business_group_id
and l_effective_date between effective_start_date and effective_end_date
and stat_cd='A' and BNFT_CAGR_PRTN_CD='BNFT'
and eligy_prfl_id not in (select information1
from ben_copy_entity_results
where copy_entity_txn_id=p_copy_entity_txn_id
and table_alias='ELP'
);
Select xyz.information262 VRBL_RT_PRFL_ID,
xyz.copy_entity_result_id
From ben_copy_entity_results xyz
Where xyz.copy_entity_txn_id=p_copy_entity_txn_id
and xyz.table_alias=decode(p_vrbl_usg_code,'CVG','BVR1','AVR')
and decode(table_alias,'BVR1',information238,information253)=p_vrbl_cvg_rt_id
and dml_operation <> 'DELETE';
select dml_operation,
datetrack_mode,
information32 uses_vrbl_rt_flag,
future_data_exists
from ben_copy_entity_results abr
where abr.copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'ABR'
and information1 = p_vrbl_cvg_rt_id
and l_effective_date between information2 and information3
and dml_operation <> 'DELETE';
select
information83 into l_RT_ELIG_PRFL_FLAG
from
ben_copy_entity_results
where
table_alias = 'VPF'
and copy_entity_txn_id = p_copy_entity_txn_id
and information1 = l_rec.VRBL_RT_PRFL_ID
and l_effective_date between information2 and information3;
select
elp.information1,
elp.information170 into l_elig_prfl_id, l_elp_name
from
ben_copy_entity_results vpf,
ben_copy_entity_results vep,
ben_copy_entity_results elp
where
vpf.copy_entity_txn_id = elp.copy_entity_txn_id
and vpf.copy_entity_txn_id = vep.copy_entity_txn_id
and vpf.copy_entity_txn_id = p_copy_entity_txn_id
and vpf.table_alias = 'VPF'
and vep.table_alias = 'VEP'
and elp.table_alias = 'ELP'
and vpf.information1 = l_rec.VRBL_RT_PRFL_ID
and vpf.information1 = vep.information262
and elp.information1 = vep.information263
and l_effective_date between vpf.information2 and vpf.information3
and l_effective_date between vep.information2 and vep.information3
and l_effective_date between elp.information2 and elp.information3;
update
ben_copy_entity_results
set
INFORMATION266 = l_elig_prfl_id,
INFORMATION186 = l_elp_name
where
copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'VPF'
and information1 = l_rec.VRBL_RT_PRFL_ID
and l_effective_date between information2 and information3;
if (l_rate_row.dml_operation = 'REUSE' or l_rate_row.dml_operation = 'UPDATE') and l_rate_row.uses_vrbl_rt_flag = l_rate_vpf_exits then
-- then make no change since there is no change to rate row.
null;
elsif (l_rate_row.dml_operation = 'REUSE' or l_rate_row.dml_operation = 'UPDATE') and l_rate_row.uses_vrbl_rt_flag <> l_rate_vpf_exits then
-- there can be two cases when future date may or may not exists
-- if the future data exists we need to set datetrack mode to correction because we are not asking the question on page.
if l_rate_row.future_data_exists = 'Y' then
Update ben_copy_entity_results
set INFORMATION32=l_rate_vpf_exits,
dml_operation = 'UPDATE',
datetrack_mode = 'CORRECTION'
where copy_entity_txn_id=p_copy_entity_txn_id
and table_alias='ABR'
and information1= p_vrbl_cvg_rt_id
and l_effective_date between information2 and information3;
Update ben_copy_entity_results
set INFORMATION32=l_rate_vpf_exits,
dml_operation = 'UPDATE',
datetrack_mode = 'UPDATE'
where copy_entity_txn_id=p_copy_entity_txn_id
and table_alias='ABR'
and information1= p_vrbl_cvg_rt_id
and l_effective_date between information2 and information3;
Update ben_copy_entity_results
set INFORMATION32=l_rate_vpf_exits
where copy_entity_txn_id=p_copy_entity_txn_id and table_alias='ABR'
and information1= p_vrbl_cvg_rt_id
and l_effective_date between information2 and information3;
select
information186,
information266,
copy_entity_result_id
from
ben_copy_entity_results
where
table_alias = 'VPF'
and copy_entity_txn_id = p_copy_entity_txn_id
and information1 = p_vrbl_rt_prfl_id
and p_effective_date between information2 and information3
and (information266 is null or information186 is null)
and dml_operation <> 'DELETE'
and status='VALID'
for update of information266, information186;
select
elp.information1,
elp.information170 into l_elig_prfl_id, l_elpro_name
from
ben_copy_entity_results vpf,
ben_copy_entity_results vep,
ben_copy_entity_results elp
where
vpf.copy_entity_txn_id = elp.copy_entity_txn_id
and vpf.copy_entity_txn_id = vep.copy_entity_txn_id
and vpf.copy_entity_txn_id = p_copy_entity_txn_id
and vpf.table_alias = 'VPF'
and vep.table_alias = 'VEP'
and elp.table_alias = 'ELP'
and vpf.information1 = p_VRBL_RT_PRFL_ID
and vpf.information1 = vep.information262
and elp.information1 = vep.information263
and p_effective_date between vpf.information2 and vpf.information3
and vpf.dml_operation <> 'DELETE' and vpf.status='VALID'
and p_effective_date between vep.information2 and vep.information3
and vep.dml_operation <> 'DELETE' and vep.status='VALID'
and p_effective_date between elp.information2 and elp.information3
and elp.dml_operation <> 'DELETE' and elp.status='VALID';
update
ben_copy_entity_results
set
information266 = l_elig_prfl_id,
information186 = l_elpro_name
where
current of c_vpf;
The following procedures call plan copy apis to selectively copy delpro to staging area.
Out of the following procedures we can probably ask plan copy to provide a public function create_dep_elpro_results
which will just coy a Dpny Elig and its criteria
*/
FUNCTION get_dpnt_prfl_name(
p_eligy_prfl_id IN Number
,p_copy_entity_txn_id IN Number
)
RETURN VARCHAR2 IS
Cursor csr_txn_prfl_name (
c_eligy_prfl_id NUMBER
,c_copy_entity_txn_id NUMBER
)
IS
Select information170 name
from ben_copy_entity_results
where table_alias='DCE'
and copy_entity_txn_id=c_copy_entity_txn_id
and information1=c_eligy_prfl_id;
Select ade.information255 ELIGY_PRFL_ID,
ade.information11 mndtry_flag ,
ade.copy_entity_result_id
From ben_copy_entity_results ade
Where ade.copy_entity_txn_id=p_copy_entity_txn_id
and ade.table_alias='ADE'
and decode(c_dpnt_dsgn_level_code,'PL',ade.information261,'PTIP',ade.information259)=c_dpnt_dsgn_object_id;
Select 1
From ben_copy_entity_results
Where table_alias='DCE'
and copy_entity_txn_id=c_copy_txn_id
and information1=c_eligy_prfl_id;
Select table_alias,INFORMATION261, INFORMATION246
From ben_copy_entity_results
Where parent_entity_result_id=c_parent_id;
Select 'Y'
From BEN_COPY_ENTITY_RESULTS
Where copy_entity_txn_id=p_copy_entity_txn_id
AND table_alias=p_table_alias
AND information1=p_information1
AND result_type_cd='DISPLAY';
Select table_alias,INFORMATION261,copy_entity_result_id,information5,information11
From ben_copy_entity_results
Where parent_entity_result_id=c_parent_id and TABLE_ALIAS='EPG';
Select table_alias, INFORMATION222,INFORMATION223,INFORMATION224,INFORMATION233,INFORMATION241,INFORMATION243,INFORMATION245,
INFORMATION246,INFORMATION254,INFORMATION272
From ben_copy_entity_results
Where parent_entity_result_id=c_parent_id
and TABLE_ALIAS in ('EAP','ECL','ECP','EHW','ELS','EPF','EBN','EPZ','ESA','ECV');
select org_information5
from hr_organization_information org
where org.organization_id =c_bg_id
and org.org_information_context = 'Business Group Information';
UPDATE ben_copy_entity_results set INFORMATION5= l_ppl_flx where copy_entity_result_id=l_rec.copy_entity_result_id;
if(p_pgm_id is not null) then -- only Program is selected
begin
select -- if PGM is copied to staging, return the Pgm Name in Staging
information170 into l_overview_name
from
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and information1 = p_pgm_id
and table_alias = 'PGM'
and l_effective_date between information2 and information3;
select
name into l_overview_name
from
ben_pgm_f pgm
where
pgm.business_group_id = l_business_group_id
and pgm.pgm_id = p_pgm_id
and l_effective_date between pgm.effective_start_date and pgm.effective_end_date;
select -- if Pgm and Plan Type is copied to staging, then return their names in staging
pgm.information170 || ' - ' || ptp.information170 into l_overview_name
from
ben_copy_entity_results pgm,
ben_copy_entity_results ctp,
ben_copy_entity_results ptp
where
ctp.copy_entity_txn_id = p_copy_entity_txn_id
and ctp.table_alias = 'CTP'
and ptp.copy_entity_txn_id = ctp.copy_entity_txn_id
and ptp.table_alias = 'PTP'
and pgm.copy_entity_txn_id = ctp.copy_entity_txn_id
and pgm.table_alias = 'PGM'
and ctp.information1 = p_ctp_id
and l_effective_date between ctp.information2 and ctp.information3
and ptp.information1 = ctp.information248
and l_effective_date between ptp.information2 and ptp.information3
and pgm.information1 = ctp.information260
and l_effective_date between pgm.information2 and pgm.information3;
select
pgm.name || ' - ' || ptp.name into l_overview_name
from
ben_pgm_f pgm,
ben_ptip_f ctp,
ben_pl_typ_f ptp
where
ctp.business_group_id = l_business_group_id
and ctp.ptip_id = p_ctp_id
and ctp.pgm_id = pgm.pgm_id
and ptp.pl_typ_id = ctp.pl_typ_id
and ctp.business_group_id = pgm.business_group_id
and ptp.business_group_id = pgm.business_group_id
and l_effective_date between pgm.effective_start_date and pgm.effective_end_date
and l_effective_date between ptp.effective_start_date and ptp.effective_end_date
and l_effective_date between ctp.effective_start_date and ctp.effective_end_date;
Select cpe.* from
Ben_copy_entity_results cpe
Where
cpe.copy_Entity_txn_Id = p_copy_entity_txn_id
And cpe.Table_Alias='PLN'
--And cpe.Dml_operation='INSERT'
And cpe.Dml_operation <> 'DELETE'
And p_effective_date between cpe.Information2 And cpe.Information3 ;
Select cpe.* from
Ben_copy_entity_results cpe
Where
cpe.copy_Entity_txn_Id = p_copy_entity_txn_id
And cpe.Table_Alias='CPY'
--And cpe.Dml_operation='INSERT'
And cpe.Dml_operation <> 'DELETE'
And cpe.Information260 is not null
Order by cpe.Information311,cpe.Information310
For Update of cpe.Information262 ;
delete from ben_copy_entity_results
where
copy_entity_txn_id =p_copy_entity_txn_id
And table_Alias='CPY'
And information261 is not null ;
Update Ben_Copy_Entity_Results
Set Information262 = l_pgm_Yr_Perd_Sequence_Number
Where Current Of c_CPY ;
Select
max(ordr_num)+10 into l_Sequence_Number
From
Ben_Popl_Yr_Perd cpy
Where
cpy.pl_id = l_PLN.Information1
And cpy.business_group_id = p_business_group_id;
Select
8 into l_pkId
From
Ben_Popl_Yr_Perd cpy,
Ben_Yr_Perd yrp
Where
cpy.pl_id = l_PLN.Information1
And cpy.business_group_id = p_business_group_id
And cpy.business_group_id = yrp.business_group_id
And cpy.yr_perd_id = yrp.yr_perd_id
And yrp.start_date = l_CPY.information311
And yrp.end_date = l_CPY.information310;
/*Select
8 into l_pkId
From
Ben_copy_entity_results cpy
Where
cpy.copy_entity_txn_id = p_copy_entity_txn_id
And p_effective_date between cpy.information2 and cpy.information3
And cpy.information261 = l_PLN.Information1
And cpy.information311 =l_CPY.information311
And cpy.information310 = l_CPY.information310
And cpy.dml_operation <>'DELETE' ;*/
Select BEN_POPL_YR_PERD_S.nextval into l_pkId
From dual ;
p_dml_operation =>'INSERT' ,
p_table_Alias => 'CPY' ,
p_information1 => l_pkId,
p_information4 => l_CPY.information4,
p_information5 => l_CPY.information311 ||'-'||l_CPY.information310, -- 9999 put name for h-grid
--p_information265 => 0,
--
p_information261 => l_PLN.information1, -- Plan Id
p_Information262 => l_Sequence_Number ,
p_information240 => l_CPY.information240, -- Year Period Id
--
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date
);
Select psl.Name into l_temp
From
BEN_PER_INFO_CHG_CS_LER_F psl
Where
psl.business_group_id = p_business_group_id
And p_effective_date between psl.effective_start_date and psl.effective_end_date
And psl.Name = l_name ;
Select
to_char(PERSON_TYPE_ID) into l_val
From
Per_Person_Types ppt
Where
ppt.System_Person_Type =p_val
And ppt.ACTIVE_FLAG='Y'
And ppt.Business_Group_Id = p_business_group_id
And ppt.default_flag='Y' ;
Select
psl.*
from
BEN_PER_INFO_CHG_CS_LER_F psl
Where
psl.business_group_id = p_business_group_id
And p_effective_date between psl.effective_start_date and psl.effective_end_date
And
(
Upper(psl.SOURCE_TABLE) = upper(p_source_table)
And upper(psl.Source_column)=upper(p_source_column)
And psl.Old_Val = p_old_val
And NVL(psl.New_Val,-1) = p_new_Val
) ;
Select cpe.*
from
Ben_copy_entity_Results cpe,
Ben_copy_entity_Results lpl
Where
cpe.copy_entity_txn_id=0
And cpe.Table_Alias ='PSL'
And lpl.copy_entity_txn_id=p_copy_entity_txn_id
And lpl.Table_Alias='LPL'
And p_effective_date between lpl.Information2 and lpl.Information3
And lpl.Information258 = cpe.copy_entity_result_id
And lpl.dml_operation='INSERT' ;
Select
BEN_PER_INFO_CHG_CS_LER_F_S.nextVal into
l_per_info_chg_cs_ler_id
From dual ;
p_dml_operation =>'INSERT' ,
p_table_Alias => 'PSL' ,
p_information1 => l_per_info_chg_cs_ler_id,
p_information4 => p_business_group_id,
p_information11 =>'N',
p_information141 => upper(l_CPE.INFORMATION12), --SOURCE COLUMN
p_information142 => upper(l_CPE.INFORMATION11), --SOURCE TABLE
p_Information185 => decode_Value( p_business_group_id,
l_CPE.INFORMATION13,
l_CPE.Information15), -- OLD_VAL
p_INFORMATION186 => decode_Value( p_business_group_id,
l_CPE.INFORMATION14,
l_CPE.Information15), -- NEW_VAL
p_INFORMATION218 => l_Name,
--
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date
);
Update Ben_Copy_Entity_Results
Set
INFORMATION258 = l_per_info_chg_cs_ler_id -- PER_INFO_CHG_CS_LER_ID
Where
Copy_Entity_Txn_Id= p_copy_entity_txn_id
--And p_effective_date between Information2 And Information3
And Table_Alias='LPL'
And Information258 = l_CPE.Copy_entity_result_id ;
select
PSTL_ZIP_RNG_ID
from
BEN_PSTL_ZIP_RNG_F
where
business_group_id = p_business_group_id;
select
BENFTS_GRP_ID
from
BEN_BENFTS_GRP
where business_group_id = p_business_group_id;
select yrp.*
from BEN_YR_PERD yrp
where
yrp.business_group_id = p_business_group_id
and not exists (
select null
from ben_copy_entity_results cpe,
pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
and trt.table_route_id = cpe.table_route_id
and trt.table_alias = c_table_alias
and information1 = yrp.yr_perd_id
and information4 = yrp.business_group_id
);
select table_route_id
from pqh_table_route trt
where
trt.table_alias = c_parent_table_alias ;
select
information263 ordr_num
from
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
and p_effective_date between information2 and information3
and information263 is null
for update of information263;
select
information268 ordr_num
from
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CTP'
and p_effective_date between information2 and information3
and information268 is null
for update of information268;
update
ben_copy_entity_results
set
information263 = l_sequence
where current of c_cpp_sequence;
update
ben_copy_entity_results
set
information268 = l_sequence
where current of c_ctp_sequence;
select pbt.balance_name||' - '||pbd.dimension_name name
from pay_balance_types pbt,pay_balance_dimensions pbd, pay_defined_balances pdb
where (pdb.business_group_id is null or pdb.business_group_id = c_bg_id )
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.defined_balance_id=c_balance_id;
select name
from ben_bnfts_bal_f
where business_group_id = c_bg_id
and c_effective_date between effective_start_date and effective_end_date
and bnfts_bal_id =c_balance_id;
select information170
from ben_copy_entity_results where
table_alias='BNB' and copy_entity_txn_id=c_txn_id
and information1=c_balance_id and
c_effective_date between information2 and information3;
select name
from fnd_currencies_vl
where (start_date_active is null or start_date_active <=c_effective_date)
and (end_date_active is null or end_date_active >= c_effective_date)
and enabled_flag = 'Y' and currency_code=c_currency_code;
Select information170
From ben_copy_entity_results
Where copy_entity_txn_id=p_copy_entity_txn_id
and table_alias=p_table_alias
and information1=p_information1
and p_effective_date between nvl(information2,p_effective_date) and nvl(information3,p_effective_date);
select information218 into l_name
from ben_copy_entity_results
where copy_entity_txn_id=p_copy_entity_txn_id
and table_alias=p_table_alias
and information1=p_information1;
select information142||' - '|| information141 into l_name
from ben_copy_entity_results
where copy_entity_txn_id=p_copy_entity_txn_id
and table_alias=p_table_alias
and information1=p_information1
and rownum=1;
select
name into l_name
from
ben_pl_f
where
pl_id = p_information1
and business_group_id = l_bg_id
and l_effective_date between effective_start_date and effective_end_date;
Select 'Y'
From BEN_COPY_ENTITY_RESULTS
Where copy_entity_txn_id=p_copy_entity_txn_id
And table_alias=p_table_alias
And information1=p_information1
And result_type_cd='DISPLAY';
select max(information263)
from ben_copy_entity_results
where table_alias = 'CPP'
and copy_entity_txn_id = c_copy_entity_txn_id;
select max(information268)
from ben_copy_entity_results
where table_alias = 'CTP'
and copy_entity_txn_id = c_copy_entity_txn_id;
select max(information263)
from ben_copy_entity_results
where table_alias = 'COP'
and copy_entity_txn_id = c_copy_entity_txn_id
and information261 = c_plan_id;
Select COPY_ENTITY_RESULT_ID From BEN_COPY_ENTITY_RESULTS
Where copy_entity_txn_id=p_copy_entity_txn_id
And table_alias=p_table_alias
And information1=p_information1
And result_type_cd='DISPLAY';
Select
yrp.information309 strt_dt,
yrp.information308 end_dt
From
Ben_copy_entity_results yrp,
Ben_copy_entity_results enp
Where
ENP.copy_entity_result_id = p_copy_entity_result_id
And yrp.copy_entity_txn_id = ENP.copy_entity_txn_id
and yrp.table_alias='YRP'
and ENP.table_alias='ENP'
and ENP.information240 = yrp.information1 ;
select
meaning||' '||to_char(enp.information318,'yyyy-mm-dd')||' '||to_char(enp.information317,'yyyy-mm-dd') into l_enp_name
from
hr_lookups,
ben_copy_entity_results enp,
ben_copy_entity_results pet
where
lookup_type = 'BEN_ENRT_TYP_CYCL'
and enp.copy_entity_result_id = p_copy_entity_result_id
and pet.copy_entity_txn_id = enp.copy_entity_txn_id
and pet.table_alias = 'PET'
and enp.information232 = pet.information1
and lookup_code = pet.information11
and p_effective_date between pet.information2 and pet.information3;
Update
Ben_copy_entity_results ENP1
Set
ENP1.Information310 = l_ENP.strt_dt,
ENP1.Information311 = l_ENP.end_dt,
ENP1.Information5 = l_enp_name
Where
ENP1.copy_entity_result_id = p_copy_entity_result_id ;
Select
ENP.copy_entity_result_id
From
Ben_copy_entity_results ENP,
Ben_copy_entity_results pet
Where
ENP.copy_entity_txn_id = p_copy_entity_txn_id
And ENP.copy_entity_txn_id = pet.copy_entity_txn_id
And p_effective_date between pet.information2 and pet.information3
and pet.table_alias='PET'
and ENP.table_alias='ENP'
and pet.information11 in ('O','A')
and ENP.information232 = pet.information1
and pet.information260= p_pgm_id;
Select
ctp.copy_entity_result_id,
ctp.information45,
ctp.information248,
ctp.information2 effective_date -- Add effective Date for populating mappings
From
Ben_copy_entity_results ctp
Where
ctp.copy_entity_txn_id = p_copy_entity_txn_id
And ctp.copy_entity_txn_id = ctp.copy_entity_txn_id
--And p_effective_date between ctp.information2 and ctp.information3
And ctp.table_alias='CTP'
And ctp.information260= p_pgm_id
And ctp.information45 is not null
And ctp.information106 is null -- not populated already
And ctp.dml_operation <>'DELETE'
For Update of ctp.Information106,ctp.information107;
select
information44 ENRT_CD,
information101 NEW_ENRT_CD,
information102 CUR_ENRT_CD
from
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CTP'
and information44 is not null
-- and p_effective_date between information2 and information3
and dml_operation <> 'DELETE'
for update of information101, information102;
Select
cpp.copy_entity_result_id into l_default_object_id
From
ben_copy_entity_results cpp
,ben_copy_entity_results pln
Where
cpp.copy_entity_txn_id = p_copy_entity_txn_id
-- Take Effective Date from the cursor above
And l_CTP.effective_date between cpp.information2 and cpp.information3
And cpp.information13 ='Y'
And cpp.information260= p_pgm_id
And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
And l_CTP.effective_date between pln.information2 and pln.information3
And pln.information248 = l_CTP.information248
And pln.information1 = cpp.information261
And cpp.table_alias='CPP'
And pln.table_alias='PLN'
And cpp.dml_operation <>'DELETE'
And pln.dml_operation <>'DELETE'
and rownum =1 ;
Select
cop.copy_entity_result_id into l_default_object_id
From
ben_copy_entity_results pln
,ben_copy_entity_results cop
Where
pln.copy_entity_txn_id = p_copy_entity_txn_id
And l_CTP.effective_date between pln.information2 and pln.information3
And cop.copy_entity_txn_id=pln.copy_entity_txn_id
And l_CTP.effective_date between cop.information2 and cop.information3
And pln.information1 = cop.information261
And pln.information248= l_CTP.information248
And cop.information18 ='Y'
And pln.table_alias='PLN'
And cop.table_alias='COP'
And pln.dml_operation <>'DELETE'
And cop.dml_operation <>'DELETE'
and rownum =1 ;
Update
Ben_copy_entity_results ctp1
Set
ctp1.information106 =l_new_dflt_enrt_cd ,
ctp1.information107 =l_old_dflt_enrt_cd ,
ctp1.information160 = l_default_object_Id
Where current of c_CTP;
update
ben_copy_entity_results
set
information101 = l_new_enrt_cd,
information102 = l_cur_enrt_cd
where current of C_Enrt_Ctp;
Select
ctp.copy_entity_result_id,
ctp.information12,
ctp.information248,
ctp.information259,
ctp.information257 ler_id,
ctp.information2 effective_date
From
Ben_copy_entity_results ctp
Where
ctp.copy_entity_txn_id = p_copy_entity_txn_id
And ctp.copy_entity_txn_id = ctp.copy_entity_txn_id
--And p_effective_date between ctp.information2 and ctp.information3
And ctp.table_alias='LCT'
--And ctp.information260= p_pgm_id
And ctp.information12 is not null
And ctp.information103 is null
And ctp.dml_operation <>'DELETE'
For Update of ctp.Information103,ctp.information104;
select
information14 ENRT_CD,
information101 NEW_ENRT_CD,
information102 CUR_ENRT_CD
from
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'LCT'
and information14 is not null
-- and p_effective_date between information2 and information3
and dml_operation <> 'DELETE'
for update of information101, information102;
Select
copy_entity_result_id,information248 into ptipCopyEntityResultId,l_ptp_id
From
ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and l_LCT.effective_date between information2 and information3
and table_alias='CTP'
and information1 = l_LCT.information259;
Select
cpp.copy_entity_result_id into l_default_object_id
From
ben_copy_entity_results lpr1
,ben_copy_entity_results cpp
,ben_copy_entity_results pln
,ben_copy_entity_results ctp
Where
lpr1.copy_entity_txn_id = p_copy_entity_txn_id
And ctp.copy_entity_txn_id = lpr1.copy_entity_txn_id
And cpp.copy_entity_txn_id = lpr1.copy_entity_txn_id
And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
And l_LCT.effective_date between pln.information2 and pln.information3
And l_LCT.effective_date between ctp.information2 and ctp.information3
And l_LCT.effective_date between lpr1.information2 and lpr1.information3
And l_LCT.effective_date between cpp.information2 and cpp.information3
And ctp.information248 = l_ptp_id
And lpr1.information256 = cpp.information1
And lpr1.information13 ='Y'
And lpr1.information257 = l_LCT.ler_id
--And cpp.information260 = p_pgm_id
And pln.information248 = ctp.information248
And pln.information1 = cpp.information261
And lpr1.table_alias = 'LPR1'
And ctp.table_alias = 'CTP'
And cpp.table_alias = 'CPP'
And pln.table_alias = 'PLN'
And lpr1.dml_operation <> 'DELETE'
And ctp.dml_operation <> 'DELETE'
And cpp.dml_operation <> 'DELETE'
And pln.dml_operation <> 'DELETE'
and rownum = 1 ;
Select
cop.copy_entity_result_id into l_default_object_id
From
ben_copy_entity_results lop
,ben_copy_entity_results cop
,ben_copy_entity_results cpp
,ben_copy_entity_results pln
,ben_copy_entity_results ctp
Where
lop.copy_entity_txn_id = p_copy_entity_txn_id
And ctp.copy_entity_txn_id = lop.copy_entity_txn_id
And cpp.copy_entity_txn_id = lop.copy_entity_txn_id
And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
And cop.copy_entity_txn_id = pln.copy_entity_txn_id
And l_LCT.effective_date between pln.information2 and pln.information3
And l_LCT.effective_date between ctp.information2 and ctp.information3
And l_LCT.effective_date between lop.information2 and lop.information3
And l_LCT.effective_date between cop.information2 and cop.information3
And l_LCT.effective_date between cpp.information2 and cpp.information3
And ctp.information248 = l_ptp_id
And lop.information258 = cop.information1
And lop.information12 ='Y'
And lop.information257 = l_LCT.ler_id
--And cpp.information260 = p_pgm_id
And pln.information248 = ctp.information248
And pln.information1 = cpp.information261
And cpp.information261 = cop.information261
And lop.table_alias = 'LOP'
And ctp.table_alias = 'CTP'
And cpp.table_alias = 'CPP'
And pln.table_alias = 'PLN'
And cop.table_alias = 'COP'
And lop.dml_operation <> 'DELETE'
And ctp.dml_operation <> 'DELETE'
And cpp.dml_operation <> 'DELETE'
And pln.dml_operation <> 'DELETE'
And cop.dml_operation <> 'DELETE'
and rownum = 1 ;
Update
Ben_copy_entity_results ctp1
Set
ctp1.information103 =l_new_dflt_enrt_cd,
ctp1.information104 =l_old_dflt_enrt_cd,
ctp1.information160 = l_default_object_id,
ctp1.information161 = ctp1.copy_entity_result_id,
ctp1.information162 = ptipCopyEntityResultId
Where current of c_LCT;
update
ben_copy_entity_results
set
information101 = l_new_enrt_cd,
information102 = l_cur_enrt_cd
where current of c_enrt_lct;
Select
CPP.copy_entity_result_id,
CPP.information21,
CPP.information13,
CPP.information261,
CPP.information2 effective_date
From
Ben_copy_entity_results CPP
Where
CPP.copy_entity_txn_id = p_copy_entity_txn_id
And CPP.copy_entity_txn_id = CPP.copy_entity_txn_id
--And p_effective_date between CPP.information2 and CPP.information3
And CPP.table_alias='CPP'
And CPP.information260= p_pgm_id
And CPP.information21 is not null
And CPP.information106 is null -- not populated already
And CPP.dml_operation <>'DELETE'
For Update of CPP.Information106,CPP.information107;
select
information22 ENRT_CD,
information101 NEW_ENRT_CD,
information102 CUR_ENRT_CD
from
ben_copy_entity_results
where
copy_entity_Txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
-- and p_effective_date between information2 and information3
and dml_operation <> 'DELETE'
for update of information101, information102;
Select
cop.copy_entity_result_id into l_default_object_id
From
Ben_copy_entity_results cop
Where
cop.copy_entity_txn_id = p_copy_entity_txn_id
And l_CPP.effective_date between cop.information2 and cop.information3
And cop.table_alias = 'COP'
And cop.information261 = l_CPP.information261
And cop.information18 ='Y'
and rownum =1;
Update
Ben_copy_entity_results CPP1
Set
CPP1.information106 =l_new_dflt_enrt_cd,
CPP1.information107 =l_old_dflt_enrt_cd,
CPP1.information160 = l_default_object_id
Where current of c_CPP;
update
ben_copy_entity_results
set
information101 = l_new_enrt_cd,
information102 = l_cur_enrt_cd
where current of C_Enrt_Cpp;
Select pgm.*
from
Ben_copy_entity_results pgm
Where
pgm.copy_entity_txn_id = p_copy_entity_txn_id
And pgm.table_alias='PGM'
for update of pgm.information101,pgm.information102;
Update
Ben_copy_entity_results pgm1
Set
information101= l_new_enrt_cd,
information102= l_cur_enrt_cd
Where current of c_PGM ;
Select
pln.copy_entity_result_id
From
Ben_copy_entity_results cpp,
Ben_copy_entity_results pln
Where
cpp.copy_entity_txn_id = p_copy_entity_txn_id
And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
And p_effective_date between cpp.information2 and cpp.information3
And p_effective_date between pln.information2 and pln.information3
And cpp.table_alias ='CPP'
And pln.table_alias ='PLN'
And cpp.information260 = p_pgm_id
And pln.status<>'DELETE'
And cpp.status<>'DELETE'
And cpp.information261 = pln.information1 ;
Select
pln.copy_entity_result_id
From
Ben_copy_entity_results cpp,
Ben_copy_entity_results pln
Where
cpp.copy_entity_txn_id = 229
And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
And sysdate between cpp.information2 and cpp.information3
And sysdate between pln.information2 and pln.information3
And cpp.table_alias ='CPP'
And pln.table_alias ='PLN'
And cpp.information260 = 310
And pln.status<>'DELETE'
And cpp.status<>'DELETE'
And cpp.information261 = pln.information1 ; */
select context into l_context
from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id ;
delete from ben_copy_entity_results
where rowid in ( select min(rowid)
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and information1 is not null
group by table_alias,information1, information2, information3
having count( table_alias) > 1 );
update ben_copy_entity_results a
set future_data_exists ='Y'
where a.copy_entity_txn_id = p_copy_entity_txn_id
and a.future_data_exists is null
and a.information3 < to_date('4712/12/31','YYYY/MM/DD')
and exists
( select 'Y' from ben_copy_entity_results b
where b.copy_entity_txn_id = a.copy_entity_txn_id
and b.table_alias = a.table_alias
and b.information1 = a.information1
and b.information2 = a.information3+1);
update ben_copy_entity_results
set future_data_exists = nvl(future_Data_exists,'N'),
result_type_cd = 'DISPLAY'
where copy_entity_txn_id = p_copy_entity_txn_id;
Select fff.information112 into l_rule_name
from ben_copy_entity_results fff,
ben_copy_entity_results ben
where fff.table_alias='FFF'
and fff.copy_entity_txn_id=p_copy_entity_txn_id
and ben.copy_entity_txn_id=p_copy_entity_txn_id
and fff.information1=decode(p_table_alias,'CTP',ben.INFORMATION277,'CPP',ben.INFORMATION264,'COP',ben.INFORMATION266,'PLN',ben.information272,'LPR1',ben.INFORMATION263,'LOP',ben.INFORMATION264,'LCT',ben.INFORMATION13,'CCM',ben.INFORMATION266)
and ben.information1=p_id
and l_effective_date between ben.information2 and ben.information3
and l_effective_date between fff.information2 and fff.information3;
procedure update_task_list_row(p_copy_entity_txn_id Number,p_effective_date Date)
is
cursor c_pgm is select information1 pgm_id, information170 name, information36 Alws_Unrstrctd_Enrt_Flag,information50 pgm_uom
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PGM'
and p_effective_date between information2 and information3;
update ben_copy_entity_results
set INFORMATION260 = l_pgmrow.pgm_id /*SAVED_TASK_PGMID*/
,INFORMATION185 = l_pgmrow.name /* SAVED_PROGRAM_NAME*/
,INFORMATION14 = l_pgmrow.Alws_Unrstrctd_Enrt_Flag /* PGM_ALWS_UNRSTRCTD*/
,INFORMATION15 = l_pgmrow.pgm_uom /*PGM_UOM */
,INFORMATION100 = 'Y' -- PROGRAM_TASK,
,INFORMATION101 = 'Y' -- PLAN_AND_OPTIONS_TASK,
,INFORMATION102 = 'Y' -- SCHEDULING_TASK,
,INFORMATION103 = 'Y' -- ENROLLMENT_REQUIREMENTS_TASK,
,INFORMATION104 = 'Y' -- ELIGIBILITY_PROFILE_TASK,
,INFORMATION105 = 'Y' -- DEFAULT_ENROLLMENT_TASK,
,INFORMATION106 = 'Y' -- REVIEW_AND_SUBMIT_TASK
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'BEN_PDW_TASK_LIST' ;
end update_task_list_row;
update pqh_copy_entity_txns
set status = 'COPYING_IN_PROGRESS'
,start_with = null
where copy_entity_txn_id = p_copy_entity_txn_id;
update_task_list_row(p_copy_entity_txn_id,fnd_date.canonical_to_date(p_effective_date));
update pqh_copy_entity_txns
set status = 'COPIED' /* To disable View Log Icon */
,start_with = 'BEN_PDW_PLN_OVVW_FUNC' /*enable the continue icon*/
where copy_entity_txn_id = p_copy_entity_txn_id;
update pqh_copy_entity_txns
set status = 'ERROR' /* To disable View Log Icon */
,start_with = null /*disable the continue icon*/
where copy_entity_txn_id = p_copy_entity_txn_id;
update pqh_copy_entity_txns
set status = 'COPYING_IN_PROGRESS'
,start_with = null
where copy_entity_txn_id = p_copy_entity_txn_id;
update pqh_copy_entity_txns
set status = 'COPIED' /* To disable View Log Icon */
,start_with = 'BEN_PDW_PLN_OVVW_FUNC' /*show the continue icon*/
where copy_entity_txn_id = p_copy_entity_txn_id;
update pqh_copy_entity_txns
set status = 'ERROR' /* To disable View Log Icon */
,start_with = null /*disable the continue icon*/
where copy_entity_txn_id = p_copy_entity_txn_id;