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