[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'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';
select ppa.payroll_action_id,
ppa.report_type,
ppa.report_qualifier,
ppa.report_category,
ppa.business_group_id,
ppa.effective_date,
pay_core_utils.get_parameter('BP_HI_NUMBER',ppa.legislative_parameters) bp_hi_number, --3506171
fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',ppa.legislative_parameters)) reported_date,
trunc(fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',ppa.legislative_parameters)),'YYYY') year_start_date
into g_pact
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
SELECT DISTINCT asg.assignment_id,
asg.establishment_id
FROM per_assignments_f asg,
pay_payroll_actions xppa,
hr_organization_units hou1,
hr_organization_information hoi1
WHERE xppa.payroll_action_id = p_payroll_action_id
and hou1.business_group_id = g_pact.business_group_id --3506171
and hoi1.organization_id = hou1.organization_id
and hoi1.org_information_context = 'KR_HI_INFORMATION'
and hoi1.org_information1 = g_pact.bp_hi_number
AND asg.business_group_id = g_pact.business_group_id
AND asg.establishment_id = hou1.organization_id
AND asg.person_id BETWEEN p_start_person_id AND p_end_person_id
-- BUG 3453612
AND xppa.effective_date between asg.effective_start_date and asg.effective_end_date
AND NOT EXISTS (SELECT NULL
FROM pay_payroll_actions ppa4,
pay_assignment_actions paa4
WHERE paa4.assignment_id = asg.assignment_id
AND paa4.source_action_id IS NULL
AND ppa4.payroll_action_id = paa4.payroll_action_id
AND ppa4.action_type = 'X'
AND ppa4.report_type = 'HIA'
AND ppa4.report_qualifier = 'KR'
AND ppa4.report_category = 'A'
AND trunc(ppa4.effective_date, 'YYYY') = trunc(xppa.effective_date, 'YYYY')
UNION ALL -- Bug : 4859742
(SELECT NULL
FROM per_people_extra_info pei
WHERE pei.person_id = asg.person_id
AND pei.pei_information6 = 'Y'
AND pei.information_type = 'PER_KR_HEALTH_INSURANCE_INFO'))
AND EXISTS (SELECT NULL
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.effective_date BETWEEN
trunc(fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',xppa.legislative_parameters)), 'YYYY')
AND fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',xppa.legislative_parameters))
AND ppa.action_type in ('R','Q')
AND paa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.source_action_id IS NULL
AND paa.assignment_id = asg.assignment_id);
SELECT pay_assignment_actions_s.nextval
INTO l_locking_action_id
FROM dual;
SELECT fue.user_entity_id,
dbi.data_type
FROM ff_user_entities fue,
ff_database_items dbi
WHERE user_entity_name = c_user_entity_name
AND fue.user_entity_id = dbi.user_entity_id;
SELECT paa.assignment_id,
pa.payroll_id,
pa.establishment_id -- 3506171
FROM per_assignments_f pa,
pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assignment_action_id
AND pa.assignment_id = paa.assignment_id
AND g_pact.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date;
SELECT pp.last_name || pp.first_name employee_name,
pp.national_identifier national_identifier,
pei.pei_information1 hi_number,
nvl(pei.pei_information4,pei.pei_information2) qualified_date,
hhoi.org_information1 business_place_code,
NULL business_place_unit
FROM per_people_extra_info pei,
per_people_f pp,
per_assignments_f pa,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_organization_information hhoi,
per_periods_of_service pds
WHERE ppa.payroll_action_id = g_pact.payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pa.assignment_id = paa.assignment_id
AND pp.person_id = pa.person_id
AND pei.person_id(+) = pp.person_id
AND pei.information_type(+) = 'PER_KR_HEALTH_INSURANCE_INFO'
AND paa.tax_unit_id = hhoi.organization_id
AND hhoi.org_information_context = 'KR_HI_INFORMATION'
AND pp.person_id = pds.person_id
AND NVL(pds.actual_termination_date,ppa.effective_date) BETWEEN pa.effective_start_date AND pa.effective_end_date
AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
AND pp.business_group_id = g_pact.business_group_id
AND paa.assignment_action_id = p_assignment_action_id;
select defined_balance_id
from pay_balance_types pbt ,
pay_balance_dimensions pbd ,
pay_defined_balances pdb
where pbt.balance_name =p_balance_name
and pbt.legislation_code ='KR'
and pbd.dimension_name =p_dimension_name
and pbd.legislation_code ='KR'
and pbt.balance_type_id =pdb.balance_type_id
and pbd.balance_dimension_id =pdb.balance_dimension_id ;
/* Bug 4199014: Performance update - Removed check for balance value (call to PAY_BALANCE_PKG.GET_VALUE)
from the WHERE clause. Changed the SELECT clause, added extra WHERE predicate
(PAA.ACTION_STATUS).
*/
cursor csr_hi_no_of_mths_prem_paid(p_assact_id pay_assignment_actions.assignment_action_id%type ,
p_start_date date ,
p_end_date date )
is
select paa.assignment_action_id
,to_number(to_char(ppa.effective_date,'MM') )
from pay_assignment_actions xpaa
,pay_payroll_actions xppa
,pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_types_f prt
where xpaa.assignment_action_id = p_assact_id
and xppa.payroll_action_id = xpaa.payroll_action_id
and xpaa.assignment_id = paa.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = 'C' -- Bug 4199014
and ppa.action_type in ('B', 'I', 'V', 'R', 'Q')
and prt.run_type_id = paa.run_type_id
and prt.run_type_name = 'MTH'
and ppa.effective_date between prt.effective_start_date
and prt.effective_end_date
and prt.legislation_code = 'KR'
and ppa.effective_date between p_start_date
and p_end_date
and xppa.business_group_id = ppa.business_group_id
order by 2 ; -- IMPORTANT: Logic below depends on this ORDER BY
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa,
per_assignments_f pa,
pay_payroll_actions ppa
WHERE pa.assignment_id = l_assignment_id
AND paa.assignment_id = l_assignment_id
AND paa.assignment_id = pa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between trunc(p_effective_date, 'YYYY') and (add_months(trunc(p_effective_date,'YYYY'),12)-1)
AND ppa.action_type in ('B', 'V', 'R', 'Q', 'I')
AND paa.action_status = 'C'
AND p_effective_date between pa.effective_start_date and pa.effective_end_date
ORDER BY paa.action_sequence desc;
l_arch_tab.delete;
/* Bug 4199014: (Performance update)
csr_hi_no_of_mths_prem_paid now gets only the
assignment actions corresponding to a monthly
run. IT NO LONGER FILTERS THE DATA BASED ON THE
VALUE OF BALANCE HI_PREM_EE_WO_ADJ_ASG_MTD_MTH.
We place this check after the cursor's execution.
*/
l_assact_tbl.delete ;
l_ppa_mth_tbl.delete ;
SELECT printer,
print_style,
decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
FROM pay_payroll_actions pact,
fnd_concurrent_requests fcr
WHERE fcr.request_id = pact.request_id
AND pact.payroll_action_id = p_payroll_action_id;