1 PACKAGE BODY PSP_PSPLSEMP_XMLP_PKG AS
2 /* $Header: PSPLSEMPB.pls 120.3.12020000.4 2013/03/27 00:39:59 lkodaman ship $ */
3 FUNCTION CF_ORGANIZATION_NAMEFORMULA(ORGANIZATION_ID_V IN NUMBER) RETURN CHAR IS
4 CURSOR ORG_NAME_CUR IS
5 SELECT
6 NAME
7 FROM
8 HR_ORGANIZATION_UNITS
9 WHERE ORGANIZATION_ID = ORGANIZATION_ID_V;
10 L_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
11 BEGIN
12 OPEN ORG_NAME_CUR;
13 FETCH ORG_NAME_CUR
14 INTO L_ORG_NAME;
15 CLOSE ORG_NAME_CUR;
16 IF (L_ORG_NAME IS NULL) THEN
17 L_ORG_NAME := 'INVALID ORGANIZATION NAME';
18 END IF;
19 RETURN L_ORG_NAME;
20 EXCEPTION
21 WHEN OTHERS THEN
22 RETURN 'Invalid Organization Name';
23 END CF_ORGANIZATION_NAMEFORMULA;
24
25 FUNCTION CF_EMPLOYEE_NAMEFORMULA(PERSON_ID_V IN NUMBER) RETURN CHAR IS
26 CURSOR EMP_NAME_CUR IS
27 SELECT
28 FULL_NAME
29 FROM
30 PER_PEOPLE_F PPF1
31 WHERE PPF1.PERSON_ID = PERSON_ID_V
32 AND PPF1.CURRENT_EMPLOYEE_FLAG = 'Y'
33 AND ( TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
34 AND EFFECTIVE_END_DATE
35 OR TRUNC(SYSDATE) < (
36 SELECT
37 MIN(PPF2.EFFECTIVE_START_DATE)
38 FROM
39 PER_PEOPLE_F PPF2
40 WHERE PPF2.PERSON_ID = PPF1.PERSON_ID
41 AND PPF2.CURRENT_EMPLOYEE_FLAG = 'Y' ) );
42 L_EMP_NAME VARCHAR2(240);
43 BEGIN
44 OPEN EMP_NAME_CUR;
45 FETCH EMP_NAME_CUR
46 INTO L_EMP_NAME;
47 CLOSE EMP_NAME_CUR;
48 IF (L_EMP_NAME IS NULL) THEN
49 L_EMP_NAME := 'INVALID EMPLOYEE NAME';
50 END IF;
51 RETURN L_EMP_NAME;
52 END CF_EMPLOYEE_NAMEFORMULA;
53
54 FUNCTION CF_GL_DESCRIPTIONFORMULA(GL_CODE_COMBINATION_ID IN NUMBER) RETURN CHAR IS
55 V_GL_DESCRIPTION VARCHAR2(1000);
56 V_SET_OF_BOOKS_ID NUMBER := PSP_GENERAL.GET_SPECIFIC_PROFILE('GL_SET_OF_BKS_ID');
57 BEGIN
58 IF (GL_CODE_COMBINATION_ID IS NOT NULL) THEN
59 V_GL_DESCRIPTION := PSP_GENERAL.GET_GL_VALUES(V_SET_OF_BOOKS_ID
60 ,GL_CODE_COMBINATION_ID);
61 RETURN V_GL_DESCRIPTION;
62 END IF;
63 RETURN NULL;
64 EXCEPTION
65 WHEN OTHERS THEN
66 RETURN 'Invalid GL Code Combination';
67 END CF_GL_DESCRIPTIONFORMULA;
68
69 FUNCTION CF_PROJECT_NUMBERFORMULA(PROJECT_ID_V IN NUMBER) RETURN CHAR IS
70 CURSOR PROJECT_NUMBER_CUR IS
71 SELECT
72 SEGMENT1
73 FROM
74 PA_PROJECTS_ALL
75 WHERE PROJECT_ID = PROJECT_ID_V;
76 L_PROJECT_NUMBER VARCHAR2(30);
77 BEGIN
78 IF (PROJECT_ID_V IS NOT NULL) THEN
79 OPEN PROJECT_NUMBER_CUR;
80 FETCH PROJECT_NUMBER_CUR
81 INTO L_PROJECT_NUMBER;
82 CLOSE PROJECT_NUMBER_CUR;
83 RETURN L_PROJECT_NUMBER;
84 END IF;
85 RETURN NULL;
86 EXCEPTION
87 WHEN OTHERS THEN
88 RETURN 'Invalid Project Number';
89 END CF_PROJECT_NUMBERFORMULA;
90
91 FUNCTION CF_TASK_NUMBERFORMULA(TASK_ID_V IN NUMBER) RETURN CHAR IS
92 CURSOR TASK_NUMBER_CUR IS
93 SELECT
94 TASK_NUMBER
95 FROM
96 PA_TASKS_EXPEND_V -- Bug : 16391366 (20/03/2013)
97 WHERE TASK_ID = TASK_ID_V;
98 L_TASK_NUMBER VARCHAR2(300); -- Bug : 16391366 (27/03/2013)
99 BEGIN
100 IF (TASK_ID_V IS NOT NULL) THEN
101 OPEN TASK_NUMBER_CUR;
102 FETCH TASK_NUMBER_CUR
103 INTO L_TASK_NUMBER;
104 CLOSE TASK_NUMBER_CUR;
105 RETURN L_TASK_NUMBER;
106 END IF;
107 RETURN NULL;
108 EXCEPTION
109 WHEN OTHERS THEN
110 RETURN 'Invalid Task Number';
111 END CF_TASK_NUMBERFORMULA;
112
113 FUNCTION CF_AWARD_NUMBERFORMULA(AWARD_ID_V IN NUMBER) RETURN CHAR IS
114 CURSOR AWARD_NUMBER_CUR IS
115 SELECT
116 AWARD_NUMBER
117 FROM
118 GMS_AWARDS_ALL
119 WHERE AWARD_ID = AWARD_ID_V;
120 L_AWARD_NUMBER VARCHAR2(30);
121 BEGIN
122 IF (AWARD_ID_V IS NOT NULL) THEN
123 OPEN AWARD_NUMBER_CUR;
124 FETCH AWARD_NUMBER_CUR
125 INTO L_AWARD_NUMBER;
126 CLOSE AWARD_NUMBER_CUR;
127 IF L_AWARD_NUMBER IS NULL THEN
128 BEGIN
129 SELECT
130 DEFAULT_DIST_AWARD_NUMBER
131 INTO L_AWARD_NUMBER
132 FROM
133 GMS_IMPLEMENTATIONS
134 WHERE DEFAULT_DIST_AWARD_ID = AWARD_ID_V
135 AND AWARD_DISTRIBUTION_OPTION = 'Y';
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 NULL;
139 END;
140 END IF;
141 RETURN L_AWARD_NUMBER;
142 END IF;
143 RETURN NULL;
144 EXCEPTION
145 WHEN OTHERS THEN
146 RETURN 'Invalid Award Number';
147 END CF_AWARD_NUMBERFORMULA;
148
149 FUNCTION CF_EXP_ORG_NAMEFORMULA(EXPENDITURE_ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
150 CURSOR EXP_ORG_NAME_CUR IS
151 SELECT
152 NAME
153 FROM
154 HR_ORGANIZATION_UNITS
155 WHERE ORGANIZATION_ID = EXPENDITURE_ORGANIZATION_ID;
156 L_EXP_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
157 BEGIN
158 IF (EXPENDITURE_ORGANIZATION_ID IS NOT NULL) THEN
159 OPEN EXP_ORG_NAME_CUR;
160 FETCH EXP_ORG_NAME_CUR
161 INTO L_EXP_ORG_NAME;
162 CLOSE EXP_ORG_NAME_CUR;
163 RETURN L_EXP_ORG_NAME;
164 END IF;
165 RETURN NULL;
166 EXCEPTION
167 WHEN OTHERS THEN
168 RETURN 'Invalid Expenditure Organization Name';
169 END CF_EXP_ORG_NAMEFORMULA;
170
171 FUNCTION CF_DESCRIPTIONFORMULA(TYPE IN NUMBER
172 ,SCHEDULING_TYPES_CODE IN VARCHAR2) RETURN CHAR IS
173 CURSOR ASSIGNMENT_NUMBER_CUR IS
174 SELECT
175 ASSIGNMENT_NUMBER
176 FROM
177 PER_ASSIGNMENTS_F PAF
178 WHERE PAF.ASSIGNMENT_ID = TYPE
179 AND PAF.ASSIGNMENT_TYPE = 'E'
180 AND ROWNUM = 1;
181 CURSOR ELEMENT_GROUP_CUR IS
182 SELECT
183 ELEMENT_GROUP_NAME
184 FROM
185 PSP_ELEMENT_GROUPS PEG
186 WHERE PEG.ELEMENT_GROUP_ID = TYPE
187 AND ROWNUM = 1;
188 CURSOR ELEMENT_TYPE_CUR IS
189 SELECT
190 ELEMENT_NAME
191 FROM
192 PAY_ELEMENT_TYPES_F PET
193 WHERE PET.ELEMENT_TYPE_ID = TYPE
194 AND ROWNUM = 1;
195 L_HIER_DESC VARCHAR2(100);
196 BEGIN
197 IF (SCHEDULING_TYPES_CODE = 'A') THEN
198 OPEN ASSIGNMENT_NUMBER_CUR;
199 FETCH ASSIGNMENT_NUMBER_CUR
200 INTO L_HIER_DESC;
201 CLOSE ASSIGNMENT_NUMBER_CUR;
202 ELSIF (SCHEDULING_TYPES_CODE = 'EG') THEN
203 OPEN ELEMENT_GROUP_CUR;
204 FETCH ELEMENT_GROUP_CUR
205 INTO L_HIER_DESC;
206 CLOSE ELEMENT_GROUP_CUR;
207 ELSIF (SCHEDULING_TYPES_CODE = 'ET') THEN
208 OPEN ELEMENT_TYPE_CUR;
209 FETCH ELEMENT_TYPE_CUR
210 INTO L_HIER_DESC;
211 CLOSE ELEMENT_TYPE_CUR;
212 END IF;
213 RETURN L_HIER_DESC;
214 EXCEPTION
215 WHEN OTHERS THEN
216 RETURN NULL;
217 END CF_DESCRIPTIONFORMULA;
218
219 FUNCTION AFTERREPORT RETURN BOOLEAN IS
220 BEGIN
221 -- HR_STANDARD.EVENT('AFTER REPORT');
222 RETURN (TRUE);
223 EXCEPTION
224 WHEN OTHERS THEN
225 NULL;
226 END AFTERREPORT;
227
228 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
229 L_RETCODE NUMBER;
230 L_ORGANIZATION_ID VARCHAR2(4000);
231 CURSOR C_ORG_COUNT IS
232 SELECT
233 COUNT(1)
234 FROM
235 PSP_REPORT_TEMPLATE_DETAILS
236 WHERE TEMPLATE_ID = P_ORG_TEMPLATE_ID
237 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
238 AND CRITERIA_LOOKUP_CODE = 'ORG';
239 CURSOR C_ORG_CURSOR IS
240 SELECT
241 CRITERIA_VALUE1
242 FROM
243 PSP_REPORT_TEMPLATE_DETAILS
244 WHERE TEMPLATE_ID = P_ORG_TEMPLATE_ID
245 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
246 AND CRITERIA_LOOKUP_CODE = 'ORG';
247 L_NUM NUMBER;
248 BEGIN
249 -- HR_STANDARD.EVENT('BEFORE REPORT');
250 IF P_ORG_TEMPLATE_ID IS NULL THEN
251 L_ORGANIZATION_ID := NULL;
252 ELSE
253 OPEN C_ORG_COUNT;
254 FETCH C_ORG_COUNT
255 INTO L_NUM;
256 CLOSE C_ORG_COUNT;
257 IF L_NUM <> 0 THEN
258 FOR l_org_rec IN C_ORG_CURSOR LOOP
259 IF ((L_NUM - C_ORG_CURSOR%ROWCOUNT) = 0) THEN
260 L_ORGANIZATION_ID := L_ORGANIZATION_ID || L_ORG_REC.CRITERIA_VALUE1;
261 ELSE
262 L_ORGANIZATION_ID := L_ORGANIZATION_ID || L_ORG_REC.CRITERIA_VALUE1 || ',';
263 END IF;
264 END LOOP;
265 ELSE
266 L_ORGANIZATION_ID := NULL;
267 END IF;
268 END IF;
269 PSP_MATRIX_DRIVER_PKG.LOAD_ORGANIZATIONS(RETCODE => L_RETCODE
270 ,P_ORGANIZATION_ID => L_ORGANIZATION_ID
271 ,P_PERIOD_FROM => P_BEGIN_DATE
272 ,P_PERIOD_TO => P_END_DATE
273 ,P_REPORT_TYPE => P_REPORT_TYPE
274 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
275 ,P_SET_OF_BOOKS_ID => P_SET_OF_BOOKS_ID);
276 IF (L_RETCODE <> 0) THEN
277 RETURN (FALSE);
278 END IF;
279 P_RUN_ID := NVL(PSP_MATRIX_DRIVER_PKG.GET_RUN_ID
280 ,0);
281 IF (P_RUN_ID = 0) THEN
282 RETURN (FALSE);
283 END IF;
284 RETURN (TRUE);
285 END BEFOREREPORT;
286
287 FUNCTION CF_HIER_DESCFORMULA(SCHEDULING_TYPES_CODE IN VARCHAR2
288 ,CF_DESCRIPTION IN VARCHAR2) RETURN CHAR IS
289 BEGIN
290 RETURN SCHEDULING_TYPES_CODE || '-' || CF_DESCRIPTION;
291 END CF_HIER_DESCFORMULA;
292
293 FUNCTION AFTERPFORM RETURN BOOLEAN IS
294 CURSOR C1(P_LOOKUP_CODE IN VARCHAR2,L_TEMPLATE_ID IN NUMBER) IS
295 SELECT
296 COUNT(1)
297 FROM
298 PSP_REPORT_TEMPLATE_DETAILS
299 WHERE TEMPLATE_ID = L_TEMPLATE_ID
300 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
301 AND CRITERIA_LOOKUP_CODE = P_LOOKUP_CODE;
302 L_NUM NUMBER;
303 BEGIN
304 IF P_ORG_TEMPLATE_ID IS NULL THEN
305 P_ORGANIZATION_ID := ' 1 = 1 ';
306 ELSE
307 OPEN C1('ORG',P_ORG_TEMPLATE_ID);
308 FETCH C1
309 INTO L_NUM;
310 CLOSE C1;
311 IF L_NUM <> 0 THEN
312 P_ORGANIZATION_ID := ' hou.organization_id IN (select criteria_value1 from psp_report_template_details
313 where template_id = ' || P_ORG_TEMPLATE_ID || '
314 and criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
315 and criteria_lookup_code = ''ORG'' ' || ' ) ';
316 ELSE
317 P_ORGANIZATION_ID := ' 1 = 1 ';
318 END IF;
319 END IF;
320 RETURN (TRUE);
321 END AFTERPFORM;
322
323 FUNCTION CF_DATE_FORMATFORMULA RETURN CHAR IS
324 BEGIN
325 RETURN (FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
326 END CF_DATE_FORMATFORMULA;
327
328 FUNCTION CF_REPORT_RUN_DATEFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
329 BEGIN
330 /*SRW.REFERENCE(SYSDATE)*/NULL;
331 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
332 RETURN (TO_CHAR(SYSDATE
333 ,CF_DATE_FORMAT));
334 END CF_REPORT_RUN_DATEFORMULA;
335
336 FUNCTION CF_SCH_BEGIN_DATE_DISPFORMULA(SCHEDULE_BEGIN_DATE IN DATE
337 ,CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
338 BEGIN
339 /*SRW.REFERENCE(SCHEDULE_BEGIN_DATE)*/NULL;
340 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
341 RETURN (TO_CHAR(SCHEDULE_BEGIN_DATE
342 ,CF_DATE_FORMAT));
343 END CF_SCH_BEGIN_DATE_DISPFORMULA;
344
345 FUNCTION CF_SCH_END_DATE_DSPFORMULA(SCHEDULE_END_DATE IN DATE
346 ,CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
347 BEGIN
348 /*SRW.REFERENCE(SCHEDULE_END_DATE)*/NULL;
349 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
350 RETURN (TO_CHAR(SCHEDULE_END_DATE
351 ,CF_DATE_FORMAT));
352 END CF_SCH_END_DATE_DSPFORMULA;
353
354 FUNCTION CF_PERIOD_START_DATE_DISPFORMU(PERIOD_START_DATE IN DATE
355 ,CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
356 BEGIN
357 /*SRW.REFERENCE(PERIOD_START_DATE)*/NULL;
358 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
359 RETURN (TO_CHAR(PERIOD_START_DATE
360 ,CF_DATE_FORMAT));
361 END CF_PERIOD_START_DATE_DISPFORMU;
362
363 FUNCTION CF_PERIOD_END_DATE_DISPFORMULA(PERIOD_END_DATE IN DATE
364 ,CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
365 BEGIN
366 /*SRW.REFERENCE(PERIOD_END_DATE)*/NULL;
367 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
368 RETURN (TO_CHAR(PERIOD_END_DATE
369 ,CF_DATE_FORMAT));
370 END CF_PERIOD_END_DATE_DISPFORMULA;
371
372 FUNCTION CF_BEGIN_DATE_DISPFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
373 BEGIN
374 /*SRW.REFERENCE(P_BEGIN_DATE)*/NULL;
375 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
376 RETURN (TO_CHAR(P_BEGIN_DATE
377 ,CF_DATE_FORMAT));
378 END CF_BEGIN_DATE_DISPFORMULA;
379
380 FUNCTION CF_END_DATE_DISPFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
381 BEGIN
382 /*SRW.REFERENCE(P_END_DATE)*/NULL;
383 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
384 RETURN (TO_CHAR(P_END_DATE
385 ,CF_DATE_FORMAT));
386 END CF_END_DATE_DISPFORMULA;
387
388 END PSP_PSPLSEMP_XMLP_PKG;
[Home] [Help]