[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_FPS_NI_AND_OTHERS_13
Source
1 PACKAGE body PAY_GB_FPS_NI_AND_OTHERS_13 AS
2 /* $Header: pygbrtifpsni13.pkb 120.0.12020000.12 2013/03/29 13:41:41 ssarap noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2012 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_FPS_NI_AND_OTHERS_13
10 Purpose:
11 It fetches the live data and for NI Balances for FPS RTI Processes 2013-14.
12 This is a UK Specific payroll package.
13 History:
14 19-Dec-2012 rajganga 115.0 15890022 Created.
15 10-Jan-2013 rajganga 115.1 15890022 Created.
16 21-Feb-2013 pbalu 115.2 16268879 Fine Tuned the Master Insert, as it creates problem with certain BACS run.
17 21-Feb-2013 pbalu 115.3 16268879 GSCC errors.
18 04-Mar-2013 sampmand 115.4 16386622 NI adjustment logic modified to check for ER run level values.
19 11-Mar-2013 rajganga 115.6 16402171 Added fnd_canonical date conversion.
20 15-Mar-2013 ssarap 115.7 16496657 Changed the data type of number(15) to number(15,2).
21 19-Mar-2013 pbalu 115.8 16510056 Random digit fetching for BACS modified
22 26-Mar-2013 ssarap 115.10 16306737 Added a third party prepayment check for pay_pre_payments tables
23 in function FPS_BACS_PREPROCESS.
24 29-Mar-2013 ssarap 115.11 16555308 Added the assignment id OUT
25 param to fetch_HASH_FPS_PER. Fetch the assignment id of largest bacs.
26 =============================================================================*/
27 --
28 --
29 -- Declare variables to hold NI Defined balance id's
30 g_nia_able_id pay_defined_balances.defined_balance_id%TYPE;
31 g_nia_able_id_run pay_defined_balances.defined_balance_id%TYPE;
32 g_nia_tot_id pay_defined_balances.defined_balance_id%TYPE;
33 g_nia_tot_id_run pay_defined_balances.defined_balance_id%TYPE;
34 g_nib_tot_id_run pay_defined_balances.defined_balance_id%TYPE;
35 g_nic_tot_id_run pay_defined_balances.defined_balance_id%TYPE;
36 g_nid_tot_id_run pay_defined_balances.defined_balance_id%TYPE;
37 g_nie_tot_id_run pay_defined_balances.defined_balance_id%TYPE;
38 g_nij_tot_id_run pay_defined_balances.defined_balance_id%TYPE;
39 g_nil_tot_id_run pay_defined_balances.defined_balance_id%TYPE;
40 g_nia_lel_id pay_defined_balances.defined_balance_id%TYPE;
41 g_nia_uel_id pay_defined_balances.defined_balance_id%TYPE;
42 g_nia_uap_id pay_defined_balances.defined_balance_id%TYPE; -- 8357870
43 g_nia_auel_id pay_defined_balances.defined_balance_id%TYPE; --EOY 07/08
44 g_nia_et_id pay_defined_balances.defined_balance_id%TYPE;
45 g_nib_able_id pay_defined_balances.defined_balance_id%TYPE;
46 g_nib_able_id_run pay_defined_balances.defined_balance_id%TYPE;
47 g_nib_tot_id pay_defined_balances.defined_balance_id%TYPE;
48 g_nib_lel_id pay_defined_balances.defined_balance_id%TYPE;
49 g_nib_uel_id pay_defined_balances.defined_balance_id%TYPE;
50 g_nib_uap_id pay_defined_balances.defined_balance_id%TYPE; -- 8357870
51 g_nib_auel_id pay_defined_balances.defined_balance_id%TYPE; --EOY 07/08
52 g_nib_et_id pay_defined_balances.defined_balance_id%TYPE;
53 g_nic_able_id pay_defined_balances.defined_balance_id%TYPE;
54 g_nic_able_id_run pay_defined_balances.defined_balance_id%TYPE;
55 g_nic_tot_id pay_defined_balances.defined_balance_id%TYPE;
56 g_nic_lel_id pay_defined_balances.defined_balance_id%TYPE;
57 g_nic_uel_id pay_defined_balances.defined_balance_id%TYPE;
58 g_nic_uap_id pay_defined_balances.defined_balance_id%TYPE; -- 8357870
59 g_nic_auel_id pay_defined_balances.defined_balance_id%TYPE; --EOY 07/08
60 g_nic_et_id pay_defined_balances.defined_balance_id%TYPE;
61 g_nic_ers_rebate_id pay_defined_balances.defined_balance_id%TYPE;
62 g_nid_able_id pay_defined_balances.defined_balance_id%TYPE;
63 g_nid_able_id_run pay_defined_balances.defined_balance_id%TYPE;
64 g_nid_tot_id pay_defined_balances.defined_balance_id%TYPE;
65 g_nid_lel_id pay_defined_balances.defined_balance_id%TYPE;
66 g_nid_uel_id pay_defined_balances.defined_balance_id%TYPE;
67 g_nid_uap_id pay_defined_balances.defined_balance_id%TYPE; -- 8357870
68 g_nid_auel_id pay_defined_balances.defined_balance_id%TYPE; --EOY 07/08
69 g_nid_et_id pay_defined_balances.defined_balance_id%TYPE;
70 g_nid_ers_rebate_id pay_defined_balances.defined_balance_id%TYPE;
71 g_nid_ees_rebate_id pay_defined_balances.defined_balance_id%TYPE;
72 g_nid_rebate_emp_id pay_defined_balances.defined_balance_id%TYPE;
73 g_nie_able_id pay_defined_balances.defined_balance_id%TYPE;
74 g_nie_able_id_run pay_defined_balances.defined_balance_id%TYPE;
75 g_nie_tot_id pay_defined_balances.defined_balance_id%TYPE;
76 g_nie_lel_id pay_defined_balances.defined_balance_id%TYPE;
77 g_nie_uel_id pay_defined_balances.defined_balance_id%TYPE;
78 g_nie_uap_id pay_defined_balances.defined_balance_id%TYPE; -- 8357870
79 g_nie_auel_id pay_defined_balances.defined_balance_id%TYPE; --EOY 07/08
80 g_nie_et_id pay_defined_balances.defined_balance_id%TYPE;
81 g_nie_ers_rebate_id pay_defined_balances.defined_balance_id%TYPE;
82 g_nig_tot_id pay_defined_balances.defined_balance_id%TYPE;
83 g_nij_able_id pay_defined_balances.defined_balance_id%TYPE;
84 g_nij_able_id_run pay_defined_balances.defined_balance_id%TYPE;
85 g_nij_tot_id pay_defined_balances.defined_balance_id%TYPE;
86 g_nij_lel_id pay_defined_balances.defined_balance_id%TYPE;
87 g_nij_uel_id pay_defined_balances.defined_balance_id%TYPE;
88 g_nij_uap_id pay_defined_balances.defined_balance_id%TYPE; -- 8357870
89 g_nij_auel_id pay_defined_balances.defined_balance_id%TYPE; --EOY 07/08
90 g_nij_et_id pay_defined_balances.defined_balance_id%TYPE;
91 g_nil_able_id pay_defined_balances.defined_balance_id%TYPE;
92 g_nil_able_id_run pay_defined_balances.defined_balance_id%TYPE;
93 g_nil_tot_id pay_defined_balances.defined_balance_id%TYPE;
94 g_nil_lel_id pay_defined_balances.defined_balance_id%TYPE;
95 g_nil_uel_id pay_defined_balances.defined_balance_id%TYPE;
96 g_nil_uap_id pay_defined_balances.defined_balance_id%TYPE; -- 8357870
97 g_nil_auel_id pay_defined_balances.defined_balance_id%TYPE; --EOY 07/08
98 g_nil_et_id pay_defined_balances.defined_balance_id%TYPE;
99 g_nia_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
100 g_nia_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
101 g_nia_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
102 g_nia_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
103 g_nib_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
104 g_nib_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
105 g_nib_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
106 g_nib_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
107 g_nic_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
108 g_nic_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
109 g_nic_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
110 g_nic_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
111 g_nid_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
112 g_nid_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
113 g_nid_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
114 g_nid_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
115 g_nie_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
116 g_nie_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
117 g_nie_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
118 g_nie_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
119 g_nij_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
120 g_nij_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
121 g_nij_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
122 g_nij_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
123 g_nil_e_ytd_id pay_defined_balances.defined_balance_id%TYPE;
124 g_nil_e_ptd_id pay_defined_balances.defined_balance_id%TYPE;
125 g_nil_r_ytd_id pay_defined_balances.defined_balance_id%TYPE;
126 g_nil_r_ptd_id pay_defined_balances.defined_balance_id%TYPE;
127 PROCEDURE load_defined_balances
128 IS
129 CURSOR get_defined_balance_id (p_balance_name VARCHAR2, p_dimension_name VARCHAR2)
130 IS
131 SELECT defined_balance_id
132 FROM pay_defined_balances db,
133 pay_balance_types b,
134 pay_balance_dimensions d
135 WHERE b.balance_name = p_balance_name
136 AND d.dimension_name = p_dimension_name
137 AND db.balance_type_id = b.balance_type_id
138 AND db.balance_dimension_id = d.balance_dimension_id;
139 BEGIN
140 OPEN get_defined_balance_id('NI A Able','_ASG_TD_YTD');
141 FETCH get_defined_balance_id INTO g_nia_able_id;
142 CLOSE get_defined_balance_id;
143 --
144 OPEN get_defined_balance_id('NI A Able','_ASG_RUN');
145 FETCH get_defined_balance_id INTO g_nia_able_id_run;
146 CLOSE get_defined_balance_id;
147 --
148 OPEN get_defined_balance_id('NI A Employee','_ASG_TD_YTD');
149 FETCH get_defined_balance_id INTO g_nia_e_ytd_id;
150 CLOSE get_defined_balance_id;
151 --
152 OPEN get_defined_balance_id('NI A Employee','_ASG_RUN');
153 FETCH get_defined_balance_id INTO g_nia_e_ptd_id;
154 CLOSE get_defined_balance_id;
155 --
156 OPEN get_defined_balance_id('NI A Employer','_ASG_TD_YTD');
157 FETCH get_defined_balance_id INTO g_nia_r_ytd_id;
158 CLOSE get_defined_balance_id;
159 --
160 OPEN get_defined_balance_id('NI A Employer','_ASG_RUN');
161 FETCH get_defined_balance_id INTO g_nia_r_ptd_id;
162 CLOSE get_defined_balance_id;
163 --
164 OPEN get_defined_balance_id('NI A Total','_ASG_TD_YTD');
165 FETCH get_defined_balance_id INTO g_nia_tot_id;
166 CLOSE get_defined_balance_id;
167 --
168 OPEN get_defined_balance_id('NI A Total','_ASG_RUN');
169 FETCH get_defined_balance_id INTO g_nia_tot_id_run;
170 CLOSE get_defined_balance_id;
171 --
172 OPEN get_defined_balance_id('NI A Able LEL','_ASG_TD_YTD');
173 FETCH get_defined_balance_id INTO g_nia_lel_id;
174 CLOSE get_defined_balance_id;
175 --
176 OPEN get_defined_balance_id('NI A Able UEL','_ASG_TD_YTD');
177 FETCH get_defined_balance_id INTO g_nia_uel_id;
178 CLOSE get_defined_balance_id;
179 -- 8357870 begin
180 OPEN get_defined_balance_id('NI A Able UAP','_ASG_TD_YTD');
181 FETCH get_defined_balance_id INTO g_nia_uap_id;
182 CLOSE get_defined_balance_id;
183 -- 8357870 end
184 --EOY 07/08 begin
185 OPEN get_defined_balance_id('NI A Able AUEL','_ASG_TD_YTD');
186 FETCH get_defined_balance_id INTO g_nia_auel_id;
187 CLOSE get_defined_balance_id;
188 --EOY 07/08 end
189 OPEN get_defined_balance_id('NI A Able ET','_ASG_TD_YTD');
190 FETCH get_defined_balance_id INTO g_nia_et_id;
191 CLOSE get_defined_balance_id;
192 --
193 OPEN get_defined_balance_id('NI B Able','_ASG_TD_YTD');
194 FETCH get_defined_balance_id INTO g_nib_able_id;
195 CLOSE get_defined_balance_id;
196 --
197 --
198 OPEN get_defined_balance_id('NI B Able','_ASG_RUN');
199 FETCH get_defined_balance_id INTO g_nib_able_id_run;
200 CLOSE get_defined_balance_id;
201 --
202 OPEN get_defined_balance_id('NI B Employee','_ASG_TD_YTD');
203 FETCH get_defined_balance_id INTO g_nib_e_ytd_id;
204 CLOSE get_defined_balance_id;
205 --
206 OPEN get_defined_balance_id('NI B Employee','_ASG_RUN');
207 FETCH get_defined_balance_id INTO g_nib_e_ptd_id;
208 CLOSE get_defined_balance_id;
209 --
210 OPEN get_defined_balance_id('NI B Employer','_ASG_TD_YTD');
211 FETCH get_defined_balance_id INTO g_nib_r_ytd_id;
212 CLOSE get_defined_balance_id;
213 --
214 OPEN get_defined_balance_id('NI B Employer','_ASG_RUN');
215 FETCH get_defined_balance_id INTO g_nib_r_ptd_id;
216 CLOSE get_defined_balance_id;
217 --
218 --
219 OPEN get_defined_balance_id('NI B Total','_ASG_TD_YTD');
220 FETCH get_defined_balance_id INTO g_nib_tot_id;
221 CLOSE get_defined_balance_id;
222 --
223 OPEN get_defined_balance_id('NI B Total','_ASG_RUN');
224 FETCH get_defined_balance_id INTO g_nib_tot_id_run;
225 CLOSE get_defined_balance_id;
226 --
227 OPEN get_defined_balance_id('NI B Able LEL','_ASG_TD_YTD');
228 FETCH get_defined_balance_id INTO g_nib_lel_id;
229 CLOSE get_defined_balance_id;
230 --
231 OPEN get_defined_balance_id('NI B Able UEL','_ASG_TD_YTD');
232 FETCH get_defined_balance_id INTO g_nib_uel_id;
233 CLOSE get_defined_balance_id;
234 -- 8357870 begin
235 OPEN get_defined_balance_id('NI B Able UAP','_ASG_TD_YTD');
236 FETCH get_defined_balance_id INTO g_nib_uap_id;
237 CLOSE get_defined_balance_id;
238 -- 8357870 end
239 --EOY 07/08 begin
240 OPEN get_defined_balance_id('NI B Able AUEL','_ASG_TD_YTD');
241 FETCH get_defined_balance_id INTO g_nib_auel_id;
242 CLOSE get_defined_balance_id;
243 --EOY 07/08 end
244 OPEN get_defined_balance_id('NI B Able ET','_ASG_TD_YTD');
245 FETCH get_defined_balance_id INTO g_nib_et_id;
246 CLOSE get_defined_balance_id;
247 --
248 OPEN get_defined_balance_id('NI C Able LEL','_ASG_TD_YTD');
249 FETCH get_defined_balance_id INTO g_nic_lel_id;
250 CLOSE get_defined_balance_id;
251 --
252 OPEN get_defined_balance_id('NI C Able UEL','_ASG_TD_YTD');
253 FETCH get_defined_balance_id INTO g_nic_uel_id;
254 CLOSE get_defined_balance_id;
255 -- 8357870 begin
256 OPEN get_defined_balance_id('NI C Able UAP','_ASG_TD_YTD');
257 FETCH get_defined_balance_id INTO g_nic_uap_id;
258 CLOSE get_defined_balance_id;
259 -- 8357870 end
260 --EOY 07/08 Begin
261 OPEN get_defined_balance_id('NI C Able AUEL','_ASG_TD_YTD');
262 FETCH get_defined_balance_id INTO g_nic_auel_id;
263 CLOSE get_defined_balance_id;
264 --EOY 07/08 End
265 OPEN get_defined_balance_id('NI C Able ET','_ASG_TD_YTD');
266 FETCH get_defined_balance_id INTO g_nic_et_id;
267 CLOSE get_defined_balance_id;
268 --
269 OPEN get_defined_balance_id('NI C Able','_ASG_TD_YTD');
270 FETCH get_defined_balance_id INTO g_nic_able_id;
271 CLOSE get_defined_balance_id;
272 --
273 --
274 OPEN get_defined_balance_id('NI C Able','_ASG_RUN');
275 FETCH get_defined_balance_id INTO g_nic_able_id_run;
276 CLOSE get_defined_balance_id;
277 --
278
279 OPEN get_defined_balance_id('NI C Employee','_ASG_TD_YTD');
280 FETCH get_defined_balance_id INTO g_nic_e_ytd_id;
281 CLOSE get_defined_balance_id;
282 --
283 OPEN get_defined_balance_id('NI C Employee','_ASG_RUN');
284 FETCH get_defined_balance_id INTO g_nic_e_ptd_id;
285 CLOSE get_defined_balance_id;
286 --
287 OPEN get_defined_balance_id('NI C Employer','_ASG_TD_YTD');
288 FETCH get_defined_balance_id INTO g_nic_r_ytd_id;
289 CLOSE get_defined_balance_id;
290 --
291 OPEN get_defined_balance_id('NI C Employer','_ASG_RUN');
292 FETCH get_defined_balance_id INTO g_nic_r_ptd_id;
293 CLOSE get_defined_balance_id;
294 --
295 OPEN get_defined_balance_id('NI C Total','_ASG_TD_YTD');
296 FETCH get_defined_balance_id INTO g_nic_tot_id;
297 CLOSE get_defined_balance_id;
298
299 OPEN get_defined_balance_id('NI C Total','_ASG_RUN');
300 FETCH get_defined_balance_id INTO g_nic_tot_id_run;
301 CLOSE get_defined_balance_id;
302 --
303 -- Fix for Bug 1976152, added the below stmt to fetch the balance id
304 -- for the balance NI C Employers Rebate
305 OPEN get_defined_balance_id('NI C Ers Rebate','_ASG_TD_YTD');
306 FETCH get_defined_balance_id INTO g_nic_ers_rebate_id;
307 CLOSE get_defined_balance_id;
308 --
309 OPEN get_defined_balance_id('NI D Able','_ASG_TD_YTD');
310 FETCH get_defined_balance_id INTO g_nid_able_id;
311 CLOSE get_defined_balance_id;
312 --
313 OPEN get_defined_balance_id('NI D Able','_ASG_RUN');
314 FETCH get_defined_balance_id INTO g_nid_able_id_run;
315 CLOSE get_defined_balance_id;
316 --
317
318 OPEN get_defined_balance_id('NI D Employee','_ASG_TD_YTD');
319 FETCH get_defined_balance_id INTO g_nid_e_ytd_id;
320 CLOSE get_defined_balance_id;
321 --
322 OPEN get_defined_balance_id('NI D Employee','_ASG_RUN');
323 FETCH get_defined_balance_id INTO g_nid_e_ptd_id;
324 CLOSE get_defined_balance_id;
325 --
326 OPEN get_defined_balance_id('NI D Employer','_ASG_TD_YTD');
327 FETCH get_defined_balance_id INTO g_nid_r_ytd_id;
328 CLOSE get_defined_balance_id;
329 --
330 OPEN get_defined_balance_id('NI D Employer','_ASG_RUN');
331 FETCH get_defined_balance_id INTO g_nid_r_ptd_id;
332 CLOSE get_defined_balance_id;
333 --
334 OPEN get_defined_balance_id('NI D Total','_ASG_TD_YTD');
335 FETCH get_defined_balance_id INTO g_nid_tot_id;
336 CLOSE get_defined_balance_id;
337 --
338 OPEN get_defined_balance_id('NI D Total','_ASG_RUN');
339 FETCH get_defined_balance_id INTO g_nid_tot_id_run;
340 CLOSE get_defined_balance_id;
341 --
342 OPEN get_defined_balance_id('NI D Able LEL','_ASG_TD_YTD');
343 FETCH get_defined_balance_id INTO g_nid_lel_id;
344 CLOSE get_defined_balance_id;
345 --
346 OPEN get_defined_balance_id('NI D Able UEL','_ASG_TD_YTD');
347 FETCH get_defined_balance_id INTO g_nid_uel_id;
348 CLOSE get_defined_balance_id;
349 -- 8357870 begin
350 OPEN get_defined_balance_id('NI D Able UAP','_ASG_TD_YTD');
351 FETCH get_defined_balance_id INTO g_nid_uap_id;
352 CLOSE get_defined_balance_id;
353 -- 8357870 end
354 --EOY 07/08 Begin
355 OPEN get_defined_balance_id('NI D Able AUEL','_ASG_TD_YTD');
356 FETCH get_defined_balance_id INTO g_nid_auel_id;
357 CLOSE get_defined_balance_id;
358 --EOY 07/08 End
359 OPEN get_defined_balance_id('NI D Able ET','_ASG_TD_YTD');
360 FETCH get_defined_balance_id INTO g_nid_et_id;
361 CLOSE get_defined_balance_id;
362 --
363 OPEN get_defined_balance_id('NI D Ers Rebate','_ASG_TD_YTD');
364 FETCH get_defined_balance_id INTO g_nid_ers_rebate_id;
365 CLOSE get_defined_balance_id;
366 --
367 OPEN get_defined_balance_id('NI D Ees Rebate','_ASG_TD_YTD');
368 FETCH get_defined_balance_id INTO g_nid_ees_rebate_id;
369 CLOSE get_defined_balance_id;
370 --
371 OPEN get_defined_balance_id('NI D Rebate to Employee','_ASG_TD_YTD');
372 FETCH get_defined_balance_id INTO g_nid_rebate_emp_id;
373 CLOSE get_defined_balance_id;
374 --
375 OPEN get_defined_balance_id('NI E Able','_ASG_TD_YTD');
376 FETCH get_defined_balance_id INTO g_nie_able_id;
377 CLOSE get_defined_balance_id;
378 --
379 OPEN get_defined_balance_id('NI E Able','_ASG_RUN');
380 FETCH get_defined_balance_id INTO g_nie_able_id_run;
381 CLOSE get_defined_balance_id;
382 --
383
384 OPEN get_defined_balance_id('NI E Employee','_ASG_TD_YTD');
385 FETCH get_defined_balance_id INTO g_nie_e_ytd_id;
386 CLOSE get_defined_balance_id;
387 --
388 OPEN get_defined_balance_id('NI E Employee','_ASG_RUN');
389 FETCH get_defined_balance_id INTO g_nie_e_ptd_id;
390 CLOSE get_defined_balance_id;
391 --
392 OPEN get_defined_balance_id('NI E Employer','_ASG_TD_YTD');
393 FETCH get_defined_balance_id INTO g_nie_r_ytd_id;
394 CLOSE get_defined_balance_id;
395 --
396 OPEN get_defined_balance_id('NI E Employer','_ASG_RUN');
397 FETCH get_defined_balance_id INTO g_nie_r_ptd_id;
398 CLOSE get_defined_balance_id;
399 OPEN get_defined_balance_id('NI E Total','_ASG_TD_YTD');
400 FETCH get_defined_balance_id INTO g_nie_tot_id;
401 CLOSE get_defined_balance_id;
402 --
403 OPEN get_defined_balance_id('NI E Total','_ASG_RUN');
404 FETCH get_defined_balance_id INTO g_nie_tot_id_run;
405 CLOSE get_defined_balance_id;
406 --
407 OPEN get_defined_balance_id('NI E Able LEL','_ASG_TD_YTD');
408 FETCH get_defined_balance_id INTO g_nie_lel_id;
409 CLOSE get_defined_balance_id;
410 --
411 OPEN get_defined_balance_id('NI E Able UEL','_ASG_TD_YTD');
412 FETCH get_defined_balance_id INTO g_nie_uel_id;
413 CLOSE get_defined_balance_id;
414 -- 8357870 begin
415 OPEN get_defined_balance_id('NI E Able UAP','_ASG_TD_YTD');
416 FETCH get_defined_balance_id INTO g_nie_uap_id;
417 CLOSE get_defined_balance_id;
418 -- 8357870 end
419 --EOY 07/08 Begin
420 OPEN get_defined_balance_id('NI E Able AUEL','_ASG_TD_YTD');
421 FETCH get_defined_balance_id INTO g_nie_auel_id;
422 CLOSE get_defined_balance_id;
423 --EOY 07/08 End
424 OPEN get_defined_balance_id('NI E Able ET','_ASG_TD_YTD');
425 FETCH get_defined_balance_id INTO g_nie_et_id;
426 CLOSE get_defined_balance_id;
427 OPEN get_defined_balance_id('NI J Able','_ASG_TD_YTD');
428 FETCH get_defined_balance_id INTO g_nij_able_id;
429 CLOSE get_defined_balance_id;
430 --
431 OPEN get_defined_balance_id('NI J Able','_ASG_RUN');
432 FETCH get_defined_balance_id INTO g_nij_able_id_run;
433 CLOSE get_defined_balance_id;
434 --
435 OPEN get_defined_balance_id('NI J Employee','_ASG_TD_YTD');
436 FETCH get_defined_balance_id INTO g_nij_e_ytd_id;
437 CLOSE get_defined_balance_id;
438 --
439 OPEN get_defined_balance_id('NI J Employee','_ASG_RUN');
440 FETCH get_defined_balance_id INTO g_nij_e_ptd_id;
441 CLOSE get_defined_balance_id;
442 --
443 OPEN get_defined_balance_id('NI J Employer','_ASG_TD_YTD');
444 FETCH get_defined_balance_id INTO g_nij_r_ytd_id;
445 CLOSE get_defined_balance_id;
446 --
447 OPEN get_defined_balance_id('NI J Employer','_ASG_RUN');
448 FETCH get_defined_balance_id INTO g_nij_r_ptd_id;
449 CLOSE get_defined_balance_id;
450 --
451 OPEN get_defined_balance_id('NI J Total','_ASG_TD_YTD');
452 FETCH get_defined_balance_id INTO g_nij_tot_id;
453 CLOSE get_defined_balance_id;
454 --
455 OPEN get_defined_balance_id('NI J Total','_ASG_RUN');
456 FETCH get_defined_balance_id INTO g_nij_tot_id_run;
457 CLOSE get_defined_balance_id;
458 --
459 OPEN get_defined_balance_id('NI J Able LEL','_ASG_TD_YTD');
460 FETCH get_defined_balance_id INTO g_nij_lel_id;
461 CLOSE get_defined_balance_id;
462 --
463 OPEN get_defined_balance_id('NI J Able UEL','_ASG_TD_YTD');
464 FETCH get_defined_balance_id INTO g_nij_uel_id;
465 CLOSE get_defined_balance_id;
466 -- 8357870 begin
467 OPEN get_defined_balance_id('NI J Able UAP','_ASG_TD_YTD');
468 FETCH get_defined_balance_id INTO g_nij_uap_id;
469 CLOSE get_defined_balance_id;
470 -- 8357870 end
471 --EOY 07/08 Begin
472 OPEN get_defined_balance_id('NI J Able AUEL','_ASG_TD_YTD');
473 FETCH get_defined_balance_id INTO g_nij_auel_id;
474 CLOSE get_defined_balance_id;
475 --EOY 07/08 End
476 OPEN get_defined_balance_id('NI J Able ET','_ASG_TD_YTD');
477 FETCH get_defined_balance_id INTO g_nij_et_id;
478 CLOSE get_defined_balance_id;
479 --
480 OPEN get_defined_balance_id('NI L Able','_ASG_TD_YTD');
481 FETCH get_defined_balance_id INTO g_nil_able_id;
482 CLOSE get_defined_balance_id;
483 --
484 OPEN get_defined_balance_id('NI L Able','_ASG_RUN');
485 FETCH get_defined_balance_id INTO g_nil_able_id_run;
486 CLOSE get_defined_balance_id;
487 --
488
489 OPEN get_defined_balance_id('NI L Employee','_ASG_TD_YTD');
490 FETCH get_defined_balance_id INTO g_nil_e_ytd_id;
491 CLOSE get_defined_balance_id;
492 --
493 OPEN get_defined_balance_id('NI L Employee','_ASG_RUN');
494 FETCH get_defined_balance_id INTO g_nil_e_ptd_id;
495 CLOSE get_defined_balance_id;
496 --
497 OPEN get_defined_balance_id('NI L Employer','_ASG_TD_YTD');
498 FETCH get_defined_balance_id INTO g_nil_r_ytd_id;
499 CLOSE get_defined_balance_id;
500 --
501 OPEN get_defined_balance_id('NI L Employer','_ASG_RUN');
502 FETCH get_defined_balance_id INTO g_nil_r_ptd_id;
503 CLOSE get_defined_balance_id;
504 --
505 OPEN get_defined_balance_id('NI L Total','_ASG_TD_YTD');
506 FETCH get_defined_balance_id INTO g_nil_tot_id;
507 CLOSE get_defined_balance_id;
508 --
509 OPEN get_defined_balance_id('NI L Total','_ASG_RUN');
510 FETCH get_defined_balance_id INTO g_nil_tot_id_run;
511 CLOSE get_defined_balance_id;
512 --
513 OPEN get_defined_balance_id('NI L Able LEL','_ASG_TD_YTD');
514 FETCH get_defined_balance_id INTO g_nil_lel_id;
515 CLOSE get_defined_balance_id;
516 --
517 OPEN get_defined_balance_id('NI L Able UEL','_ASG_TD_YTD');
518 FETCH get_defined_balance_id INTO g_nil_uel_id;
519 CLOSE get_defined_balance_id;
520 -- 8357870 begin
521 OPEN get_defined_balance_id('NI L Able UAP','_ASG_TD_YTD');
522 FETCH get_defined_balance_id INTO g_nil_uap_id;
523 CLOSE get_defined_balance_id;
524 -- 8357870 end
525 --EOY 07/08 Begin
526 OPEN get_defined_balance_id('NI L Able AUEL','_ASG_TD_YTD');
527 FETCH get_defined_balance_id INTO g_nil_auel_id;
528 CLOSE get_defined_balance_id;
529 --EOY 07/08 End
530 OPEN get_defined_balance_id('NI L Able ET','_ASG_TD_YTD');
531 FETCH get_defined_balance_id INTO g_nil_et_id;
532 CLOSE get_defined_balance_id;
533 --
534 END load_defined_balances;
535
536 PROCEDURE load_defined_balances_aggr
537 IS
538 CURSOR get_defined_balance_id (p_balance_name VARCHAR2, p_dimension_name VARCHAR2)
539 IS
540 SELECT defined_balance_id
541 FROM pay_defined_balances db,
542 pay_balance_types b,
543 pay_balance_dimensions d
544 WHERE b.balance_name = p_balance_name
545 AND d.dimension_name = p_dimension_name
546 AND db.balance_type_id = b.balance_type_id
547 AND db.balance_dimension_id = d.balance_dimension_id;
548 BEGIN
549 OPEN get_defined_balance_id('NI A Able','_PER_TD_YTD');
550 FETCH get_defined_balance_id INTO g_nia_able_id;
551 CLOSE get_defined_balance_id;
552 --
553 OPEN get_defined_balance_id('NI A Able','_ASG_RUN');
554 FETCH get_defined_balance_id INTO g_nia_able_id_run;
555 CLOSE get_defined_balance_id;
556 --
557 OPEN get_defined_balance_id('NI A Employee','_PER_TD_YTD');
558 FETCH get_defined_balance_id INTO g_nia_e_ytd_id;
559 CLOSE get_defined_balance_id;
560 --
561 OPEN get_defined_balance_id('NI A Employee','_ASG_RUN');
562 FETCH get_defined_balance_id INTO g_nia_e_ptd_id;
563 CLOSE get_defined_balance_id;
564 --
565 OPEN get_defined_balance_id('NI A Employer','_PER_TD_YTD');
566 FETCH get_defined_balance_id INTO g_nia_r_ytd_id;
567 CLOSE get_defined_balance_id;
568 --
569 OPEN get_defined_balance_id('NI A Employer','_ASG_RUN');
570 FETCH get_defined_balance_id INTO g_nia_r_ptd_id;
571 CLOSE get_defined_balance_id;
572 --
573 OPEN get_defined_balance_id('NI A Total','_PER_TD_YTD');
574 FETCH get_defined_balance_id INTO g_nia_tot_id;
575 CLOSE get_defined_balance_id;
576 --
577 OPEN get_defined_balance_id('NI A Total','_ASG_RUN');
578 FETCH get_defined_balance_id INTO g_nia_tot_id_run;
579 CLOSE get_defined_balance_id;
580 --
581 OPEN get_defined_balance_id('NI A Able LEL','_PER_TD_YTD');
582 FETCH get_defined_balance_id INTO g_nia_lel_id;
583 CLOSE get_defined_balance_id;
584 --
585 OPEN get_defined_balance_id('NI A Able UEL','_PER_TD_YTD');
586 FETCH get_defined_balance_id INTO g_nia_uel_id;
587 CLOSE get_defined_balance_id;
588 -- 8357870 begin
589 OPEN get_defined_balance_id('NI A Able UAP','_PER_TD_YTD');
590 FETCH get_defined_balance_id INTO g_nia_uap_id;
591 CLOSE get_defined_balance_id;
592 -- 8357870 end
593 --EOY 07/08 begin
594 OPEN get_defined_balance_id('NI A Able AUEL','_PER_TD_YTD');
595 FETCH get_defined_balance_id INTO g_nia_auel_id;
596 CLOSE get_defined_balance_id;
597 --EOY 07/08 end
598 OPEN get_defined_balance_id('NI A Able ET','_PER_TD_YTD');
599 FETCH get_defined_balance_id INTO g_nia_et_id;
600 CLOSE get_defined_balance_id;
601 --
602 OPEN get_defined_balance_id('NI B Able','_PER_TD_YTD');
603 FETCH get_defined_balance_id INTO g_nib_able_id;
604 CLOSE get_defined_balance_id;
605 --
606 OPEN get_defined_balance_id('NI B Able','_ASG_RUN');
607 FETCH get_defined_balance_id INTO g_nib_able_id_run;
608 CLOSE get_defined_balance_id;
609 --
610
611 OPEN get_defined_balance_id('NI B Employee','_PER_TD_YTD');
612 FETCH get_defined_balance_id INTO g_nib_e_ytd_id;
613 CLOSE get_defined_balance_id;
614 --
615 OPEN get_defined_balance_id('NI B Employee','_ASG_RUN');
616 FETCH get_defined_balance_id INTO g_nib_e_ptd_id;
617 CLOSE get_defined_balance_id;
618 --
619 OPEN get_defined_balance_id('NI B Employer','_PER_TD_YTD');
620 FETCH get_defined_balance_id INTO g_nib_r_ytd_id;
621 CLOSE get_defined_balance_id;
622 --
623 OPEN get_defined_balance_id('NI B Employer','_ASG_RUN');
624 FETCH get_defined_balance_id INTO g_nib_r_ptd_id;
625 CLOSE get_defined_balance_id;
626 --
627 --
628 OPEN get_defined_balance_id('NI B Total','_PER_TD_YTD');
629 FETCH get_defined_balance_id INTO g_nib_tot_id;
630 CLOSE get_defined_balance_id;
631 --
632 OPEN get_defined_balance_id('NI B Total','_ASG_RUN');
633 FETCH get_defined_balance_id INTO g_nib_tot_id_run;
634 CLOSE get_defined_balance_id;
635 --
636 OPEN get_defined_balance_id('NI B Able LEL','_PER_TD_YTD');
637 FETCH get_defined_balance_id INTO g_nib_lel_id;
638 CLOSE get_defined_balance_id;
639 --
640 OPEN get_defined_balance_id('NI B Able UEL','_PER_TD_YTD');
641 FETCH get_defined_balance_id INTO g_nib_uel_id;
642 CLOSE get_defined_balance_id;
643 -- 8357870 begin
644 OPEN get_defined_balance_id('NI B Able UAP','_PER_TD_YTD');
645 FETCH get_defined_balance_id INTO g_nib_uap_id;
646 CLOSE get_defined_balance_id;
647 -- 8357870 end
648 --EOY 07/08 begin
649 OPEN get_defined_balance_id('NI B Able AUEL','_PER_TD_YTD');
650 FETCH get_defined_balance_id INTO g_nib_auel_id;
651 CLOSE get_defined_balance_id;
652 --EOY 07/08 end
653 OPEN get_defined_balance_id('NI B Able ET','_PER_TD_YTD');
654 FETCH get_defined_balance_id INTO g_nib_et_id;
655 CLOSE get_defined_balance_id;
656 --
657 OPEN get_defined_balance_id('NI C Able LEL','_PER_TD_YTD');
658 FETCH get_defined_balance_id INTO g_nic_lel_id;
659 CLOSE get_defined_balance_id;
660 --
661 OPEN get_defined_balance_id('NI C Able UEL','_PER_TD_YTD');
662 FETCH get_defined_balance_id INTO g_nic_uel_id;
663 CLOSE get_defined_balance_id;
664 -- 8357870 begin
665 OPEN get_defined_balance_id('NI C Able UAP','_PER_TD_YTD');
666 FETCH get_defined_balance_id INTO g_nic_uap_id;
667 CLOSE get_defined_balance_id;
668 -- 8357870 end
669 --EOY 07/08 Begin
670 OPEN get_defined_balance_id('NI C Able AUEL','_PER_TD_YTD');
671 FETCH get_defined_balance_id INTO g_nic_auel_id;
672 CLOSE get_defined_balance_id;
673 --EOY 07/08 End
674 OPEN get_defined_balance_id('NI C Able ET','_PER_TD_YTD');
675 FETCH get_defined_balance_id INTO g_nic_et_id;
676 CLOSE get_defined_balance_id;
677 --
678 OPEN get_defined_balance_id('NI C Able','_PER_TD_YTD');
679 FETCH get_defined_balance_id INTO g_nic_able_id;
680 CLOSE get_defined_balance_id;
681 --
682 OPEN get_defined_balance_id('NI C Able','_ASG_RUN');
683 FETCH get_defined_balance_id INTO g_nic_able_id_run;
684 CLOSE get_defined_balance_id;
685 --
686
687 OPEN get_defined_balance_id('NI C Employee','_PER_TD_YTD');
688 FETCH get_defined_balance_id INTO g_nic_e_ytd_id;
689 CLOSE get_defined_balance_id;
690 --
691 OPEN get_defined_balance_id('NI C Employee','_ASG_RUN');
692 FETCH get_defined_balance_id INTO g_nic_e_ptd_id;
693 CLOSE get_defined_balance_id;
694 --
695 OPEN get_defined_balance_id('NI C Employer','_PER_TD_YTD');
696 FETCH get_defined_balance_id INTO g_nic_r_ytd_id;
697 CLOSE get_defined_balance_id;
698 --
699 OPEN get_defined_balance_id('NI C Employer','_ASG_RUN');
700 FETCH get_defined_balance_id INTO g_nic_r_ptd_id;
701 CLOSE get_defined_balance_id;
702
703 OPEN get_defined_balance_id('NI C Total','_PER_TD_YTD');
704 FETCH get_defined_balance_id INTO g_nic_tot_id;
705 CLOSE get_defined_balance_id;
706 --
707 OPEN get_defined_balance_id('NI C Total','_ASG_RUN');
708 FETCH get_defined_balance_id INTO g_nic_tot_id_run;
709 CLOSE get_defined_balance_id;
710 --
711 -- Fix for Bug 1976152, added the below stmt to fetch the balance id
712 -- for the balance NI C Employers Rebate
713 OPEN get_defined_balance_id('NI C Ers Rebate','_PER_TD_YTD');
714 FETCH get_defined_balance_id INTO g_nic_ers_rebate_id;
715 CLOSE get_defined_balance_id;
716 --
717 OPEN get_defined_balance_id('NI D Able','_PER_TD_YTD');
718 FETCH get_defined_balance_id INTO g_nid_able_id;
719 CLOSE get_defined_balance_id;
720 --
721 OPEN get_defined_balance_id('NI D Able','_ASG_RUN');
722 FETCH get_defined_balance_id INTO g_nid_able_id_run;
723 CLOSE get_defined_balance_id;
724 --
725
726 OPEN get_defined_balance_id('NI D Employee','_PER_TD_YTD');
727 FETCH get_defined_balance_id INTO g_nid_e_ytd_id;
728 CLOSE get_defined_balance_id;
729 --
730 OPEN get_defined_balance_id('NI D Employee','_ASG_RUN');
731 FETCH get_defined_balance_id INTO g_nid_e_ptd_id;
732 CLOSE get_defined_balance_id;
733 --
734 OPEN get_defined_balance_id('NI D Employer','_PER_TD_YTD');
735 FETCH get_defined_balance_id INTO g_nid_r_ytd_id;
736 CLOSE get_defined_balance_id;
737 --
738 OPEN get_defined_balance_id('NI D Employer','_ASG_RUN');
739 FETCH get_defined_balance_id INTO g_nid_r_ptd_id;
740 CLOSE get_defined_balance_id;
741 --
742 OPEN get_defined_balance_id('NI D Total','_PER_TD_YTD');
743 FETCH get_defined_balance_id INTO g_nid_tot_id;
744 CLOSE get_defined_balance_id;
745 --
746 OPEN get_defined_balance_id('NI D Total','_ASG_RUN');
747 FETCH get_defined_balance_id INTO g_nid_tot_id_run;
748 CLOSE get_defined_balance_id;
749 --
750 OPEN get_defined_balance_id('NI D Able LEL','_PER_TD_YTD');
751 FETCH get_defined_balance_id INTO g_nid_lel_id;
752 CLOSE get_defined_balance_id;
753 --
754 OPEN get_defined_balance_id('NI D Able UEL','_PER_TD_YTD');
755 FETCH get_defined_balance_id INTO g_nid_uel_id;
756 CLOSE get_defined_balance_id;
757 -- 8357870 begin
758 OPEN get_defined_balance_id('NI D Able UAP','_PER_TD_YTD');
759 FETCH get_defined_balance_id INTO g_nid_uap_id;
760 CLOSE get_defined_balance_id;
761 -- 8357870 end
762 --EOY 07/08 Begin
763 OPEN get_defined_balance_id('NI D Able AUEL','_PER_TD_YTD');
764 FETCH get_defined_balance_id INTO g_nid_auel_id;
765 CLOSE get_defined_balance_id;
766 --EOY 07/08 End
767 OPEN get_defined_balance_id('NI D Able ET','_PER_TD_YTD');
768 FETCH get_defined_balance_id INTO g_nid_et_id;
769 CLOSE get_defined_balance_id;
770 --
771 OPEN get_defined_balance_id('NI D Ers Rebate','_PER_TD_YTD');
772 FETCH get_defined_balance_id INTO g_nid_ers_rebate_id;
773 CLOSE get_defined_balance_id;
774 --
775 OPEN get_defined_balance_id('NI D Ees Rebate','_PER_TD_YTD');
776 FETCH get_defined_balance_id INTO g_nid_ees_rebate_id;
777 CLOSE get_defined_balance_id;
778 --
779 OPEN get_defined_balance_id('NI D Rebate to Employee','_PER_TD_YTD');
780 FETCH get_defined_balance_id INTO g_nid_rebate_emp_id;
781 CLOSE get_defined_balance_id;
782 --
783 OPEN get_defined_balance_id('NI E Able','_PER_TD_YTD');
784 FETCH get_defined_balance_id INTO g_nie_able_id;
785 CLOSE get_defined_balance_id;
786 --
787 OPEN get_defined_balance_id('NI E Able','_ASG_RUN');
788 FETCH get_defined_balance_id INTO g_nie_able_id_run;
789 CLOSE get_defined_balance_id;
790 --
791
792 OPEN get_defined_balance_id('NI E Employee','_PER_TD_YTD');
793 FETCH get_defined_balance_id INTO g_nie_e_ytd_id;
794 CLOSE get_defined_balance_id;
795 --
796 OPEN get_defined_balance_id('NI E Employee','_ASG_RUN');
797 FETCH get_defined_balance_id INTO g_nie_e_ptd_id;
798 CLOSE get_defined_balance_id;
799 --
800 OPEN get_defined_balance_id('NI E Employer','_PER_TD_YTD');
801 FETCH get_defined_balance_id INTO g_nie_r_ytd_id;
802 CLOSE get_defined_balance_id;
803 --
804 OPEN get_defined_balance_id('NI E Employer','_ASG_RUN');
805 FETCH get_defined_balance_id INTO g_nie_r_ptd_id;
806 CLOSE get_defined_balance_id;
807 OPEN get_defined_balance_id('NI E Total','_PER_TD_YTD');
808 FETCH get_defined_balance_id INTO g_nie_tot_id;
809 CLOSE get_defined_balance_id;
810 --
811 OPEN get_defined_balance_id('NI E Total','_ASG_RUN');
812 FETCH get_defined_balance_id INTO g_nie_tot_id_run;
813 CLOSE get_defined_balance_id;
814 --
815 OPEN get_defined_balance_id('NI E Able LEL','_PER_TD_YTD');
816 FETCH get_defined_balance_id INTO g_nie_lel_id;
817 CLOSE get_defined_balance_id;
818 --
819 OPEN get_defined_balance_id('NI E Able UEL','_PER_TD_YTD');
820 FETCH get_defined_balance_id INTO g_nie_uel_id;
821 CLOSE get_defined_balance_id;
822 -- 8357870 begin
823 OPEN get_defined_balance_id('NI E Able UAP','_PER_TD_YTD');
824 FETCH get_defined_balance_id INTO g_nie_uap_id;
825 CLOSE get_defined_balance_id;
826 -- 8357870 end
827 --EOY 07/08 Begin
828 OPEN get_defined_balance_id('NI E Able AUEL','_PER_TD_YTD');
829 FETCH get_defined_balance_id INTO g_nie_auel_id;
830 CLOSE get_defined_balance_id;
831 --EOY 07/08 End
832 OPEN get_defined_balance_id('NI E Able ET','_PER_TD_YTD');
833 FETCH get_defined_balance_id INTO g_nie_et_id;
834 CLOSE get_defined_balance_id;
835 OPEN get_defined_balance_id('NI J Able','_PER_TD_YTD');
836 FETCH get_defined_balance_id INTO g_nij_able_id;
837 CLOSE get_defined_balance_id;
838 --
839 OPEN get_defined_balance_id('NI J Able','_ASG_RUN');
840 FETCH get_defined_balance_id INTO g_nij_able_id_run;
841 CLOSE get_defined_balance_id;
842 --
843
844 OPEN get_defined_balance_id('NI J Employee','_PER_TD_YTD');
845 FETCH get_defined_balance_id INTO g_nij_e_ytd_id;
846 CLOSE get_defined_balance_id;
847 --
848 OPEN get_defined_balance_id('NI J Employee','_ASG_RUN');
849 FETCH get_defined_balance_id INTO g_nij_e_ptd_id;
850 CLOSE get_defined_balance_id;
851 --
852 OPEN get_defined_balance_id('NI J Employer','_PER_TD_YTD');
853 FETCH get_defined_balance_id INTO g_nij_r_ytd_id;
854 CLOSE get_defined_balance_id;
855 --
856 OPEN get_defined_balance_id('NI J Employer','_ASG_RUN');
857 FETCH get_defined_balance_id INTO g_nij_r_ptd_id;
858 CLOSE get_defined_balance_id;
859 --
860 OPEN get_defined_balance_id('NI J Total','_PER_TD_YTD');
861 FETCH get_defined_balance_id INTO g_nij_tot_id;
862 CLOSE get_defined_balance_id;
863 --
864 OPEN get_defined_balance_id('NI J Total','_ASG_RUN');
865 FETCH get_defined_balance_id INTO g_nij_tot_id_run;
866 CLOSE get_defined_balance_id;
867 --
868 OPEN get_defined_balance_id('NI J Able LEL','_PER_TD_YTD');
869 FETCH get_defined_balance_id INTO g_nij_lel_id;
870 CLOSE get_defined_balance_id;
871 --
872 OPEN get_defined_balance_id('NI J Able UEL','_PER_TD_YTD');
873 FETCH get_defined_balance_id INTO g_nij_uel_id;
874 CLOSE get_defined_balance_id;
875 -- 8357870 begin
876 OPEN get_defined_balance_id('NI J Able UAP','_PER_TD_YTD');
877 FETCH get_defined_balance_id INTO g_nij_uap_id;
878 CLOSE get_defined_balance_id;
879 -- 8357870 end
880 --EOY 07/08 Begin
881 OPEN get_defined_balance_id('NI J Able AUEL','_PER_TD_YTD');
882 FETCH get_defined_balance_id INTO g_nij_auel_id;
883 CLOSE get_defined_balance_id;
884 --EOY 07/08 End
885 OPEN get_defined_balance_id('NI J Able ET','_PER_TD_YTD');
886 FETCH get_defined_balance_id INTO g_nij_et_id;
887 CLOSE get_defined_balance_id;
888 --
889 OPEN get_defined_balance_id('NI L Able','_PER_TD_YTD');
890 FETCH get_defined_balance_id INTO g_nil_able_id;
891 CLOSE get_defined_balance_id;
892 --
893 OPEN get_defined_balance_id('NI L Able','_ASG_RUN');
894 FETCH get_defined_balance_id INTO g_nil_able_id_run;
895 CLOSE get_defined_balance_id;
896 --
897
898 OPEN get_defined_balance_id('NI L Employee','_PER_TD_YTD');
899 FETCH get_defined_balance_id INTO g_nil_e_ytd_id;
900 CLOSE get_defined_balance_id;
901 --
902 OPEN get_defined_balance_id('NI L Employee','_ASG_RUN');
903 FETCH get_defined_balance_id INTO g_nil_e_ptd_id;
904 CLOSE get_defined_balance_id;
905 --
906 OPEN get_defined_balance_id('NI L Employer','_PER_TD_YTD');
907 FETCH get_defined_balance_id INTO g_nil_r_ytd_id;
908 CLOSE get_defined_balance_id;
909 --
910 OPEN get_defined_balance_id('NI L Employer','_ASG_RUN');
911 FETCH get_defined_balance_id INTO g_nil_r_ptd_id;
912 CLOSE get_defined_balance_id;
913 --
914 OPEN get_defined_balance_id('NI L Total','_PER_TD_YTD');
915 FETCH get_defined_balance_id INTO g_nil_tot_id;
916 CLOSE get_defined_balance_id;
917 --
918 OPEN get_defined_balance_id('NI L Total','_ASG_RUN');
919 FETCH get_defined_balance_id INTO g_nil_tot_id_run;
920 CLOSE get_defined_balance_id;
921
922 OPEN get_defined_balance_id('NI L Able LEL','_PER_TD_YTD');
923 FETCH get_defined_balance_id INTO g_nil_lel_id;
924 CLOSE get_defined_balance_id;
925 --
926 OPEN get_defined_balance_id('NI L Able UEL','_PER_TD_YTD');
927 FETCH get_defined_balance_id INTO g_nil_uel_id;
928 CLOSE get_defined_balance_id;
929 -- 8357870 begin
930 OPEN get_defined_balance_id('NI L Able UAP','_PER_TD_YTD');
931 FETCH get_defined_balance_id INTO g_nil_uap_id;
932 CLOSE get_defined_balance_id;
933 -- 8357870 end
934 --EOY 07/08 Begin
935 OPEN get_defined_balance_id('NI L Able AUEL','_PER_TD_YTD');
936 FETCH get_defined_balance_id INTO g_nil_auel_id;
937 CLOSE get_defined_balance_id;
938 --EOY 07/08 End
939 OPEN get_defined_balance_id('NI L Able ET','_PER_TD_YTD');
940 FETCH get_defined_balance_id INTO g_nil_et_id;
941 CLOSE get_defined_balance_id;
942 --
943 END load_defined_balances_aggr;
944
945 PROCEDURE get_ni_bal_paye_aggr_for_asg(
946 p_asg_id IN NUMBER,
947 l_last_asg_action_id IN NUMBER,
948 p_ni_a_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
949 p_ni_b_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
950 p_ni_c_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
951 p_ni_d_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
952 p_ni_e_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
953 p_ni_j_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
954 P_NI_L_BALANCE IN OUT NOCOPY pay_gb_rti_fps_archive.ACT_INFO_REC )
955 IS
956 -- local variables to store NI total and able balances
957 l_nia_tot NUMBER(15,2) :=0;
958 l_nia_able NUMBER(15,2) :=0;
959 l_nib_tot NUMBER(15,2) :=0;
960 l_nib_able NUMBER(15,2) :=0;
961 l_nic_tot NUMBER(15,2) :=0;
962 l_nic_able NUMBER(15,2) :=0;
963 l_nid_tot NUMBER(15,2) :=0;
964 l_nid_able NUMBER(15,2) :=0;
965 l_nie_tot NUMBER(15,2) :=0;
966 l_nie_able NUMBER(15,2) :=0;
967 l_nij_tot NUMBER(15,2) :=0;
968 l_nij_able NUMBER(15,2) :=0;
969 l_nil_tot NUMBER(15,2) :=0;
970 l_nil_able NUMBER(15,2) :=0;
971
972 l_nia_able_run NUMBER(15,2) :=0;
973 l_nia_e_ptd NUMBER(15,2) :=0;
974 l_nib_able_run NUMBER(15,2) :=0;
975 l_nib_e_ptd NUMBER(15,2) :=0;
976 l_nic_able_run NUMBER(15,2) :=0;
977 l_nic_e_ptd NUMBER(15,2) :=0;
978 l_nid_able_run NUMBER(15,2) :=0;
979 l_nid_e_ptd NUMBER(15,2) :=0;
980 l_nie_able_run NUMBER(15,2) :=0;
981 l_nie_e_ptd NUMBER(15,2) :=0;
982 l_nij_able_run NUMBER(15,2) :=0;
983 l_nij_e_ptd NUMBER(15,2) :=0;
984 l_nil_able_run NUMBER(15,2) :=0;
985 l_nil_e_ptd NUMBER(15,2) :=0;
986
987 l_nia_tot_run NUMBER(15,2) :=0;
988 l_nia_r_ytd NUMBER(15,2) :=0;
989 l_nia_r_ptd NUMBER(15,2) :=0;
990 l_nib_tot_run NUMBER(15,2) :=0;
991 l_nib_r_ytd NUMBER(15,2) :=0;
992 l_nib_r_ptd NUMBER(15,2) :=0;
993 l_nic_tot_run NUMBER(15,2) :=0;
994 l_nic_r_ytd NUMBER(15,2) :=0;
995 l_nic_r_ptd NUMBER(15,2) :=0;
996 l_nid_tot_run NUMBER(15,2) :=0;
997 l_nid_r_ytd NUMBER(15,2) :=0;
998 l_nid_r_ptd NUMBER(15,2) :=0;
999 l_nie_tot_run NUMBER(15,2) :=0;
1000 l_nie_r_ytd NUMBER(15,2) :=0;
1001 l_nie_r_ptd NUMBER(15,2) :=0;
1002 l_nij_tot_run NUMBER(15,2) :=0;
1003 l_nij_r_ytd NUMBER(15,2) :=0;
1004 l_nij_r_ptd NUMBER(15,2) :=0;
1005 l_nil_tot_run NUMBER(15,2) :=0;
1006 l_nil_r_ytd NUMBER(15,2) :=0;
1007 l_nil_r_ptd NUMBER(15,2) :=0;
1008
1009 l_count_values NUMBER := 0;
1010 l_asg_action_id NUMBER;
1011 l_tax_pay_def_bal_id NUMBER;
1012 l_tax_pay_val NUMBER;
1013 l_reversal VARCHAR2(1);
1014
1015 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
1016 IS
1017 SELECT ASSIGNMENT_ACTION_ID
1018 FROM pay_assignment_actions
1019 WHERE SOURCE_ACTION_ID = c_lst_act_id;
1020 CURSOR csr_get_taxable_pay(c_balance_name VARCHAR2, c_dim_name VARCHAR2)
1021 IS
1022 SELECT pdb.defined_balance_id
1023 FROM pay_defined_balances pdb,
1024 pay_balance_dimensions pbd,
1025 pay_balance_types pbt
1026 WHERE pbt.balance_name = c_balance_name
1027 AND pbd.database_item_suffix = c_dim_name
1028 AND pbt.balance_type_id = pdb.balance_type_id
1029 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1030 AND pbt.legislation_code = 'GB';
1031 CURSOR csr_tax_pay_value(p_last_asg_action_id NUMBER,l_tax_pay_def_bal_id NUMBER)
1032 IS
1033 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
1034 FROM dual;
1035
1036 cursor csr_reversal is
1037 select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
1038 where paa.assignment_action_id = l_last_asg_action_id
1039 and paa.payroll_action_id = ppa.payroll_action_id
1040 and ACTION_TYPE in ('V');
1041
1042 BEGIN
1043 hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_balances_for_asg.');
1044 load_defined_balances_aggr();
1045 -- fetch NI x Total/Able balances for checking whether this assignment
1046 -- reported in the P35 report or not. -- Bug 6271548
1047 hr_utility.trace('fetching NI x Total/Able balances');
1048 OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1049 FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1050 CLOSE csr_get_taxable_pay;
1051 OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1052 FETCH csr_tax_pay_value INTO l_tax_pay_val;
1053 CLOSE csr_tax_pay_value;
1054 hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1055 -- Test whether we can get run level value with parent action id.
1056 -- If not pass the child assignment action id.
1057 IF l_tax_pay_val = 0 THEN
1058 OPEN csr_child_act_id(l_last_asg_action_id);
1059 FETCH csr_child_act_id INTO l_asg_action_id;
1060 CLOSE csr_child_act_id;
1061 ELSE
1062 l_asg_action_id := l_last_asg_action_id;
1063 END IF;
1064 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1065 if l_asg_action_id is null then
1066 l_asg_action_id := l_last_asg_action_id;
1067 end if;
1068
1069 open csr_reversal;
1070 fetch csr_reversal into l_reversal;
1071 close csr_reversal;
1072
1073 -- NI A
1074 l_nia_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
1075 l_nia_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1076 l_nia_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_tot_id_run);
1077 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
1078 l_nia_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ytd_id);
1079 l_nia_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
1080 -- NI B
1081 l_nib_tot := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id);
1082 l_nib_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
1083 l_nib_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id_run);
1084 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
1085 l_nib_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ytd_id);
1086 l_nib_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
1087 -- NI C
1088 l_nic_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nic_tot_id);
1089 l_nic_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
1090 l_nic_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_tot_id_run);
1091 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
1092 l_nic_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ytd_id);
1093 l_nic_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
1094 -- NI D
1095 l_nid_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nid_tot_id);
1096 l_nid_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
1097 l_nid_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_tot_id_run);
1098 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
1099 l_nid_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ytd_id);
1100 l_nid_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
1101 -- NI E
1102 l_nie_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nie_tot_id);
1103 l_nie_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
1104 l_nie_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_tot_id_run);
1105 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
1106 l_nie_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ytd_id);
1107 l_nie_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
1108 -- NI J
1109 l_nij_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nij_tot_id);
1110 l_nij_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
1111 l_nij_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_tot_id_run);
1112 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
1113 l_nij_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ytd_id);
1114 l_nij_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
1115 -- NI L
1116 l_nil_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nil_tot_id);
1117 l_nil_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
1118 l_nil_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_tot_id_run);
1119 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
1120 l_nil_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ytd_id);
1121 l_nil_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
1122
1123 if l_reversal is not null then
1124
1125 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
1126 l_nia_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
1127
1128 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
1129 l_nib_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
1130
1131 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
1132 l_nic_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
1133
1134 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
1135 l_nid_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
1136
1137 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
1138 l_nie_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
1139
1140 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
1141 l_nij_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
1142
1143 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
1144 l_nil_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1145
1146 end if;
1147
1148
1149 IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 ) or
1150 ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1151 hr_utility.trace('fetching NI A Total/Able balances');
1152 p_ni_a_balance.assignment_id := p_asg_id;
1153 p_ni_a_balance.act_info1 := 'A';
1154 p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1155 p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1156 p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
1157 p_ni_a_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uap_id);
1158 p_ni_a_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uel_id);
1159 p_ni_a_balance.act_info7 := 100 * l_nia_r_ytd ;
1160 p_ni_a_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ytd_id);
1161 p_ni_a_balance.act_info9 := 100 * l_nia_r_ptd ;
1162 p_ni_a_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
1163 p_ni_a_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1164 l_count_values := l_count_values +1;
1165 END IF;
1166 IF (NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 ) or
1167 ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1168 hr_utility.trace('fetching NI B Total/Able balances');
1169 p_ni_b_balance.assignment_id := p_asg_id;
1170 p_ni_b_balance.act_info1 := 'B';
1171 p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1172 p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1173 p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
1174 p_ni_b_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uap_id);
1175 p_ni_b_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uel_id);
1176 p_ni_b_balance.act_info7 := 100 * l_nib_r_ytd ;
1177 p_ni_b_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ytd_id);
1178 p_ni_b_balance.act_info9 := 100 * l_nib_r_ptd ;
1179 p_ni_b_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
1180 p_ni_b_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
1181 l_count_values := l_count_values +1;
1182 END IF;
1183 IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 ) or
1184 ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1185 hr_utility.trace('fetching NI C Total/Able balances');
1186 p_ni_c_balance.assignment_id := p_asg_id;
1187 p_ni_c_balance.act_info1 := 'C';
1188 p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1189 p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1190 p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
1191 p_ni_c_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uap_id);
1192 p_ni_c_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uel_id);
1193 p_ni_c_balance.act_info7 := 100 * l_nic_r_ytd ;
1194 p_ni_c_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ytd_id);
1195 p_ni_c_balance.act_info9 := 100 * l_nic_r_ptd ;
1196 p_ni_c_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
1197 p_ni_c_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
1198 l_count_values := l_count_values +1;
1199 END IF;
1200 IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1201 ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1202 hr_utility.trace('fetching NI D Total/Able balances');
1203 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1204 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1205 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1206 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1207 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1208 p_ni_d_balance.assignment_id := p_asg_id;
1209 p_ni_d_balance.act_info1 := 'D';
1210 p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1211 p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1212 p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
1213 p_ni_d_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uap_id);
1214 p_ni_d_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uel_id);
1215 p_ni_d_balance.act_info7 := 100 * l_nid_r_ytd ;
1216 p_ni_d_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ytd_id);
1217 p_ni_d_balance.act_info9 := 100 * l_nid_r_ptd ;
1218 p_ni_d_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
1219 p_ni_e_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
1220 l_count_values := l_count_values +1;
1221 END IF;
1222 IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 ) or
1223 ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1224 hr_utility.trace('fetching NI E Total/Able balances');
1225 p_ni_e_balance.assignment_id := p_asg_id;
1226 p_ni_e_balance.act_info1 := 'E';
1227 p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1228 p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1229 p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
1230 p_ni_e_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uap_id);
1231 p_ni_e_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uel_id);
1232 p_ni_e_balance.act_info7 := 100 * l_nie_r_ytd ;
1233 p_ni_e_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ytd_id);
1234 p_ni_e_balance.act_info9 := 100 * l_nie_r_ptd ;
1235 p_ni_e_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
1236 p_ni_e_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
1237 l_count_values := l_count_values +1;
1238 END IF;
1239 IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 ) or
1240 ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1241 hr_utility.trace('fetching NI J Total/Able balances');
1242 p_ni_j_balance.assignment_id := p_asg_id;
1243 p_ni_j_balance.act_info1 := 'J';
1244 p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1245 p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1246 p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
1247 p_ni_j_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uap_id);
1248 p_ni_j_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uel_id);
1249 p_ni_j_balance.act_info7 := 100 * l_nij_r_ytd ;
1250 p_ni_j_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ytd_id);
1251 p_ni_j_balance.act_info9 := 100 * l_nij_r_ptd ;
1252 p_ni_j_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
1253 p_ni_j_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
1254 l_count_values := l_count_values +1;
1255 END IF;
1256 IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 ) or
1257 ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1258 hr_utility.trace('fetching NI L Total/Able balances');
1259 p_ni_l_balance.assignment_id := p_asg_id;
1260 p_ni_l_balance.act_info1 := 'L';
1261 p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
1262 p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1263 p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
1264 p_ni_l_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uap_id);
1265 p_ni_l_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uel_id);
1266 p_ni_l_balance.act_info7 := 100 * l_nil_r_ytd ;
1267 p_ni_l_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ytd_id);
1268 p_ni_l_balance.act_info9 := 100 * l_nil_r_ptd ;
1269 p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1270 p_ni_l_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
1271 l_count_values := l_count_values +1;
1272 END IF;
1273
1274 hr_utility.trace('Number of categories: ' || l_count_values);
1275 END get_ni_bal_paye_aggr_for_asg;
1276
1277 PROCEDURE get_ni_only_bal_rti_rpt(
1278 l_last_asg_action_id IN NUMBER,
1279 p_ni_a_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1280 p_ni_b_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1281 p_ni_c_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1282 p_ni_d_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1283 p_ni_e_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1284 p_ni_j_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1285 p_ni_l_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec )
1286 IS
1287 -- local variables to store NI total and able balances
1288 l_nia_tot NUMBER(15,2) :=0;
1289 l_nia_able NUMBER(15,2) :=0;
1290 l_nib_tot NUMBER(15,2) :=0;
1291 l_nib_able NUMBER(15,2) :=0;
1292 l_nic_tot NUMBER(15,2) :=0;
1293 l_nic_able NUMBER(15,2) :=0;
1294 l_nid_tot NUMBER(15,2) :=0;
1295 l_nid_able NUMBER(15,2) :=0;
1296 l_nie_tot NUMBER(15,2) :=0;
1297 l_nie_able NUMBER(15,2) :=0;
1298 l_nij_tot NUMBER(15,2) :=0;
1299 l_nij_able NUMBER(15,2) :=0;
1300 l_nil_tot NUMBER(15,2) :=0;
1301 l_nil_able NUMBER(15,2) :=0;
1302
1303 l_nia_able_run NUMBER(15,2) :=0;
1304 l_nia_e_ptd NUMBER(15,2) :=0;
1305 l_nib_able_run NUMBER(15,2) :=0;
1306 l_nib_e_ptd NUMBER(15,2) :=0;
1307 l_nic_able_run NUMBER(15,2) :=0;
1308 l_nic_e_ptd NUMBER(15,2) :=0;
1309 l_nid_able_run NUMBER(15,2) :=0;
1310 l_nid_e_ptd NUMBER(15,2) :=0;
1311 l_nie_able_run NUMBER(15,2) :=0;
1312 l_nie_e_ptd NUMBER(15,2) :=0;
1313 l_nij_able_run NUMBER(15,2) :=0;
1314 l_nij_e_ptd NUMBER(15,2) :=0;
1315 l_nil_able_run NUMBER(15,2) :=0;
1316 l_nil_e_ptd NUMBER(15,2) :=0;
1317
1318 l_nia_tot_run NUMBER(15,2) :=0;
1319 l_nia_r_ytd NUMBER(15,2) :=0;
1320 l_nia_r_ptd NUMBER(15,2) :=0;
1321 l_nib_tot_run NUMBER(15,2) :=0;
1322 l_nib_r_ytd NUMBER(15,2) :=0;
1323 l_nib_r_ptd NUMBER(15,2) :=0;
1324 l_nic_tot_run NUMBER(15,2) :=0;
1325 l_nic_r_ytd NUMBER(15,2) :=0;
1326 l_nic_r_ptd NUMBER(15,2) :=0;
1327 l_nid_tot_run NUMBER(15,2) :=0;
1328 l_nid_r_ytd NUMBER(15,2) :=0;
1329 l_nid_r_ptd NUMBER(15,2) :=0;
1330 l_nie_tot_run NUMBER(15,2) :=0;
1331 l_nie_r_ytd NUMBER(15,2) :=0;
1332 l_nie_r_ptd NUMBER(15,2) :=0;
1333 l_nij_tot_run NUMBER(15,2) :=0;
1334 l_nij_r_ytd NUMBER(15,2) :=0;
1335 l_nij_r_ptd NUMBER(15,2) :=0;
1336 l_nil_tot_run NUMBER(15,2) :=0;
1337 l_nil_r_ytd NUMBER(15,2) :=0;
1338 l_nil_r_ptd NUMBER(15,2) :=0;
1339
1340 l_count_values NUMBER := 0;
1341 l_asg_action_id NUMBER;
1342 l_tax_pay_def_bal_id NUMBER;
1343 l_tax_pay_val NUMBER;
1344 l_reversal VARCHAR2(1);
1345
1346 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
1347 IS
1348 SELECT ASSIGNMENT_ACTION_ID
1349 FROM pay_assignment_actions
1350 WHERE SOURCE_ACTION_ID = c_lst_act_id;
1351 CURSOR csr_get_taxable_pay(c_balance_name VARCHAR2, c_dim_name VARCHAR2)
1352 IS
1353 SELECT pdb.defined_balance_id
1354 FROM pay_defined_balances pdb,
1355 pay_balance_dimensions pbd,
1356 pay_balance_types pbt
1357 WHERE pbt.balance_name = c_balance_name
1358 AND pbd.database_item_suffix = c_dim_name
1359 AND pbt.balance_type_id = pdb.balance_type_id
1360 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1361 AND pbt.legislation_code = 'GB';
1362 CURSOR csr_tax_pay_value(p_last_asg_action_id NUMBER,l_tax_pay_def_bal_id NUMBER)
1363 IS
1364 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
1365 FROM dual;
1366
1367 cursor csr_reversal is
1368 select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
1369 where paa.assignment_action_id = l_last_asg_action_id
1370 and paa.payroll_action_id = ppa.payroll_action_id
1371 and ACTION_TYPE in ('V');
1372
1373 BEGIN
1374 hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_bal_rti_rpt.');
1375 load_defined_balances_aggr();
1376 -- fetch NI x Total/Able balances for checking whether this assignment
1377 -- reported in the P35 report or not. -- Bug 6271548
1378 hr_utility.trace('fetching NI x Total/Able balances');
1379 OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1380 FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1381 CLOSE csr_get_taxable_pay;
1382 OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1383 FETCH csr_tax_pay_value INTO l_tax_pay_val;
1384 CLOSE csr_tax_pay_value;
1385 hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1386 -- Test whether we can get run level value with parent action id.
1387 -- If not pass the child assignment action id.
1388 IF l_tax_pay_val = 0 THEN
1389 OPEN csr_child_act_id(l_last_asg_action_id);
1390 FETCH csr_child_act_id INTO l_asg_action_id;
1391 CLOSE csr_child_act_id;
1392 ELSE
1393 l_asg_action_id := l_last_asg_action_id;
1394 END IF;
1395 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1396 if l_asg_action_id is null then
1397 l_asg_action_id := l_last_asg_action_id;
1398 end if;
1399
1400 open csr_reversal;
1401 fetch csr_reversal into l_reversal;
1402 close csr_reversal;
1403
1404 -- NI A
1405 l_nia_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
1406 l_nia_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1407 l_nia_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_tot_id_run);
1408 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
1409 l_nia_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ytd_id);
1410 l_nia_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
1411 -- NI B
1412 l_nib_tot := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id);
1413 l_nib_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
1414 l_nib_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id_run);
1415 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
1416 l_nib_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ytd_id);
1417 l_nib_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
1418 -- NI C
1419 l_nic_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nic_tot_id);
1420 l_nic_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
1421 l_nic_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_tot_id_run);
1422 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
1423 l_nic_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ytd_id);
1424 l_nic_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
1425 -- NI D
1426 l_nid_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nid_tot_id);
1427 l_nid_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
1428 l_nid_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_tot_id_run);
1429 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
1430 l_nid_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ytd_id);
1431 l_nid_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
1432 -- NI E
1433 l_nie_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nie_tot_id);
1434 l_nie_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
1435 l_nie_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_tot_id_run);
1436 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
1437 l_nie_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ytd_id);
1438 l_nie_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
1439 -- NI J
1440 l_nij_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nij_tot_id);
1441 l_nij_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
1442 l_nij_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_tot_id_run);
1443 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
1444 l_nij_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ytd_id);
1445 l_nij_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
1446 -- NI L
1447 l_nil_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nil_tot_id);
1448 l_nil_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
1449 l_nil_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_tot_id_run);
1450 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
1451 l_nil_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ytd_id);
1452 l_nil_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
1453
1454 if l_reversal is not null then
1455
1456 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
1457 l_nia_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
1458
1459 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
1460 l_nib_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
1461
1462 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
1463 l_nic_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
1464
1465 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
1466 l_nid_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
1467
1468 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
1469 l_nie_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
1470
1471 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
1472 l_nij_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
1473
1474 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
1475 l_nil_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1476
1477 end if;
1478
1479 IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 ) or
1480 ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1481 hr_utility.trace('fetching NI A Total/Able balances');
1482 p_ni_a_balance.act_info1 := 'A';
1483 p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1484 p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1485 p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
1486 p_ni_a_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uap_id);
1487 p_ni_a_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uel_id);
1488 p_ni_a_balance.act_info7 := 100 * l_nia_r_ytd ;
1489 p_ni_a_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ytd_id);
1490 p_ni_a_balance.act_info9 := 100 * l_nia_r_ptd ;
1491 p_ni_a_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
1492 p_ni_a_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1493 l_count_values := l_count_values +1;
1494 END IF;
1495 IF (NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 ) or
1496 ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1497 hr_utility.trace('fetching NI B Total/Able balances');
1498 p_ni_b_balance.act_info1 := 'B';
1499 p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1500 p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1501 p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
1502 p_ni_b_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uap_id);
1503 p_ni_b_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uel_id);
1504 p_ni_b_balance.act_info7 := 100 * l_nib_r_ytd ;
1505 p_ni_b_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ytd_id);
1506 p_ni_b_balance.act_info9 := 100 * l_nib_r_ptd ;
1507 p_ni_b_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
1508 p_ni_b_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
1509 l_count_values := l_count_values +1;
1510 END IF;
1511 IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 ) or
1512 ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1513 hr_utility.trace('fetching NI C Total/Able balances');
1514 p_ni_c_balance.act_info1 := 'C';
1515 p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1516 p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1517 p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
1518 p_ni_c_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uap_id);
1519 p_ni_c_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uel_id);
1520 p_ni_c_balance.act_info7 := 100 * l_nic_r_ytd ;
1521 p_ni_c_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ytd_id);
1522 p_ni_c_balance.act_info9 := 100 * l_nic_r_ptd ;
1523 p_ni_c_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
1524 p_ni_c_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
1525 l_count_values := l_count_values +1;
1526 END IF;
1527 IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1528 ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1529 hr_utility.trace('fetching NI D Total/Able balances');
1530 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1531 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1532 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1533 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1534 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1535 p_ni_d_balance.act_info1 := 'D';
1536 p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1537 p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1538 p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
1539 p_ni_d_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uap_id);
1540 p_ni_d_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uel_id);
1541 p_ni_d_balance.act_info7 := 100 * l_nid_r_ytd ;
1542 p_ni_d_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ytd_id);
1543 p_ni_d_balance.act_info9 := 100 * l_nid_r_ptd ;
1544 p_ni_d_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
1545 p_ni_d_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
1546 l_count_values := l_count_values +1;
1547 END IF;
1548 IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 ) or
1549 ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1550 hr_utility.trace('fetching NI E Total/Able balances');
1551 p_ni_e_balance.act_info1 := 'E';
1552 p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1553 p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1554 p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
1555 p_ni_e_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uap_id);
1556 p_ni_e_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uel_id);
1557 p_ni_e_balance.act_info7 := 100 * l_nie_r_ytd ;
1558 p_ni_e_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ytd_id);
1559 p_ni_e_balance.act_info9 := 100 * l_nie_r_ptd ;
1560 p_ni_e_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
1561 p_ni_e_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
1562 l_count_values := l_count_values +1;
1563 END IF;
1564 IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 ) or
1565 ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1566 hr_utility.trace('fetching NI J Total/Able balances');
1567 p_ni_j_balance.act_info1 := 'J';
1568 p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1569 p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1570 p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
1571 p_ni_j_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uap_id);
1572 p_ni_j_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uel_id);
1573 p_ni_j_balance.act_info7 := 100 * l_nij_r_ytd ;
1574 p_ni_j_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ytd_id);
1575 p_ni_j_balance.act_info9 := 100 * l_nij_r_ptd ;
1576 p_ni_j_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
1577 p_ni_j_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
1578 l_count_values := l_count_values +1;
1579 END IF;
1580 IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 ) or
1581 ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1582 hr_utility.trace('fetching NI L Total/Able balances');
1583 p_ni_l_balance.act_info1 := 'L';
1584 p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
1585 p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1586 p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
1587 p_ni_l_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uap_id);
1588 p_ni_l_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uel_id);
1589 p_ni_l_balance.act_info7 := 100 * l_nil_r_ytd ;
1590 p_ni_l_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ytd_id);
1591 p_ni_l_balance.act_info9 := 100 * l_nil_r_ptd ;
1592 p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1593 p_ni_l_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
1594 l_count_values := l_count_values +1;
1595 END IF;
1596 hr_utility.trace('Number of categories: ' || l_count_values);
1597 END get_ni_only_bal_rti_rpt;
1598
1599 /*
1600 procedure get_ni_balances_for_asg(
1601 l_last_asg_action_id in number,
1602 p_ni_a_balance in out nocopy action_info_table,
1603 p_ni_b_balance in out nocopy action_info_table,
1604 p_ni_c_balance in out nocopy action_info_table,
1605 p_ni_d_balance in out nocopy action_info_table,
1606 p_ni_e_balance in out nocopy action_info_table,
1607 p_ni_j_balance in out nocopy action_info_table,
1608 p_ni_l_balance in out nocopy action_info_table
1609 )
1610 */
1611
1612 PROCEDURE get_ni_balances_for_asg(
1613 l_last_asg_action_id IN NUMBER,
1614 p_ni_a_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1615 p_ni_b_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1616 p_ni_c_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1617 p_ni_d_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1618 p_ni_e_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1619 p_ni_j_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
1620 P_NI_L_BALANCE IN OUT NOCOPY pay_gb_rti_fps_archive.ACT_INFO_REC )
1621 IS
1622 -- local variables to store NI total and able balances
1623 l_nia_tot NUMBER(15,2) :=0;
1624 l_nia_able NUMBER(15,2) :=0;
1625 l_nib_tot NUMBER(15,2) :=0;
1626 l_nib_able NUMBER(15,2) :=0;
1627 l_nic_tot NUMBER(15,2) :=0;
1628 l_nic_able NUMBER(15,2) :=0;
1629 l_nid_tot NUMBER(15,2) :=0;
1630 l_nid_able NUMBER(15,2) :=0;
1631 l_nie_tot NUMBER(15,2) :=0;
1632 l_nie_able NUMBER(15,2) :=0;
1633 l_nij_tot NUMBER(15,2) :=0;
1634 l_nij_able NUMBER(15,2) :=0;
1635 l_nil_tot NUMBER(15,2) :=0;
1636 l_nil_able NUMBER(15,2) :=0;
1637
1638 l_nia_able_run NUMBER(15,2) :=0;
1639 l_nia_e_ptd NUMBER(15,2) :=0;
1640 l_nib_able_run NUMBER(15,2) :=0;
1641 l_nib_e_ptd NUMBER(15,2) :=0;
1642 l_nic_able_run NUMBER(15,2) :=0;
1643 l_nic_e_ptd NUMBER(15,2) :=0;
1644 l_nid_able_run NUMBER(15,2) :=0;
1645 l_nid_e_ptd NUMBER(15,2) :=0;
1646 l_nie_able_run NUMBER(15,2) :=0;
1647 l_nie_e_ptd NUMBER(15,2) :=0;
1648 l_nij_able_run NUMBER(15,2) :=0;
1649 l_nij_e_ptd NUMBER(15,2) :=0;
1650 l_nil_able_run NUMBER(15,2) :=0;
1651 l_nil_e_ptd NUMBER(15,2) :=0;
1652
1653 l_nia_tot_run NUMBER(15,2) :=0;
1654 l_nia_r_ytd NUMBER(15,2) :=0;
1655 l_nia_r_ptd NUMBER(15,2) :=0;
1656 l_nib_tot_run NUMBER(15,2) :=0;
1657 l_nib_r_ytd NUMBER(15,2) :=0;
1658 l_nib_r_ptd NUMBER(15,2) :=0;
1659 l_nic_tot_run NUMBER(15,2) :=0;
1660 l_nic_r_ytd NUMBER(15,2) :=0;
1661 l_nic_r_ptd NUMBER(15,2) :=0;
1662 l_nid_tot_run NUMBER(15,2) :=0;
1663 l_nid_r_ytd NUMBER(15,2) :=0;
1664 l_nid_r_ptd NUMBER(15,2) :=0;
1665 l_nie_tot_run NUMBER(15,2) :=0;
1666 l_nie_r_ytd NUMBER(15,2) :=0;
1667 l_nie_r_ptd NUMBER(15,2) :=0;
1668 l_nij_tot_run NUMBER(15,2) :=0;
1669 l_nij_r_ytd NUMBER(15,2) :=0;
1670 l_nij_r_ptd NUMBER(15,2) :=0;
1671 l_nil_tot_run NUMBER(15,2) :=0;
1672 l_nil_r_ytd NUMBER(15,2) :=0;
1673 l_nil_r_ptd NUMBER(15,2) :=0;
1674
1675 l_count_values NUMBER := 0;
1676 l_asg_action_id NUMBER;
1677 l_tax_pay_def_bal_id NUMBER;
1678 l_tax_pay_val NUMBER;
1679 l_child_flag VARCHAR2(2) :='N';
1680 l_reversal VARCHAR2(1);
1681
1682 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
1683 IS
1684 SELECT ASSIGNMENT_ACTION_ID
1685 FROM pay_assignment_actions
1686 WHERE SOURCE_ACTION_ID = c_lst_act_id;
1687
1688
1689 CURSOR csr_get_taxable_pay(c_balance_name VARCHAR2, c_dim_name VARCHAR2)
1690 IS
1691 SELECT pdb.defined_balance_id
1692 FROM pay_defined_balances pdb,
1693 pay_balance_dimensions pbd,
1694 pay_balance_types pbt
1695 WHERE pbt.balance_name = c_balance_name
1696 AND pbd.database_item_suffix = c_dim_name
1697 AND pbt.balance_type_id = pdb.balance_type_id
1698 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1699 AND pbt.legislation_code = 'GB';
1700 CURSOR csr_tax_pay_value(p_last_asg_action_id NUMBER,l_tax_pay_def_bal_id NUMBER)
1701 IS
1702 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
1703 FROM dual;
1704
1705 cursor csr_reversal is
1706 select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
1707 where paa.assignment_action_id = l_last_asg_action_id
1708 and paa.payroll_action_id = ppa.payroll_action_id
1709 and ACTION_TYPE in ('V');
1710
1711 BEGIN
1712 hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_balances_for_asg.');
1713 load_defined_balances();
1714 -- fetch NI x Total/Able balances for checking whether this assignment
1715 -- reported in the P35 report or not. -- Bug 6271548
1716 hr_utility.trace('fetching NI x Total/Able balances');
1717
1718 OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1719 FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1720 CLOSE csr_get_taxable_pay;
1721
1722 OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1723 FETCH csr_tax_pay_value INTO l_tax_pay_val;
1724 CLOSE csr_tax_pay_value;
1725
1726 hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1727 -- Test whether we can get run level value with parent action id.
1728 -- If not pass the child assignment action id.
1729
1730 hr_utility.trace('l_child_flag := '||l_child_flag);
1731
1732 IF l_tax_pay_val = 0 THEN
1733 OPEN csr_child_act_id(l_last_asg_action_id);
1734 FETCH csr_child_act_id INTO l_asg_action_id;
1735 CLOSE csr_child_act_id;
1736 l_child_flag := 'Y';
1737 ELSE
1738 l_asg_action_id := l_last_asg_action_id;
1739 l_child_flag := 'N';
1740 END IF;
1741
1742 if l_asg_action_id is null then
1743 l_asg_action_id := l_last_asg_action_id;
1744 l_child_flag := 'N';
1745 end if;
1746
1747 open csr_reversal;
1748 fetch csr_reversal into l_reversal;
1749 close csr_reversal;
1750
1751 if l_child_flag = 'N' then
1752
1753 hr_utility.trace('Processing NI with Master Action ID.');
1754 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1755 -- NI A
1756 l_nia_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
1757 l_nia_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1758 l_nia_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_tot_id_run);
1759 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
1760 l_nia_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ytd_id);
1761 l_nia_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
1762 -- NI B
1763 l_nib_tot := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id);
1764 l_nib_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
1765 l_nib_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id_run);
1766 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
1767 l_nib_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ytd_id);
1768 l_nib_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
1769 -- NI C
1770 l_nic_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nic_tot_id);
1771 l_nic_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
1772 l_nic_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_tot_id_run);
1773 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
1774 l_nic_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ytd_id);
1775 l_nic_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
1776 -- NI D
1777 l_nid_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nid_tot_id);
1778 l_nid_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
1779 l_nid_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_tot_id_run);
1780 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
1781 l_nid_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ytd_id);
1782 l_nid_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
1783 -- NI E
1784 l_nie_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nie_tot_id);
1785 l_nie_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
1786 l_nie_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_tot_id_run);
1787 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
1788 l_nie_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ytd_id);
1789 l_nie_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
1790 -- NI J
1791 l_nij_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nij_tot_id);
1792 l_nij_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
1793 l_nij_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_tot_id_run);
1794 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
1795 l_nij_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ytd_id);
1796 l_nij_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
1797 -- NI L
1798 l_nil_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nil_tot_id);
1799 l_nil_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
1800 l_nil_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_tot_id_run);
1801 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
1802 l_nil_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ytd_id);
1803 l_nil_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
1804
1805 if l_reversal is not null then
1806
1807 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
1808 l_nia_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
1809
1810 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
1811 l_nib_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
1812
1813 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
1814 l_nic_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
1815
1816 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
1817 l_nid_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
1818
1819 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
1820 l_nie_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
1821
1822 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
1823 l_nij_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
1824
1825 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
1826 l_nil_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1827
1828 end if;
1829
1830
1831 IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 ) or
1832 ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1833 hr_utility.trace('fetching NI A Total/Able balances');
1834 p_ni_a_balance.act_info1 := 'A';
1835 p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1836 p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1837 p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
1838 p_ni_a_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uap_id);
1839 p_ni_a_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uel_id);
1840 p_ni_a_balance.act_info7 := 100 * l_nia_r_ytd ;
1841 p_ni_a_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ytd_id);
1842 p_ni_a_balance.act_info9 := 100 * l_nia_r_ptd ;
1843 p_ni_a_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
1844 p_ni_a_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1845 l_count_values := l_count_values +1;
1846 END IF;
1847 IF (NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 ) or
1848 ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1849 hr_utility.trace('fetching NI B Total/Able balances');
1850 p_ni_b_balance.act_info1 := 'B';
1851 p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1852 p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1853 p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
1854 p_ni_b_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uap_id);
1855 p_ni_b_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uel_id);
1856 p_ni_b_balance.act_info7 := 100 * l_nib_r_ytd ;
1857 p_ni_b_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ytd_id);
1858 p_ni_b_balance.act_info9 := 100 * l_nib_r_ptd ;
1859 p_ni_b_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
1860 p_ni_b_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
1861 l_count_values := l_count_values +1;
1862 END IF;
1863 IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 ) or
1864 ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1865 hr_utility.trace('fetching NI C Total/Able balances');
1866 p_ni_c_balance.act_info1 := 'C';
1867 p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1868 p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1869 p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
1870 p_ni_c_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uap_id);
1871 p_ni_c_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uel_id);
1872 p_ni_c_balance.act_info7 := 100 * l_nic_r_ytd ;
1873 p_ni_c_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ytd_id);
1874 p_ni_c_balance.act_info9 := 100 * l_nic_r_ptd ;
1875 p_ni_c_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
1876 p_ni_c_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
1877 l_count_values := l_count_values +1;
1878 END IF;
1879 IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1880 ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1881 hr_utility.trace('fetching NI D Total/Able balances');
1882 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1883 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1884 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1885 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1886 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1887 p_ni_d_balance.act_info1 := 'D';
1888 p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1889 p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1890 p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
1891 p_ni_d_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uap_id);
1892 p_ni_d_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uel_id);
1893 p_ni_d_balance.act_info7 := 100 * l_nid_r_ytd ;
1894 p_ni_d_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ytd_id);
1895 p_ni_d_balance.act_info9 := 100 * l_nid_r_ptd ;
1896 p_ni_d_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
1897 p_ni_d_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
1898 l_count_values := l_count_values +1;
1899 END IF;
1900 IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 ) or
1901 ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1902 hr_utility.trace('fetching NI E Total/Able balances');
1903 p_ni_e_balance.act_info1 := 'E';
1904 p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1905 p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1906 p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
1907 p_ni_e_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uap_id);
1908 p_ni_e_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uel_id);
1909 p_ni_e_balance.act_info7 := 100 * l_nie_r_ytd ;
1910 p_ni_e_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ytd_id);
1911 p_ni_e_balance.act_info9 := 100 * l_nie_r_ptd ;
1912 p_ni_e_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
1913 p_ni_e_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
1914 l_count_values := l_count_values +1;
1915 END IF;
1916 IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 ) or
1917 ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1918 hr_utility.trace('fetching NI J Total/Able balances');
1919 p_ni_j_balance.act_info1 := 'J';
1920 p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1921 p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1922 p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
1923 p_ni_j_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uap_id);
1924 p_ni_j_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uel_id);
1925 p_ni_j_balance.act_info7 := 100 * l_nij_r_ytd ;
1926 p_ni_j_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ytd_id);
1927 p_ni_j_balance.act_info9 := 100 * l_nij_r_ptd ;
1928 p_ni_j_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
1929 p_ni_j_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
1930 l_count_values := l_count_values +1;
1931 END IF;
1932 IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 ) or
1933 ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1934 hr_utility.trace('fetching NI L Total/Able balances');
1935 p_ni_l_balance.act_info1 := 'L';
1936 p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
1937 p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1938 p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
1939 p_ni_l_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uap_id);
1940 p_ni_l_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uel_id);
1941 p_ni_l_balance.act_info7 := 100 * l_nil_r_ytd ;
1942 p_ni_l_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ytd_id);
1943 p_ni_l_balance.act_info9 := 100 * l_nil_r_ptd ;
1944 p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1945 p_ni_l_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
1946 l_count_values := l_count_values +1;
1947 END IF;
1948
1949 ELSE -- If Child Flag is set
1950 p_ni_a_balance.act_info7 := 0;
1951 p_ni_a_balance.act_info8 := 0;
1952 p_ni_a_balance.act_info9 := 0;
1953 p_ni_a_balance.act_info10 := 0;
1954
1955 p_ni_b_balance.act_info7 := 0;
1956 p_ni_b_balance.act_info8 := 0;
1957 p_ni_b_balance.act_info9 := 0;
1958 p_ni_b_balance.act_info10 := 0;
1959
1960
1961 p_ni_c_balance.act_info7 := 0;
1962 p_ni_c_balance.act_info8 := 0;
1963 p_ni_c_balance.act_info9 := 0;
1964 p_ni_c_balance.act_info10 := 0;
1965
1966 p_ni_d_balance.act_info7 := 0;
1967 p_ni_d_balance.act_info8 := 0;
1968 p_ni_d_balance.act_info9 := 0;
1969 p_ni_d_balance.act_info10 := 0;
1970
1971
1972 p_ni_e_balance.act_info7 := 0;
1973 p_ni_e_balance.act_info8 := 0;
1974 p_ni_e_balance.act_info9 := 0;
1975 p_ni_e_balance.act_info10 := 0;
1976
1977
1978 p_ni_j_balance.act_info7 := 0;
1979 p_ni_j_balance.act_info8 := 0;
1980 p_ni_j_balance.act_info9 := 0;
1981 p_ni_j_balance.act_info10 := 0;
1982
1983
1984 p_ni_l_balance.act_info7 := 0;
1985 p_ni_l_balance.act_info8 := 0;
1986 p_ni_l_balance.act_info9 := 0;
1987 p_ni_l_balance.act_info10 := 0;
1988
1989 for child in csr_child_act_id(l_last_asg_action_id)
1990 loop
1991
1992 l_asg_action_id := child.ASSIGNMENT_ACTION_ID;
1993 l_count_values :=0;
1994
1995 hr_utility.trace('Processing NI with Child Action ID.');
1996 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1997
1998 -- NI A
1999 l_nia_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_tot_id);
2000 l_nia_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_able_id);
2001 l_nia_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_tot_id_run);
2002 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2003 l_nia_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ytd_id);
2004 l_nia_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
2005 -- NI B
2006 l_nib_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_tot_id);
2007 l_nib_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_able_id);
2008 l_nib_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id_run);
2009 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2010 l_nib_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ytd_id);
2011 l_nib_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
2012 -- NI C
2013 l_nic_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nic_tot_id);
2014 l_nic_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_able_id);
2015 l_nic_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_tot_id_run);
2016 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2017 l_nic_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ytd_id);
2018 l_nic_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
2019 -- NI D
2020 l_nid_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nid_tot_id);
2021 l_nid_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_able_id);
2022 l_nid_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_tot_id_run);
2023 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2024 l_nid_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ytd_id);
2025 l_nid_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
2026 -- NI E
2027 l_nie_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_tot_id);
2028 l_nie_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_able_id);
2029 l_nie_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_tot_id_run);
2030 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2031 l_nie_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ytd_id);
2032 l_nie_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
2033 -- NI J
2034 l_nij_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nij_tot_id);
2035 l_nij_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_able_id);
2036 l_nij_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_tot_id_run);
2037 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2038 l_nij_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ytd_id);
2039 l_nij_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
2040 -- NI L
2041 l_nil_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nil_tot_id);
2042 l_nil_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_able_id);
2043 l_nil_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_tot_id_run);
2044 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2045 l_nil_r_ytd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ytd_id);
2046 l_nil_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2047
2048
2049 IF NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 THEN
2050 hr_utility.trace('fetching NI A Total/Able balances');
2051 p_ni_a_balance.act_info1 := 'A';
2052 p_ni_a_balance.act_info2 := 100 * l_nia_able_run;
2053 p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
2054 p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
2055 p_ni_a_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uap_id);
2056 p_ni_a_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_uel_id);
2057 p_ni_a_balance.act_info7 := 100 * l_nia_r_ytd;
2058 p_ni_a_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ytd_id);
2059 p_ni_a_balance.act_info9 := p_ni_a_balance.act_info9 + (100 * l_nia_r_ptd);
2060 p_ni_a_balance.act_info10 := p_ni_a_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id));
2061 p_ni_a_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
2062 l_count_values := l_count_values +1;
2063 END IF;
2064 IF NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 THEN
2065 hr_utility.trace('fetching NI B Total/Able balances');
2066 p_ni_b_balance.act_info1 := 'B';
2067 p_ni_b_balance.act_info2 := 100 * l_nib_able_run;
2068 p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
2069 p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
2070 p_ni_b_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uap_id);
2071 p_ni_b_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_uel_id);
2072 p_ni_b_balance.act_info7 := 100 * l_nib_r_ytd;
2073 p_ni_b_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ytd_id);
2074 p_ni_b_balance.act_info9 := p_ni_b_balance.act_info9 + (100 * l_nib_r_ptd);
2075 p_ni_b_balance.act_info10 := p_ni_b_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id));
2076 p_ni_b_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
2077 l_count_values := l_count_values +1;
2078 END IF;
2079 IF NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 THEN
2080 hr_utility.trace('fetching NI C Total/Able balances');
2081 p_ni_c_balance.act_info1 := 'C';
2082 p_ni_c_balance.act_info2 := 100 * l_nic_able_run;
2083 p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
2084 p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
2085 p_ni_c_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uap_id);
2086 p_ni_c_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_uel_id);
2087 p_ni_c_balance.act_info7 := 100 * l_nic_r_ytd;
2088 p_ni_c_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ytd_id);
2089 p_ni_c_balance.act_info9 := p_ni_c_balance.act_info9 + (100 * l_nic_r_ptd);
2090 p_ni_c_balance.act_info10 := p_ni_c_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id));
2091 p_ni_c_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
2092 l_count_values := l_count_values +1;
2093 END IF;
2094 IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 THEN
2095 hr_utility.trace('fetching NI D Total/Able balances');
2096 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2097 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2098 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2099 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2100 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2101 p_ni_d_balance.act_info1 := 'D';
2102 p_ni_d_balance.act_info2 := 100 * l_nid_able_run;
2103 p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2104 p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
2105 p_ni_d_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uap_id);
2106 p_ni_d_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_uel_id);
2107 p_ni_d_balance.act_info7 := 100 * l_nid_r_ytd;
2108 p_ni_d_balance.act_info8 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_e_ytd_id);
2109 p_ni_d_balance.act_info9 := p_ni_d_balance.act_info9 + (100 * l_nid_r_ptd);
2110 p_ni_d_balance.act_info10 := p_ni_d_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id));
2111 p_ni_d_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
2112 hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2113 hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2114 hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2115 hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2116
2117 l_count_values := l_count_values +1;
2118 END IF;
2119 IF NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 THEN
2120 hr_utility.trace('fetching NI E Total/Able balances');
2121 p_ni_e_balance.act_info1 := 'E';
2122 p_ni_e_balance.act_info2 := 100 * l_nie_able_run;
2123 p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
2124 p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
2125 p_ni_e_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uap_id);
2126 p_ni_e_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_uel_id);
2127 p_ni_e_balance.act_info7 := 100 * l_nie_r_ytd ;
2128 p_ni_e_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ytd_id);
2129 p_ni_e_balance.act_info9 := p_ni_e_balance.act_info9 + (100 * l_nie_r_ptd);
2130 p_ni_e_balance.act_info10 := p_ni_e_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id));
2131 p_ni_e_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
2132 l_count_values := l_count_values +1;
2133 END IF;
2134 IF NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 THEN
2135 hr_utility.trace('fetching NI J Total/Able balances');
2136 p_ni_j_balance.act_info1 := 'J';
2137 p_ni_j_balance.act_info2 := 100 * l_nij_able_run;
2138 p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
2139 p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
2140 p_ni_j_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uap_id);
2141 p_ni_j_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_uel_id);
2142 p_ni_j_balance.act_info7 := 100 * l_nij_r_ytd ;
2143 p_ni_j_balance.act_info8 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ytd_id);
2144 p_ni_j_balance.act_info9 := p_ni_j_balance.act_info9 + (100 * l_nij_r_ptd);
2145 p_ni_j_balance.act_info10 := p_ni_j_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id));
2146 p_ni_j_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
2147 l_count_values := l_count_values +1;
2148 END IF;
2149 IF NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 THEN
2150 hr_utility.trace('fetching NI L Total/Able balances');
2151 p_ni_l_balance.act_info1 := 'L';
2152 p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
2153 p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
2154 p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
2155 p_ni_l_balance.act_info5 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uap_id);
2156 p_ni_l_balance.act_info6 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_uel_id);
2157 p_ni_l_balance.act_info7 := 100 * l_nil_r_ytd ;
2158 p_ni_l_balance.act_info8 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_e_ytd_id);
2159 p_ni_l_balance.act_info9 := p_ni_l_balance.act_info9 + (100 * l_nil_r_ptd);
2160 p_ni_l_balance.act_info10 := p_ni_l_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id));
2161 p_ni_l_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
2162 l_count_values := l_count_values +1;
2163 END IF;
2164 end loop; -- Child Loop Ends
2165 END IF; -- If Ends for Child flag
2166
2167 hr_utility.trace('Number of categories: ' || l_count_values);
2168
2169
2170 END get_ni_balances_for_asg;
2171
2172
2173 -- Uses the new route to get the NI YTD figures when an assignment is terminated.
2174 function get_ni_only_agg_route_ytd(f_asg_act_id number,f_balance_name varchar2) return number
2175 is
2176 L_bal_type_id number;
2177 L_value number;
2178 begin
2179
2180 hr_utility.trace('f_asg_act_id :' || f_asg_act_id);
2181 hr_utility.trace('f_balance_name : '|| f_balance_name);
2182
2183 select balance_type_id into l_bal_type_id from pay_balance_types where balance_name = f_balance_name
2184 and legislation_code = 'GB';
2185
2186 hr_utility.trace('l_bal_type_id :' || l_bal_type_id);
2187
2188 select sum(target.result_value)
2189 into
2190 l_value
2191 from
2192 pay_assignment_actions BAL_ASSACT
2193 ,pay_payroll_actions BACT
2194 ,per_time_periods BPTP
2195 ,pay_assignment_actions ASSACT
2196 ,pay_payroll_actions PACT
2197 ,per_time_periods PPTP
2198 ,pay_run_results RR
2199 ,pay_run_result_values TARGET
2200 ,pay_balance_feeds_f FEED
2201 where BAL_ASSACT.assignment_action_id = f_asg_act_id
2202 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
2203 and FEED.balance_type_id = l_bal_type_id + decode(TARGET.input_value_id, 0, 0, 0)
2204 and FEED.input_value_id = TARGET.input_value_id
2205 and TARGET.run_result_id = RR.run_result_id
2206 and RR.assignment_action_id = ASSACT.assignment_action_id
2207 and ASSACT.payroll_action_id = PACT.payroll_action_id
2208 and PACT.effective_date between
2209 FEED.effective_start_date and FEED.effective_end_date
2210 and RR.status in ('P','PA')
2211 and BPTP.time_period_id = BACT.time_period_id
2212 and PPTP.time_period_id = PACT.time_period_id
2213 and PPTP.regular_payment_date >= /* fin year start */
2214 ( to_date('06-04-' || to_char( to_number(
2215 to_char( BPTP.regular_payment_date,'YYYY'))
2216 + decode(sign( BPTP.regular_payment_date - to_date('06-04-'
2217 || to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
2218 -1,-1,0)),'DD-MM-YYYY'))
2219
2220 and PPTP.regular_payment_date >= /* Termination check for RTI NI Reporting assignment. */
2221 ( nvl(
2222 (select max(fnd_date.canonical_to_date(px.AEI_INFORMATION2)) from per_all_assignments_f paa1, per_all_assignments_f paa2,
2223 per_assignment_extra_info PX
2224 where paa1.assignment_id = BAL_ASSACT.assignment_id
2225 and paa1.person_id = paa2.person_id
2226 and PX.assignment_id = paa2.assignment_id
2227 and PX.AEI_INFORMATION1 = 'Y'
2228 and PX.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
2229 and paa2.ASSIGNMENT_STATUS_TYPE_ID = (select ASSIGNMENT_STATUS_TYPE_ID from per_assignment_status_types
2230 where USER_STATUS = 'Terminate Assignment')
2231 and fnd_date.canonical_to_date(PX.AEI_INFORMATION2) between ( to_date('06-04-' || to_char( to_number(
2232 to_char( BPTP.regular_payment_date,'YYYY'))
2233 + decode(sign( BPTP.regular_payment_date - to_date('06-04-'
2234 || to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
2235 -1,-1,0)),'DD-MM-YYYY')) and BPTP.regular_payment_date ), PPTP.regular_payment_date)
2236
2237 )
2238 and PACT.effective_date >=
2239 /* find the latest td payroll transfer date - compare each of the */
2240 /* assignment rows with its predecessor looking for the payroll */
2241 /* that had a different tax district at that date */
2242 ( select /*+ ordered use_nl(ASS PASS NROLL FLEX PROLL PFLEX)
2243 INDEX(NROLL PAY_PAYROLLS_F_PK)
2244 INDEX(PROLL PAY_PAYROLLS_F_PK) */
2245 nvl(max(ASS.effective_start_date),
2246 to_date('01/01/0001','DD/MM/YYYY'))
2247 from per_assignments_f ASS
2248 ,per_assignments_f PASS /* previous assignment */
2249 ,pay_payrolls_f NROLL
2250 ,hr_soft_coding_keyflex FLEX
2251 ,pay_payrolls_f PROLL
2252 ,hr_soft_coding_keyflex PFLEX
2253 where ASS.assignment_id = BAL_ASSACT.assignment_id
2254 and NROLL.payroll_id = ASS.payroll_id
2255 and ASS.effective_start_date between
2256 NROLL.effective_start_date and NROLL.effective_end_date
2257 and NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
2258 and ASS.assignment_id = PASS.assignment_id
2259 and PASS.effective_end_date = (ASS.effective_start_date - 1)
2260 and ASS.effective_start_date <= BACT.effective_date
2261 and PROLL.payroll_id = PASS.payroll_id
2262 and ASS.effective_start_date between
2263 PROLL.effective_start_date and PROLL.effective_end_date
2264 and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
2265 and ASS.payroll_id <> PASS.payroll_id
2266 and FLEX.segment1 <> PFLEX.segment1
2267 )
2268 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
2269 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
2270
2271 hr_utility.trace('l_value :' || l_value);
2272
2273 return l_value;
2274
2275 exception
2276 when others then
2277 hr_utility.trace('Exception in get_ni_only_agg_route_ytd: ' || SQLCODE || SQLERRM );
2278 return 0;-- returning zero on exception
2279
2280 end get_ni_only_agg_route_ytd;
2281
2282 --
2283
2284 -- For aggregated assginments
2285 PROCEDURE get_ni_only_agg_bal_for_asg(
2286 l_last_asg_action_id IN NUMBER,
2287 p_ni_a_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2288 p_ni_b_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2289 p_ni_c_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2290 p_ni_d_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2291 p_ni_e_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2292 p_ni_j_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2293 P_NI_L_BALANCE IN OUT NOCOPY pay_gb_rti_fps_archive.ACT_INFO_REC )
2294 IS
2295 -- local variables to store NI total and able balances
2296 l_nia_tot NUMBER(15,2) :=0;
2297 l_nia_able NUMBER(15,2) :=0;
2298 l_nib_tot NUMBER(15,2) :=0;
2299 l_nib_able NUMBER(15,2) :=0;
2300 l_nic_tot NUMBER(15,2) :=0;
2301 l_nic_able NUMBER(15,2) :=0;
2302 l_nid_tot NUMBER(15,2) :=0;
2303 l_nid_able NUMBER(15,2) :=0;
2304 l_nie_tot NUMBER(15,2) :=0;
2305 l_nie_able NUMBER(15,2) :=0;
2306 l_nij_tot NUMBER(15,2) :=0;
2307 l_nij_able NUMBER(15,2) :=0;
2308 l_nil_tot NUMBER(15,2) :=0;
2309 l_nil_able NUMBER(15,2) :=0;
2310
2311 l_nia_able_run NUMBER(15,2) :=0;
2312 l_nia_e_ptd NUMBER(15,2) :=0;
2313 l_nib_able_run NUMBER(15,2) :=0;
2314 l_nib_e_ptd NUMBER(15,2) :=0;
2315 l_nic_able_run NUMBER(15,2) :=0;
2316 l_nic_e_ptd NUMBER(15,2) :=0;
2317 l_nid_able_run NUMBER(15,2) :=0;
2318 l_nid_e_ptd NUMBER(15,2) :=0;
2319 l_nie_able_run NUMBER(15,2) :=0;
2320 l_nie_e_ptd NUMBER(15,2) :=0;
2321 l_nij_able_run NUMBER(15,2) :=0;
2322 l_nij_e_ptd NUMBER(15,2) :=0;
2323 l_nil_able_run NUMBER(15,2) :=0;
2324 l_nil_e_ptd NUMBER(15,2) :=0;
2325
2326 l_nia_tot_run NUMBER(15,2) :=0;
2327 l_nia_r_ytd NUMBER(15,2) :=0;
2328 l_nia_r_ptd NUMBER(15,2) :=0;
2329 l_nib_tot_run NUMBER(15,2) :=0;
2330 l_nib_r_ytd NUMBER(15,2) :=0;
2331 l_nib_r_ptd NUMBER(15,2) :=0;
2332 l_nic_tot_run NUMBER(15,2) :=0;
2333 l_nic_r_ytd NUMBER(15,2) :=0;
2334 l_nic_r_ptd NUMBER(15,2) :=0;
2335 l_nid_tot_run NUMBER(15,2) :=0;
2336 l_nid_r_ytd NUMBER(15,2) :=0;
2337 l_nid_r_ptd NUMBER(15,2) :=0;
2338 l_nie_tot_run NUMBER(15,2) :=0;
2339 l_nie_r_ytd NUMBER(15,2) :=0;
2340 l_nie_r_ptd NUMBER(15,2) :=0;
2341 l_nij_tot_run NUMBER(15,2) :=0;
2342 l_nij_r_ytd NUMBER(15,2) :=0;
2343 l_nij_r_ptd NUMBER(15,2) :=0;
2344 l_nil_tot_run NUMBER(15,2) :=0;
2345 l_nil_r_ytd NUMBER(15,2) :=0;
2346 l_nil_r_ptd NUMBER(15,2) :=0;
2347
2348 l_count_values NUMBER := 0;
2349 l_asg_action_id NUMBER;
2350 l_tax_pay_def_bal_id NUMBER;
2351 l_tax_pay_val NUMBER;
2352 l_reversal VARCHAR2(1);
2353
2354 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
2355 IS
2356 SELECT ASSIGNMENT_ACTION_ID
2357 FROM pay_assignment_actions
2358 WHERE SOURCE_ACTION_ID = c_lst_act_id;
2359 CURSOR csr_get_taxable_pay(c_balance_name VARCHAR2, c_dim_name VARCHAR2)
2360 IS
2361 SELECT pdb.defined_balance_id
2362 FROM pay_defined_balances pdb,
2363 pay_balance_dimensions pbd,
2364 pay_balance_types pbt
2365 WHERE pbt.balance_name = c_balance_name
2366 AND pbd.database_item_suffix = c_dim_name
2367 AND pbt.balance_type_id = pdb.balance_type_id
2368 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2369 AND pbt.legislation_code = 'GB';
2370 CURSOR csr_tax_pay_value(p_last_asg_action_id NUMBER,l_tax_pay_def_bal_id NUMBER)
2371 IS
2372 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
2373 FROM dual;
2374
2375 cursor csr_reversal is
2376 select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
2377 where paa.assignment_action_id = l_last_asg_action_id
2378 and paa.payroll_action_id = ppa.payroll_action_id
2379 and ACTION_TYPE in ('V');
2380
2381 BEGIN
2382 hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_for_asg.');
2383 load_defined_balances();
2384 -- fetch NI x Total/Able balances for checking whether this assignment
2385 -- reported in the P35 report or not. -- Bug 6271548
2386 hr_utility.trace('fetching NI x Total/Able balances');
2387 OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2388 FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2389 CLOSE csr_get_taxable_pay;
2390 OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2391 FETCH csr_tax_pay_value INTO l_tax_pay_val;
2392 CLOSE csr_tax_pay_value;
2393 hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2394 -- Test whether we can get run level value with parent action id.
2395 -- If not pass the child assignment action id.
2396 IF l_tax_pay_val = 0 THEN
2397 OPEN csr_child_act_id(l_last_asg_action_id);
2398 FETCH csr_child_act_id INTO l_asg_action_id;
2399 CLOSE csr_child_act_id;
2400 ELSE
2401 l_asg_action_id := l_last_asg_action_id;
2402 END IF;
2403
2404 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2405 if l_asg_action_id is null then
2406 l_asg_action_id := l_last_asg_action_id;
2407 end if;
2408
2409 open csr_reversal;
2410 fetch csr_reversal into l_reversal;
2411 close csr_reversal;
2412
2413 -- NI A
2414 l_nia_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id,'NI A Total');
2415 l_nia_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI A Able');
2416 l_nia_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_tot_id_run);
2417 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2418 l_nia_r_ytd := get_ni_only_agg_route_ytd(l_asg_action_id, 'NI A Employer');
2419 l_nia_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
2420 -- NI B
2421 l_nib_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI B Total');
2422 l_nib_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI B Able');
2423 l_nib_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_tot_id_run);
2424 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2425 l_nib_r_ytd := get_ni_only_agg_route_ytd(l_asg_action_id, 'NI B Employer');
2426 l_nib_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
2427 -- NI C
2428 l_nic_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id,'NI C Total');
2429 l_nic_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI C Able');
2430 l_nic_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_tot_id_run);
2431 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2432 l_nic_r_ytd := get_ni_only_agg_route_ytd(l_asg_action_id, 'NI C Employer');
2433 l_nic_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
2434 -- NI D
2435 l_nid_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI D Total');
2436 l_nid_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI D Able');
2437 l_nid_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_tot_id_run);
2438 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2439 l_nid_r_ytd := get_ni_only_agg_route_ytd(l_asg_action_id, 'NI D Employer');
2440 l_nid_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
2441 -- NI E
2442 l_nie_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI E Total');
2443 l_nie_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI E Able');
2444 l_nie_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_tot_id_run);
2445 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2446 l_nie_r_ytd := get_ni_only_agg_route_ytd(l_asg_action_id, 'NI E Employer');
2447 l_nie_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
2448 -- NI J
2449 l_nij_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI J Total');
2450 l_nij_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI J Able');
2451 l_nij_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_tot_id_run);
2452 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2453 l_nij_r_ytd := get_ni_only_agg_route_ytd(l_asg_action_id, 'NI J Employer');
2454 l_nij_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
2455 -- NI L
2456 l_nil_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Total');
2457 l_nil_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Able');
2458 l_nil_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_tot_id_run);
2459 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2460 l_nil_r_ytd := get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Employer');
2461 l_nil_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2462
2463 if l_reversal is not null then
2464
2465 l_nia_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2466 l_nia_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2467
2468 l_nib_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2469 l_nib_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2470
2471 l_nic_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2472 l_nic_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2473
2474 l_nid_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2475 l_nid_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2476
2477 l_nie_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2478 l_nie_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2479
2480 l_nij_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2481 l_nij_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2482
2483 l_nil_able_run := hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2484 l_nil_e_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2485
2486 end if;
2487
2488 IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 ) or
2489 ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
2490 hr_utility.trace('fetching NI A Total/Able balances');
2491 p_ni_a_balance.act_info1 := 'A';
2492 p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
2493 p_ni_a_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able LEL');
2494 p_ni_a_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able ET');
2495 p_ni_a_balance.act_info5 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able UAP');
2496 p_ni_a_balance.act_info6 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able UEL');
2497 p_ni_a_balance.act_info7 := 100 * l_nia_r_ytd ;
2498 p_ni_a_balance.act_info8 := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI A Employee');
2499 p_ni_a_balance.act_info9 := 100 * l_nia_r_ptd ;
2500 p_ni_a_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2501 p_ni_a_balance.act_info11 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able');
2502 l_count_values := l_count_values +1;
2503 END IF;
2504 IF (NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 ) or
2505 ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
2506 hr_utility.trace('fetching NI B Total/Able balances');
2507 p_ni_b_balance.act_info1 := 'B';
2508 p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
2509 p_ni_b_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able LEL');
2510 p_ni_b_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able ET');
2511 p_ni_b_balance.act_info5 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able UAP');
2512 p_ni_b_balance.act_info6 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able UEL');
2513 p_ni_b_balance.act_info7 := 100 * l_nib_r_ytd ;
2514 p_ni_b_balance.act_info8 := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI B Employee');
2515 p_ni_b_balance.act_info9 := 100 * l_nib_r_ptd ;
2516 p_ni_b_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2517 p_ni_b_balance.act_info11 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able');
2518 l_count_values := l_count_values +1;
2519 END IF;
2520 IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 ) or
2521 ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
2522 hr_utility.trace('fetching NI C Total/Able balances');
2523 p_ni_c_balance.act_info1 := 'C';
2524 p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
2525 p_ni_c_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able LEL');
2526 p_ni_c_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able ET');
2527 p_ni_c_balance.act_info5 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able UAP');
2528 p_ni_c_balance.act_info6 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able UEL');
2529 p_ni_c_balance.act_info7 := 100 * l_nic_r_ytd ;
2530 p_ni_c_balance.act_info8 := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI C Employee');
2531 p_ni_c_balance.act_info9 := 100 * l_nic_r_ptd ;
2532 p_ni_c_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2533 p_ni_c_balance.act_info11 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able');
2534 l_count_values := l_count_values +1;
2535 END IF;
2536 IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
2537 ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2538 hr_utility.trace('fetching NI D Total/Able balances');
2539 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2540 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2541 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2542 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2543 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2544 p_ni_d_balance.act_info1 := 'D';
2545 p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2546 p_ni_d_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able LEL');
2547 p_ni_d_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able ET');
2548 p_ni_d_balance.act_info5 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able UAP');
2549 p_ni_d_balance.act_info6 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able UEL');
2550 p_ni_d_balance.act_info7 := 100 * l_nid_r_ytd ;
2551 p_ni_d_balance.act_info8 := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI D Employee');
2552 p_ni_d_balance.act_info9 := 100 * l_nid_r_ptd ;
2553 p_ni_d_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2554 p_ni_d_balance.act_info11 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able');
2555 l_count_values := l_count_values +1;
2556 END IF;
2557 IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 ) or
2558 ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
2559 hr_utility.trace('fetching NI E Total/Able balances');
2560 p_ni_e_balance.act_info1 := 'E';
2561 p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
2562 p_ni_e_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able LEL');
2563 p_ni_e_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able ET');
2564 p_ni_e_balance.act_info5 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able UAP');
2565 p_ni_e_balance.act_info6 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able UEL');
2566 p_ni_e_balance.act_info7 := 100 * l_nie_r_ytd ;
2567 p_ni_e_balance.act_info8 := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI E Employee');
2568 p_ni_e_balance.act_info9 := 100 * l_nie_r_ptd ;
2569 p_ni_e_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2570 p_ni_e_balance.act_info11 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able');
2571 l_count_values := l_count_values +1;
2572 END IF;
2573 IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 ) or
2574 ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
2575 hr_utility.trace('fetching NI J Total/Able balances');
2576 p_ni_j_balance.act_info1 := 'J';
2577 p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
2578 p_ni_j_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able LEL');
2579 p_ni_j_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able ET');
2580 p_ni_j_balance.act_info5 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able UAP');
2581 p_ni_j_balance.act_info6 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able UEL');
2582 p_ni_j_balance.act_info7 := 100 * l_nij_r_ytd ;
2583 p_ni_j_balance.act_info8 := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI J Employee');
2584 p_ni_j_balance.act_info9 := 100 * l_nij_r_ptd ;
2585 p_ni_j_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2586 p_ni_j_balance.act_info11 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able');
2587 l_count_values := l_count_values +1;
2588 END IF;
2589 IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 ) or
2590 ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
2591 hr_utility.trace('fetching NI L Total/Able balances');
2592 p_ni_l_balance.act_info1 := 'L';
2593 p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
2594 p_ni_l_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able LEL');
2595 p_ni_l_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able ET');
2596 p_ni_l_balance.act_info5 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able UAP');
2597 p_ni_l_balance.act_info6 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able UEL');
2598 p_ni_l_balance.act_info7 := 100 * l_nil_r_ytd ;
2599 p_ni_l_balance.act_info8 := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Employee');
2600 p_ni_l_balance.act_info9 := 100 * l_nil_r_ptd ;
2601 p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2602 p_ni_l_balance.act_info11 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able');
2603 l_count_values := l_count_values +1;
2604 END IF;
2605 hr_utility.trace('Number of categories: ' || l_count_values);
2606 END get_ni_only_agg_bal_for_asg;
2607
2608
2609 -- new cursor for NI only aggregation case:
2610 PROCEDURE get_ni_only_agg_bal_sum_asg(
2611 l_last_asg_action_id IN NUMBER,
2612 p_ni_a_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2613 p_ni_b_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2614 p_ni_c_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2615 p_ni_d_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2616 p_ni_e_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2617 p_ni_j_balance IN OUT nocopy pay_gb_rti_fps_archive.act_info_rec,
2618 P_NI_L_BALANCE IN OUT NOCOPY pay_gb_rti_fps_archive.ACT_INFO_REC )
2619 IS
2620 -- local variables to store NI total and able balances
2621 l_nia_tot NUMBER(15,2) :=0;
2622 l_nia_able NUMBER(15,2) :=0;
2623 l_nib_tot NUMBER(15,2) :=0;
2624 l_nib_able NUMBER(15,2) :=0;
2625 l_nic_tot NUMBER(15,2) :=0;
2626 l_nic_able NUMBER(15,2) :=0;
2627 l_nid_tot NUMBER(15,2) :=0;
2628 l_nid_able NUMBER(15,2) :=0;
2629 l_nie_tot NUMBER(15,2) :=0;
2630 l_nie_able NUMBER(15,2) :=0;
2631 l_nij_tot NUMBER(15,2) :=0;
2632 l_nij_able NUMBER(15,2) :=0;
2633 l_nil_tot NUMBER(15,2) :=0;
2634 l_nil_able NUMBER(15,2) :=0;
2635 l_count_values NUMBER := 0;
2636 l_asg_action_id NUMBER;
2637 l_tax_pay_def_bal_id NUMBER;
2638 l_tax_pay_val NUMBER;
2639 CURSOR csr_child_act_id(c_lst_act_id NUMBER)
2640 IS
2641 SELECT ASSIGNMENT_ACTION_ID
2642 FROM pay_assignment_actions
2643 WHERE SOURCE_ACTION_ID = c_lst_act_id;
2644 CURSOR csr_get_taxable_pay(c_balance_name VARCHAR2, c_dim_name VARCHAR2)
2645 IS
2646 SELECT pdb.defined_balance_id
2647 FROM pay_defined_balances pdb,
2648 pay_balance_dimensions pbd,
2649 pay_balance_types pbt
2650 WHERE pbt.balance_name = c_balance_name
2651 AND pbd.database_item_suffix = c_dim_name
2652 AND pbt.balance_type_id = pdb.balance_type_id
2653 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2654 AND pbt.legislation_code = 'GB';
2655 CURSOR csr_tax_pay_value(p_last_asg_action_id NUMBER,l_tax_pay_def_bal_id NUMBER)
2656 IS
2657 SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
2658 FROM dual;
2659 BEGIN
2660 hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_sum_asg.');
2661 load_defined_balances();
2662 -- fetch NI x Total/Able balances for checking whether this assignment
2663 -- reported in the P35 report or not. -- Bug 6271548
2664 hr_utility.trace('fetching NI x Total/Able balances');
2665 OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2666 FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2667 CLOSE csr_get_taxable_pay;
2668 OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2669 FETCH csr_tax_pay_value INTO l_tax_pay_val;
2670 CLOSE csr_tax_pay_value;
2671 hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2672 -- Test whether we can get run level value with parent action id.
2673 -- If not pass the child assignment action id.
2674 IF l_tax_pay_val = 0 THEN
2675 OPEN csr_child_act_id(l_last_asg_action_id);
2676 FETCH csr_child_act_id INTO l_asg_action_id;
2677 CLOSE csr_child_act_id;
2678 ELSE
2679 l_asg_action_id := l_last_asg_action_id;
2680 END IF;
2681
2682 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2683 if l_asg_action_id is null then
2684 l_asg_action_id := l_last_asg_action_id;
2685 end if;
2686 -- NI A
2687 l_nia_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id,'NI A Total');
2688 l_nia_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI A Able');
2689 -- NI B
2690 l_nib_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI B Total');
2691 l_nib_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI B Able');
2692 -- NI C
2693 l_nic_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id,'NI C Total');
2694 l_nic_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI C Able');
2695 -- NI D
2696 l_nid_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI D Total');
2697 l_nid_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI D Able');
2698 -- NI E
2699 l_nie_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI E Total');
2700 l_nie_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI E Able');
2701 -- NI J
2702 l_nij_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI J Total');
2703 l_nij_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI J Able');
2704 -- NI L
2705 l_nil_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Total');
2706 l_nil_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Able');
2707 IF NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 THEN
2708 hr_utility.trace('fetching NI A Total/Able balances');
2709 p_ni_a_balance.act_info1 := 'A';
2710 -- p_ni_a_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2711 p_ni_a_balance.act_info3 := nvl(p_ni_a_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able LEL');
2712 p_ni_a_balance.act_info4 := nvl(p_ni_a_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able ET');
2713 p_ni_a_balance.act_info5 := nvl(p_ni_a_balance.act_info5,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able UAP');
2714 p_ni_a_balance.act_info6 := nvl(p_ni_a_balance.act_info6,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able UEL');
2715 p_ni_a_balance.act_info7 := nvl(p_ni_a_balance.act_info7,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI A Employer');
2716 p_ni_a_balance.act_info8 := nvl(p_ni_a_balance.act_info8,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI A Employee');
2717 -- p_ni_a_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_r_ptd_id);
2718 -- p_ni_a_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2719 l_count_values := l_count_values +1;
2720 END IF;
2721 IF NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 THEN
2722 hr_utility.trace('fetching NI B Total/Able balances');
2723 p_ni_b_balance.act_info1 := 'B';
2724 -- p_ni_b_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2725 p_ni_b_balance.act_info3 := nvl(p_ni_b_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able LEL');
2726 p_ni_b_balance.act_info4 := nvl(p_ni_b_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able ET');
2727 p_ni_b_balance.act_info5 := nvl(p_ni_b_balance.act_info5,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able UAP');
2728 p_ni_b_balance.act_info6 := nvl(p_ni_b_balance.act_info6,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able UEL');
2729 p_ni_b_balance.act_info7 := nvl(p_ni_b_balance.act_info7,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI B Employer');
2730 p_ni_b_balance.act_info8 := nvl(p_ni_b_balance.act_info8,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI B Employee');
2731 --p_ni_b_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_r_ptd_id);
2732 --p_ni_b_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2733 l_count_values := l_count_values +1;
2734 END IF;
2735 IF NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 THEN
2736 hr_utility.trace('fetching NI C Total/Able balances');
2737 p_ni_c_balance.act_info1 := 'C';
2738 -- p_ni_c_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2739 p_ni_c_balance.act_info3 := nvl(p_ni_c_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able LEL');
2740 p_ni_c_balance.act_info4 := nvl(p_ni_c_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able ET');
2741 p_ni_c_balance.act_info5 := nvl(p_ni_c_balance.act_info5,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able UAP');
2742 p_ni_c_balance.act_info6 := nvl(p_ni_c_balance.act_info6,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able UEL');
2743 p_ni_c_balance.act_info7 := nvl(p_ni_c_balance.act_info7,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI C Employer');
2744 p_ni_c_balance.act_info8 := nvl(p_ni_c_balance.act_info8,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI C Employee');
2745 --p_ni_c_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_r_ptd_id);
2746 --p_ni_c_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2747 l_count_values := l_count_values +1;
2748 END IF;
2749 IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2750 hr_utility.trace('fetching NI D Total/Able balances');
2751 hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2752 hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2753 hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2754 hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2755 hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2756 p_ni_d_balance.act_info1 := 'D';
2757 -- p_ni_d_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2758 p_ni_d_balance.act_info3 := nvl(p_ni_d_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able LEL');
2759 p_ni_d_balance.act_info4 := nvl(p_ni_d_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able ET');
2760 p_ni_d_balance.act_info5 := nvl(p_ni_d_balance.act_info5,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able UAP');
2761 p_ni_d_balance.act_info6 := nvl(p_ni_d_balance.act_info6,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able UEL');
2762 p_ni_d_balance.act_info7 := nvl(p_ni_d_balance.act_info7,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI D Employer');
2763 p_ni_d_balance.act_info8 := nvl(p_ni_d_balance.act_info8,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI D Employee');
2764 --p_ni_d_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_r_ptd_id);
2765 --p_ni_d_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2766 l_count_values := l_count_values +1;
2767 END IF;
2768 IF NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 THEN
2769 hr_utility.trace('fetching NI E Total/Able balances');
2770 p_ni_e_balance.act_info1 := 'E';
2771 -- p_ni_e_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2772 p_ni_e_balance.act_info3 := nvl(p_ni_e_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able LEL');
2773 p_ni_e_balance.act_info4 := nvl(p_ni_e_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able ET');
2774 p_ni_e_balance.act_info5 := nvl(p_ni_e_balance.act_info5,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able UAP');
2775 p_ni_e_balance.act_info6 := nvl(p_ni_e_balance.act_info6,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able UEL');
2776 p_ni_e_balance.act_info7 := nvl(p_ni_e_balance.act_info7,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI E Employer');
2777 p_ni_e_balance.act_info8 := nvl(p_ni_e_balance.act_info8,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI E Employee');
2778 --p_ni_e_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_r_ptd_id);
2779 --p_ni_e_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2780 l_count_values := l_count_values +1;
2781 END IF;
2782 IF NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 THEN
2783 hr_utility.trace('fetching NI J Total/Able balances');
2784 p_ni_j_balance.act_info1 := 'J';
2785 --p_ni_j_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2786 p_ni_j_balance.act_info3 := nvl(p_ni_j_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able LEL');
2787 p_ni_j_balance.act_info4 := nvl(p_ni_j_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able ET');
2788 p_ni_j_balance.act_info5 := nvl(p_ni_j_balance.act_info5,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able UAP');
2789 p_ni_j_balance.act_info6 := nvl(p_ni_j_balance.act_info6,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able UEL');
2790 p_ni_j_balance.act_info7 := nvl(p_ni_j_balance.act_info7,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI J Employer');
2791 p_ni_j_balance.act_info8 := nvl(p_ni_j_balance.act_info8,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI J Employee');
2792 --p_ni_j_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_r_ptd_id);
2793 --p_ni_j_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2794 l_count_values := l_count_values +1;
2795 END IF;
2796 IF NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 THEN
2797 hr_utility.trace('fetching NI L Total/Able balances');
2798 p_ni_l_balance.act_info1 := 'L';
2799 --p_ni_l_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2800 p_ni_l_balance.act_info3 := nvl(p_ni_l_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able LEL');
2801 p_ni_l_balance.act_info4 := nvl(p_ni_l_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able ET');
2802 p_ni_l_balance.act_info5 := nvl(p_ni_l_balance.act_info5,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able UAP');
2803 p_ni_l_balance.act_info6 := nvl(p_ni_l_balance.act_info6,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able UEL');
2804 p_ni_l_balance.act_info7 := nvl(p_ni_l_balance.act_info7,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Employer');
2805 p_ni_l_balance.act_info8 := nvl(p_ni_l_balance.act_info8,0) + 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Employee');
2806 --p_ni_l_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2807 --p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2808 l_count_values := l_count_values +1;
2809 END IF;
2810 hr_utility.trace('Number of categories: ' || l_count_values);
2811 hr_utility.trace('Leaving PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_sum_asg.');
2812 END get_ni_only_agg_bal_sum_asg;
2813 -- end aggregated assignments
2814 FUNCTION Bacs_random_digit
2815 RETURN VARCHAR2
2816 IS
2817 str VARCHAR2(50) := '-./0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
2818 l_random NUMBER(38);
2819 l_randomindex NUMBER(2);
2820 l_randomstr VARCHAR2(1);
2821 L_FINALSTR VARCHAR2(4) := '/';
2822 BEGIN
2823 FOR i IN 1..3
2824 LOOP
2825 l_random := ABS(dbms_random.random);
2826 l_randomindex := mod(l_random,39);
2827 l_randomstr := SUBSTR(str,l_randomindex+1,1);
2828 l_finalstr := l_finalstr||l_randomstr;
2829 END LOOP;
2830 RETURN l_finalstr;
2831 END BACS_RANDOM_DIGIT;
2832 FUNCTION FPS_hash(
2833 p_random_string IN VARCHAR2,
2834 p_originator_bank IN VARCHAR2,
2835 p_recipient_bank IN VARCHAR2,
2836 p_payment_amount IN VARCHAR2)
2837 RETURN VARCHAR2
2838 IS
2839 l_random_string VARCHAR2(4) := p_random_string;
2840 l_originator_bank VARCHAR2(6) := p_originator_bank;
2841 l_recipient_bank VARCHAR2(6) := p_recipient_bank;
2842 l_payment_amount VARCHAR2(11) := p_payment_amount;
2843 L_HASH_STR VARCHAR2(27);
2844 l_hash VARCHAR2(64);
2845 BEGIN
2846 hr_utility.trace(' Entered FPS_hash ');
2847 l_hash_str := l_random_string||l_originator_bank||l_recipient_bank||l_payment_amount;
2848 hr_utility.trace(' input for hash '||l_hash_str );
2849 L_HASH := FPS_BACS_SHA_256(L_HASH_STR);
2850 hr_utility.trace(' Generated hash '||L_HASH );
2851 RETURN l_hash;
2852 END FPS_hash;
2853 FUNCTION fetch_random_digit_bacs(
2854 p_ORG_SORT_CODE IN VARCHAR2,
2855 p_RECEIPIENT_SORT_CODE IN VARCHAR2,
2856 p_amount IN VARCHAR2 )
2857 RETURN VARCHAR2
2858 IS
2859 L_random VARCHAR2(4);
2860 L_PAYROLL_ACTION_ID NUMBER;
2861 L_ASSIGNMENT_ACTION_ID NUMBER;
2862
2863 L_DUMMY number;
2864 L_COUNT_CHECK NUMBER := 0;
2865 BEGIN
2866 hr_utility.trace(' Entered Fetch random digit for BACS ');
2867 hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2868 hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2869 hr_utility.trace(' p_amount : '||p_amount);
2870 L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2871 hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2872 L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2873 hr_utility.trace(' L_ASSIGNMENT_ACTION_ID : '||L_ASSIGNMENT_ACTION_ID);
2874
2875 /*
2876 --16510056 Begin
2877 The below fix is not needed for BACS now.
2878 This is creating problems in NON RTI instances
2879 --15903040 Begin
2880 LOOP
2881 --This is added to check whether the Random digit insert is completed before the fetch starts
2882 --This is needed because the calling program runs in multithread where the
2883 --call to fetch the random string may be executed before the insert completed.
2884 Begin
2885 SELECT count( distinct PAA.PRE_PAYMENT_PAYROLL_ACTION_ID) into l_dummy FROM
2886 (
2887 SELECT DISTINCT payroll_action_id PRE_PAYMENT_PAYROLL_ACTION_ID
2888 FROM pay_payroll_actions
2889 WHERE payroll_action_id IN
2890 (
2891 SELECT DISTINCT payroll_action_id
2892 FROM pay_assignment_actions
2893 WHERE assignment_action_id IN
2894 (
2895 SELECT LOCKED_ACTION_ID
2896 FROM pay_action_interlocks
2897 WHERE locking_action_id IN
2898 (
2899 SELECT ASSIGNMENT_ACTION_ID
2900 FROM pay_assignment_actions
2901 WHERE payroll_action_id = L_PAYROLL_ACTION_ID
2902 )
2903 )
2904 )
2905 ) PAA, PAY_GB_BACS_FPS BACS_FPS
2906 WHERE PAA.PRE_PAYMENT_PAYROLL_ACTION_ID is not null
2907 and PAA.PRE_PAYMENT_PAYROLL_ACTION_ID = BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID
2908 and BACS_FPS.PRE_PAYMENT_ID is null
2909 AND nvl(BACS_FPS.RANDOM_STRING,'no data') <> 'COMP';
2910
2911 if l_dummy > 0 then
2912 L_COUNT_CHECK := L_COUNT_CHECK +1;
2913 IF L_COUNT_CHECK < 200 THEN
2914 HR_UTILITY.TRACE('All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2915 fnd_file.put_line(FND_FILE.LOG,'All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2916 dbms_lock.sleep(3); --Sleep for three seconds
2917 Else
2918 HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2919 fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2920 hr_utility.raise_error;
2921 End If;
2922 else
2923 exit;
2924 End if;
2925 End;
2926 END LOOP;
2927 --15903040 End
2928 --16510056 End
2929 */
2930
2931 SELECT RANDOM_STRING
2932 INTO L_RANDOM
2933 FROM PAY_GB_BACS_FPS bacs,
2934 pay_assignment_actions paa
2935 WHERE paa.payroll_action_id = L_PAYROLL_ACTION_ID -- CONTEXT (BACS payroll action id)
2936 and paa.ASSIGNMENT_action_id = L_ASSIGNMENT_ACTION_ID -- CONTEXT (BACS ASSIGNMENT action id)
2937 AND paa.pre_payment_id = bacs.pre_payment_id
2938 AND BACS.ORG_SORT_CODE = P_ORG_SORT_CODE --PARAMETER
2939 AND BACS.RECEIPIENT_SORT_CODE = P_RECEIPIENT_SORT_CODE --PARAMETER
2940 AND BACS.AMOUNT = P_AMOUNT ; --PARAMETER
2941 hr_utility.trace(' Fetched random digit for BACS '||L_random);
2942 RETURN L_random;
2943 EXCEPTION
2944 WHEN no_data_found THEN
2945 L_random := ' ';
2946 RETURN L_random;
2947 END fetch_random_digit_bacs;
2948 FUNCTION Insert_master(
2949 P_PAYROLL_ACTION_ID IN NUMBER,
2950 P_PROGRAM IN VARCHAR2,
2951 l_insert_status OUT NOCOPY NUMBER)
2952 RETURN NUMBER
2953 IS
2954
2955 L_DUMMY NUMBER := 0;
2956 --global variables used for locking;
2957 l_lockname VARCHAR2(25) := 'Lock_PAY_GB_BACS_FPS';
2958 l_lockhandle VARCHAR2(200);
2959 l_result PLS_INTEGER;
2960 p_ltype NUMBER :=6; -- Exclusive lock
2961 p_retval NUMBER;
2962 l_count_lock_failures NUMBER :=0;
2963 l_message VARCHAR2(150);
2964 L_PAY_GB_BACS_FPS PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%type;
2965 BEGIN
2966 hr_utility.trace(' Inserting Master Record in PAY_GB_BACS_FPS ');
2967 fnd_file.put_line(FND_FILE.LOG,' Master Insert start at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
2968 --15903040 Begin
2969 IF l_lockhandle IS NULL THEN
2970 LOOP
2971 dbms_lock.allocate_unique(l_lockname, l_lockhandle);
2972 p_retval := dbms_lock.request(l_lockhandle, p_ltype);
2973 IF p_retval = 0 THEN
2974 --Lock is succesful. p_retval = 0
2975 fnd_file.put_line(FND_FILE.LOG,' Lock granted at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
2976 EXIT;
2977 elsif p_retval =1 THEN
2978 L_MESSAGE := 'Timeout at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2979 HR_UTILITY.TRACE(L_MESSAGE);
2980 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2981 l_count_lock_failures := l_count_lock_failures +1;
2982 elsif p_retval =2 THEN
2983 L_MESSAGE := 'Deadlock at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2984 HR_UTILITY.TRACE(L_MESSAGE);
2985 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2986 l_count_lock_failures := l_count_lock_failures +1;
2987 elsif p_retval =3 THEN
2988 L_MESSAGE := 'Parameter Error at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2989 HR_UTILITY.TRACE(L_MESSAGE);
2990 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2991 hr_utility.raise_error;
2992 elsif p_retval=4 THEN
2993 L_MESSAGE := 'Already owned at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2994 HR_UTILITY.TRACE(L_MESSAGE);
2995 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2996 hr_utility.raise_error;
2997 elsif p_retval=5 THEN
2998 L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2999 HR_UTILITY.TRACE(L_MESSAGE);
3000 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3001 hr_utility.raise_error;
3002 END IF;
3003 IF L_COUNT_LOCK_FAILURES < 50 THEN
3004 HR_UTILITY.TRACE('Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3005 fnd_file.put_line(FND_FILE.LOG,'Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3006 dbms_lock.sleep(1);
3007 ELSE
3008 L_MESSAGE := 'Lock cannot be obtained in 50 attempts hence error - '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3009 HR_UTILITY.TRACE(L_MESSAGE);
3010 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3011 hr_utility.raise_error;
3012 END IF;
3013 END LOOP;
3014 END IF;
3015 --The below section (till release lock) will run in single threaded mode eventhough called from multiple threads.
3016 BEGIN
3017 SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3018 INTO L_PAY_GB_BACS_FPS
3019 FROM PAY_GB_BACS_FPS
3020 WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID;
3021 hr_utility.trace(' Second check - Data present in PAY_GB_BACS_FPS - No insert happened');
3022 fnd_file.put_line(FND_FILE.LOG,' Second check - Data present in PAY_GB_BACS_FPS - No insert happened at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3023 EXCEPTION
3024 WHEN no_data_found THEN
3025 INSERT
3026 INTO PAY_GB_BACS_FPS
3027 (
3028 PRE_PAYMENT_PAYROLL_ACTION_ID,
3029 CREATION_DATE,
3030 TRIGGERED_BY
3031 )
3032 VALUES
3033 (
3034 P_PAYROLL_ACTION_ID,
3035 SYSDATE,
3036 P_PROGRAM
3037 );
3038 COMMIT; -- 14807372 This commit is needed so that other threads know that insert
3039 --for P_PAYROLL_ACTION_ID is inprogress or completed.
3040 l_insert_status := 5; --This means insert of master record happened.
3041 END;
3042 IF l_lockhandle IS NOT NULL THEN
3043 p_retval := dbms_lock.release(l_lockhandle);
3044 IF p_retval =3 THEN
3045 L_MESSAGE := 'Parameter Error at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3046 HR_UTILITY.TRACE(L_MESSAGE);
3047 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3048 hr_utility.raise_error;
3049 elsif p_retval=4 THEN
3050 L_MESSAGE := 'Do not own lock specified by id or lockhandle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3051 HR_UTILITY.TRACE(L_MESSAGE);
3052 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3053 hr_utility.raise_error;
3054 elsif p_retval=5 THEN
3055 L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3056 HR_UTILITY.TRACE(L_MESSAGE);
3057 fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3058 hr_utility.raise_error;
3059 END IF;
3060 END IF;
3061 --15903040 End
3062 hr_utility.trace(' Master Record insert completed ');
3063 fnd_file.put_line(FND_FILE.LOG,' Master Insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3064 RETURN L_DUMMY;
3065 END INSERT_MASTER;
3066 FUNCTION fetch_HASH_FPS_ASG
3067 (
3068 P_PAYROLL_ACTION_ID IN NUMBER,
3069 P_ASSIGNMENT_ID IN NUMBER,
3070 P_LARGE_BACS_AMT OUT NOCOPY NUMBER
3071 )
3072 RETURN VARCHAR2
3073 IS
3074 L_HASH VARCHAR2(64);
3075 L_AMOUNT NUMBER;
3076
3077 L_DUMMY PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%TYPE;
3078 L_COUNT_CHECK NUMBER := 0;
3079 BEGIN
3080 hr_utility.trace(' Entered Fetch hash for FPS - ASSIGNMENT');
3081 --15903040 Begin
3082 LOOP
3083 --This is added to check whether the HASH insert is completed before the HASH fetch starts
3084 --This is needed because the calling program(FPS) runs in multithread where the
3085 --call to fetch the Hash string may be executed before the insert completed.
3086 Begin
3087 SELECT PRE_PAYMENT_PAYROLL_ACTION_ID INTO L_DUMMY
3088 FROM PAY_GB_BACS_FPS
3089 WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID
3090 AND PRE_PAYMENT_ID is null
3091 AND RANDOM_STRING = 'COMP';
3092 exit;
3093 Exception
3094 When no_data_found then
3095 L_COUNT_CHECK := L_COUNT_CHECK +1;
3096 IF L_COUNT_CHECK < 200 THEN
3097 HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3098 fnd_file.put_line(FND_FILE.LOG,L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3099 dbms_lock.sleep(3); --Sleep for three seconds
3100 Else
3101 HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3102 fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3103 hr_utility.raise_error;
3104 End If;
3105 End;
3106 END LOOP;
3107 --15903040 End
3108 SELECT HASH,
3109 AMOUNT
3110 INTO L_HASH,
3111 L_AMOUNT
3112 FROM PAY_GB_BACS_FPS
3113 WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3114 AND ASSIGNMENT_ID = p_ASSIGNMENT_ID ;
3115 hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3116 hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);
3117 P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3118 RETURN L_HASH;
3119 --NO DATA FOUND OR MORE ROWS FETCHED SHOULD STOP THE PROGRAM hence exception not handled
3120 END fetch_HASH_FPS_ASG;
3121
3122 FUNCTION fetch_HASH_FPS_PER(
3123 P_PAYROLL_ACTION_ID IN NUMBER,
3124 p_PERSON_ID IN NUMBER ,
3125 P_LARGE_BACS_AMT OUT NOCOPY NUMBER,
3126 P_ASG_ID OUT NOCOPY NUMBER)
3127 RETURN VARCHAR2
3128 IS
3129 L_HASH VARCHAR2(64);
3130
3131 L_AMOUNT NUMBER;
3132 L_ASG_ID NUMBER;
3133 L_DUMMY PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%TYPE;
3134 L_COUNT_CHECK NUMBER := 0;
3135 BEGIN
3136 hr_utility.trace(' Entered Fetch hash for FPS - PERSON');
3137 --15903040 Begin
3138 LOOP
3139 --This is added to check whether the HASH insert is completed before the HASH fetch starts
3140 --This is needed because the calling program(FPS) runs in multithread where the
3141 --call to fetch the Hash string may be executed before the insert completed.
3142 Begin
3143 SELECT PRE_PAYMENT_PAYROLL_ACTION_ID INTO L_DUMMY
3144 FROM PAY_GB_BACS_FPS
3145 WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID
3146 AND PRE_PAYMENT_ID is null
3147 AND RANDOM_STRING = 'COMP';
3148 exit;
3149 Exception
3150 When no_data_found then
3151 L_COUNT_CHECK := L_COUNT_CHECK +1;
3152 IF L_COUNT_CHECK < 200 THEN
3153 HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3154 fnd_file.put_line(FND_FILE.LOG,L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3155 dbms_lock.sleep(3); --Sleep for three seconds
3156 Else
3157 HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3158 fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3159 hr_utility.raise_error;
3160 End If;
3161 End;
3162 END LOOP;
3163 --15903040 End
3164 SELECT HASH,AMOUNT,ASSIGNMENT_ID
3165 INTO L_HASH,L_AMOUNT,L_ASG_ID
3166 FROM PAY_GB_BACS_FPS
3167 WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3168 AND PERSON_ID = p_PERSON_ID ;
3169 hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3170 hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);
3171 hr_utility.trace(' Fetched amount for FPS for Assignment '||L_ASG_ID);
3172 P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3173 P_ASG_ID := L_ASG_ID;
3174 RETURN L_HASH;
3175 --NO DATA FOUND OR MORE ROWS FETCHED SHOULD STOP THE PROGRAM hence exception not handled
3176 END fetch_HASH_FPS_PER;
3177
3178 FUNCTION FPS_BACS_PREPROCESS(
3179 P_PAYROLL_ACTION_ID IN NUMBER,
3180 P_PROGRAM IN VARCHAR2 )
3181 RETURN NUMBER
3182 IS
3183 PRAGMA AUTONOMOUS_TRANSACTION;
3184 --This program will be called from FPS and BACS
3185 L_PAY_GB_BACS_FPS PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%type;
3186 L_PAYROLL_ACTION_ID NUMBER;
3187 B_PAYROLL_ACTION_ID NUMBER;
3188 l_return NUMBER := 0;
3189 l_insert_bacs NUMBER;
3190 l_effective_date DATE;
3191 l_tax_ref VARCHAR2(50);
3192 l_business_group_id NUMBER;
3193 l_uptake_value VARCHAR2(100) := 'NONE';
3194 l_insert_status NUMBER;
3195 -- Cursor to fetch the details of the processed employees to comapre against
3196 -- the config values when the profile value of RTI Uptake is set as 'PARTIAL'.
3197 CURSOR get_paye_ref(c_effective_date DATE)
3198 IS
3199 SELECT paaf.business_group_id,
3200 flex.segment1
3201 FROM pay_assignment_actions paa,
3202 per_all_assignments_f paaf,
3203 hr_soft_coding_keyflex flex,
3204 pay_payrolls_f ppf,
3205 pay_payroll_actions ppa
3206 WHERE ppa.payroll_action_id = L_PAYROLL_ACTION_ID
3207 AND paa.payroll_action_id = ppa.payroll_action_id
3208 AND paaf.assignment_id = paa.assignment_id
3209 AND paaf.payroll_id = ppf.payroll_id
3210 AND ppf.soft_coding_keyflex_id = flex.SOFT_CODING_KEYFLEX_ID
3211 AND c_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
3212 AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
3213
3214
3215 type r_cursor
3216 IS
3217 REF
3218 CURSOR;
3219 C_PREPAY_PAYROLL_ACTN_ID r_cursor;
3220 BEGIN
3221 hr_utility.trace(' Entered FPS_BACS_PREPROCESS at'||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3222 fnd_file.put_line(FND_FILE.LOG,' Entered FPS_BACS_PREPROCESS at'||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3223 -- Get the profile value
3224 fnd_profile.get('GB RTI Uptake', l_uptake_value);
3225 fnd_file.put_line(FND_FILE.LOG,' l_uptake_value : '||l_uptake_value);
3226
3227 -- When RTI Profile is set to None return without doing any processing.
3228 IF l_uptake_value IS NULL OR l_uptake_value = 'NONE' THEN
3229 RETURN l_return;
3230 END IF;
3231
3232
3233 IF P_PROGRAM = 'BACS' THEN
3234 fnd_file.put_line(FND_FILE.LOG,' Called from BACS');
3235 B_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
3236 fnd_file.put_line(FND_FILE.LOG,' BACS Payroll Action ID :'|| B_PAYROLL_ACTION_ID);
3237 OPEN C_PREPAY_PAYROLL_ACTN_ID FOR
3238 SELECT DISTINCT payroll_action_id
3239 FROM pay_payroll_actions
3240 WHERE payroll_action_id IN
3241 ( SELECT DISTINCT payroll_action_id
3242 FROM pay_assignment_actions
3243 WHERE assignment_action_id IN
3244 (SELECT LOCKED_ACTION_ID
3245 FROM pay_action_interlocks
3246 WHERE locking_action_id IN
3247 (SELECT ASSIGNMENT_ACTION_ID
3248 FROM pay_assignment_actions
3249 WHERE payroll_action_id = B_PAYROLL_ACTION_ID
3250 )
3251 )
3252 )
3253 ORDER BY payroll_action_id Nulls Last;
3254 ELSE
3255 hr_utility.trace(' Called from FPS with Pre Payment payroll action id ');
3256 OPEN C_PREPAY_PAYROLL_ACTN_ID FOR
3257 SELECT DISTINCT payroll_action_id
3258 FROM pay_payroll_actions
3259 WHERE payroll_action_id = P_PAYROLL_ACTION_ID
3260 ORDER BY payroll_action_id Nulls Last;
3261 END IF;
3262 LOOP
3263 FETCH C_PREPAY_PAYROLL_ACTN_ID INTO L_PAYROLL_ACTION_ID;
3264 hr_utility.trace(' Operation started for '||L_PAYROLL_ACTION_ID);
3265 fnd_file.put_line(FND_FILE.LOG,' Operation started for '||L_PAYROLL_ACTION_ID);
3266
3267 --14797457 - If no BACS payment then exit the loop
3268 IF L_PAYROLL_ACTION_ID IS NULL THEN
3269 EXIT;
3270 END IF;
3271
3272
3273 -- initialise the variables
3274 l_insert_bacs := NULL;
3275 l_business_group_id := NULL;
3276 l_tax_ref := NULL;
3277 l_effective_date := NULL;
3278 -- check whether the RTI live is for specific PAYE ref
3279 IF l_uptake_value = 'PARTIAL' THEN
3280 SELECT effective_date
3281 INTO l_effective_date
3282 FROM pay_payroll_actions
3283 WHERE payroll_action_id = L_PAYROLL_ACTION_ID;
3284 OPEN get_paye_ref(l_effective_date);
3285 FETCH get_paye_ref INTO l_business_group_id, l_tax_ref;
3286 CLOSE get_paye_ref;
3287 fnd_file.put_line(FND_FILE.LOG,' l_business_group_id: ' || l_business_group_id);
3288 fnd_file.put_line(FND_FILE.LOG,' l_tax_ref: ' || l_tax_ref);
3289
3290 -- get the tax reference and effective date from configurations defined for this business group.
3291 BEGIN
3292 SELECT 1
3293
3294 INTO l_insert_bacs
3295 FROM pqp_configuration_values
3296 WHERE business_group_id = l_business_group_id
3297
3298 AND pcv_information_category = 'PAY_GB_RTI_FPS_BACS'
3299 AND pcv_information1 = l_tax_ref
3300 AND l_effective_date >= fnd_date.canonical_to_date(PCV_INFORMATION2);
3301 EXCEPTION
3302 WHEN OTHERS THEN
3303
3304
3305 hr_utility.trace(' No Config found for the paye reference:' || l_tax_ref);
3306 fnd_file.put_line(FND_FILE.LOG,' No Config found for the paye reference:' || l_tax_ref);
3307 RETURN l_return;
3308 END;
3309
3310 END IF; -- if l_uptake_value = 'PARTIAL'
3311 -- Follow the normal behaviour when the profile value is either set to ALL or
3312 -- for the current PAYE ref the RTI is enabled through config values.
3313 IF l_insert_bacs IS NOT NULL OR l_uptake_value = 'ALL' THEN
3314 BEGIN
3315 fnd_file.put_line(FND_FILE.LOG,' Check for PAYROLL_ACTION_ID '||L_PAYROLL_ACTION_ID);
3316 SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3317 INTO L_PAY_GB_BACS_FPS
3318 FROM PAY_GB_BACS_FPS
3319 WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3320 hr_utility.trace(' Data present in PAY_GB_BACS_FPS - No insert happened');
3321 fnd_file.put_line(FND_FILE.LOG,' Data present in PAY_GB_BACS_FPS - No insert happened');
3322 RETURN l_return;
3323 EXCEPTION
3324 WHEN no_data_found THEN
3325 l_insert_status := 0;
3326 --Insert the master row.
3327 fnd_file.put_line(FND_FILE.LOG,' Data not present in PAY_GB_BACS_FPS- Calling Master insert');
3328 l_return := Insert_master(L_PAYROLL_ACTION_ID,P_PROGRAM,l_insert_status);
3329 fnd_file.put_line(FND_FILE.LOG,' Master insert completed - Moving to other inserts');
3330 --Insert the computed rows with random digits and hash value
3331 BEGIN --14807372
3332 IF l_insert_status = 5 THEN --15903040
3333 --l_insert_status = 5 means Insert_master inserted the master row in this thread/session
3334 --so below insert should be executed.
3335 fnd_file.put_line(FND_FILE.LOG,' Main insert started at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3336 INSERT
3337 INTO PAY_GB_BACS_FPS
3338 (
3339 PRE_PAYMENT_PAYROLL_ACTION_ID,
3340 PRE_PAYMENT_ID,
3341 PERSON_ID,
3342 ASSIGNMENT_ID,
3343 AGGREGATION_FLAG,
3344 ORG_SORT_CODE,
3345 RECEIPIENT_SORT_CODE,
3346 AMOUNT,
3347 RANDOM_STRING,
3348 HASH,
3349 CREATION_DATE,
3350 TRIGGERED_BY
3351 )
3352 SELECT bacs.PAYROLL_ACTION_ID,
3353 bacs.PRE_PAYMENT_ID,
3354 bacs.PERSON_ID,
3355 bacs.ASSIGNMENT_ID,
3356 bacs.Aggregation,
3357 bacs.Org_sort_code,
3358 bacs.Receipient_sort_code,
3359 bacs.Amount,
3360 bacs.RandomStr,
3361 PAY_GB_FPS_NI_AND_OTHERS_13.FPS_HASH(bacs.RandomStr,bacs.Org_sort_code, bacs.Receipient_sort_code,bacs.Amount) Hash,
3362 SYSDATE,
3363 P_PROGRAM
3364 FROM
3365 (SELECT lpad(Orgkey.segment3,6,'0') Org_sort_code,
3366 lpad(deskey.segment3,6,'0') Receipient_sort_code,
3367 PAA.PAYROLL_ACTION_ID ,
3368 ppp.PRE_PAYMENT_ID,
3369 PAPF.PERSON_ID,
3370 PAA.ASSIGNMENT_ID,
3371 lpad(ppp.value*100,11,'0') Amount,
3372 PAPF.PER_information10 Aggregation,
3373 DECODE(PAPF.PER_information10,'Y',RANK() OVER (PARTITION BY PAA.PAYROLL_ACTION_ID,PAPF.PERSON_ID order by ppp.value DESC),RANK() OVER (PARTITION BY PAA.PAYROLL_ACTION_ID,PAA.ASSIGNMENT_ID order by ppp.value DESC)) highest,
3374 PAY_GB_FPS_NI_AND_OTHERS_13.BACS_RANDOM_DIGIT RandomStr
3375 FROM pay_pre_payments ppp,
3376 pay_payment_types ppt,
3377 pay_org_payment_methods_f popf,
3378 pay_personal_payment_methods_f pppf,
3379 pay_external_accounts Orgkey,
3380 pay_external_accounts deskey,
3381 PAY_ASSIGNMENT_ACTIONS PAA,
3382 PER_ALL_PEOPLE_F PAPF,
3383 per_all_assignments_f paaf,
3384 pay_payroll_actions ppa
3385 WHERE popf.ORG_PAYMENT_METHOD_ID = ppp.ORG_PAYMENT_METHOD_ID
3386 AND pppf.personal_payment_method_ID = ppp.personal_payment_method_ID
3387 AND popf.PAYMENT_TYPE_ID = ppt.PAYMENT_TYPE_ID
3388 AND ppt.PAYMENT_TYPE_NAME = 'BACS Tape'
3389 AND popf.defined_balance_id is not null --Bug#16306737
3390 AND orgkey.external_account_id = popf.external_account_id
3391 AND deskey.external_account_id = pppf.external_account_id
3392 AND ppa.effective_date BETWEEN popf.effective_start_date AND popf.effective_end_date
3393 AND ppa.effective_date BETWEEN pppf.effective_start_date AND pppf.effective_end_date
3394 AND ppp.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
3395 AND PAA.PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
3396 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
3397 AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
3398 AND PAAF.PERSON_ID = PAPF.PERSON_ID
3399 AND ppa.effective_date BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date
3400 AND ppa.effective_date BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
3401 ) BACS
3402 WHERE BACS.highest = 1
3403 AND 1 = -- This check is to prevent inserts from multiple programs at the same time
3404 (SELECT COUNT(*)
3405 FROM PAY_GB_BACS_FPS
3406 WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
3407 );
3408 hr_utility.trace(' All insert completed for '||L_PAYROLL_ACTION_ID);
3409 fnd_file.put_line(FND_FILE.LOG,' Main insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3410 --15903040 Begin
3411 --This update statement will be fired once.
3412 --This will indicate all inserts for the payroll action id is completed.
3413 update PAY_GB_BACS_FPS
3414 set RANDOM_STRING = 'COMP'
3415 where PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
3416 and RANDOM_STRING is null
3417 and PRE_PAYMENT_ID is null;
3418 --15903040 End
3419 END IF;-- check for l_insert_status = 5
3420 fnd_file.put_line(FND_FILE.LOG,' Sanity check start at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3421
3422 DELETE
3423 FROM PAY_GB_BACS_FPS
3424 WHERE rowid NOT IN
3425 (SELECT MIN(rowid)
3426 FROM PAY_GB_BACS_FPS
3427 WHERE NVL(AGGREGATION_FLAG,'N')= 'N'
3428 GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
3429 PERSON_ID,
3430 ASSIGNMENT_ID,
3431 AMOUNT
3432 HAVING COUNT(*) > 1
3433 )
3434 AND (PRE_PAYMENT_PAYROLL_ACTION_ID,PERSON_ID,ASSIGNMENT_ID,AMOUNT) IN
3435 (SELECT PRE_PAYMENT_PAYROLL_ACTION_ID,
3436 PERSON_ID,
3437 ASSIGNMENT_ID,
3438 AMOUNT
3439 FROM PAY_GB_BACS_FPS
3440 WHERE NVL(AGGREGATION_FLAG,'N')= 'N'
3441 GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
3442 PERSON_ID,
3443 ASSIGNMENT_ID,
3444 AMOUNT
3445 HAVING COUNT(*) > 1
3446 );
3447 hr_utility.trace(' Deleted Non aggregated duplicate rows for '||L_PAYROLL_ACTION_ID);
3448 DELETE
3449 FROM PAY_GB_BACS_FPS
3450 WHERE rowid NOT IN
3451 (SELECT MIN(rowid)
3452 FROM PAY_GB_BACS_FPS
3453 WHERE NVL(AGGREGATION_FLAG,'N') = 'Y'
3454 -- AND PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID -- To Delete all Payroll actions' duplicates
3455 GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
3456 PERSON_ID,
3457 AMOUNT
3458 HAVING COUNT(*) > 1
3459 )
3460 AND (PRE_PAYMENT_PAYROLL_ACTION_ID, PERSON_ID, AMOUNT) IN
3461 (SELECT PRE_PAYMENT_PAYROLL_ACTION_ID,
3462 PERSON_ID,
3463 AMOUNT
3464 FROM PAY_GB_BACS_FPS
3465 WHERE NVL(AGGREGATION_FLAG,'N') = 'Y'
3466 -- AND PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID -- To Delete all Payroll actions' duplicates
3467 GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
3468 PERSON_ID,
3469 AMOUNT
3470 HAVING COUNT(*) > 1
3471 );
3472 fnd_file.put_line(FND_FILE.LOG,' Sanity check completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3473 hr_utility.trace(' Deleted aggregated duplicate rows for '||L_PAYROLL_ACTION_ID);
3474 L_PAYROLL_ACTION_ID := NULL;
3475 --14807372
3476 EXCEPTION
3477 WHEN OTHERS THEN
3478
3479 --Master insert completed but actual data inserts failed so rollback Master also
3480 --This is needed as commit is done after master insert
3481 DELETE
3482 FROM PAY_GB_BACS_FPS
3483 WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3484 COMMIT;
3485 hr_utility.raise_error;
3486 END;
3487 END;
3488 COMMIT;
3489 END IF; -- end for if l_insert_bacs = true OR l_uptake_value = 'ALL'
3490 END LOOP;
3491 CLOSE C_PREPAY_PAYROLL_ACTN_ID;
3492 fnd_file.put_line(FND_FILE.LOG,' leaving FPS_BACS_PREPROCESS'||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3493 RETURN l_return;
3494 END FPS_BACS_PREPROCESS;
3495 --
3496 --FPS RollBack Start
3497 FUNCTION get_version(
3498 p_assig_id IN NUMBER,
3499 p_type IN VARCHAR2,
3500 p_aei_id OUT nocopy NUMBER)
3501 RETURN NUMBER
3502 IS
3503 l_proc CONSTANT VARCHAR2(50):= 'get_version';
3504 l_ovn NUMBER;
3505 CURSOR csr_ovn
3506 IS
3507 SELECT object_version_number,
3508 assignment_extra_info_id
3509 FROM per_assignment_extra_info
3510 WHERE assignment_id = p_assig_id
3511 AND information_type = p_type;
3512 BEGIN
3513 hr_utility.set_location('Entering: '||l_proc,1);
3514 OPEN csr_ovn;
3515 FETCH csr_ovn INTO l_ovn, p_aei_id;
3516 CLOSE csr_ovn;
3517 RETURN l_ovn;
3518 hr_utility.set_location('Leaving: '||l_proc,999);
3519 END;
3520 PROCEDURE restored(
3521 p_pactid IN NUMBER)
3522 IS
3523 l_proc CONSTANT VARCHAR2(50):= 'restored';
3524 l_ovn NUMBER;
3525 l_aei_id NUMBER;
3526 l_assignment_id NUMBER;
3527 CURSOR csr_archive_details
3528 IS
3529 SELECT paa.assignment_id,
3530 paa.assignment_action_id,
3531 pai.action_information6, --EXPAT
3532 pai.action_information7, --EXPAT
3533 pai.action_information8, --EXPAT
3534 pai.action_information9, --EXPAT
3535 pai.action_information10, --EXPAT
3536 pai.action_information12, --PENSIONER
3537 pai.action_information4, --STARTER
3538 pai.action_information5, --STARTER
3539 pai.action_information20 -- Number of Periods Covered
3540 FROM pay_assignment_actions paa,
3541 pay_action_information pai
3542 WHERE paa.payroll_action_id = p_pactid
3543 AND paa.assignment_action_id = pai.action_context_id
3544 AND pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
3545 AND pai.action_context_type = 'AAP';
3546 BEGIN
3547 hr_utility.set_location('Entering: '||l_proc,1);
3548 FOR archive_rec IN csr_archive_details
3549 LOOP
3550 -- Number of Periods Covered
3551 l_ovn := get_version(archive_rec.assignment_id,'GB_PAY_RTI',l_aei_id);
3552 IF l_ovn IS NOT NULL THEN
3553 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_PAY_RTI',
3554 p_aei_information1 => archive_rec.action_information20);
3555 END IF;
3556 --EXPAT Flag
3557 IF (archive_rec.action_information9 IS NOT NULL) OR (archive_rec.action_information10 IS NOT NULL) OR (archive_rec.action_information6 ='Y') OR (archive_rec.action_information7 ='Y') OR (archive_rec.action_information8 ='Y') THEN
3558 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
3559 IF l_ovn IS NOT NULL THEN
3560 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
3561 p_aei_information19 => 'N');
3562 END IF;
3563 END IF;
3564 --PENSIONER Flag
3565 IF (archive_rec.action_information12 IS NOT NULL) THEN
3566 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
3567 IF l_ovn IS NOT NULL THEN
3568 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information9 => 'N');
3569 END IF;
3570 END IF;
3571 --STARTER Flag
3572 IF (archive_rec.action_information4 IS NOT NULL) OR (archive_rec.action_information5 IS NOT NULL) THEN
3573 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
3574 IF l_ovn IS NOT NULL THEN
3575 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
3576 END IF;
3577 END IF;
3578 -- Delete from pay_gb_fps_details
3579 DELETE
3580 FROM pay_gb_fps_details
3581 WHERE FPS_ASG_ACT_ID = archive_rec.ASSIGNMENT_ACTION_ID
3582 AND ASSIGNMENT_ID =archive_rec.ASSIGNMENT_ID;
3583 END LOOP;
3584 END;
3585 PROCEDURE fps_rollback(
3586 errbuf OUT NOCOPY VARCHAR2,
3587 retcode OUT NOCOPY NUMBER,
3588 p_year IN NUMBER,
3589 p_actid IN NUMBER)
3590 IS
3591 l_proc CONSTANT VARCHAR2(50):= 'fps_rollback';
3592 l_id NUMBER;
3593 BEGIN
3594 hr_utility.set_location('Entering: '||l_proc,1);
3595 hr_utility.set_location('Year : ' || p_year, 10);
3596 hr_utility.set_location('Action : ' || p_actid, 10);
3597 restored(p_actid);
3598 -- Next called the Core's ROLLBACK routine
3599 l_id := fnd_request.submit_request(application => 'PAY', program => 'ROLLBACK', argument1 => 'ROLLBACK', argument2 => NULL, argument3 => NULL, argument4 => p_year, argument5 => 'X', -- magnetic report
3600 argument6 => p_actid, -- payroll action_id
3601 argument7 => NULL, -- assignmenet_set
3602 argument8 => 'PAYROLL_ACTION_ID='||p_actid, argument9 => NULL);
3603 hr_utility.set_location('Leaving: '||l_proc,999);
3604 END fps_rollback;
3605 --FPS RollBack End
3606 END PAY_GB_FPS_NI_AND_OTHERS_13;