APPS.PQH_PA_WHATIF_PROCESS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19

  SELECT name
    FROM fnd_currencies_vl
   WHERE currency_code = p_uom
     AND enabled_flag = 'Y'
     AND p_effective_date BETWEEN nvl(start_date_active,p_effective_date) AND nvl(end_date_active,p_effective_date);

Line: 26

  SELECT meaning
    FROM hr_lookups
   WHERE lookup_type= 'BEN_NNMNTRY_UOM'
     AND lookup_code=p_nnmntry_uom
     AND enabled_flag = 'Y'
     AND p_effective_date BETWEEN nvl(start_date_active,p_effective_date) AND nvl(end_date_active,p_effective_date);

Line: 75

    SELECT lf_evt_ocrd_dt
      FROM ben_ptnl_ler_for_per ptn
     WHERE ptn.person_id          =  p_person_id
       AND ptn.business_group_id  =  p_business_group_id
       AND ptn.ler_id IN ( SELECT ler_id FROM ben_ler_f ler WHERE ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
       AND ptn.ptnl_ler_for_per_stat_cd NOT IN('VOIDD','PROCD');

Line: 119

SELECT ptn.ler_id,ler.name,ler.ovridg_le_flag
  FROM ben_ptnl_ler_for_per ptn,
       ben_ler_f            ler
  WHERE    ptn.person_id          =  p_person_id
       AND ptn.business_group_id  =  p_business_group_id
       AND ptn.ler_id             =ler.ler_id
       AND p_effective_date between ler.effective_start_date and effective_end_date
       AND ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO')
       AND ptn.ptnl_ler_for_per_stat_cd NOT IN('VOIDD','PROCD');

Line: 141

g_hierarchy.DELETE;

Line: 189

  UPDATE ben_ptnl_ler_for_per
     SET ptnl_ler_for_per_stat_cd = 'VOIDD'
   WHERE person_id =  p_person_id
     AND business_group_id = p_business_group_id
     AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
     AND lf_evt_ocrd_dt <= p_effective_date
     AND ptnl_ler_for_per_stat_cd IN ('UNPROCD', 'DTCTD');  -- 5763776 Removed 'PROCD'. Should not require this.

Line: 218

  UPDATE ben_per_in_ler
     SET per_in_ler_stat_cd = 'VOIDD'
        ,voidd_dt = p_effective_date
   WHERE person_id         = p_person_id
     AND business_group_id = p_business_group_id
     AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
     AND per_in_ler_stat_cd = 'STRTD';

Line: 249

  UPDATE ben_ptnl_ler_for_per
     SET ptnl_ler_for_per_stat_cd = 'VOIDD'
   WHERE person_id =  p_person_id
     AND business_group_id = p_business_group_id
     AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO')
                     and ler.ler_id <> p_winning_ler_id)
     AND lf_evt_ocrd_dt <= p_effective_date
     AND ptnl_ler_for_per_stat_cd IN ('UNPROCD', 'DTCTD');

Line: 299

    select trs.transaction_step_id
          ,trs.api_name
    from   hr_api_transaction_steps trs
    where  trs.transaction_id = p_transaction_id
      and  trs.object_type is null
      and  trs.api_name not in ('BEN_PROCESS_COMPENSATION_W.PROCESS_API')
    order by trs.processing_order, trs.transaction_step_id;

Line: 395

select transaction_step_id
      ,api_name
from   hr_api_transaction_steps
where  transaction_id = p_transaction_id
       order by transaction_step_id, api_name;

Line: 402

select  datatype
       ,name
       ,varchar2_value
       ,number_value
       ,date_value
from   hr_api_transaction_values
where  transaction_step_id = c_transaction_step_id
       order by transaction_value_id, datatype;

Line: 412

select ler.ler_id
from   ben_ler_f ler
where  ler.typ_cd = p_typ_cd
and    ler.business_group_id  = p_business_group_id
and    p_effective_date between
      ler.effective_start_date and ler.effective_end_date;

Line: 420

select date_of_birth,original_date_of_hire hire_date
from   per_all_people_f
where  person_id = p_person_id
and    business_group_id  = p_business_group_id
and    p_effective_date between
       effective_start_date and effective_end_date;

