APPS.PAY_HK_IR56_MAGTAPE SQL Statements

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

Line: 43

    p_sql := 'SELECT distinct person_id '                            ||
             'FROM  per_people_f ppf, '                              ||
                    'pay_payroll_actions ppa '                       ||
             'WHERE ppa.payroll_action_id = :payroll_action_id '     ||
             'AND    ppa.business_group_id = ppf.business_group_id ' ||
             'ORDER BY ppf.person_id';

Line: 68

      SELECT pay_assignment_actions_s.NEXTVAL
      FROM  dual;

Line: 80

SELECT      paei.assignment_extra_info_id,
                paei.assignment_id,
                paei.aei_information1,
                paei.aei_information4
    FROM        per_assignment_extra_info paei,
                per_assignment_info_types pait
    WHERE       paei.information_type = 'HR_IR56B_REPORTING_INFO_HK'
    AND         paei.assignment_id = c_assignment_id
    AND         paei.information_type = pait.information_type
    AND         pait.active_inactive_flag = 'Y'
    AND         paei.aei_information1 = c_reporting_year
    AND         paei.aei_information4 = c_legal_entity_id;

Line: 102

select distinct paaf.assignment_id,
paa.assignment_action_id,
pay_core_utils.get_parameter('ARCHIVE_ACTION_ID',ppa2.legislative_parameters) archive_action_id,
pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa3.legislative_parameters) legal_entity_id,
pay_core_utils.get_parameter('REPORTING_YEAR',ppa3.legislative_parameters) reporting_year
from per_people_f papf,
     per_assignments_f paaf,
     pay_payroll_actions ppa, --magtape action
     pay_payroll_actions ppa2,--report action
	 pay_payroll_actions ppa3,--archive action
     pay_assignment_actions paa
where
     ppa.payroll_action_id = c_payroll_action_id
and  papf.person_id between  c_start_person_id and c_end_person_id
and  papf.person_id = paaf.person_id
and  papf.business_group_id = ppa.business_group_id
and  ppa3.payroll_action_id = paa.payroll_action_id
and  paaf.assignment_id= paa.assignment_id
and  ppa2.payroll_action_id = pay_core_utils.get_parameter('REPORT_ACTION_ID',ppa.legislative_parameters)
and  ppa3.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID',ppa2.legislative_parameters)
and  ppa3.action_type = 'X'
and  ppa3.action_status = 'C'
and  paa.action_status  = 'C' /* Bug No : 2829320 - To prevent Magtape fetching errored archive records */
 and  not exists /* dont process locked assignments, bug 7324233, it should only lock the assignments which has magtape generated previously */
 (select locked_action_id
  from   pay_action_interlocks pai,
         pay_payroll_actions ppa1,
         pay_payroll_actions ppa2,
         pay_assignment_actions paa1
  where pai.locked_action_id = paa.assignment_action_id
  and   ppa1.action_type='X'
  and   ppa1.report_type = 'HK_IR56B_MAGTAPE'
  and   ppa2.action_type='X'
  and   ppa2.action_status='C'
  and   ppa1.action_status='C'
  and   ppa2.report_type = 'HK_IR56B_REPORT'
  and   paa1.assignment_action_id = pai.locking_action_id
  and   ppa1.payroll_action_id = paa1.payroll_action_id
  and   ppa2.payroll_action_id = pay_core_utils.get_parameter('REPORT_ACTION_ID',
        ppa1.legislative_parameters)
  );