[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_WF_UTIL
Source
1 PACKAGE BODY oe_order_wf_util AS
2 /* $Header: OEXUOWFB.pls 120.44.12020000.9 2013/04/09 08:06:27 spothula ship $ */
3
4 -- record for dynamic notification body for quote/blanket
5 TYPE line_record IS RECORD (
6 line_num VARCHAR2 (100),
7 item mtl_system_items_kfv.concatenated_segments%TYPE,
8 uom VARCHAR2 (3),
9 quantity NUMBER,
10 unit_selling_price NUMBER,
11 line_amount NUMBER,
12 line_id NUMBER,
13 inventory_item_id NUMBER,
14 --- the following column needed for line_margin API
15 item_type_code VARCHAR2 (30),
16 open_flag VARCHAR2 (1),
17 shipped_quantity NUMBER,
18 ordered_quantity NUMBER,
19 source_type_code VARCHAR2 (30),
20 ship_from_org_id NUMBER,
21 project_id NUMBER,
22 actual_shipment_date DATE,
23 fulfillment_date DATE
24 );
25
26 -- This procedure retrieves the name of the flow that needs to
27 -- be created.
28 FUNCTION get_processname (
29 p_itemtype IN VARCHAR2,
30 p_itemkey IN VARCHAR2,
31 p_wfasgn_item_type IN VARCHAR2 := fnd_api.g_miss_char,
32 p_salesdocumenttypecode IN VARCHAR2 DEFAULT NULL,
33 p_line_rec IN oe_order_pub.line_rec_type
34 := oe_order_pub.g_miss_line_rec
35 )
36 RETURN VARCHAR2
37 IS
38 l_process_name VARCHAR2 (30);
39
40 /* In the five cursors below, trunc(sysdate) was put in instead of sysdate in expr.
41 trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate) to fix bug 3108864 */
42 CURSOR find_hdrprocessname (itemkey VARCHAR2)
43 IS
44 SELECT wf_assign.process_name
45 FROM oe_workflow_assignments wf_assign,
46 oe_order_headers_all header
47 WHERE header.header_id = TO_NUMBER (itemkey)
48 AND header.order_type_id = wf_assign.order_type_id
49 AND SYSDATE >= wf_assign.start_date_active
50 AND TRUNC (SYSDATE) <= NVL (wf_assign.end_date_active, SYSDATE)
51 AND wf_assign.line_type_id IS NULL
52 AND NVL (wf_assign.wf_item_type, oe_globals.g_wfi_hdr) =
53 oe_globals.g_wfi_hdr;
54
55 /* NEED UPGRADE ON NEW COLUMN? */
56 CURSOR find_lineprocessname
57 IS
58 SELECT wf_assign.process_name
59 FROM oe_workflow_assignments wf_assign,
60 oe_order_headers_all header
61 WHERE NVL (p_wfasgn_item_type, '-99') =
62 NVL (wf_assign.item_type_code,
63 NVL (p_wfasgn_item_type, '-99')
64 )
65 AND header.header_id = p_line_rec.header_id
66 AND header.order_type_id = wf_assign.order_type_id
67 AND p_line_rec.line_type_id = wf_assign.line_type_id
68 AND wf_assign.line_type_id IS NOT NULL
69 AND SYSDATE >= wf_assign.start_date_active
70 AND TRUNC (SYSDATE) <= NVL (wf_assign.end_date_active, SYSDATE)
71 ORDER BY wf_assign.item_type_code;
72
73 CURSOR find_negotiatehdrprocessname (itemkey VARCHAR2)
74 IS
75 SELECT wf_assign.process_name
76 FROM oe_workflow_assignments wf_assign,
77 oe_order_headers_all header
78 WHERE header.header_id = TO_NUMBER (itemkey)
79 AND header.order_type_id = wf_assign.order_type_id
80 AND SYSDATE >= wf_assign.start_date_active
81 AND TRUNC (SYSDATE) <= NVL (wf_assign.end_date_active, SYSDATE)
82 AND wf_assign.wf_item_type = oe_globals.g_wfi_ngo
83 AND wf_assign.line_type_id IS NULL;
84
85 CURSOR find_bktngohdrprocessname (itemkey VARCHAR2)
86 IS
87 SELECT wf_assign.process_name
88 FROM oe_workflow_assignments wf_assign,
89 oe_blanket_headers_all header
90 WHERE header.header_id = TO_NUMBER (itemkey)
91 AND header.order_type_id = wf_assign.order_type_id
92 AND SYSDATE >= wf_assign.start_date_active
93 AND TRUNC (SYSDATE) <= NVL (wf_assign.end_date_active, SYSDATE)
94 AND wf_assign.wf_item_type = oe_globals.g_wfi_ngo
95 AND wf_assign.line_type_id IS NULL;
96
97 CURSOR find_blankethdrprocessname (itemkey VARCHAR2)
98 IS
99 SELECT wf_assign.process_name
100 FROM oe_workflow_assignments wf_assign,
101 oe_blanket_headers_all blanket
102 WHERE blanket.header_id = TO_NUMBER (itemkey)
103 AND blanket.order_type_id = wf_assign.order_type_id
104 AND SYSDATE >= wf_assign.start_date_active
105 AND TRUNC (SYSDATE) <= NVL (wf_assign.end_date_active, SYSDATE)
106 AND wf_assign.wf_item_type = oe_globals.g_wfi_bkt
107 AND wf_assign.line_type_id IS NULL;
108
109 --
110 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
111 --
112 BEGIN
113 IF l_debug_level > 0
114 THEN
115 oe_debug_pub.ADD ( 'IN GET_PROCESSNAME: '
116 || p_itemtype
117 || '/'
118 || p_itemkey
119 || '/'
120 || p_wfasgn_item_type
121 );
122 END IF;
123
124 IF (p_itemtype = oe_globals.g_wfi_hdr)
125 THEN
126 OPEN find_hdrprocessname (p_itemkey);
127
128 FETCH find_hdrprocessname
129 INTO l_process_name;
130
131 CLOSE find_hdrprocessname;
132 ELSIF (p_itemtype = oe_globals.g_wfi_lin)
133 THEN
134 IF l_debug_level > 0
135 THEN
136 oe_debug_pub.ADD ( 'header_id: '
137 || p_line_rec.header_id
138 || ' line_id: '
139 || p_line_rec.line_id
140 || ' line_type_id: '
141 || p_line_rec.line_type_id
142 );
143 END IF;
144
145 OPEN find_lineprocessname;
146
147 FETCH find_lineprocessname
148 INTO l_process_name;
149
150 CLOSE find_lineprocessname;
151 ELSIF (p_itemtype = oe_globals.g_wfi_ngo)
152 THEN
153 IF NVL (p_salesdocumenttypecode, 'O') = 'O'
154 THEN
155 OPEN find_negotiatehdrprocessname (p_itemkey);
156
157 FETCH find_negotiatehdrprocessname
158 INTO l_process_name;
159
160 CLOSE find_negotiatehdrprocessname;
161 ELSIF NVL (p_salesdocumenttypecode, 'O') = 'B'
162 THEN
163 OPEN find_bktngohdrprocessname (p_itemkey);
164
165 FETCH find_bktngohdrprocessname
166 INTO l_process_name;
167
168 CLOSE find_bktngohdrprocessname;
169 ELSE
170 -- error
171 RAISE fnd_api.g_exc_error;
172 END IF;
173 ELSIF (p_itemtype = oe_globals.g_wfi_bkt)
174 THEN
175 OPEN find_blankethdrprocessname (p_itemkey);
176
177 FETCH find_blankethdrprocessname
178 INTO l_process_name;
179
180 CLOSE find_blankethdrprocessname;
181 END IF;
182
183 IF l_process_name IS NULL
184 THEN
185 RAISE NO_DATA_FOUND;
186 END IF;
187
188 IF l_debug_level > 0
189 THEN
190 oe_debug_pub.ADD ('PROCESS NAME IS ' || l_process_name);
191 END IF;
192
193 RETURN l_process_name;
194
195 IF l_debug_level > 0
196 THEN
197 oe_debug_pub.ADD ('EXITING GET_PROCESSNAME');
198 END IF;
199 EXCEPTION
200 WHEN NO_DATA_FOUND
201 THEN
202 fnd_message.set_name ('ONT', 'OE_MISS_FLOW');
203 oe_msg_pub.ADD;
204 RAISE fnd_api.g_exc_error;
205 WHEN OTHERS
206 THEN
207 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
208 THEN
209 oe_msg_pub.add_exc_msg (g_pkg_name, 'GET_ProcessName');
210 RAISE fnd_api.g_exc_unexpected_error;
211 END IF;
212 END get_processname;
213
214 PROCEDURE set_header_descriptor (
215 document_id IN VARCHAR2,
216 display_type IN VARCHAR2,
217 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
218 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
219 )
220 IS
221 l_header_id NUMBER;
222 l_order_number NUMBER;
223 l_order_type_id NUMBER;
224 l_order_type_name VARCHAR2 (80);
225 l_order_category_code VARCHAR2 (30);
226 l_order_type_txt VARCHAR2 (2000);
227 l_header_txt VARCHAR2 (2000);
228 --
229 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
230 l_item_type VARCHAR2 (240); --ame er 16084377
231 l_item_key VARCHAR2 (100); --ame er 16084377
232 l_org_id NUMBER;
233 --
234 BEGIN
235 document_type := display_type;
236
237 BEGIN
238 -- if viewing method is through URL
239 -- fix bug 1332384
240 SELECT item_key, item_type
241 INTO l_item_key, l_item_type --l_header_id ame er 16084377
242 FROM wf_item_activity_statuses
243 WHERE notification_id = TO_NUMBER (document_id);
244 EXCEPTION
245 WHEN NO_DATA_FOUND
246 THEN
247 -- Begin Changes for bug 8570400
248 BEGIN
249 SELECT item_key, item_type
250 INTO l_item_key, l_item_type --l_header_id ame er 16084377
251 FROM wf_item_activity_statuses_h
252 WHERE notification_id = TO_NUMBER (document_id);
253 EXCEPTION
254 WHEN TOO_MANY_ROWS
255 THEN
256 NULL;
257 WHEN NO_DATA_FOUND
258 THEN
259 -- if viewing method is email
260 l_item_key := TO_NUMBER (wf_engine.setctx_itemkey);
261 --ame er 16084377
262 END; -- End Changes for bug 8570400
263 END;
264
265 -- ame er 16084377 BEGIN
266 BEGIN
267 IF l_debug_level > 0
268 THEN
269 oe_debug_pub.ADD ( 'before getting attribute:'
270 );
271 END IF;
272
273 IF l_item_type = 'OEAME'
274 THEN --ame er 16084377
275 l_header_id :=
276 wf_engine.getitemattrnumber ('OEAME', l_item_key, 'HEADER_ID');
277 ELSE
278 l_header_id := TO_NUMBER (l_item_key);
279 END IF; --ame er 16084377
280
281 IF l_debug_level > 0
282 THEN
283 oe_debug_pub.ADD ('After getting attribute:' || l_header_id);
284 END IF;
285
286 SELECT org_id
287 INTO l_org_id
288 FROM oe_order_headers_all
289 WHERE header_id = l_header_id;
290
291 mo_global.set_policy_context ('S', l_org_id);
292
293 IF l_debug_level > 0
294 THEN
295 oe_debug_pub.ADD ('After setting org context:' || l_header_id);
296 END IF;
297
298 IF l_header_id IS NULL
299 THEN
300 l_header_id := l_item_key;
301 END IF;
302 EXCEPTION
303 WHEN NO_DATA_FOUND
304 THEN
305 l_header_id := l_item_key;
306 END;
307
308 -- ame er 16084377 END
309 SELECT order_number, order_type_id, order_category_code
310 INTO l_order_number, l_order_type_id, l_order_category_code
311 FROM oe_order_headers_all
312 WHERE header_id = l_header_id;
313
314 SELECT t.NAME
315 INTO l_order_type_name
316 FROM oe_transaction_types_tl t
317 WHERE t.LANGUAGE = USERENV ('LANG')
318 AND t.transaction_type_id = l_order_type_id;
319
320 fnd_message.set_name ('ONT', 'OE_WF_ORDER_TYPE');
321 fnd_message.set_token ('ORDER_TYPE', l_order_type_name);
322 l_order_type_txt := fnd_message.get;
323
324 IF l_order_category_code = 'RETURN'
325 THEN
326 fnd_message.set_name ('ONT', 'OE_WF_RETURN_ORDER');
327 fnd_message.set_token ('ORDER_NUMBER', TO_CHAR (l_order_number));
328 l_header_txt := fnd_message.get;
329 ELSE
330 fnd_message.set_name ('ONT', 'OE_WF_SALES_ORDER');
331 fnd_message.set_token ('ORDER_NUMBER', TO_CHAR (l_order_number));
332 l_header_txt := fnd_message.get;
333 END IF;
334
335 document := SUBSTRB (l_order_type_txt || ', ' || l_header_txt, 1, 240);
336 EXCEPTION
337 WHEN OTHERS
338 THEN
339 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
340 THEN
341 oe_msg_pub.add_exc_msg (g_pkg_name, 'Set_Header_Descriptor');
342 RAISE fnd_api.g_exc_unexpected_error;
343 END IF;
344 END set_header_descriptor;
345
346 PROCEDURE set_line_descriptor (
347 document_id IN VARCHAR2,
348 display_type IN VARCHAR2,
349 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
350 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
351 )
352 IS
353 l_header_id NUMBER;
354 l_line_id NUMBER;
355 l_order_number NUMBER;
356 l_order_type_id NUMBER;
357 l_order_type_name VARCHAR2 (80);
358 l_order_category_code VARCHAR2 (30);
359 l_order_type_txt VARCHAR2 (2000);
360 l_line_txt VARCHAR2 (2000);
361 l_line_number NUMBER;
362 l_shipment_number NUMBER;
363 l_option_number NUMBER;
364 l_service_number NUMBER;
365 --
366 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
367 --
368 BEGIN
369 document_type := display_type;
370
371 BEGIN
372 -- if viewing method is through URL
373 -- fix bug 1332384
374 SELECT item_key
375 INTO l_line_id
376 FROM wf_item_activity_statuses
377 WHERE notification_id = TO_NUMBER (document_id);
378 EXCEPTION
379 WHEN NO_DATA_FOUND
380 THEN
381 -- Begin Changes for bug 8570400
382 BEGIN
383 SELECT item_key
384 INTO l_line_id
385 FROM wf_item_activity_statuses_h
386 WHERE notification_id = TO_NUMBER (document_id);
387 EXCEPTION
388 WHEN TOO_MANY_ROWS
389 THEN
390 NULL;
391 WHEN NO_DATA_FOUND
392 THEN
393 -- if viewing method is email
394 l_line_id := TO_NUMBER (wf_engine.setctx_itemkey);
395 END; -- End Changes for bug 8570400
396 END;
397
398 SELECT header_id, line_number, shipment_number, option_number,
399 service_number
400 INTO l_header_id, l_line_number, l_shipment_number, l_option_number,
401 l_service_number
402 FROM oe_order_lines_all
403 WHERE line_id = l_line_id;
404
405 SELECT order_number, order_type_id, order_category_code
406 INTO l_order_number, l_order_type_id, l_order_category_code
407 FROM oe_order_headers_all
408 WHERE header_id = l_header_id;
409
410 SELECT t.NAME
411 INTO l_order_type_name
412 FROM oe_transaction_types_tl t
413 WHERE t.LANGUAGE = USERENV ('LANG')
414 AND t.transaction_type_id = l_order_type_id;
415
416 fnd_message.set_name ('ONT', 'OE_WF_ORDER_TYPE');
417 fnd_message.set_token ('ORDER_TYPE', l_order_type_name);
418 l_order_type_txt := fnd_message.get;
419
420 IF l_order_category_code = 'RETURN'
421 THEN
422 fnd_message.set_name ('ONT', 'OE_WF_RETURN_LINE');
423 fnd_message.set_token ('ORDER_NUMBER', TO_CHAR (l_order_number));
424 fnd_message.set_token ('LINE_NUMBER', TO_CHAR (l_line_number));
425 fnd_message.set_token ('SHIPMENT_NUMBER',
426 TO_CHAR (l_shipment_number)
427 );
428 fnd_message.set_token ('OPTION_NUMBER', TO_CHAR (l_option_number));
429 fnd_message.set_token ('SERVICE_NUMBER', TO_CHAR (l_service_number));
430 l_line_txt := fnd_message.get;
431 ELSE
432 fnd_message.set_name ('ONT', 'OE_WF_LINE');
433 fnd_message.set_token ('ORDER_NUMBER', TO_CHAR (l_order_number));
434 fnd_message.set_token ('LINE_NUMBER', TO_CHAR (l_line_number));
435 fnd_message.set_token ('SHIPMENT_NUMBER',
436 TO_CHAR (l_shipment_number)
437 );
438 fnd_message.set_token ('OPTION_NUMBER', TO_CHAR (l_option_number));
439 fnd_message.set_token ('SERVICE_NUMBER', TO_CHAR (l_service_number));
440 l_line_txt := fnd_message.get;
441 END IF;
442
443 document := SUBSTRB (l_order_type_txt || ', ' || l_line_txt, 1, 240);
444 EXCEPTION
445 WHEN OTHERS
446 THEN
447 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
448 THEN
449 oe_msg_pub.add_exc_msg (g_pkg_name, 'Set_Line_Descriptor');
450 RAISE fnd_api.g_exc_unexpected_error;
451 END IF;
452 END set_line_descriptor;
453
454 PROCEDURE set_header_user_key (p_header_rec IN oe_order_pub.header_rec_type)
455 IS
456 sales_order VARCHAR2 (240);
457 --
458 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
459 --
460 BEGIN
461 IF p_header_rec.order_category_code = 'RETURN'
462 THEN
463 fnd_message.set_name ('ONT', 'OE_WF_RETURN_ORDER');
464 ELSE
465 fnd_message.set_name ('ONT', 'OE_WF_SALES_ORDER');
466 END IF;
467
468 fnd_message.set_token ('ORDER_NUMBER',
469 TO_CHAR (p_header_rec.order_number)
470 );
471 EXCEPTION
472 WHEN OTHERS
473 THEN
474 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
475 THEN
476 oe_msg_pub.add_exc_msg (g_pkg_name, 'Set_Header_User_Key');
477 RAISE fnd_api.g_exc_unexpected_error;
478 END IF;
479 END set_header_user_key;
480
481 PROCEDURE set_line_user_key (p_line_rec IN oe_order_pub.line_rec_type)
482 IS
483 l_header_id NUMBER;
484 --
485 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
486 --
487 BEGIN
488 l_header_id := p_line_rec.header_id;
489 -- Performance Bug 1929163:
490 -- Use cached header rec to get header values instead
491 -- of a query to select the values.
492 oe_order_cache.load_order_header (l_header_id);
493
494 IF oe_order_cache.g_header_rec.order_category_code = 'RETURN'
495 THEN
496 fnd_message.set_name ('ONT', 'OE_WF_RETURN_LINE');
497 ELSE
498 fnd_message.set_name ('ONT', 'OE_WF_LINE');
499 END IF;
500
501 fnd_message.set_token ('ORDER_NUMBER',
502 TO_CHAR (oe_order_cache.g_header_rec.order_number)
503 );
504 fnd_message.set_token ('LINE_NUMBER', TO_CHAR (p_line_rec.line_number));
505 fnd_message.set_token ('SHIPMENT_NUMBER',
506 TO_CHAR (p_line_rec.shipment_number)
507 );
508 fnd_message.set_token ('OPTION_NUMBER',
509 TO_CHAR (p_line_rec.option_number)
510 );
511 fnd_message.set_token ('SERVICE_NUMBER',
512 TO_CHAR (p_line_rec.service_number)
513 );
514 EXCEPTION
515 WHEN OTHERS
516 THEN
517 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
518 THEN
519 oe_msg_pub.add_exc_msg (g_pkg_name, 'Set_Line_User_Key');
520 RAISE fnd_api.g_exc_unexpected_error;
521 END IF;
522 END set_line_user_key;
523
524 -- This procedure starts the flow, by calling WF_ENGINE utilities.
525 --
526 PROCEDURE start_flow (p_itemtype IN VARCHAR2, p_itemkey IN VARCHAR2)
527 IS
528 --
529 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
530 --
531 BEGIN
532 IF l_debug_level > 0
533 THEN
534 oe_debug_pub.ADD ('IN START_FLOW');
535 END IF;
536
537 wf_engine.startprocess (p_itemtype, p_itemkey);
538
539 IF l_debug_level > 0
540 THEN
541 oe_debug_pub.ADD ('EXITING START_FLOW');
542 END IF;
543 EXCEPTION
544 WHEN OTHERS
545 THEN
546 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
547 THEN
548 oe_msg_pub.add_exc_msg (g_pkg_name, 'Start_Flow');
549 RAISE fnd_api.g_exc_unexpected_error;
550 END IF;
551 END start_flow;
552
553 PROCEDURE start_linefork (p_itemkey IN VARCHAR2)
554 IS
555 --
556 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
557 --
558 BEGIN
559 IF l_debug_level > 0
560 THEN
561 oe_debug_pub.ADD ('IN START_LINEFORK');
562 END IF;
563
564 wf_engine.startforkprocess ('OEOL', p_itemkey);
565
566 -- Refresh old notifications here if needed.
567 IF l_debug_level > 0
568 THEN
569 oe_debug_pub.ADD ('EXITING START_LINEFORK');
570 END IF;
571 EXCEPTION
572 WHEN OTHERS
573 THEN
574 IF wf_core.error_name = 'WFENG_NOFORK_ONERROR'
575 THEN
576 fnd_message.set_name ('ONT', 'OE_WF_SPLIT_FORK_ERR');
577 oe_msg_pub.ADD;
578 END IF;
579
580 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
581 THEN
582 oe_msg_pub.add_exc_msg (g_pkg_name, 'Start_LineFork');
583 RAISE fnd_api.g_exc_unexpected_error;
584 END IF;
585 END start_linefork;
586
587 -- This process is called from OE_ORDER_PVT.HEader to create the
588 -- Header workitem and start the flow.
589 PROCEDURE createstart_hdrprocess (
590 p_header_rec IN oe_order_pub.header_rec_type
591 )
592 IS
593 --
594 l_count NUMBER;
595 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
596 --
597 BEGIN
598 IF l_debug_level > 0
599 THEN
600 oe_debug_pub.ADD ('IN CREATESTART_HDRPROCESS');
601 END IF;
602
603 create_hdrworkitem (p_header_rec);
604 -- We do not start the flow when the record is written, rather
605 -- when the transaction is committed. Here we will set the
606 -- Global.
607 oe_globals.g_start_header_flow := p_header_rec.header_id;
608
609 -- For OENH
610 -- Check if a OENH flow exists, if so set the parent
611 SELECT COUNT (1)
612 INTO l_count
613 FROM wf_items
614 WHERE item_type = oe_globals.g_wfi_ngo
615 AND item_key = TO_CHAR (p_header_rec.header_id);
616
617 IF l_count > 0
618 THEN
619 wf_item.set_item_parent (oe_globals.g_wfi_hdr,
620 TO_CHAR (p_header_rec.header_id),
621 oe_globals.g_wfi_ngo,
622 TO_CHAR (p_header_rec.header_id),
623 ''
624 );
625 END IF;
626
627 /* Start_Flow(OE_GLOBALS.G_WFI_HDR, p_header_rec.header_id); */
628 IF l_debug_level > 0
629 THEN
630 oe_debug_pub.ADD ('EXITING CREATESTART_HDRPROCESS');
631 END IF;
632 EXCEPTION
633 WHEN fnd_api.g_exc_error
634 THEN -- 2590433
635 RAISE;
636 WHEN OTHERS
637 THEN
638 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
639 THEN
640 oe_msg_pub.add_exc_msg (g_pkg_name, 'Start_HdrProcess');
641 RAISE fnd_api.g_exc_unexpected_error;
642 END IF;
643 END createstart_hdrprocess;
644
645 -- This procedure creates the Header WorkItem
646 --
647 PROCEDURE create_hdrworkitem (p_header_rec IN oe_order_pub.header_rec_type)
648 IS
649 l_hdr_process_name VARCHAR2 (30);
650 l_aname wf_engine.nametabtyp;
651 l_aname2 wf_engine.nametabtyp;
652 l_avalue wf_engine.numtabtyp;
653 l_avaluetext wf_engine.texttabtyp;
654 sales_order VARCHAR2 (240);
655 l_user_name VARCHAR2 (100);
656 l_validate_user NUMBER;
657 l_owner_role VARCHAR2 (100);
658 --
659 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
660 --
661 BEGIN
662 IF l_debug_level > 0
663 THEN
664 oe_debug_pub.ADD ('IN CREATE_HDRWORKITEM');
665 END IF;
666
667 -- Get name of Header Process
668 IF l_debug_level > 0
669 THEN
670 oe_debug_pub.ADD ('GET HEADER PROCESS NAME');
671 END IF;
672
673 l_hdr_process_name :=
674 get_processname (p_itemtype => oe_globals.g_wfi_hdr,
675 p_itemkey => p_header_rec.header_id
676 );
677 set_header_user_key (p_header_rec);
678 sales_order := SUBSTRB (fnd_message.get, 1, 240);
679
680 SELECT user_name
681 INTO l_owner_role
682 FROM fnd_user
683 WHERE user_id = fnd_global.user_id;
684
685 -- Create Header Work item
686 wf_engine.createprocess (oe_globals.g_wfi_hdr,
687 TO_CHAR (p_header_rec.header_id),
688 l_hdr_process_name,
689 sales_order,
690 l_owner_role
691 );
692 wf_engine.additemattr (oe_globals.g_wfi_hdr,
693 TO_CHAR (p_header_rec.header_id),
694 '#WAITFORDETAIL',
695 NULL,
696 0
697 );
698
699 IF l_debug_level > 0
700 THEN
701 oe_debug_pub.ADD ('AFTER WF_ENGINE.CREATEPROCESS');
702 END IF;
703
704 -- Set various Header Attributes
705 l_aname (1) := 'USER_ID';
706 l_avalue (1) := fnd_global.user_id;
707 l_aname (2) := 'APPLICATION_ID';
708 l_avalue (2) := fnd_global.resp_appl_id;
709 l_aname (3) := 'RESPONSIBILITY_ID';
710 l_avalue (3) := fnd_global.resp_id;
711 l_aname (4) := 'ORG_ID';
712 l_avalue (4) := TO_NUMBER (oe_globals.g_org_id);
713 l_aname (5) := 'ORDER_NUMBER';
714 l_avalue (5) := p_header_rec.order_number;
715 wf_engine.setitemattrnumberarray (oe_globals.g_wfi_hdr,
716 p_header_rec.header_id,
717 l_aname,
718 l_avalue
719 );
720
721 /* new logic to get FROM_ROLE */
722 BEGIN
723 SELECT user_name
724 INTO l_user_name
725 FROM fnd_user
726 WHERE user_id = fnd_global.user_id;
727 EXCEPTION
728 WHEN OTHERS
729 THEN
730 l_user_name := NULL; -- do not set FROM_ROLE then
731 END;
732
733 l_aname2 (1) := 'ORDER_CATEGORY';
734 l_avaluetext (1) := p_header_rec.order_category_code;
735 l_aname2 (2) := 'NOTIFICATION_APPROVER';
736 l_avaluetext (2) := fnd_profile.VALUE ('OE_NOTIFICATION_APPROVER');
737 l_aname2 (3) := 'NOTIFICATION_FROM_ROLE';
738 l_avaluetext (3) := l_user_name;
739 l_aname2 (4) := 'ORDER_DETAILS_URL';
740 l_avaluetext (4) :=
741 RTRIM (fnd_profile.VALUE ('APPS_FRAMEWORK_AGENT'), '/')
742 || '/OA_HTML/OA.jsp?akRegionCode=ORDER_DETAILS_PAGE'
743 || '&'
744 || 'akRegionApplicationId=660'
745 || '&'
746 || 'HeaderId='
747 || TO_CHAR (p_header_rec.header_id);
748 wf_engine.setitemattrtextarray (oe_globals.g_wfi_hdr,
749 p_header_rec.header_id,
750 l_aname2,
751 l_avaluetext
752 );
753
754 IF l_debug_level > 0
755 THEN
756 oe_debug_pub.ADD ('EXITING CREATE_HDRWORKITEM');
757 END IF;
758 EXCEPTION
759 WHEN fnd_api.g_exc_error
760 THEN -- 2590433
761 RAISE;
762 WHEN OTHERS
763 THEN
764 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
765 THEN
766 oe_msg_pub.add_exc_msg (g_pkg_name, 'Create_HdrWorkItem');
767 RAISE fnd_api.g_exc_unexpected_error;
768 END IF;
769 END create_hdrworkitem;
770
771 -- THis is called from OE_ORDER_PVT.LINES to create the Line
772 -- workitem and start the line flow.
773 PROCEDURE createstart_lineprocess (p_line_rec IN oe_order_pub.line_rec_type)
774 IS
775 l_item_type VARCHAR2 (30);
776 l_index NUMBER;
777 --
778 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
779 --
780 BEGIN
781 -- If this is a Split Insert then we need to fork the flow
782 IF p_line_rec.split_from_line_id IS NOT NULL
783 THEN
784 IF l_debug_level > 0
785 THEN
786 oe_debug_pub.ADD ('SPLIT FROM LINE ID IS NOT NULL');
787 END IF;
788
789 createstart_linefork (p_line_rec);
790 ELSE -- Regular Flow creation
791 -- Get Wf itme type
792 l_item_type := oe_order_wf_util.get_wf_item_type (p_line_rec);
793
794 IF l_debug_level > 0
795 THEN
796 oe_debug_pub.ADD ('IN CREATESTART_LINEPROCESS');
797 END IF;
798
799 create_lineworkitem (p_line_rec, l_item_type);
800
801 -- We do not start the flow when the record is written, rather
802 -- when the transaction is committed.
803 -- Start_Flow(OE_GLOBALS.G_WFI_LIN, p_Line_rec.Line_id);
804 -- Add line Id to global table for later processing.
805
806 -- Bug 3000619, references to G_START_LINE_FLOWS_TBL are changed.
807 IF (oe_globals.g_start_line_flows_tbl.COUNT = 0)
808 THEN
809 IF l_debug_level > 0
810 THEN
811 oe_debug_pub.ADD ( 'G_START_LINE_FLOWS_TBL.COUNT = 0'
812 || p_line_rec.ato_line_id
813 || '-'
814 || p_line_rec.line_id,
815 2
816 );
817 END IF;
818
819 oe_globals.g_start_line_flows_tbl (1).line_id :=
820 p_line_rec.line_id;
821 oe_globals.g_start_line_flows_tbl (1).post_write_ato_line_id :=
822 p_line_rec.ato_line_id;
823 ELSE -- the table has entries
824 l_index := oe_globals.g_start_line_flows_tbl.LAST;
825
826 IF l_debug_level > 0
827 THEN
828 oe_debug_pub.ADD ( 'G_START_LINE_FLOWS_TBL.COUNT = '
829 || TO_CHAR (l_index)
830 || p_line_rec.ato_line_id
831 || '-'
832 || p_line_rec.line_id,
833 3
834 );
835 END IF;
836
837 oe_globals.g_start_line_flows_tbl (l_index + 1).line_id :=
838 p_line_rec.line_id;
839 oe_globals.g_start_line_flows_tbl (l_index + 1).post_write_ato_line_id :=
840 p_line_rec.ato_line_id;
841 END IF;
842
843 IF l_debug_level > 0
844 THEN
845 oe_debug_pub.ADD ('EXITING CREATESTART_LINEPROCESS');
846 END IF;
847 END IF;
848 EXCEPTION
849 WHEN fnd_api.g_exc_error
850 THEN -- 2590433
851 RAISE;
852 WHEN OTHERS
853 THEN
854 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
855 THEN
856 oe_msg_pub.add_exc_msg (g_pkg_name, 'CreateStart_LineProcess');
857 RAISE fnd_api.g_exc_unexpected_error;
858 END IF;
859 END createstart_lineprocess;
860
861 PROCEDURE createstart_linefork (p_line_rec IN oe_order_pub.line_rec_type)
862 IS
863 --
864 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
865 --
866 BEGIN
867 IF l_debug_level > 0
868 THEN
869 oe_debug_pub.ADD ('IN CREATESTART_LINEFORK');
870 END IF;
871
872 create_linefork (p_line_rec);
873 start_linefork (p_line_rec.line_id);
874
875 IF l_debug_level > 0
876 THEN
877 oe_debug_pub.ADD ('EXITING CREATESTART_LINEFORK');
878 END IF;
879 EXCEPTION
880 WHEN OTHERS
881 THEN
882 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
883 THEN
884 oe_msg_pub.add_exc_msg (g_pkg_name, 'CreateStart_LineFork');
885 RAISE fnd_api.g_exc_unexpected_error;
886 END IF;
887 END createstart_linefork;
888
889 -- This procedure creates the Line WorkItem.
890 --
891 PROCEDURE create_lineworkitem (
892 p_line_rec IN oe_order_pub.line_rec_type,
893 p_item_type IN VARCHAR2
894 )
895 IS
896 l_line_process_name VARCHAR2 (30);
897 l_item_type VARCHAR2 (30);
898 l_order_number NUMBER;
899 l_aname wf_engine.nametabtyp;
900 l_aname2 wf_engine.nametabtyp;
901 l_avalue wf_engine.numtabtyp;
902 l_avaluetext wf_engine.texttabtyp;
903 line VARCHAR2 (240);
904 l_owner_role VARCHAR2 (100);
905 --
906 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
907 --
908 BEGIN
909 IF l_debug_level > 0
910 THEN
911 oe_debug_pub.ADD ('IN CREATE_LINEWORKITEM');
912 END IF;
913
914 l_item_type := p_item_type;
915
916 -- Get name of Line Process
917 IF l_debug_level > 0
918 THEN
919 oe_debug_pub.ADD ('GET LINE PROCESSNAME');
920 END IF;
921
922 l_line_process_name :=
923 get_processname (p_itemtype => oe_globals.g_wfi_lin,
924 p_itemkey => p_line_rec.line_id,
925 p_wfasgn_item_type => l_item_type,
926 p_line_rec => p_line_rec
927 );
928 set_line_user_key (p_line_rec);
929 line := SUBSTRB (fnd_message.get, 1, 240);
930
931 SELECT user_name
932 INTO l_owner_role
933 FROM fnd_user
934 WHERE user_id = fnd_global.user_id;
935
936 -- Create Line Work item
937 wf_engine.createprocess (oe_globals.g_wfi_lin,
938 TO_CHAR (p_line_rec.line_id),
939 l_line_process_name,
940 line,
941 l_owner_role
942 );
943
944 IF l_debug_level > 0
945 THEN
946 oe_debug_pub.ADD ('AFTER WF_ENGINE.CREATEPROCESS');
947 END IF;
948
949 -- Set various Line Attributes
950 l_aname (1) := 'USER_ID';
951 l_avalue (1) := fnd_global.user_id;
952 l_aname (2) := 'APPLICATION_ID';
953 l_avalue (2) := fnd_global.resp_appl_id;
954 l_aname (3) := 'RESPONSIBILITY_ID';
955 l_avalue (3) := fnd_global.resp_id;
956 l_aname (4) := 'ORG_ID';
957 l_avalue (4) := TO_NUMBER (oe_globals.g_org_id);
958 wf_engine.setitemattrnumberarray (oe_globals.g_wfi_lin,
959 p_line_rec.line_id,
960 l_aname,
961 l_avalue
962 );
963 l_aname2 (1) := 'LINE_CATEGORY';
964 l_avaluetext (1) := p_line_rec.line_category_code;
965 l_aname2 (2) := 'NOTIFICATION_APPROVER';
966 l_avaluetext (2) := fnd_profile.VALUE ('OE_NOTIFICATION_APPROVER');
967 wf_engine.setitemattrtextarray (oe_globals.g_wfi_lin,
968 p_line_rec.line_id,
969 l_aname2,
970 l_avaluetext
971 );
972 wf_item.set_item_parent (oe_globals.g_wfi_lin,
973 TO_CHAR (p_line_rec.line_id),
974 oe_globals.g_wfi_hdr,
975 TO_CHAR (p_line_rec.header_id),
976 '',
977 TRUE
978 );
979
980 IF l_debug_level > 0
981 THEN
982 oe_debug_pub.ADD ('EXITING CREATE_LINEWORKITEM');
983 END IF;
984 EXCEPTION
985 WHEN fnd_api.g_exc_error
986 THEN -- 2590433
987 RAISE;
988 WHEN OTHERS
989 THEN
990 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
991 THEN
992 oe_msg_pub.add_exc_msg (g_pkg_name, 'Create_LineWorkItem');
993 RAISE fnd_api.g_exc_unexpected_error;
994 END IF;
995 END create_lineworkitem;
996
997 PROCEDURE create_linefork (p_line_rec IN oe_order_pub.line_rec_type)
998 IS
999 l_order_number NUMBER;
1000 l_header_id NUMBER;
1001 line VARCHAR2 (240);
1002 --
1003 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1004 --
1005 BEGIN
1006 IF l_debug_level > 0
1007 THEN
1008 oe_debug_pub.ADD ('IN CREATE_LINEFORK');
1009 END IF;
1010
1011 set_line_user_key (p_line_rec);
1012 line := SUBSTRB (fnd_message.get, 1, 240);
1013 wf_engine.createforkprocess ('OEOL',
1014 p_line_rec.split_from_line_id,
1015 p_line_rec.line_id,
1016 TRUE,
1017 TRUE
1018 );
1019 wf_engine.setitemuserkey (oe_globals.g_wfi_lin, p_line_rec.line_id,
1020 line);
1021
1022 IF l_debug_level > 0
1023 THEN
1024 oe_debug_pub.ADD ('EXITING CREATE_LINEFORK');
1025 END IF;
1026 EXCEPTION
1027 WHEN OTHERS
1028 THEN
1029 IF wf_core.error_name = 'WFENG_NOFORK_ONERROR'
1030 THEN
1031 fnd_message.set_name ('ONT', 'OE_WF_SPLIT_FORK_ERR');
1032 oe_msg_pub.ADD;
1033 END IF;
1034
1035 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
1036 THEN
1037 oe_msg_pub.add_exc_msg (g_pkg_name, 'Create_LineFork');
1038 RAISE fnd_api.g_exc_unexpected_error;
1039 END IF;
1040 END create_linefork;
1041
1042 FUNCTION get_wf_item_type (p_line_rec IN oe_order_pub.line_rec_type)
1043 RETURN VARCHAR2
1044 IS
1045 l_item_rec oe_order_cache.item_rec_type;
1046 --
1047 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1048 --
1049 BEGIN
1050 l_item_rec :=
1051 oe_order_cache.load_item (p_line_rec.inventory_item_id,
1052 p_line_rec.ship_from_org_id
1053 );
1054
1055 IF l_debug_level > 0
1056 THEN
1057 oe_debug_pub.ADD ('ITEM_TYPE_CODE IS ' || p_line_rec.item_type_code);
1058 END IF;
1059
1060 -- Code for Returns
1061 IF p_line_rec.line_category_code = 'RETURN'
1062 THEN
1063 RETURN 'STANDARD';
1064 END IF;
1065
1066 IF oe_ota_util.is_ota_line (p_line_rec.order_quantity_uom)
1067 THEN
1068 RETURN 'EDUCATION_ITEM';
1069 --ELSIF ( p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD AND
1070 -- l_item_rec.replenish_to_order_flag = 'Y') OR
1071
1072 -- ## 1820608 ato item under a top pto model should start ato_item flow.
1073 ELSIF ( p_line_rec.item_type_code = oe_globals.g_item_standard
1074 OR p_line_rec.item_type_code = oe_globals.g_item_option
1075 OR p_line_rec.item_type_code = oe_globals.g_item_included
1076 ) --9775352
1077 AND p_line_rec.ato_line_id = p_line_rec.line_id
1078 THEN
1079 RETURN 'ATO_ITEM';
1080 ELSIF ( p_line_rec.item_type_code = oe_globals.g_item_model
1081 AND p_line_rec.line_id = p_line_rec.ato_line_id
1082 )
1083 THEN
1084 RETURN 'ATO_MODEL';
1085 ELSIF (p_line_rec.item_type_code = oe_globals.g_item_config)
1086 THEN
1087 RETURN 'CONFIGURATION';
1088 ELSIF (p_line_rec.item_type_code = oe_globals.g_item_included)
1089 THEN
1090 RETURN 'II';
1091 ELSIF (p_line_rec.item_type_code = oe_globals.g_item_kit)
1092 THEN
1093 RETURN 'KIT';
1094 ELSIF ( p_line_rec.item_type_code = oe_globals.g_item_model
1095 AND p_line_rec.line_id = p_line_rec.top_model_line_id
1096 AND p_line_rec.ato_line_id IS NULL
1097 )
1098 THEN
1099 RETURN 'PTO_MODEL';
1100 ELSIF ( p_line_rec.item_type_code = oe_globals.g_item_class
1101 AND p_line_rec.ato_line_id IS NULL
1102 )
1103 THEN
1104 RETURN 'PTO_CLASS';
1105 ELSIF ( p_line_rec.item_type_code = oe_globals.g_item_option
1106 AND p_line_rec.ato_line_id IS NULL
1107 )
1108 THEN
1109 RETURN 'PTO_OPTION';
1110 -- for ato under pto, we want to start ato model flow
1111 -- even if the item_type_code is class. For ato under ato
1112 -- start standard flow.
1113 ELSIF ( p_line_rec.item_type_code = oe_globals.g_item_class
1114 AND p_line_rec.ato_line_id IS NOT NULL
1115 )
1116 THEN
1117 IF p_line_rec.ato_line_id = p_line_rec.line_id
1118 THEN
1119 RETURN 'ATO_MODEL';
1120 ELSE
1121 RETURN 'ATO_CLASS'; --FP bug no 4572207
1122 END IF;
1123 ELSIF ( p_line_rec.item_type_code = oe_globals.g_item_option
1124 AND p_line_rec.ato_line_id IS NOT NULL
1125 )
1126 THEN
1127 RETURN 'ATO_OPTION'; --FB bug no 4572207
1128 ELSIF (p_line_rec.item_type_code = oe_globals.g_item_standard)
1129 THEN
1130 RETURN 'STANDARD';
1131 ELSIF (p_line_rec.item_type_code = oe_globals.g_item_service)
1132 THEN
1133 RETURN 'SERVICE';
1134 ELSE
1135 fnd_message.set_name ('ONT', 'OE_INVALID_WF_ITEM_TYPE');
1136 oe_msg_pub.ADD;
1137 RAISE fnd_api.g_exc_error;
1138 END IF;
1139 EXCEPTION
1140 WHEN OTHERS
1141 THEN
1142 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
1143 THEN
1144 oe_msg_pub.add_exc_msg (g_pkg_name, 'Get_Wf_Item_Type');
1145 END IF;
1146
1147 RAISE fnd_api.g_exc_unexpected_error;
1148 END get_wf_item_type;
1149
1150 -- This procedure starts flows for all the Header and Line records created in a Process Order
1151 -- Transaction. The WF item has been created when the record is written to the db - post_write
1152 -- processing.
1153 PROCEDURE start_all_flows
1154 IS
1155 ctr NUMBER;
1156 l_type_id NUMBER;
1157 l_item_type_code VARCHAR2 (30);
1158 l_ato_line_id NUMBER;
1159 l_line_id NUMBER;
1160 l_item_type VARCHAR2 (30);
1161 l_line_rec oe_order_pub.line_rec_type;
1162 l_header_id NUMBER;
1163 --
1164 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1165 --
1166 BEGIN
1167 IF l_debug_level > 0
1168 THEN
1169 oe_debug_pub.ADD ('ENTERING START_ALL_FLOWS');
1170 END IF;
1171
1172 -- This code should be invoked again, once it starts executing
1173 -- Starting flows can cause the creation of new lines, but we do not
1174 -- want the creation call to get in here again.
1175 IF (NOT oe_globals.g_flow_processing_started)
1176 THEN
1177 oe_globals.g_flow_processing_started := TRUE;
1178
1179 IF l_debug_level > 0
1180 THEN
1181 oe_debug_pub.ADD ('FLOW PROCESSING STARTED');
1182 END IF;
1183
1184 -- Issue Savepoint
1185 SAVEPOINT start_all_flows;
1186
1187 -- Check if Header flow needs to be started
1188 IF (oe_globals.g_start_header_flow IS NOT NULL)
1189 THEN
1190 BEGIN
1191 -- Sanity Check to verify that header exists
1192 -- The create of the header could have been rolled back
1193 -- but the PL/SQL global is not in synch.
1194 SELECT order_type_id
1195 INTO l_type_id
1196 FROM oe_order_headers_all
1197 WHERE header_id = oe_globals.g_start_header_flow;
1198
1199 IF l_debug_level > 0
1200 THEN
1201 oe_debug_pub.ADD ('STARTING HEADER FLOW');
1202 END IF;
1203
1204 -- Start flow and clear global
1205 start_flow (oe_globals.g_wfi_hdr,
1206 oe_globals.g_start_header_flow
1207 );
1208 oe_globals.g_start_header_flow := NULL;
1209 EXCEPTION
1210 WHEN NO_DATA_FOUND
1211 THEN
1212 IF l_debug_level > 0
1213 THEN
1214 oe_debug_pub.ADD
1215 ( 'HEADER '
1216 || oe_globals.g_start_header_flow
1217 || ' IS MISSING , BUT ID EXISTS IN GLOBAL TABLE'
1218 );
1219 END IF;
1220
1221 oe_globals.g_start_header_flow := NULL;
1222 END;
1223 END IF;
1224
1225 -- Check if Line Flows need to be started
1226 IF (oe_globals.g_start_line_flows_tbl.COUNT > 0)
1227 THEN
1228 -- This global table can grow while it is being processed, hence we can cannot
1229 -- loop thru using first ... count. Starting a line flow can create included item
1230 -- lines (booking, scheduling activity).
1231 IF l_debug_level > 0
1232 THEN
1233 oe_debug_pub.ADD ('START LINE FLOWS');
1234 END IF;
1235
1236 ctr := oe_globals.g_start_line_flows_tbl.FIRST;
1237
1238 WHILE (ctr IS NOT NULL)
1239 LOOP
1240 IF l_debug_level > 0
1241 THEN
1242 oe_debug_pub.ADD
1243 ( 'INSIDE LOOP '
1244 || 'LINE ID IS '
1245 || oe_globals.g_start_line_flows_tbl (ctr).line_id
1246 );
1247 END IF;
1248
1249 BEGIN
1250 -- Sanity Check to verify that line exists
1251 -- The create of the line could have been rolled back
1252 -- but the PL/SQL table is not in synch.
1253
1254 -- Bug 3000619
1255 SELECT line_type_id, item_type_code, ato_line_id,
1256 line_id
1257 INTO l_type_id, l_item_type_code, l_ato_line_id,
1258 l_line_id
1259 FROM oe_order_lines_all
1260 WHERE line_id =
1261 oe_globals.g_start_line_flows_tbl (ctr).line_id;
1262
1263 IF l_debug_level > 0
1264 THEN
1265 oe_debug_pub.ADD
1266 ( 'STARTING LINE FLOW '
1267 || 'LINE ID = '
1268 || oe_globals.g_start_line_flows_tbl (ctr).line_id
1269 );
1270 END IF;
1271
1272 IF l_item_type_code = 'CLASS'
1273 AND l_ato_line_id IS NOT NULL
1274 AND l_ato_line_id <> l_line_id
1275 THEN
1276 IF l_debug_level > 0
1277 THEN
1278 oe_debug_pub.ADD
1279 ('need to check wf assignment for ato under ato under pto, 3'
1280 );
1281 END IF;
1282
1283 IF oe_globals.g_start_line_flows_tbl (ctr).post_write_ato_line_id <>
1284 l_ato_line_id
1285 THEN
1286 wf_engine.abortprocess
1287 (itemtype => oe_globals.g_wfi_lin,
1288 itemkey => oe_globals.g_start_line_flows_tbl
1289 (ctr).line_id
1290 );
1291 wf_purge.items
1292 (itemtype => oe_globals.g_wfi_lin,
1293 itemkey => oe_globals.g_start_line_flows_tbl
1294 (ctr).line_id,
1295 FORCE => TRUE,
1296 docommit => FALSE
1297 );
1298 oe_line_util.query_row
1299 (p_line_id => oe_globals.g_start_line_flows_tbl
1300 (ctr).line_id,
1301 x_line_rec => l_line_rec
1302 );
1303 l_item_type :=
1304 oe_order_wf_util.get_wf_item_type (l_line_rec);
1305 create_lineworkitem (l_line_rec, l_item_type);
1306 END IF; -- if post_write_ato_line_id <> l_ato_line_id
1307 END IF; -- item_type = CLASS
1308
1309 start_flow (oe_globals.g_wfi_lin,
1310 oe_globals.g_start_line_flows_tbl (ctr).line_id
1311 ); -- End bug 3000619
1312 EXCEPTION
1313 WHEN NO_DATA_FOUND
1314 THEN
1315 IF l_debug_level > 0
1316 THEN
1317 oe_debug_pub.ADD
1318 ( 'LINE '
1319 || oe_globals.g_start_line_flows_tbl (ctr).line_id
1320 || ' IS MISSING , BUT ID EXISTS IN GLOBAL TABLE'
1321 );
1322 END IF;
1323 END;
1324
1325 ctr := oe_globals.g_start_line_flows_tbl.NEXT (ctr);
1326 END LOOP;
1327
1328 -- Clear the Global table.
1329 oe_globals.g_start_line_flows_tbl.DELETE;
1330 END IF;
1331
1332 -- Start OENH/OEBH handling
1333 IF (oe_globals.g_start_negotiate_header_flow IS NOT NULL)
1334 THEN
1335 BEGIN
1336 -- Sanity Check to verify that negotiate header exists
1337 -- The create of the header could have been rolled back
1338 -- but the PL/SQL global is not in synch.
1339 IF oe_globals.g_sales_document_type_code = 'O'
1340 THEN
1341 SELECT header_id
1342 INTO l_header_id
1343 FROM oe_order_headers_all
1344 WHERE header_id = oe_globals.g_start_negotiate_header_flow;
1345 ELSIF oe_globals.g_sales_document_type_code = 'B'
1346 THEN
1347 SELECT header_id
1348 INTO l_header_id
1349 FROM oe_blanket_headers_all
1350 WHERE header_id = oe_globals.g_start_negotiate_header_flow;
1351 END IF;
1352
1353 IF l_debug_level > 0
1354 THEN
1355 oe_debug_pub.ADD ('STARTING NEGOTIATE HEADER FLOW');
1356 END IF;
1357
1358 -- Start flow and clear global
1359 start_flow (oe_globals.g_wfi_ngo,
1360 oe_globals.g_start_negotiate_header_flow
1361 );
1362 oe_globals.g_start_negotiate_header_flow := NULL;
1363 EXCEPTION
1364 WHEN NO_DATA_FOUND
1365 THEN
1366 IF l_debug_level > 0
1367 THEN
1368 oe_debug_pub.ADD
1369 ( 'Negotiate Header (OENH): '
1370 || oe_globals.g_start_negotiate_header_flow
1371 || ' IS MISSING , BUT ID EXISTS IN GLOBAL'
1372 );
1373 END IF;
1374
1375 oe_globals.g_start_negotiate_header_flow := NULL;
1376 END;
1377 END IF;
1378
1379 IF (oe_globals.g_start_blanket_header_flow IS NOT NULL)
1380 THEN
1381 BEGIN
1382 -- Sanity Check to verify that negotiate header exists
1383 -- The create of the header could have been rolled back
1384 -- but the PL/SQL global is not in synch.
1385 SELECT header_id
1386 INTO l_header_id
1387 FROM oe_blanket_headers_all
1388 WHERE header_id = oe_globals.g_start_blanket_header_flow;
1389
1390 IF l_debug_level > 0
1391 THEN
1392 oe_debug_pub.ADD ('STARTING BLANKET HEADER FLOW');
1393 END IF;
1394
1395 -- Start flow and clear global
1396 start_flow (oe_globals.g_wfi_bkt,
1397 oe_globals.g_start_blanket_header_flow
1398 );
1399 oe_globals.g_start_blanket_header_flow := NULL;
1400 EXCEPTION
1401 WHEN NO_DATA_FOUND
1402 THEN
1403 IF l_debug_level > 0
1404 THEN
1405 oe_debug_pub.ADD
1406 ( 'Blanket Header (OEBH): '
1407 || oe_globals.g_start_blanket_header_flow
1408 || ' IS MISSING , BUT ID EXISTS IN GLOBAL'
1409 );
1410 END IF;
1411
1412 oe_globals.g_start_blanket_header_flow := NULL;
1413 END;
1414 END IF;
1415
1416 -- End of OENH/OEBH handling
1417
1418 -- Reset global value
1419 oe_globals.g_flow_processing_started := FALSE;
1420 END IF;
1421
1422 IF l_debug_level > 0
1423 THEN
1424 oe_debug_pub.ADD ('EXITING START_ALL_FLOWS');
1425 END IF;
1426 EXCEPTION
1427 WHEN OTHERS
1428 THEN
1429 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
1430 THEN
1431 oe_msg_pub.add_exc_msg (g_pkg_name, 'Start_All_Flows');
1432 -- Rollback to savepoint
1433 ROLLBACK TO start_all_flows;
1434 -- Clear Globals
1435 clear_flowstart_globals;
1436 RAISE fnd_api.g_exc_unexpected_error;
1437 END IF;
1438 END start_all_flows;
1439
1440 PROCEDURE clear_flowstart_globals
1441 IS
1442 --
1443 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1444 --
1445 BEGIN
1446 IF l_debug_level > 0
1447 THEN
1448 oe_debug_pub.ADD ('IN CLEAR_FLOWSTART_GLOBALS');
1449 END IF;
1450
1451 oe_globals.g_start_header_flow := NULL;
1452 oe_globals.g_start_negotiate_header_flow := NULL;
1453 oe_globals.g_start_blanket_header_flow := NULL;
1454 oe_globals.g_start_line_flows_tbl.DELETE;
1455 oe_globals.g_flow_processing_started := FALSE;
1456 END clear_flowstart_globals;
1457
1458 PROCEDURE delete_row (p_type IN VARCHAR2, p_id IN NUMBER)
1459 IS
1460 l_status VARCHAR2 (30);
1461 l_result VARCHAR2 (240);
1462 l_count NUMBER;
1463 l_transaction_phase_code VARCHAR2 (30);
1464 --
1465 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1466 --
1467 BEGIN
1468 IF l_debug_level > 0
1469 THEN
1470 oe_debug_pub.ADD ('IN OE_ORDER_WF_UTIL.DELETE_ROW');
1471 END IF;
1472
1473 IF p_type = 'HEADER'
1474 THEN
1475 -- Start of 12609242
1476 SELECT COUNT (1)
1477 INTO l_count
1478 FROM wf_items
1479 WHERE item_type = oe_globals.g_wfi_hdr AND item_key = TO_CHAR (p_id);
1480
1481 IF l_count > 0
1482 THEN
1483 --End of 12609242
1484 wf_engine.itemstatus (itemtype => oe_globals.g_wfi_hdr,
1485 itemkey => p_id,
1486 status => l_status,
1487 RESULT => l_result
1488 );
1489
1490 IF l_status <> 'COMPLETE'
1491 THEN
1492 wf_engine.abortprocess (itemtype => oe_globals.g_wfi_hdr,
1493 itemkey => p_id
1494 );
1495 END IF;
1496
1497 wf_purge.items (itemtype => oe_globals.g_wfi_hdr,
1498 itemkey => p_id,
1499 FORCE => TRUE,
1500 docommit => FALSE
1501 );
1502 END IF; --12609242
1503
1504 SELECT COUNT (1)
1505 INTO l_count
1506 FROM wf_items
1507 WHERE item_type = oe_globals.g_wfi_ngo AND item_key = TO_CHAR (p_id);
1508
1509 IF l_count > 0
1510 THEN
1511 wf_purge.items (itemtype => oe_globals.g_wfi_ngo,
1512 itemkey => p_id,
1513 FORCE => TRUE,
1514 docommit => FALSE
1515 );
1516 END IF;
1517 ELSIF p_type = 'LINE'
1518 THEN
1519 SELECT transaction_phase_code
1520 INTO l_transaction_phase_code
1521 FROM oe_order_lines_all
1522 WHERE line_id = p_id;
1523
1524 IF NVL (l_transaction_phase_code, 'F') <> 'N'
1525 THEN
1526 -- Start of 12609242
1527 SELECT COUNT (1)
1528 INTO l_count
1529 FROM wf_items
1530 WHERE item_type = oe_globals.g_wfi_lin
1531 AND item_key = TO_CHAR (p_id);
1532
1533 IF l_count > 0
1534 THEN
1535 --End of 12609242
1536 wf_engine.itemstatus (itemtype => oe_globals.g_wfi_lin,
1537 itemkey => p_id,
1538 status => l_status,
1539 RESULT => l_result
1540 );
1541
1542 --Added the nvl condition to fix bug 2333095
1543 IF NVL (l_status, 'ACTIVE') <> 'COMPLETE'
1544 THEN
1545 wf_engine.abortprocess (itemtype => oe_globals.g_wfi_lin,
1546 itemkey => p_id
1547 );
1548 END IF;
1549
1550 IF l_debug_level > 0
1551 THEN
1552 oe_debug_pub.ADD ('PURGING WF ITEM');
1553 END IF;
1554
1555 wf_purge.items (itemtype => oe_globals.g_wfi_lin,
1556 itemkey => p_id,
1557 FORCE => TRUE,
1558 docommit => FALSE
1559 );
1560 END IF; --12609242
1561 END IF;
1562 ELSIF p_type = 'NEGOTIATE'
1563 THEN
1564 --Start of 13020709
1565 SELECT COUNT (1)
1566 INTO l_count
1567 FROM wf_items
1568 WHERE item_type = oe_globals.g_wfi_ngo AND item_key = TO_CHAR (p_id);
1569
1570 IF l_count > 0
1571 THEN
1572 --End of 13020709
1573 wf_engine.itemstatus (itemtype => oe_globals.g_wfi_ngo,
1574 itemkey => p_id,
1575 status => l_status,
1576 RESULT => l_result
1577 );
1578
1579 IF NVL (l_status, 'ACTIVE') <> 'COMPLETE'
1580 THEN
1581 wf_engine.abortprocess (itemtype => oe_globals.g_wfi_ngo,
1582 itemkey => p_id
1583 );
1584 END IF;
1585
1586 IF l_debug_level > 0
1587 THEN
1588 oe_debug_pub.ADD ('PURGING WF ITEM - Negotiate');
1589 END IF;
1590
1591 wf_purge.items (itemtype => oe_globals.g_wfi_ngo,
1592 itemkey => p_id,
1593 FORCE => TRUE,
1594 docommit => FALSE
1595 );
1596 END IF; --13020709
1597 ELSIF p_type = 'BLANKET'
1598 THEN
1599 -- Bug 8537639
1600 SELECT COUNT (1)
1601 INTO l_count
1602 FROM wf_items
1603 --where item_type=OE_GLOBALS.G_WFI_NGO
1604 WHERE item_type = oe_globals.g_wfi_bkt --13020709
1605 AND item_key = TO_CHAR (p_id);
1606
1607 IF l_count > 0
1608 THEN
1609 -- Bug 8537639
1610 wf_engine.itemstatus (itemtype => oe_globals.g_wfi_bkt,
1611 itemkey => p_id,
1612 status => l_status,
1613 RESULT => l_result
1614 );
1615
1616 IF NVL (l_status, 'ACTIVE') <> 'COMPLETE'
1617 THEN
1618 wf_engine.abortprocess (itemtype => oe_globals.g_wfi_bkt,
1619 itemkey => p_id
1620 );
1621 END IF;
1622
1623 IF l_debug_level > 0
1624 THEN
1625 oe_debug_pub.ADD ('PURGING WF ITEM - BLANKET');
1626 END IF;
1627
1628 wf_purge.items (itemtype => oe_globals.g_wfi_bkt,
1629 itemkey => p_id,
1630 FORCE => TRUE,
1631 docommit => FALSE
1632 );
1633
1634 /* Bug 8537639 */
1635 --Uncommented below Select clause for bug 13020709
1636 SELECT COUNT (1)
1637 INTO l_count
1638 FROM wf_items
1639 WHERE item_type = oe_globals.g_wfi_ngo
1640 AND item_key = TO_CHAR (p_id);
1641
1642 IF l_count > 0
1643 THEN
1644 wf_purge.items (itemtype => oe_globals.g_wfi_ngo,
1645 itemkey => p_id,
1646 FORCE => TRUE,
1647 docommit => FALSE
1648 );
1649 END IF;
1650 END IF; --13020709
1651 ELSE
1652 IF l_debug_level > 0
1653 THEN
1654 oe_debug_pub.ADD
1655 ('OE_ORDER_WF_UTIL: DELETE TYPE NOT IN HEADER,LINE,NEGOTIATE,BLANKET'
1656 );
1657 END IF;
1658
1659 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
1660 THEN
1661 oe_msg_pub.add_exc_msg (g_pkg_name, 'Delete_Row');
1662 RAISE fnd_api.g_exc_unexpected_error;
1663 END IF;
1664 END IF;
1665
1666 IF l_debug_level > 0
1667 THEN
1668 oe_debug_pub.ADD ('EXITING OE_ORDER_WF_UTIL.DELETE_ROW');
1669 END IF;
1670 END delete_row;
1671
1672 PROCEDURE update_flow_status_code (
1673 p_header_id IN NUMBER DEFAULT NULL,
1674 p_line_id IN NUMBER DEFAULT NULL,
1675 p_flow_status_code IN VARCHAR2,
1676 p_item_type IN VARCHAR2 DEFAULT NULL,
1677 p_sales_document_type_code IN VARCHAR2 DEFAULT NULL,
1678 x_return_status OUT NOCOPY VARCHAR2
1679 )
1680 IS
1681 l_flow_status_code VARCHAR2 (30);
1682 l_header_rec oe_order_pub.header_rec_type;
1683 l_old_header_rec oe_order_pub.header_rec_type;
1684 l_line_tbl oe_order_pub.line_tbl_type;
1685 l_old_line_tbl oe_order_pub.line_tbl_type;
1686 l_return_status VARCHAR2 (30);
1687 l_source_document_id NUMBER;
1688 l_source_document_line_id NUMBER;
1689 l_header_id NUMBER;
1690 l_orig_sys_document_ref VARCHAR2 (50);
1691 l_orig_sys_line_ref VARCHAR2 (50);
1692 l_order_source_id NUMBER;
1693 l_orig_sys_shipment_ref VARCHAR2 (50);
1694 l_change_sequence VARCHAR2 (50);
1695 l_source_document_type_id NUMBER;
1696 l_index NUMBER;
1697 l_blanket_lock_control NUMBER;
1698 l_msg_count NUMBER;
1699 l_msg_data VARCHAR2 (2000);
1700 l_itemkey_sso NUMBER; -- GENESIS
1701 --
1702 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1703 --
1704 l_line_rec oe_order_pub.line_rec_type; --OIP ER
1705
1706 CURSOR lines
1707 IS
1708 SELECT line_id, lock_control
1709 FROM oe_order_lines_all
1710 WHERE header_id = p_header_id
1711 FOR UPDATE NOWAIT;
1712
1713 l_line_id NUMBER;
1714 l_lock_control NUMBER;
1715 BEGIN
1716 IF l_debug_level > 0
1717 THEN
1718 oe_debug_pub.ADD ('ENTERING UPDATE_FLOW_STATUS_CODE', 5);
1719 oe_debug_pub.ADD ( 'UFSC: GLOBAL RECURSION WITHOUT EXCEPTION: '
1720 || oe_order_util.g_recursion_without_exception
1721 );
1722 oe_debug_pub.ADD ( 'UFSC: GLOBAL CACHE BOOKED FLAG'
1723 || oe_order_cache.g_header_rec.booked_flag
1724 );
1725 oe_debug_pub.ADD ( 'UFSC: GLOBAL PICTURE HEADER BOOKED FLAG'
1726 || oe_order_util.g_header_rec.booked_flag
1727 );
1728 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW LINE TABLE= '
1729 || oe_order_util.g_line_tbl.COUNT
1730 );
1731 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD LINE TABLE= '
1732 || oe_order_util.g_old_line_tbl.COUNT
1733 );
1734 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW LINE ADJ TABLE= '
1735 || oe_order_util.g_line_adj_tbl.COUNT
1736 );
1737 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD LINE ADJ TABLE= '
1738 || oe_order_util.g_old_line_adj_tbl.COUNT
1739 );
1740 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW HDR ADJ TABLE= '
1741 || oe_order_util.g_header_adj_tbl.COUNT
1742 );
1743 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD HDR ADJ TABLE= '
1744 || oe_order_util.g_old_header_adj_tbl.COUNT
1745 );
1746 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW HDR SCREDIT TABLE= '
1747 || oe_order_util.g_header_scredit_tbl.COUNT
1748 );
1749 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD HDR SCREDIT TABLE= '
1750 || oe_order_util.g_old_header_scredit_tbl.COUNT
1751 );
1752 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW LINE SCREDIT TABLE= '
1753 || oe_order_util.g_line_scredit_tbl.COUNT
1754 );
1755 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD LINE SCREDIT TABLE= '
1756 || oe_order_util.g_old_line_scredit_tbl.COUNT
1757 );
1758 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW LOT SERIAL TABLE= '
1759 || oe_order_util.g_lot_serial_tbl.COUNT
1760 );
1761 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD LOT SERIAL TABLE= '
1762 || oe_order_util.g_old_lot_serial_tbl.COUNT
1763 );
1764 END IF;
1765
1766 SAVEPOINT update_flow_status_code;
1767 x_return_status := fnd_api.g_ret_sts_success;
1768
1769 -- Check if the ASO is installed to call the NOTIFY_OC.
1770 IF oe_globals.g_aso_installed IS NULL
1771 THEN
1772 oe_globals.g_aso_installed :=
1773 oe_globals.check_product_installed (697);
1774 END IF;
1775
1776 -- *** Negotiation Changes Start ***
1777 IF p_item_type IN (oe_globals.g_wfi_ngo, oe_globals.g_wfi_bkt)
1778 THEN
1779 IF p_header_id IS NOT NULL
1780 THEN
1781 -- validate p_flow_status
1782 SELECT lookup_code
1783 INTO l_flow_status_code
1784 FROM oe_lookups
1785 WHERE lookup_type = 'FLOW_STATUS'
1786 AND lookup_code = p_flow_status_code
1787 AND enabled_flag = 'Y'
1788 AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)
1789 AND NVL (end_date_active, SYSDATE);
1790
1791 IF p_item_type = oe_globals.g_wfi_ngo
1792 THEN
1793 IF p_sales_document_type_code IS NULL
1794 THEN
1795 -- for Negotiation, you must pass the document type code
1796 RAISE fnd_api.g_exc_unexpected_error;
1797 END IF;
1798
1799 IF p_sales_document_type_code = 'O'
1800 THEN
1801 IF l_debug_level > 0
1802 THEN
1803 oe_debug_pub.ADD
1804 ( 'Update_Flow_Status_Code for Quote:'
1805 || p_flow_status_code,
1806 5
1807 );
1808 END IF;
1809
1810 oe_header_util.lock_row (p_header_id => p_header_id,
1811 p_x_header_rec => l_header_rec,
1812 x_return_status => l_return_status
1813 );
1814
1815 IF l_return_status = fnd_api.g_ret_sts_error
1816 THEN
1817 RAISE fnd_api.g_exc_error;
1818 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1819 THEN
1820 RAISE fnd_api.g_exc_unexpected_error;
1821 END IF;
1822
1823 -- is entity HEADER correct?
1824 oe_msg_pub.set_msg_context
1825 (p_entity_code => 'HEADER',
1826 p_entity_id => l_header_rec.header_id,
1827 p_header_id => l_header_rec.header_id,
1828 p_line_id => NULL,
1829 p_order_source_id => l_header_rec.order_source_id,
1830 p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref,
1831 p_orig_sys_document_line_ref => NULL,
1832 p_change_sequence => l_header_rec.change_sequence,
1833 p_source_document_type_id => l_header_rec.source_document_type_id,
1834 p_source_document_id => l_header_rec.source_document_id,
1835 p_source_document_line_id => NULL
1836 );
1837 l_old_header_rec := l_header_rec;
1838
1839 UPDATE oe_order_headers_all
1840 SET flow_status_code = p_flow_status_code
1841 --Bug 8435596
1842 ,
1843 last_update_date = SYSDATE,
1844 last_updated_by = fnd_global.user_id,
1845 last_update_login = fnd_global.login_id,
1846 lock_control = lock_control + 1
1847 WHERE header_id = p_header_id;
1848
1849 -- Also update all lines to have the same flow_status_code for quotes
1850 OPEN lines;
1851
1852 LOOP
1853 FETCH lines
1854 INTO l_line_id, l_lock_control;
1855
1856 EXIT WHEN lines%NOTFOUND;
1857 END LOOP;
1858
1859 CLOSE lines;
1860
1861 UPDATE oe_order_lines_all
1862 SET flow_status_code = p_flow_status_code
1863 --Bug 8435596
1864 ,
1865 last_update_date = SYSDATE,
1866 last_updated_by = fnd_global.user_id,
1867 last_update_login = fnd_global.login_id,
1868 lock_control = lock_control + 1
1869 WHERE header_id = p_header_id;
1870
1871 l_header_rec.flow_status_code := p_flow_status_code;
1872 l_header_rec.lock_control := l_header_rec.lock_control + 1;
1873 -- aksingh performance
1874 -- As the update is on headers table, it is time to update
1875 -- cache also!
1876 oe_order_cache.set_order_header (l_header_rec);
1877 -- Bug 1755817: clear the cached constraint results for header entity
1878 -- when order header is updated.
1879 oe_pc_constraints_admin_pvt.clear_cached_results
1880 (p_validation_entity_id => oe_pc_globals.g_entity_header);
1881 -- added for notification framework
1882 -- calling notification framework to get index position
1883 oe_order_util.update_global_picture
1884 (p_upd_new_rec_if_exists => FALSE,
1885 p_old_header_rec => l_old_header_rec,
1886 p_header_rec => l_header_rec,
1887 p_header_id => p_header_id,
1888 x_index => l_index,
1889 x_return_status => l_return_status
1890 );
1891
1892 IF l_debug_level > 0
1893 THEN
1894 oe_debug_pub.ADD
1895 ( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: '
1896 || l_return_status
1897 );
1898 oe_debug_pub.ADD ('INDEX IS: ' || l_index, 1);
1899 oe_debug_pub.ADD ( 'HEADER FLOW STATUS IS: '
1900 || p_flow_status_code,
1901 1
1902 );
1903 END IF;
1904
1905 IF (l_return_status <> fnd_api.g_ret_sts_success)
1906 THEN
1907 RAISE fnd_api.g_exc_unexpected_error;
1908 END IF;
1909
1910 IF l_index IS NOT NULL
1911 THEN
1912 -- update global picture directly
1913 oe_order_util.g_header_rec :=
1914 oe_order_util.g_old_header_rec;
1915 oe_order_util.g_header_rec.flow_status_code :=
1916 p_flow_status_code;
1917 oe_order_util.g_header_rec.last_update_date :=
1918 l_header_rec.last_update_date;
1919 oe_order_util.g_header_rec.operation :=
1920 oe_globals.g_opr_update;
1921
1922 IF l_debug_level > 0
1923 THEN
1924 oe_debug_pub.ADD
1925 ( 'GLOBAL HEADER FLOW STATUS IS: '
1926 || oe_order_util.g_header_rec.flow_status_code,
1927 1
1928 );
1929 oe_debug_pub.ADD
1930 ( 'GLOBAL HEADER OPERATION IS: '
1931 || oe_order_util.g_header_rec.operation,
1932 1
1933 );
1934 END IF;
1935 END IF;
1936
1937 -- bug 4732614
1938 IF l_debug_level > 0
1939 THEN
1940 oe_debug_pub.ADD
1941 ('OEXUOWFB.pls: Calling Process_Requests_And_Notify......',
1942 1
1943 );
1944 END IF;
1945
1946 oe_order_pvt.process_requests_and_notify
1947 (p_header_rec => l_header_rec,
1948 p_old_header_rec => l_old_header_rec,
1949 x_return_status => l_return_status
1950 );
1951
1952 IF l_return_status = fnd_api.g_ret_sts_error
1953 THEN
1954 IF l_debug_level > 0
1955 THEN
1956 oe_debug_pub.ADD
1957 ( 'Process_Requests_And_Notify,return_status='
1958 || l_return_status
1959 || ',Raising FND_API.G_EXC_ERROR exception',
1960 2
1961 );
1962 END IF;
1963
1964 RAISE fnd_api.g_exc_error;
1965 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1966 THEN
1967 IF l_debug_level > 0
1968 THEN
1969 oe_debug_pub.ADD
1970 ( 'Process_Requests_And_Notify,return_status='
1971 || l_return_status
1972 || ',Raising FND_API.G_EXC_UNEXPECTED_ERROR exception',
1973 2
1974 );
1975 END IF;
1976
1977 RAISE fnd_api.g_exc_unexpected_error;
1978 END IF; -- bug 4732614 ends
1979 ELSIF p_sales_document_type_code = 'B'
1980 THEN -- Blanket Negotitation
1981 IF l_debug_level > 0
1982 THEN
1983 oe_debug_pub.ADD
1984 ( 'Update_Flow_Status_Code for Blanket Negotiation:'
1985 || p_flow_status_code,
1986 5
1987 );
1988 END IF;
1989
1990 SELECT lock_control
1991 INTO l_blanket_lock_control
1992 FROM oe_blanket_headers_all
1993 WHERE header_id = p_header_id
1994 FOR UPDATE NOWAIT;
1995
1996 /* avoid dependency on blanket code, do a direct lock row
1997
1998 OE_Blanket_Util.Lock_Row(p_blanket_id=>p_header_id
1999 , p_blanket_line_id => null
2000 , p_x_lock_control=>l_blanket_lock_control
2001 , x_return_status => l_return_status
2002 , x_msg_count => l_msg_count
2003 , x_msg_data => l_msg_data);
2004 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2005 RAISE FND_API.G_EXC_ERROR;
2006 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2008 END IF;
2009
2010 OE_MSG_PUB.set_msg_context
2011 ( p_entity_code => 'BLANKET'
2012 ,p_entity_id => p_header_id
2013 ,p_header_id => p_header_id);
2014
2015
2016 */
2017 UPDATE oe_blanket_headers_all
2018 SET flow_status_code = p_flow_status_code
2019 --Bug 8435596
2020 ,
2021 last_update_date = SYSDATE,
2022 last_updated_by = fnd_global.user_id,
2023 lock_control = lock_control + 1
2024 WHERE header_id = p_header_id;
2025 END IF; --check sales_document_type_code
2026 ELSIF p_item_type = oe_globals.g_wfi_bkt
2027 THEN
2028 IF l_debug_level > 0
2029 THEN
2030 oe_debug_pub.ADD
2031 ( 'Update_Flow_Status_Code for Blanket Fulfillment:'
2032 || p_flow_status_code,
2033 5
2034 );
2035 END IF;
2036
2037 -- lock row or select for update here
2038 SELECT lock_control
2039 INTO l_blanket_lock_control
2040 FROM oe_blanket_headers_all
2041 WHERE header_id = p_header_id
2042 FOR UPDATE NOWAIT;
2043
2044 /* avoid dependency on blanket API
2045 OE_Blanket_Util.Lock_Row(p_blanket_id=>p_header_id
2046 , p_blanket_line_id => null
2047 , p_x_lock_control=>l_blanket_lock_control
2048 , x_return_status => l_return_status
2049 , x_msg_count => l_msg_count
2050 , x_msg_data => l_msg_data);
2051 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2052 RAISE FND_API.G_EXC_ERROR;
2053 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2055 END IF;
2056 */-- set msg context follows
2057 UPDATE oe_blanket_headers_all
2058 SET flow_status_code = p_flow_status_code
2059 --Bug 8435596
2060 ,
2061 last_update_date = SYSDATE,
2062 last_updated_by = fnd_global.user_id,
2063 last_update_login = fnd_global.login_id,
2064 lock_control = lock_control + 1
2065 WHERE header_id = p_header_id;
2066 END IF;
2067 END IF; -- p_header_id is not null
2068 ELSE
2069 -- *** END negotiation/blanket changes ***
2070
2071 -- regular processing for OEOH/OEOL starts below
2072
2073 -- we will process the line_id if both header id and line id are passed
2074 IF p_line_id IS NOT NULL
2075 THEN
2076 -- validate p_flow_status
2077 SELECT lookup_code
2078 INTO l_flow_status_code
2079 FROM oe_lookups
2080 WHERE lookup_type = 'LINE_FLOW_STATUS'
2081 AND lookup_code = p_flow_status_code
2082 AND enabled_flag = 'Y'
2083 AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)
2084 AND NVL (end_date_active, SYSDATE);
2085
2086 IF ( (oe_globals.g_aso_installed = 'Y')
2087 OR (NVL (fnd_profile.VALUE ('ONT_DBI_INSTALLED'), 'N') = 'Y')
2088 )
2089 THEN
2090 oe_line_util.lock_rows (p_line_id => p_line_id,
2091 x_line_tbl => l_old_line_tbl,
2092 x_return_status => l_return_status
2093 );
2094
2095 IF l_return_status = fnd_api.g_ret_sts_error
2096 THEN
2097 RAISE fnd_api.g_exc_error;
2098 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2099 THEN
2100 RAISE fnd_api.g_exc_unexpected_error;
2101 END IF;
2102
2103 oe_msg_pub.set_msg_context
2104 (p_entity_code => 'LINE',
2105 p_entity_id => l_old_line_tbl (1).line_id,
2106 p_header_id => l_old_line_tbl (1).header_id,
2107 p_line_id => l_old_line_tbl (1).line_id,
2108 p_order_source_id => l_old_line_tbl (1).order_source_id,
2109 p_orig_sys_document_ref => l_old_line_tbl (1).orig_sys_document_ref,
2110 p_orig_sys_document_line_ref => l_old_line_tbl (1).orig_sys_line_ref,
2111 p_orig_sys_shipment_ref => l_old_line_tbl (1).orig_sys_shipment_ref,
2112 p_change_sequence => l_old_line_tbl (1).change_sequence,
2113 p_source_document_type_id => l_old_line_tbl (1).source_document_type_id,
2114 p_source_document_id => l_old_line_tbl (1).source_document_id,
2115 p_source_document_line_id => l_old_line_tbl (1).source_document_line_id
2116 );
2117 l_line_tbl := l_old_line_tbl;
2118 ELSE
2119 SELECT source_document_id, source_document_line_id,
2120 header_id, orig_sys_document_ref,
2121 orig_sys_line_ref, order_source_id,
2122 orig_sys_shipment_ref, change_sequence,
2123 source_document_type_id
2124 INTO l_source_document_id, l_source_document_line_id,
2125 l_header_id, l_orig_sys_document_ref,
2126 l_orig_sys_line_ref, l_order_source_id,
2127 l_orig_sys_shipment_ref, l_change_sequence,
2128 l_source_document_type_id
2129 FROM oe_order_lines_all
2130 WHERE line_id = p_line_id
2131 FOR UPDATE NOWAIT;
2132
2133 oe_msg_pub.set_msg_context
2134 (p_entity_code => 'LINE',
2135 p_entity_id => p_line_id,
2136 p_header_id => l_header_id,
2137 p_line_id => p_line_id,
2138 p_order_source_id => l_order_source_id,
2139 p_orig_sys_document_ref => l_orig_sys_document_ref,
2140 p_orig_sys_document_line_ref => l_orig_sys_line_ref,
2141 p_orig_sys_shipment_ref => l_orig_sys_shipment_ref,
2142 p_change_sequence => l_change_sequence,
2143 p_source_document_type_id => l_source_document_type_id,
2144 p_source_document_id => l_source_document_id,
2145 p_source_document_line_id => l_source_document_line_id
2146 );
2147 --7138604 : l_line_tbl(1) is used below, so it has to be
2148 -- initialized in order to prevent NO_DATA_FOUND exception
2149 l_line_tbl (1) := oe_order_pub.g_miss_line_rec;
2150 END IF;
2151 ELSIF p_header_id IS NOT NULL
2152 THEN
2153 -- validate p_flow_status
2154 SELECT lookup_code
2155 INTO l_flow_status_code
2156 FROM oe_lookups
2157 WHERE lookup_type = 'FLOW_STATUS'
2158 AND lookup_code = p_flow_status_code
2159 AND enabled_flag = 'Y'
2160 AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)
2161 AND NVL (end_date_active, SYSDATE);
2162
2163 oe_header_util.lock_row (p_header_id => p_header_id,
2164 p_x_header_rec => l_header_rec,
2165 x_return_status => l_return_status
2166 );
2167
2168 IF l_return_status = fnd_api.g_ret_sts_error
2169 THEN
2170 RAISE fnd_api.g_exc_error;
2171 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2172 THEN
2173 RAISE fnd_api.g_exc_unexpected_error;
2174 END IF;
2175
2176 oe_msg_pub.set_msg_context
2177 (p_entity_code => 'HEADER',
2178 p_entity_id => l_header_rec.header_id,
2179 p_header_id => l_header_rec.header_id,
2180 p_line_id => NULL,
2181 p_order_source_id => l_header_rec.order_source_id,
2182 p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref,
2183 p_orig_sys_document_line_ref => NULL,
2184 p_change_sequence => l_header_rec.change_sequence,
2185 p_source_document_type_id => l_header_rec.source_document_type_id,
2186 p_source_document_id => l_header_rec.source_document_id,
2187 p_source_document_line_id => NULL
2188 );
2189 l_old_header_rec := l_header_rec;
2190 END IF;
2191
2192 IF p_line_id IS NOT NULL
2193 THEN
2194 UPDATE oe_order_lines_all
2195 SET flow_status_code = p_flow_status_code,
2196 last_update_date = SYSDATE,
2197 --last_updated_by = FND_GLOBAL.USER_ID,
2198 last_updated_by =
2199 NVL (oe_standard_wf.g_user_id, fnd_global.user_id),
2200 -- For bug 9386040
2201 last_update_login = fnd_global.login_id,
2202 lock_control = lock_control + 1
2203 WHERE line_id = p_line_id;
2204
2205 IF (l_debug_level > 0)
2206 THEN
2207 oe_debug_pub.ADD
2208 ( 'Calling raise business Event for SCCC p_header_id :'
2209 || p_header_id
2210 || ' line_id :'
2211 || p_line_id
2212 || ' status :'
2213 || p_flow_status_code
2214 );
2215 END IF;
2216
2217 -- ADDED FULFILLED CONDITION FOR BUG 13534511
2218 --OIP SUN ER Changes
2219 IF p_flow_status_code = 'SHIPPED'
2220 OR p_flow_status_code = 'FULFILLED'
2221 THEN
2222 oe_line_util.query_row (p_line_id, l_line_rec);
2223 oe_order_util.raise_business_event (l_line_rec.header_id,
2224 l_line_rec.line_id,
2225 p_flow_status_code
2226 );
2227 END IF;
2228
2229 IF ( (oe_globals.g_aso_installed = 'Y')
2230 OR (NVL (fnd_profile.VALUE ('ONT_DBI_INSTALLED'), 'N') = 'Y')
2231 )
2232 THEN
2233 l_line_tbl (1).flow_status_code := p_flow_status_code;
2234 l_line_tbl (1).lock_control := l_line_tbl (1).lock_control + 1;
2235 -- added for notification framework
2236 -- calling notification framework to get index position
2237 oe_order_util.update_global_picture
2238 (p_upd_new_rec_if_exists => FALSE,
2239 p_header_id => l_line_tbl
2240 (1).header_id,
2241 p_old_line_rec => l_old_line_tbl
2242 (1),
2243 p_line_rec => l_line_tbl
2244 (1),
2245 p_line_id => p_line_id,
2246 x_index => l_index,
2247 x_return_status => l_return_status
2248 );
2249
2250 IF l_debug_level > 0
2251 THEN
2252 oe_debug_pub.ADD
2253 ( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE LINE FLOW STATUS CODE IS: '
2254 || l_return_status
2255 );
2256 oe_debug_pub.ADD ('INDEX IS: ' || l_index, 1);
2257 END IF;
2258
2259 --OE_DEBUG_PUB.ADD('Line Flow Status is: ' || p_flow_status_code ,1);
2260 IF (l_return_status <> fnd_api.g_ret_sts_success)
2261 THEN
2262 RAISE fnd_api.g_exc_unexpected_error;
2263 END IF;
2264
2265 IF l_index IS NOT NULL
2266 THEN
2267 -- update global picture directly
2268 -- First copy the old picture to the new and then update the
2269 -- changed columns for the new global table.
2270 --12930466 OE_ORDER_UTIL.g_old_line_tbl(l_index) := l_old_line_tbl(1); --Added for bug 5842114
2271 --12930466 OE_ORDER_UTIL.g_line_tbl(l_index) := OE_ORDER_UTIL.g_old_line_tbl(l_index);
2272
2273 -- Added below condition for bug 13101601
2274 IF NOT oe_order_util.g_line_tbl.EXISTS (l_index)
2275 THEN
2276 oe_order_util.g_line_tbl (l_index) :=
2277 oe_order_util.g_old_line_tbl (l_index);
2278 END IF;
2279
2280 -- End of bug 13101601
2281 oe_order_util.g_line_tbl (l_index).flow_status_code :=
2282 p_flow_status_code;
2283 oe_order_util.g_line_tbl (l_index).lock_control :=
2284 l_line_tbl (1).lock_control;
2285 oe_order_util.g_line_tbl (l_index).line_id :=
2286 l_line_tbl (1).line_id;
2287 oe_order_util.g_line_tbl (l_index).header_id :=
2288 l_line_tbl (1).header_id;
2289 oe_order_util.g_line_tbl (l_index).last_update_date :=
2290 l_line_tbl (1).last_update_date;
2291 oe_order_util.g_line_tbl (l_index).operation :=
2292 oe_globals.g_opr_update;
2293
2294 IF l_debug_level > 0
2295 THEN
2296 oe_debug_pub.ADD
2297 ( 'GLOBAL LINE FLOW STATUS IS: '
2298 || oe_order_util.g_line_tbl (l_index).flow_status_code,
2299 1
2300 );
2301 oe_debug_pub.ADD
2302 ( 'GLOBAL LINE OPERATION IS: '
2303 || oe_order_util.g_line_tbl (l_index).operation,
2304 1
2305 );
2306 END IF;
2307 END IF;
2308
2309 -- bug 4732614
2310 IF l_debug_level > 0
2311 THEN
2312 oe_debug_pub.ADD
2313 ('OEXUOWFB.pls: Calling Process_Requests_And_Notify......',
2314 1
2315 );
2316 END IF;
2317
2318 oe_order_pvt.process_requests_and_notify
2319 (p_line_tbl => l_line_tbl,
2320 p_old_line_tbl => l_old_line_tbl,
2321 x_return_status => l_return_status
2322 );
2323
2324 IF l_return_status = fnd_api.g_ret_sts_error
2325 THEN
2326 IF l_debug_level > 0
2327 THEN
2328 oe_debug_pub.ADD
2329 ( 'Process_Requests_And_Notify,return_status='
2330 || l_return_status
2331 || ',Raising FND_API.G_EXC_ERROR exception',
2332 2
2333 );
2334 END IF;
2335
2336 RAISE fnd_api.g_exc_error;
2337 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2338 THEN
2339 IF l_debug_level > 0
2340 THEN
2341 oe_debug_pub.ADD
2342 ( 'Process_Requests_And_Notify,return_status='
2343 || l_return_status
2344 || ',Raising FND_API.G_EXC_UNEXPECTED_ERROR exception',
2345 2
2346 );
2347 END IF;
2348
2349 RAISE fnd_api.g_exc_unexpected_error;
2350 END IF; -- bug 4732614 ends
2351 END IF; /*ASO installed */
2352
2353 oe_msg_pub.reset_msg_context ('LINE');
2354
2355 /********************GENESIS********************************
2356 * Some statuses are not going through process order and *
2357 * the update_flow_status is getting called directly. So *
2358 * we need to call synch_header_line for AIA enabled order *
2359 * sources. *
2360 ***********************************************************/
2361 IF l_debug_level > 0
2362 THEN
2363 oe_debug_pub.ADD
2364 ( ' GENESIS : UPDATE FLOW STATUS - p_header_id: '
2365 || p_header_id
2366 );
2367 oe_debug_pub.ADD
2368 ( ' GENESIS : UPDATE FLOW STATUS - p_line_id: '
2369 || p_line_id
2370 );
2371 oe_debug_pub.ADD
2372 ( ' GENESIS : UPDATE FLOW STATUS - l_header_rec.order_source_id: '
2373 || l_header_rec.order_source_id
2374 );
2375 oe_debug_pub.ADD
2376 ( ' GENESIS : UPDATE FLOW STATUS - l_order_source_id: '
2377 || l_order_source_id
2378 );
2379 oe_debug_pub.ADD
2380 ( ' GENESIS : UPDATE FLOW STATUS - l_line_tbl(1).order_source_id: '
2381 || l_line_tbl (1).order_source_id
2382 );
2383 oe_debug_pub.ADD
2384 ( ' GENESIS : UPDATE FLOW STATUS - l_line_tbl(1).header_id: '
2385 || l_line_tbl (1).header_id
2386 );
2387 oe_debug_pub.ADD
2388 ( ' GENESIS : UPDATE FLOW STATUS - g_aso_installed: '
2389 || oe_globals.g_aso_installed
2390 );
2391 oe_debug_pub.ADD
2392 ( ' GENESIS : UPDATE FLOW STATUS - DBI Installed: '
2393 || NVL (fnd_profile.VALUE ('ONT_DBI_INSTALLED'),
2394 'No.'
2395 )
2396 );
2397 oe_debug_pub.ADD
2398 ( ' GENESIS : UPDATE FLOW STATUS - p_flow_status_code: '
2399 || p_flow_status_code
2400 );
2401 END IF;
2402
2403 ----
2404 -- Bug 12313333
2405 --
2406 -- CTO workflow sets the flow_status_code to 'EXTERNAL_' || 'REQ_REQUESTED'
2407 -- during b2b line processing directly through a call to this
2408 -- procedure Update_Flow_Status_Code(...). To enable the O2C business
2409 -- event raise (when both ASO as well as DBI are installed) during
2410 -- such a status change, we introduce this check on the flow status
2411 -- code as well.
2412 ----
2413 -- The following call to raise Business Event has been done for bug 13534511
2414 IF ( p_flow_status_code LIKE '%REQ_REQUESTED'
2415 OR NOT ( (oe_globals.g_aso_installed = 'Y')
2416 OR (NVL (fnd_profile.VALUE ('ONT_DBI_INSTALLED'),
2417 'N'
2418 ) = 'Y'
2419 )
2420 )
2421 )
2422 AND ( (oe_genesis_util.source_aia_enabled (l_order_source_id)
2423 )
2424 OR (oe_genesis_util.source_aia_enabled
2425 (l_line_tbl (1).order_source_id
2426 )
2427 )
2428 )
2429 AND oe_genesis_util.status_needs_sync (p_flow_status_code)
2430 THEN
2431 oe_line_util.query_row (p_line_id => p_line_id,
2432 x_line_rec => l_line_tbl (1)
2433 );
2434 l_line_tbl (1).flow_status_code := p_flow_status_code;
2435 l_line_tbl (1).lock_control := l_line_tbl (1).lock_control + 1;
2436 oe_header_util.query_row
2437 (p_header_id => l_line_tbl (1).header_id,
2438 x_header_rec => l_header_rec
2439 );
2440
2441 SELECT oe_xml_message_seq_s.NEXTVAL
2442 INTO l_itemkey_sso
2443 FROM DUAL;
2444
2445 IF l_debug_level > 0
2446 THEN
2447 oe_debug_pub.ADD
2448 ( ' GENESIS : UPDATE FLOW STATUS - l_itemkey_sso'
2449 || l_itemkey_sso
2450 );
2451 END IF;
2452
2453 IF l_debug_level > 0
2454 THEN
2455 oe_debug_pub.ADD (' GENESIS : UPDATE FLOW STATUS');
2456 END IF;
2457
2458 oe_sync_order_pvt.insert_sync_line
2459 (p_line_rec => l_line_tbl
2460 (1),
2461 p_change_type => 'LINE_STATUS',
2462 p_req_id => l_itemkey_sso,
2463 x_return_status => l_return_status
2464 );
2465 oe_sync_order_pvt.sync_header_line
2466 (p_header_rec => l_header_rec,
2467 p_line_rec => NULL,
2468 p_hdr_req_id => l_itemkey_sso,
2469 p_lin_req_id => l_itemkey_sso,
2470 p_change_type => 'LINE_STATUS'
2471 );
2472 END IF;
2473 -- GENESIS --
2474 ELSIF p_header_id IS NOT NULL
2475 THEN
2476 UPDATE oe_order_headers_all
2477 SET flow_status_code = p_flow_status_code
2478 --Bug 8435596
2479 ,
2480 last_update_date = SYSDATE,
2481 last_updated_by = fnd_global.user_id,
2482 last_update_login = fnd_global.login_id,
2483 lock_control = lock_control + 1
2484 WHERE header_id = p_header_id;
2485
2486 l_header_rec.flow_status_code := p_flow_status_code;
2487 l_header_rec.lock_control := l_header_rec.lock_control + 1;
2488 -- aksingh performance
2489 -- As the update is on headers table, it is time to update
2490 -- cache also!
2491 oe_order_cache.set_order_header (l_header_rec);
2492 -- Bug 1755817: clear the cached constraint results for header entity
2493 -- when order header is updated.
2494 oe_pc_constraints_admin_pvt.clear_cached_results
2495 (p_validation_entity_id => oe_pc_globals.g_entity_header);
2496 -- added for notification framework
2497 -- calling notification framework to get index position
2498 oe_order_util.update_global_picture
2499 (p_upd_new_rec_if_exists => FALSE,
2500 p_old_header_rec => l_old_header_rec,
2501 p_header_rec => l_header_rec,
2502 p_header_id => p_header_id,
2503 x_index => l_index,
2504 x_return_status => l_return_status
2505 );
2506
2507 IF l_debug_level > 0
2508 THEN
2509 oe_debug_pub.ADD
2510 ( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: '
2511 || l_return_status
2512 );
2513 oe_debug_pub.ADD ('INDEX IS: ' || l_index, 1);
2514 oe_debug_pub.ADD ( 'HEADER FLOW STATUS IS: '
2515 || p_flow_status_code,
2516 1
2517 );
2518 END IF;
2519
2520 IF (l_return_status <> fnd_api.g_ret_sts_success)
2521 THEN
2522 RAISE fnd_api.g_exc_unexpected_error;
2523 END IF;
2524
2525 IF l_index IS NOT NULL
2526 THEN
2527 -- update global picture directly
2528 oe_order_util.g_header_rec := oe_order_util.g_old_header_rec;
2529 oe_order_util.g_header_rec.flow_status_code :=
2530 p_flow_status_code;
2531 oe_order_util.g_header_rec.last_update_date :=
2532 l_header_rec.last_update_date;
2533 oe_order_util.g_header_rec.operation :=
2534 oe_globals.g_opr_update;
2535
2536 IF l_debug_level > 0
2537 THEN
2538 oe_debug_pub.ADD
2539 ( 'GLOBAL HEADER FLOW STATUS IS: '
2540 || oe_order_util.g_header_rec.flow_status_code,
2541 1
2542 );
2543 oe_debug_pub.ADD ( 'GLOBAL HEADER OPERATION IS: '
2544 || oe_order_util.g_header_rec.operation,
2545 1
2546 );
2547 END IF;
2548 END IF;
2549
2550 -- bug 4732614
2551 IF l_debug_level > 0
2552 THEN
2553 oe_debug_pub.ADD
2554 ('OEXUOWFB.pls: Calling Process_Requests_And_Notify......',
2555 1
2556 );
2557 END IF;
2558
2559 oe_order_pvt.process_requests_and_notify
2560 (p_header_rec => l_header_rec,
2561 p_old_header_rec => l_old_header_rec,
2562 x_return_status => l_return_status
2563 );
2564
2565 IF l_return_status = fnd_api.g_ret_sts_error
2566 THEN
2567 IF l_debug_level > 0
2568 THEN
2569 oe_debug_pub.ADD
2570 ( 'Process_Requests_And_Notify,return_status='
2571 || l_return_status
2572 || ',Raising FND_API.G_EXC_ERROR exception',
2573 2
2574 );
2575 END IF;
2576
2577 RAISE fnd_api.g_exc_error;
2578 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2579 THEN
2580 IF l_debug_level > 0
2581 THEN
2582 oe_debug_pub.ADD
2583 ( 'Process_Requests_And_Notify,return_status='
2584 || l_return_status
2585 || ',Raising FND_API.G_EXC_UNEXPECTED_ERROR exception',
2586 2
2587 );
2588 END IF;
2589
2590 RAISE fnd_api.g_exc_unexpected_error;
2591 END IF; -- bug 4732614 ends
2592 ELSE
2593 IF l_debug_level > 0
2594 THEN
2595 oe_debug_pub.ADD
2596 ('UPDATE_FLOW_STATUS_CODE: HEADER_ID AND LINE_ID ARE NULL');
2597 END IF;
2598
2599 x_return_status := fnd_api.g_ret_sts_unexp_error;
2600 END IF;
2601 END IF; -- End of OENH/OEBH vs OEOH/OEOL processing
2602
2603 --Bug 3356542
2604 oe_pc_constraints_admin_pvt.clear_cached_results;
2605
2606 IF l_debug_level > 0
2607 THEN
2608 oe_debug_pub.ADD ('ENTERING UPDATE_FLOW_STATUS_CODE', 5);
2609 oe_debug_pub.ADD ( 'UFSC: GLOBAL RECURSION WITHOUT EXCEPTION: '
2610 || oe_order_util.g_recursion_without_exception
2611 );
2612 oe_debug_pub.ADD ( 'UFSC: GLOBAL CACHE BOOKED FLAG'
2613 || oe_order_cache.g_header_rec.booked_flag
2614 );
2615 oe_debug_pub.ADD ( 'UFSC: GLOBAL PICTURE HEADER BOOKED FLAG'
2616 || oe_order_util.g_header_rec.booked_flag
2617 );
2618 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW LINE TABLE= '
2619 || oe_order_util.g_line_tbl.COUNT
2620 );
2621 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD LINE TABLE= '
2622 || oe_order_util.g_old_line_tbl.COUNT
2623 );
2624 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW LINE ADJ TABLE= '
2625 || oe_order_util.g_line_adj_tbl.COUNT
2626 );
2627 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD LINE ADJ TABLE= '
2628 || oe_order_util.g_old_line_adj_tbl.COUNT
2629 );
2630 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW HDR ADJ TABLE= '
2631 || oe_order_util.g_header_adj_tbl.COUNT
2632 );
2633 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD HDR ADJ TABLE= '
2634 || oe_order_util.g_old_header_adj_tbl.COUNT
2635 );
2636 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW HDR SCREDIT TABLE= '
2637 || oe_order_util.g_header_scredit_tbl.COUNT
2638 );
2639 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD HDR SCREDIT TABLE= '
2640 || oe_order_util.g_old_header_scredit_tbl.COUNT
2641 );
2642 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW LINE SCREDIT TABLE= '
2643 || oe_order_util.g_line_scredit_tbl.COUNT
2644 );
2645 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD LINE SCREDIT TABLE= '
2646 || oe_order_util.g_old_line_scredit_tbl.COUNT
2647 );
2648 oe_debug_pub.ADD ( 'UFSC: COUNT OF NEW LOT SERIAL TABLE= '
2649 || oe_order_util.g_lot_serial_tbl.COUNT
2650 );
2651 oe_debug_pub.ADD ( 'UFSC: COUNT OF OLD LOT SERIAL TABLE= '
2652 || oe_order_util.g_old_lot_serial_tbl.COUNT
2653 );
2654 oe_debug_pub.ADD ('EXITING UPDATE_FLOW_STATUS_CODE', 5);
2655 END IF;
2656 EXCEPTION
2657 WHEN NO_DATA_FOUND
2658 THEN
2659 ROLLBACK TO update_flow_status_code;
2660
2661 IF l_debug_level > 0
2662 THEN
2663 oe_debug_pub.ADD ('UPDATE_FLOW_STATUS_CODE: NO_DATA_FOUND');
2664 END IF;
2665
2666 IF p_line_id IS NOT NULL
2667 THEN
2668 oe_msg_pub.reset_msg_context ('LINE');
2669 ELSIF p_header_id IS NOT NULL
2670 THEN
2671 oe_msg_pub.reset_msg_context ('HEADER');
2672 END IF;
2673 WHEN fnd_api.g_exc_error
2674 THEN
2675 ROLLBACK TO update_flow_status_code;
2676
2677 IF l_debug_level > 0
2678 THEN
2679 oe_debug_pub.ADD ('UPDATE_FLOW_STATUS_CODE: ERROR', 5);
2680 END IF;
2681
2682 x_return_status := fnd_api.g_ret_sts_error;
2683
2684 IF p_line_id IS NOT NULL
2685 THEN
2686 oe_msg_pub.reset_msg_context ('LINE');
2687 ELSIF p_header_id IS NOT NULL
2688 THEN
2689 oe_msg_pub.reset_msg_context ('HEADER');
2690 END IF;
2691 WHEN app_exceptions.record_lock_exception
2692 THEN
2693 ROLLBACK TO update_flow_status_code;
2694
2695 IF l_debug_level > 0
2696 THEN
2697 oe_debug_pub.ADD ('UPDATE_FLOW_STATUS_CODE: LOCK EXC', 5);
2698 END IF;
2699
2700 x_return_status := fnd_api.g_ret_sts_error;
2701
2702 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_error)
2703 THEN
2704 fnd_message.set_name ('ONT', 'OE_LOCK_ROW_ALREADY_LOCKED');
2705 oe_msg_pub.ADD;
2706 END IF;
2707
2708 IF p_line_id IS NOT NULL
2709 THEN
2710 oe_msg_pub.reset_msg_context ('LINE');
2711 ELSIF p_header_id IS NOT NULL
2712 THEN
2713 oe_msg_pub.reset_msg_context ('HEADER');
2714 END IF;
2715 WHEN OTHERS
2716 THEN
2717 ROLLBACK TO update_flow_status_code;
2718
2719 IF l_debug_level > 0
2720 THEN
2721 oe_debug_pub.ADD ('UPDATE_FLOW_STATUS_CODE: UNEXP ERROR', 5);
2722 END IF;
2723
2724 x_return_status := fnd_api.g_ret_sts_unexp_error;
2725
2726 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2727 THEN
2728 oe_msg_pub.add_exc_msg (g_pkg_name, 'Update_Flow_Status_Code');
2729 END IF;
2730
2731 IF p_line_id IS NOT NULL
2732 THEN
2733 oe_msg_pub.reset_msg_context ('LINE');
2734 ELSIF p_header_id IS NOT NULL
2735 THEN
2736 oe_msg_pub.reset_msg_context ('HEADER');
2737 END IF;
2738 END update_flow_status_code;
2739
2740 PROCEDURE set_notification_approver (
2741 itemtype IN VARCHAR2,
2742 itemkey IN VARCHAR2,
2743 actid IN NUMBER,
2744 funcmode IN VARCHAR2,
2745 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2746 )
2747 IS
2748 v_id VARCHAR2 (240);
2749 v_value VARCHAR2 (240);
2750 l_type VARCHAR2 (30);
2751 v_header_id NUMBER;
2752 --
2753 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2754 --
2755 BEGIN
2756 --
2757 -- RUN mode - normal process execution
2758 --
2759 IF (funcmode = 'RUN')
2760 THEN
2761 l_type :=
2762 wf_engine.getactivityattrtext (itemtype, itemkey, actid,
2763 'SOURCE');
2764
2765 IF l_type = 'PROFILE_APPROVER'
2766 THEN
2767 -- changed call from fnd_profile.value to oe_profile.value to retrieve profile in created_by context
2768 IF itemtype = 'OEOH'
2769 THEN
2770 v_value :=
2771 oe_profile.VALUE
2772 (p_header_id => TO_NUMBER (itemkey),
2773 p_line_id => NULL,
2774 p_profile_option_name => 'OE_NOTIFICATION_APPROVER'
2775 );
2776 ELSIF itemtype = 'OEOL'
2777 THEN
2778 v_value :=
2779 oe_profile.VALUE
2780 (p_header_id => NULL,
2781 p_line_id => TO_NUMBER (itemkey),
2782 p_profile_option_name => 'OE_NOTIFICATION_APPROVER'
2783 );
2784 END IF;
2785 ELSIF l_type = 'ORDER_CREATED_BY'
2786 THEN
2787 IF itemtype = 'OEOH'
2788 THEN
2789 SELECT created_by
2790 INTO v_id
2791 FROM oe_order_headers_all
2792 WHERE header_id = TO_NUMBER (itemkey);
2793 ELSIF itemtype = 'OEOL'
2794 THEN
2795 SELECT header_id
2796 INTO v_header_id
2797 FROM oe_order_lines_all
2798 WHERE line_id = TO_NUMBER (itemkey);
2799
2800 SELECT created_by
2801 INTO v_id
2802 FROM oe_order_headers_all
2803 WHERE header_id = v_header_id;
2804 END IF;
2805
2806 SELECT user_name
2807 INTO v_value
2808 FROM fnd_user
2809 WHERE user_id = v_id
2810 AND (employee_id IS NULL OR employee_id IN (SELECT person_id
2811 FROM per_people_f)
2812 );
2813 /*
2814 sales rep is not available in
2815 WF view yet
2816
2817 ELSIF l_type = 'ORDER_SALESPERSON' THEN
2818 SELECT SALESREP_ID
2819 INTO v_id
2820 FROM OE_ORDER_HEADERS_ALL
2821 WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
2822 */
2823 ELSIF l_type = 'CREATED_BY'
2824 THEN
2825 IF itemtype = 'OEOH'
2826 THEN
2827 SELECT created_by
2828 INTO v_id
2829 FROM oe_order_headers_all
2830 WHERE header_id = TO_NUMBER (itemkey);
2831 ELSIF itemtype = 'OEOL'
2832 THEN
2833 SELECT created_by
2834 INTO v_id
2835 FROM oe_order_lines_all
2836 WHERE line_id = TO_NUMBER (itemkey);
2837 END IF;
2838
2839 SELECT user_name
2840 INTO v_value
2841 FROM fnd_user
2842 WHERE user_id = v_id
2843 AND (employee_id IS NULL OR employee_id IN (SELECT person_id
2844 FROM per_people_f)
2845 );
2846 /*
2847 sales rep is not available in
2848 WF view yet
2849 ELSIF l_type = 'SALESPERSON' THEN
2850 IF itemtype='OEOH' THEN
2851 SELECT SALESREP_ID
2852 INTO v_id
2853 FROM OE_ORDER_HEADERS_ALL
2854 WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
2855 ELSIF itemtype='OEOL' THEN
2856 SELECT SALESREP_ID
2857 INTO v_id
2858 FROM OE_ORDER_LINES_ALL
2859 WHERE LINE_ID = TO_NUMBER(ITEMKEY);
2860 END IF;
2861 */
2862 END IF;
2863
2864 wf_engine.setitemattrtext (itemtype,
2865 itemkey,
2866
2867 -- Bug 9386150: provide a default value for 'v_value'
2868 'NOTIFICATION_APPROVER',
2869 NVL (v_value, 'SYSADMIN')
2870 );
2871 resultout := 'COMPLETE:COMPLETE';
2872 END IF;
2873 EXCEPTION
2874 WHEN OTHERS
2875 THEN
2876 wf_core.CONTEXT ('',
2877 'Set_Notification_Approver',
2878 itemtype,
2879 itemkey,
2880 TO_CHAR (actid),
2881 funcmode
2882 );
2883 RAISE;
2884 END set_notification_approver;
2885
2886 PROCEDURE update_quote_blanket (
2887 p_item_type IN VARCHAR2,
2888 p_item_key IN VARCHAR2,
2889 p_flow_status_code IN VARCHAR2 DEFAULT NULL,
2890 p_open_flag IN VARCHAR2 DEFAULT NULL,
2891 p_draft_submitted_flag IN VARCHAR2 DEFAULT NULL,
2892 x_return_status OUT NOCOPY VARCHAR2
2893 )
2894 IS
2895 l_header_id NUMBER;
2896 l_flow_status_code VARCHAR2 (30);
2897 l_header_rec oe_order_pub.header_rec_type;
2898 l_old_header_rec oe_order_pub.header_rec_type;
2899 l_return_status VARCHAR2 (30);
2900 l_index NUMBER;
2901 l_blanket_lock_control NUMBER;
2902 l_msg_count NUMBER;
2903 l_msg_data VARCHAR2 (2000);
2904 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2905 l_updated_flag VARCHAR2 (1) := 'N';
2906 l_sales_document_type_code VARCHAR2 (30);
2907 l_line_id NUMBER;
2908 l_lock_control NUMBER;
2909
2910 CURSOR lines
2911 IS
2912 SELECT line_id, lock_control
2913 FROM oe_order_lines_all
2914 WHERE header_id = l_header_id
2915 FOR UPDATE NOWAIT;
2916
2917 CURSOR blanket_lines
2918 IS
2919 SELECT line_id, lock_control
2920 FROM oe_blanket_lines_all
2921 WHERE header_id = l_header_id
2922 FOR UPDATE NOWAIT;
2923 BEGIN
2924 IF l_debug_level > 0
2925 THEN
2926 oe_debug_pub.ADD ('ENTERING UPDATE_QUOTE_BLANKET', 5);
2927 END IF;
2928
2929 SAVEPOINT update_quote_blanket;
2930 x_return_status := fnd_api.g_ret_sts_success;
2931
2932 -- Check if the ASO is installed to call the NOTIFY_OC.
2933 IF oe_globals.g_aso_installed IS NULL
2934 THEN
2935 oe_globals.g_aso_installed :=
2936 oe_globals.check_product_installed (697);
2937 END IF;
2938
2939 l_header_id := TO_NUMBER (p_item_key);
2940
2941 IF p_flow_status_code IS NOT NULL
2942 THEN
2943 -- if flow_status_code is passed in, validate it
2944 SELECT lookup_code
2945 INTO l_flow_status_code
2946 FROM oe_lookups
2947 WHERE lookup_type = 'FLOW_STATUS'
2948 AND lookup_code = p_flow_status_code
2949 AND enabled_flag = 'Y'
2950 AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)
2951 AND NVL (end_date_active, SYSDATE);
2952 END IF;
2953
2954 IF p_item_type = oe_globals.g_wfi_bkt
2955 THEN
2956 l_sales_document_type_code := 'B';
2957 ELSE -- itemtype = OENH
2958 l_sales_document_type_code :=
2959 wf_engine.getitemattrtext (p_item_type,
2960 p_item_key,
2961 'SALES_DOCUMENT_TYPE_CODE'
2962 );
2963 END IF;
2964
2965 IF l_sales_document_type_code = 'O'
2966 THEN
2967 oe_header_util.lock_row (p_header_id => l_header_id,
2968 p_x_header_rec => l_header_rec,
2969 x_return_status => l_return_status
2970 );
2971
2972 IF l_return_status = fnd_api.g_ret_sts_error
2973 THEN
2974 RAISE fnd_api.g_exc_error;
2975 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2976 THEN
2977 RAISE fnd_api.g_exc_unexpected_error;
2978 END IF;
2979
2980 -- is entity HEADER correct?
2981 oe_msg_pub.set_msg_context
2982 (p_entity_code => 'HEADER',
2983 p_entity_id => l_header_rec.header_id,
2984 p_header_id => l_header_rec.header_id,
2985 p_line_id => NULL,
2986 p_order_source_id => l_header_rec.order_source_id,
2987 p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref,
2988 p_orig_sys_document_line_ref => NULL,
2989 p_change_sequence => l_header_rec.change_sequence,
2990 p_source_document_type_id => l_header_rec.source_document_type_id,
2991 p_source_document_id => l_header_rec.source_document_id,
2992 p_source_document_line_id => NULL
2993 );
2994 l_old_header_rec := l_header_rec;
2995
2996 IF p_flow_status_code IS NOT NULL
2997 THEN
2998 UPDATE oe_order_headers_all
2999 SET flow_status_code = p_flow_status_code
3000 --Bug 8435596
3001 ,
3002 last_update_date = SYSDATE,
3003 last_updated_by = fnd_global.user_id,
3004 last_update_login = fnd_global.login_id
3005 WHERE header_id = l_header_id;
3006
3007 -- Also update all lines to have the same flow_status_code
3008 OPEN lines;
3009
3010 LOOP
3011 FETCH lines
3012 INTO l_line_id, l_lock_control;
3013
3014 EXIT WHEN lines%NOTFOUND;
3015 END LOOP;
3016
3017 CLOSE lines;
3018
3019 UPDATE oe_order_lines_all
3020 SET flow_status_code = p_flow_status_code
3021 --Bug 8435596
3022 ,
3023 last_update_date = SYSDATE,
3024 last_updated_by = fnd_global.user_id,
3025 last_update_login = fnd_global.login_id,
3026 lock_control = lock_control + 1
3027 WHERE header_id = l_header_id;
3028
3029 l_updated_flag := 'Y';
3030 l_header_rec.flow_status_code := p_flow_status_code;
3031 END IF;
3032
3033 IF p_open_flag IS NOT NULL
3034 THEN
3035 UPDATE oe_order_headers_all
3036 SET open_flag = p_open_flag,
3037 -- Bug 12849250
3038 last_update_date = SYSDATE,
3039 last_updated_by = fnd_global.user_id,
3040 last_update_login = fnd_global.login_id
3041 WHERE header_id = l_header_id;
3042
3043 l_updated_flag := 'Y';
3044 l_header_rec.open_flag := p_open_flag;
3045
3046 -- XDING bug FP5172433
3047 UPDATE oe_order_lines_all
3048 SET open_flag = p_open_flag,
3049 -- Bug 12849250
3050 last_update_date = SYSDATE,
3051 last_updated_by = fnd_global.user_id,
3052 last_update_login = fnd_global.login_id
3053 WHERE header_id = l_header_id;
3054 -- XDING bug FP5172433
3055 END IF;
3056
3057 IF p_draft_submitted_flag IS NOT NULL
3058 THEN
3059 UPDATE oe_order_headers_all
3060 SET draft_submitted_flag = p_draft_submitted_flag,
3061 -- Bug 12849250
3062 last_update_date = SYSDATE,
3063 last_updated_by = fnd_global.user_id,
3064 last_update_login = fnd_global.login_id
3065 WHERE header_id = l_header_id;
3066
3067 l_updated_flag := 'Y';
3068 l_header_rec.draft_submitted_flag := p_draft_submitted_flag;
3069 END IF;
3070
3071 IF l_updated_flag = 'Y'
3072 THEN
3073 UPDATE oe_order_headers_all
3074 SET lock_control = lock_control + 1
3075 WHERE header_id = l_header_id;
3076
3077 l_header_rec.lock_control := l_header_rec.lock_control + 1;
3078 END IF;
3079
3080 -- aksingh performance
3081 -- As the update is on headers table, it is time to update
3082 -- cache also!
3083 oe_order_cache.set_order_header (l_header_rec);
3084 -- Bug 1755817: clear the cached constraint results for header entity
3085 -- when order header is updated.
3086 oe_pc_constraints_admin_pvt.clear_cached_results
3087 (p_validation_entity_id => oe_pc_globals.g_entity_header);
3088 -- added for notification framework
3089 -- calling notification framework to get index position
3090 oe_order_util.update_global_picture
3091 (p_upd_new_rec_if_exists => FALSE,
3092 p_old_header_rec => l_old_header_rec,
3093 p_header_rec => l_header_rec,
3094 p_header_id => l_header_id,
3095 x_index => l_index,
3096 x_return_status => l_return_status
3097 );
3098
3099 IF l_debug_level > 0
3100 THEN
3101 oe_debug_pub.ADD
3102 ( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: '
3103 || l_return_status
3104 );
3105 oe_debug_pub.ADD ('INDEX IS: ' || l_index, 1);
3106 oe_debug_pub.ADD ('HEADER FLOW STATUS IS: ' || p_flow_status_code,
3107 1
3108 );
3109 END IF;
3110
3111 IF (l_return_status <> fnd_api.g_ret_sts_success)
3112 THEN
3113 RAISE fnd_api.g_exc_unexpected_error;
3114 END IF;
3115
3116 IF l_index IS NOT NULL
3117 THEN
3118 -- update global picture directly
3119 oe_order_util.g_header_rec := oe_order_util.g_old_header_rec;
3120
3121 IF p_flow_status_code IS NOT NULL
3122 THEN
3123 oe_order_util.g_header_rec.flow_status_code :=
3124 p_flow_status_code;
3125 END IF;
3126
3127 IF p_open_flag IS NOT NULL
3128 THEN
3129 oe_order_util.g_header_rec.open_flag := p_open_flag;
3130 END IF;
3131
3132 IF p_draft_submitted_flag IS NOT NULL
3133 THEN
3134 oe_order_util.g_header_rec.draft_submitted_flag :=
3135 p_draft_submitted_flag;
3136 END IF;
3137
3138 oe_order_util.g_header_rec.last_update_date :=
3139 l_header_rec.last_update_date;
3140 oe_order_util.g_header_rec.operation := oe_globals.g_opr_update;
3141
3142 IF l_debug_level > 0
3143 THEN
3144 oe_debug_pub.ADD ( 'GLOBAL HEADER FLOW STATUS IS: '
3145 || oe_order_util.g_header_rec.flow_status_code,
3146 1
3147 );
3148 oe_debug_pub.ADD ( 'GLOBAL HEADER OPEN_FLAG IS: '
3149 || oe_order_util.g_header_rec.open_flag,
3150 1
3151 );
3152 oe_debug_pub.ADD
3153 ( 'GLOBAL HEADER DRAFT_SUBMITTED_FLAG IS: '
3154 || oe_order_util.g_header_rec.draft_submitted_flag,
3155 1
3156 );
3157 oe_debug_pub.ADD ( 'GLOBAL HEADER OPERATION IS: '
3158 || oe_order_util.g_header_rec.operation,
3159 1
3160 );
3161 END IF;
3162 END IF;
3163
3164 -- bug 4732614
3165 IF l_debug_level > 0
3166 THEN
3167 oe_debug_pub.ADD
3168 ('OEXUOWFB.pls: Calling Process_Requests_And_Notify......',
3169 1
3170 );
3171 END IF;
3172
3173 oe_order_pvt.process_requests_and_notify
3174 (p_header_rec => l_header_rec,
3175 p_old_header_rec => l_old_header_rec,
3176 x_return_status => l_return_status
3177 );
3178
3179 IF l_return_status = fnd_api.g_ret_sts_error
3180 THEN
3181 IF l_debug_level > 0
3182 THEN
3183 oe_debug_pub.ADD
3184 ( 'Process_Requests_And_Notify,return_status='
3185 || l_return_status
3186 || ',Raising FND_API.G_EXC_ERROR exception',
3187 2
3188 );
3189 END IF;
3190
3191 RAISE fnd_api.g_exc_error;
3192 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
3193 THEN
3194 IF l_debug_level > 0
3195 THEN
3196 oe_debug_pub.ADD
3197 ( 'Process_Requests_And_Notify,return_status='
3198 || l_return_status
3199 || ',Raising FND_API.G_EXC_UNEXPECTED_ERROR exception',
3200 2
3201 );
3202 END IF;
3203
3204 RAISE fnd_api.g_exc_unexpected_error;
3205 END IF; -- bug 4732614 ends
3206 ELSIF l_sales_document_type_code = 'B'
3207 THEN -- Blanket Negotitation/Fulfillment
3208 SELECT lock_control
3209 INTO l_blanket_lock_control
3210 FROM oe_blanket_headers_all
3211 WHERE header_id = l_header_id
3212 FOR UPDATE NOWAIT;
3213
3214 /* avoid dependency on blanket API
3215 OE_Blanket_Util.Lock_Row(p_blanket_id=>l_header_id
3216 , p_blanket_line_id => null
3217 , p_x_lock_control=>l_blanket_lock_control
3218 , x_return_status => l_return_status
3219 , x_msg_count => l_msg_count
3220 , x_msg_data => l_msg_data);
3221 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3222 RAISE FND_API.G_EXC_ERROR;
3223 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3225 END IF;
3226
3227 OE_MSG_PUB.set_msg_context
3228 ( p_entity_code => 'BLANKET'
3229 ,p_entity_id => p_header_id
3230 ,p_header_id => p_header_id);
3231
3232
3233 */
3234 IF p_flow_status_code IS NOT NULL
3235 THEN
3236 UPDATE oe_blanket_headers_all
3237 SET flow_status_code = p_flow_status_code
3238 --Bug 8435596
3239 ,
3240 last_update_date = SYSDATE,
3241 last_updated_by = fnd_global.user_id,
3242 last_update_login = fnd_global.login_id
3243 WHERE header_id = l_header_id;
3244
3245 l_updated_flag := 'Y';
3246 END IF;
3247
3248 IF p_open_flag IS NOT NULL
3249 THEN
3250 UPDATE oe_blanket_headers_all
3251 SET open_flag = p_open_flag,
3252 -- Bug 12849250
3253 last_update_date = SYSDATE,
3254 last_updated_by = fnd_global.user_id,
3255 last_update_login = fnd_global.login_id
3256 WHERE header_id = l_header_id;
3257
3258 -- ZB put the code here
3259 oe_debug_pub.ADD ('Acquiring locks on blanket lines');
3260
3261 OPEN blanket_lines;
3262
3263 LOOP
3264 FETCH blanket_lines
3265 INTO l_line_id, l_lock_control;
3266
3267 EXIT WHEN blanket_lines%NOTFOUND;
3268 END LOOP;
3269
3270 CLOSE blanket_lines;
3271
3272 oe_debug_pub.ADD ('Updating blanket lines 4 open flag');
3273
3274 UPDATE oe_blanket_lines_all
3275 SET open_flag = p_open_flag,
3276 -- Bug 12849250
3277 last_update_date = SYSDATE,
3278 last_updated_by = fnd_global.user_id,
3279 last_update_login = fnd_global.login_id
3280 WHERE header_id = l_header_id;
3281
3282 -- End code
3283 l_updated_flag := 'Y';
3284 END IF;
3285
3286 IF p_draft_submitted_flag IS NOT NULL
3287 THEN
3288 UPDATE oe_blanket_headers_all
3289 SET draft_submitted_flag = p_draft_submitted_flag,
3290 -- Bug 12849250
3291 last_update_date = SYSDATE,
3292 last_updated_by = fnd_global.user_id,
3293 last_update_login = fnd_global.login_id
3294 WHERE header_id = l_header_id;
3295
3296 l_updated_flag := 'Y';
3297 END IF;
3298
3299 IF l_updated_flag = 'Y'
3300 THEN
3301 UPDATE oe_blanket_headers_all
3302 SET lock_control = lock_control + 1
3303 WHERE header_id = l_header_id;
3304 END IF;
3305 END IF; --check sales_document_type_code
3306
3307 -- Bug 3356542
3308 oe_pc_constraints_admin_pvt.clear_cached_results;
3309
3310 IF l_debug_level > 0
3311 THEN
3312 oe_debug_pub.ADD ('EXITING UPDATE_QUOTE_BLANKET', 5);
3313 END IF;
3314 EXCEPTION
3315 WHEN NO_DATA_FOUND
3316 THEN
3317 ROLLBACK TO update_quote_blanket;
3318
3319 IF l_debug_level > 0
3320 THEN
3321 oe_debug_pub.ADD ('UPDATE_QUOTE_BLANKET: NO_DATA_FOUND');
3322 END IF;
3323
3324 x_return_status := fnd_api.g_ret_sts_error;
3325
3326 IF l_header_id IS NOT NULL
3327 THEN
3328 oe_msg_pub.reset_msg_context ('HEADER');
3329 END IF;
3330 WHEN fnd_api.g_exc_error
3331 THEN
3332 ROLLBACK TO update_quote_blanket;
3333
3334 IF l_debug_level > 0
3335 THEN
3336 oe_debug_pub.ADD ('UPDATE_QUOTE_BLANKET: ERROR', 5);
3337 END IF;
3338
3339 x_return_status := fnd_api.g_ret_sts_error;
3340
3341 IF l_header_id IS NOT NULL
3342 THEN
3343 oe_msg_pub.reset_msg_context ('HEADER');
3344 END IF;
3345 WHEN app_exceptions.record_lock_exception
3346 THEN
3347 ROLLBACK TO update_quote_blanket;
3348
3349 IF l_debug_level > 0
3350 THEN
3351 oe_debug_pub.ADD ('UPDATE_QUOTE_BLANKET: LOCK EXC', 5);
3352 END IF;
3353
3354 x_return_status := fnd_api.g_ret_sts_error;
3355
3356 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_error)
3357 THEN
3358 fnd_message.set_name ('ONT', 'OE_LOCK_ROW_ALREADY_LOCKED');
3359 oe_msg_pub.ADD;
3360 END IF;
3361
3362 IF l_header_id IS NOT NULL
3363 THEN
3364 oe_msg_pub.reset_msg_context ('HEADER');
3365 END IF;
3366 WHEN OTHERS
3367 THEN
3368 ROLLBACK TO update_quote_blanket;
3369
3370 IF l_debug_level > 0
3371 THEN
3372 oe_debug_pub.ADD ('UPDATE_QUOTE_BLANKET: UNEXP ERROR', 5);
3373 END IF;
3374
3375 x_return_status := fnd_api.g_ret_sts_unexp_error;
3376
3377 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3378 THEN
3379 oe_msg_pub.add_exc_msg (g_pkg_name, 'Update_Quote_Blanket');
3380 END IF;
3381
3382 IF l_header_id IS NOT NULL
3383 THEN
3384 oe_msg_pub.reset_msg_context ('HEADER');
3385 END IF;
3386 END update_quote_blanket;
3387
3388 /* -------------------------------------------------
3389 PROCEDURE: Create_WorkItem_Upgrade
3390 USAGE: This is used for blanket upgrade only
3391 at this time. It will create the WF process,
3392 but will not start the flow. Caller can use
3393 handleerror API call to jump to the right
3394 activity
3395 ----------------------------------------------------- */
3396 PROCEDURE create_workitem_upgrade (
3397 p_item_type IN VARCHAR2,
3398 p_item_key IN VARCHAR2,
3399 p_process_name IN VARCHAR2,
3400 p_transaction_number IN NUMBER,
3401 p_sales_document_type_code IN VARCHAR2,
3402 p_user_id IN NUMBER,
3403 p_resp_id IN NUMBER,
3404 p_appl_id IN NUMBER,
3405 p_org_id IN NUMBER
3406 )
3407 IS
3408 user_key_string VARCHAR2 (240);
3409 l_valid_process VARCHAR2 (30);
3410 l_aname wf_engine.nametabtyp;
3411 l_aname2 wf_engine.nametabtyp;
3412 l_avalue wf_engine.numtabtyp;
3413 l_avaluetext wf_engine.texttabtyp;
3414 l_user_name VARCHAR2 (100);
3415 l_validate_user NUMBER;
3416 l_owner_role VARCHAR2 (100);
3417 --
3418 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3419 --
3420 BEGIN
3421 IF l_debug_level > 0
3422 THEN
3423 oe_debug_pub.ADD
3424 ( 'Entering Create_WorkItem_Upgrade. item_type/item_key='
3425 || p_item_type
3426 || '/'
3427 || p_item_key,
3428 1
3429 );
3430 END IF;
3431
3432 -- validate the p_process_name is ok
3433 SELECT NAME
3434 INTO l_valid_process
3435 FROM wf_activities
3436 WHERE item_type = p_item_type
3437 AND NAME = p_process_name
3438 AND runnable_flag = 'Y'
3439 AND end_date IS NULL;
3440
3441 IF l_debug_level > 0
3442 THEN
3443 oe_debug_pub.ADD ('process_name: ' || l_valid_process, 4);
3444 END IF;
3445
3446 IF p_sales_document_type_code = 'O'
3447 THEN
3448 fnd_message.set_name ('ONT', 'OE_NTF_QUOTE');
3449 ELSIF p_sales_document_type_code = 'B'
3450 THEN
3451 fnd_message.set_name ('ONT', 'OE_NTF_BSA');
3452 END IF;
3453
3454 user_key_string :=
3455 SUBSTRB (fnd_message.get, 1, 240)
3456 || ' '
3457 || TO_CHAR (p_transaction_number);
3458
3459 SELECT user_name
3460 INTO l_owner_role
3461 FROM fnd_user
3462 WHERE user_id = p_user_id;
3463
3464 -- Create process
3465 wf_engine.createprocess (p_item_type,
3466 p_item_key,
3467 p_process_name,
3468 user_key_string,
3469 l_owner_role
3470 );
3471
3472 IF l_debug_level > 0
3473 THEN
3474 oe_debug_pub.ADD ('After WF_ENGINE.CreateProcess', 4);
3475 END IF;
3476
3477 -- Set various Header Attributes
3478 l_aname (1) := 'USER_ID';
3479 l_avalue (1) := p_user_id;
3480 l_aname (2) := 'APPLICATION_ID';
3481 l_avalue (2) := p_appl_id;
3482 l_aname (3) := 'RESPONSIBILITY_ID';
3483 l_avalue (3) := p_resp_id;
3484 l_aname (4) := 'ORG_ID';
3485 l_avalue (4) := p_org_id;
3486 l_aname (5) := 'TRANSACTION_NUMBER';
3487 l_avalue (5) := p_transaction_number;
3488 wf_engine.setitemattrnumberarray (p_item_type,
3489 p_item_key,
3490 l_aname,
3491 l_avalue
3492 );
3493
3494 IF l_debug_level > 0
3495 THEN
3496 oe_debug_pub.ADD ('EXITING CREATE_WORKITEM_UPGRADE', 4);
3497 END IF;
3498 EXCEPTION
3499 WHEN OTHERS
3500 THEN
3501 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
3502 THEN
3503 oe_msg_pub.add_exc_msg (g_pkg_name, 'Create_WorkItem_Upgrade');
3504 RAISE fnd_api.g_exc_unexpected_error;
3505 END IF;
3506 END create_workitem_upgrade;
3507
3508 PROCEDURE createstart_hdrinternal (
3509 p_item_type IN VARCHAR2,
3510 p_header_id IN NUMBER,
3511 p_transaction_number IN NUMBER,
3512 p_sales_document_type_code IN VARCHAR2
3513 )
3514 IS
3515 --
3516 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3517 l_count NUMBER;
3518 --
3519 BEGIN
3520 IF l_debug_level > 0
3521 THEN
3522 oe_debug_pub.ADD ('IN CREATESTART_HDRINTERNAL');
3523 END IF;
3524
3525 create_hdrworkiteminternal (p_item_type,
3526 p_header_id,
3527 p_transaction_number,
3528 p_sales_document_type_code
3529 );
3530
3531 IF p_item_type = oe_globals.g_wfi_ngo
3532 THEN
3533 oe_globals.g_start_negotiate_header_flow := p_header_id;
3534 ELSIF p_item_type = oe_globals.g_wfi_bkt
3535 THEN
3536 oe_globals.g_start_blanket_header_flow := p_header_id;
3537
3538 -- For OEBH
3539 -- Check if a OENH flow exists, if so set the parent
3540 SELECT COUNT (1)
3541 INTO l_count
3542 FROM wf_items
3543 WHERE item_type = oe_globals.g_wfi_ngo
3544 AND item_key = TO_CHAR (p_header_id);
3545
3546 IF l_count > 0
3547 THEN
3548 wf_item.set_item_parent (oe_globals.g_wfi_bkt,
3549 TO_CHAR (p_header_id),
3550 oe_globals.g_wfi_ngo,
3551 TO_CHAR (p_header_id),
3552 ''
3553 );
3554 END IF;
3555 END IF;
3556
3557 oe_globals.g_sales_document_type_code := p_sales_document_type_code;
3558
3559 IF l_debug_level > 0
3560 THEN
3561 oe_debug_pub.ADD ('EXITING CREATESTART_HDRPROCESSINTERNAL');
3562 END IF;
3563 EXCEPTION
3564 WHEN fnd_api.g_exc_error
3565 THEN
3566 RAISE;
3567 WHEN OTHERS
3568 THEN
3569 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
3570 THEN
3571 oe_msg_pub.add_exc_msg (g_pkg_name, 'CreateStart_HdrInternal');
3572 RAISE fnd_api.g_exc_unexpected_error;
3573 END IF;
3574 END createstart_hdrinternal;
3575
3576 /* ----------------------------------------------------------
3577 PROCEDURE: Create_HdrWorkItemInternal
3578 USAGE: The main create WF work item routine for item type
3579 OENH and OEBH
3580 ------------------------------------------------------------- */
3581 PROCEDURE create_hdrworkiteminternal (
3582 p_item_type IN VARCHAR2,
3583 p_header_id IN NUMBER,
3584 p_transaction_number IN NUMBER,
3585 p_sales_document_type_code IN VARCHAR2
3586 )
3587 IS
3588 l_hdr_process_name VARCHAR2 (30);
3589 l_aname wf_engine.nametabtyp;
3590 l_aname2 wf_engine.nametabtyp;
3591 l_avalue wf_engine.numtabtyp;
3592 l_avaluetext wf_engine.texttabtyp;
3593 user_key_string VARCHAR2 (240);
3594 l_user_name VARCHAR2 (100);
3595 l_validate_user NUMBER;
3596 l_sales_document_type VARCHAR2 (240);
3597 l_owner_role VARCHAR2 (100);
3598 --
3599 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3600 --
3601 BEGIN
3602 IF l_debug_level > 0
3603 THEN
3604 oe_debug_pub.ADD
3605 ( 'IN CREATE_HDRWORKITEMINTERNAL, ITEM_TYPE/ITEM_KEY='
3606 || p_item_type
3607 || '/'
3608 || TO_CHAR (p_header_id)
3609 );
3610 END IF;
3611
3612 l_hdr_process_name :=
3613 get_processname
3614 (p_itemtype => p_item_type,
3615 p_itemkey => p_header_id,
3616 p_salesdocumenttypecode => p_sales_document_type_code
3617 );
3618
3619 IF l_debug_level > 0
3620 THEN
3621 oe_debug_pub.ADD ('Get ProcessName: ' || l_hdr_process_name);
3622 END IF;
3623
3624 -- set user key
3625 IF p_sales_document_type_code = 'O'
3626 THEN
3627 fnd_message.set_name ('ONT', 'OE_NTF_QUOTE');
3628 ELSIF p_sales_document_type_code = 'B'
3629 THEN
3630 fnd_message.set_name ('ONT', 'OE_NTF_BSA');
3631 END IF;
3632
3633 l_sales_document_type := SUBSTRB (fnd_message.get, 1, 240);
3634 user_key_string :=
3635 l_sales_document_type || ' ' || TO_CHAR (p_transaction_number);
3636
3637 SELECT user_name
3638 INTO l_owner_role
3639 FROM fnd_user
3640 WHERE user_id = fnd_global.user_id;
3641
3642 -- Create Header Work item
3643 wf_engine.createprocess (p_item_type,
3644 TO_CHAR (p_header_id),
3645 l_hdr_process_name,
3646 user_key_string,
3647 l_owner_role
3648 );
3649
3650 IF l_debug_level > 0
3651 THEN
3652 oe_debug_pub.ADD ('AFTER WF_ENGINE.CREATEPROCESS');
3653 END IF;
3654
3655 -- Set various Header Attributes
3656 l_aname (1) := 'USER_ID';
3657 l_avalue (1) := fnd_global.user_id;
3658 l_aname (2) := 'APPLICATION_ID';
3659 l_avalue (2) := fnd_global.resp_appl_id;
3660 l_aname (3) := 'RESPONSIBILITY_ID';
3661 l_avalue (3) := fnd_global.resp_id;
3662 l_aname (4) := 'ORG_ID';
3663 l_avalue (4) := TO_NUMBER (oe_globals.g_org_id);
3664 l_aname (5) := 'TRANSACTION_NUMBER';
3665 l_avalue (5) := p_transaction_number;
3666
3667 IF p_item_type = oe_globals.g_wfi_ngo
3668 THEN
3669 l_aname (6) := 'HEADER_ID';
3670 l_avalue (6) := p_header_id;
3671 END IF;
3672
3673 wf_engine.setitemattrnumberarray (p_item_type,
3674 TO_CHAR (p_header_id),
3675 l_aname,
3676 l_avalue
3677 );
3678
3679 /* get FROM_ROLE */
3680 BEGIN
3681 SELECT user_name
3682 INTO l_user_name
3683 FROM fnd_user
3684 WHERE user_id = fnd_global.user_id;
3685 EXCEPTION
3686 WHEN OTHERS
3687 THEN
3688 l_user_name := NULL; -- do not set FROM_ROLE then
3689 END;
3690
3691 wf_engine.setitemattrtext (p_item_type,
3692 TO_CHAR (p_header_id),
3693 'NOTIFICATION_FROM_ROLE',
3694 l_user_name
3695 );
3696
3697 IF p_item_type = oe_globals.g_wfi_ngo
3698 THEN
3699 -- if this is a negotiation flow, set some item attributes that
3700 -- only apply to negotiations
3701 l_aname2 (1) := 'SALES_DOCUMENT_TYPE_CODE';
3702 l_avaluetext (1) := p_sales_document_type_code;
3703 l_aname2 (2) := 'SALES_DOCUMENT_TYPE';
3704 l_avaluetext (2) := l_sales_document_type;
3705 -- CONTRACT_ATTACHMENT will not be set here, it may be too early, we need
3706 -- to call contract to determine the attachment, at a later time, it's done
3707 -- at Initiate_Approval
3708 wf_engine.setitemattrtextarray (p_item_type,
3709 TO_CHAR (p_header_id),
3710 l_aname2,
3711 l_avaluetext
3712 );
3713 END IF; -- only need to set text item attr if it is OENH
3714
3715 IF l_debug_level > 0
3716 THEN
3717 oe_debug_pub.ADD ('EXITING CREATE_HDRWORKITEMINTERNAL');
3718 END IF;
3719 EXCEPTION
3720 WHEN fnd_api.g_exc_error
3721 THEN
3722 RAISE;
3723 WHEN OTHERS
3724 THEN
3725 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
3726 THEN
3727 oe_msg_pub.add_exc_msg (g_pkg_name, 'Create_HdrWorkItemInternal');
3728 RAISE fnd_api.g_exc_unexpected_error;
3729 END IF;
3730 END create_hdrworkiteminternal;
3731
3732 PROCEDURE set_negotiate_hdr_user_key (
3733 p_header_id IN NUMBER,
3734 p_sales_document_type_code IN VARCHAR2,
3735 p_transaction_number IN NUMBER
3736 )
3737 IS
3738 l_user_key VARCHAR2 (240);
3739 BEGIN
3740 IF p_sales_document_type_code = 'O'
3741 THEN
3742 fnd_message.set_name ('ONT', 'OE_WF_QUOTE_ORDER');
3743 fnd_message.set_token ('QUOTE_NUMBER',
3744 TO_CHAR (p_transaction_number)
3745 );
3746 ELSIF p_sales_document_type_code = 'B'
3747 THEN
3748 fnd_message.set_name ('ONT', 'OE_WF_BLANKET_ORDER');
3749 fnd_message.set_token ('BLANKET_NUMBER',
3750 TO_CHAR (p_transaction_number)
3751 );
3752 END IF;
3753
3754 l_user_key := SUBSTRB (fnd_message.get, 1, 240);
3755 wf_engine.setitemuserkey (oe_globals.g_wfi_ngo, p_header_id, l_user_key);
3756 EXCEPTION
3757 WHEN OTHERS
3758 THEN
3759 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
3760 THEN
3761 oe_msg_pub.add_exc_msg (g_pkg_name, 'Set_Negotiate_Hdr_User_Key');
3762 RAISE fnd_api.g_exc_unexpected_error;
3763 END IF;
3764 END set_negotiate_hdr_user_key;
3765
3766 PROCEDURE set_blanket_hdr_user_key (
3767 p_header_id IN NUMBER,
3768 p_transaction_number IN NUMBER
3769 )
3770 IS
3771 l_user_key VARCHAR2 (240);
3772 BEGIN
3773 fnd_message.set_name ('ONT', 'OE_WF_BLANKET_ORDER');
3774 fnd_message.set_token ('BLANKET_NUMBER',
3775 TO_CHAR (p_transaction_number));
3776 l_user_key := SUBSTRB (fnd_message.get, 1, 240);
3777 wf_engine.setitemuserkey (oe_globals.g_wfi_bkt, p_header_id,
3778 l_user_key);
3779 EXCEPTION
3780 WHEN OTHERS
3781 THEN
3782 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
3783 THEN
3784 oe_msg_pub.add_exc_msg (g_pkg_name, 'Set_Blanket_Hdr_User_Key');
3785 RAISE fnd_api.g_exc_unexpected_error;
3786 END IF;
3787 END set_blanket_hdr_user_key;
3788
3789 /******************************
3790 *** Set_transaction_Details **
3791 ******************************/
3792 /*
3793 */
3794 PROCEDURE set_transaction_details (
3795 document_id IN VARCHAR2,
3796 display_type IN VARCHAR2,
3797 document IN OUT NOCOPY VARCHAR2,
3798 document_type IN OUT NOCOPY VARCHAR2
3799 )
3800 IS
3801 l_sales_document_type_code VARCHAR2 (1);
3802 l_item_key VARCHAR2 (240);
3803 l_item_type VARCHAR2 (8);
3804 l_blanket_flag VARCHAR2 (1);
3805 -- HTML variables
3806 l_document VARCHAR2 (32000) := '';
3807 l_header_id VARCHAR2 (240); -- ame er 16084377
3808 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3809 BEGIN
3810 -- select ITEM_KEY, ITEM_TYPE
3811 -- into l_item_key, l_item_type
3812 -- from wf_item_activity_statuses_v
3813 -- where NOTIFICATION_ID = to_number(document_id);
3814
3815 -- replaced with this. see bug#4930449
3816 BEGIN
3817 SELECT item_key, item_type
3818 INTO l_item_key, l_item_type
3819 FROM wf_item_activity_statuses
3820 WHERE notification_id = TO_NUMBER (document_id);
3821 EXCEPTION
3822 WHEN NO_DATA_FOUND
3823 THEN
3824 SELECT item_key, item_type
3825 INTO l_item_key, l_item_type
3826 FROM wf_item_activity_statuses_h
3827 WHERE notification_id = TO_NUMBER (document_id);
3828 END;
3829
3830 /* 9047023: End */
3831 -- ame er 16084377 code begins
3832 BEGIN
3833 IF l_debug_level > 0
3834 THEN
3835 oe_debug_pub.ADD ('before getting attribute');
3836 END IF;
3837
3838 IF l_item_type = 'OEAME'
3839 THEN
3840 l_header_id :=
3841 wf_engine.getitemattrnumber (l_item_type,
3842 l_item_key,
3843 'HEADER_ID'
3844 );
3845
3846 IF l_debug_level > 0
3847 THEN
3848 oe_debug_pub.ADD ('After getting attribute:' || l_header_id);
3849 END IF;
3850 ELSE
3851 l_header_id := l_item_key;
3852 END IF;
3853 EXCEPTION
3854 WHEN NO_DATA_FOUND
3855 THEN
3856 l_header_id := l_item_key;
3857 END;
3858
3859 -- ame er 16084377 code ends
3860
3861 -- Get the Sales Document Type
3862 IF l_item_type = 'OEBH'
3863 THEN
3864 l_blanket_flag := 'Y';
3865 ELSE
3866 l_sales_document_type_code :=
3867 wf_engine.getitemattrtext (oe_globals.g_wfi_ngo,
3868 l_header_id, -- ame er 16084377
3869 'SALES_DOCUMENT_TYPE_CODE'
3870 );
3871
3872 IF l_sales_document_type_code = 'B'
3873 THEN
3874 l_blanket_flag := 'Y';
3875 END IF;
3876 END IF;
3877
3878 IF l_blanket_flag = 'Y'
3879 THEN
3880 oe_order_wf_util.build_blanket_doc
3881 (p_item_type => l_item_type,
3882 p_item_key => l_header_id,
3883 -- ame er 16084377
3884 p_display_type => display_type,
3885 p_x_document => l_document
3886 );
3887 -----------------------------
3888 -- Sales Document is Quote --
3889 -----------------------------
3890 ELSE -- l_blanket_flag = 'N'
3891 oe_order_wf_util.build_quote_doc
3892 (p_item_type => l_item_type,
3893 p_item_key => l_header_id,
3894 -- ame er 16084377
3895 p_display_type => display_type,
3896 p_x_document => l_document
3897 );
3898 END IF; -- l_sales_document_type_code = 'B'
3899
3900 document := l_document;
3901 END set_transaction_details;
3902
3903 /*************************
3904 ** BUILD_BLANKET_DOC **
3905 *************************/
3906 PROCEDURE build_blanket_doc (
3907 p_item_type IN VARCHAR2,
3908 p_item_key IN VARCHAR2,
3909 p_display_type IN VARCHAR2,
3910 p_x_document IN OUT NOCOPY VARCHAR2
3911 )
3912 IS
3913 l_wf_header_attr VARCHAR2 (30);
3914 l_salesrep VARCHAR2 (240);
3915 l_sold_to VARCHAR2 (240);
3916 l_expiration_date DATE;
3917 l_transaction_id NUMBER;
3918 --l_header_id NUMBER;
3919 l_blanket_flag VARCHAR2 (1);
3920 l_item_type VARCHAR2 (8);
3921 l_aname wf_engine.nametabtyp;
3922 l_avaluetext wf_engine.texttabtyp;
3923 l_transaction_number NUMBER;
3924 -- Blanket Header Attributes
3925 l_order_number NUMBER;
3926 l_blanket_min_amount NUMBER;
3927 l_blanket_max_amount NUMBER;
3928 l_start_date_active DATE;
3929 l_end_date_active DATE;
3930 l_credit_hold VARCHAR2 (3);
3931 l_creation_date DATE;
3932 l_ship_to_address VARCHAR2 (40);
3933 l_invoice_to_address VARCHAR2 (40);
3934 l_payment_term VARCHAR2 (15);
3935 -- HTML variables
3936 l_document VARCHAR2 (32000) := '';
3937 l_line_msg VARCHAR2 (1000);
3938 nl VARCHAR2 (1) := '';
3939 i NUMBER := 0;
3940 -- New Line char.
3941 nlchar VARCHAR2 (2) := fnd_global.NEWLINE;
3942 -- fnd messages
3943 l_msg_bsa VARCHAR2 (240);
3944 l_msg_creation_date VARCHAR2 (240);
3945 l_msg_activation_date VARCHAR2 (240);
3946 l_msg_expiration_date VARCHAR2 (240);
3947 l_msg_ship_to VARCHAR2 (240);
3948 l_msg_invoice_to VARCHAR2 (240);
3949 l_msg_credit_holds VARCHAR2 (240);
3950 l_msg_payment_term VARCHAR2 (240);
3951 l_msg_min_amt_agreed VARCHAR2 (240);
3952 l_msg_max_amt_agreed VARCHAR2 (240);
3953 l_msg_salesperson VARCHAR2 (240);
3954 l_msg_customer VARCHAR2 (240);
3955 l_msg_blanket_number VARCHAR2 (240);
3956 l_org_id number ; --16084377 ame bsa
3957 BEGIN
3958 l_transaction_id := TO_NUMBER (p_item_key);
3959 -- set fnd message titles for tables
3960 l_msg_bsa := fnd_message.get_string ('ONT', 'OE_NTF_BSA');
3961 l_msg_creation_date :=
3962 fnd_message.get_string ('ONT', 'OE_NTF_CREATION_DATE');
3963 l_msg_activation_date :=
3964 fnd_message.get_string ('ONT', 'OE_NTF_ACTIVATION_DATE');
3965 l_msg_expiration_date :=
3966 fnd_message.get_string ('ONT', 'OE_NTF_EXPIRATION_DATE');
3967 l_msg_ship_to := fnd_message.get_string ('ONT', 'OE_NTF_SHIP_TO');
3968 l_msg_invoice_to := fnd_message.get_string ('ONT', 'OE_NTF_INVOICE_TO');
3969 l_msg_credit_holds :=
3970 fnd_message.get_string ('ONT', 'OE_NTF_CREDIT_HOLDS');
3971 l_msg_payment_term :=
3972 fnd_message.get_string ('ONT', 'OE_NTF_PAYMENT_TERM');
3973 l_msg_min_amt_agreed :=
3974 fnd_message.get_string ('ONT', 'OE_NTF_MIN_AMT_AGREED');
3975 l_msg_max_amt_agreed :=
3976 fnd_message.get_string ('ONT', 'OE_NTF_MAX_AMT_AGREED');
3977
3978 -- set values
3979 SELECT /* MOAC_SQL_CHANGE */ headers.order_number, headers.creation_date,
3980 shipto.LOCATION NAME, invoiceto.LOCATION NAME, terms.NAME,
3981 blnk_ext.blanket_min_amount, blnk_ext.blanket_max_amount,
3982 blnk_ext.start_date_active, blnk_ext.end_date_active, headers.org_id
3983 INTO l_order_number, l_creation_date,
3984 l_ship_to_address, l_invoice_to_address, l_payment_term,
3985 l_blanket_min_amount, l_blanket_max_amount,
3986 l_start_date_active, l_end_date_active, l_org_id
3987 FROM oe_blanket_headers_all headers,
3988 oe_blanket_headers_ext blnk_ext,
3989 hz_cust_site_uses_all shipto,
3990 hz_cust_site_uses_all invoiceto,
3991 ra_terms_tl terms
3992 WHERE headers.header_id = l_transaction_id
3993 AND headers.order_number = blnk_ext.order_number(+)
3994 AND headers.ship_to_org_id = shipto.site_use_id(+)
3995 AND shipto.site_use_code(+) = 'SHIP_TO'
3996 AND shipto.org_id(+) = headers.org_id
3997 AND headers.invoice_to_org_id = invoiceto.site_use_id(+)
3998 AND invoiceto.site_use_code(+) = 'BILL_TO'
3999 AND invoiceto.org_id(+) = headers.org_id
4000 AND headers.payment_term_id = terms.term_id(+)
4001 AND terms.LANGUAGE(+) = USERENV ('LANG');
4002
4003 mo_global.set_policy_context ('S', l_org_id); -- 16084377 ame bsa
4004
4005 --- ??? Check with the PM
4006 l_credit_hold :=
4007 oe_order_wf_util.check_credit_hold
4008 (p_hold_entity_code => 'O',
4009 p_hold_entity_id => l_transaction_id
4010 );
4011
4012 --------------
4013 -- HTML --
4014 IF p_display_type IS NULL OR p_display_type = 'text/html'
4015 THEN
4016 --------------------------------------
4017 -- **HEADINGS FOR THE HEADER TABLE**--
4018 --------------------------------------
4019 l_document :=
4020 l_document
4021 || nl
4022 || nl
4023 || '<!-- OE_BLANKET_HEADERS -->'
4024 || nl
4025 || nl
4026 || '<P>';
4027 l_document :=
4028 l_document
4029 || '<br><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=4>'
4030 || l_msg_bsa
4031 || '</font><br>';
4032 l_document :=
4033 l_document
4034 || '<table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>';
4035 l_document :=
4036 l_document
4037 || '<table sumarry="" width=100% border=0 cellpadding=3 cellspacing=1 bgcolor=white> <tr>';
4038 --------------------------------------------------
4039 -- IF the WF Header Attributes are not enables ---
4040 --------------------------------------------------
4041 l_wf_header_attr := wf_core.TRANSLATE ('WF_HEADER_ATTR');
4042
4043 IF l_wf_header_attr <> 'Y'
4044 THEN
4045 -- set fnd msg title
4046 l_msg_blanket_number :=
4047 fnd_message.get_string ('ONT', 'OE_NTF_BLANKET_NUMBER');
4048 l_msg_salesperson :=
4049 fnd_message.get_string ('ONT', 'OE_NTF_SALESPERSON');
4050 l_msg_customer :=
4051 fnd_message.get_string ('ONT', 'OE_NTF_CUSTOMER');
4052 l_document :=
4053 l_document
4054 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4055 || l_msg_blanket_number
4056 || '</font></th>';
4057 l_document :=
4058 l_document
4059 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4060 || l_msg_customer
4061 || '</font></th>';
4062 l_document :=
4063 l_document
4064 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4065 || l_msg_salesperson
4066 || '</font></th>';
4067 END IF;
4068
4069 -- WF Header Attributes ---
4070
4071 -----------------------------
4072
4073 -- **HEADINGS FOR THE BLNAKET HEADER TABLE**--
4074 l_document :=
4075 l_document
4076 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4077 || l_msg_creation_date
4078 || '</font></th>';
4079 l_document :=
4080 l_document
4081 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4082 || l_msg_activation_date
4083 || '</font></th>';
4084 l_document :=
4085 l_document
4086 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4087 || l_msg_expiration_date
4088 || '</font></th>';
4089 l_document :=
4090 l_document
4091 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4092 || l_msg_ship_to
4093 || '</font></th>';
4094 l_document :=
4095 l_document
4096 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4097 || l_msg_invoice_to
4098 || '</font></th>';
4099 l_document :=
4100 l_document
4101 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4102 || l_msg_credit_holds
4103 || '</font></th>';
4104 l_document :=
4105 l_document
4106 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4107 || l_msg_payment_term
4108 || '</font></th>';
4109 l_document :=
4110 l_document
4111 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4112 || l_msg_min_amt_agreed
4113 || '</font></th>';
4114 l_document :=
4115 l_document
4116 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4117 || l_msg_max_amt_agreed
4118 || '</font></th></tr>';
4119
4120 --------------------------------------------------
4121 -- IF the WF Header Attributes are not enables ---
4122 --------------------------------------------------
4123 IF l_wf_header_attr <> 'Y'
4124 THEN
4125 l_transaction_number :=
4126 wf_engine.getitemattrnumber (p_item_type,
4127 p_item_key,
4128 'TRANSACTION_NUMBER'
4129 );
4130 l_salesrep :=
4131 wf_engine.getitemattrtext (p_item_type,
4132 p_item_key,
4133 'SALESPERSON'
4134 );
4135 l_sold_to :=
4136 wf_engine.getitemattrtext (p_item_type, p_item_key, 'SOLD_TO');
4137 l_document :=
4138 l_document
4139 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4140 || TO_CHAR (l_transaction_number)
4141 || '</font></td>';
4142 l_document :=
4143 l_document
4144 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4145 || l_sold_to
4146 || '</font></td>';
4147 l_document :=
4148 l_document
4149 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4150 || l_salesrep
4151 || '</font></td>';
4152 END IF; --- l_wf_header_attr <> 'Y' ---
4153
4154 l_document :=
4155 l_document
4156 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4157 || TO_CHAR (l_creation_date, 'DD-MON-YYYY')
4158 || '</font></td>';
4159 l_document :=
4160 l_document
4161 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4162 || TO_CHAR (l_start_date_active, 'DD-MON-YYYY')
4163 || '</font></td>';
4164 l_document :=
4165 l_document
4166 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4167 || TO_CHAR (l_end_date_active, 'DD-MON-YYYY')
4168 || '</font></td>';
4169 l_document :=
4170 l_document
4171 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4172 || l_ship_to_address
4173 || '</font></td>';
4174 l_document :=
4175 l_document
4176 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4177 || l_invoice_to_address
4178 || '</font></td>';
4179 l_document :=
4180 l_document
4181 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4182 || l_credit_hold
4183 || '</font></td>';
4184 l_document :=
4185 l_document
4186 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4187 || l_payment_term
4188 || '</font></td>';
4189 l_document :=
4190 l_document
4191 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4192 || TO_CHAR (l_blanket_min_amount)
4193 || '</font></td>';
4194 l_document :=
4195 l_document
4196 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4197 || TO_CHAR (l_blanket_max_amount)
4198 || '</font></td></tr>';
4199 l_document := l_document || '</TABLE></TD></TR></TABLE></P>' || nl;
4200 ----------------
4201 --- TEXT ---
4202 ELSIF p_display_type = 'text/plain'
4203 THEN
4204 l_transaction_number :=
4205 wf_engine.getitemattrnumber (p_item_type,
4206 p_item_key,
4207 'TRANSACTION_NUMBER'
4208 );
4209 l_salesrep :=
4210 wf_engine.getitemattrtext (p_item_type, p_item_key, 'SALESPERSON');
4211 l_sold_to :=
4212 wf_engine.getitemattrtext (p_item_type, p_item_key, 'SOLD_TO');
4213 -- set fnd msg title
4214 l_msg_blanket_number :=
4215 fnd_message.get_string ('ONT', 'OE_NTF_BLANKET_NUMBER');
4216 l_msg_salesperson :=
4217 fnd_message.get_string ('ONT', 'OE_NTF_SALESPERSON');
4218 l_msg_customer := fnd_message.get_string ('ONT', 'OE_NTF_CUSTOMER');
4219 l_document :=
4220 l_document
4221 || l_msg_blanket_number
4222 || ': '
4223 || TO_CHAR (l_transaction_number)
4224 || nlchar;
4225 l_document :=
4226 l_document || l_msg_salesperson || ': ' || l_salesrep || nlchar;
4227 l_document :=
4228 l_document || l_msg_customer || ': ' || l_sold_to || nlchar;
4229 l_document :=
4230 l_document
4231 || l_msg_creation_date
4232 || ': '
4233 || TO_CHAR (l_creation_date, 'DD-MON-YYYY')
4234 || nlchar;
4235 l_document :=
4236 l_document
4237 || l_msg_activation_date
4238 || ': '
4239 || TO_CHAR (l_start_date_active, 'DD-MON-YYYY')
4240 || nlchar;
4241 l_document :=
4242 l_document
4243 || l_msg_expiration_date
4244 || ': '
4245 || TO_CHAR (l_end_date_active, 'DD-MON-YYYY')
4246 || nlchar;
4247 l_document :=
4248 l_document || l_msg_ship_to || ': ' || l_ship_to_address || nlchar;
4249 l_document :=
4250 l_document
4251 || l_msg_invoice_to
4252 || ': '
4253 || l_invoice_to_address
4254 || nlchar;
4255 l_document :=
4256 l_document || l_msg_credit_holds || ': ' || l_credit_hold
4257 || nlchar;
4258 l_document :=
4259 l_document || l_msg_payment_term || ': ' || l_payment_term
4260 || nlchar;
4261 l_document :=
4262 l_document
4263 || l_msg_min_amt_agreed
4264 || ': '
4265 || TO_CHAR (l_blanket_min_amount)
4266 || nlchar;
4267 l_document :=
4268 l_document
4269 || l_msg_max_amt_agreed
4270 || ': '
4271 || TO_CHAR (l_blanket_max_amount)
4272 || nlchar;
4273 END IF;
4274
4275 p_x_document := l_document;
4276 END build_blanket_doc;
4277
4278 /************************
4279 ** BUILD_QUOTE_DOC *
4280 ************************/
4281 /*
4282 */
4283 PROCEDURE build_quote_doc (
4284 p_item_type IN VARCHAR2,
4285 p_item_key IN VARCHAR2,
4286 p_display_type IN VARCHAR2,
4287 p_x_document IN OUT NOCOPY VARCHAR2
4288 )
4289 IS
4290 l_wf_header_attr VARCHAR2 (30);
4291 l_salesrep VARCHAR2 (240);
4292 l_sold_to VARCHAR2 (240);
4293 l_expiration_date DATE;
4294 l_transaction_id NUMBER;
4295 l_blanket_flag VARCHAR2 (1);
4296 l_item_type VARCHAR2 (8);
4297 l_aname wf_engine.nametabtyp;
4298 l_avaluetext wf_engine.texttabtyp;
4299 l_transaction_number NUMBER;
4300 -- Quote Header table attributes
4301 l_creation_date DATE;
4302 l_ship_to_address VARCHAR2 (40);
4303 l_invoice_to_address VARCHAR2 (40);
4304 l_transactional_curr_code VARCHAR2 (3);
4305 l_payment_term VARCHAR2 (15);
4306 l_order_total NUMBER;
4307 l_credit_hold VARCHAR2 (3);
4308 l_order_margin_percent NUMBER;
4309 l_order_margin_amount NUMBER;
4310 -- Quote Line Attributes
4311 l_line_margin_percent NUMBER;
4312 l_line_rec oe_order_pub.line_rec_type;
4313 l_unit_cost NUMBER;
4314 l_unit_margin_amount NUMBER;
4315 l_margin_percent NUMBER;
4316 l_line line_record;
4317 -- ?? Is it still being used somewhere
4318 -- HTML variables
4319 l_document VARCHAR2 (32000) := '';
4320 l_line_msg VARCHAR2 (1000);
4321 nl VARCHAR2 (1) := '';
4322 i NUMBER := 0;
4323 l_url VARCHAR2 (1000);
4324 -- New Line char.
4325 nlchar VARCHAR2 (2) := fnd_global.NEWLINE;
4326 -- FND msg titles
4327 l_msg_creation_date VARCHAR2 (240);
4328 l_msg_salesperson VARCHAR2 (240);
4329 l_msg_customer VARCHAR2 (240);
4330 l_msg_quote VARCHAR2 (240);
4331 l_msg_total VARCHAR2 (240);
4332 l_msg_uom VARCHAR2 (240);
4333 l_msg_item VARCHAR2 (240);
4334 l_msg_quantity VARCHAR2 (240);
4335 l_msg_currency VARCHAR2 (240);
4336 l_msg_unit_selling_price VARCHAR2 (240);
4337 l_msg_margin_percent VARCHAR2 (240);
4338 l_msg_line_details VARCHAR2 (240);
4339 l_msg_expiration_date VARCHAR2 (240);
4340 l_msg_ship_to VARCHAR2 (240);
4341 l_msg_invoice_to VARCHAR2 (240);
4342 l_msg_credit_holds VARCHAR2 (240);
4343 l_msg_payment_term VARCHAR2 (240);
4344 l_msg_quote_number VARCHAR2 (240);
4345 l_msg_line_number VARCHAR2 (240);
4346 l_msg_quote_details VARCHAR2 (240);
4347 l_msg_first_five_lines VARCHAR2 (240);
4348
4349 -- Cursor to build line table
4350 CURSOR line_cursor (v_header_id NUMBER)
4351 IS
4352 SELECT ol.line_number
4353 || '.'
4354 || ol.shipment_number
4355 || '.'
4356 || ol.option_number
4357 || '.'
4358 || ol.component_number,
4359 msi.concatenated_segments, ol.order_quantity_uom,
4360 ol.ordered_quantity, ol.unit_selling_price,
4361 ol.ordered_quantity * ol.unit_selling_price, ol.line_id,
4362 --- the following column needed for line_margin API
4363 ol.inventory_item_id, ol.item_type_code, ol.open_flag,
4364 ol.shipped_quantity, ol.ordered_quantity,
4365 ol.source_type_code, ol.ship_from_org_id, ol.project_id,
4366 ol.actual_shipment_date, ol.fulfillment_date
4367 FROM oe_order_lines_all ol, mtl_system_items_kfv msi
4368 WHERE ol.header_id = v_header_id
4369 AND ol.inventory_item_id = msi.inventory_item_id
4370 AND msi.organization_id =
4371 NVL
4372 (ol.ship_from_org_id,
4373 oe_sys_parameters.VALUE ('MASTER_ORGANIZATION_ID')
4374 ) -- Bug 6215694
4375 ORDER BY line_number,
4376 shipment_number,
4377 option_number,
4378 component_number;
4379
4380 l_prec_inited BOOLEAN := FALSE;
4381 -- Bug 6275663
4382 l_org_id NUMBER;
4383 BEGIN
4384 l_transaction_id := TO_NUMBER (p_item_key);
4385
4386 SELECT org_id
4387 INTO l_org_id
4388 FROM oe_order_headers_all
4389 WHERE header_id = l_transaction_id;
4390
4391 mo_global.set_policy_context ('S', l_org_id);
4392
4393 -- Bug 6275663
4394 IF (NVL (oe_order_util.g_precision, 0) = 0)
4395 THEN
4396 l_prec_inited :=
4397 oe_order_util.get_precision (p_header_id => l_transaction_id);
4398 END IF;
4399
4400 -- set fnd message titles for tables
4401 l_msg_quote := fnd_message.get_string ('ONT', 'OE_NTF_QUOTE');
4402 l_msg_creation_date :=
4403 fnd_message.get_string ('ONT', 'OE_NTF_CREATION_DATE');
4404 l_msg_margin_percent :=
4405 fnd_message.get_string ('ONT', 'OE_NTF_MARGIN_PERCENT');
4406 l_msg_ship_to := fnd_message.get_string ('ONT', 'OE_NTF_SHIP_TO');
4407 l_msg_invoice_to := fnd_message.get_string ('ONT', 'OE_NTF_INVOICE_TO');
4408 l_msg_credit_holds :=
4409 fnd_message.get_string ('ONT', 'OE_NTF_CREDIT_HOLDS');
4410 l_msg_payment_term :=
4411 fnd_message.get_string ('ONT', 'OE_NTF_PAYMENT_TERM');
4412 l_msg_total := fnd_message.get_string ('ONT', 'OE_NTF_TOTAL');
4413 l_msg_line_details :=
4414 fnd_message.get_string ('ONT', 'OE_NTF_LINE_DETAILS');
4415 l_msg_item := fnd_message.get_string ('ONT', 'OE_NTF_ITEM');
4416 l_msg_uom := fnd_message.get_string ('ONT', 'OE_NTF_UOM');
4417 l_msg_quantity := fnd_message.get_string ('ONT', 'OE_NTF_QUANTITY');
4418 l_msg_currency := fnd_message.get_string ('ONT', 'OE_NTF_CURRENCY');
4419 l_msg_unit_selling_price :=
4420 fnd_message.get_string ('ONT', 'OE_NTF_UNIT_SELLING_PRICE');
4421 l_msg_line_number :=
4422 fnd_message.get_string ('ONT', 'OE_NTF_LINE_NUMBER');
4423 l_msg_quote_details :=
4424 fnd_message.get_string ('ONT', 'OE_NTF_ADL_QUOTE_DETAILS');
4425 l_msg_first_five_lines :=
4426 fnd_message.get_string ('ONT', 'OE_NTF_FIRST_FIVE_LINES');
4427 -------------------------
4428
4429 -- set value
4430 -- Build the header attribute values
4431 l_order_total := oe_oe_totals_summary.prt_order_total (l_transaction_id);
4432 --- ???? Check with PM
4433 l_credit_hold :=
4434 oe_order_wf_util.check_credit_hold
4435 (p_hold_entity_code => 'O',
4436 p_hold_entity_id => l_transaction_id
4437 );
4438 oe_margin_pvt.get_order_margin
4439 (p_header_id => l_transaction_id,
4440 x_order_margin_percent => l_order_margin_percent,
4441 x_order_margin_amount => l_order_margin_amount
4442 );
4443 l_order_margin_percent :=
4444 ROUND (l_order_margin_percent, oe_order_util.g_precision);
4445 -- Bug 6275663
4446
4447 SELECT /* MOAC_SQL_CHANGE */ headers.creation_date, shipto.NAME, invoiceto.LOCATION
4448 NAME,
4449 headers.transactional_curr_code, terms.NAME
4450 INTO l_creation_date, l_ship_to_address, l_invoice_to_address,
4451 l_transactional_curr_code, l_payment_term
4452 FROM oe_order_headers_all headers,
4453 oe_ship_to_orgs_v shipto,
4454 hz_cust_site_uses_all invoiceto,
4455 ra_terms terms
4456 WHERE headers.header_id = l_transaction_id
4457 AND headers.ship_to_org_id = shipto.organization_id(+)
4458 AND headers.invoice_to_org_id = invoiceto.site_use_id(+)
4459 AND invoiceto.site_use_code(+) = 'BILL_TO'
4460 AND invoiceto.org_id(+) = headers.org_id
4461 AND headers.payment_term_id = terms.term_id(+);
4462
4463 -- DISPLAY_TYPE = HTML --
4464 IF p_display_type IS NULL OR p_display_type = 'text/html'
4465 THEN
4466 --------------------------------------
4467 -- **HEADINGS FOR THE HEADER TABLE**--
4468 --------------------------------------
4469 l_document :=
4470 l_document
4471 || nl
4472 || nl
4473 || '<!-- OE_HEADERS_DETAILS -->'
4474 || nl
4475 || nl
4476 || '<P>';
4477 l_document :=
4478 l_document
4479 || '<br><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=4>'
4480 || l_msg_quote
4481 || '</font><br>';
4482 l_document :=
4483 l_document
4484 || '<table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>';
4485 l_document :=
4486 l_document
4487 || '<table sumarry="" width=100% border=0 cellpadding=3 cellspacing=1 bgcolor=white> <tr>';
4488 --------------------------------------------------
4489 -- IF the WF Header Attributes are not enables ---
4490 --------------------------------------------------
4491 l_wf_header_attr := wf_core.TRANSLATE ('WF_HEADER_ATTR');
4492
4493 IF l_wf_header_attr <> 'Y'
4494 THEN
4495 -- set FND msg title
4496 l_msg_quote_number :=
4497 fnd_message.get_string ('ONT', 'OE_NTF_QUOTE_NUMBER');
4498 l_msg_salesperson :=
4499 fnd_message.get_string ('ONT', 'OE_NTF_SALESPERSON');
4500 l_msg_customer :=
4501 fnd_message.get_string ('ONT', 'OE_NTF_CUSTOMER');
4502 l_msg_expiration_date :=
4503 fnd_message.get_string ('ONT', 'OE_NTF_EXPIRATION_DATE');
4504 l_document :=
4505 l_document
4506 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4507 || l_msg_quote_number
4508 || '</font></th>';
4509 l_document :=
4510 l_document
4511 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4512 || l_msg_customer
4513 || '</font></th>';
4514 l_document :=
4515 l_document
4516 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4517 || l_msg_expiration_date
4518 || '</font></th>';
4519 l_document :=
4520 l_document
4521 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4522 || l_msg_salesperson
4523 || '</font></th>';
4524 END IF;
4525
4526 -- WF Header Attributes ---
4527 -----------------------------
4528
4529 -- **HEADINGS FOR THE HEADER TABLE**--
4530 l_document :=
4531 l_document
4532 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4533 || l_msg_creation_date
4534 || '</font></th>';
4535 l_document :=
4536 l_document
4537 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4538 || l_msg_ship_to
4539 || '</font></th>';
4540 l_document :=
4541 l_document
4542 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4543 || l_msg_invoice_to
4544 || '</font></th>';
4545 l_document :=
4546 l_document
4547 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4548 || l_msg_credit_holds
4549 || '</font></th>';
4550 l_document :=
4551 l_document
4552 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4553 || l_msg_currency
4554 || '</font></th>';
4555 l_document :=
4556 l_document
4557 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4558 || l_msg_total
4559 || '</font></th>';
4560 l_document :=
4561 l_document
4562 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4563 || l_msg_margin_percent
4564 || '</font></th>';
4565 l_document :=
4566 l_document
4567 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4568 || l_msg_payment_term
4569 || '</font></th></tr>';
4570
4571 --------------------------------------------------
4572 -- IF the WF Header Attributes are not enables ---
4573 --------------------------------------------------
4574 IF l_wf_header_attr <> 'Y'
4575 THEN
4576 l_transaction_number :=
4577 wf_engine.getitemattrnumber (p_item_type,
4578 p_item_key,
4579 'TRANSACTION_NUMBER'
4580 );
4581 l_salesrep :=
4582 wf_engine.getitemattrtext (p_item_type,
4583 p_item_key,
4584 'SALESPERSON'
4585 );
4586 l_sold_to :=
4587 wf_engine.getitemattrtext (p_item_type, p_item_key, 'SOLD_TO');
4588
4589 BEGIN
4590 --Bug 12884612: Using to_date to convert from text to date correctly
4591 l_expiration_date :=
4592 TO_DATE (wf_engine.getitemattrtext (p_item_type,
4593 p_item_key,
4594 'EXPIRATION_DATE'
4595 ),
4596 'DD-MON-RRRR'
4597 );
4598 EXCEPTION
4599 WHEN OTHERS
4600 THEN
4601 -- to avoid regression for quotes already open at the time of 12884612 patch application
4602 l_expiration_date :=
4603 TO_DATE (wf_engine.getitemattrtext (p_item_type,
4604 p_item_key,
4605 'EXPIRATION_DATE'
4606 )
4607 );
4608 END;
4609
4610 l_document :=
4611 l_document
4612 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4613 || TO_CHAR (l_transaction_number)
4614 || '</font></td>';
4615 l_document :=
4616 l_document
4617 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4618 || l_sold_to
4619 || '</font></td>';
4620 l_document :=
4621 l_document
4622 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4623 || TO_CHAR (l_expiration_date, 'DD-MON-YYYY')
4624 || '</font></td>';
4625 l_document :=
4626 l_document
4627 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4628 || l_salesrep
4629 || '</font></td>';
4630 END IF; -- l_wf_header_attr <> 'Y' --
4631
4632 l_document :=
4633 l_document
4634 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4635 || TO_CHAR (l_creation_date, 'DD-MON-YYYY')
4636 || '</font></td>';
4637 l_document :=
4638 l_document
4639 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4640 || l_ship_to_address
4641 || '</font></td>';
4642 l_document :=
4643 l_document
4644 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4645 || l_invoice_to_address
4646 || '</font></td>';
4647 l_document :=
4648 l_document
4649 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4650 || l_credit_hold
4651 || '</font></td>';
4652 l_document :=
4653 l_document
4654 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4655 || l_transactional_curr_code
4656 || '</font></td>';
4657 l_document :=
4658 l_document
4659 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4660 || l_order_total
4661 || '</font></td>';
4662 l_document :=
4663 l_document
4664 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4665 || TO_CHAR (l_order_margin_percent)
4666 || '</font></td>';
4667 l_document :=
4668 l_document
4669 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4670 || l_payment_term
4671 || '</font></td></tr>';
4672 l_document := l_document || '</TABLE></TD></TR></TABLE></P>' || nl;
4673 -----------------------------------
4674 -- Build the Lines Detail Table --
4675 -----------------------------------
4676 l_document :=
4677 l_document
4678 || nl
4679 || nl
4680 || '<!-- OE_LINE_DETAILS -->'
4681 || nl
4682 || nl
4683 || '<P>';
4684 l_document :=
4685 l_document
4686 || '<br><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=4>'
4687 || l_msg_line_details
4688 || '</font><br>';
4689 l_document := l_document || l_msg_first_five_lines;
4690 l_document :=
4691 l_document
4692 || '<br><table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>';
4693 l_document :=
4694 l_document
4695 || '<table sumarry="" width=100% border=0 cellpadding=3 cellspacing=1 bgcolor=white> <tr>';
4696 -- **HEADINGS FOR THE LINE TABLE**--
4697 l_document :=
4698 l_document
4699 || '<th scope=col width=10% align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4700 || l_msg_line_number
4701 || '</font></th>';
4702 l_document :=
4703 l_document
4704 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4705 || l_msg_item
4706 || '</font></th>';
4707 l_document :=
4708 l_document
4709 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4710 || l_msg_uom
4711 || '</font></th>';
4712 l_document :=
4713 l_document
4714 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4715 || l_msg_quantity
4716 || '</font></th>';
4717 l_document :=
4718 l_document
4719 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4720 || l_msg_unit_selling_price
4721 || '</font></th>';
4722 l_document :=
4723 l_document
4724 || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4725 || l_msg_margin_percent
4726 || '</font></th></tr>';
4727
4728 --** BUILD THE LINE TABLE** --
4729 -- Line detail columns -> Items, UOM, Selling Price
4730 OPEN line_cursor (l_transaction_id);
4731
4732 LOOP
4733 FETCH line_cursor
4734 INTO l_line;
4735
4736 EXIT WHEN line_cursor%NOTFOUND;
4737 i := i + 1;
4738 l_line_rec.header_id := l_transaction_id; --bug 5210735
4739 l_line_rec.line_id := l_line.line_id;
4740 l_line_rec.inventory_item_id := l_line.inventory_item_id;
4741 l_line_rec.item_type_code := l_line.item_type_code;
4742 l_line_rec.open_flag := l_line.open_flag;
4743 l_line_rec.shipped_quantity := l_line.shipped_quantity;
4744 l_line_rec.ordered_quantity := l_line.ordered_quantity;
4745 l_line_rec.source_type_code := l_line.source_type_code;
4746 l_line_rec.source_type_code := l_line.source_type_code;
4747 l_line_rec.ship_from_org_id := l_line.ship_from_org_id;
4748 l_line_rec.project_id := l_line.project_id;
4749 l_line_rec.actual_shipment_date := l_line.actual_shipment_date;
4750 l_line_rec.fulfillment_date := l_line.fulfillment_date;
4751 l_line_rec.unit_selling_price := l_line.unit_selling_price;
4752 --bug 5155086
4753 oe_margin_pvt.get_line_margin
4754 (p_line_rec => l_line_rec,
4755 x_unit_cost => l_unit_cost,
4756 x_unit_margin_amount => l_unit_margin_amount,
4757 x_margin_percent => l_line_margin_percent
4758 );
4759 l_line_margin_percent :=
4760 ROUND (l_line_margin_percent, oe_order_util.g_precision);
4761 -- Bug 6275663
4762 l_document :=
4763 l_document
4764 || '<tr><td align=CENTER valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4765 || NVL (l_line.line_num, '&' || 'nbsp')
4766 || '</font></td>';
4767 l_document :=
4768 l_document
4769 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4770 || NVL (l_line.item, '&' || 'nbsp')
4771 || '</font></td>';
4772 l_document :=
4773 l_document
4774 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4775 || NVL (l_line.uom, '&' || 'nbsp')
4776 || '</font></td>';
4777 l_document :=
4778 l_document
4779 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4780 || NVL (TO_CHAR (l_line.quantity), '&' || 'nbsp')
4781 || '</font></td>';
4782 l_document :=
4783 l_document
4784 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4785 || TO_CHAR (l_line.unit_selling_price)
4786 || '</font></td>';
4787 l_document :=
4788 l_document
4789 || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>'
4790 || TO_CHAR (l_line_margin_percent)
4791 || '</font></td>';
4792 EXIT WHEN i = 5;
4793 END LOOP;
4794
4795 CLOSE line_cursor;
4796
4797 l_document := l_document || '</TABLE></TD></TR></TABLE></P>' || nl;
4798 -- show the URL link to OIP
4799 l_url :=
4800 RTRIM (fnd_profile.VALUE ('APPS_FRAMEWORK_AGENT'), '/')
4801 || '/OA_HTML/OA.jsp?akRegionCode=ORDER_DETAILS_PAGE'
4802 || '&'
4803 || 'akRegionApplicationId=660'
4804 || '&'
4805 || 'HeaderId='
4806 || p_item_key;
4807 l_document :=
4808 l_document
4809 || '<TABLE width="100%" SUMMARY=""><TR> <TD align=right><A HREF="'
4810 || l_url
4811 || '" TARGET="_top">'
4812 || l_msg_quote_details
4813 || '</A></TD></TR></TABLE>'
4814 || nl;
4815 -------------------------
4816 -- DISPLAY_TYPE = TEXT --
4817 ELSIF p_display_type = 'text/plain'
4818 THEN
4819 -- set FND msg title
4820 l_msg_quote_number :=
4821 fnd_message.get_string ('ONT', 'OE_NTF_QUOTE_NUMBER');
4822 l_msg_salesperson :=
4823 fnd_message.get_string ('ONT', 'OE_NTF_SALESPERSON');
4824 l_msg_customer := fnd_message.get_string ('ONT', 'OE_NTF_CUSTOMER');
4825 l_msg_expiration_date :=
4826 fnd_message.get_string ('ONT', 'OE_NTF_EXPIRATION_DATE');
4827 l_transaction_number :=
4828 wf_engine.getitemattrnumber (p_item_type,
4829 p_item_key,
4830 'TRANSACTION_NUMBER'
4831 );
4832 l_salesrep :=
4833 wf_engine.getitemattrtext (p_item_type, p_item_key, 'SALESPERSON');
4834 l_sold_to :=
4835 wf_engine.getitemattrtext (p_item_type, p_item_key, 'SOLD_TO');
4836
4837 BEGIN
4838 --Bug 12884612: Using to_date to convert from text to date correctly
4839 l_expiration_date :=
4840 TO_DATE (wf_engine.getitemattrtext (p_item_type,
4841 p_item_key,
4842 'EXPIRATION_DATE'
4843 ),
4844 'DD-MON-RRRR'
4845 );
4846 EXCEPTION
4847 WHEN OTHERS
4848 THEN
4849 -- to avoid regression for quotes already open at the time of 12884612 patch application
4850 l_expiration_date :=
4851 TO_DATE (wf_engine.getitemattrtext (p_item_type,
4852 p_item_key,
4853 'EXPIRATION_DATE'
4854 )
4855 );
4856 END;
4857
4858 l_document :=
4859 l_document || l_msg_quote_number || l_transaction_number || nlchar;
4860 l_document := l_document || l_msg_customer || l_sold_to || nlchar;
4861 l_document :=
4862 l_document
4863 || l_msg_expiration_date
4864 || TO_CHAR (l_expiration_date, 'DD-MON-YYYY')
4865 || nlchar;
4866 l_document := l_document || l_msg_salesperson || l_salesrep || nlchar;
4867 l_document :=
4868 l_document
4869 || l_msg_creation_date
4870 || TO_CHAR (l_creation_date, 'DD-MON-YYYY')
4871 || nlchar;
4872 l_document :=
4873 l_document || l_msg_ship_to || l_ship_to_address || nlchar;
4874 l_document :=
4875 l_document || l_msg_invoice_to || l_invoice_to_address || nlchar;
4876 l_document :=
4877 l_document || l_msg_credit_holds || l_credit_hold || nlchar;
4878 l_document :=
4879 l_document || l_msg_currency || l_transactional_curr_code
4880 || nlchar;
4881 l_document := l_document || l_msg_total || l_order_total || nlchar;
4882 l_document :=
4883 l_document
4884 || l_msg_total
4885 || TO_CHAR (l_order_margin_percent)
4886 || nlchar;
4887 l_document :=
4888 l_document || l_msg_payment_term || l_payment_term || nlchar;
4889 -- ?? fix the nbsp
4890 /*
4891 l_document := l_document || NLCHAR || NLCHAR || '<!-- OE_LINES_DETAILS -->' || NLCHAR || NLCHAR;
4892 l_document := l_document || 'Line Number:' || nvl(l_line.line_num, '&' || 'nbsp') || NLCHAR;
4893 l_document := l_document || 'Item:' || nvl(l_line.item, '&' || 'nbsp') || NLCHAR;
4894 l_document := l_document || 'UOM:' || nvl(l_line.uom, '&'||'nbsp') || NLCHAR;
4895 l_document := l_document || 'Quantity:' || nvl(to_char(l_line.quantity), '&' ||'nbsp') || NLCHAR;
4896 l_document := l_document || 'Unit Price:' || TO_CHAR(l_line.unit_selling_price) || NLCHAR;
4897 l_document := l_document || 'Margin:' || TO_CHAR(l_line_margin_percent) || NLCHAR;
4898 */
4899 END IF;
4900
4901 p_x_document := l_document;
4902 END build_quote_doc;
4903
4904 /* todo:Move it to the Holds package later */
4905
4906 /*************************
4907 ** CHECK_CREDIT_HOLD *
4908 *************************/
4909 FUNCTION check_credit_hold (
4910 p_hold_entity_code IN VARCHAR2,
4911 p_hold_entity_id IN NUMBER
4912 )
4913 RETURN VARCHAR2
4914 IS
4915 l_result_out VARCHAR2 (30);
4916 BEGIN
4917 -- Initialize result to TRUE i.e. holds are found
4918 l_result_out := 'Y';
4919
4920 BEGIN
4921 SELECT 'Y'
4922 INTO l_result_out
4923 FROM oe_hold_sources hs, oe_hold_definitions h
4924 WHERE hs.hold_entity_code = p_hold_entity_code
4925 AND TO_CHAR (hs.hold_entity_id) =
4926 TO_CHAR (p_hold_entity_id)
4927 --9371206
4928 AND hs.hold_id = 1
4929 AND hs.released_flag = 'N'
4930 AND ROUND (NVL (hs.hold_until_date, SYSDATE)) >= ROUND (SYSDATE)
4931 AND hs.hold_id = h.hold_id
4932 AND SYSDATE BETWEEN NVL (h.start_date_active, SYSDATE)
4933 AND NVL (h.end_date_active, SYSDATE);
4934 EXCEPTION
4935 WHEN NO_DATA_FOUND
4936 THEN
4937 l_result_out := 'N';
4938 --IF l_debug_level > 0 THEN
4939 -- oe_debug_pub.add( 'NO HOLDS FOUND FOR HEADER ID: ' || P_HDR_ID ) ;
4940 --END IF;
4941 WHEN TOO_MANY_ROWS
4942 THEN
4943 NULL;
4944 END;
4945
4946 RETURN l_result_out;
4947 END check_credit_hold;
4948
4949 PROCEDURE complete_eligible_and_book (
4950 p_api_version_number IN NUMBER,
4951 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4952 p_header_id IN NUMBER,
4953 x_return_status OUT NOCOPY VARCHAR2,
4954 x_msg_count OUT NOCOPY NUMBER,
4955 x_msg_data OUT NOCOPY VARCHAR2
4956 )
4957 IS
4958 l_api_name CONSTANT VARCHAR2 (30) := 'Complete_eligible_and_Book';
4959 -- Use local variables instead of literals.
4960 l_wfeng_status VARCHAR2 (24) := 'WFENG_STATUS';
4961 l_root VARCHAR2 (24) := 'ROOT';
4962 l_negotiation VARCHAR2 (1) := 'N';
4963 l_oenh VARCHAR2 (8) := 'OENH';
4964 l_oebh VARCHAR2 (8) := 'OEBH';
4965 l_oeol VARCHAR2 (8) := 'OEOL';
4966 l_oeoh VARCHAR2 (8) := 'OEOH';
4967 l_standard_block VARCHAR2 (128)
4968 := 'OE_STANDARD_WF.STANDARD_BLOCK';
4969 l_eng_notified VARCHAR2 (8) := 'NOTIFIED';
4970 l_eng_deferred VARCHAR2 (8) := 'DEFERRED';
4971 l_retval VARCHAR2 (30);
4972 l_activity VARCHAR2 (30);
4973 l_book_eligible VARCHAR2 (1);
4974 l_book_deferred VARCHAR2 (1);
4975 l_booked_flag VARCHAR2 (1);
4976 l_flow_status_code VARCHAR2 (30);
4977 l_flow_status VARCHAR2 (256);
4978 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4979
4980 CURSOR c_eligible_activity
4981 IS
4982 SELECT pa.activity_name
4983 FROM wf_item_activity_statuses s,
4984 wf_process_activities pa,
4985 wf_lookups l,
4986 wf_activities_vl act
4987 WHERE s.activity_status = l.lookup_code
4988 AND l.lookup_type = l_wfeng_status
4989 AND s.process_activity = pa.instance_id
4990 AND pa.activity_item_type = act.item_type
4991 AND pa.activity_name = act.NAME
4992 AND pa.process_name <> l_root
4993 AND act.VERSION =
4994 (SELECT MAX (VERSION)
4995 FROM wf_activities_vl act2
4996 WHERE act.item_type = act2.item_type
4997 AND act.NAME = act2.NAME)
4998 AND UPPER (s.activity_status) = l_eng_notified
4999 AND s.item_type = l_oenh
5000 AND s.item_key = p_header_id
5001 AND act.FUNCTION = l_standard_block;
5002
5003 CURSOR book_eligible
5004 IS
5005 SELECT 'Y'
5006 FROM wf_item_activity_statuses wias, wf_process_activities wpa
5007 WHERE wias.item_type = 'OEOH'
5008 AND wias.item_key = p_header_id
5009 AND wias.activity_status = 'NOTIFIED'
5010 AND wpa.activity_name = 'BOOK_ELIGIBLE'
5011 AND wpa.instance_id = wias.process_activity;
5012
5013 CURSOR book_deferred
5014 IS
5015 SELECT 'Y'
5016 FROM wf_item_activity_statuses wias, wf_process_activities wpa
5017 WHERE wias.item_type = 'OEOH'
5018 AND wias.item_key = p_header_id
5019 AND wias.activity_status = 'DEFERRED'
5020 AND wpa.activity_name = 'BOOK_DEFER'
5021 AND wpa.instance_id = wias.process_activity;
5022 BEGIN
5023 IF l_debug_level > 0
5024 THEN
5025 oe_debug_pub.ADD ( 'In Complete_eligible_and_Book, header_id'
5026 || p_header_id
5027 );
5028 END IF;
5029
5030 OPEN c_eligible_activity;
5031
5032 FETCH c_eligible_activity
5033 INTO l_activity;
5034
5035 IF c_eligible_activity%NOTFOUND
5036 THEN
5037 oe_debug_pub.ADD ('c_eligible_activity NOT FOUND');
5038 fnd_message.set_name ('ONT', 'OE_NO_ELIGIBLE_ACTIVITIES');
5039 oe_msg_pub.ADD;
5040 ELSE
5041 CLOSE c_eligible_activity;
5042
5043 IF l_debug_level > 0
5044 THEN
5045 oe_debug_pub.ADD ('ELIGIBLE Activity: ' || l_activity);
5046 END IF;
5047
5048 BEGIN
5049 wf_engine.completeactivityinternalname (oe_globals.g_wfi_ngo,
5050 TO_CHAR (p_header_id),
5051 l_activity,
5052 l_retval
5053 ,TRUE
5054 ); --14599978
5055 EXCEPTION
5056 WHEN OTHERS
5057 THEN
5058 oe_debug_pub.ADD ('Workflow complete activity failed. Raising');
5059 RAISE;
5060 END;
5061
5062 -- The order could have been booked already becuase the booking was synchronous.
5063 SELECT booked_flag, flow_status_code
5064 INTO l_booked_flag, l_flow_status_code
5065 FROM oe_order_headers
5066 WHERE header_id = p_header_id;
5067
5068 IF l_debug_level > 0
5069 THEN
5070 oe_debug_pub.ADD ('l_booked_flag: ' || l_booked_flag);
5071 END IF;
5072
5073 IF l_booked_flag = 'N'
5074 THEN
5075 -- If the order is book eligigble then try to book it also.
5076 OPEN book_eligible;
5077
5078 FETCH book_eligible
5079 INTO l_book_eligible;
5080
5081 IF (book_eligible%NOTFOUND)
5082 THEN
5083 IF l_debug_level > 0
5084 THEN
5085 oe_debug_pub.ADD ('BOOKING NOT ELIGIBLE');
5086 END IF;
5087
5088 -- Booking can be a high cost activity and may be deferred
5089 OPEN book_deferred;
5090
5091 FETCH book_deferred
5092 INTO l_book_deferred;
5093
5094 IF (book_deferred%FOUND)
5095 THEN
5096 IF l_debug_level > 0
5097 THEN
5098 oe_debug_pub.ADD ('BOOKING IS DEFERRED');
5099 END IF;
5100
5101 fnd_message.set_name ('ONT', 'OE_ORDER_BOOK_DEFERRED');
5102 oe_msg_pub.ADD;
5103
5104 CLOSE book_deferred;
5105 ELSE
5106 SELECT meaning
5107 INTO l_flow_status
5108 FROM oe_lookups
5109 WHERE lookup_type = 'FLOW_STATUS'
5110 AND lookup_code = l_flow_status_code
5111 AND enabled_flag = 'Y'
5112 AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)
5113 AND NVL (end_date_active, SYSDATE);
5114
5115 fnd_message.set_name ('ONT', 'OE_QUOTE_NOT_BOOKED');
5116 fnd_message.set_token ('FLOW_STATUS', l_flow_status);
5117 oe_msg_pub.ADD;
5118 END IF; -- book_deferred%FOUND
5119 ELSE -- book_eligible%NOTFOUND
5120 IF l_debug_level > 0
5121 THEN
5122 oe_debug_pub.ADD
5123 ('Calling OE_Order_Book_Util.Complete_Book_Eligible');
5124 END IF;
5125
5126 oe_order_book_util.complete_book_eligible
5127 (p_api_version_number => 1.0,
5128 p_header_id => p_header_id,
5129 x_return_status => x_return_status,
5130 x_msg_count => x_msg_count,
5131 x_msg_data => x_msg_data
5132 );
5133 END IF; -- book_eligible%NOTFOUND
5134 END IF; -- l_booked_flag = 'N'
5135 END IF; -- l_activity_Cur%NOTFOUND
5136 EXCEPTION
5137 WHEN fnd_api.g_exc_error
5138 THEN
5139 x_return_status := fnd_api.g_ret_sts_error;
5140
5141 IF (book_eligible%ISOPEN)
5142 THEN
5143 CLOSE book_eligible;
5144 END IF;
5145
5146 oe_msg_pub.count_and_get (p_count => x_msg_count,
5147 p_data => x_msg_data
5148 );
5149 oe_msg_pub.reset_msg_context (p_entity_code => 'HEADER');
5150 WHEN fnd_api.g_exc_unexpected_error
5151 THEN
5152 x_return_status := fnd_api.g_ret_sts_unexp_error;
5153
5154 IF (book_eligible%ISOPEN)
5155 THEN
5156 CLOSE book_eligible;
5157 END IF;
5158
5159 oe_msg_pub.count_and_get (p_count => x_msg_count,
5160 p_data => x_msg_data
5161 );
5162 oe_msg_pub.reset_msg_context (p_entity_code => 'HEADER');
5163 WHEN OTHERS
5164 THEN
5165 x_return_status := fnd_api.g_ret_sts_unexp_error;
5166
5167 IF (book_eligible%ISOPEN)
5168 THEN
5169 CLOSE book_eligible;
5170 END IF;
5171
5172 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
5173 THEN
5174 oe_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5175 END IF;
5176
5177 oe_msg_pub.count_and_get (p_count => x_msg_count,
5178 p_data => x_msg_data
5179 );
5180 oe_msg_pub.reset_msg_context (p_entity_code => 'HEADER');
5181 END complete_eligible_and_book;
5182 END oe_order_wf_util;