APPS.PSP_ER_AME SQL Statements

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

Line: 4

procedure insert_error(p_request_id    in integer,
                       p_message_level in varchar2,
                       p_source_id     in integer,
                       p_err_mesg      in varchar2,
                       p_retry_request_id in integer) is
 l_return_status varchar2(1);

Line: 10

 failed_insertion exception;

Line: 20

    fnd_msg_pub.add_exc_msg('PSP_ER_AME','INSERT_ERROR');

Line: 21

    raise failed_insertion;

Line: 96

  select decode(approval_type,'PRE','N','Y') workflow_approval_req_flag,
         approval_type,
         custom_approval_code,
         sup_levels
  from psp_report_templates_h
  where request_id = p_request_id;

Line: 104

  select er.person_id
    from psp_eff_report_details erd,
         psp_eff_reports er
   where erd.project_id is null
     and erd.effort_Report_id  = er.effort_report_id
     and er.request_id = p_request_id;

Line: 112

  select substr(report_Template_code,6,3)
    from psp_report_templates_h
   where request_id = p_request_id;

Line: 120

  select er.person_id
    from psp_eff_report_details erd,
         psp_eff_reports er
   where erd.award_id is null
     and erd.effort_Report_id  = er.effort_report_id
     and er.request_id = p_request_id;

Line: 129

                      select min(erd.effort_report_detail_id),
                             erd.investigator_person_id,
                            null
                     from psp_eff_reports er,
                          psp_eff_report_details erd
                     where er.effort_report_id = erd.effort_report_id
                       and er.status_code = 'N'
                       and er.request_id =  p_request_id
                       and er.person_id between p_start_person  and  p_end_person
                      group by erd.investigator_person_id;

Line: 142

  cursor er_cur_emp is select min(effort_report_detail_id),
                            person_id,
                            null
                     from psp_eff_reports er,
                          psp_eff_report_details erd
                     where er.effort_report_id = erd.effort_report_id
                       and er.status_code = 'N'
                       and er.request_id =  p_request_id
                       and er.person_id between p_start_person  and  p_end_person
                      group by person_id;

Line: 154

  cursor er_cur_sup_1 is select min(erd.effort_report_detail_id),
                            min(er.person_id),
                            asg.supervisor_id
                       from per_assignments_f asg,
                            psp_eff_reports er,
                            psp_eff_report_details erd
                      where asg.person_id = er.person_id
                        and er.status_code = 'N'
                        and asg.assignment_type ='E'
                        and trunc(er.end_date) between asg.effective_start_date and
                                                       asg.effective_end_date
                        and asg.primary_flag = 'Y'
                        and er.effort_report_id = erd.effort_report_id
                        and er.request_id =  p_request_id
                        and er.person_id between p_start_person  and  p_end_person
                      group by asg.supervisor_id;

Line: 180

   function make_select return varchar2 is
      select_string varchar2(1000) := null;

Line: 183

     hr_utility.trace ('psperamb--> Entered make_select');

Line: 186

          select_string := ',assignment_id' ;

Line: 188

          select_string := ',null';

Line: 192

          select_string := select_string||',project_id' ;

Line: 194

          select_string := select_string||',null';

Line: 198

          select_string := select_string ||',award_id' ;

Line: 200

          select_string := select_string ||',null';

Line: 204

          select_string := select_string ||',task_id' ;

Line: 206

          select_string := select_string ||',null';

Line: 210

          select_string := select_string ||',expenditure_organization_id' ;

Line: 212

          select_string := select_string ||',null';

Line: 216

          select_string := select_string ||',expenditure_type' ;

Line: 218

          select_string := select_string ||',null';

Line: 222

          select_string := select_string ||',segment1' ;

Line: 224

          select_string := select_string ||',null';

Line: 228

          select_string := select_string ||',segment2' ;

Line: 230

          select_string := select_string ||',null';

Line: 234

          select_string := select_string ||',segment3' ;

Line: 236

          select_string := select_string ||',null';

Line: 240

          select_string := select_string ||',segment4' ;

Line: 242

          select_string := select_string ||',null';

Line: 246

          select_string := select_string ||',segment5' ;

Line: 248

          select_string := select_string ||',null';

Line: 252

          select_string := select_string ||',segment6' ;

Line: 254

          select_string := select_string ||',null';

Line: 258

          select_string := select_string ||',segment7' ;

Line: 260

          select_string := select_string ||',null';

