eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PSP_PSPLDAWD_XMLP_PKG

Source


1 PACKAGE BODY PSP_PSPLDAWD_XMLP_PKG AS
2 /* $Header: PSPLDAWDB.pls 120.4.12020000.4 2013/03/27 00:37:02 lkodaman ship $ */
3   FUNCTION AFTERPFORM RETURN BOOLEAN IS
4     CURSOR C1(P_LOOKUP_CODE IN VARCHAR2,P_TEMPLATE_ID IN NUMBER) IS
5       SELECT
6         COUNT(1)
7       FROM
8         PSP_REPORT_TEMPLATE_DETAILS
9       WHERE TEMPLATE_ID = P_TEMPLATE_ID
10         AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
11         AND CRITERIA_LOOKUP_CODE = P_LOOKUP_CODE;
12     L_NUM NUMBER;
13     L_NUM2 NUMBER;
14   BEGIN
15     IF P_AWD_TEMPLATE_ID IS NULL THEN
16       P_AWARD_ID := ' and 1 = 1 ';
17     ELSE
18       OPEN C1('AWD',P_AWD_TEMPLATE_ID);
19       FETCH C1
20        INTO L_NUM;
21       CLOSE C1;
22       IF L_NUM <> 0 THEN
23         P_AWARD_ID := ' and a.award_id  IN (select criteria_value1 from psp_report_template_details
24                                where template_id = ' || P_AWD_TEMPLATE_ID || '
25                                and   criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
26                                and   criteria_lookup_code = ''AWD'' ' || ' ) ';
27       ELSE
28         P_AWARD_ID := ' and 1 = 1 ';
29       END IF;
30     END IF;
31     RETURN (TRUE);
32   END AFTERPFORM;
33 
34   FUNCTION BEFOREPFORM(ORIENTATION IN VARCHAR2) RETURN BOOLEAN IS
35   BEGIN
36     --ORIENTATION := 'LANDSCAPE';
37     RETURN (TRUE);
38   END BEFOREPFORM;
39 
40   FUNCTION CF_EMPLOYEE_NUMBERFORMULA RETURN VARCHAR2 IS
41     V_EMPLOYEE_NUMBER VARCHAR2(30);
42   BEGIN
43     RETURN NULL;
44     RETURN NULL;
45   EXCEPTION
46     WHEN OTHERS THEN
47       RETURN 'ERROR';
48   END CF_EMPLOYEE_NUMBERFORMULA;
49 
50   FUNCTION CF_INSTITUTION_NAMEFORMULA RETURN VARCHAR2 IS
51     V_INSTITUTION_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
52     V_INSTITUTION_ID VARCHAR2(30);
53   BEGIN
54     V_INSTITUTION_ID := VALUE('PSP_ORG_REPORT');
55     IF V_INSTITUTION_ID IS NULL THEN
56       RETURN NULL;
57     END IF;
58     SELECT
59       DISTINCT
60       NAME
61     INTO V_INSTITUTION_NAME
62     FROM
63       HR_ORGANIZATION_UNITS
64     WHERE ORGANIZATION_ID = TO_NUMBER(V_INSTITUTION_ID);
65     RETURN V_INSTITUTION_NAME;
66     RETURN NULL;
67   EXCEPTION
68     WHEN OTHERS THEN
69       RETURN NULL;
70   END CF_INSTITUTION_NAMEFORMULA;
71 
72   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
73   BEGIN
74     --HR_STANDARD.EVENT('BEFORE REPORT');
75     RETURN (TRUE);
76   END BEFOREREPORT;
77 
78   FUNCTION CF_CURRENCY_FORMATFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
79   BEGIN
80     /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
81     RETURN (FND_CURRENCY.GET_FORMAT_MASK(CURRENCY_CODE
82                                        ,30));
83   END CF_CURRENCY_FORMATFORMULA;
84 
85   FUNCTION CF_SUM_CURRENCY_CODEFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
86   BEGIN
87     /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
88     RETURN ('(' || CURRENCY_CODE || ')');
89   END CF_SUM_CURRENCY_CODEFORMULA;
90 
91   FUNCTION CF_CURR_AWD_AMT_TOTALFORMULA(CF_CURRENCY_FORMAT IN VARCHAR2
92                                        ,CS_CURR_AWD_AMT_TOTAL IN NUMBER) RETURN CHAR IS
93   BEGIN
94     /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
95     /*SRW.REFERENCE(CS_CURR_AWD_AMT_TOTAL)*/NULL;
96     RETURN (TO_CHAR(CS_CURR_AWD_AMT_TOTAL
97                   ,CF_CURRENCY_FORMAT));
98   END CF_CURR_AWD_AMT_TOTALFORMULA;
99 
100   FUNCTION CF_EARNINGS_AMOUNTFORMULA(CF_CURRENCY_FORMAT IN VARCHAR2
101                                     ,EARNINGS_AMOUNT IN NUMBER) RETURN CHAR IS
102   BEGIN
103     /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
104     /*SRW.REFERENCE(EARNINGS_AMOUNT)*/NULL;
105     RETURN (TO_CHAR(EARNINGS_AMOUNT
106                   ,CF_CURRENCY_FORMAT));
107   END CF_EARNINGS_AMOUNTFORMULA;
108 
109   FUNCTION CF_AWARD_NAMEFORMULA(AWARD_ID IN NUMBER) RETURN VARCHAR2 IS
110     V_AWARD_NAME VARCHAR2(50);
111   BEGIN
112     SELECT
113       AWARD_NUMBER
114     INTO V_AWARD_NAME
115     FROM
116       GMS_AWARDS_ALL
117     WHERE AWARD_ID = CF_AWARD_NAMEFORMULA.AWARD_ID;
118     RETURN V_AWARD_NAME;
119     RETURN NULL;
120   EXCEPTION
121     WHEN OTHERS THEN
122       RETURN 'ERROR';
123   END CF_AWARD_NAMEFORMULA;
124 
125   FUNCTION CF_EMPLOYEE_NAMEFORMULA(PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
126     V_PERSON_NAME VARCHAR2(240);
127   BEGIN
128     SELECT
129       FULL_NAME
130     INTO V_PERSON_NAME
131     FROM
132       PER_ALL_PEOPLE_F
133     WHERE PERSON_ID = CF_EMPLOYEE_NAMEFORMULA.PERSON_ID
134       AND ( P_END_DATE Between EFFECTIVE_START_DATE
135       AND EFFECTIVE_END_DATE );
136     RETURN V_PERSON_NAME;
137     RETURN NULL;
138   EXCEPTION
139     WHEN NO_DATA_FOUND THEN
140       RETURN 'No_data_Found';
141     WHEN OTHERS THEN
142       RETURN 'ERROR';
143   END CF_EMPLOYEE_NAMEFORMULA;
144 
145   FUNCTION CF_ASSIGNMENT_NUMBERFORMULA(ASSIGNMENT_ID IN NUMBER
146                                       ,PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
147     V_ASSIGNMENT_NUMBER VARCHAR2(50);
148   BEGIN
149     SELECT
150       ASSIGNMENT_NUMBER
151     INTO V_ASSIGNMENT_NUMBER
152     FROM
153       PER_ASSIGNMENTS_F
154     WHERE ASSIGNMENT_ID = CF_ASSIGNMENT_NUMBERFORMULA.ASSIGNMENT_ID
155       AND PERSON_ID = CF_ASSIGNMENT_NUMBERFORMULA.PERSON_ID
156       AND ( P_END_DATE BETWEEN EFFECTIVE_START_DATE
157       AND EFFECTIVE_END_DATE )
158       AND PERIOD_OF_SERVICE_ID IS NOT NULL;
159     RETURN V_ASSIGNMENT_NUMBER;
160     RETURN NULL;
161   EXCEPTION
162     WHEN OTHERS THEN
163       RETURN 'ERROR';
164   END CF_ASSIGNMENT_NUMBERFORMULA;
165 
166   FUNCTION CF_ELEMENT_NAMEFORMULA(ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
167     V_ELEMENT_NAME VARCHAR2(80);
168   BEGIN
169     SELECT
170       ELEMENT_NAME
171     INTO V_ELEMENT_NAME
172     FROM
173       PAY_ELEMENT_TYPES_F
174     WHERE ELEMENT_TYPE_ID = CF_ELEMENT_NAMEFORMULA.ELEMENT_TYPE_ID
175       AND ( P_END_DATE Between EFFECTIVE_START_DATE
176       AND EFFECTIVE_END_DATE );
177     RETURN V_ELEMENT_NAME;
178     RETURN NULL;
179   EXCEPTION
180     WHEN OTHERS THEN
181       RETURN 'ERROR';
182   END CF_ELEMENT_NAMEFORMULA;
183 
184   FUNCTION CF_TIME_PERIOD_NAMEFORMULA(TIME_PERIOD_ID IN NUMBER) RETURN VARCHAR2 IS
185     V_PERIOD_NAME VARCHAR2(35);
186   BEGIN
187     SELECT
188       DISTINCT
189       PERIOD_NAME
190     INTO V_PERIOD_NAME
191     FROM
192       PER_TIME_PERIODS
193     WHERE TIME_PERIOD_ID = CF_TIME_PERIOD_NAMEFORMULA.TIME_PERIOD_ID;
194     RETURN V_PERIOD_NAME;
195     RETURN NULL;
196   EXCEPTION
197     WHEN OTHERS THEN
198       RETURN 'ERROR';
199   END CF_TIME_PERIOD_NAMEFORMULA;
200 
201   FUNCTION AFTERREPORT RETURN BOOLEAN IS
202   BEGIN
203     --HR_STANDARD.EVENT('AFTER REPORT');
204     RETURN (TRUE);
205   END AFTERREPORT;
206 
207   FUNCTION CF_DATE_FORMATFORMULA RETURN CHAR IS
208   BEGIN
209     RETURN (VALUE('ICX_DATE_FORMAT_MASK'));
210   END CF_DATE_FORMATFORMULA;
211 
212   FUNCTION CF_START_DATE_DISPLAYFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
213   BEGIN
214     /*SRW.REFERENCE(P_START_DATE)*/NULL;
215     /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
216     RETURN (TO_CHAR(P_START_DATE
217                   ,CF_DATE_FORMAT));
218   END CF_START_DATE_DISPLAYFORMULA;
219 
220   FUNCTION CF_END_DATE_DISPFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
221   BEGIN
222     /*SRW.REFERENCE(P_END_DATE)*/NULL;
223     /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
224     RETURN (TO_CHAR(P_END_DATE
225                   ,CF_DATE_FORMAT));
226   END CF_END_DATE_DISPFORMULA;
227 
228   FUNCTION CF_CHARGING_INSTRUCTIONSFORMUL(EXPENDITURE_ORGANIZATION_ID IN NUMBER
229                                          ,TASK_ID IN NUMBER
230                                          ,PROJECT_ID IN NUMBER
231                                          ,AWARD_ID IN VARCHAR2
232                                          ,EXPENDITURE_TYPE IN VARCHAR2) RETURN CHAR IS
233     L_PROJECT_NUMBER PA_PROJECTS_ALL.SEGMENT1%TYPE;
234     L_TASK_NUMBER VARCHAR2(300); -- PA_TASKS.TASK_NUMBER%TYPE; -- Bug :16391366  (27/03/2013)
235     L_EXP_ORG HR_ORGANIZATION_UNITS.NAME%TYPE;
236     V_AWARD_NAME VARCHAR2(50);
237   BEGIN
238    SELECT
239       AWARD_NUMBER
240     INTO V_AWARD_NAME
241     FROM
242       GMS_AWARDS_ALL
243     WHERE AWARD_ID = CF_CHARGING_INSTRUCTIONSFORMUL.AWARD_ID;
244     IF EXPENDITURE_ORGANIZATION_ID IS NOT NULL THEN
245       SELECT
246         NAME
247       INTO L_EXP_ORG
248       FROM
249         HR_ALL_ORGANIZATION_UNITS
250       WHERE ORGANIZATION_ID = EXPENDITURE_ORGANIZATION_ID
251         AND ROWNUM = 1;
252     ELSE
253       L_EXP_ORG := '';
254     END IF;
255     IF TASK_ID IS NOT NULL THEN
256       SELECT
257         TASK_NUMBER
258       INTO L_TASK_NUMBER
259       FROM
260         PA_TASKS_EXPEND_V   -- Bug : 16391366  (20/03/2013)
261       WHERE TASK_ID = cf_charging_instructionsformul.TASK_ID;
262     ELSE
263       L_TASK_NUMBER := '';
264     END IF;
265     IF PROJECT_ID IS NOT NULL THEN
266       SELECT
267         SEGMENT1
268       INTO L_PROJECT_NUMBER
269       FROM
270         PA_PROJECTS_ALL
271       WHERE PROJECT_ID = cf_charging_instructionsformul.PROJECT_ID;
272     ELSE
273       L_PROJECT_NUMBER := '';
274     END IF;
275     RETURN (L_PROJECT_NUMBER || '-' || L_TASK_NUMBER || '-' || V_AWARD_NAME || '-' || EXPENDITURE_TYPE || '-' || L_EXP_ORG);
276   EXCEPTION
277     WHEN OTHERS THEN
278       RETURN 'ERROR';
279   END CF_CHARGING_INSTRUCTIONSFORMUL;
280 
281   FUNCTION CF_DATE_RUNFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
282   BEGIN
283     /*SRW.REFERENCE(SYSDATE)*/NULL;
284     /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
285     RETURN (TO_CHAR(SYSDATE
286                   ,CF_DATE_FORMAT));
287   END CF_DATE_RUNFORMULA;
288 
289   PROCEDURE PUT(NAME IN VARCHAR2
290                ,VAL IN VARCHAR2) IS
291   BEGIN
292     /*STPROC.INIT('begin FND_PROFILE.PUT(:NAME, :VAL); end;');
293     STPROC.BIND_I(NAME);
294     STPROC.BIND_I(VAL);
295     STPROC.EXECUTE;*/
296     null;
297   END PUT;
298 
299   FUNCTION DEFINED(NAME IN VARCHAR2) RETURN BOOLEAN IS
300     X0 BOOLEAN;
301   BEGIN
302     /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.DEFINED(:NAME); :X0 := sys.diutil.bool_to_int(X0rv); end;');
303     STPROC.BIND_I(NAME);
304     STPROC.BIND_O(X0);
305     STPROC.EXECUTE;
306     STPROC.RETRIEVE(2
307                    ,X0);*/
308     RETURN X0;
309   END DEFINED;
310 
311   PROCEDURE GET(NAME IN VARCHAR2
312                ,VAL OUT NOCOPY VARCHAR2) IS
313   BEGIN
314   /*
315     STPROC.INIT('begin FND_PROFILE.GET(:NAME, :VAL); end;');
316     STPROC.BIND_I(NAME);
317     STPROC.BIND_O(VAL);
318     STPROC.EXECUTE;
319     STPROC.RETRIEVE(2
320                    ,VAL);*/
321 		   null;
322   END GET;
323 
324   FUNCTION VALUE(NAME IN VARCHAR2) RETURN VARCHAR2 IS
325     X0 VARCHAR2(2000);
326   BEGIN
327     --STPROC.INIT('begin :X0 := FND_PROFILE.VALUE(:NAME); end;');
328 	begin
329 	X0 := FND_PROFILE.VALUE(NAME);
330 	end;
331    /* STPROC.BIND_O(X0);
332     STPROC.BIND_I(NAME);
333     STPROC.EXECUTE;
334     STPROC.RETRIEVE(1
335                    ,X0);*/
336     RETURN X0;
337   END VALUE;
338 
339   FUNCTION SAVE_USER(X_NAME IN VARCHAR2
340                     ,X_VALUE IN VARCHAR2) RETURN BOOLEAN IS
341     X0 BOOLEAN;
342   BEGIN
343   /*
344     STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE_USER(:X_NAME, :X_VALUE); :X0 := sys.diutil.bool_to_int(X0rv); end;');
345     STPROC.BIND_I(X_NAME);
346     STPROC.BIND_I(X_VALUE);
347     STPROC.BIND_O(X0);
348     STPROC.EXECUTE;
349     STPROC.RETRIEVE(3
350                    ,X0);*/
351     RETURN X0;
352   END SAVE_USER;
353 
354   FUNCTION SAVE(X_NAME IN VARCHAR2
355                ,X_VALUE IN VARCHAR2
356                ,X_LEVEL_NAME IN VARCHAR2
357                ,X_LEVEL_VALUE IN VARCHAR2
358                ,X_LEVEL_VALUE_APP_ID IN VARCHAR2) RETURN BOOLEAN IS
359     X0 BOOLEAN;
360   BEGIN
361   /*
362     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;');
363     STPROC.BIND_I(X_NAME);
364     STPROC.BIND_I(X_VALUE);
365     STPROC.BIND_I(X_LEVEL_NAME);
366     STPROC.BIND_I(X_LEVEL_VALUE);
367     STPROC.BIND_I(X_LEVEL_VALUE_APP_ID);
368     STPROC.BIND_O(X0);
369     STPROC.EXECUTE;
370     STPROC.RETRIEVE(6
371                    ,X0);*/
372     RETURN X0;
373   END SAVE;
374 
375   PROCEDURE GET_SPECIFIC(NAME_Z IN VARCHAR2
376                         ,USER_ID_Z IN NUMBER
377                         ,RESPONSIBILITY_ID_Z IN NUMBER
378                         ,APPLICATION_ID_Z IN NUMBER
379                         ,VAL_Z OUT NOCOPY VARCHAR2
380                         ,DEFINED_Z OUT NOCOPY BOOLEAN) IS
381   BEGIN
382 /*    STPROC.INIT('declare DEFINED_Z BOOLEAN; begin DEFINED_Z := sys.diutil.int_to_bool(:DEFINED_Z); FND_PROFILE.GET_SPECIFIC(:NAME_Z, :USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :VAL_Z, DEFINED_Z);
383 	:DEFINED_Z := sys.diutil.bool_to_int(DEFINED_Z); end;');
384     STPROC.BIND_O(DEFINED_Z);
385     STPROC.BIND_I(NAME_Z);
386     STPROC.BIND_I(USER_ID_Z);
387     STPROC.BIND_I(RESPONSIBILITY_ID_Z);
388     STPROC.BIND_I(APPLICATION_ID_Z);
389     STPROC.BIND_O(VAL_Z);
390     STPROC.EXECUTE;
391     STPROC.RETRIEVE(1
392                    ,DEFINED_Z);
393     STPROC.RETRIEVE(6
394                    ,VAL_Z);*/
395 		   null;
396   END GET_SPECIFIC;
397 
398   FUNCTION VALUE_SPECIFIC(NAME IN VARCHAR2
399                          ,USER_ID IN NUMBER
400                          ,RESPONSIBILITY_ID IN NUMBER
401                          ,APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
402     X0 VARCHAR2(2000);
403   BEGIN
404   /*
405     STPROC.INIT('begin :X0 := FND_PROFILE.VALUE_SPECIFIC(:NAME, :USER_ID, :RESPONSIBILITY_ID, :APPLICATION_ID); end;');
406     STPROC.BIND_O(X0);
407     STPROC.BIND_I(NAME);
408     STPROC.BIND_I(USER_ID);
409     STPROC.BIND_I(RESPONSIBILITY_ID);
410     STPROC.BIND_I(APPLICATION_ID);
411     STPROC.EXECUTE;
412     STPROC.RETRIEVE(1
413                    ,X0);*/
414     RETURN X0;
415   END VALUE_SPECIFIC;
416 
417   PROCEDURE INITIALIZE(USER_ID_Z IN NUMBER
418                       ,RESPONSIBILITY_ID_Z IN NUMBER
419                       ,APPLICATION_ID_Z IN NUMBER
420                       ,SITE_ID_Z IN NUMBER) IS
421   BEGIN
422   /*
423     STPROC.INIT('begin FND_PROFILE.INITIALIZE(:USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :SITE_ID_Z); end;');
424     STPROC.BIND_I(USER_ID_Z);
425     STPROC.BIND_I(RESPONSIBILITY_ID_Z);
426     STPROC.BIND_I(APPLICATION_ID_Z);
427     STPROC.BIND_I(SITE_ID_Z);
428     STPROC.EXECUTE;*/
429     null;
430   END INITIALIZE;
431 
432   PROCEDURE PUTMULTIPLE(NAMES IN VARCHAR2
433                        ,VALS IN VARCHAR2
434                        ,NUM IN NUMBER) IS
435   BEGIN
436   /*
437     STPROC.INIT('begin FND_PROFILE.PUTMULTIPLE(:NAMES, :VALS, :NUM); end;');
438     STPROC.BIND_I(NAMES);
439     STPROC.BIND_I(VALS);
440     STPROC.BIND_I(NUM);
441     STPROC.EXECUTE;*/
442     null;
443   END PUTMULTIPLE;
444 
445 END PSP_PSPLDAWD_XMLP_PKG;