APPS.PQH_RBC_STAGE_TO_RBC SQL Statements

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

Line: 19

   select information1
   into l_parent_id
   from ben_copy_entity_results
   where copy_entity_txn_id = p_copy_entity_txn_id
   and copy_entity_result_id = p_copy_entity_result_id
   and table_alias = 'RMN';

Line: 45

Select *
from ben_acty_base_rt_f
where pl_id = p_pl_id
and  business_group_id = p_business_group_id
and  mapping_table_pk_id = p_mapping_table_pk_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 75

select CRITERIA_SHORT_CODE, ELIGY_PRFL_ID
into p_criteria_short_code, p_elig_prfl_id
from pqh_rate_matrix_nodes
where rate_matrix_node_id = p_rmn_id
and business_group_id = p_business_group_id;

Line: 94

     update ben_copy_entity_results
     set information1 = p_rmn_id
     where COPY_ENTITY_RESULT_ID = p_copy_entity_result_id
     and  copy_entity_txn_id = p_copy_entity_txn_id;

Line: 99

    update ben_copy_entity_results
    set  information161 = p_rmn_id
    where GS_PARENT_ENTITY_RESULT_ID = p_copy_entity_result_id
    and   copy_entity_txn_id = p_copy_entity_txn_id
    and table_alias in ('RMV');

Line: 105

    update ben_copy_entity_results
    set  information161 = p_rmn_id
    where PARENT_ENTITY_RESULT_ID = p_copy_entity_result_id
    and   copy_entity_txn_id = p_copy_entity_txn_id
    and table_alias in ('RMR');

Line: 121

   select meaning into l_name
     from hr_lookups
    where lookup_type = 'PQH_GSP_LE_PT_NAME'
      and lookup_code = 'RBC_PT';

Line: 154

l_sql := 'select '||l_seq||' from dual';

Line: 217

   select min(effective_start_date)
     into l_plan_type_date
     from ben_pl_typ_f
    where business_group_id = p_business_group_id
      and opt_typ_cd ='RBC'
      and pl_typ_stat_cd ='A';

Line: 250

         select pl_typ_id,name,effective_start_date
           into l_pt_id,l_pt_name,l_effective_start_date
           from ben_pl_typ_f
          where trunc(effective_end_date) = hr_general.end_of_time
            and business_group_id = p_business_group_id
            and opt_typ_cd ='RBC';

Line: 278

      hr_utility.set_location('setup is fine, update staging area',10);

Line: 292

procedure Delete_RMR(p_copy_entity_txn_id in number,
                        p_effective_date     in date,
                        p_business_group_id  in number,
                        p_Date_Track_Mode    in Varchar2) is

cursor del_rmr is
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and   table_alias = 'RMR'
and dml_operation = 'DELETE';

Line: 309

     hr_utility.set_location('Entering: Delete_RMR', 10);

Line: 314

   PQH_RATE_MATRIX_RATES_API.delete_rate_matrix_rate
                       (p_rate_matrix_rate_ID	  => del_rec.Information1
                       ,p_effective_start_date    => l_effective_start_date
                       ,p_effective_end_date      => l_effective_end_date
                       ,p_object_version_number   => l_ovn
                       ,p_effective_date          => p_effective_date
                       ,p_datetrack_mode          => p_Date_Track_Mode);

Line: 324

 hr_utility.set_location('Leaving: Delete_RMR', 10);

Line: 325

End Delete_RMR;

Line: 327

procedure Delete_RMV(p_copy_entity_txn_id in number,
                        p_effective_date     in date,
                        p_business_group_id  in number) is

cursor del_rmv is
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and   table_alias = 'RMV'
and dml_operation = 'DELETE';

Line: 339

 hr_utility.set_location('Entering: Delete_RMV', 10);

Line: 342

   PQH_RT_MATRIX_NODE_VALUES_API.delete_rt_matrix_node_value
                              (p_effective_date  => p_effective_date
                              ,p_NODE_VALUE_ID	 =>  Del_rec.information1
                              ,p_object_version_number => del_rec.information265
                                 );

