[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 parameter_value
from pay_action_parameters
where parameter_name = 'RANGE_PERSON_ID';
select par.parameter_value
from pay_report_format_parameters par,
pay_report_format_mappings_f map
where map.report_format_mapping_id = par.report_format_mapping_id
and map.report_type in ('AU_PS_DATA_FILE','AU_PS_DATA_FILE_VAL')
and map.report_format in ('AU_PS_DATA_FILE','AU_PS_DATA_FILE_VAL')
and map.report_qualifier = 'AU'
and par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
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 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_population_ranges ppr
WHERE paa.payroll_action_id = c_payroll_action_id
AND ppr.payroll_action_id = p_payroll_action_id
AND ppr.chunk_number = c_chunk
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'
AND a.person_id = ppr.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 /*+ INDEX (mpa PAY_PAYROLL_ACTIONS_PK)
INDEX (ppr PAY_POPULATION_RANGES_N4)
INDEX (p PER_PEOPLE_F_PK)
INDEX (apa PAY_PAYROLL_ACTIONS_N52)
INDEX (a PER_ASSIGNMENTS_F_N12)
INDEX (apac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX (ppai PAY_ACTION_INTERLOCKS_FK2)
INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
*/
DISTINCT 'ASSIGNMENT_ACTION_ID=C',
ppac.assignment_action_id ps_report_id,
ppac.assignment_id
FROM pay_payroll_actions mpa,
pay_population_ranges ppr,
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 ppr.payroll_action_id = mpa.payroll_action_id
AND p.person_id = ppr.person_id
AND ppr.chunk_number = c_chunk
AND p.person_id = a.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 apa.report_qualifier = 'AU'
AND apa.report_category = 'REPORT'
AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
AND ppa.report_qualifier = 'AU'
AND ppa.report_category = 'REPORT'
AND apa.business_group_id = mpa.business_group_id
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 locked_action_id
FROM pay_action_interlocks pail,
pay_assignment_actions paa1,
pay_payroll_actions paas
WHERE paas.report_type='AU_PS_DATA_FILE'
AND paas.report_qualifier = 'AU'
AND paas.report_category = 'REPORT'
AND paas.action_status='C'
AND paa1.assignment_id = a.assignment_id
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 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_population_ranges ppr
WHERE paa.payroll_action_id = c_payroll_action_id
AND ppr.payroll_action_id = p_payroll_action_id
AND ppr.chunk_number = c_chunk
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'
AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE',paa.assignment_action_id)='A'
AND a.person_id = ppr.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 /*+ 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,
pay_population_ranges ppr
WHERE mpa.payroll_action_id =c_payroll_action_id
AND ppr.payroll_action_id = mpa.payroll_action_id
AND ppr.chunk_number = c_chunk
AND p.person_id = a.person_id
AND p.person_id = ppr.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
(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;