75: -- Global types
76: type r_assignments is record
77: (
78: payroll_id per_all_assignments_f.payroll_id%type,
79: legal_entity_id hr_all_organization_units.organization_id%type,
80: legal_entity hr_all_organization_units.name%type,
81: occupational_level_id hr_lookups.lookup_code%type,
82: occupational_category_id hr_lookups.lookup_code%type,
83: occupational_level hr_lookups.meaning%type,
76: type r_assignments is record
77: (
78: payroll_id per_all_assignments_f.payroll_id%type,
79: legal_entity_id hr_all_organization_units.organization_id%type,
80: legal_entity hr_all_organization_units.name%type,
81: occupational_level_id hr_lookups.lookup_code%type,
82: occupational_category_id hr_lookups.lookup_code%type,
83: occupational_level hr_lookups.meaning%type,
84: occupational_category hr_lookups.meaning%type,
95:
96: -- Added for Employment equity report enhancement
97: type r_Encome_diff_rec is record
98: (
99: legal_entity_id hr_all_organization_units.organization_id%type,
100: legal_entity hr_all_organization_units.NAME%type,
101: occupational_code_id hr_lookups.lookup_code%type,
102: occupational_code hr_lookups.meaning%type,
103: ma number,
96: -- Added for Employment equity report enhancement
97: type r_Encome_diff_rec is record
98: (
99: legal_entity_id hr_all_organization_units.organization_id%type,
100: legal_entity hr_all_organization_units.NAME%type,
101: occupational_code_id hr_lookups.lookup_code%type,
102: occupational_code hr_lookups.meaning%type,
103: ma number,
104: mc number,
1812: sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
1813: from hr_lookups hl,
1814: hr_lookups hl1,
1815: hr_lookups hl2,
1816: hr_all_organization_units haou,
1817: per_assignment_extra_info paei,
1818: per_all_assignments_f paaf,
1819: per_all_people_f papf
1820: where papf.business_group_id = p_business_group_id
1895: 0 FI,
1896: 0 FW,
1897: 0 total
1898: from hr_lookups hl
1899: , hr_all_organization_units haou
1900: where not exists
1901: (
1902: select 'X'
1903: from per_za_employment_equity pzee
1952: 0 FI,
1953: 0 FW,
1954: 0 total
1955: from hr_lookups hl
1956: , hr_all_organization_units haou
1957: where not exists
1958: (
1959: select 'X'
1960: from per_za_employment_equity pzee
2031: sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2032: from hr_lookups hl,
2033: hr_lookups hl1,
2034: hr_lookups hl2,
2035: hr_all_organization_units haou,
2036: per_assignment_extra_info paei,
2037: per_all_assignments_f paaf,
2038: per_all_people_f papf
2039: where papf.business_group_id = p_business_group_id
2113: 0 FI,
2114: 0 FW,
2115: 0 total
2116: from hr_lookups hl
2117: , hr_all_organization_units haou
2118: where not exists
2119: (
2120: select 'X'
2121: from per_za_employment_equity pzee
2171: 0 FI,
2172: 0 FW,
2173: 0 total
2174: from hr_lookups hl
2175: , hr_all_organization_units haou
2176: where not exists
2177: (
2178: select 'X'
2179: from per_za_employment_equity pzee
2268: sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2269: from hr_lookups hl,
2270: hr_lookups hl1,
2271: hr_lookups hl2,
2272: hr_all_organization_units haou,
2273: per_assignment_extra_info paei,
2274: per_all_assignments_f paaf,
2275: per_all_people_f papf
2276: where papf.business_group_id = p_business_group_id
2360: 0 FI,
2361: 0 FW,
2362: 0 total
2363: from hr_lookups hl
2364: , hr_all_organization_units haou
2365: where not exists
2366: (
2367: select 'X'
2368: from per_za_employment_equity pzee
2417: 0 FI,
2418: 0 FW,
2419: 0 total
2420: from hr_lookups hl
2421: , hr_all_organization_units haou
2422: where not exists
2423: (
2424: select 'X'
2425: from per_za_employment_equity pzee
2515: sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2516: from hr_lookups hl,
2517: hr_lookups hl1,
2518: hr_lookups hl2,
2519: hr_all_organization_units haou,
2520: per_assignment_extra_info paei,
2521: per_all_assignments_f paaf,
2522: per_all_people_f papf
2523: where papf.business_group_id = p_business_group_id
2607: 0 FI,
2608: 0 FW,
2609: 0 total
2610: from hr_lookups hl
2611: , hr_all_organization_units haou
2612: where not exists
2613: (
2614: select 'X'
2615: from per_za_employment_equity pzee
2664: 0 FI,
2665: 0 FW,
2666: 0 total
2667: from hr_lookups hl
2668: , hr_all_organization_units haou
2669: where not exists
2670: (
2671: select 'X'
2672: from per_za_employment_equity pzee
2765: sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2766: from hr_lookups hl,
2767: hr_lookups hl1,
2768: hr_lookups hl2,
2769: hr_all_organization_units haou,
2770: per_assignment_extra_info paei,
2771: per_all_assignments_f paaf,
2772: per_periods_of_service ppos,
2773: per_all_people_f papf
2860: 0 FI,
2861: 0 FW,
2862: 0 total
2863: from hr_lookups hl
2864: , hr_all_organization_units haou
2865: where not exists
2866: (
2867: select 'X'
2868: from per_za_employment_equity pzee
2917: 0 FI,
2918: 0 FW,
2919: 0 total
2920: from hr_lookups hl
2921: , hr_all_organization_units haou
2922: where not exists
2923: (
2924: select 'X'
2925: from per_za_employment_equity pzee
2995: sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2996: from hr_lookups hl,
2997: hr_lookups hl1,
2998: hr_lookups hl2,
2999: hr_all_organization_units haou,
3000: per_assignment_extra_info paei,
3001: per_all_assignments_f paaf,
3002: per_periods_of_service ppos,
3003: per_all_people_f papf
3093: 0 FI,
3094: 0 FW,
3095: 0 total
3096: from hr_lookups hl
3097: , hr_all_organization_units haou
3098: where not exists
3099: (
3100: select 'X'
3101: from per_za_employment_equity pzee
3150: 0 FI,
3151: 0 FW,
3152: 0 total
3153: from hr_lookups hl
3154: , hr_all_organization_units haou
3155: where not exists
3156: (
3157: select 'X'
3158: from per_za_employment_equity pzee
3250: sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
3251: from hr_lookups hl,
3252: hr_lookups hl1,
3253: hr_lookups hl2,
3254: hr_all_organization_units haou,
3255: per_assignment_extra_info paei,
3256: per_all_assignments_f paaf,
3257: per_periods_of_service ppos,
3258: per_all_people_f papf
3346: 0 FI,
3347: 0 FW,
3348: 0 total
3349: from hr_lookups hl
3350: , hr_all_organization_units haou
3351: where not exists
3352: (
3353: select 'X'
3354: from per_za_employment_equity pzee
3402: 0 FI,
3403: 0 FW,
3404: 0 total
3405: from hr_lookups hl
3406: , hr_all_organization_units haou
3407: where not exists
3408: (
3409: select 'X'
3410: from per_za_employment_equity pzee
3516: sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
3517: from hr_lookups hl,
3518: hr_lookups hl1,
3519: hr_lookups hl2,
3520: hr_all_organization_units haou,
3521: per_assignment_extra_info paei,
3522: per_all_assignments_f paaf,
3523: per_periods_of_service ppos,
3524: per_all_people_f papf
3645: 0 FC,
3646: 0 FI,
3647: 0 FW,
3648: 0 total
3649: from hr_all_organization_units haou
3650: where not exists
3651: (
3652: select 'X'
3653: from per_za_employment_equity pzee
3715: 0 FC,
3716: 0 FI,
3717: 0 FW,
3718: 0 total
3719: from hr_all_organization_units haou
3720: where not exists
3721: (
3722: select 'X'
3723: from per_za_employment_equity pzee
4095: , p_fw IN number
4096: , p_total IN number
4097: , p_cat_index IN number
4098: , p_lev_index IN number
4099: , p_legal_entity_id IN hr_all_organization_units.organization_id%type
4100: , p_occupational_level IN hr_lookups.meaning%type
4101: , p_occupational_category IN hr_lookups.meaning%type
4102: , p_occupational_level_id IN hr_lookups.lookup_code%type
4103: , p_occupational_category_id IN hr_lookups.lookup_code%type
4199:
4200: -- Procedure is used to sort the employee data
4201: -- and ready to inset into global tables
4202:
4203: Procedure cat_lev_data ( p_legal_entity_id IN hr_all_organization_units.organization_id%type
4204: , p_occupational_level IN hr_lookups.meaning%type
4205: , p_occupational_category IN hr_lookups.meaning%type
4206: , p_race IN per_all_people_f.per_information4%type
4207: , p_sex IN per_all_people_f.sex%type
5035: , g_cat_Enc_Diff_table(l_rowind).fc
5036: , g_cat_Enc_Diff_table(l_rowind).fi
5037: , g_cat_Enc_Diff_table(l_rowind).fw
5038: , g_cat_Enc_Diff_table(l_rowind).total
5039: FROM hr_all_organization_units haou
5040: Where haou.organization_id = g_cat_Enc_Diff_table(l_rowind).legal_entity_id;
5041:
5042:
5043: INSERT INTO per_za_employment_equity
5078: , g_cat_Enc_Diff_table(l_rowind).fc_inc
5079: , g_cat_Enc_Diff_table(l_rowind).fi_inc
5080: , g_cat_Enc_Diff_table(l_rowind).fw_inc
5081: , g_cat_Enc_Diff_table(l_rowind).total_inc
5082: FROM hr_all_organization_units haou
5083: Where haou.organization_id = g_cat_Enc_Diff_table(l_rowind).legal_entity_id;
5084:
5085: l_rowind := g_cat_Enc_Diff_table.next(l_rowind);
5086: END loop;
5126: , g_lev_Enc_Diff_table(l_rowind).fc
5127: , g_lev_Enc_Diff_table(l_rowind).fi
5128: , g_lev_Enc_Diff_table(l_rowind).fw
5129: , g_lev_Enc_Diff_table(l_rowind).total
5130: FROM hr_all_organization_units haou
5131: Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
5132:
5133: INSERT INTO per_za_employment_equity
5134: (
5168: , g_lev_Enc_Diff_table(l_rowind).fc_inc
5169: , g_lev_Enc_Diff_table(l_rowind).fi_inc
5170: , g_lev_Enc_Diff_table(l_rowind).fw_inc
5171: , g_lev_Enc_Diff_table(l_rowind).total_inc
5172: FROM hr_all_organization_units haou
5173: Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
5174:
5175: l_rowind := g_lev_Enc_Diff_table.next(l_rowind);
5176: END loop;
5216: 0 FI,
5217: 0 FW,
5218: 0 total
5219: from hr_lookups hl
5220: , hr_all_organization_units haou
5221: where not exists
5222: (
5223: select 'X'
5224: from per_za_employment_equity pzee
5271: 0 FI,
5272: 0 FW,
5273: 0 total
5274: from hr_lookups hl
5275: , hr_all_organization_units haou
5276: where not exists
5277: (
5278: select 'X'
5279: from per_za_employment_equity pzee
5326: 0 FI,
5327: 0 FW,
5328: 0 total
5329: from hr_lookups hl
5330: , hr_all_organization_units haou
5331: where not exists
5332: (
5333: select 'X'
5334: from per_za_employment_equity pzee
5381: 0 FI,
5382: 0 FW,
5383: 0 total
5384: from hr_lookups hl
5385: , hr_all_organization_units haou
5386: where not exists
5387: (
5388: select 'X'
5389: from per_za_employment_equity pzee