[Home] [Help]
1: PACKAGE BODY ghr_pay_calc AS
2: /* $Header: ghpaycal.pkb 120.72.12010000.10 2008/12/03 11:57:00 utokachi ship $ */
3:
4:
5: FUNCTION get_form_item_name
6: RETURN VARCHAR2 IS
7: BEGIN
8: -- Because forms can not directly read this global variable we need
9: -- to write just a little function on the server to return the value!!
10: RETURN(ghr_pay_calc.form_item_name);
11: --
12: END get_form_item_name;
13: --
14: PROCEDURE set_form_item_name(p_value IN VARCHAR2) IS
12: END get_form_item_name;
13: --
14: PROCEDURE set_form_item_name(p_value IN VARCHAR2) IS
15: BEGIN
16: ghr_pay_calc.form_item_name := p_value;
17: --
18: END set_form_item_name;
19:
20: --
27: RETURN BOOLEAN IS
28: --
29: l_pos_ei_data per_position_extra_info%ROWTYPE;
30: l_range_or_match pay_user_tables.range_or_match%type;
31: l_retained_grade ghr_pay_calc.retained_grade_rec_type;
32: l_user_table_id pay_user_tables.user_table_id%type;
33: l_proc varchar2(30) := 'get_open_pay_range';
34:
35: cursor c_pay_type is
139: --
140: -- Next get the name of the retrieved user_table_id
141: IF l_user_table_id IS NOT NULL THEN
142: --
143: l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
144: -- Note: Must have a table name if we have a table_id
145: IF l_user_table_name = ghr_pay_calc.l_standard_table_name THEN
146: RETURN('0');
147: ELSE
141: IF l_user_table_id IS NOT NULL THEN
142: --
143: l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
144: -- Note: Must have a table name if we have a table_id
145: IF l_user_table_name = ghr_pay_calc.l_standard_table_name THEN
146: RETURN('0');
147: ELSE
148: RETURN('6');
149: END IF;
242: ,p_pa_request_id IN NUMBER
243: ,p_open_range_out_basic_pay IN NUMBER
244: ,p_open_out_locality_adj IN NUMBER
245: )
246: RETURN ghr_pay_calc.pay_calc_in_rec_type IS
247:
248: l_populated_rec ghr_pay_calc.pay_calc_in_rec_type;
249: --
250: BEGIN
244: ,p_open_out_locality_adj IN NUMBER
245: )
246: RETURN ghr_pay_calc.pay_calc_in_rec_type IS
247:
248: l_populated_rec ghr_pay_calc.pay_calc_in_rec_type;
249: --
250: BEGIN
251: --
252: l_populated_rec.person_id := p_person_id;
284: RETURN l_populated_rec;
285:
286: END;
287: --
288: PROCEDURE validate_in_data(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type) IS
289: --
290: CURSOR c_chk_pos IS
291: SELECT 1
292: FROM hr_all_positions_f pos
296: l_ret_val BOOLEAN := FALSE;
297: BEGIN
298: -- Must have a person
299: IF p_pay_calc_data.person_id IS NULL THEN
300: ghr_pay_calc.form_item_name := 'PAR.EMPLOYEE_LAST_NAME';
301: hr_utility.set_message(8301,'GHR_38244_PAY_CALC_NO_PER');
302: raise pay_calc_message;
303: END IF;
304:
303: END IF;
304:
305: -- Must have a position -- well that is kinda obvious!!!
306: IF p_pay_calc_data.position_id IS NULL THEN
307: ghr_pay_calc.form_item_name := 'PAR.TO_POSITION_TITLE';
308: hr_utility.set_message(8301,'GHR_38016_PAY_CALC_NO_POS');
309: raise pay_calc_message;
310: END IF;
311:
327: -- Must have a noa if the family is 'SALARY_CHG'
328: IF SUBSTR(p_pay_calc_data.noa_family_code ,1,8) = 'GHR_SAL_'
329: -- IF p_pay_calc_data.noa_family_code = 'SALARY_CHG'
330: AND p_pay_calc_data.noa_code IS NULL THEN
331: ghr_pay_calc.form_item_name := 'PAR.FIRST_NOA_CODE';
332: hr_utility.set_message(8301,'GHR_38246_PAY_CALC_NO_NOA');
333: raise pay_calc_message;
334: END IF;
335:
337: -- defaulted to sysdate
338:
339: -- Must have a pay rate determinant (e.g 0,5,6,7)
340: IF p_pay_calc_data.pay_rate_determinant IS NULL THEN
341: ghr_pay_calc.form_item_name := 'PAR.PAY_RATE_DETERMINANT';
342: hr_utility.set_message(8301,'GHR_38021_PAY_CALC_NO_PRD');
343: raise pay_calc_message;
344: END IF;
345:
356: END IF;
357:
358: -- Must have a step_or_rate (e.g 01,02) which is associated to the position
359: IF p_pay_calc_data.step_or_rate IS NULL THEN
360: ghr_pay_calc.form_item_name := 'PAR.TO_STEP_OR_RATE';
361: hr_utility.set_message(8301,'GHR_38019_PAY_CALC_NO_STEP');
362: raise pay_calc_message;
363: END IF;
364:
478: --
479: hr_utility.set_message(8301, 'GHR_38251_NO_CONVERSION');
480: hr_utility.set_message_token('FROM_PAY_BASIS',p_in_pay_basis);
481: hr_utility.set_message_token('TO_PAY_BASIS',p_out_pay_basis);
482: raise ghr_pay_calc.pay_calc_message;
483: END;
484: --
485: ---------------------------
486: --
629:
630: l_retained_pay_plan VARCHAR2(30);
631: l_retained_grade VARCHAR2(30);
632: l_retained_step_or_rate VARCHAR2(30);
633: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
634: l_temp_step VARCHAR2(30);
635: BEGIN
636:
637: l_retained_pay_plan := p_retained_pay_plan;
790: hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
791: hr_utility.set_message_token('GRADE',p_grade_or_level);
792: -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
793: hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date));
794: raise ghr_pay_calc.pay_calc_message;
795: ELSE
796: p_PT_value := l_PT_value;
797: p_PT_eff_start_date := l_PT_eff_start_date;
798: p_PT_eff_end_date := l_PT_eff_end_date;
823: FROM pay_user_column_instances_f cin
824: ,pay_user_rows_f urw
825: ,pay_user_columns col
826: ,pay_user_tables utb
827: WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name
828: AND col.user_table_id = utb.user_table_id
829: AND col.user_column_name = p_step_or_rate
830: AND urw.user_table_id = utb.user_table_id
831: AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
839: l_record_found := TRUE;
840: IF l_pay_plan_value IS NULL THEN
841: -- Set tokens to give name of pay table(standard), pay plan, grade, step and date
842: hr_utility.set_message(8301, 'GHR_38252_NULL_PAY_PLAN_VALUE');
843: hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.l_standard_table_name );
844: hr_utility.set_message_token('STEP',p_step_or_rate);
845: hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
846: hr_utility.set_message_token('GRADE',p_grade_or_level);
847: -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
852: --
853: IF NOT l_record_found THEN
854: -- Set tokens to give name of pay table(standard), pay plan, grade, step and date
855: hr_utility.set_message(8301, 'GHR_38253_NO_PAY_PLAN_VALUE');
856: hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.l_standard_table_name);
857: hr_utility.set_message_token('STEP',p_step_or_rate);
858: hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
859: hr_utility.set_message_token('GRADE',p_grade_or_level);
860: -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
908:
909: END LEO_position;
910: --
911: -- This local procedure gets the default basic pay to do the comparison in the locality pay
912: FUNCTION get_GM_GH_def_basic_pay(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
913: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type)
914: RETURN NUMBER IS
915:
916: l_std_user_table_id NUMBER;
909: END LEO_position;
910: --
911: -- This local procedure gets the default basic pay to do the comparison in the locality pay
912: FUNCTION get_GM_GH_def_basic_pay(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
913: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type)
914: RETURN NUMBER IS
915:
916: l_std_user_table_id NUMBER;
917:
930:
931: CURSOR get_std_user_table_id IS
932: SELECT utb.user_table_id
933: FROM pay_user_tables utb
934: WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
935:
936: BEGIN
937: -- First get the id of standard pay table for later use
938: FOR c_rec IN get_std_user_table_id LOOP
987:
988:
989: ---------------------------
990: ---------------------------
991: PROCEDURE get_locality_adj (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
992: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
993: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
994: --
995: l_basic_pay_pa NUMBER; -- This is the basis_pay based in as a PA format
988:
989: ---------------------------
990: ---------------------------
991: PROCEDURE get_locality_adj (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
992: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
993: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
994: --
995: l_basic_pay_pa NUMBER; -- This is the basis_pay based in as a PA format
996: l_basic_pay_pos NUMBER;
989: ---------------------------
990: ---------------------------
991: PROCEDURE get_locality_adj (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
992: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
993: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
994: --
995: l_basic_pay_pa NUMBER; -- This is the basis_pay based in as a PA format
996: l_basic_pay_pos NUMBER;
997: l_basic_pay_pos_pa NUMBER;
1010: --
1011: l_proc VARCHAR2(30) := 'get_locality_adj';
1012: l_new_step_or_rate VARCHAR2(30); --AVR
1013: l_session ghr_history_api.g_session_var_type;
1014: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1015:
1016:
1017: l_user_table_id pay_user_tables.user_table_id%type;
1018: l_GM_unadjusted_rate NUMBER; --AVR
1044: l_essl_table BOOLEAN := FALSE;
1045:
1046: -- Bug 3021003
1047: l_ret_flag BOOLEAN := FALSE;
1048: l_retained_grade ghr_pay_calc.retained_grade_rec_type;
1049:
1050: BEGIN
1051:
1052: l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1189: l_locality_adj := 0;
1190: --Bug 4740036 Start
1191: --Bug 4740036 Start
1192:
1193: ELSIF ghr_pay_calc.get_user_table_name(l_user_table_id) NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name)
1194: AND l_pay_plan in ('GG','GS')
1195: AND p_pay_calc_data.pay_rate_determinant IN ('6','E','F')
1196: AND ghr_pay_calc.LEO_position (l_dummy
1197: ,p_pay_calc_data.position_id
1192:
1193: ELSIF ghr_pay_calc.get_user_table_name(l_user_table_id) NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name)
1194: AND l_pay_plan in ('GG','GS')
1195: AND p_pay_calc_data.pay_rate_determinant IN ('6','E','F')
1196: AND ghr_pay_calc.LEO_position (l_dummy
1197: ,p_pay_calc_data.position_id
1198: ,l_dummy
1199: ,l_dummy
1200: ,p_pay_calc_data.effective_date) THEN
1198: ,l_dummy
1199: ,l_dummy
1200: ,p_pay_calc_data.effective_date) THEN
1201: hr_utility.set_location('AB p_pay_calc_data.pay_rate_determinant ' || p_pay_calc_data.pay_rate_determinant,99);
1202: FOR c_rec IN c_user_table_id(ghr_pay_calc.l_standard_table_name) LOOP
1203: l_std_user_table_id := c_rec.user_table_id;
1204: END LOOP;
1205: hr_utility.set_location('AB l_std_user_table_id ' || l_std_user_table_id,99);
1206: IF p_pay_calc_data.pay_rate_determinant = '6' THEN
1275: ,l_dummy_date
1276: ,l_dummy_date);
1277: END IF;
1278: hr_utility.set_location('AB l_std_basic_pay ' || l_std_basic_pay,99);
1279: l_adjustment_percentage := ghr_pay_calc.get_leo_lpa_percentage
1280: (p_pay_calc_data.duty_station_id
1281: ,p_pay_calc_data.effective_date);
1282: hr_utility.set_location('AB l_adjustment_percentage ' || l_adjustment_percentage,99);
1283: l_std_locality_adj := ROUND(l_std_basic_pay * (NVL(l_adjustment_percentage,0)/100),0);
1468: IF p_pay_calc_out_data.basic_pay >= p_pay_calc_data.current_adj_basic_pay THEN
1469: l_locality_adj := 0;
1470: --
1471: ELSE
1472: l_locality_adj := ghr_pay_calc.convert_amount
1473: (p_pay_calc_data.current_adj_basic_pay - p_pay_calc_out_data.basic_pay
1474: ,p_pay_calc_data.pay_basis
1475: ,'PA');
1476: --
1480: -- should never have really got here as the basic pay should have stopped us
1481: -- calculating if we didn't know how to
1482: hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
1483: hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
1484: raise ghr_pay_calc.unable_to_calculate;
1485: END IF; -- End of PRD checks
1486: --
1487: END IF; -- End of LEO checks
1488: --
1505: --
1506: END get_locality_adj;
1507: --
1508: ---------------------------
1509: PROCEDURE get_adj_basic_pay (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1510: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1511:
1512: --
1513: l_locality_adj_conv NUMBER;
1506: END get_locality_adj;
1507: --
1508: ---------------------------
1509: PROCEDURE get_adj_basic_pay (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1510: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1511:
1512: --
1513: l_locality_adj_conv NUMBER;
1514: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1510: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1511:
1512: --
1513: l_locality_adj_conv NUMBER;
1514: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1515:
1516: BEGIN
1517:
1518: l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1537: ELSE
1538: -- convert the locality_adj (which is always 'PA') to the pay_basis of the
1539: -- basic pay so they can then be added together
1540: --
1541: l_locality_adj_conv := ghr_pay_calc.convert_amount(p_pay_calc_out_data.locality_adj
1542: ,'PA'
1543: ,p_pay_calc_data.pay_basis);
1544: --
1545: p_pay_calc_out_data.adj_basic_pay := NVL(p_pay_calc_out_data.basic_pay,0)
1588:
1589: END get_ppi_amount;
1590:
1591: ---------------------------
1592: PROCEDURE get_other_pay_amount (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1593: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1594:
1595: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1596:
1589: END get_ppi_amount;
1590:
1591: ---------------------------
1592: PROCEDURE get_other_pay_amount (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1593: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1594:
1595: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1596:
1597: BEGIN
1591: ---------------------------
1592: PROCEDURE get_other_pay_amount (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1593: ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1594:
1595: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1596:
1597: BEGIN
1598:
1599: l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1623: RAISE;
1624: END get_other_pay_amount;
1625:
1626: ---------------------------
1627: PROCEDURE get_total_salary (p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
1628: ,p_pay_basis IN VARCHAR2) IS
1629:
1630: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1631: BEGIN
1626: ---------------------------
1627: PROCEDURE get_total_salary (p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
1628: ,p_pay_basis IN VARCHAR2) IS
1629:
1630: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1631: BEGIN
1632:
1633: l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1634:
1729: --
1730: l_second_noa_family_code ghr_families.noa_family_code%TYPE;
1731: l_run_pay_calc_again BOOLEAN := FALSE;
1732: --
1733: l_pay_calc_data ghr_pay_calc.pay_calc_in_rec_type; -- This is the main IN record structure
1734: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type; -- This is the main OUT record structure
1735: l_retained_grade ghr_pay_calc.retained_grade_rec_type; -- This contains retained grade info if needed
1736: --
1737: l_message_set BOOLEAN := FALSE;
1730: l_second_noa_family_code ghr_families.noa_family_code%TYPE;
1731: l_run_pay_calc_again BOOLEAN := FALSE;
1732: --
1733: l_pay_calc_data ghr_pay_calc.pay_calc_in_rec_type; -- This is the main IN record structure
1734: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type; -- This is the main OUT record structure
1735: l_retained_grade ghr_pay_calc.retained_grade_rec_type; -- This contains retained grade info if needed
1736: --
1737: l_message_set BOOLEAN := FALSE;
1738: l_calculated BOOLEAN := TRUE;
1731: l_run_pay_calc_again BOOLEAN := FALSE;
1732: --
1733: l_pay_calc_data ghr_pay_calc.pay_calc_in_rec_type; -- This is the main IN record structure
1734: l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type; -- This is the main OUT record structure
1735: l_retained_grade ghr_pay_calc.retained_grade_rec_type; -- This contains retained grade info if needed
1736: --
1737: l_message_set BOOLEAN := FALSE;
1738: l_calculated BOOLEAN := TRUE;
1739: --
2270: END IF;
2271: ---Bug 7557159 Start
2272: IF p_pay_rate_determinant = 'D' THEN
2273: hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2274: raise ghr_pay_calc.open_pay_range_mesg;
2275: END IF;
2276: ---Bug 7557159 End
2277: if get_open_pay_range ( l_position_id
2278: , p_person_id
2281: , NVL(p_effective_date,TRUNC(sysdate)) ) then
2282: --6489042 during appointment of ESSL employees of PRD 2 enabling basic pay for user entry
2283: if p_pay_rate_determinant <> '2' then
2284: hr_utility.set_message(8301, 'GHR_38713_OPEN_PAY_RANGE');
2285: raise ghr_pay_calc.open_pay_range_mesg;
2286: elsif p_pay_rate_determinant = '2' and l_noa_family_code IN ('APP') then
2287: hr_utility.set_message(8301, 'GHR_38713_OPEN_PAY_RANGE');
2288: raise ghr_pay_calc.open_pay_range_mesg;
2289: end if;
2284: hr_utility.set_message(8301, 'GHR_38713_OPEN_PAY_RANGE');
2285: raise ghr_pay_calc.open_pay_range_mesg;
2286: elsif p_pay_rate_determinant = '2' and l_noa_family_code IN ('APP') then
2287: hr_utility.set_message(8301, 'GHR_38713_OPEN_PAY_RANGE');
2288: raise ghr_pay_calc.open_pay_range_mesg;
2289: end if;
2290: end if;
2291: end if;
2292: --- end if;
2417: l_pay_calc_out_data.pay_table_id := l_pay_calc_data.user_table_id;
2418: l_pay_plan := l_pay_calc_data.pay_plan;
2419: -- Bug#4748583 Added the following IF Condition to set the PRD to '6' for 0491 table.
2420: -- Bug#5089732 GL Pay Plan
2421: IF get_user_table_name(l_pay_calc_out_data.pay_table_id) = ghr_pay_calc.l_spl491_table_name AND
2422: (l_pay_calc_data.pay_plan <> 'GL' OR
2423: (l_pay_plan = 'GG' and
2424: l_pay_calc_data.effective_date < to_date('2006/01/08','YYYY/MM/DD')
2425: )
2429: END IF;
2430: -- Bug#4748583
2431: END IF;
2432: --Bug#5089732 Set the Pay Rate Determinant for GL,GG Pay Plans
2433: IF get_user_table_name(l_pay_calc_out_data.calculation_pay_table_id) = ghr_pay_calc.l_spl491_table_name AND
2434: (l_pay_plan = 'GL' OR
2435: (l_pay_plan = 'GG' and
2436: l_pay_calc_data.effective_date >= to_date('2006/01/08','YYYY/MM/DD')
2437: )
2704: (l_pay_calc_data
2705: ,l_pay_calc_out_data
2706: ,l_message_set
2707: ,l_calculated);
2708: WHEN ghr_pay_calc.pay_calc_message THEN
2709: l_message_set := TRUE;
2710: l_calculated := TRUE;
2711: ---Bug 7423379 Start (Fix was revoked - AVR 10/24)
2712: ----IF l_pay_plan = 'AD' THEN
2790: l_pay_calc_out_data.open_localityadj_field := TRUE;
2791: end if;*/
2792: --Begin Bug# 7557159
2793: IF l_pay_plan='GS' AND l_pay_rate_determinant='D' THEN
2794: l_prd_d_pay_amount := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => l_pay_plan
2795: ,p_grade_or_level => l_grade_or_level
2796: ,p_step_or_rate => '10'
2797: ,p_effective_date => p_effective_date);
2798:
2863: RAISE;
2864: END main_pay_calc;
2865:
2866:
2867: PROCEDURE sql_main_pay_calc (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
2868: ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
2869: ,p_message_set OUT NOCOPY BOOLEAN
2870: ,p_calculated OUT NOCOPY BOOLEAN
2871: ) IS
2864: END main_pay_calc;
2865:
2866:
2867: PROCEDURE sql_main_pay_calc (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
2868: ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
2869: ,p_message_set OUT NOCOPY BOOLEAN
2870: ,p_calculated OUT NOCOPY BOOLEAN
2871: ) IS
2872: l_message_set BOOLEAN;
3002: hr_utility.set_location('effective_date ...'|| to_char(p_effective_date,'DD-MON-YYYY'),5);
3003: hr_utility.set_location('In new current adj basic pay ...'|| to_char(p_cur_adj_basic_pay),5);
3004: hr_utility.set_location('In new basic pay ...'|| to_char(p_new_basic_pay),5);
3005:
3006: ghr_pay_calc.get_pay_table_value (p_user_table_id => p_user_table_id
3007: ,p_pay_plan => p_pay_plan
3008: ,p_grade_or_level => p_grade_or_level
3009: ,p_step_or_rate => p_step_or_rate
3010: ,p_effective_date => p_effective_date
3012: ,p_PT_eff_start_date => l_PT_eff_start_date
3013: ,p_PT_eff_end_date => l_PT_eff_end_date);
3014:
3015: hr_utility.set_location('l_PT_value ...'|| to_char(l_PT_value),5);
3016: ghr_pay_calc.get_pay_table_value (p_user_table_id => p_user_table_id
3017: ,p_pay_plan => p_pay_plan
3018: ,p_grade_or_level => p_grade_or_level
3019: ,p_step_or_rate => p_step_or_rate
3020: ,p_effective_date => (l_PT_eff_start_date - 1)
3022: ,p_PT_eff_start_date => l_PT_o_eff_start_date
3023: ,p_PT_eff_end_date => l_PT_o_eff_end_date);
3024:
3025: hr_utility.set_location('l_PT_o_value ...'|| to_char(l_PT_o_value),5);
3026: l_SPT_value := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => p_pay_plan
3027: ,p_grade_or_level => p_grade_or_level
3028: ,p_step_or_rate => p_step_or_rate
3029: ,p_effective_date => p_effective_date);
3030:
3028: ,p_step_or_rate => p_step_or_rate
3029: ,p_effective_date => p_effective_date);
3030:
3031: hr_utility.set_location('l_SPT_value ...'|| to_char(l_SPT_value),5);
3032: l_SPT_o_value := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => p_pay_plan
3033: ,p_grade_or_level => p_grade_or_level
3034: ,p_step_or_rate => p_step_or_rate
3035: ,p_effective_date => (l_PT_eff_start_date -1) );
3036:
3070:
3071: end get_locality_adj_894_PRDM_GS;
3072:
3073: PROCEDURE get_locality_adj_894_PRDM_GM
3074: (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3075: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3076: ,p_new_std_relative_rate OUT NOCOPY NUMBER
3077: ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3078: ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3071: end get_locality_adj_894_PRDM_GS;
3072:
3073: PROCEDURE get_locality_adj_894_PRDM_GM
3074: (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3075: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3076: ,p_new_std_relative_rate OUT NOCOPY NUMBER
3077: ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3078: ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3079:
3107:
3108: CURSOR get_std_user_table_id IS
3109: SELECT utb.user_table_id
3110: FROM pay_user_tables utb
3111: WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3112:
3113: BEGIN
3114: hr_utility.set_location('Entering ...'|| l_proc,5);
3115: -- First get the id of standard pay table for later use
3204: IF l_new_std_relative_rate > new_adj_basic_pay THEN
3205: new_adj_basic_pay := l_new_std_relative_rate;
3206: new_locality_adj := new_adj_basic_pay - l_new_basic_pay;
3207: ELSIF (l_new_basic_pay > new_adj_basic_pay) AND
3208: (l_user_table_name <> ghr_pay_calc.l_standard_table_name) THEN
3209: new_adj_basic_pay := l_new_basic_pay;
3210: new_locality_adj := 0;
3211: END IF;
3212:
3362: END get_pay_basis;
3363:
3364: ---------------------------
3365: PROCEDURE get_locality_894_itpay
3366: (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3367: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3368: ,p_new_basic_pay IN NUMBER
3369: ,p_GM_unadjusted_rate OUT NOCOPY NUMBER
3370: ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3363:
3364: ---------------------------
3365: PROCEDURE get_locality_894_itpay
3366: (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3367: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3368: ,p_new_basic_pay IN NUMBER
3369: ,p_GM_unadjusted_rate OUT NOCOPY NUMBER
3370: ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3371: ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3401:
3402: CURSOR get_std_user_table_id IS
3403: SELECT utb.user_table_id
3404: FROM pay_user_tables utb
3405: WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3406:
3407: ----
3408: l_assignment_id per_assignments_f.assignment_id%type;
3409: l_value varchar2(60);
3539: END get_locality_894_itpay;
3540:
3541: ---------------------------
3542: PROCEDURE get_locality_892_itpay
3543: (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3544: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3545: ,p_new_basic_pay IN NUMBER
3546: ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3547: ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3540:
3541: ---------------------------
3542: PROCEDURE get_locality_892_itpay
3543: (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3544: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3545: ,p_new_basic_pay IN NUMBER
3546: ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3547: ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3548:
3578:
3579: CURSOR get_std_user_table_id IS
3580: SELECT utb.user_table_id
3581: FROM pay_user_tables utb
3582: WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3583:
3584: ----
3585: l_assignment_id per_assignments_f.assignment_id%type;
3586: l_value varchar2(60);
3714: END get_locality_892_itpay;
3715:
3716: -- FWFA Changes. Created procedures get_special_pay_table_value, special_rate_pay_calc
3717: -- Create function fwfa_pay_calc
3718: FUNCTION fwfa_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3719: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type)
3720: RETURN BOOLEAN IS
3721: CURSOR cur_ppl(p_pay_plan VARCHAR2) IS
3722: SELECT 1
3715:
3716: -- FWFA Changes. Created procedures get_special_pay_table_value, special_rate_pay_calc
3717: -- Create function fwfa_pay_calc
3718: FUNCTION fwfa_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3719: ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type)
3720: RETURN BOOLEAN IS
3721: CURSOR cur_ppl(p_pay_plan VARCHAR2) IS
3722: SELECT 1
3723: FROM ghr_pay_plans ppl
3762: END LOOP;
3763:
3764:
3765: IF p_pay_calc_data.effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
3766: l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
3767: --
3768: FOR cur_ppl_rec IN cur_ppl(l_pay_plan)
3769: LOOP
3770: l_gs_equivalent := TRUE;
3779: g_fwfa_pay_calc_flag := FALSE;
3780: RETURN FALSE;
3781: END IF;
3782: ELSE
3783: IF l_user_table_name NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) AND
3784: l_gs_equivalent THEN
3785: g_fwfa_pay_calc_flag := TRUE;
3786: RETURN TRUE;
3787: ELSE
3867: RAISE;
3868: --
3869: END get_special_pay_table_value;
3870:
3871: PROCEDURE special_rate_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3872: ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
3873: ,p_retained_grade IN OUT NOCOPY ghr_pay_calc.retained_grade_rec_type) IS
3874:
3875: l_new_prd VARCHAR2(30);
3868: --
3869: END get_special_pay_table_value;
3870:
3871: PROCEDURE special_rate_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3872: ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
3873: ,p_retained_grade IN OUT NOCOPY ghr_pay_calc.retained_grade_rec_type) IS
3874:
3875: l_new_prd VARCHAR2(30);
3876: l_new_step VARCHAR2(30);
3869: END get_special_pay_table_value;
3870:
3871: PROCEDURE special_rate_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3872: ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
3873: ,p_retained_grade IN OUT NOCOPY ghr_pay_calc.retained_grade_rec_type) IS
3874:
3875: l_new_prd VARCHAR2(30);
3876: l_new_step VARCHAR2(30);
3877: l_term_ret_pay_prd BOOLEAN;
3975: g_pay_table_upd_flag := FALSE;
3976: ELSE
3977: p_position_pay_table := p_special_rate_table;
3978: p_new_prd := '6';
3979: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
3980: g_pay_table_upd_flag := FALSE;
3981: END IF;
3982: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
3983: p_position_pay_table := p_special_rate_table;
3981: END IF;
3982: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
3983: p_position_pay_table := p_special_rate_table;
3984: p_new_prd := '6';
3985: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
3986: g_pay_table_upd_flag := FALSE;
3987: ELSE
3988: p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
3989: p_new_prd := '6';
3984: p_new_prd := '6';
3985: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
3986: g_pay_table_upd_flag := FALSE;
3987: ELSE
3988: p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
3989: p_new_prd := '6';
3990: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
3991: g_pay_table_upd_flag := TRUE;
3992: END IF;
3986: g_pay_table_upd_flag := FALSE;
3987: ELSE
3988: p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
3989: p_new_prd := '6';
3990: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
3991: g_pay_table_upd_flag := TRUE;
3992: END IF;
3993: ELSIF p_prd IN ('E','F') THEN
3994: IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
3999: g_pay_table_upd_flag := FALSE;
4000: ELSE
4001: p_retained_pay_table := p_special_rate_table;
4002: p_new_prd := p_prd;
4003: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4004: g_pay_table_upd_flag := FALSE;
4005: END IF;
4006: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4007: p_retained_pay_table := p_special_rate_table;
4005: END IF;
4006: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4007: p_retained_pay_table := p_special_rate_table;
4008: p_new_prd := p_prd;
4009: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4010: g_pay_table_upd_flag := FALSE;
4011: ELSE
4012: p_retained_pay_table := ghr_pay_calc.l_spl491_table_name;
4013: p_new_prd := p_prd;
4008: p_new_prd := p_prd;
4009: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4010: g_pay_table_upd_flag := FALSE;
4011: ELSE
4012: p_retained_pay_table := ghr_pay_calc.l_spl491_table_name;
4013: p_new_prd := p_prd;
4014: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4015: g_pay_table_upd_flag := TRUE;
4016: END IF;
4010: g_pay_table_upd_flag := FALSE;
4011: ELSE
4012: p_retained_pay_table := ghr_pay_calc.l_spl491_table_name;
4013: p_new_prd := p_prd;
4014: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4015: g_pay_table_upd_flag := TRUE;
4016: END IF;
4017: ELSIF p_prd IN ('3','4','J','K','U','V','R') THEN --bug 4999237
4018: IF NOT(p_pp_grd_exists) THEN
4015: g_pay_table_upd_flag := TRUE;
4016: END IF;
4017: ELSIF p_prd IN ('3','4','J','K','U','V','R') THEN --bug 4999237
4018: IF NOT(p_pp_grd_exists) THEN
4019: p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4020: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4021: g_pay_table_upd_flag := TRUE;
4022: ELSE
4023: p_position_pay_table := p_special_rate_table;
4016: END IF;
4017: ELSIF p_prd IN ('3','4','J','K','U','V','R') THEN --bug 4999237
4018: IF NOT(p_pp_grd_exists) THEN
4019: p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4020: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4021: g_pay_table_upd_flag := TRUE;
4022: ELSE
4023: p_position_pay_table := p_special_rate_table;
4024: g_pay_table_upd_flag := FALSE;
4037: g_pay_table_upd_flag := FALSE;
4038: ELSE
4039: p_position_pay_table := p_special_rate_table;
4040: p_new_prd := p_prd;
4041: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4042: g_pay_table_upd_flag := FALSE;
4043: END IF;
4044: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4045: p_position_pay_table := p_special_rate_table;
4043: END IF;
4044: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4045: p_position_pay_table := p_special_rate_table;
4046: p_new_prd := p_prd;
4047: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4048: g_pay_table_upd_flag := FALSE;
4049: ELSE
4050: p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4051: p_new_prd := p_prd;
4046: p_new_prd := p_prd;
4047: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4048: g_pay_table_upd_flag := FALSE;
4049: ELSE
4050: p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4051: p_new_prd := p_prd;
4052: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4053: g_pay_table_upd_flag := TRUE;
4054: END IF;
4048: g_pay_table_upd_flag := FALSE;
4049: ELSE
4050: p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4051: p_new_prd := p_prd;
4052: p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4053: g_pay_table_upd_flag := TRUE;
4054: END IF;
4055: IF p_prd = '0' THEN
4056: p_new_prd := '6';
4071: g_pay_table_upd_flag := FALSE;
4072: ELSE
4073: p_position_pay_table := p_special_rate_table;
4074: p_new_prd := '0';
4075: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4076: g_pay_table_upd_flag := FALSE;
4077: END IF;
4078: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4079: p_position_pay_table := p_special_rate_table;
4077: END IF;
4078: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4079: p_position_pay_table := p_special_rate_table;
4080: p_new_prd := '0';
4081: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4082: g_pay_table_upd_flag := FALSE;
4083: ELSE
4084: p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4085: p_new_prd := '0';
4080: p_new_prd := '0';
4081: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4082: g_pay_table_upd_flag := FALSE;
4083: ELSE
4084: p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4085: p_new_prd := '0';
4086: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4087: g_pay_table_upd_flag := TRUE;
4088: END IF;
4082: g_pay_table_upd_flag := FALSE;
4083: ELSE
4084: p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4085: p_new_prd := '0';
4086: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4087: g_pay_table_upd_flag := TRUE;
4088: END IF;
4089: ELSIF p_prd IN ('E','F') THEN
4090: IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4099: p_new_prd := 'A';
4100: ELSIF p_prd = 'F' THEN
4101: p_new_prd := 'B';
4102: END IF;
4103: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4104: g_pay_table_upd_flag := FALSE;
4105: END IF;
4106: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4107: p_retained_pay_table := p_special_rate_table;
4109: p_new_prd := 'A';
4110: ELSIF p_prd = 'F' THEN
4111: p_new_prd := 'B';
4112: END IF;
4113: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4114: g_pay_table_upd_flag := FALSE;
4115: ELSE
4116: p_retained_pay_table := ghr_pay_calc.l_standard_table_name;
4117: IF p_prd = 'E' THEN
4112: END IF;
4113: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4114: g_pay_table_upd_flag := FALSE;
4115: ELSE
4116: p_retained_pay_table := ghr_pay_calc.l_standard_table_name;
4117: IF p_prd = 'E' THEN
4118: p_new_prd := 'A';
4119: ELSIF p_prd = 'F' THEN
4120: p_new_prd := 'B';
4118: p_new_prd := 'A';
4119: ELSIF p_prd = 'F' THEN
4120: p_new_prd := 'B';
4121: END IF;
4122: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4123: g_pay_table_upd_flag := TRUE;
4124: END IF;
4125: ELSIF p_prd IN ('3','4','J','K','U','V','R') THEN
4126: IF NOT(p_pp_grd_exists) THEN
4123: g_pay_table_upd_flag := TRUE;
4124: END IF;
4125: ELSIF p_prd IN ('3','4','J','K','U','V','R') THEN
4126: IF NOT(p_pp_grd_exists) THEN
4127: p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4128: g_pay_table_upd_flag := TRUE;
4129: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4130: ELSE
4131: p_position_pay_table := p_special_rate_table;
4125: ELSIF p_prd IN ('3','4','J','K','U','V','R') THEN
4126: IF NOT(p_pp_grd_exists) THEN
4127: p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4128: g_pay_table_upd_flag := TRUE;
4129: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4130: ELSE
4131: p_position_pay_table := p_special_rate_table;
4132: g_pay_table_upd_flag := FALSE;
4133: p_calculation_pay_table := p_special_rate_table;
4153: g_pay_table_upd_flag := FALSE;
4154: ELSE
4155: p_position_pay_table := p_special_rate_table;
4156: p_new_prd := p_prd;
4157: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4158: g_pay_table_upd_flag := FALSE;
4159: END IF;
4160: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4161: p_position_pay_table := p_special_rate_table;
4159: END IF;
4160: ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4161: p_position_pay_table := p_special_rate_table;
4162: p_new_prd := p_prd;
4163: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4164: g_pay_table_upd_flag := FALSE;
4165: ELSE
4166: p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4167: p_new_prd := p_prd;
4162: p_new_prd := p_prd;
4163: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4164: g_pay_table_upd_flag := FALSE;
4165: ELSE
4166: p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4167: p_new_prd := p_prd;
4168: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4169: g_pay_table_upd_flag := TRUE;
4170: END IF;
4164: g_pay_table_upd_flag := FALSE;
4165: ELSE
4166: p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4167: p_new_prd := p_prd;
4168: p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4169: g_pay_table_upd_flag := TRUE;
4170: END IF;
4171: END IF;
4172: END IF;
4188: in ('2','C','M','P','R','S') then
4189: p_pay_calc_out_data.open_pay_fields := TRUE;
4190: hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
4191: hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
4192: raise ghr_pay_calc.unable_to_calculate;
4193: end if;
4194: end if;
4195: ---
4196:
4226: --Begin Bug 7046241
4227: l_gr_pay_plan := FALSE;
4228: --End Bug 7046241
4229: -- 2. Get the user table name into local variable
4230: l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
4231: hr_utility.set_location('user table name:' ||l_user_table_name,30);
4232: -- 3. Get the Locality Percentage Value
4233: l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_pay_calc_data.duty_station_id
4234: ,p_pay_calc_data.effective_date),0);
4229: -- 2. Get the user table name into local variable
4230: l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
4231: hr_utility.set_location('user table name:' ||l_user_table_name,30);
4232: -- 3. Get the Locality Percentage Value
4233: l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_pay_calc_data.duty_station_id
4234: ,p_pay_calc_data.effective_date),0);
4235: hr_utility.set_location('l_adjustment_percentage ..' || to_char(l_adjustment_percentage),31);
4236: l_adj_perc_factor := l_adjustment_percentage/100;
4237: hr_utility.set_location('l_adj_perc_factor ..' || to_char(l_adj_perc_factor),32);
4240:
4241: -- Check LEO Posiiton or NOT
4242: -- Bug#4709111 Modified p_pay_calc_data.grade_or_level to l_grade_or_level.
4243: -- Bug#5089732 Treat all the positions with pay plan GL as LEO Positions.
4244: IF (ghr_pay_calc.LEO_position (l_dummy
4245: ,p_pay_calc_data.position_id
4246: ,l_dummy
4247: ,l_dummy
4248: ,p_pay_calc_data.effective_date)
4248: ,p_pay_calc_data.effective_date)
4249: AND l_grade_or_level between 03 and 10) OR
4250: l_pay_plan = 'GL' THEN
4251: l_leo_flag := TRUE;
4252: l_default_pay_table := ghr_pay_calc.l_spl491_table_name;
4253: ELSE
4254: l_leo_flag := FALSE;
4255: l_default_pay_table := ghr_pay_calc.l_standard_table_name;
4256: END IF;
4251: l_leo_flag := TRUE;
4252: l_default_pay_table := ghr_pay_calc.l_spl491_table_name;
4253: ELSE
4254: l_leo_flag := FALSE;
4255: l_default_pay_table := ghr_pay_calc.l_standard_table_name;
4256: END IF;
4257:
4258:
4259: --Bug #5919700 For 890 action and GM Pay plan GS should be considered
4272: END IF;
4273:
4274:
4275: -- Bug#5741977 - Set the default Pay Plan as GL as GS, GG are end dated in 0491 table.
4276: IF l_default_pay_table = ghr_pay_calc.l_spl491_table_name AND
4277: l_pay_plan IN ('GS','GG') AND
4278: p_pay_calc_data.effective_date >= to_date('2007/01/07','YYYY/MM/DD') THEN
4279: l_default_pay_plan := 'GL';
4280: ------ Bug#5741977 start
4311: IF p_pay_calc_data.noa_code NOT IN ('894','890') then
4312: p_pay_calc_out_data.open_pay_fields := TRUE;
4313: hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
4314: hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
4315: raise ghr_pay_calc.unable_to_calculate;
4316: END IF;
4317: -- Bug# 4866952 Added the new pay calculation method for Pay Adjustment actions
4318: -- Processed after 01-MAY-2005
4319: IF p_pay_calc_data.effective_date = TO_DATE('2005/05/01','YYYY/MM/DD') THEN
4379: hr_utility.set_location('GOt Current Years Special Pay Table Value at step 10'|| l_special_rate,7788);
4380: --Now Get current Years's default pay table value.for max rate....BUG# 4999237.
4381: hr_utility.set_location('Now Get current Years default pay table value.for max rate.',7788);
4382:
4383: ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4384: ,p_pay_plan =>l_default_pay_plan
4385: ,p_grade_or_level =>l_grade_or_level
4386: ,p_step_or_rate =>'10'
4387: ,p_effective_date =>p_pay_calc_data.effective_date
4433: -- Then new basic = current basic + (new special rate step10 value - old special rate step 10 value/2)
4434:
4435: IF l_old_basic_pay IS NOT NULL AND l_special_rate IS NOT NULL AND
4436:
4437: l_user_table_name NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) THEN
4438:
4439: l_increment := Round((l_special_rate - l_old_basic_pay)/2,0);
4440: hr_utility.set_location('INCREMENTED HALF VALUE'||l_increment,7788);
4441: l_new_basic_pay := p_pay_calc_data.current_basic_pay + l_increment;
4456: IF l_new_basic_pay <= l_special_rate THEN
4457: l_new_adj_basic_pay := l_special_rate;
4458: hr_utility.set_location('SPECIAL RATE IS GREATER'||l_special_rate,7788);
4459: hr_utility.set_location('get current value for step10 in default table',7788);
4460: ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4461: ,p_pay_plan =>l_default_pay_plan
4462: ,p_grade_or_level =>l_grade_or_level
4463: ,p_step_or_rate => '10'
4464: ,p_effective_date =>p_pay_calc_data.effective_date
4482:
4483: hr_utility.set_location('special rate is lesser or old special rate is not available',7788);
4484:
4485: hr_utility.set_location('::: 50101491 :::Inside Else Condition',40);
4486: l_old_adj_perc_factor := (NVL(ghr_pay_calc.get_lpa_percentage(p_pay_calc_data.duty_station_id
4487: ,p_pay_calc_data.effective_date - 1
4488: ),0))/100;
4489:
4490:
4493: hr_utility.set_location('l_default_table_id: '||l_default_table_id,95);
4494: hr_utility.set_location(' l_pay_plan: '||l_pay_plan,95);
4495: hr_utility.set_location(' l_default_pay_plan: '||l_default_pay_plan,95);
4496: hr_utility.set_location(' l_grade_or_level: '||l_grade_or_level,95);
4497: ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4498: ,p_pay_plan =>l_default_pay_plan
4499: ,p_grade_or_level =>l_grade_or_level
4500: ,p_step_or_rate => '10'
4501: ,p_effective_date =>p_pay_calc_data.effective_date - 1
4509: l_old_locality_adj := ROUND(l_old_basic_pay * l_old_adj_perc_factor,0);
4510: l_old_adj_basic_pay := l_old_basic_pay + l_old_locality_adj;
4511: hr_utility.set_location('Old adj basic at step 10: '||to_char(l_old_adj_basic_pay),95);
4512: hr_utility.set_location('Old locality at step 10: '||to_char(l_old_locality_adj),95);
4513: ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4514: ,p_pay_plan =>l_default_pay_plan
4515: ,p_grade_or_level =>l_grade_or_level
4516: ,p_step_or_rate =>'10'
4517: ,p_effective_date =>p_pay_calc_data.effective_date
4556: END IF;
4557: END IF; --IF NVL(l_special_rate,0)
4558: -- Set the PRD if Pay Retention is terminated.
4559: -- 7046241
4560: IF l_term_ret_pay_prd and l_gr_pay_plan and l_calculation_pay_table = ghr_pay_calc.l_standard_table_name then
4561: ghr_history_fetch.fetch_positionei(
4562: p_position_id => p_pay_calc_data.position_id,
4563: p_information_type => 'GHR_US_POS_VALID_GRADE',
4564: p_date_effective => p_pay_calc_data.effective_date,
4573: END IF;
4574: -- 7046241
4575: IF l_term_ret_pay_prd THEN
4576: IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','R') THEN
4577: IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4578: l_new_prd := '0';
4579: ELSE
4580: l_new_prd := '6';
4581: END IF;
4579: ELSE
4580: l_new_prd := '6';
4581: END IF;
4582: ELSIF p_pay_calc_data.pay_rate_determinant = 'U' THEN
4583: IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4584: l_new_prd := 'B';
4585: ELSE
4586: l_new_prd := 'F';
4587: END IF;
4585: ELSE
4586: l_new_prd := 'F';
4587: END IF;
4588: ELSIF p_pay_calc_data.pay_rate_determinant = 'V' THEN
4589: IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4590: l_new_prd := 'A';
4591: ELSE
4592: l_new_prd := 'E';
4593: END IF;
4623: IF p_pay_calc_data.noa_family_code IN ('APP','CHG_DUTY_STATION','CONV_APP','EXT_NTE','POS_CHG'
4624: ,'REALIGNMENT','REASSIGNMENT', 'RETURN_TO_DUTY') THEN
4625: hr_utility.set_message(8301, 'GHR_38260_NO_CALC_PAY_PLAN');
4626: hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
4627: raise ghr_pay_calc.unable_to_calculate;
4628: END IF;
4629: -- Added this IF condition Bug#5114467
4630: IF l_pay_plan NOT IN ('GM','GR') THEN
4631: --Bug# 7518210 added GR
4628: END IF;
4629: -- Added this IF condition Bug#5114467
4630: IF l_pay_plan NOT IN ('GM','GR') THEN
4631: --Bug# 7518210 added GR
4632: l_new_Position_Pay_Table := ghr_pay_calc.l_standard_table_name;
4633: l_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4634: g_pay_table_upd_flag := TRUE;
4635: l_new_prd := '0';
4636: l_new_basic_pay := ROUND(p_pay_calc_data.current_adj_basic_pay / (1 + l_adj_perc_factor),0);
4629: -- Added this IF condition Bug#5114467
4630: IF l_pay_plan NOT IN ('GM','GR') THEN
4631: --Bug# 7518210 added GR
4632: l_new_Position_Pay_Table := ghr_pay_calc.l_standard_table_name;
4633: l_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4634: g_pay_table_upd_flag := TRUE;
4635: l_new_prd := '0';
4636: l_new_basic_pay := ROUND(p_pay_calc_data.current_adj_basic_pay / (1 + l_adj_perc_factor),0);
4637: l_new_locality_adj := ROUND(l_new_basic_pay * l_adj_perc_factor);
4778: l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4779: l_calculation_pay_table := get_user_table_name(l_user_table_id);
4780: ELSE --End Bug# 5608741
4781: -- Processing of NOACs other than WGI/QSI
4782: IF l_user_table_name IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) THEN
4783: l_pay_calc_process := 'L';
4784: ELSE
4785: hr_utility.set_location('user tbale id: '||to_char(l_user_table_id),40);
4786: hr_utility.set_location('pay Plan: '||l_pay_plan,50);
4795: ,p_PT_eff_start_date => l_pt_eff_start_date
4796: ,p_PT_eff_end_date => l_pt_eff_end_date
4797: ,p_pp_grd_exists => l_pp_grd_exists );
4798: hr_utility.set_location('value '||to_char(l_pt_value),80);
4799: ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4800: ,p_pay_plan =>l_default_pay_plan
4801: ,p_grade_or_level =>l_grade_or_level
4802: ,p_step_or_rate =>l_step_or_rate
4803: ,p_effective_date =>p_pay_calc_data.effective_date
4859:
4860:
4861: -- get_basic_pay
4862: hr_utility.set_location('2. l_step_or_rate: '||l_step_or_rate,999999);
4863: ghr_pay_calc.get_pay_table_value(p_user_table_id => l_default_table_id
4864: ,p_pay_plan => l_default_pay_plan
4865: ,p_grade_or_level => l_grade_or_level
4866: ,p_step_or_rate => l_step_or_rate
4867: ,p_effective_date => p_pay_calc_data.effective_date
4894: hr_utility.set_location('new ADJ BA : '||to_char(l_new_adj_basic_pay),30);
4895: hr_utility.set_location('new PRD : '||l_new_prd,40);
4896: hr_utility.set_location('new Step : '||l_new_step,50);
4897: --Bug#5089732 Set the Pay Rate Determinant for GL,GG Pay Plans
4898: IF l_calculation_pay_table = ghr_pay_calc.l_spl491_table_name AND
4899: l_pay_plan IN ('GL','GG') AND
4900: l_new_prd IN ('6','E','F') THEN
4901: IF l_new_prd = '6' THEN
4902: l_new_prd := '0';
4913: -- And Code for determining Calculation pay table For Regular Emp. in GM pay table and 894 action
4914: IF l_pay_plan = 'GM' AND p_pay_calc_data.noa_code= 894 THEN
4915: -- AC : Bug#5725928 - Added the condition so that the pay adjustment process should be
4916: -- doing the comparison of locality verses special rate only if the pay table is not 0000
4917: IF l_calculation_pay_table <> ghr_pay_calc.l_standard_table_name THEN
4918: IF l_new_locality_adj > l_new_special_rate THEN
4919: l_new_prd := '0';
4920: ELSE
4921: l_new_prd := '6';
4994: l_locality_adj NUMBER;
4995: l_leo_flag BOOLEAN;
4996: l_std_user_table_id NUMBER;
4997: l_std_user_table_name varchar2(80);
4998: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
4999: l_pay_plan VARCHAR2(10);
5000: l_pay_basis VARCHAR2(10);
5001: l_grade_or_level VARCHAR2(60);
5002: l_user_table_id NUMBER;
5076: ELSE
5077: p_award_salary := ROUND(p_adj_basic_pay);
5078: END IF;
5079: ELSE
5080: l_pay_table := SUBSTR(ghr_pay_calc.get_user_table_name(l_user_table_id),1,4);
5081:
5082:
5083: IF (l_equivalent_pay_plan = 'GS') THEN
5084:
5112: --code for getting default table values......
5113:
5114:
5115:
5116: IF ghr_pay_calc.LEO_position (p_prd
5117: ,p_position_id
5118: ,l_std_user_table_id
5119: ,p_duty_station_id
5120: ,p_effective_date)
5118: ,l_std_user_table_id
5119: ,p_duty_station_id
5120: ,p_effective_date)
5121: AND p_grade_or_level between 03 and 10 THEN
5122: l_std_user_table_name := Ghr_pay_calc.l_spl491_table_name;
5123:
5124: ELSE
5125: l_std_user_table_name := Ghr_pay_calc.l_standard_table_name;
5126:
5121: AND p_grade_or_level between 03 and 10 THEN
5122: l_std_user_table_name := Ghr_pay_calc.l_spl491_table_name;
5123:
5124: ELSE
5125: l_std_user_table_name := Ghr_pay_calc.l_standard_table_name;
5126:
5127: END IF;
5128: FOR c_rec IN get_user_table_id(l_std_user_table_name) LOOP
5129: l_std_user_table_id := c_rec.user_table_id;
5128: FOR c_rec IN get_user_table_id(l_std_user_table_name) LOOP
5129: l_std_user_table_id := c_rec.user_table_id;
5130: END LOOP;
5131:
5132: ghr_pay_calc.get_pay_table_value (p_user_table_id => l_std_user_table_id
5133: ,p_pay_plan => l_pay_plan
5134: ,p_grade_or_level => l_grade_or_level
5135: ,p_step_or_rate => l_max_step
5136: ,p_effective_date => p_effective_date
5138: ,p_PT_eff_start_date => l_PT_eff_start_date
5139: ,p_PT_eff_end_date => l_PT_eff_end_date
5140: );
5141: hr_utility.set_location('default rate '||l_PT_value,8877);
5142: l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id,p_effective_date),0);
5143: hr_utility.set_location('l_adjustment_percentage '||l_adjustment_percentage,8877);
5144: l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5145:
5146: l_pt_value := l_pt_value + l_locality_adj;
5157: end if;
5158: ELSIF l_pay_table IN('0491','0000') THEN
5159:
5160: BEGIN
5161: ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_user_table_id
5162: ,p_pay_plan => l_pay_plan
5163: ,p_grade_or_level => l_grade_or_level
5164: ,p_step_or_rate => l_max_step
5165: ,p_effective_date => p_effective_date
5166: ,p_PT_value => l_PT_value
5167: ,p_PT_eff_start_date => l_PT_eff_start_date
5168: ,p_PT_eff_end_date => l_PT_eff_end_date);
5169:
5170: l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id
5171: ,p_effective_date),0);
5172:
5173: l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5174:
5175: p_award_salary := ROUND(l_PT_value+l_locality_adj);
5176:
5177:
5178: EXCEPTION
5179: WHEN ghr_pay_calc.pay_calc_message THEN
5180: BEGIN
5181: IF ghr_pay_calc.LEO_position (p_prd
5182: ,p_position_id
5183: ,l_std_user_table_id
5177:
5178: EXCEPTION
5179: WHEN ghr_pay_calc.pay_calc_message THEN
5180: BEGIN
5181: IF ghr_pay_calc.LEO_position (p_prd
5182: ,p_position_id
5183: ,l_std_user_table_id
5184: ,p_duty_station_id
5185: ,p_effective_date)
5183: ,l_std_user_table_id
5184: ,p_duty_station_id
5185: ,p_effective_date)
5186: AND p_grade_or_level between 03 and 10 THEN
5187: l_std_user_table_name := Ghr_pay_calc.l_spl491_table_name;
5188:
5189: ELSE
5190: l_std_user_table_name := Ghr_pay_calc.l_standard_table_name;
5191:
5186: AND p_grade_or_level between 03 and 10 THEN
5187: l_std_user_table_name := Ghr_pay_calc.l_spl491_table_name;
5188:
5189: ELSE
5190: l_std_user_table_name := Ghr_pay_calc.l_standard_table_name;
5191:
5192: END IF;
5193: FOR c_rec IN get_user_table_id(l_std_user_table_name) LOOP
5194: l_std_user_table_id := c_rec.user_table_id;
5192: END IF;
5193: FOR c_rec IN get_user_table_id(l_std_user_table_name) LOOP
5194: l_std_user_table_id := c_rec.user_table_id;
5195: END LOOP;
5196: ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_std_user_table_id
5197: ,p_pay_plan => l_pay_plan
5198: ,p_grade_or_level => l_grade_or_level
5199: ,p_step_or_rate => l_max_step
5200: ,p_effective_date => p_effective_date
5201: ,p_PT_value => l_PT_value
5202: ,p_PT_eff_start_date => l_PT_eff_start_date
5203: ,p_PT_eff_end_date => l_PT_eff_end_date);
5204:
5205: l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id
5206: ,p_effective_date),0);
5207: l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5208: p_award_salary := ROUND(l_PT_value+l_locality_adj);
5209: END;
5223: hr_utility.set_location('EFF DT '||to_char(p_effective_date),20);
5224: hr_utility.set_location('MAX STEP '||l_max_step,30);
5225: hr_utility.set_location('PAY PLAN '||l_pay_plan,40);
5226: hr_utility.set_location('GRADE '||l_grade_or_level,50);
5227: ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_user_table_id
5228: ,p_pay_plan => l_pay_plan
5229: ,p_grade_or_level => l_grade_or_level
5230: ,p_step_or_rate => l_max_step
5231: ,p_effective_date => p_effective_date
5233: ,p_PT_eff_start_date => l_PT_eff_start_date
5234: ,p_PT_eff_end_date => l_PT_eff_end_date);
5235: hr_utility.set_location('VALUE '||to_char(l_pt_value),60);
5236: hr_utility.set_location('Pay Basis '||p_pay_basis,70);
5237: p_award_salary := ROUND(ghr_pay_calc.convert_amount(l_PT_value,p_pay_basis,'PA'));
5238:
5239: END IF; --(p_pay_plan = l_equivalent_pay_plan)
5240: END IF; --p_prd not in('3','4','J','K','U','V'
5241: --Begin Bug# 5039156
5269: p_award_salary := NULL;
5270: hr_utility.set_location('Leaving ' || l_proc,10);
5271: END award_amount_calc;
5272: -- Bug# 4748927 End
5273: END ghr_pay_calc;