[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select '1' from dual where
exists (
select '1' from ben_ext_rcd r,
ben_ext_rcd_in_file rif,
ben_ext_file f
WHERE f.business_group_id is NULL
and f.ext_file_id =rif.ext_file_id
and r.ext_rcd_id = rif.ext_rcd_id
and f.name = p_file_name
AND f.name in ('ANSI-834 Full Profile','ANSI-834 Change Event')
and r.name = 'EC-Employment Class'
);
update ben_ext_rcd_in_file
set seq_num = seq_num * 10
where ext_FILE_ID = (select ext_FILE_ID from
ben_ext_file f
where f.business_group_id is NULL
and f.name = p_file_name
AND f.name in ('ANSI-834 Full Profile','ANSI-834 Change Event'));
update ben_EXT_DATA_ELMT_IN_RCD
set seq_num = seq_num * 10
where ext_rcd_id = (select rcd.ext_rcd_id from
ben_ext_file f
,ben_ext_rcd rcd
,BEN_EXT_RCD_IN_FILE rif
where f.business_group_id is NULL
and f.name = p_file_name
and rcd.EXT_RCD_ID =rif.ext_rcd_id
and f.EXT_FILE_ID =rif.EXT_FILE_ID
AND f.name in ('ANSI-834 Full Profile','ANSI-834 Change Event')
and ( (rcd.name = 'INS- Insured Benefit_021' and f.name = 'ANSI-834 Full Profile')
or (rcd.name = 'INS- Insured Benefit_001' and f.name = 'ANSI-834 Change Event'))
);
procedure delete_crit_adv_conditon
(p_ext_crit_prfl_id in number ) is
cursor c1 is
select ecv.ext_crit_val_id ,
ecv.object_version_number ,
ecv.LEGISLATION_CODE
from ben_ext_crit_val ecv ,
ben_ext_crit_typ ect
where ect.ext_crit_prfl_id = p_ext_crit_prfl_id
and ect.ext_crit_typ_id = ecv.ext_crit_typ_id
and ect.crit_typ_cd = 'ADV'
;
select ext_crit_cmbn_id ,
object_version_number ,
LEGISLATION_CODE
from ben_ext_crit_cmbn
where ext_crit_val_id = p_ext_crit_val_id
;
l_proc varchar2(100) := 'BEN_EXT_SEED.delete_crit_adv_conditon' ;
BEN_ext_crit_cmbn_API.delete_ext_crit_cmbn
(p_validate => FALSE
,p_EXT_CRIT_CMBN_ID => k.EXT_CRIT_CMBN_ID
,p_LEGISLATION_CODE => k.LEGISLATION_CODE
,p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
,p_effective_date => trunc(sysdate)
);
BEN_ext_crit_val_API.delete_ext_crit_val
(p_validate => FALSE
,p_EXT_CRIT_VAL_ID => I.EXT_CRIT_VAL_ID
,p_LEGISLATION_CODE => I.LEGISLATION_CODE
,p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
);
g_Ext_adv_crit_cmbn.delete ;
select business_group_id
into g_business_group_id
from per_business_groups_perf
where name = p_business_group ;
select 'x' from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_val
;
cursor c1 is select meaning
from hr_lookups
where lookup_type = 'US_STATE'
and lookup_code = p_val;
cursor c2 is select name
from ben_benfts_grp
where benfts_grp_id = p_val;
cursor c3 is SELECT user_status from PER_ASSIGNMENT_STATUS_TYPES
WHERE active_flag ='Y'
and assignment_status_type_id = p_val;
cursor c4 is SELECT name from PER_ORGANIZATION_UNITS
WHERE internal_external_flag = 'INT'
and organization_id = p_val;
cursor c5 is select location_code from hr_locations
where trunc(sysdate) <=
nvl(inactive_date,to_date('31124712','DDMMYYYY'))
and location_id = p_val;
cursor c6 is SELECT name from hr_tax_units_v
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate)
between nvl(date_from,trunc(sysdate))
and nvl(date_to,trunc(sysdate))
and tax_unit_id = p_val;
cursor c7 is SELECT full_name||' '||national_identifier
||' '||employee_number from per_all_people_f
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate)
between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and person_id = p_val;
cursor c8 is select formula_name
from ff_formulas_f
where --nvl(legislation_code,nvl(p_legislation_code,'~~nvl~~'))
-- = nvl(p_legislation_code,'~~nvl~~')
-- and nvl(business_group_id,nvl(p_business_group_id,-1))
-- = nvl(p_business_group_id,-1) and
trunc(sysdate) between effective_start_date
and effective_end_date
and formula_id = p_val;
cursor c9 is select name
from ben_ler_f
where nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate) between effective_start_date
and effective_end_date
and ler_id = p_val;
cursor c10 is select user_person_type
from per_person_types
where nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and active_flag = 'Y'
and person_type_id = p_val;
cursor ca is SELECT name from ben_cm_typ_f
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and cm_typ_id = p_val;
cursor cb is SELECT payroll_name from pay_all_payrolls_f
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate)
between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and payroll_id = p_val;
cursor cc is SELECT element_name from pay_element_types_f
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and element_type_id = p_val;
cursor cd is SELECT name from pay_input_values_f
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and input_value_id = p_val;
cursor ce is select name
from ben_pl_f
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and pl_id = p_val;
cursor cf is select name
from ben_rptg_grp_v
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and rptg_grp_id = p_val;
cursor cf is select meaning
from hr_lookups
where lookup_type = 'BEN_PER_IN_LER_STAT'
and lookup_code = p_val;
cursor cg is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_SUSPEND'
and lookup_code = p_val;
cursor ch is select name
from ben_ler_v
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and ler_id = p_val;
cursor ci is select name
from ben_pgm_f
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and pgm_id = p_val;
cursor cj is select name
from ben_pl_f
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and pl_id = p_val;
cursor ck is select name
from ben_rptg_grp_v
where business_group_id = p_business_group_id
and rptg_grp_id = p_val;
cursor cl is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_CRIT_MISC'
and lookup_code = p_val;
cursor cm is select name
from ben_pl_typ_f
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and pl_typ_id = p_val;
cursor cn is select start_date||' - '||end_date
from ben_yr_perd
where business_group_id = p_business_group_id
and yr_perd_id = p_val;
cursor co is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_CRIT_MISC'
and lookup_code = p_val;
cursor cp is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_SUSPEND'
and lookup_code = p_val;
cursor cq is select meaning
from hr_lookups
where lookup_type = 'BEN_ENRT_RSLT_MTHD'
and lookup_code = p_val;
cursor cr is select meaning
from hr_lookups
where lookup_type = 'BEN_PRTT_ENRT_RSLT_STAT'
and lookup_code = p_val;
cursor cs is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val;
cursor ct is select name
from ben_actn_typ
where actn_typ_id = p_val ;
cursor cu is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cv is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cw is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_CHG_EVT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate)) ;
cursor cep is select event_group_name
from pay_event_groups
where event_group_id = p_val
;
cursor cx is select user_name
from fnd_user
where user_id = p_val
and trunc(sysdate) between nvl(start_date, trunc(sysdate))
and nvl(end_date, trunc(sysdate));
cursor cy is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cz is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor caa is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cab is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cac is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cad is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cae is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor caf is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cag is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cah is select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val;
select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and lookup_code = p_val;
select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_CHG_EVT'
and lookup_code = p_val;
select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_CHG_EVT'
and lookup_code = p_val;
select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_TTL_COND_OPER'
and lookup_code = p_val; --p_oper_cd;
select meaning
from hr_lookups
where lookup_type = 'BEN_EXT_ASMT_TO_USE'
and lookup_code = p_val; -- value for the criteria Person Assignment To Use
select MEANING
from HR_LOOKUPS
where LOOKUP_CODE = p_crit_typ_cd
and lookup_type = 'BEN_EXT_CRIT_TYP'
and substr(lookup_code, 1, 1) = 'C'
and lookup_code not in ('CBU')
and trunc(sysdate) between
nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
select meaning
from hr_lookups
where lookup_type = 'BEN_ENRT_MTHD'
and lookup_code = p_val; -- value for the criteria Enrollment method
select to_char(enp.strt_dt, 'DD-MON-RRRR') || ' - ' || to_char( enp.end_dt, 'DD-MON-RRRR')
from ben_enrt_perd enp
where ENRT_PERD_ID = p_val
;
select meaning
from hr_lookups
where lookup_type = p_type
and lookup_code = p_val; --
select name
from per_jobs_vl job
where job_id = p_val;
select name
from per_business_groups_perf
where business_group_id = p_val;
select name
from HR_ALL_POSITIONS_F job
where position_id = p_val
and trunc(sysdate) between
EFFECTIVE_START_DATE
and EFFECTIVE_END_DATE ;
select ASSIGNMENT_SET_NAME from
hr_assignment_sets
where ASSIGNMENT_SET_ID = p_val
;
cursor c1 is select lookup_code
from hr_lookups
where lookup_type = 'US_STATE'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor c2 is select benfts_grp_id
from ben_benfts_grp
where nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and name = p_meaning;
cursor c3 is SELECT assignment_status_type_id
from PER_ASSIGNMENT_STATUS_TYPES
WHERE
-- nvl(legislation_code,nvl(p_legislation_code,'~~nvl~~'))
-- = nvl(p_legislation_code,'~~nvl~~') and
nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and active_flag ='Y'
and user_status = p_meaning;
cursor c4 is SELECT organization_id from PER_ORGANIZATION_UNITS
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate)
between nvl(date_from,trunc(sysdate))
and nvl(date_to,trunc(sysdate))
and internal_external_flag = 'INT'
and name = p_meaning;
cursor c5 is select location_id from hr_locations
where trunc(sysdate) <=
nvl(inactive_date,to_date('31124712','DDMMYYYY'))
and location_code = p_meaning ;
cursor c6 is SELECT tax_unit_id from hr_tax_units_v
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate)
between nvl(date_from,trunc(sysdate))
and nvl(date_to,trunc(sysdate))
and name = p_meaning ;
cursor c7 is SELECT person_id
from per_all_people_f
WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate)
between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and full_name||' '||national_identifier||' '||employee_number = p_meaning
and full_name like SUBSTR(p_meaning,1, INSTR(p_meaning,' ')-1)||'%'; -- 4300295. Perf fix.
cursor c8 is select formula_id --formula_name
from ff_formulas_f
where --nvl(legislation_code,nvl(p_legislation_code,'~~nvl~~'))
-- = nvl(p_legislation_code,'~~nvl~~')
-- and nvl(business_group_id,nvl(p_business_group_id,-1))
-- = nvl(p_business_group_id,-1) and
trunc(sysdate) between effective_start_date
and effective_end_date
and formula_name = p_meaning;
cursor c9 is select ler_id
from ben_ler_f
where nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate) between effective_start_date
and effective_end_date
and name = p_meaning;
cursor c10 is select person_type_id
from per_person_types
where nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and active_flag = 'Y'
and user_person_type = p_meaning;
cursor ca is SELECT cm_typ_id from ben_cm_typ_f
WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and name = p_meaning ;
cursor cb is SELECT payroll_id from pay_all_payrolls_f
WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and trunc(sysdate)
between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and payroll_name = p_meaning;
cursor cc is SELECT element_type_id from pay_element_types_f
WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and element_name = p_meaning;
cursor cd is SELECT input_value_id from pay_input_values_f
WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and name = p_meaning
and element_type_id = (SELECT element_type_id from pay_element_types_f
WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
and element_name = p_parent_meaning);
cursor ce is select pl_id
from ben_pl_f
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and name = p_meaning ;
cursor cf is select name
from ben_rptg_grp_v
where business_group_id = p_business_group_id
and rptg_grp_id = p_val_1;
cursor cf is select lookup_code
from hr_lookups
where lookup_type = 'BEN_PER_IN_LER_STAT'
and meaning = p_meaning;
cursor cg is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_SUSPEND'
and meaning = p_meaning;
cursor ch is select name
from ben_ler_v
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and ler_id = p_val_1;
cursor ci is select pgm_id
from ben_pgm_f
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and name = p_meaning ;
cursor cj is select pl_id
from ben_pl_f
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and name = p_meaning ;
cursor ck is select rptg_grp_id
from ben_rptg_grp_v
where business_group_id = p_business_group_id
and name = p_meaning ;
cursor cl is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_CRIT_MISC'
and meaning = p_meaning;
cursor cm is select pl_typ_id
from ben_pl_typ_f
where business_group_id = p_business_group_id
and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
and nvl(effective_end_date,trunc(sysdate))
and name = p_meaning;
cursor cn is select yr_perd_id
from ben_yr_perd
where business_group_id = p_business_group_id
and start_date||' - '||end_date = p_meaning;
cursor co is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_CRIT_MISC'
and meaning = p_meaning;
cursor cp is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_SUSPEND'
and meaning = p_meaning;
cursor cq is select lookup_code
from hr_lookups
where lookup_type = 'BEN_ENRT_RSLT_MTHD'
and meaning = p_meaning;
cursor cr is select lookup_code
from hr_lookups
where lookup_type = 'BEN_PRTT_ENRT_RSLT_STAT'
and meaning = p_meaning;
cursor cs is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning;
cursor ct is select actn_typ_id
from ben_actn_typ
where name = p_meaning ;
cursor cu is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cv is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cw is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_CHG_EVT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cep is select event_group_id
from pay_event_groups
where event_group_name = p_meaning
and nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
;
cursor cx is select user_id
from fnd_user
where user_name = p_meaning
and trunc(sysdate) between nvl(start_date, trunc(sysdate))
and nvl(end_date, trunc(sysdate));
cursor cy is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cz is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor caa is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cab is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cac is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cad is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cae is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor caf is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cag is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
cursor cah is select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning;
select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_DT'
and meaning = p_meaning;
select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_CHG_EVT'
and meaning = p_meaning;
select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_CHG_EVT'
and meaning = p_meaning;
select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_TTL_COND_OPER'
and meaning = p_meaning; --p_oper_cd;
select lookup_code
from HR_LOOKUPS
where LOOKUP_CODE = p_crit_typ_cd
and lookup_type = 'BEN_EXT_CRIT_TYP'
and substr(lookup_code, 1, 1) = 'C'
and lookup_code not in ('CBU')
and trunc(sysdate) between
nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
select lookup_code
from hr_lookups
where lookup_type = 'BEN_EXT_ASMT_TO_USE'
and meaning = p_meaning; -- code for the criteria Person Assignment To Use
select lookup_code
from hr_lookups
where lookup_type = 'BEN_ENRT_MTHD'
and meaning = p_meaning; -- value for the criteria Enrollment method
select ENRT_PERD_ID
from ben_enrt_perd enp ,
ben_pl_f pl,
ben_popl_enrt_typ_cycl_f pet
where to_char(enp.strt_dt, 'DD-MON-RRRR') || ' - ' || to_char( enp.end_dt, 'DD-MON-RRRR') = p_meaning
and pl.name = p_parent_meaning
and pl.pl_id = pet.pl_id
and pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
and trunc(sysdate) between nvl(pl.effective_start_date,trunc(sysdate))
and nvl(pl.effective_end_date,trunc(sysdate))
and enp.business_group_id = p_business_group_id
;
select lookup_code
from hr_lookups
where lookup_type = p_type
and meaning = p_meaning
; --
select job_id
from per_jobs_vl job
where name = p_meaning
and business_group_id = p_business_group_id
;
select business_group_id
from per_business_groups_perf
where name = p_meaning
;
select position_id
from HR_ALL_POSITIONS_F job
where name = p_meaning
and trunc(sysdate) between
EFFECTIVE_START_DATE
and EFFECTIVE_END_DATE
and business_group_id = p_business_group_id
;
select ASSIGNMENT_SET_ID from
hr_assignment_sets
where ASSIGNMENT_SET_NAME = p_meaning
and nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
;
,p_last_update_vc in VARCHAR2
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY VARCHAR2
,p_legislation_code IN OUT NOCOPY VARCHAR2
,p_business_group in VARCHAR2
,p_business_group_id out NOCOPY NUMBER
) is
begin
p_last_update_date := TO_DATE(p_last_update_vc, 'YYYY/MM/DD HH24:MI:SS');
p_last_updated_by := 1;
p_last_updated_by := 0;
,p_last_update_date IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group in VARCHAR2
) is
--
l_ext_file_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_file_id ,
OBJECT_VERSION_NUMBER ,
XML_TAG_NAME
INTO l_ext_file_id ,
l_ovn,
l_xml_tag_name
FROM ben_ext_file
WHERE name = p_file_name
AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND nvl(legislation_code,'~NULL~') = nvl(l_legislation_code,'~NULL~');
select erf.ext_rcd_in_file_id,
rcd.ext_rcd_id
into l_ext_rcd_in_file_id ,
l_ext_rcd_id
from ben_Ext_rcd_in_file erf,
ben_Ext_rcd rcd
where rcd.name = p_ext_group_record
and rcd.ext_rcd_id = erf.ext_rcd_id
and erf.ext_file_id = l_ext_file_id
;
select der.Ext_data_elmt_in_rcd_id
into l_ext_data_elmt_in_rcd_id1
from ben_Ext_data_elmt elmt ,
ben_Ext_data_elmt_in_rcd der
where der.ext_rcd_id = l_Ext_rcd_id
and elmt.ext_data_elmt_id = der.ext_data_elmt_id
and elmt.name = p_ext_group_elmt1
;
select der.Ext_data_elmt_in_rcd_id
into l_ext_data_elmt_in_rcd_id2
from ben_Ext_data_elmt elmt ,
ben_Ext_data_elmt_in_rcd der
where der.ext_rcd_id = l_Ext_rcd_id
and elmt.ext_data_elmt_id = der.ext_data_elmt_id
and elmt.name = p_ext_group_elmt2
;
,p_last_update_date IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group in VARCHAR2
,p_xml_tag_name in VARCHAR2
,p_ext_group_record in VARCHAR2
,p_ext_group_elmt1 in VARCHAR2
,p_ext_group_elmt2 in VARCHAR2
) is
--
l_ext_file_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
SELECT USERENV('SESSIONID')
INTO l_sessionid
FROM DUAL;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_file_id ,
OBJECT_VERSION_NUMBER ,
XML_TAG_NAME
INTO l_ext_file_id ,
l_ovn,
l_xml_tag_name
FROM ben_ext_file
WHERE name = p_file_name
AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND nvl(legislation_code,'~NULL~') = nvl(l_legislation_code,'~NULL~');
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_xml_tag_name => p_xml_tag_name
);
,p_last_update_date IN VARCHAR2
,p_rcd_type_cd IN VARCHAR2
,p_low_lvl_cd IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group IN VARCHAR2
,p_xml_tag_name in VARCHAR2
) IS
--
l_ext_rcd_id number;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT EXT_RCD_ID,
OBJECT_VERSION_NUMBER,
XML_TAG_NAME,
RCD_TYPE_CD ,
LOW_LVL_CD
INTO l_ext_rcd_id,
l_ovn,
l_xml_tag_name,
l_RCD_TYPE_CD,
l_LOW_LVL_CD
FROM ben_ext_rcd
WHERE name = p_record_name
AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND nvl(legislation_code,'~NULL~') = nvl(l_legislation_code,'~NULL~');
ben_xrc_upd.upd(p_effective_date => l_last_update_date
,p_ext_rcd_id => l_ext_rcd_id
,p_name => p_record_name
,p_rcd_type_cd => p_rcd_type_cd
,p_low_lvl_cd => p_low_lvl_cd
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_object_version_number => l_ovn
,p_xml_tag_name => p_xml_tag_name );
ben_xrc_ins.ins(p_effective_date => l_last_update_date
,p_ext_rcd_id => l_ext_rcd_id
,p_name => p_record_name
,p_rcd_type_cd => p_rcd_type_cd
,p_low_lvl_cd => p_low_lvl_cd
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_xml_tag_name => p_xml_tag_name );
,p_last_update_date IN VARCHAR2
,p_rqd_flag IN VARCHAR2 default 'N'
,p_hide_flag IN VARCHAR2 default 'N'
,p_CHG_RCD_UPD_FLAG IN VARCHAR2 default 'N'
,p_seq_num IN VARCHAR2
,p_sprs_cd IN VARCHAR2
,p_any_or_all_cd IN VARCHAR2 default 'N'
,p_sort1_element IN VARCHAR2 DEFAULT NULL
,p_sort2_element IN VARCHAR2 DEFAULT NULL
,p_sort3_element IN VARCHAR2 DEFAULT NULL
,p_sort4_element IN VARCHAR2 DEFAULT NULL
,p_legislation_code IN VARCHAR2
,p_business_group in VARCHAR2
) IS
--
l_ext_file_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
SELECT EXT_DATA_ELMT_IN_RCD_ID
from ben_ext_data_elmt_in_rcd eir , ben_ext_data_elmt elmt
where eir.ext_rcd_id = c_ext_rcd_id
and eir.ext_data_elmt_id = elmt.ext_data_elmt_id
and elmt.name = c_sort_element
and NVL(eir.legislation_code,'~NULL~') = NVL(c_legislation_code,'~NULL~')
AND nvl( c_new_business_group_id, -1) = nvl(eir.business_group_id , -1)
;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_file_id
INTO l_ext_file_id
FROM ben_ext_file
WHERE name = p_file_name
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1);
SELECT ext_rcd_id
INTO l_ext_rcd_id
FROM ben_ext_rcd
WHERE name = p_parent_record_name
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1);
SELECT ext_rcd_in_file_id, object_version_number
INTO l_ext_rcd_in_file_id,l_object_version_number
FROM ben_ext_rcd_in_file
WHERE ext_file_id = l_ext_file_id
AND ext_rcd_id = l_ext_rcd_id
AND seq_num = p_seq_num
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
AND NVL( l_new_business_group_id, -1) = NVL(business_group_id , -1);
(p_effective_date => l_last_update_date
,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
,p_seq_num => p_seq_num
,p_sprs_cd => p_sprs_cd
,p_ext_rcd_id => l_ext_rcd_id
,p_ext_file_id => l_ext_file_id
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_any_or_all_cd => p_any_or_all_cd
,p_hide_flag => p_hide_flag
,p_rqd_flag => p_rqd_flag
,p_CHG_RCD_UPD_FLAG => nvl(p_CHG_RCD_UPD_FLAG,'N')
,p_sort1_data_elmt_in_rcd_id => l_sort1_elm_in_rcd_id
,p_sort2_data_elmt_in_rcd_id => l_sort2_elm_in_rcd_id
,p_sort3_data_elmt_in_rcd_id => l_sort3_elm_in_rcd_id
,p_sort4_data_elmt_in_rcd_id => l_sort4_elm_in_rcd_id
);
/* If the Same Sequence Find Delete the Record for the Sequence */
declare
cursor c1 (c_ext_rcd_in_file_id number) is
select 'x'
from ben_Ext_where_clause
where ext_rcd_in_file_id = c_ext_rcd_in_file_id ;
select 'x'
from ben_ext_incl_chg
where ext_rcd_in_file_id = c_ext_rcd_in_file_id ;
select object_version_number,ext_rcd_in_file_id
into l_object_version_number,l_rcd_in_file_id
from ben_ext_rcd_in_file
where ext_file_id = l_ext_file_id
and ext_rcd_id <> nvl(l_ext_rcd_id,-1)
and seq_num = p_seq_num
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
/* if the sequence found delete the old reco */
-- this delete may error if it has child
-- **** Before apply any delete keep in mind that every upload recrod_in_file is
-- called twice , one for the record_in_file and one for the sort order
-- this is done in such way to keep the backward copatibility ****
if ( l_new_business_group_id is null or g_override = 'Y' ) and
l_sort1_elm_in_rcd_id is null and
l_sort2_elm_in_rcd_id is null and
l_sort4_elm_in_rcd_id is null then
-- make sure there us no wheere clause child
open c1(l_rcd_in_file_id) ;
ben_xrf_del.del(p_effective_date => l_last_update_date
,p_ext_rcd_in_file_id => l_rcd_in_file_id
,p_legislation_code => l_legislation_code
,p_object_version_number => l_object_version_number);
/* Deleteion part is over for duplicate seq number */
ben_xrf_ins.ins(p_effective_date => l_last_update_date
,p_ext_rcd_in_file_id => l_rcd_in_file_id
,p_legislation_code => l_legislation_code
,p_ext_rcd_id => l_ext_rcd_id
,p_ext_file_id => l_ext_file_id
,p_business_group_id => l_new_business_group_id
,p_seq_num => p_seq_num
,p_sprs_cd => p_sprs_cd
,p_any_or_all_cd => p_any_or_all_cd
,p_hide_flag => p_hide_flag
,p_rqd_flag => p_rqd_flag
,p_chg_rcd_upd_flag => nvl(p_chg_rcd_upd_flag,'N')
,p_sort1_data_elmt_in_rcd_id => l_sort1_elm_in_rcd_id
,p_sort2_data_elmt_in_rcd_id => l_sort2_elm_in_rcd_id
,p_sort3_data_elmt_in_rcd_id => l_sort3_elm_in_rcd_id
,p_sort4_data_elmt_in_rcd_id => l_sort4_elm_in_rcd_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number);
,p_last_update_date IN VARCHAR2
,p_ttl_fnctn_cd IN VARCHAR2
,p_ttl_cond_oper_cd IN VARCHAR2
,p_ttl_cond_val IN VARCHAR2
,p_data_elmt_typ_cd IN VARCHAR2
,p_data_elmt_rl IN VARCHAR2
,p_frmt_mask_cd IN VARCHAR2
,p_string_val IN VARCHAR2
,p_dflt_val IN VARCHAR2
,p_max_length_num IN VARCHAR2
,p_just_cd IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group in varchar2
,p_xml_tag_name in VARCHAR2
,p_defined_balance in VARCHAR2 DEFAULT NULL
) IS
--
l_ext_field_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,l_last_update_date in date ) IS
SELECT formula_id
FROM ff_formulas_f
WHERE formula_name = p_data_elmt_rl
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND nvl(l_last_update_date,trunc(sysdate)) BETWEEN effective_start_date and effective_end_date ;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_data_elmt_id , object_version_number,string_val
INTO l_tmp_id , l_tmp_ovn,l_string
FROM ben_ext_data_elmt
WHERE name = p_data_elemt_name
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) ) ;
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_tmp_ovn
,p_xml_tag_name => p_xml_tag_name );
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_xml_tag_name => p_xml_tag_name );
SELECT ext_data_elmt_id , object_version_number,string_val
INTO l_tmp_id , l_tmp_ovn,l_string
FROM ben_ext_data_elmt
WHERE name = p_data_elemt_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
--AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
p_effective_date => l_last_update_date
,p_ext_data_elmt_id => l_tmp_id
,p_ttl_fnctn_cd => p_ttl_fnctn_cd
,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
,p_ttl_cond_val => p_ttl_cond_val
,p_data_elmt_typ_cd => p_data_elmt_typ_cd
,p_frmt_mask_cd => p_frmt_mask_cd
,p_string_val => p_string_val
,p_dflt_val => p_dflt_val
,p_max_length_num => p_max_length_num
,p_just_cd => p_just_cd
,p_legislation_code => l_legislation_code
,p_business_group_id => l_new_business_group_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_tmp_ovn
,p_xml_tag_name => p_xml_tag_name );
ben_xel_ins.ins(p_effective_date => l_last_update_date
,p_ext_data_elmt_id => l_ext_data_elmt_id
,p_name => p_data_elemt_name
,p_ttl_fnctn_cd => p_ttl_fnctn_cd
,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
,p_ttl_cond_val => p_ttl_cond_val
,p_data_elmt_typ_cd => p_data_elmt_typ_cd
,p_ext_fld_id => null
,p_data_elmt_rl => null
,p_frmt_mask_cd => p_frmt_mask_cd
,p_string_val => p_string_val
,p_dflt_val => p_dflt_val
,p_max_length_num => p_max_length_num
,p_just_cd => p_just_cd
,p_legislation_code => l_legislation_code
,p_business_group_id => l_new_business_group_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_xml_tag_name => p_xml_tag_name );
SELECT ext_rcd_id
INTO l_ext_rcd_id
FROM ben_ext_rcd
WHERE name = p_parent_record_name
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT ext_data_elmt_id
INTO l_parent_data_elmt_id
FROM ben_ext_data_elmt
WHERE name = p_parent_data_element
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
--AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT ext_data_elmt_id , object_version_number,string_val
INTO l_tmp_id , l_tmp_ovn ,l_string
FROM ben_ext_data_elmt
WHERE name = p_data_elemt_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
p_effective_date => l_last_update_date
,p_ext_data_elmt_id => l_tmp_id
,p_ttl_cond_ext_data_elmt_id => l_ext_rcd_id
,p_ttl_sum_ext_data_elmt_id => l_parent_data_elmt_id
,p_ttl_fnctn_cd => p_ttl_fnctn_cd
,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
,p_ttl_cond_val => p_ttl_cond_val
,p_data_elmt_typ_cd => p_data_elmt_typ_cd
,p_frmt_mask_cd => p_frmt_mask_cd
,p_dflt_val => p_dflt_val
,p_max_length_num => p_max_length_num
,p_just_cd => p_just_cd
,p_legislation_code => l_legislation_code
,p_business_group_id => l_new_business_group_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_tmp_ovn
,p_xml_tag_name => p_xml_tag_name);
ben_xel_ins.ins(p_effective_date => l_last_update_date
,p_ext_data_elmt_id => l_ext_data_elmt_id
,p_name => p_data_elemt_name
,p_ttl_cond_ext_data_elmt_id => l_ext_rcd_id
,p_ttl_sum_ext_data_elmt_id => l_parent_data_elmt_id
,p_ttl_fnctn_cd => p_ttl_fnctn_cd
,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
,p_ttl_cond_val => p_ttl_cond_val
,p_data_elmt_typ_cd => p_data_elmt_typ_cd
,p_ext_fld_id => NULL
,p_data_elmt_rl => NULL
,p_frmt_mask_cd => p_frmt_mask_cd
,p_string_val => NULL
,p_dflt_val => p_dflt_val
,p_max_length_num => p_max_length_num
,p_just_cd => p_just_cd
,p_legislation_code => l_legislation_code
,p_business_group_id => l_new_business_group_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_xml_tag_name => p_xml_tag_name );
select c.defined_balance_id ID
from pay_defined_balances c ,
pay_balance_types a ,
pay_balance_dimensions b
where
a.balance_type_id = c.balance_type_id
and c.balance_dimension_id = b.balance_dimension_id
and b.dimension_level in ( 'PER' ,'ASG')
and a.balance_name || ' [ ' || b.dimension_name || ' ]' = p_defined_balance
AND ( NVL(a.legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~') or l_legislation_code is null )
AND ( ( nvl(l_new_business_group_id, -1) = nvl(a.business_group_id , nvl(l_new_business_group_id, -1))) or
( p_business_group is null and a.business_group_id is null ) ) ;
SELECT ext_data_elmt_id , object_version_number ,string_val
INTO l_tmp_id , l_tmp_ovn,l_string
FROM ben_ext_data_elmt
WHERE name = p_data_elemt_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
p_effective_date => l_last_update_date
,p_ext_data_elmt_id => l_tmp_id
,p_ttl_fnctn_cd => p_ttl_fnctn_cd
,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
,p_ttl_cond_val => p_ttl_cond_val
,p_data_elmt_typ_cd => p_data_elmt_typ_cd
,p_ext_fld_id => l_ext_field_id
,p_string_val => p_string_val
,p_frmt_mask_cd => p_frmt_mask_cd
,p_dflt_val => p_dflt_val
,p_max_length_num => p_max_length_num
,p_just_cd => p_just_cd
,p_legislation_code => l_legislation_code
,p_business_group_id => l_new_business_group_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_tmp_ovn
,p_defined_balance_id => l_defined_balance_id
,p_xml_tag_name => p_xml_tag_name);
ben_xel_ins.ins(p_effective_date => l_last_update_date
,p_ext_data_elmt_id => l_ext_data_elmt_id
,p_name => p_data_elemt_name
,p_ttl_fnctn_cd => p_ttl_fnctn_cd
,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
,p_ttl_cond_val => p_ttl_cond_val
,p_data_elmt_typ_cd => p_data_elmt_typ_cd
,p_ext_fld_id => l_ext_field_id
,p_data_elmt_rl => null -- p_data_elmt_rl
,p_frmt_mask_cd => p_frmt_mask_cd
,p_string_val => p_string_val
,p_dflt_val => p_dflt_val
,p_max_length_num => p_max_length_num
,p_just_cd => p_just_cd
,p_legislation_code => l_legislation_code
,p_business_group_id => l_new_business_group_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_defined_balance_id => l_defined_balance_id
,p_xml_tag_name => p_xml_tag_name);
SELECT ext_fld_id
INTO l_ext_field_id
FROM ben_ext_fld
WHERE short_name = p_field_short_name;
SELECT ext_data_elmt_id , object_version_number ,string_val
INTO l_tmp_id , l_tmp_ovn,l_string
FROM ben_ext_data_elmt
WHERE name = p_data_elemt_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
p_effective_date => l_last_update_date
,p_ext_data_elmt_id => l_tmp_id
,p_ttl_fnctn_cd => p_ttl_fnctn_cd
,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
,p_ttl_cond_val => p_ttl_cond_val
,p_data_elmt_typ_cd => p_data_elmt_typ_cd
,p_ext_fld_id => l_ext_field_id
,p_string_val => p_string_val
,p_frmt_mask_cd => p_frmt_mask_cd
,p_dflt_val => p_dflt_val
,p_max_length_num => p_max_length_num
,p_just_cd => p_just_cd
,p_legislation_code => l_legislation_code
,p_business_group_id => l_new_business_group_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_tmp_ovn
,p_xml_tag_name => p_xml_tag_name);
ben_xel_ins.ins(p_effective_date => l_last_update_date
,p_ext_data_elmt_id => l_ext_data_elmt_id
,p_name => p_data_elemt_name
,p_ttl_fnctn_cd => p_ttl_fnctn_cd
,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
,p_ttl_cond_val => p_ttl_cond_val
,p_data_elmt_typ_cd => p_data_elmt_typ_cd
,p_ext_fld_id => l_ext_field_id
,p_data_elmt_rl => null -- p_data_elmt_rl
,p_frmt_mask_cd => p_frmt_mask_cd
,p_string_val => p_string_val
,p_dflt_val => p_dflt_val
,p_max_length_num => p_max_length_num
,p_just_cd => p_just_cd
,p_legislation_code => l_legislation_code
,p_business_group_id => l_new_business_group_id
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_xml_tag_name => p_xml_tag_name);
,p_last_update_date IN VARCHAR2
,p_rqd_flag IN VARCHAR2
,p_hide_flag IN VARCHAR2
,p_seq_num IN VARCHAR2
,p_strt_pos IN VARCHAR2
,p_dlmtr_val IN VARCHAR2
,p_sprs_cd IN VARCHAR2
,p_any_or_all_cd IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group in VARCHAR2
) IS
--
l_ext_rcd_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_rcd_id
INTO l_ext_rcd_id
FROM ben_ext_rcd
WHERE name = p_record_name
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
SELECT ext_data_elmt_id
INTO l_ext_data_elmt_id
FROM ben_ext_data_elmt
WHERE name = p_data_element_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
--AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT OBJECT_VERSION_NUMBER,
EXT_DATA_ELMT_IN_RCD_ID,
STRT_POS ,
DLMTR_VAL,
RQD_FLAG,
HIDE_FLAG,
SPRS_CD
INTO l_ovn,
l_EXT_DATA_ELMT_IN_RCD_ID,
l_STRT_POS ,
l_DLMTR_VAL,
l_RQD_FLAG ,
l_HIDE_FLAG,
l_SPRS_CD
FROM ben_ext_data_elmt_in_rcd
WHERE ext_rcd_id = l_ext_rcd_id
AND ext_data_elmt_id = l_ext_data_elmt_id
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
AND NVL(seq_num,-987123654) = NVL(p_seq_num,-987123654);
ben_xer_upd.upd(p_effective_date => l_last_update_date
,p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_rqd_flag => p_rqd_flag
,p_hide_flag => p_hide_flag
,p_strt_pos => p_strt_pos
,p_dlmtr_val => p_dlmtr_val
,p_SPRS_CD => p_SPRS_CD
,p_object_version_number => l_ovn);
/* If the Same Sequence Find Delete the Record for the Sequence */
Declare
cursor c_ext_where_clause (p_ext_data_elmt_in_rcd_id number) is
select ext_where_clause_id,object_version_number
from ben_ext_where_clause
where ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
select ext_incl_chg_id,object_version_number
from ben_ext_incl_chg
where ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
select object_version_number,ext_data_elmt_in_rcd_id
from ben_ext_data_elmt_in_rcd
where ext_data_elmt_id <> nvl(c_ext_data_elmt_id,-1)
and ext_rcd_id = c_ext_rcd_id
and seq_num = p_seq_num ;
/* if the sequence found delete the old reco */
-- before deleting make sure the chile in where and inclusion are deleted
if ( l_new_business_group_id is null or g_override = 'Y' ) then
--- delete where clause of the data element in rcd
for i in c_ext_where_clause( l_ext_data_elmt_in_rcd_id)
Loop
l_obj_ver_number := i.object_version_number ;
ben_xer_del.del(p_effective_date => l_last_update_date
,p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id
,p_object_version_number => l_object_version_number);
ben_xer_ins.ins(p_effective_date => l_last_update_date
,p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id
,p_business_group_id => l_new_business_group_id
,p_ext_data_elmt_id => l_ext_data_elmt_id
,p_ext_rcd_id => l_ext_rcd_id
,p_legislation_code => l_legislation_code
,p_rqd_flag => p_rqd_flag
,p_hide_flag => p_hide_flag
,p_strt_pos => p_strt_pos
,p_dlmtr_val => p_dlmtr_val
,p_sprs_cd => p_sprs_cd
,p_seq_num => p_seq_num
,p_any_or_all_cd => p_any_or_all_cd
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number);
,p_last_update_date IN VARCHAR2
,p_seq_num IN VARCHAR2
,p_oper_cd IN VARCHAR2
,p_val IN VARCHAR2
,p_and_or_cd IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group IN VARCHAR2
) is
l_ext_file_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_rcd_id
INTO l_ext_rcd_id
FROM ben_ext_rcd
WHERE name = p_record_name
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
;
SELECT ext_file_id
INTO l_ext_file_id
FROM ben_ext_file
WHERE name = p_file_name
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
;
SELECT ext_rcd_in_file_id
INTO l_ext_rcd_in_file_id
FROM ben_ext_rcd_in_file
WHERE ext_file_id = l_ext_file_id
AND ext_rcd_id = l_ext_rcd_id
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT ext_data_elmt_id
INTO l_ext_data_elmt_id
FROM ben_ext_data_elmt
WHERE name = p_data_elmt_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
;
SELECT ext_data_elmt_id
INTO l_cond_ext_data_elmt_id
FROM ben_ext_data_elmt
WHERE name = p_cond_ext_data_elmt_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
--AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
;
SELECT ext_data_elmt_in_rcd_id
INTO l_ext_data_elmt_in_rcd_id
FROM ben_ext_data_elmt_in_rcd rcd,
ben_ext_data_elmt elmt
WHERE rcd.ext_rcd_id = l_ext_rcd_id
and elmt.name = l_record_data_elmt_name
AND rcd.ext_data_elmt_id = elmt.ext_data_elmt_id
AND nvl( l_new_business_group_id, -1) = nvl(rcd.business_group_id , -1)
AND nvl( l_new_business_group_id, -1) = nvl(elmt.business_group_id , -1)
AND NVL(rcd.legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
AND NVL(elmt.legislation_code,'~NVL~')= NVL(l_legislation_code,'~NVL~');
SELECT ext_data_elmt_in_rcd_id
INTO l_cond_ext_data_elmt_in_rcd_id
FROM ben_ext_data_elmt_in_rcd
WHERE ext_rcd_id = l_ext_rcd_id
AND ext_data_elmt_id = l_ext_data_elmt_id
AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT 'Y'
INTO l_temp
FROM ben_ext_where_clause
WHERE cond_ext_data_elmt_in_rcd_id = l_cond_ext_data_elmt_in_rcd_id
AND ext_rcd_in_file_id = l_ext_rcd_in_file_id
AND seq_num = p_seq_num
--AND ext_data_elmt_in_rcd_id = l_ext_data_elmt_in_rcd_id
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT 'Y'
INTO l_temp
FROM ben_ext_where_clause
WHERE cond_ext_data_elmt_in_rcd_id = l_cond_ext_data_elmt_in_rcd_id
AND seq_num = p_seq_num
AND ext_data_elmt_in_rcd_id = l_ext_data_elmt_in_rcd_id
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT 'Y'
INTO l_temp
FROM ben_ext_where_clause
WHERE ext_data_elmt_id = l_ext_data_elmt_id
AND cond_ext_data_elmt_id = l_cond_ext_data_elmt_id
AND seq_num = p_seq_num
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT ext_where_clause_id, object_version_number
FROM ben_ext_where_clause xwc
WHERE ( business_group_id is null
or business_group_id = l_new_business_group_id )
and (legislation_code is null
or legislation_code = l_legislation_code )
and (ext_rcd_in_file_id = p_ext_rcd_in_file_id
or p_ext_rcd_in_file_id is null )
and (ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
or p_ext_data_elmt_in_rcd_id is null)
and (ext_data_elmt_id = p_ext_data_elmt_id
or p_ext_data_elmt_id is null)
and seq_num = p_seq_num
;
ben_xwc_ins.ins(p_effective_date => l_last_update_date
,p_ext_where_clause_id => l_ext_where_clause_id
,p_seq_num => p_seq_num
,p_oper_cd => p_oper_cd
,p_val => p_val
,p_and_or_cd => p_and_or_cd
,p_ext_data_elmt_id => l_ext_data_elmt_id
,p_cond_ext_data_elmt_id => l_cond_ext_data_elmt_id
,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
,p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id
,p_cond_ext_data_elmt_in_rcd_id => l_cond_ext_data_elmt_in_rcd_id
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_object_version_number => l_object_version_number);
,p_last_update_date IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group in VARCHAR2
,p_chg_evt_source IN VARCHAR2 DEFAULT NULL
) IS
--
cursor cw (c_code varchar2)
is select 'x'
from hr_lookups
where lookup_type = 'BEN_EXT_CHG_EVT'
and lookup_code = c_code
and enabled_flag = 'Y'
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_rcd_id
INTO l_ext_rcd_id
FROM ben_ext_rcd
WHERE name = p_record_name
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
SELECT ext_file_id
INTO l_ext_file_id
FROM ben_ext_file
WHERE name = p_file_name
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
SELECT ext_rcd_in_file_id
INTO l_ext_rcd_in_file_id
FROM ben_ext_rcd_in_file
WHERE ext_file_id = l_ext_file_id
AND ext_rcd_id = l_ext_rcd_id
AND NVL(legislation_code,'~NULL~')= NVL(l_legislation_code,'~NULL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
SELECT ext_data_elmt_id
INTO l_ext_data_elmt_id
FROM ben_ext_data_elmt
WHERE name = p_data_elmt_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
--AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~')= NVL(l_legislation_code,'~NULL~');
SELECT ext_data_elmt_in_rcd_id
INTO l_ext_data_elmt_in_rcd_id
FROM ben_ext_data_elmt_in_rcd
WHERE ext_rcd_id = l_ext_rcd_id
AND ext_data_elmt_id = l_ext_data_elmt_id
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT 'Y'
INTO l_temp
FROM ben_ext_incl_chg
WHERE (l_ext_rcd_in_file_id IS NULL OR
ext_rcd_in_file_id = l_ext_rcd_in_file_id)
AND (l_ext_data_elmt_in_rcd_id IS NULL OR
ext_data_elmt_in_rcd_id = l_ext_data_elmt_in_rcd_id)
AND chg_evt_cd = l_chg_evt_cd
AND NVL(l_new_business_group_id, -1) = NVL(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT ext_incl_chg_id , object_version_number
FROM ben_ext_incl_chg
WHERE (l_ext_rcd_in_file_id IS NULL OR
ext_rcd_in_file_id = l_ext_rcd_in_file_id)
AND (l_ext_data_elmt_in_rcd_id IS NULL OR
ext_data_elmt_in_rcd_id = l_ext_data_elmt_in_rcd_id)
AND chg_evt_cd = l_chg_evt_cd
AND NVL(l_new_business_group_id, -1) = NVL(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
p_effective_date => l_last_update_date,
p_ext_incl_chg_id => i.ext_incl_chg_id,
p_object_version_number => l_object_version_number
);
ben_xic_ins.ins(p_effective_date => l_last_update_date
,p_ext_incl_chg_id => l_ext_incl_chg_id
,p_chg_evt_cd => l_chg_evt_cd
,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
,p_ext_data_elmt_in_rcd_id=> l_ext_data_elmt_in_rcd_id
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_chg_evt_source => p_chg_evt_source );
ben_xic_ins.ins(p_effective_date => l_last_update_date
,p_ext_incl_chg_id => l_ext_incl_chg_id
,p_chg_evt_cd => l_chg_evt_cd
,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
,p_ext_data_elmt_in_rcd_id=> l_ext_data_elmt_in_rcd_id
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_object_version_number => l_object_version_number
,p_chg_evt_source => p_chg_evt_source );
,p_last_update_date IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group in VARCHAR2
,p_ext_global_flag in VARCHAR2 default 'N'
) IS
--
l_ext_prfl_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_crit_prfl_id
INTO l_ext_prfl_id
FROM ben_ext_crit_prfl
WHERE name = p_profile_name
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_ext_global_flag => nvl(p_ext_global_flag,'N')
,p_object_version_number => l_object_version_number
);
delete_crit_adv_conditon
(p_ext_crit_prfl_id => l_ext_prfl_id ) ;
,p_last_update_date IN VARCHAR2
,p_crit_typ_cd IN VARCHAR2
,p_excld_flag IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group IN VARCHAR2
) IS
--
l_ext_prfl_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_crit_prfl_id
INTO l_ext_prfl_id
FROM ben_ext_crit_prfl
WHERE name = p_profile_name
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT 'Y'
INTO l_temp
FROM ben_ext_crit_typ
WHERE ext_crit_prfl_id = l_ext_prfl_id
AND crit_typ_cd = p_type_code
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
ben_xct_ins.ins(p_effective_date => l_last_update_date
,p_ext_crit_typ_id => l_ext_crit_typ_id
,p_crit_typ_cd => p_type_code
,p_excld_flag => p_excld_flag
,p_ext_crit_prfl_id => l_ext_prfl_id
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number);
,p_last_update_date IN VARCHAR2
,p_val2 IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group IN VARCHAR2
,p_ext_crit_val_id in varchar2 default null
,p_lookup_code1 in varchar2 default null
,p_lookup_code2 in varchar2 default null
) IS
--
cursor cw (c_code varchar2)
is select 'x'
from hr_lookups
where lookup_type = 'BEN_EXT_CHG_EVT'
and lookup_code = c_code
and enabled_flag = 'Y'
and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_crit_prfl_id
INTO l_ext_prfl_id
FROM ben_ext_crit_prfl
WHERE name = p_profile_name
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT ext_crit_typ_id
INTO l_ext_crit_typ_id
FROM ben_ext_crit_typ
WHERE ext_crit_prfl_id = l_ext_prfl_id
AND crit_typ_cd = p_TYPE_CODE
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT 'Y'
INTO l_temp
FROM ben_ext_crit_val
WHERE ext_crit_typ_id = l_ext_crit_typ_id
AND val_1 = l_value
AND nvl(val_2,'~NVL~') = NVL(l_value2,'~NVL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
;
SELECT 'Y'
FROM ben_ext_crit_val
WHERE ext_crit_typ_id = l_ext_crit_typ_id
AND val_1 = l_value
AND nvl(val_2,'~NVL~') = NVL(l_value2,'~NVL~')
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
and val_1 = p_val ;
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number);
,p_last_update_date IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group in VARCHAR2
,p_ext_crit_val_id in varchar2 default null
,p_lookup_code1 in varchar2 default null
,p_lookup_code2 in varchar2 default null
) IS
--
l_ext_prfl_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_crit_prfl_id
INTO l_ext_prfl_id
FROM ben_ext_crit_prfl
WHERE name = p_profile_name
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT ext_crit_typ_id
INTO l_ext_crit_typ_id
FROM ben_ext_crit_typ
WHERE ext_crit_prfl_id = l_ext_prfl_id
AND crit_typ_cd = p_type_code
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT ext_crit_val_id
INTO l_ext_crit_val_id
FROM ben_ext_crit_val
WHERE ext_crit_typ_id = l_ext_crit_typ_id
--AND val_1 = p_val
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT 'Y'
INTO l_temp
FROM ben_ext_crit_cmbn
WHERE ext_crit_val_id = l_ext_crit_val_id
AND crit_typ_cd = p_crit_typ_cd
and oper_cd = p_oper_cd
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
ben_xcc_ins.ins(p_effective_date => l_last_update_date
,p_ext_crit_cmbn_id => l_ext_crit_cmbn_id
,p_ext_crit_val_id => l_ext_crit_val_id
,p_crit_typ_cd => p_crit_typ_cd
,p_oper_cd => p_oper_cd
,p_val_1 => l_val_1
,p_val_2 => l_val_2
,p_business_group_id => l_new_business_group_id
,p_legislation_code => l_legislation_code
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_object_version_number => l_object_version_number);
,p_last_update_date IN VARCHAR2
,p_kickoff_wrt_prc_flag IN VARCHAR2
,p_apnd_rqst_id_flag IN VARCHAR2
,p_prmy_sort_cd IN VARCHAR2
,p_scnd_sort_cd IN VARCHAR2
,p_strt_dt IN VARCHAR2
,p_end_dt IN VARCHAR2
,p_spcl_hndl_flag IN VARCHAR2
,p_upd_cm_sent_dt_flag IN VARCHAR2
,p_use_eff_dt_for_chgs_flag IN VARCHAR2
,p_data_typ_cd IN VARCHAR2
,p_ext_typ_cd IN VARCHAR2
,p_drctry_name IN VARCHAR2
,p_output_name IN VARCHAR2
,p_post_processing_rule IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group IN VARCHAR2
,p_xml_tag_name in VARCHAR2
,p_output_type in VARCHAR2
,p_xdo_template_name in VARCHAR2
,p_ext_global_flag in VARCHAR2 default 'N'
,p_cm_display_flag in VARCHAR2 default 'N'
) IS
--
l_ext_prfl_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_file_id
INTO l_ext_file_id
FROM ben_ext_file
WHERE name = p_file_name
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
SELECT formula_id
INTO l_ext_post_prcs_rl
FROM ff_formulas_f
WHERE formula_name = p_post_processing_rule
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND trunc(sysdate) BETWEEN effective_start_date and effective_end_date ;
select xdo.template_id
from xdo_templates_b xdo
where xdo.template_code = P_xdo_template_name
order by decode(xdo.application_id ,FND_GLOBAL.resp_appl_id,1,2)
;
SELECT ext_crit_prfl_id
INTO l_ext_prfl_id
FROM ben_ext_crit_prfl
WHERE name = p_profile_name
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
,p_last_update_date => p_last_update_date
,p_legislation_code => p_legislation_code
,p_business_group => p_business_group
) ;
SELECT object_version_number ,ext_dfn_id
INTO l_ovn,l_ext_dfn_id
FROM ben_ext_dfn
WHERE ext_file_id = l_ext_file_id
AND name = p_definition_name
AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
,p_last_update_date => l_last_update_date
,p_creation_date => l_last_update_date
,p_last_update_login => 0
,p_created_by => l_last_updated_by
,p_last_updated_by => l_last_updated_by
,p_ext_post_prcs_rl => l_ext_post_prcs_rl
,p_object_version_number => l_object_version_number
,p_xml_tag_name => p_xml_tag_name
,p_output_type => l_output_type
,p_xdo_template_id => l_template_id
,p_ext_global_flag => nvl(p_ext_global_flag, 'N')
,p_cm_display_flag => nvl(p_cm_display_flag, 'N')
);
,p_last_update_date IN VARCHAR2
,p_val IN VARCHAR2
,p_dcd_val IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_business_group IN VARCHAR2
,p_chg_evt_source in VARCHAR2 default null
) is
l_ext_data_elmt_decd_id NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
select decd_flag
from ben_ext_fld
where ext_fld_id = p_ext_fld_id ;
,p_last_update_vc => p_last_update_date
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_legislation_code => l_legislation_code
,p_business_group => p_business_group
,p_business_group_id => l_new_business_group_id );
SELECT ext_data_elmt_id,ext_fld_id
INTO l_ext_data_elmt_id,l_ext_fld_id
FROM ben_ext_data_elmt
WHERE name = p_element_name
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
--AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
SELECT 'Y'
INTO l_temp
FROM ben_ext_data_elmt_decd
WHERE ext_data_elmt_id = l_ext_data_elmt_id
AND val = p_val
AND dcd_val = p_dcd_val
AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
( p_business_group is null and business_group_id is null ) )
--AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
, p_last_update_date => l_last_update_date
, p_creation_date => l_last_update_date
, p_last_updated_by => l_last_updated_by
, p_last_update_login => 0
, p_created_by => l_last_updated_by
, p_object_version_number => l_object_version_number
, p_chg_evt_source => p_chg_evt_source );
cursor cep is select event_group_name
from pay_event_groups
where event_group_id = p_CHG_EVT_CD
;
cursor cep is select event_group_id
from pay_event_groups
where event_group_name = P_CHG_EVT_CD
and nvl(business_group_id,nvl(p_business_group_id,-1))
= nvl(p_business_group_id,-1)
;