[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYIEP30_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYIEP30_XMLP_PKG AS
2 /* $Header: PAYIEP30B.pls 120.2 2008/03/26 12:54:10 amakrish noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 CURSOR CUR_ARCSTARTDATE(VP_PAYROLL_ACTION_ID IN PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID%TYPE) IS
5 SELECT
6 MIN(PPA_ARC.START_DATE) START_DATE
7 FROM
8 PAY_ASSIGNMENT_ACTIONS PAA_P30,
9 PAY_ACTION_INTERLOCKS PAI_P30,
10 PAY_ASSIGNMENT_ACTIONS PAA_ARC,
11 PAY_PAYROLL_ACTIONS PPA_ARC
12 WHERE PAA_P30.PAYROLL_ACTION_ID = VP_PAYROLL_ACTION_ID
13 AND PAA_P30.ASSIGNMENT_ACTION_ID = PAI_P30.LOCKING_ACTION_ID
14 AND PAA_ARC.ASSIGNMENT_ACTION_ID = PAI_P30.LOCKED_ACTION_ID
15 AND PPA_ARC.PAYROLL_ACTION_ID = PAA_ARC.PAYROLL_ACTION_ID;
16 CURSOR CUR_ENDDATE(VP_PAYROLL_ACTION_ID IN PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID%TYPE) IS
17 SELECT
18 SUBSTR(PPA_P30.LEGISLATIVE_PARAMETERS
19 ,INSTR(PPA_P30.LEGISLATIVE_PARAMETERS
20 ,'END_DATE=') + 9
21 ,10) END_DATE
22 FROM
23 PAY_PAYROLL_ACTIONS PPA_P30
24 WHERE PPA_P30.PAYROLL_ACTION_ID = VP_PAYROLL_ACTION_ID;
25 CURSOR CUR_GET_START_DATE(VP_REPORT_END_DATE IN DATE) IS
26 SELECT
27 TO_DATE(RULE_MODE || '/' || TO_CHAR(VP_REPORT_END_DATE
28 ,'YYYY')
29 ,'dd/mm/yyyy')
30 FROM
31 PAY_LEGISLATION_RULES
32 WHERE LEGISLATION_CODE = 'IE'
33 AND RULE_TYPE = 'L';
34 V_CUR_ARCSTARTDATE CUR_ARCSTARTDATE%ROWTYPE;
35 V_CUR_ENDDATE CUR_ENDDATE%ROWTYPE;
36 BEGIN
37 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
38 -- commented by atul P_REPORT_MODE := NVL(P_REPORT_MODE,'DETAIL');
39 LP_REPORT_MODE := NVL(P_REPORT_MODE,'DETAIL');
40 --commented by atul P_SUPPLEMENTARY_RUN := NVL(P_SUPPLEMENTARY_RUN,'N');
41 LP_SUPPLEMENTARY_RUN := NVL(P_SUPPLEMENTARY_RUN,'N');
42
43 -- commented by atul IF P_REPORT_MODE = 'DETAIL' THEN
44 IF LP_REPORT_MODE = 'DETAIL' THEN
45 IF P_SORT_BY = 'PAYROLL' THEN
46 CP_PAYROLL_ID := 'pact_er.ACTION_INFORMATION2';
47 CP_ORDER_BY := ' Order By pact_er.ACTION_INFORMATION2,pact_ee.action_information1';
48 ELSE
49 CP_PAYROLL_ID := 'NULL';
50 CP_ORDER_BY := ' Order By pact_ee.action_information1';
51 END IF;
52 END IF;
53 OPEN CUR_ARCSTARTDATE(P_P30_ACTION_ID);
54 FETCH CUR_ARCSTARTDATE
55 INTO
56 V_CUR_ARCSTARTDATE;
57 CLOSE CUR_ARCSTARTDATE;
58 OPEN CUR_ENDDATE(P_P30_ACTION_ID);
59 FETCH CUR_ENDDATE
60 INTO
61 V_CUR_ENDDATE;
62 CLOSE CUR_ENDDATE;
63 -- commented by atul P_REPORT_DATE := TO_CHAR(FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE),'DD-MON-YYYY');
64 LP_REPORT_DATE := TO_CHAR(FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE),format_mask);
65 -- commented by atul CP_REPORT_DATE := TO_DATE(P_REPORT_DATE,'DD-MON-YYYY');
66 CP_REPORT_DATE := TO_DATE(LP_REPORT_DATE,format_mask);
67 P_START_DATE := V_CUR_ARCSTARTDATE.START_DATE;
68 P_START_DATE1 := TO_CHAR(P_START_DATE,format_mask);
69 P_END_DATE := TO_DATE(V_CUR_ENDDATE.END_DATE
70 ,'YYYY/MM/DD');
71 P_END_DATE1 := TO_CHAR(P_END_DATE,format_mask);
72 OPEN CUR_GET_START_DATE(P_END_DATE);
73 FETCH CUR_GET_START_DATE
74 INTO
75 CP_TAX_START_DATE;
76 CLOSE CUR_GET_START_DATE;
77 --commented by atul CP_SUPPLEMENTARY_RUN := HR_REPORTS.GET_LOOKUP_MEANING('YES_NO',P_SUPPLEMENTARY_RUN);
78 CP_SUPPLEMENTARY_RUN := HR_REPORTS.GET_LOOKUP_MEANING('YES_NO',LP_SUPPLEMENTARY_RUN);
79
80 RETURN (TRUE);
81 END BEFOREREPORT;
82
83 FUNCTION AFTERREPORT RETURN BOOLEAN IS
84 BEGIN
85 RETURN (TRUE);
86 END AFTERREPORT;
87
88 FUNCTION CF_BUSINESS_GROUPFORMULA RETURN VARCHAR2 IS
89 V_BUSINESS_GROUP HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
90 BEGIN
91 V_BUSINESS_GROUP := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
92 RETURN V_BUSINESS_GROUP;
93 END CF_BUSINESS_GROUPFORMULA;
94
95 FUNCTION CF_LEGISLATION_CODEFORMULA RETURN VARCHAR2 IS
96 V_LEGISLATION_CODE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%TYPE := NULL;
97 CURSOR LEGISLATION_CODE(C_BUSINESS_GROUP_ID IN HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE) IS
98 SELECT
99 ORG_INFORMATION9
100 FROM
101 HR_ORGANIZATION_INFORMATION
102 WHERE ORGANIZATION_ID = C_BUSINESS_GROUP_ID
103 AND ORG_INFORMATION9 is not null
104 AND ORG_INFORMATION_CONTEXT = 'Business Group Information';
105 BEGIN
106 OPEN LEGISLATION_CODE(P_BUSINESS_GROUP_ID);
107 FETCH LEGISLATION_CODE
108 INTO
109 V_LEGISLATION_CODE;
110 CLOSE LEGISLATION_CODE;
111 RETURN V_LEGISLATION_CODE;
112 END CF_LEGISLATION_CODEFORMULA;
113
114 FUNCTION CF_CURRENCY_FORMAT_MASKFORMULA(CF_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
115 V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
116 V_FORMAT_MASK VARCHAR2(100) := NULL;
117 V_FIELD_LENGTH NUMBER(3) := 14;
118 CURSOR CURRENCY_FORMAT_MASK(C_TERRITORY_CODE IN FND_CURRENCIES.ISSUING_TERRITORY_CODE%TYPE) IS
119 SELECT
120 CURRENCY_CODE
121 FROM
122 FND_CURRENCIES
123 WHERE ISSUING_TERRITORY_CODE = C_TERRITORY_CODE;
124 BEGIN
125 OPEN CURRENCY_FORMAT_MASK(CF_LEGISLATION_CODE);
126 FETCH CURRENCY_FORMAT_MASK
127 INTO
128 V_CURRENCY_CODE;
129 CLOSE CURRENCY_FORMAT_MASK;
130 V_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(V_CURRENCY_CODE
131 ,V_FIELD_LENGTH);
132 RETURN V_FORMAT_MASK;
133 END CF_CURRENCY_FORMAT_MASKFORMULA;
134
135 PROCEDURE SET_CURRENCY_FORMAT_MASK IS
136 BEGIN
137 NULL;
138 END SET_CURRENCY_FORMAT_MASK;
139
140 FUNCTION P_BUSINESS_GROUP_IDVALIDTRIGGE RETURN BOOLEAN IS
141 BEGIN
142 RETURN (TRUE);
143 END P_BUSINESS_GROUP_IDVALIDTRIGGE;
144
145 FUNCTION CF_CALCULATE_TOTAL_PRSIFORMULA(REPORT_ID IN NUMBER
146 ,ASSIGNMENT_ID IN NUMBER) RETURN NUMBER IS
147 CURSOR YTD_BALANCES(VP_PAYROLL_ACTION_ID IN PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID%TYPE,
148 VP_ASSIGNMENT_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID%TYPE,
149 VP_BALANCE_NAME IN PAY_BALANCE_TYPES.BALANCE_NAME%TYPE,VP_DIMENSION_NAME IN VARCHAR2,
150 VP_ACTION_CONTEXT_ID IN NUMBER,VP_EFFECTIVE_DATE IN DATE) IS
151 SELECT
152 PACT_YTDBAL.ACTION_INFORMATION4 BALANCE_VALUE
153 FROM
154 PAY_ASSIGNMENT_ACTIONS PAA_P30,
155 PAY_ACTION_INTERLOCKS PAI_P30,
156 PAY_ASSIGNMENT_ACTIONS PAA_ARC,
157 PAY_ACTION_INFORMATION PACT_YTDBAL,
158 PAY_DEFINED_BALANCES PDB_YTDBAL,
159 PAY_BALANCE_TYPES PBT_YTDBAL,
160 PAY_BALANCE_DIMENSIONS PBD_YTDBAL,
161 PAY_ASSIGNMENT_ACTIONS PAA_SRC,
162 PAY_PAYROLL_ACTIONS PPA_SRC
163 WHERE PAA_P30.PAYROLL_ACTION_ID = VP_PAYROLL_ACTION_ID
164 AND PAA_P30.ASSIGNMENT_ID = VP_ASSIGNMENT_ID
165 AND PAA_P30.ASSIGNMENT_ACTION_ID = PAI_P30.LOCKING_ACTION_ID
166 AND PAA_ARC.ASSIGNMENT_ACTION_ID = PAI_P30.LOCKED_ACTION_ID
167 AND PACT_YTDBAL.ACTION_INFORMATION_CATEGORY = 'EMEA BALANCES'
168 AND PACT_YTDBAL.ACTION_CONTEXT_ID = VP_ACTION_CONTEXT_ID
169 AND PACT_YTDBAL.ACTION_CONTEXT_ID = PAA_ARC.ASSIGNMENT_ACTION_ID
170 AND PACT_YTDBAL.ACTION_CONTEXT_TYPE = 'AAP'
171 AND PDB_YTDBAL.DEFINED_BALANCE_ID = PACT_YTDBAL.ACTION_INFORMATION1
172 AND PDB_YTDBAL.BALANCE_TYPE_ID = PBT_YTDBAL.BALANCE_TYPE_ID
173 AND PBT_YTDBAL.BALANCE_NAME = VP_BALANCE_NAME
174 AND PBD_YTDBAL.DIMENSION_NAME = VP_DIMENSION_NAME
175 AND PBD_YTDBAL.BALANCE_DIMENSION_ID = PDB_YTDBAL.BALANCE_DIMENSION_ID
176 AND PBT_YTDBAL.LEGISLATION_CODE = 'IE'
177 AND PACT_YTDBAL.ACTION_CONTEXT_TYPE = 'AAP'
178 AND PAA_SRC.ASSIGNMENT_ACTION_ID = PACT_YTDBAL.SOURCE_ID
179 AND PAA_SRC.PAYROLL_ACTION_ID = PPA_SRC.PAYROLL_ACTION_ID
180 AND PPA_SRC.EFFECTIVE_DATE <= VP_EFFECTIVE_DATE
181 ORDER BY
182 PACT_YTDBAL.EFFECTIVE_DATE,
183 PACT_YTDBAL.ACTION_CONTEXT_ID,
184 NVL(PACT_YTDBAL.ACTION_INFORMATION5
185 ,0);
186 CURSOR CUR_GET_PREV_P30_LOCK_ID(VP_PAYROLL_ACTION_ID IN PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID%TYPE,VP_ASSIGNMENT_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE,VP_TAX_START_DATE IN DATE,VP_REPORT_END_DATE IN DATE) IS
187 SELECT
188 PPA.PAYROLL_ACTION_ID PREV_DATA_LOCK_ID,
189 TO_DATE(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
190 ,INSTR(PPA.LEGISLATIVE_PARAMETERS
191 ,'END_DATE=') + 9
192 ,10)
193 ,'YYYY/MM/DD')
194 FROM
195 PAY_PAYROLL_ACTIONS PPA,
196 PAY_ASSIGNMENT_ACTIONS PAA
197 WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
198 AND PPA.REPORT_TYPE = 'IEP30_PRGLOCK'
199 AND PAA.ASSIGNMENT_ID = VP_ASSIGNMENT_ID
200 AND PAA.ASSIGNMENT_ACTION_ID = (
201 SELECT
202 TO_NUMBER(SUBSTR(MAX(LPAD(PAA2.ACTION_SEQUENCE
203 ,15
204 ,'0') || PAA2.ASSIGNMENT_ACTION_ID)
205 ,16))
206 FROM
207 PAY_PAYROLL_ACTIONS PPA2,
208 PAY_ASSIGNMENT_ACTIONS PAA2
209 WHERE PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
210 AND PPA2.REPORT_TYPE = 'IEP30_PRGLOCK'
211 AND PAA2.ASSIGNMENT_ID = VP_ASSIGNMENT_ID
212 AND PPA2.PAYROLL_ACTION_ID <> VP_PAYROLL_ACTION_ID
213 AND TO_DATE(SUBSTR(PPA2.LEGISLATIVE_PARAMETERS
214 ,INSTR(PPA2.LEGISLATIVE_PARAMETERS
215 ,'END_DATE=') + 9
216 ,10)
217 ,'YYYY/MM/DD') BETWEEN VP_TAX_START_DATE
218 AND VP_REPORT_END_DATE )
219 ORDER BY
220 PPA.PAYROLL_ACTION_ID;
221 CURSOR GET_ACTION_CONTEXT(VP_PAYROLL_ACTION_ID IN PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID%TYPE,VP_ASSIGNMENT_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE) IS
222 SELECT
223 FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(PAA_RUN.ACTION_SEQUENCE
224 ,15
225 ,'0') || PACT_YTDBAL.ACTION_CONTEXT_ID)
226 ,16))
227 FROM
228 PAY_ASSIGNMENT_ACTIONS PAA_P30,
229 PAY_ACTION_INTERLOCKS PAI_P30,
230 PAY_ASSIGNMENT_ACTIONS PAA_ARC,
231 PAY_ACTION_INFORMATION PACT_YTDBAL,
232 PAY_ACTION_INTERLOCKS PAI_ARC,
233 PAY_ASSIGNMENT_ACTIONS PAA_RUN,
234 PAY_PAYROLL_ACTIONS PPA_RUN
235 WHERE PAA_P30.PAYROLL_ACTION_ID = VP_PAYROLL_ACTION_ID
236 AND PAA_P30.ASSIGNMENT_ID = VP_ASSIGNMENT_ID
237 AND PAA_P30.ASSIGNMENT_ACTION_ID = PAI_P30.LOCKING_ACTION_ID
238 AND PAA_ARC.ASSIGNMENT_ACTION_ID = PAI_P30.LOCKED_ACTION_ID
239 AND PAA_ARC.ASSIGNMENT_ACTION_ID = PAI_ARC.LOCKING_ACTION_ID
240 AND PACT_YTDBAL.ACTION_CONTEXT_ID = PAA_ARC.ASSIGNMENT_ACTION_ID
241 AND PAA_RUN.ASSIGNMENT_ACTION_ID = PAI_ARC.LOCKED_ACTION_ID
242 AND PACT_YTDBAL.ACTION_CONTEXT_TYPE = 'AAP'
243 AND PAA_RUN.SOURCE_ACTION_ID IS NULL
244 AND PAA_RUN.PAYROLL_ACTION_ID = PPA_RUN.PAYROLL_ACTION_ID
245 AND PPA_RUN.ACTION_TYPE in ( 'R' , 'Q' );
246 V_PREV_DATA_LOCK_ID NUMBER;
247 V_PRE_DATE_EFF_DATE DATE;
248 V_CURR_YTD_BALANCES YTD_BALANCES%ROWTYPE;
249 V_PREV_YTD_BALANCES YTD_BALANCES%ROWTYPE;
250 V_ACTION_CONTEXT_ID NUMBER(15,0);
251 pragma autonomous_transaction;
252 BEGIN
253 CP_PAYE_YTD := 0;
254 CP_EE_PRSI_YTD := 0;
255 CP_ER_PRSI_YTD := 0;
256 CP_PAYE_PTD := 0;
257 CP_EE_PRSI_PTD := 0;
258 CP_ER_PRSI_PTD := 0;
259 OPEN GET_ACTION_CONTEXT(REPORT_ID,ASSIGNMENT_ID);
260 FETCH GET_ACTION_CONTEXT
261 INTO
262 V_ACTION_CONTEXT_ID;
263 CLOSE GET_ACTION_CONTEXT;
264 OPEN YTD_BALANCES(REPORT_ID,ASSIGNMENT_ID,'IE Net Tax','_ASG_YTD',V_ACTION_CONTEXT_ID,P_END_DATE);
265 FETCH YTD_BALANCES
266 INTO
267 V_CURR_YTD_BALANCES;
268 CLOSE YTD_BALANCES;
269 CP_PAYE_YTD := NVL(V_CURR_YTD_BALANCES.BALANCE_VALUE
270 ,0);
271 V_CURR_YTD_BALANCES.BALANCE_VALUE := NULL;
272 OPEN YTD_BALANCES(REPORT_ID,ASSIGNMENT_ID,'IE PRSI Employee','_ASG_YTD',V_ACTION_CONTEXT_ID,P_END_DATE);
273 FETCH YTD_BALANCES
274 INTO
275 V_CURR_YTD_BALANCES;
276 CLOSE YTD_BALANCES;
277 CP_EE_PRSI_YTD := NVL(V_CURR_YTD_BALANCES.BALANCE_VALUE
278 ,0);
279 V_CURR_YTD_BALANCES.BALANCE_VALUE := NULL;
280 OPEN YTD_BALANCES(REPORT_ID,ASSIGNMENT_ID,'IE PRSI K Employee Lump Sum','_ASG_YTD',V_ACTION_CONTEXT_ID,P_END_DATE);
281 FETCH YTD_BALANCES
282 INTO
283 V_CURR_YTD_BALANCES;
284 CLOSE YTD_BALANCES;
285 CP_EE_PRSI_YTD := CP_EE_PRSI_YTD + NVL(V_CURR_YTD_BALANCES.BALANCE_VALUE
286 ,0);
287 V_CURR_YTD_BALANCES.BALANCE_VALUE := NULL;
288 OPEN YTD_BALANCES(REPORT_ID,ASSIGNMENT_ID,'IE PRSI M Employee Lump Sum','_ASG_YTD',V_ACTION_CONTEXT_ID,P_END_DATE);
289 FETCH YTD_BALANCES
290 INTO
291 V_CURR_YTD_BALANCES;
292 CLOSE YTD_BALANCES;
293 CP_EE_PRSI_YTD := CP_EE_PRSI_YTD + NVL(V_CURR_YTD_BALANCES.BALANCE_VALUE
294 ,0);
295 V_CURR_YTD_BALANCES.BALANCE_VALUE := NULL;
296 OPEN YTD_BALANCES(REPORT_ID,ASSIGNMENT_ID,'IE PRSI Employer','_ASG_YTD',V_ACTION_CONTEXT_ID,P_END_DATE);
297 FETCH YTD_BALANCES
298 INTO
299 V_CURR_YTD_BALANCES;
300 CLOSE YTD_BALANCES;
301 CP_ER_PRSI_YTD := NVL(V_CURR_YTD_BALANCES.BALANCE_VALUE
302 ,0);
303 V_CURR_YTD_BALANCES.BALANCE_VALUE := NULL;
304 OPEN YTD_BALANCES(REPORT_ID,ASSIGNMENT_ID,'IE PRSI K Employer Lump Sum','_ASG_YTD',V_ACTION_CONTEXT_ID,P_END_DATE);
305 FETCH YTD_BALANCES
306 INTO
307 V_CURR_YTD_BALANCES;
308 CLOSE YTD_BALANCES;
309 CP_ER_PRSI_YTD := CP_ER_PRSI_YTD + NVL(V_CURR_YTD_BALANCES.BALANCE_VALUE
310 ,0);
311 V_CURR_YTD_BALANCES.BALANCE_VALUE := NULL;
312 OPEN YTD_BALANCES(REPORT_ID,ASSIGNMENT_ID,'IE PRSI M Employer Lump Sum','_ASG_YTD',V_ACTION_CONTEXT_ID,P_END_DATE);
313 FETCH YTD_BALANCES
314 INTO
315 V_CURR_YTD_BALANCES;
316 CLOSE YTD_BALANCES;
317 CP_ER_PRSI_YTD := CP_ER_PRSI_YTD + NVL(V_CURR_YTD_BALANCES.BALANCE_VALUE
318 ,0);
319 V_CURR_YTD_BALANCES.BALANCE_VALUE := NULL;
320 OPEN CUR_GET_PREV_P30_LOCK_ID(REPORT_ID,ASSIGNMENT_ID,CP_TAX_START_DATE,P_END_DATE);
321 FETCH CUR_GET_PREV_P30_LOCK_ID
322 INTO
323 V_PREV_DATA_LOCK_ID
324 ,V_PRE_DATE_EFF_DATE;
325 IF CUR_GET_PREV_P30_LOCK_ID%NOTFOUND THEN
326 V_PREV_DATA_LOCK_ID := NULL;
327 V_PRE_DATE_EFF_DATE := NULL;
328 CP_PAYE_PTD := CP_PAYE_YTD;
329 CP_EE_PRSI_PTD := CP_EE_PRSI_YTD;
330 CP_ER_PRSI_PTD := CP_ER_PRSI_YTD;
331 ELSE
332 OPEN GET_ACTION_CONTEXT(V_PREV_DATA_LOCK_ID,ASSIGNMENT_ID);
333 FETCH GET_ACTION_CONTEXT
334 INTO
335 V_ACTION_CONTEXT_ID;
336 CLOSE GET_ACTION_CONTEXT;
337 OPEN YTD_BALANCES(V_PREV_DATA_LOCK_ID,ASSIGNMENT_ID,'IE Net Tax','_ASG_YTD',V_ACTION_CONTEXT_ID,V_PRE_DATE_EFF_DATE);
338 FETCH YTD_BALANCES
339 INTO
340 V_PREV_YTD_BALANCES;
341 CLOSE YTD_BALANCES;
342 CP_PAYE_PTD := CP_PAYE_YTD - NVL(V_PREV_YTD_BALANCES.BALANCE_VALUE
343 ,0);
344 V_PREV_YTD_BALANCES.BALANCE_VALUE := NULL;
345 OPEN YTD_BALANCES(V_PREV_DATA_LOCK_ID,ASSIGNMENT_ID,'IE PRSI Employee','_ASG_YTD',V_ACTION_CONTEXT_ID,V_PRE_DATE_EFF_DATE);
346 FETCH YTD_BALANCES
347 INTO
348 V_PREV_YTD_BALANCES;
349 CLOSE YTD_BALANCES;
350 CP_EE_PRSI_PTD := CP_EE_PRSI_YTD - NVL(V_PREV_YTD_BALANCES.BALANCE_VALUE
351 ,0);
352 V_PREV_YTD_BALANCES.BALANCE_VALUE := NULL;
353 OPEN YTD_BALANCES(V_PREV_DATA_LOCK_ID,ASSIGNMENT_ID,'IE PRSI K Employee Lump Sum','_ASG_YTD',V_ACTION_CONTEXT_ID,V_PRE_DATE_EFF_DATE);
354 FETCH YTD_BALANCES
355 INTO
356 V_PREV_YTD_BALANCES;
357 CLOSE YTD_BALANCES;
358 CP_EE_PRSI_PTD := CP_EE_PRSI_PTD - NVL(V_PREV_YTD_BALANCES.BALANCE_VALUE
359 ,0);
360 V_PREV_YTD_BALANCES.BALANCE_VALUE := NULL;
361 OPEN YTD_BALANCES(V_PREV_DATA_LOCK_ID,ASSIGNMENT_ID,'IE PRSI M Employee Lump Sum','_ASG_YTD',V_ACTION_CONTEXT_ID,V_PRE_DATE_EFF_DATE);
362 FETCH YTD_BALANCES
363 INTO
364 V_PREV_YTD_BALANCES;
365 CLOSE YTD_BALANCES;
366 CP_EE_PRSI_PTD := CP_EE_PRSI_PTD - NVL(V_PREV_YTD_BALANCES.BALANCE_VALUE
367 ,0);
368 V_PREV_YTD_BALANCES.BALANCE_VALUE := NULL;
369 OPEN YTD_BALANCES(V_PREV_DATA_LOCK_ID,ASSIGNMENT_ID,'IE PRSI Employer','_ASG_YTD',V_ACTION_CONTEXT_ID,V_PRE_DATE_EFF_DATE);
370 FETCH YTD_BALANCES
371 INTO
372 V_PREV_YTD_BALANCES;
373 CLOSE YTD_BALANCES;
374 CP_ER_PRSI_PTD := CP_ER_PRSI_YTD - NVL(V_PREV_YTD_BALANCES.BALANCE_VALUE
375 ,0);
376 V_PREV_YTD_BALANCES.BALANCE_VALUE := NULL;
377 OPEN YTD_BALANCES(V_PREV_DATA_LOCK_ID,ASSIGNMENT_ID,'IE PRSI K Employer Lump Sum','_ASG_YTD',V_ACTION_CONTEXT_ID,V_PRE_DATE_EFF_DATE);
378 FETCH YTD_BALANCES
379 INTO
380 V_PREV_YTD_BALANCES;
381 CLOSE YTD_BALANCES;
382 CP_ER_PRSI_PTD := CP_ER_PRSI_PTD - NVL(V_PREV_YTD_BALANCES.BALANCE_VALUE
383 ,0);
384 V_PREV_YTD_BALANCES.BALANCE_VALUE := NULL;
385 OPEN YTD_BALANCES(V_PREV_DATA_LOCK_ID,ASSIGNMENT_ID,'IE PRSI M Employer Lump Sum','_ASG_YTD',V_ACTION_CONTEXT_ID,V_PRE_DATE_EFF_DATE);
386 FETCH YTD_BALANCES
387 INTO
388 V_PREV_YTD_BALANCES;
389 CLOSE YTD_BALANCES;
390 CP_ER_PRSI_PTD := CP_ER_PRSI_PTD - NVL(V_PREV_YTD_BALANCES.BALANCE_VALUE
391 ,0);
392 V_PREV_YTD_BALANCES.BALANCE_VALUE := NULL;
393 END IF;
394 CLOSE CUR_GET_PREV_P30_LOCK_ID;
395 CP_TOTAL_PRSI_REPORT := NVL(CP_EE_PRSI_PTD
396 ,0) + NVL(CP_ER_PRSI_PTD
397 ,0);
398 CP_TOTAL_PRSI_YTD := NVL(CP_EE_PRSI_YTD
399 ,0) + NVL(CP_ER_PRSI_YTD
400 ,0);
401 IF ((CP_PAYE_PTD < 0) OR (CP_EE_PRSI_PTD < 0) OR (CP_ER_PRSI_PTD < 0)) THEN
402 RETURN (1);
403 END IF;
404 /* CP_PAYE_YTD := 0;
405 CP_EE_PRSI_YTD := 0;
406 CP_ER_PRSI_YTD := 0;
407 CP_TOTAL_PRSI_YTD := 0; */
408
409 RETURN (0);
410 END CF_CALCULATE_TOTAL_PRSIFORMULA;
411
412 FUNCTION CF_PAYROLL_NAMEFORMULA(PAYROLL_ID IN VARCHAR2) RETURN CHAR IS
413 CURSOR CUR_PAYROLL(VP_PAYROLL_ID IN PAY_PAYROLLS_F.PAYROLL_ID%TYPE) IS
414 SELECT
415 PAYROLL_NAME
416 FROM
417 PAY_PAYROLLS_F
418 WHERE PAYROLL_ID = VP_PAYROLL_ID;
419 V_CUR_PAYROLL CUR_PAYROLL%ROWTYPE;
420 BEGIN
421 IF P_SORT_BY = 'PAYROLL' THEN
422 OPEN CUR_PAYROLL(PAYROLL_ID);
423 FETCH CUR_PAYROLL
424 INTO
425 V_CUR_PAYROLL;
426 CLOSE CUR_PAYROLL;
427 END IF;
428 RETURN (V_CUR_PAYROLL.PAYROLL_NAME);
429 END CF_PAYROLL_NAMEFORMULA;
430
431 FUNCTION CP_PAYE_YTD_P RETURN NUMBER IS
432 BEGIN
433 RETURN CP_PAYE_YTD;
434 END CP_PAYE_YTD_P;
435
436 FUNCTION CP_EE_PRSI_YTD_P RETURN NUMBER IS
437 BEGIN
438 RETURN CP_EE_PRSI_YTD;
439 END CP_EE_PRSI_YTD_P;
440
441 FUNCTION CP_ER_PRSI_YTD_P RETURN NUMBER IS
442 BEGIN
443 RETURN CP_ER_PRSI_YTD;
444 END CP_ER_PRSI_YTD_P;
445
446 FUNCTION CP_TOTAL_PRSI_REPORT_P RETURN NUMBER IS
447 BEGIN
448 RETURN CP_TOTAL_PRSI_REPORT;
449 END CP_TOTAL_PRSI_REPORT_P;
450
451 FUNCTION CP_TOTAL_PRSI_YTD_P RETURN NUMBER IS
452 BEGIN
453 RETURN CP_TOTAL_PRSI_YTD;
454 END CP_TOTAL_PRSI_YTD_P;
455
456 FUNCTION CP_PAYE_PTD_P RETURN NUMBER IS
457 BEGIN
458 RETURN CP_PAYE_PTD;
459 END CP_PAYE_PTD_P;
460
461 FUNCTION CP_EE_PRSI_PTD_P RETURN NUMBER IS
462 BEGIN
463 RETURN CP_EE_PRSI_PTD;
464 END CP_EE_PRSI_PTD_P;
465
466 FUNCTION CP_ER_PRSI_PTD_P RETURN NUMBER IS
467 BEGIN
468 RETURN CP_ER_PRSI_PTD;
469 END CP_ER_PRSI_PTD_P;
470
471 FUNCTION CP_PAYROLL_ID_P RETURN VARCHAR2 IS
472 BEGIN
473 RETURN CP_PAYROLL_ID;
474 END CP_PAYROLL_ID_P;
475
476 FUNCTION CP_SUPPLEMENTARY_RUN_P RETURN VARCHAR2 IS
477 BEGIN
478 RETURN CP_SUPPLEMENTARY_RUN;
479 END CP_SUPPLEMENTARY_RUN_P;
480
481 FUNCTION CP_REPORT_DATE_P RETURN DATE IS
482 BEGIN
483 RETURN CP_REPORT_DATE;
484 END CP_REPORT_DATE_P;
485
486 FUNCTION CP_ORDER_BY_P RETURN VARCHAR2 IS
487 BEGIN
488 RETURN CP_ORDER_BY;
489 END CP_ORDER_BY_P;
490
491 FUNCTION CP_TAX_START_DATE_P RETURN DATE IS
492 BEGIN
493 RETURN CP_TAX_START_DATE;
494 END CP_TAX_START_DATE_P;
495
496 END PAY_PAYIEP30_XMLP_PKG;