Line: 428

Select meaning
From hr_lookups
Where lookup_code=c_lookup_cd
and lookup_type='BEN_TM_UOM';

Line: 2248

   SELECT val
     FROM ben_enrt_bnft
    WHERE elig_per_elctbl_chc_id  = p_elig_per_elctbl_chc_id
      AND business_group_id       = p_business_group_id
      AND bnft_typ_cd             = 'CVG' ;

Line: 2282

Select Meaning
From hr_lookups
Where lookup_type='BEN_SS_DRVD_LABELS'
  and lookup_code='COBJ';

Line: 2288

Select pil.per_in_ler_id,
       ler.name           name,
       ler.ler_id
From   ben_per_in_ler  pil,
       ben_ler_f ler
Where  pil.person_id           =  p_person_id
   AND ler.ler_id              =  pil.ler_id
   AND p_effective_date BETWEEN ler.effective_start_date and ler.effective_end_date
   AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
   AND pil.lf_evt_ocrd_dt     <=  p_effective_date
   AND pil.business_group_id   =  p_business_group_id
   AND ler.typ_cd              NOT IN ('COMP','SCHEDDU','SCHEDDO')
   ORDER BY pil.lf_evt_ocrd_dt desc, 1 desc;

Line: 2304

Select  pgm.pgm_id
       ,pgm.name
       ,pgm.pgm_uom uom
       ,pgm.acty_ref_perd_cd acty_ref_perd_cd
       ,popl.pil_elctbl_chc_popl_id
From  ben_pil_elctbl_chc_popl popl,
      ben_pgm_f pgm
Where popl.per_in_ler_id = p_per_in_ler_id
  and popl.pgm_id        =pgm.pgm_id
  and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;

Line: 2317

Select distinct pt.pl_typ_id,
       pt.name
From   ben_pl_typ_f pt,
       ben_elig_per_elctbl_chc epe,
       ben_pil_elctbl_chc_popl popl
Where  pt.pl_typ_id  = epe.pl_typ_id
       and popl.per_in_ler_id          = p_per_in_ler_id
       and popl.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
       and epe.comp_lvl_cd             = 'PLAN'
       and popl.pl_id                  = epe.pl_id
       and p_effective_date between pt.effective_start_date and pt.effective_end_date;

Line: 2331

Select pt.pl_typ_id
      ,pt.name
      ,epe.ptip_id
From   ben_elig_per_elctbl_chc epe,
       ben_pl_typ_f pt
Where  epe.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
   and epe.pl_typ_id              = pt.pl_typ_id
   and epe.comp_lvl_cd            = 'PLAN'
   and p_effective_date between pt.effective_start_date and pt.effective_end_date;*/

Line: 2341

Select pt.pl_typ_id,
       pt.name,
       ptip.ptip_id
From   ben_pl_typ_f pt,
       ben_ptip_f ptip
Where
       ptip.pl_typ_id=pt.pl_typ_id
   and pt.pl_typ_id in (  Select epe.pl_typ_id
                          From ben_elig_per_elctbl_chc epe
	                  Where epe.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
	                    and epe.comp_lvl_cd            = 'PLAN'
			   and epe.pgm_id=ptip.pgm_id
		       )
	and p_effective_date between pt.effective_start_date and pt.effective_end_date
	and p_effective_date between ptip.effective_start_date and ptip.effective_end_date;

Line: 2361

Select epe.pl_id,
       epe.plip_id,
       pl.name name,
       pl.nip_pl_uom uom,
       pl.nip_acty_ref_perd_cd acty_ref_perd_cd,
       epe.elctbl_flag,
       epe.elig_per_elctbl_chc_id
From   ben_pl_f pl,
       ben_elig_per_elctbl_chc epe
Where
       epe.per_in_ler_id = p_per_in_ler_id
   and epe.comp_lvl_cd   = 'PLAN'
   and epe.pl_id         = pl.pl_id
   and epe.pl_typ_id     = p_pl_typ_id
   and nvl(epe.pgm_id ,-1) = p_pgm_id
   and p_effective_date between pl.effective_start_date and effective_end_date;

Line: 2379