Line: 264

          select_string := select_string ||',segment8' ;

Line: 266

          select_string := select_string ||',null';

Line: 270

          select_string := select_string ||',segment9' ;

Line: 272

          select_string := select_string ||',null';

Line: 276

          select_string := select_string ||',segment10' ;

Line: 278

          select_string := select_string ||',null';

Line: 282

          select_string := select_string ||',segment11' ;

Line: 284

          select_string := select_string ||',null';

Line: 288

          select_string := select_string ||',segment12' ;

Line: 290

          select_string := select_string ||',null';

Line: 294

          select_string := select_string ||',segment13' ;

Line: 296

          select_string := select_string ||',null';

Line: 300

          select_string := select_string ||',segment14' ;

Line: 302

          select_string := select_string ||',null';

Line: 306

          select_string := select_string ||',segment15' ;

Line: 308

          select_string := select_string ||',null';

Line: 312

          select_string := select_string ||',segment16' ;

Line: 314

          select_string := select_string ||',null';

Line: 318

          select_string := select_string ||',segment17' ;

Line: 320

          select_string := select_string ||',null';

Line: 324

          select_string := select_string ||',segment18' ;

Line: 326

          select_string := select_string ||',null';

Line: 330

          select_string := select_string ||',segment19' ;

Line: 332

          select_string := select_string ||',null';

Line: 336

          select_string := select_string ||',segment20' ;

Line: 338

          select_string := select_string ||',null';

Line: 342

          select_string := select_string ||',segment21' ;

Line: 344

          select_string := select_string ||',null';

Line: 348

          select_string := select_string ||',segment22' ;

Line: 350

          select_string := select_string ||',null';

Line: 354

          select_string := select_string ||',segment23' ;

Line: 356

          select_string := select_string ||',null';

Line: 360

          select_string := select_string ||',segment24' ;

Line: 362

          select_string := select_string ||',null';

Line: 366

          select_string := select_string ||',segment25' ;

Line: 368

          select_string := select_string ||',null';

Line: 372

          select_string := select_string ||',segment26' ;

Line: 374

          select_string := select_string ||',null';

Line: 378

          select_string := select_string ||',segment27' ;

Line: 380

          select_string := select_string ||',null';

Line: 384

          select_string := select_string ||',segment28' ;

Line: 386

          select_string := select_string ||',null';

Line: 390

          select_string := select_string ||',segment29' ;

Line: 392

          select_string := select_string ||',null';

Line: 396

          select_string := select_string ||',segment30' ;

Line: 398

          select_string := select_string ||',null';

Line: 400

     hr_utility.trace ('psperamb--> Exiting make_select, string='||select_string);

Line: 401

      return select_string;

Line: 404

        fnd_msg_pub.add_exc_msg('PSP_ER_AME','MAKE_SELECT');

Line: 782

   procedure insert_into_approvals(p_custom_approvals        in varchar2,
                                  p_approval_type           in varchar2,
                                  p_effort_report_detail_id in integer,
                                  p_break_attribute         in integer,
                                  p_break_attribute2        in integer,
                                  p_wf_role_name            in varchar2,
                                  p_wf_orig_system          in varchar2,
                                  p_wf_orig_system_id       in integer,
                                  p_er_approval_status      in varchar2,
                                  p_approver_order_number   in integer,
                                  p_ame_transaction_id      in varchar2,
                                  p_approver_display_name   in varchar2) is

     l_approval_status varchar2(1) := nvl( p_er_approval_status,'P');

Line: 801

         insert into psp_eff_report_approvals
                 (effort_report_approval_id,
                  effort_report_detail_id,
                  wf_role_name,
                  wf_orig_system_id,
                  wf_orig_system,
                  approver_order_num,
                  approval_status,
                  last_update_date,
                  last_updated_by,
                  last_update_login,
                  creation_date,
                  created_by,
                  wf_role_display_name,
                  object_version_number)
          values (psp_eff_report_approvals_s.nextval,
                  p_effort_report_detail_id,
                  p_wf_role_name,
                  p_wf_orig_system_id,
                  p_wf_orig_system,
                  p_approver_order_number,
                  l_approval_status,
                  sysdate,
                  l_user_id,
                  l_login_id,
                  sysdate,
                  l_user_id,
                  p_approver_display_name,
                  1);

