1 Package Body pay_fr_attestation_assedic As
2 /* $Header: pyfraasd.pkb 120.0 2005/05/29 04:57:09 appldev noship $ */
3 --
4 --G_stat_cadre stores the pension category of given assignment.
5 G_STAT_CADRE VARCHAR2(4) Default Null;
6
7 Procedure set_defined_balance_ids(p_actual_hours_worked_id Out Nocopy Number ,
8 p_days_unpaid_id Out Nocopy Number ,
9 p_days_partially_paid_id Out Nocopy Number,
10 p_subject_to_unemployment_id Out Nocopy Number,
11 p_non_monthly_earnings_id Out Nocopy Number,
12 p_ee_unemployment_ta_id Out Nocopy Number,
13 p_ee_unemployment_tb_id Out Nocopy Number
14 )Is
15
16 --Changed the cursor to fetch both Transactional and Contractual Indemnity --Bug#2953140.
17 -- Modifeid name of 'FR_ACTUAL_HRS_WORKED' to 'FR_ACTUAL_HRS_WORKED_ASSEDIC'
18 -- as part of time analysis changes
19 Cursor csr_defined_balance_id Is
20 Select
21 max(decode(balance_name,'FR_ACTUAL_HRS_WORKED_ASSEDIC',defined_balance_id)),
22 max(decode(balance_name,'FR_DAYS_UNPAID',defined_balance_id)),
23 max(decode(balance_name,'FR_DAYS_PARTIALLY_PAID',defined_balance_id)),
24 max(decode(balance_name,'FR_SUBJECT_TO_UNEMPLOYMENT',defined_balance_id)),
25 max(decode(balance_name,'FR_NON_MONTHLY_EARNINGS',defined_balance_id)),
26 max(decode(balance_name,'FR_EE_UNEMPLOYMENT_TA',defined_balance_id)),
27 max(decode(balance_name,'FR_EE_UNEMPLOYMENT_TB',defined_balance_id)),
28 max(decode(balance_name,'FR_NPIL_PAYMENT',defined_balance_id)),
29 max(decode(balance_name,'FR_LEGAL_TERMINATION_INDEMNITY',defined_balance_id)),
30 max(decode(balance_name,'FR_CONVENTIONAL_INDEMNITY',defined_balance_id)),
31 max(decode(balance_name,'FR_TRANSACTIONAL_INDEMNITY',defined_balance_id)),
32 max(decode(balance_name,'FR_CONTRACTUAL_INDEMNITY',defined_balance_id))
33 From
34 pay_balance_types pbt ,
35 pay_defined_balances pdb,
36 pay_balance_dimensions pbd
37 Where pbt.balance_type_id = pdb.balance_type_id
38 and pdb.balance_dimension_id = pbd.balance_dimension_id
39 and pbt.balance_name in ('FR_ACTUAL_HRS_WORKED_ASSEDIC',
40 'FR_DAYS_UNPAID',
41 'FR_DAYS_PARTIALLY_PAID',
42 'FR_SUBJECT_TO_UNEMPLOYMENT',
43 'FR_NON_MONTHLY_EARNINGS',
44 'FR_EE_UNEMPLOYMENT_TA' ,
45 'FR_EE_UNEMPLOYMENT_TB',
46 'FR_NPIL_PAYMENT',
47 'FR_LEGAL_TERMINATION_INDEMNITY',
48 'FR_CONVENTIONAL_INDEMNITY',
49 'FR_TRANSACTIONAL_INDEMNITY',
50 'FR_CONTRACTUAL_INDEMNITY'
51 )
52 and pbd.database_item_suffix = decode(pbt.balance_name,'FR_NPIL_PAYMENT','_ASG_ITD'
53 ,'FR_LEGAL_TERMINATION_INDEMNITY','_ASG_ITD'
54 ,'FR_CONVENTIONAL_INDEMNITY','_ASG_ITD'
55 ,'FR_CONTRACTUAL_INDEMNITY', '_ASG_ITD'
56 ,'FR_TRANSACTIONAL_INDEMNITY','_ASG_ITD','_ASG_PTD')
57 and pdb.legislation_code = 'FR'
58 and pbd.legislation_code = 'FR'; --Reduce cost by 50%
59 Begin
60 Open csr_defined_balance_id ;
61 --Fetching values for transactional and contractual indemnity ids also --bug#2953140
62 Fetch csr_defined_balance_id Into p_actual_hours_worked_id ,
63 p_days_unpaid_id,
64 p_days_partially_paid_id ,
65 p_subject_to_unemployment_id,
66 p_non_monthly_earnings_id ,
67 p_ee_unemployment_ta_id,
68 p_ee_unemployment_tb_id,
69 g_npil_payment_id ,
70 g_legal_term_indemnity_id,
71 g_conventional_indemnity_id,
72 g_transactional_indemnity_id,
73 g_contractual_indemnity_id;
74 Close csr_defined_balance_id;
75
76 End set_defined_balance_ids;
77 --
78 Function get_last_fulltime_day_worked(p_person_id In Number ,
79 p_last_day_worked In Date) Return Date Is
80 Cursor csr_last_fulltime_day_worked Is
81 select nvl(min(paa.date_start)-1,p_last_day_worked)
82 from
83 per_absence_attendances paa
84 where exists (select paat.absence_attendance_type_id
85 from per_absence_attendance_types paat
86 Where paat.absence_category in ('S','UL')
87 and paa.absence_attendance_type_id = paat.absence_attendance_type_id
88 ) --Not possible to Join as 'connect by ' with 'join' fails in 8i.
89 and level=rownum
90 start with p_last_day_worked between paa.date_start and paa.date_end and paa.person_id = p_person_id
91 connect by prior paa.date_start = paa.date_end+1 and prior person_id = person_id;
92
93
94 l_last_fulltime_day_worked Date;
95 Begin
96 Open csr_last_fulltime_day_worked ;
97 Fetch csr_last_fulltime_day_worked Into l_last_fulltime_day_worked;
98 Close csr_last_fulltime_day_worked;
99
100 return l_last_fulltime_day_worked;
101
102 End get_last_fulltime_day_worked;
103
104 --
105 Function get_estab_head_count(p_establishment_id Number ,
106 p_actual_termination_date Date) Return Number Is
107
108 Cursor csr_head_count Is
109 Select action_information24
110 From pay_action_information paa,
111 pay_payroll_actions ppa
112 Where paa.action_context_id = ppa.payroll_action_id
113 and action_information_category ='FR_DUCS_PAGE_INFO'
114 and action_information1 = p_establishment_id
115 and action_information2 = 'ASSEDIC'
116 and ppa.report_category = 'DUCS_ARCHIVE'
117 and ppa.report_qualifier = 'FR'
118 and ppa.effective_date = to_date(('31-12-'||to_number(to_char(p_actual_termination_date,'YYYY')-1)),'DD-MM-YYYY') ;
119
120 l_head_count Number;
121
122 Begin
123 Open csr_head_count;
124 Fetch csr_head_count Into l_head_count;
125 Close csr_head_count;
126
127 Return l_head_count ;
128
129 End;
130
131 --
132 Function get_pension_provider_info(p_assignment_id Number ,
133 p_establishment_id Number ,
134 p_termination_date Date,
135 p_type Varchar2) Return Varchar2 Is
136 Cursor csr_pension_provider_id(c_assignment_id Number ,c_establishment_id Number, c_type Varchar2) Is
137 Select
138 'A' flag ,decode(c_type,'ARRCO',entry_information2 ,'AGIRC' ,entry_information4) "Pension_Provider_Id"
139 From
140 pay_element_entries_f peef ,
141 pay_element_links_f pel ,
142 pay_element_types_f pet
143 Where peef.assignment_id = c_assignment_id
144 and peef.element_link_id = pel.element_link_id
145 and pel.element_type_id = pet.element_type_id
146 and p_termination_date between peef.effective_start_date and peef.effective_end_date
147 and p_termination_date between pet.effective_start_date and pet.effective_end_date
148 and p_termination_date between pel.effective_start_date and pel.effective_end_date
149 and pet.element_name = 'FR_PENSION'
150 and decode(c_type,'ARRCO',entry_information2 ,'AGIRC' ,entry_information4) Is Not Null
151 union
152 Select
153 decode(hoi.org_information3,'N','Z',hoi.org_information3) flag ,hoi.org_information1
154 From
155 hr_all_organization_units haou,
156 hr_organization_information hoi
157 Where haou.organization_id = c_establishment_id
158 and haou.organization_id = hoi.organization_id
159 and hoi.org_information_context = 'FR_ESTAB_PE_PRVS'
160 and exists
161 (Select 1
162 From
163 hr_organization_information hoi1
164 Where hoi1.organization_id = hoi.org_information1
165 and hoi1.org_information_context = 'FR_PE_PRV_INFO'
166 and hoi1.org_information2 = c_type
167 ) ;
168
169
170 Cursor csr_name_location(c_organization_id Number ) Is
171 Select
172 haou.name --Establishment_Name
173 ||decode(hla.address_line_1,Null,'',','||hla.address_line_1) --Number_Road
174 ||decode(hla.address_line_2,Null,'',','||hla.address_line_2) --Complement
175 ||decode(hla.region_3,Null,'',','||hla.region_3) --Small Town
176 ||decode(hla.town_or_city,Null,'',','||hla.town_or_city) --City
177 From
178 hr_all_organization_units haou ,
179 hr_locations_all hla
180 where haou.organization_id = c_organization_id
181 and haou.location_id = hla.location_id (+);
182
183 l_pension_provider_id Number;
184 l_flag Varchar2(2);
185 l_name_location Varchar2(2000);
186 Begin
187 --Get the pension provider from 'FR_PENSION' Element Entry.
188 --G_STAT_CADRE is set in pension_category Function.
189 IF ((G_STAT_CADRE = 'Yes') OR (G_STAT_CADRE IS NULL)) THEN
190 -- hr_utility.set_location('Inside IF: Y', 30);
191 Open csr_pension_provider_id(p_assignment_id,p_establishment_id,p_type);
192 Fetch csr_pension_provider_id Into l_flag,l_pension_provider_id;
193 Close csr_pension_provider_id;
194 ELSIF ((G_STAT_CADRE = 'No') AND (p_type='ARRCO')) THEN
195 -- hr_utility.set_location('Inside IF: N', 40);
196 Open csr_pension_provider_id(p_assignment_id,p_establishment_id,p_type);
197 Fetch csr_pension_provider_id Into l_flag,l_pension_provider_id;
198 Close csr_pension_provider_id;
199 -- hr_utility.set_location('Inside Leaving IF: N', 40);
200 END IF;
201
202
203 --Get the Location
204 If l_pension_provider_id Is Not Null Then
205 Open csr_name_location(l_pension_provider_id);
206 Fetch csr_name_location Into l_name_location;
207 Close csr_name_location;
208 End If;
209 --
210 Return l_name_location;
211
212 End get_pension_provider_info;
213
214 --
215 Function pension_category(p_business_group_id Number ,
216 p_assignment_id Number,
217 p_actual_termination_date Date ,
218 p_period_of_service_id Number) Return Varchar2 Is
219 Cursor stat_cadre Is
220 Select hr_reports.get_lookup_meaning('YES_NO',(hruserdt.get_table_value(p_business_group_id ,'FR_APEC_AGIRC','AGIRC',entry_information1,p_actual_termination_date)))
221 From
222 pay_element_entries_f peef ,
223 pay_element_links_f pel ,
224 pay_element_types_f pet
225 Where peef.assignment_id = p_assignment_id
226 and peef.element_link_id = pel.element_link_id
227 and pel.element_type_id = pet.element_type_id
228 and pet.element_name = 'FR_PENSION'
229 and p_actual_termination_date between peef.effective_start_date and peef.effective_end_date
230 and p_actual_termination_date between pet.effective_start_date and pet.effective_end_date
231 and p_actual_termination_date between pel.effective_start_date and pel.effective_end_date ;
232
233 l_temp Varchar2(4);
234 Begin
235 g_service_id(g_service_id.count+1) := p_period_of_service_id;
236
237 --Global variable to store return value of stat_cadre cursor.
238
239 G_STAT_CADRE := NULL;
240 Open stat_cadre ;
241 Fetch stat_cadre Into l_temp;
242 Close stat_cadre;
243 G_STAT_CADRE := l_temp;
244 Return l_temp;
245
246 End pension_category;
247
248 --
249 Procedure insert_date_run(p_effective_date varchar2 ) Is
250 l_ovn Number;
251 Begin
252 For i in g_service_id.First..g_service_id.Last Loop
253 --
254 Select object_version_number
255 Into l_ovn
256 From per_periods_of_service pps
257 where period_of_service_id = g_service_id(i) ;
258 --
259 --hr_utility.trace_on(Null,'ASSEDIC');
260 hr_utility.set_location('p_effective_date='||p_effective_date,10);
261 hr_utility.set_location('period_of_service_id='||g_service_id(i),20);
262 hr_utility.set_location('ovn='||l_ovn,30);
263
264 hr_periods_of_service_api.update_pds_details(P_EFFECTIVE_DATE =>fnd_date.canonical_to_date(p_effective_date)
265 ,P_PERIOD_OF_SERVICE_ID =>g_service_id(i)
266 ,P_OBJECT_VERSION_NUMBER =>l_ovn
267 ,P_PDS_INFORMATION14 =>p_effective_date
268 );
269 --
270 End Loop;
271
272 End;
273
274 --
275 --Changed the signature to return both Transactional and Contractual Indemnities. --Bug#2953140
276
277 Procedure get_termination_indemnities(p_assignment_id In Number ,
278 p_last_day_worked In Date ,
279 p_actual_termination_date In Date ,
280 p_npil Out Nocopy Number ,
281 p_holiday_pay_amount Out Nocopy Number,
282 p_hoilday_pay_rate Out Nocopy Number ,
283 p_ft_contract_indemnity Out Nocopy Number,
284 p_legal_indemnity Out Nocopy Number,
285 p_conventional_indemnity Out Nocopy Number,
286 p_transactional_indemnity Out Nocopy Number,
287 p_contractual_indemnity Out Nocopy Number) Is
288
289 Cursor csr_get_hpil Is
290 Select /*+ORDERED*/
291 pap.information6 accounting_method,
292 sum(decode(piv.name,'Pay Value',prrv.result_value)) ,
293 sum(decode(piv.name,'Rate',prrv.result_value))
294 From
295 pay_assignment_actions paa,
296 pay_payroll_actions ppa,
297 pay_accrual_plans pap ,
298 pay_input_values_f piv_base,
299 pay_element_types_f pet ,
300 pay_element_classifications pec ,
301 pay_run_results prr,
302 pay_input_values_f piv,
303 pay_run_result_values prrv
304 Where paa.assignment_id = p_assignment_id
305 and ppa.payroll_action_id = paa.payroll_action_id
306 and ppa.date_earned >= p_last_day_worked
307 and ppa.action_type in ('Q','R','B','I')
308 and ppa.business_group_id = pap.business_group_id
309 and pap.accrual_category like 'FR%HOLIDAY'
310 and pap.information_category like 'FR_FR%HOLIDAY'
311 and pap.information28 = piv_base.input_value_id
312 and piv_base.element_type_id = pet.element_type_id
313 and pec.classification_id = pet.classification_id
314 and pec.classification_name = 'Earnings'
315 and pec.business_group_id is null
316 and pec.legislation_code = 'FR'
317 and prr.assignment_action_id = paa.assignment_action_id
318 and prr.element_type_id = pet.element_type_id
319 and prr.status in ('P','PA')
320 and pet.element_type_id = piv.element_type_id
321 and piv.name in ('Pay Value','Rate')
322 and prrv.run_result_id = prr.run_result_id
323 and prrv.input_value_id = piv.input_value_id
324 and p_actual_termination_date between pet.effective_start_date
325 and pet.effective_end_date
326 and p_actual_termination_date between piv.effective_start_date
327 and piv.effective_end_date
328 and p_actual_termination_date between piv_base.effective_start_date
329 and piv_base.effective_end_date
330 group by paa.assignment_id ,pap.information6;
331
332 Cursor csr_get_ft_indemnity Is
333 Select
334 sum(decode(pet.element_name,'FR_FIXED_TERM_CONTRACT_INDEMNITY_PAY',prrv.result_value))
335 From
336 pay_element_types_f pet ,
337 pay_input_values_f piv ,
338 pay_payroll_actions ppa,
339 pay_assignment_actions paa,
340 pay_run_results prr,
341 pay_run_result_values prrv
342 Where pet.element_type_id = piv.element_type_id
343 and ppa.payroll_action_id = paa.payroll_action_id
344 and prr.assignment_action_id = paa.assignment_action_id
345 and prr.element_type_id = pet.element_type_id
346 and prr.status in ('P','PA')
347 and prrv.run_result_id = prr.run_result_id
348 and prrv.input_value_id = piv.input_value_id
349 and ppa.action_type in ('Q','R','B','I')
350 and pet.element_name ='FR_FIXED_TERM_CONTRACT_INDEMNITY_PAY'
351 and piv.name = 'Pay Value'
352 and paa.assignment_id = p_assignment_id
353 and ppa.date_earned >= p_last_day_worked
354 and p_actual_termination_date between pet.effective_start_date and pet.effective_end_date
355 and p_actual_termination_date between piv.effective_start_date and piv.effective_end_date
356 group by paa.assignment_id;
357
358 --Changed the cursor to fetch both Transactional and contractual balances --Bug#2953140.
359 Cursor csr_get_indemnities Is
360 Select
361 pay_balance_pkg.get_value(g_npil_payment_id,max(paa.assignment_action_id)) npil,
362 pay_balance_pkg.get_value(g_legal_term_indemnity_id,max(paa.assignment_action_id)) legal,
363 pay_balance_pkg.get_value(g_conventional_indemnity_id,max(paa.assignment_action_id)) conventional,
364 pay_balance_pkg.get_value(g_contractual_indemnity_id,max(paa.assignment_action_id)) contractual,
365 pay_balance_pkg.get_value(g_transactional_indemnity_id,max(paa.assignment_action_id)) transactional
366 From
367 pay_payroll_actions ppa ,
368 pay_assignment_actions paa
369 Where ppa.payroll_action_id = paa.payroll_action_id
370 and paa.assignment_id = p_assignment_id
371 and ppa.action_type in ('R' ,'Q')
372 and paa.action_status = 'C'
373 and paa.source_action_id Is Null
374 and ppa.date_earned >= p_last_day_worked
375 group by paa.assignment_id ;
376 l_accounting_method Varchar2(30);
377 Begin
378
379 Open csr_get_ft_indemnity;
380 Fetch csr_get_ft_indemnity Into p_ft_contract_indemnity;
381 Close csr_get_ft_indemnity;
382 --
383 Open csr_get_indemnities;
384 Fetch csr_get_indemnities Into p_npil,
385 p_legal_indemnity ,
386 p_conventional_indemnity,
387 p_contractual_indemnity,
388 p_transactional_indemnity;
389 Close csr_get_indemnities;
390 --
391 Open csr_get_hpil ;
392 Fetch csr_get_hpil Into l_accounting_method,p_holiday_pay_amount,p_hoilday_pay_rate;
393 If l_accounting_method ='FR_WORK_DAYS' Then --Bug:2883952
394 p_hoilday_pay_rate := ceil(p_hoilday_pay_rate*6/5);
395 End If;
396 Close csr_get_hpil ;
397 --
398
399 End get_termination_indemnities;
400
401
402 End pay_fr_attestation_assedic;
[Home] [Help]