APPS.PAY_BALANCE_FEED_DEL_PKG SQL Statements

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

Line: 20

select rowid,
       balance_type_id,
       input_value_id,
       effective_start_date
from PAY_BALANCE_FEEDS_F
where BALANCE_FEED_ID=c_bal_feed_id;

Line: 28

select rowid
from pay_sub_classification_rules_f
where SUB_CLASSIFICATION_RULE_ID = c_sub_class_id;

Line: 37

     select bf.rowid row_id,
            bf.balance_type_id,
            bf.input_value_id,
            bf.effective_start_date
     from   pay_sub_classification_rules_f scr,
            pay_input_values_f iv,
            pay_balance_feeds_f bf,
            pay_balance_classifications bc,
            pay_balance_types bt
     where  scr.sub_classification_rule_id = p_sub_classification_rule_id
       and  iv.element_type_id = scr.element_type_id
       and  iv.name = p_pay_value_name
       and  bc.classification_id = scr.classification_id
       and  bt.balance_type_id = bc.balance_type_id
       and  bf.balance_type_id = bt.balance_type_id
       and  bf.input_value_id = iv.input_value_id
       and  bf.effective_start_date = scr.effective_start_date
       and  bf.effective_end_date   = scr.effective_end_date
       and  scr.effective_start_date between iv.effective_start_date
                                         and iv.effective_end_date
     for update;

Line: 84

    before delete from pay_balance_feeds_f.

*/

            If bal_type_id is not null then
               hr_balance_feeds.lock_balance_type(bal_type_id);

Line: 99

            Delete from pay_balance_feeds_f
            where  rowid = bal_rowid;

Line: 121

                       delete from pay_balance_feeds_f bf
                       where  bf.rowid = v_bf_rec.row_id;

Line: 127

              delete from pay_sub_classification_rules_f
              where   rowid   = sub_rowid;

Line: 138

            delete from hr_application_ownerships
            where key_name = 'SUB_CLASSIFICATION_RULE_ID'
            and key_value = c_sub_class_id;

Line: 172

   select /*+ ORDERED INDEX (PLB PAY_PERSON_LATEST_BALANCES_FK1)
              USE_NL (PLB) */
          plb.latest_balance_id
   from   pay_defined_balances       pdb,
          pay_person_latest_balances plb
   where  pdb.balance_type_id      = l_balance_type_id
   and    plb.defined_balance_id   = pdb.defined_balance_id
   and    exists (
          select null
          from   pay_run_results       prr,
                 pay_run_result_values rrv
          where  rrv.input_value_id  = l_input_value_id
          and    prr.run_result_id   = rrv.run_result_id
          and    prr.status          in ('P', 'PA')
          and    nvl(rrv.result_value, '0') <> '0');

Line: 189

   select
          lb.latest_balance_id
   from   pay_defined_balances       pdb,
          pay_latest_balances lb
   where  pdb.balance_type_id      = l_balance_type_id
   and    lb.defined_balance_id   = pdb.defined_balance_id
   and    exists (
          select null
          from   pay_run_results       prr,
                 pay_run_result_values rrv
          where  rrv.input_value_id  = l_input_value_id
          and    prr.run_result_id   = rrv.run_result_id
          and    prr.status          in ('P', 'PA')
          and    nvl(rrv.result_value, '0') <> '0');

Line: 207

   select /*+ ORDERED INDEX (PLB PAY_PERSON_LATEST_BALANCES_FK1)
              USE_NL (PLB) */
          plb.latest_balance_id
   from   pay_defined_balances       pdb,
          pay_person_latest_balances plb
   where  pdb.balance_type_id      = l_balance_type_id
   and    plb.defined_balance_id   = pdb.defined_balance_id;

Line: 219

   select /*+ ORDERED INDEX (PLB PAY_LATEST_BALANCES_FK1)
              USE_NL (PLB) */
          plb.latest_balance_id
   from   pay_defined_balances           pdb,
          pay_latest_balances            plb
   where  pdb.balance_type_id      = l_balance_type_id
   and    plb.defined_balance_id   = pdb.defined_balance_id;

