DBA Data[Home] [Help]

APPS.PAY_RUN_BALANCE_BUILD SQL Statements

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

Line: 34

    l_update boolean;
Line: 38

  l_update := FALSE;
Line: 41

       l_update := TRUE;
Line: 44

    l_update := TRUE;
Line: 47

  if (l_update = TRUE) then
     begin
--
       select balance_validation_id
         into l_bal_valid_id
         from pay_balance_validation
        where defined_balance_id = p_defined_balace_id
          and business_group_id = p_business_group_id;
Line: 56

        update pay_balance_validation
           set run_balance_status = p_status,
               balance_load_date = g_start_date
         where balance_validation_id = l_bal_valid_id;
Line: 64

       insert into pay_balance_validation
                      (balance_validation_id,
                       defined_balance_id,
                       business_group_id,
                       run_balance_status,
                       balance_load_date)
       values ( pay_balance_validation_s.nextval,
                p_defined_balace_id,
                p_business_group_id,
                p_status,
                g_start_date);
Line: 92

    select pdb.defined_balance_id,
           ppa.business_group_id,
           nvl(pay_core_utils.get_parameter('BAL_LVL',
                                            ppa.legislative_parameters),
               'BOTH') balance_level,
           pbd.dimension_level
      from pay_payroll_actions    ppa,
           per_business_groups    pbg,
           pay_defined_balances   pdb,
           pay_balance_dimensions pbd
     where ppa.payroll_action_id = p_pact_id
       and ppa.business_group_id = pbg.business_group_id
       and ((pdb.business_group_id = pbg.business_group_id
             and pdb.legislation_code is null)
           or
            (pdb.legislation_code = pbg.legislation_code
             and pdb.business_group_id is null)
           or
            (pdb.legislation_code is null
             and pdb.business_group_id is null)
          )
       and pdb.save_run_balance = 'Y'
       and pdb.balance_dimension_id = pbd.balance_dimension_id
       and ((pbd.dimension_level =
                  nvl(pay_core_utils.get_parameter('BAL_LVL',
                                                   ppa.legislative_parameters),
                      'BOTH'))
           or
            (nvl(pay_core_utils.get_parameter('BAL_LVL',
                                               ppa.legislative_parameters),
                 'BOTH')
                            = 'BOTH')
           );
Line: 146

           select nvl(pbv.run_balance_status, 'I')
             into l_run_bal_stat
             from pay_defined_balances pdb,
                  pay_balance_validation pbv
            where pdb.defined_balance_id = dbrec.defined_balance_id
              and pbv.defined_balance_id (+) = pdb.defined_balance_id
              and pbv.business_group_id (+) = dbrec.business_group_id;
Line: 173

              update pay_balance_validation
                 set balance_load_date = g_start_date
               where dbrec.defined_balance_id = defined_balance_id
                 and dbrec.business_group_id = business_group_id;
Line: 187

    select business_group_id
      into l_bus_grp
      from pay_payroll_actions
     where payroll_action_id = p_pactid;
Line: 194

      select nvl(pbv.run_balance_status, 'I')
        into l_run_bal_stat
        from pay_defined_balances pdb,
             pay_balance_validation pbv
       where pdb.defined_balance_id = g_def_bal_id
         and pbv.defined_balance_id (+) = pdb.defined_balance_id
         and pbv.business_group_id (+) = l_bus_grp;
Line: 211

         update pay_balance_validation
            set balance_load_date = g_start_date
          where defined_balance_id = g_def_bal_id
            and business_group_id = l_bus_grp;
Line: 233

      select
             pay_core_utils.get_parameter('DEF_BAL_ID',
                                          pa1.legislative_parameters),
             nvl(pay_core_utils.get_parameter('BAL_LVL',
                                          pa1.legislative_parameters),
                 'BOTH'),
             nvl(pay_core_utils.get_parameter('PROC_MODE',
                                          pa1.legislative_parameters),
                 'ALL'),
             to_date(pay_core_utils.get_parameter('BAL_START_DATE',
                                          pa1.legislative_parameters),
                     'YYYY/MM/DD'),
             nvl(pay_core_utils.get_parameter('LOAD_TYPE',
                                          pa1.legislative_parameters),
                 'ALL'),
             pbg.legislation_code,
             pbg.business_group_id,
             'GEN_BAL_'||p_pact_id
        into
             g_def_bal_id,
             g_bal_lvl,
             g_proc_mode,
             g_start_date,
             g_load_type,
             g_leg_code,
             g_bus_grp,
             g_att_name
        from pay_payroll_actions    pa1,
             per_business_groups    pbg
       where pa1.payroll_action_id    = p_pact_id
         and pa1.business_group_id    = pbg.business_group_id;
