[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sql := ' select distinct p.person_id' ||
' from per_people_f p,' ||
' pay_payroll_actions pa ' ||
' where pa.payroll_action_id = :payroll_action_id' ||
' and p.business_group_id = pa.business_group_id' ||
' order by p.person_id';
SELECT pay_core_utils.get_parameter('IS_TESTING',ppa.legislative_parameters),
pay_core_utils.get_parameter('ARCHIVE_PAYROLL_ACTION',ppa.legislative_parameters),
pay_core_utils.get_parameter('PAYMENT_SUMMARY_TYPE',ppa.legislative_parameters) /* bug 6630375 */
FROM pay_payroll_actions ppa
WHERE ppa.payroll_Action_id = p_payroll_action_id;
SELECT DISTINCT 'ASSIGNMENT_ACTION_ID=C',
paa.assignment_action_id ps_report_id,
paa.assignment_id
FROM pay_assignment_actions paa,
per_assignments_f a
WHERE paa.payroll_action_id = c_payroll_action_id
AND paa.action_status = 'C'
AND a.assignment_id = paa.assignment_id
AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG',paa.assignment_action_id)='YES'
AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',paa.assignment_action_id)='NO' /* Added for bug 5257622 */
AND a.person_id BETWEEN c_start_person_id AND c_end_person_id ;
SELECT /*+ INDEX (apac PAY_ASSIGNMENT_ACTIONS_N50)
INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
INDEX(mpa PAY_PAYROLL_ACTIONS_PK)
INDEX(ppai PAY_ACTION_INTERLOCKS_FK2)
INDEX (p PER_PEOPLE_F_PK)
INDEX(a PER_ASSIGNMENTS_F_PK) */
DISTINCT 'ASSIGNMENT_ACTION_ID=C',
ppac.assignment_action_id ps_report_id,
ppac.assignment_id
FROM pay_payroll_actions mpa,
per_people_f p,
per_assignments_f a,
pay_payroll_actions apa,
pay_assignment_actions apac,
pay_payroll_actions ppa,
pay_assignment_actions ppac,
pay_action_interlocks ppai
WHERE mpa.payroll_action_id =c_payroll_action_id
AND p.person_id = a.person_id
AND p.person_id BETWEEN c_start_person_id AND c_end_person_id
AND p.business_group_id = mpa.business_group_id
AND apa.payroll_action_id = apac.payroll_action_id
AND ppa.payroll_action_id = ppac.payroll_action_id
AND apac.assignment_action_id = ppai.locked_action_id
AND ppac.assignment_action_id = ppai.locking_action_id
and apa.action_status = 'C'
AND ppa.action_status = 'C'
AND apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
AND a.assignment_id = apac.assignment_id
AND a.assignment_id = ppac.assignment_id
AND apa.report_type ='AU_PAYMENT_SUMMARY'
AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
AND pay_core_utils.get_parameter('BUSINESS_GROUP_ID',apa.legislative_parameters)=
pay_core_utils.get_parameter('BUSINESS_GROUP_ID',mpa.legislative_parameters)
AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',apa.legislative_parameters)=
pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mpa.legislative_parameters)
AND pay_core_utils.get_parameter('FINANCIAL_YEAR',apa.legislative_parameters)=
pay_core_utils.get_parameter('FINANCIAL_YEAR',mpa.legislative_parameters)
AND NOT EXISTS /* 5471093 */
(SELECT /*+ ORDERED */ locked_action_id
FROM pay_action_interlocks pail,
pay_assignment_actions paa1,
pay_payroll_actions paas
WHERE paas.action_type='X'
and paas.action_status='C'
AND paas.report_type='AU_PS_DATA_FILE'
AND paa1.payroll_action_id = paas.payroll_action_id
AND pail.locking_action_id = paa1.assignment_action_id
AND pail.locked_action_id = ppac.assignment_action_id);
SELECT DISTINCT 'ASSIGNMENT_ACTION_ID=C',
paa.assignment_action_id ps_report_id,
paa.assignment_id
FROM pay_assignment_actions paa,
per_assignments_f a,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_status = 'C'
AND a.assignment_id = paa.assignment_id
AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG',paa.assignment_action_id)='YES'
AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',paa.assignment_action_id)='NO'
AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE',paa.assignment_action_id)='A'
AND ppa.report_type = 'AU_PAY_SUMM_AMEND'
AND a.person_id BETWEEN c_start_person_id AND c_end_person_id ;
SELECT /*+ INDEX (apac PAY_ASSIGNMENT_ACTIONS_N50)
INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
INDEX(mpa PAY_PAYROLL_ACTIONS_PK)
INDEX(ppai PAY_ACTION_INTERLOCKS_FK2)
INDEX (p PER_PEOPLE_F_PK)
INDEX(a PER_ASSIGNMENTS_F_PK) */
DISTINCT 'ASSIGNMENT_ACTION_ID=C',
ppac.assignment_action_id ps_report_id,
ppac.assignment_id
FROM pay_payroll_actions mpa,
per_people_f p,
per_assignments_f a,
pay_payroll_actions apa,
pay_assignment_actions apac,
pay_payroll_actions ppa,
pay_assignment_actions ppac,
pay_action_interlocks ppai
WHERE mpa.payroll_action_id =c_payroll_action_id
AND p.person_id = a.person_id
AND p.person_id BETWEEN c_start_person_id AND c_end_person_id
AND p.business_group_id = mpa.business_group_id
AND apa.payroll_action_id = apac.payroll_action_id
AND ppa.payroll_action_id = ppac.payroll_action_id
AND apac.assignment_action_id = ppai.locked_action_id
AND ppac.assignment_action_id = ppai.locking_action_id
and apa.action_status = 'C'
AND ppa.action_status = 'C'
AND apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
AND a.assignment_id = apac.assignment_id
AND a.assignment_id = ppac.assignment_id
AND apa.report_type ='AU_PAY_SUMM_AMEND'
AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
AND pay_core_utils.get_parameter('BUSINESS_GROUP_ID',apa.legislative_parameters)=
pay_core_utils.get_parameter('BUSINESS_GROUP_ID',mpa.legislative_parameters)
AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',apa.legislative_parameters)=
pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mpa.legislative_parameters)
AND pay_core_utils.get_parameter('FINANCIAL_YEAR',apa.legislative_parameters)=
pay_core_utils.get_parameter('FINANCIAL_YEAR',mpa.legislative_parameters)
AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE', apac.assignment_action_id)='A'
AND NOT EXISTS /* 5471093 */
(SELECT /*+ ORDERED */ locked_action_id
FROM pay_action_interlocks pail,
pay_assignment_actions paa1,
pay_payroll_actions paas
WHERE paas.action_type='X'
and paas.action_status='C'
AND paas.report_type='AU_PS_DATA_FILE'
AND paa1.payroll_action_id = paas.payroll_action_id
AND pail.locking_action_id = paa1.assignment_action_id
AND pail.locked_action_id = ppac.assignment_action_id);
SELECT pay_assignment_actions_s.NEXTVAL
FROM dual;