[Home] [Help]
104: occupational_level_id hr_lookups.lookup_code%type,
105: occupational_category_id hr_lookups.lookup_code%type,
106: occupational_level hr_lookups.meaning%type,
107: occupational_category hr_lookups.meaning%type,
108: race per_all_people_f.per_information4%type,
109: sex per_all_people_f.sex%type,
110: annual_income number
111: );
112:
105: occupational_category_id hr_lookups.lookup_code%type,
106: occupational_level hr_lookups.meaning%type,
107: occupational_category hr_lookups.meaning%type,
108: race per_all_people_f.per_information4%type,
109: sex per_all_people_f.sex%type,
110: annual_income number
111: );
112:
113: type r_averages is record
157: occupational_level_id hr_lookups.lookup_code%type,
158: occupational_category_id hr_lookups.lookup_code%type,
159: occupational_level hr_lookups.meaning%type,
160: occupational_category hr_lookups.meaning%type,
161: race per_all_people_f.per_information4%type,
162: sex per_all_people_f.sex%type,
163: employment_type varchar2(30), --Permanent/Non Permanent
164: foreigner varchar2(30), --Foreigner or not
165: annual_income number
158: occupational_category_id hr_lookups.lookup_code%type,
159: occupational_level hr_lookups.meaning%type,
160: occupational_category hr_lookups.meaning%type,
161: race per_all_people_f.per_information4%type,
162: sex per_all_people_f.sex%type,
163: employment_type varchar2(30), --Permanent/Non Permanent
164: foreigner varchar2(30), --Foreigner or not
165: annual_income number
166: );
744: l_eea_balance_type_id pay_balance_types.balance_type_id%type;
745: l_input_value_id pay_input_values_f.input_value_id%type;
746: l_index number;
747: l_proc constant varchar2(60) := g_package || 'get_avg_5_lowest_salary';
748: l_race per_all_people_f.per_information4%type; -- Bug 4413678
749:
750: begin
751:
752: hr_utility.set_location('Entering ' || l_proc, 10);
818:
819: -- Bug 4413678: Begin
820: Select per_information4
821: into l_race
822: From per_all_people_f papf
823: Where papf.person_id = l_assignment.person_id
824: and p_report_date between papf.effective_start_date and papf.effective_end_date;
825: -- Bug 4413678: End
826:
1198: -- This function returns the person's legislated employment type (permanent or non-permanent)
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);
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'
1881: and paaf.person_id = papf.person_id
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'
2100: and paaf.person_id = papf.person_id
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
2337: and paaf.person_id = papf.person_id
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
2584: and paaf.person_id = papf.person_id
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'
2834: and ppos.person_id = papf.person_id
2835: and ppos.date_start between add_months(p_report_date, -12) + 1 and p_report_date
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'
3064: and ppos.person_id = papf.person_id
3065: and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) > add_months(p_report_date, -12) + 1
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'
3319: and ppos.person_id = papf.person_id
3320: and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) between add_months(p_report_date, -12) + 1 and p_report_date
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'
3585: and ppos.person_id = papf.person_id
3586: and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) between add_months(p_report_date, -12) + 1 and p_report_date
4262:
4263: Procedure cat_lev_data ( p_legal_entity_id IN hr_all_organization_units.organization_id%type
4264: , p_occupational_level IN hr_lookups.meaning%type
4265: , p_occupational_category IN hr_lookups.meaning%type
4266: , p_race IN per_all_people_f.per_information4%type
4267: , p_sex IN per_all_people_f.sex%type
4268: , p_income IN number
4269: , p_occupational_level_id IN hr_lookups.lookup_code%type
4270: , p_occupational_category_id IN hr_lookups.lookup_code%type
4263: Procedure cat_lev_data ( p_legal_entity_id IN hr_all_organization_units.organization_id%type
4264: , p_occupational_level IN hr_lookups.meaning%type
4265: , p_occupational_category IN hr_lookups.meaning%type
4266: , p_race IN per_all_people_f.per_information4%type
4267: , p_sex IN per_all_people_f.sex%type
4268: , p_income IN number
4269: , p_occupational_level_id IN hr_lookups.lookup_code%type
4270: , p_occupational_category_id IN hr_lookups.lookup_code%type
4271: ) is
4689: l_eea_balance_type_id pay_balance_types.balance_type_id%type;
4690: l_input_value_id pay_input_values_f.input_value_id%type;
4691: l_index number;
4692: l_proc constant varchar2(60) := g_package || 'get_avg_5_lowest_salary';
4693: l_race per_all_people_f.per_information4%type; -- Bug 4413678
4694: l_sex per_all_people_f.sex%type;
4695: --Changes for Bug 7237663
4696: l_er_income number;
4697: l_er_annual_income number;
4690: l_input_value_id pay_input_values_f.input_value_id%type;
4691: l_index number;
4692: l_proc constant varchar2(60) := g_package || 'get_avg_5_lowest_salary';
4693: l_race per_all_people_f.per_information4%type; -- Bug 4413678
4694: l_sex per_all_people_f.sex%type;
4695: --Changes for Bug 7237663
4696: l_er_income number;
4697: l_er_annual_income number;
4698: l_er_balance_type_id pay_balance_types.balance_type_id%type;
4787:
4788: -- Bug 4413678: Begin
4789: Select per_information4, papf.sex
4790: into l_race, l_sex
4791: From per_all_people_f papf
4792: Where papf.person_id = l_assignment.person_id
4793: and p_report_date between papf.effective_start_date and papf.effective_end_date;
4794: -- Bug 4413678: End
4795:
5647:
5648: Procedure cat_lev_data ( p_legal_entity_id IN hr_all_organization_units.organization_id%type
5649: , p_occupational_level IN hr_lookups.meaning%type
5650: , p_occupational_category IN hr_lookups.meaning%type
5651: , p_race IN per_all_people_f.per_information4%type
5652: , p_sex IN per_all_people_f.sex%type
5653: , p_income IN number
5654: , p_occupational_level_id IN hr_lookups.lookup_code%type
5655: , p_occupational_category_id IN hr_lookups.lookup_code%type
5648: Procedure cat_lev_data ( p_legal_entity_id IN hr_all_organization_units.organization_id%type
5649: , p_occupational_level IN hr_lookups.meaning%type
5650: , p_occupational_category IN hr_lookups.meaning%type
5651: , p_race IN per_all_people_f.per_information4%type
5652: , p_sex IN per_all_people_f.sex%type
5653: , p_income IN number
5654: , p_occupational_level_id IN hr_lookups.lookup_code%type
5655: , p_occupational_category_id IN hr_lookups.lookup_code%type
5656: , p_table IN OUT nocopy t_E_differential
6082: l_input_value_id pay_input_values_f.input_value_id%type;
6083: l_input_value_id2 pay_input_values_f.input_value_id%type;
6084: l_index number;
6085: l_proc constant varchar2(60) := g_package || 'get_avg_5_lowest_salary';
6086: l_race per_all_people_f.per_information4%type; -- Bug 4413678
6087: l_sex per_all_people_f.sex%type;
6088: --Changes for Bug 7237663
6089: l_er_income number;
6090: l_er_annual_income number;
6083: l_input_value_id2 pay_input_values_f.input_value_id%type;
6084: l_index number;
6085: l_proc constant varchar2(60) := g_package || 'get_avg_5_lowest_salary';
6086: l_race per_all_people_f.per_information4%type; -- Bug 4413678
6087: l_sex per_all_people_f.sex%type;
6088: --Changes for Bug 7237663
6089: l_er_income number;
6090: l_er_annual_income number;
6091: l_er_balance_type_id pay_balance_types.balance_type_id%type;
6188: decode(sign(replace(substr(papf.PER_INFORMATION11,1,10),'/','')
6189: -'19940427')
6190: ,-1,null,'F')))
6191: into l_race, l_sex, l_type
6192: From per_all_people_f papf
6193: Where papf.person_id = l_assignment.person_id
6194: and p_report_date between papf.effective_start_date and papf.effective_end_date;
6195: -- Bug 4413678: End
6196:
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'
7468: and paaf.person_id = papf.person_id
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'
7687: and paaf.person_id = papf.person_id
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
7924: and paaf.person_id = papf.person_id
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
8171: and paaf.person_id = papf.person_id
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'
8421: and ppos.person_id = papf.person_id
8422: and ppos.date_start between add_months(p_report_date, -12) + 1 and p_report_date
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'
8651: and ppos.person_id = papf.person_id
8652: and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) > add_months(p_report_date, -12) + 1
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'
8906: and ppos.person_id = papf.person_id
8907: and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) between add_months(p_report_date, -12) + 1 and p_report_date
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'
9175: and ppos.person_id = papf.person_id
9176: and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) between add_months(p_report_date, -12) + 1 and p_report_date