1 PACKAGE BODY PSP_PSPLSODR_XMLP_PKG AS
2 /* $Header: PSPLSODRB.pls 120.5.12020000.4 2013/03/27 00:52:01 lkodaman ship $ */
3 FUNCTION CF_GL_DESCFORMULA(GL_CODE_COMBINATION_ID IN NUMBER) RETURN CHAR IS
4 L_GL_CODE VARCHAR2(1000);
5 L_SOB NUMBER(30) := PSP_GENERAL.GET_SPECIFIC_PROFILE('GL_SET_OF_BKS_ID');
6 BEGIN
7 IF GL_CODE_COMBINATION_ID IS NOT NULL THEN
8 L_GL_CODE := PSP_GENERAL.GET_GL_VALUES(L_SOB
9 ,GL_CODE_COMBINATION_ID);
10 RETURN L_GL_CODE;
11 END IF;
12 RETURN NULL;
13 EXCEPTION
14 WHEN OTHERS THEN
15 RETURN 'Invalid GL Code Combination';
16 END CF_GL_DESCFORMULA;
17
18 FUNCTION CF_TASK_NAMEFORMULA(PROJECT_ID_1 IN NUMBER
19 ,TASK_ID_1 IN NUMBER) RETURN CHAR IS
20 CURSOR TASK_ID_CUR IS
21 SELECT
22 TASK_NUMBER
23 FROM
24 PA_TASKS_EXPEND_V -- Bug : 16391366 (20/03/2013)
25 WHERE PROJECT_ID = PROJECT_ID_1
26 AND TASK_ID = TASK_ID_1;
27 L_TASK_NUMBER VARCHAR2(300); -- Bug : 16391366 (27/03/2013)
28 BEGIN
29 IF TASK_ID_1 IS NOT NULL THEN
30 OPEN TASK_ID_CUR;
31 FETCH TASK_ID_CUR
32 INTO L_TASK_NUMBER;
33 RETURN L_TASK_NUMBER;
34 CLOSE TASK_ID_CUR;
35 END IF;
36 RETURN NULL;
37 EXCEPTION
38 WHEN OTHERS THEN
39 RETURN 'Invalid Task Number';
40 END CF_TASK_NAMEFORMULA;
41
42 FUNCTION CF_AWARD_NUMBERFORMULA(AWARD_ID_1 IN NUMBER) RETURN CHAR IS
43 CURSOR AWARD_NUMBER_CUR IS
44 SELECT
45 AWARD_NUMBER
46 FROM
47 GMS_AWARDS_ALL
48 WHERE AWARD_ID = AWARD_ID_1;
49 L_AWARD_NUMBER VARCHAR2(20);
50 BEGIN
51 IF AWARD_ID_1 IS NOT NULL THEN
52 OPEN AWARD_NUMBER_CUR;
53 FETCH AWARD_NUMBER_CUR
54 INTO L_AWARD_NUMBER;
55 IF L_AWARD_NUMBER IS NULL THEN
56 BEGIN
57 SELECT
58 DEFAULT_DIST_AWARD_NUMBER
59 INTO L_AWARD_NUMBER
60 FROM
61 GMS_IMPLEMENTATIONS
62 WHERE DEFAULT_DIST_AWARD_ID = AWARD_ID_1
63 AND AWARD_DISTRIBUTION_OPTION = 'Y';
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 NULL;
67 END;
68 END IF;
69 RETURN L_AWARD_NUMBER;
70 END IF;
71 RETURN NULL;
72 EXCEPTION
73 WHEN OTHERS THEN
74 RETURN 'Invalid Award Number';
75 END CF_AWARD_NUMBERFORMULA;
76
77 FUNCTION CF_PROJECT_NUMBERFORMULA(PROJECT_ID_1 IN NUMBER) RETURN CHAR IS
78 CURSOR PROJECT_NUMBER_CUR IS
79 SELECT
80 SEGMENT1
81 FROM
82 PA_PROJECTS_ALL
83 WHERE PROJECT_ID = PROJECT_ID_1;
84 L_PROJECT_NUMBER VARCHAR2(30);
85 BEGIN
86 IF PROJECT_ID_1 IS NOT NULL THEN
87 OPEN PROJECT_NUMBER_CUR;
88 FETCH PROJECT_NUMBER_CUR
89 INTO L_PROJECT_NUMBER;
90 RETURN L_PROJECT_NUMBER;
91 END IF;
92 RETURN NULL;
93 EXCEPTION
94 WHEN OTHERS THEN
95 RETURN 'Invalid Project Number';
96 END CF_PROJECT_NUMBERFORMULA;
97
98 FUNCTION CF_ORG_NAMEFORMULA(EXPENDITURE_ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
99 CURSOR ORG_NAME_CUR IS
100 SELECT
101 NAME
102 FROM
103 HR_ORGANIZATION_UNITS
104 WHERE ORGANIZATION_ID = EXPENDITURE_ORGANIZATION_ID;
105 L_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
106 BEGIN
107 IF EXPENDITURE_ORGANIZATION_ID IS NOT NULL THEN
108 OPEN ORG_NAME_CUR;
109 FETCH ORG_NAME_CUR
110 INTO L_ORG_NAME;
111 RETURN L_ORG_NAME;
112 END IF;
113 RETURN NULL;
114 EXCEPTION
115 WHEN OTHERS THEN
116 RETURN 'Invalid Expenditure Organization';
117 END CF_ORG_NAMEFORMULA;
118
119 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
120 P_RETURN_STATUS NUMBER;
121 P_LOG_MESSAGE VARCHAR2(100);
122 L_ORGANIZATION_ID VARCHAR2(2000);
123 L_BEGIN_POSITION NUMBER;
124 L_END_POSITION NUMBER;
125 CURSOR C_ORG_COUNT IS
126 SELECT
127 COUNT(1)
128 FROM
129 PSP_REPORT_TEMPLATE_DETAILS
130 WHERE TEMPLATE_ID = P_ORG_TEMPLATE_ID
131 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
132 AND CRITERIA_LOOKUP_CODE = 'ORG';
133 CURSOR C_ORG_CURSOR IS
134 SELECT
135 CRITERIA_VALUE1
136 FROM
137 PSP_REPORT_TEMPLATE_DETAILS
138 WHERE TEMPLATE_ID = P_ORG_TEMPLATE_ID
139 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
140 AND CRITERIA_LOOKUP_CODE = 'ORG';
141 L_NUM NUMBER;
142 BEGIN
143 IF P_ORG_TEMPLATE_ID IS NULL THEN
144 L_ORGANIZATION_ID := NULL;
145 ELSE
146 OPEN C_ORG_COUNT;
147 FETCH C_ORG_COUNT
148 INTO L_NUM;
149 CLOSE C_ORG_COUNT;
150 IF L_NUM <> 0 THEN
151 FOR l_org_rec IN C_ORG_CURSOR LOOP
152 IF ((L_NUM - C_ORG_CURSOR%ROWCOUNT) = 0) THEN
153 L_ORGANIZATION_ID := L_ORGANIZATION_ID || L_ORG_REC.CRITERIA_VALUE1;
154 ELSE
155 L_ORGANIZATION_ID := L_ORGANIZATION_ID || L_ORG_REC.CRITERIA_VALUE1 || ',';
156 END IF;
157 END LOOP;
158 ELSE
159 L_ORGANIZATION_ID := NULL;
160 END IF;
161 END IF;
162 PSP_MATRIX_DRIVER_PKG.LOAD_ORG_SCHEDULE(P_RETURN_STATUS => P_RETURN_STATUS
163 ,P_LOG_MESSAGE => P_LOG_MESSAGE
164 ,P_LIST_ORGANIZATION_ID => L_ORGANIZATION_ID
165 ,P_PERIOD_FROM => P_BEGIN_DATE
166 ,P_PERIOD_TO => P_END_DATE
167 ,P_REPORT_TYPE => P_REPORT_TYPE
168 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
169 ,P_SET_OF_BOOKS_ID => P_SET_OF_BOOKS_ID);
170 P_RUN_ID := PSP_MATRIX_DRIVER_PKG.GET_RUN_ID;
171 IF P_RETURN_STATUS = 0 THEN
172 -- HR_STANDARD.EVENT('BEFORE REPORT');
173 null;
174 RETURN (TRUE);
175 ELSE
176 RETURN (FALSE);
177 END IF;
178 END BEFOREREPORT;
179
180 FUNCTION AFTERREPORT RETURN BOOLEAN IS
181 BEGIN
182 P_RUN_ID := PSP_MATRIX_DRIVER_PKG.GET_RUN_ID;
183 DELETE FROM PSP_MATRIX_DRIVER
184 WHERE RUN_ID = P_RUN_ID;
185 --HR_STANDARD.EVENT('AFTER REPORT');
186 null;
187 RETURN (TRUE);
188 EXCEPTION
189 WHEN OTHERS THEN
190 NULL;
191 END AFTERREPORT;
192
193 FUNCTION CF_ORG_SCHEDULE_IDFORMULA(ORG_SCHEDULE_ID IN NUMBER) RETURN NUMBER IS
194 BEGIN
195 RETURN (ORG_SCHEDULE_ID);
196 END CF_ORG_SCHEDULE_IDFORMULA;
197
198 FUNCTION AFTERPFORM RETURN BOOLEAN IS
199 CURSOR C1(P_LOOKUP_CODE IN VARCHAR2,L_TEMPLATE_ID IN NUMBER) IS
200 SELECT
201 COUNT(1)
202 FROM
203 PSP_REPORT_TEMPLATE_DETAILS
204 WHERE TEMPLATE_ID = L_TEMPLATE_ID
205 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
206 AND CRITERIA_LOOKUP_CODE = P_LOOKUP_CODE;
207 L_NUM NUMBER;
208 L_NUM1 NUMBER;
209 BEGIN
210 IF P_ORG_TEMPLATE_ID IS NULL THEN
211 P_ORGANIZATION_ID := ' 1 = 1 ';
212 ELSE
213 OPEN C1('ORG',P_ORG_TEMPLATE_ID);
214 FETCH C1
215 INTO L_NUM;
216 CLOSE C1;
217 IF L_NUM <> 0 THEN
218 P_ORGANIZATION_ID := ' hou.organization_id IN (select criteria_value1 from psp_report_template_details
219 where template_id = ' || P_ORG_TEMPLATE_ID || '
220 and criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
221 and criteria_lookup_code = ''ORG'' ' || ' ) ';
222 ELSE
223 P_ORGANIZATION_ID := ' 1 = 1 ';
224 END IF;
225 END IF;
226 RETURN (TRUE);
227 END AFTERPFORM;
228
229 FUNCTION CF_DATE_FORMATFORMULA RETURN CHAR IS
230 BEGIN
231 RETURN (FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
232 END CF_DATE_FORMATFORMULA;
233
234 FUNCTION CF_SCH_BEGIN_DATE_DISPFORMULA(SCHEDULE_BEGIN_DATE IN DATE
235 ,CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
236 BEGIN
237 /*SRW.REFERENCE(SCHEDULE_BEGIN_DATE)*/NULL;
238 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
239 RETURN (TO_CHAR(SCHEDULE_BEGIN_DATE
240 ,CF_DATE_FORMAT));
241 END CF_SCH_BEGIN_DATE_DISPFORMULA;
242
243 FUNCTION CF_SCH_END_DATE_DISPFORMULA(SCHEDULE_END_DATE IN DATE
244 ,CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
245 BEGIN
246 /*SRW.REFERENCE(SCHEDULE_END_DATE)*/NULL;
247 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
248 RETURN (TO_CHAR(SCHEDULE_END_DATE
249 ,CF_DATE_FORMAT));
250 END CF_SCH_END_DATE_DISPFORMULA;
251
252 FUNCTION CF_REPORT_RUN_DATEFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
253 BEGIN
254 /*SRW.REFERENCE(SYSDATE)*/NULL;
255 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
256 RETURN (TO_CHAR(SYSDATE
257 ,CF_DATE_FORMAT));
258 END CF_REPORT_RUN_DATEFORMULA;
259
260 FUNCTION CF_PERIOD_START_DATEFORMULA(PERIOD_START_DATE IN DATE
261 ,CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
262 BEGIN
263 /*SRW.REFERENCE(PERIOD_START_DATE)*/NULL;
264 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
265 RETURN (TO_CHAR(PERIOD_START_DATE
266 ,CF_DATE_FORMAT));
267 END CF_PERIOD_START_DATEFORMULA;
268
269 FUNCTION CF_PERIOD_END_DATE_DISPFORMULA(PERIOD_END_DATE IN DATE
270 ,CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
271 BEGIN
272 /*SRW.REFERENCE(PERIOD_END_DATE)*/NULL;
273 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
274 RETURN (TO_CHAR(PERIOD_END_DATE
275 ,CF_DATE_FORMAT));
276 END CF_PERIOD_END_DATE_DISPFORMULA;
277
278 FUNCTION CF_BEGIN_DATE_DISPFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
279 BEGIN
280 /*SRW.REFERENCE(P_BEGIN_DATE)*/NULL;
281 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
282 RETURN (TO_CHAR(P_BEGIN_DATE
283 ,CF_DATE_FORMAT));
284 END CF_BEGIN_DATE_DISPFORMULA;
285
286 FUNCTION CF_END_DATE_DISPFORMULA(CF_DATE_FORMAT IN VARCHAR2) RETURN CHAR IS
287 BEGIN
288 /*SRW.REFERENCE(P_END_DATE)*/NULL;
289 /*SRW.REFERENCE(CF_DATE_FORMAT)*/NULL;
290 RETURN (TO_CHAR(P_END_DATE
291 ,CF_DATE_FORMAT));
292 END CF_END_DATE_DISPFORMULA;
293
294 END PSP_PSPLSODR_XMLP_PKG;
[Home] [Help]