1 PACKAGE BODY PSP_PSPRCDLS_XMLP_PKG AS
2 /* $Header: PSPRCDLSB.pls 120.6.12020000.4 2013/03/27 00:41:23 lkodaman ship $ */
3 FUNCTION CF_ASSIGNMENT_NUMBERFORMULA(ASSIGNMENT_ID IN NUMBER) RETURN VARCHAR2 IS
4 X_ASSIGNMENT_NUMBER VARCHAR2(30);
5 BEGIN
6 SELECT
7 ASSIGNMENT_NUMBER
8 INTO X_ASSIGNMENT_NUMBER
9 FROM
10 PER_ASSIGNMENTS_F
11 WHERE ASSIGNMENT_ID = CF_ASSIGNMENT_NUMBERFORMULA.ASSIGNMENT_ID
12 AND ASSIGNMENT_TYPE = 'E'
13 AND ROWNUM < 2;
14 RETURN (X_ASSIGNMENT_NUMBER);
15 RETURN NULL;
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 RETURN ('no_data_found');
19 WHEN TOO_MANY_ROWS THEN
20 RETURN ('too many rows');
21 WHEN OTHERS THEN
22 RETURN ('error');
23 END CF_ASSIGNMENT_NUMBERFORMULA;
24
25 FUNCTION CF_PERSON_NAMEFORMULA(PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
26 X_PERSON_NAME VARCHAR2(240);
27 X_END_DATE DATE;
28 BEGIN
29 SELECT
30 FULL_NAME
31 INTO X_PERSON_NAME
32 FROM
33 PER_PEOPLE_F
34 WHERE PERSON_ID = CF_PERSON_NAMEFORMULA.PERSON_ID
35 AND P_END_DATE BETWEEN EFFECTIVE_START_DATE
36 AND EFFECTIVE_END_DATE;
37 RETURN (X_PERSON_NAME);
38 RETURN NULL;
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 RETURN ('no data found');
42 WHEN TOO_MANY_ROWS THEN
43 RETURN ('too many rows');
44 END CF_PERSON_NAMEFORMULA;
45
46 FUNCTION AFTERPFORM RETURN BOOLEAN IS
47 BEGIN
48 -- ORIENTATION := 'LANDSCAPE';
49 SELECT
50 END_DATE
51 INTO P_END_DATE
52 FROM
53 PER_TIME_PERIODS
54 WHERE TIME_PERIOD_ID = P_TIME_PERIOD_ID;
55 RETURN (TRUE);
56 RETURN NULL;
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 /*SRW.MESSAGE(1
60 ,'End Date not found for the selected time period id')*/NULL;
61 RETURN (FALSE);
62 WHEN TOO_MANY_ROWS THEN
63 /*SRW.MESSAGE(2
67 /*SRW.MESSAGE(3
64 ,'Too many rows found for the selected time period id')*/NULL;
65 RETURN (FALSE);
66 WHEN OTHERS THEN
68 ,'Others exception raised')*/NULL;
69 RETURN (FALSE);
70 END AFTERPFORM;
71
72 FUNCTION CF_MISMATCH_ASSGFORMULA(SUM_DL_D_ASSG IN NUMBER
73 ,SUM_SL_D_ASSG IN NUMBER
74 ,SUM_DL_C_ASSG IN NUMBER
75 ,SUM_SL_C_ASSG IN NUMBER) RETURN VARCHAR2 IS
76 BEGIN
77 IF NVL(SUM_DL_D_ASSG
78 ,0) <> NVL(SUM_SL_D_ASSG
79 ,0) OR NVL(SUM_DL_C_ASSG
80 ,0) <> NVL(SUM_SL_C_ASSG
81 ,0) THEN
82 RETURN ('Mismatch');
83 END IF;
84 RETURN NULL;
85 END CF_MISMATCH_ASSGFORMULA;
86
87 FUNCTION CF_MISMATCH_PERSONFORMULA(SUM_DL_D_PERSON IN NUMBER
88 ,SUM_SL_D_PERSON IN NUMBER
89 ,SUM_DL_C_PERSON IN NUMBER
90 ,SUM_SL_C_PERSON IN NUMBER) RETURN VARCHAR2 IS
91 BEGIN
92 IF NVL(SUM_DL_D_PERSON
93 ,0) <> NVL(SUM_SL_D_PERSON
94 ,0) OR NVL(SUM_DL_C_PERSON
95 ,0) <> NVL(SUM_SL_C_PERSON
96 ,0) THEN
97 RETURN ('Mismatch');
98 END IF;
99 RETURN NULL;
100 END CF_MISMATCH_PERSONFORMULA;
101
102 FUNCTION CF_MISMATCH_REPORTFORMULA(SUM_DL_D_TOTAL IN NUMBER
103 ,SUM_SL_D_TOTAL IN NUMBER
104 ,SUM_DL_C_TOTAL IN NUMBER
105 ,SUM_SL_C_TOTAL IN NUMBER) RETURN VARCHAR2 IS
106 BEGIN
107 IF NVL(SUM_DL_D_TOTAL
108 ,0) <> NVL(SUM_SL_D_TOTAL
109 ,0) OR NVL(SUM_DL_C_TOTAL
110 ,0) <> NVL(SUM_SL_C_TOTAL
111 ,0) THEN
112 RETURN ('Mismatch');
113 END IF;
114 RETURN NULL;
115 END CF_MISMATCH_REPORTFORMULA;
116
117 FUNCTION CF_CHARGING_INSTRUCTIONSFORMUL(GL_CODE_COMBINATION_ID IN NUMBER
118 ,PROJECT_ID IN NUMBER
119 ,TASK_ID IN NUMBER
120 ,AWARD_ID IN NUMBER
121 ,EXPENDITURE_ORGANIZATION_ID IN NUMBER
122 ,EXPENDITURE_TYPE IN VARCHAR2) RETURN CHAR IS
123 V_RETCODE NUMBER;
124 L_CHART_OF_ACCTS VARCHAR2(20);
125 GL_FLEX_VALUES VARCHAR2(2000);
126 L_PROJECT_NAME VARCHAR2(30);
127 L_AWARD_NUMBER VARCHAR2(15);
128 L_TASK_NUMBER VARCHAR2(300); -- Bug : 16391366 (27/03/2013)
129 L_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
130 L_POETA VARCHAR2(360);
131 BEGIN
132 IF GL_CODE_COMBINATION_ID IS NOT NULL THEN
133 V_RETCODE := PSP_GENERAL.FIND_CHART_OF_ACCTS(TO_NUMBER(P_SET_OF_BOOKS_ID)
134 ,L_CHART_OF_ACCTS);
135 GL_FLEX_VALUES := FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME => 'SQLGL'
136 ,KEY_FLEX_CODE => 'GL#'
137 ,STRUCTURE_NUMBER => TO_NUMBER(L_CHART_OF_ACCTS)
138 ,COMBINATION_ID => GL_CODE_COMBINATION_ID);
139 RETURN (GL_FLEX_VALUES);
140 ELSE
141 IF PROJECT_ID IS NOT NULL THEN
142 SELECT
143 NAME
144 INTO L_PROJECT_NAME
145 FROM
146 PA_PROJECTS_ALL
147 WHERE PROJECT_ID = CF_CHARGING_INSTRUCTIONSFORMUL.PROJECT_ID;
148 SELECT
149 TASK_NUMBER
150 INTO L_TASK_NUMBER
151 FROM
152 PA_TASKS_EXPEND_V -- Bug : 16391366 (20/03/2013)
153 WHERE TASK_ID = CF_CHARGING_INSTRUCTIONSFORMUL.TASK_ID;
154 IF AWARD_ID IS NOT NULL THEN
155 SELECT
156 AWARD_NUMBER
157 INTO L_AWARD_NUMBER
158 FROM
159 GMS_AWARDS_ALL
160 WHERE AWARD_ID = CF_CHARGING_INSTRUCTIONSFORMUL.AWARD_ID;
161 ELSE
162 L_AWARD_NUMBER := '';
163 END IF;
164 SELECT
165 NAME
166 INTO L_ORG_NAME
167 FROM
168 HR_ALL_ORGANIZATION_UNITS
169 WHERE ORGANIZATION_ID = CF_CHARGING_INSTRUCTIONSFORMUL.EXPENDITURE_ORGANIZATION_ID;
170 L_POETA := L_PROJECT_NAME || ' ' || L_TASK_NUMBER || ' ' || L_AWARD_NUMBER || ' ' || L_ORG_NAME || ' ' || EXPENDITURE_TYPE;
171 ELSE
172 L_POETA := '';
173 END IF;
174 RETURN (L_POETA);
175 END IF;
176 END CF_CHARGING_INSTRUCTIONSFORMUL;
177
178 FUNCTION CF_ORG_REPORTFORMULA RETURN VARCHAR2 IS
179 X_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
180 X_ORG_ID VARCHAR2(15);
181 BEGIN
182 GET('PSP_ORG_REPORT'
183 ,X_ORG_ID);
184 IF X_ORG_ID IS NOT NULL THEN
185 SELECT
186 NAME
187 INTO X_ORG_NAME
188 FROM
189 HR_ORGANIZATION_UNITS
190 WHERE ORGANIZATION_ID = TO_NUMBER(X_ORG_ID);
191 RETURN (X_ORG_NAME);
192 END IF;
193 RETURN NULL;
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 RETURN ('Organization Defined in Profile Not Found');
197 END CF_ORG_REPORTFORMULA;
198
199 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
200 BEGIN
201 -- HR_STANDARD.EVENT('BEFORE REPORT');
202 RETURN (TRUE);
203 END BEFOREREPORT;
204
208 IF P_TIME_PERIOD_ID IS NOT NULL THEN
205 FUNCTION CF_TIME_PERIODFORMULA RETURN VARCHAR2 IS
206 X_TIME_PERIOD VARCHAR2(35);
207 BEGIN
209 SELECT
210 PERIOD_NAME
211 INTO X_TIME_PERIOD
212 FROM
213 PER_TIME_PERIODS
214 WHERE TIME_PERIOD_ID = P_TIME_PERIOD_ID;
215 RETURN (X_TIME_PERIOD);
216 END IF;
217 RETURN NULL;
218 END CF_TIME_PERIODFORMULA;
219
220 FUNCTION CF_SOURCE_TYPEFORMULA RETURN VARCHAR2 IS
221 X_SOURCE_TYPE VARCHAR2(80);
222 BEGIN
223 SELECT
224 MEANING
225 INTO X_SOURCE_TYPE
226 FROM
227 PSP_LOOKUPS
228 WHERE LOOKUP_TYPE = 'PSP_SOURCE_TYPE'
229 AND LOOKUP_CODE = P_SOURCE_TYPE;
230 RETURN (X_SOURCE_TYPE);
231 END CF_SOURCE_TYPEFORMULA;
232
233 FUNCTION CF_1FORMULA(CURRENCY_CODE IN VARCHAR2
234 ,PERSON_ID IN NUMBER
235 ,ASSIGNMENT_ID IN NUMBER
236 ,GL_CODE_COMBINATION_ID IN NUMBER
237 ,PROJECT_ID IN NUMBER
238 ,TASK_ID IN NUMBER
239 ,AWARD_ID IN NUMBER
240 ,EXPENDITURE_TYPE IN VARCHAR2
241 ,EXPENDITURE_ORGANIZATION_ID IN NUMBER) RETURN NUMBER IS
242 V_DEBIT_AMOUNT_DL NUMBER := 0;
243 V_SUSPENSE_CODE VARCHAR2(500) := '';
244 V_CR NUMBER := 0;
245 V_DR NUMBER := 0;
246 I PLS_INTEGER := 0;
247 CURSOR C1 IS
248 SELECT
249 SUM(DECODE(A.DR_CR_FLAG
250 ,'C'
251 ,B.DISTRIBUTION_AMOUNT
252 ,0)) DL_CREDIT_AMOUNT,
253 SUM(DECODE(A.DR_CR_FLAG
254 ,'D'
255 ,B.DISTRIBUTION_AMOUNT
256 ,0)) DL_DEBIT_AMOUNT,
257 B.SUSPENSE_REASON_CODE
258 FROM
259 PSP_SUMMARY_LINES A,
260 PSP_PAYROLL_CONTROLS PPC,
261 PSP_DISTRIBUTION_LINES_HISTORY B
262 WHERE A.SUMMARY_LINE_ID = B.SUMMARY_LINE_ID
263 AND PPC.PAYROLL_CONTROL_ID = A.PAYROLL_CONTROL_ID
264 AND PPC.CURRENCY_CODE = CF_1FORMULA.CURRENCY_CODE
265 AND A.STATUS_CODE = 'A'
266 AND B.STATUS_CODE = 'A'
267 AND A.SOURCE_TYPE = P_SOURCE_TYPE
268 AND A.SOURCE_CODE = P_SOURCE_CODE
269 AND A.TIME_PERIOD_ID = P_TIME_PERIOD_ID
270 AND ( A.INTERFACE_BATCH_NAME = P_BATCH_NAME
271 OR A.INTERFACE_BATCH_NAME IS NULL )
272 AND A.PERSON_ID = CF_1FORMULA.PERSON_ID
273 AND A.ASSIGNMENT_ID = CF_1FORMULA.ASSIGNMENT_ID
274 AND A.GL_CODE_COMBINATION_ID = CF_1FORMULA.GL_CODE_COMBINATION_ID
275 GROUP BY
276 A.DR_CR_FLAG,
277 B.SUSPENSE_REASON_CODE;
278 CURSOR C2 IS
279 SELECT
280 SUM(DECODE(A.DR_CR_FLAG
281 ,'C'
282 ,B.DISTRIBUTION_AMOUNT
283 ,0)) DL_CREDIT_AMOUNT,
284 SUM(DECODE(A.DR_CR_FLAG
285 ,'D'
286 ,B.DISTRIBUTION_AMOUNT
287 ,0)) DL_DEBIT_AMOUNT,
288 B.SUSPENSE_REASON_CODE
289 FROM
290 PSP_SUMMARY_LINES A,
291 PSP_PAYROLL_CONTROLS PPC,
292 PSP_DISTRIBUTION_LINES_HISTORY B
293 WHERE A.SUMMARY_LINE_ID = B.SUMMARY_LINE_ID
294 AND PPC.PAYROLL_CONTROL_ID = A.PAYROLL_CONTROL_ID
295 AND PPC.CURRENCY_CODE = CF_1FORMULA.CURRENCY_CODE
296 AND A.STATUS_CODE = 'A'
297 AND B.STATUS_CODE = 'A'
298 AND A.SOURCE_TYPE = P_SOURCE_TYPE
299 AND A.SOURCE_CODE = P_SOURCE_CODE
300 AND A.TIME_PERIOD_ID = P_TIME_PERIOD_ID
301 AND ( A.INTERFACE_BATCH_NAME = P_BATCH_NAME
302 OR A.INTERFACE_BATCH_NAME IS NULL )
303 AND A.PERSON_ID = CF_1FORMULA.PERSON_ID
304 AND A.ASSIGNMENT_ID = CF_1FORMULA.ASSIGNMENT_ID
305 AND A.PROJECT_ID = CF_1FORMULA.PROJECT_ID
306 AND A.TASK_ID = CF_1FORMULA.TASK_ID
307 AND ( A.AWARD_ID = CF_1FORMULA.AWARD_ID
308 OR A.AWARD_ID IS NULL
309 AND CF_1FORMULA.AWARD_ID IS NULL )
310 AND A.EXPENDITURE_TYPE = CF_1FORMULA.EXPENDITURE_TYPE
311 AND A.EXPENDITURE_ORGANIZATION_ID = CF_1FORMULA.EXPENDITURE_ORGANIZATION_ID
312 GROUP BY
313 A.DR_CR_FLAG,
314 B.SUSPENSE_REASON_CODE;
315 BEGIN
316 CP_CREDIT_AMOUNT_DL := 0;
317 CP_SUSPENSE := '';
318 IF GL_CODE_COMBINATION_ID IS NOT NULL THEN
319 OPEN C1;
320 LOOP
321 FETCH C1
322 INTO V_CR,V_DR,V_SUSPENSE_CODE;
323 EXIT WHEN C1%NOTFOUND;
324 CP_CREDIT_AMOUNT_DL := CP_CREDIT_AMOUNT_DL + V_CR;
325 V_DEBIT_AMOUNT_DL := V_DEBIT_AMOUNT_DL + V_DR;
326 IF V_SUSPENSE_CODE IS NOT NULL THEN
327 I := I + 1;
328 IF I = 1 THEN
329 CP_SUSPENSE := 'Suspense Reason: ' || V_SUSPENSE_CODE;
330 ELSE
331 CP_SUSPENSE := CP_SUSPENSE || ', ' || V_SUSPENSE_CODE;
332 END IF;
333 END IF;
334 END LOOP;
335 CLOSE C1;
336 ELSE
337 OPEN C2;
338 LOOP
339 FETCH C2
340 INTO V_CR,V_DR,V_SUSPENSE_CODE;
341 EXIT WHEN C2%NOTFOUND;
342 CP_CREDIT_AMOUNT_DL := CP_CREDIT_AMOUNT_DL + V_CR;
343 V_DEBIT_AMOUNT_DL := V_DEBIT_AMOUNT_DL + V_DR;
344 IF V_SUSPENSE_CODE IS NOT NULL THEN
345 I := I + 1;
346 IF I = 1 THEN
347 CP_SUSPENSE := 'Suspense Reason: ' || V_SUSPENSE_CODE;
348 ELSE
349 CP_SUSPENSE := CP_SUSPENSE || ', ' || V_SUSPENSE_CODE;
350 END IF;
351 END IF;
352 END LOOP;
353 CLOSE C2;
354 END IF;
355 RETURN (V_DEBIT_AMOUNT_DL);
356 RETURN NULL;
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 RETURN (NULL);
360 END CF_1FORMULA;
361
362 FUNCTION CF_AMT_SL_CFORMULA(GL_CODE_COMBINATION_ID IN NUMBER
363 ,SL_CREDIT_AMOUNT IN NUMBER) RETURN NUMBER IS
364 X_AMOUNT_SUMMARY NUMBER;
365 BEGIN
366 IF GL_CODE_COMBINATION_ID IS NULL THEN
367 X_AMOUNT_SUMMARY := 0 - SL_CREDIT_AMOUNT;
368 ELSE
369 X_AMOUNT_SUMMARY := SL_CREDIT_AMOUNT;
370 END IF;
371 RETURN (X_AMOUNT_SUMMARY);
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 RETURN (NULL);
375 END CF_AMT_SL_CFORMULA;
376
377 FUNCTION CF_CURRENCY_FORMATFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
378 BEGIN
379 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
380 RETURN (FND_CURRENCY.GET_FORMAT_MASK(CURRENCY_CODE
381 ,30));
382 END CF_CURRENCY_FORMATFORMULA;
383
384 FUNCTION CF_CURRENCY_CODEFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
385 BEGIN
386 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
387 RETURN ('(' || CURRENCY_CODE || ')');
388 END CF_CURRENCY_CODEFORMULA;
389
390 FUNCTION CF_SUM_SL_D_TOTAL_DSPFORMULA(CS_SUM_SL_D_TOTAL IN NUMBER
391 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
392 BEGIN
393 /*SRW.REFERENCE(CS_SUM_SL_D_TOTAL)*/NULL;
394 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
395 RETURN (TO_CHAR(CS_SUM_SL_D_TOTAL
396 ,CF_CURRENCY_FORMAT));
397 END CF_SUM_SL_D_TOTAL_DSPFORMULA;
398
399 FUNCTION CF_SUM_SL_C_TOTAL_DSPFORMULA(CS_SUM_SL_C_TOTAL IN NUMBER
400 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
401 BEGIN
402 /*SRW.REFERENCE(CS_SUM_SL_C_TOTAL)*/NULL;
403 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
404 RETURN (TO_CHAR(CS_SUM_SL_C_TOTAL
405 ,CF_CURRENCY_FORMAT));
406 END CF_SUM_SL_C_TOTAL_DSPFORMULA;
407
408 FUNCTION CF_SUM_DL_D_TOTAL_DSPFORMULA(CS_SUM_DL_D_TOTAL IN NUMBER
409 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
410 BEGIN
411 /*SRW.REFERENCE(CS_SUM_DL_D_TOTAL)*/NULL;
412 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
413 RETURN (TO_CHAR(CS_SUM_DL_D_TOTAL
414 ,CF_CURRENCY_FORMAT));
415 END CF_SUM_DL_D_TOTAL_DSPFORMULA;
416
417 FUNCTION CF_SUM_DL_C_TOTAL_DSPFORMULA(CS_SUM_DL_C_TOTAL IN NUMBER
418 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
419 BEGIN
420 /*SRW.REFERENCE(CS_SUM_DL_C_TOTAL)*/NULL;
421 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
422 RETURN (TO_CHAR(CS_SUM_DL_C_TOTAL
423 ,CF_CURRENCY_FORMAT));
424 END CF_SUM_DL_C_TOTAL_DSPFORMULA;
425
426 FUNCTION CF_MISMATCH_TOTAL_DSPFORMULA(CS_SUM_DL_D_TOTAL IN NUMBER
427 ,CS_SUM_SL_D_TOTAL IN NUMBER
428 ,CS_SUM_DL_C_TOTAL IN NUMBER
429 ,CS_SUM_SL_C_TOTAL IN NUMBER) RETURN CHAR IS
430 BEGIN
431 IF NVL(CS_SUM_DL_D_TOTAL
432 ,0) <> NVL(CS_SUM_SL_D_TOTAL
433 ,0) OR NVL(CS_SUM_DL_C_TOTAL
434 ,0) <> NVL(CS_SUM_SL_C_TOTAL
435 ,0) THEN
436 RETURN ('Mismatch');
437 END IF;
438 RETURN NULL;
439 END CF_MISMATCH_TOTAL_DSPFORMULA;
440
441 FUNCTION CF_SUM_SL_D_PERSON_DSPFORMULA(SUM_SL_D_PERSON IN NUMBER
442 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
443 BEGIN
444 /*SRW.REFERENCE(SUM_SL_D_PERSON)*/NULL;
445 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
446 RETURN (TO_CHAR(SUM_SL_D_PERSON
447 ,CF_CURRENCY_FORMAT));
448 END CF_SUM_SL_D_PERSON_DSPFORMULA;
449
450 FUNCTION CF_SUM_SL_C_PERSON_DSPFORMULA(SUM_SL_C_PERSON IN NUMBER
451 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
452 BEGIN
453 /*SRW.REFERENCE(SUM_SL_C_PERSON)*/NULL;
454 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
455 RETURN (TO_CHAR(SUM_SL_C_PERSON
456 ,CF_CURRENCY_FORMAT));
457 END CF_SUM_SL_C_PERSON_DSPFORMULA;
458
459 FUNCTION CF_SUM_DL_D_PERSON_DSPFORMULA(SUM_DL_D_PERSON IN NUMBER
460 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
461 BEGIN
462 /*SRW.REFERENCE(SUM_DL_D_PERSON)*/NULL;
463 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
464 RETURN (TO_CHAR(SUM_DL_D_PERSON
465 ,CF_CURRENCY_FORMAT));
466 END CF_SUM_DL_D_PERSON_DSPFORMULA;
467
468 FUNCTION CF_SUM_DL_C_PERSON_DSPFORMULA(SUM_DL_C_PERSON IN NUMBER
469 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
470 BEGIN
471 /*SRW.REFERENCE(SUM_DL_C_PERSON)*/NULL;
472 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
473 RETURN (TO_CHAR(SUM_DL_C_PERSON
474 ,CF_CURRENCY_FORMAT));
475 END CF_SUM_DL_C_PERSON_DSPFORMULA;
476
477 FUNCTION CF_SUM_SL_D_ASSG_DSPFORMULA(SUM_SL_D_ASSG IN NUMBER
478 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
479 BEGIN
480 /*SRW.REFERENCE(SUM_SL_D_ASSG)*/NULL;
481 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
482 RETURN (TO_CHAR(SUM_SL_D_ASSG
483 ,CF_CURRENCY_FORMAT));
484 END CF_SUM_SL_D_ASSG_DSPFORMULA;
485
486 FUNCTION CF_SUM_SL_C_ASSG_DSPFORMULA(SUM_SL_C_ASSG IN NUMBER
487 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
488 BEGIN
489 /*SRW.REFERENCE(SUM_SL_C_ASSG)*/NULL;
490 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
491 RETURN (TO_CHAR(SUM_SL_C_ASSG
492 ,CF_CURRENCY_FORMAT));
493 END CF_SUM_SL_C_ASSG_DSPFORMULA;
494
495 FUNCTION CF_SUM_DL_D_ASSG_DSPFORMULA(SUM_DL_D_ASSG IN NUMBER
496 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
497 BEGIN
498 /*SRW.REFERENCE(SUM_DL_D_ASSG)*/NULL;
499 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
500 RETURN (TO_CHAR(SUM_DL_D_ASSG
501 ,CF_CURRENCY_FORMAT));
502 END CF_SUM_DL_D_ASSG_DSPFORMULA;
503
504 FUNCTION CF_SUM_DL_C_ASSG_DSPFORMULA(SUM_DL_C_ASSG IN NUMBER
505 ,CF_CURRENCY_FORMAT IN VARCHAR2
506 ,SUM_SL_C_ASSG IN NUMBER) RETURN CHAR IS
507 BEGIN
508 /*SRW.REFERENCE(SUM_DL_C_ASSG)*/NULL;
509 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
510 RETURN (TO_CHAR(SUM_SL_C_ASSG
511 ,CF_CURRENCY_FORMAT));
512 END CF_SUM_DL_C_ASSG_DSPFORMULA;
513
514 FUNCTION CF_SL_DEBIT_AMOUNT_DSPFORMULA(SL_DEBIT_AMOUNT IN NUMBER
515 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
516 BEGIN
517 /*SRW.REFERENCE(SL_DEBIT_AMOUNT)*/NULL;
518 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
519 RETURN (TO_CHAR(SL_DEBIT_AMOUNT
520 ,CF_CURRENCY_FORMAT));
521 END CF_SL_DEBIT_AMOUNT_DSPFORMULA;
522
523 FUNCTION CF_AMT_SL_C_DSPFORMULA(CF_AMT_SL_C IN NUMBER
524 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
525 BEGIN
526 /*SRW.REFERENCE(CF_AMT_SL_C)*/NULL;
527 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
528 RETURN (TO_CHAR(CF_AMT_SL_C
529 ,CF_CURRENCY_FORMAT));
530 END CF_AMT_SL_C_DSPFORMULA;
531
532 FUNCTION CF_CREDIT_AMOUNT_DL_DSPFORMULA(CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
533 BEGIN
534 /*SRW.REFERENCE(CP_CREDIT_AMOUNT_DL)*/NULL;
535 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
536 RETURN (TO_CHAR(CP_CREDIT_AMOUNT_DL
537 ,CF_CURRENCY_FORMAT));
538 END CF_CREDIT_AMOUNT_DL_DSPFORMULA;
539
540 FUNCTION CF_1_DSPFORMULA(CF_1 IN NUMBER
541 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
542 BEGIN
543 /*SRW.REFERENCE(CF_1)*/NULL;
544 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
545 RETURN (TO_CHAR(CF_1
546 ,CF_CURRENCY_FORMAT));
547 END CF_1_DSPFORMULA;
548
549 FUNCTION CF_MISMATCH_LINEFORMULA(CF_1 IN NUMBER
550 ,SL_DEBIT_AMOUNT IN NUMBER
551 ,CF_AMT_SL_C IN NUMBER) RETURN CHAR IS
552 BEGIN
553 IF NVL(CF_1
554 ,0) <> NVL(SL_DEBIT_AMOUNT
555 ,0) OR NVL(CP_CREDIT_AMOUNT_DL
556 ,0) <> NVL(CF_AMT_SL_C
557 ,0) THEN
558 RETURN ('Mismatch');
559 END IF;
560 RETURN NULL;
561 END CF_MISMATCH_LINEFORMULA;
562
563 FUNCTION AFTERREPORT RETURN BOOLEAN IS
564 BEGIN
565 -- HR_STANDARD.EVENT('AFTER REPORT');
566 RETURN (TRUE);
567 END AFTERREPORT;
568
569 FUNCTION CP_CREDIT_AMOUNT_DL_P RETURN NUMBER IS
570 BEGIN
571 RETURN CP_CREDIT_AMOUNT_DL;
572 END CP_CREDIT_AMOUNT_DL_P;
573
574 FUNCTION CP_SUSPENSE_P RETURN VARCHAR2 IS
575 BEGIN
576 RETURN CP_SUSPENSE;
577 END CP_SUSPENSE_P;
578
579 PROCEDURE GET_ANNUAL_SALARY(P_ASSIGNMENT_ID IN NUMBER
580 ,P_SESSION_DATE IN DATE
581 ,P_ANNUAL_SALARY OUT NOCOPY NUMBER) IS
582 BEGIN
583 /* STPROC.INIT('begin PSP_GENERAL.GET_ANNUAL_SALARY(:P_ASSIGNMENT_ID, :P_SESSION_DATE, :P_ANNUAL_SALARY); end;');
584 STPROC.BIND_I(P_ASSIGNMENT_ID);
585 STPROC.BIND_I(P_SESSION_DATE);
586 STPROC.BIND_O(P_ANNUAL_SALARY);
587 STPROC.EXECUTE;
588 STPROC.RETRIEVE(3
589 ,P_ANNUAL_SALARY);*/null;
590 END GET_ANNUAL_SALARY;
591
592 PROCEDURE GET_GL_CCID(P_PAYROLL_ID IN NUMBER
593 ,P_SET_OF_BOOKS_ID IN NUMBER
594 ,P_COST_KEYFLEX_ID IN NUMBER
595 ,X_GL_CCID OUT NOCOPY NUMBER) IS
596 BEGIN
597 /* STPROC.INIT('begin PSP_GENERAL.GET_GL_CCID(:P_PAYROLL_ID, :P_SET_OF_BOOKS_ID, :P_COST_KEYFLEX_ID, :X_GL_CCID); end;');
598 STPROC.BIND_I(P_PAYROLL_ID);
599 STPROC.BIND_I(P_SET_OF_BOOKS_ID);
600 STPROC.BIND_I(P_COST_KEYFLEX_ID);
601 STPROC.BIND_O(X_GL_CCID);
602 STPROC.EXECUTE;
603 STPROC.RETRIEVE(4
604 ,X_GL_CCID);*/null;
605 END GET_GL_CCID;
606
607 FUNCTION BUSINESS_DAYS(LOW_DATE IN DATE
608 ,HIGH_DATE IN DATE) RETURN NUMBER IS
609 X0 NUMBER;
610 BEGIN
611 /* STPROC.INIT('begin :X0 := PSP_GENERAL.BUSINESS_DAYS(:LOW_DATE, :HIGH_DATE); end;');
612 STPROC.BIND_O(X0);
613 STPROC.BIND_I(LOW_DATE);
614 STPROC.BIND_I(HIGH_DATE);
615 STPROC.EXECUTE;
616 STPROC.RETRIEVE(1
617 ,X0);*/
618 RETURN X0;
619 END BUSINESS_DAYS;
620
621 FUNCTION GET_GL_DESCRIPTION(A_CODE_COMBINATION_ID IN NUMBER) RETURN VARCHAR2 IS
622 X0 VARCHAR2(2000);
623 BEGIN
624 /* STPROC.INIT('begin :X0 := PSP_GENERAL.GET_GL_DESCRIPTION(:A_CODE_COMBINATION_ID); end;');
625 STPROC.BIND_O(X0);
626 STPROC.BIND_I(A_CODE_COMBINATION_ID);
627 STPROC.EXECUTE;
628 STPROC.RETRIEVE(1
629 ,X0);*/
630 RETURN X0;
631 END GET_GL_DESCRIPTION;
632
633 PROCEDURE PUT(NAME IN VARCHAR2
634 ,VAL IN VARCHAR2) IS
635 BEGIN
636 /* STPROC.INIT('begin FND_PROFILE.PUT(:NAME, :VAL); end;');
637 STPROC.BIND_I(NAME);
638 STPROC.BIND_I(VAL);
639 STPROC.EXECUTE;*/null;
640 END PUT;
641
642 FUNCTION DEFINED(NAME IN VARCHAR2) RETURN BOOLEAN IS
643 X0 BOOLEAN;
644 BEGIN
645 /* STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.DEFINED(:NAME); :X0 := sys.diutil.bool_to_int(X0rv); end;');
646 STPROC.BIND_I(NAME);
647 STPROC.BIND_O(X0);
648 STPROC.EXECUTE;
649 STPROC.RETRIEVE(2
650 ,X0);*/null;
651 RETURN X0;
652 END DEFINED;
653
654 PROCEDURE GET(NAME IN VARCHAR2
655 ,VAL OUT NOCOPY VARCHAR2) IS
656 BEGIN
657 /* STPROC.INIT('begin FND_PROFILE.GET(:NAME, :VAL); end;');
658 STPROC.BIND_I(NAME);
659 STPROC.BIND_O(VAL);
660 STPROC.EXECUTE;
661 STPROC.RETRIEVE(2
662 ,VAL);*/
663
664 FND_PROFILE.GET(NAME, VAL);
665 END GET;
666
667 FUNCTION VALUE(NAME IN VARCHAR2) RETURN VARCHAR2 IS
668 X0 VARCHAR2(2000);
669 BEGIN
670 /* STPROC.INIT('begin :X0 := FND_PROFILE.VALUE(:NAME); end;');
671 STPROC.BIND_O(X0);
672 STPROC.BIND_I(NAME);
673 STPROC.EXECUTE;
674 STPROC.RETRIEVE(1
675 ,X0);*/
676 RETURN X0;
677 END VALUE;
678
679 FUNCTION SAVE_USER(X_NAME IN VARCHAR2
680 ,X_VALUE IN VARCHAR2) RETURN BOOLEAN IS
681 X0 BOOLEAN;
682 BEGIN
683 /* STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE_USER(:X_NAME, :X_VALUE); :X0 := sys.diutil.bool_to_int(X0rv); end;');
684 STPROC.BIND_I(X_NAME);
685 STPROC.BIND_I(X_VALUE);
686 STPROC.BIND_O(X0);
687 STPROC.EXECUTE;
688 STPROC.RETRIEVE(3
689 ,X0);*/
690 RETURN X0;
691 END SAVE_USER;
692
693 FUNCTION SAVE(X_NAME IN VARCHAR2
694 ,X_VALUE IN VARCHAR2
695 ,X_LEVEL_NAME IN VARCHAR2
696 ,X_LEVEL_VALUE IN VARCHAR2
697 ,X_LEVEL_VALUE_APP_ID IN VARCHAR2) RETURN BOOLEAN IS
698 X0 BOOLEAN;
699 BEGIN
700 /* STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE(:X_NAME, :X_VALUE, :X_LEVEL_NAME, :X_LEVEL_VALUE, :X_LEVEL_VALUE_APP_ID); :X0 := sys.diutil.bool_to_int(X0rv); end;');
701 STPROC.BIND_I(X_NAME);
702 STPROC.BIND_I(X_VALUE);
703 STPROC.BIND_I(X_LEVEL_NAME);
704 STPROC.BIND_I(X_LEVEL_VALUE);
705 STPROC.BIND_I(X_LEVEL_VALUE_APP_ID);
706 STPROC.BIND_O(X0);
707 STPROC.EXECUTE;
708 STPROC.RETRIEVE(6
709 ,X0);*/
710 RETURN X0;
711 END SAVE;
712
713 PROCEDURE GET_SPECIFIC(NAME_Z IN VARCHAR2
714 ,USER_ID_Z IN NUMBER
715 ,RESPONSIBILITY_ID_Z IN NUMBER
716 ,APPLICATION_ID_Z IN NUMBER
717 ,VAL_Z OUT NOCOPY VARCHAR2
718 ,DEFINED_Z OUT NOCOPY BOOLEAN) IS
719 BEGIN
720 /* STPROC.INIT('declare DEFINED_Z BOOLEAN; begin
721 DEFINED_Z := sys.diutil.int_to_bool(:DEFINED_Z);
722 FND_PROFILE.GET_SPECIFIC(:NAME_Z, :USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :VAL_Z, DEFINED_Z);
723 :DEFINED_Z := sys.diutil.bool_to_int(DEFINED_Z); end;');
724 STPROC.BIND_O(DEFINED_Z);
725 STPROC.BIND_I(NAME_Z);
726 STPROC.BIND_I(USER_ID_Z);
727 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
728 STPROC.BIND_I(APPLICATION_ID_Z);
729 STPROC.BIND_O(VAL_Z);
730 STPROC.EXECUTE;
731 STPROC.RETRIEVE(1
732 ,DEFINED_Z);
733 STPROC.RETRIEVE(6
734 ,VAL_Z);*/null;
735 END GET_SPECIFIC;
736
737 FUNCTION VALUE_SPECIFIC(NAME IN VARCHAR2
738 ,USER_ID IN NUMBER
739 ,RESPONSIBILITY_ID IN NUMBER
740 ,APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
741 X0 VARCHAR2(2000);
742 BEGIN
743 /* STPROC.INIT('begin :X0 := FND_PROFILE.VALUE_SPECIFIC(:NAME, :USER_ID, :RESPONSIBILITY_ID, :APPLICATION_ID); end;');
744 STPROC.BIND_O(X0);
745 STPROC.BIND_I(NAME);
746 STPROC.BIND_I(USER_ID);
747 STPROC.BIND_I(RESPONSIBILITY_ID);
748 STPROC.BIND_I(APPLICATION_ID);
749 STPROC.EXECUTE;
750 STPROC.RETRIEVE(1
751 ,X0);*/
752 RETURN X0;
753 END VALUE_SPECIFIC;
754
755 PROCEDURE INITIALIZE(USER_ID_Z IN NUMBER
756 ,RESPONSIBILITY_ID_Z IN NUMBER
757 ,APPLICATION_ID_Z IN NUMBER
758 ,SITE_ID_Z IN NUMBER) IS
759 BEGIN
760 /*STPROC.INIT('begin FND_PROFILE.INITIALIZE(:USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :SITE_ID_Z); end;');
761 STPROC.BIND_I(USER_ID_Z);
762 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
763 STPROC.BIND_I(APPLICATION_ID_Z);
764 STPROC.BIND_I(SITE_ID_Z);
765 STPROC.EXECUTE;*/null;
766 END INITIALIZE;
767
768 PROCEDURE PUTMULTIPLE(NAMES IN VARCHAR2
769 ,VALS IN VARCHAR2
770 ,NUM IN NUMBER) IS
771 BEGIN
772 /* STPROC.INIT('begin FND_PROFILE.PUTMULTIPLE(:NAMES, :VALS, :NUM); end;');
773 STPROC.BIND_I(NAMES);
774 STPROC.BIND_I(VALS);
775 STPROC.BIND_I(NUM);
776 STPROC.EXECUTE;*/null;
777 END PUTMULTIPLE;
778
779 END PSP_PSPRCDLS_XMLP_PKG;
[Home] [Help]