[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (get_parameter
(legislative_parameters
, 'LEGAL_EMPLOYER'
)
) LEGAL_EMPLOYER_ID
, (get_parameter
(legislative_parameters
, 'LU_REQUEST'
)
) LU_REQUEST
,(get_parameter
(legislative_parameters
, 'LOCAL_UNIT'
)
) LOCAL_UNIT_ID
,(get_parameter
(legislative_parameters
, 'MONTH'
)
) L_MONTH
,(get_parameter
(legislative_parameters
, 'YEAR'
)
) L_YEAR
,FND_DATE.canonical_to_date((get_parameter
(legislative_parameters
, 'RETROACTIVE_PAYMENT_FROM'
)
)) L_RETROACTIVE_PAYMENT_FROM
,FND_DATE.canonical_to_date((get_parameter
(legislative_parameters
, 'RETROACTIVE_PAYMENT_TO'
)
)) L_RETROACTIVE_PAYMENT_TO
, effective_date, business_group_id bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
from HR_ORGANIZATION_UNITS o1
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
WHERE o1.business_group_id = g_business_group_id
and hoi1.organization_id = o1.organization_id
and hoi1.org_information1 = 'SE_LOCAL_UNIT'
and hoi1.org_information_context = 'CLASS'
and o1.organization_id = hoi2.organization_id
and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
and o1.organization_id = csr_local_unit_ID;
SELECT national_identifier, last_name || ' ' || first_name name ,DATE_OF_BIRTH
FROM
per_all_people_f WHERE
BUSINESS_GROUP_ID=g_business_group_id
AND person_id=csr_v_person_id
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND months_between (csr_v_end_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
AND months_between (csr_v_end_date,DATE_OF_BIRTH) < 768 /* and age less than 64 */
ORDER BY last_name || ' ' || first_name;
SELECT effective_start_date FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21233
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND primary_flag='Y'
AND employee_category='WC' ;
SELECT MAX(effective_start_date) FROM per_all_people_f papf WHERE
CURRENT_EMPLOYEE_FLAG IS NULL
AND person_id=csr_v_person_id--21257
AND EFFECTIVE_END_DATE --'31-dec-2000'
BETWEEN csr_v_start_date AND csr_v_end_date /*'01-jan-2000' AND csr_v_end_date '31-dec-2000'*/
AND NOT EXISTS
(SELECT 1 FROM per_all_people_f WHERE
CURRENT_EMPLOYEE_FLAG='Y'
AND person_id=papf.person_id --21257
AND effective_start_date >papf.effective_start_date
);
/*SELECT effective_start_date,effective_end_date,pj.JOB_INFORMATION1,employee_category
FROM
per_all_people_f papf,
per_jobs pj
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND primary_flag='Y'
AND pj.job_id=papf.job_id
AND papf.job_id IS NOT NULL
AND papf.emloyee_category IS NOT NULL*/
SELECT paaf.effective_start_date,paaf.effective_end_date,
decode(pj.JOB_INFORMATION1,'Y','M',null) job,paaf.employee_category,
payroll_id
FROM
per_all_assignments_f paaf,
per_jobs pj
WHERE person_id=csr_v_person_id --21197 --21257 --21233
AND csr_v_start_date <=paaf.EFFECTIVE_END_DATE AND
csr_v_end_date >=paaf.EFFECTIVE_START_DATE
AND primary_flag='Y'
AND pj.job_id(+)=paaf.job_id
-- AND paaf.employee_category IN ('BC','WC')
AND (paaf.job_id IS NOT NULL
OR paaf.employee_category IS NOT NULL);
/*SELECT JOB_INFORMATION1 FROM per_jobs pj, per_roles pr
WHERE pj.job_id=pr.job_id
AND pj.JOB_INFORMATION_CATEGORY='SE'
AND pr.person_id=csr_v_person_id; --21257 */
/*SELECT JOB_INFORMATION1,start_date, start_date+(e_date-start_date-1) end_date
FROM
(
SELECT JOB_INFORMATION1,start_date,lead( start_date, 1, to_date('31-12-4713','dd-mm-yyyy') )
over (order by start_date ASC) e_date
FROM per_jobs pj, per_roles pr
WHERE pj.job_id=pr.job_id
AND pj.JOB_INFORMATION_CATEGORY='SE'
AND pr.person_id=csr_v_person_id /*21197*/--)
/*WHERE start_date<=csr_v_end_date --'31-dec-2005'
AND start_date+(e_date-start_date-1)>=csr_v_start_date; /*'01-jan-2005'*/
SELECT DISTINCT employee_category ,EFFECTIVE_START_DATE
FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND primary_flag='Y'
ORDER BY EFFECTIVE_START_DATE;
SELECT MAX(EFFECTIVE_end_DATE) FROM
per_all_assignments_f
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND employee_category='BC'
AND primary_flag='Y';
/*SELECT employee_category FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21233
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND primary_flag='Y';*/
SELECT o1.NAME legal_employer_name
-- , hoi2.org_information2 org_number
-- , hoi1.organization_id legal_id
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id = g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_employer_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
SELECT papf.person_id --,paaf.assignment_id
FROM per_all_assignments_f paaf,
per_all_people_f papf,
hr_soft_coding_keyflex hsck
WHERE papf.business_group_id=csr_v_business_group_id -- 3133 --paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND papf.person_id=paaf.person_id
--AND paaf.primary_flag='Y'
AND hsck.segment2=to_char(csr_v_local_unit_id) --3268)
AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND months_between (csr_v_effective_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
AND months_between (csr_v_effective_date,DATE_OF_BIRTH) < 768
AND papf.CURRENT_EMPLOYEE_FLAG='Y'
AND paaf.employee_category IN ('WC','BC')
AND paaf.employment_category IN ('SE_VTR','SE_HW') --add one more type
ORDER BY papf.person_id;
SELECT paaf.assignment_id,
paaf.employee_category,
paaf.hourly_salaried_code,
hsck.SEGMENT9 working_percentage,
paaf.frequency,
paaf.normal_hours
FROM per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
WHERE paaf.person_id=csr_v_person_id
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.employee_category IN ('WC','BC')
AND paaf.employment_category IN ('SE_VTR','SE_HW') --add one more type
ORDER BY paaf.assignment_id;
SELECT greatest(fnd_Date.canonical_to_date(eev1.screen_entry_value),csr_v_start_date) start_date,
least(fnd_Date.canonical_to_date(eev2.screen_entry_value),csr_v_end_date) end_date
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_input_values_f iv2
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
,pay_element_entry_values_f eev2
WHERE asg1.assignment_id = csr_v_assignment_id
AND csr_v_end_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND csr_v_end_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
--AND asg2.primary_flag = 'Y'
AND asg1.assignment_id=asg2.assignment_id
AND et.element_name = 'Sickness Details'
AND et.legislation_code = 'SE'
--OR et.business_group_id=3261 ) --checking for the business group, it should be removed
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Start Date'
AND iv2.element_type_id = et.element_type_id
AND iv2.name = 'End Date'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND eev2.element_entry_id = ee.element_entry_id
AND eev2.input_value_id = iv2.input_value_id
AND ee.effective_start_date <= csr_v_end_date
AND ee.effective_end_date >= csr_v_start_date
AND eev1.effective_start_date <= csr_v_end_date
AND eev1.effective_end_date >= csr_v_start_date
AND eev2.effective_start_date <= csr_v_end_date
AND eev2.effective_end_date >= csr_v_start_date;
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'SE'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
SELECT MAX(assignment_action_id)
FROM pay_Assignment_actions WHERE
payroll_action_id=csr_v_payroll_action_id; --23
SELECT hoi2.ORG_INFORMATION1 local_unit_id
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';
SELECT o1.NAME local_unit_name
-- , hoi2.org_information2 org_number
-- , hoi1.organization_id legal_id
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id = g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
SELECT START_DATE,end_date FROM per_time_periods WHERE payroll_id=csr_v_payroll_id --3469
AND /*'15-jan-2005'*/ csr_v_effective_date BETWEEN START_DATE AND end_date;
SELECT MEANING
FROM hr_lookups
WHERE LOOKUP_TYPE = 'HR_SE_CALENDAR_MONTH'
AND ENABLED_FLAG = 'Y'
AND LOOKUP_CODE = csr_v_month; -- 01;
'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';
-- Insert the report Parameters
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT DETAILS'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => g_legal_employer_id
, p_action_information3 => L_LEGAL_EMPLOYER_NAME
, p_action_information4 => hr_general.decode_lookup('SE_REQUEST_LEVEL',g_LU_request)
, p_action_information5 => g_local_unit_id
, p_action_information6 => L_LOCAL_UNIT_NAME
, p_action_information7 => l_month --TO_CHAR(TO_DATE(g_month,'MM'),'MONTH')
, p_action_information8 => g_year
, p_action_information9 => FND_DATE.DATE_TO_CANONICAL(g_retroactive_payment_from)
, p_action_information10 => FND_DATE.DATE_TO_CANONICAL(g_retroactive_payment_to)
);
'select 1 from dual where to_char(:payroll_action_id) = dummy';
select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
from HR_ORGANIZATION_UNITS o1
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
WHERE o1.business_group_id = g_business_group_id
and hoi1.organization_id = o1.organization_id
and hoi1.org_information1 = 'SE_LOCAL_UNIT'
and hoi1.org_information_context = 'CLASS'
and o1.organization_id = hoi2.organization_id
and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
and o1.organization_id = csr_local_unit_ID;
SELECT national_identifier, last_name || ' ' || first_name name ,DATE_OF_BIRTH
FROM
per_all_people_f WHERE
BUSINESS_GROUP_ID=g_business_group_id
AND person_id=csr_v_person_id
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND months_between (csr_v_end_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
AND months_between (csr_v_end_date,DATE_OF_BIRTH) < 768 /* and age less than 64 */
ORDER BY last_name || ' ' || first_name;
SELECT effective_start_date FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21233
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND primary_flag='Y'
AND employee_category='WC' ;
SELECT MAX(effective_start_date) FROM per_all_people_f papf WHERE
CURRENT_EMPLOYEE_FLAG IS NULL
AND person_id=csr_v_person_id--21257
AND EFFECTIVE_END_DATE --'31-dec-2000'
BETWEEN csr_v_start_date AND csr_v_end_date /*'01-jan-2000' AND csr_v_end_date '31-dec-2000'*/
AND NOT EXISTS
(SELECT 1 FROM per_all_people_f WHERE
CURRENT_EMPLOYEE_FLAG='Y'
AND person_id=papf.person_id --21257
AND effective_start_date >papf.effective_start_date
);
/*SELECT effective_start_date,effective_end_date,pj.JOB_INFORMATION1,employee_category
FROM
per_all_people_f papf,
per_jobs pj
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND primary_flag='Y'
AND pj.job_id=papf.job_id
AND papf.job_id IS NOT NULL
AND papf.emloyee_category IS NOT NULL*/
SELECT paaf.effective_start_date,paaf.effective_end_date,
decode(pj.JOB_INFORMATION1,'Y','M',null) job,paaf.employee_category,
payroll_id
FROM
per_all_assignments_f paaf,
per_jobs pj
WHERE person_id=csr_v_person_id --21197 --21257 --21233
AND csr_v_start_date <=paaf.EFFECTIVE_END_DATE AND
csr_v_end_date >=paaf.EFFECTIVE_START_DATE
AND primary_flag='Y'
AND pj.job_id(+)=paaf.job_id
-- AND paaf.employee_category IN ('BC','WC')
AND (paaf.job_id IS NOT NULL
OR paaf.employee_category IS NOT NULL);
/*SELECT JOB_INFORMATION1 FROM per_jobs pj, per_roles pr
WHERE pj.job_id=pr.job_id
AND pj.JOB_INFORMATION_CATEGORY='SE'
AND pr.person_id=csr_v_person_id; --21257 */
SELECT JOB_INFORMATION1,start_date, start_date+(e_date-start_date-1) end_date
FROM
(
SELECT JOB_INFORMATION1,start_date,lead( start_date, 1, to_date('31-12-4713','dd-mm-yyyy') )
over (order by start_date ASC) e_date
FROM per_jobs pj, per_roles pr
WHERE pj.job_id=pr.job_id
AND pj.JOB_INFORMATION_CATEGORY='SE'
AND pr.person_id=csr_v_person_id /*21197*/)
WHERE start_date<=csr_v_end_date --'31-dec-2005'
AND start_date+(e_date-start_date-1)>=csr_v_start_date; /*'01-jan-2005'*/
SELECT DISTINCT employee_category ,EFFECTIVE_START_DATE
FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND primary_flag='Y'
ORDER BY EFFECTIVE_START_DATE;
SELECT MAX(EFFECTIVE_end_DATE) FROM
per_all_assignments_f
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND employee_category='BC'
AND primary_flag='Y';
/*SELECT employee_category FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21233
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND primary_flag='Y';*/
SELECT o1.NAME legal_employer_name
-- , hoi2.org_information2 org_number
-- , hoi1.organization_id legal_id
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id = g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_employer_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
SELECT distinct papf.person_id --,paaf.assignment_id
FROM per_all_assignments_f paaf,
per_all_people_f papf,
hr_soft_coding_keyflex hsck
WHERE papf.business_group_id=csr_v_business_group_id -- 3133 --paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND papf.person_id=paaf.person_id
and papf.person_id between p_start_person and p_end_person
--AND paaf.primary_flag='Y'
AND hsck.segment2=to_char(csr_v_local_unit_id) --3268)
AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND months_between (csr_v_effective_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
AND months_between (csr_v_effective_date,DATE_OF_BIRTH) < 768
AND papf.CURRENT_EMPLOYEE_FLAG='Y'
AND paaf.employee_category IN ('WC','BC')
AND paaf.employment_category IN ('SE_VTR','SE_HW','SE_PE') --add one more type
ORDER BY papf.person_id;
SELECT paaf.assignment_id,
paaf.employee_category,
paaf.hourly_salaried_code,
fnd_number.canonical_to_number(hsck.SEGMENT9) working_percentage,
paaf.frequency,
paaf.normal_hours
FROM per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
WHERE paaf.person_id=csr_v_person_id
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.employee_category IN ('WC','BC')
AND paaf.employment_category IN ('SE_VTR','SE_HW','SE_PE') --add one more type
ORDER BY paaf.assignment_id;
SELECT paaf.assignment_id,
paaf.employee_category,
paaf.hourly_salaried_code,
fnd_number.canonical_to_number(hsck.SEGMENT9) working_percentage,
paaf.frequency,
paaf.normal_hours,
paaf.payroll_id
FROM per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck --,
-- per_all_people_f papf
WHERE paaf.business_group_id=csr_v_business_group_id
-- papf.person_id=paaf.person_id
and paaf.person_id between p_start_person and p_end_person
--AND paaf.primary_flag='Y'
AND hsck.segment2=to_char(csr_v_local_unit_id)
-- and csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
-- AND papf.effective_end_date
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.employee_category IN ('WC','BC')
AND paaf.employment_category IN ('SE_VTR','SE_HW','SE_PE') --add one more type
/* AND months_between (csr_v_end_date,papf.DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
/* AND months_between (csr_v_end_date,papf.DATE_OF_BIRTH) < 768
AND papf.CURRENT_EMPLOYEE_FLAG='Y' ;*/
SELECT greatest(fnd_Date.canonical_to_date(eev1.screen_entry_value),csr_v_start_date) start_date,
least(fnd_Date.canonical_to_date(eev2.screen_entry_value),csr_v_end_date) end_date
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_input_values_f iv2
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
,pay_element_entry_values_f eev2
WHERE asg1.assignment_id = csr_v_assignment_id
AND csr_v_end_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND csr_v_end_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
--AND asg2.primary_flag = 'Y'
AND asg1.assignment_id=asg2.assignment_id
AND et.element_name = 'Sickness Details'
AND et.legislation_code = 'SE'
--OR et.business_group_id=3261 ) --checking for the business group, it should be removed
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Start Date'
AND iv2.element_type_id = et.element_type_id
AND iv2.name = 'End Date'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND eev2.element_entry_id = ee.element_entry_id
AND eev2.input_value_id = iv2.input_value_id
AND ee.effective_start_date <= csr_v_end_date
AND ee.effective_end_date >= csr_v_start_date
AND eev1.effective_start_date <= csr_v_end_date
AND eev1.effective_end_date >= csr_v_start_date
AND eev2.effective_start_date <= csr_v_end_date
AND eev2.effective_end_date >= csr_v_start_date;
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'SE'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
SELECT MAX(assignment_action_id)
FROM pay_Assignment_actions WHERE
payroll_action_id=csr_v_payroll_action_id; --23
SELECT hoi2.ORG_INFORMATION1 local_unit_id
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';
SELECT o1.NAME local_unit_name
-- , hoi2.org_information2 org_number
-- , hoi1.organization_id legal_id
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id = g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
SELECT START_DATE,end_date FROM per_time_periods WHERE payroll_id=csr_v_payroll_id --3469
AND /*'15-jan-2005'*/ csr_v_effective_date BETWEEN START_DATE AND end_date;
SELECT SUM(RESULT_VALUE) total --prrv1.* ,paa.assignment_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv1,
pay_input_values_f pivf,
pay_element_types_f petf
/* pay_run_result_values prrv2,
pay_run_result_values prrv3*/
WHERE ppa.effective_date BETWEEN csr_v_start_date --'01-feb-2000' --p_group_start_date --'01-jun-1999' --p_report_start_date
AND csr_v_end_date --'28-feb-2000'
/* AND p_group_end_date /*'01-jun-2000' */--p_report_end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id =csr_v_assignment_id --32488 --p_assignment_id --21035 --p_assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id --62358 -- p_element_type_id
AND petf.element_type_id=csr_v_element_type_id --'Sick Pay 1 to 14 days' --p_element_name --'Sick Pay 1 to 14 days'
AND petf.element_type_id=pivf.element_type_id
AND pivf.element_type_id=prr.element_type_id
AND prr.run_result_id = prrv1.run_result_id
AND prrv1.input_value_id =pivf.input_value_id --139722 --p_input_value_id;
SELECT org_information3 Type, org_information4 Element_Type_Id, org_information5 Input_value_Id,
org_information6 Balance_Type_Id,org_information7 Balance_Dimension_Id
FROM hr_organization_information hoi
WHERE hoi.organization_id=csr_v_organization_id --3134
AND hoi.org_information_context='SE_WAGES_SALARY_DETAILS'
AND hoi.org_information1=csr_v_category --'BH'
AND hoi.org_information2=csr_v_display_name;--'CCD'
SELECT defined_balance_id FROM pay_defined_balances
WHERE
balance_type_id=csr_v_balance_type_id --10504412
AND balance_dimension_id=csr_v_balance_dimension_id; --5525498
select 1 from
per_all_people_f papf,
per_all_assignments_f paaf
where papf.person_id=paaf.person_id
and paaf.assignment_id=csr_v_assignment_id
and csr_v_end_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
AND papf.effective_end_date
and csr_v_end_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND months_between (csr_v_end_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
AND months_between (csr_v_end_date,DATE_OF_BIRTH) < 768
AND papf.CURRENT_EMPLOYEE_FLAG='Y' ;
cursor csr_category_insert(csr_v_payroll_action_id number, csr_v_category varchar2, csr_v_local_unit_id number )
is
select 1 from pay_action_information
where action_context_id=csr_v_payroll_action_id --45446
and action_information2=csr_v_category --'BH'
AND action_information3=csr_v_local_unit_id;
cursor csr_local_unit_insert(csr_v_payroll_action_id number, csr_v_category varchar2, csr_v_local_unit_id number )
is
select 1 from pay_action_information
where action_context_id=csr_v_payroll_action_id --45446
and action_information2=csr_v_category --'BH'
and action_information3=csr_v_local_unit_id;
SELECT papf.period_type, min(ptp.start_date),min(ptp.end_date)
FROM per_time_periods ptp,
pay_all_payrolls_f papf
WHERE ptp.payroll_id=csr_v_payroll_id --4337 --3469
AND ptp.payroll_id=papf.payroll_id
AND /*'15-jan-2005'*/ ptp.START_DATE >=csr_v_start_date
AND ptp.end_date <=csr_v_end_date
AND csr_v_end_date between papf.EFFECTIVE_START_DATE
AND papf.EFFECTIVE_end_DATE
GROUP BY papf.period_type;
SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id=csr_v_assignment_id
AND csr_v_end_date BETWEEN effective_start_date AND
effective_end_date*/
l_ovn NUMBER;
l_check_insert number;
IF g_LU_request ='LU_SELECTED' THEN
/* THis is for Given LOCAL UNIT */
OPEN csr_CFAR_FROM_LU (g_local_unit_id);
/* check whether record has been inserted for White Collar Hourly Employee */
open csr_local_unit_insert(p_payroll_action_id,'LU',g_local_unit_id);
fetch csr_local_unit_insert into l_check_insert;
close csr_local_unit_insert;
if l_check_insert is null then
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT INFORMATION'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => 'LU'
, p_action_information3 => g_local_unit_id
, p_action_information4 => l_local_unit_name --lr_CFAR_FROM_LU.LU_NAME
, p_action_information5 => null --L_CFAR_NUMBER
, p_action_information6 => NULL
, p_action_information7 => NULL
);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* check whether record has been inserted for Blue Collar Hourly Employee */
open csr_category_insert(p_payroll_action_id,'BH',l_local_unit_id);
fetch csr_category_insert into l_check_insert;
IF csr_category_insert%NOTFOUND THEN
/*Insert the record*/
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT INFORMATION'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => 'BH'
, p_action_information3 => l_local_unit_id
, p_action_information4 => l_bh_worked_calendar_month
, p_action_information5 => l_bh_worked_payment_period
, p_action_information6 => NULL
, p_action_information7 => NULL
, p_action_information8 => NULL
, p_action_information9 => NULL
, p_action_information10 => NULL
, p_action_information11 => NULL
, p_action_information12 => l_bh_pbt_value
, p_action_information13 => l_bh_pcow_value
, p_action_information14 => l_bh_nha_value
, p_action_information15 => l_bh_nho_value
, p_action_information16 => l_bh_retroactive_pay
, p_action_information17 => l_bh_ppo_value
, p_action_information18 => l_bh_sick_pay
, p_action_information19 => l_bh_total_employees
);
/*update the record*/
update pay_action_information set
--action_information4=action_information4+l_bh_worked_calendar_month,
action_information5=action_information5+l_bh_worked_payment_period,
action_information12=action_information12+l_bh_pbt_value,
action_information13=action_information13+l_bh_pcow_value,
action_information14=action_information14+l_bh_nha_value,
action_information15=action_information15+l_bh_nho_value,
action_information16=action_information16+l_bh_retroactive_pay,
action_information17=action_information17+l_bh_ppo_value,
action_information18=action_information18+l_bh_sick_pay,
action_information19=action_information19+l_bh_total_employees
where action_context_id=p_payroll_action_id
and action_information2='BH'
AND action_information3=l_local_unit_id;
close csr_category_insert;
l_check_insert:=null;
/* check whether record has been inserted for Blue Collar Salaried Employee */
open csr_category_insert(p_payroll_action_id,'BS',l_local_unit_id);
fetch csr_category_insert into l_check_insert;
IF csr_category_insert%NOTFOUND THEN
/*Insert the record*/
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT INFORMATION'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => 'BS'
, p_action_information3 => l_local_unit_id
, p_action_information4 => null
, p_action_information5 => null
, p_action_information6 => null
, p_action_information7 => l_bs_gross_pay
, p_action_information8 => l_bs_working_agreement
, p_action_information9 => l_bs_tcdp_value
, p_action_information10 => l_bs_tcow_value
, p_action_information11 => NULL
, p_action_information12 => NULL
, p_action_information13 => NULL
, p_action_information14 => l_bs_nha_value
, p_action_information15 => l_bs_nho_value
, p_action_information16 => l_bs_retroactive_pay
, p_action_information17 => l_bs_ppo_value
, p_action_information18 => l_bs_sick_pay
, p_action_information19 => l_bs_total_employees
);
/*update the record*/
update pay_action_information set
action_information7=action_information7+l_bs_gross_pay,
action_information8=action_information8+l_bs_working_agreement,
action_information9=action_information9+l_bs_tcdp_value,
action_information10=action_information10+l_bs_tcow_value,
action_information14=action_information14+l_bs_nha_value,
action_information15=action_information15+l_bs_nho_value,
action_information16=action_information16+l_bs_retroactive_pay,
action_information17=action_information17+l_bs_ppo_value,
action_information18=action_information18+l_bs_sick_pay,
action_information19=action_information19+l_bs_total_employees
where action_context_id=p_payroll_action_id
and action_information2='BS'
AND action_information3=l_local_unit_id;
close csr_category_insert;
l_check_insert:=null;
/* check whether record has been inserted for White Collar Salaried Employee */
open csr_category_insert(p_payroll_action_id,'WS',l_local_unit_id);
fetch csr_category_insert into l_check_insert;
IF csr_category_insert%NOTFOUND THEN
/*Insert the record*/
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT INFORMATION'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => 'WS'
, p_action_information3 => l_local_unit_id
, p_action_information4 => null
, p_action_information5 => null
, p_action_information6 => l_ws_full_time_employee
, p_action_information7 => l_ws_gross_pay
, p_action_information8 => l_ws_working_agreement
, p_action_information9 => l_ws_tcdp_value
, p_action_information10 => l_ws_tcow_value
, p_action_information11 => NULL
, p_action_information12 => NULL
, p_action_information13 => NULL
, p_action_information14 => l_ws_nha_value
, p_action_information15 => l_ws_nho_value
, p_action_information16 => l_ws_retroactive_pay
, p_action_information17 => l_ws_ppo_value
, p_action_information18 => l_ws_sick_pay
, p_action_information19 => l_ws_total_employees
);
/*update the record*/
update pay_action_information set
action_information6=action_information6+l_ws_full_time_employee,
action_information7=action_information7+l_ws_gross_pay,
action_information8=action_information8+l_ws_working_agreement,
action_information9=action_information9+l_ws_tcdp_value,
action_information10=action_information10+l_ws_tcow_value,
action_information14=action_information14+l_ws_nha_value,
action_information15=action_information15+l_ws_nho_value,
action_information16=action_information16+l_ws_retroactive_pay,
action_information17=action_information17+l_ws_ppo_value,
action_information18=action_information18+l_ws_sick_pay,
action_information19=action_information19+l_ws_total_employees
where action_context_id=p_payroll_action_id
and action_information2='WS'
AND action_information3=l_local_unit_id;
close csr_category_insert;
l_check_insert:=null;
/* if all the local units under the legal employer is selected */
for csr_local in csr_Local_unit_Legal(g_legal_employer_id ) loop
l_local_unit_id:=csr_local.local_unit_id;
/* check whether record has been inserted for White Collar Hourly Employee */
open csr_local_unit_insert(p_payroll_action_id,'LU',l_local_unit_id);
fetch csr_local_unit_insert into l_check_insert;
close csr_local_unit_insert;
if l_check_insert is null then
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT INFORMATION'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => 'LU'
, p_action_information3 => l_local_unit_id
, p_action_information4 => l_local_unit_name --lr_CFAR_FROM_LU.LU_NAME
, p_action_information5 => null --L_CFAR_NUMBER
, p_action_information6 => NULL
, p_action_information7 => NULL
);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* If element is selected */
IF l_type='ELEMENT' THEN
OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
/* check whether record has been inserted for Blue Collar Hourly Employee */
open csr_category_insert(p_payroll_action_id,'BH',l_local_unit_id);
fetch csr_category_insert into l_check_insert;
IF csr_category_insert%NOTFOUND THEN
/*Insert the record*/
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT INFORMATION'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => 'BH'
, p_action_information3 => l_local_unit_id
, p_action_information4 => l_bh_worked_calendar_month
, p_action_information5 => l_bh_worked_payment_period
, p_action_information6 => NULL
, p_action_information7 => NULL
, p_action_information8 => NULL
, p_action_information9 => NULL
, p_action_information10 => NULL
, p_action_information11 => NULL
, p_action_information12 => l_bh_pbt_value
, p_action_information13 => l_bh_pcow_value
, p_action_information14 => l_bh_nha_value
, p_action_information15 => l_bh_nho_value
, p_action_information16 => l_bh_retroactive_pay
, p_action_information17 => l_bh_ppo_value
, p_action_information18 => l_bh_sick_pay
, p_action_information19 => l_bh_total_employees
);
/*update the record*/
update pay_action_information set
--action_information4=action_information4+l_bh_worked_calendar_month,
action_information5=action_information5+l_bh_worked_payment_period,
action_information12=action_information12+l_bh_pbt_value,
action_information13=action_information13+l_bh_pcow_value,
action_information14=action_information14+l_bh_nha_value,
action_information15=action_information15+l_bh_nho_value,
action_information16=action_information16+l_bh_retroactive_pay,
action_information17=action_information17+l_bh_ppo_value,
action_information18=action_information18+l_bh_sick_pay,
action_information19=action_information19+l_bh_total_employees
where action_context_id=p_payroll_action_id
and action_information2='BH'
AND action_information3=l_local_unit_id;
close csr_category_insert;
l_check_insert:=null;
/* check whether record has been inserted for Blue Collar Salaried Employee */
open csr_category_insert(p_payroll_action_id,'BS',l_local_unit_id);
fetch csr_category_insert into l_check_insert;
IF csr_category_insert%NOTFOUND THEN
/*Insert the record*/
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT INFORMATION'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => 'BS'
, p_action_information3 => l_local_unit_id
, p_action_information4 => null
, p_action_information5 => null
, p_action_information6 => null
, p_action_information7 => l_bs_gross_pay
, p_action_information8 => l_bs_working_agreement
, p_action_information9 => l_bs_tcdp_value
, p_action_information10 => l_bs_tcow_value
, p_action_information11 => NULL
, p_action_information12 => NULL
, p_action_information13 => NULL
, p_action_information14 => l_bs_nha_value
, p_action_information15 => l_bs_nho_value
, p_action_information16 => l_bs_retroactive_pay
, p_action_information17 => l_bs_ppo_value
, p_action_information18 => l_bs_sick_pay
, p_action_information19 => l_bs_total_employees
);
/*update the record*/
update pay_action_information set
action_information7=action_information7+l_bs_gross_pay,
action_information8=action_information8+l_bs_working_agreement,
action_information9=action_information9+l_bs_tcdp_value,
action_information10=action_information10+l_bs_tcow_value,
action_information14=action_information14+l_bs_nha_value,
action_information15=action_information15+l_bs_nho_value,
action_information16=action_information16+l_bs_retroactive_pay,
action_information17=action_information17+l_bs_ppo_value,
action_information18=action_information18+l_bs_sick_pay,
action_information19=action_information19+l_bs_total_employees
where action_context_id=p_payroll_action_id
and action_information2='BS'
AND action_information3=l_local_unit_id;
close csr_category_insert;
l_check_insert:=null;
/* check whether record has been inserted for White Collar Salaried Employee */
open csr_category_insert(p_payroll_action_id,'WS',l_local_unit_id);
fetch csr_category_insert into l_check_insert;
IF csr_category_insert%NOTFOUND THEN
/*Insert the record*/
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT INFORMATION'
, p_action_information1 => 'PYSEWSSA'
, p_action_information2 => 'WS'
, p_action_information3 => l_local_unit_id
, p_action_information4 => null
, p_action_information5 => null
, p_action_information6 => l_ws_full_time_employee
, p_action_information7 => l_ws_gross_pay
, p_action_information8 => l_ws_working_agreement
, p_action_information9 => l_ws_tcdp_value
, p_action_information10 => l_ws_tcow_value
, p_action_information11 => NULL
, p_action_information12 => NULL
, p_action_information13 => NULL
, p_action_information14 => l_ws_nha_value
, p_action_information15 => l_ws_nho_value
, p_action_information16 => l_ws_retroactive_pay
, p_action_information17 => l_ws_ppo_value
, p_action_information18 => l_ws_sick_pay
, p_action_information19 => l_ws_total_employees
);
/*update the record*/
update pay_action_information set
action_information6=action_information6+l_ws_full_time_employee,
action_information7=action_information7+l_ws_gross_pay,
action_information8=action_information8+l_ws_working_agreement,
action_information9=action_information9+l_ws_tcdp_value,
action_information10=action_information10+l_ws_tcow_value,
action_information14=action_information14+l_ws_nha_value,
action_information15=action_information15+l_ws_nho_value,
action_information16=action_information16+l_ws_retroactive_pay,
action_information17=action_information17+l_ws_ppo_value,
action_information18=action_information18+l_ws_sick_pay,
action_information19=action_information19+l_ws_total_employees
where action_context_id=p_payroll_action_id
and action_information2='WS'
AND action_information3=l_local_unit_id;
close csr_category_insert;
l_check_insert:=null;
SELECT u.creator_id
FROM ff_user_entities u, ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'SE')
AND (u.business_group_id IS NULL)
AND u.creator_type = 'B';
SELECT u.creator_id
FROM ff_user_entities u, ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'SE')
AND (u.business_group_id IS NULL)
AND u.creator_type = 'B';
SELECT distinct
pai.ACTION_INFORMATION7 Reporting_Year,
pai1.ACTION_INFORMATION3 Legal_Employer,
pai1.ACTION_INFORMATION4 Local_unit_id,
pai1.ACTION_INFORMATION5 Local_unit,
pai.ACTION_INFORMATION8 Insurance_Number--,
/*pai1.ACTION_INFORMATION6 Cfar_Number,
pai1.ACTION_INFORMATION7 Name,
pai1.ACTION_INFORMATION8 Employee_Category,
pai1.ACTION_INFORMATION9 White_Collar,
pai1.ACTION_INFORMATION10 Gross_Salary,
pai1.ACTION_INFORMATION11 Terminated,
pai1.ACTION_INFORMATION12 Painter*/
/*FROM
pay_action_information pai,
pay_payroll_actions ppa,
pay_action_information pai1
WHERE
pai.action_context_id = ppa.payroll_action_id
AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
AND pai.action_context_id = pai1.action_context_id
AND pai1.action_context_id= ppa.payroll_action_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'PER'
AND pai1.action_information1 = 'PYSEFORA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.ACTION_INFORMATION9=l_employee_category
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSEFORA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'
ORDER BY --pai1.ACTION_INFORMATION3,
pai1.ACTION_INFORMATION4 ;--pai1.ACTION_INFORMATION8 ;
SELECT distinct
pai.ACTION_INFORMATION7 Reporting_Year,
pai1.ACTION_INFORMATION3 Legal_Employer,
pai1.ACTION_INFORMATION4 Local_unit_id,
pai1.ACTION_INFORMATION5 Local_unit,
pai.ACTION_INFORMATION8 Insurance_Number,
pai1.ACTION_INFORMATION9 Employee_Category
/*pai1.ACTION_INFORMATION6 Cfar_Number,
pai1.ACTION_INFORMATION7 Name,
pai1.ACTION_INFORMATION8 Employee_Category,
pai1.ACTION_INFORMATION9 White_Collar,
pai1.ACTION_INFORMATION10 Gross_Salary,
pai1.ACTION_INFORMATION11 Terminated,
pai1.ACTION_INFORMATION12 Painter*/
/*FROM
pay_action_information pai,
pay_payroll_actions ppa,
pay_action_information pai1
WHERE
pai.action_context_id = ppa.payroll_action_id
AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
AND pai.action_context_id = pai1.action_context_id
AND pai1.action_context_id= ppa.payroll_action_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'PER'
AND pai1.action_information1 = 'PYSEFORA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.ACTION_INFORMATION9 IN ('BC','WC')
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSEFORA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'
ORDER BY --pai1.ACTION_INFORMATION3,
pai1.ACTION_INFORMATION9, pai1.ACTION_INFORMATION4 ;--pai1.ACTION_INFORMATION8 ;
SELECT
pai1.ACTION_INFORMATION6 Cfar_Number,
pai1.ACTION_INFORMATION7 Person_Number,
pai1.ACTION_INFORMATION8 Name,
pai1.ACTION_INFORMATION9 Employee_Category,
pai1.ACTION_INFORMATION10 White_Collar,
nvl(pai1.ACTION_INFORMATION11,0) Gross_Salary,
pai1.ACTION_INFORMATION12 Terminated,
pai1.ACTION_INFORMATION13 Painter
FROM
--pay_action_information pai,
pay_payroll_actions ppa,
pay_action_information pai1
WHERE
pai1.action_context_id = ppa.payroll_action_id
AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
/*AND pai.action_context_id = pai1.action_context_id*/
/*AND pai1.action_context_id= ppa.payroll_action_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'PER'
AND pai1.action_information1 = 'PYSEFORA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.ACTION_INFORMATION9=l_employee_category
AND pai1.ACTION_INFORMATION4=local_unit_id
/*AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSEFORA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'*/
/*ORDER BY pai1.ACTION_INFORMATION8;
SELECT action_information3 local_unit_id
FROM pay_action_information pai
WHERE pai.action_context_id= csr_v_payroll_action_id
AND pai.action_context_type='PA'
AND pai.action_information2 = 'LU'
AND pai.action_information1 = 'PYSEWSSA'
AND pai.action_information_category = 'EMEA REPORT INFORMATION'
GROUP BY action_information3;
SELECT pai.action_information3 legal_employer,
pai1.action_information4 local_unit,
pai.action_information7 month,
pai.action_information8 year,
fnd_date.canonical_to_date(pai.action_information9) retroactive_date_from,
fnd_date.canonical_to_date(pai.action_information10) retroactive_date_to,
decode(pai2.action_information4,0,NULL,pai2.action_information4) bh_worked_calendar_month,
decode(pai2.action_information5,0,NULL,pai2.action_information5) bh_worked_payment_period,
decode(pai2.action_information12,0,NULL,pai2.action_information12) bh_pbt_value,
decode(pai2.action_information13,0,NULL,pai2.action_information13) bh_pcow_value,
decode(pai2.action_information14,0,NULL,pai2.action_information14) bh_nha_value,
decode(pai2.action_information15,0,NULL,pai2.action_information15) bh_nho_value,
decode(pai2.action_information16,0,NULL,pai2.action_information16) bh_retroactive_pay,
decode(pai2.action_information17,0,NULL,pai2.action_information17) bh_ppo_value,
decode(pai2.action_information18,0,NULL,pai2.action_information18) bh_sick_pay,
decode(pai2.action_information19,0,NULL,pai2.action_information19) bh_total_employees,
decode(pai3.action_information7,0,NULL,pai3.action_information7) bs_gross_pay,
decode(pai3.action_information8,0,NULL,pai3.action_information8) bs_working_agreement,
decode(pai3.action_information9,0,NULL,pai3.action_information9) bs_tcdp_value,
decode(pai3.action_information10,0,NULL,pai3.action_information10) bs_tcow_value,
decode(pai3.action_information14,0,NULL,pai3.action_information14) bs_nha_value,
decode(pai3.action_information15,0,NULL,pai3.action_information15) bs_nho_value,
decode(pai3.action_information16,0,NULL,pai3.action_information16) bs_retroactive_pay,
decode(pai3.action_information17,0,NULL,pai3.action_information17) bs_ppo_value,
decode(pai3.action_information18,0,NULL,pai3.action_information18) bs_sick_pay,
decode(pai3.action_information19,0,NULL,pai3.action_information19) bs_total_employees,
decode(pai4.action_information6,0,NULL,pai4.action_information6) ws_full_time_employee,
decode(pai4.action_information7,0,NULL,pai4.action_information7) ws_gross_pay,
decode(pai4.action_information8,0,NULL,pai4.action_information8) ws_working_agreement,
decode(pai4.action_information9,0,NULL,pai4.action_information9) ws_tcdp_value,
decode(pai4.action_information10,0,NULL,pai4.action_information10) ws_tcow_value,
decode(pai4.action_information14,0,NULL,pai4.action_information14) ws_nha_value,
decode(pai4.action_information15,0,NULL,pai4.action_information15) ws_nho_value,
decode(pai4.action_information16,0,NULL,pai4.action_information16) ws_retroactive_pay,
decode(pai4.action_information17,0,NULL,pai4.action_information17) ws_ppo_value,
decode(pai4.action_information18,0,NULL,pai4.action_information18) ws_sick_pay,
decode(pai4.action_information19,0,NULL,pai4.action_information19) ws_total_employees,
decode(pai5.action_information6,0,NULL,pai5.action_information6) wh_full_time_employee,
decode(pai5.action_information7,0,NULL,pai5.action_information7) wh_gross_pay,
decode(pai5.action_information8,0,NULL,pai5.action_information8) wh_working_agreement,
decode(pai5.action_information9,0,NULL,pai5.action_information9) wh_tcdp_value,
decode(pai5.action_information16,0,NULL,pai5.action_information16) wh_retroactive_pay,
decode(pai5.action_information17,0,NULL,pai5.action_information17) wh_ppo_value,
decode(pai5.action_information18,0,NULL,pai5.action_information18) wh_sick_pay,
decode(pai5.action_information19,0,NULL,pai5.action_information19) wh_total_employees
FROM
pay_action_information pai,
pay_payroll_actions ppa,
pay_action_information pai1,
pay_action_information pai2,
pay_action_information pai3,
pay_action_information pai4,
pay_action_information pai5
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id --45660 --p_payroll_action_id
AND ppa.payroll_action_id=pai.action_context_id
AND pai.action_context_id=pai1.action_context_id
AND pai1.action_context_id=pai2.action_context_id
AND pai2.action_context_id=pai3.action_context_id
AND pai3.action_context_id=pai4.action_context_id
AND pai4.action_context_id=pai5.action_context_id
and pai5.action_context_id=ppa.payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSEWSSA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information1 = 'PYSEWSSA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai2.action_context_type='PA'
AND pai2.action_information2 = 'BH'
AND pai2.action_information1 = 'PYSEWSSA'
AND pai2.action_information_category = 'EMEA REPORT INFORMATION'
AND pai3.action_context_type='PA'
AND pai3.action_information2 = 'BH'
AND pai3.action_information1 = 'PYSEWSSA'
AND pai3.action_information_category = 'EMEA REPORT INFORMATION'
AND pai4.action_context_type='PA'
AND pai4.action_information2 = 'BS'
AND pai4.action_information1 = 'PYSEWSSA'
AND pai4.action_information_category = 'EMEA REPORT INFORMATION'
AND pai5.action_context_type='PA'
AND pai5.action_information2 = 'WH'
AND pai5.action_information1 = 'PYSEWSSA'
AND pai5.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.action_information3=csr_v_local_unit_id --3135 --csr_v_local_unit_id
AND pai1.action_information3=pai2.action_information3
AND pai2.action_information3=pai3.action_information3
AND pai3.action_information3=pai4.action_information3
AND pai4.action_information3=pai5.action_information3
AND pai5.action_information3=pai1.action_information3
ORDER BY pai1.action_information3; */
SELECT pai.action_information3 legal_employer,
pai1.action_information4 local_unit,
pai.action_information7 || ' ' || pai.action_information8 period,-- month,
--pai.action_information8 year,
fnd_date.canonical_to_date(pai.action_information9) retroactive_date_from,
fnd_date.canonical_to_date(pai.action_information10) retroactive_date_to
FROM
pay_action_information pai,
pay_action_information pai1,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id
AND ppa.payroll_action_id=pai.action_context_id
AND pai.action_context_id=pai1.action_context_id
AND pai1.action_context_id=ppa.payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSEWSSA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information1 = 'PYSEWSSA'
AND pai1.action_information3=csr_v_local_unit_id;
SELECT
--decode(sum(pai2.action_information4),0,NULL,sum(pai2.action_information4)) bh_worked_calendar_month,
decode(sum(pai2.action_information5),0,NULL,sum(pai2.action_information5)) bh_worked_payment_period,
decode(sum(pai2.action_information12),0,NULL,sum(pai2.action_information12)) bh_pbt_value,
decode(sum(pai2.action_information13),0,NULL,sum(pai2.action_information13)) bh_pcow_value,
decode(sum(pai2.action_information14),0,NULL,sum(pai2.action_information14)) bh_nha_value,
decode(sum(pai2.action_information15),0,NULL,sum(pai2.action_information15)) bh_nho_value,
decode(sum(pai2.action_information16),0,NULL,sum(pai2.action_information16)) bh_retroactive_pay,
decode(sum(pai2.action_information17),0,NULL,sum(pai2.action_information17)) bh_ppo_value,
decode(sum(pai2.action_information18),0,NULL,sum(pai2.action_information18)) bh_sick_pay,
decode(sum(pai2.action_information19),0,NULL,sum(pai2.action_information19)) bh_total_employees
FROM
pay_action_information pai1,
pay_action_information pai2,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id
AND ppa.payroll_action_id=pai1.action_context_id
AND pai1.action_context_id=pai2.action_context_id
AND pai2.action_context_id=ppa.payroll_action_id
AND pai1.action_information3=to_char(csr_v_local_unit_id /*3135*/) --csr_v_local_unit_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information1 = 'PYSEWSSA'
AND pai1.action_information_id=csr_v_action_information_id
AND pai1.action_information3=pai2.action_information3
AND pai2.action_context_type='PA'
AND pai2.action_information2 = 'BH'
AND pai2.action_information1 = 'PYSEWSSA'
AND pai2.action_information_category = 'EMEA REPORT INFORMATION';
IS SELECT pai2.action_information4 bh_worked_calendar_month
FROM
pay_action_information pai1,
pay_action_information pai2,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id
AND ppa.payroll_action_id=pai1.action_context_id
AND pai1.action_context_id=pai2.action_context_id
AND pai2.action_context_id=ppa.payroll_action_id
AND pai1.action_information3=to_char(csr_v_local_unit_id /*3135*/) --csr_v_local_unit_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information1 = 'PYSEWSSA'
AND pai1.action_information_id=csr_v_action_information_id
AND pai1.action_information3=pai2.action_information3
AND pai2.action_context_type='PA'
AND pai2.action_information2 = 'BH'
AND pai2.action_information1 = 'PYSEWSSA'
AND pai2.action_information_category = 'EMEA REPORT INFORMATION'
AND ROWNUM <2;
SELECT
decode(sum(pai3.action_information7),0,NULL,sum(pai3.action_information7)) bs_gross_pay,
decode(sum(pai3.action_information8),0,NULL,sum(pai3.action_information8)) bs_working_agreement,
decode(sum(pai3.action_information9),0,NULL,sum(pai3.action_information9)) bs_tcdp_value,
decode(sum(pai3.action_information10),0,NULL,sum(pai3.action_information10)) bs_tcow_value,
decode(sum(pai3.action_information14),0,NULL,sum(pai3.action_information14)) bs_nha_value,
decode(sum(pai3.action_information15),0,NULL,sum(pai3.action_information15)) bs_nho_value,
decode(sum(pai3.action_information16),0,NULL,sum(pai3.action_information16)) bs_retroactive_pay,
decode(sum(pai3.action_information17),0,NULL,sum(pai3.action_information17)) bs_ppo_value,
decode(sum(pai3.action_information18),0,NULL,sum(pai3.action_information18)) bs_sick_pay,
decode(sum(pai3.action_information19),0,NULL,sum(pai3.action_information19)) bs_total_employees
FROM
pay_action_information pai1,
pay_action_information pai3,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id
AND ppa.payroll_action_id=pai1.action_context_id
AND pai1.action_context_id=pai3.action_context_id
AND pai3.action_context_id=ppa.payroll_action_id
AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information1 = 'PYSEWSSA'
AND pai1.action_information_id=csr_v_action_information_id
AND pai1.action_information3=pai3.action_information3
AND pai3.action_context_type='PA'
AND pai3.action_information2 = 'BS'
AND pai3.action_information1 = 'PYSEWSSA'
AND pai3.action_information_category = 'EMEA REPORT INFORMATION';
SELECT
decode(sum(pai4.action_information6),0,NULL,sum(pai4.action_information6)) ws_full_time_employee,
decode(sum(pai4.action_information7),0,NULL,sum(pai4.action_information7)) ws_gross_pay,
decode(sum(pai4.action_information8),0,NULL,sum(pai4.action_information8)) ws_working_agreement,
decode(sum(pai4.action_information9),0,NULL,sum(pai4.action_information9)) ws_tcdp_value,
decode(sum(pai4.action_information10),0,NULL,sum(pai4.action_information10)) ws_tcow_value,
decode(sum(pai4.action_information14),0,NULL,sum(pai4.action_information14)) ws_nha_value,
decode(sum(pai4.action_information15),0,NULL,sum(pai4.action_information15)) ws_nho_value,
decode(sum(pai4.action_information16),0,NULL,sum(pai4.action_information16)) ws_retroactive_pay,
decode(sum(pai4.action_information17),0,NULL,sum(pai4.action_information17)) ws_ppo_value,
decode(sum(pai4.action_information18),0,NULL,sum(pai4.action_information18)) ws_sick_pay,
decode(sum(pai4.action_information19),0,NULL,sum(pai4.action_information19)) ws_total_employees
FROM
pay_action_information pai1,
pay_action_information pai4,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id
AND ppa.payroll_action_id=pai1.action_context_id
AND pai1.action_context_id=pai4.action_context_id
AND pai4.action_context_id=ppa.payroll_action_id
AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information3=pai4.action_information3
AND pai1.action_information1 = 'PYSEWSSA'
AND pai1.action_information_id=csr_v_action_information_id
AND pai4.action_context_type='PA'
AND pai4.action_information2 = 'WS'
AND pai4.action_information1 = 'PYSEWSSA'
AND pai4.action_information_category = 'EMEA REPORT INFORMATION';
SELECT
decode(sum(pai5.action_information6),0,NULL,sum(pai5.action_information6)) wh_full_time_employee,
decode(sum(pai5.action_information7),0,NULL,sum(pai5.action_information7)) wh_gross_pay,
decode(sum(pai5.action_information8),0,NULL,sum(pai5.action_information8)) wh_working_agreement,
decode(sum(pai5.action_information9),0,NULL,sum(pai5.action_information9)) wh_tcdp_value,
decode(sum(pai5.action_information16),0,NULL,sum(pai5.action_information16)) wh_retroactive_pay,
decode(sum(pai5.action_information17),0,NULL,sum(pai5.action_information17)) wh_ppo_value,
decode(sum(pai5.action_information18),0,NULL,sum(pai5.action_information18)) wh_sick_pay,
decode(sum(pai5.action_information19),0,NULL,sum(pai5.action_information19)) wh_total_employees
FROM
pay_action_information pai1,
pay_action_information pai5,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id
AND ppa.payroll_action_id=pai1.action_context_id
AND pai1.action_context_id=pai5.action_context_id
AND pai5.action_context_id=ppa.payroll_action_id
AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information1 = 'PYSEWSSA'
AND pai1.action_information_id=csr_v_action_information_id
AND pai1.action_information3=pai5.action_information3
AND pai5.action_context_type='PA'
AND pai5.action_information2 = 'WH'
AND pai5.action_information1 = 'PYSEWSSA'
AND pai5.action_information_category = 'EMEA REPORT INFORMATION';
SELECT MIN(action_information_id)
FROM pay_action_information pai
WHERE pai.action_context_id= csr_v_payroll_action_id
AND pai.action_context_type='PA'
AND pai.action_information2 = 'LU'
AND pai.action_information1 = 'PYSEWSSA'
AND pai.action_information3=to_char(csr_v_local_unit_id)
AND pai.action_information_category = 'EMEA REPORT INFORMATION';
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
AND fcrs.priority_request_id = fcrs1.priority_request_id
AND ppa.request_id between fcrs1.request_id and fcrs.request_id
AND ppa.request_id = fcrs1.request_id;