1 PACKAGE BODY PAY_PYAURECD_XMLP_PKG AS
2 /* $Header: PYAURECDB.pls 120.1 2008/03/31 09:48:11 amakrish noship $ */
3
4 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 REQ_ID NUMBER;
6 L_PACT_ID NUMBER;
7 Rep_name varchar2(50);
8 BEGIN
9 /*added as fix:*/
10 select concurrent_program_name into Rep_name from fnd_concurrent_programs fc,FND_CONCURRENT_REQUESTS fn
11 where fn.concurrent_program_id=fc.concurrent_program_id
12 and request_id= FND_GLOBAL.conc_request_id;
13 if Rep_name='PYAUREPSR_XML' then
14 select
15 SUBSTR(argument1,INSTR(argument1,'=',1)+1,LENGTH(argument1)),
16 SUBSTR(argument2,INSTR(argument2,'=',1)+1,LENGTH(argument2)),
17 SUBSTR(argument3,INSTR(argument3,'=',1)+1,LENGTH(argument3)),
18 SUBSTR(argument4,INSTR(argument4,'=',1)+1,LENGTH(argument4)),
19 SUBSTR(argument5,INSTR(argument5,'=',1)+1,LENGTH(argument5)),
20 SUBSTR(argument6,INSTR(argument6,'=',1)+1,LENGTH(argument6)),
21 SUBSTR(argument7,INSTR(argument7,'=',1)+1,LENGTH(argument7)),
22 SUBSTR(argument8,INSTR(argument8,'=',1)+1,LENGTH(argument8)),
23 SUBSTR(argument9,INSTR(argument9,'=',1)+1,LENGTH(argument9)),
24 SUBSTR(argument10,INSTR(argument10,'=',1)+1,LENGTH(argument10)),
25 SUBSTR(argument11,INSTR(argument11,'=',1)+1,LENGTH(argument11)),
26 substr(argument12,instr(argument12,'=',1)+1,LENGTH(argument12)),
27 substr(argument13,instr(argument13,'=',1)+1,LENGTH(argument13)),
28 substr(argument14,instr(argument14,'=',1)+1,LENGTH(argument14)),
29 substr(argument15,instr(argument15,'=',1)+1,LENGTH(argument15)),
30 substr(argument16,instr(argument16,'=',1)+1,LENGTH(argument16)),
31 substr(argument17,instr(argument17,'=',1)+1,LENGTH(argument17)),
32 substr(argument18,instr(argument18,'=',1)+1,LENGTH(argument18)),
33 substr(argument19,instr(argument19,'=',1)+1,LENGTH(argument19)),
34 substr(argument20,instr(argument20,'=',1)+1,LENGTH(argument20)),
35 substr(argument21,instr(argument21,'=',1)+1,LENGTH(argument21))
36 into
37 LP_PAYROLL_ACTION_ID,
38 LP_BUSINESS_GROUP_ID,
39 LP_ORGANIZATION_ID,
40 LP_PAYROLL_ID,
41 LP_REGISTERED_EMPLOYER,
42 LP_ASSIGNMENT_ID,
43 LP_START_DATE_D,
44 LP_END_DATE_D,
45 LP_PAYROLL_RUN_ID,
46 LP_PERIOD_END_DATE_D,
47 LP_EMPLOYEE_TYPE,
48 LP_YTD_TOTALS,
49 LP_ZERO_RECORDS,
50 LP_NEGATIVE_RECORDS,
51 LP_SORT_ORDER_1,
52 LP_SORT_ORDER_2,
53 LP_SORT_ORDER_3,
54 LP_SORT_ORDER_4,
55 P_PAYSUM_FLAG,
56 LP_LST_YEAR_TERM,
57 LP_DELETE_ACTIONS
58
59 from FND_CONCURRENT_REQUESTS
60 where request_id= FND_GLOBAL.conc_request_id;
61
62 end if;
63 if Rep_name='PYAURECD_XML' then
64 select
65 SUBSTR(argument1,INSTR(argument1,'=',1)+1,LENGTH(argument1)),
66 SUBSTR(argument2,INSTR(argument2,'=',1)+1,LENGTH(argument2)),
67 SUBSTR(argument3,INSTR(argument3,'=',1)+1,LENGTH(argument3)),
68 SUBSTR(argument4,INSTR(argument4,'=',1)+1,LENGTH(argument4)),
69 SUBSTR(argument5,INSTR(argument5,'=',1)+1,LENGTH(argument5)),
70 SUBSTR(argument6,INSTR(argument6,'=',1)+1,LENGTH(argument6)),
71 SUBSTR(argument7,INSTR(argument7,'=',1)+1,LENGTH(argument7)),
72 SUBSTR(argument8,INSTR(argument8,'=',1)+1,LENGTH(argument8)),
73 SUBSTR(argument9,INSTR(argument9,'=',1)+1,LENGTH(argument9)),
74 SUBSTR(argument10,INSTR(argument10,'=',1)+1,LENGTH(argument10)),
75 SUBSTR(argument11,INSTR(argument11,'=',1)+1,LENGTH(argument11)),
76 substr(argument12,instr(argument12,'=',1)+1,LENGTH(argument12)),
77 substr(argument13,instr(argument13,'=',1)+1,LENGTH(argument13)),
78 substr(argument14,instr(argument14,'=',1)+1,LENGTH(argument14)),
79 substr(argument15,instr(argument15,'=',1)+1,LENGTH(argument15)),
80 substr(argument16,instr(argument16,'=',1)+1,LENGTH(argument16)),
81 substr(argument17,instr(argument17,'=',1)+1,LENGTH(argument17)),
82 substr(argument18,instr(argument18,'=',1)+1,LENGTH(argument18)),
83 substr(argument21,instr(argument21,'=',1)+1,LENGTH(argument21))
84 into
85 LP_PAYROLL_ACTION_ID,
86 LP_BUSINESS_GROUP_ID,
87 LP_ORGANIZATION_ID,
88 LP_PAYROLL_ID,
89 LP_REGISTERED_EMPLOYER,
90 LP_ASSIGNMENT_ID,
91 LP_START_DATE_D,
92 LP_END_DATE_D,
93 LP_PAYROLL_RUN_ID,
94 LP_PERIOD_END_DATE_D,
95 LP_EMPLOYEE_TYPE,
96 LP_YTD_TOTALS,
97 LP_ZERO_RECORDS,
98 LP_NEGATIVE_RECORDS,
99 LP_SORT_ORDER_1,
100 LP_SORT_ORDER_2,
101 LP_SORT_ORDER_3,
102 LP_SORT_ORDER_4,
103 LP_DELETE_ACTIONS
104
105 from FND_CONCURRENT_REQUESTS
106 where request_id= FND_GLOBAL.conc_request_id;
107
108 end if;
109
110 LP_START_DATE := to_date(LP_START_DATE_D,'YYYY/MM/DD');
111 LP_END_DATE := to_date(LP_END_DATE_D,'YYYY/MM/DD');
112 LP_PERIOD_END_DATE := to_date(LP_PERIOD_END_DATE_D,'YYYY/MM/DD');
113 LP_START_DATE_D:=to_char(LP_START_DATE,'DD-MON-YYYY');
114 LP_END_DATE_D:=to_char(LP_END_DATE,'DD-MON-YYYY');
115 /*fix ends*/
116 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
117 ORDERBY_CLAUSE := ' ';
118 IF (P_PAYSUM_FLAG IS NULL) THEN
119 P_PAYSUM_FLAG := 'N';
120 END IF;
121 IF (LP_SORT_ORDER_1 IS NOT NULL) THEN
122 IF (LP_SORT_ORDER_1 = 'EMPLOYEE') THEN
123 ORDERBY_CLAUSE := 'Order By pai_asg.action_information1';
124 ELSIF (LP_SORT_ORDER_1 = 'LEGAL_EMPLOYER') THEN
125 ORDERBY_CLAUSE := 'Order By pai_asg.action_information9';
126 LP_SORT_ORDER_1 := 'LEGAL EMPLOYER';
127 ELSIF (LP_SORT_ORDER_1 = 'ORGANIZATION') THEN
128 ORDERBY_CLAUSE := 'Order By pai_asg.action_information8';
129 ELSE
130 ORDERBY_CLAUSE := 'Order By pai_asg.action_information10';
131 END IF;
132 END IF;
133 IF (LP_SORT_ORDER_2 IS NOT NULL) THEN
134 IF (LP_SORT_ORDER_2 = 'EMPLOYEE') THEN
135 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information1';
136 ELSIF (LP_SORT_ORDER_2 = 'LEGAL_EMPLOYER') THEN
137 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information9';
138 LP_SORT_ORDER_2 := 'LEGAL EMPLOYER';
139 ELSIF (LP_SORT_ORDER_2 = 'ORGANIZATION') THEN
140 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information8';
141 ELSE
142 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information10';
143 END IF;
144 END IF;
145 IF (LP_SORT_ORDER_3 IS NOT NULL) THEN
146 IF (LP_SORT_ORDER_3 = 'EMPLOYEE') THEN
147 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information1';
148 ELSIF (LP_SORT_ORDER_3 = 'LEGAL_EMPLOYER') THEN
149 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information9';
150 LP_SORT_ORDER_3 := 'LEGAL EMPLOYER';
151 ELSIF (LP_SORT_ORDER_3 = 'ORGANIZATION') THEN
152 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information8';
153 ELSE
154 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information10';
155 END IF;
156 END IF;
157 IF (LP_SORT_ORDER_4 IS NOT NULL) THEN
158 IF (LP_SORT_ORDER_4 = 'EMPLOYEE') THEN
159 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information1';
160 ELSIF (LP_SORT_ORDER_4 = 'LEGAL_EMPLOYER') THEN
161 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information9';
162 LP_SORT_ORDER_4 := 'LEGAL EMPLOYER';
163 ELSIF (LP_SORT_ORDER_4 = 'ORGANIZATION') THEN
164 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information8';
165 ELSE
166 ORDERBY_CLAUSE := ORDERBY_CLAUSE || ',pai_asg.action_information10';
167 END IF;
168 END IF;
169 IF (P_PAYSUM_FLAG = 'Y') THEN
170 ORDERBY_CLAUSE := 'Order by pai_asg.assignment_id';
171 END IF;
172 CP_PS_PAYSUM_GROSS := 0;
173 CP_PS_WORKPLACE_GIVING := 0;
174 CP_PS_REP_ALLOWANCES := 0;
175 CP_PS_ETP := 0;
176 CP_PS_RFB := 0;
177 CP_PS_ASSESSABLE_ETP := 0;
178 CP_PS_CDEP := 0;
179 CP_PS_OTHER_INCOME := 0;
180 CP_PS_UNION_FEES := 0;
181 CP_PS_LUMPSUM_PAYMENTS := 0;
182 CP_MAN_PS_PAYSUM_GROSS := 0;
183 CP_MAN_PS_WORKPLACE_GIVING := 0;
184 CP_MAN_PS_REP_ALLOWANCES := 0;
185 CP_MAN_PS_ETP := 0;
186 CP_MAN_PS_RFB := 0;
187 CP_MAN_PS_ASSESSABLE_ETP := 0;
188 CP_MAN_PS_CDEP := 0;
189 CP_MAN_PS_OTHER_INCOME := 0;
190 CP_MAN_PS_UNION_FEES := 0;
191 CP_MAN_PS_LUMPSUM_PAYMENTS := 0;
192 CP_PRINTED_COUNT := 0;
193 CP_MANUAL_COUNT := 0;
194 RETURN (TRUE);
195 END BEFOREREPORT;
196
197 FUNCTION AFTERREPORT RETURN BOOLEAN IS
198 BEGIN
199 IF LP_DELETE_ACTIONS = 'Y' THEN
200 BEGIN
201 DELETE FROM PAY_ACTION_INFORMATION
202 WHERE ACTION_INFORMATION_CATEGORY = 'AU_EMPLOYEE_RECON_DETAILS'
203 AND ACTION_CONTEXT_ID = LP_PAYROLL_ACTION_ID;
204 IF P_PAYSUM_FLAG = 'Y' THEN
205 DELETE FROM PAY_ACTION_INFORMATION
206 WHERE ACTION_INFORMATION_CATEGORY in ( 'AU_ELEMENT_RECON_DETAILS' , 'AU_BALANCE_RECON_DETAILS_YTD' , 'AU_PS_BALANCE_RECON_DETAILS' )
207 AND ACTION_CONTEXT_ID in (
208 SELECT
209 PAA.ASSIGNMENT_ACTION_ID
210 FROM
211 PAY_ASSIGNMENT_ACTIONS PAA
212 WHERE PAYROLL_ACTION_ID = LP_PAYROLL_ACTION_ID
213 AND PAA.ACTION_STATUS = 'C' );
214 ELSE
215 DELETE FROM PAY_ACTION_INFORMATION
216 WHERE ACTION_INFORMATION_CATEGORY in ( 'AU_ELEMENT_RECON_DETAILS' , 'AU_BALANCE_RECON_DETAILS_RUN' , 'AU_BALANCE_RECON_DETAILS_YTD' )
217 AND ACTION_CONTEXT_ID in (
218 SELECT
219 PAA.ASSIGNMENT_ACTION_ID
220 FROM
221 PAY_ASSIGNMENT_ACTIONS PAA
222 WHERE PAA.PAYROLL_ACTION_ID = LP_PAYROLL_ACTION_ID
223 AND PAA.ACTION_STATUS = 'C' );
224 DELETE FROM PAY_ACTION_INFORMATION
225 WHERE ACTION_INFORMATION_CATEGORY = 'AU_ARCHIVE_ASG_DETAILS'
226 AND ACTION_INFORMATION2 = TO_CHAR(LP_PAYROLL_ACTION_ID);
227 END IF;
228 EXCEPTION
229 WHEN OTHERS THEN
230 NULL;
231 END;
232 BEGIN
233 DELETE FROM PAY_ASSIGNMENT_ACTIONS PAA
234 WHERE PAA.PAYROLL_ACTION_ID = LP_PAYROLL_ACTION_ID;
235 DELETE FROM PAY_PAYROLL_ACTIONS PPA
236 WHERE PPA.PAYROLL_ACTION_ID = LP_PAYROLL_ACTION_ID;
237 EXCEPTION
238 WHEN OTHERS THEN
239 NULL;
240 END;
241 COMMIT;
242 END IF;
243 RETURN (TRUE);
244 END AFTERREPORT;
245
246 FUNCTION CF_BUSINESS_GROUPFORMULA RETURN VARCHAR2 IS
247 V_BUSINESS_GROUP HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
248 BEGIN
249 V_BUSINESS_GROUP := HR_REPORTS.GET_BUSINESS_GROUP(LP_BUSINESS_GROUP_ID);
250 RETURN V_BUSINESS_GROUP;
251 END CF_BUSINESS_GROUPFORMULA;
252
253 FUNCTION CF_LEGISLATION_CODEFORMULA RETURN VARCHAR2 IS
254 V_LEGISLATION_CODE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%TYPE := NULL;
255 CURSOR LEGISLATION_CODE(C_BUSINESS_GROUP_ID IN HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE) IS
256 SELECT
257 ORG_INFORMATION9
258 FROM
259 HR_ORGANIZATION_INFORMATION
260 WHERE ORGANIZATION_ID = C_BUSINESS_GROUP_ID
261 AND ORG_INFORMATION9 is not null
262 AND ORG_INFORMATION_CONTEXT = 'Business Group Information';
263 BEGIN
264 OPEN LEGISLATION_CODE(LP_BUSINESS_GROUP_ID);
265 FETCH LEGISLATION_CODE
266 INTO
267 V_LEGISLATION_CODE;
268 CLOSE LEGISLATION_CODE;
269 RETURN V_LEGISLATION_CODE;
270 END CF_LEGISLATION_CODEFORMULA;
271
272 FUNCTION CF_CURRENCY_FORMAT_MASKFORMULA(CF_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
273 V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
274 V_FORMAT_MASK VARCHAR2(100) := NULL;
275 V_FIELD_LENGTH NUMBER(3) := 14;
276 CURSOR CURRENCY_FORMAT_MASK(C_TERRITORY_CODE IN FND_CURRENCIES.ISSUING_TERRITORY_CODE%TYPE) IS
277 SELECT
278 CURRENCY_CODE
279 FROM
280 FND_CURRENCIES
281 WHERE ISSUING_TERRITORY_CODE = C_TERRITORY_CODE;
282 BEGIN
283 OPEN CURRENCY_FORMAT_MASK(CF_LEGISLATION_CODE);
284 FETCH CURRENCY_FORMAT_MASK
285 INTO
286 V_CURRENCY_CODE;
287 CLOSE CURRENCY_FORMAT_MASK;
288 V_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(V_CURRENCY_CODE
289 ,V_FIELD_LENGTH);
290 RETURN V_FORMAT_MASK;
291 END CF_CURRENCY_FORMAT_MASKFORMULA;
292
293 PROCEDURE SET_CURRENCY_FORMAT_MASK IS
294 BEGIN
295 NULL;
296 END SET_CURRENCY_FORMAT_MASK;
297
298 FUNCTION P_BUSINESS_GROUP_IDVALIDTRIGGE RETURN BOOLEAN IS
299 BEGIN
300 RETURN (TRUE);
301 END P_BUSINESS_GROUP_IDVALIDTRIGGE;
302
303 FUNCTION CF_EMPLOYEE_TYPE_DISPLAYFORMUL RETURN CHAR IS
304 BEGIN
305 IF (LP_EMPLOYEE_TYPE = 'B') THEN
306 RETURN ('Both Current and Terminated Employees');
307 END IF;
308 IF (LP_EMPLOYEE_TYPE = 'C') THEN
309 RETURN ('Only Current Employees');
310 END IF;
311 IF (LP_EMPLOYEE_TYPE = 'T') THEN
312 RETURN ('Only Terminated Employees');
313 END IF;
314 END CF_EMPLOYEE_TYPE_DISPLAYFORMUL;
315
316 FUNCTION CF_EMP_DISPLAYFORMULA RETURN CHAR IS
317 L_EMP_NAME VARCHAR2(80) := NULL;
318 CURSOR GET_EMP_NAME(C_ASS_ID IN PER_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE) IS
319 SELECT
320 SUBSTR(PPF.FULL_NAME
321 ,1
322 ,80)
323 FROM
324 PER_PEOPLE_F PPF,
325 PER_ASSIGNMENTS_F PAF
326 WHERE PAF.PERSON_ID = PPF.PERSON_ID
327 AND PAF.ASSIGNMENT_ID = C_ASS_ID;
328 BEGIN
329 IF (LP_ASSIGNMENT_ID IS NOT NULL) THEN
330 OPEN GET_EMP_NAME(LP_ASSIGNMENT_ID);
331 FETCH GET_EMP_NAME
332 INTO
333 L_EMP_NAME;
334 CLOSE GET_EMP_NAME;
335 END IF;
336 RETURN L_EMP_NAME;
337 END CF_EMP_DISPLAYFORMULA;
338
339 FUNCTION CF_LEG_EMP_DISPLAYFORMULA RETURN CHAR IS
340 L_LEG_EMP_NAME VARCHAR2(80) := NULL;
341 CURSOR GET_LEG_EMP_NAME(C_LEG_EMP_ID IN HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE) IS
342 SELECT
343 SUBSTR(NAME
344 ,1
345 ,80)
346 FROM
347 HR_ORGANIZATION_UNITS
348 WHERE ORGANIZATION_ID = C_LEG_EMP_ID;
349 BEGIN
350 IF (LP_REGISTERED_EMPLOYER IS NOT NULL) THEN
351 OPEN GET_LEG_EMP_NAME(LP_REGISTERED_EMPLOYER);
352 FETCH GET_LEG_EMP_NAME
353 INTO
354 L_LEG_EMP_NAME;
355 CLOSE GET_LEG_EMP_NAME;
356 END IF;
357 RETURN L_LEG_EMP_NAME;
358 END CF_LEG_EMP_DISPLAYFORMULA;
359
360 FUNCTION CF_NEG_RECORDS_DISPLAYFORMULA RETURN CHAR IS
361 BEGIN
362 IF (LP_NEGATIVE_RECORDS = 'Y') THEN
363 RETURN ('Yes');
364 END IF;
365 IF (LP_NEGATIVE_RECORDS = 'N') THEN
366 RETURN ('No');
367 END IF;
368 END CF_NEG_RECORDS_DISPLAYFORMULA;
369
370 FUNCTION CF_NO_DATA_END_REPORTFORMULA(CS_DATA_EXISTS IN NUMBER) RETURN CHAR IS
371 BEGIN
372 IF (CS_DATA_EXISTS = 0) THEN
373 RETURN (CP_NO_DATA_FOUND);
374 ELSE
375 RETURN (CP_END_REPORT);
376 END IF;
377 END CF_NO_DATA_END_REPORTFORMULA;
378
379 FUNCTION CF_ORG_ID_DISPLAYFORMULA RETURN CHAR IS
380 L_ORG_NAME VARCHAR2(80) := NULL;
381 CURSOR GET_ORG_NAME(C_ORGANIZATION_ID IN HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE) IS
382 SELECT
383 SUBSTR(NAME
384 ,1
385 ,80)
386 FROM
387 HR_ORGANIZATION_UNITS
388 WHERE ORGANIZATION_ID = C_ORGANIZATION_ID;
389 BEGIN
390 IF (LP_ORGANIZATION_ID IS NOT NULL) THEN
391 OPEN GET_ORG_NAME(LP_ORGANIZATION_ID);
392 FETCH GET_ORG_NAME
393 INTO
394 L_ORG_NAME;
395 CLOSE GET_ORG_NAME;
396 END IF;
397 RETURN L_ORG_NAME;
398 END CF_ORG_ID_DISPLAYFORMULA;
399
400 FUNCTION CF_PAYROLL_NAMEFORMULA RETURN VARCHAR2 IS
401 L_PAYROLL_NAME VARCHAR2(80) := NULL;
402 CURSOR GET_PAYROLL_NAME(C_PAYROLL_ID IN PAY_ALL_PAYROLLS_F.PAYROLL_ID%TYPE) IS
403 SELECT
404 SUBSTR(PAYROLL_NAME
405 ,1
406 ,80)
407 FROM
408 PAY_ALL_PAYROLLS_F
409 WHERE PAYROLL_ID = C_PAYROLL_ID;
410 BEGIN
411 IF (LP_PAYROLL_ID IS NOT NULL) THEN
412 OPEN GET_PAYROLL_NAME(LP_PAYROLL_ID);
413 FETCH GET_PAYROLL_NAME
414 INTO
415 L_PAYROLL_NAME;
416 CLOSE GET_PAYROLL_NAME;
417 END IF;
418 RETURN L_PAYROLL_NAME;
419 END CF_PAYROLL_NAMEFORMULA;
420
421 FUNCTION CF_PAYROLL_RUN_DISPLAYFORMUL RETURN CHAR IS
422 L_PAYROLL_RUN VARCHAR2(80) := NULL;
423 CURSOR PAYROLL_RUN(C_PAYROLL_ACTION_ID IN PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID%TYPE) IS
424 SELECT
425 SUBSTR(PAP.PAYROLL_NAME
426 ,1
427 ,71 - LENGTH(PTP.PERIOD_NAME) - LENGTH(HRL.MEANING)) || ' - ' || PTP.PERIOD_NAME || ' (' || HRL.MEANING || ' ' || TO_CHAR(PPA.DISPLAY_RUN_NUMBER) || ')' DISPLAY
428 FROM
429 PAY_ALL_PAYROLLS_F PAP,
430 PER_TIME_PERIODS PTP,
431 PAY_PAYROLL_ACTIONS PPA,
432 HR_LOOKUPS HRL
433 WHERE PPA.PAYROLL_ACTION_ID = C_PAYROLL_ACTION_ID
434 AND PAP.PAYROLL_ID = PPA.PAYROLL_ID
435 AND PAP.PAYROLL_ID = PTP.PAYROLL_ID
436 AND PPA.DATE_EARNED between PTP.START_DATE
437 AND PTP.END_DATE
438 AND HRL.LOOKUP_TYPE = 'ACTION_TYPE'
439 AND HRL.LOOKUP_CODE = PPA.ACTION_TYPE;
440 BEGIN
441 IF (LP_PAYROLL_RUN_ID IS NOT NULL) THEN
442 OPEN PAYROLL_RUN(LP_PAYROLL_RUN_ID);
443 FETCH PAYROLL_RUN
444 INTO
445 L_PAYROLL_RUN;
446 CLOSE PAYROLL_RUN;
447 END IF;
448 RETURN L_PAYROLL_RUN;
449 END CF_PAYROLL_RUN_DISPLAYFORMUL;
450
451 FUNCTION CF_PERIOD_PAYROLLFORMULA(CF_PAYROLL_RUN_DISPLAY IN VARCHAR2) RETURN CHAR IS
452 L_PERIOD_PAYROLL VARCHAR2(100);
453 BEGIN
454 IF (LP_START_DATE IS NOT NULL) THEN
455 L_PERIOD_PAYROLL := 'Period Dates: ' || TO_CHAR(LP_START_DATE
456 ,'DD-MON-RRRR') || ' to ' || TO_CHAR(LP_END_DATE
457 ,'DD-MON-RRRR');
458 ELSE
459 L_PERIOD_PAYROLL := 'Payroll Run: ' || CF_PAYROLL_RUN_DISPLAY;
460 END IF;
461 RETURN (L_PERIOD_PAYROLL);
462 END CF_PERIOD_PAYROLLFORMULA;
463
464 FUNCTION CF_YTD_TOTALS_DISPLAYFORMULA RETURN CHAR IS
465 BEGIN
466 IF (LP_YTD_TOTALS = 'Y') THEN
467 RETURN ('Yes');
468 END IF;
469 IF (LP_YTD_TOTALS = 'N') THEN
470 RETURN ('No');
471 END IF;
472 END CF_YTD_TOTALS_DISPLAYFORMULA;
473
474 FUNCTION CF_ZERO_RECORDS_DISPLAYFORMULA RETURN CHAR IS
475 BEGIN
476 IF (LP_ZERO_RECORDS = 'Y') THEN
477 RETURN ('Yes');
478 END IF;
479 IF (LP_ZERO_RECORDS = 'N') THEN
480 RETURN ('No');
481 END IF;
482 END CF_ZERO_RECORDS_DISPLAYFORMULA;
483
484 FUNCTION CF_RETURN_BALANCESFORMULA(CS_ASG_TAXABLE_EARNINGS IN NUMBER
485 ,CS_ASG_NON_TAX_EARNINGS IN NUMBER
486 ,CS_ASG_DEDUCTIONS IN NUMBER
487 ,CS_ASG_TAX IN NUMBER
488 ,CS_ASG_NET_PAYMENT_RUN IN NUMBER
489 ,CS_ASG_EMP_CHG IN NUMBER
490 ,CS_ASG_GROSS_EARNINGS IN NUMBER
491 ,CS_ASG_PRE_TAX_DEDUCTIONS IN NUMBER
492 ,CS_ASG_DIRECT_PAYMENTS IN NUMBER
493 ,CS_ASG_YTD_GROSS_EARNINGS IN NUMBER
494 ,CS_ASG_YTD_PRE_TAX_DEDUCTIONS IN NUMBER
495 ,CS_ASG_YTD_DIRECT_PAYMENTS IN NUMBER
496 ,CS_ASG_YTD_TAX_EARN IN NUMBER
497 ,CS_ASG_YTD_NON_TAX IN NUMBER
498 ,CS_ASG_YTD_DEDUCTIONS IN NUMBER
499 ,CS_ASG_YTD_TAX IN NUMBER
500 ,CS_ASG_YTD_NET_PAY IN NUMBER
501 ,CS_ASG_YTD_EMP_CHG IN NUMBER
502 ,MANUAL_PS_ISSUED IN VARCHAR2
503 ,CS_PS_PAYSUM_GROSS IN NUMBER
504 ,CS_PS_WORKPLACE_GIVING IN NUMBER
505 ,CS_PS_REP_ALLOWANCES IN NUMBER
506 ,CS_PS_ETP IN NUMBER
507 ,CS_PS_RFB IN NUMBER
508 ,CS_PS_ASSESSABLE_ETP IN NUMBER
509 ,CS_PS_CDEP IN NUMBER
510 ,CS_PS_OTHER_INCOME IN NUMBER
511 ,CS_PS_UNION_FEES IN NUMBER
512 ,CS_PS_LUMPSUM_PAYMENTS IN NUMBER) RETURN NUMBER IS
513 --PRAGMA autonomous_transaction;
514 BEGIN
515 CP_TAXABLE_EARNINGS := CS_ASG_TAXABLE_EARNINGS;
516 CP_NON_TAXABLE_EARNINGS := CS_ASG_NON_TAX_EARNINGS;
517 CP_DEDUCTIONS := CS_ASG_DEDUCTIONS;
518 CP_TAX := CS_ASG_TAX;
519 CP_NET_PAYMENT := CS_ASG_NET_PAYMENT_RUN;
520 CP_EMP_CHG := CS_ASG_EMP_CHG;
521 CP_GROSS_EARNINGS := CS_ASG_GROSS_EARNINGS;
522 CP_PRE_TAX_DEDUCTIONS := CS_ASG_PRE_TAX_DEDUCTIONS;
523 CP_DIRECT_PAYMENTS := CS_ASG_DIRECT_PAYMENTS;
524 CP_YTD_GROSS_EARNINGS := CS_ASG_YTD_GROSS_EARNINGS;
525 --INSERT INTO LOG_MSG VALUES('SAMPATH','PYAURECD','CP_YTD_GROSS_EARNINGS',CP_YTD_GROSS_EARNINGS);COMMIT;
526 CP_YTD_PRE_TAX_DEDUCTIONS := CS_ASG_YTD_PRE_TAX_DEDUCTIONS;
527 CP_YTD_DIRECT_PAYMENTS := CS_ASG_YTD_DIRECT_PAYMENTS;
528 CP_YTD_TAXABLE_EARNINGS := CS_ASG_YTD_TAX_EARN;
529 CP_YTD_NON_TAXABLE_EARNINGS := CS_ASG_YTD_NON_TAX;
530 CP_YTD_DEDUCTIONS := CS_ASG_YTD_DEDUCTIONS;
531 CP_YTD_TAX := CS_ASG_YTD_TAX;
532 CP_YTD_NET_PAYMENT := CS_ASG_YTD_NET_PAY;
533 CP_YTD_EMP_CHG := CS_ASG_YTD_EMP_CHG;
534 IF (LP_ZERO_RECORDS = 'Y' AND CS_ASG_NET_PAYMENT_RUN = 0) THEN
535 CP_TAXABLE_EARNINGS := 0;
536 CP_NON_TAXABLE_EARNINGS := 0;
537 CP_DEDUCTIONS := 0;
538 CP_TAX := 0;
539 CP_NET_PAYMENT := 0;
540 CP_EMP_CHG := 0;
541 CP_GROSS_EARNINGS := 0;
542 CP_PRE_TAX_DEDUCTIONS := 0;
543 CP_DIRECT_PAYMENTS := 0;
544 CP_YTD_GROSS_EARNINGS := 0;
545 CP_YTD_DIRECT_PAYMENTS := 0;
546 CP_YTD_PRE_TAX_DEDUCTIONS := 0;
547 CP_YTD_TAXABLE_EARNINGS := 0;
548 CP_YTD_NON_TAXABLE_EARNINGS := 0;
549 CP_YTD_DEDUCTIONS := 0;
550 CP_YTD_TAX := 0;
551 CP_YTD_NET_PAYMENT := 0;
552 CP_YTD_EMP_CHG := 0;
553 END IF;
554 IF (LP_NEGATIVE_RECORDS = 'Y' AND CS_ASG_NET_PAYMENT_RUN < 0) THEN
555 CP_TAXABLE_EARNINGS := 0;
556 CP_NON_TAXABLE_EARNINGS := 0;
557 CP_DEDUCTIONS := 0;
558 CP_TAX := 0;
559 CP_NET_PAYMENT := 0;
560 CP_EMP_CHG := 0;
561 CP_GROSS_EARNINGS := 0;
562 CP_PRE_TAX_DEDUCTIONS := 0;
563 CP_DIRECT_PAYMENTS := 0;
564 CP_YTD_DIRECT_PAYMENTS := 0;
565 CP_YTD_GROSS_EARNINGS := 0;
566 CP_YTD_PRE_TAX_DEDUCTIONS := 0;
567 CP_YTD_TAXABLE_EARNINGS := 0;
568 CP_YTD_NON_TAXABLE_EARNINGS := 0;
569 CP_YTD_DEDUCTIONS := 0;
570 CP_YTD_TAX := 0;
571 CP_YTD_NET_PAYMENT := 0;
572 CP_YTD_EMP_CHG := 0;
573 END IF;
574 IF (P_PAYSUM_FLAG = 'Y') THEN
575 IF MANUAL_PS_ISSUED = 'Y' THEN
576 CP_MAN_PS_PAYSUM_GROSS := CP_MAN_PS_PAYSUM_GROSS + CS_PS_PAYSUM_GROSS;
577 CP_MAN_PS_WORKPLACE_GIVING := CP_MAN_PS_WORKPLACE_GIVING + CS_PS_WORKPLACE_GIVING;
578 CP_MAN_PS_REP_ALLOWANCES := CP_MAN_PS_REP_ALLOWANCES + CS_PS_REP_ALLOWANCES;
579 CP_MAN_PS_ETP := CP_MAN_PS_ETP + CS_PS_ETP;
580 CP_MAN_PS_RFB := CP_MAN_PS_RFB + CS_PS_RFB;
581 CP_MAN_PS_ASSESSABLE_ETP := CP_MAN_PS_ASSESSABLE_ETP + CS_PS_ASSESSABLE_ETP;
582 CP_MAN_PS_CDEP := CP_MAN_PS_CDEP + CS_PS_CDEP;
583 CP_MAN_PS_OTHER_INCOME := CP_MAN_PS_OTHER_INCOME + CS_PS_OTHER_INCOME;
584 CP_MAN_PS_UNION_FEES := CP_MAN_PS_UNION_FEES + CS_PS_UNION_FEES;
585 CP_MAN_PS_LUMPSUM_PAYMENTS := CP_MAN_PS_LUMPSUM_PAYMENTS + CS_PS_LUMPSUM_PAYMENTS;
586 CP_MANUAL_COUNT := CP_MANUAL_COUNT + 1;
587 ELSE
588 CP_PS_PAYSUM_GROSS := CP_PS_PAYSUM_GROSS + CS_PS_PAYSUM_GROSS;
589 CP_PS_WORKPLACE_GIVING := CP_PS_WORKPLACE_GIVING + CS_PS_WORKPLACE_GIVING;
590 CP_PS_REP_ALLOWANCES := CP_PS_REP_ALLOWANCES + CS_PS_REP_ALLOWANCES;
591 CP_PS_ETP := CP_PS_ETP + CS_PS_ETP;
592 CP_PS_RFB := CP_PS_RFB + CS_PS_RFB;
593 CP_PS_ASSESSABLE_ETP := CP_PS_ASSESSABLE_ETP + CS_PS_ASSESSABLE_ETP;
594 CP_PS_CDEP := CP_PS_CDEP + CS_PS_CDEP;
595 CP_PS_OTHER_INCOME := CP_PS_OTHER_INCOME + CS_PS_OTHER_INCOME;
596 CP_PS_UNION_FEES := CP_PS_UNION_FEES + CS_PS_UNION_FEES;
597 CP_PS_LUMPSUM_PAYMENTS := CP_PS_LUMPSUM_PAYMENTS + CS_PS_LUMPSUM_PAYMENTS;
598 CP_PRINTED_COUNT := CP_PRINTED_COUNT + 1;
599 END IF;
600 END IF;
601 RETURN (NULL);
602 END CF_RETURN_BALANCESFORMULA;
603
604 FUNCTION CF_FIN_YEAR_DISPLAYFORMULA RETURN CHAR IS
605 L_FIN_YEAR VARCHAR2(80);
606 BEGIN
607 IF (LP_START_DATE IS NOT NULL AND LP_END_DATE IS NOT NULL) THEN
608 L_FIN_YEAR := TO_CHAR(LP_START_DATE
609 ,'YYYY') || '/' || TO_CHAR(LP_END_DATE
610 ,'YYYY');
611 END IF;
612 RETURN L_FIN_YEAR;
613 END CF_FIN_YEAR_DISPLAYFORMULA;
614
615 FUNCTION CF_LST_YEAR_TERM_DISPLAYFORMUL RETURN CHAR IS
616 BEGIN
617 IF (LP_LST_YEAR_TERM = 'Y' OR LP_LST_YEAR_TERM IS NULL) THEN
618 RETURN ('Yes');
619 ELSE
620 RETURN ('No');
621 END IF;
622 END CF_LST_YEAR_TERM_DISPLAYFORMUL;
623
624 FUNCTION CF_TITLE_DISPLAYFORMULA RETURN CHAR IS
625 L_TITLE VARCHAR2(100);
626 BEGIN
627 IF (P_PAYSUM_FLAG = 'Y') THEN
628 L_TITLE := 'End of Year Reconciliation Detail Report (AUD)';
629 ELSE
630 L_TITLE := 'Payroll Reconciliation Detail Report (AUD) ';
631 END IF;
632 RETURN (L_TITLE);
633 END CF_TITLE_DISPLAYFORMULA;
634
635 FUNCTION CP_TAXABLE_EARNINGS_P RETURN NUMBER IS
636 BEGIN
637 RETURN CP_TAXABLE_EARNINGS;
638 END CP_TAXABLE_EARNINGS_P;
639
640 FUNCTION CP_NON_TAXABLE_EARNINGS_P RETURN NUMBER IS
641 BEGIN
642 RETURN CP_NON_TAXABLE_EARNINGS;
643 END CP_NON_TAXABLE_EARNINGS_P;
644
645 FUNCTION CP_DEDUCTIONS_P RETURN NUMBER IS
646 BEGIN
647 RETURN CP_DEDUCTIONS;
648 END CP_DEDUCTIONS_P;
649
650 FUNCTION CP_PRE_TAX_DEDUCTIONS_P RETURN NUMBER IS
651 BEGIN
652 RETURN CP_PRE_TAX_DEDUCTIONS;
653 END CP_PRE_TAX_DEDUCTIONS_P;
654
655 FUNCTION CP_DIRECT_PAYMENTS_P RETURN NUMBER IS
656 BEGIN
657 RETURN CP_DIRECT_PAYMENTS;
658 END CP_DIRECT_PAYMENTS_P;
659
660 FUNCTION CP_TAX_P RETURN NUMBER IS
661 BEGIN
662 RETURN CP_TAX;
663 END CP_TAX_P;
664
665 FUNCTION CP_GROSS_EARNINGS_P RETURN NUMBER IS
666 BEGIN
667 RETURN CP_GROSS_EARNINGS;
668 END CP_GROSS_EARNINGS_P;
669
670 FUNCTION CP_NET_PAYMENT_P RETURN NUMBER IS
671 BEGIN
672 RETURN CP_NET_PAYMENT;
673 END CP_NET_PAYMENT_P;
674
675 FUNCTION CP_YTD_TAXABLE_EARNINGS_P RETURN NUMBER IS
676 BEGIN
677 RETURN CP_YTD_TAXABLE_EARNINGS;
678 END CP_YTD_TAXABLE_EARNINGS_P;
679
680 FUNCTION CP_YTD_NON_TAXABLE_EARNINGS_P RETURN NUMBER IS
681 BEGIN
682 RETURN CP_YTD_NON_TAXABLE_EARNINGS;
683 END CP_YTD_NON_TAXABLE_EARNINGS_P;
684
685 FUNCTION CP_YTD_GROSS_EARNINGS_P RETURN NUMBER IS
686 BEGIN
687 RETURN CP_YTD_GROSS_EARNINGS;
688 END CP_YTD_GROSS_EARNINGS_P;
689
690 FUNCTION CP_YTD_PRE_TAX_DEDUCTIONS_P RETURN NUMBER IS
691 BEGIN
692 RETURN CP_YTD_PRE_TAX_DEDUCTIONS;
693 END CP_YTD_PRE_TAX_DEDUCTIONS_P;
694
695 FUNCTION CP_YTD_DIRECT_PAYMENTS_P RETURN NUMBER IS
696 BEGIN
697 RETURN CP_YTD_DIRECT_PAYMENTS;
698 END CP_YTD_DIRECT_PAYMENTS_P;
699
700 FUNCTION CP_YTD_DEDUCTIONS_P RETURN NUMBER IS
701 BEGIN
702 RETURN CP_YTD_DEDUCTIONS;
703 END CP_YTD_DEDUCTIONS_P;
704
705 FUNCTION CP_YTD_TAX_P RETURN NUMBER IS
706 BEGIN
707 RETURN CP_YTD_TAX;
708 END CP_YTD_TAX_P;
709
710 FUNCTION CP_YTD_NET_PAYMENT_P RETURN NUMBER IS
711 BEGIN
712 RETURN CP_YTD_NET_PAYMENT;
713 END CP_YTD_NET_PAYMENT_P;
714
715 FUNCTION CP_EMP_CHG_P RETURN NUMBER IS
716 BEGIN
717 RETURN CP_EMP_CHG;
718 END CP_EMP_CHG_P;
719
720 FUNCTION CP_YTD_EMP_CHG_P RETURN NUMBER IS
721 BEGIN
722 RETURN CP_YTD_EMP_CHG;
723 END CP_YTD_EMP_CHG_P;
724
725 FUNCTION CP_END_REPORT_P RETURN VARCHAR2 IS
726 BEGIN
727 RETURN CP_END_REPORT;
728 END CP_END_REPORT_P;
729
730 FUNCTION CP_NO_DATA_FOUND_P RETURN VARCHAR2 IS
731 BEGIN
732 RETURN CP_NO_DATA_FOUND;
733 END CP_NO_DATA_FOUND_P;
734
735 FUNCTION CP_PS_WORKPLACE_GIVING_P RETURN NUMBER IS
736 BEGIN
737 RETURN CP_PS_WORKPLACE_GIVING;
738 END CP_PS_WORKPLACE_GIVING_P;
739
740 FUNCTION CP_MAN_PS_WORKPLACE_GIVING_P RETURN NUMBER IS
741 BEGIN
742 RETURN CP_MAN_PS_WORKPLACE_GIVING;
743 END CP_MAN_PS_WORKPLACE_GIVING_P;
744
745 FUNCTION CP_PS_PAYSUM_GROSS_P RETURN NUMBER IS
746 BEGIN
747 RETURN CP_PS_PAYSUM_GROSS;
748 END CP_PS_PAYSUM_GROSS_P;
749
750 FUNCTION CP_MAN_PS_PAYSUM_GROSS_P RETURN NUMBER IS
751 BEGIN
752 RETURN CP_MAN_PS_PAYSUM_GROSS;
753 END CP_MAN_PS_PAYSUM_GROSS_P;
754
755 FUNCTION CP_PS_REP_ALLOWANCES_P RETURN NUMBER IS
756 BEGIN
757 RETURN CP_PS_REP_ALLOWANCES;
758 END CP_PS_REP_ALLOWANCES_P;
759
760 FUNCTION CP_MAN_PS_REP_ALLOWANCES_P RETURN NUMBER IS
761 BEGIN
762 RETURN CP_MAN_PS_REP_ALLOWANCES;
763 END CP_MAN_PS_REP_ALLOWANCES_P;
764
765 FUNCTION CP_PS_ETP_P RETURN NUMBER IS
766 BEGIN
767 RETURN CP_PS_ETP;
768 END CP_PS_ETP_P;
769
770 FUNCTION CP_MAN_PS_ETP_P RETURN NUMBER IS
771 BEGIN
772 RETURN CP_MAN_PS_ETP;
773 END CP_MAN_PS_ETP_P;
774
775 FUNCTION CP_PS_RFB_P RETURN NUMBER IS
776 BEGIN
777 RETURN CP_PS_RFB;
778 END CP_PS_RFB_P;
779
780 FUNCTION CP_MAN_PS_RFB_P RETURN NUMBER IS
781 BEGIN
782 RETURN CP_MAN_PS_RFB;
783 END CP_MAN_PS_RFB_P;
784
785 FUNCTION CP_PS_ASSESSABLE_ETP_P RETURN NUMBER IS
786 BEGIN
787 RETURN CP_PS_ASSESSABLE_ETP;
788 END CP_PS_ASSESSABLE_ETP_P;
789
790 FUNCTION CP_MAN_PS_ASSESSABLE_ETP_P RETURN NUMBER IS
791 BEGIN
792 RETURN CP_MAN_PS_ASSESSABLE_ETP;
793 END CP_MAN_PS_ASSESSABLE_ETP_P;
794
795 FUNCTION CP_PS_CDEP_P RETURN NUMBER IS
796 BEGIN
797 RETURN CP_PS_CDEP;
798 END CP_PS_CDEP_P;
799
800 FUNCTION CP_MAN_PS_CDEP_P RETURN NUMBER IS
801 BEGIN
802 RETURN CP_MAN_PS_CDEP;
803 END CP_MAN_PS_CDEP_P;
804
805 FUNCTION CP_PS_OTHER_INCOME_P RETURN NUMBER IS
806 BEGIN
807 RETURN CP_PS_OTHER_INCOME;
808 END CP_PS_OTHER_INCOME_P;
809
810 FUNCTION CP_MAN_PS_OTHER_INCOME_P RETURN NUMBER IS
811 BEGIN
812 RETURN CP_MAN_PS_OTHER_INCOME;
813 END CP_MAN_PS_OTHER_INCOME_P;
814
815 FUNCTION CP_PS_UNION_FEES_P RETURN NUMBER IS
816 BEGIN
817 RETURN CP_PS_UNION_FEES;
818 END CP_PS_UNION_FEES_P;
819
820 FUNCTION CP_MAN_PS_UNION_FEES_P RETURN NUMBER IS
821 BEGIN
822 RETURN CP_MAN_PS_UNION_FEES;
823 END CP_MAN_PS_UNION_FEES_P;
824
825 FUNCTION CP_PS_LUMPSUM_PAYMENTS_P RETURN NUMBER IS
826 BEGIN
827 RETURN CP_PS_LUMPSUM_PAYMENTS;
828 END CP_PS_LUMPSUM_PAYMENTS_P;
829
830 FUNCTION CP_MAN_PS_LUMPSUM_PAYMENTS_P RETURN NUMBER IS
831 BEGIN
832 RETURN CP_MAN_PS_LUMPSUM_PAYMENTS;
833 END CP_MAN_PS_LUMPSUM_PAYMENTS_P;
834
835 FUNCTION CP_PRINTED_COUNT_P RETURN NUMBER IS
836 BEGIN
837 RETURN CP_PRINTED_COUNT;
838 END CP_PRINTED_COUNT_P;
839
840 FUNCTION CP_MANUAL_COUNT_P RETURN NUMBER IS
841 BEGIN
842 RETURN CP_MANUAL_COUNT;
843 END CP_MANUAL_COUNT_P;
844
845 END PAY_PYAURECD_XMLP_PKG;
846
[Home] [Help]