Select opt.opt_id,
       oipl.oipl_id,
       opt.name,
       epe.elctbl_flag,
       epe.elig_per_elctbl_chc_id
From   ben_elig_per_elctbl_chc epe,
       ben_oipl_f              oipl,
       ben_opt_f               opt
Where  epe.per_in_ler_id   = p_per_in_ler_id
  AND  epe.plip_id    = p_plip_id
  AND  epe.comp_lvl_cd = 'OIPL'
  AND  epe.oipl_id     = oipl.oipl_id
  AND  oipl.opt_id     = opt.opt_id
  AND  p_effective_date between oipl.effective_start_date and oipl.effective_end_date
  AND  p_effective_date between  opt.effective_start_date and  opt.effective_end_date;

Line: 2396

Select opt.opt_id,
       oipl.oipl_id,
       opt.name,
       epe.elctbl_flag,
       epe.elig_per_elctbl_chc_id
From   ben_elig_per_elctbl_chc epe,
       ben_oipl_f              oipl,
       ben_opt_f               opt
Where  epe.per_in_ler_id   = p_per_in_ler_id
  AND  epe.plip_id is NULL and epe.pl_id=p_pl_id
  AND  epe.comp_lvl_cd = 'OIPL'
  AND  epe.oipl_id     = oipl.oipl_id
  AND  oipl.opt_id     = opt.opt_id
  AND  p_effective_date between oipl.effective_start_date and oipl.effective_end_date
  AND  p_effective_date between  opt.effective_start_date and  opt.effective_end_date;

Line: 2413

Select abr.name,
       abr.acty_base_rt_id,
       ecr.enrt_rt_id,
       decode(ecr.entr_val_at_enrt_flag, 'Y', ecr.dflt_val, ecr.val) val,
       ecr.cmcd_val,
       ecr.cmcd_acty_ref_perd_cd,
       ecr.nnmntry_uom
From   ben_enrt_rt             ecr,
       ben_acty_base_rt_f      abr
Where  ecr.elig_per_elctbl_chc_id  =  p_elig_per_elctbl_chc_id
   AND ecr.dsply_on_enrt_flag = 'Y'
   AND ecr.enrt_bnft_id IS NULL
   AND abr.acty_base_rt_id = ecr.acty_base_rt_id
   AND p_effective_date BETWEEN abr.effective_start_date AND abr.effective_end_date

UNION ALL
Select abr.name,
       abr.acty_base_rt_id,
       ecr.enrt_rt_id,
       decode(ecr.entr_val_at_enrt_flag, 'Y', ecr.dflt_val, ecr.val) val,
       ecr.cmcd_val,
       ecr.cmcd_acty_ref_perd_cd,
       ecr.nnmntry_uom
From   ben_enrt_rt             ecr,
       ben_enrt_bnft           enb,
       ben_acty_base_rt_f      abr
Where  enb.elig_per_elctbl_chc_id  =  p_elig_per_elctbl_chc_id
  AND  ecr.dsply_on_enrt_flag = 'Y'
  AND  enb.enrt_bnft_id = ecr.enrt_bnft_id
  AND  abr.acty_base_rt_id = ecr.acty_base_rt_id
  AND  p_effective_date between abr.effective_start_date and abr.effective_end_date;

Line: 2937

Select Meaning
From hr_lookups
Where lookup_type='BEN_SS_DRVD_LABELS'
  and lookup_code='COBJ';

Line: 2943

Select pil.per_in_ler_id,
       ler.name           name,
       ler.ler_id
From   ben_per_in_ler  pil,
       ben_ler_f ler
Where  pil.person_id           =  p_person_id
   AND ler.ler_id              =  pil.ler_id
   AND p_effective_date BETWEEN ler.effective_start_date and ler.effective_end_date
   AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
   AND pil.lf_evt_ocrd_dt     <=  p_effective_date
   AND pil.business_group_id   =  p_business_group_id
   AND ler.typ_cd              NOT IN ('COMP','SCHEDDU','SCHEDDO')
   ORDER BY pil.lf_evt_ocrd_dt desc, 1 desc;

Line: 2959

Select  pgm.pgm_id
       ,pgm.name
       ,pgm.pgm_uom uom
       ,pgm.acty_ref_perd_cd acty_ref_perd_cd
       ,popl.pil_elctbl_chc_popl_id
