232: l_element_context VARCHAR2 (5);
233:
234: -- Archiving the data , as this will fire once
235: CURSOR csr_legal_employer_details (
236: csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
237: )
238: IS
239: SELECT o1.NAME legal_employer_name
240: , hoi2.org_information2 org_number
239: SELECT o1.NAME legal_employer_name
240: , hoi2.org_information2 org_number
241: , hoi1.organization_id legal_id
242: FROM hr_organization_units o1
243: , hr_organization_information hoi1
244: , hr_organization_information hoi2
245: WHERE o1.business_group_id = g_business_group_id
246: AND hoi1.organization_id = o1.organization_id
247: AND hoi1.organization_id = csr_v_legal_employer_id
240: , hoi2.org_information2 org_number
241: , hoi1.organization_id legal_id
242: FROM hr_organization_units o1
243: , hr_organization_information hoi1
244: , hr_organization_information hoi2
245: WHERE o1.business_group_id = g_business_group_id
246: AND hoi1.organization_id = o1.organization_id
247: AND hoi1.organization_id = csr_v_legal_employer_id
248: AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
289: )
290: is
291: select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
292: from HR_ORGANIZATION_UNITS o1
293: , HR_ORGANIZATION_INFORMATION hoi1
294: , HR_ORGANIZATION_INFORMATION hoi2
295: WHERE o1.business_group_id = g_business_group_id
296: and hoi1.organization_id = o1.organization_id
297: and hoi1.org_information1 = 'SE_LOCAL_UNIT'
290: is
291: select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
292: from HR_ORGANIZATION_UNITS o1
293: , HR_ORGANIZATION_INFORMATION hoi1
294: , HR_ORGANIZATION_INFORMATION hoi2
295: WHERE o1.business_group_id = g_business_group_id
296: and hoi1.organization_id = o1.organization_id
297: and hoi1.org_information1 = 'SE_LOCAL_UNIT'
298: and hoi1.org_information_context = 'CLASS'
340: SELECT hoi_le.org_information1 local_unit_id,
341: hou_lu.NAME local_unit_name,
342: hoi_lu.org_information2 CFAR
343: FROM hr_organization_units hou_le,
344: hr_organization_information hoi_le,
345: hr_organization_units hou_lu,
346: hr_organization_information hoi_lu
347: WHERE hoi_le.organization_id = hou_le.organization_id
348: AND hou_le.organization_id = csr_legal_employer_ID
342: hoi_lu.org_information2 CFAR
343: FROM hr_organization_units hou_le,
344: hr_organization_information hoi_le,
345: hr_organization_units hou_lu,
346: hr_organization_information hoi_lu
347: WHERE hoi_le.organization_id = hou_le.organization_id
348: AND hou_le.organization_id = csr_legal_employer_ID
349: AND hoi_le.org_information_context = 'SE_LOCAL_UNITS'
350: AND hou_lu.organization_id = hoi_le.org_information1
355: -- ********************* ALL LE for GIVEN Business group id***********************
356: CURSOR csr_ALL_LE_FOR_BG is
357: select o1.name legal_employer_name,hoi1.organization_id
358: from hr_organization_units o1,
359: hr_organization_information hoi1,
360: hr_organization_information hoi2
361: where o1.business_group_id = g_business_group_id
362: and hoi1.organization_id = o1.organization_id
363: and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
356: CURSOR csr_ALL_LE_FOR_BG is
357: select o1.name legal_employer_name,hoi1.organization_id
358: from hr_organization_units o1,
359: hr_organization_information hoi1,
360: hr_organization_information hoi2
361: where o1.business_group_id = g_business_group_id
362: and hoi1.organization_id = o1.organization_id
363: and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
364: and hoi1.org_information_context = 'CLASS'
2263: ,paa.employment_category EMP_CAT
2264: ,pap.SEX
2265: FROM
2266: hr_all_organization_units hou_le,
2267: hr_organization_information hoi_le,
2268: hr_all_organization_units hou_lu,
2269: hr_organization_information hoi_lu,
2270: PER_ALL_ASSIGNMENTS_F paa
2271: ,HR_SOFT_CODING_KEYFLEX scl1
2265: FROM
2266: hr_all_organization_units hou_le,
2267: hr_organization_information hoi_le,
2268: hr_all_organization_units hou_lu,
2269: hr_organization_information hoi_lu,
2270: PER_ALL_ASSIGNMENTS_F paa
2271: ,HR_SOFT_CODING_KEYFLEX scl1
2272: ,PER_ALL_PEOPLE_F pap
2273: WHERE