Line: 229

   select '1' from dual
    where exists (select 1
     from pay_run_results prr,
          pay_run_result_values rrv
    where rrv.input_value_id = l_input_value_id
      and prr.run_result_id  = rrv.run_result_id
      and prr.status         in ('P', 'PA')
      and nvl(rrv.result_value, '0') <> '0');

Line: 241

   select plb.latest_balance_id,
          'P' balance_type
     from pay_person_latest_balances plb,
          pay_defined_balances pdb,
          pay_balance_dimensions pbd
    where pdb.balance_type_id = l_balance_type_id
      and pdb.defined_balance_id = plb.defined_balance_id
      and pdb.balance_dimension_id = pbd.balance_dimension_id
      and pbd.feed_checking_type = 'F'
    union
    select plb.latest_balance_id,
           'B' balance_type
      from pay_latest_balances plb,
           pay_defined_balances pdb,
           pay_balance_dimensions pbd
     where pdb.balance_type_id = l_balance_type_id
       and pdb.defined_balance_id = plb.defined_balance_id
       and pdb.balance_dimension_id = pbd.balance_dimension_id
       and pbd.feed_checking_type = 'F';

Line: 282

         select parameter_value
         into   g_lat_bal_check_mode
         from   pay_action_parameters
         where  parameter_name = 'LAT_BAL_CHECK_MODE';

Line: 321

        delete from pay_balance_context_values BCV
        where  BCV.latest_balance_id = plbcrec.latest_balance_id;

Line: 324

        delete from pay_person_latest_balances PLB
        where  PLB.latest_balance_id = plbcrec.latest_balance_id;

Line: 337

       delete from pay_balance_context_values BCV
        where  BCV.latest_balance_id = albcrec.latest_balance_id;

Line: 340

        delete from pay_assignment_latest_balances ALB
        where  ALB.latest_balance_id = albcrec.latest_balance_id;

Line: 344

        Delete_Proc_PAY_MGR (X_errbuf  => X_errbuf,
                             X_retcode => X_retcode,
                             p_cursor  => 'alatbalc',
                             p_balance_type_id => to_char(l_balance_type_id),
                             X_batch_size => l_batch_size);

Line: 354

        delete from pay_latest_balances LB
        where  LB.latest_balance_id = lbcrec.latest_balance_id;

Line: 386

         delete from pay_balance_context_values BCV
         where  BCV.latest_balance_id = plbcrec.latest_balance_id;

Line: 389

         delete from pay_person_latest_balances PLB
         where  PLB.latest_balance_id = plbcrec.latest_balance_id;

Line: 414

          Delete_Proc_PAY_MGR (X_errbuf  => X_errbuf,
                               X_retcode => X_retcode,
                               p_cursor  => 'alatbalc',
                               p_balance_type_id => to_char(l_balance_type_id),
                               X_batch_size => l_batch_size);

Line: 440

         delete from pay_latest_balances ALB
         where  ALB.latest_balance_id = lbcrec.latest_balance_id;

Line: 455

      delete from pay_balance_context_values BCV
      where  BCV.latest_balance_id = plbcrec.latest_balance_id;

Line: 458

      delete from pay_person_latest_balances PLB
      where  PLB.latest_balance_id = plbcrec.latest_balance_id;

Line: 468

        Delete_Proc_PAY_MGR (X_errbuf  => X_errbuf,
                             X_retcode => X_retcode,
                             p_cursor  => 'albc',
                             p_balance_type_id => to_char(l_balance_type_id),
                             p_input_value_id  => to_char(l_input_value_id),
                             X_batch_size => l_batch_size);

Line: 481

      delete from pay_latest_balances ALB
      where  ALB.latest_balance_id = lbcrec.latest_balance_id;