From  ben_pil_elctbl_chc_popl popl,
      ben_pgm_f pgm
Where popl.per_in_ler_id = p_per_in_ler_id
  and popl.pgm_id        =pgm.pgm_id
  and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;

Line: 2972

Select pt.pl_typ_id,
       pt.name
From   ben_pl_typ_f pt
Where  pt.pl_typ_id IN ( Select epe.pl_typ_id
                           From ben_elig_per_elctbl_chc epe,
				ben_pil_elctbl_chc_popl popl
                          Where popl.per_in_ler_id          = p_per_in_ler_id
			    and popl.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
			    and epe.comp_lvl_cd             = 'PLAN'
			    and popl.pl_id                  = epe.pl_id
                        )
   and p_effective_date between pt.effective_start_date and pt.effective_end_date;

Line: 2987

Select pt.pl_typ_id
      ,pt.name
      ,epe.ptip_id
From   ben_elig_per_elctbl_chc epe,
       ben_pl_typ_f pt
Where  epe.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
   and epe.pl_typ_id              = pt.pl_typ_id
   and epe.comp_lvl_cd            = 'PLAN'
   and p_effective_date between pt.effective_start_date and pt.effective_end_date;*/

Line: 2997

Select pt.pl_typ_id,
       pt.name,
       ptip.ptip_id
From   ben_pl_typ_f pt,
       ben_ptip_f ptip
Where
       ptip.pl_typ_id=pt.pl_typ_id
   and pt.pl_typ_id in (  Select epe.pl_typ_id
                          From ben_elig_per_elctbl_chc epe
	                  Where epe.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
	                    and epe.comp_lvl_cd            = 'PLAN'
			   and epe.pgm_id=ptip.pgm_id
		       )
	and p_effective_date between pt.effective_start_date and pt.effective_end_date
	and p_effective_date between ptip.effective_start_date and ptip.effective_end_date;

Line: 3017

Select epe.pl_id,
       epe.plip_id,
       pl.name name,
       pl.nip_pl_uom uom,
       pl.nip_acty_ref_perd_cd acty_ref_perd_cd,
       epe.elctbl_flag,
       epe.elig_per_elctbl_chc_id
From   ben_pl_f pl,
       ben_elig_per_elctbl_chc epe
Where
       epe.per_in_ler_id = p_per_in_ler_id
   and epe.comp_lvl_cd   = 'PLAN'
   and epe.pl_id         = pl.pl_id
   and epe.pl_typ_id     = p_pl_typ_id
   and nvl(epe.pgm_id ,-1) = p_pgm_id
   and p_effective_date between pl.effective_start_date and effective_end_date;

Line: 3035

Select opt.opt_id,
       oipl.oipl_id,
       opt.name,
       epe.elctbl_flag,
       epe.elig_per_elctbl_chc_id
From   ben_elig_per_elctbl_chc epe,
       ben_oipl_f              oipl,
       ben_opt_f               opt
Where  epe.per_in_ler_id   = p_per_in_ler_id
  AND  epe.plip_id    = p_plip_id
  AND  epe.comp_lvl_cd = 'OIPL'
  AND  epe.oipl_id     = oipl.oipl_id
  AND  oipl.opt_id     = opt.opt_id
  AND  p_effective_date between oipl.effective_start_date and oipl.effective_end_date
  AND  p_effective_date between  opt.effective_start_date and  opt.effective_end_date;

Line: 3052

Select opt.opt_id,
       oipl.oipl_id,
       opt.name,
       epe.elctbl_flag,
       epe.elig_per_elctbl_chc_id
From   ben_elig_per_elctbl_chc epe,
       ben_oipl_f              oipl,
       ben_opt_f               opt
Where  epe.per_in_ler_id   = p_per_in_ler_id
  AND  epe.plip_id is NULL and epe.pl_id=p_pl_id
  AND  epe.comp_lvl_cd = 'OIPL'
  AND  epe.oipl_id     = oipl.oipl_id
  AND  oipl.opt_id     = opt.opt_id
  AND  p_effective_date between oipl.effective_start_date and oipl.effective_end_date
  AND  p_effective_date between  opt.effective_start_date and  opt.effective_end_date;

