[Home] [Help]
38:
39: 21-AUG-2007 sodhingr 115.6 6163428 Changed the procedure, Conver_Period_Type to uncomment
40: the code to check the payroll calculation rule defined at the payroll level.
41: Also, moved the variable name l_normal_hours to global variable as this
42: will be used by the uncommented code to get the standard hours.
43: 08-Jan-2008 sudedas 115.7 6718164 Added new Function term_skip_rule_rwage
44: 21-Jan-2008 sudedas 115.8 Corrected Logic for Term Rule LSPD
45: Used Cursor csr_lspprocd_min_dtearned.
46: 22-May-2008 sudedas 115.9 6163428 Changed Convert_Period_Type to Create an
46: 22-May-2008 sudedas 115.9 6163428 Changed Convert_Period_Type to Create an
47: Exception for Regular Salary that Always uses
48: Annualized Method irrespective of Payroll
49: Calculation Method. Created Function
50: get_wrk_sch_std_hrs to Calculate Standard
51: Hours per Week From Work Schedule.
52: 25-Aug-2008 sudedas 115.10 5895804 Added Functions hours_between, calc_reduced_reg
53: 3556204 calc_vacation_pay, calc_sick_pay
54: ER 3855241
126: l_normal_hours NUMBER := 0;
127:
128:
129: -- Intrduced for Bug# 6163428
130: -- Introducing NEW Function for Calculating Standard Hours From Work Schedule
131: -- Assuming Work Schedule Always defines Number of Standard Hours
132: -- in Each Week Day i.e. User Table Structure has Rows Days of the Week
133:
134: FUNCTION get_wrk_sch_std_hrs(p_assignment_action_id IN number
127:
128:
129: -- Intrduced for Bug# 6163428
130: -- Introducing NEW Function for Calculating Standard Hours From Work Schedule
131: -- Assuming Work Schedule Always defines Number of Standard Hours
132: -- in Each Week Day i.e. User Table Structure has Rows Days of the Week
133:
134: FUNCTION get_wrk_sch_std_hrs(p_assignment_action_id IN number
135: ,p_assignment_id IN number
278: RETURN ln_std_hrs_wrksch;
279:
280: END get_wrk_sch_std_hrs;
281:
282: -- End of NEW Function for Calculating Standard Hours From Work Schedule
283:
284: --- Functions
285: FUNCTION get_legislation_code(p_business_group_id NUMBER)
286: RETURN VARCHAR2 IS
908: /***********************************************************
909: The is wrapper is added to check the caluclation rule given
910: at the payroll level. Depending upon the Rule we will the
911: Get_Annualizing_Factor fun calls. If the rule is
912: standard it goes to Standard Caluclation type. If the rule
913: is Annual then it goes to ANNU rule
914: **************************************************************/
915: IF p_period_start_date IS NULL THEN
916: v_stnd_start_date := sysdate;
1009: ,p_period_start_date
1010: ,p_period_end_date
1011: ,p_from_freq);
1012:
1013: -- Calling Function to get Standard Hours Worked
1014: -- As per the Work Schedule Entered.
1015: -- Assuming Work Schedule Always defines Number of Standard Hours in Each Day
1016:
1017: l_normal_hours := get_wrk_sch_std_hrs(
1011: ,p_from_freq);
1012:
1013: -- Calling Function to get Standard Hours Worked
1014: -- As per the Work Schedule Entered.
1015: -- Assuming Work Schedule Always defines Number of Standard Hours in Each Day
1016:
1017: l_normal_hours := get_wrk_sch_std_hrs(
1018: p_assignment_action_id => p_assignment_action_id
1019: ,p_assignment_id => p_assignment_id
1026: hr_utility.trace('From Work Schedule l_normal_hours := '||l_normal_hours);
1027:
1028: -- In case Work Schedule is NOT defined
1029: -- If Assignment Frequency is Week
1030: -- Standard Condition will give Number of Hours per Week
1031: -- (I am NOT Considering any Frequency other than Week)
1032:
1033: IF NVL(l_normal_hours, 0) = 0 THEN
1034: open get_asg_hours_freq(p_date_earned
1037: ,l_normal_hours;
1038: close get_asg_hours_freq;
1039: END IF;
1040:
1041: hr_utility.trace('From Standard Condition l_normal_hours := '||l_normal_hours);
1042:
1043: hr_utility.trace('p_figure := '||p_figure);
1044: hr_utility.trace('v_from_stnd_factor := '||v_from_stnd_factor);
1045:
1044: hr_utility.trace('v_from_stnd_factor := '||v_from_stnd_factor);
1045:
1046: -- Following Condition will Arrive If NO Work Schedule Defined
1047: -- Or, Work Schedule User Table does NOT have
1048: -- "Days of the Week / Standard Hours Worked" Structure
1049: -- Or, Standard Condition Specifies Frequency Other than Week
1050: -- like Day / Month / Year etc.
1051:
1052: -- Defaulting it to 40 hours / Week to Avoid
1045:
1046: -- Following Condition will Arrive If NO Work Schedule Defined
1047: -- Or, Work Schedule User Table does NOT have
1048: -- "Days of the Week / Standard Hours Worked" Structure
1049: -- Or, Standard Condition Specifies Frequency Other than Week
1050: -- like Day / Month / Year etc.
1051:
1052: -- Defaulting it to 40 hours / Week to Avoid
1053: -- Divide by Zero Condition but Calculation Might be Wrong
1218: IF UPPER(p_wsched) = 'NOT ENTERED' THEN
1219:
1220: hr_utility.set_location('Prorate_Earnings', 7);
1221: hr_utility.trace('p_wsched NOT ENTERED');
1222: hr_utility.trace('Calling Standard Hours Worked');
1223:
1224: v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
1225: p_range_start_date,
1226: p_range_end_date,
1220: hr_utility.set_location('Prorate_Earnings', 7);
1221: hr_utility.trace('p_wsched NOT ENTERED');
1222: hr_utility.trace('Calling Standard Hours Worked');
1223:
1224: v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
1225: p_range_start_date,
1226: p_range_end_date,
1227: p_asg_std_freq);
1228:
1936: -- For Hourly pay basis:
1937: -- Get hours scheduled for the current period either from:
1938: -- 1. ASG work schedule
1939: -- 2. ORG default work schedule
1940: -- 3. ASG standard hours and frequency
1941: -- Multiply the hours scheduled for period by normal Hourly Rate (ie. from
1942: -- pre-defined earnings, REGULAR_WAGES_RATE) pay period earnings.
1943: --
1944: -- In either case, need to find the payroll period type, let's do it upfront:
1985: -- Pay basis is hourly,
1986: -- Get hours scheduled for the current period either from:
1987: -- 1. ASG work schedule
1988: -- 2. ORG default work schedule
1989: -- 3. ASG standard hours and frequency
1990: -- Do we pass in Work Schedule from asst scl db item? Yes
1991: -- 10-JAN-1996 hparicha : We no longer assume "standard hours" represent
1992: -- a weekly figure. We also no longer use a week as
1993: -- the basis for annualization,
1987: -- 1. ASG work schedule
1988: -- 2. ORG default work schedule
1989: -- 3. ASG standard hours and frequency
1990: -- Do we pass in Work Schedule from asst scl db item? Yes
1991: -- 10-JAN-1996 hparicha : We no longer assume "standard hours" represent
1992: -- a weekly figure. We also no longer use a week as
1993: -- the basis for annualization,
1994: -- even when using work schedule - ie. need to find ACTUAL
1995: -- scheduled hours, not
2026: hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
2027: ELSE
2028:
2029: hr_utility.trace('Asg has No Work Schedule');
2030: hr_utility.trace('Calling Standard_Hours_Worked');
2031:
2032: v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
2033: p_period_start,
2034: p_period_end,
2028:
2029: hr_utility.trace('Asg has No Work Schedule');
2030: hr_utility.trace('Calling Standard_Hours_Worked');
2031:
2032: v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
2033: p_period_start,
2034: p_period_end,
2035: p_asst_std_freq);
2036: hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
3184:
3185: END get_hourly_rate;
3186:
3187:
3188: FUNCTION standard_hours_worked(
3189: p_std_hrs in NUMBER,
3190: p_range_start in DATE,
3191: p_range_end in DATE,
3192: p_std_freq in VARCHAR2) RETURN NUMBER IS
3201: v_curr_date DATE;
3202: v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
3203: v_day_no NUMBER;
3204:
3205: BEGIN -- standard_hours_worked
3206:
3207: /* Init */
3208: c_wkdays_per_week := 5;
3209: c_wkdays_per_month := 20;
3213: v_curr_date := NULL;
3214: v_curr_day :=NULL;
3215:
3216: -- Check for valid range
3217: hr_utility.trace('Entered standard_hours_worked');
3218:
3219: IF p_range_start > p_range_end THEN
3220: hr_utility.trace('p_range_start greater than p_range_end');
3221: RETURN v_total_hours;
3280: v_curr_date := v_curr_date + 1;
3281: EXIT WHEN v_curr_date > p_range_end;
3282: END LOOP;
3283: hr_utility.trace(' Final v_total_hours = '||to_char(v_total_hours));
3284: hr_utility.trace(' Leaving standard_hours_worked' );
3285: --
3286: RETURN v_total_hours;
3287: --
3288: END standard_hours_worked;
3284: hr_utility.trace(' Leaving standard_hours_worked' );
3285: --
3286: RETURN v_total_hours;
3287: --
3288: END standard_hours_worked;
3289:
3290: -- +-------------------------------------------------------------------------+
3291: -- |-----------------< good_time_format >-------------------------|
3292: -- +-------------------------------------------------------------------------+
3699: END IF;
3700:
3701:
3702:
3703: /* Calculate hours worked based on standard conditions if the actual hours
3704: worked are not available from either ATG work schedule or work schedule
3705: at assignment/org level */
3706:
3707: IF NOT l_work_schedule_found THEN
3704: worked are not available from either ATG work schedule or work schedule
3705: at assignment/org level */
3706:
3707: IF NOT l_work_schedule_found THEN
3708: hr_utility.trace('Calculating hours based on Standard conditions ');
3709: hr_utility.trace( 'Assignment Id '||assignment_id);
3710: hr_utility.trace( 'date_earned '||date_earned);
3711: OPEN get_asg_hours_freq(date_earned,assignment_id);
3712: FETCH get_asg_hours_freq
3716: hr_utility.trace( 'l_asg_frequency '||l_asg_frequency);
3717: hr_utility.trace( 'l_normal_hours '||l_normal_hours);
3718:
3719: IF l_asg_frequency IS NOT NULL and l_normal_hours IS NOT NULL THEN
3720: l_total_hours := standard_hours_worked(l_normal_hours
3721: ,p_period_start_date
3722: ,p_period_end_date
3723: ,l_asg_frequency);
3724: return l_total_hours;
3756:
3757: CURSOR csr_term_dates(p_date_earned date
3758: ,p_assignment_id number) is
3759: select pds.actual_termination_date
3760: ,pds.last_standard_process_date
3761: ,pds.final_process_date
3762: from per_periods_of_service PDS,
3763: per_assignments_f ASS
3764: WHERE PDS.actual_termination_date <= p_date_earned
3922: return 'Y';
3923: END IF;
3924: END IF; -- ATD <= Current Pay Period Date Earned
3925:
3926: ELSIF lv_term_typ = 'L' THEN -- Term Rule 'Last Standard Process Date'
3927: IF ((ld_atd <= ctx_date_earned
3928: AND ld_lspd <= ctx_date_earned) OR
3929: (ld_atd <= ctx_date_earned
3930: AND ld_lspd > ctx_date_earned)) THEN
4092: END;
4093: END IF;
4094:
4095:
4096: /* Calculate hours worked based on standard conditions if the actual hours
4097: worked are not available from either ATG work schedule or work schedule
4098: at assignment/org level */
4099:
4100: IF NOT l_work_schedule_found THEN
4097: worked are not available from either ATG work schedule or work schedule
4098: at assignment/org level */
4099:
4100: IF NOT l_work_schedule_found THEN
4101: hr_utility.trace('Calculating hours based on Standard conditions ');
4102: hr_utility.trace( 'Assignment Id '||assignment_id);
4103: hr_utility.trace( 'date_earned '||date_earned);
4104: OPEN get_asg_hours_freq(date_earned,assignment_id);
4105: FETCH get_asg_hours_freq
4109: hr_utility.trace( 'l_asg_frequency '||l_asg_frequency);
4110: hr_utility.trace( 'l_normal_hours '||l_normal_hours);
4111:
4112: IF l_asg_frequency IS NOT NULL and pay_core_ff_udfs.g_normal_hours IS NOT NULL THEN
4113: l_total_hours := standard_hours_worked(l_normal_hours
4114: ,p_period_start_date
4115: ,p_period_end_date
4116: ,l_asg_frequency);
4117: return l_total_hours;