[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ARCHIVE_MTRA
Source
1 PACKAGE BODY PAY_FI_ARCHIVE_MTRA AS
2 /* $Header: pyfimtra.pkb 120.5 2012/01/19 09:18:52 rpahune ship $ */
3
4 TYPE lock_rec IS RECORD (
5 archive_assact_id NUMBER);
6
7 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
8
9 g_debug boolean := hr_utility.debug_enabled;
10 g_lock_table lock_table;
11 g_index NUMBER := -1;
12 g_index_assact NUMBER := -1;
13 g_index_bal NUMBER := -1;
14 g_package VARCHAR2(33) := ' PAY_FI_ARCHIVE_MTRA.';
15 g_payroll_action_id NUMBER;
16 g_arc_payroll_action_id NUMBER;
17 g_business_group_id NUMBER;
18 g_format_mask VARCHAR2(50);
19 g_err_num NUMBER;
20 g_errm VARCHAR2(150);
21 /* GET PARAMETER */
22 FUNCTION GET_PARAMETER(
23 p_parameter_string IN VARCHAR2
24 ,p_token IN VARCHAR2
25 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
26 IS
27 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
28 l_start_pos NUMBER;
29 l_delimiter VARCHAR2(1):=' ';
30 l_proc VARCHAR2(40):= g_package||' get parameter ';
31 BEGIN
32 --
33 IF g_debug THEN
34 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
35 END IF;
36 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
37 --
38 IF l_start_pos = 0 THEN
39 l_delimiter := '|';
40 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
41 END IF;
42
43 IF l_start_pos <> 0 THEN
44 l_start_pos := l_start_pos + length(p_token||'=');
45 l_parameter := substr(p_parameter_string,
46 l_start_pos,
47 instr(p_parameter_string||' ',
48 l_delimiter,l_start_pos)
49 - l_start_pos);
50 IF p_segment_number IS NOT NULL THEN
51 l_parameter := ':'||l_parameter||':';
52 l_parameter := substr(l_parameter,
53 instr(l_parameter,':',1,p_segment_number)+1,
54 instr(l_parameter,':',1,p_segment_number+1) -1
55 - instr(l_parameter,':',1,p_segment_number));
56 END IF;
57 END IF;
58 --
59 IF g_debug THEN
60 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
61 END IF;
62
66
63 RETURN l_parameter;
64
65 END;
67 /* GET ALL PARAMETERS */
68 PROCEDURE GET_ALL_PARAMETERS(
69 p_payroll_action_id IN NUMBER
70 ,p_business_group_id OUT NOCOPY NUMBER
71 ,p_legal_employer_id OUT NOCOPY NUMBER
72 ,p_local_unit_id OUT NOCOPY NUMBER
73 ,p_adjustment_wt OUT NOCOPY NUMBER
74 ,p_adjustment_ss OUT NOCOPY NUMBER
75 ,p_adjustment_ts OUT NOCOPY NUMBER
76 ,p_vat OUT NOCOPY NUMBER
77 ,p_month OUT NOCOPY VARCHAR2
78 ,p_year OUT NOCOPY VARCHAR2
79 ,p_due_date OUT NOCOPY DATE
80 ,p_ref_number OUT NOCOPY NUMBER
81 ,p_effective_date OUT NOCOPY DATE
82 ,p_archive OUT NOCOPY VARCHAR2
83 ) IS
84
85 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
86 SELECT PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
87 ,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_ID')
88 ,FND_NUMBER.CANONICAL_TO_NUMBER(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'ADJUSTMENT_WT'))
89 ,FND_NUMBER.CANONICAL_TO_NUMBER(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'ADJUSTMENT_SS'))
90 ,FND_NUMBER.CANONICAL_TO_NUMBER(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'ADJUSTMENT_TS'))
91 ,FND_NUMBER.CANONICAL_TO_NUMBER(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'VAT'))
92 ,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'MONTH_RPT')
93 ,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'YEAR_RPT')
94 ,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
95 ,FND_DATE.CANONICAL_TO_DATE(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'DUE_DATE'))
96 ,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'REF_NUMBER')
97 ,effective_date
98 ,business_group_id
99 FROM pay_payroll_actions
100 WHERE payroll_action_id = p_payroll_action_id;
101 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
102 --
103 BEGIN
104
105 OPEN csr_parameter_info (p_payroll_action_id);
106
107 FETCH csr_parameter_info
108 INTO p_legal_employer_id
109 ,p_local_unit_id
110 ,p_adjustment_wt
111 ,p_adjustment_ss
112 ,p_adjustment_ts
113 ,p_vat
114 ,p_month
115 ,p_year
116 ,p_archive
117 ,p_due_date
118 ,p_ref_number
119 ,p_effective_date
120 ,p_business_group_id;
121
122 CLOSE csr_parameter_info;
123 --
124 IF g_debug THEN
125 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
126 END IF;
127 END GET_ALL_PARAMETERS;
128
129 /* RANGE CODE */
130 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
131 ,p_sql OUT NOCOPY VARCHAR2)
132 IS
133
134
135 l_action_info_id NUMBER;
136 l_ovn NUMBER;
137 l_start_date VARCHAR2(30);
138 l_end_date VARCHAR2(30);
139 l_defined_balance_id NUMBER := 0;
140 l_count NUMBER := 0;
141 l_prev_prepay NUMBER := 0;
142 l_prepay_action_id NUMBER;
143 l_actid NUMBER;
144 l_assignment_id NUMBER;
145 l_action_sequence NUMBER;
146 l_assact_id NUMBER;
147 l_pact_id NUMBER;
148 l_flag NUMBER := 0;
149 l_element_context VARCHAR2(5);
150 l_business_group_id NUMBER;
151 l_month VARCHAR2(2);
152 l_year VARCHAR2(4);
153 l_canonical_start_date DATE;
154 l_canonical_end_date DATE;
155 l_effective_date DATE;
156 l_legal_employer_id NUMBER ;
157 l_local_unit_id NUMBER ;
158 l_adjustment_wt NUMBER ;
159 l_adjustment_ss NUMBER ;
160 l_adjustment_ts NUMBER ;
161 l_vat NUMBER ;
162 l_emp_type VARCHAR2(2);
163 l_emp_id hr_organization_units.organization_id%TYPE ;
164 l_due_date1 DATE ;
165 l_due_date2 VARCHAR2(30);
166 l_ref_number NUMBER ;
167 l_ref_num VARCHAR2(20);
168
169 l_Record_code VARCHAR2(240) :=' ';
170 l_tax_payer_name hr_organization_units.name%TYPE ;
171 l_le_name hr_organization_units.name%TYPE ;
172 l_lu_name hr_organization_units.name%TYPE ;
173 l_address_line_1 hr_locations.address_line_1%TYPE ;
174 l_address_line_2 hr_locations.address_line_2%TYPE ;
175 l_address_line_3 hr_locations.address_line_3%TYPE ;
176 l_postal_code hr_locations.postal_code%TYPE ;
177 l_town_or_city hr_locations.town_or_city%TYPE ;
178 l_country hr_locations.country%TYPE ;
179 l_business_id hr_organization_information.org_information1%TYPE ;
180 l_Sal_subject_Wt NUMBER;
181 l_Sal_subject_Ts NUMBER;
182 l_Pay_subject_Wt NUMBER;
183 l_Pay_subject_ts NUMBER;
184 l_wt_deduction NUMBER;
185 l_employer_ss_fee NUMBER;
186 l_ss_fee NUMBER;
187 l_exem_ss_fee NUMBER;
188 l_ins_ss_fee NUMBER;
189 l_ts_deduction NUMBER;
190 l_vat_bal NUMBER;
191 l_y_number hr_organization_information.org_information1%TYPE ;
192 l_contact_person hr_organization_information.org_information1%TYPE ;
193 l_phone hr_organization_information.org_information1%TYPE ;
194 l_fax hr_organization_information.org_information1%TYPE ;
195 l_tax_office_ba pay_external_accounts.segment3%TYPE ;
199 l_employer_pay_meth_id hr_organization_information.org_information1%TYPE ;
196 l_tax_office_pay_meth_id hr_organization_information.org_information2%TYPE ;
197 l_tax_office_name hr_organization_units.name%TYPE ;
198 l_employer_ba pay_external_accounts.segment3%TYPE ;
200 l_tax_office_id hr_organization_information.organization_id%TYPE ;
201 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
202 l_counter number := 0;
203 l_archive VARCHAR2(3);
204 l_reporting_date DATE;
205 l_termination_date date;
206 l_ele_effective_date date;
207 l_tax_card_type_code varchar2 (50);
208 l_subsidy_for_low_paid_emp number ;
209 l_subsidy_witholding_tax_ded number := 0;
210 l_subsidy_tax_at_source_dec number := 0;
211 l_subsidy_for_low_paid_bal ff_database_items.user_name%type;
212
213
214
215
216
217
218 /* Cursors */
219
220 Cursor csr_Local_Unit_Details ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
221 IS
222 SELECT o1.name , hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION7
223 FROM hr_organization_units o1
224 , hr_organization_information hoi1
225 , hr_organization_information hoi2
226 WHERE o1.business_group_id =l_business_group_id
227 AND hoi1.organization_id = o1.organization_id
228 AND hoi1.organization_id = csr_v_local_unit_id
229 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
230 AND hoi1.org_information_context = 'CLASS'
231 AND o1.organization_id =hoi2.organization_id
232 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
233
234 rg_Local_Unit_Details csr_Local_Unit_Details%rowtype;
235
236 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
237 IS
238 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION11
239 FROM hr_organization_units o1
240 , hr_organization_information hoi1
241 , hr_organization_information hoi2
242 WHERE o1.business_group_id =l_business_group_id
243 AND hoi1.organization_id = o1.organization_id
244 AND hoi1.organization_id = csr_v_legal_emp_id
245 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
246 AND hoi1.org_information_context = 'CLASS'
247 AND o1.organization_id =hoi2.organization_id
248 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS' ;
249
250
251 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
252
253 Cursor csr_Legal_Emp_Contact ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
254 IS
255 SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
256 FROM hr_organization_units o1
257 , hr_organization_information hoi1
258 , hr_organization_information hoi2
259 , hr_organization_information hoi3
260 , hr_organization_information hoi4
261 WHERE o1.business_group_id =l_business_group_id
262 AND hoi1.organization_id = o1.organization_id
263 AND hoi1.organization_id = csr_v_legal_emp_id
264 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
265 AND hoi1.org_information_context = 'CLASS'
266 AND hoi2.organization_id (+)= o1.organization_id
267 AND hoi2.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
268 AND hoi2.org_information1(+)= 'FAX'
269 AND hoi3.organization_id (+)= o1.organization_id
270 AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
271 AND hoi3.org_information1 (+)= 'PHONE'
272 AND hoi4.organization_id (+)= o1.organization_id
273 AND hoi4.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
274 AND hoi4.org_information1 (+)= 'PERSON' ;
275
276 rg_Legal_Emp_Contact csr_Legal_Emp_Contact%rowtype;
277
278 Cursor csr_Local_Unit_contact ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
279 IS
280 SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
281 FROM hr_organization_units o1
282 , hr_organization_information hoi1
283 , hr_organization_information hoi2
284 , hr_organization_information hoi3
285 , hr_organization_information hoi4
286 WHERE o1.business_group_id =l_business_group_id
287 AND hoi1.organization_id = o1.organization_id
288 AND hoi1.organization_id = csr_v_local_unit_id
289 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
290 AND hoi1.org_information_context = 'CLASS'
291 AND hoi2.organization_id (+)= o1.organization_id
292 AND hoi2.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
293 AND hoi2.org_information1(+)= 'FAX'
294 AND hoi3.organization_id (+)= o1.organization_id
295 AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
296 AND hoi3.org_information1 (+)= 'PHONE'
297 AND hoi4.organization_id (+)= o1.organization_id
298 AND hoi4.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
299 AND hoi4.org_information1 (+)= 'PERSON' ;
300
301 rg_Local_Unit_contact csr_Local_Unit_contact%rowtype;
302
303 Cursor csr_Local_Unit_addr ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
304 IS
305 SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
306 hoi1.POSTAL_CODE , hoi1.TOWN_OR_CITY , hoi1.COUNTRY
307 FROM hr_organization_units o1
308 , hr_locations hoi1
312 AND hoi2.organization_id = o1.organization_id
309 ,hr_organization_information hoi2
310 WHERE o1.business_group_id = l_business_group_id
311 AND hoi1.location_id = o1.location_id
313 AND hoi2.organization_id = csr_v_local_unit_id
314 AND hoi2.org_information1 = 'FI_LOCAL_UNIT'
315 AND hoi2.org_information_context = 'CLASS' ;
316
317 rg_Local_Unit_addr csr_Local_Unit_addr%rowtype;
318
319 Cursor csr_Legal_Emp_addr ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
320 IS
321 SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
322 hoi1.POSTAL_CODE , hoi1.TOWN_OR_CITY , hoi1.COUNTRY
323 FROM hr_organization_units o1
324 , hr_locations hoi1
325 ,hr_organization_information hoi2
326 WHERE o1.business_group_id = l_business_group_id
327 AND hoi1.location_id = o1.location_id
328 AND hoi2.organization_id = o1.organization_id
329 AND hoi2.organization_id = csr_v_legal_emp_id
330 AND hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
331 AND hoi2.org_information_context = 'CLASS' ;
332
333
334 rg_Legal_Emp_addr csr_Legal_Emp_addr%rowtype;
335
336 Cursor csr_Tax_Office_Details ( csr_v_tax_office_id hr_organization_information.ORGANIZATION_ID%TYPE)
337 IS
338 SELECT o1.name ,hoi2.ORG_INFORMATION1 ,hoi2.ORG_INFORMATION2
339 FROM hr_organization_units o1
340 , hr_organization_information hoi1
341 , hr_organization_information hoi2
342 , hr_organization_information hoi3
343 WHERE o1.business_group_id =l_business_group_id
344 AND hoi1.organization_id = o1.organization_id
345 AND hoi1.organization_id = csr_v_tax_office_id
346 AND hoi1.org_information1 = 'PROV_TAX_OFFICE'
347 AND hoi1.org_information_context = 'CLASS'
348 AND hoi3.organization_id = o1.organization_id
349 AND hoi3.org_information1 = 'HR_PAYEE'
350 AND hoi3.org_information_context = 'CLASS'
351 AND o1.organization_id =hoi2.organization_id
352 AND hoi2.ORG_INFORMATION_CONTEXT='FI_THIRD_PARTY_PAYMENT';
353
354 rg_Tax_Office_Details csr_Tax_Office_Details%rowtype;
355
356 Cursor csr_account_number ( csr_v_payment_method_id pay_org_payment_methods_f.org_payment_method_id%TYPE)
357 IS
358 SELECT b.segment3
359 FROM pay_org_payment_methods_f a , pay_external_accounts b
360 WHERE a.org_payment_method_id = csr_v_payment_method_id
361 AND a.external_account_id = b.external_account_id
362 AND a.business_group_id=l_business_group_id
363 AND a.pmeth_information_category ='FI Third Party Payment';
364
365 rg_account_number csr_account_number%rowtype;
366
367 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
368 IS
369 SELECT ue.creator_id
370 FROM ff_user_entities ue,
371 ff_database_items di
372 WHERE di.user_name = csr_v_Balance_Name
373 AND ue.user_entity_id = di.user_entity_id
374 AND ue.legislation_code = 'FI'
375 AND ue.business_group_id is NULL
376 AND ue.creator_type = 'B';
377
378 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
379
380 /* Cursor to take all the payroll runs for the given period for given Payroll Type and Payroll */
381 cursor csr_prepaid_assignments_lu (
382
383 p_legal_employer_id number,
384 p_local_unit_id number,
385 p_start_date date,
386 p_end_date date
387 ) is
388 select paaf.person_id, act.assignment_id assignment_id, act.assignment_action_id run_action_id,
389 act1.assignment_action_id
390 prepaid_action_id, appa.effective_date, appa.payroll_action_id,
391 appa2.payroll_action_id payactid, hsck.segment2 local_unit_id
392 from pay_payroll_actions appa,
393 pay_payroll_actions appa2,
394 pay_assignment_actions act,
395 pay_assignment_actions act1,
396 pay_action_interlocks pai,
397 per_all_assignments_f paaf,
398 hr_soft_coding_keyflex hsck,
399 hr_organization_information hoi--,
400 -- pay_payrolls_f ppa
401 where appa.action_type in ('R', 'Q')
402 and act.payroll_action_id = appa.payroll_action_id
403 and act.source_action_id is null -- Master Action
404 and act.action_status IN ('C','S') -- 10229501
405 and act.assignment_action_id = pai.locked_action_id
406 and act1.assignment_action_id = pai.locking_action_id
407 and act1.action_status IN ('C','S') -- 10229501
408 and act1.payroll_action_id = appa2.payroll_action_id
409 and appa2.action_type in ('P', 'U')
410 and paaf.assignment_id = act.assignment_id
411 -- and paaf.assignment_id = p_assignemtn_id
412 and appa.effective_date between paaf.effective_start_date and paaf.effective_end_date
413 and appa.effective_date between p_start_date and p_end_date
414 and paaf.primary_flag = 'Y'
415 -- and paaf.person_id between p_start_person and p_end_person
416 -- and ppa.payroll_id = paaf.payroll_id
417 -- and ppa.payroll_id = nvl (g_payroll_id, ppa.payroll_id)
418 -- and ppa.period_type = g_payroll_type
419 -- and g_year_last_date between ppa.effective_start_date and ppa.effective_end_date
420 and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
424 and hoi.org_information1 = hsck.segment2
421 and hsck.segment2 = nvl (to_char (p_local_unit_id), hsck.segment2)
422 and hoi.organization_id = p_legal_employer_id
423 and hoi.org_information_context = 'FI_LOCAL_UNITS'
425 order by person_id, assignment_id, payroll_action_id, prepaid_action_id;
426
427 /* Cursor to get the latest effective_date */
428 cursor csr_asg_effective_date (
429 p_asg_id number,
430 p_end_date date,
431 p_start_date date,
432 p_business_group_id number
433 ) is
434 select max (effective_end_date) effective_date
435 from per_all_assignments_f paa
436 where assignment_id = p_asg_id
437 and paa.effective_start_date <= p_end_date
438 and paa.effective_end_date > = p_start_date
439 and assignment_status_type_id in (select assignment_status_type_id
440 from per_assignment_status_types
441 where per_system_status = 'ACTIVE_ASSIGN'
442 and active_flag = 'Y'
443 and ( (legislation_code is null and business_group_id is null)
444 or (business_group_id = p_business_group_id)
445 ));
446 /* To get the tax Card Type */
447 cursor get_element_details (
448 p_assignment_id number,
449 p_value_date date
450 ) is
451 select eev1.screen_entry_value screen_entry_value
452 from per_all_assignments_f asg1,
453 per_all_assignments_f asg2,
454 per_all_people_f per,
455 pay_element_links_f el,
456 pay_element_types_f et,
457 pay_input_values_f iv1,
458 pay_element_entries_f ee,
459 pay_element_entry_values_f eev1
460 where asg1.assignment_id = p_assignment_id
461 and p_value_date between asg1.effective_start_date and asg1.effective_end_date
462 and p_value_date between asg2.effective_start_date and asg2.effective_end_date
463 and p_value_date between per.effective_start_date and per.effective_end_date
464 and per.person_id = asg1.person_id
465 and asg2.person_id = per.person_id
466 and asg2.primary_flag = 'Y'
467 and et.element_name = 'Tax Card'
468 and (et.legislation_code = 'FI' or et.business_group_id = g_business_group_id)
469 and iv1.element_type_id = et.element_type_id
470 and iv1.name = 'Tax Card Type'
471 and el.business_group_id = per.business_group_id
472 and el.element_type_id = et.element_type_id
473 and ee.assignment_id = asg2.assignment_id
474 and ee.element_link_id = el.element_link_id
475 and eev1.element_entry_id = ee.element_entry_id
476 and eev1.input_value_id = iv1.input_value_id
477 and p_value_date between ee.effective_start_date and ee.effective_end_date
478 and p_value_date between eev1.effective_start_date and eev1.effective_end_date;
479
480 l_person_id per_all_people_f.person_id%type := -1;
481 l_month_start_date date;
482
483 /* End of Cursors */
484
485
486 BEGIN
487 -- g_debug:=true;
488 IF g_debug THEN
489 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
490 END IF;
491
492 p_sql := 'SELECT DISTINCT person_id
493 FROM per_people_f ppf
494 ,pay_payroll_actions ppa
495 WHERE ppa.payroll_action_id = :payroll_action_id
496 AND ppa.business_group_id = ppf.business_group_id
497 ORDER BY ppf.person_id';
498
499 PAY_FI_ARCHIVE_MTRA.GET_ALL_PARAMETERS(
500 p_payroll_action_id
501 ,l_business_group_id
502 ,l_legal_employer_id
503 ,l_local_unit_id
504 ,l_adjustment_wt
505 ,l_adjustment_ss
506 ,l_adjustment_ts
507 ,l_vat
508 ,l_month
509 ,l_year
510 ,l_due_date1
511 ,l_ref_number
512 ,l_effective_date
513 ,l_archive ) ;
514
515 l_reporting_date := last_day(to_date(l_month || l_year,'MMYYYY'));
516
517 l_ref_num:=lpad(l_ref_number,20,'0');
518
519 IF l_due_date1 IS NULL THEN
520 l_due_date2:= NULL;
521 ELSE
522 l_due_date2:= FND_DATE.DATE_TO_CANONICAL(l_due_date1);
523 END IF;
524
525
526 IF l_archive = 'Y' THEN
527
528
529
530 SELECT count(*) INTO l_count
531 FROM pay_action_information
532 WHERE action_information_category = 'EMEA REPORT DETAILS'
533 AND action_information1 = 'PYFIMTRA'
534 AND action_context_id = p_payroll_action_id;
535
536
537
538 IF l_count < 1 then
539
540
541 hr_utility.set_location('Entered Procedure GETDATA',10);
542
543 BEGIN
544 pay_balance_pkg.set_context('TAX_UNIT_ID',l_legal_employer_id);
545 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id);
546 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(l_reporting_date));
547 pay_balance_pkg.set_context('JURISDICTION_CODE',NULL);
548 pay_balance_pkg.set_context('SOURCE_ID',NULL);
549 pay_balance_pkg.set_context('TAX_GROUP',NULL);
550 END;
554 hr_utility.set_location('Calculated the Wage Payment Month and Due Date',30);
551 hr_utility.set_location('Set the contexts',20);
552
553
555
556
557 OPEN csr_Legal_Emp_Details(l_legal_employer_id);
558 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
559 CLOSE csr_Legal_Emp_Details;
560
561 l_le_name := rg_Legal_Emp_Details.name ;
562 l_y_number := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
563
564
565 IF l_local_unit_id IS NOT NULL THEN
566
567 l_emp_type:='LU' ;
568 l_emp_id:=l_local_unit_id;
569 hr_utility.set_location('Calculation for Local Unit',40);
570
571 BEGIN
572
573 SELECT MAX(ASSIGNMENT_ACTION_ID)
574 INTO l_assignment_action_id
575 FROM pay_run_balances
576 WHERE local_unit_id = l_local_unit_id
577 AND TO_CHAR(effective_date,'MMYYYY')=l_month||l_year ;
578
579 EXCEPTION
580 WHEN others THEN
581 NULL;
582 END;
583
584 hr_utility.set_location('Fetched the Assignment action id',50);
585
586
587 /* Pick up the details belonging to Local Unit */
588
589 OPEN csr_Local_Unit_Details( l_local_unit_id);
590 FETCH csr_Local_Unit_Details INTO rg_Local_Unit_Details;
591 CLOSE csr_Local_Unit_Details;
592
593 l_tax_payer_name := rg_Local_Unit_Details.name ;
594 l_lu_name := rg_Local_Unit_Details.name ;
595 l_business_id := l_y_number||'-'||rg_Local_Unit_Details.ORG_INFORMATION1 ;
596 l_tax_office_id := rg_Local_Unit_Details.ORG_INFORMATION7 ;
597
598
599 hr_utility.set_location('Pick up the details belonging to Local Unit',60);
600
601 /* Pick up the contact details belonging to Local Unit*/
602
603 OPEN csr_Local_Unit_contact( l_local_unit_id);
604 FETCH csr_Local_Unit_contact INTO rg_Local_Unit_contact;
605 CLOSE csr_Local_Unit_contact;
606
610
607 l_contact_person := rg_Local_Unit_contact.contact_person ;
608 l_phone := rg_Local_Unit_contact.phone ;
609 l_fax := rg_Local_Unit_contact.fax ;
611 hr_utility.set_location('Pick up the contact details belonging to Local Unit',70);
612
613 /* Pick up the Address details belonging to Local Unit*/
614
615 OPEN csr_Local_Unit_addr( l_local_unit_id);
616 FETCH csr_Local_Unit_addr INTO rg_Local_Unit_addr;
617 CLOSE csr_Local_Unit_addr;
618
619 l_address_line_1 := rg_Local_Unit_addr.address_line_1 ;
620 l_address_line_2 := rg_Local_Unit_addr.address_line_2 ;
621 l_address_line_3 := rg_Local_Unit_addr.address_line_3 ;
622 l_postal_code := rg_Local_Unit_addr.postal_code ;
623 l_town_or_city := rg_Local_Unit_addr.town_or_city ;
624 l_country := rg_Local_Unit_addr.country ;
625
626
627 l_subsidy_for_low_paid_bal := 'SUBSIDY_FOR_LOW_PAID_EMPLOYEES_PER_LU_MONTH';
628
629 OPEN csr_Get_Defined_Balance_Id( 'TAX_AT_SOURCE_LU_MONTH');
630
631 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
632
633 CLOSE csr_Get_Defined_Balance_Id;
634
635 l_ts_deduction := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
636
637
638
639 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX_LU_MONTH');
640 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
641 CLOSE csr_Get_Defined_Balance_Id;
642
643 l_wt_deduction := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
644
645
646 OPEN csr_Get_Defined_Balance_Id( 'VAT_LU_MONTH');
647 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
648 CLOSE csr_Get_Defined_Balance_Id;
649
650 l_vat_bal := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
651
652 OPEN csr_Get_Defined_Balance_Id( 'SALARIES_SUBJECT_TO_WITHHOLD_TAX_LU_MONTH');
653 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
654 CLOSE csr_Get_Defined_Balance_Id;
655
656 l_Sal_subject_Wt :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
657
661
658 OPEN csr_Get_Defined_Balance_Id( 'SALARIES_SUBJECT_TO_TAX_AT_SOURCE_LU_MONTH');
659 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
660 CLOSE csr_Get_Defined_Balance_Id;
662
663
664 l_Sal_subject_Ts := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
665
666 OPEN csr_Get_Defined_Balance_Id( 'PAYMENTS_SUBJECT_TO_WITHHOLD_TAX_LU_MONTH');
667 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
668 CLOSE csr_Get_Defined_Balance_Id;
669
670 l_Pay_subject_Wt := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
671
672 OPEN csr_Get_Defined_Balance_Id( 'PAYMENTS_SUBJECT_TO_TAX_AT_SOURCE_LU_MONTH');
673 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
674 CLOSE csr_Get_Defined_Balance_Id;
675
676 l_Pay_subject_ts := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
677
678
679 OPEN csr_Get_Defined_Balance_Id( 'SOCIAL_SECURITY_LU_MONTH');
680 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
681 CLOSE csr_Get_Defined_Balance_Id;
682
683 l_ss_fee :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
684
685 OPEN csr_Get_Defined_Balance_Id( 'EXEMPTED_SOCIAL_SECURITY_LU_MONTH');
686 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
687 CLOSE csr_Get_Defined_Balance_Id;
688
689 l_exem_ss_fee := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
690
691
692
693 OPEN csr_Get_Defined_Balance_Id( 'INSURANCE_SALARY_LU_MONTH');
694 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
695 CLOSE csr_Get_Defined_Balance_Id;
696
697 l_ins_ss_fee := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id );
698
699 l_employer_ss_fee := nvl(l_ss_fee,0) + nvl(l_exem_ss_fee,0) + nvl(l_ins_ss_fee ,0) ;
700 ELSE
701
702 l_emp_type:='LE' ;
703 l_emp_id:=l_legal_employer_id ;
704
705 BEGIN
706
707 SELECT MAX(ASSIGNMENT_ACTION_ID)
708 INTO l_assignment_action_id
709 FROM pay_run_balances
710 WHERE tax_unit_id =l_legal_employer_id
711 AND TO_CHAR(effective_date,'MMYYYY')=l_month||l_year ;
712
713 EXCEPTION
714 WHEN others THEN
715 NULL;
716 END;
717
718 /* Pick up the details belonging to Legal Employer */
719
720 OPEN csr_Legal_Emp_Details(l_legal_employer_id);
721 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
722 CLOSE csr_Legal_Emp_Details;
723
724 l_tax_payer_name := rg_Legal_Emp_Details.name ;
725 l_business_id := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
726 l_tax_office_id := rg_Legal_Emp_Details.ORG_INFORMATION11 ;
727
728 /* Pick up the contact details belonging to Legal Employer */
729
730 OPEN csr_Legal_Emp_contact( l_legal_employer_id);
731 FETCH csr_Legal_Emp_contact INTO rg_Legal_Emp_contact;
732 CLOSE csr_Legal_Emp_contact;
733
734 l_contact_person := rg_Legal_Emp_Contact .contact_person ;
735 l_phone := rg_Legal_Emp_Contact .phone ;
736 l_fax := rg_Legal_Emp_Contact .fax ;
737
738
739 /* Pick up the Address details belonging to Legal Employer */
740
741 OPEN csr_Legal_Emp_addr(l_legal_employer_id);
742 FETCH csr_Legal_Emp_addr INTO rg_Legal_Emp_addr;
743 CLOSE csr_Legal_Emp_addr;
744
745 l_address_line_1 := rg_Legal_Emp_addr.address_line_1 ;
746 l_address_line_2 := rg_Legal_Emp_addr.address_line_2 ;
747 l_address_line_3 := rg_Legal_Emp_addr.address_line_3 ;
748 l_postal_code := rg_Legal_Emp_addr.postal_code ;
752 l_subsidy_for_low_paid_bal := 'SUBSIDY_FOR_LOW_PAID_EMPLOYEES_PER_LE_MONTH';
749 l_town_or_city := rg_Legal_Emp_addr.town_or_city ;
750 l_country := rg_Legal_Emp_addr.country ;
751
753
757
754 OPEN csr_Get_Defined_Balance_Id( 'TAX_AT_SOURCE_LE_MONTH');
755 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
756 CLOSE csr_Get_Defined_Balance_Id;
758 l_ts_deduction := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
759
760 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX_LE_MONTH');
761 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
762 CLOSE csr_Get_Defined_Balance_Id;
763
764 l_wt_deduction := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
765
766
767 OPEN csr_Get_Defined_Balance_Id( 'VAT_LE_MONTH');
768 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
769 CLOSE csr_Get_Defined_Balance_Id;
770
771 l_vat_bal := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
772
773 OPEN csr_Get_Defined_Balance_Id( 'SALARIES_SUBJECT_TO_WITHHOLD_TAX_LE_MONTH');
774 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
775 CLOSE csr_Get_Defined_Balance_Id;
776
777 l_Sal_subject_Wt := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
778
779 OPEN csr_Get_Defined_Balance_Id( 'SALARIES_SUBJECT_TO_TAX_AT_SOURCE_LE_MONTH');
780 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
781 CLOSE csr_Get_Defined_Balance_Id;
782
783 l_Sal_subject_Ts := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
784
785 OPEN csr_Get_Defined_Balance_Id( 'PAYMENTS_SUBJECT_TO_WITHHOLD_TAX_LE_MONTH');
786 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
787 CLOSE csr_Get_Defined_Balance_Id;
788
789 l_Pay_subject_Wt := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
790
791 OPEN csr_Get_Defined_Balance_Id( 'PAYMENTS_SUBJECT_TO_TAX_AT_SOURCE_LE_MONTH');
792 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
793 CLOSE csr_Get_Defined_Balance_Id;
794
795 l_Pay_subject_ts := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
796
797
798 OPEN csr_Get_Defined_Balance_Id( 'SOCIAL_SECURITY_LE_MONTH');
799 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
800 CLOSE csr_Get_Defined_Balance_Id;
801
802 l_ss_fee := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
803
804 OPEN csr_Get_Defined_Balance_Id( 'EXEMPTED_SOCIAL_SECURITY_LE_MONTH');
805 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
806 CLOSE csr_Get_Defined_Balance_Id;
807
808 l_exem_ss_fee := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
809
810 OPEN csr_Get_Defined_Balance_Id( 'INSURANCE_SALARY_LE_MONTH');
811 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
812 CLOSE csr_Get_Defined_Balance_Id;
813
814 l_ins_ss_fee := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, l_legal_employer_id, NULL, NULL, NULL, l_reporting_date );
815
816 l_employer_ss_fee := nvl(l_ss_fee,0) + nvl(l_exem_ss_fee,0) + nvl(l_ins_ss_fee ,0) ;
817
818
819 END IF ;
820
821
822
823 /* Pick up the tax office details belonging to Employer*/
824
825 OPEN csr_Tax_Office_Details( l_tax_office_id );
826 FETCH csr_Tax_Office_Details INTO rg_Tax_Office_Details;
827 CLOSE csr_Tax_Office_Details;
828
829 l_tax_office_name := rg_Tax_Office_Details.name ;
830 l_tax_office_pay_meth_id := rg_Tax_Office_Details.ORG_INFORMATION2 ;
831 l_employer_pay_meth_id := rg_Tax_Office_Details.ORG_INFORMATION1 ;
832
833 hr_utility.set_location('Pick up the tax office details belonging to Employer',70);
834
835 OPEN csr_account_number( l_tax_office_pay_meth_id );
836 FETCH csr_account_number INTO rg_account_number;
837 CLOSE csr_account_number;
838
839 l_tax_office_ba := rg_account_number.segment3 ;
840
841 OPEN csr_account_number( l_employer_pay_meth_id);
842 FETCH csr_account_number INTO rg_account_number;
843 CLOSE csr_account_number;
844
845 l_employer_ba := rg_account_number.segment3 ;
846 hr_utility.set_location('Before populating pl/sql table',70);
847 IF l_vat IS NULL THEN
848 l_vat:=l_vat_bal;
849 END IF;
850
851 l_month_start_date := trunc(l_reporting_date,'MM');
852
853 for rec_prepaid_assignments in csr_prepaid_assignments_lu (
854 p_legal_employer_id => l_legal_employer_id,
855 p_local_unit_id => l_local_unit_id,
856 p_start_date => l_month_start_date,
857 p_end_date => l_reporting_date)
858 loop
859 if rec_prepaid_assignments.person_id <> l_person_id then
860 open csr_asg_effective_date (
861 p_asg_id => rec_prepaid_assignments.assignment_id,
862 p_end_date => l_reporting_date,
863 p_start_date => l_month_start_date,
867 fetch csr_asg_effective_date into l_termination_date;
864 p_business_group_id => l_business_group_id
865 );
866
868 close csr_asg_effective_date;
869
870 if l_termination_date < l_reporting_date then
871 l_ele_effective_date := l_termination_date;
872 else
873 l_ele_effective_date := l_reporting_date;
874 end if;
875 l_tax_card_type_code := null;
876
877 -- Get the tax card type
878 open get_element_details (rec_prepaid_assignments.assignment_id,l_ele_effective_date);
879 fetch get_element_details into l_tax_card_type_code;
880 close get_element_details;
881
885
882 open csr_get_defined_balance_id( l_subsidy_for_low_paid_bal);
883 fetch csr_get_defined_balance_id into lr_get_defined_balance_id;
884 close csr_get_defined_balance_id;
886 l_subsidy_for_low_paid_emp := pay_balance_pkg.get_value(p_defined_balance_id => lr_Get_Defined_Balance_Id.creator_id,
887 p_assignment_id => rec_prepaid_assignments.assignment_id,
888 p_virtual_date => l_ele_effective_date
889 );
890
891 IF l_tax_card_type_code = 'TS' then
892 l_subsidy_tax_at_source_dec := l_subsidy_tax_at_source_dec + l_subsidy_for_low_paid_emp;
893 else
894 l_subsidy_witholding_tax_ded := l_subsidy_witholding_tax_ded + l_subsidy_for_low_paid_emp;
895 end if;
896
897 end if;
898 l_person_id := rec_prepaid_assignments.person_id;
899 end loop;
900
901 pay_action_information_api.create_action_information (
902 p_action_information_id => l_action_info_id
903 ,p_action_context_id => p_payroll_action_id
904 ,p_action_context_type => 'PA'
905 ,p_object_version_number => l_ovn
906 ,p_effective_date => l_effective_date
907 ,p_source_id => NULL
908 ,p_source_text => NULL
909 ,p_action_information_category => 'EMEA REPORT INFORMATION'
910 ,p_action_information1 => 'PYFIMTRA'
911 ,p_action_information2 => l_emp_type
912 ,p_action_information3 => FND_NUMBER.NUMBER_TO_CANONICAL(l_subsidy_tax_at_source_dec) --l_emp_id
913 ,p_action_information4 => l_month||l_year
914 ,p_action_information5 => l_tax_payer_name
915 ,p_action_information6 => l_address_line_1
916 ,p_action_information7 => l_address_line_2
917 ,p_action_information8 => l_address_line_3
918 ,p_action_information9 => l_postal_code || ' ' ||l_town_or_city ||' '||l_country
919 ,p_action_information10 => l_business_id
920 ,p_action_information11 => FND_NUMBER.NUMBER_TO_CANONICAL(l_Sal_subject_Wt)
921 ,p_action_information12 => FND_NUMBER.NUMBER_TO_CANONICAL(l_Sal_subject_Ts)
922 ,p_action_information13 => FND_NUMBER.NUMBER_TO_CANONICAL(l_Pay_subject_Wt)
923 ,p_action_information14 => FND_NUMBER.NUMBER_TO_CANONICAL(l_Pay_subject_ts)
924 ,p_action_information15 => FND_NUMBER.NUMBER_TO_CANONICAL(l_wt_deduction)
925 ,p_action_information16 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_adjustment_wt,0))
926 ,p_action_information17 => FND_NUMBER.NUMBER_TO_CANONICAL(l_employer_ss_fee)
927 ,p_action_information18 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_adjustment_ss,0))
928 ,p_action_information19 => FND_NUMBER.NUMBER_TO_CANONICAL(l_ts_deduction)
929 ,p_action_information20 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_adjustment_ts,0))
930 ,p_action_information21 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_vat,0))
931 ,p_action_information22 => l_contact_person
932 ,p_action_information23 => l_phone
933 ,p_action_information24 => l_fax
934 ,p_action_information25 => l_tax_office_ba
935 ,p_action_information26 => l_tax_office_name
936 ,p_action_information27 => l_employer_ba
937 ,p_action_information28 => l_due_date2
938 ,p_action_information29 => l_ref_num
939 ,p_action_information30 => FND_NUMBER.NUMBER_TO_CANONICAL(l_subsidy_witholding_tax_ded) ); -- null );
940
941
942 pay_action_information_api.create_action_information (
943 p_action_information_id => l_action_info_id
947 ,p_effective_date => l_effective_date
944 ,p_action_context_id => p_payroll_action_id
945 ,p_action_context_type => 'PA'
946 ,p_object_version_number => l_ovn
948 ,p_source_id => NULL
949 ,p_source_text => NULL
950 ,p_action_information_category => 'EMEA REPORT DETAILS'
951 ,p_action_information1 => 'PYFIMTRA'
952 ,p_action_information2 => l_le_name
953 ,p_action_information3 => l_lu_name
954 ,p_action_information4 => l_month
955 ,p_action_information5 => l_year
956 ,p_action_information6 => null
957 ,p_action_information7 => null
958 ,p_action_information8 => null
959 ,p_action_information9 => null
960 ,p_action_information10 => null
961 ,p_action_information11 => null
962 ,p_action_information12 => null
963 ,p_action_information13 => null
964 ,p_action_information14 => null
965 ,p_action_information15 => null
966 ,p_action_information16 => null
967 ,p_action_information17 => null
968 ,p_action_information18 => null
969 ,p_action_information19 => null
970 ,p_action_information20 => null
971 ,p_action_information21 => null
972 ,p_action_information22 => null
973 ,p_action_information23 => null
974 ,p_action_information24 => null
975 ,p_action_information25 => null
976 ,p_action_information26 => null
977 ,p_action_information27 => null
978 ,p_action_information28 => null
979 ,p_action_information29 => null
980 ,p_action_information30 => null );
981
982
983 END IF;
984
985 END IF;
986
987 IF g_debug THEN
988 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
989 END IF;
990
991 EXCEPTION
992 WHEN OTHERS THEN
993 -- Return cursor that selects no rows
994 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
995
996 END RANGE_CODE;
997
998 /* ASSIGNMENT ACTION CODE */
999 PROCEDURE ASSIGNMENT_ACTION_CODE
1000 (p_payroll_action_id IN NUMBER
1001 ,p_start_person IN NUMBER
1002 ,p_end_person IN NUMBER
1003 ,p_chunk IN NUMBER)
1004 IS
1005 BEGIN
1006 IF g_debug THEN
1007 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
1008 END IF;
1009
1010 IF g_debug THEN
1011 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
1012 END IF;
1013
1014
1015 END ASSIGNMENT_ACTION_CODE;
1016
1017
1018 /* INITIALIZATION CODE */
1019 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
1020 IS
1021
1022 BEGIN
1023 IF g_debug THEN
1024 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
1025 END IF;
1026
1027
1028 IF g_debug THEN
1029 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
1030 END IF;
1031
1032 EXCEPTION WHEN OTHERS THEN
1033 g_err_num := SQLCODE;
1034 IF g_debug THEN
1035 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
1036 END IF;
1037 END INITIALIZATION_CODE;
1038
1039 /* ARCHIVE CODE */
1040 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
1041 ,p_effective_date IN DATE)
1042 IS
1043
1044 BEGIN
1045 IF g_debug THEN
1046 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',80);
1047 END IF;
1048
1049 IF g_debug THEN
1050 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',90);
1051 END IF;
1052
1053 END ARCHIVE_CODE;
1054 END PAY_FI_ARCHIVE_MTRA;