1 PACKAGE BODY msc_ask_oracle AS
2 /*$Header: MSCASKOB.pls 120.1 2005/06/17 13:13:39 appldev $ */
3
4 -- This procedure is called from the concurrent program to register the
5 -- questions for Ask Oracle via SRS
6 PROCEDURE register_question(
7 errbuf OUT NoCopy VARCHAR2,
8 retcode OUT NoCopy VARCHAR2,
9 x_mode IN NUMBER,
10 x_question_code IN VARCHAR2,
11 x_question_type IN VARCHAR2 DEFAULT NULL,
12 x_lang_code IN VARCHAR2 DEFAULT NULL,
13 x_question IN VARCHAR2 DEFAULT NULL,
14 x_package_name IN VARCHAR2 DEFAULT NULL,
15 x_copy_question IN VARCHAR2 DEFAULT NULL)
16 IS
17 x_question_id number := null;
18 BEGIN
19 msc_util.msc_debug('******** Start of Program ********');
20 msc_util.msc_debug('Mode:'|| to_char(x_mode));
21 msc_util.msc_debug('QuestionType:'|| x_question_type);
22 msc_util.msc_debug('QuestionCode:'|| x_question_code);
23 msc_util.msc_debug('Question:'|| x_question);
24 msc_util.msc_debug('Language:'|| x_lang_code);
25 msc_util.msc_debug('PackageName:'|| x_package_name);
26 msc_util.msc_debug('Copy Question:'|| x_copy_question);
27 msc_util.msc_debug('**********************************');
28 -- There are multiple modes
29 -- (1) new question, so insert data
30 -- (2) copy question from a existing question, so insert data
31 -- (3) update question, so update data
32 -- (4) delete question, so delete data
33 IF (x_mode = 1) THEN
34 -- First insert into msc_questions_b and then into msc_questions_tl
35 -- Note that the sequence will ensure that all question_ids are
36 -- greater than 5001
37 SELECT msc_questions_s.nextval
38 INTO x_question_id
39 FROM dual;
40 msc_util.msc_debug('QuestionId:'|| to_char(x_question_id));
41
42 INSERT INTO MSC_QUESTIONS_B(
43 QUESTION_ID
44 ,ANSWER_ID
45 ,QUESTION_CODE
46 ,QUESTION_TYPE
47 ,PACKAGE_NAME
48 ,LAST_UPDATE_DATE
49 ,LAST_UPDATED_BY
50 ,CREATION_DATE
51 ,CREATED_BY
52 ,LAST_UPDATE_LOGIN )
53 SELECT
54 x_question_id
55 ,null
56 ,x_question_code
57 ,x_question_type
58 ,x_package_name
59 ,sysdate
60 ,fnd_global.user_id
61 ,sysdate
62 ,fnd_global.user_id
63 ,fnd_global.conc_login_id
64 FROM dual
65 WHERE not exists (select 'already exists'
66 from msc_questions_b
67 where question_code = x_question_code);
68 msc_util.msc_debug('Inserted into msc_questions_b:'|| sql%rowcount);
69
70 INSERT INTO MSC_QUESTIONS_TL(
71 QUESTION_ID
72 ,LANGUAGE
73 ,USER_QUESTION_NAME
74 ,DESCRIPTION
75 ,SOURCE_LANG
76 ,TRANSLATED
77 ,LAST_UPDATE_DATE
78 ,LAST_UPDATED_BY
79 ,CREATION_DATE
80 ,CREATED_BY
81 ,LAST_UPDATE_LOGIN )
82 SELECT
83 x_question_id
84 ,x_lang_code
85 ,x_question
86 ,NULL
87 ,x_lang_code
88 ,NULL
89 ,sysdate
90 ,fnd_global.user_id
91 ,sysdate
92 ,fnd_global.user_id
93 ,fnd_global.conc_login_id
94 FROM dual
95 WHERE not exists
96 (select 'already exists'
97 from msc_questions_tl
98 where question_id = (select question_id
99 from msc_questions_b
100 where question_code = x_question_code)
101 and language = x_lang_code);
102
103 msc_util.msc_debug('Inserted msc_questions_tl:' || to_char(sql%rowcount));
104 ELSIF (x_mode = 2) THEN -- copy mode
105 SELECT msc_questions_s.nextval
106 INTO x_question_id
107 FROM dual;
108 msc_util.msc_debug('QuestionId:'|| to_char(x_question_id));
109
110 INSERT INTO MSC_QUESTIONS_B(
111 QUESTION_ID
112 ,ANSWER_ID
113 ,QUESTION_CODE
114 ,QUESTION_TYPE
115 ,PACKAGE_NAME
116 ,LAST_UPDATE_DATE
117 ,LAST_UPDATED_BY
118 ,CREATION_DATE
119 ,CREATED_BY
120 ,LAST_UPDATE_LOGIN )
121 SELECT
122 x_question_id
123 ,null
124 ,x_question_code
125 ,q.question_type
126 ,q.package_name
127 ,sysdate
128 ,fnd_global.user_id
129 ,sysdate
130 ,fnd_global.user_id
131 ,fnd_global.conc_login_id
132 FROM msc_questions_b q
133 WHERE q.question_code = x_copy_question
134 AND not exists (select 'already exists'
135 from msc_questions_b
136 where question_code = x_question_code);
137 msc_util.msc_debug('Inserted into msc_questions_b:'|| sql%rowcount);
138
139 INSERT INTO MSC_QUESTIONS_TL(
140 QUESTION_ID
141 ,LANGUAGE
142 ,USER_QUESTION_NAME
143 ,DESCRIPTION
144 ,SOURCE_LANG
145 ,TRANSLATED
146 ,LAST_UPDATE_DATE
147 ,LAST_UPDATED_BY
148 ,CREATION_DATE
149 ,CREATED_BY
150 ,LAST_UPDATE_LOGIN )
151 SELECT
152 x_question_id
153 ,q.language
154 ,x_question
155 ,NULL
156 ,x_lang_code
157 ,NULL
158 ,sysdate
159 ,fnd_global.user_id
160 ,sysdate
161 ,fnd_global.user_id
162 ,fnd_global.conc_login_id
163 FROM msc_questions_tl q
164 WHERE q.language = x_lang_code
165 AND q.question_id = (select question_id
166 from msc_questions_b
167 x where question_code = x_copy_question)
168 AND not exists
169 (select 'already exists'
170 from msc_questions_tl
171 where question_id = (select question_id
172 from msc_questions_b
173 where question_code = x_question_code)
174 and language = x_lang_code);
175
176 msc_util.msc_debug('Inserted msc_questions_tl:' || to_char(sql%rowcount));
177 ELSIF (x_mode = 3) THEN -- update mode
178 update msc_questions_b
179 set package_name = x_package_name
180 where question_code = x_question_code;
181 msc_util.msc_debug('Updated msc_questions_b:' || to_char(sql%rowcount) ||
182 ':'|| x_question_code ||'with package name value:' ||
183 x_package_name);
184
185 update msc_questions_tl
186 set user_question_name = x_question
187 where question_id = (select question_id
188 from msc_questions_b
189 where question_code = x_question_code)
190 and language = x_lang_code;
191 msc_util.msc_debug('Updated msc_questions_tl:' || to_char(sql%rowcount) ||
192 ':' || x_question_code || 'with user question:' ||
193 x_question);
194 ELSIF (x_mode = 4) THEN -- delete mode
195 delete msc_questions_tl
196 where question_id = (select question_id
197 from msc_questions_b
198 where question_code = x_question_code);
199 msc_util.msc_debug('Deleted msc_questions_tl:'|| to_char(sql%rowcount) ||
200 ':' || x_question_code);
201
202 delete msc_questions_b
203 where question_code = x_question_code;
204 msc_util.msc_debug('Deleted msc_questions_b:'|| to_char(sql%rowcount) ||
205 ':' || x_question_code);
206 END IF;
207 COMMIT WORK;
208 retcode := 0;
209 return;
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 errbuf := 'MSC_ASK_ORACLE.register_question:' || to_char(sqlcode) || ':'
214 || substr(sqlerrm,1,60);
215 retcode := 2;
216 END register_question;
217
218 FUNCTION ask(
219 x_question_id IN NUMBER,
220 x_question_type IN VARCHAR2,
221 x_question IN VARCHAR2,
222 x_plan_id IN NUMBER,
223 x_key1 IN NUMBER DEFAULT NULL,
224 x_key2 IN NUMBER DEFAULT NULL,
225 x_key3 IN NUMBER DEFAULT NULL,
226 x_key4 IN NUMBER DEFAULT NULL,
227 x_answer_id OUT NoCopy NUMBER,
228 x_err_msg OUT NoCopy VARCHAR2,
229 x_msg_count OUT NoCopy NUMBER)
230 RETURN BOOLEAN
231 IS
232 p_answer_id NUMBER;
233 p_err_msg VARCHAR2(2000);
234 p_msg_count NUMBER;
235 x_stmt_num NUMBER := 0;
236 v_sql_stmt VARCHAR2(400);
237 x_question_code VARCHAR2(25);
238 x_pkg_name VARCHAR2(25);
239 BEGIN
240 -- First do the fuzzy match
241 IF (x_question_id IS NULL and x_question is NOT NULL) THEN
242 x_stmt_num := 10;
243 ELSE
244 -- Setup the package variables
245 x_stmt_num := 20;
246 v_question_id := x_question_id;
247 v_question_type := x_question_type;
248 v_question_code := x_question_code;
249 v_question := x_question;
250 v_plan_id := x_plan_id;
251 v_seq_num := 0;
252 v_key1 := x_key1;
253 v_key2 := x_key2;
254
255 -- Call the appropriate procedure to process this questions
256 x_stmt_num := 30;
257 SELECT NVL(package_name,'MSC_ASK_ORACLE'), question_code
258 INTO x_pkg_name, x_question_code
259 FROM msc_questions_b
260 WHERE question_id = x_question_id;
261
262 x_stmt_num := 40;
263 v_sql_stmt := 'BEGIN' || ' ' ||
264 x_pkg_name || '.' || x_question_code ||
265 '(:p_answer_id, :p_err_msg, :p_msg_count);' ||
266 'END;';
267 x_stmt_num := 50;
268 -- dbms_output.put_line(x_stmt_num || ':' || v_sql_stmt);
269 EXECUTE IMMEDIATE v_sql_stmt USING IN OUT
270 p_answer_id,IN OUT p_err_msg, IN OUT p_msg_count;
271
272 x_stmt_num := 60;
273 x_answer_id := p_answer_id;
274 x_err_msg := p_err_msg;
275 x_msg_count := p_msg_count;
276
277 return(TRUE);
278 END IF;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 x_err_msg := 'MSC_ASK_ORACLE.ASK:' || to_char(x_stmt_num) || ':' ||
283 to_char(sqlcode) || ':' || substr(sqlerrm,1,60);
284 return(FALSE);
285 END ask;
286
287 PROCEDURE get_question(
288 x_question_id OUT NoCopy NUMBER,
289 x_question_type OUT NoCopy VARCHAR2,
290 x_question_code OUT NoCopy NUMBER)
291 IS
292 BEGIN
293 x_question_id := v_question_id;
294 x_question_code := v_question_code;
295 x_question_type := v_question_type;
296 return;
297 END get_question;
298
299 PROCEDURE get_key(
300 x_question_type OUT NoCopy VARCHAR2,
301 x_plan_id OUT NoCopy NUMBER,
302 x_key1 OUT NoCopy NUMBER,
303 x_key2 OUT NoCopy NUMBER)
304 IS
305 BEGIN
306 x_question_type := v_question_type;
307 x_plan_id := v_plan_id;
308 x_key1 := v_key1;
309 x_key2 := v_key2;
310 return;
311 END get_key;
312
313 PROCEDURE get_answer_id(
314 answer_id OUT NoCopy NUMBER)
315 IS
316 BEGIN
317 SELECT msc_answers_s.nextval
318 INTO answer_id
319 FROM sys.dual;
320
321 return;
322 EXCEPTION
323 WHEN OTHERS THEN
324 raise;
325 END get_answer_id;
326
327 PROCEDURE get_sequence(
328 seq_num OUT NoCopy NUMBER)
329 IS
330 BEGIN
331 v_seq_num := v_seq_num + 5;
332 seq_num := v_seq_num;
333 return;
334 EXCEPTION
335 WHEN OTHERS THEN
336 raise;
337 END get_sequence;
338
339 PROCEDURE insert_answer(
340 a_id IN NUMBER,
341 q_id IN NUMBER,
342 seq IN NUMBER,
343 ans IN VARCHAR2)
344 IS
345 BEGIN
346 -- dbms_output.put_line('Inserting answer:'|| ans);
347 INSERT INTO MSC_ANSWERS(
348 ANSWER_ID,
349 QUESTION_ID,
350 SEQ_NUM,
351 SESSION_ID,
352 ANSWER_TEXT,
353 LAST_UPDATE_DATE,
354 LAST_UPDATED_BY,
355 CREATION_DATE,
356 CREATED_BY)
357 SELECT
358 a_id,
359 q_id,
360 seq,
361 USERENV('SESSIONID'),
362 ans,
363 sysdate,
364 FND_GLOBAL.USER_ID,
365 sysdate,
366 FND_GLOBAL.USER_ID
367 FROM sys.dual;
368
369 return;
370 EXCEPTION
371 WHEN OTHERS THEN
372 -- dbms_output.put_line('Error in inserting answer:'|| to_char(sqlcode));
373 raise;
374 END insert_answer;
375
376 PROCEDURE late_prj(
377 x_answer_id IN OUT NoCopy NUMBER,
378 x_err_msg IN OUT NoCopy VARCHAR2,
379 x_msg_count IN OUT NoCopy NUMBER)
380 IS
381 l_plan NUMBER;
382 l_qtype VARCHAR2(10);
383 l_key1 NUMBER;
384 l_key2 NUMBER;
385 l_prj VARCHAR2(100);
386 l_task VARCHAR2(100);
387 l_ans VARCHAR2(100);
388 l_es DATE;
389 l_ef DATE;
390 l_ls DATE;
391 l_lf DATE;
392 l_ss DATE;
393 l_sf DATE;
394 l_due_date DATE;
395 BEGIN
396 -- Get the keys
397 get_key(l_qtype,l_plan,l_key1,l_key2);
398 IF (l_qtype = 'SUPPLY') THEN
399 -- First find the end demand that this supply is pegged to, and get
400 -- its due date
401 SELECT min(peg2.demand_date)
402 INTO l_due_date
403 FROM msc_full_pegging peg2, msc_full_pegging peg1
404 WHERE peg1.plan_id = l_plan
405 AND peg1.transaction_id (+) = l_key1
406 AND peg2.pegging_id = peg1.end_pegging_id (+);
407 -- Then, get the dates of this task as defined in Oracle Projects
408 SELECT p2.project_number||'-'||p1.task_number || '-' ||p2.task_name,
409 early_start_date,
410 early_finish_date,late_start_date, late_finish_date,
411 scheduled_start_date, scheduled_finish_date
412 INTO l_prj, l_es, l_ef, l_ls, l_lf, l_ss, l_sf
413 FROM pjm_tasks_v p2, pa_tasks_v p1, msc_supplies m
414 WHERE p2.project_id = p1.project_id
415 AND p2.task_id = p1.task_id
416 AND p1.project_id = m.project_id
417 AND p1.task_id = m.task_id
418 AND m.transaction_id = l_key1;
419
420 -- Construct the message that is to be displayed and insert it
421 IF (l_es > l_due_date) THEN
422 l_ans := 'The project '||l_prj || 'is early. It has a early ' ||
423 'completion date of '|| to_char(l_ef) ||' and completion'||
424 'date of '|| to_char(l_due_date);
425 ELSIF (l_lf < l_due_date) THEN
426 l_ans := 'The project '||l_prj || 'is late. It has a late ' ||
427 'completion date of '|| to_char(l_lf) ||' and completion'||
428 'date of '|| to_char(l_due_date);
429 ELSE
430 l_ans := 'The project '||l_prj || 'is within guidelines. It has a '||
431 'early completion date of '|| to_char(l_ef) ||' , a late completion'
432 ||' date of ' || to_char(l_lf) ||' , a early start date of ' ||
433 to_char(l_es) || ', a late start date of '|| to_char(l_ls) ||
434 ' and completion date of '|| to_char(l_due_date);
435 END IF;
436 ELSIF (l_qtype = 'DEMAND') THEN
437 null;
438 END IF;
439 EXCEPTION
440 WHEN OTHERS THEN
441 raise;
442 END late_prj;
443
444 -- This procedure answers questions about why a supply is late
445 -- supplier capacity issue
446 -- resource capacity overloading
447 PROCEDURE late_supply(
448 x_answer_id IN OUT NoCopy NUMBER,
449 x_err_msg IN OUT NoCopy VARCHAR2,
450 x_msg_count IN OUT NoCopy NUMBER)
451 IS
452 l_plan NUMBER;
453 l_qtype VARCHAR2(10);
454 l_qid NUMBER;
455 l_qcode VARCHAR2(25);
456 l_key1 NUMBER;
457 l_key2 NUMBER;
458 l_ansid NUMBER;
459 l_ans VARCHAR2(1000);
460 l_seq NUMBER := 0; -- remember to increment while inserting the answers
461
462 l_so_num VARCHAR2(100);
463 l_dmd_schedule VARCHAR2(30);
464 l_dmd_date DATE;
465 l_op_seq_num NUMBER;
466 l_op_code VARCHAR2(10);
467 l_res_seq_num NUMBER;
468 l_start_date DATE;
469 l_end_date DATE;
470
471 CURSOR pegging(lkey NUMBER, lplan NUMBER) IS
472 SELECT dmd.order_number,NULL, --dmd.demand_schedule_name,
473 dmd.using_assembly_demand_date
474 FROM msc_demands dmd, msc_full_pegging peg
475 WHERE peg.transaction_id = lkey
476 AND peg.plan_id = lplan
477 AND peg.demand_id = dmd.demand_id
478 AND peg.plan_id = dmd.plan_id
479 AND dmd.using_assembly_demand_date < peg.supply_date;
480
481 CURSOR overloaded_res(lkey NUMBER, lplan NUMBER) IS
482 SELECT res.operation_seq_num, res.std_op_code, res.resource_seq_num,
483 res.start_date, res.end_date
484 FROM msc_resource_requirements res, msc_supplies sup
485 WHERE sup.plan_id = lplan
486 AND sup.transaction_id = lkey
487 AND res.plan_id = sup.plan_id
488 AND res.supply_id = sup.transaction_id
489 AND NVL(res.end_date,res.start_date) < sup.new_schedule_date
490 ORDER BY res.operation_seq_num, res.resource_seq_num;
491 BEGIN
492 -- Get all the key information
493 get_question(l_qid,l_qtype,l_qcode);
494 get_key(l_qtype,l_plan,l_key1,l_key2);
495 -- dbms_output.put_line('Entering late supply:'|| to_char(l_plan) || ':' ||
496 -- to_char(l_key1));
497 IF (x_answer_id is NULL) THEN
498 get_answer_id(l_ansid);
499 END IF;
500 get_sequence(l_seq);
501
502 -- Check if the supply is late
503 OPEN pegging(l_key1,l_plan);
504 FETCH pegging INTO l_so_num, l_dmd_schedule, l_dmd_date;
505 IF pegging%NOTFOUND THEN
506 -- dbms_output.put_line('No data is found here!');
507 -- This supply does not cause any late demands, so it is not late
508 -- dbms_output.put_line('No demands are late');
509 l_ans := 'This supply does not cause any late demand';
510 insert_answer(l_ansid,l_qid,l_seq,l_ans);
511 ELSE
512 -- dbms_output.put_line('l_so_num=' || l_so_num ||':l_dmd_schedule=' ||
513 -- l_dmd_schedule|| ':l_dmd_date='|| to_char(l_dmd_date));
514 -- Check each of the resources to see which is overloaded
515 OPEN overloaded_res(l_key1, l_plan);
516 LOOP
517 FETCH overloaded_res INTO l_op_seq_num, l_op_code, l_res_seq_num,
518 l_start_date, l_end_date;
519 EXIT WHEN overloaded_res%NOTFOUND;
520 -- For each of the overloaded resources and operations, insert answers
521 get_sequence(l_seq);
522 -- dbms_output.put_line('Overloaded resources:'|| to_char(l_seq));
523 END LOOP;
524 END IF;
525 x_answer_id := l_ansid;
526 return;
527 EXCEPTION
528 WHEN OTHERS THEN
529 raise;
530 END late_supply;
531
532 -- This procedure answers questions about why a demand is late
533 -- looks at the late supplies the demand is pegged to, and picks the
534 -- one that is lowest in the chain
535 -- for this supply, it figures out why the supply is late
536 PROCEDURE late_demand(
537 x_answer_id IN OUT NoCopy NUMBER,
538 x_err_msg IN OUT NoCopy VARCHAR2,
539 x_msg_count IN OUT NoCopy NUMBER)
540 IS
541 l_plan NUMBER;
542 l_qtype VARCHAR2(10);
543 l_qid NUMBER;
544 l_qcode VARCHAR2(25);
545 l_key1 NUMBER;
546 l_key2 NUMBER;
547 l_ansid NUMBER;
548 l_ans VARCHAR2(1000);
549 l_seq NUMBER := 0; -- remember to increment while inserting the answers
550
551 l_so_num VARCHAR2(100);
552 l_dmd_schedule VARCHAR2(30);
553 l_dmd_date DATE;
554 l_op_seq_num NUMBER;
555 l_op_code VARCHAR2(10);
556 l_res_seq_num NUMBER;
557 l_start_date DATE;
558 l_end_date DATE;
559 l_supply_id NUMBER;
560 l_err_msg VARCHAR2(2000);
561 l_msg_count NUMBER;
562
563 CURSOR pegging(lkey NUMBER, lplan NUMBER) IS
564 SELECT peg.transaction_id
565 FROM msc_full_pegging peg
566 WHERE peg.demand_date > peg.supply_date
567 START WITH peg.demand_id = lkey
568 AND peg.plan_id = lplan
569 CONNECT BY PRIOR peg.pegging_id = peg.prev_pegging_id
570 ORDER BY peg.demand_date;
571 BEGIN
572 -- Get all the key information
573 get_question(l_qid,l_qtype,l_qcode);
574 get_key(l_qtype,l_plan,l_key1,l_key2);
575 -- dbms_output.put_line('Entering late demand:'|| to_char(l_plan) || ':' ||
576 -- to_char(l_key1));
577 IF (x_answer_id is NULL) THEN
578 get_answer_id(l_ansid);
579 END IF;
580 get_sequence(l_seq);
581 -- Check if the supply is late
582 OPEN pegging(l_key1,l_plan);
583 FETCH pegging INTO l_supply_id;
584 IF pegging%NOTFOUND THEN
585 -- dbms_output.put_line('No data is found here!');
586 -- This supply does not cause any late demands, so it is not late
587 -- dbms_output.put_line('No demands are late');
588 l_ans := 'This demand or its dependent demands are satisfied on time';
589 insert_answer(l_ansid,l_qid,l_seq,l_ans);
590 return;
591 END IF ;
592 -- Now that we have the late supply, find out why that is late
593 -- Set the package variable key appropriately
594 v_key1 := l_supply_id;
595 MSC_ASK_ORACLE.LATE_SUPPLY(
596 x_answer_id => l_ansid,
597 x_err_msg => l_err_msg,
598 x_msg_count => l_msg_count);
599 x_answer_id := l_ansid;
600 x_err_msg := l_err_msg;
601 x_msg_count := l_msg_count;
602 -- Reset the package variable key for future use
603 v_key1 := l_key1;
604 return;
605 EXCEPTION
606 WHEN OTHERS THEN
607 raise;
608 END late_demand;
609
610 END msc_ask_oracle;
[Home] [Help]