Line: 832

            insert into psp_eff_report_approvals
                 (effort_report_approval_id,
                  effort_report_detail_id,
                  wf_role_name,
                  wf_orig_system,
                  wf_orig_system_id,
                  approver_order_num,
                  approval_status,
                  last_update_date,
                  last_updated_by,
                  last_update_login,
                  creation_date,
                  created_by,
                  wf_role_display_name,
                  object_version_number)
            select psp_eff_report_approvals_s.nextval,
                   effort_report_detail_id,
                   p_wf_role_name,
                   p_wf_orig_system,
                   p_wf_orig_system_id,
                   p_approver_order_number,
                   l_approval_status,
                    sysdate,
                  l_user_id,
                  l_login_id,
                  sysdate,
                  l_user_id,
                  p_approver_display_name,
                  1
              from psp_eff_report_details erd,
                   psp_eff_reports er
             where erd.effort_report_id = er.effort_report_id
               and er.request_id = p_request_id
               and nvl(investigator_person_id,-999) = nvl(p_break_attribute,-999)
               and er.person_id between p_start_person and p_end_person
               and er.status_code = 'N';

Line: 871

            hr_utility.trace('psperamb-->emp and esu insert');

Line: 873

            insert into psp_eff_report_approvals
                 (effort_report_approval_id,
                  effort_report_detail_id,
                  wf_role_name,
                  wf_orig_system,
                  wf_orig_system_id,
                  approver_order_num,
                  approval_status,
                  last_update_date,
                  last_updated_by,
                  last_update_login,
                  creation_date,
                  created_by,
                  wf_role_display_name,
                  object_version_number)
            select psp_eff_report_approvals_s.nextval,
                   effort_report_detail_id,
                   p_wf_role_name,
                   p_wf_orig_system,
                   p_wf_orig_system_id,
                   p_approver_order_number,
                   l_approval_status,
                  sysdate,
                  l_user_id,
                  l_login_id,
                  sysdate,
                  l_user_id,
                  p_approver_display_name,
                  1
              from psp_eff_report_details erd,
                   psp_eff_reports er
             where erd.effort_report_id = er.effort_report_id
               and er.request_id = p_request_id
               and nvl(er.person_id,-999) = nvl(p_break_attribute,-999)
               and er.person_id between p_start_person and p_end_person
               and er.status_code = 'N';

Line: 910

              hr_utility.trace('psperamb-->emp and esu insert -1');

Line: 913

             insert into psp_eff_report_approvals
                 (effort_report_approval_id,
                  effort_report_detail_id,
                  wf_role_name,
                  wf_orig_system,
                  wf_orig_system_id,
                  approver_order_num,
                  approval_status,
                  last_update_date,
                  last_updated_by,
                  last_update_login,
                  creation_date,
                  created_by,
                  wf_role_display_name,
                  object_version_number)
            select psp_eff_report_approvals_s.nextval,
                   effort_report_detail_id,
                   p_wf_role_name,
                   p_wf_orig_system,
                   p_wf_orig_system_id,
                   p_approver_order_number,
                   l_approval_status,
                  sysdate,
                  l_user_id,
                  l_login_id,
                  sysdate,
                  l_user_id,
                  p_approver_display_name,
                  1
              from psp_eff_report_details erd,
                   psp_eff_reports er,
                   per_all_assignments_f asg
             where erd.effort_report_id = er.effort_report_id
               and er.request_id = p_request_id
               and er.person_id between p_start_person and p_end_person
               and er.status_code = 'N'
               and asg.person_id = er.person_id
               and asg.assignment_type ='E'
               and trunc(er.end_date) between asg.effective_start_date
                                   and asg.effective_end_date
               and asg.primary_flag = 'Y'
               and nvl(asg.supervisor_id,-999) = nvl(p_break_attribute2,-999);

Line: 960

        fnd_msg_pub.add_exc_msg('PSP_ER_AME','INSERT_INTO_APPROVALS');

Line: 976

  select project_id, task_id, award_id
    from psp_eff_Report_details
   where effort_report_Detail_id = p_effort_Report_Detail_id;

Line: 983

     select er.person_id,
            erd.task_number,
            erd.assignment_number
     from psp_eff_report_details erd,
          psp_eff_reports er
     where erd.effort_report_id = er.effort_report_id
       and er.request_id = p_request_id
       and nvl(erd.task_id,-9999) = nvl(p_group_attribute, -9999)
       and er.person_id between p_start_person  and  p_end_person
     group by er.person_id,
              erd.task_number,
              erd.assignment_number;

