[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_WF_UTIL
Source
1 PACKAGE BODY OE_Order_Wf_Util AS
2 /* $Header: OEXUOWFB.pls 120.17.12010000.3 2008/10/31 20:51:55 snimmaga 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, --- the following column needed for line_margin API
14 item_type_code VARCHAR2(30),
15 open_flag VARCHAR2(1),
16 SHIPPED_QUANTITY NUMBER,
17 ORDERED_QUANTITY NUMBER,
18 SOURCE_TYPE_CODE VARCHAR2(30),
19 SHIP_FROM_ORG_ID NUMBER,
20 PROJECT_ID NUMBER,
21 ACTUAL_SHIPMENT_DATE DATE,
22 FULFILLMENT_DATE DATE
23 );
24
25 -- This procedure retrieves the name of the flow that needs to
26 -- be created.
27 FUNCTION Get_ProcessName
28 ( p_itemtype IN VARCHAR2
29 , p_itemkey IN VARCHAR2
30 , p_wfasgn_item_type IN VARCHAR2 := FND_API.G_MISS_CHAR
31 , p_SalesDocumentTypeCode IN VARCHAR2 Default null
32 , p_line_rec IN OE_Order_PUB.Line_Rec_Type := OE_Order_Pub.G_MISS_LINE_REC
33 ) RETURN VARCHAR2
34 IS
35 l_process_name varchar2(30);
36
37 /* In the five cursors below, trunc(sysdate) was put in instead of sysdate in expr.
38 trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate) to fix bug 3108864 */
39
40 CURSOR find_HdrProcessname(itemkey varchar2) is
41 select wf_assign.process_name
42 from oe_workflow_assignments wf_assign,
43 oe_order_headers_all header
44 where header.header_id = to_number(itemkey)
45 and header.order_type_id = wf_assign.order_type_id
46 and sysdate >= wf_assign.start_date_active
47 and trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
48 and wf_assign.line_type_id IS NULL
49 and nvl(wf_assign.wf_item_type, OE_GLOBALS.G_WFI_HDR) = OE_GLOBALS.G_WFI_HDR;
50 /* NEED UPGRADE ON NEW COLUMN? */
51
52 CURSOR find_LineProcessname is
53 SELECT wf_assign.process_name
54 FROM oe_workflow_assignments wf_assign,
55 oe_order_headers_all header
56 WHERE nvl(p_wfasgn_item_type,'-99') =
57 nvl(wf_assign.item_type_code,nvl(p_wfasgn_item_type,'-99'))
58 AND header.header_id = p_line_rec.header_id AND
59 header.order_type_id = wf_assign.order_type_id
60 AND p_line_rec.line_type_id = wf_assign.line_type_id
61 AND wf_assign.line_type_id IS NOT NULL
62 AND sysdate >= wf_assign.start_date_active
63 AND trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
64 Order by wf_assign.item_type_code;
65
66 CURSOR find_NegotiateHdrProcessname(itemkey varchar2) is
67 SELECT wf_assign.process_name
68 FROM oe_workflow_assignments wf_assign,
69 oe_order_headers_all header
70 WHERE header.header_id = to_number(itemkey)
71 and header.order_type_id = wf_assign.order_type_id
72 and sysdate >= wf_assign.start_date_active
73 and trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
74 and wf_assign.wf_item_type = OE_GLOBALS.G_WFI_NGO
75 and wf_assign.line_type_id IS NULL;
76
77 CURSOR find_BktNgoHdrProcessname(itemkey varchar2) is
78 SELECT wf_assign.process_name
79 FROM oe_workflow_assignments wf_assign,
80 oe_blanket_headers_all header
81 WHERE header.header_id = to_number(itemkey)
82 and header.order_type_id = wf_assign.order_type_id
83 and sysdate >= wf_assign.start_date_active
84 and trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
85 and wf_assign.wf_item_type = OE_GLOBALS.G_WFI_NGO
86 and wf_assign.line_type_id IS NULL;
87
88 CURSOR find_BlanketHdrProcessname(itemkey varchar2) is
89 SELECT wf_assign.process_name
90 FROM oe_workflow_assignments wf_assign,
91 oe_blanket_headers_all blanket
92 WHERE blanket.header_id = to_number(itemkey)
93 and blanket.order_type_id = wf_assign.order_type_id
94 and sysdate >= wf_assign.start_date_active
95 and trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
96 and wf_assign.wf_item_type = OE_GLOBALS.G_WFI_BKT
97 and wf_assign.line_type_id IS NULL;
98
99 --
100 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
101 --
102 BEGIN
103
104 IF l_debug_level > 0 THEN
105 oe_debug_pub.add( 'IN GET_PROCESSNAME: ' || p_itemtype || '/' || p_itemkey || '/' || p_wfasgn_item_type) ;
106 END IF;
107
108 IF (p_itemtype = OE_GLOBALS.G_WFI_HDR) THEN
109
110 OPEN find_HdrProcessname(p_itemkey);
111 FETCH find_HdrProcessname
112 INTO l_process_name;
113
114 CLOSE find_HdrProcessname;
115 ELSIF (p_itemtype = OE_GLOBALS.G_WFI_LIN) THEN
116 IF l_debug_level > 0 THEN
117 oe_debug_pub.add( 'header_id: ' || p_line_rec.header_id || ' line_id: ' || p_line_rec.line_id || ' line_type_id: ' || p_line_rec.line_type_id);
118 END IF;
119 OPEN find_LineProcessname;
120 FETCH find_LineProcessname
121 INTO l_process_name;
122
123 CLOSE find_LineProcessname;
124 ELSIF (p_itemtype = OE_GLOBALS.G_WFI_NGO) THEN
125 IF nvl(p_SalesDocumentTypeCode, 'O') = 'O' THEN
126 OPEN find_NegotiateHdrProcessname(p_itemkey);
127 FETCH find_NegotiateHdrProcessname
128 INTO l_process_name;
129
130 CLOSE find_NegotiateHdrProcessname;
131 ELSIF nvl(p_SalesDocumentTypeCode, 'O') = 'B' THEN
132 OPEN find_BktNgoHdrProcessname(p_itemkey);
133 FETCH find_BktNgoHdrProcessname
134 INTO l_process_name;
135
136 CLOSE find_BktNgoHdrProcessname;
137 ELSE
138 -- error
139 RAISE FND_API.G_EXC_ERROR;
140 END IF;
141
142 ELSIF (p_itemtype = OE_GLOBALS.G_WFI_BKT) THEN
143 OPEN find_BlanketHdrProcessname(p_itemkey);
144 FETCH find_BlanketHdrProcessname
145 INTO l_process_name;
146
147 CLOSE find_BlanketHdrProcessname;
148 END IF;
149
150
151 IF l_process_name IS NULL THEN
152 RAISE NO_DATA_FOUND;
153 END IF;
154
155 IF l_debug_level > 0 THEN
156 oe_debug_pub.add( 'PROCESS NAME IS '||L_PROCESS_NAME ) ;
157 END IF;
158
159 RETURN l_process_name;
160 IF l_debug_level > 0 THEN
161 oe_debug_pub.add( 'EXITING GET_PROCESSNAME' ) ;
162 END IF;
163
164 EXCEPTION
165
166 WHEN NO_DATA_FOUND THEN
167 fnd_message.set_name('ONT','OE_MISS_FLOW');
168 OE_MSG_PUB.Add;
169 RAISE FND_API.G_EXC_ERROR;
170
171 WHEN OTHERS THEN
172 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
173 THEN
174 OE_MSG_PUB.Add_Exc_Msg
175 ( G_PKG_NAME
176 , 'GET_ProcessName'
177 );
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 END IF;
180 END Get_ProcessName;
181
182
183 PROCEDURE Set_Header_Descriptor(document_id in varchar2,
184 display_type in varchar2,
185 document in out NOCOPY /* file.sql.39 change */ varchar2,
186 document_type in out NOCOPY /* file.sql.39 change */ varchar2)
187 IS
188
189 l_header_id NUMBER;
190 l_order_number NUMBER;
191 l_order_type_id NUMBER;
192 l_order_type_name VARCHAR2(80);
193 l_order_category_code VARCHAR2(30);
194 l_order_type_txt VARCHAR2(2000);
195 l_header_txt VARCHAR2(2000);
196 --
197 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
198 --
199 BEGIN
200 document_type := display_type;
201 BEGIN
202 -- if viewing method is through URL
203 -- fix bug 1332384
204 SELECT item_key
205 INTO l_header_id
206 FROM wf_item_activity_statuses
207 where notification_id = to_number(document_id);
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 -- if viewing method is email
211 l_header_id := to_number(wf_engine.setctx_itemkey);
212 END;
213
214 SELECT order_number, order_type_id, order_category_code
215 into l_order_number, l_order_type_id, l_order_category_code
216 from oe_order_headers_all
217 where header_id = l_header_id;
218
219 SELECT T.NAME
220 INTO l_order_type_name
221 FROM OE_TRANSACTION_TYPES_TL T
222 WHERE T.LANGUAGE = userenv('LANG')
223 AND T.TRANSACTION_TYPE_ID = l_order_type_id;
224
225 fnd_message.set_name('ONT', 'OE_WF_ORDER_TYPE');
226 fnd_message.set_token('ORDER_TYPE', l_order_type_name);
227 l_order_type_txt := fnd_message.get;
228
229 IF l_order_category_code = 'RETURN' THEN
230 fnd_message.set_name('ONT', 'OE_WF_RETURN_ORDER');
231 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
232 l_header_txt := fnd_message.get;
233 ELSE
234 fnd_message.set_name('ONT', 'OE_WF_SALES_ORDER');
235 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
236 l_header_txt := fnd_message.get;
237 END IF;
238 document := substrb(l_order_type_txt || ', ' || l_header_txt, 1, 240);
239
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
244 THEN
245 OE_MSG_PUB.Add_Exc_Msg
246 (G_PKG_NAME
247 , 'Set_Header_Descriptor'
248 );
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250 END IF;
251
252 END Set_Header_Descriptor;
253
254
255 PROCEDURE Set_Line_Descriptor(document_id in varchar2,
256 display_type in varchar2,
257 document in out NOCOPY /* file.sql.39 change */ varchar2,
258 document_type in out NOCOPY /* file.sql.39 change */ varchar2)
259 IS
260 l_header_id NUMBER;
261 l_line_id NUMBER;
262 l_order_number NUMBER;
263 l_order_type_id NUMBER;
264 l_order_type_name VARCHAR2(80);
265 l_order_category_code VARCHAR2(30);
266 l_order_type_txt VARCHAR2(2000);
267 l_line_txt VARCHAR2(2000);
268 l_line_number NUMBER;
269 l_shipment_number NUMBER;
270 l_option_number NUMBER;
271 l_service_number NUMBER;
272
273 --
274 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
275 --
276 BEGIN
277 document_type := display_type;
278 BEGIN
279 -- if viewing method is through URL
280 -- fix bug 1332384
281 SELECT item_key
282 INTO l_line_id
283 FROM wf_item_activity_statuses
284 where notification_id = to_number(document_id);
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287 -- if viewing method is email
288 l_line_id := to_number(wf_engine.setctx_itemkey);
289 END;
290 SELECT header_id, line_number, shipment_number, option_number, service_number
291 into l_header_id, l_line_number, l_shipment_number, l_option_number, l_service_number
292 FROM oe_order_lines_all
293 WHERE line_id = l_line_id;
294
295 SELECT order_number, order_type_id, order_category_code
296 into l_order_number, l_order_type_id, l_order_category_code
297 from oe_order_headers_all
298 where header_id = l_header_id;
299
300 SELECT T.NAME
301 INTO l_order_type_name
302 FROM OE_TRANSACTION_TYPES_TL T
303 WHERE T.LANGUAGE = userenv('LANG')
304 AND T.TRANSACTION_TYPE_ID = l_order_type_id;
305
306 fnd_message.set_name('ONT', 'OE_WF_ORDER_TYPE');
307 fnd_message.set_token('ORDER_TYPE', l_order_type_name);
308 l_order_type_txt := fnd_message.get;
309
310 IF l_order_category_code = 'RETURN' THEN
311 fnd_message.set_name('ONT', 'OE_WF_RETURN_LINE');
312 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
313 fnd_message.set_token('LINE_NUMBER', to_char(l_line_number));
314 fnd_message.set_token('SHIPMENT_NUMBER', to_char(l_shipment_number));
315 fnd_message.set_token('OPTION_NUMBER', to_char(l_option_number));
316 fnd_message.set_token('SERVICE_NUMBER', to_char(l_service_number));
317 l_line_txt := fnd_message.get;
318 ELSE
319 fnd_message.set_name('ONT', 'OE_WF_LINE');
320 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
321 fnd_message.set_token('LINE_NUMBER', to_char(l_line_number));
322 fnd_message.set_token('SHIPMENT_NUMBER', to_char(l_shipment_number));
323 fnd_message.set_token('OPTION_NUMBER', to_char(l_option_number));
324 fnd_message.set_token('SERVICE_NUMBER', to_char(l_service_number));
325 l_line_txt := fnd_message.get;
326 END IF;
327
328 document := substrb(l_order_type_txt || ', ' || l_line_txt, 1, 240);
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
333 THEN
334 OE_MSG_PUB.Add_Exc_Msg
335 (G_PKG_NAME
336 , 'Set_Line_Descriptor'
337 );
338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
339 END IF;
340
341 END Set_Line_Descriptor;
342
343
344 PROCEDURE Set_Header_User_Key(p_header_rec IN OE_Order_PUB.Header_Rec_Type)
345 IS
346 sales_order VARCHAR2(240);
347 --
348 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
349 --
350 BEGIN
351 if p_header_rec.order_category_code = 'RETURN' then
352 fnd_message.set_name('ONT', 'OE_WF_RETURN_ORDER');
353 else
354 fnd_message.set_name('ONT', 'OE_WF_SALES_ORDER');
355 end if;
356
357 fnd_message.set_token('ORDER_NUMBER', to_char(p_header_rec.order_number));
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
362 THEN
363 OE_MSG_PUB.Add_Exc_Msg
364 ( G_PKG_NAME
365 , 'Set_Header_User_Key'
366 );
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
368 END IF;
369 END Set_Header_User_Key;
370
371 PROCEDURE Set_Line_User_Key(p_line_rec IN OE_Order_PUB.Line_Rec_Type)
372 IS
373 l_header_id NUMBER;
374 --
375 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
376 --
377 BEGIN
378
379 l_header_id := p_line_rec.header_id;
380
381 -- Performance Bug 1929163:
382 -- Use cached header rec to get header values instead
383 -- of a query to select the values.
384
385 OE_Order_Cache.Load_Order_Header(l_header_id);
386
387 if OE_Order_Cache.g_header_rec.order_category_code = 'RETURN' THEN
388 fnd_message.set_name('ONT', 'OE_WF_RETURN_LINE');
389 else
390 fnd_message.set_name('ONT', 'OE_WF_LINE');
391 end if;
392
393 fnd_message.set_token('ORDER_NUMBER',
394 to_char(OE_Order_Cache.g_header_rec.order_number));
395 fnd_message.set_token('LINE_NUMBER', to_char(p_line_rec.line_number));
396 fnd_message.set_token('SHIPMENT_NUMBER', to_char(p_line_rec.shipment_number));
397 fnd_message.set_token('OPTION_NUMBER', to_char(p_line_rec.option_number));
398 fnd_message.set_token('SERVICE_NUMBER', to_char(p_line_rec.service_number));
399
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
404 THEN
405 OE_MSG_PUB.Add_Exc_Msg
406 ( G_PKG_NAME
407 , 'Set_Line_User_Key'
408 );
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 END IF;
411 END Set_Line_User_Key;
412
413
414
415 -- This procedure starts the flow, by calling WF_ENGINE utilities.
416 --
417 PROCEDURE Start_Flow
418 ( p_itemtype in varchar2
419 , p_itemkey in varchar2
420 )
421 IS
422 --
423 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
424 --
425 BEGIN
426
427 IF l_debug_level > 0 THEN
428 oe_debug_pub.add( 'IN START_FLOW' ) ;
429 END IF;
430
431 WF_ENGINE.StartProcess(p_itemtype, p_itemkey);
432
433 IF l_debug_level > 0 THEN
434 oe_debug_pub.add( 'EXITING START_FLOW' ) ;
435 END IF;
436 EXCEPTION
437 WHEN OTHERS THEN
438 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
439 THEN
440 OE_MSG_PUB.Add_Exc_Msg
441 ( G_PKG_NAME
442 , 'Start_Flow'
443 );
444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
445 END IF;
446
447 END Start_flow;
448
449 PROCEDURE Start_LineFork
450 (p_itemkey IN Varchar2
451 )
452 IS
453 --
454 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
455 --
456 BEGIN
457
458 IF l_debug_level > 0 THEN
459 oe_debug_pub.add( 'IN START_LINEFORK' ) ;
460 END IF;
461
462 WF_ENGINE.StartForkProcess('OEOL', p_itemkey);
463
464 -- Refresh old notifications here if needed.
465 IF l_debug_level > 0 THEN
466 oe_debug_pub.add( 'EXITING START_LINEFORK' ) ;
467 END IF;
468
469
470 EXCEPTION
471
472
473 WHEN OTHERS THEN
474 IF wf_core.error_name = 'WFENG_NOFORK_ONERROR' THEN
475 FND_MESSAGE.SET_NAME('ONT','OE_WF_SPLIT_FORK_ERR');
476 OE_MSG_PUB.ADD;
477 END IF;
478 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
479 THEN
480 OE_MSG_PUB.Add_Exc_Msg
481 ( G_PKG_NAME
482 , 'Start_LineFork'
483 );
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485 END IF;
486
487 END Start_LineFork;
488
489 -- This process is called from OE_ORDER_PVT.HEader to create the
490 -- Header workitem and start the flow.
491
492 PROCEDURE CreateStart_HdrProcess
493 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
494 )
495 IS
496 --
497 l_count NUMBER;
498 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
499 --
500 BEGIN
501
502 IF l_debug_level > 0 THEN
503 oe_debug_pub.add( 'IN CREATESTART_HDRPROCESS' ) ;
504 END IF;
505
506 Create_HdrWorkItem(p_header_rec);
507
508 -- We do not start the flow when the record is written, rather
509 -- when the transaction is committed. Here we will set the
510 -- Global.
511 OE_GLOBALS.G_START_HEADER_FLOW := p_header_rec.header_id;
512
513 -- For OENH
514 -- Check if a OENH flow exists, if so set the parent
515 SELECT count(1)
516 INTO l_count
517 FROM wf_items
518 WHERE item_type=OE_GLOBALS.G_WFI_NGO
519 AND item_key =to_char(p_header_rec.header_id);
520
521 IF l_count > 0 THEN
522 WF_ITEM.Set_Item_Parent(OE_GLOBALS.G_WFI_HDR,
523 to_char(p_header_rec.header_id),
524 OE_GLOBALS.G_WFI_NGO,
525 to_char(p_header_rec.header_id), '');
526 END IF;
527
528
529
530 /* Start_Flow(OE_GLOBALS.G_WFI_HDR, p_header_rec.header_id); */
531
532 IF l_debug_level > 0 THEN
533 oe_debug_pub.add( 'EXITING CREATESTART_HDRPROCESS' ) ;
534 END IF;
535
536 EXCEPTION
537 WHEN FND_API.G_EXC_ERROR THEN -- 2590433
538 RAISE;
539 WHEN OTHERS THEN
540 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
541 THEN
542 OE_MSG_PUB.Add_Exc_Msg
543 ( G_PKG_NAME
544 , 'Start_HdrProcess'
545 );
546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
547 END IF;
548
549 END CreateStart_HdrProcess;
550
551 -- This procedure creates the Header WorkItem
552 --
553 PROCEDURE Create_HdrWorkItem
554 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
555 )
556 IS
557 l_hdr_process_name VARCHAR2(30);
558 l_aname wf_engine.nametabtyp;
559 l_aname2 wf_engine.nametabtyp;
560 l_avalue wf_engine.numtabtyp;
561 l_avaluetext wf_engine.texttabtyp;
562
563 sales_order VARCHAR2(240);
564 l_user_name VARCHAR2(100);
565 l_validate_user NUMBER;
566 l_owner_role VARCHAR2(100);
567
568 --
569 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
570 --
571 BEGIN
572
573 IF l_debug_level > 0 THEN
574 oe_debug_pub.add( 'IN CREATE_HDRWORKITEM' ) ;
575 END IF;
576
577
578 -- Get name of Header Process
579 IF l_debug_level > 0 THEN
580 oe_debug_pub.add( 'GET HEADER PROCESS NAME' ) ;
581 END IF;
582 l_hdr_process_name := Get_ProcessName(p_itemtype => OE_GLOBALS.G_WFI_HDR, p_itemkey => p_header_rec.header_id);
583
584 Set_Header_User_Key(p_header_rec);
585 sales_order := substrb(fnd_message.get, 1, 240);
586
587 SELECT user_name
588 INTO l_owner_role
589 FROM FND_USER
590 WHERE USER_ID = FND_GLOBAL.USER_ID;
591 -- Create Header Work item
592 WF_ENGINE.CreateProcess(OE_Globals.G_WFI_HDR,to_char(p_header_rec.header_id),
593 l_hdr_process_name,
594 sales_order,
595 l_owner_role);
596
597 WF_ENGINE.additemattr(OE_Globals.G_WFI_HDR,to_char(p_header_rec.header_id),
598 '#WAITFORDETAIL',null,0);
599
600 IF l_debug_level > 0 THEN
601 oe_debug_pub.add( 'AFTER WF_ENGINE.CREATEPROCESS' ) ;
602 END IF;
603
604 -- Set various Header Attributes
605 l_aname(1) := 'USER_ID';
606 l_avalue(1) := FND_GLOBAL.USER_ID;
607 l_aname(2) := 'APPLICATION_ID';
608 l_avalue(2) := FND_GLOBAL.RESP_APPL_ID;
609 l_aname(3) := 'RESPONSIBILITY_ID';
610 l_avalue(3) := FND_GLOBAL.RESP_ID;
611 l_aname(4) := 'ORG_ID';
612 l_avalue(4) := to_number(OE_GLOBALS.G_ORG_ID);
613 l_aname(5) := 'ORDER_NUMBER';
614 l_avalue(5) := p_header_rec.order_number;
615
616 wf_engine.SetItemAttrNumberArray( OE_GLOBALS.G_WFI_HDR
617 , p_header_rec.header_id
618 , l_aname
619 , l_avalue
620 );
621
622 /* new logic to get FROM_ROLE */
623 BEGIN
624 select user_name
625 into l_user_name
626 from fnd_user
627 where user_id = FND_GLOBAL.USER_ID;
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 l_user_name := null; -- do not set FROM_ROLE then
632 END;
633
634
635 l_aname2(1) := 'ORDER_CATEGORY';
636 l_avaluetext(1) := p_header_rec.order_category_code;
637 l_aname2(2) := 'NOTIFICATION_APPROVER';
638 l_avaluetext(2) := FND_PROFILE.VALUE('OE_NOTIFICATION_APPROVER');
639 l_aname2(3) := 'NOTIFICATION_FROM_ROLE';
640 l_avaluetext(3) := l_user_name;
641 l_aname2(4) := 'ORDER_DETAILS_URL';
642 l_avaluetext(4) := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/')||
643 '/OA_HTML/OA.jsp?akRegionCode=ORDER_DETAILS_PAGE' || '&' ||
644 'akRegionApplicationId=660' || '&' || 'HeaderId=' || to_char(p_header_rec.header_id);
645
646
647 wf_engine.SetItemAttrTextArray( OE_GLOBALS.G_WFI_HDR
648 , p_header_rec.header_id
649 , l_aname2
650 , l_avaluetext
651 );
652
653 IF l_debug_level > 0 THEN
654 oe_debug_pub.add( 'EXITING CREATE_HDRWORKITEM' ) ;
655 END IF;
656
657 EXCEPTION
658 WHEN FND_API.G_EXC_ERROR THEN -- 2590433
659 RAISE;
660 WHEN OTHERS THEN
661 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
662 THEN
663 OE_MSG_PUB.Add_Exc_Msg
664 ( G_PKG_NAME
665 , 'Create_HdrWorkItem'
666 );
667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
668 END IF;
669
670 END Create_HdrWorkItem;
671
672 -- THis is called from OE_ORDER_PVT.LINES to create the Line
673 -- workitem and start the line flow.
674
675 PROCEDURE CreateStart_LineProcess
676 ( p_Line_rec IN OE_Order_PUB.Line_Rec_Type
677 )
678 IS
679 l_item_type varchar2(30);
680 l_index number;
681 --
682 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
683 --
684 BEGIN
685 -- If this is a Split Insert then we need to fork the flow
686 IF p_line_rec.split_from_line_id IS NOT NULL THEN
687 IF l_debug_level > 0 THEN
688 oe_debug_pub.add( 'SPLIT FROM LINE ID IS NOT NULL' ) ;
689 END IF;
690 CreateStart_LineFork(p_Line_rec);
691 ELSE -- Regular Flow creation
692 -- Get Wf itme type
693 l_item_type := OE_Order_Wf_Util.get_wf_item_type(p_line_rec);
694 IF l_debug_level > 0 THEN
695 oe_debug_pub.add( 'IN CREATESTART_LINEPROCESS' ) ;
696 END IF;
697 Create_LineWorkItem(p_Line_rec, l_item_type);
698
699 -- We do not start the flow when the record is written, rather
700 -- when the transaction is committed.
701 -- Start_Flow(OE_GLOBALS.G_WFI_LIN, p_Line_rec.Line_id);
702 -- Add line Id to global table for later processing.
703
704 -- Bug 3000619, references to G_START_LINE_FLOWS_TBL are changed.
705 IF (OE_GLOBALS.G_START_LINE_FLOWS_TBL.COUNT = 0) THEN
706 IF l_debug_level > 0 THEN
707 oe_debug_pub.add( 'G_START_LINE_FLOWS_TBL.COUNT = 0'|| p_Line_rec.ato_Line_id || '-'|| p_Line_rec.Line_id , 2) ;
708 END IF;
709 OE_GLOBALS.G_START_LINE_FLOWS_TBL(1).line_id := p_Line_rec.Line_id;
710 OE_GLOBALS.G_START_LINE_FLOWS_TBL(1).post_write_ato_line_id := p_Line_rec.ato_Line_id;
711 ELSE -- the table has entries
712 l_index := OE_GLOBALS.G_START_LINE_FLOWS_TBL.LAST;
713 IF l_debug_level > 0 THEN
714 oe_debug_pub.add( 'G_START_LINE_FLOWS_TBL.COUNT = ' || to_char(l_index) || p_Line_rec.ato_Line_id || '-'|| p_Line_rec.Line_id, 3 ) ;
715 END IF;
716 OE_GLOBALS.G_START_LINE_FLOWS_TBL(l_index + 1).line_id := p_Line_rec.Line_id;
717 OE_GLOBALS.G_START_LINE_FLOWS_TBL(l_index + 1).post_write_ato_line_id := p_Line_rec.ato_Line_id;
718 END IF;
719
720 IF l_debug_level > 0 THEN
721 oe_debug_pub.add( 'EXITING CREATESTART_LINEPROCESS' ) ;
722 END IF;
723 END IF;
724
725 EXCEPTION
726 WHEN FND_API.G_EXC_ERROR THEN -- 2590433
727 RAISE;
728 WHEN OTHERS THEN
729 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
730 THEN
731 OE_MSG_PUB.Add_Exc_Msg
732 ( G_PKG_NAME
733 , 'CreateStart_LineProcess'
734 );
735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
736 END IF;
737
738 END CreateStart_LineProcess;
739
740 PROCEDURE CreateStart_LineFork
741 ( p_Line_rec IN OE_Order_PUB.Line_Rec_Type
742 )
743 IS
744 --
745 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
746 --
747 BEGIN
748
749 IF l_debug_level > 0 THEN
750 oe_debug_pub.add( 'IN CREATESTART_LINEFORK' ) ;
751 END IF;
752
753 Create_LineFork(p_Line_rec);
754
755 Start_LineFork(p_line_rec.line_id);
756
757 IF l_debug_level > 0 THEN
758 oe_debug_pub.add( 'EXITING CREATESTART_LINEFORK' ) ;
759 END IF;
760
761
762 EXCEPTION
763 WHEN OTHERS 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
767 ( G_PKG_NAME
768 , 'CreateStart_LineFork'
769 );
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 END IF;
772
773 END CreateStart_LineFork;
774
775
776
777
778 -- This procedure creates the Line WorkItem.
779 --
780 PROCEDURE Create_LineWorkItem
781 ( p_Line_rec IN OE_Order_PUB.Line_Rec_Type,
782 p_item_type IN VARCHAR2
783 )
784 IS
785 l_line_process_name VARCHAR2(30);
786 l_item_type varchar2(30);
787 l_order_number NUMBER;
788 l_aname wf_engine.nametabtyp;
789 l_aname2 wf_engine.nametabtyp;
790 l_avalue wf_engine.numtabtyp;
791 l_avaluetext wf_engine.texttabtyp;
792
793 line VARCHAR2(240);
794 l_owner_role VARCHAR2(100);
795 --
796 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
797 --
798 BEGIN
799
800 IF l_debug_level > 0 THEN
801 oe_debug_pub.add( 'IN CREATE_LINEWORKITEM' ) ;
802 END IF;
803 l_item_type := p_item_type;
804
805 -- Get name of Line Process
806 IF l_debug_level > 0 THEN
807 oe_debug_pub.add( 'GET LINE PROCESSNAME' ) ;
808 END IF;
809 l_line_process_name := Get_ProcessName(p_itemtype => OE_GLOBALS.G_WFI_LIN, p_itemkey => p_Line_rec.line_id, p_wfasgn_item_type => l_item_type, p_line_rec => p_line_rec);
810
811 Set_Line_User_Key(p_line_rec);
812 line := substrb(fnd_message.get, 1, 240);
813
814 SELECT user_name
815 INTO l_owner_role
816 FROM FND_USER
817 WHERE USER_ID = FND_GLOBAL.USER_ID;
818
819 -- Create Line Work item
820 WF_ENGINE.CreateProcess(OE_Globals.G_WFI_LIN,to_char(p_Line_rec.line_id),
821 l_line_process_name,
822 line,
823 l_owner_role);
824
825
826 IF l_debug_level > 0 THEN
827 oe_debug_pub.add( 'AFTER WF_ENGINE.CREATEPROCESS' ) ;
828 END IF;
829
830
831 -- Set various Line Attributes
832 l_aname(1) := 'USER_ID';
833 l_avalue(1) := FND_GLOBAL.USER_ID;
834 l_aname(2) := 'APPLICATION_ID';
835 l_avalue(2) := FND_GLOBAL.RESP_APPL_ID;
836 l_aname(3) := 'RESPONSIBILITY_ID';
837 l_avalue(3) := FND_GLOBAL.RESP_ID;
838 l_aname(4) := 'ORG_ID';
839 l_avalue(4) := to_number(OE_GLOBALS.G_ORG_ID);
840
841 wf_engine.SetItemAttrNumberArray( OE_GLOBALS.G_WFI_LIN
842 , p_line_rec.line_id
843 , l_aname
844 , l_avalue
845 );
846
847 l_aname2(1) := 'LINE_CATEGORY';
848 l_avaluetext(1) := p_line_rec.line_category_code;
849 l_aname2(2) := 'NOTIFICATION_APPROVER';
850 l_avaluetext(2) := FND_PROFILE.VALUE('OE_NOTIFICATION_APPROVER');
851
852 wf_engine.SetItemAttrTextArray( OE_GLOBALS.G_WFI_LIN
853 , p_line_rec.line_id
854 , l_aname2
855 , l_avaluetext
856 );
857
858 WF_ITEM.Set_Item_Parent(OE_Globals.G_WFI_LIN,
859 to_char(p_Line_rec.line_id),
860 OE_GLOBALS.G_WFI_HDR,
861 to_char(p_Line_rec.header_id), '',
862 true);
863
864 IF l_debug_level > 0 THEN
865 oe_debug_pub.add( 'EXITING CREATE_LINEWORKITEM' ) ;
866 END IF;
867
868 EXCEPTION
869 WHEN FND_API.G_EXC_ERROR THEN -- 2590433
870 RAISE;
871 WHEN OTHERS THEN
872 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
873 THEN
874 OE_MSG_PUB.Add_Exc_Msg
875 ( G_PKG_NAME
876 , 'Create_LineWorkItem'
877 );
878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
879 END IF;
880
881 END Create_LineWorkItem;
882
883 PROCEDURE Create_LineFork
884 (p_Line_rec IN OE_Order_PUB.Line_Rec_Type
885 )
886
887 IS
888 l_order_number NUMBER;
889 l_header_id NUMBER;
890 line VARCHAR2(240);
891
892 --
893 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
894 --
895 BEGIN
896
897 IF l_debug_level > 0 THEN
898 oe_debug_pub.add( 'IN CREATE_LINEFORK' ) ;
899 END IF;
900
901 Set_Line_User_Key(p_line_rec);
902 line := substrb(fnd_message.get, 1, 240);
903
904 WF_ENGINE.CreateForkProcess('OEOL', p_line_rec.split_from_line_id,
905 p_line_rec.line_id,
906 true,
907 true );
908
909 wf_engine.SetItemUserKey( OE_GLOBALS.G_WFI_LIN
910 , p_line_rec.line_id
911 , line);
912
913 IF l_debug_level > 0 THEN
914 oe_debug_pub.add( 'EXITING CREATE_LINEFORK' ) ;
915 END IF;
916
917 EXCEPTION
918 WHEN OTHERS THEN
919 IF wf_core.error_name = 'WFENG_NOFORK_ONERROR' THEN
920 FND_MESSAGE.SET_NAME('ONT','OE_WF_SPLIT_FORK_ERR');
921 OE_MSG_PUB.ADD;
922 END IF;
923
924 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
925 THEN
926 OE_MSG_PUB.Add_Exc_Msg
927 ( G_PKG_NAME
928 , 'Create_LineFork'
929 );
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
931 END IF;
932
933 END Create_LineFork;
934
935 FUNCTION Get_Wf_Item_type
936 ( p_Line_rec IN OE_Order_PUB.Line_Rec_Type
937 ) RETURN VARCHAR2
938 IS
939 l_item_rec OE_ORDER_CACHE.item_rec_type;
940 --
941 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
942 --
943 BEGIN
944 l_item_rec :=
945 OE_Order_Cache.Load_Item (p_line_rec.inventory_item_id
946 ,p_line_rec.ship_from_org_id);
947
948 IF l_debug_level > 0 THEN
949 oe_debug_pub.add( 'ITEM_TYPE_CODE IS ' || P_LINE_REC.ITEM_TYPE_CODE ) ;
950 END IF;
951
952 -- Code for Returns
953 IF p_line_rec.line_category_code = 'RETURN' THEN
954 RETURN 'STANDARD';
955 END IF;
956
957 IF OE_OTA_UTIL.Is_OTA_Line(p_line_rec.order_quantity_uom) THEN
958
959 RETURN 'EDUCATION_ITEM';
960
961 --ELSIF ( p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD AND
962 -- l_item_rec.replenish_to_order_flag = 'Y') OR
963
964 -- ## 1820608 ato item under a top pto model should start ato_item flow.
965
966 ELSIF ( p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD OR
967 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION)
968 AND p_line_rec.ato_line_id = p_line_rec.line_id THEN
969 RETURN 'ATO_ITEM';
970
971 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL AND
972 p_line_rec.line_id = p_line_rec.ato_line_id) THEN
973
974 RETURN 'ATO_MODEL';
975 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG) THEN
976
977 RETURN 'CONFIGURATION';
978
979 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED) THEN
980
981 RETURN 'II';
982 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT) THEN
983
984 RETURN 'KIT';
985 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL AND
986 p_line_rec.line_id = p_line_rec.top_model_line_id AND
987 p_line_rec.ato_line_id IS NULL) THEN
988
989 RETURN 'PTO_MODEL';
990 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS AND
991 p_line_rec.ato_line_id IS NULL) THEN
992
993 RETURN 'PTO_CLASS';
994 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION AND
995 p_line_rec.ato_line_id IS NULL) THEN
996
997 RETURN 'PTO_OPTION';
998
999
1000 -- for ato under pto, we want to start ato model flow
1001 -- even if the item_type_code is class. For ato under ato
1002 -- start standard flow.
1003
1004 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS AND
1005 p_line_rec.ato_line_id IS NOT NULL) THEN
1006
1007 IF p_line_rec.ato_line_id = p_line_rec.line_id
1008 THEN
1009 RETURN 'ATO_MODEL';
1010 ELSE
1011 RETURN 'ATO_CLASS'; --FP bug no 4572207
1012 END IF;
1013
1014 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION AND
1015 p_line_rec.ato_line_id IS NOT NULL) THEN
1016
1017 RETURN 'ATO_OPTION'; --FB bug no 4572207
1018 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD) THEN
1019
1020 RETURN 'STANDARD';
1021 ELSIF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE) THEN
1022
1023 RETURN 'SERVICE';
1024
1025 ELSE
1026
1027 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_WF_ITEM_TYPE');
1028 OE_MSG_PUB.ADD;
1029 RAISE FND_API.G_EXC_ERROR;
1030 END IF;
1031
1032 EXCEPTION
1033
1034 WHEN OTHERS THEN
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
1038 ( G_PKG_NAME ,
1039 'Get_Wf_Item_Type'
1040 );
1041 END IF;
1042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043
1044 END Get_Wf_Item_type;
1045
1046
1047 -- This procedure starts flows for all the Header and Line records created in a Process Order
1048 -- Transaction. The WF item has been created when the record is written to the db - post_write
1049 -- processing.
1050 PROCEDURE Start_All_Flows
1051 IS
1052
1053 ctr NUMBER;
1054 l_type_id NUMBER;
1055 l_item_type_code VARCHAR2(30);
1056 l_ato_line_id NUMBER;
1057 l_line_id NUMBER;
1058 l_item_type varchar2(30);
1059 l_line_rec oe_order_pub.line_rec_type;
1060 l_header_id NUMBER;
1061 --
1062 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1063 --
1064 BEGIN
1065
1066 IF l_debug_level > 0 THEN
1067 oe_debug_pub.add( 'ENTERING START_ALL_FLOWS' ) ;
1068 END IF;
1069
1070 -- This code should be invoked again, once it starts executing
1071 -- Starting flows can cause the creation of new lines, but we do not
1072 -- want the creation call to get in here again.
1073 IF (NOT OE_GLOBALS.G_FLOW_PROCESSING_STARTED) THEN
1074 OE_GLOBALS.G_FLOW_PROCESSING_STARTED := TRUE;
1075 IF l_debug_level > 0 THEN
1076 oe_debug_pub.add( 'FLOW PROCESSING STARTED' ) ;
1077 END IF;
1078
1079 -- Issue Savepoint
1080 SAVEPOINT Start_All_Flows;
1081
1082 -- Check if Header flow needs to be started
1083 IF (OE_GLOBALS.G_START_HEADER_FLOW IS NOT NULL) THEN
1084
1085 BEGIN
1086 -- Sanity Check to verify that header exists
1087 -- The create of the header could have been rolled back
1088 -- but the PL/SQL global is not in synch.
1089 Select order_type_id
1090 into l_type_id
1091 from oe_order_headers_all
1092 where header_id = OE_GLOBALS.G_START_HEADER_FLOW;
1093
1094 IF l_debug_level > 0 THEN
1095 oe_debug_pub.add( 'STARTING HEADER FLOW' ) ;
1096 END IF;
1097
1098 -- Start flow and clear global
1099 Start_Flow(OE_GLOBALS.G_WFI_HDR, OE_GLOBALS.G_START_HEADER_FLOW);
1100 OE_GLOBALS.G_START_HEADER_FLOW := NULL;
1101
1102 EXCEPTION
1103 WHEN NO_DATA_FOUND THEN
1104 IF l_debug_level > 0 THEN
1105 oe_debug_pub.add( 'HEADER '||OE_GLOBALS.G_START_HEADER_FLOW||' IS MISSING , BUT ID EXISTS IN GLOBAL TABLE' ) ;
1106 END IF;
1107 OE_GLOBALS.G_START_HEADER_FLOW := NULL;
1108 END;
1109
1110
1111 END IF;
1112
1113 -- Check if Line Flows need to be started
1114 IF (OE_GLOBALS.G_START_LINE_FLOWS_TBL.COUNT > 0) THEN
1115
1116 -- This global table can grow while it is being processed, hence we can cannot
1117 -- loop thru using first ... count. Starting a line flow can create included item
1118 -- lines (booking, scheduling activity).
1119
1120 IF l_debug_level > 0 THEN
1121 oe_debug_pub.add( 'START LINE FLOWS' ) ;
1122 END IF;
1123
1124 ctr := OE_GLOBALS.G_START_LINE_FLOWS_TBL.FIRST;
1125
1126 WHILE (ctr is NOT NULL) LOOP
1127
1128 IF l_debug_level > 0 THEN
1129 oe_debug_pub.add( 'INSIDE LOOP '||'LINE ID IS '||OE_GLOBALS.G_START_LINE_FLOWS_TBL ( CTR ).line_id ) ;
1130 END IF;
1131
1132 BEGIN
1133
1134 -- Sanity Check to verify that line exists
1135 -- The create of the line could have been rolled back
1136 -- but the PL/SQL table is not in synch.
1137
1138 -- Bug 3000619
1139
1140 Select line_type_id, item_type_code,
1141 ato_line_id, line_id
1142 into l_type_id, l_item_type_code,
1143 l_ato_line_id, l_line_id
1144 from oe_order_lines_all
1145 where line_id = OE_GLOBALS.G_START_LINE_FLOWS_TBL(ctr).line_id;
1146
1147 IF l_debug_level > 0 THEN
1148 oe_debug_pub.add( 'STARTING LINE FLOW '||'LINE ID = ' ||OE_GLOBALS.G_START_LINE_FLOWS_TBL ( CTR ).line_id ) ;
1149 END IF;
1150
1151 IF l_item_type_code = 'CLASS' AND
1152 l_ato_line_id is not null AND
1153 l_ato_line_id <> l_line_id THEN
1154 IF l_debug_level > 0 THEN
1155 oe_debug_pub.add('need to check wf assignment for ato under ato under pto, 3');
1156 END IF;
1157
1158 IF OE_GLOBALS.G_START_LINE_FLOWS_TBL(CTR).post_write_ato_line_id
1159 <> l_ato_line_id THEN
1160
1161 WF_ENGINE.AbortProcess
1162 (itemtype => OE_GLOBALS.G_WFI_LIN,
1163 itemkey => OE_GLOBALS.G_START_LINE_FLOWS_TBL(CTR).line_id);
1164
1165 WF_PURGE.Items
1166 (itemtype => OE_GLOBALS.G_WFI_LIN,
1167 itemkey => OE_GLOBALS.G_START_LINE_FLOWS_TBL(CTR).line_id,
1168 force => TRUE,
1169 docommit => false);
1170
1171 OE_Line_Util.Query_Row
1172 ( p_line_id => OE_GLOBALS.G_START_LINE_FLOWS_TBL(CTR).line_id
1173 ,x_line_rec => l_line_rec);
1174
1175 l_item_type := OE_Order_Wf_Util.get_wf_item_type(l_line_rec);
1176
1177 Create_LineWorkItem(l_line_rec, l_item_type);
1178
1179 END IF; -- if post_write_ato_line_id <> l_ato_line_id
1180 END IF; -- item_type = CLASS
1181
1182 Start_Flow(OE_GLOBALS.G_WFI_LIN, OE_GLOBALS.G_START_LINE_FLOWS_TBL(ctr).line_id); -- End bug 3000619
1183
1184 EXCEPTION
1185 WHEN NO_DATA_FOUND THEN
1186 IF l_debug_level > 0 THEN
1187 oe_debug_pub.add( 'LINE '||OE_GLOBALS.G_START_LINE_FLOWS_TBL ( CTR ).line_id ||' IS MISSING , BUT ID EXISTS IN GLOBAL TABLE' ) ;
1188 END IF;
1189 END;
1190
1191
1192 ctr := OE_GLOBALS.G_START_LINE_FLOWS_TBL.NEXT(ctr);
1193
1194 END LOOP;
1195
1196 -- Clear the Global table.
1197 OE_GLOBALS.G_START_LINE_FLOWS_TBL.DELETE;
1198
1199 END IF;
1200
1201 -- Start OENH/OEBH handling
1202 IF (OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW IS NOT NULL) THEN
1203 BEGIN
1204 -- Sanity Check to verify that negotiate header exists
1205 -- The create of the header could have been rolled back
1206 -- but the PL/SQL global is not in synch.
1207 IF OE_GLOBALS.G_SALES_DOCUMENT_TYPE_CODE = 'O' THEN
1208 Select header_id
1209 into l_header_id
1210 from oe_order_headers_all
1211 where header_id = OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW;
1212 ELSIF OE_GLOBALS.G_SALES_DOCUMENT_TYPE_CODE = 'B' THEN
1213 Select header_id
1214 into l_header_id
1215 from oe_blanket_headers_all
1216 where header_id = OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW;
1217 END IF;
1218 IF l_debug_level > 0 THEN
1219 oe_debug_pub.add( 'STARTING NEGOTIATE HEADER FLOW' ) ;
1220 END IF;
1221
1222 -- Start flow and clear global
1223 Start_Flow(OE_GLOBALS.G_WFI_NGO, OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW);
1224 OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW := NULL;
1225
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND THEN
1228 IF l_debug_level > 0 THEN
1229 oe_debug_pub.add( 'Negotiate Header (OENH): '||OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW||' IS MISSING , BUT ID EXISTS IN GLOBAL' ) ;
1230 END IF;
1231 OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW:= NULL;
1232 END;
1233 END IF;
1234
1235 IF (OE_GLOBALS.G_START_BLANKET_HEADER_FLOW IS NOT NULL) THEN
1236 BEGIN
1237 -- Sanity Check to verify that negotiate header exists
1238 -- The create of the header could have been rolled back
1239 -- but the PL/SQL global is not in synch.
1240 Select header_id
1241 into l_header_id
1242 from oe_blanket_headers_all
1243 where header_id = OE_GLOBALS.G_START_BLANKET_HEADER_FLOW;
1244
1245 IF l_debug_level > 0 THEN
1246 oe_debug_pub.add( 'STARTING BLANKET HEADER FLOW' ) ;
1247 END IF;
1248
1249 -- Start flow and clear global
1250 Start_Flow(OE_GLOBALS.G_WFI_BKT, OE_GLOBALS.G_START_BLANKET_HEADER_FLOW);
1251 OE_GLOBALS.G_START_BLANKET_HEADER_FLOW := NULL;
1252
1253 EXCEPTION
1254 WHEN NO_DATA_FOUND THEN
1255 IF l_debug_level > 0 THEN
1256 oe_debug_pub.add( 'Blanket Header (OEBH): '||OE_GLOBALS.G_START_BLANKET_HEADER_FLOW||' IS MISSING , BUT ID EXISTS IN GLOBAL' ) ;
1257 END IF;
1258 OE_GLOBALS.G_START_BLANKET_HEADER_FLOW := NULL;
1259 END;
1260 END IF;
1261
1262 -- End of OENH/OEBH handling
1263
1264
1265 -- Reset global value
1266 OE_GLOBALS.G_FLOW_PROCESSING_STARTED := FALSE;
1267 END IF;
1268
1269 IF l_debug_level > 0 THEN
1270 oe_debug_pub.add( 'EXITING START_ALL_FLOWS' ) ;
1271 END IF;
1272
1273 EXCEPTION
1274 WHEN OTHERS THEN
1275 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1276 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Start_All_Flows');
1277
1278 -- Rollback to savepoint
1279 ROLLBACK TO Start_All_Flows;
1280 -- Clear Globals
1281 Clear_FlowStart_Globals;
1282
1283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1284 END IF;
1285
1286 END start_all_flows;
1287
1288 Procedure Clear_FlowStart_Globals
1289 IS
1290 --
1291 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1292 --
1293 BEGIN
1294
1295 IF l_debug_level > 0 THEN
1296 oe_debug_pub.add( 'IN CLEAR_FLOWSTART_GLOBALS' ) ;
1297 END IF;
1298
1299 OE_GLOBALS.G_START_HEADER_FLOW := NULL;
1300 OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW:= NULL;
1301 OE_GLOBALS.G_START_BLANKET_HEADER_FLOW := NULL;
1302 OE_GLOBALS.G_START_LINE_FLOWS_TBL.DELETE;
1303 OE_GLOBALS.G_FLOW_PROCESSING_STARTED := FALSE;
1304
1305 END Clear_FlowStart_Globals;
1306
1307
1308 PROCEDURE Delete_Row
1309 ( p_type IN VARCHAR2,
1310 p_id IN NUMBER
1311
1312 ) IS
1313
1314 l_status VARCHAR2(30);
1315 l_result VARCHAR2(240);
1316 l_count NUMBER;
1317 l_transaction_phase_code VARCHAR2(30);
1318 --
1319 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1320 --
1321 BEGIN
1322 IF l_debug_level > 0 THEN
1323 oe_debug_pub.add( 'IN OE_ORDER_WF_UTIL.DELETE_ROW' ) ;
1324 END IF;
1325
1326 IF p_type = 'HEADER' THEN
1327 WF_ENGINE.ItemStatus(itemtype => OE_GLOBALS.G_WFI_HDR,
1328 itemkey => p_id,
1329 status => l_status,
1330 result => l_result);
1331 IF l_status <> 'COMPLETE' THEN
1332 WF_ENGINE.AbortProcess(itemtype => OE_GLOBALS.G_WFI_HDR,
1333 itemkey => p_id);
1334 END IF;
1335 WF_PURGE.Items(itemtype => OE_GLOBALS.G_WFI_HDR,
1336 itemkey => p_id,
1337 force => TRUE,
1338 docommit => false);
1339
1340 select count(1)
1341 into l_count
1342 from wf_items
1343 where item_type=OE_GLOBALS.G_WFI_NGO
1344 and item_key=to_char(p_id);
1345
1346 IF l_count > 0 THEN
1347 WF_PURGE.Items(itemtype => OE_GLOBALS.G_WFI_NGO,
1348 itemkey => p_id,
1349 force => TRUE,
1350 docommit => false);
1351 END IF;
1352
1353 ELSIF p_type = 'LINE' THEN
1354 SELECT transaction_phase_code
1355 INTO l_transaction_phase_code
1356 FROM oe_order_lines_all
1357 WHERE line_id = p_id;
1358
1359 IF nvl(l_transaction_phase_code, 'F') <> 'N' THEN
1360
1361 WF_ENGINE.ItemStatus(itemtype => OE_GLOBALS.G_WFI_LIN,
1362 itemkey => p_id,
1363 status => l_status,
1364 result => l_result);
1365 --Added the nvl condition to fix bug 2333095
1366 IF nvl(l_status,'ACTIVE') <> 'COMPLETE' THEN
1367 WF_ENGINE.AbortProcess(itemtype => OE_GLOBALS.G_WFI_LIN,
1368 itemkey => p_id);
1369 END IF;
1370 IF l_debug_level > 0 THEN
1371 oe_debug_pub.add( 'PURGING WF ITEM' ) ;
1372 END IF;
1373 WF_PURGE.Items(itemtype => OE_GLOBALS.G_WFI_LIN,
1374 itemkey => p_id,
1375 force => TRUE,
1376 docommit => false);
1377 END IF;
1378
1379 ELSIF p_type = 'NEGOTIATE' THEN
1380 WF_ENGINE.ItemStatus(itemtype => OE_GLOBALS.G_WFI_NGO,
1381 itemkey => p_id,
1382 status => l_status,
1383 result => l_result);
1384 IF nvl(l_status,'ACTIVE') <> 'COMPLETE' THEN
1385 WF_ENGINE.AbortProcess(itemtype => OE_GLOBALS.G_WFI_NGO,
1386 itemkey => p_id);
1387 END IF;
1388 IF l_debug_level > 0 THEN
1389 oe_debug_pub.add( 'PURGING WF ITEM - Negotiate') ;
1390 END IF;
1391 WF_PURGE.Items(itemtype => OE_GLOBALS.G_WFI_NGO,
1392 itemkey => p_id,
1393 force => TRUE,
1394 docommit => false);
1395 ELSIF p_type = 'BLANKET' THEN
1396 WF_ENGINE.ItemStatus(itemtype => OE_GLOBALS.G_WFI_BKT,
1397 itemkey => p_id,
1398 status => l_status,
1399 result => l_result);
1400 IF nvl(l_status,'ACTIVE') <> 'COMPLETE' THEN
1401 WF_ENGINE.AbortProcess(itemtype => OE_GLOBALS.G_WFI_BKT,
1402 itemkey => p_id);
1403 END IF;
1404 IF l_debug_level > 0 THEN
1405 oe_debug_pub.add( 'PURGING WF ITEM - BLANKET') ;
1406 END IF;
1407 WF_PURGE.Items(itemtype => OE_GLOBALS.G_WFI_BKT,
1408 itemkey => p_id,
1409 force => TRUE,
1410 docommit => false);
1411
1412 select count(1)
1413 into l_count
1414 from wf_items
1415 where item_type=OE_GLOBALS.G_WFI_NGO
1416 and item_key=to_char(p_id);
1417
1418 IF l_count > 0 THEN
1419 WF_PURGE.Items(itemtype => OE_GLOBALS.G_WFI_NGO,
1420 itemkey => p_id,
1421 force => TRUE,
1422 docommit => false);
1423 END IF;
1424
1425 ELSE
1426 IF l_debug_level > 0 THEN
1427 oe_debug_pub.add( 'OE_ORDER_WF_UTIL: DELETE TYPE NOT IN HEADER,LINE,NEGOTIATE,BLANKET' ) ;
1428 END IF;
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
1432 ( G_PKG_NAME
1433 , 'Delete_Row'
1434 );
1435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1436 END IF;
1437 END IF;
1438 IF l_debug_level > 0 THEN
1439 oe_debug_pub.add( 'EXITING OE_ORDER_WF_UTIL.DELETE_ROW' ) ;
1440 END IF;
1441
1442 END Delete_Row;
1443
1444 PROCEDURE Update_Flow_Status_Code
1445 (
1446 p_header_id IN NUMBER DEFAULT NULL,
1447 p_line_id IN NUMBER DEFAULT NULL,
1448 p_flow_status_code IN VARCHAR2,
1449 p_item_type IN VARCHAR2 DEFAULT NULL,
1450 p_sales_document_type_code IN VARCHAR2 DEFAULT NULL,
1451 x_return_status OUT NOCOPY VARCHAR2
1452 )
1453 IS
1454 l_flow_status_code VARCHAR2(30);
1455 l_header_rec OE_Order_PUB.Header_Rec_Type;
1456 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
1457 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1458 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
1459 l_return_status VARCHAR2(30);
1460 l_source_document_id NUMBER;
1461 l_source_document_line_id NUMBER;
1462 l_header_id NUMBER;
1463 l_orig_sys_document_ref VARCHAR2(50);
1464 l_orig_sys_line_ref VARCHAR2(50);
1465 l_order_source_id NUMBER;
1466 l_orig_sys_shipment_ref VARCHAR2(50);
1467 l_change_sequence VARCHAR2(50);
1468 l_source_document_type_id NUMBER;
1469 l_index NUMBER;
1470 l_blanket_lock_control NUMBER;
1471 l_msg_count NUMBER;
1472 l_msg_data VARCHAR2(2000);
1473 l_itemkey_sso number; -- GENESIS
1474 --
1475 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1476 --
1477 Cursor lines IS
1478 SELECT line_id, lock_control
1479 FROM OE_ORDER_LINES_ALL
1480 WHERE HEADER_ID = p_header_id
1481 FOR UPDATE NOWAIT;
1482
1483 l_line_id NUMBER;
1484 l_lock_control NUMBER;
1485
1486
1487 BEGIN
1488 IF l_debug_level > 0 THEN
1489 oe_debug_pub.add('ENTERING UPDATE_FLOW_STATUS_CODE' , 5 ) ;
1490 oe_debug_pub.add('UFSC: GLOBAL RECURSION WITHOUT EXCEPTION: ' || OE_ORDER_UTIL.G_RECURSION_WITHOUT_EXCEPTION) ;
1491 oe_debug_pub.add('UFSC: GLOBAL CACHE BOOKED FLAG' || OE_ORDER_CACHE.G_HEADER_REC.BOOKED_FLAG ) ;
1492 oe_debug_pub.add('UFSC: GLOBAL PICTURE HEADER BOOKED FLAG' || OE_ORDER_UTIL.G_HEADER_REC.BOOKED_FLAG ) ;
1493 oe_debug_pub.add('UFSC: COUNT OF NEW LINE TABLE= '|| OE_ORDER_UTIL.G_LINE_TBL.COUNT ) ;
1494 oe_debug_pub.add('UFSC: COUNT OF OLD LINE TABLE= '|| OE_ORDER_UTIL.G_OLD_LINE_TBL.COUNT ) ;
1495 oe_debug_pub.add('UFSC: COUNT OF NEW LINE ADJ TABLE= '|| OE_ORDER_UTIL.G_LINE_ADJ_TBL.COUNT ) ;
1496 oe_debug_pub.add('UFSC: COUNT OF OLD LINE ADJ TABLE= '|| OE_ORDER_UTIL.G_OLD_LINE_ADJ_TBL.COUNT ) ;
1497 oe_debug_pub.add('UFSC: COUNT OF NEW HDR ADJ TABLE= '|| OE_ORDER_UTIL.G_HEADER_ADJ_TBL.COUNT ) ;
1498 oe_debug_pub.add('UFSC: COUNT OF OLD HDR ADJ TABLE= '|| OE_ORDER_UTIL.G_OLD_HEADER_ADJ_TBL.COUNT ) ;
1499 oe_debug_pub.add('UFSC: COUNT OF NEW HDR SCREDIT TABLE= '|| OE_ORDER_UTIL.G_HEADER_SCREDIT_TBL.COUNT ) ;
1500 oe_debug_pub.add('UFSC: COUNT OF OLD HDR SCREDIT TABLE= '|| OE_ORDER_UTIL.G_OLD_HEADER_SCREDIT_TBL.COUNT ) ;
1501 oe_debug_pub.add('UFSC: COUNT OF NEW LINE SCREDIT TABLE= '|| OE_ORDER_UTIL.G_LINE_SCREDIT_TBL.COUNT ) ;
1502 oe_debug_pub.add('UFSC: COUNT OF OLD LINE SCREDIT TABLE= '|| OE_ORDER_UTIL.G_OLD_LINE_SCREDIT_TBL.COUNT ) ;
1503 oe_debug_pub.add('UFSC: COUNT OF NEW LOT SERIAL TABLE= '|| OE_ORDER_UTIL.G_LOT_SERIAL_TBL.COUNT ) ;
1504 oe_debug_pub.add('UFSC: COUNT OF OLD LOT SERIAL TABLE= '|| OE_ORDER_UTIL.G_OLD_LOT_SERIAL_TBL.COUNT ) ;
1505 END IF;
1506
1507 SAVEPOINT UPDATE_FLOW_STATUS_CODE;
1508
1509 x_return_status := FND_API.G_RET_STS_SUCCESS;
1510
1511 -- Check if the ASO is installed to call the NOTIFY_OC.
1512 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
1513 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
1514 END IF;
1515
1516 -- *** Negotiation Changes Start ***
1517 IF p_item_type in (OE_GLOBALS.G_WFI_NGO, OE_GLOBALS.G_WFI_BKT) THEN
1518 IF p_header_id IS NOT NULL THEN
1519 -- validate p_flow_status
1520 SELECT lookup_code
1521 INTO l_flow_status_code
1522 FROM oe_lookups
1523 WHERE lookup_type= 'FLOW_STATUS'
1524 AND lookup_code = p_flow_status_code
1525 AND enabled_flag = 'Y'
1526 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1527 AND NVL(END_DATE_ACTIVE, SYSDATE);
1528
1529 IF p_item_type = OE_GLOBALS.G_WFI_NGO THEN
1530
1531 IF p_sales_document_type_code is null THEN
1532 -- for Negotiation, you must pass the document type code
1533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1534 END IF;
1535
1536 IF p_sales_document_type_code = 'O' THEN
1537 IF l_debug_level > 0 THEN
1538 oe_debug_pub.add('Update_Flow_Status_Code for Quote:' || p_flow_status_code, 5 ) ;
1539 END IF;
1540 OE_Header_Util.Lock_Row(p_header_id=>p_header_id
1541 , p_x_header_rec=>l_header_rec
1542 , x_return_status => l_return_status);
1543 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1544 RAISE FND_API.G_EXC_ERROR;
1545 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1547 END IF;
1548
1549 -- is entity HEADER correct?
1550 OE_MSG_PUB.set_msg_context
1551 ( p_entity_code => 'HEADER'
1552 ,p_entity_id => l_header_rec.header_id
1553 ,p_header_id => l_header_rec.header_id
1554 ,p_line_id => null
1555 ,p_order_source_id => l_header_rec.order_source_id
1556 ,p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref
1557 ,p_orig_sys_document_line_ref => null
1558 ,p_change_sequence => l_header_rec.change_sequence
1559 ,p_source_document_type_id => l_header_rec.source_document_type_id
1560 ,p_source_document_id => l_header_rec.source_document_id
1561 ,p_source_document_line_id => null );
1562
1563 l_old_header_rec := l_header_rec;
1564
1565 UPDATE OE_ORDER_HEADERS_ALL
1566 SET FLOW_STATUS_CODE = p_flow_status_code
1567 , LOCK_CONTROL = LOCK_CONTROL + 1
1568 WHERE HEADER_ID = p_header_id;
1569
1570 -- Also update all lines to have the same flow_status_code for quotes
1571 Open Lines;
1572 Loop
1573 FETCH lines into l_line_id, l_lock_control;
1574 EXIT WHEN Lines%NOTFOUND;
1575 End Loop;
1576 Close Lines;
1577
1578 UPDATE OE_ORDER_LINES_ALL
1579 SET FLOW_STATUS_CODE = p_flow_status_code,
1580 LOCK_CONTROL = LOCK_CONTROL + 1
1581 WHERE HEADER_ID = p_header_id;
1582
1583
1584 l_header_rec.flow_status_code := p_flow_status_code;
1585 l_header_rec.lock_control := l_header_rec.lock_control + 1;
1586
1587 -- aksingh performance
1588 -- As the update is on headers table, it is time to update
1589 -- cache also!
1590 OE_Order_Cache.Set_Order_Header(l_header_rec);
1591
1592 -- Bug 1755817: clear the cached constraint results for header entity
1593 -- when order header is updated.
1594 OE_PC_Constraints_Admin_Pvt.Clear_Cached_Results
1595 (p_validation_entity_id => OE_PC_GLOBALS.G_ENTITY_HEADER);
1596
1597 -- added for notification framework
1598 -- calling notification framework to get index position
1599 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
1600 p_old_header_rec => l_old_header_rec,
1601 p_Header_rec => l_header_rec,
1602 p_header_id => p_header_id,
1603 x_index => l_index,
1604 x_return_status => l_return_status);
1605
1606 IF l_debug_level > 0 THEN
1607 oe_debug_pub.add('UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: ' || L_RETURN_STATUS ) ;
1608 oe_debug_pub.add( 'INDEX IS: ' || L_INDEX , 1 ) ;
1609 oe_debug_pub.add( 'HEADER FLOW STATUS IS: ' || P_FLOW_STATUS_CODE , 1 ) ;
1610 END IF;
1611
1612 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1614 END IF;
1615
1616 IF l_index is not NULL THEN
1617 -- update global picture directly
1618 OE_ORDER_UTIL.g_header_rec := OE_ORDER_UTIL.g_old_header_rec;
1619 OE_ORDER_UTIL.g_header_rec.flow_status_code:=p_flow_status_code;
1620 OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
1621 OE_ORDER_UTIL.g_header_rec.operation:=OE_GLOBALS.G_OPR_UPDATE;
1622 IF l_debug_level > 0 THEN
1623 oe_debug_pub.add( 'GLOBAL HEADER FLOW STATUS IS: ' || OE_ORDER_UTIL.G_HEADER_REC.FLOW_STATUS_CODE,1);
1624 oe_debug_pub.add( 'GLOBAL HEADER OPERATION IS: ' || OE_ORDER_UTIL.G_HEADER_REC.OPERATION,1);
1625 END IF;
1626 END IF;
1627 -- bug 4732614
1628 IF l_debug_level > 0 THEN
1629 oe_debug_pub.add('OEXUOWFB.pls: Calling Process_Requests_And_Notify......', 1);
1630 END IF;
1631
1632 OE_Order_PVT.Process_Requests_And_Notify
1633 ( p_header_rec => l_header_rec
1634 ,p_old_header_rec => l_old_header_rec
1635 ,x_return_status => l_return_status);
1636
1637 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1638 IF l_debug_level > 0 THEN
1639 Oe_Debug_pub.Add('Process_Requests_And_Notify,return_status='||l_return_status||',Raising FND_API.G_EXC_ERROR exception',2);
1640 END IF;
1641 RAISE FND_API.G_EXC_ERROR;
1642 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1643 IF l_debug_level > 0 THEN
1644 Oe_Debug_pub.Add('Process_Requests_And_Notify,return_status='||l_return_status||',Raising FND_API.G_EXC_UNEXPECTED_ERROR exception',2);
1645 END IF;
1646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1647 END IF; -- bug 4732614 ends
1648 ELSIF p_sales_document_type_code = 'B' THEN -- Blanket Negotitation
1649 IF l_debug_level > 0 THEN
1650 oe_debug_pub.add('Update_Flow_Status_Code for Blanket Negotiation:' || p_flow_status_code, 5 ) ;
1651 END IF;
1652
1653 SELECT lock_control
1654 INTO l_blanket_lock_control
1655 FROM oe_blanket_headers_all
1656 WHERE header_id = p_header_id
1657 FOR UPDATE NOWAIT;
1658
1659 /* avoid dependency on blanket code, do a direct lock row
1660
1661 OE_Blanket_Util.Lock_Row(p_blanket_id=>p_header_id
1662 , p_blanket_line_id => null
1663 , p_x_lock_control=>l_blanket_lock_control
1664 , x_return_status => l_return_status
1665 , x_msg_count => l_msg_count
1666 , x_msg_data => l_msg_data);
1667 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1668 RAISE FND_API.G_EXC_ERROR;
1669 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1671 END IF;
1672
1673 OE_MSG_PUB.set_msg_context
1674 ( p_entity_code => 'BLANKET'
1675 ,p_entity_id => p_header_id
1676 ,p_header_id => p_header_id);
1677
1678
1679 */
1680 UPDATE OE_BLANKET_HEADERS_ALL
1681 SET FLOW_STATUS_CODE = p_flow_status_code
1682 , LOCK_CONTROL = LOCK_CONTROL + 1
1683 WHERE HEADER_ID = p_header_id;
1684
1685 END IF; --check sales_document_type_code
1686
1687 ELSIF p_item_type = OE_GLOBALS.G_WFI_BKT THEN
1688 IF l_debug_level > 0 THEN
1689 oe_debug_pub.add('Update_Flow_Status_Code for Blanket Fulfillment:' || p_flow_status_code, 5 ) ;
1690 END IF;
1691
1692 -- lock row or select for update here
1693 SELECT lock_control
1694 INTO l_blanket_lock_control
1695 FROM oe_blanket_headers_all
1696 WHERE header_id = p_header_id
1697 FOR UPDATE NOWAIT;
1698
1699 /* avoid dependency on blanket API
1700 OE_Blanket_Util.Lock_Row(p_blanket_id=>p_header_id
1701 , p_blanket_line_id => null
1702 , p_x_lock_control=>l_blanket_lock_control
1703 , x_return_status => l_return_status
1704 , x_msg_count => l_msg_count
1705 , x_msg_data => l_msg_data);
1706 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1707 RAISE FND_API.G_EXC_ERROR;
1708 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1710 END IF;
1711 */
1712 -- set msg context follows
1713
1714 UPDATE OE_BLANKET_HEADERS_ALL
1715 SET FLOW_STATUS_CODE = p_flow_status_code
1716 , LOCK_CONTROL = LOCK_CONTROL + 1
1717 WHERE HEADER_ID = p_header_id;
1718
1719
1720 END IF;
1721
1722 END IF; -- p_header_id is not null
1723
1724
1725 ELSE
1726 -- *** END negotiation/blanket changes ***
1727
1728
1729 -- regular processing for OEOH/OEOL starts below
1730
1731
1732 -- we will process the line_id if both header id and line id are passed
1733
1734 IF p_line_id IS NOT NULL THEN
1735 -- validate p_flow_status
1736
1737 SELECT lookup_code
1738 INTO l_flow_status_code
1739 FROM oe_lookups
1740 WHERE lookup_type = 'LINE_FLOW_STATUS'
1741 AND lookup_code = p_flow_status_code
1742 AND enabled_flag = 'Y'
1743 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1744 AND NVL(END_DATE_ACTIVE, SYSDATE);
1745
1746 IF ( (OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
1747 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') ) THEN
1748
1749 OE_Line_Util.Lock_Rows
1750 (p_line_id=>p_line_id
1751 , x_line_tbl=>l_old_line_tbl
1752 , x_return_status => l_return_status);
1753
1754 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1755 RAISE FND_API.G_EXC_ERROR;
1756 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1758 END IF;
1759
1760 OE_MSG_PUB.set_msg_context
1761 ( p_entity_code => 'LINE'
1762 ,p_entity_id => l_old_line_tbl(1).line_id
1763 ,p_header_id => l_old_line_tbl(1).header_id
1764 ,p_line_id => l_old_line_tbl(1).line_id
1765 ,p_order_source_id => l_old_line_tbl(1).order_source_id
1766 ,p_orig_sys_document_ref => l_old_line_tbl(1).orig_sys_document_ref
1767 ,p_orig_sys_document_line_ref => l_old_line_tbl(1).orig_sys_line_ref
1768 ,p_orig_sys_shipment_ref => l_old_line_tbl(1).orig_sys_shipment_ref
1769 ,p_change_sequence => l_old_line_tbl(1).change_sequence
1770 ,p_source_document_type_id => l_old_line_tbl(1).source_document_type_id
1771 ,p_source_document_id => l_old_line_tbl(1).source_document_id
1772 ,p_source_document_line_id => l_old_line_tbl(1).source_document_line_id );
1773
1774 l_line_tbl := l_old_line_tbl;
1775
1776 ELSE
1777 SELECT source_document_id,
1778 source_document_line_id,
1779 header_id,
1780 orig_sys_document_ref,
1781 orig_sys_line_ref,
1782 order_source_id,
1783 orig_sys_shipment_ref,
1784 change_sequence,
1785 source_document_type_id
1786 INTO l_source_document_id,
1787 l_source_document_line_id,
1788 l_header_id,
1789 l_orig_sys_document_ref,
1790 l_orig_sys_line_ref,
1791 l_order_source_id,
1792 l_orig_sys_shipment_ref,
1793 l_change_sequence,
1794 l_source_document_type_id
1795 FROM OE_ORDER_LINES_ALL
1796 WHERE line_id = p_line_id
1797 FOR UPDATE NOWAIT;
1798
1799 OE_MSG_PUB.set_msg_context
1800 ( p_entity_code => 'LINE'
1801 ,p_entity_id => p_line_id
1802 ,p_header_id => l_header_id
1803 ,p_line_id => p_line_id
1804 ,p_order_source_id => l_order_source_id
1805 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1806 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
1807 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
1808 ,p_change_sequence => l_change_sequence
1809 ,p_source_document_type_id => l_source_document_type_id
1810 ,p_source_document_id => l_source_document_id
1811 ,p_source_document_line_id => l_source_document_line_id );
1812
1813 --7138604 : l_line_tbl(1) is used below, so it has to be
1814 -- initialized in order to prevent NO_DATA_FOUND exception
1815 l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
1816
1817 END IF;
1818 ELSIF p_header_id IS NOT NULL THEN
1819 -- validate p_flow_status
1820
1821 SELECT lookup_code
1822 INTO l_flow_status_code
1823 FROM oe_lookups
1824 WHERE lookup_type = 'FLOW_STATUS'
1825 AND lookup_code = p_flow_status_code
1826 AND enabled_flag = 'Y'
1827 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1828 AND NVL(END_DATE_ACTIVE, SYSDATE);
1829
1830
1831 OE_Header_Util.Lock_Row
1832 (p_header_id=>p_header_id
1833 , p_x_header_rec=>l_header_rec
1834 , x_return_status => l_return_status);
1835
1836 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1837 RAISE FND_API.G_EXC_ERROR;
1838 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1839 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1840 END IF;
1841
1842 OE_MSG_PUB.set_msg_context
1843 ( p_entity_code => 'HEADER'
1844 ,p_entity_id => l_header_rec.header_id
1845 ,p_header_id => l_header_rec.header_id
1846 ,p_line_id => null
1847 ,p_order_source_id => l_header_rec.order_source_id
1848 ,p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref
1849 ,p_orig_sys_document_line_ref => null
1850 ,p_change_sequence => l_header_rec.change_sequence
1851 ,p_source_document_type_id => l_header_rec.source_document_type_id
1852 ,p_source_document_id => l_header_rec.source_document_id
1853 ,p_source_document_line_id => null );
1854
1855 l_old_header_rec := l_header_rec;
1856 END IF;
1857
1858 IF p_line_id is NOT NULL THEN
1859
1860 UPDATE OE_ORDER_LINES_ALL
1861 SET FLOW_STATUS_CODE = p_flow_status_code,
1862 last_update_date = SYSDATE,
1863 last_updated_by = FND_GLOBAL.USER_ID,
1864 last_update_login = FND_GLOBAL.LOGIN_ID,
1865 LOCK_CONTROL = LOCK_CONTROL + 1
1866 WHERE LINE_ID = p_line_id;
1867
1868 IF ( (OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
1869 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') ) THEN
1870
1871
1872 l_line_tbl(1).flow_status_code := p_flow_status_code;
1873 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
1874
1875 -- added for notification framework
1876 -- calling notification framework to get index position
1877 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
1878 p_header_id => l_line_tbl(1).header_id,
1879 p_old_line_rec => l_old_line_tbl(1),
1880 p_line_rec =>l_line_tbl(1),
1881 p_line_id => p_line_id,
1882 x_index => l_index,
1883 x_return_status => l_return_status);
1884
1885 IF l_debug_level > 0 THEN
1886 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE LINE FLOW STATUS CODE IS: ' || L_RETURN_STATUS ) ;
1887 oe_debug_pub.add( 'INDEX IS: ' || L_INDEX , 1 ) ;
1888 END IF;
1889 --OE_DEBUG_PUB.ADD('Line Flow Status is: ' || p_flow_status_code ,1);
1890 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1892 END IF;
1893
1894 IF l_index is not NULL THEN
1895
1896 -- update global picture directly
1897 -- First copy the old picture to the new and then update the
1898 -- changed columns for the new global table.
1899 OE_ORDER_UTIL.g_old_line_tbl(l_index) := l_old_line_tbl(1); --Added for bug 5842114
1900 OE_ORDER_UTIL.g_line_tbl(l_index) := OE_ORDER_UTIL.g_old_line_tbl(l_index);
1901 OE_ORDER_UTIL.g_line_tbl(l_index).flow_status_code:=p_flow_status_code;
1902 OE_ORDER_UTIL.g_line_tbl(l_index).lock_control:=l_line_tbl(1).lock_control;
1903 OE_ORDER_UTIL.g_line_tbl(l_index).line_id:=l_line_tbl(1).line_id;
1904 OE_ORDER_UTIL.g_line_tbl(l_index).header_id:=l_line_tbl(1).header_id;
1905 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date:=l_line_tbl(1).last_update_date;
1906 OE_ORDER_UTIL.g_line_tbl(l_index).operation:=OE_GLOBALS.G_OPR_UPDATE;
1907 IF l_debug_level > 0 THEN
1908 oe_debug_pub.add( 'GLOBAL LINE FLOW STATUS IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_INDEX ) .FLOW_STATUS_CODE , 1 ) ;
1909 oe_debug_pub.add( 'GLOBAL LINE OPERATION IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_INDEX ) .OPERATION , 1 ) ;
1910 END IF;
1911 END IF;
1912 -- bug 4732614
1913 IF l_debug_level > 0 THEN
1914 oe_debug_pub.add('OEXUOWFB.pls: Calling Process_Requests_And_Notify......', 1);
1915 END IF;
1916
1917 OE_Order_PVT.Process_Requests_And_Notify
1918 ( p_line_tbl => l_line_tbl
1919 ,p_old_line_tbl => l_old_line_tbl
1920 ,x_return_status => l_return_status);
1921
1922 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1923 IF l_debug_level > 0 THEN
1924 Oe_Debug_pub.Add('Process_Requests_And_Notify,return_status='||l_return_status||',Raising FND_API.G_EXC_ERROR exception',2);
1925 END IF;
1926 RAISE FND_API.G_EXC_ERROR;
1927 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1928 IF l_debug_level > 0 THEN
1929 Oe_Debug_pub.Add('Process_Requests_And_Notify,return_status='||l_return_status||',Raising FND_API.G_EXC_UNEXPECTED_ERROR exception',2);
1930 END IF;
1931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1932 END IF; -- bug 4732614 ends
1933 END IF; /*ASO installed */
1934
1935 OE_MSG_PUB.Reset_Msg_Context('LINE');
1936 /********************GENESIS********************************
1937 * Some statuses are not going through process order and *
1938 * the update_flow_status is getting called directly. So *
1939 * we need to call synch_header_line for AIA enabled order *
1940 * sources. *
1941 ***********************************************************/
1942 -- Corrected the debug message texts while working on
1943 -- Bug 7261522.
1944 IF l_debug_level > 0 THEN
1945 oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - p_header_id: ' || p_header_id);
1946 oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - p_line_id: ' || p_line_id);
1947 oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - l_header_rec.order_source_id: ' || l_header_rec.order_source_id);
1948 oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - l_order_source_id: ' || l_order_source_id);
1949 oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - l_line_tbl(1).order_source_id: ' || l_line_tbl(1).order_source_id);
1950 oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - l_line_tbl(1).header_id: '||l_line_tbl(1).header_id);
1951 END IF;
1952
1953 -- This IF has been corrected during Bug 7261522 resolution.
1954 IF (
1955 (Oe_Genesis_Util.source_aia_enabled(l_order_source_id)) OR
1956 (Oe_Genesis_Util.source_aia_enabled(l_line_tbl(1).order_source_id))
1957 )
1958 AND
1959 (
1960 p_flow_status_code = 'FULFILLED'
1961 )
1962 THEN
1963 -- Line querying uncommented during Bug 7261522 resolution.
1964 oe_line_util.query_row(
1965 p_line_id => p_line_id
1966 ,x_line_rec => l_line_tbl(1)
1967 );
1968 -- After querying the line, relevant attributes are set on it
1969 -- (implemented for Bug 7261522 resolution).
1970 l_line_tbl(1).flow_status_code := p_flow_status_code;
1971 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
1972
1973
1974 OE_Header_UTIL.Query_Row
1975 (p_header_id => l_line_tbl(1).header_id
1976 ,x_header_rec => l_header_rec
1977 );
1978
1979 select OE_XML_MESSAGE_SEQ_S.nextval
1980 into l_itemkey_sso
1981 from dual;
1982
1983 IF l_debug_level > 0 THEN
1984 oe_debug_pub.add( ' GENESIS : UPDATE FLOW STATUS - l_itemkey_sso'||l_itemkey_sso);
1985 END IF;
1986
1987 IF l_debug_level > 0 THEN
1988 oe_debug_pub.add( ' GENESIS : UPDATE FLOW STATUS');
1989 END IF;
1990
1991 OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(1),
1992 p_change_type => 'LINE_STATUS',
1993 p_req_id => l_itemkey_sso,
1994 X_RETURN_STATUS => L_RETURN_STATUS);
1995
1996 OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE( p_header_rec => l_header_rec
1997 ,p_line_rec => null
1998 ,p_hdr_req_id => l_itemkey_sso
1999 ,p_lin_req_id => l_itemkey_sso
2000 ,p_change_type => 'LINE_STATUS');
2001 END IF;
2002 -- GENESIS --
2003
2004 ELSIF p_header_id IS NOT NULL THEN
2005
2006 UPDATE OE_ORDER_HEADERS_ALL
2007 SET FLOW_STATUS_CODE = p_flow_status_code
2008 , LOCK_CONTROL = LOCK_CONTROL + 1
2009 WHERE HEADER_ID = p_header_id;
2010
2011 l_header_rec.flow_status_code := p_flow_status_code;
2012 l_header_rec.lock_control := l_header_rec.lock_control + 1;
2013
2014 -- aksingh performance
2015 -- As the update is on headers table, it is time to update
2016 -- cache also!
2017 OE_Order_Cache.Set_Order_Header(l_header_rec);
2018
2019 -- Bug 1755817: clear the cached constraint results for header entity
2020 -- when order header is updated.
2021 OE_PC_Constraints_Admin_Pvt.Clear_Cached_Results
2022 (p_validation_entity_id => OE_PC_GLOBALS.G_ENTITY_HEADER);
2023
2024 -- added for notification framework
2025 -- calling notification framework to get index position
2026 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
2027 p_old_header_rec => l_old_header_rec,
2028 p_Header_rec =>l_header_rec,
2029 p_header_id => p_header_id,
2030 x_index => l_index,
2031 x_return_status => l_return_status);
2032
2033 IF l_debug_level > 0 THEN
2034 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: ' || L_RETURN_STATUS ) ;
2035 oe_debug_pub.add( 'INDEX IS: ' || L_INDEX , 1 ) ;
2036 oe_debug_pub.add( 'HEADER FLOW STATUS IS: ' || P_FLOW_STATUS_CODE , 1 ) ;
2037 END IF;
2038 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2039 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2040 END IF;
2041
2042 IF l_index is not NULL THEN
2043 -- update global picture directly
2044 OE_ORDER_UTIL.g_header_rec := OE_ORDER_UTIL.g_old_header_rec;
2045 OE_ORDER_UTIL.g_header_rec.flow_status_code:=p_flow_status_code;
2046 OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
2047 OE_ORDER_UTIL.g_header_rec.operation:=OE_GLOBALS.G_OPR_UPDATE;
2048 IF l_debug_level > 0 THEN
2049 oe_debug_pub.add( 'GLOBAL HEADER FLOW STATUS IS: ' || OE_ORDER_UTIL.G_HEADER_REC.FLOW_STATUS_CODE , 1 ) ;
2050 oe_debug_pub.add( 'GLOBAL HEADER OPERATION IS: ' || OE_ORDER_UTIL.G_HEADER_REC.OPERATION , 1 ) ;
2051 END IF;
2052 END IF;
2053 -- bug 4732614
2054 IF l_debug_level > 0 THEN
2055 oe_debug_pub.add('OEXUOWFB.pls: Calling Process_Requests_And_Notify......', 1);
2056 END IF;
2057
2058 OE_Order_PVT.Process_Requests_And_Notify
2059 ( p_header_rec => l_header_rec
2060 ,p_old_header_rec => l_old_header_rec
2061 ,x_return_status => l_return_status);
2062
2063 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2064 IF l_debug_level > 0 THEN
2065 Oe_Debug_pub.Add('Process_Requests_And_Notify,return_status='||l_return_status||',Raising FND_API.G_EXC_ERROR exception',2);
2066 END IF;
2067 RAISE FND_API.G_EXC_ERROR;
2068 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2069 IF l_debug_level > 0 THEN
2070 Oe_Debug_pub.Add('Process_Requests_And_Notify,return_status='||l_return_status||',Raising FND_API.G_EXC_UNEXPECTED_ERROR exception',2);
2071 END IF;
2072 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2073 END IF; -- bug 4732614 ends
2074 ELSE
2075 IF l_debug_level > 0 THEN
2076 oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: HEADER_ID AND LINE_ID ARE NULL' ) ;
2077 END IF;
2078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2079 END IF;
2080
2081 END IF; -- End of OENH/OEBH vs OEOH/OEOL processing
2082
2083 --Bug 3356542
2084 OE_PC_Constraints_Admin_PVT.Clear_Cached_Results;
2085
2086 IF l_debug_level > 0 THEN
2087 oe_debug_pub.add('ENTERING UPDATE_FLOW_STATUS_CODE' , 5 ) ;
2088 oe_debug_pub.add('UFSC: GLOBAL RECURSION WITHOUT EXCEPTION: ' || OE_ORDER_UTIL.G_RECURSION_WITHOUT_EXCEPTION) ;
2089 oe_debug_pub.add('UFSC: GLOBAL CACHE BOOKED FLAG' || OE_ORDER_CACHE.G_HEADER_REC.BOOKED_FLAG ) ;
2090 oe_debug_pub.add('UFSC: GLOBAL PICTURE HEADER BOOKED FLAG' || OE_ORDER_UTIL.G_HEADER_REC.BOOKED_FLAG ) ;
2091 oe_debug_pub.add('UFSC: COUNT OF NEW LINE TABLE= '|| OE_ORDER_UTIL.G_LINE_TBL.COUNT ) ;
2092 oe_debug_pub.add('UFSC: COUNT OF OLD LINE TABLE= '|| OE_ORDER_UTIL.G_OLD_LINE_TBL.COUNT ) ;
2093 oe_debug_pub.add('UFSC: COUNT OF NEW LINE ADJ TABLE= '|| OE_ORDER_UTIL.G_LINE_ADJ_TBL.COUNT ) ;
2094 oe_debug_pub.add('UFSC: COUNT OF OLD LINE ADJ TABLE= '|| OE_ORDER_UTIL.G_OLD_LINE_ADJ_TBL.COUNT ) ;
2095 oe_debug_pub.add('UFSC: COUNT OF NEW HDR ADJ TABLE= '|| OE_ORDER_UTIL.G_HEADER_ADJ_TBL.COUNT ) ;
2096 oe_debug_pub.add('UFSC: COUNT OF OLD HDR ADJ TABLE= '|| OE_ORDER_UTIL.G_OLD_HEADER_ADJ_TBL.COUNT ) ;
2097 oe_debug_pub.add('UFSC: COUNT OF NEW HDR SCREDIT TABLE= '|| OE_ORDER_UTIL.G_HEADER_SCREDIT_TBL.COUNT ) ;
2098 oe_debug_pub.add('UFSC: COUNT OF OLD HDR SCREDIT TABLE= '|| OE_ORDER_UTIL.G_OLD_HEADER_SCREDIT_TBL.COUNT ) ;
2099 oe_debug_pub.add('UFSC: COUNT OF NEW LINE SCREDIT TABLE= '|| OE_ORDER_UTIL.G_LINE_SCREDIT_TBL.COUNT ) ;
2100 oe_debug_pub.add('UFSC: COUNT OF OLD LINE SCREDIT TABLE= '|| OE_ORDER_UTIL.G_OLD_LINE_SCREDIT_TBL.COUNT ) ;
2101 oe_debug_pub.add('UFSC: COUNT OF NEW LOT SERIAL TABLE= '|| OE_ORDER_UTIL.G_LOT_SERIAL_TBL.COUNT ) ;
2102 oe_debug_pub.add('UFSC: COUNT OF OLD LOT SERIAL TABLE= '|| OE_ORDER_UTIL.G_OLD_LOT_SERIAL_TBL.COUNT ) ;
2103 oe_debug_pub.add('EXITING UPDATE_FLOW_STATUS_CODE' , 5 ) ;
2104 END IF;
2105
2106 EXCEPTION
2107 WHEN NO_DATA_FOUND THEN
2108 ROLLBACK TO UPDATE_FLOW_STATUS_CODE;
2109
2110 IF l_debug_level > 0 THEN
2111 oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: NO_DATA_FOUND' ) ;
2112 END IF;
2113 IF p_line_id IS NOT NULL THEN
2114 OE_MSG_PUB.Reset_Msg_Context('LINE');
2115 ELSIF p_header_id IS NOT NULL THEN
2116 OE_MSG_PUB.Reset_Msg_Context('HEADER');
2117 END IF;
2118 WHEN FND_API.G_EXC_ERROR THEN
2119 ROLLBACK TO UPDATE_FLOW_STATUS_CODE;
2120 IF l_debug_level > 0 THEN
2121 oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: ERROR' , 5 ) ;
2122 END IF;
2123 x_return_status := FND_API.G_RET_STS_ERROR;
2124 IF p_line_id IS NOT NULL THEN
2125 OE_MSG_PUB.Reset_Msg_Context('LINE');
2126 ELSIF p_header_id IS NOT NULL THEN
2127 OE_MSG_PUB.Reset_Msg_Context('HEADER');
2128 END IF;
2129 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
2130 ROLLBACK TO UPDATE_FLOW_STATUS_CODE;
2131 IF l_debug_level > 0 THEN
2132 oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: LOCK EXC' , 5 ) ;
2133 END IF;
2134 x_return_status := FND_API.G_RET_STS_ERROR;
2135 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2136 THEN
2137 fnd_message.set_name('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
2138 OE_MSG_PUB.Add;
2139 END IF;
2140 IF p_line_id IS NOT NULL THEN
2141 OE_MSG_PUB.Reset_Msg_Context('LINE');
2142 ELSIF p_header_id IS NOT NULL THEN
2143 OE_MSG_PUB.Reset_Msg_Context('HEADER');
2144 END IF;
2145 WHEN OTHERS THEN
2146 ROLLBACK TO UPDATE_FLOW_STATUS_CODE;
2147 IF l_debug_level > 0 THEN
2148 oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: UNEXP ERROR' , 5 ) ;
2149 END IF;
2150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2151 IF FND_MSG_PUB.Check_Msg_Level
2152 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2153 THEN
2154 OE_MSG_PUB.Add_Exc_Msg
2155 ( G_PKG_NAME
2156 , 'Update_Flow_Status_Code'
2157 );
2158 END IF;
2159 IF p_line_id IS NOT NULL THEN
2160 OE_MSG_PUB.Reset_Msg_Context('LINE');
2161 ELSIF p_header_id IS NOT NULL THEN
2162 OE_MSG_PUB.Reset_Msg_Context('HEADER');
2163 END IF;
2164 END Update_Flow_Status_Code;
2165
2166
2167 PROCEDURE Set_Notification_Approver(
2168 itemtype in varchar2,
2169 itemkey in varchar2,
2170 actid in number,
2171 funcmode in varchar2,
2172 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
2173 IS
2174 v_id VARCHAR2(240);
2175 v_value VARCHAR2(240);
2176 l_type VARCHAR2(30);
2177 v_header_id NUMBER;
2178
2179 --
2180 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2181 --
2182 BEGIN
2183
2184 --
2185 -- RUN mode - normal process execution
2186 --
2187 IF (funcmode = 'RUN') THEN
2188 l_type := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'SOURCE');
2189 IF l_type = 'PROFILE_APPROVER' THEN
2190 -- changed call from fnd_profile.value to oe_profile.value to retrieve profile in created_by context
2191 IF itemtype = 'OEOH' THEN
2192 v_value := oe_profile.value(p_header_id => to_number(itemkey),
2193 p_line_id => null,
2194 p_profile_option_name => 'OE_NOTIFICATION_APPROVER');
2195 ELSIF itemtype = 'OEOL' THEN
2196 v_value := oe_profile.value(p_header_id => null,
2197 p_line_id => to_number(itemkey),
2198 p_profile_option_name => 'OE_NOTIFICATION_APPROVER');
2199 END IF;
2200 ELSIF l_type = 'ORDER_CREATED_BY' THEN
2201 IF itemtype = 'OEOH' THEN
2202 SELECT CREATED_BY
2203 INTO v_id
2204 FROM OE_ORDER_HEADERS_ALL
2205 WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
2206 ELSIF itemtype = 'OEOL' THEN
2207 SELECT HEADER_ID
2208 INTO v_header_id
2209 FROM OE_ORDER_LINES_ALL
2210 WHERE LINE_ID = TO_NUMBER(ITEMKEY);
2211
2212 SELECT CREATED_BY
2213 INTO v_id
2214 FROM OE_ORDER_HEADERS_ALL
2215 WHERE HEADER_ID = v_header_id;
2216 END IF;
2217 SELECT USER_NAME
2218 INTO v_value
2219 FROM FND_USER
2220 WHERE USER_ID = v_id
2221 AND (EMPLOYEE_ID is null
2222 OR
2223 EMPLOYEE_ID in (SELECT PERSON_ID
2224 FROM PER_PEOPLE_F));
2225 /*
2226 sales rep is not available in
2227 WF view yet
2228
2229 ELSIF l_type = 'ORDER_SALESPERSON' THEN
2230 SELECT SALESREP_ID
2231 INTO v_id
2232 FROM OE_ORDER_HEADERS_ALL
2233 WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
2234 */
2235 ELSIF l_type = 'CREATED_BY' THEN
2236 IF itemtype='OEOH' THEN
2237 SELECT CREATED_BY
2238 INTO v_id
2239 FROM OE_ORDER_HEADERS_ALL
2240 WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
2241 ELSIF itemtype='OEOL' THEN
2242 SELECT CREATED_BY
2243 INTO v_id
2244 FROM OE_ORDER_LINES_ALL
2245 WHERE LINE_ID = TO_NUMBER(ITEMKEY);
2246 END IF;
2247
2248 SELECT USER_NAME
2249 INTO v_value
2250 FROM FND_USER
2251 WHERE USER_ID = v_id
2252 AND (EMPLOYEE_ID is null
2253 OR
2254 EMPLOYEE_ID in (SELECT PERSON_ID
2255 FROM PER_PEOPLE_F));
2256 /*
2257 sales rep is not available in
2258 WF view yet
2259 ELSIF l_type = 'SALESPERSON' THEN
2260 IF itemtype='OEOH' THEN
2261 SELECT SALESREP_ID
2262 INTO v_id
2263 FROM OE_ORDER_HEADERS_ALL
2264 WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
2265 ELSIF itemtype='OEOL' THEN
2266 SELECT SALESREP_ID
2267 INTO v_id
2268 FROM OE_ORDER_LINES_ALL
2269 WHERE LINE_ID = TO_NUMBER(ITEMKEY);
2270 END IF;
2271 */
2272 END IF;
2273 wf_engine.SetItemAttrText(itemtype, itemkey, 'NOTIFICATION_APPROVER', v_value);
2274 resultout := 'COMPLETE:COMPLETE';
2275 END IF;
2276 Exception
2277 When Others Then
2278 wf_core.context('', 'Set_Notification_Approver', itemtype, itemkey,
2279 to_char(actid), funcmode);
2280 raise;
2281 End Set_Notification_Approver;
2282
2283 PROCEDURE Update_Quote_Blanket(p_item_type IN VARCHAR2,
2284 p_item_key IN VARCHAR2,
2285 p_flow_status_code IN VARCHAR2 DEFAULT NULL,
2286 p_open_flag IN VARCHAR2 DEFAULT NULL,
2287 p_draft_submitted_flag IN VARCHAR2 DEFAULT NULL,
2288 x_return_status OUT NOCOPY VARCHAR2)
2289 IS
2290 l_header_id NUMBER;
2291 l_flow_status_code VARCHAR2(30);
2292 l_header_rec OE_Order_PUB.Header_Rec_Type;
2293 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
2294 l_return_status VARCHAR2(30);
2295 l_index NUMBER;
2296 l_blanket_lock_control NUMBER;
2297 l_msg_count NUMBER;
2298 l_msg_data VARCHAR2(2000);
2299 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2300 l_updated_flag VARCHAR2(1) := 'N';
2301 l_sales_document_type_code VARCHAR2(30);
2302 l_line_id NUMBER;
2303 l_lock_control NUMBER;
2304
2305 Cursor lines IS
2306 SELECT line_id, lock_control
2307 FROM OE_ORDER_LINES_ALL
2308 WHERE HEADER_ID = l_header_id
2309 FOR UPDATE NOWAIT;
2310
2311 Cursor blanket_lines IS
2312 SELECT line_id, lock_control
2313 FROM OE_BLANKET_LINES_ALL
2314 WHERE HEADER_ID = l_header_id
2315 FOR UPDATE NOWAIT;
2316
2317 BEGIN
2318 IF l_debug_level > 0 THEN
2319 oe_debug_pub.add( 'ENTERING UPDATE_QUOTE_BLANKET' , 5 ) ;
2320 END IF;
2321
2322 SAVEPOINT UPDATE_QUOTE_BLANKET;
2323
2324 x_return_status := FND_API.G_RET_STS_SUCCESS;
2325
2326 -- Check if the ASO is installed to call the NOTIFY_OC.
2327 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
2328 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
2329 END IF;
2330
2331 l_header_id := to_number(p_item_key);
2332 IF p_flow_status_code is not null THEN
2333 -- if flow_status_code is passed in, validate it
2334 SELECT lookup_code
2335 INTO l_flow_status_code
2336 FROM oe_lookups
2337 WHERE lookup_type= 'FLOW_STATUS'
2338 AND lookup_code = p_flow_status_code
2339 AND enabled_flag = 'Y'
2340 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
2341 AND NVL(END_DATE_ACTIVE, SYSDATE);
2342
2343 END IF;
2344
2345 IF p_item_type = OE_GLOBALS.G_WFI_BKT THEN
2346 l_sales_document_type_code := 'B';
2347 ELSE -- itemtype = OENH
2348 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(p_item_type, p_item_key, 'SALES_DOCUMENT_TYPE_CODE');
2349 END IF;
2350
2351
2352 IF l_sales_document_type_code = 'O' THEN
2353 OE_Header_Util.Lock_Row(p_header_id=>l_header_id
2354 , p_x_header_rec=>l_header_rec
2355 , x_return_status => l_return_status);
2356 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2357 RAISE FND_API.G_EXC_ERROR;
2358 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2360 END IF;
2361
2362 -- is entity HEADER correct?
2363 OE_MSG_PUB.set_msg_context
2364 ( p_entity_code => 'HEADER'
2365 ,p_entity_id => l_header_rec.header_id
2366 ,p_header_id => l_header_rec.header_id
2367 ,p_line_id => null
2368 ,p_order_source_id => l_header_rec.order_source_id
2369 ,p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref
2370 ,p_orig_sys_document_line_ref => null
2371 ,p_change_sequence => l_header_rec.change_sequence
2372 ,p_source_document_type_id => l_header_rec.source_document_type_id
2373 ,p_source_document_id => l_header_rec.source_document_id
2374 ,p_source_document_line_id => null );
2375
2376 l_old_header_rec := l_header_rec;
2377
2378 IF p_flow_status_code is not null THEN
2379 UPDATE OE_ORDER_HEADERS_ALL
2380 SET FLOW_STATUS_CODE = p_flow_status_code
2381 WHERE HEADER_ID = l_header_id;
2382
2383 -- Also update all lines to have the same flow_status_code
2384 Open Lines;
2385 Loop
2386 FETCH lines into l_line_id, l_lock_control;
2387 EXIT WHEN Lines%NOTFOUND;
2388 End Loop;
2389 Close Lines;
2390
2391 UPDATE OE_ORDER_LINES_ALL
2392 SET FLOW_STATUS_CODE = p_flow_status_code,
2393 LOCK_CONTROL = LOCk_CONTROL + 1
2394 WHERE HEADER_ID = l_header_id;
2395
2396 l_updated_flag := 'Y';
2397 l_header_rec.flow_status_code := p_flow_status_code;
2398 END IF;
2399
2400 IF p_open_flag is not null THEN
2401 UPDATE OE_ORDER_HEADERS_ALL
2402 SET OPEN_FLAG = p_open_flag
2403 WHERE HEADER_ID = l_header_id;
2404
2405 l_updated_flag := 'Y';
2406 l_header_rec.open_flag := p_open_flag;
2407
2408 -- XDING bug FP5172433
2409 UPDATE OE_ORDER_LINES_ALL
2410 SET OPEN_FLAG = p_open_flag
2411 WHERE HEADER_ID = l_header_id;
2412 -- XDING bug FP5172433
2413
2414 END IF;
2415
2416 IF p_draft_submitted_flag is not null THEN
2417 UPDATE OE_ORDER_HEADERS_ALL
2418 SET DRAFT_SUBMITTED_FLAG = p_draft_submitted_flag
2419 WHERE HEADER_ID = l_header_id;
2420
2421 l_updated_flag := 'Y';
2422 l_header_rec.draft_submitted_flag := p_draft_submitted_flag;
2423 END IF;
2424
2425 IF l_updated_flag = 'Y' THEN
2426 UPDATE OE_ORDER_HEADERS_ALL
2427 SET LOCK_CONTROL = LOCK_CONTROL + 1
2428 WHERE HEADER_ID = l_header_id;
2429
2430 l_header_rec.lock_control := l_header_rec.lock_control + 1;
2431 END IF;
2432
2433
2434 -- aksingh performance
2435 -- As the update is on headers table, it is time to update
2436 -- cache also!
2437 OE_Order_Cache.Set_Order_Header(l_header_rec);
2438
2439 -- Bug 1755817: clear the cached constraint results for header entity
2440 -- when order header is updated.
2441 OE_PC_Constraints_Admin_Pvt.Clear_Cached_Results
2442 (p_validation_entity_id => OE_PC_GLOBALS.G_ENTITY_HEADER);
2443
2444 -- added for notification framework
2445 -- calling notification framework to get index position
2446 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
2447 p_old_header_rec => l_old_header_rec,
2448 p_Header_rec =>l_header_rec,
2449 p_header_id => l_header_id,
2450 x_index => l_index,
2451 x_return_status => l_return_status);
2452
2453 IF l_debug_level > 0 THEN
2454 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: ' || L_RETURN_STATUS ) ;
2455 oe_debug_pub.add( 'INDEX IS: ' || L_INDEX , 1 ) ;
2456 oe_debug_pub.add( 'HEADER FLOW STATUS IS: ' || P_FLOW_STATUS_CODE , 1 ) ;
2457 END IF;
2458
2459 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2461 END IF;
2462
2463 IF l_index is not NULL THEN
2464 -- update global picture directly
2465 OE_ORDER_UTIL.g_header_rec := OE_ORDER_UTIL.g_old_header_rec;
2466 IF p_flow_status_code is not null THEN
2467 OE_ORDER_UTIL.g_header_rec.flow_status_code:=p_flow_status_code;
2468 END IF;
2469 IF p_open_flag is not null THEN
2470 OE_ORDER_UTIL.g_header_rec.open_flag:=p_open_flag;
2471 END IF;
2472 IF p_draft_submitted_flag is not null THEN
2473 OE_ORDER_UTIL.g_header_rec.draft_submitted_flag:=p_draft_submitted_flag;
2474 END IF;
2475 OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
2476 OE_ORDER_UTIL.g_header_rec.operation:=OE_GLOBALS.G_OPR_UPDATE;
2477 IF l_debug_level > 0 THEN
2478 oe_debug_pub.add( 'GLOBAL HEADER FLOW STATUS IS: ' || OE_ORDER_UTIL.G_HEADER_REC.FLOW_STATUS_CODE , 1 );
2479 oe_debug_pub.add( 'GLOBAL HEADER OPEN_FLAG IS: ' || OE_ORDER_UTIL.G_HEADER_REC.OPEN_FLAG , 1 );
2480 oe_debug_pub.add( 'GLOBAL HEADER DRAFT_SUBMITTED_FLAG IS: ' || OE_ORDER_UTIL.G_HEADER_REC.DRAFT_SUBMITTED_FLAG , 1 );
2481 oe_debug_pub.add( 'GLOBAL HEADER OPERATION IS: ' || OE_ORDER_UTIL.G_HEADER_REC.OPERATION , 1 ) ;
2482 END IF;
2483 END IF;
2484 -- bug 4732614
2485 IF l_debug_level > 0 THEN
2486 oe_debug_pub.add('OEXUOWFB.pls: Calling Process_Requests_And_Notify......', 1);
2487 END IF;
2488
2489 OE_Order_PVT.Process_Requests_And_Notify
2490 ( p_header_rec => l_header_rec
2491 ,p_old_header_rec => l_old_header_rec
2492 ,x_return_status => l_return_status);
2493
2494 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2495 IF l_debug_level > 0 THEN
2496 Oe_Debug_pub.Add('Process_Requests_And_Notify,return_status='||l_return_status||',Raising FND_API.G_EXC_ERROR exception',2);
2497 END IF;
2498 RAISE FND_API.G_EXC_ERROR;
2499 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2500 IF l_debug_level > 0 THEN
2501 Oe_Debug_pub.Add('Process_Requests_And_Notify,return_status='||l_return_status||',Raising FND_API.G_EXC_UNEXPECTED_ERROR exception',2);
2502 END IF;
2503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2504 END IF; -- bug 4732614 ends
2505 ELSIF l_sales_document_type_code = 'B' THEN -- Blanket Negotitation/Fulfillment
2506 SELECT lock_control
2507 INTO l_blanket_lock_control
2508 FROM oe_blanket_headers_all
2509 WHERE header_id = l_header_id
2510 FOR UPDATE NOWAIT;
2511
2512 /* avoid dependency on blanket API
2513 OE_Blanket_Util.Lock_Row(p_blanket_id=>l_header_id
2514 , p_blanket_line_id => null
2515 , p_x_lock_control=>l_blanket_lock_control
2516 , x_return_status => l_return_status
2517 , x_msg_count => l_msg_count
2518 , x_msg_data => l_msg_data);
2519 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2520 RAISE FND_API.G_EXC_ERROR;
2521 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2523 END IF;
2524
2525 OE_MSG_PUB.set_msg_context
2526 ( p_entity_code => 'BLANKET'
2527 ,p_entity_id => p_header_id
2528 ,p_header_id => p_header_id);
2529
2530
2531 */
2532
2533 IF p_flow_status_code is not null THEN
2534 UPDATE OE_BLANKET_HEADERS_ALL
2535 SET FLOW_STATUS_CODE = p_flow_status_code
2536 WHERE HEADER_ID = l_header_id;
2537
2538 l_updated_flag := 'Y';
2539 END IF;
2540
2541 IF p_open_flag is not null THEN
2542 UPDATE OE_BLANKET_HEADERS_ALL
2543 SET OPEN_FLAG = p_open_flag
2544 WHERE HEADER_ID = l_header_id;
2545
2546 -- ZB put the code here
2547 oe_debug_pub.add('Acquiring locks on blanket lines');
2548 open blanket_lines;
2549 loop
2550 fetch blanket_lines into l_line_id, l_lock_control;
2551 EXIT WHEN blanket_lines%NOTFOUND;
2552 end loop;
2553 close blanket_lines;
2554
2555 oe_debug_pub.add('Updating blanket lines 4 open flag');
2556 update OE_BLANKET_LINES_ALL
2557 SET OPEN_FLAG = p_open_flag
2558 WHERE HEADER_ID = l_header_id;
2559 -- End code
2560
2561 l_updated_flag := 'Y';
2562 END IF;
2563
2564 IF p_draft_submitted_flag is not null THEN
2565 UPDATE OE_BLANKET_HEADERS_ALL
2566 SET DRAFT_SUBMITTED_FLAG = p_draft_submitted_flag
2567 WHERE HEADER_ID = l_header_id;
2568
2569 l_updated_flag := 'Y';
2570 END IF;
2571
2572 IF l_updated_flag = 'Y' THEN
2573 UPDATE OE_BLANKET_HEADERS_ALL
2574 SET LOCK_CONTROL = LOCK_CONTROL + 1
2575 WHERE HEADER_ID = l_header_id;
2576 END IF;
2577 END IF; --check sales_document_type_code
2578 -- Bug 3356542
2579 OE_PC_Constraints_Admin_PVT.Clear_Cached_Results;
2580
2581 IF l_debug_level > 0 THEN
2582 oe_debug_pub.add( 'EXITING UPDATE_QUOTE_BLANKET' , 5 ) ;
2583 END IF;
2584
2585 EXCEPTION
2586 WHEN NO_DATA_FOUND THEN
2587 ROLLBACK TO UPDATE_QUOTE_BLANKET;
2588 IF l_debug_level > 0 THEN
2589 oe_debug_pub.add( 'UPDATE_QUOTE_BLANKET: NO_DATA_FOUND' ) ;
2590 END IF;
2591 x_return_status := FND_API.G_RET_STS_ERROR;
2592 IF l_header_id IS NOT NULL THEN
2593 OE_MSG_PUB.Reset_Msg_Context('HEADER');
2594 END IF;
2595 WHEN FND_API.G_EXC_ERROR THEN
2596 ROLLBACK TO UPDATE_QUOTE_BLANKET;
2597 IF l_debug_level > 0 THEN
2598 oe_debug_pub.add( 'UPDATE_QUOTE_BLANKET: ERROR' , 5 ) ;
2599 END IF;
2600 x_return_status := FND_API.G_RET_STS_ERROR;
2601 IF l_header_id IS NOT NULL THEN
2602 OE_MSG_PUB.Reset_Msg_Context('HEADER');
2603 END IF;
2604 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
2605 ROLLBACK TO UPDATE_QUOTE_BLANKET;
2606 IF l_debug_level > 0 THEN
2607 oe_debug_pub.add( 'UPDATE_QUOTE_BLANKET: LOCK EXC' , 5 ) ;
2608 END IF;
2609 x_return_status := FND_API.G_RET_STS_ERROR;
2610 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2611 THEN
2612 fnd_message.set_name('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
2613 OE_MSG_PUB.Add;
2614 END IF;
2615 IF l_header_id IS NOT NULL THEN
2616 OE_MSG_PUB.Reset_Msg_Context('HEADER');
2617 END IF;
2618 WHEN OTHERS THEN
2619 ROLLBACK TO UPDATE_QUOTE_BLANKET;
2620 IF l_debug_level > 0 THEN
2621 oe_debug_pub.add( 'UPDATE_QUOTE_BLANKET: UNEXP ERROR' , 5 ) ;
2622 END IF;
2623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2624 IF FND_MSG_PUB.Check_Msg_Level
2625 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2626 THEN
2627 OE_MSG_PUB.Add_Exc_Msg
2628 ( G_PKG_NAME
2629 , 'Update_Quote_Blanket'
2630 );
2631 END IF;
2632 IF l_header_id IS NOT NULL THEN
2633 OE_MSG_PUB.Reset_Msg_Context('HEADER');
2634 END IF;
2635
2636 END Update_Quote_Blanket;
2637
2638
2639 /* -------------------------------------------------
2640 PROCEDURE: Create_WorkItem_Upgrade
2641 USAGE: This is used for blanket upgrade only
2642 at this time. It will create the WF process,
2643 but will not start the flow. Caller can use
2644 handleerror API call to jump to the right
2645 activity
2646 ----------------------------------------------------- */
2647 PROCEDURE Create_WorkItem_Upgrade
2648 (p_item_type IN VARCHAR2,
2649 p_item_key IN VARCHAR2,
2650 p_process_name IN VARCHAR2,
2651 p_transaction_number IN NUMBER,
2652 p_sales_document_type_code IN VARCHAR2,
2653 p_user_id IN NUMBER,
2654 p_resp_id IN NUMBER,
2655 p_appl_id IN NUMBER,
2656 p_org_id IN NUMBER
2657 )
2658 IS
2659 user_key_string VARCHAR2(240);
2660 l_valid_process VARCHAR2(30);
2661 l_aname wf_engine.nametabtyp;
2662 l_aname2 wf_engine.nametabtyp;
2663 l_avalue wf_engine.numtabtyp;
2664 l_avaluetext wf_engine.texttabtyp;
2665 l_user_name VARCHAR2(100);
2666 l_validate_user NUMBER;
2667 l_owner_role VARCHAR2(100);
2668
2669 --
2670 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2671 --
2672 BEGIN
2673
2674 IF l_debug_level > 0 THEN
2675 oe_debug_pub.add( 'Entering Create_WorkItem_Upgrade. item_type/item_key=' || p_item_type || '/' || p_item_key, 1) ;
2676 END IF;
2677
2678 -- validate the p_process_name is ok
2679 select name
2680 into l_valid_process
2681 from wf_activities
2682 where item_type=p_item_type
2683 and name=p_process_name
2684 and runnable_flag = 'Y'
2685 and end_date is null;
2686
2687 IF l_debug_level > 0 THEN
2688 oe_debug_pub.add( 'process_name: ' || l_valid_process, 4);
2689 END IF;
2690
2691 IF p_sales_document_type_code = 'O' THEN
2692 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
2693 ELSIF p_sales_document_type_code = 'B' THEN
2694 fnd_message.set_name('ONT', 'OE_NTF_BSA');
2695 END IF;
2696
2697 user_key_string := substrb(fnd_message.get, 1, 240) || ' ' || to_char(p_transaction_number);
2698
2699 SELECT user_name
2700 INTO l_owner_role
2701 FROM FND_USER
2702 WHERE USER_ID = p_user_id;
2703
2704 -- Create process
2705 WF_ENGINE.CreateProcess(p_item_type,
2706 p_item_key,
2707 p_process_name,
2708 user_key_string,
2709 l_owner_role);
2710
2711
2712 IF l_debug_level > 0 THEN
2713 oe_debug_pub.add( 'After WF_ENGINE.CreateProcess', 4 ) ;
2714 END IF;
2715
2716 -- Set various Header Attributes
2717 l_aname(1) := 'USER_ID';
2718 l_avalue(1) := p_user_id;
2719 l_aname(2) := 'APPLICATION_ID';
2720 l_avalue(2) := p_appl_id;
2721 l_aname(3) := 'RESPONSIBILITY_ID';
2722 l_avalue(3) := p_resp_id;
2723 l_aname(4) := 'ORG_ID';
2724 l_avalue(4) := p_org_id;
2725 l_aname(5) := 'TRANSACTION_NUMBER';
2726 l_avalue(5) := p_transaction_number;
2727
2728 wf_engine.SetItemAttrNumberArray(p_item_type
2729 , p_item_key
2730 , l_aname
2731 , l_avalue
2732 );
2733
2734 IF l_debug_level > 0 THEN
2735 oe_debug_pub.add( 'EXITING CREATE_WORKITEM_UPGRADE' , 4) ;
2736 END IF;
2737
2738 EXCEPTION
2739 WHEN OTHERS THEN
2740 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2741 THEN
2742 OE_MSG_PUB.Add_Exc_Msg
2743 ( G_PKG_NAME
2744 , 'Create_WorkItem_Upgrade'
2745 );
2746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2747 END IF;
2748
2749 END Create_WorkItem_Upgrade;
2750
2751 PROCEDURE CreateStart_HdrInternal
2752 ( p_item_type IN VARCHAR2,
2753 p_header_id IN NUMBER,
2754 p_transaction_number IN NUMBER,
2755 p_sales_document_type_code IN VARCHAR2
2756 )
2757 IS
2758 --
2759 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2760 l_count NUMBER;
2761 --
2762 BEGIN
2763
2764 IF l_debug_level > 0 THEN
2765 oe_debug_pub.add( 'IN CREATESTART_HDRINTERNAL' ) ;
2766 END IF;
2767
2768 Create_HdrWorkItemInternal(p_item_type, p_header_id, p_transaction_number, p_sales_document_type_code);
2769
2770 IF p_item_type = OE_GLOBALS.G_WFI_NGO THEN
2771 OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW := p_header_id;
2772 ELSIF p_item_type = OE_GLOBALS.G_WFI_BKT THEN
2773 OE_GLOBALS.G_START_BLANKET_HEADER_FLOW := p_header_id;
2774 -- For OEBH
2775 -- Check if a OENH flow exists, if so set the parent
2776 SELECT count(1)
2777 INTO l_count
2778 FROM wf_items
2779 WHERE item_type=OE_GLOBALS.G_WFI_NGO
2780 AND item_key =to_char(p_header_id);
2781
2782 IF l_count > 0 THEN
2783 WF_ITEM.Set_Item_Parent(OE_GLOBALS.G_WFI_BKT,
2784 to_char(p_header_id),
2785 OE_GLOBALS.G_WFI_NGO,
2786 to_char(p_header_id), '');
2787 END IF;
2788
2789 END IF;
2790
2791 OE_GLOBALS.G_SALES_DOCUMENT_TYPE_CODE := p_sales_document_type_code;
2792
2793 IF l_debug_level > 0 THEN
2794 oe_debug_pub.add( 'EXITING CREATESTART_HDRPROCESSINTERNAL' ) ;
2795 END IF;
2796
2797 EXCEPTION
2798 WHEN FND_API.G_EXC_ERROR THEN
2799 RAISE;
2800 WHEN OTHERS THEN
2801 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2802 THEN
2803 OE_MSG_PUB.Add_Exc_Msg
2804 ( G_PKG_NAME
2805 , 'CreateStart_HdrInternal'
2806 );
2807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2808 END IF;
2809
2810 END CreateStart_HdrInternal;
2811
2812
2813
2814
2815 /* ----------------------------------------------------------
2816 PROCEDURE: Create_HdrWorkItemInternal
2817 USAGE: The main create WF work item routine for item type
2818 OENH and OEBH
2819 ------------------------------------------------------------- */
2820
2821 PROCEDURE Create_HdrWorkItemInternal
2822 (p_item_type IN VARCHAR2,
2823 p_header_id IN NUMBER,
2824 p_transaction_number IN NUMBER,
2825 p_sales_document_type_code IN VARCHAR2
2826 )
2827 IS
2828 l_hdr_process_name VARCHAR2(30);
2829 l_aname wf_engine.nametabtyp;
2830 l_aname2 wf_engine.nametabtyp;
2831 l_avalue wf_engine.numtabtyp;
2832 l_avaluetext wf_engine.texttabtyp;
2833
2834 user_key_string VARCHAR2(240);
2835 l_user_name VARCHAR2(100);
2836 l_validate_user NUMBER;
2837 l_sales_document_type VARCHAR2(240);
2838 l_owner_role VARCHAR2(100);
2839
2840 --
2841 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2842 --
2843 BEGIN
2844
2845 IF l_debug_level > 0 THEN
2846 oe_debug_pub.add( 'IN CREATE_HDRWORKITEMINTERNAL, ITEM_TYPE/ITEM_KEY=' || p_item_type || '/' || to_char(p_header_id)) ;
2847 END IF;
2848
2849 l_hdr_process_name := Get_ProcessName(p_itemtype=> p_item_type, p_itemkey=>p_header_id, p_SalesDocumentTypeCode=> p_sales_document_type_code);
2850
2851 IF l_debug_level > 0 THEN
2852 oe_debug_pub.add( 'Get ProcessName: ' || l_hdr_process_name);
2853 END IF;
2854
2855 -- set user key
2856 IF p_sales_document_type_code = 'O' THEN
2857 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
2858 ELSIF p_sales_document_type_code = 'B' THEN
2859 fnd_message.set_name('ONT', 'OE_NTF_BSA');
2860 END IF;
2861
2862 l_sales_document_type := substrb(fnd_message.get, 1, 240);
2863 user_key_string := l_sales_document_type || ' ' || to_char(p_transaction_number);
2864
2865 SELECT user_name
2866 INTO l_owner_role
2867 FROM FND_USER
2868 WHERE USER_ID = FND_GLOBAL.USER_ID;
2869
2870 -- Create Header Work item
2871 WF_ENGINE.CreateProcess(p_item_type,
2872 to_char(p_header_id),
2873 l_hdr_process_name,
2874 user_key_string,
2875 l_owner_role);
2876
2877
2878 IF l_debug_level > 0 THEN
2879 oe_debug_pub.add( 'AFTER WF_ENGINE.CREATEPROCESS' ) ;
2880 END IF;
2881
2882 -- Set various Header Attributes
2883 l_aname(1) := 'USER_ID';
2884 l_avalue(1) := FND_GLOBAL.USER_ID;
2885 l_aname(2) := 'APPLICATION_ID';
2886 l_avalue(2) := FND_GLOBAL.RESP_APPL_ID;
2887 l_aname(3) := 'RESPONSIBILITY_ID';
2888 l_avalue(3) := FND_GLOBAL.RESP_ID;
2889 l_aname(4) := 'ORG_ID';
2890 l_avalue(4) := to_number(OE_GLOBALS.G_ORG_ID);
2891 l_aname(5) := 'TRANSACTION_NUMBER';
2892 l_avalue(5) := p_transaction_number;
2893
2894 IF p_item_type = OE_GLOBALS.G_WFI_NGO THEN
2895 l_aname(6) := 'HEADER_ID';
2896 l_avalue(6) := p_header_id;
2897 END IF;
2898
2899 wf_engine.SetItemAttrNumberArray(p_item_type
2900 , to_char(p_header_id)
2901 , l_aname
2902 , l_avalue
2903 );
2904
2905 /* get FROM_ROLE */
2906 BEGIN
2907 select user_name
2908 into l_user_name
2909 from fnd_user
2910 where user_id = FND_GLOBAL.USER_ID;
2911
2912 EXCEPTION
2913 WHEN OTHERS THEN
2914 l_user_name := null; -- do not set FROM_ROLE then
2915 END;
2916
2917 wf_engine.SetItemAttrText(p_item_type,
2918 to_char(p_header_id),
2919 'NOTIFICATION_FROM_ROLE',
2920 l_user_name);
2921
2922 IF p_item_type = OE_GLOBALS.G_WFI_NGO THEN
2923 -- if this is a negotiation flow, set some item attributes that
2924 -- only apply to negotiations
2925
2926 l_aname2(1) := 'SALES_DOCUMENT_TYPE_CODE';
2927 l_avaluetext(1) := p_sales_document_type_code;
2928 l_aname2(2) := 'SALES_DOCUMENT_TYPE';
2929 l_avaluetext(2) := l_sales_document_type;
2930
2931 -- CONTRACT_ATTACHMENT will not be set here, it may be too early, we need
2932 -- to call contract to determine the attachment, at a later time, it's done
2933 -- at Initiate_Approval
2934
2935 wf_engine.SetItemAttrTextArray(p_item_type
2936 , to_char(p_header_id)
2937 , l_aname2
2938 , l_avaluetext
2939 );
2940 END IF; -- only need to set text item attr if it is OENH
2941
2942 IF l_debug_level > 0 THEN
2943 oe_debug_pub.add( 'EXITING CREATE_HDRWORKITEMINTERNAL' ) ;
2944 END IF;
2945
2946 EXCEPTION
2947 WHEN FND_API.G_EXC_ERROR THEN
2948 RAISE;
2949 WHEN OTHERS THEN
2950 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2951 THEN
2952 OE_MSG_PUB.Add_Exc_Msg
2953 ( G_PKG_NAME
2954 , 'Create_HdrWorkItemInternal'
2955 );
2956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2957 END IF;
2958
2959 END Create_HdrWorkItemInternal;
2960
2961 PROCEDURE Set_Negotiate_Hdr_User_Key(p_header_id IN NUMBER,
2962 p_sales_document_type_code IN VARCHAR2,
2963 p_transaction_number IN NUMBER)
2964 IS
2965 l_user_key VARCHAR2(240);
2966
2967 BEGIN
2968
2969 IF p_sales_document_type_code = 'O' THEN
2970 fnd_message.set_name('ONT', 'OE_WF_QUOTE_ORDER');
2971 fnd_message.set_token('QUOTE_NUMBER', to_char(p_transaction_number));
2972 ELSIF p_sales_document_type_code = 'B' THEN
2973 fnd_message.set_name('ONT', 'OE_WF_BLANKET_ORDER');
2974 fnd_message.set_token('BLANKET_NUMBER', to_char(p_transaction_number));
2975 END IF;
2976
2977 l_user_key := substrb(fnd_message.get, 1, 240);
2978 wf_engine.SetItemUserKey( OE_GLOBALS.G_WFI_NGO
2979 , p_header_id
2980 , l_user_key);
2981 EXCEPTION
2982 WHEN OTHERS THEN
2983 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2984 THEN
2985 OE_MSG_PUB.Add_Exc_Msg
2986 ( G_PKG_NAME
2987 , 'Set_Negotiate_Hdr_User_Key'
2988 );
2989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2990 END IF;
2991 END Set_Negotiate_Hdr_User_Key;
2992
2993
2994 PROCEDURE Set_Blanket_Hdr_User_Key(p_header_id IN NUMBER,
2995 p_transaction_number IN NUMBER)
2996 IS
2997 l_user_key VARCHAR2(240);
2998
2999 BEGIN
3000
3001 fnd_message.set_name('ONT', 'OE_WF_BLANKET_ORDER');
3002 fnd_message.set_token('BLANKET_NUMBER', to_char(p_transaction_number));
3003
3004 l_user_key := substrb(fnd_message.get, 1, 240);
3005 wf_engine.SetItemUserKey( OE_GLOBALS.G_WFI_BKT
3006 , p_header_id
3007 , l_user_key);
3008 EXCEPTION
3009 WHEN OTHERS THEN
3010 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3011 THEN
3012 OE_MSG_PUB.Add_Exc_Msg
3013 ( G_PKG_NAME
3014 , 'Set_Blanket_Hdr_User_Key'
3015 );
3016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3017 END IF;
3018 END Set_Blanket_Hdr_User_Key;
3019
3020
3021 /******************************
3022 *** Set_transaction_Details **
3023 ******************************/
3024 /*
3025 */
3026 Procedure Set_transaction_Details (document_id in varchar2,
3027 display_type in varchar2,
3028 document in out NOCOPY varchar2,
3029 document_type in out NOCOPY varchar2)
3030 IS
3031
3032 l_sales_document_type_code VARCHAR2(1);
3033 l_item_key VARCHAR2(240);
3034 l_item_type VARCHAR2(8);
3035 l_blanket_flag VARCHAR2(1);
3036
3037
3038 -- HTML variables
3039 l_document VARCHAR2(32000) := '';
3040
3041 BEGIN
3042
3043 -- select ITEM_KEY, ITEM_TYPE
3044 -- into l_item_key, l_item_type
3045 -- from wf_item_activity_statuses_v
3046 -- where NOTIFICATION_ID = to_number(document_id);
3047
3048 -- replaced with this. see bug#4930449
3049 select ITEM_KEY, ITEM_TYPE
3050 into l_item_key, l_item_type
3051 from WF_ITEM_ACTIVITY_STATUSES
3052 where NOTIFICATION_ID = to_number(document_id);
3053
3054
3055 -- Get the Sales Document Type
3056 if l_item_type = 'OEBH' THEN
3057 l_blanket_flag := 'Y';
3058 else
3059 l_sales_document_type_code := wf_engine.GetItemAttrText(
3060 OE_GLOBALS.G_WFI_NGO,
3061 l_item_key,
3062 'SALES_DOCUMENT_TYPE_CODE');
3063 if l_sales_document_type_code = 'B' then
3064 l_blanket_flag := 'Y';
3065 end if;
3066
3067 end if;
3068
3069
3070 IF l_blanket_flag = 'Y' THEN
3071
3072 OE_Order_WF_Util.build_blanket_doc (p_item_type => l_item_type,
3073 p_item_key => l_item_key,
3074 p_display_type => display_type,
3075 p_x_document => l_document);
3076
3077
3078 -----------------------------
3079 -- Sales Document is Quote --
3080 -----------------------------
3081 ELSE -- l_blanket_flag = 'N'
3082
3083 OE_Order_WF_Util.build_quote_doc (p_item_type => l_item_type,
3084 p_item_key => l_item_key,
3085 p_display_type => display_type,
3086 p_x_document => l_document);
3087
3088
3089 END IF; -- l_sales_document_type_code = 'B'
3090
3091 document := l_document;
3092
3093 END Set_transaction_Details;
3094
3095
3096
3097 /*************************
3098 ** BUILD_BLANKET_DOC **
3099 *************************/
3100 procedure build_blanket_doc (p_item_type in varchar2,
3101 p_item_key in varchar2,
3102 p_display_type in varchar2,
3103 p_x_document in out NOCOPY varchar2
3104 )
3105 IS
3106 l_wf_header_attr VARCHAR2(30);
3107 l_salesrep VARCHAR2(240);
3108 l_sold_to VARCHAR2(240);
3109 l_expiration_date DATE;
3110
3111 l_transaction_id NUMBER;
3112 --l_header_id NUMBER;
3113 l_blanket_flag VARCHAR2(1);
3114
3115 l_item_type VARCHAR2(8);
3116 l_aname wf_engine.nametabtyp;
3117 l_avaluetext wf_engine.texttabtyp;
3118
3119
3120 l_transaction_number NUMBER;
3121
3122
3123 -- Blanket Header Attributes
3124 l_order_number NUMBER;
3125 l_blanket_min_amount NUMBER;
3126 l_blanket_max_amount NUMBER;
3127 l_start_date_active DATE;
3128 l_end_date_active DATE;
3129 l_credit_hold VARCHAR2(3);
3130 l_creation_date DATE;
3131 l_ship_to_address VARCHAR2(40);
3132 l_invoice_to_address VARCHAR2(40);
3133 l_payment_term VARCHAR2(15);
3134
3135
3136 -- HTML variables
3137 l_document VARCHAR2(32000) := '';
3138 l_line_msg VARCHAR2(1000);
3139 NL VARCHAR2(1) := '';
3140 i number := 0;
3141
3142 -- New Line char.
3143 NLCHAR VARCHAR2(2) := FND_GLOBAL.Newline;
3144
3145 -- fnd messages
3146 l_msg_BSA VARCHAR2(240);
3147 l_msg_creation_date VARCHAR2(240);
3148 l_msg_activation_date VARCHAR2(240);
3149 l_msg_expiration_date VARCHAR2(240);
3150 l_msg_ship_to VARCHAR2(240);
3151 l_msg_invoice_to VARCHAR2(240);
3152 l_msg_credit_holds VARCHAR2(240);
3153 l_msg_payment_term VARCHAR2(240);
3154 l_msg_min_amt_agreed VARCHAR2(240);
3155 l_msg_max_amt_agreed VARCHAR2(240);
3156 l_msg_salesperson VARCHAR2(240);
3157 l_msg_customer VARCHAR2(240);
3158 l_msg_blanket_number VARCHAR2(240);
3159
3160 BEGIN
3161
3162 l_transaction_id := to_number(p_item_key);
3163
3164 -- set fnd message titles for tables
3165 l_msg_BSA := FND_MESSAGE.Get_String('ONT', 'OE_NTF_BSA');
3166 l_msg_creation_date := FND_MESSAGE.Get_String('ONT', 'OE_NTF_CREATION_DATE');
3167 l_msg_activation_date := FND_MESSAGE.Get_String('ONT', 'OE_NTF_ACTIVATION_DATE');
3168 l_msg_expiration_date := FND_MESSAGE.Get_String('ONT', 'OE_NTF_EXPIRATION_DATE');
3169 l_msg_ship_to := FND_MESSAGE.Get_String('ONT', 'OE_NTF_SHIP_TO');
3170 l_msg_invoice_to := FND_MESSAGE.Get_String('ONT', 'OE_NTF_INVOICE_TO');
3171 l_msg_credit_holds := FND_MESSAGE.Get_String('ONT', 'OE_NTF_CREDIT_HOLDS');
3172 l_msg_payment_term := FND_MESSAGE.Get_String('ONT', 'OE_NTF_PAYMENT_TERM');
3173 l_msg_min_amt_agreed := FND_MESSAGE.Get_String('ONT', 'OE_NTF_MIN_AMT_AGREED');
3174 l_msg_max_amt_agreed := FND_MESSAGE.Get_String('ONT', 'OE_NTF_MAX_AMT_AGREED');
3175
3176
3177 -- set values
3178 select /* MOAC_SQL_CHANGE */ headers.order_number, headers.CREATION_DATE,
3179 shipto.location name,
3180 invoiceto.LOCATION name,
3181 terms.NAME,
3182 blnk_ext.BLANKET_MIN_AMOUNT, blnk_ext.BLANKET_MAX_AMOUNT,
3183 blnk_ext.START_DATE_ACTIVE, blnk_ext.END_DATE_ACTIVE
3184 INTO l_order_number, l_creation_date, l_ship_to_address, l_invoice_to_address,
3185 l_payment_term,
3186 l_blanket_min_amount, l_blanket_max_amount,
3187 l_start_date_active, l_end_date_active
3188 FROM oe_blanket_headers_all headers,
3189 oe_blanket_headers_ext blnk_ext,
3190 hz_cust_site_uses_all shipto,
3191 hz_cust_site_uses_all invoiceto,
3192 ra_terms_tl terms
3193 where headers.header_id = l_transaction_id
3194 and headers.order_number = blnk_ext.order_number(+)
3195 and headers.ship_to_org_id = shipto.site_use_id(+)
3196 and shipto.site_use_code(+) = 'SHIP_TO'
3197 and shipto.org_id(+) = headers.org_id
3198 and headers.invoice_to_org_id = invoiceto.site_use_id(+)
3199 and invoiceto.site_use_code(+) = 'BILL_TO'
3200 and invoiceto.org_id(+) = headers.org_id
3201 and headers.payment_term_id = terms.term_id(+)
3202 and terms.language(+) = userenv('LANG');
3203
3204
3205 --- ??? Check with the PM
3206 l_credit_hold := OE_Order_Wf_Util.check_credit_hold (p_hold_entity_code => 'O',
3207 p_hold_entity_id => l_transaction_id);
3208
3209
3210
3211
3212 --------------
3213 -- HTML --
3214 If p_display_type IS NULL OR p_display_type='text/html' Then
3215
3216
3217 --------------------------------------
3218 -- **HEADINGS FOR THE HEADER TABLE**--
3219 --------------------------------------
3220 l_document := l_document || NL || NL || '<!-- OE_BLANKET_HEADERS -->'|| NL || NL || '<P>';
3221 l_document := l_document || '<br><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=4>' || l_msg_BSA ||'</font><br>';
3222 l_document := l_document || '<table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>';
3223 l_document := l_document || '<table sumarry="" width=100% border=0 cellpadding=3 cellspacing=1 bgcolor=white> <tr>';
3224
3225
3226 --------------------------------------------------
3227 -- IF the WF Header Attributes are not enables ---
3228 --------------------------------------------------
3229 l_wf_header_attr := wf_core.translate('WF_HEADER_ATTR');
3230
3231 IF l_wf_header_attr <> 'Y' THEN
3232
3233 -- set fnd msg title
3234 l_msg_blanket_number := FND_MESSAGE.Get_String('ONT', 'OE_NTF_BLANKET_NUMBER');
3235 l_msg_salesperson := FND_MESSAGE.Get_String('ONT', 'OE_NTF_SALESPERSON');
3236 l_msg_customer := FND_MESSAGE.Get_String('ONT', 'OE_NTF_CUSTOMER');
3237
3238 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_blanket_number || '</font></th>';
3239 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_customer || '</font></th>';
3240 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_salesperson || '</font></th>';
3241
3242 END IF;
3243 -- WF Header Attributes ---
3244
3245 -----------------------------
3246
3247 -- **HEADINGS FOR THE BLNAKET HEADER TABLE**--
3248
3249 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_creation_date || '</font></th>';
3250 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_activation_date || '</font></th>';
3251 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_expiration_date || '</font></th>';
3252 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_ship_to || '</font></th>';
3253 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_invoice_to || '</font></th>';
3254 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_credit_holds || '</font></th>';
3255 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_payment_term || '</font></th>';
3256 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_min_amt_agreed || '</font></th>';
3257 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_max_amt_agreed || '</font></th></tr>';
3258
3259
3260
3261
3262
3263
3264 --------------------------------------------------
3265 -- IF the WF Header Attributes are not enables ---
3266 --------------------------------------------------
3267 IF l_wf_header_attr <> 'Y' THEN
3268 l_transaction_number := wf_engine.GetItemAttrNumber(
3269 p_item_type,
3270 p_item_key,
3271 'TRANSACTION_NUMBER');
3272
3273 l_salesrep := wf_engine.GetItemAttrText(
3274 p_item_type,
3275 p_item_key,
3276 'SALESPERSON');
3277 l_sold_to := wf_engine.GetItemAttrText(
3278 p_item_type,
3279 p_item_key,
3280 'SOLD_TO');
3281
3282
3283
3284 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_transaction_number) || '</font></td>';
3285 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_sold_to || '</font></td>';
3286 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_salesrep || '</font></td>';
3287
3288 END IF; --- l_wf_header_attr <> 'Y' ---
3289
3290
3291
3292 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_creation_date, 'DD-MON-YYYY') || '</font></td>';
3293 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_start_date_active, 'DD-MON-YYYY') || '</font></td>';
3294 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_end_date_active, 'DD-MON-YYYY') || '</font></td>';
3295 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_ship_to_address || '</font></td>';
3296 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_invoice_to_address || '</font></td>';
3297 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_credit_hold || '</font></td>';
3298 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_payment_term || '</font></td>';
3299 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || to_char(l_blanket_min_amount) || '</font></td>';
3300 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || to_char(l_blanket_max_amount) || '</font></td></tr>';
3301
3302 l_document := l_document || '</TABLE></TD></TR></TABLE></P>' || NL;
3303
3304
3305 ----------------
3306 --- TEXT ---
3307 elsif p_display_type = 'text/plain' then
3308
3309 l_transaction_number := wf_engine.GetItemAttrNumber(
3310 p_item_type,
3311 p_item_key,
3312 'TRANSACTION_NUMBER');
3313
3314 l_salesrep := wf_engine.GetItemAttrText(
3315 p_item_type,
3316 p_item_key,
3317 'SALESPERSON');
3318 l_sold_to := wf_engine.GetItemAttrText(
3319 p_item_type,
3320 p_item_key,
3321 'SOLD_TO');
3322
3323 -- set fnd msg title
3324 l_msg_blanket_number := FND_MESSAGE.Get_String('ONT', 'OE_NTF_BLANKET_NUMBER');
3325 l_msg_salesperson := FND_MESSAGE.Get_String('ONT', 'OE_NTF_SALESPERSON');
3326 l_msg_customer := FND_MESSAGE.Get_String('ONT', 'OE_NTF_CUSTOMER');
3327
3328
3329 l_document := l_document || l_msg_blanket_number || ': ' || TO_CHAR(l_transaction_number) || NLCHAR;
3330 l_document := l_document || l_msg_salesperson || ': ' || l_salesrep || NLCHAR;
3331 l_document := l_document || l_msg_customer || ': ' || l_sold_to || NLCHAR;
3332 l_document := l_document || l_msg_creation_date || ': ' || TO_CHAR(l_creation_date, 'DD-MON-YYYY') || NLCHAR;
3333 l_document := l_document || l_msg_activation_date || ': ' || TO_CHAR(l_start_date_active, 'DD-MON-YYYY') || NLCHAR;
3334 l_document := l_document || l_msg_expiration_date || ': ' || TO_CHAR(l_end_date_active, 'DD-MON-YYYY') || NLCHAR;
3335 l_document := l_document || l_msg_ship_to || ': ' || l_ship_to_address || NLCHAR;
3336 l_document := l_document || l_msg_invoice_to || ': ' || l_invoice_to_address || NLCHAR;
3337 l_document := l_document || l_msg_credit_holds || ': ' || l_credit_hold || NLCHAR;
3338 l_document := l_document || l_msg_payment_term || ': ' || l_payment_term || NLCHAR;
3339 l_document := l_document || l_msg_min_amt_agreed || ': ' || TO_CHAR(l_blanket_min_amount) || NLCHAR;
3340 l_document := l_document || l_msg_max_amt_agreed || ': ' || TO_CHAR(l_blanket_max_amount) || NLCHAR;
3341
3342 end if;
3343
3344 p_x_document := l_document;
3345 END build_blanket_doc;
3346
3347
3348
3349 /************************
3350 ** BUILD_QUOTE_DOC *
3351 ************************/
3352 /*
3353 */
3354 procedure build_quote_doc ( p_item_type in varchar2,
3355 p_item_key in varchar2,
3356 p_display_type in varchar2,
3357 p_x_document in out NOCOPY varchar2
3358 )
3359 IS
3360 l_wf_header_attr VARCHAR2(30);
3361 l_salesrep VARCHAR2(240);
3362 l_sold_to VARCHAR2(240);
3363 l_expiration_date DATE;
3364
3365 l_transaction_id NUMBER;
3366 l_blanket_flag VARCHAR2(1);
3367
3368 l_item_type VARCHAR2(8);
3369 l_aname wf_engine.nametabtyp;
3370 l_avaluetext wf_engine.texttabtyp;
3371
3372
3373 l_transaction_number NUMBER;
3374
3375 -- Quote Header table attributes
3376 l_creation_date DATE;
3377 l_ship_to_address VARCHAR2(40);
3378 l_invoice_to_address VARCHAR2(40);
3379 l_transactional_curr_code VARCHAR2(3);
3380 l_payment_term VARCHAR2(15);
3381 l_order_total NUMBER;
3382 l_credit_hold VARCHAR2(3);
3383 l_order_margin_percent NUMBER;
3384 l_order_margin_amount NUMBER;
3385
3386
3387 -- Quote Line Attributes
3388 l_line_margin_percent NUMBER;
3389 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
3390 l_unit_cost NUMBER;
3391 l_unit_margin_amount NUMBER;
3392 l_margin_percent NUMBER;
3393 l_line line_record; -- ?? Is it still being used somewhere
3394
3395
3396 -- HTML variables
3397 l_document VARCHAR2(32000) := '';
3398 l_line_msg VARCHAR2(1000);
3399 NL VARCHAR2(1) := '';
3400 i number := 0;
3401 l_url VARCHAR2(1000);
3402
3403 -- New Line char.
3404 NLCHAR VARCHAR2(2) := FND_GLOBAL.Newline;
3405
3406 -- FND msg titles
3407 l_msg_creation_date VARCHAR2(240);
3408 l_msg_salesperson VARCHAR2(240);
3409 l_msg_customer VARCHAR2(240);
3410 l_msg_quote VARCHAR2(240);
3411 l_msg_total VARCHAR2(240);
3412 l_msg_UOM VARCHAR2(240);
3413 l_msg_item VARCHAR2(240);
3414 l_msg_quantity VARCHAR2(240);
3415 l_msg_currency VARCHAR2(240);
3416 l_msg_unit_selling_price VARCHAR2(240);
3417 l_msg_margin_percent VARCHAR2(240);
3418 l_msg_line_details VARCHAR2(240);
3419 l_msg_expiration_date VARCHAR2(240);
3420 l_msg_ship_to VARCHAR2(240);
3421 l_msg_invoice_to VARCHAR2(240);
3422 l_msg_credit_holds VARCHAR2(240);
3423 l_msg_payment_term VARCHAR2(240);
3424 l_msg_quote_number VARCHAR2(240);
3425 l_msg_line_number VARCHAR2(240);
3426 l_msg_quote_details VARCHAR2(240);
3427 l_msg_first_five_lines VARCHAR2(240);
3428
3429 -- Cursor to build line table
3430 CURSOR line_cursor(v_header_id NUMBER) IS
3431 SELECT ol.line_number || '.' ||ol.shipment_number ||'.' ||
3432 ol.option_number ||'.'|| ol.component_number,
3433 msi.concatenated_segments,
3434 ol.order_quantity_uom,
3435 ol.ordered_quantity,
3436 ol.unit_selling_price,
3437 ol.ordered_quantity * ol.unit_selling_price,
3438 ol.line_id, --- the following column needed for line_margin API
3439 ol.inventory_item_id,
3440 ol.item_type_code,
3441 ol.open_flag,
3442 ol.SHIPPED_QUANTITY,
3443 ol.ORDERED_QUANTITY,
3444 ol.SOURCE_TYPE_CODE,
3445 ol.SHIP_FROM_ORG_ID,
3446 ol.PROJECT_ID,
3447 ol.ACTUAL_SHIPMENT_DATE,
3448 ol.FULFILLMENT_DATE
3449 FROM oe_order_lines_all ol,
3450 mtl_system_items_kfv msi
3451 WHERE ol.header_id = v_header_id
3452 AND ol.inventory_item_id = msi.inventory_item_id
3453 AND msi.organization_id = nvl(ol.ship_from_org_id,
3454 oe_sys_parameters.Value('MASTER_ORGANIZATION_ID')) -- Bug 6215694
3455 ORDER BY line_number, shipment_number, option_number, component_number;
3456
3457 l_prec_inited BOOLEAN := FALSE; -- Bug 6275663
3458 l_org_id NUMBER;
3459
3460 BEGIN
3461
3462 l_transaction_id := to_number(p_item_key);
3463 select org_id into l_org_id from oe_order_headers_all where header_id = l_transaction_id;
3464 MO_GLOBAL.set_policy_context('S', l_org_id);
3465
3466 -- Bug 6275663
3467 IF ( nvl(Oe_Order_Util.G_Precision,0) = 0 ) THEN
3468 l_prec_inited := Oe_Order_Util.Get_Precision (p_header_id => l_transaction_id);
3469 END IF;
3470
3471 -- set fnd message titles for tables
3472 l_msg_quote := FND_MESSAGE.Get_String('ONT', 'OE_NTF_QUOTE');
3473 l_msg_creation_date := FND_MESSAGE.Get_String('ONT', 'OE_NTF_CREATION_DATE');
3474 l_msg_margin_percent := FND_MESSAGE.Get_String('ONT', 'OE_NTF_MARGIN_PERCENT');
3475 l_msg_ship_to := FND_MESSAGE.Get_String('ONT', 'OE_NTF_SHIP_TO');
3476 l_msg_invoice_to := FND_MESSAGE.Get_String('ONT', 'OE_NTF_INVOICE_TO');
3477 l_msg_credit_holds := FND_MESSAGE.Get_String('ONT', 'OE_NTF_CREDIT_HOLDS');
3478 l_msg_payment_term := FND_MESSAGE.Get_String('ONT', 'OE_NTF_PAYMENT_TERM');
3479 l_msg_total := FND_MESSAGE.Get_String('ONT', 'OE_NTF_TOTAL');
3480 l_msg_line_details := FND_MESSAGE.Get_String('ONT', 'OE_NTF_LINE_DETAILS');
3481 l_msg_item := FND_MESSAGE.Get_String('ONT', 'OE_NTF_ITEM');
3482 l_msg_uom := FND_MESSAGE.Get_String('ONT', 'OE_NTF_UOM');
3483 l_msg_quantity := FND_MESSAGE.Get_String('ONT', 'OE_NTF_QUANTITY');
3484 l_msg_currency := FND_MESSAGE.Get_STring('ONT', 'OE_NTF_CURRENCY');
3485 l_msg_unit_selling_price := FND_MESSAGE.Get_String('ONT', 'OE_NTF_UNIT_SELLING_PRICE');
3486 l_msg_line_number := FND_MESSAGE.Get_String('ONT', 'OE_NTF_LINE_NUMBER');
3487 l_msg_quote_details := FND_MESSAGE.Get_String('ONT', 'OE_NTF_ADL_QUOTE_DETAILS');
3488 l_msg_first_five_lines := FND_MESSAGE.Get_String('ONT', 'OE_NTF_FIRST_FIVE_LINES');
3489 -------------------------
3490
3491 -- set value
3492 -- Build the header attribute values
3493 l_order_total := OE_OE_TOTALS_SUMMARY.PRT_ORDER_TOTAL(l_transaction_id);
3494
3495 --- ???? Check with PM
3496 l_credit_hold := OE_Order_Wf_Util.check_credit_hold (p_hold_entity_code => 'O',
3497 p_hold_entity_id => l_transaction_id);
3498 OE_MARGIN_PVT.Get_Order_Margin ( p_header_id => l_transaction_id,
3499 x_order_margin_percent => l_order_margin_percent,
3500 x_order_margin_amount => l_order_margin_amount);
3501
3502 l_order_margin_percent := Round(l_order_margin_percent, Oe_Order_Util.G_Precision); -- Bug 6275663
3503
3504
3505
3506 select /* MOAC_SQL_CHANGE */ headers.CREATION_DATE,shipto.name,
3507 invoiceto.LOCATION name,
3508 headers.TRANSACTIONAL_CURR_CODE, terms.NAME
3509 INTO l_creation_date, l_ship_to_address, l_invoice_to_address,
3510 l_transactional_curr_code, l_payment_term
3511 FROM oe_order_headers_all headers,
3512 oe_ship_to_orgs_v shipto,
3513 HZ_CUST_SITE_USES_ALL invoiceto,
3514 ra_terms terms
3515 where headers.header_id = l_transaction_id
3516 and headers.SHIP_TO_ORG_ID = shipto.organization_id(+)
3517 and headers.INVOICE_TO_ORG_ID = invoiceto.SITE_USE_ID(+)
3518 and invoiceto.SITE_USE_CODE(+) = 'BILL_TO'
3519 and invoiceto.ORG_ID(+) = headers.org_id
3520 and headers.payment_term_id = terms.term_id(+);
3521
3522
3523
3524 -- DISPLAY_TYPE = HTML --
3525 IF p_display_type IS NULL OR p_display_type='text/html' THEN
3526
3527 --------------------------------------
3528 -- **HEADINGS FOR THE HEADER TABLE**--
3529 --------------------------------------
3530 l_document := l_document || NL || NL || '<!-- OE_HEADERS_DETAILS -->'|| NL || NL || '<P>';
3531 l_document := l_document || '<br><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=4>' || l_msg_quote || '</font><br>';
3532 l_document := l_document || '<table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>';
3533 l_document := l_document || '<table sumarry="" width=100% border=0 cellpadding=3 cellspacing=1 bgcolor=white> <tr>';
3534
3535
3536 --------------------------------------------------
3537 -- IF the WF Header Attributes are not enables ---
3538 --------------------------------------------------
3539 l_wf_header_attr := wf_core.translate('WF_HEADER_ATTR');
3540
3541 IF l_wf_header_attr <> 'Y' THEN
3542
3543 -- set FND msg title
3544 l_msg_quote_number := FND_MESSAGE.Get_String('ONT', 'OE_NTF_QUOTE_NUMBER');
3545 l_msg_salesperson := FND_MESSAGE.Get_String('ONT', 'OE_NTF_SALESPERSON');
3546 l_msg_customer := FND_MESSAGE.Get_String('ONT', 'OE_NTF_CUSTOMER');
3547 l_msg_expiration_date := FND_MESSAGE.Get_String('ONT', 'OE_NTF_EXPIRATION_DATE');
3548
3549 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_quote_number || '</font></th>';
3550 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_customer || '</font></th>';
3551 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_expiration_date || '</font></th>';
3552 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_salesperson || '</font></th>';
3553
3554 END IF;
3555 -- WF Header Attributes ---
3556 -----------------------------
3557
3558
3559
3560 -- **HEADINGS FOR THE HEADER TABLE**--
3561
3562 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_creation_date || '</font></th>';
3563 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_ship_to || '</font></th>';
3564 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_invoice_to || '</font></th>';
3565 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_credit_holds || '</font></th>';
3566 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_currency || '</font></th>';
3567 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_total || '</font></th>';
3568 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_margin_percent || '</font></th>';
3569 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_payment_term || '</font></th></tr>';
3570
3571
3572 --------------------------------------------------
3573 -- IF the WF Header Attributes are not enables ---
3574 --------------------------------------------------
3575 IF l_wf_header_attr <> 'Y' THEN
3576 l_transaction_number := wf_engine.GetItemAttrNumber(
3577 p_item_type,
3578 p_item_key,
3579 'TRANSACTION_NUMBER');
3580 l_salesrep := wf_engine.GetItemAttrText(
3581 p_item_type,
3582 p_item_key,
3583 'SALESPERSON');
3584 l_sold_to := wf_engine.GetItemAttrText(
3585 p_item_type,
3586 p_item_key,
3587 'SOLD_TO');
3588 l_expiration_date := wf_engine.GetItemAttrText(
3589 p_item_type,
3590 p_item_key,
3591 'EXPIRATION_DATE');
3592
3593 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_transaction_number) || '</font></td>';
3594 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_sold_to || '</font></td>';
3595 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_expiration_date, 'DD-MON-YYYY') || '</font></td>';
3596 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_salesrep || '</font></td>';
3597
3598
3599
3600 END IF; -- l_wf_header_attr <> 'Y' --
3601
3602
3603 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_creation_date, 'DD-MON-YYYY') || '</font></td>';
3604 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_ship_to_address || '</font></td>';
3605 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_invoice_to_address || '</font></td>';
3606 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_credit_hold || '</font></td>';
3607 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_transactional_curr_code || '</font></td>';
3608 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_order_total || '</font></td>';
3609
3610 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || to_char(l_order_margin_percent) || '</font></td>';
3611 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_payment_term || '</font></td></tr>';
3612
3613
3614 l_document := l_document || '</TABLE></TD></TR></TABLE></P>' || NL;
3615
3616
3617 -----------------------------------
3618 -- Build the Lines Detail Table --
3619 -----------------------------------
3620 l_document := l_document || NL || NL || '<!-- OE_LINE_DETAILS -->'|| NL || NL || '<P>';
3621 l_document := l_document || '<br><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=4>' || l_msg_line_details ||'</font><br>';
3622
3623 l_document := l_document || l_msg_first_five_lines;
3624 l_document := l_document || '<br><table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>';
3625 l_document := l_document || '<table sumarry="" width=100% border=0 cellpadding=3 cellspacing=1 bgcolor=white> <tr>';
3626
3627 -- **HEADINGS FOR THE LINE TABLE**--
3628 l_document := l_document || '<th scope=col width=10% align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_line_number || '</font></th>';
3629 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_item || '</font></th>';
3630 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_uom || '</font></th>';
3631 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_quantity || '</font></th>';
3632 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_unit_selling_price || '</font></th>';
3633 l_document := l_document || '<th scope=col align=LEFT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>' || l_msg_margin_percent || '</font></th></tr>';
3634
3635 --** BUILD THE LINE TABLE** --
3636 -- Line detail columns -> Items, UOM, Selling Price
3637
3638
3639
3640 OPEN line_cursor(l_transaction_id);
3641 LOOP
3642 FETCH line_cursor into l_line;
3643 EXIT WHEN line_cursor%NOTFOUND;
3644 i := i + 1;
3645 l_line_rec.header_id := l_transaction_id; --bug 5210735
3646 l_line_rec.line_id := l_line.line_id;
3647 l_line_rec.inventory_item_id := l_line.inventory_item_id;
3648 l_line_rec.item_type_code := l_line.item_type_code;
3649 l_line_rec.open_flag := l_line.open_flag;
3650 l_line_rec.SHIPPED_QUANTITY := l_line.SHIPPED_QUANTITY;
3651 l_line_rec.ORDERED_QUANTITY := l_line.ORDERED_QUANTITY;
3652 l_line_rec.SOURCE_TYPE_CODE := l_line.SOURCE_TYPE_CODE;
3653 l_line_rec.SOURCE_TYPE_CODE := l_line.SOURCE_TYPE_CODE;
3654 l_line_rec.SHIP_FROM_ORG_ID := l_line.SHIP_FROM_ORG_ID;
3655 l_line_rec.PROJECT_ID := l_line.PROJECT_ID;
3656 l_line_rec.ACTUAL_SHIPMENT_DATE := l_line.ACTUAL_SHIPMENT_DATE;
3657 l_line_rec.FULFILLMENT_DATE := l_line.FULFILLMENT_DATE;
3658 l_line_rec.unit_selling_price := l_line.unit_selling_price; --bug 5155086
3659
3660 OE_MARGIN_PVT.Get_Line_Margin ( p_line_rec => l_line_rec,
3661 x_unit_cost => l_unit_cost,
3662 x_unit_margin_amount => l_unit_margin_amount,
3663 x_margin_percent => l_line_margin_percent);
3664
3665 l_line_margin_percent := Round(l_line_margin_percent, Oe_Order_Util.G_Precision); -- Bug 6275663
3666
3667
3668 l_document := l_document || '<tr><td align=CENTER valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || nvl(l_line.line_num, '&' || 'nbsp') || '</font></td>';
3669 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || nvl(l_line.item, '&' || 'nbsp') || '</font></td>';
3670 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || nvl(l_line.uom, '&'||'nbsp') || '</font></td>';
3671 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || nvl(to_char(l_line.quantity), '&' ||'nbsp') || '</font></td>';
3672 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_line.unit_selling_price) || '</font></td>';
3673 l_document := l_document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif" size=2>' || TO_CHAR(l_line_margin_percent) || '</font></td>';
3674
3675 exit when i = 5;
3676 end loop;
3677
3678 close line_cursor;
3679
3680 l_document := l_document || '</TABLE></TD></TR></TABLE></P>' || NL;
3681
3682 -- show the URL link to OIP
3683 l_url := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/')||'/OA_HTML/OA.jsp?akRegionCode=ORDER_DETAILS_PAGE' || '&' || 'akRegionApplicationId=660' || '&' || 'HeaderId=' || p_item_key;
3684
3685 l_document := l_document ||'<TABLE width="100%" SUMMARY=""><TR> <TD align=right><A HREF="' ||
3686 l_url || '" TARGET="_top">' || l_msg_quote_details || '</A></TD></TR></TABLE>'
3687 || NL;
3688
3689 -------------------------
3690 -- DISPLAY_TYPE = TEXT --
3691 ELSIF p_display_type = 'text/plain' THEN
3692
3693 -- set FND msg title
3694 l_msg_quote_number := FND_MESSAGE.Get_String('ONT', 'OE_NTF_QUOTE_NUMBER');
3695 l_msg_salesperson := FND_MESSAGE.Get_String('ONT', 'OE_NTF_SALESPERSON');
3696 l_msg_customer := FND_MESSAGE.Get_String('ONT', 'OE_NTF_CUSTOMER');
3697 l_msg_expiration_date := FND_MESSAGE.Get_String('ONT', 'OE_NTF_EXPIRATION_DATE');
3698
3699 l_transaction_number := wf_engine.GetItemAttrNumber(
3700 p_item_type,
3701 p_item_key,
3702 'TRANSACTION_NUMBER');
3703
3704 l_salesrep := wf_engine.GetItemAttrText(
3705 p_item_type,
3706 p_item_key,
3707 'SALESPERSON');
3708 l_sold_to := wf_engine.GetItemAttrText(
3709 p_item_type,
3710 p_item_key,
3711 'SOLD_TO');
3712 l_expiration_date := wf_engine.GetItemAttrText(
3713 p_item_type,
3714 p_item_key,
3715 'EXPIRATION_DATE');
3716
3717
3718 l_document := l_document || l_msg_quote_number || l_transaction_number || NLCHAR;
3719 l_document := l_document || l_msg_customer || l_sold_to || NLCHAR;
3720 l_document := l_document || l_msg_expiration_date|| TO_CHAR(l_expiration_date, 'DD-MON-YYYY') || NLCHAR;
3721 l_document := l_document || l_msg_salesperson || l_salesrep || NLCHAR;
3722 l_document := l_document || l_msg_creation_date || TO_CHAR(l_creation_date, 'DD-MON-YYYY') || NLCHAR;
3723 l_document := l_document || l_msg_ship_to || l_ship_to_address || NLCHAR;
3724 l_document := l_document || l_msg_invoice_to || l_invoice_to_address || NLCHAR;
3725 l_document := l_document || l_msg_credit_holds || l_credit_hold || NLCHAR;
3726 l_document := l_document || l_msg_currency || l_transactional_curr_code || NLCHAR;
3727 l_document := l_document || l_msg_total || l_order_total || NLCHAR;
3728 l_document := l_document || l_msg_total || to_char(l_order_margin_percent) || NLCHAR;
3729 l_document := l_document || l_msg_payment_term || l_payment_term || NLCHAR;
3730
3731 -- ?? fix the nbsp
3732 /*
3733 l_document := l_document || NLCHAR || NLCHAR || '<!-- OE_LINES_DETAILS -->' || NLCHAR || NLCHAR;
3734 l_document := l_document || 'Line Number:' || nvl(l_line.line_num, '&' || 'nbsp') || NLCHAR;
3735 l_document := l_document || 'Item:' || nvl(l_line.item, '&' || 'nbsp') || NLCHAR;
3736 l_document := l_document || 'UOM:' || nvl(l_line.uom, '&'||'nbsp') || NLCHAR;
3737 l_document := l_document || 'Quantity:' || nvl(to_char(l_line.quantity), '&' ||'nbsp') || NLCHAR;
3738 l_document := l_document || 'Unit Price:' || TO_CHAR(l_line.unit_selling_price) || NLCHAR;
3739 l_document := l_document || 'Margin:' || TO_CHAR(l_line_margin_percent) || NLCHAR;
3740 */
3741
3742 END IF;
3743
3744
3745
3746 p_x_document := l_document;
3747 END build_quote_doc;
3748
3749
3750
3751
3752 /* todo:Move it to the Holds package later */
3753
3754 /*************************
3755 ** CHECK_CREDIT_HOLD *
3756 *************************/
3757
3758
3759 function check_credit_hold (p_hold_entity_code IN varchar2,
3760 p_hold_entity_id IN number
3761 )
3762 RETURN VARCHAR2
3763 IS
3764 l_result_out VARCHAR2(30);
3765
3766 BEGIN
3767
3768 -- Initialize result to TRUE i.e. holds are found
3769 l_result_out := 'Y';
3770
3771
3772 BEGIN
3773 select 'Y'
3774 into l_result_out
3775 from oe_hold_sources HS,
3776 oe_hold_definitions h
3777 where HS.hold_entity_code = p_hold_entity_code
3778 and HS.hold_entity_id = p_hold_entity_id
3779 and HS.hold_id = 1
3780 and HS.released_flag = 'N'
3781 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >= ROUND( SYSDATE )
3782 AND hs.hold_id = h.hold_id
3783 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
3784 AND NVL( H.END_DATE_ACTIVE, SYSDATE );
3785
3786 EXCEPTION
3787 WHEN NO_DATA_FOUND THEN
3788 l_result_out := 'N';
3789 --IF l_debug_level > 0 THEN
3790 -- oe_debug_pub.add( 'NO HOLDS FOUND FOR HEADER ID: ' || P_HDR_ID ) ;
3791 --END IF;
3792 WHEN TOO_MANY_ROWS THEN
3793 null;
3794 END;
3795
3796 RETURN l_result_out;
3797
3798 END check_credit_hold;
3799
3800 PROCEDURE Complete_eligible_and_Book
3801 ( p_api_version_number IN NUMBER
3802 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3803 , p_header_id IN NUMBER
3804 , x_return_status OUT NOCOPY VARCHAR2
3805 , x_msg_count OUT NOCOPY NUMBER
3806 , x_msg_data OUT NOCOPY VARCHAR2
3807 )
3808 IS
3809 l_api_name CONSTANT VARCHAR2(30) := 'Complete_eligible_and_Book';
3810 -- Use local variables instead of literals.
3811 l_wfeng_status varchar2(24) := 'WFENG_STATUS';
3812 l_root varchar2(24) := 'ROOT';
3813 l_negotiation varchar2(1) := 'N';
3814 l_oenh varchar2(8) := 'OENH';
3815 l_oebh varchar2(8) := 'OEBH';
3816 l_oeol varchar2(8) := 'OEOL';
3817 l_oeoh varchar2(8) := 'OEOH';
3818 l_standard_block varchar2(128) := 'OE_STANDARD_WF.STANDARD_BLOCK';
3819 l_eng_notified varchar2(8) := 'NOTIFIED';
3820 l_eng_deferred varchar2(8) := 'DEFERRED';
3821 l_retval VARCHAR2(30);
3822 l_activity VARCHAR2(30);
3823 l_book_eligible VARCHAR2(1);
3824 l_book_deferred VARCHAR2(1);
3825 l_booked_flag VARCHAR2(1);
3826 l_flow_status_code VARCHAR2(30);
3827 l_flow_status VARCHAR2(256);
3828 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3829
3830 CURSOR c_eligible_activity IS
3831 select pa.activity_name
3832 from wf_item_activity_statuses s,
3833 wf_process_activities pa, wf_lookups l,
3834 wf_activities_vl act
3835 where s.activity_status = l.lookup_code
3836 and l.lookup_type = l_wfeng_status
3837 and s.process_activity = pa.instance_id
3838 and pa.activity_item_type = act.item_type
3839 and pa.activity_name = act.name
3840 and pa.process_name <> l_root
3841 and act.version = (select max(version)
3842 from wf_activities_vl act2
3843 where act.item_type = act2.item_type
3844 and act.name = act2.name)
3845 and upper(s.activity_status) = l_eng_notified
3846 and s.item_type = l_oenh
3847 and s.item_key = p_header_id
3848 and act.function = l_standard_block;
3849
3850 CURSOR book_eligible IS
3851 SELECT 'Y'
3852 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
3853 , WF_PROCESS_ACTIVITIES WPA
3854 WHERE WIAS.item_type = 'OEOH'
3855 AND WIAS.item_key = p_header_id
3856 AND WIAS.activity_status = 'NOTIFIED'
3857 AND WPA.activity_name = 'BOOK_ELIGIBLE'
3858 AND WPA.instance_id = WIAS.process_activity;
3859
3860 CURSOR book_deferred IS
3861 SELECT 'Y'
3862 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
3863 , WF_PROCESS_ACTIVITIES WPA
3864 WHERE WIAS.item_type = 'OEOH'
3865 AND WIAS.item_key = p_header_id
3866 AND WIAS.activity_status = 'DEFERRED'
3867 AND WPA.activity_name = 'BOOK_DEFER'
3868 AND WPA.instance_id = WIAS.process_activity;
3869
3870 BEGIN
3871 IF l_debug_level > 0 THEN
3872 oe_debug_pub.add('In Complete_eligible_and_Book, header_id' || p_header_id);
3873 END IF;
3874
3875
3876 open c_eligible_activity;
3877 FETCH c_eligible_activity into l_activity;
3878 IF c_eligible_activity%NOTFOUND THEN
3879 oe_debug_pub.add('c_eligible_activity NOT FOUND');
3880 fnd_message.set_name('ONT','OE_NO_ELIGIBLE_ACTIVITIES');
3881 OE_MSG_PUB.ADD;
3882 ELSE
3883 close c_eligible_activity;
3884 IF l_debug_level > 0 THEN
3885 oe_debug_pub.add('ELIGIBLE Activity: ' || l_activity);
3886 END IF;
3887
3888 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_NGO,
3889 to_char(p_header_id), l_activity, l_retval);
3890 -- The order could have been booked already becuase the booking was synchronous.
3891 select booked_flag, flow_status_code
3892 into l_booked_flag, l_flow_status_code
3893 from oe_order_headers
3894 where header_id = p_header_id;
3895 IF l_debug_level > 0 THEN
3896 oe_debug_pub.add('l_booked_flag: ' || l_booked_flag);
3897 END IF;
3898
3899 IF l_booked_flag = 'N' THEN
3900 -- If the order is book eligigble then try to book it also.
3901 OPEN book_eligible;
3902 FETCH book_eligible INTO l_book_eligible;
3903 IF (book_eligible%NOTFOUND) THEN
3904 IF l_debug_level > 0 THEN
3905 oe_debug_pub.add( 'BOOKING NOT ELIGIBLE' ) ;
3906 END IF;
3907 -- Booking can be a high cost activity and may be deferred
3908 OPEN book_deferred;
3909 FETCH book_deferred INTO l_book_deferred;
3910 IF (book_deferred%FOUND) THEN
3911 IF l_debug_level > 0 THEN
3912 oe_debug_pub.add('BOOKING IS DEFERRED' );
3913 END IF;
3914 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_BOOK_DEFERRED');
3915 OE_MSG_PUB.ADD;
3916 CLOSE book_deferred;
3917 ELSE
3918 select MEANING
3919 into l_flow_status
3920 from oe_lookups
3921 WHERE lookup_type= 'FLOW_STATUS'
3922 AND lookup_code = l_flow_status_code
3923 AND enabled_flag = 'Y'
3924 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
3925 AND NVL(END_DATE_ACTIVE, SYSDATE);
3926
3927 FND_MESSAGE.SET_NAME('ONT','OE_QUOTE_NOT_BOOKED');
3928 FND_MESSAGE.SET_TOKEN('FLOW_STATUS', l_flow_status);
3929 OE_MSG_PUB.ADD;
3930 END IF; -- book_deferred%FOUND
3931 ELSE -- book_eligible%NOTFOUND
3932 IF l_debug_level > 0 THEN
3933 oe_debug_pub.add('Calling OE_Order_Book_Util.Complete_Book_Eligible' ) ;
3934 END IF;
3935
3936 OE_Order_Book_Util.Complete_Book_Eligible
3937 ( p_api_version_number => 1.0
3938 , p_header_id => p_header_id
3939 , x_return_status => x_return_status
3940 , x_msg_count => x_msg_count
3941 , x_msg_data => x_msg_data);
3942
3943 END IF; -- book_eligible%NOTFOUND
3944 END IF; -- l_booked_flag = 'N'
3945 END IF; -- l_activity_Cur%NOTFOUND
3946
3947
3948 EXCEPTION
3949 WHEN FND_API.G_EXC_ERROR THEN
3950 x_return_status := FND_API.G_RET_STS_ERROR;
3951 IF (book_eligible%ISOPEN) THEN
3952 CLOSE book_eligible;
3953 END IF;
3954 OE_MSG_PUB.Count_And_Get
3955 ( p_count => x_msg_count
3956 , p_data => x_msg_data
3957 );
3958 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
3959 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3961 IF (book_eligible%ISOPEN) THEN
3962 CLOSE book_eligible;
3963 END IF;
3964 OE_MSG_PUB.Count_And_Get
3965 ( p_count => x_msg_count
3966 , p_data => x_msg_data
3967 );
3968 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
3969 WHEN OTHERS THEN
3970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3971 IF (book_eligible%ISOPEN) THEN
3972 CLOSE book_eligible;
3973 END IF;
3974 IF OE_MSG_PUB.Check_Msg_Level
3975 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3976 THEN
3977 OE_MSG_PUB.Add_Exc_Msg
3978 ( G_PKG_NAME
3979 , l_api_name
3980 );
3981 END IF;
3982 OE_MSG_PUB.Count_And_Get
3983 ( p_count => x_msg_count
3984 , p_data => x_msg_data
3985 );
3986 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
3987
3988 end Complete_eligible_and_Book;
3989
3990
3991
3992 END OE_Order_Wf_Util;