[Home] [Help]
PACKAGE BODY: APPS.ZPB_EXCP_PVT
Source
1 PACKAGE body zpb_excp_pvt AS
2 /* $Header: ZPBVEXCB.pls 120.0.12010.5 2006/08/03 12:05:19 appldev noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(12) := 'zpb_excp_pvt';
6
7 -------------------------------------------------------------------------------
8
9 PROCEDURE run_exception (
10 p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
12 p_commit IN VARCHAR2 := FND_API.G_FALSE,
13 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
14 x_return_status OUT NOCOPY varchar2,
15 x_msg_count OUT NOCOPY number,
16 x_msg_data OUT NOCOPY varchar2,
17 p_task_id IN NUMBER,
18 p_user_id IN NUMBER )
19
20 IS
21
22 l_api_name CONSTANT VARCHAR2(13) := 'run_exception';
23 l_api_version CONSTANT NUMBER := 1.0;
24
25 l_exception_limit ZPB_TASK_PARAMETERS.value%type;
26 l_query_path ZPB_TASK_PARAMETERS.value%type;
27 l_query_name ZPB_TASK_PARAMETERS.value%type;
28 l_query VARCHAR2(8000);
29 l_user_id VARCHAR2(64);
30 l_task_id VARCHAR2(64);
31 l_dim ZPB_STATUS_SQL.dimension_name%type;
32 l_hier ZPB_STATUS_SQL.hierarchy_name%type;
33 l_count NUMBER;
34 l_excp_ct VARCHAR2(32766);
35
36 l_instance_id NUMBER;
37
38 cursor task_dfn is
39 select name, value
40 from zpb_task_parameters
41 where task_id = p_task_id
42 and ( name = EXCEPTION_LIMIT
43 or name = QUERY_OBJECT_PATH
44 or name = QUERY_OBJECT_NAME );
45
46 cursor statSqlMbrs is
47 select dimension_name dim, hierarchy_name hier
48 from zpb_status_sql
49 where (query_path = l_query);
50
51 BEGIN
52
53 -- Standard call to check for call compatibility.
54 IF NOT FND_API.Compatible_API_Call( l_api_version,
55 p_api_version,
56 l_api_name,
57 G_PKG_NAME)
58 THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF FND_API.to_Boolean(p_init_msg_list) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65 -- Initialize API return status to success
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 -- API body
69
70 l_user_id := to_char(p_user_id);
71 l_task_id := to_char(p_task_id);
72
73 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Running Exceptions for user ' || l_user_id || '.');
74
75 for each in task_dfn loop
76 if (each.name = EXCEPTION_LIMIT) then
77 l_exception_limit := each.value;
78 end if;
79 if (each.name = QUERY_OBJECT_PATH) then
80 l_query_path := each.value;
81 end if;
82 if (each.name = QUERY_OBJECT_NAME) then
83 l_query_name := each.value;
84 end if;
85 end loop;
86
87 l_query := l_query_path || '/' || l_query_name;
88
89
90
91 -- update FRM.CPR formula
92 select analysis_cycle_id into l_instance_id
93 from zpb_analysis_cycle_tasks
94 where task_id = p_task_id;
95
96 zpb_aw.execute('call SC.EXCEPCPRMOD(''' || to_char(l_instance_id) || ''')');
97
98 -- Standard Start of API savepoint
99 SAVEPOINT zpb_excp_pvt_run_exception;
100
101 --populate zpb_status_sql_members table
102 --ZPB_AW_STATUS.RUN_OLAPI_QUERIES( l_query );
103
104 --Test the query entry. The same dimension and hierarchy must exist for all rows.
105 l_count := 1;
106 for each in statSqlMbrs loop
107 if (l_count > 1) then
108 if (l_dim <> each.dim or l_hier <> each.hier) then
109 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'The specified query shows inconsistent results.');
110 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 --RAISE_APPLICATION_ERROR(-20000, 'The specified query shows inconsistent results.', TRUE);
112 end if;
113 else
114 l_dim := each.dim;
115 l_hier := each.hier;
116 end if;
117 l_count := l_count + 1;
118 end loop;
119
120 --call AW exception program
121 --dbms_output.put_line('call sc.exception.chk(' || '''' || l_task_id || ''' ''' || l_user_id || ''')' );
122
123 --Use for debug. Allows for use of show statements in OLAP DML. Be sure to comment out if statement
124 --below
125 --ZPB_AW.EXECUTE( 'call sc.exception.chk(' || '''' || l_task_id || ''' ''' || l_user_id || ''')' );
126 l_excp_ct := ZPB_AW.INTERP( 'shw sc.exception.chk(' || '''' || l_task_id || ''' ''' || l_user_id || ''')' );
127
128 --dbms_output.put_line('Exception count : ' || l_excp_ct);
129
130 -- add in owner and approver names (need to update who columns)
131 if l_excp_ct<>'NA' and l_excp_ct<>' ' then
132
133 if to_number(l_excp_ct) > 0 then
134
135 update zpb_excp_results z
136 set owner = (
137 select user_name
138 from fnd_user f
139 where f.user_id = z.owner_id),
140 LAST_UPDATED_BY = fnd_global.USER_ID,
141 LAST_UPDATE_DATE = SYSDATE,
142 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
143 where task_id = l_task_id;
144
145 update zpb_excp_results z
146 set approver = (
147 select user_name
148 from fnd_user f
149 where f.user_id = z.approver_id),
150 LAST_UPDATED_BY = fnd_global.USER_ID,
151 LAST_UPDATE_DATE = SYSDATE,
152 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
153 where task_id = l_task_id;
154 end if;
155 end if;
156
157 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Exceptions check completed for user ' || l_user_id || '.');
158
159 -- End of API body.
160
161 -- Standard check of p_commit.
162 IF FND_API.To_Boolean( p_commit ) THEN
163 COMMIT WORK;
164 END IF;
165 -- Standard call to get message count and if count is 1, get message info.
166 FND_MSG_PUB.Count_And_Get(
167 p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170
171 EXCEPTION
172 WHEN FND_API.G_EXC_ERROR THEN
173 ROLLBACK TO zpb_excp_pvt_run_exception;
174 x_return_status := FND_API.G_RET_STS_ERROR;
175 FND_MSG_PUB.Count_And_Get(
176 p_count => x_msg_count,
177 p_data => x_msg_data
178 );
179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
180 ROLLBACK TO zpb_excp_pvt_run_exception;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 FND_MSG_PUB.Count_And_Get(
183 p_count => x_msg_count,
184 p_data => x_msg_data
185 );
186 WHEN OTHERS THEN
187 ROLLBACK TO zpb_excp_pvt_run_exception;
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
190 FND_MSG_PUB.Add_Exc_Msg(
191 G_PKG_NAME,
192 l_api_name
193 );
194 END IF;
195 FND_MSG_PUB.Count_And_Get(
196 p_count => x_msg_count,
197 p_data => x_msg_data
198 );
199
200 END run_exception;
201
202 -------------------------------------------------------------------------------
203
204 PROCEDURE test_run_exception
205
206 is
207
208 return_status varchar2(32766);
209 msg_count number;
210 msg_data varchar2(32766);
211
212 begin
213
214 dbms_output.put_line('start run exception test');
215 run_exception(1.0, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, return_status, msg_count, msg_data, 219, 1005156);
216 dbms_output.put_line(return_status);
217 dbms_output.put_line(msg_count);
218 --dbms_output.put_line(msg_data);
219 dbms_output.put_line('run exception test complete');
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 raise;
224 end test_run_exception;
225
226 ----------------------------------------------------------------------------------
227
228 procedure request_child_nodes(
229 p_api_version IN NUMBER,
230 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
231 p_commit IN VARCHAR2 := FND_API.G_FALSE,
232 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY number,
235 x_msg_data OUT NOCOPY varchar2,
236 p_task_id IN zpb_excp_explanations.task_id%type,
237 p_notification_id IN zpb_excp_explanations.notification_id%type)
238
239 IS
240
241 l_api_name CONSTANT VARCHAR2(30) := 'request_child_nodes';
242 l_api_version CONSTANT NUMBER := 1.0;
243
244 BEGIN
245
246 -- Standard Start of API savepoint
247 SAVEPOINT zpb_excp_request_child_nodes;
248 -- Standard call to check for call compatibility.
249 IF NOT FND_API.Compatible_API_Call( l_api_version,
250 p_api_version,
251 l_api_name,
252 G_PKG_NAME)
253 THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF FND_API.to_Boolean(p_init_msg_list) THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260 -- Initialize API return status to success
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262
263 -- API body
264
265 -- clean up from any previous requests
266 delete from zpb_excp_explanations
267 where task_id = p_task_id
268 and notification_id = p_notification_id;
269
270
271 insert into zpb_excp_explanations(NOTIFICATION_ID, TASK_ID, MEMBER_ID, MEMBER_DISPLAY,
272 OWNER_ID, OWNER, APPROVER_ID, APPROVER, STATUS, VALUE_flag, value_number,
273 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
274 (select p_notification_id, e.TASK_ID, e.MEMBER_ID, e.MEMBER_DISPLAY,
275 e.OWNER_ID, e.OWNER, e.APPROVER_ID, e.APPROVER, g_req_child_nodes, e.VALUE_flag, e.value_number,
276 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
277 from zpb_excp_explanations e
278 where e.task_id = p_task_id
279 and e.owner_id = nvl(sys_context('ZPB_CONTEXT', 'shadow_id'), fnd_global.user_id));
280
281 insert into zpb_excp_explanations(NOTIFICATION_ID, TASK_ID, MEMBER_ID, MEMBER_DISPLAY,
282 OWNER_ID, OWNER, APPROVER_ID, APPROVER, STATUS, VALUE_flag, value_number,
283 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
284 (select p_notification_id, e.TASK_ID, e.MEMBER_ID, e.MEMBER_DISPLAY,
285 e.OWNER_ID, e.OWNER, e.APPROVER_ID, e.APPROVER, g_req_child_nodes, e.VALUE_flag, e.value_number,
286 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
287 from zpb_excp_results e
288 where e.task_id = p_task_id
289 and e.owner_id = nvl(sys_context('ZPB_CONTEXT', 'shadow_id'), fnd_global.user_id));
290
291 -- End of API body.
292
293 -- Standard check of p_commit.
294 IF FND_API.To_Boolean( p_commit ) THEN
295 COMMIT WORK;
296 END IF;
297 -- Standard call to get message count and if count is 1, get message info.
298 FND_MSG_PUB.Count_And_Get(
299 p_count => x_msg_count,
300 p_data => x_msg_data
301 );
302
303 EXCEPTION
304 WHEN FND_API.G_EXC_ERROR THEN
305 ROLLBACK TO zpb_excp_populate_child_nodes;
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 FND_MSG_PUB.Count_And_Get(
308 p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
312 ROLLBACK TO zpb_excp_populate_child_nodes;
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 FND_MSG_PUB.Count_And_Get(
315 p_count => x_msg_count,
316 p_data => x_msg_data
317 );
318 WHEN OTHERS THEN
319 ROLLBACK TO zpb_excp_populate_child_nodes;
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
321 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
322 FND_MSG_PUB.Add_Exc_Msg(
323 G_PKG_NAME,
324 l_api_name
325 );
326 END IF;
327 FND_MSG_PUB.Count_And_Get(
328 p_count => x_msg_count,
329 p_data => x_msg_data
330 );
331
332 END request_child_nodes;
333
334 ----------------------------------------------------------------------------------
335
336 PROCEDURE request_children (
337 p_api_version IN NUMBER,
338 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
339 p_commit IN VARCHAR2 := FND_API.G_FALSE,
340 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
341 x_return_status OUT nocopy varchar2,
342 x_msg_count OUT nocopy number,
343 x_msg_data OUT nocopy varchar2,
344 p_notification_id IN zpb_excp_explanations.notification_id%type,
345 p_task_id IN zpb_excp_explanations.task_id%type )
346
347 IS
348
349 l_api_name CONSTANT VARCHAR2(30) := 'request_children';
350 l_api_version CONSTANT NUMBER := 1.0;
351
352 l_query_path ZPB_TASK_PARAMETERS.value%type;
353 l_query_name ZPB_TASK_PARAMETERS.value%type;
354 l_query VARCHAR2(8000);
355 l_user_id NUMBER;
356 l_resp_id NUMBER;
357 l_resp_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%type;
358 l_task_id VARCHAR2(16);
359 l_dim ZPB_STATUS_SQL.dimension_name%type;
360 l_hier ZPB_STATUS_SQL.hierarchy_name%type;
361 l_count NUMBER;
362 l_child_ct VARCHAR2(2000);
363
364 cursor task_dfn is
365 select name, value
366 from zpb_task_parameters
367 where task_id = p_task_id
368 and ( name = QUERY_OBJECT_PATH
369 or name = QUERY_OBJECT_NAME );
370
371 cursor statSqlMbrs is
372 select dimension_name dim, hierarchy_name hier
373 from zpb_status_sql
374 where (query_path = l_query);
375
376 BEGIN
377
378 -- Standard Start of API savepoint
379 SAVEPOINT zpb_excp_pvt_run_exception;
380 -- Standard call to check for call compatibility.
381 IF NOT FND_API.Compatible_API_Call( l_api_version,
382 p_api_version,
383 l_api_name,
384 G_PKG_NAME)
385 THEN
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387 END IF;
388 -- Initialize message list if p_init_msg_list is set to TRUE.
389 IF FND_API.to_Boolean(p_init_msg_list) THEN
390 FND_MSG_PUB.initialize;
391 END IF;
392 -- Initialize API return status to success
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 -- API body
396 l_user_id := FND_GLOBAL.USER_ID;
397 l_resp_id := FND_GLOBAL.RESP_ID;
398
399 --l_user_id := 1005262;
400 --l_resp_id := 57124;
401 --dbms_output.put_line(l_user_id);
402 --dbms_output.put_line(l_resp_id);
403
404 select responsibility_key into l_resp_key
405 from fnd_responsibility
406 where responsibility_id = l_resp_id;
407
408 l_task_id := to_char(p_task_id);
409 --dbms_output.put_line(l_task_id);
410
411 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Finding children for task_id ' || p_task_id || ' and notification_id ' || p_notification_id || '.');
412
413 for each in task_dfn loop
414 if (each.name = QUERY_OBJECT_PATH) then
415 l_query_path := each.value;
416 end if;
417 if (each.name = QUERY_OBJECT_NAME) then
418 l_query_name := each.value;
419 end if;
420 end loop;
421
422 l_query := l_query_path || '/' || l_query_name;
423
424 --dbms_output.put_line(l_query);
425
426 --
427 --Removed the following call, as this should be called on the OLAP connection
428 --ZPB_AW.INITIALIZE_WORKSPACE(1.0, FND_API.G_FALSE, p_validation_level, x_return_status, x_msg_count, x_msg_data, l_user_id, l_resp_key);
429
430 --populate zpb_status_sql_members table
431 --ZPB_AW_STATUS.RUN_OLAPI_QUERIES( l_query );
432
433 --test the OLAPI query results. The same dimension and hierarchy must exist for all rows.
434 l_count := 1;
435 for each in statSqlMbrs loop
436 if (l_count > 1) then
437 if (l_dim <> each.dim or l_hier <> each.hier) then
438 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'The specified query shows inconsistent results.');
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
440 --RAISE_APPLICATION_ERROR(-20000, 'The specified query shows inconsistent results.', TRUE);
441 end if;
442 else
443 l_dim := each.dim;
444 l_hier := each.hier;
445 end if;
446 l_count := l_count + 1;
447 end loop;
448
449 --dbms_output.put_line('query line count : ' || to_char(l_count));
450
451 --dbms_output.put_line('call sc.exception.exp(' || '''' || p_task_id || ''' ''' || p_notification_id || ''')' );
452 l_child_ct := ZPB_AW.INTERP( 'shw sc.exception.exp(' || '''' || to_char(p_task_id) || ''' ''' || to_char(p_notification_id) || ''')' );
453 --dbms_output.put_line('Child count : ' || l_child_ct);
454
455 ZPB_AW.CLEAN_WORKSPACE(1.0, FND_API.G_FALSE, p_validation_level, x_return_status, x_msg_count, x_msg_data);
456
457 -- add in owner names (need to update who columns)
458 if to_number(l_child_ct) > 0 then
459 update zpb_excp_explanations z
460 set owner = (
461 select user_name
462 from fnd_user f
463 where f.user_id = z.owner_id),
464 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
465 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
466 where task_id = p_task_id
467 and notification_id = p_notification_id;
468 end if;
469
470 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, l_child_ct || ' children found for task_id ' || p_task_id || ' and notification_id ' || p_notification_id || '.');
471
472 -- End of API body.
473
474 -- Standard check of p_commit.
475 IF FND_API.To_Boolean( p_commit ) THEN
476 COMMIT WORK;
477 END IF;
478 -- Standard call to get message count and if count is 1, get message info.
479 FND_MSG_PUB.Count_And_Get(
480 p_count => x_msg_count,
481 p_data => x_msg_data
482 );
483
484 EXCEPTION
485 WHEN FND_API.G_EXC_ERROR THEN
486 ROLLBACK TO zpb_excp_pvt_run_exception;
487 x_return_status := FND_API.G_RET_STS_ERROR;
488 FND_MSG_PUB.Count_And_Get(
489 p_count => x_msg_count,
490 p_data => x_msg_data
491 );
492 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
493 ROLLBACK TO zpb_excp_pvt_run_exception;
494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
495 FND_MSG_PUB.Count_And_Get(
496 p_count => x_msg_count,
497 p_data => x_msg_data
498 );
499 WHEN OTHERS THEN
500 ROLLBACK TO zpb_excp_pvt_run_exception;
501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
502 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
503 FND_MSG_PUB.Add_Exc_Msg(
504 G_PKG_NAME,
505 l_api_name
506 );
507 END IF;
508 FND_MSG_PUB.Count_And_Get(
509 p_count => x_msg_count,
510 p_data => x_msg_data
511 );
512
513 END request_children;
514 -------------------------------------------------------------------------------
515
516 PROCEDURE test_req_child
517
518 is
519
520 return_status varchar2(4000);
521 msg_count number;
522 msg_data varchar2(4000);
523 msg_out varchar2(2000);
524 i number;
525
526 begin
527
528 dbms_output.put_line('start request children test');
529 request_children(1.0, FND_API.G_TRUE, FND_API.G_TRUE, FND_API.G_VALID_LEVEL_FULL, return_status, msg_count, msg_data, 10000, 9087);
530 dbms_output.put_line(return_status);
531 dbms_output.put_line(msg_count);
532 --dbms_output.put_line(msg_data);
533 --i := 1;
534 --select fnd_msg_pub.get(-1) into msg_out from dual;
535 --dbms_output.put_line(msg_out);
536 --while i < msg_count loop
537 -- select fnd_msg_pub.get(-2) into msg_out from dual;
538 -- dbms_output.put_line(msg_out);
539 -- i := i + 1;
540 --end loop;
541 dbms_output.put_line('request children test complete');
542
543 EXCEPTION
544 WHEN OTHERS THEN
545 raise;
546 end test_req_child;
547 -------------------------------------------------------------------------------
548
549
550 END zpb_excp_pvt;