Line: 998

     select er.person_id,
            erd.project_number,
            erd.assignment_number
     from psp_eff_report_details erd,
          psp_eff_reports er
     where erd.effort_report_id = er.effort_report_id
       and er.request_id = p_request_id
       and nvl(erd.project_id,-9999) = nvl(p_group_attribute, -9999)
       and er.person_id between p_start_person  and  p_end_person
     group by er.person_id,
              erd.project_number,
              erd.assignment_number;

Line: 1014

     select er.person_id,
            erd.award_number,
            erd.assignment_number
     from psp_eff_report_details erd,
          psp_eff_reports er
     where erd.effort_report_id = er.effort_report_id
       and er.request_id = p_request_id
       and nvl(erd.award_id,-9999) = nvl(p_group_attribute, -9999)
       and er.person_id between p_start_person  and  p_end_person
     group by er.person_id,
              erd.award_number,
              erd.assignment_number;

Line: 1028

     select distinct er.person_id
       from psp_eff_reports er,
            psp_eff_report_details erd
      where er.effort_report_id = erd.effort_report_id
        and erd.ame_transaction_id = p_ame_transaction_id
        and er.person_id between p_start_person  and  p_end_person
        and er.request_id = p_request_id;

Line: 1049

   procedure insert_errors is
     pragma autonomous_transaction;

Line: 1056

       insert into psp_report_errors (error_sequence_id,
                                      request_id,
                                      message_level,
                                      source_id,
                                      error_message,
                                      retry_request_id)
                               values (psp_report_errors_s.nextval,
                                       p_request_id,
                                       'E',
                                       t_source_id(i) ,
                                       t_err_mesg(i),
                                       p_retry_request_id);

Line: 1072

       hr_utility.trace('PSPERAMB-->POPULATER_ERRORS--> INSERT_ERRORS When others='||l_sqlerrm);

Line: 1073

       insert into psp_report_errors
                    (error_sequence_id,
                     request_id,
                     message_level,
                     source_id,
                     error_message,
                     retry_request_id)
        select psp_report_errors_s.nextval,
               p_request_id,
               'E',
                null ,
      'Package, procedure = PSP_ER_AME,insert_errors-->ERROR inserting into psp_report_errors ',
               p_retry_request_id
        from dual;

Line: 1119

        insert_error(p_request_id, 'E', p_group_attribute, l_err_mesg, p_retry_request_id);

Line: 1124

        insert_error(p_request_id, 'E', p_group_attribute, l_err_mesg, p_retry_request_id);

Line: 1142

           insert_errors;

Line: 1168

               insert_errors;

Line: 1198

           insert_errors;

Line: 1218

              hr_utility.trace('before insert into GPI');

Line: 1225

           insert_errors;

Line: 1236

        insert_error(p_request_id, 'E', null, p_error_out, p_retry_request_id);

Line: 1273

       insert_error(p_request_id, 'E', l_err_person_id, l_error_out, p_retry_request_id);

Line: 1291

        insert_error(p_request_id, 'E', l_err_person_id, l_error_out, p_retry_request_id);

Line: 1302

   update psp_eff_reports
     set status_code = 'A'
    where status_code = 'N'
      and request_id = p_request_id
      and person_id between p_start_person and  p_end_person;

Line: 1316

   sql_string :=  'select dtls.effort_report_detail_id,
                            rep.person_id '
                            ||make_select||
                     '  from psp_eff_report_details dtls,
                             psp_eff_reports rep
                        where rep.effort_report_id = dtls.effort_report_id
                          and rep.status_code ='|| ''''||'N'||'''' || '
                          and rep.request_id = :1
                          and rep.person_id between :1 and  :2 ';

Line: 1325

   hr_utility.trace('psperamb--> custom select string='||sql_string);

Line: 1349

                      update psp_eff_report_details
                         set ame_transaction_id = l_ame_txn_id
                      where effort_report_detail_id = t_erd_id(i);

Line: 1361

                      update psp_eff_report_details
                         set ame_transaction_id = l_ame_txn_id
                      where effort_report_detail_id = er_rec.effort_report_detail_id;

Line: 1380

                insert_error(p_request_id,
                     'E',
                     null ,
                     l_err_mesg, p_retry_request_id);

Line: 1385

        select er.person_id
          into l_error_out
        from psp_eff_reports er,
             psp_eff_report_details erd
        where er.effort_report_id = erd.effort_report_id
          and er.request_id = p_request_id
          and erd.effort_report_detail_id = er_rec.effort_report_detail_id;

