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