1 PACKAGE BODY OE_SCH_CONC_REQUESTS AS
2 /* $Header: OEXCSCHB.pls 120.20 2006/07/13 11:32:53 rmoharan noship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_SCH_CONC_REQUESTS';
6 --5166476
7 /*
8 TYPE status_arr IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
9 OE_line_status_Tbl status_arr;
10 */
11
12 FUNCTION model_processed(p_model_id IN NUMBER
13 ,p_line_id IN NUMBER)
14 RETURN BOOLEAN
15 IS
16 l_found BOOLEAN:= FALSE;
17 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
18 BEGIN
19
20 /* If many of the lines selected are part of a smc/ato/non-smc model, then delayed
21 * request must get logged only for one of the lines.
22 */
23 IF oe_model_id_tbl.EXISTS(p_model_id) THEN
24 l_found := TRUE;
25 ELSIF p_model_id = p_line_id THEN
26 oe_model_id_tbl(p_model_id) := p_model_id;
27 END IF;
28 RETURN (l_found);
29 END model_processed;
30
31 FUNCTION included_processed(p_inc_item_id IN NUMBER)
32 RETURN BOOLEAN
33 IS
34 l_found BOOLEAN:= FALSE;
35 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
36 BEGIN
37
38 /* to list the included items processed alone
39 */
40 IF l_debug_level > 0 THEN
41 OE_DEBUG_PUB.Add('INSIDE INCLUDED_PROCESSED',1);
42 END IF;
43
44 IF oe_included_id_tbl.EXISTS(p_inc_item_id) THEN
45 l_found := TRUE;
46 ELSE
47 oe_included_id_tbl(p_inc_item_id) := p_inc_item_id;
48 END IF;
49 IF l_found THEN
50 IF l_debug_level > 0 THEN
51 OE_DEBUG_PUB.Add('INCLIDED ITEM LISTED',1);
52 END IF;
53 ELSE
54 IF l_debug_level > 0 THEN
55 OE_DEBUG_PUB.Add('INCLIDED ITEM NOT LISTED',1);
56 END IF;
57 END IF;
58 RETURN (l_found);
59 END included_processed;
60
61 FUNCTION set_processed(p_set_id IN NUMBER)
62 RETURN BOOLEAN
63 IS
64 l_found BOOLEAN:= FALSE;
65 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
66 BEGIN
67
68 /* If many of the lines selected are part of a ship set / Arrival set, then delayed
69 * request must get logged only for one of the lines.
70 */
71 IF oe_set_id_tbl.EXISTS(p_set_id) THEN
72 l_found := TRUE;
73 ELSE
74 oe_set_id_tbl(p_set_id) := p_set_id;
75 END IF;
76 RETURN (l_found);
77 END set_processed;
78
79 FUNCTION Line_Eligible (p_line_id IN NUMBER)
80 RETURN BOOLEAN
81 IS
82 l_activity_status_code VARCHAR2(8);
83 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
84 BEGIN
85
86 -- Check for workflow status to be Purchase Release Eligible
87 SELECT ACTIVITY_STATUS
88 INTO l_activity_status_code
89 FROM wf_item_activity_statuses wias, wf_process_activities wpa
90 WHERE wias.item_type = 'OEOL' AND
91 wias.item_key = to_char(p_line_id) AND
92 wias.process_activity = wpa.instance_id AND
93 wpa.activity_item_type = 'OEOL' AND
94 wpa.activity_name = 'SCHEDULING_ELIGIBLE' AND
95 wias.activity_status = 'NOTIFIED';
96
97 -- Return true since the record exists.
98 RETURN TRUE;
99
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 IF l_debug_level > 0 THEN
103 oe_debug_pub.add( 'RETURNING FALSE 1 ' , 1 ) ;
104 END IF;
105 RETURN FALSE;
106 WHEN OTHERS THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END Line_Eligible;
109
110 /*-----------------------------------------------------------------
111 PROCEDURE : Request
112 DESCRIPTION: Schedule Orders Concurrent Request
113 -----------------------------------------------------------------*/
114 PROCEDURE Request (ERRBUF OUT NOCOPY VARCHAR2,
115 RETCODE OUT NOCOPY VARCHAR2,
116 /* Moac */
117 p_org_id IN NUMBER,
118 p_order_number_low IN NUMBER,
119 p_order_number_high IN NUMBER,
120 p_request_date_low IN VARCHAR2,
121 p_request_date_high IN VARCHAR2,
122 p_customer_po_number IN VARCHAR2,
123 p_ship_to_location IN VARCHAR2,
124 p_order_type IN VARCHAR2,
125 p_customer IN VARCHAR2,
126 p_ordered_date_low IN VARCHAR2,
127 p_ordered_date_high IN VARCHAR2,
128 p_warehouse IN VARCHAR2,
129 p_item IN VARCHAR2,
130 p_demand_class IN VARCHAR2,
131 p_planning_priority IN VARCHAR2,
132 p_shipment_priority IN VARCHAR2,
133 p_line_type IN VARCHAR2,
134 p_line_request_date_low IN VARCHAR2,
135 p_line_request_date_high IN VARCHAR2,
136 p_line_ship_to_location IN VARCHAR2,
137 p_sch_ship_date_low IN VARCHAR2,
138 p_sch_ship_date_high IN VARCHAR2,
139 p_sch_arrival_date_low IN VARCHAR2,
140 p_sch_arrival_date_high IN VARCHAR2,
141 p_booked IN VARCHAR2,
142 p_sch_mode IN VARCHAR2,
143 p_dummy1 IN VARCHAR2,
144 p_dummy2 IN VARCHAR2,
145 p_apply_warehouse IN VARCHAR2,
146 p_apply_sch_date IN VARCHAR2,
147 p_order_by_first IN VARCHAR2,
148 p_order_by_sec IN VARCHAR2)
149 IS
150 l_apply_sch_date DATE;
151 l_arrival_set_id NUMBER;
152 l_ato_line_id NUMBER;
153 l_atp_tbl OE_ATP.Atp_Tbl_Type;
154 l_booked_flag VARCHAR2(1);
155 l_control_rec OE_GLOBALS.Control_Rec_Type;
156 l_cursor_id INTEGER;
157 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
158 l_found BOOLEAN;
159 l_header_id NUMBER;
160 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
161 l_item_type_code VARCHAR2(30);
162 l_line_id NUMBER;
163 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
164 l_line_request_date_high DATE;
165 l_line_request_date_low DATE;
166 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
167 l_msg_count NUMBER;
168 l_msg_data VARCHAR2(2000) := NULL;
169 l_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
170 l_order_date_type_code VARCHAR2(30);
171 l_ordered_date_high DATE;
172 l_ordered_date_low DATE;
173 l_process_order BOOLEAN := FALSE;
174 l_rec_failure NUMBER := 0;
175 l_rec_processed NUMBER := 0;
176 l_rec_success NUMBER := 0;
177 l_request_date DATE;
178 l_request_date_high DATE;
179 l_request_date_low DATE;
180 l_request_id VARCHAR2(50);
181 l_return_status VARCHAR2(1);
182 l_retval INTEGER;
183 l_sch_arrival_date_high DATE;
184 l_sch_arrival_date_low DATE;
185 l_sch_ship_date_high DATE;
186 l_sch_ship_date_low DATE;
187 l_schedule_status_code VARCHAR2(30);
188 l_ship_from_org_id NUMBER;
189 l_ship_set_id NUMBER;
190 l_smc_flag VARCHAR2(1);
191 l_stmt VARCHAR2(2000);
192 l_temp_flag BOOLEAN; -- temp variable (re-usable).
193 l_temp_line_id NUMBER;
194 l_temp_num NUMBER; -- temp variable (re-usable).
195 l_top_model_line_id NUMBER;
196 l_link_to_line_id NUMBER;
197
198 -- Moac
199 l_single_org BOOLEAN := FALSE;
200 l_old_org_id NUMBER := -99;
201 l_org_id NUMBER;
202 l_selected_line_tbl OE_GLOBALS.Selected_Record_Tbl; -- R12.MOAC
203 l_failure BOOLEAN := FALSE;
204 l_index NUMBER;
205 -- Moac. Changed the below cursor logic to also join to oe_order_lines for OU.
206 CURSOR wf_item IS
207 SELECT item_key, l.org_id
208 FROM wf_item_activity_statuses wias,
209 wf_process_activities wpa,
210 oe_order_lines l
211 WHERE wias.item_type = 'OEOL'
212 AND wias.process_activity = wpa.instance_id
213 AND wpa.activity_item_type = 'OEOL'
214 AND wpa.activity_name = 'SCHEDULING_ELIGIBLE'
215 AND wias.activity_status = 'NOTIFIED'
216 AND wias.item_key = l.line_id
217 Order by l.org_id;
218
219 CURSOR progress_pto IS
220 SELECT line_id
221 FROM oe_order_lines_all
222 WHERE header_id = l_header_id
223 AND top_model_line_id = l_line_id
224 AND item_type_code in ('MODEL','KIT','CLASS','OPTION')
225 AND ((ato_line_id is not null AND
226 ato_line_id = line_id) OR
227 ato_line_id is null)
228 AND open_flag = 'Y';
229
230 BEGIN
231 --Bug#4220950
232 ERRBUF := 'Schedule Orders Request completed successfully';
233 RETCODE := 0;
234
235 -- Moac Start
236 IF MO_GLOBAL.get_access_mode = 'S' THEN
237 l_single_org := true;
238 ELSIF p_org_id IS NOT NULL THEN
239 l_single_org := true;
240 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
241 END IF;
242 -- Moac End.
243
244 -- Turning debug on for testing purpose.
245 fnd_file.put_line(FND_FILE.LOG, 'Parameters:');
246 fnd_file.put_line(FND_FILE.LOG, ' order_number_low = '||
247 p_order_number_low);
248 fnd_file.put_line(FND_FILE.LOG, ' order_number_high = '||
249 p_order_number_high);
250 fnd_file.put_line(FND_FILE.LOG, ' request_date_low = '||
251 p_request_date_low);
252 fnd_file.put_line(FND_FILE.LOG, ' request_date_high = '||
253 p_request_date_high);
254 fnd_file.put_line(FND_FILE.LOG, ' customer_po_number = '||
255 p_customer_po_number);
256 fnd_file.put_line(FND_FILE.LOG, ' ship_to_location = '||
257 p_ship_to_location);
258 fnd_file.put_line(FND_FILE.LOG, ' order_type = '||
259 p_order_type);
260 fnd_file.put_line(FND_FILE.LOG, ' customer = '||
261 p_customer);
262 fnd_file.put_line(FND_FILE.LOG, ' item = '||
263 p_item);
264 fnd_file.put_line(FND_FILE.LOG, ' ordered_date_low = ' ||
265 p_ordered_date_low);
266 fnd_file.put_line(FND_FILE.LOG, ' ordered_date_high = ' ||
267 p_ordered_date_high);
268 fnd_file.put_line(FND_FILE.LOG, ' warehouse = ' ||
269 p_warehouse);
270 fnd_file.put_line(FND_FILE.LOG, ' demand_class = ' ||
271 p_demand_class);
272 fnd_file.put_line(FND_FILE.LOG, ' planning_priority = ' ||
273 p_planning_priority);
274 fnd_file.put_line(FND_FILE.LOG, ' shipment_priority = ' ||
275 p_shipment_priority);
276 fnd_file.put_line(FND_FILE.LOG, ' line_type = ' ||
277 p_line_type);
278 fnd_file.put_line(FND_FILE.LOG, ' line_request_date_low = ' ||
279 p_line_request_date_low);
280 fnd_file.put_line(FND_FILE.LOG, ' line_request_date_high = ' ||
281 p_line_request_date_high);
282 fnd_file.put_line(FND_FILE.LOG, ' line_ship_to_location = ' ||
283 p_line_ship_to_location);
284 fnd_file.put_line(FND_FILE.LOG, ' sch_ship_date_low = ' ||
285 p_sch_ship_date_low);
286 fnd_file.put_line(FND_FILE.LOG, ' sch_ship_date_high = ' ||
287 p_sch_ship_date_high);
288 fnd_file.put_line(FND_FILE.LOG, ' sch_arrival_date_low = ' ||
289 p_sch_arrival_date_low);
290 fnd_file.put_line(FND_FILE.LOG, ' sch_arrival_date_high = ' ||
291 p_sch_arrival_date_high);
292 fnd_file.put_line(FND_FILE.LOG, ' booked = ' ||
293 p_booked);
294 fnd_file.put_line(FND_FILE.LOG, ' sch_mode = ' ||
295 p_sch_mode);
296 fnd_file.put_line(FND_FILE.LOG, ' dummy1 = ' ||
297 p_dummy1);
298 fnd_file.put_line(FND_FILE.LOG, ' apply_warehouse = ' ||
299 p_apply_warehouse);
300 fnd_file.put_line(FND_FILE.LOG, ' apply_sch_date = ' ||
301 p_apply_sch_date);
302 fnd_file.put_line(FND_FILE.LOG, ' order_by_first = ' ||
303 p_order_by_first);
304 fnd_file.put_line(FND_FILE.LOG, ' order_by_sec = ' ||
305 p_order_by_sec);
306
307 FND_PROFILE.Get(NAME => 'CONC_REQUEST_ID',
308 VAL => l_request_id);
309 OE_MSG_PUB.Initialize; -- Initializing message pub to clear messages.
310
311 IF p_sch_mode NOT IN ('SCHEDULE','RESCHEDULE') AND
312 (p_apply_sch_date IS NOT NULL OR
313 p_apply_warehouse IS NOT NULL)
314 THEN
315 Fnd_Message.set_name('ONT', 'ONT_SCH_INVALID_MODE_ATTRB');
316 Oe_Msg_Pub.Add;
317 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
318 l_msg_data := Fnd_Message.get_string('ONT',
319 'ONT_SCH_INVALID_MODE_ATTRB');
320 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
321 ERRBUF := 'ONT_SCH_INVALID_MODE_ATTRB';
322 IF l_debug_level > 0 THEN
323 OE_DEBUG_PUB.Add('Error : Schedule date supplied for wrong mode.',1);
324 END IF;
325 RETCODE := 2;
326 RETURN;
327 END IF;
328
329 -- Convert dates passed as varchar2 parameters to date variables.
330 SELECT fnd_date.canonical_to_date(p_request_date_low),
331 fnd_date.canonical_to_date(p_request_date_high),
332 fnd_date.canonical_to_date(p_ordered_date_low),
333 fnd_date.canonical_to_date(p_ordered_date_high),
334 fnd_date.canonical_to_date(p_line_request_date_low),
335 fnd_date.canonical_to_date(p_line_request_date_high),
336 fnd_date.canonical_to_date(p_sch_ship_date_low),
337 fnd_date.canonical_to_date(p_sch_ship_date_high),
338 fnd_date.canonical_to_date(p_sch_arrival_date_low),
339 fnd_date.canonical_to_date(p_sch_arrival_date_high)
340 -- fnd_date.canonical_to_date(p_apply_sch_date)
341 INTO l_request_date_low,
342 l_request_date_high,
343 l_ordered_date_low,
344 l_ordered_date_high,
345 l_line_request_date_low,
346 l_line_request_date_high,
347 l_sch_ship_date_low,
348 l_sch_ship_date_high,
349 l_sch_arrival_date_low,
350 l_sch_arrival_date_high
351 -- l_apply_sch_date
352 FROM DUAL;
353
354 SELECT fnd_date.chardt_to_date(p_apply_sch_date)
355 INTO l_apply_sch_date
356 FROM dual;
357
358 IF l_debug_level > 0 THEN
359 OE_DEBUG_PUB.Add('Schedule date'||l_apply_sch_date,1);
360 END IF;
361 /* When user does not specifiy any parameters, we drive the scheduling
362 * through workflow. Pick up all the lines which are schedule eligible
363 * and notified status, call wf_engine to complete the activity.
364 * If value is passed through any of the parameters, then get the header and
365 * line records and call wf_engine.
366 */
367 IF p_order_number_low IS NULL AND
368 p_order_number_high IS NULL AND
369 p_request_date_low IS NULL AND
370 p_request_date_high IS NULL AND
371 p_customer_po_number IS NULL AND
372 p_ship_to_location IS NULL AND
373 p_order_type IS NULL AND
374 p_customer IS NULL AND
375 p_item IS NULL AND
376 p_ordered_date_low IS NULL AND
377 p_ordered_date_high IS NULL AND
378 p_warehouse IS NULL AND
379 p_demand_class IS NULL AND
380 p_planning_priority IS NULL AND
381 p_shipment_priority IS NULL AND
382 p_line_type IS NULL AND
383 p_line_request_date_low IS NULL AND
384 p_line_request_date_high IS NULL AND
385 p_line_ship_to_location IS NULL AND
386 p_sch_ship_date_low IS NULL AND
387 p_sch_ship_date_high IS NULL AND
388 p_sch_arrival_date_low IS NULL AND
389 p_sch_arrival_date_high IS NULL AND
390 p_sch_mode IS NULL AND
391 nvl(p_booked, 'Y') = 'Y'
392 THEN
393 FOR k IN wf_item LOOP
394 IF l_debug_level > 0 THEN
395 oe_debug_pub.add('***** 1. Processing item key '||
396 k.item_key ||' *****', 1);
397 END IF;
398
399 -- Moac Start
400 IF NOT l_single_org and k.org_id <> l_old_org_id THEN
401 l_old_org_id := k.org_id;
402 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => k.org_id);
403 END IF;
404 -- Moac End
405
406 -- Need to check whether still line is eligible for processing
407 IF Line_Eligible(p_line_id => to_number(K.ITEM_KEY)) THEN
408
409 IF l_debug_level > 0 THEN
410 oe_debug_pub.add( 'COMPLETING ACTIVITY FOR : ' || K.ITEM_KEY , 1 ) ;
411 END IF;
412
413
414
415 g_process_records := 0;
416 g_failed_records := 0;
417
418 wf_engine.CompleteActivityInternalName
419 ('OEOL',
420 k.item_key,
421 'SCHEDULING_ELIGIBLE',
422 'COMPLETE');
423
424 OE_MSG_PUB.Count_And_Get
425 ( p_count => l_msg_count,
426 p_data => l_msg_data);
427
428 FOR I in 1..l_msg_count LOOP
429 l_msg_data := OE_MSG_PUB.Get(I,'F');
430
431 -- Write Messages in the log file
432 fnd_file.put_line(FND_FILE.LOG, l_msg_data);
433
434 END LOOP;
435 --5166476
436
437 --IF g_failed_records > 0 THEN
438 IF OE_SCH_CONC_REQUESTS.oe_line_status_tbl.EXISTS(k.item_key) AND
439 OE_SCH_CONC_REQUESTS.oe_line_status_tbl(k.item_key)= 'N' THEN
440 l_failure := TRUE;
441 END IF;
442 IF l_debug_level > 0 THEN
443 oe_debug_pub.add( 'R1 PROCESSED: '||l_rec_processed||' FAILED: '||l_rec_failure , 1 ) ;
444 END IF;
445
446 -- Moac
447 END IF;
448
449 END LOOP;
450 ELSE -- Some parameter is passed
451
452 -- Open cursor.
453 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
454
455 -- Building the dynamic query based on parameters passed.
456 -- Moac Changed below cursor to use oe_order_headers_all
457 /*Start MOAC_SQL_CHANGE */
458 l_stmt := 'SELECT H.header_id, L.Line_id, L.org_id ';
459 IF NVL(p_sch_mode, 'LINE_ELIGIBLE') = 'LINE_ELIGIBLE' THEN
460 l_stmt := l_stmt || 'FROM oe_order_headers_all H, oe_order_lines L, '
461 || ' wf_item_activity_statuses wias, wf_process_activities wpa ';
462 ELSE
463 l_stmt := l_stmt || 'FROM oe_order_headers_all H, oe_order_lines L ';
464 END IF;
465 l_stmt := l_stmt || 'WHERE H.header_id = L.header_id '
466 || 'AND H.org_id = L.org_id '
467 || 'AND H.open_flag = ''Y'''||' AND L.open_flag = ''Y''';
468 /*End MOAC_SQL_CHANGE */
469
470 -- Building where clause.
471 -- Moac Start
472 IF p_org_id is NOT NULL THEN
473 l_stmt := l_stmt || ' AND L.org_id = :org_id';
474 END IF;
475 -- Moac End
476
477 IF p_order_number_low IS NOT NULL THEN
478 l_stmt := l_stmt || ' AND H.order_number >= :order_number_low';
479 END IF;
480 IF p_order_number_high IS NOT NULL THEN
481 l_stmt := l_stmt || ' AND H.order_number <= :order_number_high';
482 END IF;
483 IF p_request_date_low IS NOT NULL THEN
484 l_stmt := l_stmt || ' AND H.request_date >= :request_date_low';
485 END IF;
486 IF p_request_date_high IS NOT NULL THEN
487 l_stmt := l_stmt || ' AND H.request_date <= :request_date_high';
488 END IF;
489 IF p_customer_po_number IS NOT NULL THEN
490 l_stmt := l_stmt || ' AND H.cust_po_number = :customer_po_number';
491 END IF;
492 IF p_ship_to_location IS NOT NULL THEN
493 l_stmt := l_stmt || ' AND H.ship_to_org_id = :ship_to_location';
494 END IF;
495 IF p_order_type IS NOT NULL THEN
496 l_stmt := l_stmt || ' AND H.order_type_id = :order_type';
497 END IF;
498 IF p_customer IS NOT NULL THEN
499 l_stmt := l_stmt || ' AND H.sold_to_org_id = :customer';
500 END IF;
501 IF p_item IS NOT NULL THEN
502 l_stmt := l_stmt || ' AND L.inventory_item_id = :item';
503 END IF;
504 IF p_ordered_date_low IS NOT NULL THEN
505 l_stmt := l_stmt || ' AND H.ordered_date >= :ordered_date_low';
506 END IF;
507 IF p_ordered_date_high IS NOT NULL THEN
508 l_stmt := l_stmt || ' AND H.ordered_date <= :ordered_date_high';
509 END IF;
510 IF p_warehouse IS NOT NULL THEN
511 l_stmt := l_stmt || ' AND L.ship_from_org_id = :warehouse';
512 END IF;
513 IF p_demand_class IS NOT NULL THEN
514 l_stmt := l_stmt || ' AND L.demand_class_code = :demand_class';
515 END IF;
516 IF p_planning_priority IS NOT NULL THEN
517 l_stmt := l_stmt || ' AND L.planning_priority = :planning_priority';
518 END IF;
519 IF p_shipment_priority IS NOT NULL THEN
520 l_stmt := l_stmt || ' AND L.shipment_priority_code = :shipment_priority';
521 END IF;
522 IF p_line_type IS NOT NULL THEN
523 l_stmt := l_stmt || ' AND L.line_type_id = :line_type';
524 END IF;
525 IF p_line_request_date_low IS NOT NULL THEN
526 l_stmt := l_stmt || ' AND L.request_date >= :line_request_date_low';
527 END IF;
528 IF p_line_request_date_high IS NOT NULL THEN
529 l_stmt := l_stmt || ' AND L.request_date <= :line_request_date_high';
530 END IF;
531 IF p_line_ship_to_location IS NOT NULL THEN
532 l_stmt := l_stmt || ' AND L.ship_to_org_id = :line_ship_to_location';
533 END IF;
534 IF p_sch_ship_date_low IS NOT NULL THEN
535 l_stmt := l_stmt || ' AND L.schedule_ship_date >= :sch_ship_date_low';
536 END IF;
537 IF p_sch_ship_date_high IS NOT NULL THEN
538 l_stmt := l_stmt || ' AND L.schedule_ship_date <= :sch_ship_date_high';
539 END IF;
540 IF p_sch_arrival_date_low IS NOT NULL THEN
541 l_stmt := l_stmt || ' AND L.schedule_arrival_date >= :sch_arrival_date_low';
542 END IF;
543 IF p_sch_arrival_date_high IS NOT NULL THEN
544 l_stmt := l_stmt || ' AND L.schedule_arrival_date <= :sch_arrival_date_high';
545 END IF;
546 IF p_booked IS NOT NULL THEN
547 l_stmt := l_stmt || ' AND L.booked_flag = :booked';
548 END IF;
549 IF p_sch_mode = 'SCHEDULE' THEN
550 l_stmt := l_stmt || ' AND L.schedule_status_code IS NULL';
551 ELSIF p_sch_mode IN ('UNSCHEDULE','RESCHEDULE','RESCHEDULE_RD') THEN
552 l_stmt := l_stmt || ' AND L.schedule_status_code IS NOT NULL';
553 ELSIF NVL(p_sch_mode, 'LINE_ELIGIBLE') = 'LINE_ELIGIBLE' THEN
554 l_stmt := l_stmt || ' AND wias.item_type = ''OEOL'''
555 || ' AND wias.process_activity = wpa.instance_id'
556 || ' AND wpa.activity_item_type = ''OEOL'''
557 || ' AND wpa.activity_name = ''SCHEDULING_ELIGIBLE'''
558 || ' AND wias.activity_status = ''NOTIFIED'''
559 || ' AND wias.item_key = to_char(L.line_id)';
560 END IF;
561
562 -- Building order by clause.
563 IF p_order_by_first IS NOT NULL THEN
564 l_stmt := l_stmt ||' ORDER BY L.'||p_order_by_first;
565 IF p_order_by_sec IS NOT NULL THEN
566 l_stmt := l_stmt ||', L.'||p_order_by_sec;
567 END IF;
568 ELSIF p_order_by_sec IS NOT NULL THEN
569 l_stmt := l_stmt ||' ORDER BY L.'||p_order_by_sec;
570
571
572 END IF;
573 -- Moac start
574 IF NOT l_single_org then
575 IF p_order_by_first IS NOT NULL OR
576 p_order_by_sec IS NOT NULL THEN
577 l_stmt := l_stmt || ', L.org_id' ;
578 ELSE
579 l_stmt := l_stmt ||' ORDER BY L.org_id';
580 END IF;
581 END IF;
582 -- Moac End.
583 IF NOT l_single_org OR
584 (p_order_by_first IS NOT NULL OR
585 p_order_by_sec IS NOT NULL) then
586 l_stmt := l_stmt || ', L.top_model_line_id,l.line_id' ; --5166476
587 ELSE
588 l_stmt := l_stmt ||' ORDER BY L.top_model_line_id,l.line_id' ;
589 END IF;
590
591 IF l_debug_level > 0 THEN
592 oe_debug_pub.add('Query : ' || l_stmt, 1 ) ;
593 END IF;
594
595 -- Parse statement.
596 DBMS_SQL.Parse(l_cursor_id,l_stmt,DBMS_SQL.NATIVE);
597
598 -- Bind variables
599 -- Moac Start
600 IF p_org_id is NOT NULL THEN
601 DBMS_SQL.Bind_Variable(l_cursor_id, ':org_id', p_org_id);
602 END IF;
603 -- Moac End
604
605 IF p_order_number_low IS NOT NULL THEN
606 DBMS_SQL.Bind_Variable(l_cursor_id, ':order_number_low',
607 p_order_number_low);
608 END IF;
609 IF p_order_number_high IS NOT NULL THEN
610 DBMS_SQL.Bind_Variable(l_cursor_id, ':order_number_high',
611 p_order_number_high);
612 END IF;
613 IF p_request_date_low IS NOT NULL THEN
614 DBMS_SQL.Bind_Variable(l_cursor_id, ':request_date_low',
615 l_request_date_low);
616 END IF;
617 IF p_request_date_high IS NOT NULL THEN
618 DBMS_SQL.Bind_Variable(l_cursor_id, ':request_date_high',
619 l_request_date_high);
620 END IF;
621 IF p_customer_po_number IS NOT NULL THEN
622 DBMS_SQL.Bind_Variable(l_cursor_id, ':customer_po_number',
623 p_customer_po_number);
624 END IF;
625 IF p_ship_to_location IS NOT NULL THEN
626 DBMS_SQL.Bind_Variable(l_cursor_id, ':ship_to_location',
627 p_ship_to_location);
628 END IF;
629 IF p_order_type IS NOT NULL THEN
630 DBMS_SQL.Bind_Variable(l_cursor_id, ':order_type', p_order_type);
631 END IF;
632 IF p_customer IS NOT NULL THEN
633 DBMS_SQL.Bind_Variable(l_cursor_id, ':customer', p_customer);
634 END IF;
635 IF p_item IS NOT NULL THEN
636 DBMS_SQL.Bind_Variable(l_cursor_id, ':item', p_item);
637 END IF;
638 IF p_ordered_date_low IS NOT NULL THEN
639 DBMS_SQL.Bind_Variable(l_cursor_id, ':ordered_date_low',
640 l_ordered_date_low);
641 END IF;
642 IF p_ordered_date_high IS NOT NULL THEN
643 DBMS_SQL.Bind_Variable(l_cursor_id, ':ordered_date_high',
644 l_ordered_date_high);
645 END IF;
646 IF p_warehouse IS NOT NULL THEN
647 DBMS_SQL.Bind_Variable(l_cursor_id, ':warehouse', p_warehouse);
648 END IF;
649 IF p_demand_class IS NOT NULL THEN
650 DBMS_SQL.Bind_Variable(l_cursor_id, ':demand_class', p_demand_class);
651 END IF;
652 IF p_planning_priority IS NOT NULL THEN
653 DBMS_SQL.Bind_Variable(l_cursor_id, ':planning_priority',
654 p_planning_priority);
655 END IF;
656 IF p_shipment_priority IS NOT NULL THEN
657 DBMS_SQL.Bind_Variable(l_cursor_id, ':shipment_priority',
658 p_shipment_priority);
659 END IF;
660 IF p_line_type IS NOT NULL THEN
661 DBMS_SQL.Bind_Variable(l_cursor_id, ':line_type', p_line_type);
662 END IF;
663 IF p_line_request_date_low IS NOT NULL THEN
664 DBMS_SQL.Bind_Variable(l_cursor_id, ':line_request_date_low',
665 l_line_request_date_low);
666 END IF;
667 IF p_line_request_date_high IS NOT NULL THEN
668 DBMS_SQL.Bind_Variable(l_cursor_id, ':line_request_date_high',
669 l_line_request_date_high);
670 END IF;
671 IF p_line_ship_to_location IS NOT NULL THEN
672 DBMS_SQL.Bind_Variable(l_cursor_id, ':line_ship_to_location',
673 p_line_ship_to_location);
674 END IF;
675 IF p_sch_ship_date_low IS NOT NULL THEN
676 DBMS_SQL.Bind_Variable(l_cursor_id, ':sch_ship_date_low',
677 l_sch_ship_date_low);
678 END IF;
679 IF p_sch_ship_date_high IS NOT NULL THEN
680 DBMS_SQL.Bind_Variable(l_cursor_id, ':sch_ship_date_high',
681 l_sch_ship_date_high);
682 END IF;
683 IF p_sch_arrival_date_low IS NOT NULL THEN
684 DBMS_SQL.Bind_Variable(l_cursor_id, ':sch_arrival_date_low',
685 l_sch_arrival_date_low);
686 END IF;
687 IF p_sch_arrival_date_high IS NOT NULL THEN
688 DBMS_SQL.Bind_Variable(l_cursor_id, ':sch_arrival_date_high',
689 l_sch_arrival_date_high);
690 END IF;
691 IF p_booked IS NOT NULL THEN
692 DBMS_SQL.Bind_Variable(l_cursor_id, ':booked', p_booked);
693 END IF;
694
695 -- Map output columns
696 DBMS_SQL.Define_Column(l_cursor_id, 1, l_header_id);
697 DBMS_SQL.Define_Column(l_cursor_id, 2, l_line_id);
698 DBMS_SQL.Define_Column(l_cursor_id, 3, l_org_id); -- Moac
699
700 IF l_debug_level > 0 THEN
701 oe_debug_pub.add('Before executing query.',1);
702 END IF;
703
704 -- Execute query.
705 l_retval := DBMS_SQL.Execute(l_cursor_id);
706
707 IF l_debug_level > 0 THEN
708 oe_debug_pub.add('Execution Result : ' || l_retval, 2) ;
709 END IF;
710
711 -- Process each row retrieved.
712 LOOP
713
714 IF l_debug_level > 0 THEN
715 oe_debug_pub.add('Execution Result : ' || l_retval, 2) ;
716 END IF;
717
718 IF DBMS_SQL.Fetch_Rows(l_cursor_id) = 0 THEN
719 EXIT;
720 END IF;
721
722 DBMS_SQL.Column_Value(l_cursor_id, 1, l_header_id);
723 DBMS_SQL.Column_Value(l_cursor_id, 2, l_line_id);
724 DBMS_SQL.Column_Value(l_cursor_id, 3, l_org_id); -- Moac
725
726
727 IF l_debug_level > 0 THEN
728 oe_debug_pub.add('***** 1. Processing Line Id '||
729 l_line_id ||' *****', 1);
730 END IF;
731 --4777400: Context set is Moved up to set before call to get_date_type
732 -- Moac Start
733 IF NOT l_single_org and l_org_id <> l_old_org_id THEN
734 l_old_org_id := l_org_id;
735 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_org_id);
736 END IF;
737 -- Moac End.
738 l_order_date_type_code := NVL
739 (OE_SCHEDULE_UTIL.Get_Date_Type(l_header_id),'SHIP');
740 l_temp_line_id := 0;
741
742 BEGIN
743
744 SELECT L.line_id,
745 L.booked_flag,
746 L.request_date,
747 L.ship_from_org_id,
748 L.ship_set_id,
749 L.arrival_set_id,
750 L.ato_line_id,
751 L.top_model_line_id,
752 L.link_to_line_id,
753 L.ship_model_complete_flag,
754 L.item_type_code,
755 L.schedule_status_code
756 INTO l_temp_line_id,
757 l_booked_flag,
758 l_request_date,
759 l_ship_from_org_id,
760 l_ship_set_id,
761 l_arrival_set_id,
762 l_ato_line_id,
763 l_top_model_line_id,
764 l_link_to_line_id,
765 l_smc_flag,
766 l_item_type_code,
767 l_schedule_status_code
768 FROM oe_order_lines_all L
769 WHERE L.open_flag = 'Y'
770 AND L.line_id = l_line_id;
771
772
773 EXCEPTION
774 WHEN no_data_found THEN
775 NULL;
776 END;
777
778
779 IF l_temp_line_id <> 0 THEN
780
781 g_conc_program := 'Y';
782 g_recorded := 'N'; -- 5166476
783
784 IF nvl(p_sch_mode, 'LINE_ELIGIBLE') = 'LINE_ELIGIBLE' THEN
785 --5166476
786 IF Line_Eligible(p_line_id => l_line_id) THEN
787
788 IF l_debug_level > 0 THEN
789 oe_debug_pub.add(to_char(l_line_id) || ' - Line Eligible', 1);
790 END IF;
791
792 --l_found := FALSE;
793
794 --IF NOT l_found THEN
795 g_process_records := 0;
796 g_failed_records := 0;
797
798 wf_engine.CompleteActivityInternalName ('OEOL',
799 to_char(l_line_id),
800 'SCHEDULING_ELIGIBLE',
801 'COMPLETE');
802
803 OE_MSG_PUB.Count_And_Get (p_count => l_msg_count,
804 p_data => l_msg_data);
805
806 FOR I in 1..l_msg_count LOOP
807 l_msg_data := OE_MSG_PUB.Get(I,'F');
808
809 -- Write Messages in the log file
810 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
811
812 END LOOP;
813 --5166476
814
815 --IF g_failed_records > 0 THEN
816 IF OE_SCH_CONC_REQUESTS.oe_line_status_tbl.EXISTS(l_line_id) AND
817 OE_SCH_CONC_REQUESTS.oe_line_status_tbl(l_line_id) ='N' THEN
818 l_failure := TRUE;
819 END IF;
820 IF l_debug_level > 0 THEN
821 oe_debug_pub.add( 'R2 PROCESSED: '||l_rec_processed||' FAILED: '||l_rec_failure, 1 ) ;
822 END IF;
823
824 --END IF;
825 END IF;
826 ELSIF p_sch_mode = 'SCHEDULE' AND
827 l_schedule_status_code IS NULL THEN
828
829 IF l_debug_level > 0 THEN
830 oe_debug_pub.add(to_char(l_line_id) || ' - Schedule', 1);
831 END IF;
832 l_found := FALSE;
833
834 IF l_smc_flag = 'Y' AND
835 l_top_model_line_id IS NOT NULL THEN
836 l_found := model_processed(l_top_model_line_id,l_top_model_line_id);
837 --5166476
838 IF l_found AND
839 oe_line_status_tbl.EXISTS(l_top_model_line_id) THEN
840 IF OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_top_model_line_id) = 'N' THEN
841 --5166476
842 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'N';
843 ELSE
844 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'Y';
845 END IF;
846 IF l_debug_level > 0 THEN
847 oe_debug_pub.add( 'R3.1 PROCESSED: '||l_line_id, 1 ) ;
848 END IF;
849 END IF;
850 ELSIF l_ato_line_id IS NOT NULL THEN
851 --l_top_model_line_id = l_ato_line_id THEN --5166476
852 l_found := model_processed(l_ato_line_id,l_ato_line_id);
853 --5166476
854 IF l_found AND
855 oe_line_status_tbl.EXISTS(l_ato_line_id) THEN
856 IF OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_ato_line_id) ='N' THEN
857 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'N';
858 ELSE
859 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_id) := 'Y';
860 END IF;
861 IF l_debug_level > 0 THEN
862 oe_debug_pub.add( 'R3.2 PROCESSED: '||l_line_id, 1 ) ;
863 END IF;
864 END IF;
865 IF NOT l_found AND
866 l_top_model_line_id IS NOT NULL AND
867 l_top_model_line_id <> l_ato_line_id AND
868 (p_apply_warehouse IS NULL AND
869 p_apply_sch_date IS NULL)THEN
870 l_found := model_processed(l_top_model_line_id,l_line_id);
871 END IF;
872
873 ELSIF l_top_model_line_id IS NOT NULL THEN
874 IF (p_apply_warehouse IS NOT NULL OR
875 p_apply_sch_date IS NOT NULL) AND
876 l_item_type_code NOT IN (OE_GLOBALS.G_ITEM_INCLUDED) THEN
877 l_found := model_processed(l_line_id,l_line_id);
878 IF l_debug_level > 0 THEN
879 oe_debug_pub.add( 'R3.4 PROCESSED '||l_line_id,1);
880 END IF;
881 --5166476
882 ELSIF l_top_model_line_id <> l_link_to_line_id AND
883 l_item_type_code = (OE_GLOBALS.G_ITEM_INCLUDED) AND
884 (p_apply_warehouse IS NOT NULL OR
885 p_apply_sch_date IS NOT NULL) THEN
886 l_found := model_processed(l_link_to_line_id,l_line_id);
887 IF l_found AND
888 oe_line_status_tbl.EXISTS(l_link_to_line_id) AND
889 oe_line_status_tbl(l_link_to_line_id) ='N' THEN
890 oe_line_status_tbl(l_line_id) := 'N';
891 END IF;
892 ELSE
893
894 l_found := model_processed(l_top_model_line_id,l_line_id);
895 --5166476
896 IF l_found AND
897 oe_line_status_tbl.EXISTS(l_top_model_line_id) AND
898 oe_line_status_tbl(l_top_model_line_id) ='N' THEN
899 oe_line_status_tbl(l_line_id) := 'N';
900 IF l_debug_level > 0 THEN
901 oe_debug_pub.add( 'R3.5 PROCESSED: '||l_line_id, 1 ) ;
902 END IF;
903 END IF;
904
905 END IF;
906 END IF;
907
908 IF NOT l_found THEN
909 IF p_apply_warehouse IS NOT NULL OR
910 p_apply_sch_date IS NOT NULL
911 THEN
912
913 -- Define a save point
914 SAVEPOINT Schedule_Line;
915
916 IF l_rec_processed > 1 THEN
917 -- Initially this will be set to FND_API.G_TRUE
918 l_init_msg_list := FND_API.G_FALSE;
919 END IF;
920
921 oe_line_util.lock_row
922 (x_return_status => l_return_status
923 ,p_x_line_rec => l_line_rec
924 ,p_line_id => l_line_id);
925
926 --l_line_tbl := OE_ORDER_PUB.G_MISS_LINE_TBL;
927 --l_old_line_tbl := OE_ORDER_PUB.G_MISS_LINE_TBL;
928 --l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
929 --l_line_tbl(1).line_id := l_line_id;
930 --l_line_tbl(1).header_id := l_header_id;
931 l_line_tbl(1) := l_line_rec;
932 l_old_line_tbl(1) := l_line_rec;
933
934 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
935
936 IF p_apply_warehouse IS NOT NULL THEN
937 l_line_tbl(1).ship_from_org_id := p_apply_warehouse;
938 END IF;
939
940 IF p_apply_sch_date IS NOT NULL THEN
941 IF l_order_date_type_code = 'SHIP' THEN
942 l_line_tbl(1).schedule_ship_date := l_apply_sch_date;
943 ELSE
944 l_line_tbl(1).schedule_arrival_date := l_apply_sch_date;
945 END IF;
946 ELSE
947 IF l_order_date_type_code = 'SHIP' THEN
948 l_line_tbl(1).schedule_ship_date := l_request_date;
949 ELSE
950 l_line_tbl(1).schedule_arrival_date := l_request_date;
951 END IF;
952
953 END IF;
954 --4892724
955 l_line_tbl(1).change_reason := 'SYSTEM';
956 l_line_tbl(1).change_comments := 'SCHEDULE ORDERS CONCURRENT PROGRAM';
957
958
959 -- Call to process order
960 l_control_rec.controlled_operation := TRUE;
961 l_control_rec.write_to_db := TRUE;
962 l_control_rec.PROCESS := FALSE;
963 l_control_rec.default_attributes := TRUE;
964 l_control_rec.change_attributes := TRUE;
965 l_process_order := TRUE;
966 l_control_rec.check_security := TRUE;-- 5168540
967
968 g_process_records := 0;
969 g_failed_records := 0;
970
971 Oe_Order_Pvt.Lines
972 (p_init_msg_list => l_init_msg_list,
973 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
974 p_control_rec => l_control_rec,
975 p_x_line_tbl => l_line_tbl,
976 p_x_old_line_tbl => l_old_line_tbl,
977 x_return_status => l_return_status);
978
979 IF l_debug_level > 0 THEN
980 oe_debug_pub.add('Oe_Order_Pvt.Lines returns with - '
981 || l_return_status);
982 END IF;
983
984 IF l_return_status IN (FND_API.G_RET_STS_ERROR,
985 FND_API.G_RET_STS_UNEXP_ERROR) THEN
986 IF l_debug_level > 0 THEN
987 oe_debug_pub.add('#### FAILURE #### LINE_ID - '
988 || to_char(l_line_id) || ' ####');
989 END IF;
990 --5166476
991 IF g_recorded = 'N' THEN
992 --5166476
993 OE_SCH_CONC_REQUESTS.oe_line_status_tbl(l_line_id) :='N';
994 g_recorded := 'Y';
995 END IF;
996 --5166476
997 --IF l_smc_flag = 'Y' AND
998 IF l_top_model_line_id IS NOT NULL AND
999 l_smc_flag = 'Y' AND
1000 l_ato_line_id IS NULL THEN
1001 OE_line_status_Tbl(l_top_model_line_id) := 'N';
1002 ELSIF l_ato_line_id IS NOT NULL THEN
1003 OE_line_status_Tbl(l_ato_line_id) := 'N';
1004 END IF;
1005
1006
1007 l_failure := TRUE;
1008
1009 ROLLBACK TO SAVEPOINT Schedule_Line;
1010
1011 END IF;
1012
1013 IF l_debug_level > 0 THEN
1014 oe_debug_pub.add( 'R3 PROCESSED: '||l_line_id,1);
1015 END IF;
1016
1017 ELSE -- No scheduling attributes are provided
1018
1019 IF l_debug_level > 0 THEN
1020 oe_debug_pub.add('No scheduling attributes. Booked flag - '
1021 || l_booked_flag);
1022 END IF;
1023
1024 g_process_records := 0;
1025 g_failed_records := 0;
1026
1027 --R12.MOAC
1028 l_selected_line_tbl(1).id1 := l_line_id;
1029
1030 OE_GROUP_SCH_UTIL.Schedule_Multi_lines
1031 (p_selected_line_tbl => l_selected_line_tbl, --R12.MOAC
1032 p_line_count => 1,
1033 p_sch_action => 'SCHEDULE',
1034 x_atp_tbl => l_atp_tbl,
1035 x_return_status => l_return_status,
1036 x_msg_count => l_msg_count,
1037 x_msg_data => l_msg_data);
1038
1039 --ELSE
1040 IF l_debug_level > 0 THEN
1041 oe_debug_pub.add('Return Status After Schedule_Multi_lines '||l_return_status,1);
1042 END IF;
1043
1044 IF NVL(l_booked_flag,'N') ='Y'
1045 AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1046
1047 IF l_debug_level > 0 THEN
1048 oe_debug_pub.add('It is a Booked Order' );
1049 END IF;
1050 -- Added PTO Logic as part of bug 5186581
1051 IF l_top_model_line_id is not null
1052 AND l_top_model_line_id = l_line_id
1053 AND l_ato_line_id is null
1054 AND l_smc_flag = 'N' THEN
1055
1056 IF l_debug_level > 0 THEN
1057
1058 oe_debug_pub.add('It is a PTO Model' );
1059 END IF;
1060
1061 -- Workflow wont progress all child lines for the Non SMC PTO model scenario. We have to progress all the
1062 -- child lines if the to Model is NON SMC
1063
1064 FOR M IN progress_pto LOOP
1065
1066
1067 IF l_debug_level > 0 THEN
1068 oe_debug_pub.add('Progressing Line ' || M.line_id, 1);
1069 END IF;
1070
1071 BEGIN
1072 -- COMPLETING ACTIVITY
1073 wf_engine.CompleteActivityInternalName
1074 ('OEOL',
1075 to_char(M.line_id),
1076 'SCHEDULING_ELIGIBLE',
1077 'COMPLETE');
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 NULL;
1081 END;
1082
1083
1084 END LOOP;
1085
1086 ELSE -- Call for each line or ATO/SMC...
1087
1088
1089 BEGIN
1090 -- COMPLETING ACTIVITY
1091 wf_engine.CompleteActivityInternalName
1092 ('OEOL',
1093 to_char(l_line_id),
1094 'SCHEDULING_ELIGIBLE',
1095 'COMPLETE');
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 NULL;
1099 END;
1100 END IF;
1101 END IF;
1102 --5166476
1103
1104 --IF g_failed_records > 0 THEN
1105 IF OE_SCH_CONC_REQUESTS.oe_line_status_tbl.EXISTS(l_line_id) AND
1106 OE_SCH_CONC_REQUESTS.oe_line_status_tbl(l_line_id) = 'N' THEN
1107 l_failure := TRUE;
1108 END IF;
1109 IF l_debug_level > 0 THEN
1110 oe_debug_pub.add( 'R4 PROCESSED: '||l_rec_processed||' FAILED: '||l_rec_failure, 1 ) ;
1111 END IF;
1112 END IF;
1113 END IF;
1114
1115
1116 ELSIF p_sch_mode = 'UNSCHEDULE' AND
1117 l_schedule_status_code IS NOT NULL THEN
1118
1119 IF l_debug_level > 0 THEN
1120 oe_debug_pub.add(to_char(l_line_id) || ' - Unschedule', 1);
1121 END IF;
1122
1123 l_found := FALSE;
1124
1125 IF l_smc_flag = 'Y' AND
1126 l_top_model_line_id IS NOT NULL THEN
1127 l_found := model_processed(l_top_model_line_id,l_top_model_line_id);
1128 ELSIF l_ato_line_id IS NOT NULL THEN
1129 --l_top_model_line_id = l_ato_line_id THEN
1130 l_found := model_processed(l_ato_line_id,l_ato_line_id);
1131 ELSIF l_smc_flag = 'N' AND
1132 l_top_model_line_id IS NOT NULL AND
1133 (l_ato_line_id IS NULL OR
1134 l_ato_line_id <> l_top_model_line_id) AND
1135 l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
1136
1137 l_found := included_processed(l_line_id);
1138 END IF;
1139
1140 IF NOT l_found THEN
1141 g_process_records := 0;
1142 g_failed_records := 0;
1143
1144 IF l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
1145 --5166476
1146 --g_process_records := g_process_records + 1;
1147 OE_SCH_CONC_REQUESTS.oe_line_status_tbl(l_line_id) := 'Y';
1148 END IF;
1149 --R12.MOAC
1150 l_selected_line_tbl(1).id1 := l_line_id;
1151 OE_GROUP_SCH_UTIL.Schedule_Multi_lines
1152 (p_selected_line_tbl => l_selected_line_tbl,
1153 p_line_count => 1,
1154 p_sch_action => 'UNSCHEDULE',
1155 x_atp_tbl => l_atp_tbl,
1156 x_return_status => l_return_status,
1157 x_msg_count => l_msg_count,
1158 x_msg_data => l_msg_data);
1159 --5166476
1160
1161 --IF g_failed_records > 0 THEN
1162 IF OE_SCH_CONC_REQUESTS.oe_line_status_tbl.EXISTS(l_line_id) AND
1163 OE_SCH_CONC_REQUESTS.oe_line_status_tbl(l_line_id) = 'N' THEN
1164 l_failure := TRUE;
1165 END IF;
1166 IF l_debug_level > 0 THEN
1167 oe_debug_pub.add( 'R5 PROCESSED: '||l_rec_processed||' FAILED: '||l_rec_failure, 1 ) ;
1168 END IF;
1169
1170 END IF;
1171 ELSIF p_sch_mode IN ('RESCHEDULE','RESCHEDULE_RD') THEN
1172
1173 l_temp_flag := FALSE;
1174
1175 IF l_debug_level > 0 THEN
1176 oe_debug_pub.add(to_char(l_line_id) || ' - Reschedule', 1);
1177 END IF;
1178
1179 IF l_smc_flag = 'Y' AND
1180 l_top_model_line_id IS NOT NULL THEN
1181 l_temp_flag := model_processed(l_top_model_line_id,l_top_model_line_id);
1182 --5166476
1183 IF l_temp_flag AND
1184 oe_line_status_tbl.EXISTS(l_top_model_line_id) AND
1185 oe_line_status_tbl(l_top_model_line_id) = 'N' THEN
1186 oe_line_status_tbl(l_line_id) := 'N';
1187 /*
1188 l_rec_processed := l_rec_processed + 1;
1189 l_rec_failure := l_rec_failure + 1;
1190 */
1191 IF l_debug_level > 0 THEN
1192 oe_debug_pub.add( 'R6.1 PROCESSED: '||l_rec_processed||' FAILED: '||l_rec_failure, 1 ) ;
1193 END IF;
1194 END IF;
1195 ELSIF l_ato_line_id IS NOT NULL THEN
1196 --l_ato_line_id = l_top_model_line_id THEN
1197 l_temp_flag := model_processed(l_ato_line_id,l_ato_line_id);
1198 --5166476
1199 IF l_temp_flag AND
1200 oe_line_status_tbl.EXISTS(l_ato_line_id) AND
1201 oe_line_status_tbl(l_ato_line_id) = 'N' THEN
1202 oe_line_status_tbl(l_line_id) := 'N';
1203 /*
1204 l_rec_processed := l_rec_processed + 1;
1205 l_rec_failure := l_rec_failure + 1;
1206 */
1207 IF l_debug_level > 0 THEN
1208 oe_debug_pub.add( 'R6.2 PROCESSED: '||l_rec_processed||' FAILED: '||l_rec_failure, 1 ) ;
1209 END IF;
1210 END IF;
1211
1212 END IF;
1213
1214 /* If many of the lines selected are part of a set, then delayed
1215 * request must get logged only for one of the lines.
1216 */
1217 IF l_ship_set_id IS NOT NULL OR
1218 l_arrival_set_id IS NOT NULL THEN
1219
1220 l_temp_flag := set_processed( NVL(l_ship_set_id,l_arrival_set_id));
1221 END IF;
1222
1223 IF NOT l_temp_flag THEN
1224
1225 -- Define a save point
1226 SAVEPOINT Schedule_Line;
1227
1228 IF l_rec_processed > 1 THEN
1229 l_init_msg_list := FND_API.G_FALSE;
1230 END IF;
1231
1232 oe_line_util.lock_row
1233 (x_return_status => l_return_status
1234 ,p_x_line_rec => l_line_rec
1235 ,p_line_id => l_line_id);
1236
1237 l_line_tbl(1) := l_line_rec;
1238 l_old_line_tbl(1) := l_line_rec;
1239
1240 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
1241
1242 IF p_sch_mode = 'RESCHEDULE_RD' THEN
1243 l_apply_sch_date := l_line_tbl(1).request_date;
1244 END IF;
1245
1246 l_line_tbl(1).ship_from_org_id
1247 := NVL(p_apply_warehouse, l_ship_from_org_id);
1248
1249
1250 IF l_apply_sch_date IS NOT NULL THEN
1251 IF l_order_date_type_code = 'SHIP' THEN
1252 l_line_tbl(1).schedule_ship_date := l_apply_sch_date;
1253 ELSE
1254 l_line_tbl(1).schedule_arrival_date := l_apply_sch_date;
1255 END IF;
1256 END IF;
1257
1258 --l_line_tbl(1).schedule_action_code := OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE;
1259
1260 --4892724
1261 l_line_tbl(1).change_reason := 'SYSTEM';
1262 l_line_tbl(1).change_comments := 'SCHEDULE ORDERS CONCURRENT PROGRAM';
1263
1264 -- Call to process order
1265 l_control_rec.controlled_operation := TRUE;
1266 l_control_rec.write_to_db := TRUE;
1267 --l_control_rec.PROCESS := FALSE;
1268 l_control_rec.default_attributes := TRUE;
1269 l_control_rec.change_attributes := TRUE;
1270 l_process_order := TRUE;
1271 l_control_rec.check_security := TRUE;-- 5168540
1272
1273 g_process_records := 0;
1274 g_failed_records := 0;
1275
1276 Oe_Order_Pvt.Lines
1277 (p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1278 p_init_msg_list => l_init_msg_list,
1279 p_control_rec => l_control_rec,
1280 p_x_line_tbl => l_line_tbl,
1281 p_x_old_line_tbl => l_old_line_tbl,
1282 x_return_status => l_return_status);
1283
1284 IF l_debug_level > 0 THEN
1285 oe_debug_pub.add('Oe_Order_Pvt.Lines returns with - '
1286 || l_return_status);
1287 END IF;
1288
1289 IF l_return_status IN
1290 (FND_API.G_RET_STS_ERROR,FND_API.G_RET_STS_UNEXP_ERROR)
1291 THEN
1292 ROLLBACK TO SAVEPOINT Schedule_Line;
1293 IF l_debug_level > 0 THEN
1294 oe_debug_pub.add('#### FAILURE #### LINE_ID - '
1295 || to_char(l_line_id) || ' ####');
1296 END IF;
1297 --5166476
1298 OE_SCH_CONC_REQUESTS.oe_line_status_tbl(l_line_id) := 'N';
1299 --516476
1300 IF l_smc_flag = 'Y' AND
1301 l_top_model_line_id IS NOT NULL THEN
1302 OE_line_status_Tbl(l_top_model_line_id) := 'N';
1303 ELSIF l_ato_line_id IS NOT NULL THEN
1304 OE_line_status_Tbl(l_ato_line_id) := 'N';
1305 END IF;
1306 l_failure := TRUE;
1307 END IF;
1308 IF l_debug_level > 0 THEN
1309 oe_debug_pub.add( 'R6 PROCESSED: '||l_rec_processed||' FAILED: '||l_rec_failure, 1 ) ;
1310 END IF;
1311
1312 END IF;
1313 END IF; -- line eligible
1314
1315 IF l_process_order = TRUE
1316 AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1317 IF l_debug_level > 0 THEN
1318 oe_debug_pub.add('After Call to Process Order ',1);
1319 END IF;
1320 BEGIN
1321
1322 l_control_rec.controlled_operation := TRUE;
1323 l_control_rec.process := TRUE;
1324 l_control_rec.process_entity := OE_GLOBALS.G_ENTITY_ALL;
1325 l_control_rec.check_security := FALSE;
1326 l_control_rec.clear_dependents := FALSE;
1327 l_control_rec.default_attributes := FALSE;
1328 l_control_rec.change_attributes := FALSE;
1329 l_control_rec.validate_entity := FALSE;
1330 l_control_rec.write_to_DB := FALSE;
1331
1332 -- Instruct API to clear its request table
1333
1334 l_control_rec.clear_api_cache := FALSE;
1335 l_control_rec.clear_api_requests := TRUE;
1336
1337 oe_line_util.Post_Line_Process (p_control_rec => l_control_rec,
1338 p_x_line_tbl => l_line_tbl );
1339 g_process_records := 0;
1340 g_failed_records := 0;
1341
1342 IF l_debug_level > 0 THEN
1343 oe_debug_pub.add('GOING TO EXECUTE DELAYED REQUESTS ', 2);
1344 END IF;
1345
1346 OE_DELAYED_REQUESTS_PVT.Process_Delayed_Requests
1347 (x_return_status => l_return_status);
1348
1349 IF l_return_status IN (FND_API.G_RET_STS_ERROR,
1350 FND_API.G_RET_STS_UNEXP_ERROR) THEN
1351 IF l_debug_level > 0 THEN
1352 oe_debug_pub.add('#### FAILURE #### LINE_ID - '
1353 || to_char(l_line_id) || ' ####');
1354 END IF;
1355
1356 l_failure := TRUE;
1357
1358 OE_Delayed_Requests_PVT.Clear_Request(l_return_status);
1359
1360 IF l_debug_level > 0 THEN
1361 oe_debug_pub.add('AFTER CLEARING DELAYED REQUESTS: '|| l_return_status, 2);
1362 END IF;
1363
1364 ROLLBACK TO SAVEPOINT Schedule_Line;
1365 END IF;
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 OE_Delayed_Requests_PVT.Clear_Request(l_return_status);
1369 IF l_debug_level > 0 THEN
1370 oe_debug_pub.add('IN WHEN OTHERS '|| l_return_status, 2);
1371 END IF;
1372 END;
1373
1374 l_process_order := FALSE;
1375 ELSE -- (5174789)Return status is not success
1376 OE_DELAYED_REQUESTS_PVT.Clear_Request(l_return_status);
1377 l_process_order := FALSE;
1378 END IF;
1379
1380 END IF;
1381 END LOOP; -- loop for each row of dynamic query.
1382
1383 -- close the cursor
1384 DBMS_SQL.Close_Cursor(l_cursor_id);
1385
1386 END IF; -- if parameters passed are null.
1387
1388 OE_MSG_PUB.Save_Messages(p_request_id => to_number(l_request_id));
1389 --5166476
1390 --l_rec_success := l_rec_processed - l_rec_failure;
1391 l_rec_success :=0;
1392 l_rec_processed := 0;
1393 l_rec_failure := 0;
1394 l_index := OE_SCH_CONC_REQUESTS.oe_line_status_tbl.FIRST;
1395 WHILE l_index is not null
1396 LOOP
1397 --oe_debug_pub.add( 'R7 : '||l_index||' Status: '||oe_line_status_tbl(l_index), 1 ) ;
1398 IF OE_SCH_CONC_REQUESTS.oe_line_status_tbl(l_index) = 'Y' THEN
1399 l_rec_success := l_rec_success + 1;
1400 ELSE
1401 l_rec_failure := l_rec_failure + 1;
1402 END IF;
1403 l_rec_processed := l_rec_processed +1;
1404 l_index := OE_SCH_CONC_REQUESTS.oe_line_status_tbl.NEXT(l_index);
1405 END LOOP;
1406
1407 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Lines Selected : ' || l_rec_processed);
1408 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Lines Failed : ' || l_rec_failure);
1409 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Lines Successfully Processed : ' || l_rec_success);
1410
1411 IF l_failure THEN
1412 RETCODE := 1;
1413 END IF;
1414
1415 EXCEPTION
1416 WHEN FND_API.G_EXC_ERROR THEN
1417 fnd_file.put_line(FND_FILE.LOG,
1418 'Error executing Scheduling, Exception:G_EXC_ERROR');
1419
1420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1421 fnd_file.put_line(FND_FILE.LOG,
1422 'Error executing Scheduling, Exception:G_EXC_UNEXPECTED_ERROR');
1423
1424 WHEN OTHERS THEN
1425 fnd_file.put_line(FND_FILE.LOG, 'Unexpected error in OE_SCH_CONC_REQUESTS.Request');
1426 fnd_file.put_line(FND_FILE.LOG, substr(sqlerrm, 1, 2000));
1427 DBMS_SQL.Close_Cursor(l_cursor_id);
1428
1429 END Request;
1430
1431 END OE_SCH_CONC_REQUESTS;
[Home] [Help]