Line: 348

  /* pqh_rbc_elpro.delete_criteria
                 (

                 )*/

  end loop;

Line: 355

   hr_utility.set_location('Leaving: Delete_RMV', 10);

Line: 356

End Delete_RMV;

Line: 358

procedure Delete_RMN(p_copy_entity_txn_id in number,
                        p_effective_date     in date,
                        p_business_group_id  in number) is

cursor del_rmn is
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and   table_alias = 'RMN'
and dml_operation = 'DELETE';

Line: 371

 hr_utility.set_location('Entering: Delete_RMN', 10);

Line: 374

   PQH_RATE_MATRIX_NODES_API.delete_rate_matrix_node
                          (p_effective_date        => p_effective_date
                          ,p_rate_matrix_node_id   => del_rec.information1
                          ,p_object_version_number => del_rec.information265
                          );

Line: 383

 hr_utility.set_location('Entering: Delete_RMN', 10);

Line: 385

End Delete_RMN;

Line: 387

procedure Delete_plan(p_copy_entity_txn_id in number,
                        p_effective_date     in date,
                        p_business_group_id  in number,
                        p_Date_Track_Mode in varchar2) is

cursor del_plan is
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and   table_alias = 'PLN'
and dml_operation = 'DELETE';

Line: 404

   hr_utility.set_location('inside delete_plan',1);

Line: 408

   ben_Plan_api.delete_Plan
                (p_pl_id                     => del_rec.information1
                ,p_effective_start_date      => l_effective_start_date
                ,p_effective_end_date        => l_effective_end_date
                ,p_object_version_number     => l_ovn
                ,p_effective_date            => p_effective_date
                ,p_datetrack_mode            => p_Date_Track_Mode
                 );

Line: 419

  hr_utility.set_location('leaving delete_plan',1);

Line: 420

End Delete_plan;

Line: 437

   If P_Date_Track_Mode = 'UPDATE_OVERRIDE' Then
      l_Del_Dt_Mode := 'DELETE';

Line: 443

   Delete_RMR(p_copy_entity_txn_id => p_copy_entity_txn_id,
              p_effective_date  => p_effective_date,
              p_business_group_id => p_business_group_id,
              p_Date_Track_Mode => 'ZAP');

Line: 448

   Delete_RMV(p_copy_entity_txn_id => p_copy_entity_txn_id,
              p_effective_date => p_effective_date,
              p_business_group_id => p_business_group_id);

Line: 452

   Delete_RMN(p_copy_entity_txn_id => p_copy_entity_txn_id,
              p_effective_date => p_effective_date,
              p_business_group_id => p_business_group_id);

Line: 457

   Delete_plan(p_copy_entity_txn_id => p_copy_entity_txn_id,
              p_effective_date => p_effective_date,
              p_business_group_id => p_business_group_id,
              p_Date_Track_Mode => l_Del_Dt_Mode);

Line: 507

      select *
      from ben_copy_entity_results
      where copy_entity_txn_id = p_copy_entity_txn_id
      and table_alias = 'RMV'
      and dml_operation in ('CREATE','UPDATE')
      and information161 = p_rmn_id;

Line: 533

    Assigning number values depending upon the criteria selected.
    if organization hierarchy id (information223 ) is not null then
        number_value1 = information223
        number_value2 = information224
    if  position hierarchy id (information225 ) is not null then
        number_value1 = information225
        number_value2 = information226
    else
       number_value1 = information169
       number_value2 = information174

*/
         if rmv_rec.information223 is not null then
               l_number_value1 := rmv_rec.information223;

Line: 609

      elsif rmv_rec.dml_operation in ('UPDATE')
      and l_ovn is not null
      and l_rmv_id is not null then
         hr_utility.set_location('Rate Node Value is being updated'||l_rmv_id,60);

