[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 bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT NVL (MIN (ppa.payroll_action_id), 0)
FROM pay_payroll_actions ppa
WHERE ppa.report_type = 'IEP35'
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;
' 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 1 from dual where to_char(:payroll_action_id) = dummy';
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 DISTINCT paa.assignment_id,paa.assignment_action_id,paa.payroll_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_information pai,
per_assignments_f paaf,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex flex
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.action_type ='X'
AND ppa.business_group_id = l_bg_id
AND paa.source_action_id IS NULL
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_information_category = 'IE P35 DETAIL'
AND ppa.report_type = 'IEP35'
AND paa.assignment_id = pai.assignment_id
AND paaf.assignment_id = paa.assignment_id
--AND paaf.primary_flag = 'Y'
AND paaf.business_group_id = ppa.business_group_id
AND paaf.payroll_id = ppf.payroll_id
AND ppf.effective_start_date <= l_end_date
AND ppf.effective_end_date >= l_start_date
AND flex.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
AND flex.segment4 = l_segment4
AND paaf.person_id BETWEEN stperson AND endperson
AND paaf.effective_start_date <= l_end_date
AND paaf.effective_end_date >= l_start_date
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 (ppf.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 = paaf.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)
--AND (ppf.payroll_id =l_payroll_id or l_payroll_id is null)
--AND PAY_IE_P35.check_assignment_in_set(paa.assignment_id,l_assignment_set_id,l_bg_id)=1;
AND (l_set_flag ='I' AND EXISTS(SELECT 1
FROM hr_assignment_set_amendments hasa
, hr_assignment_sets has
, per_assignments_f paf
--, pay_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 = paaf.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_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 = paaf.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 = 'IEP35MAG'
AND paa_p35.action_status = 'C'
AND TO_DATE (
pay_ie_p35.get_parameter (
ppa_p35.payroll_action_id,
'END_DATE'
),
'YYYY/MM/DD'
) BETWEEN l_start_date AND l_end_date
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 = paaf.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=paaf.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);
hr_utility.set_location ('Selected assignment ::'|| c_rec.assignment_id || ' ::and action ::passed to action creation::'||
c_rec.assignment_action_id||'::',960);
hr_utility.set_location ('Selected payroll_action::'||c_rec.payroll_action_id ,960);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
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 count(decode(pact.action_information2,0,null,null,null,1)) EMP_RBS,
sum(to_number(pact.action_information2))*100 EMP_RBS_BAL,
count(decode(pact.action_information3,0,null,null,null,1)) EMPR_RBS,
sum(to_number(pact.action_information3))*100 EMPR_RBS_BAL,
count(decode(pact.action_information4,0,null,null,null,1)) EMP_PRSA,
sum(to_number(pact.action_information4))*100 EMP_PRSA_BAL,
count(decode(pact.action_information5,0,null,null,null,1)) EMPR_PRSA,
sum(to_number(pact.action_information5))*100 EMPR_PRSA_BAL,
count(decode(pact.action_information6,0,null,null,null,1)) EMP_RAC,
sum(to_number(pact.action_information6))*100 EMP_RAC_BAL,
sum(to_number(pact.action_information1))*100 TAXABLEBENEFITS
FROM pay_assignment_actions paa
,pay_action_information pact
,pay_action_interlocks pai
WHERE paa.payroll_action_id = p_payroll_action_id
AND paa.source_action_id IS NULL
AND pai.locking_action_id = paa.assignment_action_id
AND pact.action_context_id = pai.locked_action_id
--AND pact.action_context_id = paa.assignment_action_id
AND pact.action_information_category = 'IE P35 ADDITIONAL DETAILS'
AND pact.action_context_type = 'AAP';