1 package body pay_za_mtc_pkg as
2 /* $Header: pyzamtc.pkb 120.2.12000000.3 2007/07/21 01:19:09 rpahune noship $ */
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 end pay_za_mtc_pkg;
[Home] [Help]