APPS.PQP_GB_SWF_EXTRACT SQL Statements

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

Line: 127

    select afv.version
      from ad_files af,
           ad_file_versions afv
     where af.file_id = afv.file_id
       and af.app_short_name = 'PQP'
       and af.subdir = 'patch/115/sql'
       and af.filename = 'pqpgbswfar.pkb'
  order by afv.file_version_id desc;

Line: 179

    select pai.action_information3 teacher_number,
           pai.action_information4 family_name,
           pai.action_information5 given_name,
           pai.action_information6 former_family_name,
           pai.action_information7 ni_number,
           pai.action_information8 gender,
           pai.action_information9 date_of_birth,
           pai.action_information10 ethnicity,
           pai.action_information11 disability,
           max(pai.action_information12)over() QTStatus,
           max(pai.action_information13)over() HLTAStatus,
           max(pai.action_information14)over() QTSRoute
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_STAFF_DETAILS'
       and pai.action_information1 = p_person_id;

Line: 233

           Select decode(l_staff_rec.QTStatus,'Yes','True','No','False',null)
           into l_QT_Status
           from dual;

Line: 246

   Select decode(l_staff_rec.HLTAStatus,'Yes','True','No','False',null)
          into l_HLTA_Status
   from dual;

Line: 266

    select pai.assignment_id assignment_id,
           pai.action_information2 contract_type,
           pai.action_information3 contract_st_date,
           pai.action_information4 contract_end_date,
           pai.action_information5 post,
           pai.action_information6 arrival_date,
           pai.action_information7 daily_rate,
           pai.action_information8 destination,
           pai.action_information9 origin,
           pai.action_information10 la_school_level,
           pai.action_information11 est_number
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_CONTRACT_DETAILS'
       and pai.action_information1 = p_person_id
       and pai.action_information11 = nvl(p_est_number,pai.action_information11);

Line: 287

    select pai.action_information1 pay_scale,
           pai.action_information2 regional_pay_spine,
           pai.action_information3 spine_point,
           pai.action_information4 salary_rate,
           pai.action_information5 safeguarded_salary
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_PAYMENT_DETAILS'
       and pai.assignment_id = cp_assignment_id;

Line: 301

    select pai.action_information1 cat_of_add_payment,
           pai.action_information2 add_payment_amt
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_ADD_PAYMENT_DETAILS'
       and pai.assignment_id = cp_assignment_id;

Line: 312

    select pai.action_information1 hours_worked_per_week,
           pai.action_information2 fte_hours_per_week,
           pai.action_information3 weeks_per_year
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_HOURS_DETAILS'
       and pai.assignment_id = cp_assignment_id;

Line: 324

    select pai.action_information1 role_identifier
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
       and pai.assignment_id = cp_assignment_id;

Line: 436

    select pai.action_information2 first_day,
           pai.action_information3 last_day,
           pai.action_information4 working_days_lost,
           pai.action_information5 abs_catagory,
           pai.action_information6 est_number
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_ABS_DETAILS'
       and pai.action_information1 = p_person_id
       and pai.action_information6 = nvl(p_est_number,pai.action_information6);

Line: 474

    select pai.action_information2 qual_code,
 -- Bug 12906657
           -- pai.action_information3 qual_verified,
           -- pai.action_information4 qual_subject1,
           -- pai.action_information5 qual_subject2,
           pai.action_information5 qual_verified,
	   pai.action_information3 qual_subject1,
	   pai.action_information4 qual_subject2,
-- Bug 12906657
           pai.action_information6 est_number
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_QUAL_DETAILS'
       and pai.action_information1 = p_person_id
       and pai.action_information6 = nvl(p_est_number,pai.action_information6);

Line: 521

   select COUNT(*) edu_psy_ft
     from pay_action_information pai,
          pay_assignment_actions paa
    where paa.payroll_action_id = g_payroll_action_id
      and paa.assignment_action_id = pai.action_context_id
      and pai.action_context_type = 'AAP'
      and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
      and pai.action_information1 = 'EPSY'
      and pai.action_information2 = 'F';

Line: 532

   select COUNT(*) edu_psy_pt
     from pay_action_information pai,
          pay_assignment_actions paa
    where paa.payroll_action_id = g_payroll_action_id
      and paa.assignment_action_id = pai.action_context_id
      and pai.action_context_type = 'AAP'
      and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
      and pai.action_information1 = 'EPSY'
      and pai.action_information2 = 'P';

Line: 543

   select sum(round(nvl(pai2.action_information7,0),1)) edu_psy_fte
     from pay_action_information pai,
          pay_action_information pai2,
          pay_assignment_actions paa
    where paa.payroll_action_id = g_payroll_action_id
      and paa.assignment_action_id = pai.action_context_id
      and pai.action_context_id = pai2.action_context_id
      and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
      and pai2.action_information_category = 'GB_SWF_HOURS_DETAILS'
      and pai.action_information1 = 'EPSY'
      and pai.action_information2 = 'P';

Line: 591

    select ppa.payroll_action_id
          ,pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'LEA_NUM') lea_number
          ,pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'CENSUS_DAY') census_date
          ,upper(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'DATA_RETURN_TYPE')) data_return_type
          ,NVL(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'ESTB_NUM'),'All') estab_number
          ,pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'EXCLUDE_ABS') exclude_abs
          ,pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_QUAL') exclude_qual
      from pay_payroll_actions ppa
     where ppa.request_id = p_request_id;

Line: 602

    select distinct pai.action_information1 person_id
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_STAFF_DETAILS';

Line: 611

    select distinct pai.action_information2 est_number
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_STAFF_DETAILS';

Line: 620

    select distinct pai.action_information1 person_id
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_STAFF_DETAILS'
       and (
             (cp_est_number is null AND pai.action_information2 is null)
              OR
             (cp_est_number is not null AND pai.action_information2 = cp_est_number)
           );

Line: 634

    select min(pai.action_information14) person_category
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category = 'GB_SWF_CONTRACT_DETAILS'
       and pai.action_information1 = cp_person_id;

Line: 644

    select pai.action_information2 est_number,
           count(distinct pai.action_information1) staff_count
      from pay_action_information pai,
           pay_assignment_actions paa
     where paa.payroll_action_id = g_payroll_action_id
       and paa.assignment_action_id = pai.action_context_id
       and pai.action_context_type = 'AAP'
       and pai.action_information_category ='GB_SWF_STAFF_DETAILS'
  group by pai.action_information2;