Line: 626

            PQH_RT_MATRIX_NODE_VALUES_API.update_rt_matrix_node_value
                                   (p_effective_date => p_effective_date
                                    ,p_NODE_VALUE_ID  => l_rmv_id
                                    ,p_RATE_MATRIX_NODE_ID  => rmv_rec.INFORMATION161
                                    ,p_SHORT_CODE           => rmv_rec.INFORMATION12
                                    ,p_CHAR_VALUE1          => rmv_rec.INFORMATION13
                                    ,p_CHAR_VALUE2          => rmv_rec.INFORMATION14
                                    ,p_CHAR_VALUE3          => rmv_rec.INFORMATION15
                                    ,p_CHAR_VALUE4          => rmv_rec.INFORMATION16
                                    ,p_NUMBER_VALUE1        => l_number_value2
                                    ,p_NUMBER_VALUE2        => l_number_value2
                                    ,p_NUMBER_VALUE3        => rmv_rec.INFORMATION221
                                    ,p_NUMBER_VALUE4        => rmv_rec.INFORMATION222
                                    ,p_DATE_VALUE1          => rmv_rec.INFORMATION166
                                    ,p_DATE_VALUE2          => rmv_rec.INFORMATION167
                                    ,p_DATE_VALUE3          => rmv_rec.INFORMATION306
                                    ,p_DATE_VALUE4          => rmv_rec.INFORMATION307
                                    ,p_BUSINESS_GROUP_ID    => rmv_rec.INFORMATION4
                                    ,p_object_version_number => l_ovn );

Line: 678

    select pl.pl_id pl_id, pl.name pl_name
    into p_pl_id, p_pl_name
    from ben_pl_f pl,
         pqh_rate_matrix_nodes rmn
    where rmn.pl_id = pl.pl_id
    and p_effective_date between pl.effective_start_date and pl.effective_end_date
    and rmn.rate_matrix_node_id = p_rmn_id
    and rmn.business_group_id = p_business_group_id
    and pl.business_group_id = p_business_group_id
    and rmn.business_group_id = pl.business_group_id;

Line: 706

 select name crd_name
 into l_crd_name
 from pqh_criteria_rate_defn_vl
 where CRITERIA_RATE_DEFN_ID = p_crd_id
 and    business_group_id = p_business_group_id;

Line: 726

Select short_code
into  l_rmn_short_code
from  pqh_rate_matrix_nodes
where  rate_matrix_node_id = p_rmn_id
and     business_group_id = p_business_group_id;

Line: 768

      select *
      from ben_copy_entity_results
      where copy_entity_txn_id = p_copy_entity_txn_id
      and table_alias = 'RMR'
      and dml_operation in ('CREATE','UPDATE')
      and  information161 = p_rmn_id;

Line: 785

            select min(effective_start_date)
            into l_esd
            from ben_pl_f pl, pqh_rate_matrix_nodes rmn
            where  rmn.pl_id = pl.pl_id
            and    rmn.rate_matrix_node_id = p_rmn_id;

Line: 867

      elsif rmr_rec.dml_operation in ('UPDATE')
      and l_ovn is not null
      and l_rmr_id is not null then
         hr_utility.set_location('Rate Matrix node is being updated'||l_rmr_id,60);

Line: 873

              l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode(p_table_name  => 'PQH_RATE_MATRIX_RATES_F',
                                           p_key_column_name => 'RATE_MATRIX_RATE_ID',
                                           p_key_column_value => l_rmr_id,
                                           p_effective_date => p_effective_date);

Line: 891

            PQH_RATE_MATRIX_RATES_API.update_rate_matrix_rate
                                    (p_effective_date                => p_effective_date
                                    ,p_datetrack_mode                => l_dt_mode
                                    ,p_RATE_MATRIX_RATE_ID           => l_rmr_id
                                    ,p_EFFECTIVE_START_DATE          => l_effective_start_date
                                    ,p_EFFECTIVE_END_DATE            => l_effective_end_date
                                    ,p_RATE_MATRIX_NODE_ID          => rmr_rec.Information161
                                    ,p_CRITERIA_RATE_DEFN_ID        => rmr_rec.Information162
                                    ,p_MIN_RATE_VALUE               => rmr_rec.Information294
                                    ,p_MAX_RATE_VALUE               => rmr_rec.Information295
                                    ,p_MID_RATE_VALUE               => rmr_rec.Information296
                                    ,p_RATE_VALUE                   => nvl(rmr_rec.Information297,0)
                                    ,p_BUSINESS_GROUP_ID            => rmr_rec.Information4
                                    ,p_object_version_number        => l_ovn);