Line: 266

        select rule_mode
          into g_save_run_bals
          from pay_legislation_rules
         where legislation_code = g_leg_code
           and rule_type = 'SAVE_RUN_BAL';
Line: 277

        select rule_mode
          into g_save_asg_run_bals
          from pay_legislation_rules
         where legislation_code = g_leg_code
           and rule_type = 'SAVE_ASG_RUN_BAL';
Line: 291

        select nvl(pbd.dimension_level, 'ASG')
          into g_bal_lvl
          from pay_defined_balances   pdb,
               pay_balance_dimensions pbd
         where pdb.defined_balance_id = g_def_bal_id
           and pdb.balance_dimension_id = pbd.balance_dimension_id;
Line: 321

    l_delete_bals  boolean;
Line: 335

     l_delete_bals := FALSE;
Line: 338

         l_delete_bals := TRUE;
Line: 341

     if (l_delete_bals) then
--
       delete /*+ INDEX(prb PAY_RUN_BALANCES_N2) */
         from pay_run_balances prb
        where prb.defined_balance_id = g_def_bal_id
          and prb.assignment_action_id = p_asg_act_id;
Line: 373

     l_delete_bals := FALSE;
Line: 376

         l_delete_bals := TRUE;
Line: 379

     if (l_delete_bals) then
       null;
Line: 414

    l_delete_bals boolean;
Line: 429

     l_delete_bals := FALSE;
Line: 432

         l_delete_bals := TRUE;
Line: 435

     if (l_delete_bals) then
--
     --
     hr_utility.set_location(
               'pay_run_balance_build.calculate_full_asg_balances',30);
Line: 442

       delete /*+ INDEX(prb PAY_RUN_BALANCES_N2) */
         from pay_run_balances prb
        where prb.defined_balance_id = g_def_bal_id
          and prb.assignment_action_id = p_asg_act_id;
Line: 468

     l_delete_bals := FALSE;
Line: 471

         l_delete_bals := TRUE;
Line: 474

     if (l_delete_bals) then
     --
       hr_utility.set_location(
                 'pay_run_balance_build.calculate_full_asg_balances',60);
Line: 510

    select /*+ ORDERED USE_NL(ppa prt)
           INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
           INDEX(prt PAY_RUN_TYPES_PK) */
           paa.assignment_action_id,
           ppa.effective_date,
           nvl(prt.run_method, 'N') run_method,
           ppa.business_group_id,
           pbg.legislation_code
      from pay_assignment_actions      paa,
           pay_payroll_actions         ppa,
           pay_run_types_f             prt,
           per_business_groups_perf    pbg
     where paa.assignment_id = p_assid
       and paa.payroll_action_id = ppa.payroll_action_id
       and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
       and ppa.business_group_id = pbg.business_group_id
       and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
       and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
                                  and nvl(prt.effective_end_date, ppa.effective_date)
    order by 2;
Line: 548

       delete /*+ USE_NL(prb) INDEX(prb PAY_RUN_BALANCES_N1) */
         from pay_run_balances prb
       where  prb.assignment_id = p_asg_id
       and    prb.defined_balance_id in
                              (select pba.defined_balance_id
                               from   pay_balance_attributes pba
                               ,      pay_bal_attribute_definitions bad
                               where  pba.attribute_id = bad.attribute_id
                               and    bad.attribute_name = g_att_name);
Line: 564

           delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
             from pay_run_balances prb
            where prb.assignment_id = p_asg_id;
