APPS.BEN_CWB_XCHG_PKG SQL Statements

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

Line: 22

procedure insert_into_ben_cwb_xchg(p_group_pl_id    IN number,
                                   p_lf_evt_ocrd_dt IN date,
                                   p_effective_date IN date,
                                   p_refresh_always IN varchar2 default 'N',
                                   p_currency IN varchar2 default null,
                                   p_xchg_rate IN number default null) IS
   --
   -- cursor for fetching Bg_ID, Effective_Date and Pl_UOM for exchange rate Calculation
   cursor csr_pl_dsgn_recs(p_group_pl_id number,
                           p_lf_evt_ocrd_dt date,
                           p_effective_date date) is
   select pl_uom
         ,nvl(p_effective_date, nvl(data_freeze_date, lf_evt_ocrd_dt))
         ,business_group_id
   from  ben_cwb_pl_dsgn
   where pl_id          = p_group_pl_id
   and   lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
   and   oipl_id        = -1;

Line: 44

   select distinct base_salary_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   person_id      =  -1
    and   base_salary_currency is not null

   UNION
   select distinct salary_1_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   person_id      =  -1
    and   salary_1_year_ago_currency is not null

   UNION
   select distinct salary_2_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   person_id      =  -1
    and   salary_2_year_ago_currency is not null

   UNION
   select distinct salary_3_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   person_id      =  -1
    and   salary_3_year_ago_currency is not null

   UNION
   select distinct salary_4_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   person_id      =  -1
    and   salary_4_year_ago_currency is not null

   UNION
   select distinct salary_5_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   person_id      =  -1
    and   salary_5_year_ago_currency is not null

   UNION
   select distinct mkt_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   person_id      =  -1
    and   mkt_currency is not null

   UNION
   select distinct prev_sal_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   person_id      =  -1
    and   prev_sal_currency is not null

   UNION

   select distinct cpr.currency lCurrency
     from ben_cwb_person_rates cpr
         ,ben_cwb_person_info  cpi
    where cpr.group_per_in_ler_id =  cpi.group_per_in_ler_id
    and   cpi.lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   cpi.group_pl_id    =  p_group_pl_id
    and   cpi.person_id      =  -1
    and   cpr.currency is not null;

Line: 122

   select distinct base_salary_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
      and lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
      and base_salary_currency is not null

   UNION

   select distinct cpr.currency lCurrency
     from ben_cwb_person_rates cpr
    where cpr.group_pl_id    =  p_group_pl_id
      and cpr.lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
      and cpr.currency is not null

   UNION
   select distinct salary_1_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   salary_1_year_ago_currency is not null

   UNION
   select distinct salary_2_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   salary_2_year_ago_currency is not null

   UNION
   select distinct salary_3_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   salary_3_year_ago_currency is not null

   UNION
   select distinct salary_4_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   salary_4_year_ago_currency is not null

   UNION
   select distinct salary_5_year_ago_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   salary_5_year_ago_currency is not null

   UNION
   select distinct mkt_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   mkt_currency is not null

   UNION
   select distinct prev_sal_currency lCurrency
     from ben_cwb_person_info
    where group_pl_id    =  p_group_pl_id
    and   lf_evt_ocrd_dt =  p_lf_evt_ocrd_dt
    and   prev_sal_currency is not null;

Line: 198

   l_proc     varchar2(72) := g_package||'insert_into_ben_cwb_xchg';

Line: 220

     delete from ben_cwb_xchg
      where  group_pl_id    = p_group_pl_id
      and    lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;

Line: 243

	  insert into ben_cwb_xchg(
                      group_pl_id
                     ,lf_evt_ocrd_dt
                     ,currency
                     ,xchg_rate)
             values(
                      l_xchg_rec.group_pl_id
                     ,l_xchg_rec.lf_evt_ocrd_dt
                     ,l_xchg_rec.currency
                     ,l_xchg_rec.xchg_rate
                   );

Line: 281

	  insert into ben_cwb_xchg(
                      group_pl_id
                     ,lf_evt_ocrd_dt
                     ,currency
                     ,xchg_rate)
             values(
                      l_xchg_rec.group_pl_id
                     ,l_xchg_rec.lf_evt_ocrd_dt
                     ,l_xchg_rec.currency
                     ,l_xchg_rec.xchg_rate
                   );

Line: 314

      delete from ben_cwb_xchg
      where  group_pl_id    = p_group_pl_id
      and    lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
      and    currency = l_currency;

Line: 326

	  insert into ben_cwb_xchg(
                      group_pl_id
                     ,lf_evt_ocrd_dt
                     ,currency
                     ,xchg_rate)
             values(
                      p_group_pl_id
                     ,p_lf_evt_ocrd_dt
                     ,l_currency
                     ,l_xchg_rate
                   );

Line: 348

end; -- of procedure insert_into_ben_cwb_xchg

Line: 383

  insert_into_ben_cwb_xchg(p_group_pl_id   ,
                           p_lf_evt_ocrd_dt,
   			   p_effective_date,
			   p_refresh_always,
                           null ,
                           null);