[Home] [Help]
PACKAGE BODY: APPS.PAY_PYAUPYT_XMLP_PKG
Source
1 PACKAGE BODY PAY_PYAUPYT_XMLP_PKG AS
2 /* $Header: PYAUPYLB.pls 120.1 2010/09/02 07:52:03 pmatamsr ship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 DELETE FROM PAY_ACTION_INFORMATION
7 WHERE ACTION_INFORMATION_CATEGORY = 'AU_PAYROLL_TAX_EMPLOYEE_DETAILS'
8 AND ACTION_CONTEXT_ID = P_PAYROLL_ACTION_ID_LP;
9 DELETE FROM PAY_ACTION_INFORMATION
10 WHERE ACTION_INFORMATION_CATEGORY in ( 'AU_PAYROLL_TAX_DGE_DETAILS' , 'AU_PAYROLL_TAX_BALANCE_DETAILS_YTD' )
11 AND ACTION_CONTEXT_ID in (
12 SELECT
13 PAA.ASSIGNMENT_ACTION_ID
14 FROM
15 PAY_ASSIGNMENT_ACTIONS PAA
16 WHERE PAA.PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID_LP );
17 DELETE FROM PAY_ACTION_INFORMATION
18 WHERE ACTION_INFORMATION_CATEGORY = 'AU_ARCHIVE_ASG_DETAILS'
19 AND ACTION_INFORMATION2 = P_PAYROLL_ACTION_ID_LP;
20 DELETE FROM PAY_ASSIGNMENT_ACTIONS PAA
21 WHERE PAA.PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID_LP;
22 DELETE FROM PAY_PAYROLL_ACTIONS PPA
23 WHERE PPA.PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID_LP;
24 EXCEPTION
25 WHEN OTHERS THEN
26 ROLLBACK;
27 END;
28 COMMIT;
29 RETURN (TRUE);
30 END AFTERREPORT;
31
32 FUNCTION CF_BUSINESS_GROUPFORMULA RETURN VARCHAR2 IS
33 V_BUSINESS_GROUP HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
34 BEGIN
35 V_BUSINESS_GROUP := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID_LP);
36 RETURN V_BUSINESS_GROUP;
37 END CF_BUSINESS_GROUPFORMULA;
38
39 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
40 CURSOR C_STATE IS
41 SELECT
42 MEANING
43 FROM
44 HR_LOOKUPS
45 WHERE LOOKUP_TYPE = 'AU_STATE'
46 AND ENABLED_FLAG = 'Y'
47 AND LOOKUP_CODE = P_TAX_STATE_LP;
48 CURSOR C_TYPE IS
49 SELECT
50 MEANING
51 FROM
52 HR_LOOKUPS
53 WHERE LOOKUP_TYPE = 'AU_PAYROLL_TAX_REPORT_TYPE'
54 AND ENABLED_FLAG = 'Y'
55 AND LOOKUP_CODE = P_REPORT_TYPE_LP;
56 CURSOR C_LEGAL_EMPLOYER IS
57 SELECT
58 NAME
59 FROM
60 HR_AU_LEG_EMP_V
61 WHERE ORGANIZATION_ID = P_LEGAL_EMPLOYER_LP;
62 CURSOR C_DGE IS
63 SELECT
64 ORG_INFORMATION3
65 FROM
66 HR_ORGANIZATION_UNITS HOU,
67 HR_ORGANIZATION_INFORMATION HOI
68 WHERE HOI.ORGANIZATION_ID = P_LEGAL_EMPLOYER_LP
69 AND HOI.ORG_INFORMATION2 = P_TAX_STATE_LP
70 AND HOI.ORG_INFORMATION_CONTEXT = 'AU_PAYROLL_TAX_DGE';
71
72 /* Bug 10069164 - Added cursor in order to check existence of record in FND_SESSIONS table. */
73 CURSOR C_SESSION IS
74 SELECT 'X'
75 FROM FND_SESSIONS
76 WHERE SESSION_ID = USERENV('SESSIONID');
77
78 L_SESSION_FLAG VARCHAR2(1);
79 BEGIN
80
81 select
82 SUBSTR(argument1,INSTR(argument1,'=',1)+1,LENGTH(argument1))
83 ,
84 SUBSTR(argument2,INSTR(argument2,'=',1)+1,LENGTH(argument2)),
85 SUBSTR(argument3,INSTR(argument3,'=',1)+1,LENGTH(argument3))
86 ,
87 SUBSTR(argument4,INSTR(argument4,'=',1)+1,LENGTH(argument4)),
88 SUBSTR(argument5,INSTR(argument5,'=',1)+1,LENGTH(argument5)),
89 SUBSTR(argument6,INSTR(argument6,'=',1)+1,LENGTH(argument6)),
90 SUBSTR(argument7,INSTR(argument7,'=',1)+1,LENGTH(argument7)),
91 SUBSTR(argument8,INSTR(argument8,'=',1)+1,LENGTH(argument8)),
92 SUBSTR(argument9,INSTR(argument9,'=',1)+1,LENGTH(argument9)),
93 SUBSTR(argument10,INSTR(argument10,'=',1)+1,LENGTH(argument10)),
94 SUBSTR(argument11,INSTR(argument11,'=',1)+1,LENGTH(argument11)),
95 SUBSTR(argument12,INSTR(argument12,'=',1)+1,LENGTH(argument12)),
96 SUBSTR(argument13,INSTR(argument13,'=',1)+1,LENGTH(argument13)),
97 SUBSTR(argument14,INSTR(argument14,'=',1)+1,LENGTH(argument14)),
98 SUBSTR(argument15,INSTR(argument15,'=',1)+1,LENGTH(argument15)),
99 SUBSTR(argument16,INSTR(argument16,'=',1)+1,LENGTH(argument16)),
100 SUBSTR(argument17,INSTR(argument17,'=',1)+1,LENGTH(argument17))
101 into
102 P_PAYROLL_ACTION_ID_LP
103 ,
104 P_BUSINESS_GROUP_ID_LP,
105 P_LEGAL_EMPLOYER_LP
106 ,
107 P_PERIOD_LP_1,
108 P_START_DATE_LP_1,
109 P_END_DATE_LP_1,
110 P_TAX_STATE_LP,
111 P_ACT_LP,
112 P_QLD_LP,
113 P_SA_LP,
114 P_TAS_LP,
115 P_VIC_LP ,
116 P_WA_LP ,
117 P_NSW_LP,
118 P_NT_LP,
119 P_REPORT_TYPE_LP ,
120 P_REPORT_NAME_LP
121
122 from FND_CONCURRENT_REQUESTS
123 where request_id= FND_GLOBAL.conc_request_id;
124
125 P_PERIOD_LP := TO_DATE(P_PERIOD_LP_1,'YYYY/MM/DD');
126 FINAL_DATE := TO_CHAR(P_PERIOD_LP,'MON/YYYY');
127 P_START_DATE := TO_DATE(P_START_DATE_LP_1,'YYYY/MM/DD');
128 P_END_DATE := TO_DATE(P_END_DATE_LP_1,'YYYY/MM/DD');
129
130 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
131 IF (P_LEGAL_EMPLOYER_LP IS NOT NULL) THEN
132 OPEN C_LEGAL_EMPLOYER;
133 FETCH C_LEGAL_EMPLOYER
134 INTO
135 CP_LEGAL_EMPLOYER;
136 CLOSE C_LEGAL_EMPLOYER;
137 ELSE
138 CP_LEGAL_EMPLOYER := NULL;
139 END IF;
140 IF (P_TAX_STATE_LP IS NOT NULL) THEN
141 OPEN C_STATE;
142 FETCH C_STATE
143 INTO
144 CP_TAX_STATE;
145 CLOSE C_STATE;
146 ELSE
147 CP_TAX_STATE := NULL;
148 END IF;
149 IF (P_REPORT_TYPE_LP IS NOT NULL) THEN
150 OPEN C_TYPE;
151 FETCH C_TYPE
152 INTO
153 CP_REPORT_TYPE;
154 CLOSE C_TYPE;
155 ELSE
156 CP_REPORT_TYPE := NULL;
157 END IF;
158 IF (P_LEGAL_EMPLOYER_LP IS NOT NULL AND P_TAX_STATE_LP IS NOT NULL) THEN
159 OPEN C_DGE;
160 FETCH C_DGE
161 INTO
162 CP_DGE;
163 CLOSE C_DGE;
164 ELSE
165 CP_DGE := NULL;
166 END IF;
167 /* Bug 10069164 - Added code to check existence of record in FND_SESSIONS table in order to
168 access the Global values in pay_au_paytax_pkg.get_tax function */
169 OPEN C_SESSION;
170 FETCH C_SESSION INTO L_SESSION_FLAG;
171 IF C_SESSION%NOTFOUND THEN
172 INSERT INTO FND_SESSIONS (SESSION_ID, EFFECTIVE_DATE) VALUES (USERENV('SESSIONID'),TRUNC(P_START_DATE));
173 END IF;
174 CLOSE C_SESSION;
175
176 RETURN (TRUE);
177 END BEFOREREPORT;
178
179 FUNCTION CF_LEGISLATION_CODEFORMULA RETURN VARCHAR2 IS
180 V_LEGISLATION_CODE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%TYPE := NULL;
181 CURSOR LEGISLATION_CODE(C_BUSINESS_GROUP_ID IN HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE) IS
182 SELECT
183 ORG_INFORMATION9
184 FROM
185 HR_ORGANIZATION_INFORMATION
186 WHERE ORGANIZATION_ID = C_BUSINESS_GROUP_ID
187 AND ORG_INFORMATION9 is not null
188 AND ORG_INFORMATION_CONTEXT = 'Business Group Information';
189 BEGIN
190 OPEN LEGISLATION_CODE(P_BUSINESS_GROUP_ID_LP);
191 FETCH LEGISLATION_CODE
192 INTO
193 V_LEGISLATION_CODE;
194 CLOSE LEGISLATION_CODE;
195 RETURN V_LEGISLATION_CODE;
196 END CF_LEGISLATION_CODEFORMULA;
197
198 FUNCTION CF_CURRENCY_FORMAT_MASKFORMULA(CF_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
199 V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
200 V_FORMAT_MASK VARCHAR2(100) := NULL;
201 V_FIELD_LENGTH NUMBER(3) := 30;
202 CURSOR CURRENCY_FORMAT_MASK(C_TERRITORY_CODE IN FND_CURRENCIES.ISSUING_TERRITORY_CODE%TYPE) IS
203 SELECT
204 CURRENCY_CODE
205 FROM
206 FND_CURRENCIES
207 WHERE ISSUING_TERRITORY_CODE = C_TERRITORY_CODE;
208 BEGIN
209 OPEN CURRENCY_FORMAT_MASK(CF_LEGISLATION_CODE);
210 FETCH CURRENCY_FORMAT_MASK
211 INTO
212 V_CURRENCY_CODE;
213 CLOSE CURRENCY_FORMAT_MASK;
214 IF (V_CURRENCY_CODE IS NOT NULL) THEN
215 CP_CURRENCY := '(' || V_CURRENCY_CODE || ')';
216 ELSE
217 CP_CURRENCY := NULL;
218 END IF;
219 V_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(V_CURRENCY_CODE
220 ,V_FIELD_LENGTH);
221 RETURN V_FORMAT_MASK;
222 END CF_CURRENCY_FORMAT_MASKFORMULA;
223
224 FUNCTION CF_STATE_TAXFORMULA(NO_OF_STATES IN NUMBER
225 ,CS_DGE_STATE IN VARCHAR2
226 ,CS_DGE_GROUP_NAME IN VARCHAR2
227 ,STATE_CODE IN VARCHAR2
228 ,CS_STATE_TAXABLE_INCOME IN NUMBER
229 ,LE_TAXABLE_INCOME IN NUMBER
230 ,CS_NO_OF_STATES IN NUMBER
231 ,CS_TOTAL_TAXABLE_INCOME_LE IN NUMBER) RETURN NUMBER IS
232 BEGIN
233 IF (P_LEGAL_EMPLOYER_LP IS NOT NULL) THEN
234 IF (P_TAX_STATE_LP IS NOT NULL) THEN
235 IF (P_TAX_STATE_LP = 'ACT') THEN
236 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
237 ,CS_DGE_STATE
238 ,CS_DGE_GROUP_NAME
239 ,STATE_CODE
240 ,CS_STATE_TAXABLE_INCOME
241 ,LE_TAXABLE_INCOME
242 ,CP_MESSAGE
243 ,CP_OT_MESSAGE
244 ,P_START_DATE
245 ,P_END_DATE
246 ,NVL(P_NSW_LP
247 ,-9999)));
248 END IF;
249 IF (P_TAX_STATE_LP = 'NSW') THEN
250 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
251 ,CS_DGE_STATE
252 ,CS_DGE_GROUP_NAME
253 ,STATE_CODE
254 ,CS_STATE_TAXABLE_INCOME
255 ,LE_TAXABLE_INCOME
256 ,CP_MESSAGE
257 ,CP_OT_MESSAGE
258 ,P_START_DATE
259 ,P_END_DATE
260 ,NVL(P_NSW_LP
261 ,-9999)));
262 END IF;
263 IF (P_TAX_STATE_LP = 'VIC') THEN
264 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
265 ,CS_DGE_STATE
266 ,CS_DGE_GROUP_NAME
267 ,STATE_CODE
268 ,CS_STATE_TAXABLE_INCOME
269 ,LE_TAXABLE_INCOME
270 ,CP_MESSAGE
271 ,CP_OT_MESSAGE
272 ,P_START_DATE
273 ,P_END_DATE
274 ,NVL(P_VIC_LP
275 ,-9999)));
276 END IF;
277 IF (P_TAX_STATE_LP = 'QLD') THEN
278 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
279 ,CS_DGE_STATE
280 ,CS_DGE_GROUP_NAME
281 ,STATE_CODE
282 ,CS_STATE_TAXABLE_INCOME
283 ,LE_TAXABLE_INCOME
284 ,CP_MESSAGE
285 ,CP_OT_MESSAGE
286 ,P_START_DATE
287 ,P_END_DATE
288 ,NVL(P_QLD_LP
289 ,-9999)));
290 END IF;
291 IF (P_TAX_STATE_LP = 'SA') THEN
292 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
293 ,CS_DGE_STATE
294 ,CS_DGE_GROUP_NAME
295 ,STATE_CODE
296 ,CS_STATE_TAXABLE_INCOME
297 ,LE_TAXABLE_INCOME
298 ,CP_MESSAGE
299 ,CP_OT_MESSAGE
300 ,P_START_DATE
301 ,P_END_DATE
302 ,NVL(P_SA_LP
303 ,-9999)));
304 END IF;
305 IF (P_TAX_STATE_LP = 'WA') THEN
306 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
307 ,CS_DGE_STATE
308 ,CS_DGE_GROUP_NAME
309 ,STATE_CODE
310 ,CS_STATE_TAXABLE_INCOME
311 ,LE_TAXABLE_INCOME
312 ,CP_MESSAGE
313 ,CP_OT_MESSAGE
314 ,P_START_DATE
315 ,P_END_DATE
316 ,NVL(P_WA_LP
317 ,-9999)));
318 END IF;
319 IF (P_TAX_STATE_LP = 'TAS') THEN
320 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
321 ,CS_DGE_STATE
322 ,CS_DGE_GROUP_NAME
323 ,STATE_CODE
324 ,CS_STATE_TAXABLE_INCOME
325 ,LE_TAXABLE_INCOME
326 ,CP_MESSAGE
327 ,CP_OT_MESSAGE
328 ,P_START_DATE
329 ,P_END_DATE
330 ,NVL(P_TAS_LP
331 ,-9999)));
332 END IF;
333 IF (P_TAX_STATE_LP = 'NT') THEN
334 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
335 ,CS_DGE_STATE
336 ,CS_DGE_GROUP_NAME
337 ,STATE_CODE
338 ,CS_STATE_TAXABLE_INCOME
339 ,LE_TAXABLE_INCOME
340 ,CP_MESSAGE
341 ,CP_OT_MESSAGE
342 ,P_START_DATE
343 ,P_END_DATE
344 ,NVL(P_NT_LP
345 ,-9999)));
346 END IF;
347 ELSE
348 IF (STATE_CODE = 'ACT') THEN
349 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
350 ,CS_DGE_STATE
351 ,CS_DGE_GROUP_NAME
352 ,STATE_CODE
353 ,CS_STATE_TAXABLE_INCOME
354 ,LE_TAXABLE_INCOME
355 ,CP_MESSAGE
356 ,CP_OT_MESSAGE
357 ,P_START_DATE
358 ,P_END_DATE
359 ,NVL(P_ACT_LP
360 ,-9999)));
361 END IF;
362 IF (STATE_CODE = 'NSW') THEN
363 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
364 ,CS_DGE_STATE
365 ,CS_DGE_GROUP_NAME
366 ,STATE_CODE
367 ,CS_STATE_TAXABLE_INCOME
368 ,LE_TAXABLE_INCOME
369 ,CP_MESSAGE
370 ,CP_OT_MESSAGE
371 ,P_START_DATE
372 ,P_END_DATE
373 ,NVL(P_NSW_LP
374 ,-9999)));
375 END IF;
376 IF (STATE_CODE = 'VIC') THEN
377 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
378 ,CS_DGE_STATE
379 ,CS_DGE_GROUP_NAME
380 ,STATE_CODE
381 ,CS_STATE_TAXABLE_INCOME
382 ,LE_TAXABLE_INCOME
383 ,CP_MESSAGE
384 ,CP_OT_MESSAGE
385 ,P_START_DATE
386 ,P_END_DATE
387 ,NVL(P_VIC_LP
388 ,-9999)));
389 END IF;
390 IF (STATE_CODE = 'QLD') THEN
391 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
392 ,CS_DGE_STATE
393 ,CS_DGE_GROUP_NAME
394 ,STATE_CODE
395 ,CS_STATE_TAXABLE_INCOME
396 ,LE_TAXABLE_INCOME
397 ,CP_MESSAGE
398 ,CP_OT_MESSAGE
399 ,P_START_DATE
400 ,P_END_DATE
401 ,NVL(P_QLD_LP
402 ,-9999)));
403 END IF;
404 IF (STATE_CODE = 'SA') THEN
405 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
406 ,CS_DGE_STATE
407 ,CS_DGE_GROUP_NAME
408 ,STATE_CODE
409 ,CS_STATE_TAXABLE_INCOME
410 ,LE_TAXABLE_INCOME
411 ,CP_MESSAGE
412 ,CP_OT_MESSAGE
413 ,P_START_DATE
414 ,P_END_DATE
415 ,NVL(P_SA_LP
416 ,-9999)));
417 END IF;
418 IF (STATE_CODE = 'WA') THEN
419 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
420 ,CS_DGE_STATE
421 ,CS_DGE_GROUP_NAME
422 ,STATE_CODE
423 ,CS_STATE_TAXABLE_INCOME
424 ,LE_TAXABLE_INCOME
425 ,CP_MESSAGE
426 ,CP_OT_MESSAGE
427 ,P_START_DATE
428 ,P_END_DATE
429 ,NVL(P_WA_LP
430 ,-9999)));
431 END IF;
432 IF (STATE_CODE = 'TAS') THEN
433 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
434 ,CS_DGE_STATE
435 ,CS_DGE_GROUP_NAME
436 ,STATE_CODE
437 ,CS_STATE_TAXABLE_INCOME
438 ,LE_TAXABLE_INCOME
439 ,CP_MESSAGE
440 ,CP_OT_MESSAGE
441 ,P_START_DATE
442 ,P_END_DATE
443 ,NVL(P_TAS_LP
444 ,-9999)));
445 END IF;
446 IF (STATE_CODE = 'NT') THEN
447 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
448 ,CS_DGE_STATE
449 ,CS_DGE_GROUP_NAME
450 ,STATE_CODE
451 ,CS_STATE_TAXABLE_INCOME
452 ,LE_TAXABLE_INCOME
453 ,CP_MESSAGE
454 ,CP_OT_MESSAGE
455 ,P_START_DATE
456 ,P_END_DATE
457 ,NVL(P_NT_LP
458 ,-9999)));
459 END IF;
460 END IF;
461 ELSE
462 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(CS_NO_OF_STATES
463 ,CS_DGE_STATE
464 ,CS_DGE_GROUP_NAME
465 ,STATE_CODE
466 ,CS_STATE_TAXABLE_INCOME
467 ,CS_TOTAL_TAXABLE_INCOME_LE
468 ,CP_MESSAGE
469 ,CP_OT_MESSAGE
470 ,P_START_DATE
471 ,P_END_DATE
472 ,-9999));
473 END IF;
474 END CF_STATE_TAXFORMULA;
475
476 PROCEDURE SET_FORMAT_MASK IS
477 BEGIN
478 NULL;
479 END SET_FORMAT_MASK;
480
481 FUNCTION CP_OT_MESSAGE_P RETURN VARCHAR2 IS
482 BEGIN
483 RETURN CP_OT_MESSAGE;
484 END CP_OT_MESSAGE_P;
485
486 FUNCTION CP_MESSAGE_P RETURN VARCHAR2 IS
487 BEGIN
488 RETURN CP_MESSAGE;
489 END CP_MESSAGE_P;
490
491 FUNCTION CP_CURRENCY_P RETURN VARCHAR2 IS
492 BEGIN
493 RETURN CP_CURRENCY;
494 END CP_CURRENCY_P;
495
496 FUNCTION CP_TAX_STATE_P RETURN VARCHAR2 IS
497 BEGIN
498 RETURN CP_TAX_STATE;
499 END CP_TAX_STATE_P;
500
501 FUNCTION CP_REPORT_TYPE_P RETURN VARCHAR2 IS
502 BEGIN
503 RETURN CP_REPORT_TYPE;
504 END CP_REPORT_TYPE_P;
505
506 FUNCTION CP_LEGAL_EMPLOYER_P RETURN VARCHAR2 IS
507 BEGIN
508 RETURN CP_LEGAL_EMPLOYER;
509 END CP_LEGAL_EMPLOYER_P;
510
511 FUNCTION CP_DGE_P RETURN NUMBER IS
512 BEGIN
513 RETURN CP_DGE;
514 END CP_DGE_P;
515
516 END PAY_PYAUPYT_XMLP_PKG;