Line: 570

           delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
             from pay_run_balances prb
            where prb.assignment_id = p_asg_id
              and exists (select ''
                            from pay_balance_validation pbv
                           where pbv.defined_balance_id = prb.defined_balance_id
                             and pbv.business_group_id = p_bus_grp
                             and prb.effective_date < greatest(nvl(pbv.balance_load_date,
                                                               to_date('0001/01/01 00:00:00',
                                                                       'YYYY/MM/DD HH24:MI:SS')
                                                             ),
                                                           nvl(g_start_date,
                                                               to_date('0001/01/01 00:00:00',
                                                                       'YYYY/MM/DD HH24:MI:SS')
                                                             )
                                                          )
                          );
Line: 599

         select balance_load_date,
                run_balance_status
           into l_bal_load_date,
                l_run_bal_status
           from pay_balance_validation
          where defined_balance_id = g_def_bal_id
            and business_group_id = p_bus_grp;
Line: 678

    l_delete_bals  boolean;
Line: 692

        l_delete_bals := FALSE;
Line: 695

            l_delete_bals := TRUE;
Line: 698

        if (l_delete_bals) then
--
          delete from pay_run_balances
           where defined_balance_id = g_def_bal_id
             and payroll_action_id  = p_pactid;
Line: 730

        l_delete_bals := FALSE;
Line: 733

            l_delete_bals := TRUE;
Line: 736

        if (l_delete_bals) then
--
          if (g_proc_mode = 'INVALID') then
--
            delete from pay_run_balances prb
             where prb.payroll_action_id  = p_pactid
               and exists (select ''
                             from pay_balance_validation pbv
                            where pbv.defined_balance_id =
                                           prb.defined_balance_id
                              and pbv.run_balance_status = 'P'
                              and pbv.business_group_id = p_bus_grp);
Line: 754

              delete from pay_run_balances
               where payroll_action_id  = p_pactid;
Line: 757

              delete
                from pay_run_balances prb
               where prb.payroll_action_id  = p_pactid
                 and g_start_date is not null
                 and g_start_date > p_eff_date;
Line: 802

    l_delete_bals boolean;
Line: 809

        l_delete_bals := FALSE;
Line: 812

            l_delete_bals := TRUE;
Line: 815

        if (l_delete_bals) then
--
          delete from pay_run_balances
           where defined_balance_id = g_def_bal_id
             and payroll_action_id  = p_pactid;
Line: 834

        l_delete_bals := FALSE;
Line: 837

            l_delete_bals := TRUE;
Line: 840

        if (l_delete_bals) then
--
          if (g_proc_mode = 'INVALID') then
--
            delete from pay_run_balances prb
             where prb.payroll_action_id  = p_pactid
               and exists (select ''
                             from pay_balance_validation pbv
                            where pbv.defined_balance_id = prb.defined_balance_id
                              and pbv.run_balance_status = 'P'
                              and pbv.business_group_id = p_bus_grp);
Line: 855

            delete from pay_run_balances
             where payroll_action_id  = p_pactid;
Line: 886

   select ppa.effective_date,
          ppa.business_group_id,
          pbg.legislation_code
     into l_grp_eff_date,
          l_bus_grp,
          l_leg_code
     from pay_payroll_actions ppa,
          per_business_groups pbg
    where ppa.payroll_action_id = p_pactid
      and ppa.business_group_id = pbg.business_group_id;
Line: 911

          delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
            from pay_run_balances prb
           where prb.payroll_action_id = p_pactid
             and exists (select /*+ INDEX(pbv PAY_BALANCE_VALIDATION_UK1) */ ''
                           from pay_balance_validation pbv
                          where pbv.defined_balance_id = prb.defined_balance_id
                            and pbv.run_balance_status = 'P'
                            and pbv.business_group_id = l_bus_grp);
Line: 926

             delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
               from pay_run_balances prb
              where prb.payroll_action_id = p_pactid;
Line: 932

             delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
               from pay_run_balances prb
              where prb.payroll_action_id = p_pactid
                and exists (select ''
                              from pay_balance_validation pbv
                             where pbv.defined_balance_id = prb.defined_balance_id
                               and pbv.business_group_id = l_bus_grp
                               and prb.effective_date < greatest(nvl(pbv.balance_load_date,
                                                                 to_date('0001/01/01 00:00:00',
                                                                         'YYYY/MM/DD HH24:MI:SS')
                                                               ),
                                                             nvl(g_start_date,
                                                                 to_date('0001/01/01 00:00:00',
                                                                         'YYYY/MM/DD HH24:MI:SS')
                                                               )
                                                            )
                            );
