1 package body pay_za_mtc_pkg as
2 /* $Header: pyzamtc.pkb 120.6 2010/07/08 13:38:15 vijranga ship $ */
3
4 ------------------------------------------------------------------------------
5 -- NAME
6 -- update_certificate_number
7 -- PURPOSE
8 -- Issues manual Tax Certificate Numbers
9 -- ARGUMENTS
10 -- p_errmsg - Returned error message
11 -- p_errcode - Returned error code
12 -- p_bgid - The Business Group ID
13 -- p_payroll_id - The Payroll ID
14 -- p_tax_year - The Tax Year
15 -- p_pay_action_id - The Payroll Action ID of the Tax Certificate Preprocess
16 -- p_asg_id - The Assignment ID to process
17 -- p_asg_action_id - The Assignment Action ID to process
18 -- p_tax_cert_no - The Tax Certificate Number
19 -- NOTES
20 --
21 ------------------------------------------------------------------------------
22 procedure update_certificate_number
23 (
24 p_errmsg out nocopy varchar2,
25 p_errcode out nocopy varchar2,
26 p_bgid in number,
27 p_payroll_id in number,
28 p_tax_year in varchar2,
29 p_pay_action_id in varchar2,
30 p_asg_id in number,
31 p_asg_action_id in number,
32 p_tax_cert_no in varchar2
33 ) is
34
35 -- Cursor used to update Tax Certificate Numbers
36 cursor c_tax_cert_no is
37 select serial_number
38 from pay_assignment_actions
39 where assignment_action_id = p_asg_action_id;
40
41 -- Cursor used to find all the other Main Certificate Assignment Actions,
42 -- for the current Assignment in the same Tax Year
43 cursor other_ass_main is
44 select paa.serial_number, paa.assignment_action_id
45 from pay_assignment_actions paa,
46 pay_payroll_actions ppa
47 where ppa.business_group_id = p_bgid
48 and ppa.report_type = 'ZA_IRP5'
49 and ppa.action_type = 'X'
50 and substr(ppa.legislative_parameters, instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4)
51 = p_tax_year
52 and ppa.payroll_action_id <> substr(p_pay_action_id, 28, 9)
53 and paa.payroll_action_id = ppa.payroll_action_id
54 and paa.assignment_id = p_asg_id
55 and paa.action_sequence =
56 (
57 select max(paa2.action_sequence)
58 from pay_assignment_actions paa2
59 where paa2.payroll_action_id = ppa.payroll_action_id
60 and paa2.assignment_id = p_asg_id
61 );
62
63 -- Cursor used to find all the other Lump Sum Certificate Assignment Actions for the
64 -- current Assignment in the same Tax Year, and for a specific Time Period ID
65 cursor other_ass_ls(p_period varchar2) is
66 select paa.serial_number, paa.assignment_action_id
67 from pay_assignment_actions paa,
68 pay_payroll_actions ppa,
69 ff_database_items dbi,
70 ff_archive_items arc
71 where ppa.business_group_id = p_bgid
72 and ppa.report_type = 'ZA_IRP5'
73 and ppa.action_type = 'X'
74 and substr(ppa.legislative_parameters, instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4)
75 = p_tax_year
76 and ppa.payroll_action_id <> substr(p_pay_action_id, 28, 9)
77 and paa.payroll_action_id = ppa.payroll_action_id
78 and paa.assignment_id = p_asg_id
79 and dbi.user_name = 'A_PAY_PROC_PERIOD_ID'
80 and arc.user_entity_id = dbi.user_entity_id
81 and arc.context1 = to_char(paa.assignment_action_id)
82 and arc.value = p_period
83 and paa.action_sequence <>
84 (
85 select max(paa2.action_sequence)
86 from pay_assignment_actions paa2
87 where paa2.payroll_action_id = ppa.payroll_action_id
88 and paa2.assignment_id = p_asg_id
89 );
90
91 -- Variables
92 l_old_cert_no varchar2(30);
93 l_lump_sum_ind varchar2(1);
94 l_old_num pay_assignment_actions.serial_number%type;
95 l_old_aa pay_assignment_actions.assignment_action_id%type;
96 l_period varchar2(240);
97
98 begin
99
100 -- Fetch the Tax Certificate Number to be updated
101 open c_tax_cert_no;
102 fetch c_tax_cert_no into l_old_cert_no;
103
104 if c_tax_cert_no%notfound then
105
106 -- No data found
107 p_errmsg := 'No Data found';
108 p_errcode := 20001;
109
110 else
111
112 -- Check if Certificate Number is updateable
113 if substr(l_old_cert_no, 1, 1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '&') then
114
115 -- The Tax Certificate Number is not updateable
116 p_errmsg := 'A Electronically generated or Reissued Tax Certificate Number already exist';
117 p_errcode := 20001;
118
119 else
120
121 -- Find out whether there are any old Certificate Numbers,
122 -- that should be marked as reissued
123 -- Is this a Main or a Lump Sum Certificate
124 -- added for 6266019
125
126 Select decode(count(*), 0 ,'Y', 'N')
127 into l_lump_sum_ind
128 From pay_payroll_actions ppa_arch,
129 pay_assignment_actions paa_arch
130 where paa_arch.assignment_action_id = p_asg_action_id
131 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
132 and paa_arch.assignment_action_id =
133 (
134 select max(paa.assignment_action_id)
135 from pay_assignment_actions paa
136 where paa.payroll_action_id = ppa_arch.payroll_action_id
137 and paa.assignment_id = paa_arch.assignment_id
138 ) ;
139 /* commented for 6266019
140 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID', to_char(p_asg_action_id));
141 l_lump_sum_ind := nvl(pay_balance_pkg.run_db_item('ZA_LUMP_SUM_INDICATOR', null, 'ZA'), 'N');
142 */
143 -- Check whether this is a Main Certificate
144 if l_lump_sum_ind = 'N' then
145
146 -- Find out whether a previous Main Certificate exist
147 open other_ass_main;
148 loop
149
150 fetch other_ass_main into l_old_num, l_old_aa;
151 exit when other_ass_main%notfound;
152
153 if l_old_num is not null then
154
155 if substr(l_old_num, 1, 1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then
156
157 -- The Tax Certificate Number is not updateable
158 p_errmsg := 'A Electronically generated Tax Certificate Number already exist';
159 p_errcode := 20001;
160
161 -- Exit without writing the number
162 close other_ass_main;
163 close c_tax_cert_no;
164 return;
165
166 elsif ((substr(l_old_num, 1, 2) = '&&') and
167 (substr(l_old_num, 3, 1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0'))) then
168
169 -- The Tax Certificate Number is not updateable
170 p_errmsg := 'A Electronically generated Tax Certificate Number already exist';
171 p_errcode := 20001;
172
173 -- Exit without writing the number
174 close other_ass_main;
175 close c_tax_cert_no;
176 return;
177
178 else
179
180 if substr(l_old_num, 1, 2) <> '&&' then
181
182 -- Update the Assignment Action to reflect that this is an old number
183 update pay_assignment_actions
184 set serial_number = '&&' || l_old_num
185 where assignment_action_id = l_old_aa;
186
187 end if;
188
189 end if;
190
191 end if;
192
193 end loop;
194
195 close other_ass_main;
196
197 else -- This is a Lump Sum Certificate
198
199 -- Get the current Assignment Action's Time Period ID
200 select nvl(arc.value, '')
201 into l_period
202 from ff_database_items dbi,
203 ff_archive_items arc
204 where dbi.user_name = 'A_PAY_PROC_PERIOD_ID'
205 and arc.user_entity_id = dbi.user_entity_id
206 and arc.context1 = p_asg_action_id;
207
208 -- Find out whether a previous Lump Sum Certificate exist
209 open other_ass_ls(l_period);
210 loop
211
212 fetch other_ass_ls into l_old_num, l_old_aa;
213 exit when other_ass_ls%notfound;
214
215 if l_old_num is not null then
216
217 if substr(l_old_num, 1, 1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then
218
219 -- The Tax Certificate Number is not updateable
220 p_errmsg := 'A Electronically generated Tax Certificate Number already exist';
221 p_errcode := 20001;
222
223 -- Exit without writing the number
224 close other_ass_main;
225 close c_tax_cert_no;
226 return;
227
228 elsif ((substr(l_old_num, 1, 2) = '&&') and
229 (substr(l_old_num, 3, 1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0'))) then
230
231 -- The Tax Certificate Number is not updateable
232 p_errmsg := 'A Electronically generated Tax Certificate Number already exist';
233 p_errcode := 20001;
234
235 -- Exit without writing the number
236 close other_ass_main;
237 close c_tax_cert_no;
238 return;
239
240 else
241
242 if substr(l_old_num, 1, 2) <> '&&' then
243
244 -- Update the Assignment Action to reflect that this is an old number
245 update pay_assignment_actions
246 set serial_number = '&&' || l_old_num
247 where assignment_action_id = l_old_aa;
248
249 end if;
250
251 end if;
252
253 end if;
254
255 end loop;
256
257 close other_ass_ls;
258
259 end if;
260
261 -- The Tax Certificate Number is updateable
262 update pay_assignment_actions
263 set serial_number = p_tax_cert_no
264 where assignment_action_id = p_asg_action_id;
265
266 -- Commit the record
267 commit;
268 p_errmsg := 'Tax Certificate Number Issued';
269
270 end if;
271
272 end if;
273
274 close c_tax_cert_no;
275 exception
276 when others then
277 p_errmsg := null;
278 p_errcode := null;
279 end update_certificate_number;
280
281 Procedure upd_certificate_num_EOY2010
282 (
283 p_errmsg out nocopy varchar2,
284 p_errcode out nocopy varchar2,
285 p_bgid in number,
286 p_legal_entity_id in number,
287 p_tax_year in varchar2,
288 p_period_recon in varchar2, -- 9877034 fix
289 p_payroll in number,
290 p_pay_action_id in number,
291 p_asg_id in number,
292 p_temp_cert_no in varchar2,
293 p_tax_cert_no in varchar2
294 )
295 is
296
297 --Cursor to get the assignment action id
298 cursor csr_ass_act_id is
299 select assignment_action_id
300 from pay_assignment_actions
301 where payroll_action_id = p_pay_action_id
302 and assignment_id = p_asg_id;
303
304 --Cursor to get the certificate number
305 cursor csr_cert_details (ass_act_id pay_assignment_actions.assignment_action_id%type
306 ,p_cert_num varchar2) IS
307 select pai.action_information1 cert_num, -- Certificate Number
308 pai.action_information29 man_cert_num, -- Manual Certificate Number
309 pai.action_information28 cert_ind, -- O for old electronic, M for manual, OM for old manual
310 pai.action_information30 temp_cert_num, -- Temporary Certificate Number
311 pai.action_information_id act_inf_id,
312 pai.action_information18 directive1, -- Directive 1
313 pai.action_information19 directive2, -- Directive 2
314 pai.action_information20 directive3, -- Directive 3
315 pai2.action_information26 cert_type -- MAIN/LMPSM
316 from pay_action_information pai, -- For Employee info
317 pay_action_information pai2 -- For Employee contact info
318 where pai.action_context_id=ass_act_id
319 and pai.action_information30 = p_cert_num
320 and pai.action_information_category='ZATYE_EMPLOYEE_INFO'
321 and pai2.action_information_category='ZATYE_EMPLOYEE_CONTACT_INFO'
322 and pai2.action_context_id = pai.action_context_id
323 and pai2.action_information30 = pai.action_information30
324 and pai.action_context_type = 'AAP'
325 and pai.action_context_type = pai2.action_context_type;
326
327 -- Cursor used to find all the other main certificate details
328 -- for the current Assignment in the same Tax Year
329 -- with certificate type not ITREG
330 cursor csr_other_ass_actions(p_cert_type varchar2) is
331 select paa.assignment_action_id ass_act_id,
332 pai.action_information1 cert_num, --Certificate Number
333 pai.action_information29 man_cert_num, --Manual Certificate Number
334 pai.action_information28 cert_ind, --O - old electronic, M - Manual, OM - Old Manual
335 pai.action_information30 temp_cert_num, --Temporary certificate Number
336 pai.action_information18 directive1, -- Directive 1
337 pai.action_information19 directive2, -- Directive 2
338 pai.action_information20 directive3, -- Directive 3
339 pai2.action_information26 cert_type, --MAIN/LMPSM
340 pai.action_information_id act_inf_id,
341 pai2.action_information_id act_inf_id2
342 from pay_assignment_actions paa,
343 pay_payroll_actions ppa,
344 pay_action_information pai, --For Employee Info
345 pay_action_information pai2 --For Employee contact info
349 and substr(ppa.legislative_parameters, instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4)
346 where ppa.business_group_id = p_bgid
347 and ppa.report_type = 'ZA_TYE'
348 and ppa.action_type = 'X'
350 = p_tax_year
351 and NVL(substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'PERIOD_RECON')+13, 2), '02')
352 = NVL(p_period_recon,'02') -- 9877034 fix
353 and ppa.payroll_action_id <> p_pay_action_id
354 and paa.payroll_action_id = ppa.payroll_action_id
355 and paa.assignment_id = p_asg_id
356 and paa.assignment_id = pai.assignment_id
357 and paa.assignment_action_id = pai.action_context_id
358 and pai.action_information_category= 'ZATYE_EMPLOYEE_INFO'
359 and pai.action_context_id = pai2.action_context_id
360 and pai2.action_information_category='ZATYE_EMPLOYEE_CONTACT_INFO'
361 and pai2.action_information30 = pai.action_information30
362 and pai.action_context_type = 'AAP'
363 and pai.action_context_type = pai2.action_context_type
364 and pai.action_information2 not in ('ITREG','A')
365 and pai2.action_information26 = p_cert_type;
366
367 l_proc varchar2(100):='PAY_ZA_MTC_PKG.upd_certificate_num_EOY2010';
368 rec_cert_details csr_cert_details%rowtype;
369 l_ass_act_id pay_assignment_actions.assignment_action_id%type;
370 rec_other_ass_actions csr_other_ass_actions%rowtype;
371 begin
372 hr_utility.set_location('Entering '||l_proc,10);
373 hr_utility.set_location('p_bgid :'||p_bgid,10);
374 hr_utility.set_location('p_tax_year :'||p_tax_year,10);
375 hr_utility.set_location('p_legal_entity_id :'||p_legal_entity_id,10);
376 hr_utility.set_location('p_pay_action_id :'||p_pay_action_id,10);
377 hr_utility.set_location('p_asg_id :'||p_asg_id,10);
378 hr_utility.set_location('p_temp_cert_no :'||p_temp_cert_no,10);
379 hr_utility.set_location('p_tax_cert_no :'||p_tax_cert_no,10);
380
381 -- Get the assignment action for particular preprocess
382 open csr_ass_act_id;
383 fetch csr_ass_act_id into l_ass_act_id;
384 close csr_ass_act_id;
385
386 -- Retrieve the archived certificate numbers/indicators for the selected assignment action
387 hr_utility.set_location('Retrieving certificate details for this preprocess',12);
388 open csr_cert_details(l_ass_act_id,p_temp_cert_no);
389 fetch csr_cert_details into rec_cert_details;
390 close csr_cert_details;
391
392 -- Electronically generated certificate already exists
393 if rec_cert_details.cert_num is not null then
394 hr_utility.set_location('Selected preprocess has electronic certificate issued',14);
395 p_errmsg := 'A Electronically generated or Reissued Tax Certificate Number already exist.';
396 p_errcode := 20001;
397 return;
398 else
399 --The electronic certificate not generated for selected assignment action
400 --Check whether the selected assignment action is MAIN or directive numbers.
401 --If it is main then check whether any previous main certificate is
402 --issued electronically. If not then check whether any directive present
403 --in the selected MAIN has previous Lump sum certificate electronically issued.
404 if rec_cert_details.cert_type = 'MAIN' then
405 hr_utility.set_location('Directive Number selected is MAIN',16);
406 -- Check the previous MAIN certificates if issued electronically
407 open csr_other_ass_actions('MAIN');
408 loop
409 hr_utility.set_location('Loop through the previous MAIN certificates',18);
410 fetch csr_other_ass_actions into rec_other_ass_actions;
411 exit when csr_other_ass_actions%notfound;
412
413 --Certificate number will be generated only when the IRP5/
414 --IT3A is run. If Certificate number is not null suggests
415 --electronic certificate is already issued.
416 if rec_other_ass_actions.cert_num is not null then
417 hr_utility.set_location('Previous preprocess has electronic certificate issued',22);
418 p_errmsg := 'A Electronically generated or Reissued Tax Certificate Number already exist.';
419 p_errcode := 20001;
420 close csr_other_ass_actions;
421 return;
422 elsif rec_other_ass_actions.cert_ind='M' then
423 hr_utility.set_location('Previous preprocess has manual certificate issued',24);
424 update pay_action_information
425 set action_information28 ='OM'
426 where action_information_id=rec_other_ass_actions.act_inf_id;
427 end if;
428 end loop;
429 close csr_other_ass_actions;
430
431 -- Previously issued electronic main certificate does not exist.
432 -- check whether the lump sum directive in the main certificate have
433 -- previously issued electronic lump sum certificate.
434 if rec_cert_details.directive1 is not null OR rec_cert_details.directive2 is not null
435 OR rec_cert_details.directive3 is not null
436 then
437 hr_utility.set_location('Check the previous Lump sums',30);
441 fetch csr_other_ass_actions into rec_other_ass_actions;
438 open csr_other_ass_actions('LMPSM');
439 loop
440 hr_utility.set_location('Loop through the previous LMPSM certificates',32);
442 exit when csr_other_ass_actions%notfound;
443
444 if ((rec_cert_details.directive1 = rec_other_ass_actions.directive1)
445 OR
446 (rec_cert_details.directive2 = rec_other_ass_actions.directive1)
447 OR
448 (rec_cert_details.directive3 = rec_other_ass_actions.directive1))
449 AND rec_other_ass_actions.cert_num is not null then
450 hr_utility.set_location('Previous preprocess has electronic certificate issued',36);
451 p_errmsg := 'A Electronically generated or Reissued Tax Certificate Number already exist.';
452 p_errcode := 20001;
453 close csr_other_ass_actions;
454 return;
455 elsif ((rec_cert_details.directive1 = rec_other_ass_actions.directive1)
456 OR
457 (rec_cert_details.directive2 = rec_other_ass_actions.directive1)
458 OR
459 (rec_cert_details.directive3 = rec_other_ass_actions.directive1))
460 AND rec_other_ass_actions.cert_num is null
461 AND rec_other_ass_actions.cert_ind='M' then
462 hr_utility.set_location('Previous preprocess has manual certificate issued',40);
463 update pay_action_information
464 set action_information28 ='OM'
465 where action_information_id=rec_other_ass_actions.act_inf_id;
466 end if;
467 end loop;
468 close csr_other_ass_actions;
469 end if;
470
471 --Certificate is not main, hence manual issue requested for lump
472 --sum certificate. For Lump Sum Certificates, first check whether any
473 --previous lump sum certificate issued for same directive.
474 --If not, then check the directives in the issued main certificates, if it
475 --matches with the lump sum directive
476 else
477 hr_utility.set_location('Directive Number selected is LMPSM',42);
478 open csr_other_ass_actions('LMPSM');
479 loop
480 fetch csr_other_ass_actions into rec_other_ass_actions;
481 exit when csr_other_ass_actions%notfound;
482
483 --Certificate number will be generated only when the IRP5/
484 --IT3A is run. If Certificate number is not null suggests
485 --electronic certificate is already issued.
486 if rec_other_ass_actions.directive1 = rec_cert_details.directive1
487 AND rec_other_ass_actions.cert_num is not null then
488 hr_utility.set_location('Previous preprocess has electronic certificate issued.',44);
489 p_errmsg := 'A Electronically generated or Reissued Tax Certificate Number already exist.';
490 p_errcode := 20001;
491 close csr_other_ass_actions;
492 return;
493 elsif rec_other_ass_actions.directive1 = rec_cert_details.directive1
494 AND rec_other_ass_actions.cert_ind='M' then
495 hr_utility.set_location('Previous preprocess has manual certificate issued.',46);
496 update pay_action_information
497 set action_information28 ='OM'
498 where action_information_id=rec_other_ass_actions.act_inf_id;
499 end if;
500 end loop;
501 close csr_other_ass_actions;
502
503 -- Previously issued electronic lump sum certificate does not exist.
504 -- check whether previously issued main certificate has
505 -- this directive.
506 open csr_other_ass_actions('MAIN');
507 loop
508 fetch csr_other_ass_actions into rec_other_ass_actions;
509 exit when csr_other_ass_actions%notfound;
510
511 --Certificate number will be generated only when the IRP5/
512 --IT3A is run. If Certificate number is not null suggests
513 --electronic certificate is already issued.
514 if ((rec_other_ass_actions.directive1 = rec_cert_details.directive1)
515 OR
516 (rec_other_ass_actions.directive2 = rec_cert_details.directive1)
517 OR
518 (rec_other_ass_actions.directive3 = rec_cert_details.directive1))
519 AND rec_other_ass_actions.cert_num is not null then
520 hr_utility.set_location('Previous preprocess has electronic certificate issued',48);
521 p_errmsg := 'A Electronically generated or Reissued Tax Certificate Number already exist.';
522 p_errcode := 20001;
523 close csr_other_ass_actions;
524 return;
525 end if;
526 --Did not place the else condition to update the main certificate
527 --manually issued earlier to OM, because in main certificate, there are normal
528 --incomes too which are not included in lump sum certificate. Hence manually
529 --issued main certificate will not be updated to OM in this case.
530 end loop;
531 close csr_other_ass_actions;
532 end if;
533 end if; -- End rec_cert_details.cert_num is not null
534
535 --Update the Certificate Indicator and manual certificate number
536 hr_utility.set_location('Update with manual certificate details',60);
537 update pay_action_information
538 set action_information28='M', action_information29=p_tax_cert_no
539 where action_information_id=rec_cert_details.act_inf_id;
540
541 -- Commit the record
542 hr_utility.set_location('Committing the record',70);
543 commit;
544 p_errmsg := 'Tax Certificate Number Issued.';
545
546 exception
547 when others then
548 p_errmsg := null;
549 p_errcode := null;
550 end upd_certificate_num_EOY2010;
551
552 end pay_za_mtc_pkg;
[Home] [Help]