Line: 497

     delete from pay_balance_context_values BCV
      where  BCV.latest_balance_id = plrec.latest_balance_id;

Line: 501

       delete from pay_person_latest_balances PLB
       where  PLB.latest_balance_id = plrec.latest_balance_id;

Line: 504

       delete from pay_latest_balances PLB
       where  PLB.latest_balance_id = plrec.latest_balance_id;

Line: 510

                Delete_Proc_PAY_MGR (X_errbuf  => X_errbuf,
                             X_retcode => X_retcode,
                             p_cursor  => 'pl_feed_chk_a',
                             p_balance_type_id => to_char(l_balance_type_id),
                             X_batch_size => l_batch_size);

Line: 532

PROCEDURE Delete_Proc_PAY_MGR (
               X_errbuf         out NOCOPY varchar2,
               X_retcode        out NOCOPY varchar2,
               p_cursor          in varchar2,
               p_balance_type_id in varchar2,
               p_input_value_id  in  varchar2 default null,
               X_batch_size      in  number default 1000,
               X_Num_Workers     in  number default 5)
IS

  l_module       CONSTANT VARCHAR2(90) := 'PAY_BALANCE_FEED_DEL_PKG.Delete_Proc_PAY_MGR';

Line: 545

  l_api_name     CONSTANT VARCHAR2(30)   := 'Delete_Proc_PAY_MGR';

Line: 731

END Delete_Proc_PAY_MGR;

Line: 738

PROCEDURE Delete_Proc_PAY_WKR (
               X_errbuf     out NOCOPY varchar2,
               X_retcode    out NOCOPY varchar2,
               X_batch_size  in number,
               X_Worker_Id   in number,
               X_Num_Workers in number,
               X_Argument4   in varchar2 default null,
               X_Argument5   in varchar2 default null,
               X_Argument6   in varchar2 default null,
               X_Argument7   in varchar2 default null,
               X_Argument8   in varchar2 default null,
               X_Argument9   in varchar2 default null,
               X_Argument10  in varchar2 default null)
IS

  l_module       CONSTANT VARCHAR2(90) := 'PAY_BALANCE_FEED_DEL_PKG.Delete_Proc_PAY_WKR';

Line: 758

  l_update_name     varchar2(30);

Line: 771

  update_exception exception;

Line: 788

    l_update_name := 'PAYSCD' ||X_Argument7;

Line: 793

    ad_parallel_updates_pkg.initialize_id_range(
                 X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
                 X_owner=>l_table_owner,
                 X_table=>l_table_name,
                 X_script=>l_update_name,
                 X_ID_column=>l_id_column,
                 X_worker_id=>X_Worker_Id,
                 X_num_workers=>X_num_workers,
                 X_batch_size=>X_batch_size,
                 X_debug_level=>0);

Line: 805

    ad_parallel_updates_pkg.get_id_range(
           l_start_id,
           l_end_id,
           l_any_rows_to_process,
           X_batch_size,
           TRUE);

Line: 817

         fnd_file.put_line(fnd_file.log,'Before calling Update_Payroll_Subledger');

Line: 822

      PAY_BALANCE_FEED_DEL_PKG.Delete_assnmnt_lat_bal(
                  X_errbuf=>X_errbuf,
                  X_retcode=>X_retcode,
                  x_assnmnt_start_id=>l_start_id,
                  x_assnmnt_end_id=>l_end_id,
                  p_cursor=>X_Argument4,
                  p_balance_type_id=>X_Argument5,
                  P_input_value_id=>X_Argument6);

Line: 832

         fnd_file.put_line(fnd_file.log,'After calling Update_Payroll_Subledger');

Line: 836

          raise update_exception;

Line: 843

      ad_parallel_updates_pkg.processed_id_range(
          l_rows_processed,
          l_end_id);

Line: 850

      ad_parallel_updates_pkg.get_id_range(
         l_start_id,
         l_end_id,
         l_any_rows_to_process,
         X_batch_size,
         FALSE);

