1 PACKAGE PAY_FR_SICKNESS_CALC AUTHID CURRENT_USER AS
2 /* $Header: pyfrsick.pkh 120.0 2005/05/29 05:10:37 appldev noship $ */
3 -----------------------------------------------------------------------
4 -- Date Ver Author Comments
5 -- 17-9-02 115.0 Aditya T. Initial version of package with programs reqd by
6 -- Absence report and IJSS calc
7 -- 24-9-02 ASNELL Added stubs for function calls
8 -- 30-9-02 Satyajit Added procedure Calculate_Sickness_Deduction
9 -- and function Get_Open_Days
10 -- 01-10-02 ASNELL modified stub calls
11 -- 07-10-02 ABhaduri Modified functions for IJSS and
12 -- g_overlap declaration
13 -- 08-10-02 ABhaduri Added procedure Get_abs_print_flg
14 -- For checking validity and eligibility
15 -- absences from the report.
16 -- 08-10-02 asnell Added function get_sickness_skip
17 -- 09-10-02 jrhodes Completed calc_sickness
18 -- get_sickness_skip
19 -- compare_guarantee
20 -- Get_backdated_payments
21 -- get_Reference_salary
22 -- 09-10-02 115.9 jrhodes Completed calc_ijss_gross
23 -- Included temproray FR_ROLLING_BALANCE
24 -- 10-10-02 115.10 jrhodes Added t_absence_calc
25 -- .parent_absence_start_date
26 -- 10-10-02 asnell Added get_gi_payments_audit
27 -- 11-10-02 115.13 autiwari Completed Calc_Legal_GI and changed order
28 -- of definitions of procs
29 -- 14-10-02 115.15 asnell added ,p_absence_arch to calc_cagr_gi call
30 -- 05-11-02 115.17 autiwari Modified Get_sickness_skip: added parameters
31 -- action_start_date and action_end_date
32 -- 24-12-02 115.20 abhaduri Modifications for CPAM processing
33 -- Added 3 new functions/procedures.
34 -- Modified t_absence_calc.
35 -- 05-02-03 115.21 asnell change for maternity extensions
36 -- 28-05-03 115.22 autiwari Bug#2977789 - Parameter Subrogated dropped
37 -- from function get_sickness_cpam_skip
38 -- 05-08-03 115.23 asnell added concatenated_inputs and
39 -- concatenated_result_values
40 -- 11-12-03 115.24 asnell added set_global_ids
41 -- ---------------------------------------------------------------------
42 --
43 --
44
45
46 -- GLOBAL DATA STRUCTURES
47 TYPE G_overlap_rec IS RECORD(
48 Absence_day Date,
49 IJSS_Gross Number,
50 IJSS_Net Number,
51 Holiday Varchar2(3));
52 --
53 TYPE t_absence_calc is RECORD
54 (element_entry_id number
55 ,date_earned date
56 ,ID number
57 ,effective_start_date date
58 ,effective_end_date date
59 ,initiator varchar2(30)
60 ,IJSS_payment_start_date date
61 ,IJSS_payment_end_date date
62 ,IJSS_estimated varchar2(30)
63 ,IJSS_subrogated varchar2(30)
64 ,parent_absence_id number
65 ,parent_absence_start_date date
66 ,parent_absence_inf_cat varchar2(30)
67 ,work_incident varchar2(30)
68 ,prior_linked_absence_duration number
69 -- added lines for CPAM processing
70 ,ijss_net_daily_rate number
71 ,ijss_gross_daily_rate number
72 ,abs_ptd_start_date date
73 ,abs_ptd_end_date date);
74
75 g_absence_calc t_absence_calc;
76
77 --
78 TYPE g_overlap_table IS TABLE OF g_overlap_rec INDEX BY BINARY_INTEGER;
79 g_overlap g_overlap_table;
80
81 -- PUBLIC FUNCTIONS
82 FUNCTION IJSS_Eligibility_Working_hours(
83 P_Legislation_code IN Varchar2 := 'FR',
84 P_Business_group_id IN Number,
85 P_Assignment_id IN Number,
86 P_Absence_start_date IN Date,
87 P_long_absence IN Boolean)
88 RETURN Varchar2;
89
90 FUNCTION IJSS_Eligibility_SMID(
91 P_Legislation_code IN Varchar2 := 'FR',
92 P_Business_group_id IN Number,
93 P_Assignment_id IN Number,
94 P_Absence_start_date IN Date,
95 P_long_absence IN Boolean)
96 RETURN Number;
97
98 -- Returns Amount of salary for a period
99 -- Sickness needs (Gross Salary - Professional Reductions)
100 -- Maternity needs (Gross Salary - New balance [Statutory deductions
101 -- +Conventional deductions+CSG-Non mandatory])
102 FUNCTION Get_Reference_salary(
103 P_Business_group_id IN Number,
104 P_Assignment_id IN Number,
105 P_Period_end_date IN Date,
106 P_Absence_category IN Varchar2)
107 RETURN Number;
108
109 -- Returns Amount received as backdated retro payments for the previous calendar year
110 -- Or for the calendar year before previous in a period,
111 -- Depending on the parameter 'calendar_year_before' either '1' or '2'
112 --
113 PROCEDURE Get_backdated_payments(
114 P_Business_group_id IN Number,
115 P_Assignment_id IN Number,
116 P_Period_end_date IN Date,
117 P_Absence_category IN Varchar2 default 'S',
118 p_payment_backyr_1 OUT NOCOPY Number,
119 p_payment_backyr_2 OUT NOCOPY Number) ;
120
121 -- CALC_SICKNESS_DEDUCTION
122 -- fires legislative or user formula as indicated on the establishment
123 -- to calculate the deduction for sickness absence
124
125 PROCEDURE Calculate_Sickness_Deduction
126 (p_absence_start_date IN date,
127 p_absence_end_date IN date,
128 p_asg IN pay_fr_sick_pay_processing.t_asg,
129 p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch);
130
131 -- GET_OPEN_DAYS
132 -- Function to calculate open days between two dates
133 FUNCTION Get_Open_Days
134 (p_start_date IN Date,
135 p_end_date IN Date ) RETURN Number;
136
137
138 -- CALC_IJSS
139 -- calculates IJSS gross and net and populates on the g_overlap table
140 PROCEDURE Calc_IJSS(
141 p_business_group_id IN Number,
142 p_assignment_id IN Number,
143 p_absence_id IN Number,
144 p_start_date IN Date,
145 p_end_date IN Date,
146 p_absence_duration IN Number,
147 p_work_inc_class IN Varchar2);
148
149 -- Checks for IJSS eligibility
150 PROCEDURE IJSS_Eligibility_Check(
151 P_legislation_code IN Varchar2,
152 P_business_group_id IN Number,
153 P_assignment_id IN Number,
154 P_absence_id IN Number,
155 P_abs_start_date IN Date,
156 P_short_term_eligibility OUT NOCOPY Varchar2,
157 P_long_term_eligibility OUT NOCOPY Varchar2,
158 P_Message OUT NOCOPY Varchar2);
159 --
160 -----------------------------------------------------------------------
161 -- Common Functions for LEGI / CAGR
162 --
163 PROCEDURE Get_GI_Bands_Audit
164 (p_GI_id IN Number,
165 p_asg IN pay_fr_sick_pay_processing.t_asg,
166 p_date_to IN Date,
167 p_band_expiry_duration IN Varchar2,
168 p_band1_days OUT NOCOPY Number,
169 p_band2_days OUT NOCOPY Number,
170 p_band3_days OUT NOCOPY Number,
171 p_band4_days OUT NOCOPY Number);
172
173 --
174 -----------------------------------------------------------------------
175 -- Procedure GET_GI_PAYMENTS_AUDIT
176 -----------------------------------------------------------------------
177 -- fetch results from element FR_SICKNESS_GI_INFO for a particular
178 -- absence and guarantee
179 --
180
181 PROCEDURE Get_GI_Payments_Audit
182 (p_GI_id IN Number,
183 p_asg IN pay_fr_sick_pay_processing.t_asg,
184 p_parent_absence_id IN Number,
185 p_current_date IN Date,
186 p_GI_Previous_Net OUT NOCOPY Number,
187 p_GI_Previous_Payment OUT NOCOPY Number,
188 p_GI_Previous_Adjustment OUT NOCOPY Number,
189 p_GI_Previous_IJSS_Gross OUT NOCOPY Number,
190 p_GI_Best_Method OUT NOCOPY Varchar2);
191 -----------------------------------------------------------------------
192
193 PROCEDURE Get_abs_print_flg(p_business_group_id IN Number,
194 p_parent_abs_id IN Number,
195 p_period_end_date IN Date, -- for subrogation date
196 p_person_id IN Number,
197 p_abs_duration OUT NOCOPY Number,-- for eligibility
198 p_invalid_start_date OUT NOCOPY Date, -- for comparison
199 p_subr_start_date OUT NOCOPY Date,
200 p_subr_end_date OUT NOCOPY Date,
201 p_last_absence_date OUT NOCOPY Date,
202 p_maternity_related OUT NOCOPY varchar2) ;
203
204 FUNCTION Get_Sickness_skip(
205 P_Assignment_id IN Number,
206 P_element_entry_id IN Number,
207 P_date_earned IN Date,
208 P_action_start_date IN Date,
209 P_action_end_date IN Date)
210 RETURN Varchar2;
211 --
212 FUNCTION Get_Sickness_skip_result
213 RETURN Varchar2;
214 --
215
216 PROCEDURE Compare_Guarantee
217 (p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch
218 ,p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages);
219 --
220 -- CALC_SICKNESS
221 -- initiates all sickness calculations and performs comparison of
222 -- most beneficial guarantee over the whole absence period.
223 --
224 PROCEDURE calc_sickness(
225 P_mode IN OUT NOCOPY VARCHAR2 ,
226 p_asg IN OUT NOCOPY pay_fr_sick_pay_processing.t_asg,
227 p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch,
228 p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages);
229 --
230 -----------------------------------------------------------------------
231 -- Function FR_ROLLING_BALANCE
232 ----------------------------------------------------------------------
233 Function fr_rolling_balance (p_assignment_id in number,
234 p_balance_name in varchar2,
235 p_balance_start_date in date,
236 p_balance_end_date in date) return number;
237
238 --
239 -----------------------------------------------------------------------
240 -- CALC_LEGAL_GI
241 -----------------------------------------------------------------------
242 -- Calculate Legal Guaranteed Income
243 --
244 PROCEDURE Calc_LEGAL_GI
245 (p_asg IN pay_fr_sick_pay_processing.t_asg
246 ,p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages
247 ,p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch);
248
249 -- CALC_CAGR_GI
250 PROCEDURE Calc_CAGR_GI
251 (p_asg IN pay_fr_sick_pay_processing.t_asg
252 ,p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages
253 ,p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch);
254
255 -- Addded functions and procedures for CPAM processing
256 FUNCTION get_sickness_cpam_skip(
257 p_business_group_id IN Number,
258 P_Assignment_id IN Number,
259 P_element_entry_id IN Number,
260 P_date_earned IN Date,
261 P_payment_start_date IN Date,
262 P_payment_end_date IN Date,
263 --P_subrogated IN Varchar2,
264 P_net_daily_rate IN Number,
265 P_gross_daily_rate IN Number) RETURN Varchar2;
266
267 PROCEDURE Get_CPAM_Ref_salary(
268 P_Business_group_id IN Number,
269 P_Assignment_id IN Number,
270 P_Absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_asg);
271
272 PROCEDURE get_sickness_CPAM_IJSS(
273 p_business_group_id IN Number,
274 p_assignment_id IN Number,
275 p_absence_id IN Number,
276 p_start_date IN Date,
277 p_end_date IN Date,
278 p_work_inc_class IN Varchar2);
279
280 -----------------------------------------------------------------------------
281 -- CONCATENATED_INPUTS
282 -- returns a string that is a concatenation of the entry values for a given
283 -- element entry. For use in reports when presenting the inputs for an
284 -- entry on a single line of information.
285 -----------------------------------------------------------------------------
286 FUNCTION concatenated_inputs(
287 p_element_entry_id in number
288 ,p_effective_date in DATE
289 ,p_delimiter in varchar2 DEFAULT '|'
290 )
291 RETURN varchar2;
292 PRAGMA RESTRICT_REFERENCES (concatenated_inputs ,WNDS,WNPS );
293
294 -----------------------------------------------------------------------------
295 -- CONCATENATED_RESULT_VALUES
296 -- returns a string that is a concatenation of the entry values for a given
297 -- element entry. For use in reports when presenting the inputs for a
298 -- result on a single line of information.
299 -----------------------------------------------------------------------------
300 FUNCTION concatenated_result_values(
301 p_run_result_id in number
302 ,p_delimiter in varchar2 DEFAULT '|'
303 )
304 RETURN varchar2;
305 PRAGMA RESTRICT_REFERENCES (concatenated_result_values ,WNDS,WNPS );
306
307 -----------------------------------------------------------------------------
308 -- CONCATENATED_INPUT_NAMES
309 -- returns a string that is a concatenation of the input names for a given
310 -- element type. For use in reports when providing a key to interpret
311 -- concatenated_inputs string.
312 -----------------------------------------------------------------------------
313 FUNCTION concatenated_input_names(
314 p_element_type_id in number
315 ,p_effective_date in DATE
316 ,p_delimiter in varchar2 DEFAULT '|'
317 )
318 RETURN varchar2;
319 PRAGMA RESTRICT_REFERENCES (concatenated_input_names ,WNDS,WNPS );
320
321 -----------------------------------------------------------------------------
322
323 PROCEDURE set_global_ids( p_effective_date IN date);
324
325 END PAY_FR_SICKNESS_CALC;
[Home] [Help]