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