[Home] [Help]
97:
98: -- Global types
99: type r_assignments is record
100: (
101: payroll_id per_all_assignments_f.payroll_id%type,
102: legal_entity_id hr_all_organization_units.organization_id%type,
103: legal_entity hr_all_organization_units.name%type,
104: occupational_level_id hr_lookups.lookup_code%type,
105: occupational_category_id hr_lookups.lookup_code%type,
150:
151:
152: type rec_assignments is record
153: (
154: payroll_id per_all_assignments_f.payroll_id%type,
155: legal_entity_id hr_all_organization_units.organization_id%type,
156: legal_entity hr_all_organization_units.name%type,
157: occupational_level_id hr_lookups.lookup_code%type,
158: occupational_category_id hr_lookups.lookup_code%type,
182: g_lev_Enc_Diff_table t_E_differential;
183:
184: g_grade_name per_grades.name%type;
185: g_grade_report_date date;
186: g_grade_asg_id per_all_assignments_f.assignment_id%type;
187:
188: g_position_name per_all_positions.name%type;
189: g_pos_report_date date;
190: g_pos_asg_id per_all_assignments_f.assignment_id%type;
186: g_grade_asg_id per_all_assignments_f.assignment_id%type;
187:
188: g_position_name per_all_positions.name%type;
189: g_pos_report_date date;
190: g_pos_asg_id per_all_assignments_f.assignment_id%type;
191:
192: g_job_name per_jobs.name%type;
193: g_job_report_date date;
194: g_job_asg_id per_all_assignments_f.assignment_id%type;
190: g_pos_asg_id per_all_assignments_f.assignment_id%type;
191:
192: g_job_name per_jobs.name%type;
193: g_job_report_date date;
194: g_job_asg_id per_all_assignments_f.assignment_id%type;
195:
196: g_lev_name hr_lookups.meaning%type;
197: g_lev_report_date date;
198: g_lev_asg_id per_all_assignments_f.assignment_id%type;
194: g_job_asg_id per_all_assignments_f.assignment_id%type;
195:
196: g_lev_name hr_lookups.meaning%type;
197: g_lev_report_date date;
198: g_lev_asg_id per_all_assignments_f.assignment_id%type;
199:
200: g_cat_name hr_lookups.meaning%type;
201: g_cat_report_date date;
202: g_cat_asg_id per_all_assignments_f.assignment_id%type;
198: g_lev_asg_id per_all_assignments_f.assignment_id%type;
199:
200: g_cat_name hr_lookups.meaning%type;
201: g_cat_report_date date;
202: g_cat_asg_id per_all_assignments_f.assignment_id%type;
203:
204: g_lev_flex pay_user_column_instances_f.value%type := null;
205: g_lev_segment pay_user_column_instances_f.value%type := null;
206: g_cat_flex pay_user_column_instances_f.value%type := null;
470: + 1
471: )
472: into l_count
473: from per_assignment_status_types past,
474: per_all_assignments_f paaf
475: where paaf.assignment_id = p_assignment_id
476: and past.assignment_status_type_id = paaf.assignment_status_type_id
477: and past.per_system_status = 'ACTIVE_ASSIGN'
478: and paaf.effective_start_date <= p_report_end
488:
489: -- This function returns the termination reason from the user tables.
490: function get_termination_reason
491: (
492: p_business_group_id in per_all_assignments_f.business_group_id%type,
493: p_report_date in per_all_assignments_f.effective_end_date%type,
494: p_reason_code in per_periods_of_service.leaving_reason%type
495: ) return varchar2 is
496:
489: -- This function returns the termination reason from the user tables.
490: function get_termination_reason
491: (
492: p_business_group_id in per_all_assignments_f.business_group_id%type,
493: p_report_date in per_all_assignments_f.effective_end_date%type,
494: p_reason_code in per_periods_of_service.leaving_reason%type
495: ) return varchar2 is
496:
497: l_termination_reason pay_user_column_instances_f.value%type;
573:
574: -- This function returns the average 5 highest paid employees per category or level.
575: function get_avg_5_highest_salary
576: (
577: p_report_date in per_all_assignments_f.effective_end_date%type,
578: p_business_group_id in per_all_assignments_f.business_group_id%type,
579: p_legent_param in per_assignment_extra_info.aei_information7%type := null,
580: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
581: p_occupational_level_cat in hr_lookups.meaning%type,
574: -- This function returns the average 5 highest paid employees per category or level.
575: function get_avg_5_highest_salary
576: (
577: p_report_date in per_all_assignments_f.effective_end_date%type,
578: p_business_group_id in per_all_assignments_f.business_group_id%type,
579: p_legent_param in per_assignment_extra_info.aei_information7%type := null,
580: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
581: p_occupational_level_cat in hr_lookups.meaning%type,
582: p_lookup_code in hr_lookups.lookup_code%type,
696:
697: -- This function returns the average 5 lowest paid employees per category or level.
698: function get_avg_5_lowest_salary
699: (
700: p_report_date in per_all_assignments_f.effective_end_date%type,
701: p_business_group_id in per_all_assignments_f.business_group_id%type,
702: p_legent_param in per_assignment_extra_info.aei_information7%type := null,
703: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
704: p_occupational_level_cat in hr_lookups.meaning%type,
697: -- This function returns the average 5 lowest paid employees per category or level.
698: function get_avg_5_lowest_salary
699: (
700: p_report_date in per_all_assignments_f.effective_end_date%type,
701: p_business_group_id in per_all_assignments_f.business_group_id%type,
702: p_legent_param in per_assignment_extra_info.aei_information7%type := null,
703: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
704: p_occupational_level_cat in hr_lookups.meaning%type,
705: p_lookup_code in hr_lookups.lookup_code%type,
716: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
717: paaf.pay_basis_id
718: from per_assignment_extra_info paei,
719: per_assignment_status_types past,
720: per_all_assignments_f paaf
721: where paaf.business_group_id = p_business_group_id
722: and p_report_date between paaf.effective_start_date and paaf.effective_end_date
723: and past.assignment_status_type_id = paaf.assignment_status_type_id
724: and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
729: and nvl(paei.aei_information6, 'N') <> 'Y'
730: order by paaf.payroll_id;
731:
732: l_avg_5_lowest_salary number;
733: l_old_payroll_id per_all_assignments_f.payroll_id%type := -9999;
734: l_rowind pls_integer;
735: l_active_days number;
736: l_ee_income number;
737: l_ee_annual_income number;
1199: -- The employee has to work for a continuous period of 3 months in order to be seen as permanent
1200: function get_ee_employment_type_name
1201: (
1202: p_report_date in per_all_people_f.start_date%type,
1203: p_period_of_service_id in per_all_assignments_f.period_of_service_id%type
1204: ) return varchar2 is
1205:
1206: l_ee_employment_type_name varchar2(13);
1207: l_date_start per_periods_of_service.date_start%type;
1264:
1265: -- This function returns the occupational category from the common lookups table.
1266: function get_occupational_category
1267: (
1268: p_report_date in per_all_assignments_f.effective_end_date%type,
1269: p_assignment_id in per_all_assignments_f.assignment_id%type,
1270: p_job_id in per_all_assignments_f.job_id%type,
1271: p_grade_id in per_all_assignments_f.grade_id%type,
1272: p_position_id in per_all_assignments_f.position_id%type,
1265: -- This function returns the occupational category from the common lookups table.
1266: function get_occupational_category
1267: (
1268: p_report_date in per_all_assignments_f.effective_end_date%type,
1269: p_assignment_id in per_all_assignments_f.assignment_id%type,
1270: p_job_id in per_all_assignments_f.job_id%type,
1271: p_grade_id in per_all_assignments_f.grade_id%type,
1272: p_position_id in per_all_assignments_f.position_id%type,
1273: p_business_group_id in per_all_assignments_f.business_group_id%type
1266: function get_occupational_category
1267: (
1268: p_report_date in per_all_assignments_f.effective_end_date%type,
1269: p_assignment_id in per_all_assignments_f.assignment_id%type,
1270: p_job_id in per_all_assignments_f.job_id%type,
1271: p_grade_id in per_all_assignments_f.grade_id%type,
1272: p_position_id in per_all_assignments_f.position_id%type,
1273: p_business_group_id in per_all_assignments_f.business_group_id%type
1274: ) return varchar2 is
1267: (
1268: p_report_date in per_all_assignments_f.effective_end_date%type,
1269: p_assignment_id in per_all_assignments_f.assignment_id%type,
1270: p_job_id in per_all_assignments_f.job_id%type,
1271: p_grade_id in per_all_assignments_f.grade_id%type,
1272: p_position_id in per_all_assignments_f.position_id%type,
1273: p_business_group_id in per_all_assignments_f.business_group_id%type
1274: ) return varchar2 is
1275:
1268: p_report_date in per_all_assignments_f.effective_end_date%type,
1269: p_assignment_id in per_all_assignments_f.assignment_id%type,
1270: p_job_id in per_all_assignments_f.job_id%type,
1271: p_grade_id in per_all_assignments_f.grade_id%type,
1272: p_position_id in per_all_assignments_f.position_id%type,
1273: p_business_group_id in per_all_assignments_f.business_group_id%type
1274: ) return varchar2 is
1275:
1276: begin
1269: p_assignment_id in per_all_assignments_f.assignment_id%type,
1270: p_job_id in per_all_assignments_f.job_id%type,
1271: p_grade_id in per_all_assignments_f.grade_id%type,
1272: p_position_id in per_all_assignments_f.position_id%type,
1273: p_business_group_id in per_all_assignments_f.business_group_id%type
1274: ) return varchar2 is
1275:
1276: begin
1277:
1311:
1312: -- This function returns the occupational levels from the user tables.
1313: function get_occupational_level
1314: (
1315: p_report_date in per_all_assignments_f.effective_end_date%type,
1316: p_assignment_id in per_all_assignments_f.assignment_id%type,
1317: p_job_id in per_all_assignments_f.job_id%type,
1318: p_grade_id in per_all_assignments_f.grade_id%type,
1319: p_position_id in per_all_assignments_f.position_id%type,
1312: -- This function returns the occupational levels from the user tables.
1313: function get_occupational_level
1314: (
1315: p_report_date in per_all_assignments_f.effective_end_date%type,
1316: p_assignment_id in per_all_assignments_f.assignment_id%type,
1317: p_job_id in per_all_assignments_f.job_id%type,
1318: p_grade_id in per_all_assignments_f.grade_id%type,
1319: p_position_id in per_all_assignments_f.position_id%type,
1320: p_business_group_id in per_all_assignments_f.business_group_id%type,
1313: function get_occupational_level
1314: (
1315: p_report_date in per_all_assignments_f.effective_end_date%type,
1316: p_assignment_id in per_all_assignments_f.assignment_id%type,
1317: p_job_id in per_all_assignments_f.job_id%type,
1318: p_grade_id in per_all_assignments_f.grade_id%type,
1319: p_position_id in per_all_assignments_f.position_id%type,
1320: p_business_group_id in per_all_assignments_f.business_group_id%type,
1321: p_year in number
1314: (
1315: p_report_date in per_all_assignments_f.effective_end_date%type,
1316: p_assignment_id in per_all_assignments_f.assignment_id%type,
1317: p_job_id in per_all_assignments_f.job_id%type,
1318: p_grade_id in per_all_assignments_f.grade_id%type,
1319: p_position_id in per_all_assignments_f.position_id%type,
1320: p_business_group_id in per_all_assignments_f.business_group_id%type,
1321: p_year in number
1322: ) return varchar2 is
1315: p_report_date in per_all_assignments_f.effective_end_date%type,
1316: p_assignment_id in per_all_assignments_f.assignment_id%type,
1317: p_job_id in per_all_assignments_f.job_id%type,
1318: p_grade_id in per_all_assignments_f.grade_id%type,
1319: p_position_id in per_all_assignments_f.position_id%type,
1320: p_business_group_id in per_all_assignments_f.business_group_id%type,
1321: p_year in number
1322: ) return varchar2 is
1323:
1316: p_assignment_id in per_all_assignments_f.assignment_id%type,
1317: p_job_id in per_all_assignments_f.job_id%type,
1318: p_grade_id in per_all_assignments_f.grade_id%type,
1319: p_position_id in per_all_assignments_f.position_id%type,
1320: p_business_group_id in per_all_assignments_f.business_group_id%type,
1321: p_year in number
1322: ) return varchar2 is
1323:
1324: begin
1361: (
1362: p_type in varchar2,
1363: p_flex in varchar2,
1364: p_segment in varchar2,
1365: p_job_id in per_all_assignments_f.job_id%type,
1366: p_grade_id in per_all_assignments_f.grade_id%type,
1367: p_position_id in per_all_assignments_f.position_id%type
1368: ) return varchar2 is
1369:
1362: p_type in varchar2,
1363: p_flex in varchar2,
1364: p_segment in varchar2,
1365: p_job_id in per_all_assignments_f.job_id%type,
1366: p_grade_id in per_all_assignments_f.grade_id%type,
1367: p_position_id in per_all_assignments_f.position_id%type
1368: ) return varchar2 is
1369:
1370: l_name hr_lookups.meaning%type;
1363: p_flex in varchar2,
1364: p_segment in varchar2,
1365: p_job_id in per_all_assignments_f.job_id%type,
1366: p_grade_id in per_all_assignments_f.grade_id%type,
1367: p_position_id in per_all_assignments_f.position_id%type
1368: ) return varchar2 is
1369:
1370: l_name hr_lookups.meaning%type;
1371: l_sql varchar2(32767);
1494: (
1495: p_type in varchar2,
1496: p_flex in varchar2,
1497: p_segment in varchar2,
1498: p_job_id in per_all_assignments_f.job_id%type,
1499: p_grade_id in per_all_assignments_f.grade_id%type,
1500: p_position_id in per_all_assignments_f.position_id%type
1501: ) return varchar2 is
1502:
1495: p_type in varchar2,
1496: p_flex in varchar2,
1497: p_segment in varchar2,
1498: p_job_id in per_all_assignments_f.job_id%type,
1499: p_grade_id in per_all_assignments_f.grade_id%type,
1500: p_position_id in per_all_assignments_f.position_id%type
1501: ) return varchar2 is
1502:
1503: l_sql varchar2(32767);
1496: p_flex in varchar2,
1497: p_segment in varchar2,
1498: p_job_id in per_all_assignments_f.job_id%type,
1499: p_grade_id in per_all_assignments_f.grade_id%type,
1500: p_position_id in per_all_assignments_f.position_id%type
1501: ) return varchar2 is
1502:
1503: l_sql varchar2(32767);
1504: l_indicator NUMBER;
1618: -- This procedure caches the location of the occupational category and level data.
1619: procedure cache_occupational_location
1620: (
1621: p_report_date in date,
1622: p_business_group_id in per_all_assignments_f.business_group_id%type,
1623: p_year in number
1624: ) is
1625:
1626: l_user_table_id pay_user_tables.user_table_id%type;
1798: -- This function populates an entity's sex and race and category matches.
1799: procedure populate_ee_table
1800: (
1801: p_report_code in varchar2,
1802: p_report_date in per_all_assignments_f.effective_end_date%type,
1803: p_business_group_id in per_all_assignments_f.business_group_id%type,
1804: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
1805: ) is
1806:
1799: procedure populate_ee_table
1800: (
1801: p_report_code in varchar2,
1802: p_report_date in per_all_assignments_f.effective_end_date%type,
1803: p_business_group_id in per_all_assignments_f.business_group_id%type,
1804: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
1805: ) is
1806:
1807: l_counter number;
1813: -- 2. Occupational Categories (including employees with disabilities)
1814: -- 3. Occupational Categories (only employees with disabilities)
1815: if p_report_code = 'EQ1' then
1816:
1817: -- Note: The date effective select on per_all_assignments_f is ok in this case, since an assignment
1818: -- record always exist at the same time as an employee record with status EMP
1819: insert into per_za_employment_equity
1820: (
1821: report_id,
1872: hr_lookups hl1,
1873: hr_lookups hl2,
1874: hr_all_organization_units haou,
1875: per_assignment_extra_info paei,
1876: per_all_assignments_f paaf,
1877: per_all_people_f papf
1878: where papf.business_group_id = p_business_group_id
1879: and p_report_date between papf.effective_start_date and papf.effective_end_date
1880: and papf.current_employee_flag = 'Y'
2091: hr_lookups hl1,
2092: hr_lookups hl2,
2093: hr_all_organization_units haou,
2094: per_assignment_extra_info paei,
2095: per_all_assignments_f paaf,
2096: per_all_people_f papf
2097: where papf.business_group_id = p_business_group_id
2098: and p_report_date between papf.effective_start_date and papf.effective_end_date
2099: and papf.current_employee_flag = 'Y'
2328: hr_lookups hl1,
2329: hr_lookups hl2,
2330: hr_all_organization_units haou,
2331: per_assignment_extra_info paei,
2332: per_all_assignments_f paaf,
2333: per_all_people_f papf
2334: where papf.business_group_id = p_business_group_id
2335: and papf.current_employee_flag = 'Y'
2336: and p_report_date between papf.effective_start_date and papf.effective_end_date
2575: hr_lookups hl1,
2576: hr_lookups hl2,
2577: hr_all_organization_units haou,
2578: per_assignment_extra_info paei,
2579: per_all_assignments_f paaf,
2580: per_all_people_f papf
2581: where papf.business_group_id = p_business_group_id
2582: and papf.current_employee_flag = 'Y'
2583: and p_report_date between papf.effective_start_date and papf.effective_end_date
2825: hr_lookups hl1,
2826: hr_lookups hl2,
2827: hr_all_organization_units haou,
2828: per_assignment_extra_info paei,
2829: per_all_assignments_f paaf,
2830: per_periods_of_service ppos,
2831: per_all_people_f papf
2832: where papf.business_group_id = p_business_group_id
2833: and papf.current_employee_flag = 'Y'
3055: hr_lookups hl1,
3056: hr_lookups hl2,
3057: hr_all_organization_units haou,
3058: per_assignment_extra_info paei,
3059: per_all_assignments_f paaf,
3060: per_periods_of_service ppos,
3061: per_all_people_f papf
3062: where papf.business_group_id = p_business_group_id
3063: and papf.current_employee_flag = 'Y'
3088: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <
3089: any
3090: (
3091: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code
3092: from per_all_assignments_f paaf1
3093: where paaf1.person_id = papf.person_id
3094: and paaf1.primary_flag = 'Y'
3095: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) is not null
3096: and paaf1.effective_end_date + 1 = paaf.effective_start_date
3310: hr_lookups hl1,
3311: hr_lookups hl2,
3312: hr_all_organization_units haou,
3313: per_assignment_extra_info paei,
3314: per_all_assignments_f paaf,
3315: per_periods_of_service ppos,
3316: per_all_people_f papf
3317: where papf.business_group_id = p_business_group_id
3318: and papf.current_employee_flag = 'Y'
3576: hr_lookups hl1,
3577: hr_lookups hl2,
3578: hr_all_organization_units haou,
3579: per_assignment_extra_info paei,
3580: per_all_assignments_f paaf,
3581: per_periods_of_service ppos,
3582: per_all_people_f papf
3583: where papf.business_group_id = p_business_group_id
3584: and papf.current_employee_flag = 'Y'
3799:
3800:
3801: function get_functional_type
3802: (
3803: p_report_date in per_all_assignments_f.effective_end_date%type,
3804: p_assignment_id in per_all_assignments_f.assignment_id%type,
3805: p_job_id in per_all_assignments_f.job_id%type,
3806: p_grade_id in per_all_assignments_f.grade_id%type,
3807: p_position_id in per_all_assignments_f.position_id%type,
3800:
3801: function get_functional_type
3802: (
3803: p_report_date in per_all_assignments_f.effective_end_date%type,
3804: p_assignment_id in per_all_assignments_f.assignment_id%type,
3805: p_job_id in per_all_assignments_f.job_id%type,
3806: p_grade_id in per_all_assignments_f.grade_id%type,
3807: p_position_id in per_all_assignments_f.position_id%type,
3808: p_business_group_id in per_all_assignments_f.business_group_id%type,
3801: function get_functional_type
3802: (
3803: p_report_date in per_all_assignments_f.effective_end_date%type,
3804: p_assignment_id in per_all_assignments_f.assignment_id%type,
3805: p_job_id in per_all_assignments_f.job_id%type,
3806: p_grade_id in per_all_assignments_f.grade_id%type,
3807: p_position_id in per_all_assignments_f.position_id%type,
3808: p_business_group_id in per_all_assignments_f.business_group_id%type,
3809: p_year in number
3802: (
3803: p_report_date in per_all_assignments_f.effective_end_date%type,
3804: p_assignment_id in per_all_assignments_f.assignment_id%type,
3805: p_job_id in per_all_assignments_f.job_id%type,
3806: p_grade_id in per_all_assignments_f.grade_id%type,
3807: p_position_id in per_all_assignments_f.position_id%type,
3808: p_business_group_id in per_all_assignments_f.business_group_id%type,
3809: p_year in number
3810: ) return VARCHAR2 is
3803: p_report_date in per_all_assignments_f.effective_end_date%type,
3804: p_assignment_id in per_all_assignments_f.assignment_id%type,
3805: p_job_id in per_all_assignments_f.job_id%type,
3806: p_grade_id in per_all_assignments_f.grade_id%type,
3807: p_position_id in per_all_assignments_f.position_id%type,
3808: p_business_group_id in per_all_assignments_f.business_group_id%type,
3809: p_year in number
3810: ) return VARCHAR2 is
3811:
3804: p_assignment_id in per_all_assignments_f.assignment_id%type,
3805: p_job_id in per_all_assignments_f.job_id%type,
3806: p_grade_id in per_all_assignments_f.grade_id%type,
3807: p_position_id in per_all_assignments_f.position_id%type,
3808: p_business_group_id in per_all_assignments_f.business_group_id%type,
3809: p_year in number
3810: ) return VARCHAR2 is
3811:
3812: begin
3847: function get_functional_data
3848: (
3849: p_flex in varchar2,
3850: p_segment in varchar2,
3851: p_job_id in per_all_assignments_f.job_id%type,
3852: p_grade_id in per_all_assignments_f.grade_id%type,
3853: p_position_id in per_all_assignments_f.position_id%type
3854: ) return varchar2 is
3855:
3848: (
3849: p_flex in varchar2,
3850: p_segment in varchar2,
3851: p_job_id in per_all_assignments_f.job_id%type,
3852: p_grade_id in per_all_assignments_f.grade_id%type,
3853: p_position_id in per_all_assignments_f.position_id%type
3854: ) return varchar2 is
3855:
3856: l_sql varchar2(32767);
3849: p_flex in varchar2,
3850: p_segment in varchar2,
3851: p_job_id in per_all_assignments_f.job_id%type,
3852: p_grade_id in per_all_assignments_f.grade_id%type,
3853: p_position_id in per_all_assignments_f.position_id%type
3854: ) return varchar2 is
3855:
3856: l_sql varchar2(32767);
3857: l_name hr_lookups.meaning%type;
3944: end get_functional_data;
3945:
3946: procedure populate_ee_table_EEWF
3947: (
3948: p_report_date in per_all_assignments_f.effective_end_date%type,
3949: p_business_group_id in per_all_assignments_f.business_group_id%type,
3950: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
3951: ) is
3952:
3945:
3946: procedure populate_ee_table_EEWF
3947: (
3948: p_report_date in per_all_assignments_f.effective_end_date%type,
3949: p_business_group_id in per_all_assignments_f.business_group_id%type,
3950: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
3951: ) is
3952:
3953: begin
4569: -- Procedure to initialise with the employee details
4570: --
4571: procedure init_g_cat_lev_table
4572: (
4573: p_report_date in per_all_assignments_f.effective_end_date%type,
4574: p_business_group_id in per_all_assignments_f.business_group_id%type,
4575: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
4576: p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
4577: ) is
4570: --
4571: procedure init_g_cat_lev_table
4572: (
4573: p_report_date in per_all_assignments_f.effective_end_date%type,
4574: p_business_group_id in per_all_assignments_f.business_group_id%type,
4575: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
4576: p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
4577: ) is
4578: /* commented for Bug 8911880
4587: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4588: paaf.pay_basis_id
4589: from per_assignment_extra_info paei,
4590: per_assignment_status_types past,
4591: per_all_assignments_f paaf,
4592: hr_lookups hl_cat,
4593: hr_lookups hl_lev,
4594: hr_lookups hl_fn
4595: where paaf.business_group_id = p_business_group_id
4626: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
4627: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4628: paaf.pay_basis_id
4629: from per_assignment_extra_info paei,
4630: per_all_assignments_f paaf,
4631: hr_lookups hl_cat,
4632: hr_lookups hl_lev,
4633: hr_lookups hl_fn
4634: where paaf.business_group_id = p_business_group_id
4633: hr_lookups hl_fn
4634: where paaf.business_group_id = p_business_group_id
4635: and (add_months(p_report_date,-12)+1 <=paaf.effective_end_date and p_report_date >=paaf.effective_start_date)
4636: and paaf.effective_end_date = ( select max(paaf1.effective_end_date)
4637: from per_all_assignments_f paaf1,
4638: per_assignment_status_types past
4639: where paaf1.assignment_id = paaf.assignment_id
4640: and paaf1.effective_start_date <= p_report_date
4641: and past.assignment_status_type_id = paaf1.assignment_status_type_id
4660: and nvl(paei.aei_information6, 'N') <> 'Y'
4661: order BY paei.aei_information7, paaf.payroll_id;
4662:
4663:
4664: cursor c_ele_rem(l_assignment_id per_all_assignments_f.assignment_id%type
4665: ,l_input_id pay_element_entry_values_f.input_value_id%type) is
4666: select peevf.screen_entry_value screen_value,
4667: months_between(least(peevf.effective_end_date ,p_report_date),greatest(peevf.effective_start_date-1 ,add_months(p_report_date,-12))) months_bet
4668: from pay_element_entry_values_f peevf
4673: and (add_months(p_report_date,-12)+1 <=peef.effective_end_date and p_report_date>=peef.effective_start_date)
4674: and (add_months(p_report_date,-12)+1 <=peevf.effective_end_date and p_report_date>=peevf.effective_start_date)
4675: and peef.effective_start_date between peevf.effective_start_date and peevf.effective_end_date;
4676:
4677: l_old_payroll_id per_all_assignments_f.payroll_id%type := -9999;
4678: v_ele_rem c_ele_rem%rowtype;
4679: l_rowind pls_integer;
4680: l_active_days number;
4681: l_ee_income number;
5966: -- Procedure to initialise with the employee details from reporting year 2009
5967: --
5968: procedure init_g_cat_lev_new_table
5969: (
5970: p_report_date in per_all_assignments_f.effective_end_date%type,
5971: p_business_group_id in per_all_assignments_f.business_group_id%type,
5972: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
5973: p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
5974: ) is
5967: --
5968: procedure init_g_cat_lev_new_table
5969: (
5970: p_report_date in per_all_assignments_f.effective_end_date%type,
5971: p_business_group_id in per_all_assignments_f.business_group_id%type,
5972: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
5973: p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
5974: ) is
5975: /* commented for Bug 8911880
5984: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
5985: paaf.pay_basis_id
5986: from per_assignment_extra_info paei,
5987: per_assignment_status_types past,
5988: per_all_assignments_f paaf,
5989: hr_lookups hl_cat,
5990: hr_lookups hl_lev,
5991: hr_lookups hl_fn
5992: where paaf.business_group_id = p_business_group_id
6023: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009) occupational_level,
6024: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type,
6025: paaf.pay_basis_id
6026: from per_assignment_extra_info paei,
6027: per_all_assignments_f paaf,
6028: hr_lookups hl_lev,
6029: hr_lookups hl_fn
6030: where paaf.business_group_id = p_business_group_id
6031: and (add_months(p_report_date,-12)+1 <=paaf.effective_end_date and p_report_date >=paaf.effective_start_date)
6029: hr_lookups hl_fn
6030: where paaf.business_group_id = p_business_group_id
6031: and (add_months(p_report_date,-12)+1 <=paaf.effective_end_date and p_report_date >=paaf.effective_start_date)
6032: and paaf.effective_end_date = ( select max(paaf1.effective_end_date)
6033: from per_all_assignments_f paaf1,
6034: per_assignment_status_types past
6035: where paaf1.assignment_id = paaf.assignment_id
6036: and paaf1.effective_start_date <= p_report_date
6037: and past.assignment_status_type_id = paaf1.assignment_status_type_id
6052: and nvl(paei.aei_information6, 'N') <> 'Y'
6053: order BY paei.aei_information7, paaf.payroll_id;
6054:
6055:
6056: cursor c_ele_rem(l_assignment_id per_all_assignments_f.assignment_id%type
6057: ,l_input_id pay_element_entry_values_f.input_value_id%type) is
6058: select peevf.screen_entry_value screen_value,
6059: months_between(least(peevf.effective_end_date ,p_report_date),greatest(peevf.effective_start_date-1 ,add_months(p_report_date,-12))) months_bet
6060: from pay_element_entry_values_f peevf
6065: and (add_months(p_report_date,-12)+1 <=peef.effective_end_date and p_report_date>=peef.effective_start_date)
6066: and (add_months(p_report_date,-12)+1 <=peevf.effective_end_date and p_report_date>=peevf.effective_start_date)
6067: and peef.effective_start_date between peevf.effective_start_date and peevf.effective_end_date;
6068:
6069: l_old_payroll_id per_all_assignments_f.payroll_id%type := -9999;
6070: v_ele_rem c_ele_rem%rowtype;
6071: l_rowind pls_integer;
6072: l_active_days number;
6073: l_ee_income number;
7317:
7318:
7319: function get_termination_reason_new
7320: (
7321: p_business_group_id in per_all_assignments_f.business_group_id%type,
7322: p_report_date in per_all_assignments_f.effective_end_date%type,
7323: p_reason_code in per_periods_of_service.leaving_reason%type
7324: ) return varchar2 is
7325:
7318:
7319: function get_termination_reason_new
7320: (
7321: p_business_group_id in per_all_assignments_f.business_group_id%type,
7322: p_report_date in per_all_assignments_f.effective_end_date%type,
7323: p_reason_code in per_periods_of_service.leaving_reason%type
7324: ) return varchar2 is
7325:
7326: l_termination_reason pay_user_column_instances_f.value%type;
7382:
7383: procedure populate_ee_table_new
7384: (
7385: p_report_code in varchar2,
7386: p_report_date in per_all_assignments_f.effective_end_date%type,
7387: p_business_group_id in per_all_assignments_f.business_group_id%type,
7388: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
7389: ) is
7390:
7383: procedure populate_ee_table_new
7384: (
7385: p_report_code in varchar2,
7386: p_report_date in per_all_assignments_f.effective_end_date%type,
7387: p_business_group_id in per_all_assignments_f.business_group_id%type,
7388: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
7389: ) is
7390:
7391: l_counter number;
7400: -- 2. Occupational Categories (including employees with disabilities)
7401: -- 3. Occupational Categories (only employees with disabilities)
7402: if p_report_code = 'EQ1' then
7403:
7404: -- Note: The date effective select on per_all_assignments_f is ok in this case, since an assignment
7405: -- record always exist at the same time as an employee record with status EMP
7406: insert into per_za_employment_equity
7407: (
7408: report_id,
7459: hr_lookups hl1,
7460: hr_lookups hl2,
7461: hr_all_organization_units haou,
7462: per_assignment_extra_info paei,
7463: per_all_assignments_f paaf,
7464: per_all_people_f papf
7465: where papf.business_group_id = p_business_group_id
7466: and p_report_date between papf.effective_start_date and papf.effective_end_date
7467: and papf.current_employee_flag = 'Y'
7678: hr_lookups hl1,
7679: -- hr_lookups hl2,
7680: hr_all_organization_units haou,
7681: per_assignment_extra_info paei,
7682: per_all_assignments_f paaf,
7683: per_all_people_f papf
7684: where papf.business_group_id = p_business_group_id
7685: and p_report_date between papf.effective_start_date and papf.effective_end_date
7686: and papf.current_employee_flag = 'Y'
7915: hr_lookups hl1,
7916: -- hr_lookups hl2,
7917: hr_all_organization_units haou,
7918: per_assignment_extra_info paei,
7919: per_all_assignments_f paaf,
7920: per_all_people_f papf
7921: where papf.business_group_id = p_business_group_id
7922: and papf.current_employee_flag = 'Y'
7923: and p_report_date between papf.effective_start_date and papf.effective_end_date
8162: hr_lookups hl1,
8163: -- hr_lookups hl2,
8164: hr_all_organization_units haou,
8165: per_assignment_extra_info paei,
8166: per_all_assignments_f paaf,
8167: per_all_people_f papf
8168: where papf.business_group_id = p_business_group_id
8169: and papf.current_employee_flag = 'Y'
8170: and p_report_date between papf.effective_start_date and papf.effective_end_date
8412: hr_lookups hl1,
8413: -- hr_lookups hl2,
8414: hr_all_organization_units haou,
8415: per_assignment_extra_info paei,
8416: per_all_assignments_f paaf,
8417: per_periods_of_service ppos,
8418: per_all_people_f papf
8419: where papf.business_group_id = p_business_group_id
8420: and papf.current_employee_flag = 'Y'
8642: hr_lookups hl1,
8643: -- hr_lookups hl2,
8644: hr_all_organization_units haou,
8645: per_assignment_extra_info paei,
8646: per_all_assignments_f paaf,
8647: per_periods_of_service ppos,
8648: per_all_people_f papf
8649: where papf.business_group_id = p_business_group_id
8650: and papf.current_employee_flag = 'Y'
8675: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)), '9999999999') <
8676: any
8677: (
8678: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id,2009)) lookup_code
8679: from per_all_assignments_f paaf1
8680: where paaf1.person_id = papf.person_id
8681: and paaf1.primary_flag = 'Y'
8682: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id,2009)) is not null
8683: and paaf1.effective_end_date + 1 = paaf.effective_start_date
8897: hr_lookups hl1,
8898: -- hr_lookups hl2,
8899: hr_all_organization_units haou,
8900: per_assignment_extra_info paei,
8901: per_all_assignments_f paaf,
8902: per_periods_of_service ppos,
8903: per_all_people_f papf
8904: where papf.business_group_id = p_business_group_id
8905: and papf.current_employee_flag = 'Y'
9166: hr_lookups hl1,
9167: -- hr_lookups hl2,
9168: hr_all_organization_units haou,
9169: per_assignment_extra_info paei,
9170: per_all_assignments_f paaf,
9171: per_periods_of_service ppos,
9172: per_all_people_f papf
9173: where papf.business_group_id = p_business_group_id
9174: and papf.current_employee_flag = 'Y'
9407: end populate_ee_table_new;
9408:
9409: procedure populate_ee_table_EEWF_new
9410: (
9411: p_report_date in per_all_assignments_f.effective_end_date%type,
9412: p_business_group_id in per_all_assignments_f.business_group_id%type,
9413: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
9414: ) is
9415:
9408:
9409: procedure populate_ee_table_EEWF_new
9410: (
9411: p_report_date in per_all_assignments_f.effective_end_date%type,
9412: p_business_group_id in per_all_assignments_f.business_group_id%type,
9413: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
9414: ) is
9415:
9416: begin