Line: 873

WHEN update_exception THEN
    ROLLBACK;

Line: 896

END Delete_Proc_PAY_WKR;

Line: 918

PROCEDURE Delete_assnmnt_lat_bal(
               x_errbuf             out nocopy varchar2,
               x_retcode            out nocopy varchar2,
               x_assnmnt_start_id           in number,
               x_assnmnt_end_id             in number,
               p_cursor             in varchar2,
               p_balance_type_id    in varchar2,
               p_input_value_id     in varchar2
)
IS

   -- Select all assignment latest balances to delete.
   cursor albc(p_assnmnt_start_id in varchar2,
               p_assnmnt_end_id   in varchar2,
               p_balance_type_id  in varchar2,
               p_input_value_id   in varchar2) is
   select /*+ ORDERED INDEX (PLB PAY_ASSIGNMENT_LATEST_BALA_FK2)
              USE_NL (PLB) */
          plb.latest_balance_id
   from   pay_defined_balances           pdb,
          pay_assignment_latest_balances plb
   where  pdb.balance_type_id      = p_balance_type_id
   and    plb.defined_balance_id   = pdb.defined_balance_id
   and    plb.assignment_id between p_assnmnt_start_id and p_assnmnt_end_id
   and    exists (
          select null
          from   pay_run_results       prr,
                 pay_run_result_values rrv
          where  rrv.input_value_id  = p_input_value_id
          and    prr.run_result_id   = rrv.run_result_id
          and    prr.status          in ('P', 'PA')
          and    nvl(rrv.result_value, '0') <> '0');

Line: 955

   select /*+ ORDERED INDEX (PLB PAY_ASSIGNMENT_LATEST_BALA_FK2)
              USE_NL (PLB) */
          plb.latest_balance_id
   from   pay_defined_balances           pdb,
          pay_assignment_latest_balances plb
   where  pdb.balance_type_id      = p_balance_type_id
   and    plb.defined_balance_id   = pdb.defined_balance_id
   and    plb.assignment_id between p_assnmnt_start_id and p_assnmnt_end_id;

Line: 968

   select alb.latest_balance_id
     from pay_assignment_latest_balances alb,
          pay_defined_balances pdb,
          pay_balance_dimensions pbd
    where pdb.balance_type_id = p_balance_type_id
      and pdb.defined_balance_id = alb.defined_balance_id
      and pdb.balance_dimension_id = pbd.balance_dimension_id
      and pbd.feed_checking_type = 'F'
      and alb.assignment_id between p_assnmnt_start_id and p_assnmnt_end_id;

Line: 978

   l_module            CONSTANT VARCHAR2(90) := 'PAY_BALANCE_FEED_DEL_PKG.Delete_assnmnt_lat_bal';

Line: 1012

             delete from pay_balance_context_values BCV
             where  BCV.latest_balance_id = lat_bal_list(i);

Line: 1016

             delete from pay_assignment_latest_balances ALB
             where  ALB.latest_balance_id =lat_bal_list(i);

Line: 1021

      lat_bal_list.delete;

Line: 1032

             delete from pay_balance_context_values BCV
             where  BCV.latest_balance_id = lat_bal_list(i);

Line: 1036

             delete from pay_assignment_latest_balances ALB
             where  ALB.latest_balance_id =lat_bal_list(i);

Line: 1042

      lat_bal_list.delete;

Line: 1055

            delete from pay_balance_context_values bcv
            where  bcv.latest_balance_id = lat_bal_list(i);

Line: 1059

            delete from pay_assignment_latest_balances alb
            where  alb.latest_balance_id =lat_bal_list(i);

Line: 1065

      lat_bal_list.delete;

Line: 1084

    fnd_file.put_line(FND_FILE.LOG,'Error Update_Payroll_Subledger '||SQLCODE||'  '||SQLERRM);

Line: 1085

end Delete_assnmnt_lat_bal;