Line: 3069

Select abr.name,
       abr.acty_base_rt_id,
       ecr.enrt_rt_id,
       decode(ecr.entr_val_at_enrt_flag, 'Y', ecr.dflt_val, ecr.val) val,
       ecr.cmcd_val,
       ecr.cmcd_acty_ref_perd_cd,
       ecr.nnmntry_uom
From   ben_enrt_rt             ecr,
       ben_acty_base_rt_f      abr
Where  ecr.elig_per_elctbl_chc_id  =  p_elig_per_elctbl_chc_id
   AND ecr.dsply_on_enrt_flag = 'Y'
   AND ecr.enrt_bnft_id IS NULL
   AND abr.acty_base_rt_id = ecr.acty_base_rt_id
   AND p_effective_date BETWEEN abr.effective_start_date AND abr.effective_end_date

UNION ALL
Select abr.name,
       abr.acty_base_rt_id,
       ecr.enrt_rt_id,
       decode(ecr.entr_val_at_enrt_flag, 'Y', ecr.dflt_val, ecr.val) val,
       ecr.cmcd_val,
       ecr.cmcd_acty_ref_perd_cd,
       ecr.nnmntry_uom
From   ben_enrt_rt             ecr,
       ben_enrt_bnft           enb,
       ben_acty_base_rt_f      abr
Where  enb.elig_per_elctbl_chc_id  =  p_elig_per_elctbl_chc_id
  AND  ecr.dsply_on_enrt_flag = 'Y'
  AND  enb.enrt_bnft_id = ecr.enrt_bnft_id
  AND  abr.acty_base_rt_id = ecr.acty_base_rt_id
  AND  p_effective_date between abr.effective_start_date and abr.effective_end_date;

Line: 3417

Select hierarchy_id from
pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and parent_hierarchy_id=p_plip_id
and hierarchy_type='OIPLIP';

Line: 3424

Select information3
From   pqh_role_extra_info ,
       ben_oiplip_f
Where  oipl_id=p_oipl_id
and    plip_id=p_plip_id
and    g_effective_date between effective_start_date and effective_end_date
and    role_id=g_role_id
and    information_type='BEN_SS_ROLE_COMP_OBJECTS'
and    information1='OIPLIP'
and    information2=to_char(oiplip_id);

Line: 3442

Delete from pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and hierarchy_id=C1.hierarchy_id and hierarchy_type='OIPLIP';

Line: 3461

Select hierarchy_id from
pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and parent_hierarchy_id=p_ptip_id
and hierarchy_type='PLIP';

Line: 3468

Select information3
From   pqh_role_extra_info
Where  role_id=g_role_id
and    information_type='BEN_SS_ROLE_COMP_OBJECTS'
and    information1='PLIP'
and    information2=to_char(p_plip_id);

Line: 3483

   Delete from pqh_pa_whatif_results
    where whatif_results_batch_id=p_whatif_results_batch_id
    and hierarchy_id=C1.hierarchy_id and hierarchy_type='PLIP';

Line: 3507

Select hierarchy_id from
pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and parent_hierarchy_id=p_pgm_id
and hierarchy_type='PTIP';

Line: 3514

Select information3
From   pqh_role_extra_info
Where  role_id=g_role_id
and    information_type='BEN_SS_ROLE_COMP_OBJECTS'
and    information1='PTIP'
and    information2=to_char(p_ptip_id);

Line: 3529

   Delete from pqh_pa_whatif_results
    where whatif_results_batch_id=p_whatif_results_batch_id
    and hierarchy_id=C1.hierarchy_id and hierarchy_type='PTIP';

Line: 3550

Select hierarchy_id from
pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and hierarchy_type='PGM';

Line: 3556

Select information3
From   pqh_role_extra_info
Where  role_id=g_role_id
and    information_type='BEN_SS_ROLE_COMP_OBJECTS'
and    information1='PGM'
and    information2=p_pgm_id;

Line: 3571

   Delete from pqh_pa_whatif_results
    where whatif_results_batch_id=p_whatif_results_batch_id
    and hierarchy_id=C1.hierarchy_id and hierarchy_type='PGM';

Line: 3591

