[Home] [Help]
PACKAGE BODY: APPS.PSP_PSPLDORG_XMLP_PKG
Source
1 PACKAGE BODY PSP_PSPLDORG_XMLP_PKG AS
2 /* $Header: PSPLDORGB.pls 120.3 2007/10/29 07:24:03 amakrish noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 CURSOR C1(P_LOOKUP_CODE IN VARCHAR2,L_TEMPLATE_ID IN NUMBER) IS
5 SELECT
6 COUNT(1)
7 FROM
8 PSP_REPORT_TEMPLATE_DETAILS
9 WHERE TEMPLATE_ID = L_TEMPLATE_ID
10 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
11 AND CRITERIA_LOOKUP_CODE = P_LOOKUP_CODE;
12 L_NUM NUMBER;
13 L_NUM1 NUMBER;
14 BEGIN
15 IF P_ORG_TEMPLATE_ID IS NULL THEN
16 P_ORG_ID := ' 1 = 1 ';
17 ELSE
18 OPEN C1('ORG',P_ORG_TEMPLATE_ID);
19 FETCH C1
20 INTO L_NUM;
21 CLOSE C1;
22 IF L_NUM <> 0 THEN
23 P_ORG_ID := ' paf.organization_id IN (select criteria_value1 from psp_report_template_details
24 where template_id = ' || P_ORG_TEMPLATE_ID || '
25 and criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
26 and criteria_lookup_code = ''ORG'' ' || ' ) ';
27 ELSE
28 P_ORG_ID := ' 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(PERSON_ID1 IN NUMBER
41 ,EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
42 V_EMPLOYEE_NUMBER VARCHAR2(30);
43 V_OGM_PERIOD_END DATE;
44 BEGIN
45 IF P_OGM_PERIOD IS NOT NULL THEN
46 SELECT
47 END_DATE
48 INTO V_OGM_PERIOD_END
49 FROM
50 PA_PERIODS
51 WHERE PERIOD_NAME = P_OGM_PERIOD;
52 SELECT
53 DISTINCT
54 EMPLOYEE_NUMBER
55 INTO V_EMPLOYEE_NUMBER
56 FROM
57 PER_PEOPLE_F
58 WHERE PERSON_ID = PERSON_ID1
59 AND ( V_OGM_PERIOD_END Between EFFECTIVE_START_DATE
60 AND EFFECTIVE_END_DATE );
61 ELSE
62 SELECT
63 EMPLOYEE_NUMBER
64 INTO V_EMPLOYEE_NUMBER
65 FROM
66 PER_PEOPLE_F
67 WHERE PERSON_ID = PERSON_ID1
68 AND TRUNC(EFFECTIVE_DATE) BETWEEN EFFECTIVE_START_DATE
69 AND EFFECTIVE_END_DATE;
70 END IF;
71 RETURN V_EMPLOYEE_NUMBER;
72 RETURN NULL;
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75 RETURN ('No data Found');
76 WHEN TOO_MANY_ROWS THEN
77 RETURN ('Too Many Rows');
78 WHEN OTHERS THEN
79 RETURN 'ERROR';
80 END CF_EMPLOYEE_NUMBERFORMULA;
81
82 FUNCTION CF_TIME_PERIOD_NAMEFORMULA RETURN VARCHAR2 IS
83 V_PERIOD_NAME VARCHAR2(35);
84 BEGIN
85 RETURN NULL;
86 EXCEPTION
87 WHEN OTHERS THEN
88 RETURN 'ERROR';
89 END CF_TIME_PERIOD_NAMEFORMULA;
90
91 FUNCTION CF_INSTITUTION_NAMEFORMULA RETURN VARCHAR2 IS
92 V_INSTITUTION_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
93 V_INSTITUTION_ID VARCHAR2(30);
94 BEGIN
95 V_INSTITUTION_ID := VALUE('PSP_ORG_REPORT');
96 IF V_INSTITUTION_ID IS NULL THEN
97 RETURN NULL;
98 END IF;
99 SELECT
100 NAME
101 INTO V_INSTITUTION_NAME
102 FROM
103 HR_ORGANIZATION_UNITS
104 WHERE ORGANIZATION_ID = TO_NUMBER(V_INSTITUTION_ID);
105 RETURN V_INSTITUTION_NAME;
106 RETURN NULL;
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 RETURN ('No Data Found');
110 WHEN TOO_MANY_ROWS THEN
111 RETURN ('Too Many Rows');
112 WHEN OTHERS THEN
113 RETURN NULL;
114 END CF_INSTITUTION_NAMEFORMULA;
115
116 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
117 BEGIN
118 --HR_STANDARD.EVENT('BEFORE REPORT');
119 RETURN (TRUE);
120 END BEFOREREPORT;
121
122 FUNCTION CF_CHARGING_DESCFORMULA(GL_ID IN NUMBER
123 ,PROJECT_NUMBER IN VARCHAR2
124 ,TASK_NUMBER IN VARCHAR2
125 ,AWARD_NUMBER IN VARCHAR2
126 ,EXPENDITURE_ORGANIZATION_NAME IN VARCHAR2
127 ,EXP_TYPE1 IN VARCHAR2) RETURN VARCHAR2 IS
128 T_NAME VARCHAR2(2000);
129 PROJ_NAME VARCHAR2(25);
130 TASK_NAME VARCHAR2(25);
131 AWARD_NAME VARCHAR2(15);
132 EXP_TYPE VARCHAR2(100);
133 EXP_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
134 V_RETCODE NUMBER;
135 L_CHART_OF_ACCTS VARCHAR2(20);
136 BEGIN
137 IF GL_ID IS NOT NULL THEN
138 V_RETCODE := PSP_GENERAL.FIND_CHART_OF_ACCTS(TO_NUMBER(P_SET_OF_BOOKS_ID)
139 ,L_CHART_OF_ACCTS);
140 T_NAME := FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME => 'SQLGL'
141 ,KEY_FLEX_CODE => 'GL#'
142 ,STRUCTURE_NUMBER => TO_NUMBER(L_CHART_OF_ACCTS)
143 ,COMBINATION_ID => GL_ID);
144 RETURN (T_NAME);
145 ELSE
146 RETURN (PROJECT_NUMBER || ' ' || TASK_NUMBER || ' ' || AWARD_NUMBER || ' ' || EXPENDITURE_ORGANIZATION_NAME || ' ' || EXP_TYPE1);
147 END IF;
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 RETURN ('NO_DATA_FOUND');
151 WHEN OTHERS THEN
152 RETURN ('OTHER ERROR OCCURED');
153 END CF_CHARGING_DESCFORMULA;
154
155 FUNCTION CF_CURRENCY_FORMATFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
156 BEGIN
157 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
158 RETURN (FND_CURRENCY.GET_FORMAT_MASK(CURRENCY_CODE
159 ,30));
160 END CF_CURRENCY_FORMATFORMULA;
161
162 FUNCTION CF_EARNINGS_AMOUNT_DSPFORMULA(EARNINGS_AMOUNT IN NUMBER
163 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
164 BEGIN
165 /*SRW.REFERENCE(EARNINGS_AMOUNT)*/NULL;
166 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
167 RETURN (TO_CHAR(EARNINGS_AMOUNT
168 ,CF_CURRENCY_FORMAT));
169 END CF_EARNINGS_AMOUNT_DSPFORMULA;
170
171 FUNCTION CF_CURRENCY_CODEFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
172 BEGIN
173 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
174 RETURN ('(' || CURRENCY_CODE || ')');
175 END CF_CURRENCY_CODEFORMULA;
176
177 FUNCTION CF_ORG_CURR_AMT_TOTAL_DSPFORMU(CF_CURRENCY_FORMAT IN VARCHAR2
178 ,CS_ORG_CURR_AMT_TOTAL IN NUMBER) RETURN CHAR IS
179 BEGIN
180 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
181 /*SRW.REFERENCE(CS_ORG_CURR_AMT_TOTAL)*/NULL;
182 RETURN (TO_CHAR(CS_ORG_CURR_AMT_TOTAL
183 ,CF_CURRENCY_FORMAT));
184 END CF_ORG_CURR_AMT_TOTAL_DSPFORMU;
185
186 FUNCTION AFTERREPORT RETURN BOOLEAN IS
187 BEGIN
188 --HR_STANDARD.EVENT('AFTER REPORT');
189 RETURN (TRUE);
190 END AFTERREPORT;
191
192 FUNCTION CF_DATE_FORMATFORMULA RETURN CHAR IS
193 BEGIN
194 RETURN (VALUE('ICX_DATE_FORMAT_MASK'));
195 END CF_DATE_FORMATFORMULA;
196
197 FUNCTION CF_RUN_DATEFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
198 BEGIN
199 /*SRW.REFERENCE(SYSDATE)*/NULL;
200 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
201 RETURN (TO_CHAR(SYSDATE
202 ,CF_DATE_FORMAT));
203 END CF_RUN_DATEFORMULA;
204
205 FUNCTION CF_START_DATE_SIPFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
206 BEGIN
207 /*SRW.REFERENCE(P_BEGIN_DATE)*/NULL;
208 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
209 RETURN (TO_CHAR(P_BEGIN_DATE
210 ,CF_DATE_FORMAT));
211 END CF_START_DATE_SIPFORMULA;
212
213 FUNCTION CF_END_DATE_DISPFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
214 BEGIN
215 /*SRW.REFERENCE(P_END_DATE)*/NULL;
216 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
217 RETURN (TO_CHAR(P_END_DATE
218 ,CF_DATE_FORMAT));
219 END CF_END_DATE_DISPFORMULA;
220
221 PROCEDURE PUT(NAME IN VARCHAR2
222 ,VAL IN VARCHAR2) IS
223 BEGIN
224 --STPROC.INIT('begin FND_PROFILE.PUT(:NAME, :VAL); end;');
225 begin
226 FND_PROFILE.PUT(NAME, VAL);
227 end;
228
229 --STPROC.BIND_I(NAME);
230 --STPROC.BIND_I(VAL);
231 --STPROC.EXECUTE;
232 END PUT;
233
234 FUNCTION DEFINED(NAME IN VARCHAR2) RETURN BOOLEAN IS
235 X0 BOOLEAN;
236 BEGIN
237 --STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.DEFINED(:NAME); :X0 := sys.diutil.bool_to_int(X0rv); end;');
238 declare
239 X0rv BOOLEAN;
240 begin
241 X0 := FND_PROFILE.DEFINED(NAME);
242 --X0 := sys.diutil.bool_to_int(X0rv);
243 end;
244 --STPROC.BIND_I(NAME);
245 --STPROC.BIND_O(X0);
246 --STPROC.EXECUTE;
247 --STPROC.RETRIEVE(2,X0);
248 RETURN X0;
249 END DEFINED;
250
251 PROCEDURE GET(NAME IN VARCHAR2
252 ,VAL OUT NOCOPY VARCHAR2) IS
253 BEGIN
254 --STPROC.INIT('begin FND_PROFILE.GET(:NAME, :VAL); end;');
255 begin
256 FND_PROFILE.GET(NAME, VAL);
257 end;
258 --STPROC.BIND_I(NAME);
259 --STPROC.BIND_O(VAL);
260 --STPROC.EXECUTE;
261 --STPROC.RETRIEVE(2,VAL);
262 END GET;
263
264 FUNCTION VALUE(NAME IN VARCHAR2) RETURN VARCHAR2 IS
265 X0 VARCHAR2(2000);
266 BEGIN
267 --STPROC.INIT('begin :X0 := FND_PROFILE.VALUE(:NAME); end;');
268 begin
269 X0 := FND_PROFILE.VALUE(NAME);
270 end;
271 /* STPROC.BIND_O(X0);
272 STPROC.BIND_I(NAME);
273 STPROC.EXECUTE;
274 STPROC.RETRIEVE(1
275 ,X0);*/
276 RETURN X0;
277 END VALUE;
278
279 FUNCTION SAVE_USER(X_NAME IN VARCHAR2
280 ,X_VALUE IN VARCHAR2) RETURN BOOLEAN IS
281 X0 BOOLEAN;
282 BEGIN
283 --STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE_USER(:X_NAME, :X_VALUE); :X0 := sys.diutil.bool_to_int(X0rv); end;');
284 declare
285 X0rv BOOLEAN;
286 begin
287 X0 := FND_PROFILE.SAVE_USER(X_NAME, X_VALUE);
288 --X0 := sys.diutil.bool_to_int(X0rv);
289 end;
290 /*STPROC.BIND_I(X_NAME);
291 STPROC.BIND_I(X_VALUE);
292 STPROC.BIND_O(X0);
293 STPROC.EXECUTE;
294 STPROC.RETRIEVE(3
295 ,X0);*/
296 RETURN X0;
297 END SAVE_USER;
298
299 FUNCTION SAVE(X_NAME IN VARCHAR2
300 ,X_VALUE IN VARCHAR2
301 ,X_LEVEL_NAME IN VARCHAR2
302 ,X_LEVEL_VALUE IN VARCHAR2
303 ,X_LEVEL_VALUE_APP_ID IN VARCHAR2) RETURN BOOLEAN IS
304 X0 BOOLEAN;
305 BEGIN
306 --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;');
307 declare
308 X0rv BOOLEAN;
309 begin
310 X0 := FND_PROFILE.SAVE(X_NAME, X_VALUE, X_LEVEL_NAME, X_LEVEL_VALUE, X_LEVEL_VALUE_APP_ID);
311 --X0 := sys.diutil.bool_to_int(X0rv);
312 end;
313 /*STPROC.BIND_I(X_NAME);
314 STPROC.BIND_I(X_VALUE);
315 STPROC.BIND_I(X_LEVEL_NAME);
316 STPROC.BIND_I(X_LEVEL_VALUE);
317 STPROC.BIND_I(X_LEVEL_VALUE_APP_ID);
318 STPROC.BIND_O(X0);
319 STPROC.EXECUTE;
320 STPROC.RETRIEVE(6
321 ,X0);*/
322 RETURN X0;
323 END SAVE;
324
325 PROCEDURE GET_SPECIFIC(NAME_Z IN VARCHAR2
326 ,USER_ID_Z IN NUMBER
327 ,RESPONSIBILITY_ID_Z IN NUMBER
328 ,APPLICATION_ID_Z IN NUMBER
329 ,VAL_Z OUT NOCOPY VARCHAR2
330 ,DEFINED_Z OUT NOCOPY BOOLEAN) IS
331 BEGIN
332 /*STPROC.INIT('declare DEFINED_Z BOOLEAN; begin DEFINED_Z := sys.diutil.int_to_bool(:DEFINED_Z); FND_PROFILE.GET_SPECIFIC
333 (:NAME_Z, :USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :VAL_Z, DEFINED_Z); :DEFINED_Z := sys.diutil.bool_to_int(DEFINED_Z); end;');*/
334 /* declare
335 DEFINED_Z BOOLEAN;
336 begin
337 DEFINED_Z := sys.diutil.int_to_bool(DEFINED_Z);
338 FND_PROFILE.GET_SPECIFIC(NAME_Z, USER_ID_Z, RESPONSIBILITY_ID_Z, APPLICATION_ID_Z, VAL_Z, DEFINED_Z);
339 DEFINED_Z := sys.diutil.bool_to_int(DEFINED_Z);
340 end;*/
341 /*STPROC.BIND_O(DEFINED_Z);
342 STPROC.BIND_I(NAME_Z);
343 STPROC.BIND_I(USER_ID_Z);
344 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
345 STPROC.BIND_I(APPLICATION_ID_Z);
346 STPROC.BIND_O(VAL_Z);
347 STPROC.EXECUTE;
348 STPROC.RETRIEVE(1
349 ,DEFINED_Z);
350 STPROC.RETRIEVE(6
351 ,VAL_Z);*/
352 null;
353 END GET_SPECIFIC;
354
355 FUNCTION VALUE_SPECIFIC(NAME IN VARCHAR2
356 ,USER_ID IN NUMBER
357 ,RESPONSIBILITY_ID IN NUMBER
358 ,APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
359 X0 VARCHAR2(2000);
360 BEGIN
361 --STPROC.INIT('begin :X0 := FND_PROFILE.VALUE_SPECIFIC(:NAME, :USER_ID, :RESPONSIBILITY_ID, :APPLICATION_ID); end;');
362 begin
363 X0 := FND_PROFILE.VALUE_SPECIFIC(NAME, USER_ID, RESPONSIBILITY_ID, APPLICATION_ID);
364 end;
365 /*STPROC.BIND_O(X0);
366 STPROC.BIND_I(NAME);
367 STPROC.BIND_I(USER_ID);
368 STPROC.BIND_I(RESPONSIBILITY_ID);
369 STPROC.BIND_I(APPLICATION_ID);
370 STPROC.EXECUTE;
371 STPROC.RETRIEVE(1
372 ,X0);*/
373 RETURN X0;
374 END VALUE_SPECIFIC;
375
376 PROCEDURE INITIALIZE(USER_ID_Z IN NUMBER
377 ,RESPONSIBILITY_ID_Z IN NUMBER
378 ,APPLICATION_ID_Z IN NUMBER
379 ,SITE_ID_Z IN NUMBER) IS
380 BEGIN
381 --STPROC.INIT('begin FND_PROFILE.INITIALIZE(:USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :SITE_ID_Z); end;');
382 begin
383 FND_PROFILE.INITIALIZE(USER_ID_Z, RESPONSIBILITY_ID_Z, APPLICATION_ID_Z, SITE_ID_Z);
384 end;
385 /*STPROC.BIND_I(USER_ID_Z);
386 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
387 STPROC.BIND_I(APPLICATION_ID_Z);
388 STPROC.BIND_I(SITE_ID_Z);
389 STPROC.EXECUTE;*/
390 END INITIALIZE;
391
392 PROCEDURE PUTMULTIPLE(NAMES IN VARCHAR2
393 ,VALS IN VARCHAR2
394 ,NUM IN NUMBER) IS
395 BEGIN
396 --STPROC.INIT('begin FND_PROFILE.PUTMULTIPLE(:NAMES, :VALS, :NUM); end;');
397 begin
398 FND_PROFILE.PUTMULTIPLE(NAMES, VALS, NUM);
399 end;
400 /*STPROC.BIND_I(NAMES);
401 STPROC.BIND_I(VALS);
402 STPROC.BIND_I(NUM);
403 STPROC.EXECUTE;*/
404 END PUTMULTIPLE;
405
406 END PSP_PSPLDORG_XMLP_PKG;