1 PACKAGE BODY CS_CSXSRDET_XMLP_PKG AS
2 /* $Header: CSXSRDETB.pls 120.0 2008/01/24 13:30:11 dwkrishn noship $ */
3
4 FUNCTION AFTERREPORT RETURN BOOLEAN IS
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 RETURN (TRUE);
8 END AFTERREPORT;
9
10 FUNCTION AFTERPFORM RETURN BOOLEAN IS
11 BEGIN
12 BEGIN
13 IF (P_CUSTOMER_NUM_LOW IS NOT NULL) AND (P_CUSTOMER_NUM_HIGH IS NOT NULL) THEN
14 LP_CUSTOMER_NUMBER := 'and (inc.customer_number between :P_Customer_Num_Low AND :P_Customer_Num_High)';
15 ELSIF (P_CUSTOMER_NUM_LOW IS NOT NULL) THEN
16 LP_CUSTOMER_NUMBER := 'AND inc.customer_number >= :P_Customer_Num_Low';
17 ELSIF (P_CUSTOMER_NUM_HIGH IS NOT NULL) THEN
18 LP_CUSTOMER_NUMBER := 'and inc.customer_number <= :P_Customer_Num_High ';
19 END IF;
20
21 IF (LP_CUSTOMER_NUMBER IS NULL) THEN
22 LP_CUSTOMER_NUMBER :='AND 1=1';
23 END IF;
24 IF (P_REQUEST_LOW IS NOT NULL) AND (P_REQUEST_HIGH IS NOT NULL) THEN
25 LP_REQUEST_NUMBER := 'AND (INC.incident_number BETWEEN
26 :P_request_Low AND
27 :P_request_High)';
28 ELSIF (P_REQUEST_LOW IS NOT NULL) THEN
29 LP_REQUEST_NUMBER := 'AND INC.incident_number >=
30 :P_request_Low';
31 ELSIF (P_REQUEST_HIGH IS NOT NULL) THEN
32 LP_REQUEST_NUMBER := 'AND INC.incident_number <=
33 :P_request_High ';
34 END IF;
35 IF (LP_REQUEST_NUMBER IS NULL) THEN
36 LP_REQUEST_NUMBER := 'AND 1=1';
37 END IF;
38 IF (P_TYPE IS NOT NULL) THEN
39 LP_TYPE := 'AND inc.incident_type =
40 :P_type';
41 END IF;
42 IF (LP_TYPE IS NULL) THEN
43 LP_TYPE :='AND 1=1';
44 END IF;
45 IF (P_SEVERITY IS NOT NULL) THEN
46 LP_SEVERITY := 'AND inc.severity =
47 :P_Severity';
48 END IF;
49 IF (LP_SEVERITY IS NULL) THEN
50 LP_SEVERITY :='AND 1=1';
51 END IF;
52 IF (P_URGENCY IS NOT NULL) THEN
53 LP_URGENCY := 'AND inc.URGENCY=
54 :P_Urgency ';
55 END IF;
56 IF (LP_URGENCY IS NULL) THEN
57 LP_URGENCY :='AND 1=1';
58 END IF;
59 IF (P_PROBLEM_CODE IS NOT NULL) THEN
60 LP_PROBLEM_CODE := 'AND INC.problem_code =
61 :P_problem_code';
62 END IF;
63
64 IF (LP_PROBLEM_CODE IS NULL) THEN
65 LP_PROBLEM_CODE :='AND 1=1';
66 END IF;
67 IF (P_RESOLUTION_CODE IS NOT NULL) THEN
68 LP_RESOLUTION_CODE := 'AND INC.Resolution_code =
69 :P_Resolution_code';
70 END IF;
71 IF (LP_RESOLUTION_CODE IS NULL) THEN
72 LP_RESOLUTION_CODE :='AND 1=1';
73 END IF;
74 IF (P_OWNER IS NOT NULL) THEN
75 LP_OWNER := 'AND inc.owner =
76 :P_Owner';
77 END IF;
78 IF (LP_OWNER IS NULL) THEN
79 LP_OWNER :='AND 1=1';
80 END IF;
81 IF (P_STATUS IS NOT NULL) THEN
82 LP_STATUS := 'AND inc.status_code =
83 :P_Status';
84 END IF;
85 IF (LP_STATUS IS NULL) THEN
86 LP_STATUS :='AND 1=1';
87 END IF;
88 IF (P_LOGGED_BY IS NOT NULL) THEN
89 LP_LOGGEDBY := 'AND inc.logged_by_name =
90 :P_logged_by';
91 END IF;
92 IF (LP_LOGGEDBY IS NULL) THEN
93 LP_LOGGEDBY :='AND 1=1';
94 END IF;
95 IF (P_CLOSED_DATE IS NOT NULL) THEN
96 LP_CLOSEDDATE := 'AND trunc(INC.date_closed) = ' || '''' || P_CLOSED_DATE || '''';
97 END IF;
98 IF (LP_CLOSEDDATE IS NULL) THEN
99 LP_CLOSEDDATE :='AND 1=1';
100 END IF;
101 IF (P_CLOSED_FLAG IS NOT NULL) THEN
102 LP_CLOSED_FLAG := 'AND inc.closed_flag =
103 :P_closed_flag';
104 END IF;
105 IF (LP_CLOSED_FLAG IS NULL) THEN
106 LP_CLOSED_FLAG :='AND 1=1';
107 END IF;
108 IF (P_SR_NOTES_STATUS IS NOT NULL) THEN
109 LP_SR_NOTES_STATUS := 'AND jtf_sr.status_code =
110 :p_sr_notes_status';
111 END IF;
112 IF (P_TASK_NOTES_STATUS IS NOT NULL) THEN
113 LP_TASK_NOTES_STATUS := 'AND jtf_task.status_code =
114 :p_task_notes_status';
115 END IF;
116 RETURN (TRUE);
117 END;
118 RETURN NULL;
119 END AFTERPFORM;
120
121 FUNCTION AFTERPARAMFORM RETURN BOOLEAN IS
122 BEGIN
123 IF (P_REQUEST_LOW IS NOT NULL) AND (P_REQUEST_HIGH IS NOT NULL) THEN
124 LP_REQUEST_NUMBER := 'AND (INC.incident_number BETWEEN ' || '''' || P_REQUEST_LOW || '''' || ' AND ' || '''' || P_REQUEST_HIGH || '''' || ')';
125 ELSIF (P_REQUEST_LOW IS NOT NULL) THEN
126 LP_REQUEST_NUMBER := 'AND INC.incident_number >= ' || '''' || P_REQUEST_LOW || '''' || '';
127 ELSIF (P_REQUEST_HIGH IS NOT NULL) THEN
128 LP_REQUEST_NUMBER := 'AND INC.incident_number <= ' || '''' || P_REQUEST_HIGH || '''' || '';
129 ELSE
130 LP_REQUEST_NUMBER := 'AND INC.incident_number like ' || '''%%''' || '';
131 END IF;
132 IF (LP_REQUEST_NUMBER IS NULL) THEN
133 LP_REQUEST_NUMBER := 'AND 1=1';
134 END IF;
135 IF (AFTERPFORM) THEN
136 NULL;
137 END IF;
138 RETURN (TRUE);
139 END AFTERPARAMFORM;
140
141 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
142 BEGIN
143 DECLARE
144 STATEMENT1 VARCHAR2(80);
145 STATEMENT2 VARCHAR2(80);
146 BEGIN
147 BEGIN
148 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
149 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
150 EXCEPTION
151 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
152 /*SRW.MESSAGE(1
153 ,'srw_init')*/NULL;
154 END;
155 BEGIN
156 /*SRW.REFERENCE(P_ORGANIZATION_ID)*/NULL;
157 P_ORGANIZATION_ID := FND_PROFILE.VALUE('SO_ORGANIZATION_ID');
158 END;
159 BEGIN
160 /*SRW.REFERENCE(P_ITEM_STRUCT_NUM)*/NULL;
161 EXCEPTION
162 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
163 /*SRW.MESSAGE(2
164 ,'Item Flex Select failed in before report trigger')*/NULL;
165 RAISE;
166 END;
167 BEGIN
168 /*SRW.REFERENCE(P_ITEM_STRUCT_NUM)*/NULL;
169 /*SRW.REFERENCE(P_FLEX_ITEM_CODE)*/NULL;
170 IF (P_ITEM_LOW IS NOT NULL) AND (P_ITEM_HIGH IS NOT NULL) THEN
171 P_ITEM_WHERE := ' AND ' || P_ITEM_WHERE;
172 ELSIF (P_ITEM_LOW IS NOT NULL) AND (P_ITEM_HIGH IS NULL) THEN
173 P_ITEM_WHERE := ' AND ' || P_ITEM_WHERE;
174 ELSIF (P_ITEM_LOW IS NULL) AND (P_ITEM_HIGH IS NOT NULL) THEN
175 P_ITEM_WHERE := ' AND ' || P_ITEM_WHERE;
176 ELSE
177 NULL;
178 END IF;
179 IF (P_ITEM_WHERE IS NULL) THEN
180 P_ITEM_WHERE :='AND 1=1';
181 END IF;
182 EXCEPTION
183 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
184 /*SRW.MESSAGE(3
185 ,'Item Flex Where failed in before report trigger')*/NULL;
186 RAISE;
187 END;
188 BEGIN
189 /*SRW.REFERENCE(P_USER_ID)*/NULL;
190 P_USER_ID := FND_GLOBAL.USER_ID;
191 END;
192 CS_GET_COMPANY_NAME(RP_COMPANY_NAME
193 ,P_SOB_ID);
194 CS_GET_REPORT_NAME(RP_REPORT_NAME
195 ,P_CONC_REQUEST_ID
196 ,'Service Request SUmmary Report');
197 END;
198 RP_REPORT_NAME := substr(RP_REPORT_NAME,1,instr(RP_REPORT_NAME,' (XML)'));
199
200 RETURN (TRUE);
201 END BEFOREREPORT;
202
203 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
204 BEGIN
205 RETURN RP_REPORT_NAME;
206 END RP_REPORT_NAME_P;
207
208 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
209 BEGIN
210 RETURN RP_COMPANY_NAME;
211 END RP_COMPANY_NAME_P;
212
213 /*PROCEDURE PUT(NAME IN VARCHAR2
214 ,VAL IN VARCHAR2) IS
215 BEGIN
216 STPROC.INIT('begin FND_PROFILE.PUT(:NAME, :VAL); end;');
217 STPROC.BIND_I(NAME);
218 STPROC.BIND_I(VAL);
219 STPROC.EXECUTE;
220 END PUT;*/
221
222 /* FUNCTION DEFINED(NAME IN VARCHAR2) RETURN BOOLEAN IS
223 X0 BOOLEAN;
224 BEGIN
225 STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.DEFINED(:NAME); :X0 := sys.diutil.bool_to_int(X0rv); end;');
226 STPROC.BIND_I(NAME);
227 STPROC.BIND_O(X0);
228 STPROC.EXECUTE;
229 STPROC.RETRIEVE(2
230 ,X0);
231 RETURN X0;
232 END DEFINED;*/
233
234 /* PROCEDURE GET(NAME IN VARCHAR2
235 ,VAL OUT NOCOPY VARCHAR2) IS
236 BEGIN
237 STPROC.INIT('begin FND_PROFILE.GET(:NAME, :VAL); end;');
238 STPROC.BIND_I(NAME);
239 STPROC.BIND_O(VAL);
240 STPROC.EXECUTE;
241 STPROC.RETRIEVE(2
242 ,VAL);
243 END GET;*/
244
245 /* FUNCTION VALUE(NAME IN VARCHAR2) RETURN VARCHAR2 IS
246 X0 VARCHAR2(2000);
247 BEGIN
248 STPROC.INIT('begin :X0 := FND_PROFILE.VALUE(:NAME); end;');
249 STPROC.BIND_O(X0);
250 STPROC.BIND_I(NAME);
251 STPROC.EXECUTE;
252 STPROC.RETRIEVE(1
253 ,X0);
254 RETURN X0;
255 END VALUE;
256 */
257 /* FUNCTION VALUE_WNPS(NAME IN VARCHAR2) RETURN VARCHAR2 IS
258 X0 VARCHAR2(2000);
259 BEGIN
260 STPROC.INIT('begin :X0 := FND_PROFILE.VALUE_WNPS(:NAME); end;');
261 STPROC.BIND_O(X0);
262 STPROC.BIND_I(NAME);
263 STPROC.EXECUTE;
264 STPROC.RETRIEVE(1
265 ,X0);
266 RETURN X0;
267 END VALUE_WNPS;*/
268
269 /* FUNCTION SAVE_USER(X_NAME IN VARCHAR2
270 ,X_VALUE IN VARCHAR2) RETURN BOOLEAN IS
271 X0 BOOLEAN;
272 BEGIN
273 STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE_USER(:X_NAME, :X_VALUE); :X0 := sys.diutil.bool_to_int(X0rv); end;');
274 STPROC.BIND_I(X_NAME);
275 STPROC.BIND_I(X_VALUE);
276 STPROC.BIND_O(X0);
277 STPROC.EXECUTE;
278 STPROC.RETRIEVE(3
279 ,X0);
280 RETURN X0;
281 END SAVE_USER;*/
282
283 /* FUNCTION SAVE(X_NAME IN VARCHAR2
284 ,X_VALUE IN VARCHAR2
285 ,X_LEVEL_NAME IN VARCHAR2
286 ,X_LEVEL_VALUE IN VARCHAR2
287 ,X_LEVEL_VALUE_APP_ID IN VARCHAR2) RETURN BOOLEAN IS
288 X0 BOOLEAN;
289 BEGIN
290 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;');
291 STPROC.BIND_I(X_NAME);
292 STPROC.BIND_I(X_VALUE);
293 STPROC.BIND_I(X_LEVEL_NAME);
294 STPROC.BIND_I(X_LEVEL_VALUE);
295 STPROC.BIND_I(X_LEVEL_VALUE_APP_ID);
296 STPROC.BIND_O(X0);
297 STPROC.EXECUTE;
298 STPROC.RETRIEVE(6
299 ,X0);
300 RETURN X0;
301 END SAVE;*/
302
303 /* PROCEDURE GET_SPECIFIC(NAME_Z IN VARCHAR2
304 ,USER_ID_Z IN NUMBER
305 ,RESPONSIBILITY_ID_Z IN NUMBER
306 ,APPLICATION_ID_Z IN NUMBER
307 ,VAL_Z OUT NOCOPY VARCHAR2
308 ,DEFINED_Z OUT NOCOPY BOOLEAN) IS
309 BEGIN
310 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);
311 :DEFINED_Z := sys.diutil.bool_to_int(DEFINED_Z); end;');
312 STPROC.BIND_O(DEFINED_Z);
313 STPROC.BIND_I(NAME_Z);
314 STPROC.BIND_I(USER_ID_Z);
315 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
316 STPROC.BIND_I(APPLICATION_ID_Z);
317 STPROC.BIND_O(VAL_Z);
318 STPROC.EXECUTE;
319 STPROC.RETRIEVE(1
320 ,DEFINED_Z);
321 STPROC.RETRIEVE(6
322 ,VAL_Z);
323 END GET_SPECIFIC;*/
324
325 /* FUNCTION VALUE_SPECIFIC(NAME IN VARCHAR2
326 ,USER_ID IN NUMBER
327 ,RESPONSIBILITY_ID IN NUMBER
328 ,APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
329 X0 VARCHAR2(2000);
330 BEGIN
331 STPROC.INIT('begin :X0 := FND_PROFILE.VALUE_SPECIFIC(:NAME, :USER_ID, :RESPONSIBILITY_ID, :APPLICATION_ID); end;');
332 STPROC.BIND_O(X0);
333 STPROC.BIND_I(NAME);
334 STPROC.BIND_I(USER_ID);
335 STPROC.BIND_I(RESPONSIBILITY_ID);
336 STPROC.BIND_I(APPLICATION_ID);
337 STPROC.EXECUTE;
338 STPROC.RETRIEVE(1
339 ,X0);
340 RETURN X0;
341 END VALUE_SPECIFIC;*/
342
343 /* PROCEDURE INITIALIZE(USER_ID_Z IN NUMBER
344 ,RESPONSIBILITY_ID_Z IN NUMBER
345 ,APPLICATION_ID_Z IN NUMBER
349 STPROC.BIND_I(USER_ID_Z);
346 ,SITE_ID_Z IN NUMBER) IS
347 BEGIN
348 STPROC.INIT('begin FND_PROFILE.INITIALIZE(:USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :SITE_ID_Z); end;');
350 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
351 STPROC.BIND_I(APPLICATION_ID_Z);
352 STPROC.BIND_I(SITE_ID_Z);
353 STPROC.EXECUTE;
354 END INITIALIZE;*/
355
356 /* PROCEDURE PUTMULTIPLE(NAMES IN VARCHAR2
357 ,VALS IN VARCHAR2
358 ,NUM IN NUMBER) IS
359 BEGIN
360 STPROC.INIT('begin FND_PROFILE.PUTMULTIPLE(:NAMES, :VALS, :NUM); end;');
361 STPROC.BIND_I(NAMES);
362 STPROC.BIND_I(VALS);
363 STPROC.BIND_I(NUM);
364 STPROC.EXECUTE;
365 END PUTMULTIPLE;*/
366
367 PROCEDURE CS_GET_COMPANY_NAME(RP_COMPANY_NAME IN OUT NOCOPY VARCHAR2
371 STPROC.BIND_IO(RP_COMPANY_NAME);
368 ,P_SOB_ID IN NUMBER) IS
369 BEGIN
370 /* STPROC.INIT('begin CS_REPORTS_PACKAGE.CS_GET_COMPANY_NAME(:RP_COMPANY_NAME, :P_SOB_ID); end;');
372 STPROC.BIND_I(P_SOB_ID);
373 STPROC.EXECUTE;
374 STPROC.RETRIEVE(1
375 ,RP_COMPANY_NAME);*/
376 begin
377 CS_REPORTS_PACKAGE.CS_GET_COMPANY_NAME(RP_COMPANY_NAME, P_SOB_ID);
378 end;
379
380
381 END CS_GET_COMPANY_NAME;
382
383 PROCEDURE CS_GET_REPORT_NAME(RP_REPORT_NAME IN OUT NOCOPY VARCHAR2
384 ,P_CONC_REQUEST_ID IN NUMBER
385 ,P_REPORT_NAME IN VARCHAR2) IS
386 BEGIN
387 /* STPROC.INIT('begin CS_REPORTS_PACKAGE.CS_GET_REPORT_NAME(:RP_REPORT_NAME, :P_CONC_REQUEST_ID, :P_REPORT_NAME); end;');
388 STPROC.BIND_IO(RP_REPORT_NAME);
389 STPROC.BIND_I(P_CONC_REQUEST_ID);
390 STPROC.BIND_I(P_REPORT_NAME);
391 STPROC.EXECUTE;
392 STPROC.RETRIEVE(1
393 ,RP_REPORT_NAME);*/
394 begin
395 CS_REPORTS_PACKAGE.CS_GET_REPORT_NAME(RP_REPORT_NAME, P_CONC_REQUEST_ID, P_REPORT_NAME);
396 end;
397
398 END CS_GET_REPORT_NAME;
399
400 /* PROCEDURE GET_P_STRUCT_NUM(P_ITEM_STRUCT_NUM IN OUT NOCOPY VARCHAR2
401 ,RETURN_VALUE IN OUT NOCOPY NUMBER) IS
402 BEGIN
403 STPROC.INIT('begin CS_REPORTS_PACKAGE.GET_P_STRUCT_NUM(:P_ITEM_STRUCT_NUM, :RETURN_VALUE); end;');
404 STPROC.BIND_IO(P_ITEM_STRUCT_NUM);
405 STPROC.BIND_IO(RETURN_VALUE);
406 STPROC.EXECUTE;
407 STPROC.RETRIEVE(1
408 ,P_ITEM_STRUCT_NUM);
409 STPROC.RETRIEVE(2
410 ,RETURN_VALUE);
411 END GET_P_STRUCT_NUM;*/
412
413 END CS_CSXSRDET_XMLP_PKG;
414
[Home] [Help]