Line: 959

          select balance_load_date,
                 run_balance_status
            into l_bal_load_date,
                 l_run_bal_status
            from pay_balance_validation
           where defined_balance_id = g_def_bal_id
             and business_group_id = l_bus_grp;
Line: 1011

    select paa.assignment_action_id,
           ppa.effective_date,
           nvl(prt.run_method, 'N') run_method,
           ppa.business_group_id
      from pay_assignment_actions paa,
           pay_payroll_actions    ppa,
           pay_run_types_f        prt,
           pay_assignment_actions paa_arch
     where paa_arch.assignment_action_id = p_assactid
       and paa_arch.assignment_id = paa.assignment_id
       and paa.payroll_action_id = ppa.payroll_action_id
       and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
       and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
       and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
                                  and nvl(prt.effective_end_date, ppa.effective_date)
    order by 2;
Line: 1032

    l_delete_bals boolean;
Line: 1041

    select paa.payroll_action_id,
           paa.object_id,
           paa.assignment_id,
           ppa.business_group_id
      into l_payroll_action_id, l_object_id, l_asg_id, l_bus_grp
      from pay_assignment_actions paa,
           pay_payroll_actions    ppa
     where paa.assignment_action_id = p_assactid
       and ppa.payroll_action_id = paa.payroll_action_id;
Line: 1058

    select sysdate
      into current_date
      from sys.dual;
Line: 1138

     select
            pdb.defined_balance_id
       from
            pay_balance_types      pbt,
            pay_defined_balances   pdb,
            pay_balance_dimensions pbd
      where pbd.dimension_level = 'GRP'
        and pdb.save_run_balance = 'Y'
        and pdb.balance_dimension_id = pbd.balance_dimension_id
        and pdb.balance_type_id = pbt.balance_type_id
        and ((pdb.business_group_id = p_bus_grp_id
               and pdb.legislation_code is null)
             or
              (pdb.legislation_code = p_leg_code
               and pdb.business_group_id is null)
             or
              (pdb.legislation_code is null
               and pdb.business_group_id is null)
            )
        and (    p_bal_list <> 'INVALID'
              or (    p_bal_list = 'INVALID'
                  and exists (select ''
                                from pay_balance_validation pbv
                               where pbv.defined_balance_id = pdb.defined_balance_id
                                 and pbv.business_group_id = p_bus_grp_id
                                 and pbv.run_balance_status = 'P')
                 )
            )
        and (   p_def_bal is null
             or p_def_bal = pdb.defined_balance_id
            );
Line: 1175

     select
            pdb.defined_balance_id
       from
            pay_balance_types    pbt,
            pay_defined_balances pdb,
            pay_balance_dimensions pbd
      where pbd.dimension_level = 'ASG'
        and pdb.save_run_balance = 'Y'
        and pdb.balance_dimension_id = pbd.balance_dimension_id
        and pdb.balance_type_id = pbt.balance_type_id
        and (    p_bal_list <> 'INVALID'
              or (    p_bal_list = 'INVALID'
                  and exists (select ''
                                from pay_balance_validation pbv
                               where pbv.defined_balance_id = pdb.defined_balance_id
                                 and pbv.business_group_id = p_bus_grp_id
                                 and pbv.run_balance_status = 'P')
                 )
            )
        and (   p_def_bal is null
             or p_def_bal = pdb.defined_balance_id
            )
        and ((pdb.business_group_id = p_bus_grp_id
               and pdb.legislation_code is null)
             or
              (pdb.legislation_code = p_leg_code
               and pdb.business_group_id is null)
             or
              (pdb.legislation_code is null
               and pdb.business_group_id is null)
            );
Line: 1215

         select pay_bal_attribute_definition_s.nextval
           into l_attribute_id
           from dual;
Line: 1219

         insert into pay_bal_attribute_definitions
           (attribute_id,
            attribute_name,
            alterable,
            business_group_id)
         values (l_attribute_id,
                 l_attribute_name,
                 'N',
                 g_bus_grp);