Line: 1393

          insert_error(p_request_id,
                     'E',
                     l_error_out,
                     l_sqlerrm, p_retry_request_id);

Line: 1406

      update psp_eff_report_details
         set ame_transaction_id = l_ame_txn_id
       where effort_report_detail_id = er_rec.effort_report_detail_id;

Line: 1414

          insert_into_approvals('Y',
                                approval_type_rec.approval_type,
                                er_rec.effort_report_detail_id,
                                null,
                                null,
                                l_next_approver(i).name,
                                l_next_approver(i).orig_system,
                                l_next_approver(i).orig_system_id,
                                l_next_approver(i).approval_status,
                                l_next_approver(i).approver_order_number,
                                l_ame_txn_id,
                                l_next_approver(i).display_name);

Line: 1431

hr_utility.trace('psperamb--> **** CUSTOM APPROVAL TYPE **** make_select return='||make_select);

Line: 1482

             update psp_eff_report_details
                set ame_transaction_id = l_ame_txn_id
              where nvl(investigator_person_id,-999) = nvl(break_rec.array_break_attribute(l_counter),-999)
                and effort_report_id in
                     (select effort_report_id
                        from psp_eff_reports er
                       where er.request_id = p_request_id
                         and er.person_id between p_start_person and p_end_person
                         and er.status_code = 'N');

Line: 1493

             update psp_eff_report_details
                set ame_transaction_id = l_ame_txn_id
              where effort_report_id in
                     (select erd.effort_report_id
                        from psp_eff_report_details  erd
                       where erd.effort_report_detail_id = break_rec.array_detail_id(l_counter));

Line: 1500

             update psp_eff_report_details erd
                set erd.ame_transaction_id = l_ame_txn_id
              where erd.effort_report_id in
                     (select er.effort_report_id
                        from psp_eff_reports er,
                             per_all_assignments_f asg
                       where er.request_id = p_request_id
                         and er.person_id between p_start_person and p_end_person
                         and er.status_code = 'N'
                         and asg.person_id = er.person_id
                         and asg.assignment_type ='E'
                         and trunc(er.end_date) between asg.effective_start_date
                                                   and asg.effective_end_date
                         and asg.primary_flag = 'Y'
                         and nvl(asg.supervisor_id,-999) = nvl(break_rec.array_break_attribute2(l_counter),-999));

Line: 1538

         /* insert_error(p_request_id,
                     'E',
                     null,
                     l_sqlerrm); */

Line: 1581

          insert_into_approvals('N',
                                approval_type_rec.approval_type,
                                break_rec.array_detail_id(l_counter),
                                break_rec.array_break_attribute(l_counter),
                                break_rec.array_break_attribute2(l_counter),
                                l_next_approver(i).name,
                                l_next_approver(i).orig_system,
                                l_next_approver(i).orig_system_id,
                                l_next_approver(i).approval_status,
                                l_next_approver(i).approver_order_number,
                                l_ame_txn_id,
                                l_next_approver(i).display_name);

Line: 1610

          insert_into_approvals('N',
                                approval_type_rec.approval_type,
                                break_rec.array_detail_id(l_counter),
                                break_rec.array_break_attribute(l_counter),
                                break_rec.array_break_attribute2(l_counter),
                                l_next_approver(1).name,
                                l_next_approver(1).orig_system,
                                l_next_approver(1).orig_system_id,
                                l_next_approver(1).approval_status,
                                l_next_approver(1).approver_order_number,
                                l_ame_txn_id,
                                l_next_approver(1).display_name);

Line: 1628

    ame_api2.updateapprovalstatus(applicationidin => 8403,
                                    transactiontypein => 'PSP-ER-APPROVAL',
                                    transactionidin => l_ame_txn_id,
                                    approverin => approver_rec); */

Line: 1635

     break_rec.array_detail_id.delete;

Line: 1636

     break_rec.array_break_attribute.delete;

Line: 1637

     break_rec.array_break_attribute2.delete;

Line: 1661

        select er.person_id
          into l_error_out
        from psp_eff_reports er,
             psp_eff_report_details erd
        where er.effort_report_id = erd.effort_report_id
          and er.request_id = p_request_id
          and erd.effort_report_detail_id = break_rec.array_detail_id(l_counter);

Line: 1669

          insert_error(p_request_id,
                     'E',
                     l_error_out,
                     l_sqlerrm, p_retry_request_id);