[Home] [Help]
PACKAGE BODY: APPS.OE_OEOL_SCH
Source
1 PACKAGE BODY OE_OEOL_SCH AS
2 /* $Header: OEXWSCHB.pls 120.19.12020000.7 2013/04/09 07:59:07 spothula ship $ */
3
4 PROCEDURE Bulk_Mode_Copy_Sch_Attribs
5 (p_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type);
6
7 -- Chache Values
8 sch_cached_line_id NUMBER ;
9 sch_cached_sch_status_code VARCHAR2(30);
10 -- Bug 3083995
11 sch_elg_cached_line_id NUMBER;
12 sch_cached_elg_status VARCHAR2(8);
13 sch_cached_source_type_code VARCHAR2(30); -- Added for bug 5880264
14 --
15 g_skip_check BOOLEAN :=FALSE; -- 3565621
16 g_top_model_line_id NUMBER := NULL; -- 3565621
17
18
19 /*---------------------------------------------
20 Function : Is_Scheduling_Eligible
21 ---------------------------------------------*/
22
23 FUNCTION Is_Scheduling_Eligible(p_line_id IN NUMBER)
24 RETURN BOOLEAN
25 IS
26 l_activity_status VARCHAR2(8) := null;
27 --
28 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
29 --
30 BEGIN
31 IF l_debug_level > 0 THEN
32 oe_debug_pub.add( 'ENTERING IS_SCHEDULING_ELIGIBLE: ' || P_LINE_ID , 1 ) ;
33 END IF;
34 IF NOT OE_GLOBALS.Equal(p_line_id,
35 sch_elg_cached_line_id)
36 THEN
37 SELECT ACTIVITY_STATUS
38 INTO l_activity_status
39 FROM wf_item_activity_statuses wias, wf_process_activities wpa
40 WHERE wias.item_type = 'OEOL'
41 AND wias.item_key = to_char(p_line_id)
42 AND wias.process_activity = wpa.instance_id
43 AND wpa.ACTIVITY_ITEM_TYPE = 'OEOL'
44 AND wpa.activity_name = 'SCHEDULING_ELIGIBLE'
45 AND wias.activity_status = 'NOTIFIED';
46
47 sch_elg_cached_line_id := p_line_id;
48 sch_cached_elg_status := l_activity_status;
49 END IF;
50
51 IF l_debug_level > 0 THEN
52 oe_debug_pub.add( 'EXITING IS_SCHEDULING_ELIGIBLE ' , 1 ) ;
53 END IF;
54
55 IF sch_cached_elg_status = 'NOTIFIED'
56 THEN
57 IF l_debug_level > 0 THEN
58 oe_debug_pub.add( 'Line is in schedule Eligible stage ' , 1 ) ;
59 END IF;
60 RETURN TRUE;
61 ELSE
62 IF l_debug_level > 0 THEN
63 oe_debug_pub.add( 'Line is not in schedule Eligible stage' , 1 ) ;
64 END IF;
65 RETURN FALSE;
66 END IF;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 IF l_debug_level > 0 THEN
71 oe_debug_pub.add( 'IN EXCEPTION OF IS_SCHEDULING_ELIGIBLE' , 1 ) ;
72 END IF;
73 RETURN FALSE;
74 END Is_Scheduling_Eligible;
75
76 /*---------------------------------------------
77 Function : Is_Line_Scheduled
78 ---------------------------------------------*/
79
80 FUNCTION Is_Line_Scheduled(p_line_id IN NUMBER)
81 RETURN BOOLEAN
82 IS
83 l_schedule_status_code VARCHAR2(30) := null;
84 l_source_type_code VARCHAR2(30) := null; -- Added for bug 5880264
85 --
86 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
87 --
88 BEGIN
89
90 IF l_debug_level > 0 THEN
91 oe_debug_pub.add( 'ENTERING IS_LINE_SCHEDULED: ' || P_LINE_ID , 1 ) ;
92 END IF;
93 IF NOT OE_GLOBALS.Equal(p_line_id,
94 sch_cached_line_id)
95 THEN
96
97 -- Added source_type_code in the below query for bug 5880264
98 SELECT schedule_status_code, source_type_code
99 INTO l_schedule_status_code, l_source_type_code
100 FROM oe_order_lines_all
101 WHERE line_id = p_line_id;
102
103
104 sch_cached_line_id := p_line_id;
105 sch_cached_sch_status_code := l_schedule_status_code;
106 sch_cached_source_type_code := l_source_type_code; -- Added for bug 5880264
107 END IF;
108
109 IF l_debug_level > 0 THEN
110 oe_debug_pub.add( 'EXITING IS_LINE_SCHEDULED ' , 1 ) ;
111 END IF;
112
113 -- Added source_type_code condition in the below IF for bug 5880264
114 IF sch_cached_sch_status_code IS NOT NULL OR sch_cached_source_type_code = 'EXTERNAL'
115 THEN
116 IF l_debug_level > 0 THEN
117 oe_debug_pub.add( 'Line is scheduled ' , 1 ) ;
118 END IF;
119 RETURN TRUE;
120 ELSE
121 IF l_debug_level > 0 THEN
122 oe_debug_pub.add( 'Line is not scheduled ' , 1 ) ;
123 END IF;
124 RETURN FALSE;
125 END IF;
126
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 IF l_debug_level > 0 THEN
131 oe_debug_pub.add( 'IN EXCEPTION OF IS_LINE_SCHEDULED' , 1 ) ;
132 END IF;
133 RETURN FALSE;
134
135 END Is_Line_Scheduled;
136
137 /*--------------------------------------------------------
138 Procedure Process_Child_Lines
139
140 Modfied the signature of the procedure to fix bug 3319120
141 --------------------------------------------------------*/
142
143 PROCEDURE Process_Child_Lines(p_line_id IN NUMBER,
144 p_top_model_line_id IN NUMBER,
145 p_ato_line_id IN NUMBER,
146 p_item_type_code IN VARCHAR2,
147 p_ship_model_complete_flag IN VARCHAR2)
148 IS
149 TYPE lines_ref_type IS REF CURSOR;
150 l_ref_cur_line_id lines_ref_type;
151 l_stmt VARCHAR2(1000);
152 l_where_clause VARCHAR2(200) := NULL;
153 l_line_id NUMBER;
154 l_model_id NUMBER;
155 --
156 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
157 --
158 BEGIN
159 IF l_debug_level > 0 THEN
160 oe_debug_pub.add( 'ENTERING PROCESS_CHILD_LINES',0.5); -- debug level changed to 0.5 for bug 13435459
161 END IF;
162
163 IF NVL(p_ship_model_complete_flag,'N') = 'Y'
164 AND p_top_model_line_id = p_line_id
165 THEN -- SMC
166 l_where_clause :=' WHERE ola.top_model_line_id = :P1';
167 l_model_id :=p_top_model_line_id;
168 --fix for 3565621
169 g_top_model_line_id := p_top_model_line_id;
170
171 ELSIF p_ato_line_id IS NOT NULL
172 AND p_ato_line_id = p_line_id
173 -- fix for 3565621
174 AND ( g_top_model_line_id IS NULL OR
175 ( NOT OE_Globals.Equal(p_top_model_line_id,g_top_model_line_id)
176 )
177 )
178 THEN
179 l_where_clause :=' WHERE ola.ato_line_id = :P1';
180 l_model_id :=p_ato_line_id;
181
182 ELSIF NVL(p_ship_model_complete_flag,'N') = 'N'
183 AND p_item_type_code IN('MODEL','CLASS','KIT')
184 THEN
185 l_where_clause :=' WHERE ola.link_to_line_id = :P1'||
186 ' AND ola.item_type_code = '||'''INCLUDED''';
187 l_model_id := p_line_id;
188 END IF;
189 IF l_where_clause IS NOT NULL
190 THEN
191 l_stmt :=' SELECT ola.line_id '||
192 ' FROM oe_order_lines_all ola, wf_item_activity_statuses wias, wf_process_activities wpa '||
193 l_where_clause||
194 ' AND wias.item_key = to_char(ola.line_id)'||
195 ' AND wias.item_type = '||'''OEOL'''||
196 ' AND wias.process_activity = wpa.instance_id'||
197 ' AND wpa.ACTIVITY_ITEM_TYPE = '||'''OEOL'''||
198 ' AND wpa.activity_name = '||'''SCHEDULING_ELIGIBLE'''||
199 ' AND wias.activity_status = '||'''NOTIFIED''';
200 OPEN l_ref_cur_line_id FOR l_stmt USING l_model_id;
201 LOOP
202 FETCH l_ref_cur_line_id into l_line_id;
203 EXIT WHEN l_ref_cur_line_id%NOTFOUND;
204
205 -- Processing the lines
206 IF l_debug_level > 0 THEN
207 oe_debug_pub.add( 'Processing Line '||l_line_id , 1 ) ;
208 END IF;
209
210 BEGIN --15870313
211 WF_ENGINE.CompleteActivityInternalName(
212 itemtype => 'OEOL',
213 itemkey => to_char(l_line_id),
214 activity => 'SCHEDULING_ELIGIBLE',
215 result => 'COMPLETED',
216 raise_engine_exception => TRUE );
217 EXCEPTION
218 WHEN OTHERS THEN
219 oe_debug_pub.add('Workflow complete activity failed. Raising');
220 RAISE ;
221 END ;
222 END LOOP;
223 CLOSE l_ref_cur_line_id;
224 END IF;
225 IF l_debug_level > 0 THEN
226 oe_debug_pub.add( 'EXITING PROCESS_CHILD_LINES' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
227 END IF;
228
229
230 END Process_Child_Lines;
231
232 /*-----------------------------------------------------------------------
233 Proceudure : Schedule Line
234 ----------------------------------------------------------------------- */
235
236 PROCEDURE Schedule_Line (itemtype in varchar2,
237 itemkey in varchar2,
238 actid in number,
239 funcmode in varchar2,
240 resultout in out nocopy varchar2) /* file.sql.39 change */
241 IS
242 l_line_rec OE_Order_PUB.Line_Rec_Type;
243 l_old_line_rec OE_Order_PUB.Line_Rec_Type;
244 l_return_status VARCHAR2(1);
245 l_dummy VARCHAR2(240);
246 l_write_to_db VARCHAR2(1);
247 l_msg_count NUMBER;
248 l_msg_data VARCHAR2(2000);
249 l_atp_tbl OE_ATP.atp_tbl_type;
250 l_result Varchar2(30);
251 l_out_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
252 l_line_id NUMBER := 0;
253 l_top_model_line_id NUMBER := 0;
254 l_item_type_code VARCHAR2(30);
255 l_line_category_code VARCHAR2(30);
256 l_schedule_status_code VARCHAR2(30);
257 l_source_type_code VARCHAR2(30);
258 l_ship_model_complete_flag VARCHAR2(1);
259 l_ato_line_id NUMBER;
260 l_request_date DATE;
261 l_sch_ship_date DATE;
262 l_ship_from_org_id NUMBER;
263 l_activity_status_code VARCHAR2(8);
264 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
265 l_header_id NUMBER;
266 l_order_source_id NUMBER;
267 l_orig_sys_document_ref VARCHAR2(50);
268 l_orig_sys_line_ref VARCHAR2(50);
269 l_orig_sys_shipment_ref VARCHAR2(50);
270 l_change_sequence VARCHAR2(50);
271 l_source_document_type_id NUMBER;
272 l_source_document_id NUMBER;
273 l_source_document_line_id NUMBER;
274 l_scheduled BOOLEAN;
275 l_link_to_line_id NUMBER;
276 l_child_line_id NUMBER := 0;
277 --11825106
278 l_ship_set_id NUMBER ;
279 l_arrival_set_id NUMBER;
280
281 --10143498
282 l_selected_line_tbl OE_GLOBALS.Selected_Record_Tbl;
283 l_line_count NUMBER;
284 l_subscription_enable_flag CHAR(1) := ''; --sol_ord_er #16014165
285 BEGIN
286 --
287 -- RUN mode - normal process execution
288 --
289
290 if (funcmode = 'RUN') then
291
292 IF l_debug_level > 0 THEN
293 oe_debug_pub.add( 'TST1: WITHIN SCHEDULE LINE WORKFLOW COVER ',0.5 ) ; -- debug level 0.5 added for bug 13435459
294 END IF;
295 IF l_debug_level > 0 THEN
296 oe_debug_pub.add( 'ITEM KEY IS ' || ITEMKEY ) ;
297 END IF;
298
299 OE_STANDARD_WF.Set_Msg_Context(actid);
300
301 SAVEPOINT Before_Lock;
302
303 -- If it is BULK Mode then no need to query these values from Database
304
305 IF OE_BULK_WF_UTIL.G_LINE_INDEX IS NOT NULL THEN
306 IF l_debug_level > 0 THEN
307 oe_debug_pub.add( 'SCH BULK MODE' , 5 ) ;
308 END IF;
309
310 l_line_id := OE_BULK_ORDER_PVT.G_LINE_REC.line_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
311
312 l_top_model_line_id :=
313 OE_BULK_ORDER_PVT.G_LINE_REC.top_model_line_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
314
315 l_item_type_code :=
316 OE_BULK_ORDER_PVT.G_LINE_REC.item_type_code(OE_BULK_WF_UTIL.G_LINE_INDEX);
317
318 l_line_category_code :=
319 OE_BULK_ORDER_PVT.G_LINE_REC.line_category_code(OE_BULK_WF_UTIL.G_LINE_INDEX);
320
321 l_schedule_status_code :=
322 OE_BULK_ORDER_PVT.G_LINE_REC.schedule_status_code(OE_BULK_WF_UTIL.G_LINE_INDEX);
323
324 l_ship_model_complete_flag :=
325 OE_BULK_ORDER_PVT.G_LINE_REC.ship_model_complete_flag(OE_BULK_WF_UTIL.G_LINE_INDEX);
326
327 l_ato_line_id :=
328 OE_BULK_ORDER_PVT.G_LINE_REC.ato_line_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
329
330 l_source_type_code :=
331 OE_BULK_ORDER_PVT.G_LINE_REC.source_type_code(OE_BULK_WF_UTIL.G_LINE_INDEX);
332
333 l_request_date :=
334 OE_BULK_ORDER_PVT.G_LINE_REC.request_date(OE_BULK_WF_UTIL.G_LINE_INDEX);
335
336 l_sch_ship_date :=
337 OE_BULK_ORDER_PVT.G_LINE_REC.schedule_ship_date(OE_BULK_WF_UTIL.G_LINE_INDEX);
338
339 l_ship_from_org_id :=
340 OE_BULK_ORDER_PVT.G_LINE_REC.ship_from_org_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
341
342 -- Locking of top model not needed for BULK create as model(kit) and child
343 -- (included items) are created in the same DB session
344 -- Handle external lines call not needed as BULK does not support external
345 -- lines
346 -- If the mode is BULK from Order Import Then check globals to find out
347 -- if line on activity specific hold
348
349 IF OE_BULK_HOLDS_PVT.G_Line_Holds_Tbl.EXISTS(OE_BULK_WF_UTIL.G_LINE_INDEX)
350 AND OE_BULK_HOLDS_PVT.G_Line_Holds_Tbl(OE_BULK_WF_UTIL.G_LINE_INDEX).On_Scheduling_Hold = 'Y'
351 THEN
352 l_result := FND_API.G_TRUE;
353 END IF;
354
355 ELSE -- If not BULK mode.
356
357 IF l_debug_level > 0 THEN
358 oe_debug_pub.add( 'SCH NON-BULK MODE' , 5 ) ;
359 END IF;
360
361 --Processing added for Locking
362
363 -- To improve performance:
364 -- Query all variables needed for local processing here
365 -- and query the entire line record only if line needs
366 -- to be scheduled just before the call to Schedule_Line.
367 SELECT line_id
368 , top_model_line_id
369 , item_type_code
370 , line_category_code
371 , schedule_status_code
372 , ship_model_complete_flag
373 , ato_line_id
374 , source_type_code
375 , request_date
376 , schedule_ship_date
377 , ship_from_org_id
378 , header_id
379 , order_source_id
380 , orig_sys_document_ref
381 , orig_sys_line_ref
382 , orig_sys_shipment_ref
383 , change_sequence
384 , source_document_type_id
385 , source_document_id
386 , source_document_line_id
387 , link_to_line_id
388 , inventory_item_id
389 , ship_set_id
390 , arrival_set_id
391 , subscription_enable_flag --sol_ord_er #16014165
392 INTO l_line_id, l_top_model_line_id
393 , l_item_type_code
394 , l_line_category_code
395 , l_schedule_status_code
396 , l_ship_model_complete_flag
397 , l_ato_line_id
398 , l_source_type_code
399 , l_request_date
400 , l_sch_ship_date
401 , l_ship_from_org_id
402 , l_header_id
403 , l_order_source_id
404 , l_orig_sys_document_ref
405 , l_orig_sys_line_ref
406 , l_orig_sys_shipment_ref
407 , l_change_sequence
408 , l_source_document_type_id
409 , l_source_document_id
410 , l_source_document_line_id
411 , l_link_to_line_id -- 3000761
412 , l_line_rec.inventory_item_id
413 , l_ship_set_id
414 , l_arrival_set_id
415 , l_subscription_enable_flag --sol_ord_er #16014165
416 FROM oe_order_lines
417 WHERE line_id = to_number(itemkey);
418 --FOR UPDATE; -- 3693569 :This will be locked after parent line is locked.
419
420 l_child_line_id := l_line_id;
421
422 IF l_debug_level > 0 THEN
423 oe_debug_pub.add( 'TOP MODEL LINE ID : '||L_TOP_MODEL_LINE_ID , 3 ) ;
424 END IF;
425
426 IF nvl(l_top_model_line_id,0) <> 0 THEN
427
428 IF l_debug_level > 0 THEN
429 oe_debug_pub.add( 'LOCKING MODEL '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
430 END IF;
431
432 BEGIN
433 SELECT line_id, top_model_line_id
434 INTO l_line_id, l_top_model_line_id
435 FROM oe_order_lines
436 WHERE line_id = l_top_model_line_id
437 FOR UPDATE NOWAIT; --11868761
438
439 IF l_debug_level > 0 THEN
440 oe_debug_pub.add( 'MODEL LOCKED '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
441 END IF;
442 EXCEPTION
443 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
444 IF l_debug_level > 0 THEN
445 oe_debug_pub.add('SCHEDULE_LINE: unable to lock the top model line:'||l_top_model_line_id,1);
446 END IF;
447 resultout := 'COMPLETE:INCOMPLETE';
448 OE_STANDARD_WF.Clear_Msg_Context;
449 RETURN;
450 END;
451 END IF;
452 -- 3693569: Lock the child line
453 BEGIN
454 SELECT line_id
455 INTO l_line_id
456 FROM oe_order_lines
457 WHERE line_id = l_child_line_id
458 FOR UPDATE NOWAIT; --11868761
459
460 EXCEPTION
461 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
462 IF l_debug_level > 0 THEN
463 oe_debug_pub.add('SCHEDULE_LINE: unable to lock the line:'||l_child_line_id,1);
464 END IF;
465 resultout := 'COMPLETE:INCOMPLETE';
466 OE_STANDARD_WF.Clear_Msg_Context;
467 RETURN;
468 END;
469 l_line_id := to_number(itemkey);
470
471 OE_MSG_PUB.set_msg_context(p_entity_code => 'LINE'
472 ,p_entity_id => l_line_id
473 ,p_header_id => l_header_id
474 ,p_line_id => l_line_id
475 ,p_order_source_id => l_order_source_id
476 ,p_orig_sys_document_ref => l_orig_sys_document_ref
477 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
478 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
479 ,p_change_sequence => l_change_sequence
480 ,p_source_document_type_id => l_source_document_type_id
481 ,p_source_document_id => l_source_document_id
482 ,p_source_document_line_id => l_source_document_line_id
483 );
484 /* --
485 * -- To push child lines to Schedule_Eligible block if model is not scheduled
486 * IF NVL(l_ship_model_complete_flag,'N') = 'Y'
487 * AND NOT OE_GLOBALS.Equal(l_top_model_line_id,
488 * l_line_id)
489 * THEN -- SMC
490 *
491 * l_scheduled := Is_Line_Scheduled(l_top_model_line_id);
492 *
493 * IF NOT l_scheduled
494 * THEN
495 * ROLLBACK TO Before_Lock;
496 * resultout := 'COMPLETE:INCOMPLETE';
497 * return;
498 * END IF;
499 * IF Is_Scheduling_Eligible(l_top_model_line_id) THEN -- Bug3083995
500 * ROLLBACK TO Before_Lock;
501 * resultout := 'COMPLETE:INCOMPLETE';
502 * return;
503 * END IF;
504 *
505 * ELSIF l_ato_line_id is not null
506 * AND NOT OE_GLOBALS.Equal(l_ato_line_id,
507 * l_line_id)
508 * THEN -- ATO
509 * l_scheduled := Is_Line_Scheduled(l_ato_line_id);
510 * IF NOT l_scheduled
511 * THEN
512 * ROLLBACK TO Before_Lock;
513 * resultout := 'COMPLETE:INCOMPLETE';
514 * return;
515 * END IF;
516 * IF Is_Scheduling_Eligible(l_ato_line_id) THEN ---- Bug3083995
517 * ROLLBACK TO Before_Lock;
518 * resultout := 'COMPLETE:INCOMPLETE';
519 * return;
520 * END IF;
521 * ELSIF NVL(l_ship_model_complete_flag,'N') = 'N'
522 * AND l_item_type_code ='INCLUDED'
523 * THEN -- Non SMC
524 * l_scheduled := Is_Line_Scheduled(l_link_to_line_id);
525 * IF NOT l_scheduled
526 * THEN
527 * ROLLBACK TO Before_Lock;
528 * resultout := 'COMPLETE:INCOMPLETE';
529 * return;
530 * END IF;
531 * IF Is_Scheduling_Eligible(l_link_to_line_id) THEN -- Bug3083995
532 * ROLLBACK TO Before_Lock;
533 * resultout := 'COMPLETE:INCOMPLETE';
534 * return;
535 * END IF;
536 *
537 * END IF;
538 * --
539 */
540
541 -- Added external to if stmt to bypass scheduling.
542 IF (l_item_type_code = OE_GLOBALS.G_ITEM_SERVICE) OR
543 (l_line_category_code = 'RETURN') OR
544 (l_source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL) OR
545 NVL(l_subscription_enable_flag,'N') = 'Y' --sol_ord_er #16014165
546 THEN
547
548 -- This is a service line or a return line. We will complete
549 -- this activity with not eligible for these lines.
550
551 ROLLBACK TO Before_Lock;
552
553 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' AND
554 l_source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL AND
555 l_ato_line_id = l_line_id AND
556 l_sch_ship_date is NULL THEN
557
558 BEGIN
559 IF l_debug_level > 0 THEN
560 oe_debug_pub.add( 'CALLING HANDEL_EXTERNAL_LINES IN WF' , 4 ) ;
561 END IF;
562
563 l_line_rec.line_id := l_line_id;
564 l_line_rec.ato_line_id := l_ato_line_id;
565 l_line_rec.top_model_line_id := l_top_model_line_id;
566 l_line_rec.request_date := l_request_date;
567 l_line_rec.ship_from_org_id := l_ship_from_org_id;
568
569 OE_Schedule_Util.Handle_External_Lines
570 (p_x_line_rec => l_line_rec);
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 IF l_debug_level > 0 THEN
575 oe_debug_pub.add( 'SCHEDULING WORFKLOW ERRORS' , 1 ) ;
576 END IF;
577
578 resultout := 'COMPLETE:INCOMPLETE';
579
580 OE_STANDARD_WF.Save_Messages;
581 OE_STANDARD_WF.Clear_Msg_Context;
582
583 OE_Delayed_Requests_PVT.Clear_Request
584 (x_return_status => l_return_status);
585
586 RETURN;
587 END;
588 END IF;
589
590 resultout := 'COMPLETE:NOT_ELIGIBLE';
591
592 OE_STANDARD_WF.Save_Messages;
593 OE_STANDARD_WF.Clear_Msg_Context;
594
595 OE_Delayed_Requests_PVT.Clear_Request
596 (x_return_status => l_return_status);
597 return;
598
599 END IF;
600
601
602 -- To push child lines to Schedule_Eligible block if model is not scheduled
603 -- 3565621
604 IF NOT g_skip_check
605 THEN
606 IF NVL(l_ship_model_complete_flag,'N') = 'Y'
607 AND NOT OE_GLOBALS.Equal(l_top_model_line_id,
608 l_line_id)
609 THEN -- SMC
610 IF l_debug_level > 0 THEN
611 oe_debug_pub.add( '..SMC=Y, Line not Top Model..' , 3 ) ;
612 END IF;
613
614 l_scheduled := Is_Line_Scheduled(l_top_model_line_id);
615
616 IF NOT l_scheduled
617 THEN
618 ROLLBACK TO Before_Lock;
619 resultout := 'COMPLETE:INCOMPLETE';
620 OE_STANDARD_WF.Save_Messages;
621 OE_STANDARD_WF.Clear_Msg_Context;
622 --5166476
623 IF OE_SCH_CONC_REQUESTS.g_recorded = 'N' THEN
624 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'N';
625 OE_SCH_CONC_REQUESTS.g_recorded :='Y';
626 END IF;
627
628 OE_Delayed_Requests_PVT.Clear_Request
629 (x_return_status => l_return_status);
630 return;
631 END IF;
632 IF Is_Scheduling_Eligible(l_top_model_line_id) THEN -- Bug3083995
633 ROLLBACK TO Before_Lock;
634 resultout := 'COMPLETE:INCOMPLETE';
635 OE_STANDARD_WF.Save_Messages;
636 OE_STANDARD_WF.Clear_Msg_Context;
637 --5166476
638 IF OE_SCH_CONC_REQUESTS.g_recorded = 'N' THEN
639 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'N';
640 OE_SCH_CONC_REQUESTS.g_recorded :='Y';
641 END IF;
642
643 OE_Delayed_Requests_PVT.Clear_Request
644 (x_return_status => l_return_status);
645 return;
646 END IF;
647
648 ELSIF l_ato_line_id is not null
649 AND NOT OE_GLOBALS.Equal(l_ato_line_id,
650 l_line_id)
651 THEN -- ATO
652 IF l_debug_level > 0 THEN
653 oe_debug_pub.add( '..ATO Line, Line not Top Model..' , 3 ) ;
654 END IF;
655 l_scheduled := Is_Line_Scheduled(l_ato_line_id);
656 IF NOT l_scheduled
657 THEN
658 ROLLBACK TO Before_Lock;
659 resultout := 'COMPLETE:INCOMPLETE';
660 OE_STANDARD_WF.Save_Messages;
661 OE_STANDARD_WF.Clear_Msg_Context;
662 --5166476
663 IF OE_SCH_CONC_REQUESTS.g_recorded = 'N' THEN
664 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'N';
665 OE_SCH_CONC_REQUESTS.g_recorded :='Y';
666 END IF;
667
668 OE_Delayed_Requests_PVT.Clear_Request
669 (x_return_status => l_return_status);
670 return;
671 END IF;
672 IF Is_Scheduling_Eligible(l_ato_line_id) THEN ---- Bug3083995
673 ROLLBACK TO Before_Lock;
674 resultout := 'COMPLETE:INCOMPLETE';
675 --5166476
676 IF OE_SCH_CONC_REQUESTS.g_recorded = 'N' THEN
677 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'N';
678 OE_SCH_CONC_REQUESTS.g_recorded :='Y';
679 END IF;
680
681 OE_STANDARD_WF.Save_Messages;
682 OE_STANDARD_WF.Clear_Msg_Context;
683
684 OE_Delayed_Requests_PVT.Clear_Request
685 (x_return_status => l_return_status);
686 return;
687 END IF;
688 ELSIF NVL(l_ship_model_complete_flag,'N') = 'N'
689 AND l_item_type_code ='INCLUDED'
690 AND NOT oe_line_util.Is_Subscription_Service_Line(l_link_to_line_id) -- sol_ord_er #16014165
691 THEN -- Non SMC
692 IF l_debug_level > 0 THEN
693 oe_debug_pub.add( '..SMC=N, Included Item..' , 3 ) ;
694 END IF;
695 l_scheduled := Is_Line_Scheduled(l_link_to_line_id);
696 IF NOT l_scheduled
697 THEN
698 ROLLBACK TO Before_Lock;
699 resultout := 'COMPLETE:INCOMPLETE';
700 OE_STANDARD_WF.Save_Messages;
701 OE_STANDARD_WF.Clear_Msg_Context;
702 --5166476
703 IF OE_SCH_CONC_REQUESTS.g_recorded = 'N' THEN
704 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'N';
705 OE_SCH_CONC_REQUESTS.g_recorded :='Y';
706 END IF;
707
708 OE_Delayed_Requests_PVT.Clear_Request
709 (x_return_status => l_return_status);
710 return;
711 END IF;
712 IF Is_Scheduling_Eligible(l_link_to_line_id) THEN -- Bug3083995
713 ROLLBACK TO Before_Lock;
714 resultout := 'COMPLETE:INCOMPLETE';
715 --5166476
716 IF OE_SCH_CONC_REQUESTS.g_recorded = 'N' THEN
717 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'N';
718 OE_SCH_CONC_REQUESTS.g_recorded :='Y';
719 END IF;
720
721 OE_STANDARD_WF.Save_Messages;
722 OE_STANDARD_WF.Clear_Msg_Context;
723
724 OE_Delayed_Requests_PVT.Clear_Request
725 (x_return_status => l_return_status);
726 return;
727 END IF;
728
729 END IF;
730 END IF; -- 3565621
731 --
732 IF NVL(OE_SCH_CONC_REQUESTS.g_checked_for_holds, 'N') <> 'Y' THEN -- ER 13114460
733 IF l_debug_level > 0 THEN
734 oe_debug_pub.add( 'CALLING CHECK HOLDS' , 1 ) ;
735 END IF;
736
737 OE_Holds_PUB.Check_Holds
738 ( p_api_version => 1.0
739 , p_init_msg_list => FND_API.G_FALSE
740 , p_commit => FND_API.G_FALSE
741 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
742 , x_return_status => l_out_return_status
743 , x_msg_count => l_msg_count
744 , x_msg_data => l_msg_data
745 , p_line_id => l_line_id
746 , p_hold_id => NULL
747 , p_entity_code => NULL
748 , p_entity_id => NULL
749 , p_wf_item => 'OEOL'
750 , p_wf_activity => 'LINE_SCHEDULING'
751 , p_chk_act_hold_only => 'Y'
752 , x_result_out => l_result
753 );
754
755 IF l_debug_level > 0 THEN
756 oe_debug_pub.add( 'AFTER CALLING CHECK HOLDS'||L_OUT_RETURN_STATUS||'/'||L_RESULT , 1 ) ;
757 END IF;
758
759 IF (l_out_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
760 IF l_out_return_status = FND_API.G_RET_STS_ERROR THEN
761 RAISE FND_API.G_EXC_ERROR;
762 ELSE
763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
764 END IF;
765 END IF;
766 ELSE -- ER 13114460
767 IF l_debug_level > 0 THEN
768 oe_debug_pub.add('Coming from CP, Line checked for holds already');
769 END IF;
770 END IF;
771
772 END IF; -- End IF BULK Mode
773
774 IF (l_result = FND_API.G_TRUE) THEN
775 IF l_debug_level > 0 THEN
776 oe_debug_pub.add( 'LINE IS ON HOLD' , 1 ) ;
777 END IF;
778 ROLLBACK TO Before_Lock;
779
780 -- Start modified for bug 2515791
781 IF l_schedule_status_code is not null THEN
782 -- New message 'Could not Progress. Line is on Hold' added
783 FND_MESSAGE.SET_NAME('ONT','OE_SCH_UN_PROGRESS_ON_HOLD');
784 ELSE
785 FND_MESSAGE.SET_NAME('ONT','OE_SCH_LINE_ON_HOLD');
786 END IF;
787 -- End modified for bug 2515791
788 OE_MSG_PUB.Add;
789
790 resultout := 'COMPLETE:ON_HOLD';
791 OE_STANDARD_WF.Save_Messages;
792 OE_STANDARD_WF.Clear_Msg_Context;
793
794 OE_Delayed_Requests_PVT.Clear_Request
795 (x_return_status => l_return_status);
796 return;
797 END IF;
798
799 IF (l_schedule_status_code is not null) THEN
800 ROLLBACK TO Before_Lock;
801 resultout := 'COMPLETE:COMPLETE';
802
803
804 --Added this call to fix bug 3319120
805 --
806 -- Processing child lines which are at scheduling eligible block (if any)
807 IF l_top_model_line_id = l_line_id
808 OR l_ato_line_id = l_line_id
809 OR (NVL(l_ship_model_complete_flag,'N') = 'N'
810 AND l_item_type_code IN('MODEL','CLASS','KIT'))
811 THEN
812 IF OE_GLOBALS.Equal(l_line_id,
813 sch_cached_line_id)
814 THEN -- To refresh the cached values
815 sch_cached_line_id := NULL;
816 END IF;
817 -- 3565621
818 g_skip_check := TRUE;
819 Process_Child_Lines(p_line_id => l_line_id,
820 p_top_model_line_id => l_top_model_line_id,
821 p_ato_line_id => l_ato_line_id,
822 p_ship_model_complete_flag => l_ship_model_complete_flag,
823 p_item_type_code => l_item_type_code);
824 g_skip_check := FALSE;
825 END IF;
826 --
827 l_line_rec.ship_from_org_id := l_ship_from_org_id;
828 Bulk_Mode_Copy_Sch_Attribs
829 (p_line_rec => l_line_rec);
830
831 return;
832 END IF;
833
834
835 -- This code is not required any more, since flow for the included
836 -- items starts at the end due to delayed flow changes.
837 -- Introducing dependency on delayed_flow aru.1993341
838
839
840 -- Modified this code to take care of multiple calls to MRP when SMC model call or
841 -- atp model failed to schedule. If model is in schedule eligible status, push the child records
842 -- to schedule eligible state. That way when user runs the concurrent program, system will
843 -- schedule whole model if possible. Fix is to address bug 2452175
844
845 IF (l_top_model_line_id is not null) AND
846 (nvl(l_ship_model_complete_flag,'N') = 'Y') AND
847 (l_top_model_line_id <> l_line_id) THEN
848 -- This is a SMC option/class/included item. We will bypass this
849 -- line since the model line will schedule this line.
850
851 --Bug-2452175
852
853 BEGIN
854 --Check whether the Parent line is in Schedule Eligible state
855
856 SELECT ACTIVITY_STATUS
857 INTO l_activity_status_code
858 FROM wf_item_activity_statuses wias, wf_process_activities wpa
859 WHERE wias.item_type = 'OEOL' AND
860 wias.item_key = to_char(l_top_model_line_id) AND
861 wias.process_activity = wpa.instance_id AND
862 wpa.ACTIVITY_ITEM_TYPE = 'OEOL' AND
863 wpa.activity_name = 'SCHEDULING_ELIGIBLE' AND
864 wias.activity_status = 'NOTIFIED';
865
866 -- Parent line is in Schedule Eligible Status set the line status
867 -- to Schedule Eligible.
868 IF l_debug_level > 0 THEN
869 oe_debug_pub.add( 'SMC :PUSHING LINE TO SCHEDULE ELIGIBLE' , 2 ) ;
870 END IF;
871 ROLLBACK TO Before_Lock;
872 resultout := 'COMPLETE:INCOMPLETE';
873 OE_STANDARD_WF.Save_Messages;
874 OE_STANDARD_WF.Clear_Msg_Context;
875
876 OE_Delayed_Requests_PVT.Clear_Request
877 (x_return_status => l_return_status);
878 return;
879 EXCEPTION
880 WHEN NO_DATA_FOUND THEN
881 -- Parent line is not in Schedule Eligible State.
882 NULL;
883 WHEN OTHERS THEN
884 NULL;
885 END;
886
887 END IF;
888
889 IF (l_ato_line_id is not null) AND
890 (l_line_id <> l_ato_line_id) THEN
891 -- This is an ATO option or class. We will bypass this
892 -- line since the model line will schedule this line.
893
894 --Bug-2452175
895
896 BEGIN
897 --Check whether the Parent line is in Schedule Eligible state
898
899 SELECT ACTIVITY_STATUS
900 INTO l_activity_status_code
901 FROM wf_item_activity_statuses wias, wf_process_activities wpa
902 WHERE wias.item_type = 'OEOL' AND
903 wias.item_key = to_char(l_ato_line_id) AND
904 wias.process_activity = wpa.instance_id AND
905 wpa.ACTIVITY_ITEM_TYPE = 'OEOL' AND
906 wpa.activity_name = 'SCHEDULING_ELIGIBLE' AND
907 wias.activity_status = 'NOTIFIED';
908
909 -- Parent is in Schedule Eligible Status set the line status
910 -- to Schedule Eligible.
911 IF l_debug_level > 0 THEN
912 oe_debug_pub.add( 'ATO: PUSHING LINE TO SCHEDULE ELIGIBLE' , 2 ) ;
913 END IF;
914 ROLLBACK TO Before_Lock;
915 resultout := 'COMPLETE:INCOMPLETE';
916 OE_STANDARD_WF.Save_Messages;
917 OE_STANDARD_WF.Clear_Msg_Context;
918
919 OE_Delayed_Requests_PVT.Clear_Request
920 (x_return_status => l_return_status);
921 return;
922 EXCEPTION
923 WHEN NO_DATA_FOUND THEN
924 -- Parent line is not in Schedule Eligible State.
925 NULL;
926 WHEN OTHERS THEN
927 NULL;
928 END;
929
930 END IF;
931
932 OE_Line_Util.Query_Row
933 (p_line_id => to_number(itemkey),
934 x_line_rec => l_line_rec);
935
936 OE_MSG_PUB.update_msg_context( p_entity_code => 'LINE'
937 ,p_entity_id => l_line_rec.line_id
938 ,p_header_id => l_line_rec.header_id
939 ,p_line_id => l_line_rec.line_id
940 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
941 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
942 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
943 ,p_change_sequence => l_line_rec.change_sequence
944 ,p_source_document_id => l_line_rec.source_document_id
945 ,p_source_document_line_id => l_line_rec.source_document_line_id
946 ,p_order_source_id => l_line_rec.order_source_id
947 ,p_source_document_type_id => l_line_rec.source_document_type_id);
948
949 l_old_line_rec := l_line_rec;
950 -- l_line_rec.schedule_action_code := OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE;
951 l_line_rec.schedule_action_code := OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE;
952 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
953
954 -- Added this savepoint logic to fix bug 2129583.
955 SAVEPOINT Schedule_line;
956 --10143498 : NON SMC model lines to be scheduled together while booking or progress.
957 IF NVL(l_ship_model_complete_flag,'N') = 'N' AND
958 l_schedule_status_code is null AND
959 l_ship_set_id IS NULL AND
960 l_Arrival_set_id IS NULL AND -- 11825106
961 NOT OE_GLOBALS.Equal(l_ato_line_id,
962 l_top_model_line_id) AND
963 ((l_top_model_line_id = l_line_rec.line_id
964 OR l_item_type_code IN('MODEL','CLASS','KIT'))
965 AND NVL(l_line_rec.subscription_enable_flag,'N')='N') THEN -- sol_ord_er #16014165
966
967 IF l_debug_level > 0 THEN
968 oe_debug_pub.add( 'BEFORE CALLING Schedule_Multi_lines ' , 1 ) ;
969 END IF;
970 l_selected_line_tbl(1).id1 :=l_line_rec.line_id;
971 l_selected_line_tbl(1).org_id :=l_line_rec.org_id;
972 l_line_count := 1;
973 OE_GROUP_SCH_UTIL.Schedule_Multi_lines
974 (p_selected_line_tbl => l_selected_line_tbl,
975 p_line_count => l_line_count,
976 p_sch_action => OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE,
977 x_atp_tbl => l_atp_tbl,
978 x_return_status => l_return_status,
979 x_msg_count => l_msg_count,
980 x_msg_data => l_msg_data);
981
982 ELSIF l_schedule_status_code is null THEN
983 IF l_debug_level > 0 THEN
984 oe_debug_pub.add( 'BEFORE CALLING OE_SCHEDULE_UTIL ' , 1 ) ;
985 END IF;
986 OE_SCHEDULE_UTIL.Schedule_Line
987 (p_x_line_rec => l_line_rec
988 ,p_old_line_rec => l_old_line_rec
989 ,p_caller => OE_SCHEDULE_UTIL.SCH_EXTERNAL
990 ,x_return_status => l_return_status);
991 END IF;
992 IF l_debug_level > 0 THEN
993 oe_debug_pub.add( 'L_RETURN_STATUS IS ' || L_RETURN_STATUS , 1 ) ;
994 END IF;
995 --11825106
996 IF l_ship_set_id IS NULL AND
997 l_Arrival_set_id IS NULL AND
998 oe_schedule_util.OESCH_SET_SCHEDULING = 'N' THEN
999 oe_schedule_util.OESCH_SET_SCHEDULING := 'Y' ;
1000 END IF;
1001 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1002 -- Clear oe_schedule_lines_temp table.
1003 BEGIN
1004 IF l_debug_level > 0 THEN
1005 oe_debug_pub.add( 'DELETING oe_schedule_lines_temp ' , 1 ) ;
1006 END IF;
1007 DELETE FROM oe_schedule_lines_temp;
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 NULL;
1011 END;
1012 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1013 IF l_debug_level > 0 THEN
1014 oe_debug_pub.add( 'SCHEDULING WORFKLOW EXP ERRORS' , 1 ) ;
1015 END IF;
1016 resultout := 'COMPLETE:INCOMPLETE';
1017 --8731703
1018 -- Rollback the demand for success lines.
1019 OE_SCHEDULE_UTIL.CALL_MRP_ROLLBACK (x_return_status => l_return_status);
1020 IF l_debug_level > 0 THEN
1021 oe_debug_pub.add( 'MRP Rollback result '||l_return_status , 1 ) ;
1022 END IF;
1023 -- moved this line up to for the bug fix 2884452
1024 ROLLBACK TO SAVEPOINT Schedule_line;
1025 OE_STANDARD_WF.Save_Messages;
1026 OE_STANDARD_WF.Clear_Msg_Context;
1027 --commit; /* Added this line to fix the bug 2884452 */
1028 OE_Delayed_Requests_PVT.Clear_Request
1029 (x_return_status => l_return_status);
1030 --5122730
1031 IF OE_SCH_CONC_REQUESTS.g_conc_program = 'Y' THEN
1032 IF OE_SCH_CONC_REQUESTS.g_recorded = 'N' THEN -- 5166476
1033 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_rec.line_id) := 'N';
1034 OE_SCH_CONC_REQUESTS.g_recorded := 'Y';
1035 END IF;
1036 END IF;
1037
1038 return;
1039 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1040 IF l_debug_level > 0 THEN
1041 oe_debug_pub.add( 'SCHEDULING WORFKLOW UN-EXP ERRORS' , 1 ) ;
1042 END IF;
1043 resultout := 'COMPLETE:INCOMPLETE';
1044 -- moved this line up to for the bug fix 2884452
1045 ROLLBACK TO SAVEPOINT Schedule_line;
1046 OE_STANDARD_WF.Save_Messages;
1047 OE_STANDARD_WF.Clear_Msg_Context;
1048 --commit; /* Added this line to fix the bug 2884452 */
1049 OE_Delayed_Requests_PVT.Clear_Request
1050 (x_return_status => l_return_status);
1051 -- app_exception.raise_exception;
1052 --5122730
1053 IF OE_SCH_CONC_REQUESTS.g_conc_program = 'Y' THEN
1054 IF OE_SCH_CONC_REQUESTS.g_recorded = 'N' THEN -- 5166476
1055 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_rec.line_id) := 'N';
1056 OE_SCH_CONC_REQUESTS.g_recorded := 'Y';
1057 END IF;
1058 END IF;
1059 return;
1060 END IF;
1061
1062 --
1063 -- Processing child lines which are at scheduling eligible block (if any)
1064 IF l_line_rec.top_model_line_id = l_line_rec.line_id
1065 OR l_line_rec.ato_line_id = l_line_rec.line_id
1066 OR (NVL(l_line_rec.ship_model_complete_flag,'N') = 'N'
1067 AND l_line_rec.item_type_code IN('MODEL','CLASS','KIT'))
1068 AND oe_schedule_util.OESCH_SET_SCHEDULING = 'Y' -- 11825106
1069 THEN
1070 IF OE_GLOBALS.Equal(l_line_rec.line_id,
1071 sch_cached_line_id)
1072 THEN -- To refresh the cached values
1073 sch_cached_line_id := NULL;
1074 END IF;
1075 -- 3565621
1076 g_skip_check := TRUE;
1077 Process_Child_Lines(p_line_id => l_line_rec.line_id,
1078 p_top_model_line_id => l_line_rec.top_model_line_id,
1079 p_ato_line_id => l_line_rec.ato_line_id,
1080 p_ship_model_complete_flag => l_line_rec.ship_model_complete_flag,
1081 p_item_type_code => l_line_rec.item_type_code);
1082 g_skip_check := FALSE;
1083 END IF;
1084 --
1085 --11825106
1086 IF oe_schedule_util.OESCH_SET_SCHEDULING = 'Y' THEN
1087 resultout := 'COMPLETE:COMPLETE';
1088 OE_STANDARD_WF.Clear_Msg_Context;
1089
1090 Bulk_Mode_Copy_Sch_Attribs
1091 (p_line_rec => l_line_rec);
1092 ELSE
1093 resultout := 'COMPLETE:INCOMPLETE';
1094 -- moved this line up to for the bug fix 2884452
1095 ROLLBACK TO SAVEPOINT Schedule_line;
1096 OE_STANDARD_WF.Save_Messages;
1097 OE_STANDARD_WF.Clear_Msg_Context;
1098 --commit; /* Added this line to fix the bug 2884452 */
1099 OE_Delayed_Requests_PVT.Clear_Request
1100 (x_return_status => l_return_status);
1101 --oe_schedule_util.OESCH_SET_SCHEDULING :='Y';
1102 END IF;
1103 return;
1104 end if;
1105
1106 IF (funcmode = 'CANCEL') THEN
1107 null;
1108 return;
1109 END IF;
1110 IF l_debug_level > 0 THEN -- Added debug for bug 13435459
1111 oe_debug_pub.add( 'Exiting TST1: SCHEDULE LINE WORKFLOW COVER ',0.5 ) ;
1112 END IF;
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 wf_core.context('OE_OEOL_SCH', 'Schedule Line',
1117 itemtype, itemkey, to_char(actid), funcmode);
1118 -- start data fix project
1119 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1120 p_itemtype => itemtype,
1121 p_itemkey => itemkey);
1122 OE_STANDARD_WF.Save_Messages;
1123 OE_STANDARD_WF.Clear_Msg_Context;
1124 -- end data fix project
1125 raise;
1126 END Schedule_Line;
1127
1128
1129
1130 /*----------------------------------------------------------------------
1131 Bulk_Mode_Copy_Sch_Attribs
1132
1133 This procedure sets the sceduling attributes on the bulk glabal line
1134 record, if scheduling happens through workflow.
1135 -----------------------------------------------------------------------*/
1136 PROCEDURE Bulk_Mode_Copy_Sch_Attribs
1137 (p_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type)
1138 IS
1139 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1140 l_key NUMBER;
1141 BEGIN
1142
1143 IF l_debug_level > 0 THEN
1144 oe_debug_pub.add('-- bulk mode set results on global record', 5);
1145 END IF;
1146
1147 IF OE_BULK_WF_UTIL.G_LINE_INDEX IS NOT NULL THEN
1148 -- shippable flag need to be checked based on ship from org.
1149
1150 IF p_line_rec.inventory_item_id is NULL THEN
1151 p_line_rec.inventory_item_id := OE_BULK_ORDER_PVT.G_LINE_REC.inventory_item_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
1152 END IF;
1153
1154 l_key := OE_BULK_CACHE.Load_Item
1155 ( p_key1 => p_line_rec.inventory_item_id
1156 ,p_key2 => p_line_rec.ship_from_org_id);
1157
1158 -- not comparing as we have to either do a blind assign or
1159 -- compare and assign.
1160 -- this load item will serve at the time if wf shipping call.
1161
1162 OE_BULK_ORDER_PVT.G_LINE_REC.shippable_flag
1163 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
1164 OE_BULK_CACHE.G_ITEM_TBL(l_key).shippable_item_flag;
1165
1166 IF OE_BULK_ORDER_PVT.G_LINE_REC.schedule_status_code
1167 (OE_BULK_WF_UTIL.G_LINE_INDEX) is NULL AND
1168 p_line_rec.schedule_status_code is not NULL
1169 THEN
1170 oe_debug_pub.add('2 sch bulk mode, set results wf sch',5);
1171 -- need not put original item
1172
1173 OE_BULK_ORDER_PVT.G_LINE_REC.schedule_status_code
1174 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.schedule_status_code;
1175 OE_BULK_ORDER_PVT.G_LINE_REC.schedule_ship_date
1176 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.schedule_ship_date;
1177 OE_BULK_ORDER_PVT.G_LINE_REC.schedule_arrival_date
1178 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.schedule_arrival_date;
1179 OE_BULK_ORDER_PVT.G_LINE_REC.ship_from_org_id
1180 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.ship_from_org_id;
1181 OE_BULK_ORDER_PVT.G_LINE_REC.shipping_method_code
1182 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.shipping_method_code;
1183 OE_BULK_ORDER_PVT.G_LINE_REC.delivery_lead_time
1184 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.delivery_lead_time;
1185 OE_BULK_ORDER_PVT.G_LINE_REC.visible_demand_flag
1186 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.visible_demand_flag;
1187
1188 --OE_BULK_ORDER_PVT.G_LINE_REC.PLANNING_PRIORITY.extend(l_count);
1189 --OE_BULK_ORDER_PVT.G_LINE_REC.planning_priority
1190 --(OE_BULK_WF_UTIL.G_LINE_INDEX) := l_line_rec.planning_priority;
1191
1192 OE_BULK_ORDER_PVT.G_LINE_REC.re_source_flag
1193 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.re_source_flag;
1194 OE_BULK_ORDER_PVT.G_LINE_REC.mfg_lead_time
1195 (OE_BULK_WF_UTIL.G_LINE_INDEX) := p_line_rec.mfg_lead_time;
1196 END IF;
1197
1198 END IF; -- bulk mode.
1199
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 IF l_debug_level > 0 THEN
1203 oe_debug_pub.add('-- error in setting global record '|| sqlerrm, 5);
1204 END IF;
1205 RAISE;
1206 END Bulk_Mode_Copy_Sch_Attribs;
1207
1208
1209 /*-----------------------------------------------------------------------
1210 Proceudure : Branch on Source Type
1211 ----------------------------------------------------------------------- */
1212 PROCEDURE Branch_on_source_type(
1213 itemtype in varchar2,
1214 itemkey in varchar2,
1215 actid in number,
1216 funcmode in varchar2,
1217 resultout in out nocopy varchar2) /* file.sql.39 change */
1218 IS
1219 --l_line_rec OE_Order_PUB.Line_Rec_Type;
1220 l_source_type_code VARCHAR2(30);
1221 l_ato_line_id NUMBER;
1222 l_item_type_code VARCHAR2(30);
1223 l_line_id NUMBER;
1224
1225 --
1226 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1227 --
1228 BEGIN
1229 --
1230 -- RUN mode - normal process execution
1231 --
1232 -- start data fix project
1233 OE_STANDARD_WF.Set_Msg_Context(actid);
1234 -- end data fix project
1235
1236 -- l_line_rec := OE_Line_Util.Query_Row(to_number(itemkey));
1237 /* Changes for performance, the query row is being replaced by a select.
1238
1239 OE_Line_Util.Query_Row(p_line_id => to_number(itemkey),
1240 x_line_rec => l_line_rec);
1241 */
1242
1243 l_line_id := to_number(itemkey);
1244
1245 SELECT SOURCE_TYPE_CODE,
1246 ITEM_TYPE_CODE,
1247 ATO_LINE_ID
1248 INTO l_source_type_code,
1249 l_item_type_code,
1250 l_ato_line_id
1251 FROM OE_ORDER_LINES
1252 WHERE LINE_ID = l_line_id;
1253
1254 if (funcmode = 'RUN') then
1255
1256
1257 IF l_source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL AND
1258 nvl(l_ato_line_id, -1) <> l_line_id
1259 THEN
1260 IF l_debug_level > 0 THEN
1261 oe_debug_pub.add( 'BRANCH: DROPSHIP '|| L_LINE_ID , 2 ) ;
1262 END IF;
1263 resultout := 'COMPLETE:DROPSHIP';
1264 return;
1265
1266 ELSIF l_ato_line_id = l_line_id THEN
1267
1268 IF l_item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
1269 l_item_type_code = OE_GLOBALS.G_ITEM_CLASS
1270 THEN
1271 IF l_debug_level > 0 THEN
1272 oe_debug_pub.add( 'BRANCH: BUILD '|| L_LINE_ID , 2 ) ;
1273 END IF;
1274 resultout := 'COMPLETE:BUILD';
1275 return;
1276 ELSIF (l_item_type_code = OE_GLOBALS.G_ITEM_STANDARD OR
1277 l_item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
1278 l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED) --9775352
1279 THEN
1280 IF l_debug_level > 0 THEN
1281 oe_debug_pub.add( 'BRANCH: ATO ITEM '|| L_LINE_ID , 2 ) ;
1282 END IF;
1283 resultout := 'COMPLETE:ATO_ITEM';
1284 return;
1285 END IF;
1286
1287 ELSIF l_item_type_code = OE_GLOBALS.G_ITEM_CONFIG
1288 AND OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
1289 AND MSC_ATP_GLOBAL.GET_APS_VERSION = 10 THEN
1290 IF l_debug_level > 0 THEN
1291 oe_debug_pub.add( 'BRANCH: ATO ITEM '|| L_LINE_ID , 2 ) ;
1292 END IF;
1293 resultout := 'COMPLETE:ATO_ITEM';
1294 return;
1295 ELSE
1296 IF l_debug_level > 0 THEN
1297 oe_debug_pub.add( 'BRANCH: STOCK '|| L_LINE_ID , 2 ) ;
1298 END IF;
1299 resultout := 'COMPLETE:STOCK';
1300 return;
1301 END IF;
1302 end if;
1303
1304
1305 IF (funcmode = 'CANCEL') THEN
1306 resultout := 'STOCK';
1307 return;
1308 END IF;
1309
1310
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 wf_core.context('OE_OEOL_SCH', 'Branch_on_source_type',
1314 itemtype, itemkey, to_char(actid), funcmode);
1315 -- start data fix project
1316 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1317 p_itemtype => itemtype,
1318 p_itemkey => itemkey);
1319 OE_STANDARD_WF.Save_Messages;
1320 OE_STANDARD_WF.Clear_Msg_Context;
1321 -- end data fix project
1322 raise;
1323 END Branch_on_source_type;
1324
1325 /*-----------------------------------------------------------------------
1326 Procedure : Release the line to Purchasing
1327 Description: This procedure validates the line and calls
1328 OE_Purchase_Release_PVT.Purchase_Release to release the
1329 line to purchase (i.e: insert into req interface tables).
1330 ----------------------------------------------------------------------- */
1331
1332 PROCEDURE Release_to_purchasing(
1333 itemtype in varchar2,
1334 itemkey in varchar2,
1335 actid in number,
1336 funcmode in varchar2,
1337 resultout in out nocopy varchar2) /* file.sql.39 change */
1338 IS
1339 l_line_rec OE_Order_PUB.Line_Rec_Type;
1340 l_header_rec OE_Order_PUB.Header_Rec_Type;
1341 l_drop_ship_line_rec OE_Purchase_Release_PVT.Drop_Ship_Line_Rec_Type;
1342 l_drop_ship_tbl OE_Purchase_Release_PVT.Drop_Ship_Tbl_Type;
1343 ll_drop_ship_tbl OE_Purchase_Release_PVT.Drop_Ship_Tbl_Type;
1344 l_return_status VARCHAR2(1);
1345 l_msg_count NUMBER;
1346 l_msg_data VARCHAR2(2000);
1347 l_dummy VARCHAR2(240);
1348 l_order_type_name VARCHAR2(40);
1349 l_user_name VARCHAR2(100);
1350 l_employee_id NUMBER;
1351 item_asset_flag VARCHAR2(1);
1352 item_expense_account NUMBER;
1353 org_material_account NUMBER;
1354 org_expense_account NUMBER;
1355 l_charge_account_id NUMBER;
1356 l_address_id NUMBER;
1357 l_deliver_to_location_id NUMBER;
1358 l_temp BOOLEAN; -- Fix for bug2097383
1359 -- OPM
1360 x_charge_account_id NUMBER;
1361 x_accrual_account_id NUMBER;
1362 l_allow_item_desc_update VARCHAR2(1);
1363 l_line_id NUMBER;
1364
1365 --
1366 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1367 --
1368 BEGIN
1369
1370 --
1371 -- RUN mode - normal process execution
1372 --
1373 if (funcmode = 'RUN') then
1374
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add('Entering Release to Purchasing line',1);
1377 END IF;
1378
1379 OE_STANDARD_WF.Set_Msg_Context(actid);
1380
1381 OE_Line_Util.Query_Row(p_line_id => to_number(itemkey), x_line_rec => l_line_rec);
1382 OE_Header_Util.Query_Row(p_header_id => l_line_rec.header_id, x_header_rec => l_header_rec);
1383
1384 --Bug2432009
1385 IF nvl(l_line_rec.source_type_code,'INTERNAL') = 'INTERNAL' THEN
1386 resultout := 'COMPLETE:NOT_ELIGIBLE';
1387 return;
1388 END IF;
1389
1390 OE_MSG_PUB.set_msg_context(
1391 p_entity_code => 'LINE'
1392 ,p_entity_id => l_line_rec.line_id
1393 ,p_header_id => l_line_rec.header_id
1394 ,p_line_id => l_line_rec.line_id
1395 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
1396 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
1397 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
1398 ,p_change_sequence => l_line_rec.change_sequence
1399 ,p_source_document_id => l_line_rec.source_document_id
1400 ,p_source_document_line_id => l_line_rec.source_document_line_id
1401 ,p_order_source_id => l_line_rec.order_source_id
1402 ,p_source_document_type_id => l_line_rec.source_document_type_id);
1403
1404 IF l_line_rec.ship_from_org_id is null THEN
1405 -- ship_from_org_id reqd
1406
1407 FND_MESSAGE.SET_NAME('ONT','OE_DS_WHSE_REQD');
1408 OE_MSG_PUB.Add;
1409 resultout := 'COMPLETE:INCOMPLETE';
1410 OE_STANDARD_WF.Save_Messages;
1411 OE_STANDARD_WF.Clear_Msg_Context;
1412 return;
1413 END IF;
1414
1415 BEGIN
1416 IF l_debug_level > 0 THEN
1417 oe_debug_pub.add( 'P1' , 1 ) ;
1418 END IF;
1419 SELECT name
1420 INTO l_order_type_name
1421 FROM oe_order_types_v
1422 WHERE order_type_id = l_header_rec.order_type_id;
1423
1424 IF l_debug_level > 0 THEN
1425 oe_debug_pub.add( 'P2' , 1 ) ;
1426 END IF;
1427 SELECT fu.user_name,nvl(fu.employee_id, -99)
1428 INTO l_user_name,l_employee_id
1429 FROM fnd_user fu
1430 WHERE fu.user_id = l_line_rec.created_by; --bug 4682158
1431
1432 IF l_debug_level > 0 THEN
1433 oe_debug_pub.add( 'P3' , 1 ) ;
1434 END IF;
1435 SELECT inventory_asset_flag,expense_account,allow_item_desc_update_flag
1436 into item_asset_flag,item_expense_account,l_allow_item_desc_update
1437 FROM mtl_system_items
1438 WHERE inventory_item_id = l_line_rec.inventory_item_id
1439 AND organization_id = l_line_rec.ship_from_org_id;
1440
1441 IF l_debug_level > 0 THEN
1442 oe_debug_pub.add( 'P4' , 1 ) ;
1443 END IF;
1444 SELECT material_account,expense_account
1445 into org_material_account,org_expense_account
1446 FROM mtl_parameters
1447 WHERE organization_id = l_line_rec.ship_from_org_id;
1448
1449 IF l_debug_level > 0 THEN
1450 oe_debug_pub.add( 'P5' , 1 ) ;
1451 END IF;
1452 BEGIN
1453 /* MOAC_SQL_CHANGE */
1454 SELECT LOC.LOCATION_ID
1455 INTO l_deliver_to_location_id
1456 FROM HZ_LOCATIONS LOC,
1457 HZ_PARTY_SITES PARTY,
1458 HZ_CUST_ACCT_SITES ACCT,
1459 HZ_CUST_SITE_USES_ALL CUST
1460 WHERE CUST.SITE_USE_ID=L_LINE_REC.SHIP_TO_ORG_ID
1461 AND CUST.SITE_USE_CODE='SHIP_TO'
1462 AND CUST.STATUS='A'
1463 AND ACCT.STATUS='A' --2752321
1464 AND ACCT.ORG_ID = CUST.ORG_ID
1465 AND CUST.CUST_ACCT_SITE_ID=ACCT.CUST_ACCT_SITE_ID AND ACCT.PARTY_SITE_ID=PARTY.PARTY_SITE_ID
1466 AND PARTY.LOCATION_ID=LOC.LOCATION_ID;
1467
1468 EXCEPTION
1469 WHEN NO_DATA_FOUND THEN
1470 IF l_debug_level > 0 THEN
1471 oe_debug_pub.add( 'UNABLE TO ASSOCIATE RECEIVING LOCATION ; OEXWSCHB.PLS ' , 1 ) ;
1472 END IF;
1473 FND_MESSAGE.SET_NAME('ONT','OE_DS_NO_LOC_LINK');
1474 OE_MSG_PUB.Add;
1475 resultout := 'COMPLETE:INCOMPLETE';
1476 OE_STANDARD_WF.Save_Messages;
1477 OE_STANDARD_WF.Clear_Msg_Context;
1478 return;
1479 END;
1480
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 RAISE;
1484 END;
1485 -- locking the model line so that scheduling and other fields can be updated
1486 BEGIN
1487 SELECT line_id
1488 INTO l_line_id
1489 FROM oe_order_lines_all
1490 WHERE line_id = l_line_rec.line_id
1491 FOR UPDATE NOWAIT;
1492 EXCEPTION
1493 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1494 IF l_debug_level > 0 THEN
1495 oe_debug_pub.add('OEXWSCHB.pls: unable to lock the line:'||l_line_id,1);
1496 END IF;
1497 resultout := 'DEFERRED';
1498 OE_STANDARD_WF.Clear_Msg_Context;
1499 RETURN;
1500 END;
1501
1502 IF item_asset_flag = 'Y' THEN
1503 l_charge_account_id := org_material_account;
1504 ELSE
1505 IF item_expense_account is null THEN
1506 l_charge_account_id := org_expense_account;
1507 ELSE
1508 l_charge_account_id := item_expense_account;
1509 END IF;
1510 END IF;
1511
1512 /* IF INV_GMI_RSV_BRANCH.Process_Branch(p_organization_id => l_line_rec.ship_from_org_id) -- INVCONV
1513 THEN
1514 GMI_RESERVATION_UTIL.get_OPM_account
1515 (
1516 v_dest_org_id => l_line_rec.ship_from_org_id,
1517 v_apps_item_id => l_line_rec.inventory_item_id,
1518 v_vendor_site_id => l_line_rec.org_id ,
1519 x_cc_id => x_charge_account_id,
1520 x_ac_id => x_accrual_account_id
1521 );
1522 l_charge_account_id := x_charge_account_id;
1523 --
1524 END IF; */
1525
1526 IF l_debug_level > 0 THEN
1527 oe_debug_pub.add('Charge account id : ' ||l_charge_account_id,1);
1528 END IF;
1529
1530 l_drop_ship_line_rec.header_id := l_line_rec.header_id;
1531 l_drop_ship_line_rec.order_type_name := l_order_type_name;
1532 l_drop_ship_line_rec.order_number := l_header_rec.order_number;
1533 l_drop_ship_line_rec.line_number := l_line_rec.line_number;
1534 l_drop_ship_line_rec.line_id := l_line_rec.line_id;
1535 l_drop_ship_line_rec.ship_from_org_id := l_line_rec.ship_from_org_id;
1536 l_drop_ship_line_rec.item_type_code := l_line_rec.item_type_code;
1537 l_drop_ship_line_rec.inventory_item_id := l_line_rec.inventory_item_id;
1538 l_drop_ship_line_rec.open_quantity := l_line_rec.ordered_quantity;
1539 l_drop_ship_line_rec.uom_code := l_line_rec.order_quantity_uom;
1540 l_drop_ship_line_rec.open_quantity2 := l_line_rec.ordered_quantity2; -- OPM
1541 l_drop_ship_line_rec.uom2_code := l_line_rec.ordered_quantity_uom2; -- OPM
1542 l_drop_ship_line_rec.preferred_grade := l_line_rec.preferred_grade; -- OPM
1543 l_drop_ship_line_rec.project_id := l_line_rec.project_id;
1544 l_drop_ship_line_rec.task_id := l_line_rec.task_id;
1545 l_drop_ship_line_rec.end_item_unit_number := l_line_rec.end_item_unit_number;
1546 l_drop_ship_line_rec.user_name := l_user_name;
1547 l_drop_ship_line_rec.employee_id := l_employee_id;
1548 l_drop_ship_line_rec.schedule_ship_date := l_line_rec.schedule_ship_date;
1549 l_drop_ship_line_rec.request_date := l_line_rec.request_date;
1550 l_drop_ship_line_rec.source_type_code := l_line_rec.source_type_code;
1551 l_drop_ship_line_rec.charge_account_id := l_charge_account_id;
1552 l_drop_ship_line_rec.accrual_account_id := x_accrual_account_id; -- OPM
1553 l_drop_ship_line_rec.deliver_to_location_id := l_deliver_to_location_id;
1554 l_drop_ship_line_rec.unit_list_price := l_line_rec.unit_list_price;
1555
1556 -- bug 2509121, pass user_item_description to PO.
1557 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509'
1558 AND l_line_rec.user_item_description IS NOT NULL
1559 AND nvl(l_allow_item_desc_update, 'N') = 'Y' THEN
1560 l_drop_ship_line_rec.item_description := l_line_rec.user_item_description;
1561 ELSE
1562 l_drop_ship_line_rec.item_description := null;
1563 END IF;
1564
1565 l_drop_ship_tbl(1) := l_drop_ship_line_rec;
1566
1567 IF l_debug_level > 0 THEN
1568 oe_debug_pub.add('Calling Purchase Release' ) ;
1569 END IF;
1570
1571 OE_Purchase_Release_PVT.Purchase_Release
1572 (p_api_version_number => 1.0
1573 ,p_drop_ship_tbl => l_drop_ship_tbl
1574 ,x_drop_ship_tbl => ll_drop_ship_tbl
1575 ,p_mode => 'ONLINE'
1576 ,x_return_status => l_return_status
1577 ,x_msg_count => l_msg_count
1578 ,x_msg_data => l_msg_data
1579 );
1580
1581 IF l_debug_level > 0 THEN
1582 OE_DEBUG_PUB.add('Return status : '||ll_drop_ship_tbl(1).return_status,1);
1583 END IF;
1584
1585
1586 -- Fix for the bug2097383
1587 IF ll_drop_ship_tbl(1).return_status <> FND_API.G_RET_STS_SUCCESS THEN
1588
1589 -- #5873209, do not call fnd api, instead just set the retcode (in OEXCDSPB.pls)
1590 /*IF ll_drop_ship_tbl(1).return_status = 'E' THEN
1591 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','');
1592 ELSIF ll_drop_ship_tbl(1).return_status='U' THEN
1593 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1594 END IF;*/
1595
1596 -- Changes for Bug - 2352589
1597 IF ll_drop_ship_tbl(1).result = 'ONHOLD' THEN
1598 resultout := 'COMPLETE:ON_HOLD';
1599 ELSE
1600 resultout := 'COMPLETE:INCOMPLETE';
1601 END IF;
1602
1603 OE_STANDARD_WF.Save_Messages;
1604 OE_STANDARD_WF.Clear_Msg_Context;
1605 return;
1606 END IF;
1607
1608 resultout := 'COMPLETE:COMPLETE';
1609 OE_STANDARD_WF.Clear_Msg_Context;
1610 return;
1611 end if;
1612
1613
1614 IF (funcmode = 'CANCEL') THEN
1615 null;
1616 END IF;
1617
1618
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 wf_core.context('OE_OEOL_SCH', 'Release_to_purchasing',
1622 itemtype, itemkey, to_char(actid), funcmode);
1623 -- start data fix project
1624 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1625 p_itemtype => itemtype,
1626 p_itemkey => itemkey);
1627 OE_STANDARD_WF.Save_Messages;
1628 OE_STANDARD_WF.Clear_Msg_Context;
1629 -- end data fix project
1630 raise;
1631 END Release_to_purchasing;
1632
1633 /*-----------------------------------------------------------------------
1634 Proceudure : Is Line Scheduled
1635 Description: This procedure checks to see if the line is scheduled or not.
1636 This procedure will be called before the line is deferred
1637 in deferred scheduling workflow activity. We should not defer
1638 a scheduled line, or a service or a return line.
1639 ----------------------------------------------------------------------- */
1640
1641 PROCEDURE Is_Line_Scheduled(
1642 itemtype in varchar2,
1643 itemkey in varchar2,
1644 actid in number,
1645 funcmode in varchar2,
1646 resultout in out nocopy varchar2) /* file.sql.39 change */
1647 IS
1648 l_item_type_code VARCHAR2(30);
1649 l_schedule_status_code VARCHAR2(30);
1650 l_line_category_code VARCHAR2(30);
1651 l_subscription_enable_flag CHAR(1) := ''; --sol_ord_er #16014165
1652
1653 --
1654 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1655 --
1656 BEGIN
1657 --
1658 -- RUN mode - normal process execution
1659 --
1660
1661 if (funcmode = 'RUN') then
1662
1663 IF l_debug_level > 0 THEN
1664 oe_debug_pub.add( 'ENTERING IS_LINE_SCHEDULED WORKFLOW COVER ' ) ;
1665 END IF;
1666
1667 IF l_debug_level > 0 THEN
1668 oe_debug_pub.add( 'ITEM KEY IS ' || ITEMKEY ) ;
1669 END IF;
1670
1671 OE_STANDARD_WF.Set_Msg_Context(actid);
1672
1673
1674 BEGIN
1675 SELECT item_type_code,schedule_status_code ,line_category_code,
1676 subscription_enable_flag --sol_ord_er #16014165
1677 INTO l_item_type_code,l_schedule_status_code ,l_line_category_code,
1678 l_subscription_enable_flag --sol_ord_er #16014165
1679 FROM oe_order_lines_all
1680 WHERE line_id = to_number(itemkey);
1681 EXCEPTION
1682 WHEN OTHERS THEN
1683 raise;
1684 END;
1685
1686 IF (l_schedule_status_code is not null) THEN
1687 -- Line is already scheduled.
1688 resultout := 'COMPLETE:COMPLETE';
1689 return;
1690 END IF;
1691
1692 IF (l_item_type_code = OE_GLOBALS.G_ITEM_SERVICE) OR
1693 (l_line_category_code = 'RETURN') OR
1694 NVL(l_subscription_enable_flag,'N') = 'Y' --sol_ord_er #16014165
1695 THEN
1696
1697 -- This is a service line or a return line. We will complete
1698 -- this activity with 'NOT_ELIGIBLE'
1699
1700 resultout := 'COMPLETE:NOT_ELIGIBLE';
1701 return;
1702
1703 END IF;
1704
1705 -- Line is not scheduled, nor is it a service or return line.
1706 resultout := 'COMPLETE:INCOMPLETE';
1707 OE_STANDARD_WF.Clear_Msg_Context;
1708 return;
1709 end if;
1710
1711 IF (funcmode = 'CANCEL') THEN
1712 null;
1713 return;
1714 END IF;
1715
1716 EXCEPTION
1717 WHEN OTHERS THEN
1718 wf_core.context('OE_OEOL_SCH', 'Is_Line_Scheduled',
1719 itemtype, itemkey, to_char(actid), funcmode);
1720 -- start data fix project
1721 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1722 p_itemtype => itemtype,
1723 p_itemkey => itemkey);
1724 OE_STANDARD_WF.Save_Messages;
1725 OE_STANDARD_WF.Clear_Msg_Context;
1726 -- end data fix project
1727 raise;
1728 END Is_Line_Scheduled;
1729
1730
1731 PROCEDURE Is_Line_Firmed(
1732 itemtype in varchar2,
1733 itemkey in varchar2,
1734 actid in number,
1735 funcmode in varchar2,
1736 resultout in out nocopy varchar2) /* file.sql.39 change */
1737 IS
1738 l_item_type_code VARCHAR2(30);
1739 l_firm_demand_flag VARCHAR2(1);
1740 l_line_category_code VARCHAR2(30);
1741 l_shipped_quantity NUMBER;
1742 l_fulfilled_flag VARCHAR2(1);
1743 l_open_flag VARCHAR2(1);
1744 l_cancelled_flag VARCHAR2(1);
1745 l_source_type_code VARCHAR2(30);
1746 l_subscription_enable_flag CHAR(1) := ''; --sol_ord_er #16014165
1747 BEGIN
1748 --
1749 -- RUN mode - normal process execution
1750 --
1751
1752 oe_debug_pub.add('Entering Is_Line_Firmed Workflow cover ',1 );
1753 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL > '110509' THEN
1754 if (funcmode = 'RUN') then
1755
1756
1757 oe_debug_pub.add('Item Key is ' || itemkey );
1758 OE_STANDARD_WF.Set_Msg_Context(actid);
1759
1760
1761 BEGIN
1762 --Select all required attributes, listing few attributes here.
1763 SELECT item_type_code,firm_demand_flag ,line_category_code, shipped_quantity,
1764 fulfilled_flag,open_flag,cancelled_flag,source_type_code,
1765 subscription_enable_flag --sol_ord_er #16014165
1766 INTO l_item_type_code,l_firm_demand_flag ,l_line_category_code, l_shipped_quantity,
1767 l_fulfilled_flag,l_open_flag,l_cancelled_flag,l_source_type_code,
1768 l_subscription_enable_flag --sol_ord_er #16014165
1769 FROM oe_order_lines_all
1770 WHERE line_id = to_number(itemkey);
1771
1772 EXCEPTION
1773 WHEN OTHERS THEN
1774 raise;
1775 END;
1776
1777 IF nvl(l_firm_demand_flag,'N') = 'Y' THEN
1778 -- Line is already scheduled.
1779 resultout := 'COMPLETE:COMPLETE';
1780 return;
1781 END IF;
1782
1783 IF l_item_type_code = 'SERVICE' OR
1784 l_source_type_code = 'EXTERNAL' OR
1785 l_shipped_quantity is not null OR
1786 nvl(l_cancelled_flag,'N') = 'Y' OR
1787 l_open_flag = 'N' OR
1788 nvl(l_fulfilled_flag,'N') = 'Y' OR
1789 l_line_category_code = 'RETURN' OR
1790 NVL(l_subscription_enable_flag,'N') = 'Y' --sol_ord_er #16014165
1791 THEN
1792
1793 -- This is a service line or a return line. We will complete
1794 -- this activity with 'NOT_ELIGIBLE'
1795
1796 resultout := 'COMPLETE:NOT_ELIGIBLE';
1797 return;
1798
1799 END IF;
1800
1801 -- Line is not firmed.
1802 resultout := 'COMPLETE:INCOMPLETE';
1803 OE_STANDARD_WF.Clear_Msg_Context;
1804 return;
1805 end if;
1806
1807 IF (funcmode = 'CANCEL') THEN
1808 null;
1809 return;
1810 END IF;
1811 END IF; -- Relase control.
1812 return;
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 oe_debug_pub.add('Error in Workflow',1);
1816 wf_core.context('OE_OEOL_SCH', 'Is_Line_Firmed',
1817 itemtype, itemkey, to_char(actid), funcmode);
1818 -- start data fix project
1819 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1820 p_itemtype => itemtype,
1821 p_itemkey => itemkey);
1822 OE_STANDARD_WF.Save_Messages;
1823 OE_STANDARD_WF.Clear_Msg_Context;
1824 -- end data fix project
1825 raise;
1826 END Is_Line_Firmed;
1827
1828 PROCEDURE Firm_demand(
1829 itemtype in varchar2,
1830 itemkey in varchar2,
1831 actid in number,
1832 funcmode in varchar2,
1833 resultout in out nocopy varchar2) /* file.sql.39 change */
1834 IS
1835 l_firm_demand_flag VARCHAR2(1);
1836 l_top_model_line_id NUMBER;
1837 l_ato_line_id NUMBER;
1838 l_ship_model_complete_flag VARCHAR2(1);
1839 --variable added for bug 3814076
1840 l_itemkey NUMBER;
1841
1842 CURSOR model is
1843 SELECT ola.line_id line_id
1844 FROM oe_order_lines_all ola,
1845 wf_item_activity_statuses wias,
1846 wf_process_activities wpa
1847 WHERE top_model_line_id = nvl(l_top_model_line_id, ola.top_model_line_id)
1848 And ato_line_id = nvl(l_ato_line_id, ola.ato_line_id)
1849 And wias.item_key = ola.line_id
1850 And wias.item_type = 'OEOL'
1851 And wias.process_activity = wpa.instance_id
1852 And wpa.ACTIVITY_ITEM_TYPE = 'OEOL'
1853 And wpa.activity_name = 'FIRM_ELIGIBLE'
1854 And wias.activity_status = 'NOTIFIED';
1855
1856 BEGIN
1857
1858 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL > '110509' THEN
1859 IF (funcmode = 'RUN') THEN
1860
1861 oe_debug_pub.add('Within Firm Demand Workflow cover ',1 );
1862 oe_debug_pub.add('Item Key is ' || itemkey );
1863
1864 OE_STANDARD_WF.Set_Msg_Context(actid);
1865
1866 Select firm_demand_flag, top_model_line_id, ato_line_id,
1867 ship_model_complete_flag
1868 Into l_firm_demand_flag, l_top_model_line_id, l_ato_line_id,
1869 l_ship_model_complete_flag
1870 From oe_order_lines_all
1871 Where line_id = to_number(itemkey);
1872
1873 IF Nvl(l_firm_demand_flag,'N') = 'N' THEN
1874 IF l_ship_model_complete_flag= 'Y' THEN
1875
1876 oe_debug_pub.add('Top model' || l_top_model_line_id );
1877 oe_debug_pub.add('Ato Model ' || l_top_model_line_id );
1878 oe_debug_pub.add('SMC ' || l_ship_model_complete_flag );
1879 Select firm_demand_flag
1880 Into l_firm_demand_flag
1881 From oe_order_lines
1882 Where line_id = l_top_model_line_id
1883 For Update;
1884
1885 Update oe_order_lines
1886 Set firm_demand_flag = 'Y'
1887 Where top_model_line_id = l_top_model_line_id;
1888
1889 l_ato_line_id := Null;
1890
1891 FOR I IN model LOOP
1892 IF I.line_id <> to_number(itemkey) THEN
1893 BEGIN --15870313
1894 WF_ENGINE.CompleteActivityInternalName(
1895 itemtype => 'OEOL',
1896 itemkey => to_char(I.line_id) ,
1897 activity => 'FIRM_ELIGIBLE',
1898 result => 'COMPLETED',
1899 raise_engine_exception => TRUE );
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 oe_debug_pub.add('Workflow complete activity failed. Raising');
1903 RAISE ;
1904 END ;
1905 END IF;
1906 END LOOP;
1907
1908
1909 ELSIF l_ato_line_id is not null THEN
1910
1911 Select firm_demand_flag
1912 Into l_firm_demand_flag
1913 From oe_order_lines
1914 Where line_id = l_ato_line_id
1915 For Update;
1916
1917 Update oe_order_lines
1918 Set firm_demand_flag = 'Y'
1919 Where ato_line_id = l_ato_line_id;
1920
1921 l_top_model_line_id := Null;
1922
1923 -- Update the firm flag and also move them from firm eligible block.
1924 -- The below api will be called in a loop.
1925
1926 FOR I IN model LOOP
1927 IF I.line_id <> to_number(itemkey) THEN
1928 BEGIN --15870313
1929 WF_ENGINE.CompleteActivityInternalName(
1930 itemtype => 'OEOL',
1931 itemkey => to_char(I.line_id) ,
1932 activity => 'FIRM_ELIGIBLE',
1933 result => 'COMPLETED',
1934 raise_engine_exception => TRUE);
1935 exception
1936 WHEN OTHERS THEN
1937 oe_debug_pub.add('Workflow complete activity failed. Raising');
1938 RAISE ;
1939 END ;
1940 END IF;
1941 END LOOP;
1942 ELSE
1943
1944 --bug 3814076
1945 l_itemkey := to_number(itemkey);
1946 Select firm_demand_flag
1947 Into l_firm_demand_flag
1948 From oe_order_lines
1949 Where line_id = l_itemkey
1950 For Update;
1951
1952 Update oe_order_lines
1953 Set firm_demand_flag = 'Y'
1954 Where line_id = to_number(itemkey);
1955
1956 -- Update the firm flag and also move them from firm eligible block.
1957 /* WF_ENGINE.CompleteActivityInternalName(
1958 itemtype => 'OEOL',
1959 itemkey => itemkey,
1960 activity => 'FIRM_ELIGIBLE',
1961 result => 'COMPLETED');
1962 */
1963 END IF;
1964
1965 ELSE
1966
1967 oe_debug_pub.add('Line is already firmed' );
1968
1969 END IF;
1970 resultout := 'COMPLETE:COMPLETE';
1971 OE_STANDARD_WF.Clear_Msg_Context;
1972 return;
1973 END IF;
1974
1975 IF (funcmode = 'CANCEL') THEN
1976 null;
1977 return;
1978 END IF;
1979 END IF; -- Release.
1980 Return;
1981 EXCEPTION
1982 WHEN OTHERS THEN
1983 wf_core.context('OE_OEOL_SCH', 'Firm_Demand',
1984 itemtype, itemkey, to_char(actid), funcmode);
1985 oe_debug_pub.add('error in workflow');
1986 -- start data fix project
1987 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1988 p_itemtype => itemtype,
1989 p_itemkey => itemkey);
1990 OE_STANDARD_WF.Save_Messages;
1991 OE_STANDARD_WF.Clear_Msg_Context;
1992 -- end data fix project
1993 raise;
1994
1995 End Firm_Demand;
1996 END OE_OEOL_SCH;