[Home] [Help]
PACKAGE BODY: APPS.PAY_PYAUPYT_XMLP_PKG
Source
1 PACKAGE BODY PAY_PYAUPYT_XMLP_PKG AS
2 /* $Header: PYAUPYLB.pls 120.0 2007/12/13 12:14:38 amakrish noship $ */
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 -- pragma autonomous_transaction;
73
74 BEGIN
75
76 select
77 SUBSTR(argument1,INSTR(argument1,'=',1)+1,LENGTH(argument1))
78 ,
79 SUBSTR(argument2,INSTR(argument2,'=',1)+1,LENGTH(argument2)),
80 SUBSTR(argument3,INSTR(argument3,'=',1)+1,LENGTH(argument3))
81 ,
82 SUBSTR(argument4,INSTR(argument4,'=',1)+1,LENGTH(argument4)),
83 SUBSTR(argument5,INSTR(argument5,'=',1)+1,LENGTH(argument5)),
84 SUBSTR(argument6,INSTR(argument6,'=',1)+1,LENGTH(argument6)),
85 SUBSTR(argument7,INSTR(argument7,'=',1)+1,LENGTH(argument7)),
86 SUBSTR(argument8,INSTR(argument8,'=',1)+1,LENGTH(argument8)),
87 SUBSTR(argument9,INSTR(argument9,'=',1)+1,LENGTH(argument9)),
88 SUBSTR(argument10,INSTR(argument10,'=',1)+1,LENGTH(argument10)),
89 SUBSTR(argument11,INSTR(argument11,'=',1)+1,LENGTH(argument11)),
90 SUBSTR(argument12,INSTR(argument12,'=',1)+1,LENGTH(argument12)),
91 SUBSTR(argument13,INSTR(argument13,'=',1)+1,LENGTH(argument13)),
92 SUBSTR(argument14,INSTR(argument14,'=',1)+1,LENGTH(argument14)),
93 SUBSTR(argument15,INSTR(argument15,'=',1)+1,LENGTH(argument15)),
94 SUBSTR(argument16,INSTR(argument16,'=',1)+1,LENGTH(argument16)),
95 SUBSTR(argument17,INSTR(argument17,'=',1)+1,LENGTH(argument17))
96 into
97 P_PAYROLL_ACTION_ID_LP
98 ,
99 P_BUSINESS_GROUP_ID_LP,
100 P_LEGAL_EMPLOYER_LP
101 ,
102 P_PERIOD_LP_1,
103 P_START_DATE_LP_1,
104 P_END_DATE_LP_1,
105 P_TAX_STATE_LP,
106 P_ACT_LP,
107 P_QLD_LP,
108 P_SA_LP,
109 P_TAS_LP,
110 P_VIC_LP ,
111 P_WA_LP ,
112 P_NSW_LP,
113 P_NT_LP,
114 P_REPORT_TYPE_LP ,
115 P_REPORT_NAME_LP
116
117 from FND_CONCURRENT_REQUESTS
118 where request_id= FND_GLOBAL.conc_request_id;
119
120 --insert into log_msg values('ATUL' , 'PYIEBIKA' ,'TEST' , P_BUSINESS_GROUP_ID_LP);
121
122 -- commit;
123 --RAISE_APPLICATION_ERROR(-20001,'P_PERIOD_LP_1 :'|| P_PERIOD_LP_1||','||'P_START_DATE_LP_1 :'|| P_START_DATE_LP_1 ||',' || 'P_END_DATE_LP_1 :'|| P_END_DATE_LP_1) ;
124 P_PERIOD_LP := TO_DATE(P_PERIOD_LP_1,'YYYY/MM/DD');
125 FINAL_DATE := TO_CHAR(P_PERIOD_LP,'MON/YYYY');
126 P_START_DATE := TO_DATE(P_START_DATE_LP_1,'YYYY/MM/DD');
127 P_END_DATE := TO_DATE(P_END_DATE_LP_1,'YYYY/MM/DD');
128
129 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
130 IF (P_LEGAL_EMPLOYER_LP IS NOT NULL) THEN
131 OPEN C_LEGAL_EMPLOYER;
132 FETCH C_LEGAL_EMPLOYER
133 INTO
134 CP_LEGAL_EMPLOYER;
135 CLOSE C_LEGAL_EMPLOYER;
136 ELSE
137 CP_LEGAL_EMPLOYER := NULL;
138 END IF;
139 IF (P_TAX_STATE_LP IS NOT NULL) THEN
140 OPEN C_STATE;
141 FETCH C_STATE
142 INTO
143 CP_TAX_STATE;
144 CLOSE C_STATE;
145 ELSE
146 CP_TAX_STATE := NULL;
147 END IF;
148 IF (P_REPORT_TYPE_LP IS NOT NULL) THEN
149 OPEN C_TYPE;
150 FETCH C_TYPE
151 INTO
152 CP_REPORT_TYPE;
153 CLOSE C_TYPE;
154 ELSE
155 CP_REPORT_TYPE := NULL;
156 END IF;
157 IF (P_LEGAL_EMPLOYER_LP IS NOT NULL AND P_TAX_STATE_LP IS NOT NULL) THEN
158 OPEN C_DGE;
159 FETCH C_DGE
160 INTO
161 CP_DGE;
162 CLOSE C_DGE;
163 ELSE
164 CP_DGE := NULL;
165 END IF;
166 RETURN (TRUE);
167 END BEFOREREPORT;
168
169 FUNCTION CF_LEGISLATION_CODEFORMULA RETURN VARCHAR2 IS
170 V_LEGISLATION_CODE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%TYPE := NULL;
171 CURSOR LEGISLATION_CODE(C_BUSINESS_GROUP_ID IN HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE) IS
172 SELECT
173 ORG_INFORMATION9
174 FROM
175 HR_ORGANIZATION_INFORMATION
176 WHERE ORGANIZATION_ID = C_BUSINESS_GROUP_ID
177 AND ORG_INFORMATION9 is not null
178 AND ORG_INFORMATION_CONTEXT = 'Business Group Information';
179 BEGIN
180 OPEN LEGISLATION_CODE(P_BUSINESS_GROUP_ID_LP);
181 FETCH LEGISLATION_CODE
182 INTO
183 V_LEGISLATION_CODE;
184 CLOSE LEGISLATION_CODE;
185 RETURN V_LEGISLATION_CODE;
186 END CF_LEGISLATION_CODEFORMULA;
187
188 FUNCTION CF_CURRENCY_FORMAT_MASKFORMULA(CF_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
189 V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
190 V_FORMAT_MASK VARCHAR2(100) := NULL;
191 V_FIELD_LENGTH NUMBER(3) := 30;
192 CURSOR CURRENCY_FORMAT_MASK(C_TERRITORY_CODE IN FND_CURRENCIES.ISSUING_TERRITORY_CODE%TYPE) IS
193 SELECT
194 CURRENCY_CODE
195 FROM
196 FND_CURRENCIES
197 WHERE ISSUING_TERRITORY_CODE = C_TERRITORY_CODE;
198 BEGIN
199 OPEN CURRENCY_FORMAT_MASK(CF_LEGISLATION_CODE);
200 FETCH CURRENCY_FORMAT_MASK
201 INTO
202 V_CURRENCY_CODE;
203 CLOSE CURRENCY_FORMAT_MASK;
204 IF (V_CURRENCY_CODE IS NOT NULL) THEN
205 CP_CURRENCY := '(' || V_CURRENCY_CODE || ')';
206 ELSE
207 CP_CURRENCY := NULL;
208 END IF;
209 V_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(V_CURRENCY_CODE
210 ,V_FIELD_LENGTH);
211 RETURN V_FORMAT_MASK;
212 END CF_CURRENCY_FORMAT_MASKFORMULA;
213
214 FUNCTION CF_STATE_TAXFORMULA(NO_OF_STATES IN NUMBER
215 ,CS_DGE_STATE IN VARCHAR2
216 ,CS_DGE_GROUP_NAME IN VARCHAR2
217 ,STATE_CODE IN VARCHAR2
218 ,CS_STATE_TAXABLE_INCOME IN NUMBER
219 ,LE_TAXABLE_INCOME IN NUMBER
220 ,CS_NO_OF_STATES IN NUMBER
221 ,CS_TOTAL_TAXABLE_INCOME_LE IN NUMBER) RETURN NUMBER IS
222 BEGIN
223 IF (P_LEGAL_EMPLOYER_LP IS NOT NULL) THEN
224 IF (P_TAX_STATE_LP IS NOT NULL) THEN
225 IF (P_TAX_STATE_LP = 'ACT') THEN
226 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
227 ,CS_DGE_STATE
228 ,CS_DGE_GROUP_NAME
229 ,STATE_CODE
230 ,CS_STATE_TAXABLE_INCOME
231 ,LE_TAXABLE_INCOME
232 ,CP_MESSAGE
233 ,CP_OT_MESSAGE
234 ,P_START_DATE
235 ,P_END_DATE
236 ,NVL(P_NSW_LP
237 ,-9999)));
238 END IF;
239 IF (P_TAX_STATE_LP = 'NSW') THEN
240 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
241 ,CS_DGE_STATE
242 ,CS_DGE_GROUP_NAME
243 ,STATE_CODE
244 ,CS_STATE_TAXABLE_INCOME
245 ,LE_TAXABLE_INCOME
246 ,CP_MESSAGE
247 ,CP_OT_MESSAGE
248 ,P_START_DATE
249 ,P_END_DATE
250 ,NVL(P_NSW_LP
251 ,-9999)));
252 END IF;
253 IF (P_TAX_STATE_LP = 'VIC') THEN
254 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
255 ,CS_DGE_STATE
256 ,CS_DGE_GROUP_NAME
257 ,STATE_CODE
258 ,CS_STATE_TAXABLE_INCOME
259 ,LE_TAXABLE_INCOME
260 ,CP_MESSAGE
261 ,CP_OT_MESSAGE
262 ,P_START_DATE
263 ,P_END_DATE
264 ,NVL(P_VIC_LP
265 ,-9999)));
266 END IF;
267 IF (P_TAX_STATE_LP = 'QLD') THEN
268 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
269 ,CS_DGE_STATE
270 ,CS_DGE_GROUP_NAME
271 ,STATE_CODE
272 ,CS_STATE_TAXABLE_INCOME
273 ,LE_TAXABLE_INCOME
274 ,CP_MESSAGE
275 ,CP_OT_MESSAGE
276 ,P_START_DATE
277 ,P_END_DATE
278 ,NVL(P_QLD_LP
279 ,-9999)));
280 END IF;
281 IF (P_TAX_STATE_LP = 'SA') THEN
282 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
283 ,CS_DGE_STATE
284 ,CS_DGE_GROUP_NAME
285 ,STATE_CODE
286 ,CS_STATE_TAXABLE_INCOME
287 ,LE_TAXABLE_INCOME
288 ,CP_MESSAGE
289 ,CP_OT_MESSAGE
290 ,P_START_DATE
291 ,P_END_DATE
292 ,NVL(P_SA_LP
293 ,-9999)));
294 END IF;
295 IF (P_TAX_STATE_LP = 'WA') THEN
296 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
297 ,CS_DGE_STATE
298 ,CS_DGE_GROUP_NAME
299 ,STATE_CODE
300 ,CS_STATE_TAXABLE_INCOME
301 ,LE_TAXABLE_INCOME
302 ,CP_MESSAGE
303 ,CP_OT_MESSAGE
304 ,P_START_DATE
305 ,P_END_DATE
306 ,NVL(P_WA_LP
307 ,-9999)));
308 END IF;
309 IF (P_TAX_STATE_LP = 'TAS') THEN
310 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
311 ,CS_DGE_STATE
312 ,CS_DGE_GROUP_NAME
313 ,STATE_CODE
314 ,CS_STATE_TAXABLE_INCOME
315 ,LE_TAXABLE_INCOME
316 ,CP_MESSAGE
317 ,CP_OT_MESSAGE
318 ,P_START_DATE
319 ,P_END_DATE
320 ,NVL(P_TAS_LP
321 ,-9999)));
322 END IF;
323 IF (P_TAX_STATE_LP = 'NT') THEN
324 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
325 ,CS_DGE_STATE
326 ,CS_DGE_GROUP_NAME
327 ,STATE_CODE
328 ,CS_STATE_TAXABLE_INCOME
329 ,LE_TAXABLE_INCOME
330 ,CP_MESSAGE
331 ,CP_OT_MESSAGE
332 ,P_START_DATE
333 ,P_END_DATE
334 ,NVL(P_NT_LP
335 ,-9999)));
336 END IF;
337 ELSE
338 IF (STATE_CODE = 'ACT') THEN
339 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
340 ,CS_DGE_STATE
341 ,CS_DGE_GROUP_NAME
342 ,STATE_CODE
343 ,CS_STATE_TAXABLE_INCOME
344 ,LE_TAXABLE_INCOME
345 ,CP_MESSAGE
346 ,CP_OT_MESSAGE
347 ,P_START_DATE
348 ,P_END_DATE
349 ,NVL(P_ACT_LP
350 ,-9999)));
351 END IF;
352 IF (STATE_CODE = 'NSW') THEN
353 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
354 ,CS_DGE_STATE
355 ,CS_DGE_GROUP_NAME
356 ,STATE_CODE
357 ,CS_STATE_TAXABLE_INCOME
358 ,LE_TAXABLE_INCOME
359 ,CP_MESSAGE
360 ,CP_OT_MESSAGE
361 ,P_START_DATE
362 ,P_END_DATE
363 ,NVL(P_NSW_LP
364 ,-9999)));
365 END IF;
366 IF (STATE_CODE = 'VIC') THEN
367 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
368 ,CS_DGE_STATE
369 ,CS_DGE_GROUP_NAME
370 ,STATE_CODE
371 ,CS_STATE_TAXABLE_INCOME
372 ,LE_TAXABLE_INCOME
373 ,CP_MESSAGE
374 ,CP_OT_MESSAGE
375 ,P_START_DATE
376 ,P_END_DATE
377 ,NVL(P_VIC_LP
378 ,-9999)));
379 END IF;
380 IF (STATE_CODE = 'QLD') THEN
381 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
382 ,CS_DGE_STATE
383 ,CS_DGE_GROUP_NAME
384 ,STATE_CODE
385 ,CS_STATE_TAXABLE_INCOME
386 ,LE_TAXABLE_INCOME
387 ,CP_MESSAGE
388 ,CP_OT_MESSAGE
389 ,P_START_DATE
390 ,P_END_DATE
391 ,NVL(P_QLD_LP
392 ,-9999)));
393 END IF;
394 IF (STATE_CODE = 'SA') THEN
395 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
396 ,CS_DGE_STATE
397 ,CS_DGE_GROUP_NAME
398 ,STATE_CODE
399 ,CS_STATE_TAXABLE_INCOME
400 ,LE_TAXABLE_INCOME
401 ,CP_MESSAGE
402 ,CP_OT_MESSAGE
403 ,P_START_DATE
404 ,P_END_DATE
405 ,NVL(P_SA_LP
406 ,-9999)));
407 END IF;
408 IF (STATE_CODE = 'WA') THEN
409 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
410 ,CS_DGE_STATE
411 ,CS_DGE_GROUP_NAME
412 ,STATE_CODE
413 ,CS_STATE_TAXABLE_INCOME
414 ,LE_TAXABLE_INCOME
415 ,CP_MESSAGE
416 ,CP_OT_MESSAGE
417 ,P_START_DATE
418 ,P_END_DATE
419 ,NVL(P_WA_LP
420 ,-9999)));
421 END IF;
422 IF (STATE_CODE = 'TAS') THEN
423 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
424 ,CS_DGE_STATE
425 ,CS_DGE_GROUP_NAME
426 ,STATE_CODE
427 ,CS_STATE_TAXABLE_INCOME
428 ,LE_TAXABLE_INCOME
429 ,CP_MESSAGE
430 ,CP_OT_MESSAGE
431 ,P_START_DATE
432 ,P_END_DATE
433 ,NVL(P_TAS_LP
434 ,-9999)));
435 END IF;
436 IF (STATE_CODE = 'NT') THEN
437 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(NO_OF_STATES
438 ,CS_DGE_STATE
439 ,CS_DGE_GROUP_NAME
440 ,STATE_CODE
441 ,CS_STATE_TAXABLE_INCOME
442 ,LE_TAXABLE_INCOME
443 ,CP_MESSAGE
444 ,CP_OT_MESSAGE
445 ,P_START_DATE
446 ,P_END_DATE
447 ,NVL(P_NT_LP
448 ,-9999)));
449 END IF;
450 END IF;
451 ELSE
452 RETURN (PAY_AU_PAYTAX_PKG.GET_TAX(CS_NO_OF_STATES
453 ,CS_DGE_STATE
454 ,CS_DGE_GROUP_NAME
455 ,STATE_CODE
456 ,CS_STATE_TAXABLE_INCOME
457 ,CS_TOTAL_TAXABLE_INCOME_LE
458 ,CP_MESSAGE
459 ,CP_OT_MESSAGE
460 ,P_START_DATE
461 ,P_END_DATE
462 ,-9999));
463 END IF;
464 END CF_STATE_TAXFORMULA;
465
466 PROCEDURE SET_FORMAT_MASK IS
467 BEGIN
468 NULL;
469 END SET_FORMAT_MASK;
470
471 FUNCTION CP_OT_MESSAGE_P RETURN VARCHAR2 IS
472 BEGIN
473 RETURN CP_OT_MESSAGE;
474 END CP_OT_MESSAGE_P;
475
476 FUNCTION CP_MESSAGE_P RETURN VARCHAR2 IS
477 BEGIN
478 RETURN CP_MESSAGE;
479 END CP_MESSAGE_P;
480
481 FUNCTION CP_CURRENCY_P RETURN VARCHAR2 IS
482 BEGIN
483 RETURN CP_CURRENCY;
484 END CP_CURRENCY_P;
485
486 FUNCTION CP_TAX_STATE_P RETURN VARCHAR2 IS
487 BEGIN
488 RETURN CP_TAX_STATE;
489 END CP_TAX_STATE_P;
490
491 FUNCTION CP_REPORT_TYPE_P RETURN VARCHAR2 IS
492 BEGIN
493 RETURN CP_REPORT_TYPE;
494 END CP_REPORT_TYPE_P;
495
496 FUNCTION CP_LEGAL_EMPLOYER_P RETURN VARCHAR2 IS
497 BEGIN
498 RETURN CP_LEGAL_EMPLOYER;
499 END CP_LEGAL_EMPLOYER_P;
500
501 FUNCTION CP_DGE_P RETURN NUMBER IS
502 BEGIN
503 RETURN CP_DGE;
504 END CP_DGE_P;
505
506 END PAY_PYAUPYT_XMLP_PKG;