[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aei_information1 PPSN_OVERRIDE
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND aei_information_category = 'IE_ASG_OVERRIDE';
select paaf.person_id
from per_assignments_f paaf, per_assignments_f paaf1
where paaf.assignment_id= p_assignment_id
and paaf.business_group_id = p_bg_id
and paaf.business_group_id =paaf1.business_group_id
and paaf.person_id = paaf1.person_id
and paaf1.primary_flag <> 'Y'
and paaf1.effective_end_date >= p_start_date
and paaf1.effective_start_date <= p_end_date;
SELECT MAX(paa.assignment_action_id)
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paf.person_id = l_person_id
AND paf.assignment_id = paa.assignment_id
AND paa.action_status = 'C'
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('R','Q','I','B','V')
--AND ppa.time_period_id = ptp.time_period_id
--AND ptp.end_date BETWEEN p_start_date AND p_end_date
AND ppa.payroll_id = ptp.payroll_id -- Bug 5070091 Offset payroll change
AND ppa.date_earned between ptp.start_date and ptp.end_date
AND ppa.effective_date between p_start_date and p_end_date
AND paf.effective_start_date <= p_end_date
AND paf.effective_end_date >= p_start_date;
select assignment_action_id from pay_assignment_actions
where source_action_id = p_max_action_id;
SELECT /*+ ordered */
asg.business_group_id business_group_id,
asg.person_id person_id, per.full_name full_name,
per.original_date_of_hire original_hire_date,
MIN (ptp.end_date) minimum_effective_date,
asg.primary_flag,
paa.assignment_action_id,
trim(rrv1.result_value) result_value
FROM per_people_f per,
per_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr,
pay_element_types_f pet,
pay_input_values_f piv1,
pay_run_result_values rrv1
WHERE per.person_id = p_person_id
AND per.current_employee_flag = 'Y'
AND per.effective_start_date =
(SELECT MIN (per2.effective_start_date)
FROM per_people_f per2
, per_periods_of_service pos2
WHERE per2.person_id = per.person_id
AND per2.effective_start_date <= p_end_date
AND pos2.person_id = per2.person_id
AND pos2.date_start between per2.effective_start_date and per2.effective_end_date
AND NVL (pos2.final_process_date, p_end_date) >= p_start_date
AND per2.current_employee_flag = 'Y' )
AND asg.person_id = per.person_id
AND asg.effective_start_date =
(SELECT MIN (asg2.effective_start_date)
FROM per_assignments_f asg2,
per_assignment_status_types ast
WHERE asg2.assignment_id = asg.assignment_id
AND asg2.effective_start_date <= p_end_date
AND NVL (asg2.effective_end_date, p_end_date) >= p_start_date
AND asg2.assignment_type = 'E'
AND asg2.assignment_status_type_id = ast.assignment_status_type_id )
AND asg.assignment_type = 'E'
AND paa.assignment_id = asg.assignment_id
AND paa.action_status = 'C'
and paa.assignment_action_id > p_assignment_action_id
-- used nvl because for action_type='B' l_child_assignemnt_action will be null
and paa.assignment_action_id <= nvl(l_child_assignemnt_action,p_max_action_id)
AND paa.tax_unit_id = to_number(p_segment4)
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q', 'R', 'B')
AND ppa.payroll_id = ptp.payroll_id
AND ppa.date_earned between ptp.start_date and ptp.end_date
and ppa.effective_date between p_start_date AND p_end_date
AND pet.element_name = 'IE PRSI Contribution Class'
AND pet.legislation_code = 'IE'
AND pet.element_type_id = piv1.element_type_id
AND piv1.NAME = 'Contribution_Class'
AND piv1.legislation_code = 'IE'
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND rrv1.input_value_id = piv1.input_value_id
AND rrv1.run_result_id = prr.run_result_id
GROUP BY asg.business_group_id,
asg.person_id,
per.full_name,
per.original_date_of_hire,
asg.primary_flag,
paa.assignment_action_id,
trim(rrv1.result_value)
ORDER BY asg.primary_flag desc,minimum_effective_date,paa.assignment_action_id;
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 bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT pdb.defined_balance_id
FROM pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_defined_balances pdb
WHERE pbd.dimension_name = p_dimension_name
AND pbd.business_group_id IS NULL
AND pbd.legislation_code = 'IE'
AND pbt.balance_name = p_balance_name
AND pbt.business_group_id IS NULL
AND pbt.legislation_code = 'IE'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.business_group_id IS NULL
AND pdb.legislation_code = 'IE';
SELECT NVL (MIN (ppa.payroll_action_id), 0)
FROM pay_payroll_actions ppa
WHERE ppa.report_type = 'IEP30_PRGLOCK'
AND ppa.action_status = 'C'
AND TO_DATE (
pay_ie_p35.get_parameter (
ppa.payroll_action_id,
'END_DATE'
),
'YYYY/MM/DD'
) between l_start_date and l_end_date
AND ppa.business_group_id = l_bg_id;
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 DISTINCT hasa.include_or_exclude FROM
hr_assignment_set_amendments hasa,
hr_assignment_sets has
WHERE hasa.assignment_set_id = has.assignment_set_id
AND has.business_group_id = l_bg_id
AND has.assignment_set_id = l_assignment_set_id;
SELECT /*+ ORDERED USE_NL(asg, paa, ppa, ptp, flex) push_subq */
asg.person_id,
paa.assignment_id,
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) aa -- Bug 4672715
FROM per_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_all_payrolls_f pap,
per_time_periods ptp,
hr_soft_coding_keyflex flex
WHERE paa.source_action_id IS NULL
AND paa.payroll_action_id = ppa.payroll_action_id
--Added for date track updates of payroll with diff pay ref no
AND ppa.payroll_id = pap.payroll_id
/* For time period impact */
-- AND ppa.time_period_id = ptp.time_period_id -- Bug 5070091 Offset payroll change
AND ppa.payroll_id = ptp.payroll_id
AND ppa.date_earned between ptp.start_date and ptp.end_date
--AND ptp.end_date BETWEEN l_start_date AND l_end_date
AND ppa.effective_date between l_start_date AND l_end_date
---
AND paa.action_status = 'C'
AND ppa.action_type IN ('R','Q','I','B','V') --Bug Fix 3747646
AND ppa.business_group_id = l_bg_id
AND paa.assignment_id = asg.assignment_id
AND asg.effective_start_date <= l_end_date
AND asg.effective_end_date >= l_start_date
--AND asg.primary_flag = 'Y'
AND asg.business_group_id = ppa.business_group_id
AND asg.person_id BETWEEN stperson AND endperson
--decode added to pick the previous assignments also in case of ReHire having diff overrides.
AND asg.effective_end_date = DECODE(OVERRIDE_PPSN(asg.assignment_id),NULL, --6633719
(SELECT MAX (paf.effective_end_date)
FROM per_assignments_f paf,
pay_assignment_actions paa1, --Bug fix 4130665
pay_payroll_actions ppa1,
per_time_periods ptp1 --Tar 15081088.6
,pay_all_payrolls_f pay
,hr_soft_coding_keyflex flex1
WHERE paf.person_id = asg.person_id
-- AND paf.primary_flag = 'Y'
--Added for bug fix 4130665
AND paf.assignment_id = paa1.assignment_id
AND paa1.action_status = 'C'
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type in ('R','Q','I','B','V')
/* For time period impact */
--AND ppa1.time_period_id = ptp1.time_period_id --Tar 15081088.6
AND ppa1.payroll_id = ptp1.payroll_id
AND ppa1.date_earned between ptp1.start_date and ptp1.end_date
AND ppa1.effective_date between l_start_date AND l_end_date
---------
--AND ptp.end_date BETWEEN l_start_date AND l_end_date --Tar 15081088.6 -- Bug 5070091 Offset payroll change
and pay.payroll_id = paf.payroll_id
and pay.soft_coding_keyflex_id = flex1.soft_coding_keyflex_id
and flex1.segment4 = l_segment4
AND ((paf.payroll_id = asg.payroll_id AND
paf.assignment_id =asg.assignment_id)
OR paf.assignment_id <> asg.assignment_id) -- Fix for duplicate records in Rehire case
AND paf.effective_start_date <= l_end_date
AND paf.effective_end_date >= l_start_date
AND pay.effective_start_date <= l_end_date
AND pay.effective_end_date >= l_start_date) -- Bug 4867657
,asg.effective_end_date) --6633719
--Added for bug fix 3567562,to restrict the assignments to the PAYE reference selected as parameter.
AND pap.payroll_id = asg.payroll_id
AND flex.soft_coding_keyflex_id = pap.soft_coding_keyflex_id
-- Bug 4142582
AND flex.segment4 = l_segment4
AND pap.effective_start_date <= l_end_date
AND pap.effective_end_date >= l_start_date
AND (pap.payroll_id in (select b.payroll_id from per_assignments_f a,per_assignments_f b
where a.payroll_id = l_payroll_id
and a.person_id = b.person_id
and a.person_id = asg.person_id
--bug 6642916
and a.effective_start_date<= l_end_date
and a.effective_end_date>= l_start_date)
or l_payroll_id is null) -- Vik Added for payroll
--and check_assignment_in_set(asg.assignment_id,l_assignment_set_id,l_bg_id)=1
AND ((l_assignment_set_id is not null
AND (l_set_flag ='I' AND EXISTS(SELECT 1
FROM hr_assignment_set_amendments hasa
, hr_assignment_sets has
, per_assignments_f paf
-- , pay_all_payrolls_f pay
-- , hr_soft_coding_keyflex hflex
WHERE has.assignment_set_id = hasa.assignment_set_id
AND has.business_group_id = l_bg_id
AND has.assignment_set_id = l_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = asg.person_id)
--AND paf.payroll_id = pay.payroll_id
--AND pay.soft_coding_keyflex_id = hflex.soft_coding_keyflex_id
--AND hflex.segment4 = l_segment4)
OR l_set_flag = 'E' AND NOT EXISTS(SELECT 1
FROM hr_assignment_set_amendments hasa
, hr_assignment_sets has
, per_assignments_f paf
-- , pay_all_payrolls_f pay
-- , hr_soft_coding_keyflex hflex
WHERE has.assignment_set_id = hasa.assignment_set_id
AND has.business_group_id = l_bg_id
AND has.assignment_set_id = l_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = asg.person_id
--AND paf.payroll_id = pay.payroll_id
--AND pay.soft_coding_keyflex_id = hflex.soft_coding_keyflex_id
--AND hflex.segment4 = l_segment4
)))
OR l_assignment_set_id IS NULL)
AND NOT EXISTS (
SELECT 1
FROM pay_assignment_actions paa_p35,
pay_payroll_actions ppa_p35,
per_assignments_f paaf_p35,
pay_all_payrolls_f ppf_p35,
hr_soft_coding_keyflex flex_p35,
pay_action_interlocks plock
WHERE ppa_p35.report_type = 'IEP35'
AND paa_p35.action_status = 'C'
AND TO_CHAR ( TO_DATE (
pay_ie_p35.get_parameter (
ppa_p35.payroll_action_id,
'END_DATE'
),'YYYY/MM/DD'),'YYYY') = TO_CHAR(l_end_date,'YYYY') --4641756
AND ppa_p35.payroll_action_id = paa_p35.payroll_action_id
--AND paa_p35.assignment_id = asg.assignment_id
AND paa_p35.assignment_id = paaf_p35.assignment_id
AND paaf_p35.person_id = asg.person_id
and paa_p35.assignment_action_id = plock.locking_action_id
and plock.locked_action_id in (select assignment_action_id from pay_assignment_actions
where assignment_id=asg.assignment_id)
AND paaf_p35.payroll_id = ppf_p35.payroll_id
AND ppf_p35.soft_coding_keyflex_id = flex_p35.soft_coding_keyflex_id
AND flex_p35.segment4 = l_segment4)
GROUP BY asg.person_id,paa.assignment_id
ORDER BY asg.person_id,
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) desc, -- Bug 4672715
paa.assignment_id desc;
select aei_information1 PPSN_OVERRIDE
from per_assignment_extra_info
where assignment_id = p_asg_id
and aei_information_category = 'IE_ASG_OVERRIDE';
select MAX(paei.assignment_id) ovrride_asg
from per_assignment_extra_info paei
where paei.information_type = 'IE_ASG_OVERRIDE'
and paei.aei_information1 = p_ppsn_override
and exists
(select 1 from per_all_assignments_f paaf
where paaf.assignment_id = paei.assignment_id
and paaf.person_id = p_person_id)
GROUP BY paei.aei_information1;
SELECT 'Y', ppa.action_type
FROM pay_run_result_values prrv,
pay_run_results prr,
pay_input_values_f pivf,
pay_element_types_f pet,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id= paa.payroll_action_id
AND ((paa.source_action_id= p_action_id
AND prr.assignment_action_id = paa.assignment_action_id )
OR (paa.assignment_action_id = p_action_id AND prr.assignment_action_id = paa.assignment_action_id ))
AND prr.element_type_id = pet.element_type_id
AND pet.element_name = 'IE PAYE details'
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.name = 'Tax Basis'
and result_value = 'IE_EXCLUDE';
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) asg_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.action_type in ('R','Q')
and paa.source_action_id is null
AND ppa.effective_date between l_start_date AND l_end_date;
FOR csr_select_asg_rec IN csr_locked_asgs
LOOP
hr_utility.set_location('Person id..'||to_char(csr_select_asg_rec.person_id),17);
hr_utility.set_location('csr_select_asg_rec.assignment_id'||to_char(csr_select_asg_rec.assignment_id),18);
IF l_temp_person_id <> csr_select_asg_rec.person_id
THEN
l_ppsn_override := NULL;
OPEN csr_ppsn_override(csr_select_asg_rec.assignment_id);
OPEN csr_ppsn_max_asg(l_ppsn_override,csr_select_asg_rec.person_id);
IF ((l_temp_person_id <> csr_select_asg_rec.person_id
and l_ppsn_override IS NULL)
OR
(l_ppsn_override_asg = csr_select_asg_rec.assignment_id
and l_ppsn_override IS NOT NULL)
)
THEN
--6633719
-- 5867343
l_flag_exclusion := 'N';
OPEN csr_exclusion(csr_select_asg_rec.aa);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
csr_select_asg_rec.assignment_id,
pactid,
CHUNK,
to_number(l_segment4)
);
OPEN csr_action_type(csr_select_asg_rec.assignment_id);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
csr_select_asg_rec.assignment_id,
pactid,
CHUNK,
to_number(l_segment4)
);
hr_utility.set_location('Insert asg action Intlks l_actid'||to_char(l_actid),18);
hr_utility.set_location('Insert asg action Intlks csr_select_asg_rec.aa'||to_char(csr_select_asg_rec.aa),18);
hr_nonrun_asact.insint (l_actid,csr_select_asg_rec.aa);
l_temp_person_id := csr_select_asg_rec.person_id;
SELECT * from pay_action_interlocks
where locking_action_id = p_assactid
order by locking_action_id,locked_action_id desc;
SELECT paa.assignment_action_id,ppa.action_type
FROM pay_action_interlocks pal,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pal.locked_action_id = p_action_id
AND pal.locking_action_id = paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND (ppa.action_type IN ('P', 'U')
OR (ppa.action_type='X' and ppa.report_type = 'IEPS'
and paa.source_action_id IS NULL
and exists (select 1 from pay_action_information pai
where pai.action_information_category = 'IE EMPLOYEE DETAILS'
AND pai.action_context_type='AAP'
AND pai.action_context_id=paa.assignment_action_id)))
ORDER BY paa.assignment_action_id DESC;
select ppa.action_type, ppa.report_type
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.source_action_id IS NULL
and paa.assignment_action_id = p_action_id;*/
SELECT paa.assignment_action_id
FROM pay_action_interlocks pal,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_information pai
WHERE pal.locked_action_id = p_asg_act_id
AND pal.locking_action_id = paa.assignment_action_id
AND paa.source_action_id IS NULL
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.report_type = 'IEPS'
AND pai.action_information_category = 'IE EMPLOYEE DETAILS'
AND pai.action_context_type='AAP'
AND pai.action_context_id=paa.assignment_action_id
ORDER BY 1 DESC;
SELECT paa.assignment_action_id
FROM pay_action_interlocks pal,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pal.locked_action_id = p_asg_act_id
AND pal.locking_action_id = paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.action_type IN ('P', 'U');*/
SELECT SUBSTR (pai1.action_information4, 1, 30) bval
FROM pay_action_information pai1,
pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pbt.legislation_code = 'IE'
AND UPPER (pbt.balance_name) = p_balance_name
AND pbd.legislation_code = 'IE'
AND pbd.dimension_name = p_dimension_name -- 6633719 '_PER_PAYE_REF_YTD' -- changes made
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pai1.action_context_type = 'AAP'
AND pai1.action_information_category = 'EMEA BALANCES'
AND pai1.action_information1 = pdb.defined_balance_id
AND pai1.action_context_id = p_locked_action_id;
SELECT NVL(action_information20,'N') PPSN_BAL_ARCHIVED
FROM pay_action_information pai
WHERE
pai.action_context_type = 'AAP'
AND pai.action_information_category = 'IE EMPLOYEE DETAILS'
AND pai.action_context_id = p_locked_action_id;
SELECT NVL (pact_edi.action_information4, ' '), --PPSN number
NVL (pact_edi.action_information14, ' '), -- WORKS_NUMBER
paf.person_id,
paf.primary_flag, -- changes made
paf.assignment_id,
NVL (TRIM (RPAD (pact_iedi.action_information29, 20)), ' '), -- SURNAME
NVL (TRIM (RPAD (pact_iedi.action_information28, 20)), ' '), -- FIRST_NAME
NVL (TRIM (pact_iedi.action_information25), '31-12-4712'), -- DOB
NVL (TRIM (RPAD (pact_ad.action_information5, 30)), ' '), -- ADDRESS_LINE1
NVL (TRIM (pact_ad.action_information6), ' '), -- ADDRESS_LINE2
NVL (TRIM ( pact_ad.action_information7), ' '), -- ADDRESS_LINE3 BUG 4066315
NVL (
TRIM (
hr_general.decode_lookup (
'IE_COUNTY',
TRIM (pact_ad.action_information9)
)
||' '||
hr_general.decode_lookup (
'IE_POSTAL_CODE',
TRIM (pact_ad.action_information12)
)
),
' '
), --ADDRESS LINE 4
NVL (TO_CHAR (pps.date_start, 'dd-mm-yyyy'), '31-12-4712'), -- HIRE_DATE
/*Bug 4154171*/
ptp.period_type, --PERIOD_TYPE
DECODE (
TO_CHAR (
NVL (
pps.actual_termination_date,
TO_DATE ('31-12-4712', 'DD-MM-YYYY')
),
'YYYY'
),
TO_CHAR (pay_ie_p35.get_end_date, 'RRRR') --Bug fix 3745861
, TO_CHAR (pps.actual_termination_date, 'dd-mm-yyyy'),
'31-12-4712'
), -- TERM_DATE
NVL (TRIM (RPAD (ppf.per_information1, 30)), ' '), -- MOTHERS_NAME
DECODE (SIGN (TO_NUMBER (p_arch_net_tax)), -1, 'H9', 1, 'J7'), --Q1_PR_Indicator
NVL (
ROUND (
TO_NUMBER (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
'_ASG_YTD',
'IE P45 Pay'
),
p_rr_action
)
),
2
),
0
), -- Q1_Previous_Emp_Pay
NVL (
ROUND (
TO_NUMBER (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
'_ASG_YTD',
'IE P45 Tax Deducted'
),
p_rr_action
)
),
2
),
0
) -- Q1_Previous_Emp_Tax
FROM pay_action_information pact_edi,
pay_action_information pact_iedi,
pay_action_information pact_ad,
per_assignments_f paf,
per_periods_of_service pps,
-- pay_ie_paye_details_f payef,
per_time_periods ptp,
per_people_f ppf
WHERE pact_iedi.action_information_category = 'IE EMPLOYEE DETAILS'
AND pact_iedi.action_context_type = 'AAP'
AND pact_iedi.action_context_id = p_locked_action
AND pact_edi.action_information_category = 'EMPLOYEE DETAILS'
AND pact_edi.action_context_type = 'AAP'
AND pact_edi.action_context_id = p_locked_action
AND pact_ad.action_information_category = 'ADDRESS DETAILS'
AND pact_ad.action_context_type = 'AAP'
AND pact_ad.action_information14 = 'Employee Address'
AND pact_ad.action_context_id = p_locked_action
AND ptp.time_period_id =
TO_NUMBER (pact_edi.action_information16)
AND paf.assignment_id = pact_ad.assignment_id
--AND paf.primary_flag = 'Y'
AND paf.effective_end_date =
(SELECT MAX (asg.effective_end_date)
FROM per_assignments_f asg
WHERE asg.assignment_id = paf.assignment_id
AND asg.effective_start_date <=
l_end_date --pay_ie_p35.get_start_date()
AND asg.effective_end_date >=
l_start_date -- pay_ie_p35.get_end_date()
)
AND paf.period_of_service_id = pps.period_of_service_id
AND paf.person_id = pps.person_id
AND ppf.person_id = paf.person_id
AND ppf.effective_end_date =
(SELECT MAX (per.effective_end_date)
FROM per_people_f per
WHERE per.person_id = ppf.person_id
AND per.effective_start_date <=
l_end_date --pay_ie_p35.get_start_date()
AND per.effective_end_date >=
l_start_date --pay_ie_p35.get_end_date()
);
SELECT NVL (SUBSTR (ppf.national_identifier, 1, 9), ' '), -- PPSN
-- for bug 5301598, increased the size to 12
NVL (SUBSTR (paf.assignment_number, 1, 12), ' '), --WORKS NUMBER
paf.person_id, -- FOR CALCULATION
paf.primary_flag, -- changes made
paf.assignment_id, -- FOR CALCULATION
paa.assignment_action_id, -- FOR CALCULATION
DECODE (
SIGN (
NVL (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made --6633719
'IE Net Tax'
),
p_rr_action, -- paa.assignment_action_id,
l_segment4, -- paye reference value
null,
p_context_id, --null,
p_context_value, --null,
null,
null,
null,
'TRUE'
),
0
)
),
-1, '1',
1, '0',
'0'
), --TAX OR REFUND
/*Bug 4154171*/
/*DECODE (
payef.tax_basis,
'IE_EMERGENCY', '2',
NULL, '2',
DECODE (payef.info_source, 'IE_P45', '1', '0')
), --TAX_DEDUCTION_BASIS*/
TRIM (RPAD (ppf.last_name, 20)), --SURNAME
/* Bug 4560952*/
NVL (TRIM (RPAD (ppf.first_name||' '||ppf.middle_names, 20)), ' '), --FIRST_NAME
TO_CHAR (ppf.date_of_birth, 'dd-mm-yyyy'), --DOB
NVL (TRIM (RPAD (pad.address_line1, 30)), ' '), --ADDRESS_LINE1
NVL (TRIM (pad.address_line2), ' '), --ADDRESS_LINE2
NVL (TRIM (pad.address_line3), ' '), --ADDRESS_LINE3
NVL (TRIM (hr_general.decode_lookup ('IE_COUNTY',
TRIM (pad.region_1)
)
||' '|| (
hr_general.decode_lookup (
'IE_POSTAL_CODE',
TRIM (pad.postal_code)
)
)
),
' '
), --ADDRESS_LINE4
TO_CHAR (pps.date_start, 'dd-mm-yyyy'), --HIRE_DATE
DECODE (
TO_CHAR (
NVL (
pps.actual_termination_date,
TO_DATE ('31-12-4712', 'DD-MM-YYYY')
),
'YYYY'
),
TO_CHAR (l_end_date, 'YYYY'), TO_CHAR (
pps.actual_termination_date,
'dd-mm-yyyy'
),
'31-12-4712'
), --TERM_DATE
papf.period_type, --PERIOD_TYPE
/*Bug 4154171*/
/*TO_CHAR (
NVL (
DECODE (
papf.period_type,
'Lunar Month', ROUND (
(payef.weekly_tax_credit * 52),
2
),
DECODE (
INSTR (papf.period_type, 'Week'),
0, ROUND (
(payef.monthly_tax_credit * 12),
2
),
ROUND (
(payef.weekly_tax_credit * 52),
2
)
)
),
0
)
), --ANNUAL_TAX_CREDIT*/
NVL (TRIM (RPAD (ppf.per_information1, 30)), ' '), --MOTHERS_NAME
DECODE (
SIGN (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made--6633719
'IE Net Tax'
),
p_rr_action, --paa.assignment_action_id,
l_segment4, -- paye reference value
null,
p_context_id, --null,
p_context_value, --null,
null,
null,
null,
'TRUE'
)
),
-1, 'H9',
1, 'J7'
), --Q1_PR_Indicator
NVL (
ROUND (
TO_NUMBER (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
'_ASG_YTD',
'IE P45 Pay'
),
p_rr_action
)
),
2
),
0
), -- Q1_Previous_Emp_Pay
NVL (
ROUND (
TO_NUMBER (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
'_ASG_YTD',
'IE P45 Tax Deducted'
),
p_rr_action
)
),
2
),
0
) -- Q1_Previous_Emp_Tax
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
per_people_f ppf,
per_periods_of_service pps,
per_assignments_f paf,
per_addresses pad,
-- pay_ie_paye_details_f payef, --Bug 4154171
pay_all_payrolls_f papf,
pay_payroll_actions ppa
WHERE pai.locking_action_id = p_locked_action
AND paa.assignment_action_id = pai.locked_action_id
-- Added for bug 5874653
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date BETWEEN nvl(pad.date_from,ppa.effective_date) AND nvl(pad.date_to,ppa.effective_date)
-- end bug 5874653
AND paa.source_action_id IS NULL
AND paf.assignment_id = paa.assignment_id
--AND paf.primary_flag = 'Y'
AND ppf.person_id = paf.person_id
AND pad.person_id(+) = paf.person_id
AND NVL (pad.primary_flag, 'Y') = 'Y'
AND paf.effective_end_date =
(SELECT MAX (asg.effective_end_date)
FROM per_assignments_f asg
WHERE asg.assignment_id = paf.assignment_id
AND asg.effective_start_date <= l_end_date
AND asg.effective_end_date >= l_start_date)
AND ppf.effective_end_date =
(SELECT MAX (per.effective_end_date)
FROM per_people_f per
WHERE per.person_id = ppf.person_id
AND per.effective_start_date <= l_end_date
AND per.effective_end_date >= l_start_date)
AND paf.period_of_service_id = pps.period_of_service_id
AND paf.person_id = pps.person_id
AND papf.payroll_id = paf.payroll_id
AND papf.effective_end_date =
(SELECT MAX (papf1.effective_end_date)
FROM pay_all_payrolls_f papf1
WHERE papf1.payroll_id = papf.payroll_id
AND papf1.effective_start_date <= l_end_date
AND papf1.effective_end_date >= l_start_date);
SELECT NVL (SUBSTR (ppf.national_identifier, 1, 9), ' '), --PPSN
-- for bug 5301598, increased the size to 12
NVL (SUBSTR (paf.assignment_number, 1, 12), ' '), -- WORKS NUMBER
paf.person_id, -- FOR CALCULATION
paf.primary_flag, -- changes made
paa.assignment_id, -- FOR CALCULATION
paa.assignment_action_id, -- FOR CALCULATION
DECODE (
SIGN (
NVL (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made--6633719
'IE Net Tax'
),
paa.assignment_action_id,
l_segment4, -- paye reference value
null,
p_context_id, --null,
p_context_value, --null,
null,
null,
null,
'TRUE'
),
0
)
),
-1, '1',
1, '0',
'0'
), --TAX_OR_REFUND
/*Bug 4154171*/
/* DECODE (
payef.tax_basis,
'IE_EMERGENCY', '2',
NULL, '2',
DECODE (payef.info_source, 'IE_P45', '1', '0')
), --TAX_DEDUCTION_BASIS*/
TRIM (RPAD (ppf.last_name, 20)), --SURNAME
/*Bug 4560952*/
NVL (TRIM (RPAD (ppf.first_name||' '||ppf.middle_names, 20)), ' '), --FIRST_NAME
TO_CHAR (ppf.date_of_birth, 'dd-mm-yyyy'), -- DOB
NVL (TRIM (RPAD (pad.address_line1, 30)), ' '), -- ADDRESS_LINE1
NVL (TRIM (pad.address_line2), ' '), -- ADDRESS_LINE2
NVL (TRIM (pad.address_line3), ' '), -- ADDRESS_LINE3
NVL (
TRIM (
hr_general.decode_lookup (
'IE_COUNTY',
TRIM (pad.region_1)
)
||' '||
hr_general.decode_lookup (
'IE_POSTAL_CODE',
TRIM (pad.postal_code)
)
),
' '
), -- ADDRESS_LINE4
TO_CHAR (pps.date_start, 'dd-mm-yyyy'), -- HIRE_DATE
DECODE (
TO_CHAR (
NVL (
pps.actual_termination_date,
TO_DATE ('31-12-4712', 'DD-MM-YYYY')
),
'YYYY'
),
TO_CHAR (l_end_date, 'YYYY'), TO_CHAR (
pps.actual_termination_date,
'dd-mm-yyyy'
),
'31-12-4712'
), -- TERM_DATE
papf.period_type, --PERIOD_TYPE
/*Bug 4154171*/
/* TO_CHAR (
NVL (
DECODE (
papf.period_type,
'Lunar Month', ROUND (
(payef.weekly_tax_credit * 52),
2
),
DECODE (
INSTR (papf.period_type, 'Week'),
0, ROUND (
(payef.monthly_tax_credit * 12),
2
),
ROUND (
(payef.weekly_tax_credit * 52),
2
)
)
),
0
)
), -- ANNUAL_TAX_CREDIT*/
NVL (TRIM (RPAD (ppf.per_information1, 30)), ' '), -- MOTHERS_NAME
DECODE (
SIGN (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
p_dimension_name, ---'_PER_PAYE_REF_YTD', -- changes made--6633719
'IE Net Tax'
),
paa.assignment_action_id,
l_segment4, -- paye reference value
null,
p_context_id, --null,
p_context_value, --null,
null,
null,
null,
'TRUE'
)
),
-1, 'H9',
1, 'J7'
), --Q1_PR_Indicator
NVL (
ROUND (
TO_NUMBER (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
'_ASG_YTD',
'IE P45 Pay'
),
p_locked_action
)
),
2
),
0
), -- Q1_Previous_Emp_Pay
NVL (
ROUND (
TO_NUMBER (
pay_balance_pkg.get_value (
pay_ie_p35.get_defined_balance_id (
'_ASG_YTD',
'IE P45 Tax Deducted'
),
p_locked_action
)
),
2
),
0
) -- Q1_Previous_Emp_Tax
FROM pay_assignment_actions paa,
per_people_f ppf,
per_periods_of_service pps,
per_assignments_f paf,
per_addresses pad,
-- pay_ie_paye_details_f payef,
pay_all_payrolls_f papf,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_locked_action
-- Added for bug 5874653
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date BETWEEN nvl(pad.date_from,ppa.effective_date) AND nvl(pad.date_to,ppa.effective_date)
-- end bug 5874653
AND paf.assignment_id = paa.assignment_id
--AND paf.primary_flag = 'Y'
AND ppf.person_id = paf.person_id
AND pad.person_id(+) = paf.person_id
AND NVL (pad.primary_flag, 'Y') = 'Y'
AND paf.effective_end_date =
(SELECT MAX (asg.effective_end_date)
FROM per_assignments_f asg
WHERE asg.assignment_id = paf.assignment_id
AND asg.effective_start_date <= l_end_date
AND asg.effective_end_date >= l_start_date)
AND ppf.effective_end_date =
(SELECT MAX (per.effective_end_date)
FROM per_people_f per
WHERE per.person_id = ppf.person_id
AND per.effective_start_date <= l_end_date
AND per.effective_end_date >= l_start_date)
AND paf.period_of_service_id = pps.period_of_service_id
AND paf.person_id = pps.person_id
/* AND payef.assignment_id(+) = paa.assignment_id
AND ( payef.effective_end_date =
(SELECT MAX (paye.effective_end_date)
FROM pay_ie_paye_details_f paye
WHERE paye.assignment_id = paf.assignment_id
AND paye.effective_start_date <= l_end_date
AND paye.effective_end_date >= l_start_date)
OR payef.effective_end_date IS NULL
)*/
AND papf.payroll_id = paf.payroll_id
AND papf.effective_end_date =
(SELECT MAX (papf1.effective_end_date)
FROM pay_all_payrolls_f papf1
WHERE papf1.payroll_id = papf.payroll_id
AND papf1.effective_start_date <= l_end_date
AND papf1.effective_end_date >= l_start_date);
SELECT DECODE (
paye.tax_basis,
'IE_EMERGENCY', '2',
NULL, '2',
DECODE (paye.info_source, 'IE_P45', '1', '0')
), --TAX_DEDUCTION_BASIS
NVL ( DECODE (
ptp.period_type,
'Lunar Month', ROUND (
(paye.weekly_tax_credit * 52),
2
),
DECODE (
INSTR (ptp.period_type, 'Week'),
0, ROUND (
(paye.monthly_tax_credit * 12),
2
),
ROUND (
(paye.weekly_tax_credit * 52),
2
)
)
),
0) --Bug 4111753
/*( NVL(paye.weekly_tax_credit,0) +
NVL(paye.Monthly_tax_credit,0)) * ptp.period_num */
FROM per_assignments_f paf,
per_time_periods ptp,
pay_ie_paye_details_f paye
WHERE paf.assignment_id = p_assignment_id
AND paye.assignment_id=paf.assignment_id
AND p_term_date between paf.effective_start_date
and paf.effective_end_date
-- Bug 6774415 changed effective date to certificate date
AND p_term_date between paye.certificate_start_date
and NVL(paye.certificate_end_date,to_date('31/12/4712','DD/MM/YYYY'))
AND paf.payroll_id = ptp.payroll_id
AND p_term_date between ptp.start_date and ptp.end_date
-- Bug 6774415 order by eff date to handle overlapping certificates
ORDER BY paye.effective_start_date DESC;
SELECT DECODE (
payef.tax_basis,
'IE_EMERGENCY', '2',
NULL, '2',
DECODE (payef.info_source, 'IE_P45', '1', '0')
), --TAX_DEDUCTION_BASIS
TO_CHAR (
NVL (
DECODE (
p_period_type,
'Lunar Month', ROUND (
(payef.weekly_tax_credit * 52),
2
),
DECODE (
INSTR (p_period_type, 'Week'),
0, ROUND (
(payef.monthly_tax_credit * 12),
2
),
ROUND (
(payef.weekly_tax_credit * 52),
2
)
)
),
0
)
) -- ANNUAL_TAX_CREDIT
FROM pay_ie_paye_details_f payef
WHERE payef.assignment_id=p_assignment_id
-- Bug 6774415 changed effective date to certificate date
AND payef.certificate_start_date <= l_end_date
AND NVL(payef.certificate_end_date,to_date('31/12/4712','DD/MM/YYYY')) >= l_start_date
ORDER BY payef.effective_end_date desc;
select payroll_action_id,assignment_id,chunk_number
from pay_assignment_actions
where assignment_action_id = p_assactid;
/*SELECT period_of_service_id
FROM per_periods_of_service pps
WHERE person_id = v_person_id
AND actual_termination_date is not NULL
AND actual_termination_date between l_start_date
and l_end_date
ORDER BY actual_termination_date desc; */
SELECT max(pps.period_of_service_id)
FROM per_periods_of_service pps
,per_assignments_f asg
,pay_all_payrolls_f pay
,hr_soft_coding_keyflex flex
WHERE pps.person_id = v_person_id
AND pps.person_id = asg.person_id
AND asg.period_of_service_id <> pps.period_of_service_id
AND asg.assignment_id = v_assignment_id
AND asg.payroll_id = pay.payroll_id
AND pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment4 = l_segment4
AND actual_termination_date IS NOT NULL
AND actual_termination_date BETWEEN l_start_date
AND l_end_date;
SELECT assignment_id
FROM per_assignments_f
WHERE period_of_service_id = p_pds_id
AND primary_flag = 'Y';*/
SELECT max(paa.assignment_action_id)
FROM pay_assignment_Actions paa,
pay_payroll_actions ppa
-- ,per_time_periods ptp -- removed to improve performance 4771780
WHERE paa.assignment_id in (SELECT assignment_id
FROM per_assignments_f
WHERE period_of_service_id = p_pds_id)
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('R','Q','I','B','V')
AND paa.action_status = 'C'
/* Impact of time period */
-- AND ptp.time_period_id = ppa.time_period_id
-- Removed ptp to improve the performance 4771780
-- AND ppa.payroll_id = ptp.payroll_id
-- AND ppa.date_earned between ptp.start_date and ptp.end_date
-- AND ptp.end_Date BETWEEN l_start_date
-- AND l_end_date
AND ppa.effective_date between l_start_date and l_end_date;
select NVL (SUBSTR (paf.assignment_number, 1, 12), ' '), -- WORKS NUMBER
paf.primary_flag,
paf.assignment_id
from per_assignments_f paf,
pay_assignment_actions paa
where paf.assignment_id = paa.assignment_id
and paa.assignment_action_id = p_action_id
and paf.effective_start_date <= l_end_date
and paf.effective_end_date >= l_start_date;
select NVL (SUBSTR (paf.assignment_number, 1, 12), ' '), -- WORKS NUMBER
paf.primary_flag,
paf.assignment_id,
max(effective_end_date) end_date
from per_assignments_f paf,
pay_assignment_actions paa
where paf.assignment_id = paa.assignment_id
and paa.assignment_action_id = p_action_id
and paf.effective_start_date <= l_end_date
and paf.effective_end_date >= l_start_date
group by NVL (SUBSTR (paf.assignment_number, 1, 12), ' '), paf.primary_flag, paf.assignment_id
having max(effective_end_date) >= l_end_date;
select NVL (SUBSTR (paf.assignment_number, 1, 12), ' ') -- WORKS NUMBER
from per_assignments_f paf, per_assignment_extra_info paei
where paf.assignment_id = paei.assignment_id
and aei_information1 = p_ppsn_override
and paf.effective_start_date <= l_end_date
and paf.effective_end_date >= l_start_date
and primary_flag = 'Y'
group by NVL (SUBSTR (paf.assignment_number, 1, 12), ' ');
select NVL (SUBSTR (paf.assignment_number, 1, 12), ' ') -- WORKS NUMBER
from per_assignments_f paf,
per_assignment_extra_info paei
where paf.assignment_id = paei.assignment_id
and aei_information1 = p_ppsn_override
and paf.effective_start_date <= l_end_date
and paf.effective_end_date >= l_start_date
and primary_flag = 'Y'
group by NVL (SUBSTR (paf.assignment_number, 1, 12), ' ')
having max(effective_end_date) >= l_end_date;
select aei_information1 PPSN_OVERRIDE
from per_assignment_extra_info
where assignment_id = p_asg_id
and aei_information_category = 'IE_ASG_OVERRIDE';
SELECT paa.assignment_action_id child_assignment_action_id
--,prt.run_method run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) child_assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_asg_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null or ppa.action_type in ('I','V'))
AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V')
AND paa.action_status = 'C');
SELECT pac.context_id, pac.context_value
FROM pay_action_contexts pac, ff_contexts ffc
WHERE pac.assignment_action_id = p_source_id
AND ffc.context_name = 'SOURCE_TEXT'
AND ffc.context_id = pac.context_id;
SELECT DECODE (
SIGN (TO_NUMBER (NVL (l_arch_net_tax, 0))),
-1, '1',
1, '0',
'0'
)
INTO l_arch_tax_or_refund
FROM DUAL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
hr_utility.set_location('Before Update',1009);
/* update assignment actions */
IF v_assignment_id <> l_max_assignment_id THEN
UPDATE pay_assignment_actions SET assignment_id = l_max_assignment_id
WHERE assignment_action_id = p_assactid
AND payroll_action_id= v_payroll_action_id;
UPDATE pay_assignment_actions SET assignment_id = l_max_assignment_id
WHERE source_action_id = p_assactid
AND payroll_action_id = v_payroll_action_id;
hr_utility.set_location('After Update',1009);
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = p_payroll_action_id
AND pai.action_context_type = 'PA'
AND rownum = 1
);
SELECT hoi2.org_information2, -- 'EMPLOYER_NUMBER
--nvl(trim(rpad(hou.name,30)),' '), -- ('EMPLOYER_NAME=P'),
--Added for bug fix 3567562,Modifed source of Employer Name
NVL (TRIM (RPAD (hou.name, 30)), ' '), -- ('EMPLOYER_NAME=P'),
NVL (TRIM (RPAD (hoi2.org_information3, 30)), ' '), -- ('TRADE_NAME=P'), /*Added for bug fix 3815830*/
NVL (TRIM (RPAD (hl.ADDRESS_LINE_1, 30)), ' '), -- ('EMPLOYER_ADDRESS1=P'),
NVL (TRIM (RPAD (hl.ADDRESS_LINE_2, 30)), ' '), -- ('EMPLOYER_ADDRESS2=P'),
NVL (TRIM (RPAD (hl.ADDRESS_LINE_3, 30)), ' '), -- ('EMPLOYER_ADDRESS3=P'),
NVL (TRIM (RPAD (hoi2.org_information4, 20)), ' '), -- ('CONTACT_NAME=P'),
NVL (TRIM (RPAD (hl.TELEPHONE_NUMBER_1, 12)), ' ' ), -- ('CONTACT_NUMBER=P'),
NVL (TRIM (RPAD (hl.TELEPHONE_NUMBER_2, 12)), ' ') --('FAX_NO=P') /*Added for bug fix 3815830*/
FROM hr_all_organization_units hou,
hr_locations hl,
pay_payroll_actions ppa,
hr_organization_information hoi1,
hr_organization_information hoi2
WHERE ppa.payroll_action_id = p_payroll_action_id
AND hou.business_group_id = ppa.business_group_id
AND hou.organization_id = pay_ie_p35.get_parameter (ppa.payroll_action_id, 'EMP_NO')
AND hl.location_id(+) = hou.location_id
AND hou.organization_id=hoi1.organization_id
AND hoi2.organization_id(+)= hoi1.organization_id
AND hoi1.org_information_context='CLASS'
AND hoi1.org_information1='HR_LEGAL_EMPLOYER'
AND hoi1.org_information2='Y'
AND hoi2.org_information_context (+) ='IE_EMPLOYER_INFO';
SELECT context_id
FROM ff_contexts
WHERE context_name = 'SOURCE_TEXT';
SELECT /*+ ORDERED USE_NL(paa, ppa, ptp)
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) assignment_action_id --bug fix 4004470
FROM per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE
ppf.person_id = p_person_id
AND paf.assignment_id = p_assignment_id -- vik added code for getting values for given assignment
AND ppf.current_employee_flag = 'Y'
AND paf.person_id = ppf.person_id
--AND paf.primary_flag = 'Y' -- removed join
AND paf.assignment_type = 'E'
AND paf.assignment_status_type_id =
--Added for bug fix 3828506
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status = 'ACTIVE_ASSIGN'
AND ast.assignment_status_type_id = paf.assignment_status_type_id)
AND paa.assignment_id = paf.assignment_id
AND paa.action_status = 'C'
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R', 'Q', 'I', 'B')
AND ptp.time_period_id = ppa.time_period_id
AND ptp.end_date BETWEEN p_start_date AND p_end_date
group by paa.assignment_id;*/
SELECT /*+ ordered */
asg.business_group_id business_group_id,
asg.person_id person_id, per.full_name full_name,
per.original_date_of_hire original_hire_date,
MIN (ptp.end_date) minimum_effective_date,
asg.primary_flag,
paa.assignment_action_id,
trim(rrv1.result_value) result_value
FROM per_people_f per,
per_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr,
pay_element_types_f pet,
pay_input_values_f piv1,
pay_run_result_values rrv1,
per_assignment_extra_info paei --6633719
WHERE per.person_id = p_person_id
--6633719 these condition added to fetch the contexes having similar PPSN OVERRIDE if given
AND paei.assignment_id(+) = asg.assignment_id
AND paei.aei_information_category(+) = 'IE_ASG_OVERRIDE'
AND nvl(paei.aei_information1,'X') = nvl(p_ppsn_override,'X')
--6633719
--AND asg.assignment_id = p_assignment_id -- vik added code for getting values for given assignment
AND per.current_employee_flag = 'Y'
AND per.effective_start_date =
(SELECT MIN (per2.effective_start_date)
FROM per_people_f per2
-- Bug Fix 4004384
, per_periods_of_service pos2
WHERE per2.person_id = per.person_id
AND per2.effective_start_date <= p_end_date
AND pos2.person_id = per2.person_id
AND pos2.date_start between per2.effective_start_date and per2.effective_end_date
AND NVL (pos2.final_process_date, p_end_date) >=p_start_date
-- AND NVL (per2.effective_end_date, p_end_date) >=p_start_date
--Added for bug fix 3828506
AND per2.current_employee_flag = 'Y' )
AND asg.person_id = per.person_id
AND asg.effective_start_date =
(SELECT MIN (asg2.effective_start_date)
FROM per_assignments_f asg2,
per_assignment_status_types ast --Bug fix 3828506
WHERE asg2.assignment_id = asg.assignment_id
AND asg2.effective_start_date <= p_end_date
AND NVL (asg2.effective_end_date, p_end_date) >= p_start_date
--Added for bug fix 3828506
--AND asg2.primary_flag = 'Y' ---- removed join
AND asg2.assignment_type = 'E'
AND asg2.assignment_status_type_id = ast.assignment_status_type_id )
-- Bug Fix 4004384
-- AND ast.pay_system_status ='P') -- Bug Fix 4025532
-- AND ast.per_system_status ='ACTIVE_ASSIGN')
--AND asg.primary_flag = 'Y' -- removed join
AND asg.assignment_type = 'E'
--Bug Fix 3828506
AND paa.assignment_id = asg.assignment_id
AND paa.action_status = 'C'
AND paa.tax_unit_id = l_segment4
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q', 'R', 'B')
/* impact of tim period */
-- AND ppa.time_period_id = ptp.time_period_id
AND ppa.payroll_id = ptp.payroll_id
AND ppa.date_earned between ptp.start_date and ptp.end_date
and ppa.effective_date between p_start_date and p_end_date
--
--AND ptp.end_date BETWEEN p_start_date AND p_end_date -- Bug 5070091 Offset payroll change
AND pet.element_name IN ('IE PRSI Contribution Class','Setup PRSI Context Element') /* 5763147 */
AND pet.legislation_code = 'IE'
/* AND pet.effective_start_date =
(SELECT MAX (pet2.effective_start_date)
FROM pay_element_types_f pet2
WHERE pet.element_type_id = pet2.element_type_id
AND pet2.effective_start_date <= p_end_date
AND NVL (pet2.effective_end_date, p_end_date) >=
p_start_date)*/
AND pet.element_type_id = piv1.element_type_id
AND piv1.NAME IN ('Contribution_Class','Context Contribution Class') /* 5763147 */
AND piv1.legislation_code = 'IE'
/* AND piv1.effective_start_date =
(SELECT MAX (piv1a.effective_start_date)
FROM pay_input_values_f piv1a
WHERE piv1.input_value_id = piv1a.input_value_id
AND piv1a.effective_start_date <= p_end_date
AND NVL (piv1a.effective_end_date, p_end_date) >=
p_start_date)*/
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND rrv1.input_value_id = piv1.input_value_id
AND rrv1.run_result_id = prr.run_result_id
GROUP BY asg.business_group_id,
asg.person_id,
per.full_name,
per.original_date_of_hire,
asg.primary_flag,
paa.assignment_action_id,
trim(rrv1.result_value)
ORDER BY asg.primary_flag desc,minimum_effective_date,paa.assignment_action_id;
SELECT /*+ ordered */
TO_NUMBER (
MAX (
DECODE (
piv1.NAME,
'Insurable Weeks', rrv1.result_value,
'0'
)
)
) weeks,
MAX (
DECODE (
piv1.NAME,
'Context Contribution Class', rrv1.result_value,
'0'
)
) class_name
FROM per_people_f per,
per_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr,
pay_element_types_f pet,
pay_input_values_f piv1,
pay_run_result_values rrv1
WHERE per.person_id = p_person_id
--ANd asg.assignment_id = p_assignment_id -- vik added code for getting values for given assignment
AND per.effective_start_date =
(SELECT MIN (per2.effective_start_date)
FROM per_people_f per2
WHERE per.person_id = per2.person_id
AND per2.effective_start_date <= p_end_date
AND NVL (per2.effective_end_date, p_end_date) >=
p_start_date
--Added for bug fix 3828506
AND per2.current_employee_flag = 'Y')
AND per.current_employee_flag = 'Y'
AND asg.person_id = per.person_id
--AND asg.primary_flag = 'Y' -- removed join
AND asg.assignment_type = 'E'
AND asg.assignment_status_type_id =
( SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status ='ACTIVE_ASSIGN'
AND ast.assignment_status_type_id = asg.assignment_status_type_id)
AND asg.effective_start_date =
(SELECT MIN (asg2.effective_start_date)
FROM per_assignments_f asg2,
per_assignment_status_types ast --For bug fix 3828506
WHERE asg2.assignment_id = asg.assignment_id
AND asg2.effective_start_date <= p_end_date
AND NVL (asg2.effective_end_date, p_end_date) >= p_start_date
--Added for bug fix 3828506
--AND asg2.primary_flag = 'Y' -- removed join
AND asg2.assignment_type = 'E'
AND asg2.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status ='ACTIVE_ASSIGN')
AND paa.assignment_id = asg.assignment_id
AND paa.action_status = 'C'
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type = 'I'
/* time period impact */
-- AND ppa.time_period_id = ptp.time_period_id
AND ppa.payroll_id = ptp.payroll_id
AND ppa.date_earned between ptp.start_date and ptp.end_date
AND ppa.effective_date between p_start_date and p_end_date
--
--AND ptp.end_date BETWEEN p_start_date AND p_end_date -- Bug 5070091 Offset payroll change
AND paa.assignment_action_id = prr.assignment_action_id
AND pet.element_name = 'Setup PRSI Context Element'
AND pet.legislation_code = 'IE'
/* AND pet.effective_start_date =
(SELECT MAX (pet2.effective_start_date)
FROM pay_element_types_f pet2
WHERE pet.element_type_id = pet2.element_type_id
AND pet2.effective_start_date <= p_end_date
AND NVL (pet2.effective_end_date, p_end_date) >=
p_start_date)
*/
AND pet.element_type_id = piv1.element_type_id
AND piv1.NAME IN
('Insurable Weeks', 'Context Contribution Class')
AND piv1.legislation_code = 'IE'
/* AND piv1.effective_start_date =
(SELECT MAX (piv1a.effective_start_date)
FROM pay_input_values_f piv1a
WHERE piv1.input_value_id = piv1a.input_value_id
AND piv1a.effective_start_date <= p_end_date
AND NVL (piv1a.effective_end_date, p_end_date) >=
p_start_date)*/
AND prr.element_type_id = pet.element_type_id
AND rrv1.run_result_id = prr.run_result_id
AND rrv1.input_value_id = piv1.input_value_id
GROUP BY prr.run_result_id
HAVING MAX (
DECODE (
piv1.NAME,
'Context Contribution Class', rrv1.result_value,
'0'
)
) <> '0'
ORDER BY weeks DESC;
SELECT SUBSTR (piw.combined_class, 1, 2),
NVL (piw.insurable_weeks, 0)
FROM pay_ie_p35_insurable_weeks_v piw
WHERE piw.person_id =
(SELECT asg.person_id
FROM per_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND asg.effective_start_date =
(SELECT MIN (asg2.effective_start_date)
FROM per_assignments_f asg2
WHERE asg.assignment_id =
asg2.assignment_id))
AND piw.insurable_weeks =
(SELECT MAX (piw2.insurable_weeks)
FROM pay_ie_p35_insurable_weeks_v piw2
WHERE piw2.person_id = piw.person_id
AND SUBSTR (piw2.combined_class, 1, 2) <>
RPAD (l_initial_class, 2))
AND piw.minimum_effective_date =
(SELECT MIN (piw3.minimum_effective_date)
FROM pay_ie_p35_insurable_weeks_v piw3
WHERE piw3.person_id = piw.person_id
AND piw3.insurable_weeks = piw.insurable_weeks
AND SUBSTR (piw3.combined_class, 1, 2) <>
RPAD (l_initial_class, 2));
SELECT fnd_date.canonical_to_date (
SUBSTR (fpov.profile_option_value, 1, 4)
|| '01/01 00:00:00'
)
INTO l_start_date
FROM fnd_profile_option_values fpov, fnd_profile_options fpo
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.application_id = fpov.application_id
AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
AND fpov.level_id = 10001
AND fpov.level_value = 0;
SELECT fnd_date.canonical_to_date (
SUBSTR (fpov.profile_option_value, 1, 4)
|| '12/31 23:59:59'
)
INTO l_end_date
FROM fnd_profile_option_values fpov, fnd_profile_options fpo
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.application_id = fpov.application_id
AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
AND fpov.level_id = 10001
AND fpov.level_value = 0;
select hasa.include_or_exclude from hr_assignment_set_amendments hasa, hr_assignment_sets has
where hasa.assignment_set_id = has.assignment_set_id
and has.business_group_id = p_business_group
and has.assignment_set_id = p_assignment_set_id
and hasa.assignment_id = p_assignment_id;
select distinct hasa.include_or_exclude from hr_assignment_set_amendments hasa, hr_assignment_sets has
where hasa.assignment_set_id = has.assignment_set_id
and has.business_group_id = p_business_group
and has.assignment_set_id = p_assignment_set_id;