[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_TOTAL_PACKAGE
Source
1 PACKAGE BODY py_za_total_package AS
2 /* $Header: pyzatotpkg.pkb 120.1.12000000.3 2007/07/06 05:08:48 rpahune noship $ */
3 /* Copyright (c) Oracle Corporation 2005. All rights reserved. */
4 /*
5 PRODUCT
6 Oracle Payroll - ZA Localisation
7
8 NAME
9 py_za_total_package
10
11 DESCRIPTION
12 This is the ZA Total Package supporting package.
13
14 NOTES
15 .
16
17 MODIFICATION HISTORY
18 Person Date Version Bug Comments
19 ----------- ---------- ------- ------- --------------------------------
20 J.N. Louw 03/07/2007 115.7 6166736 + trc_RFIableTotPkgPTD_Upd
21 J.N. Louw 04/04/2007 115.6 5964600
22 A. Mahanty 16/05/2006 115.5 5148830 Modified ZA_Total_Package formula
23 with reference to the new dimension
24 _rtr_asg_tax_ptd.
25 Norcalc is done even for cases when
26 retro results exist
27 A. Mahanty 01/03/2006 115.4 4346955 Modified ZA_Total_Package formula.
28 Sitcalc is done for assignments where
29 retro results exist
30 A. Mahanty 03/11/2005 115.3 4346955 Modified ZA_Total_Package formula.
31 Norcalc is done even for cases when
32 retro results exist
33 J.N. Louw 11/10/2005 115.2 4667439 Added StopHrTrace
34 J.N. Louw 14/09/2005 115.1 4566053 Added annualisation to the split
35 R.V. Pahune 05/08/2005 115.0 4346920 Balance feed enhancement
36 J.N. Louw 17/08/2005 115.0 Initial Version
37 */
38
39 -------------------------------------------------------------------------------
40 -- PACKAGE BODY GLOBAL AREA --
41 -------------------------------------------------------------------------------
42 -- Types
43 SUBTYPE BALANCE IS NUMBER(15,2);
44 -- Application Database Items
45 dbi_FXD_PRC NUMBER;
46 dbi_TOT_PKG BALANCE;
47 dbi_ZA_ACT_END_DTE DATE;
48 dbi_ZA_ACT_STRT_DTE DATE;
49 dbi_ZA_ASG_TAX_RTR_RSLTS VARCHAR2(1);
50 dbi_ZA_ASG_TX_RTR_PRD VARCHAR2(1);
51 dbi_ZA_ASG_TX_YR NUMBER(4);
52 dbi_ZA_CUR_PRD_END_DTE DATE;
53 dbi_ZA_CUR_PRD_STRT_DTE DATE;
54 dbi_ZA_DYS_IN_YR NUMBER;
55 dbi_ZA_PAY_PRDS_LFT NUMBER;
56 dbi_ZA_PAY_PRDS_PER_YR NUMBER;
57 dbi_ZA_TX_YR_END DATE;
58 dbi_ZA_TX_YR_STRT DATE;
59 -- Balances
60 bal_ANN_TXB_PKG_CMP_NRFI_YTD BALANCE;
61 bal_ANN_TXB_PKG_CMP_RFI_YTD BALANCE;
62 bal_ANN_TXB_PKG_CMP_YTD BALANCE;
63 bal_BP_PTD BALANCE;
64 bal_BP_YTD BALANCE;
65 bal_RFIABLE_TOT_PKG_CYTD BALANCE;
66 bal_RFIABLE_TOT_PKG_PTD BALANCE;
67 bal_RFIABLE_TOT_PKG_YTD BALANCE;
68 bal_TOT_TXB_AB_RUN BALANCE;
69 bal_TOT_TXB_AP_YTD BALANCE;
70 bal_TOT_TXB_FB_CYTD BALANCE;
71 bal_TOT_TXB_FB_PTD BALANCE;
72 bal_TOT_TXB_FB_YTD BALANCE;
73 bal_TOT_TXB_NI_CYTD BALANCE;
74 bal_TOT_TXB_NI_PTD BALANCE;
75 bal_TOT_TXB_NI_YTD BALANCE;
76 bal_TOT_TXB_TA_CYTD BALANCE;
77 bal_TOT_TXB_TA_PTD BALANCE;
78 bal_TOT_TXB_TA_YTD BALANCE;
79 bal_TXB_PKG_CMP_CYTD BALANCE;
80 bal_TXB_PKG_CMP_NRFI_YTD BALANCE;
81 bal_TXB_PKG_CMP_PTD BALANCE;
82 bal_TXB_PKG_CMP_RFI_YTD BALANCE;
83 bal_TXB_PKG_CMP_YTD BALANCE;
84 -- Trace Globals
85 trc_AnnBonPtd BALANCE DEFAULT 0;
86 trc_AnnPymPtd BALANCE DEFAULT 0;
87 trc_AnnTxbPkgCmpNRFI BALANCE DEFAULT 0;
88 trc_AnnTxbPkgCmpNRFI_Upd BALANCE DEFAULT 0;
89 trc_AnnTxbPkgCmpRFI BALANCE DEFAULT 0;
90 trc_AnnTxbPkgCmpRFI_Upd BALANCE DEFAULT 0;
91 trc_AnnualisationType VARCHAR2(7) DEFAULT 'Unknown';
92 trc_NegPtd BOOLEAN DEFAULT FALSE;
93 trc_PosFactor NUMBER DEFAULT 0;
94 trc_PrdFactor NUMBER DEFAULT 0;
95 trc_PrjRFIableTotPkg BALANCE DEFAULT 0;
96 trc_RFIableTotPkgPTD BALANCE DEFAULT 0;
97 trc_RFIableTotPkgPTD_Upd BALANCE DEFAULT 0;
98 trc_SitFactor NUMBER DEFAULT 1;
99 trc_TotTxbPkgCmp BALANCE DEFAULT 0;
100 trc_TxbIncPtd BALANCE DEFAULT 0;
101 trc_TxbIncYtd BALANCE DEFAULT 0;
102 trc_TxbPkgCmp BALANCE DEFAULT 0;
103 trc_TxbPkgCmpNRFI BALANCE DEFAULT 0;
104 trc_TxbPkgCmpNRFI_Upd BALANCE DEFAULT 0;
105 trc_TxbPkgCmpRFI BALANCE DEFAULT 0;
106 trc_TxbPkgCmpRFI_Upd BALANCE DEFAULT 0;
107 trc_TxbPrc NUMBER DEFAULT 0;
108 -- Global Exception Message
109 xpt_Msg VARCHAR2(100) DEFAULT 'No Error';
110 -- Global Exception
111 xpt_E EXCEPTION;
112
113 -------------------------------------------------------------------------------
114 -- PACKAGE BODY --
115 -------------------------------------------------------------------------------
116
117 -------------------------------------------------------------------------------
118 -- StartHrTrace --
119 -- Wrapper for hr_utility.trace_on --
120 -------------------------------------------------------------------------------
121 PROCEDURE StartHrTrace AS
122 BEGIN
123 IF g_HrTraceEnabled THEN
124 hr_utility.trace_on(null,g_HrTracePipeName);
125 END IF;
126 END StartHrTrace;
127 -------------------------------------------------------------------------------
128 -- WriteHrTrace --
129 -- Wrapper for hr_utility.trace --
130 -------------------------------------------------------------------------------
131 PROCEDURE WriteHrTrace(
132 p_Buffer VARCHAR2
133 )
134 AS
135 BEGIN
136 IF g_HrTraceEnabled THEN
137 -- Write the Line
138 hr_utility.trace(p_Buffer);
139 END IF;
140 END WriteHrTrace;
141 -------------------------------------------------------------------------------
142 -- StopHrTrace --
143 -- Wrapper for hr_utility.trace_off --
144 -------------------------------------------------------------------------------
145 PROCEDURE StopHrTrace AS
146 BEGIN
147 IF g_HrTraceEnabled THEN
148 hr_utility.trace_off;
149 END IF;
150 END StopHrTrace;
151 -------------------------------------------------------------------------------
152 -- LatePayPeriod --
153 -------------------------------------------------------------------------------
154 FUNCTION LatePayPeriod RETURN BOOLEAN AS
155 -- Variables
156 l_CurTxYear NUMBER(15);
157 BEGIN
158 g_indent := g_indent||' ';
159 hr_utility.set_location(g_indent||'py_za_total_package.LatePayPeriod',1);
160 -- IF the employee's assignment ended before the current tax year
161 -- it's a Late Pay Period
162 IF dbi_ZA_ACT_END_DTE < dbi_ZA_TX_YR_STRT THEN
163
164 hr_utility.set_location(g_indent||'py_za_total_package.LatePayPeriod',2);
165
166 -- Valid Late Pay Period?
167 --
168 -- Current Tax Year
169 l_CurTxYear := to_number(to_char(dbi_ZA_TX_YR_END,'YYYY'));
170
171 hr_utility.set_location(g_indent||'py_za_total_package.LatePayPeriod',3);
172
173 IF (l_CurTxYear - dbi_ZA_ASG_TX_YR) > 1 THEN
174 hr_utility.set_location(g_indent||'py_za_total_package.LatePayPeriod',4);
175 hr_utility.set_message(801, 'Late Payment Across Two Tax Years!');
176 hr_utility.raise_error;
177 ELSE
178 hr_utility.set_location(g_indent||'py_za_total_package.LatePayPeriod',5);
179 g_indent := substr(g_indent,1,length(g_indent)-3);
180 RETURN TRUE;
181 END IF;
182
183 ELSE
184 hr_utility.set_location(g_indent||'py_za_total_package.LatePayPeriod',6);
185 g_indent := substr(g_indent,1,length(g_indent)-3);
186 RETURN FALSE;
187 END IF;
188 EXCEPTION
189 WHEN OTHERS THEN
190 IF xpt_Msg = 'No Error' THEN
191 xpt_Msg := 'LatePayPeriod: '||TO_CHAR(SQLCODE);
192 END IF;
193 RAISE xpt_E;
194 END LatePayPeriod;
195 -------------------------------------------------------------------------------
196 -- LstPeriod --
197 -------------------------------------------------------------------------------
198 FUNCTION LstPeriod RETURN BOOLEAN AS
199 BEGIN
200 g_indent := g_indent||' ';
201 -- Is this the last period for the tax year
202 --
203 IF dbi_ZA_PAY_PRDS_LFT = 1 THEN
204 hr_utility.set_location(g_indent||'py_za_total_package.LstPeriod',1);
205 g_indent := substr(g_indent,1,length(g_indent)-3);
206 RETURN TRUE;
207 ELSE
208 hr_utility.set_location(g_indent||'py_za_total_package.LstPeriod',2);
209 g_indent := substr(g_indent,1,length(g_indent)-3);
210 RETURN FALSE;
211 END IF;
212 EXCEPTION
213 WHEN OTHERS THEN
214 IF xpt_Msg = 'No Error' THEN
215 xpt_Msg := 'LstPeriod: '||TO_CHAR(SQLCODE);
216 END IF;
217 RAISE xpt_E;
218 END LstPeriod;
219 -------------------------------------------------------------------------------
220 -- EmpTermInPeriod --
221 -------------------------------------------------------------------------------
222 FUNCTION EmpTermInPeriod RETURN BOOLEAN AS
223
224 BEGIN
225 g_indent := g_indent||' ';
226 -- Was the employee terminated in the current period
227 --
228 IF dbi_ZA_ACT_END_DTE BETWEEN dbi_ZA_CUR_PRD_STRT_DTE
229 AND dbi_ZA_CUR_PRD_END_DTE
230 THEN
231 hr_utility.set_location(g_indent||'py_za_total_package.EmpTermInPeriod',1);
232 g_indent := substr(g_indent,1,length(g_indent)-3);
233 RETURN TRUE;
234 ELSE
235 hr_utility.set_location(g_indent||'py_za_total_package.EmpTermInPeriod',2);
236 g_indent := substr(g_indent,1,length(g_indent)-3);
237 RETURN FALSE;
238 END IF;
239 EXCEPTION
240 WHEN OTHERS THEN
241 IF xpt_Msg = 'No Error' THEN
242 xpt_Msg := 'EmpTermInPeriod: '||TO_CHAR(SQLCODE);
243 END IF;
244 RAISE xpt_E;
245 END EmpTermInPeriod;
246 -------------------------------------------------------------------------------
247 -- EmpTermPrePeriod --
248 -------------------------------------------------------------------------------
249 FUNCTION EmpTermPrePeriod RETURN BOOLEAN AS
250
251 BEGIN
252 g_indent := g_indent||' ';
253 -- Was the employee terminated before the current period
254 --
255 IF dbi_ZA_ACT_END_DTE <= dbi_ZA_CUR_PRD_STRT_DTE THEN
256 hr_utility.set_location(g_indent||'py_za_total_package.EmpTermPrePeriod',1);
257 g_indent := substr(g_indent,1,length(g_indent)-3);
258 RETURN TRUE;
259 ELSE
260 hr_utility.set_location(g_indent||'py_za_total_package.EmpTermPrePeriod',2);
261 g_indent := substr(g_indent,1,length(g_indent)-3);
262 RETURN FALSE;
263 END IF;
264 EXCEPTION
265 WHEN OTHERS THEN
266 IF xpt_Msg = 'No Error' THEN
267 xpt_Msg := 'EmpTermPrePeriod: '||TO_CHAR(SQLCODE);
268 END IF;
269 RAISE xpt_E;
270 END EmpTermPrePeriod;
271 -------------------------------------------------------------------------------
272 -- PreErnPeriod --
273 -------------------------------------------------------------------------------
274 FUNCTION PreErnPeriod RETURN BOOLEAN AS
275
276 BEGIN
277 g_indent := g_indent||' ';
278 -- PTD Taxable Income
279 --
280
281 trc_TxbIncPtd :=
282 ( bal_TOT_TXB_NI_PTD
283 + bal_TOT_TXB_FB_PTD
284 + bal_TOT_TXB_TA_PTD
285 + bal_BP_PTD
286 );
287 -- Ptd Annual Bonus
288 trc_AnnBonPtd := bal_TOT_TXB_AB_RUN;
289 -- Ytd Annual Payments
290 trc_AnnPymPtd := bal_TOT_TXB_AP_YTD;
291
292 WriteHrTrace(g_indent||'trc_TxbIncPtd: '||to_char(trc_TxbIncPtd));
293 WriteHrTrace(g_indent||'trc_AnnBonPtd: '||to_char(trc_AnnBonPtd));
294 WriteHrTrace(g_indent||'trc_AnnPymPtd: '||to_char(trc_AnnPymPtd));
295
296 -- Annual Type PTD Income with no Period Type PTD Income
297 IF (trc_AnnBonPtd + trc_AnnPymPtd) <> 0 AND trc_TxbIncPtd <= 0 THEN
298 hr_utility.set_location(g_indent||'py_za_total_package.PreErnPeriod',1);
299 g_indent := substr(g_indent,1,length(g_indent)-3);
300 RETURN TRUE;
301 ELSE
302 hr_utility.set_location(g_indent||'py_za_total_package.PreErnPeriod',2);
303 g_indent := substr(g_indent,1,length(g_indent)-3);
304 RETURN FALSE;
305 END IF;
306 EXCEPTION
307 WHEN OTHERS THEN
308 IF xpt_Msg = 'No Error' THEN
309 xpt_Msg := 'PreErnPeriod: '||TO_CHAR(SQLCODE);
310 END IF;
311 RAISE xpt_E;
312 END PreErnPeriod;
313 -------------------------------------------------------------------
314 -- NegPtd
315 -------------------------------------------------------------------
316 FUNCTION NegPtd RETURN BOOLEAN AS
317 ------------
318 -- Variables
319 ------------
320 l_Retval BOOLEAN DEFAULT FALSE;
321
322 -------------------------------------------------------------------
323 BEGIN -- NegPtd - MAIN --
324 -------------------------------------------------------------------
325 g_indent := g_indent||' ';
326 hr_utility.set_location(g_indent||'py_za_total_package.NegPtd',1);
327 -- If any period ptd income value is negative
328 -- a site calc has to be done
329
330 IF LEAST( bal_TOT_TXB_NI_PTD
331 , bal_TOT_TXB_FB_PTD
332 , bal_TOT_TXB_TA_PTD
333 , bal_BP_PTD
334 ) < 0
335 THEN
336 hr_utility.set_location(g_indent||'py_za_total_package.NegPtd',2);
337 trc_NegPtd := TRUE;
338 END IF;
339
340 hr_utility.set_location(g_indent||'py_za_total_package.NegPtd',3);
341 g_indent := substr(g_indent,1,length(g_indent)-3);
342 RETURN trc_NegPtd;
343 EXCEPTION
344 WHEN OTHERS THEN
345 IF xpt_Msg = 'No Error' THEN
346 xpt_Msg := 'NegPtd: '||TO_CHAR(SQLCODE);
347 END IF;
348 RAISE xpt_E;
349 END NegPtd;
350 -------------------------------------------------------------------------------
351 -- PeriodFactor --
352 -------------------------------------------------------------------------------
353 PROCEDURE PeriodFactor AS
354 ------------
355 -- Variables
356 ------------
357 l_tot_inc_ytd BALANCE;
358 l_tot_inc_ptd BALANCE;
359 BEGIN
360 g_indent := g_indent||' ';
361 hr_utility.set_location(g_indent||'py_za_total_package.PeriodFactor',1);
362
363 l_tot_inc_ytd := bal_TOT_TXB_NI_YTD
364 + bal_TOT_TXB_FB_YTD
365 + bal_TOT_TXB_TA_YTD;
366 l_tot_inc_ptd := bal_TOT_TXB_NI_PTD
367 + bal_TOT_TXB_FB_PTD
368 + bal_TOT_TXB_TA_PTD;
369
370 hr_utility.set_location(g_indent||'py_za_total_package.PeriodFactor',2);
371
372 IF dbi_ZA_TX_YR_STRT < dbi_ZA_ACT_STRT_DTE THEN
373 hr_utility.set_location(g_indent||'py_za_total_package.PeriodFactor',3);
374
375 IF l_tot_inc_ytd = l_tot_inc_ptd THEN
376 hr_utility.set_location(g_indent||'py_za_total_package.PeriodFactor',4);
377 -- i.e. first pay period for the person
378 trc_PrdFactor :=
379 ( dbi_ZA_CUR_PRD_END_DTE
380 - dbi_ZA_ACT_STRT_DTE
381 + 1
382 )
383 /
384 ( dbi_ZA_CUR_PRD_END_DTE
385 - dbi_ZA_CUR_PRD_STRT_DTE
386 + 1
387 );
388 ELSE
389 hr_utility.set_location(g_indent||'py_za_total_package.PeriodFactor',5);
390 trc_PrdFactor := 1;
391 END IF;
392
393 ELSE
394 hr_utility.set_location(g_indent||'py_za_total_package.PeriodFactor',6);
395 trc_PrdFactor := 1;
396 END IF;
397
398 WriteHrTrace(g_indent||'dbi_ZA_TX_YR_STRT: '
399 ||to_char(dbi_ZA_TX_YR_STRT,'DD/MM/YYYY'));
400 WriteHrTrace(g_indent||'dbi_ZA_ACT_STRT_DTE: '
401 ||to_char(dbi_ZA_ACT_STRT_DTE,'DD/MM/YYYY'));
402 WriteHrTrace(g_indent||'dbi_ZA_CUR_PRD_END_DTE: '
403 ||to_char(dbi_ZA_CUR_PRD_END_DTE,'DD/MM/YYYY'));
404 WriteHrTrace(g_indent||'dbi_ZA_CUR_PRD_STRT_DTE: '
405 ||to_char(dbi_ZA_CUR_PRD_STRT_DTE,'DD/MM/YYYY'));
406 WriteHrTrace(g_indent||'l_tot_inc_ytd: '
407 ||to_char(l_tot_inc_ytd));
408 WriteHrTrace(g_indent||'l_tot_inc_ptd: '
409 ||to_char(l_tot_inc_ptd));
410 g_indent := substr(g_indent,1,length(g_indent)-3);
411 EXCEPTION
412 WHEN OTHERS THEN
413 IF xpt_Msg = 'No Error' THEN
414 xpt_Msg := 'PeriodFactor: '||TO_CHAR(SQLCODE);
415 END IF;
416 RAISE xpt_E;
417 END PeriodFactor;
418 -------------------------------------------------------------------------------
419 -- PossiblePeriodsFactor --
420 -------------------------------------------------------------------------------
421 PROCEDURE PossiblePeriodsFactor AS
422 BEGIN
423 g_indent := g_indent||' ';
424 IF dbi_ZA_TX_YR_STRT >= dbi_ZA_ACT_STRT_DTE THEN
425 hr_utility.set_location(g_indent||'py_za_total_package.PPF',1);
426 trc_PosFactor := 1;
427 ELSE
428 IF trc_PrdFactor <> 1 THEN
429 hr_utility.set_location(g_indent||'py_za_total_package.PPF',2);
430 --
431 trc_PosFactor :=
432 dbi_ZA_DYS_IN_YR
433 / ( dbi_ZA_TX_YR_END
434 - dbi_ZA_CUR_PRD_STRT_DTE
435 + 1
436 );
437 ELSE
438 hr_utility.set_location(g_indent||'py_za_total_package.PPF',3);
439 --
440 trc_PosFactor :=
441 dbi_ZA_DYS_IN_YR
442 / ( dbi_ZA_TX_YR_END
443 - dbi_ZA_ACT_STRT_DTE
444 + 1
445 );
446 END IF;
447 END IF;
448 g_indent := substr(g_indent,1,length(g_indent)-3);
449 EXCEPTION
450 WHEN OTHERS THEN
451 IF xpt_Msg = 'No Error' THEN
452 xpt_Msg := 'PossiblePeriodsFactor: '||TO_CHAR(SQLCODE);
453 END IF;
454 RAISE xpt_E;
455 END PossiblePeriodsFactor;
456 -------------------------------------------------------------------------------
457 -- Annualise --
458 -------------------------------------------------------------------------------
459 FUNCTION Annualise
460 (p_YtdInc IN NUMBER
461 ,p_PtdInc IN NUMBER
462 ) RETURN NUMBER
463 AS
464 l_AnnFig1 BALANCE;
465 l_AnnFig2 BALANCE;
466 l_AnnFig3 BALANCE;
467 l_AnnFig4 BALANCE;
468
469 BEGIN
470 g_indent := g_indent||' ';
471 hr_utility.set_location(g_indent||'py_za_total_package.Annualise',1);
472 -- 1
473 l_AnnFig1 := p_PtdInc / trc_PrdFactor;
474 hr_utility.set_location(g_indent||'py_za_total_package.Annualise',2);
475 -- 2
476 l_AnnFig2 := l_AnnFig1 * dbi_ZA_PAY_PRDS_LFT;
477 hr_utility.set_location(g_indent||'py_za_total_package.Annualise',3);
478 -- 3
479 l_AnnFig3 := l_AnnFig2 + p_YtdInc - p_PtdInc;
480 hr_utility.set_location(g_indent||'py_za_total_package.Annualise',4);
481 -- 4
482 l_AnnFig4 := l_AnnFig3 * trc_PosFactor;
483 hr_utility.set_location(g_indent||'py_za_total_package.Annualise',5);
484 --
485 hr_utility.set_location(g_indent||'py_za_total_package.Annualise',6);
486 --
487 WriteHrTrace(g_indent||'p_PtdInc: '||to_char(p_PtdInc ));
488 WriteHrTrace(g_indent||'trc_PrdFactor: '||to_char(trc_PrdFactor ));
489 WriteHrTrace(g_indent||'l_AnnFig1: '||to_char(l_AnnFig1 ));
490 WriteHrTrace(g_indent||'dbi_ZA_PAY_PRDS_LFT: '||to_char(dbi_ZA_PAY_PRDS_LFT));
491 WriteHrTrace(g_indent||'l_AnnFig2: '||to_char(l_AnnFig2 ));
492 WriteHrTrace(g_indent||'p_YtdInc: '||to_char(p_YtdInc ));
493 WriteHrTrace(g_indent||'p_PtdInc: '||to_char(p_PtdInc ));
494 WriteHrTrace(g_indent||'l_AnnFig3: '||to_char(l_AnnFig3 ));
495 WriteHrTrace(g_indent||'trc_PosFactor: '||to_char(trc_PosFactor ));
496 WriteHrTrace(g_indent||'l_AnnFig4: '||to_char(l_AnnFig4 ));
497 --
498 g_indent := substr(g_indent,1,length(g_indent)-3);
499 RETURN l_AnnFig4;
500 --
501 EXCEPTION
502 WHEN OTHERS THEN
503 IF xpt_Msg = 'No Error' THEN
504 xpt_Msg := 'Annualise: '||TO_CHAR(SQLCODE);
505 END IF;
506 RAISE xpt_E;
507 END Annualise;
508 -------------------------------------------------------------------------------
509 -- DaysWorked --
510 -- Returns the number of days that the person has worked --
511 -- This could be a negative number that would indicate --
512 -- a LatePayePeriod --
513 -------------------------------------------------------------------------------
514 FUNCTION DaysWorked RETURN NUMBER
515 AS
516 l_DaysWorked NUMBER;
517 l_EndDte DATE;
518 l_StrtDte DATE;
519
520 BEGIN
521 g_indent := g_indent||' ';
522 IF trc_AnnualisationType = 'YtdCalc' THEN
523 hr_utility.set_location(g_indent||'py_za_total_package.DaysWorked',1);
524 l_EndDte := dbi_ZA_CUR_PRD_STRT_DTE - 1;
525 l_StrtDte := GREATEST(dbi_ZA_ACT_STRT_DTE, dbi_ZA_TX_YR_STRT);
526
527 ELSIF trc_AnnualisationType = 'CalCalc' THEN
528 hr_utility.set_location(g_indent||'py_za_total_package.DaysWorked',2);
529 l_EndDte := dbi_ZA_TX_YR_STRT - 1;
530 l_StrtDte := GREATEST(dbi_ZA_ACT_STRT_DTE,
531 to_date('01/01/'||to_char(to_number(to_char(dbi_ZA_TX_YR_END,'YYYY'))-1),'DD/MM/YYYY'));
532
533 ELSIF trc_AnnualisationType = 'SitCalc' AND trc_NegPtd THEN
534 hr_utility.set_location(g_indent||'py_za_total_package.DaysWorked',3);
535 l_EndDte := LEAST(dbi_ZA_ACT_END_DTE, dbi_ZA_CUR_PRD_END_DTE);
536 l_StrtDte := GREATEST(dbi_ZA_ACT_STRT_DTE, dbi_ZA_TX_YR_STRT);
537
538 ELSIF trc_AnnualisationType = 'SitCalc' THEN
539 hr_utility.set_location(g_indent||'py_za_total_package.DaysWorked',4);
540 l_EndDte := LEAST(dbi_ZA_ACT_END_DTE, dbi_ZA_TX_YR_END);
541 l_StrtDte := GREATEST(dbi_ZA_ACT_STRT_DTE, dbi_ZA_TX_YR_STRT);
542 END IF;
543
544 l_DaysWorked := l_EndDte - l_StrtDte + 1;
545
546 WriteHrTrace(g_indent||'l_EndDte: '||to_char(l_EndDte,'DD/MM/YYYY'));
547 WriteHrTrace(g_indent||'l_StrtDte: '||to_char(l_StrtDte,'DD/MM/YYYY'));
548 WriteHrTrace(g_indent||'l_DaysWorked: '||to_char(l_DaysWorked));
549 g_indent := substr(g_indent,1,length(g_indent)-3);
550
551 RETURN l_DaysWorked;
552
553 EXCEPTION
554 WHEN OTHERS THEN
555 IF xpt_Msg = 'No Error' THEN
556 xpt_Msg := 'DaysWorked: '||TO_CHAR(SQLCODE);
557 END IF;
558 RAISE xpt_E;
559 END DaysWorked;
560 -------------------------------------------------------------------------------
561 -- ClearGlobals --
562 -------------------------------------------------------------------------------
563 PROCEDURE ClearGlobals AS
564
565 BEGIN
566 g_indent := g_indent||' ';
567 hr_utility.set_location(g_indent||'py_za_total_package.ClearGlobals',1);
568
569 -- Trace Globals
570 trc_AnnBonPtd := 0;
571 trc_AnnPymPtd := 0;
572 trc_AnnTxbPkgCmpNRFI := 0;
573 trc_AnnTxbPkgCmpNRFI_Upd := 0;
574 trc_AnnTxbPkgCmpRFI := 0;
575 trc_AnnTxbPkgCmpRFI_Upd := 0;
576 trc_AnnualisationType := 'Unknown';
577 trc_NegPtd := FALSE;
578 trc_PosFactor :=0;
579 trc_PrdFactor := 0;
580 trc_PrjRFIableTotPkg := 0;
581 trc_RFIableTotPkgPTD := 0;
582 trc_RFIableTotPkgPTD_Upd := 0;
583 trc_SitFactor := 1;
584 trc_TotTxbPkgCmp := 0;
585 trc_TxbIncPtd := 0;
586 trc_TxbIncYtd := 0;
587 trc_TxbPkgCmp := 0;
588 trc_TxbPkgCmpNRFI := 0;
589 trc_TxbPkgCmpNRFI_Upd := 0;
590 trc_TxbPkgCmpRFI := 0;
591 trc_TxbPkgCmpRFI_Upd := 0;
592 trc_TxbPrc := 0;
593 -- Global Exception Message
594 xpt_Msg := 'No Error';
595 g_indent := substr(g_indent,1,length(g_indent)-3);
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 IF xpt_Msg = 'No Error' THEN
600 xpt_Msg := 'ClearGlobals: '||TO_CHAR(SQLCODE);
601 END IF;
602 RAISE xpt_E;
603 END ClearGlobals;
604 -------------------------------------------------------------------------------
605 -- split_totpkg --
606 -- --
607 -------------------------------------------------------------------------------
608 FUNCTION split_totpkg(
609 -- Database Items
610 p_FXD_PRC IN NUMBER
611 ,p_TOT_PKG IN NUMBER
612 ,p_ZA_ACT_END_DTE IN DATE
613 ,p_ZA_ACT_STRT_DTE IN DATE
614 ,p_ZA_ASG_TAX_RTR_RSLTS IN VARCHAR2
615 ,p_ZA_ASG_TX_RTR_PRD IN VARCHAR2
616 ,p_ZA_ASG_TX_YR IN NUMBER
617 ,p_ZA_CUR_PRD_END_DTE IN DATE
618 ,p_ZA_CUR_PRD_STRT_DTE IN DATE
619 ,p_ZA_DYS_IN_YR IN NUMBER
620 ,p_ZA_PAY_PRDS_LFT IN NUMBER
621 ,p_ZA_PAY_PRDS_PER_YR IN NUMBER
622 ,p_ZA_TX_YR_END IN DATE
623 ,p_ZA_TX_YR_STRT IN DATE
624 -- Balances
625 ,p_ANN_TXB_PKG_CMP_NRFI_YTD IN NUMBER
626 ,p_ANN_TXB_PKG_CMP_RFI_YTD IN NUMBER
627 ,p_ANN_TXB_PKG_CMP_YTD IN NUMBER
628 ,p_BP_PTD IN NUMBER
629 ,p_BP_YTD IN NUMBER
630 ,p_RFIABLE_TOT_PKG_CYTD IN NUMBER
631 ,p_RFIABLE_TOT_PKG_PTD IN NUMBER
632 ,p_RFIABLE_TOT_PKG_YTD IN NUMBER
633 ,p_TOT_TXB_AB_RUN IN NUMBER
634 ,p_TOT_TXB_AP_YTD IN NUMBER
635 ,p_TOT_TXB_FB_CYTD IN NUMBER
636 ,p_TOT_TXB_FB_PTD IN NUMBER
637 ,p_TOT_TXB_FB_YTD IN NUMBER
638 ,p_TOT_TXB_NI_CYTD IN NUMBER
639 ,p_TOT_TXB_NI_PTD IN NUMBER
640 ,p_TOT_TXB_NI_YTD IN NUMBER
641 ,p_TOT_TXB_TA_CYTD IN NUMBER
642 ,p_TOT_TXB_TA_PTD IN NUMBER
643 ,p_TOT_TXB_TA_YTD IN NUMBER
644 ,p_TXB_PKG_CMP_CYTD IN NUMBER
645 ,p_TXB_PKG_CMP_NRFI_YTD IN NUMBER
646 ,p_TXB_PKG_CMP_PTD IN NUMBER
647 ,p_TXB_PKG_CMP_RFI_YTD IN NUMBER
648 ,p_TXB_PKG_CMP_YTD IN NUMBER
649 -- Out Parameters
650 ,p_RFIableTotPkgPTD_Upd OUT NOCOPY NUMBER
651 ,p_AnnTxbPkgCmpRFI_Upd OUT NOCOPY NUMBER
652 ,p_AnnTxbPkgCmpNRFI_Upd OUT NOCOPY NUMBER
653 ,p_TxbPkgCmpRFI_Upd OUT NOCOPY NUMBER
654 ,p_TxbPkgCmpNRFI_Upd OUT NOCOPY NUMBER
655 ) RETURN NUMBER
656 AS
657 -- Variables
658 --
659 l_Dum NUMBER := 1;
660
661 -------------------------------------------------------------------------------
662 BEGIN-- MAIN --
663 -------------------------------------------------------------------------------
664 -- Set hr_utility globals if debugging
665 --
666 --g_HrTraceEnabled := TRUE;
667 --g_HrTracePipeName := 'ZATOTPKG';
668
669 -- Call hr_utility start procedure
670 StartHrTrace;
671
672 WriteHrTrace(' ');
673 WriteHrTrace(' ');
674 WriteHrTrace(' ');
675 WriteHrTrace('------------------------------------------------------------');
676 WriteHrTrace('-- Start of Total Package Trace File');
677 WriteHrTrace('------------------------------------------------------------');
678 WriteHrTrace(' ');
679 WriteHrTrace('-- Values passed in');
680 WriteHrTrace('-- Database Items');
681 WriteHrTrace('p_FXD_PRC '||to_char(p_FXD_PRC ));
682 WriteHrTrace('p_TOT_PKG '||to_char(p_TOT_PKG ));
683 WriteHrTrace('p_ZA_ACT_END_DTE '||to_char(p_ZA_ACT_END_DTE ,'DD/MM/YYYY'));
684 WriteHrTrace('p_ZA_ACT_STRT_DTE '||to_char(p_ZA_ACT_STRT_DTE ,'DD/MM/YYYY'));
685 WriteHrTrace('p_ZA_ASG_TAX_RTR_RSLTS '|| p_ZA_ASG_TAX_RTR_RSLTS );
686 WriteHrTrace('p_ZA_ASG_TX_RTR_PRD '|| p_ZA_ASG_TX_RTR_PRD );
687 WriteHrTrace('p_ZA_ASG_TX_YR '||to_char(p_ZA_ASG_TX_YR ));
688 WriteHrTrace('p_ZA_CUR_PRD_END_DTE '||to_char(p_ZA_CUR_PRD_END_DTE ,'DD/MM/YYYY'));
689 WriteHrTrace('p_ZA_CUR_PRD_STRT_DTE '||to_char(p_ZA_CUR_PRD_STRT_DTE,'DD/MM/YYYY'));
690 WriteHrTrace('p_ZA_DYS_IN_YR '||to_char(p_ZA_DYS_IN_YR ));
691 WriteHrTrace('p_ZA_PAY_PRDS_LFT '||to_char(p_ZA_PAY_PRDS_LFT ));
692 WriteHrTrace('p_ZA_PAY_PRDS_PER_YR '||to_char(p_ZA_PAY_PRDS_PER_YR ));
693 WriteHrTrace('p_ZA_TX_YR_END '||to_char(p_ZA_TX_YR_END ,'DD/MM/YYYY'));
694 WriteHrTrace('p_ZA_TX_YR_STRT '||to_char(p_ZA_TX_YR_STRT ,'DD/MM/YYYY'));
695 WriteHrTrace('-- Balances');
696 WriteHrTrace('p_ANN_TXB_PKG_CMP_NRFI_YTD '||to_char(p_ANN_TXB_PKG_CMP_NRFI_YTD ));
697 WriteHrTrace('p_ANN_TXB_PKG_CMP_RFI_YTD '||to_char(p_ANN_TXB_PKG_CMP_RFI_YTD ));
698 WriteHrTrace('p_ANN_TXB_PKG_CMP_YTD '||to_char(p_ANN_TXB_PKG_CMP_YTD ));
699 WriteHrTrace('p_BP_PTD '||to_char(p_BP_PTD ));
700 WriteHrTrace('p_BP_YTD '||to_char(p_BP_YTD ));
701 WriteHrTrace('p_RFIABLE_TOT_PKG_CYTD '||to_char(p_RFIABLE_TOT_PKG_CYTD ));
702 WriteHrTrace('p_RFIABLE_TOT_PKG_PTD '||to_char(p_RFIABLE_TOT_PKG_PTD ));
703 WriteHrTrace('p_RFIABLE_TOT_PKG_YTD '||to_char(p_RFIABLE_TOT_PKG_YTD ));
704 WriteHrTrace('p_TOT_TXB_AB_RUN '||to_char(p_TOT_TXB_AB_RUN ));
705 WriteHrTrace('p_TOT_TXB_AP_YTD '||to_char(p_TOT_TXB_AP_YTD ));
706 WriteHrTrace('p_TOT_TXB_FB_CYTD '||to_char(p_TOT_TXB_FB_CYTD ));
707 WriteHrTrace('p_TOT_TXB_FB_PTD '||to_char(p_TOT_TXB_FB_PTD ));
708 WriteHrTrace('p_TOT_TXB_FB_YTD '||to_char(p_TOT_TXB_FB_YTD ));
709 WriteHrTrace('p_TOT_TXB_NI_CYTD '||to_char(p_TOT_TXB_NI_CYTD ));
710 WriteHrTrace('p_TOT_TXB_NI_PTD '||to_char(p_TOT_TXB_NI_PTD ));
711 WriteHrTrace('p_TOT_TXB_NI_YTD '||to_char(p_TOT_TXB_NI_YTD ));
712 WriteHrTrace('p_TOT_TXB_TA_CYTD '||to_char(p_TOT_TXB_TA_CYTD ));
713 WriteHrTrace('p_TOT_TXB_TA_PTD '||to_char(p_TOT_TXB_TA_PTD ));
714 WriteHrTrace('p_TOT_TXB_TA_YTD '||to_char(p_TOT_TXB_TA_YTD ));
715 WriteHrTrace('p_TXB_PKG_CMP_CYTD '||to_char(p_TXB_PKG_CMP_CYTD ));
716 WriteHrTrace('p_TXB_PKG_CMP_NRFI_YTD '||to_char(p_TXB_PKG_CMP_NRFI_YTD ));
717 WriteHrTrace('p_TXB_PKG_CMP_PTD '||to_char(p_TXB_PKG_CMP_PTD ));
718 WriteHrTrace('p_TXB_PKG_CMP_RFI_YTD '||to_char(p_TXB_PKG_CMP_RFI_YTD ));
719 WriteHrTrace('p_TXB_PKG_CMP_YTD '||to_char(p_TXB_PKG_CMP_YTD ));
720 WriteHrTrace('-- Out Parameters');
721 --
722 hr_utility.set_location('py_za_total_package.split_totpkg',1);
723 -------------------------------------------------------------------------------
724 -- Set variables
725 -------------------------------------------------------------------------------
726 -- Database Items
727 dbi_FXD_PRC := p_FXD_PRC;
728 dbi_TOT_PKG := p_TOT_PKG;
729 dbi_ZA_ACT_END_DTE := p_ZA_ACT_END_DTE;
730 dbi_ZA_ACT_STRT_DTE := p_ZA_ACT_STRT_DTE;
731 dbi_ZA_ASG_TAX_RTR_RSLTS := p_ZA_ASG_TAX_RTR_RSLTS;
732 dbi_ZA_ASG_TX_RTR_PRD := p_ZA_ASG_TX_RTR_PRD;
733 dbi_ZA_ASG_TX_YR := p_ZA_ASG_TX_YR;
734 dbi_ZA_CUR_PRD_END_DTE := p_ZA_CUR_PRD_END_DTE;
735 dbi_ZA_CUR_PRD_STRT_DTE := p_ZA_CUR_PRD_STRT_DTE;
736 dbi_ZA_DYS_IN_YR := p_ZA_DYS_IN_YR;
737 dbi_ZA_PAY_PRDS_LFT := p_ZA_PAY_PRDS_LFT;
738 dbi_ZA_PAY_PRDS_PER_YR := p_ZA_PAY_PRDS_PER_YR;
739 dbi_ZA_TX_YR_END := p_ZA_TX_YR_END;
740 dbi_ZA_TX_YR_STRT := p_ZA_TX_YR_STRT;
741 -------------------------------------------------------------------------------
742 -- Balances
743 -------------------------------------------------------------------------------
744 bal_ANN_TXB_PKG_CMP_NRFI_YTD := p_ANN_TXB_PKG_CMP_NRFI_YTD;
745 bal_ANN_TXB_PKG_CMP_RFI_YTD := p_ANN_TXB_PKG_CMP_RFI_YTD;
746 bal_ANN_TXB_PKG_CMP_YTD := p_ANN_TXB_PKG_CMP_YTD;
747 bal_BP_PTD := p_BP_PTD;
748 bal_BP_YTD := p_BP_YTD;
749 bal_RFIABLE_TOT_PKG_CYTD := p_RFIABLE_TOT_PKG_CYTD;
750 bal_RFIABLE_TOT_PKG_PTD := p_RFIABLE_TOT_PKG_PTD;
751 bal_RFIABLE_TOT_PKG_YTD := p_RFIABLE_TOT_PKG_YTD;
752 bal_TOT_TXB_AB_RUN := p_TOT_TXB_AB_RUN;
753 bal_TOT_TXB_AP_YTD := p_TOT_TXB_AP_YTD;
754 bal_TOT_TXB_FB_CYTD := p_TOT_TXB_FB_CYTD;
755 bal_TOT_TXB_FB_PTD := p_TOT_TXB_FB_PTD;
756 bal_TOT_TXB_FB_YTD := p_TOT_TXB_FB_YTD;
757 bal_TOT_TXB_NI_CYTD := p_TOT_TXB_NI_CYTD;
758 bal_TOT_TXB_NI_PTD := p_TOT_TXB_NI_PTD;
759 bal_TOT_TXB_NI_YTD := p_TOT_TXB_NI_YTD;
760 bal_TOT_TXB_TA_CYTD := p_TOT_TXB_TA_CYTD;
761 bal_TOT_TXB_TA_PTD := p_TOT_TXB_TA_PTD;
762 bal_TOT_TXB_TA_YTD := p_TOT_TXB_TA_YTD;
763 bal_TXB_PKG_CMP_CYTD := p_TXB_PKG_CMP_CYTD;
764 bal_TXB_PKG_CMP_NRFI_YTD := p_TXB_PKG_CMP_NRFI_YTD;
765 bal_TXB_PKG_CMP_PTD := p_TXB_PKG_CMP_PTD;
766 bal_TXB_PKG_CMP_RFI_YTD := p_TXB_PKG_CMP_RFI_YTD;
767 bal_TXB_PKG_CMP_YTD := p_TXB_PKG_CMP_YTD;
768
769
770 hr_utility.set_location('py_za_total_package.split_totpkg',2);
771
772 -------------------------------------------------------------------------------
773 -- Calculate PTD RFIable Total Package value
774 -------------------------------------------------------------------------------
775 trc_RFIableTotPkgPTD := dbi_TOT_PKG * (dbi_FXD_PRC / 100) / dbi_ZA_PAY_PRDS_PER_YR;
776 hr_utility.set_location('py_za_total_package.split_totpkg',3);
777 -- Calculate the update value
778 --
779 trc_RFIableTotPkgPTD_Upd := trc_RFIableTotPkgPTD - bal_RFIABLE_TOT_PKG_PTD;
780
781 -------------------------------------------------------------------------------
782 -- Split Taxable Package Components
783 -------------------------------------------------------------------------------
784 -- Check if there is taxable income in the package
785 --
786 trc_TotTxbPkgCmp := bal_TXB_PKG_CMP_YTD + bal_ANN_TXB_PKG_CMP_YTD;
787
788 IF trc_TotTxbPkgCmp <> 0 THEN
789 hr_utility.set_location('py_za_total_package.split_totpkg',4);
790 -- Annualisation Check
791 --
792 IF LatePayPeriod THEN
793 hr_utility.set_location('py_za_total_package.split_totpkg',5);
794 trc_AnnualisationType := 'LteCalc';
795 -- Is this a SITE Period?
796 ELSIF EmpTermPrePeriod THEN
797 hr_utility.set_location('py_za_total_package.split_totpkg',6);
798 trc_AnnualisationType := 'SitCalc';
799 ELSIF LstPeriod OR EmpTermInPeriod THEN
800 hr_utility.set_location('py_za_total_package.split_totpkg',7);
801 IF PreErnPeriod THEN
802 hr_utility.set_location('py_za_total_package.split_totpkg',8);
803 trc_AnnualisationType := 'YtdCalc';
804 ELSE
805 hr_utility.set_location('py_za_total_package.split_totpkg',9);
806 trc_AnnualisationType := 'SitCalc';
807 END IF;
808 ElSE
809 hr_utility.set_location('py_za_total_package.split_totpkg',10);
810 -- The employee has NOT been terminated!
811 IF PreErnPeriod THEN
812 hr_utility.set_location('py_za_total_package.split_totpkg',11);
813 trc_AnnualisationType := 'YtdCalc';
814 --Bug 4346955 bug 5148830
815 /* ELSIF dbi_ZA_ASG_TX_RTR_PRD = 'Y' THEN
816 hr_utility.set_location('py_za_total_package.split_totpkg',12);
817 IF dbi_ZA_ASG_TAX_RTR_RSLTS = 'Y' THEN
818 hr_utility.set_location('py_za_total_package.split_totpkg',13);
819 trc_AnnualisationType := 'SitCalc';
820 ELSIF NegPtd THEN
821 hr_utility.set_location('py_za_total_package.split_totpkg',14);
822 trc_AnnualisationType := 'SitCalc';
823 ELSE
824 hr_utility.set_location('py_za_total_package.split_totpkg',15);
825 trc_AnnualisationType := 'NorCalc';
826 END IF; */
827 ELSIF NegPtd THEN
828 hr_utility.set_location('py_za_total_package.split_totpkg',16);
829 trc_AnnualisationType := 'SitCalc';
830 ELSE
831 hr_utility.set_location('py_za_total_package.split_totpkg',17);
832 trc_AnnualisationType := 'NorCalc';
833 END IF;
834 END IF;
835 --
836 -- Pre-Earnings Check
837 --
838 IF trc_AnnualisationType = 'YtdCalc' THEN
839 hr_utility.set_location('py_za_total_package.split_totpkg',18);
840 -- Ytd Taxable Income
841 --
842 trc_TxbIncYtd := ( bal_TOT_TXB_NI_YTD
843 + bal_TOT_TXB_FB_YTD
844 + bal_TOT_TXB_TA_YTD
845 + bal_BP_YTD
846 );
847 -- If the Ytd Taxable Income = 0, execute the CalCalc
848 --
849 IF trc_TxbIncYtd = 0 THEN
850 hr_utility.set_location('py_za_total_package.split_totpkg',19);
851 -- Calendar Ytd Taxable Income
852 --
853 trc_TxbIncYtd := ( bal_TOT_TXB_NI_CYTD
854 + bal_TOT_TXB_FB_CYTD
855 + bal_TOT_TXB_TA_CYTD );
856 -- If there is no Income Execute the Base calculation
857 --
858 IF trc_TxbIncYtd = 0 THEN
859 hr_utility.set_location('py_za_total_package.split_totpkg',20);
860 trc_AnnualisationType := 'BasCalc';
861 ELSE
862 hr_utility.set_location('py_za_total_package.split_totpkg',21);
863 trc_AnnualisationType := 'CalCalc';
864 END IF;
865 END IF;
866
867 END IF;
868
869 -- Set Factors
870 --
871 IF trc_AnnualisationType IN ('SitCalc','YtdCalc','CalCalc') THEN
872 hr_utility.set_location('py_za_total_package.split_totpkg',22);
873 trc_SitFactor := dbi_ZA_DYS_IN_YR / DaysWorked;
874 END IF;
875
876 IF trc_AnnualisationType = 'NorCalc' THEN
877 hr_utility.set_location('py_za_total_package.split_totpkg',23);
878 PeriodFactor;
879 PossiblePeriodsFactor;
880 END IF;
881
882 -- Annualise RFIable Total Package
883 -- trc_PrjRFIableTotPkg := ( trc_RFIableTotPkgPTD
884 -- * dbi_ZA_PAY_PRDS_LFT )
885 -- + bal_RFIABLE_TOT_PKG_YTD
886 -- - bal_RFIABLE_TOT_PKG_PTD;
887 -------------------------------------------------------------------------
888 IF trc_AnnualisationType = 'NorCalc' THEN --
889 -------------------------------------------------------------------------
890 -- Annualise RFIable Total Package
891 trc_PrjRFIableTotPkg := Annualise(p_YtdInc => bal_RFIABLE_TOT_PKG_YTD
892 + trc_RFIableTotPkgPTD_Upd
893 ,p_PtdInc => bal_RFIABLE_TOT_PKG_PTD
894 + trc_RFIableTotPkgPTD_Upd
895 );
896 -- Annualise TxbPkgCmps
897 trc_TxbPkgCmp := Annualise(p_YtdInc => bal_TXB_PKG_CMP_YTD
898 ,p_PtdInc => bal_TXB_PKG_CMP_PTD
899 );
900 hr_utility.set_location('py_za_total_package.split_totpkg',24);
901 --
902 trc_TotTxbPkgCmp := trc_TxbPkgCmp + bal_ANN_TXB_PKG_CMP_YTD;
903 -- Calculate Split %
904 --
905 trc_TxbPrc := least((trc_PrjRFIableTotPkg / trc_TotTxbPkgCmp),1);
906
907 -- Calculate Split Values
908 -- Periodic
909 hr_utility.set_location('py_za_total_package.split_totpkg',25);
910 trc_TxbPkgCmpRFI := bal_TXB_PKG_CMP_YTD * trc_TxbPrc;
911 trc_TxbPkgCmpNRFI := bal_TXB_PKG_CMP_YTD - trc_TxbPkgCmpRFI;
912 -- Annual
913 hr_utility.set_location('py_za_total_package.split_totpkg',26);
914 trc_AnnTxbPkgCmpRFI := bal_ANN_TXB_PKG_CMP_YTD * trc_TxbPrc;
915 trc_AnnTxbPkgCmpNRFI := bal_ANN_TXB_PKG_CMP_YTD - trc_AnnTxbPkgCmpRFI;
916
917 -- Calculate Update Values
918 -- Periodic
919 hr_utility.set_location('py_za_total_package.split_totpkg',27);
920 trc_TxbPkgCmpRFI_Upd := trc_TxbPkgCmpRFI - bal_TXB_PKG_CMP_RFI_YTD;
921 trc_TxbPkgCmpNRFI_Upd := trc_TxbPkgCmpNRFI - bal_TXB_PKG_CMP_NRFI_YTD;
922 -- Annual
923 hr_utility.set_location('py_za_total_package.split_totpkg',28);
924 trc_AnnTxbPkgCmpRFI_Upd := trc_AnnTxbPkgCmpRFI - bal_ANN_TXB_PKG_CMP_RFI_YTD;
925 trc_AnnTxbPkgCmpNRFI_Upd := trc_AnnTxbPkgCmpNRFI - bal_ANN_TXB_PKG_CMP_NRFI_YTD;
926
927 hr_utility.set_location('py_za_total_package.split_totpkg',29);
928
929 -------------------------------------------------------------------------
930 ELSIF trc_AnnualisationType = 'SitCalc' THEN --
931 -------------------------------------------------------------------------
932 -- Annualise RFIable Total Package
933 --
934 trc_PrjRFIableTotPkg :=
935 ( bal_RFIABLE_TOT_PKG_YTD + trc_RFIableTotPkgPTD_Upd ) * trc_SitFactor;
936 --
937 -- Annualise TxbPkgCmps
938 --
939 trc_TxbPkgCmp := bal_TXB_PKG_CMP_YTD * trc_SitFactor;
940 trc_TotTxbPkgCmp := trc_TxbPkgCmp + bal_ANN_TXB_PKG_CMP_YTD;
941 --
942 -- Calculate Split %
943 --
944 trc_TxbPrc := least((trc_PrjRFIableTotPkg / trc_TotTxbPkgCmp),1);
945 -- Calculate Split Values
946 --
947 --
948 -- Periodic
949 --
950 hr_utility.set_location('py_za_total_package.split_totpkg',30);
951 trc_TxbPkgCmpRFI := bal_TXB_PKG_CMP_YTD * trc_TxbPrc;
952 trc_TxbPkgCmpNRFI := bal_TXB_PKG_CMP_YTD - trc_TxbPkgCmpRFI;
953 --
954 -- Annual
955 --
956 hr_utility.set_location('py_za_total_package.split_totpkg',31);
957 trc_AnnTxbPkgCmpRFI := bal_ANN_TXB_PKG_CMP_YTD * trc_TxbPrc;
958 trc_AnnTxbPkgCmpNRFI := bal_ANN_TXB_PKG_CMP_YTD - trc_AnnTxbPkgCmpRFI;
959 --
960 -- Calculate Update Values
961 --
962 --
963 -- Periodic
964 --
965 hr_utility.set_location('py_za_total_package.split_totpkg',32);
966 trc_TxbPkgCmpRFI_Upd := trc_TxbPkgCmpRFI - bal_TXB_PKG_CMP_RFI_YTD;
967 trc_TxbPkgCmpNRFI_Upd := trc_TxbPkgCmpNRFI - bal_TXB_PKG_CMP_NRFI_YTD;
968 --
969 -- Annual
970 --
971 hr_utility.set_location('py_za_total_package.split_totpkg',33);
972 trc_AnnTxbPkgCmpRFI_Upd := trc_AnnTxbPkgCmpRFI - bal_ANN_TXB_PKG_CMP_RFI_YTD;
973 trc_AnnTxbPkgCmpNRFI_Upd := trc_AnnTxbPkgCmpNRFI - bal_ANN_TXB_PKG_CMP_NRFI_YTD;
974 --
975 hr_utility.set_location('py_za_total_package.split_totpkg',34);
976 --
977 -------------------------------------------------------------------------
978 ELSIF trc_AnnualisationType = 'YtdCalc' THEN --
979 -------------------------------------------------------------------------
980 -- Annualise RFIable Total Package
981 --
982 trc_PrjRFIableTotPkg := bal_RFIABLE_TOT_PKG_YTD * trc_SitFactor;
983 -- Annualise TxbPkgCmps
984 trc_TxbPkgCmp := bal_TXB_PKG_CMP_YTD * trc_SitFactor;
985 trc_TotTxbPkgCmp := trc_TxbPkgCmp + bal_ANN_TXB_PKG_CMP_YTD;
986
987 -- Calculate Split %
988 trc_TxbPrc := least((trc_PrjRFIableTotPkg / trc_TotTxbPkgCmp),1);
989
990 -- Calculate Split Values
991 -- Annual
992 hr_utility.set_location('py_za_total_package.split_totpkg',35);
993 trc_AnnTxbPkgCmpRFI := bal_ANN_TXB_PKG_CMP_YTD * trc_TxbPrc;
994 trc_AnnTxbPkgCmpNRFI := bal_ANN_TXB_PKG_CMP_YTD - trc_AnnTxbPkgCmpRFI;
995
996 -- Calculate Update Values
997 -- Annual
998 hr_utility.set_location('py_za_total_package.split_totpkg',36);
999 trc_AnnTxbPkgCmpRFI_Upd := trc_AnnTxbPkgCmpRFI - bal_ANN_TXB_PKG_CMP_RFI_YTD;
1000 trc_AnnTxbPkgCmpNRFI_Upd := trc_AnnTxbPkgCmpNRFI - bal_ANN_TXB_PKG_CMP_NRFI_YTD;
1001
1002 hr_utility.set_location('py_za_total_package.split_totpkg',37);
1003
1004 -------------------------------------------------------------------------
1005 ELSIF trc_AnnualisationType = 'CalCalc' THEN --
1006 -------------------------------------------------------------------------
1007 -- Annualise RFIable Total Package
1008 --
1009 trc_PrjRFIableTotPkg := bal_RFIABLE_TOT_PKG_CYTD * trc_SitFactor;
1010 -- Annualise TxbPkgCmps
1011 trc_TxbPkgCmp := bal_TXB_PKG_CMP_CYTD * trc_SitFactor;
1012 trc_TotTxbPkgCmp := trc_TxbPkgCmp + bal_ANN_TXB_PKG_CMP_YTD;
1013
1014 -- Calculate Split %
1015 trc_TxbPrc := least((trc_PrjRFIableTotPkg / trc_TotTxbPkgCmp),1);
1016
1017 -- Calculate Split Values
1018 -- Annual
1019 hr_utility.set_location('py_za_total_package.split_totpkg',38);
1020 trc_AnnTxbPkgCmpRFI := bal_ANN_TXB_PKG_CMP_YTD * trc_TxbPrc;
1021 trc_AnnTxbPkgCmpNRFI := bal_ANN_TXB_PKG_CMP_YTD - trc_AnnTxbPkgCmpRFI;
1022
1023 -- Calculate Update Values
1024 -- Annual
1025 hr_utility.set_location('py_za_total_package.split_totpkg',39);
1026 trc_AnnTxbPkgCmpRFI_Upd := trc_AnnTxbPkgCmpRFI - bal_ANN_TXB_PKG_CMP_RFI_YTD;
1027 trc_AnnTxbPkgCmpNRFI_Upd := trc_AnnTxbPkgCmpNRFI - bal_ANN_TXB_PKG_CMP_NRFI_YTD;
1028
1029 hr_utility.set_location('py_za_total_package.split_totpkg',40);
1030
1031 -------------------------------------------------------------------------
1032 ELSIF trc_AnnualisationType = 'BasCalc' THEN --
1033 -------------------------------------------------------------------------
1034 -- Annualise RFIable Total Package
1035 --
1036 trc_PrjRFIableTotPkg := trc_RFIableTotPkgPTD * dbi_ZA_PAY_PRDS_PER_YR;
1037 -- Annualise TxbPkgCmps
1038 trc_TxbPkgCmp := bal_ANN_TXB_PKG_CMP_YTD * dbi_ZA_PAY_PRDS_PER_YR;
1039 trc_TotTxbPkgCmp := trc_TxbPkgCmp + bal_ANN_TXB_PKG_CMP_YTD;
1040
1041 -- Calculate Split %
1042 trc_TxbPrc := least((trc_PrjRFIableTotPkg / trc_TotTxbPkgCmp),1);
1043
1044 -- Calculate Split Values
1045 -- Annual
1046 hr_utility.set_location('py_za_total_package.split_totpkg',41);
1047 trc_AnnTxbPkgCmpRFI := bal_ANN_TXB_PKG_CMP_YTD * trc_TxbPrc;
1048 trc_AnnTxbPkgCmpNRFI := bal_ANN_TXB_PKG_CMP_YTD - trc_AnnTxbPkgCmpRFI;
1049
1050 -- Calculate Update Values
1051 -- Annual
1052 hr_utility.set_location('py_za_total_package.split_totpkg',42);
1053 trc_AnnTxbPkgCmpRFI_Upd := trc_AnnTxbPkgCmpRFI - bal_ANN_TXB_PKG_CMP_RFI_YTD;
1054 trc_AnnTxbPkgCmpNRFI_Upd := trc_AnnTxbPkgCmpNRFI - bal_ANN_TXB_PKG_CMP_NRFI_YTD;
1055
1056 hr_utility.set_location('py_za_total_package.split_totpkg',43);
1057
1058 -------------------------------------------------------------------------
1059 ELSIF trc_AnnualisationType = 'LteCalc' THEN --
1060 -------------------------------------------------------------------------
1061 -- DO NOT Annualise RFIable Total Package
1062 -- Override trc_PrjRFIableTotPkg
1063 trc_PrjRFIableTotPkg := bal_RFIABLE_TOT_PKG_YTD;
1064
1065 -- Annualise TxbPkgCmps
1066 trc_TxbPkgCmp := bal_TXB_PKG_CMP_YTD;
1067 trc_TotTxbPkgCmp := trc_TxbPkgCmp + bal_ANN_TXB_PKG_CMP_YTD;
1068
1069 -- Calculate Split %
1070 trc_TxbPrc := least((trc_PrjRFIableTotPkg / trc_TotTxbPkgCmp),1);
1071
1072 -- Calculate Split Values
1073 -- Periodic
1074 hr_utility.set_location('py_za_total_package.split_totpkg',44);
1075 trc_TxbPkgCmpRFI := bal_TXB_PKG_CMP_YTD * trc_TxbPrc;
1076 trc_TxbPkgCmpNRFI := bal_TXB_PKG_CMP_YTD - trc_TxbPkgCmpRFI;
1077 -- Annual
1078 hr_utility.set_location('py_za_total_package.split_totpkg',45);
1079 trc_AnnTxbPkgCmpRFI := bal_ANN_TXB_PKG_CMP_YTD * trc_TxbPrc;
1080 trc_AnnTxbPkgCmpNRFI := bal_ANN_TXB_PKG_CMP_YTD - trc_AnnTxbPkgCmpRFI;
1081
1082 -- Calculate Update Values
1083 -- Periodic
1084 hr_utility.set_location('py_za_total_package.split_totpkg',46);
1085 trc_TxbPkgCmpRFI_Upd := trc_TxbPkgCmpRFI - bal_TXB_PKG_CMP_RFI_YTD;
1086 trc_TxbPkgCmpNRFI_Upd := trc_TxbPkgCmpNRFI - bal_TXB_PKG_CMP_NRFI_YTD;
1087 -- Annual
1088 hr_utility.set_location('py_za_total_package.split_totpkg',47);
1089 trc_AnnTxbPkgCmpRFI_Upd := trc_AnnTxbPkgCmpRFI - bal_ANN_TXB_PKG_CMP_RFI_YTD;
1090 trc_AnnTxbPkgCmpNRFI_Upd := trc_AnnTxbPkgCmpNRFI - bal_ANN_TXB_PKG_CMP_NRFI_YTD;
1091
1092 hr_utility.set_location('py_za_total_package.split_totpkg',48);
1093
1094 -------------------------------------------------------------------------
1095 END IF;-- End CalcTyp Check
1096 -------------------------------------------------------------------------
1097 ELSE
1098 -- There exist no taxable package component values
1099 -- Calculate PTD update values
1100 --
1101 -- Periodic
1102 hr_utility.set_location('py_za_total_package.split_totpkg',49);
1103 trc_TxbPkgCmpRFI_Upd := trc_TxbPkgCmpRFI - bal_TXB_PKG_CMP_RFI_YTD;
1104 trc_TxbPkgCmpNRFI_Upd := trc_TxbPkgCmpNRFI - bal_TXB_PKG_CMP_NRFI_YTD;
1105 -- Annual
1106 hr_utility.set_location('py_za_total_package.split_totpkg',50);
1107 trc_AnnTxbPkgCmpRFI_Upd := trc_AnnTxbPkgCmpRFI - bal_ANN_TXB_PKG_CMP_RFI_YTD;
1108 trc_AnnTxbPkgCmpNRFI_Upd := trc_AnnTxbPkgCmpNRFI - bal_ANN_TXB_PKG_CMP_NRFI_YTD;
1109
1110 hr_utility.set_location('py_za_total_package.split_totpkg',51);
1111
1112 END IF;
1113
1114 -- Set output parameters
1115 --
1116 p_RFIableTotPkgPTD_Upd := trc_RFIableTotPkgPTD_Upd;
1117 p_AnnTxbPkgCmpRFI_Upd := trc_AnnTxbPkgCmpRFI_Upd;
1118 p_AnnTxbPkgCmpNRFI_Upd := trc_AnnTxbPkgCmpNRFI_Upd;
1119 p_TxbPkgCmpRFI_Upd := trc_TxbPkgCmpRFI_Upd;
1120 p_TxbPkgCmpNRFI_Upd := trc_TxbPkgCmpNRFI_Upd;
1121
1122 hr_utility.set_location('py_za_total_package.split_totpkg',52);
1123
1124 WriteHrTrace('-- Trace Variables');
1125 WriteHrTrace('trc_AnnBonPtd '||to_char(trc_AnnBonPtd ));
1126 WriteHrTrace('trc_AnnPymPtd '||to_char(trc_AnnPymPtd ));
1127 WriteHrTrace('trc_AnnTxbPkgCmpNRFI '||to_char(trc_AnnTxbPkgCmpNRFI ));
1128 WriteHrTrace('trc_AnnTxbPkgCmpNRFI_Upd '||to_char(trc_AnnTxbPkgCmpNRFI_Upd ));
1129 WriteHrTrace('trc_AnnTxbPkgCmpRFI '||to_char(trc_AnnTxbPkgCmpRFI ));
1130 WriteHrTrace('trc_AnnTxbPkgCmpRFI_Upd '||to_char(trc_AnnTxbPkgCmpRFI_Upd ));
1131 WriteHrTrace('trc_AnnualisationType '||to_char(trc_AnnualisationType ));
1132 WriteHrTrace('trc_PosFactor '||to_char(trc_PosFactor ));
1133 WriteHrTrace('trc_PrdFactor '||to_char(trc_PrdFactor ));
1134 WriteHrTrace('trc_PrjRFIableTotPkg '||to_char(trc_PrjRFIableTotPkg ));
1135 WriteHrTrace('trc_RFIableTotPkgPTD '||to_char(trc_RFIableTotPkgPTD ));
1136 WriteHrTrace('trc_RFIableTotPkgPTD_Upd '||to_char(trc_RFIableTotPkgPTD_Upd ));
1137 WriteHrTrace('trc_SitFactor '||to_char(trc_SitFactor ));
1138 WriteHrTrace('trc_TotTxbPkgCmp '||to_char(trc_TotTxbPkgCmp ));
1139 WriteHrTrace('trc_TxbIncPtd '||to_char(trc_TxbIncPtd ));
1140 WriteHrTrace('trc_TxbIncYtd '||to_char(trc_TxbIncYtd ));
1141 WriteHrTrace('trc_TxbPkgCmp '||to_char(trc_TxbPkgCmp ));
1142 WriteHrTrace('trc_TxbPkgCmpNRFI '||to_char(trc_TxbPkgCmpNRFI ));
1143 WriteHrTrace('trc_TxbPkgCmpNRFI_Upd '||to_char(trc_TxbPkgCmpNRFI_Upd ));
1144 WriteHrTrace('trc_TxbPkgCmpRFI '||to_char(trc_TxbPkgCmpRFI ));
1145 WriteHrTrace('trc_TxbPkgCmpRFI_Upd '||to_char(trc_TxbPkgCmpRFI_Upd ));
1146 WriteHrTrace('trc_TxbPrc '||to_char(trc_TxbPrc ));
1147
1148 hr_utility.set_location('py_za_total_package.split_totpkg',53);
1149
1150 ClearGlobals;
1151
1152 -- End off Trace File
1153 --
1154 WriteHrTrace(' ');
1155 WriteHrTrace('------------------------------------------------------------');
1156 WriteHrTrace('-- End of Total Package Trace File --');
1157 WriteHrTrace('------------------------------------------------------------');
1158 WriteHrTrace(' -- ');
1159
1160 -- Stop Trace File
1161 --
1162 StopHrTrace;
1163
1164 RETURN l_Dum;
1165
1166 EXCEPTION
1167 WHEN OTHERS THEN
1168 hr_utility.set_location('py_za_total_package.split_totpkg',54);
1169 WriteHrTrace('Sql error code: '||TO_CHAR(SQLCODE));
1170 WriteHrTrace('Sql error msg: '||SUBSTR(SQLERRM(SQLCODE), 1, 100));
1171 StopHrTrace;
1172 hr_utility.set_message(801, 'py_za_total_package: '||TO_CHAR(SQLCODE));
1173 hr_utility.raise_error;
1174
1175 END split_totpkg;
1176
1177 END py_za_total_package;
1178