Line: 1237

            insert into pay_balance_attributes
               (balance_attribute_id,
                attribute_id,
                defined_balance_id
               )
            values
               (pay_balance_attributes_s.nextval,
                l_attribute_id,
                grprec.defined_balance_id
               );
Line: 1260

            insert into pay_balance_attributes
               (balance_attribute_id,
                attribute_id,
                defined_balance_id
               )
            values
               (pay_balance_attributes_s.nextval,
                l_attribute_id,
                asgrec.defined_balance_id
               );
Line: 1279

     Purpose   : This returns the select statement that is used to created the
                 range rows for the Tax Filing (FLS) Archiver.
     Arguments :
     Notes     :
  */

  PROCEDURE action_range_cursor( p_payroll_action_id in number
                              ,p_sqlstr           out nocopy varchar2)
  IS
--
    lv_sql_string  VARCHAR2(32000);
Line: 1297

'select 1
   from dual
  where 1 = 0
    and 1 = :payroll_action_id';
Line: 1306

'select ppa_r.payroll_action_id
   from pay_payroll_actions    ppa,
        pay_payroll_actions    ppa_r
  where ppa.payroll_action_id = :payroll_action_id
    and ppa_r.action_type in (''R'',''Q'',''B'', ''I'',''V'')
    and ppa.business_group_id = ppa_r.business_group_id
order by ppa_r.payroll_action_id';
Line: 1317

'select distinct asg.person_id
   from
        per_periods_of_service pos,
        per_assignments_f      asg,
        pay_payroll_actions    ppa
  where ppa.payroll_action_id = :payroll_action_id
    and pos.person_id         = asg.person_id
    and pos.period_of_service_id = asg.period_of_service_id
    and pos.business_group_id = ppa.business_group_id
    and asg.business_group_id = ppa.business_group_id
order by asg.person_id';
Line: 1360

      select ppa.payroll_action_id
      from pay_payroll_actions ppa,
           pay_payroll_actions ppa_arc
      where  ppa.payroll_action_id between stpactid and enpactid
        and  ppa_arc.payroll_action_id = pact_id
        and  ppa.business_group_id = ppa_arc.business_group_id
        and  ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
      order by 1;
Line: 1373

      select distinct
             paf.assignment_id assignment_id,
             paf.person_id
      from
             per_periods_of_service     pos,
             per_all_assignments_f      paf,
             pay_payroll_actions        ppa
      where pos.person_id between stperson and endperson
        and pos.person_id         = paf.person_id
        and pos.period_of_service_id = paf.period_of_service_id
        and pos.business_group_id = ppa.business_group_id
        and  ppa.payroll_action_id = pact_id
        and  ppa.business_group_id = paf.business_group_id
      order by 1, 2;
Line: 1402

           select pay_assignment_actions_s.nextval
             into   ln_lockingactid
             from   dual;
Line: 1425

           select pay_assignment_actions_s.nextval
             into   ln_lockingactid
             from   dual;
Line: 1461

         select to_number(parameter_value),
                sysdate
           into l_timeout_sec,
                current_time
           from pay_action_parameters
          where parameter_name = 'PROCESS_TIMEOUT';
Line: 1494

    select ppa.payroll_action_id ,
           ppa.action_sequence,
           ppa.effective_date,
           ppa.business_group_id
      from
           pay_payroll_actions    ppa_arch,
           pay_payroll_actions    ppa
     where ppa_arch.payroll_action_id = p_pact_id
       and ppa_arch.business_group_id = ppa.business_group_id
       and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
    order by 2;
Line: 1510

  l_delete_bals boolean;
Line: 1548

        select count(*)
          into act_com
          from pay_assignment_actions
         where payroll_action_id = pactid
           and action_status <> 'C';
Line: 1560

          select pay_core_utils.get_parameter('REMOVE_ACT',
                                              pa1.legislative_parameters)
            into remove_act
            from pay_payroll_actions    pa1
           where pa1.payroll_action_id    = pactid;
Line: 1570

             delete from pay_balance_attributes
              where attribute_id in (select attribute_id
                                       from pay_bal_attribute_definitions
                                      where attribute_name = g_att_name
                                     );
Line: 1575

             delete from pay_bal_attribute_definitions
              where attribute_name = g_att_name;