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