Line: 942

      l_datetrack_mode := 'UPDATE_OVERRIDE';

Line: 971

          update pqh_copy_entity_txns
           set status ='COMPLETED'
           where copy_entity_txn_id = p_copy_entity_txn_id;

Line: 977

      	  Delete from Ben_Copy_Entity_Results
  	      where Copy_Entity_Txn_Id = p_copy_entity_txn_id;

Line: 1016

 select PL_TYP_ID
 into l_pl_typ_id
 from ben_pl_typ_f
 where p_effective_date between effective_start_date and effective_end_date
 and business_group_id = p_business_group_id
 and opt_typ_cd = 'RBC';

Line: 1052

      select *
      from ben_copy_entity_results
      where copy_entity_txn_id = p_copy_entity_txn_id
      and table_alias = 'PLN'
      and dml_operation in ('CREATE','UPDATE');

Line: 1092

      elsif plan_rec.dml_operation in ('UPDATE')
      and l_ovn is not null
      and l_plan_id is not null then
         hr_utility.set_location('Plan is being updated'||l_plan_id,60);

Line: 1098

              l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode(p_table_name  => 'BEN_PL_F',
                                           p_key_column_name => 'PL_ID',
                                           p_key_column_value => l_plan_id,
                                           p_effective_date => p_effective_date);

Line: 1119

            BEN_PLAN_API.update_Plan(
                                   p_pl_id                 => l_plan_id
                                  ,p_effective_start_date  => l_effective_start_date
                                  ,p_effective_end_date    => l_effective_end_date
                                  ,p_name                  => plan_rec.information170
                                  ,p_pl_stat_cd            => plan_rec.Information19
                                  ,p_business_group_id     => plan_rec.Information4
                                  ,p_object_version_number => l_ovn
                                  ,p_effective_date        => p_effective_date
                                  ,p_datetrack_mode        => l_dt_mode
                                  ,p_short_name            => plan_rec.Information94
                                  ,p_short_code            => plan_rec.Information93
                                  ,p_vrfy_fmly_mmbr_cd     =>  null);

Line: 1158

     hr_utility.set_location('Rate_matrix_node row updated',40);

Line: 1186

      select *
      from ben_copy_entity_results
      where copy_entity_txn_id = p_copy_entity_txn_id
      and table_alias = 'RMN'
      and dml_operation in ('CREATE','UPDATE')
      order by information160;

Line: 1245

      elsif rmn_rec.dml_operation in ('UPDATE')
      and l_ovn is not null
      and l_rmn_id is not null then
         hr_utility.set_location('Rate Matrix Node is being updated'||l_rmn_id,60);

Line: 1259

            PQH_RATE_MATRIX_NODES_API.update_rate_matrix_node
                                    (p_effective_date => p_effective_date
                                    ,p_rate_matrix_node_id => l_rmn_id
                                    ,p_short_code => rmn_rec.INFORMATION12
                                    ,p_pl_id => p_plan_id
                                    ,p_level_number => rmn_rec.Information160
                                    ,p_criteria_short_code => rmn_rec.INFORMATION13
                                    ,p_node_name => rmn_rec.Information219
                                    ,p_parent_node_id => l_parent_rmn_id
                                    ,p_eligy_prfl_id => rmn_rec.Information169
                                    ,p_business_group_id => rmn_rec.Information4
                                    ,p_object_version_number  => l_ovn
                                    );

Line: 1307

   hr_utility.set_location('Rate_matrix_rates row updated',40);

Line: 1351

   hr_utility.set_location('plan row checked for update',30);