Select hierarchy_id from
pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and parent_hierarchy_id=p_pnip_id
and hierarchy_type='OIPNIP';

Line: 3598

Select information3
From   pqh_role_extra_info
Where  role_id=g_role_id
and    information_type='BEN_SS_ROLE_COMP_OBJECTS'
and    information1='OIPNIP'
and    information2=to_char(p_oipnip_id);

Line: 3612

Delete from pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and hierarchy_id=C1.hierarchy_id and hierarchy_type='OIPNIP';

Line: 3632

Select hierarchy_id from
pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and parent_hierarchy_id=p_pl_typ_id
and hierarchy_type='PNIP';

Line: 3639

Select information3
From   pqh_role_extra_info
Where  role_id=g_role_id
and    information_type='BEN_SS_ROLE_COMP_OBJECTS'
and    information1='PNIP'
and    information2=to_char(p_pnip_id);

Line: 3654

   Delete from pqh_pa_whatif_results
    where whatif_results_batch_id=p_whatif_results_batch_id
    and hierarchy_id=C1.hierarchy_id and hierarchy_type='PNIP';

Line: 3675

Select hierarchy_id from
pqh_pa_whatif_results
where whatif_results_batch_id=p_whatif_results_batch_id
and hierarchy_type='PT';

Line: 3681

Select information3
From   pqh_role_extra_info
Where  role_id=g_role_id
and    information_type='BEN_SS_ROLE_COMP_OBJECTS'
and    information1='PT'
and    information2=p_pl_typ_id;

Line: 3696

   Delete from pqh_pa_whatif_results
    where whatif_results_batch_id=p_whatif_results_batch_id
    and hierarchy_id=C1.hierarchy_id and hierarchy_type='PT';

Line: 3710

Select 1
From  pqh_role_extra_info
Where role_id=g_role_id
 and  information_type='BEN_SS_ROLE_COMP_OBJECTS'
 and  information1 <> 'LE';

Line: 3742

    SELECT pqh_pa_whatif_results_s.nextval
      FROM sys.dual;

Line: 3759

    INSERT INTO PQH_PA_WHATIF_RESULTS (
                WHATIF_RESULTS_BATCH_ID
               ,PERSON_ID
               ,BUSINESS_GROUP_ID
               ,TRANSACTION_ID
               ,LER_ID
               ,HIERARCHY_ID
               ,HIERARCHY_TYPE
               ,PARENT_HIERARCHY_ID
               ,PARENT_HIERARCHY_TYPE
               ,NAME
               ,UOM
               ,NNMNTRY_UOM
               ,ACTY_REF_PERD_CD
               ,CRRNT_ELCTBL_FLAG
               ,CRRNT_CVG_VAL
               ,CRRNT_VAL
               ,CRRNT_CMCD_VAL
               ,CRRNT_CMCD_ACTY_REF_PERD_CD
               ,WATIF_ELCTBL_FLAG
               ,WATIF_CVG_VAL
               ,WATIF_VAL
               ,WATIF_CMCD_VAL
               ,WATIF_CMCD_ACTY_REF_PERD_CD
               ,OBJECT_VERSION_NUMBER)
    VALUES (
                p_whatif_results_batch_id
               ,p_person_id
               ,p_business_group_id
               ,p_transaction_id
               ,p_ler_id
               ,g_hierarchy(i).HIERARCHY_ID
               ,g_hierarchy(i).HIERARCHY_TYPE
               ,g_hierarchy(i).PARENT_HIERARCHY_ID
               ,g_hierarchy(i).PARENT_HIERARCHY_TYPE
               ,g_hierarchy(i).NAME
               ,g_hierarchy(i).UOM
               ,g_hierarchy(i).NNMNTRY_UOM
               ,g_hierarchy(i).ACTY_REF_PERD_CD
               ,g_hierarchy(i).CRRNT_ELCTBL_FLAG
               ,g_hierarchy(i).CRRNT_CVG_VAL
               ,g_hierarchy(i).CRRNT_VAL
               ,g_hierarchy(i).CRRNT_CMCD_VAL
               ,g_hierarchy(i).CRRNT_CMCD_ACTY_REF_PERD_CD
               ,g_hierarchy(i).WATIF_ELCTBL_FLAG
               ,g_hierarchy(i).WATIF_CVG_VAL
               ,g_hierarchy(i).WATIF_VAL
               ,g_hierarchy(i).WATIF_CMCD_VAL
               ,g_hierarchy(i).WATIF_CMCD_ACTY_REF_PERD_CD
               ,1 );

