[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token))
- (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT count(distinct org_information8) paycount,
org_information8
FROM pay_all_payrolls_f papf
, hr_organization_information org
, hr_soft_coding_keyflex sck
WHERE
papf.consolidation_set_id = pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID')
and to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'),'YYYY/MM/DD HH24:MI:SS')
between papf.effective_start_date and papf.effective_end_date
and org.org_information_context = 'IE_PAYPATH_INFORMATION'
and papf.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
and org.ORG_INFORMATION_ID = to_number(sck.segment2)
and org.org_information8 is not null
group by org_information8;
SELECT org.org_information8
FROM hr_organization_information org
, pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
and org.organization_id = ppa.business_group_id
and org.org_information_context = 'IE_PAYPATH_INFORMATION'
and rownum=1;
SELECT org.org_information8
FROM hr_organization_information org
WHERE
org.org_information_context = 'IE_PAYPATH_INFORMATION'
and org.ORG_INFORMATION_ID =
(SELECT to_number(segment2)
FROM
hr_soft_coding_keyflex sck
, pay_all_payrolls_f papf
, pay_payroll_actions ppa
WHERE
papf.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
and papf.payroll_id = pay_magtape_generic.get_parameter_value('PAYROLL_ID')
and papf.consolidation_set_id = pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID')
and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
and papf.payroll_id=ppa.payroll_id);
SELECT consolidation_set_name
FROM PAY_CONSOLIDATION_SETS
WHERE consolidation_set_id = pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID');
p_sqlstr := 'SELECT distinct asg.person_id
FROM per_periods_of_service pos,
per_assignments_f asg,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND pos.person_id = asg.person_id
AND pos.period_of_service_id = asg.period_of_service_id
AND pos.business_group_id = ppa.business_group_id
AND asg.business_group_id = ppa.business_group_id
ORDER BY asg.person_id';
SELECT act.assignment_action_id,
act.assignment_id,
ppp.pre_payment_id
FROM pay_assignment_actions act,
per_all_assignments_f asg,
pay_payroll_actions pa2,
pay_payroll_actions pa1,
pay_pre_payments ppp,
pay_org_payment_methods_f OPM,
pay_payment_types PPT,
per_all_people_f pap
WHERE pa1.payroll_action_id = p_payroll_action_id
AND pa2.consolidation_set_id = p_consolidation_id
AND pa2.payroll_id = NVL(p_payroll_id,pa2.payroll_id)
AND pa2.effective_date <= pa1.effective_date
AND pa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND act.payroll_action_id = pa2.payroll_action_id
AND act.action_status IN ('C','S') --10225372
AND asg.assignment_id = act.assignment_id
AND pa1.business_group_id = asg.business_group_id
AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
AND pap.person_id = asg.person_id
AND pap.person_id between p_start_person_id and p_end_person_id
AND ppp.assignment_action_id = act.assignment_action_id
AND ppp.org_payment_method_id = opm.org_payment_method_id
AND opm.payment_type_id = ppt.payment_type_id
AND ppt.territory_code = 'IE'
AND ppt.payment_type_name = 'PayPath'
AND pap.person_id = NVL(p_person_id,pap.person_id)
AND (p_assignment_set_id IS NULL
OR EXISTS ( SELECT ''
FROM hr_assignment_set_amendments hr_asg
WHERE hr_asg.assignment_set_id = p_assignment_set_id
AND hr_asg.assignment_id = asg.assignment_id
))
AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
FROM pay_action_interlocks pai1,
pay_assignment_actions act2,
pay_payroll_actions appa
WHERE pai1.locked_action_id = act.assignment_action_id
AND act2.assignment_action_id = pai1.locking_action_id
AND act2.payroll_action_id = appa.payroll_action_id
AND appa.action_type = 'X'
AND appa.report_type = 'PayPath');
SELECT pay_assignment_actions_s.nextval
INTO l_locking_action_id
FROM dual;
Select business_group_id
From pay_payroll_actions
Where payroll_action_id = p_payroll_action_id;
Select overriding_dd_date
From pay_payroll_actions
Where payroll_action_id = p_payroll_action_id;
Select ext.segment1, --Sort Code
ext.segment4 --Acc Num
From pay_external_accounts ext,
pay_org_payment_methods_f org
Where org.org_payment_method_id = p_org_payment_method_id
and p_effective_date between org.effective_start_date and org.effective_end_date
and org.external_account_id = ext.external_account_id;
Select hruserdt.get_table_value(l_business_grp_id,
'IE_EFT_RECONC_FUNC',
'RECONCILIATION',
'FUNCTION NAME',
p_effective_date)
Into l_usr_fnc_name
From dual;
EXECUTE IMMEDIATE 'select '||l_usr_fnc_name||'(:1,:2,:3,:4,:5,:6,:7,:8,:9) from dual'
INTO l_return_value
USING p_effective_date ,
p_identifier_name,
p_payroll_action_id,
p_payment_type_id,
p_org_payment_method_id,
p_personal_payment_method_id,
p_assignment_action_id,
p_pre_payment_id,
p_delimiter_string ;