1 PACKAGE PAY_NO_ABSENCE AS
2 /* $Header: pynoabsence.pkh 120.0.12000000.1 2007/05/22 05:53:22 rajesrin noship $ */
3 -- Cursor to fetch global values
4 CURSOR GLB_VALUE(GLB_NAME VARCHAR2, ABS_DATE DATE)
5 IS
6 SELECT TRIM(GLOBAL_VALUE)
7 FROM FF_GLOBALS_F GLB
8 WHERE ABS_DATE BETWEEN GLB.EFFECTIVE_START_DATE AND GLB.EFFECTIVE_END_DATE
9 AND GLB.GLOBAL_NAME = GLB_NAME
10 AND GLB.LEGISLATION_CODE = 'NO';
11
12 Function CALCULATE_PAYMENT
13 ( p_assignment_id IN NUMBER
14 ,p_effective_date IN DATE
15 -- ,p_absence_category IN VARCHAR2
16 ,p_abs_categorycode IN VARCHAR
17 ,p_abs_start_date IN DATE
18 ,p_abs_end_date IN DATE
19 ,p_prorate_start IN DATE
20 ,p_prorate_end IN DATE
21 ,p_abs_attendance_id IN NUMBER
22 -- Balance Variables
23 ,p_sickabs_paybase IN NUMBER
24 ,p_sickabs_totaldays IN NUMBER
25 -- Sickness Benefit variables
26 ,p_abs_empr_days OUT NOCOPY NUMBER
27 ,p_abs_ss_days OUT NOCOPY NUMBER
28 ,p_abs_total_days OUT NOCOPY NUMBER
29 ,p_abs_daily_rate OUT NOCOPY NUMBER
30 ,p_abs_sick_days OUT NOCOPY NUMBER
31 -- Earnings adjustment values
32 ,p_ear_value OUT NOCOPY NUMBER
33 ,p_ear_startdt OUT NOCOPY DATE
34 ,p_ear_enddt OUT NOCOPY DATE
35 -- Global values
36 ,p_abs_link_period IN NUMBER
37 ,p_abs_min_gap IN NUMBER
38 ,p_abs_month IN NUMBER
39 ,p_abs_annual_days IN NUMBER
40 ,p_abs_work_days IN NUMBER
41 ,p_abs_cal_days IN NUMBER
42 -- pay earned start and end date
43 ,p_pay_start_date IN DATE
44 ,p_pay_end_date IN DATE
45 -- To determine actual payroll period. Mainly for proration calculation.
46 ,p_hourly_paid IN Varchar2
47 -- Balance Variables
48 ,p_4weeks_paybase IN NUMBER
49 ,p_3years_paybase IN NUMBER
50 -- Reclaimable benefit output variables
51 ,p_rec_empr_days OUT NOCOPY NUMBER
52 ,p_rec_ss_days OUT NOCOPY NUMBER
53 ,p_rec_total_days OUT NOCOPY NUMBER
54 ,p_rec_daily_rate OUT NOCOPY NUMBER
55 ,p_ss_daily_rate OUT NOCOPY NUMBER
56 -- User defined daily rate calculation logic option
57 ,p_rate_option1 OUT NOCOPY VARCHAR
58 ,p_rate_option2 OUT NOCOPY VARCHAR
59 ,p_rate_option3 OUT NOCOPY VARCHAR
60 ,p_rate_option4 OUT NOCOPY VARCHAR
61 ,p_rate_option5 OUT NOCOPY VARCHAR
62 ,p_rate_option6 OUT NOCOPY VARCHAR
63 ,p_rate_option7 OUT NOCOPY VARCHAR
64 ,p_rate_option8 OUT NOCOPY VARCHAR
65 ,p_rate_option9 OUT NOCOPY VARCHAR
66 ,p_rate_option10 OUT NOCOPY VARCHAR
67 -- ,p_abs_categorycode OUT NOCOPY VARCHAR
68 ,p_abs_error OUT NOCOPY VARCHAR
69 ,p_adopt_bal_days IN NUMBER
70 ,p_parental_bal_days IN NUMBER
71 ,p_abs_child_emp_days_limit IN NUMBER
72 ,p_child_emp_days IN NUMBER
73 ,p_child_ss_days IN NUMBER
74 ,p_pts_percentage OUT NOCOPY NUMBER
75 ,p_abs_total_cal_days OUT NOCOPY NUMBER
76 ,p_sickbal_total_caldays IN NUMBER
77 ,p_abs_ear_adj_base IN NUMBER
78 ) RETURN NUMBER;
79
80 FUNCTION get_override_details
81 (p_assignment_id IN NUMBER
82 ,p_effective_date IN DATE
83 ,p_abs_start_date IN DATE
84 ,p_abs_end_date IN DATE
85 ,p_abs_categorycode IN VARCHAR2
86 ,p_start_date OUT NOCOPY DATE
87 ,p_end_date OUT NOCOPY DATE
88 ,p_over_empr_rate OUT NOCOPY NUMBER
89 ,p_over_ss_rate OUT NOCOPY NUMBER
90 ,p_over_reclaim_rate OUT NOCOPY NUMBER
91 ,p_over_empr_days OUT NOCOPY NUMBER
92 ,p_over_ss_days OUT NOCOPY NUMBER ) RETURN NUMBER;
93
94 PROCEDURE GET_SICKPAY
95 ( p_person_id IN NUMBER
96 ,p_assignment_id IN NUMBER
97 ,p_effective_date IN DATE
98 ,p_dateofjoin IN DATE
99 );
100
101
102 FUNCTION get_weekdays(p_period_start_date IN DATE
103 ,p_period_end_date IN DATE
104 ,p_work_pattern IN VARCHAR) RETURN NUMBER ;
105
106 FUNCTION get_sick_unpaid
107 (p_assignment_id IN NUMBER
108 ,p_effective_date IN DATE
109 ,p_start_date OUT NOCOPY DATE
110 ,p_end_date OUT NOCOPY DATE ) RETURN NUMBER;
111
112 FUNCTION GET_GRATE ( p_effective_date IN DATE
113 ,p_assignment_id IN NUMBER
114 ,p_business_group_id IN NUMBER) RETURN NUMBER;
115 FUNCTION GET_SOURCEID (p_source_id IN NUMBER) return number;
116
117 FUNCTION GET_26WEEK (p_assignment_action_id IN NUMBER) return date;
118
119 FUNCTION GET_USERTABLE (p_effective_date IN DATE
120 ,p_business_group_id IN NUMBER
121 ,p_usertable_name IN VARCHAR2
122 ,p_usertable_colname IN VARCHAR2
123 ,p_exact_text IN VARCHAR2 ) return number;
124
125 FUNCTION get_months_employed(p_person_id IN NUMBER
126 ,p_check_start_date IN DATE
127 ,p_check_end_date IN DATE ) return number;
128
129 FUNCTION get_parental_ben_sd(p_assignment_action_id IN NUMBER
130 ,p_element_entry_id IN NUMBER) return date;
131
132 /* Added function get_day_of_week */
133 FUNCTION get_day_of_week(p_date DATE) RETURN NUMBER;
134
135 /* Added function get_adoption_ben_sd */
136 FUNCTION get_adoption_ben_sd(p_assignment_action_id IN NUMBER
137 ,p_element_entry_id IN NUMBER) return date;
138
139 /*Added funtion get_initial_abs_sd*/
140 FUNCTION get_initial_abs_sd(p_org_entry_id IN NUMBER, p_elem_entry_id IN NUMBER) RETURN DATE;
141
142 /* 5261223 Added function for get the Assignment termination date */
143 FUNCTION get_assg_trem_date(p_business_group_id IN NUMBER,
144 p_asg_id IN NUMBER,
145 p_pay_proc_period_start_date IN DATE,
146 p_pay_proc_period_end_date IN DATE) RETURN DATE;
147
148 FUNCTION get_restrict_hol_to_6g(p_business_group_id in number ,
149 p_assignment_id in NUMBER,
150 p_effective_date IN DATE ,
151 p_restrict_hol_to_6G OUT nocopy VARCHAR2) RETURN NUMBER;
152
153 FUNCTION get_holiday_days (p_abs_category in varchar2,
154 p_abs_attendance_id IN NUMBER,
155 p_hol_days OUT nocopy NUMBER ) RETURN NUMBER;
156
157 FUNCTION get_cms_contact_date(p_assignment_id IN NUMBER, p_abs_start_date IN DATE,
158 p_cms_contact_start_date OUT nocopy DATE, p_cms_contact_end_date OUT nocopy DATE,
159 p_cms_contact_count OUT nocopy NUMBER) RETURN NUMBER ;
160
161 FUNCTION get_init_abs_st_date (p_abs_attendance_id IN NUMBER) RETURN DATE;
162
163 FUNCTION get_abs_st_date (p_abs_attendance_id IN NUMBER) RETURN DATE ;
164
165 END PAY_NO_ABSENCE;