Line: 3829

    SELECT DISTINCT whatif_results_batch_id
      FROM pqh_pa_whatif_results
     WHERE CREATION_DATE < trunc(SYSDATE);

Line: 3843

     DELETE PQH_PA_WHATIF_RESULTS
      WHERE WHATIF_RESULTS_BATCH_ID = csr_purge_data_rec.whatif_results_batch_id;

Line: 3863

Select rls.role_id
 From pqh_roles rls
     ,per_people_extra_info pei
     ,fnd_user usr
Where rls.role_type_cd=p_user_type
  and nvl(rls.enable_flag,'N')='Y'
  and rls.role_id =to_number(pei_information3)
  and pei.information_type='PQH_ROLE_USERS'
  and nvl(pei_information5,'N')='Y'
  and nvl(pei_information9,'N')='Y'
  and pei.person_id=usr.employee_id
  and usr.user_id=p_user_id;

Line: 3877

Select role_id
From  pqh_roles
Where role_type_cd=p_user_type
and   role_name='XXXX';

Line: 3926

select ptn.ptnl_ler_for_per_id,
       ptn.ptnl_ler_for_per_stat_cd,
       ptn.lf_evt_ocrd_dt lf_evt_ocrd_dt
       from   ben_ptnl_ler_for_per ptn,
       ben_ler_f            ler
where ptn.person_id = p_person_id
and ptn.business_group_id = p_business_group_id
and ptn.lf_evt_ocrd_dt > l_lf_evt_ocrd_dt
and ptn.ler_id = ler.ler_id
and ptn.lf_evt_ocrd_dt between
    ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('SCHEDDU','COMP','GSP','ABS')
and ptn.ptnl_ler_for_per_stat_cd in ('UNPROCD', 'DTCTD', 'MNL', 'MNLO')
order by lf_evt_ocrd_dt desc;

Line: 4122

  g_hierarchy.DELETE;

Line: 4133

    g_hierarchy.DELETE;

Line: 4207

      SELECT pqh_pa_whatif_results_s.nextval INTO p_whatif_results_batch_id from dual;

Line: 4218

       INSERT INTO PQH_PA_WHATIF_RESULTS (
                   WHATIF_RESULTS_BATCH_ID
                  ,PERSON_ID
                  ,BUSINESS_GROUP_ID
                  ,LER_ID
                  ,NAME
                  )
           VALUES (
                   p_whatif_results_batch_id
                  ,p_person_id
                  ,p_business_group_id
                  ,g_hierarchy(i).ler_id
                  ,g_hierarchy(i).NAME
                  );

Line: 4240

  g_hierarchy.DELETE;

Line: 4252

  g_hierarchy.DELETE;

Line: 4262

SELECT transaction_step_id
FROM   hr_api_transaction_steps
WHERE  transaction_id=txn_id;

Line: 4279

   ,p_selected_person_id          =>p_person_id
   ,p_transaction_effective_date  =>sysdate
   ,p_process_name                =>'SSBENWHATIF'
   ,p_status                      =>'ACTIVE'
   ,p_transaction_id              =>p_txn_id);

Line: 4287

DELETE FROM hr_api_transaction_values
WHERE  transaction_step_id=rec_typ.transaction_step_id;

Line: 4290

DELETE FROM hr_api_transaction_steps
WHERE  transaction_id=p_txn_id;

Line: 4304

Select whatif_lbl_txt  label
From   ben_per_info_chg_cs_ler_f a,
       ben_ler_per_info_cs_ler_f b
Where  a.PER_INFO_CHG_CS_LER_ID=b.PER_INFO_CHG_CS_LER_ID
   and b.ler_id=p_ler_id
   and a.WHATIF_LBL_TXT is not null
   and p_effective_date between a.effective_start_date and a.effective_end_date
   and p_effective_date between b.effective_start_date and b.effective_end_date;