[Home] [Help]
PACKAGE BODY: APPS.OE_SCH_ORGANIZER_UTIL
Source
1 PACKAGE BODY OE_SCH_ORGANIZER_UTIL AS
2 /* $Header: OEXUSCOB.pls 120.11.12010000.2 2008/11/11 08:23:30 rmoharan ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_SCH_ORGANIZER_UTIL';
5
6 ------------------------------------------------------------------------
7 PROCEDURE Sch_Window_Key_Commit
8 ( p_x_sch_line_tbl IN OUT NOCOPY sch_line_tbl_type,
9 x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2,
10 x_failed_count OUT NOCOPY NUMBER)
11 ------------------------------------------------------------------------
12 IS
13 l_x_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
14 l_x_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
15 l_x_line_rec OE_ORDER_PUB.Line_Rec_Type;
16 l_control_rec OE_GLOBALS.Control_Rec_Type;
17 l_count NUMBER;
18 l_init_msg VARCHAR2(30) := FND_API.G_TRUE;
19 l_failed_count NUMBER := 0;
20 K NUMBER := 2;
21 L NUMBER;
22 l_dummy NUMBER;
23 --R12.MOAC--
24 l_org_id NUMBER :=-99;
25 l_access_mode VARCHAR2(1);
26 --
27 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
28 --
29 BEGIN
30 -- for every record in g_line_tbl, parse the G_line_tbl for records to be processed together
31 -- e.g. sets, SMCs etc. those line_id will be inserted into the exclude list, only if
32 -- a given line_id which is not in the list will be processed by the loop
33
34 l_count := p_x_sch_line_tbl.count;
35 IF l_debug_level > 0 THEN
36 oe_debug_pub.add( 'SCH: IN SCH_WINDOW_KEY_COMMIT: COUNT:' || L_COUNT ) ;
37 END IF;
38 --R12.MOAC--
39 l_access_mode := mo_global.get_access_mode;
40
41 l_control_rec.controlled_operation := TRUE; --since we do process partial
42 l_control_rec.process_partial := TRUE;
43
44 For I in 1..l_count Loop --loop for all modified lines
45 BEGIN
46
47 IF l_debug_level > 0 THEN
48 oe_debug_pub.add( 'SCH: MAIN LOOP LINE_ID: ' || P_X_SCH_LINE_TBL ( I ) .LINE_ID ) ;
49 END IF;
50 IF nvl(p_x_sch_line_tbl(I).exclude, 'N') <> 'Y' THEN
51 --R12.MOAC--
52 IF l_access_mode <> 'S' and l_org_id <> p_x_sch_line_tbl(I).org_id THEN
53 l_org_id := p_x_sch_line_tbl(I).org_id;
54 mo_global.set_policy_context(p_access_mode => 'S',
55 p_org_id => l_org_id);
56 END IF;
57
58 OE_Line_Util.Query_Row
59 ( p_line_id => p_x_sch_line_tbl(I).line_id
60 , x_line_rec => l_x_line_rec
61 );
62
63 l_x_old_line_tbl(1) := l_x_line_rec;
64 l_x_line_tbl(1) := l_x_old_line_tbl(1);
65
66 l_x_line_tbl(1).source_type_code := p_x_sch_line_tbl(I).source_type_code;
67
68 l_x_line_tbl(1).schedule_ship_date := p_x_sch_line_tbl(I).schedule_ship_date;
69 IF l_x_line_tbl(1).source_type_code <> 'EXTERNAL' THEN
70 l_x_line_tbl(1).schedule_arrival_date := p_x_sch_line_tbl(I).schedule_arrival_date;
71 l_x_line_tbl(1).reserved_quantity := p_x_sch_line_tbl(I).reserved_quantity;
72 l_x_line_tbl(1).reserved_quantity2 := p_x_sch_line_tbl(I).reserved_quantity2; -- INVCONV 4668439
73 l_x_line_tbl(1).subinventory := p_x_sch_line_tbl(I).subinventory;
74 IF p_x_sch_line_tbl(I).ship_set_changed = 'Y' THEN
75 l_x_line_tbl(1).ship_set := p_x_sch_line_tbl(I).ship_set;
76 l_x_line_tbl(1).ship_set_id := null;
77 END IF;
78 IF p_x_sch_line_tbl(I).arrival_set_changed = 'Y' THEN
79 l_x_line_tbl(1).arrival_set := p_x_sch_line_tbl(I).arrival_set;
80 l_x_line_tbl(1).arrival_set_id := null;
81 END IF;
82 l_x_line_tbl(1).planning_priority := p_x_sch_line_tbl(I).planning_priority;
83 END IF;
84
85 l_x_line_tbl(1).ship_from_org_id := p_x_sch_line_tbl(I).ship_from_org_id;
86 l_x_line_tbl(1).demand_class_code := p_x_sch_line_tbl(I).demand_class_code;
87 l_x_line_tbl(1).shipment_priority_code := p_x_sch_line_tbl(I).shipment_priority_code;
88
89
90 l_x_line_tbl(1).shipping_method_code := p_x_sch_line_tbl(I).shipping_method_code;
91 /* l_x_line_tbl(1).project_id:= p_x_sch_line_tbl(I).project_id;
92 l_x_line_tbl(1).task_id := p_x_sch_line_tbl(I).task_id;
93 l_x_line_tbl(1).end_item_unit_number := p_x_sch_line_tbl(I).end_item_unit_number;
94 */
95 l_x_line_tbl(1).override_atp_date_code := p_x_sch_line_tbl(I).override_atp_date_code;
96 l_x_line_tbl(1).late_demand_penalty_factor := p_x_sch_line_tbl(I).late_demand_penalty_factor;
97 l_x_line_tbl(1).latest_acceptable_date := p_x_sch_line_tbl(I).latest_acceptable_date;
98
99
100 l_x_line_tbl(1).operation := 'UPDATE';
101 -- 4195146
102 /* Start Audit Trail */
103 l_x_line_tbl(1).change_reason := 'SYSTEM';
104 l_x_line_tbl(1).change_comments := 'SCHEDULING ORGANIZER';
105 /* End Audit Trail */
106
107 L := I + 1;
108 IF L <= l_count THEN
109 For J in L..l_count Loop --loop for remained modified lines
110 IF p_x_sch_line_tbl(J).header_id = p_x_sch_line_tbl(I).header_id THEN --if any remained lines has same header
111 IF l_debug_level > 0 THEN
112 oe_debug_pub.add( 'SCH: BINGO! LINE: ' || P_X_SCH_LINE_TBL ( J ) .LINE_ID || ' HAS SAME HEADER_ID', 5 ) ;
113 END IF;
114 p_x_sch_line_tbl(J).exclude := 'Y'; --so that we will not process them when we come to it
115
116 OE_Line_Util.Query_Row
117 ( p_line_id => p_x_sch_line_tbl(J).line_id
118 , x_line_rec => l_x_line_rec
119 );
120
121 l_x_old_line_tbl(K) := l_x_line_rec;
122
123 l_x_line_tbl(K) := l_x_old_line_tbl(K);
124
125 l_x_line_tbl(K).source_type_code := p_x_sch_line_tbl(J).source_type_code;
126
127 l_x_line_tbl(K).schedule_ship_date := p_x_sch_line_tbl(J).schedule_ship_date;
128 IF l_x_line_tbl(K).source_type_code <> 'EXTERNAL' THEN
129 l_x_line_tbl(K).schedule_arrival_date := p_x_sch_line_tbl(J).schedule_arrival_date;
130 l_x_line_tbl(K).reserved_quantity := p_x_sch_line_tbl(J).reserved_quantity;
131 l_x_line_tbl(K).reserved_quantity2 := p_x_sch_line_tbl(J).reserved_quantity2; -- INVCONV 4668439
132 l_x_line_tbl(K).subinventory := p_x_sch_line_tbl(J).subinventory;
133 IF p_x_sch_line_tbl(J).ship_set_changed = 'Y' THEN
134 l_x_line_tbl(K).ship_set := p_x_sch_line_tbl(J).ship_set;
135 l_x_line_tbl(K).ship_set_id := null;
136 END IF;
137 IF p_x_sch_line_tbl(J).arrival_set_changed = 'Y' THEN
138 l_x_line_tbl(K).arrival_set := p_x_sch_line_tbl(J).arrival_set;
139 l_x_line_tbl(K).arrival_set_id := null;
140 END IF;
141 l_x_line_tbl(K).planning_priority := p_x_sch_line_tbl(J).planning_priority;
142 END IF;
143
144 l_x_line_tbl(K).ship_from_org_id := p_x_sch_line_tbl(J).ship_from_org_id;
145 l_x_line_tbl(K).demand_class_code := p_x_sch_line_tbl(J).demand_class_code;
146 l_x_line_tbl(K).shipment_priority_code := p_x_sch_line_tbl(J).shipment_priority_code;
147
148
149
150 l_x_line_tbl(K).shipping_method_code := p_x_sch_line_tbl(J).shipping_method_code;
151 /* l_x_line_tbl(K).project_id:= p_x_sch_line_tbl(J).project_id;
152 l_x_line_tbl(K).task_id := p_x_sch_line_tbl(J).task_id;
153 l_x_line_tbl(K).end_item_unit_number := p_x_sch_line_tbl(J).end_item_unit_number;
154 */
155
156 l_x_line_tbl(K).override_atp_date_code := p_x_sch_line_tbl(J).override_atp_date_code;
157 l_x_line_tbl(K).late_demand_penalty_factor := p_x_sch_line_tbl(J).late_demand_penalty_factor;
158 l_x_line_tbl(K).latest_acceptable_date := p_x_sch_line_tbl(J).latest_acceptable_date;
159
160
161 l_x_line_tbl(K).operation := 'UPDATE';
162
163 K := K+1;
164 END IF; --same header
165 End Loop; --for remained modified lines
166 END IF; --L <= l_count
167
168 IF l_debug_level > 0 THEN
169 oe_debug_pub.add( 'SCH: GOING TO CALL LINES NOW' ) ;
170 END IF;
171
172 K := 2; --reset K
173
174 SAVEPOINT PRE_LINES;
175
176 OE_ORDER_PVT.Lines(p_validation_level => FND_API.G_VALID_LEVEL_FULL,
177 p_init_msg_list => l_init_msg,
178 p_control_rec => l_control_rec,
179 p_x_line_tbl => l_x_line_tbl,
180 p_x_old_line_tbl => l_x_old_line_tbl,
181 x_return_status => x_return_status);
182
183 l_init_msg := FND_API.G_FALSE;
184 IF l_debug_level > 0 THEN
185 oe_debug_pub.add( 'SCH: DONE CALLING LINES: ' || X_RETURN_STATUS ) ;
186 END IF;
187
188 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
191 RAISE FND_API.G_EXC_ERROR;
192 END IF;
193
194 Oe_Order_Pvt.Process_Requests_And_Notify
195 ( p_process_requests => TRUE
196 , p_init_msg_list => FND_API.G_FALSE
197 , p_notify => TRUE
198 , x_return_status => x_return_status
199 , p_line_tbl => l_x_line_tbl
200 , p_old_line_tbl => l_x_old_line_tbl
201 );
202
203 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
204 ROLLBACK TO SAVEPOINT PRE_LINES;
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
207 ROLLBACK TO SAVEPOINT PRE_LINES;
208 RAISE FND_API.G_EXC_ERROR;
209 END IF;
210
211 l_x_line_tbl.delete; -- clear out the table for every order
212 l_x_old_line_tbl.delete;
213
214 END IF; -- not marked as exclude
215
216 EXCEPTION
217 WHEN FND_API.G_EXC_ERROR THEN
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 l_failed_count := l_failed_count + l_x_line_tbl.count;
220 x_failed_count := l_failed_count;
221 l_x_line_tbl.delete;
222 l_x_old_line_tbl.delete;
223 IF l_debug_level > 0 THEN
224 oe_debug_pub.add( 'SCH: IN EXCEPTION HANDLER! FAILED COUNT: ' || L_FAILED_COUNT ) ;
225 END IF;
226 x_msg_count := oe_msg_pub.count_msg;
227 IF l_debug_level > 0 THEN
228 oe_debug_pub.add( 'SCH: IN EXCEPTION HANDLER! MSG COUNT: ' || OE_MSG_PUB.COUNT_MSG ) ;
229 END IF;
230
231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 l_failed_count := l_failed_count + l_x_line_tbl.count;
234 x_failed_count := l_failed_count;
235 IF l_debug_level > 0 THEN
236 oe_debug_pub.add( 'SCH: IN UNEXP EXCEPTION HANDLER! FAILED COUNT: ' || L_FAILED_COUNT ) ;
237 END IF;
238 l_x_line_tbl.delete;
239 l_x_old_line_tbl.delete;
240
241 x_msg_count := oe_msg_pub.count_msg;
242 IF l_debug_level > 0 THEN
243 oe_debug_pub.add( 'SCH: IN UNEXP EXCEPTION HANDLER! MSG COUNT: ' || OE_MSG_PUB.COUNT_MSG ) ;
244 END IF;
245
246 WHEN OTHERS THEN
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 l_failed_count := l_failed_count + l_x_line_tbl.count;
249 x_failed_count := l_failed_count;
250 IF l_debug_level > 0 THEN
251 oe_debug_pub.add( 'SCH: IN OTHER EXCEPTION HANDLER! FAILED COUNT: ' || L_FAILED_COUNT ) ;
252 END IF;
253 l_x_line_tbl.delete;
254 l_x_old_line_tbl.delete;
255
256 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
257 THEN
258 oe_msg_pub.Add_Exc_Msg
259 ( G_PKG_NAME
260 , 'Sch_Window_Key_Commit'
261 );
262 END IF;
263
264 x_msg_count := oe_msg_pub.count_msg;
265
266
267 END; -- block
268 End Loop; -- Main loop
269 --R12.MOAC--
270 IF l_access_mode <> 'S' THEN
271 Mo_global.set_policy_context(p_access_mode => l_access_mode , p_org_id =>'');
272 END IF;
273 x_msg_count := oe_msg_pub.count_msg;
274
275 END Sch_Window_Key_Commit;
276
277
278 ----------------------------------------------------------------------------
279 PROCEDURE Order_Boundary_Sorting(p_line_list IN VARCHAR2, p_count IN NUMBER,
280 x_line_list_tbl OUT NOCOPY line_list_tab_typ)
281 ----------------------------------------------------------------------------
282 IS
283 I NUMBER;
284 K NUMBER := 0;
285 L NUMBER;
286 M NUMBER;
287 match_count NUMBER := 1; --at least one id already there
288 j Integer;
289 init Integer;
290 nextpos Integer;
291 l_record_ids VARCHAR2(32000) := p_line_list;
292 l_line_id NUMBER;
293 l_header_id NUMBER;
294 l_header_line_tbl header_line_tab;
295 --
296 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
297 --
298 BEGIN
299 IF l_debug_level > 0 THEN
300 oe_debug_pub.add( 'SCH: IN ORDER_BOUNDARY_SORTING' ) ;
301 END IF;
302 j := 1;
303 init := 1;
304 nextpos := INSTR(l_record_ids,',',1,j) ;
305
306 FOR I IN 1..p_count LOOP
307
308 l_line_id := to_number(substr(l_record_ids,init, nextpos-init));
309 l_header_line_tbl(j).line_id := l_line_id;
310
311 SELECT header_id
312 INTO l_header_id
313 FROM oe_order_lines_all
314 WHERE line_id = l_line_id;
315
316 l_header_line_tbl(j).header_id := l_header_id;
317 l_header_line_tbl(j).exclude := 'N';
318
319 IF l_debug_level > 0 THEN
320 oe_debug_pub.add( 'SCH: LINE_ID:' || L_LINE_ID || ' HEADER_ID:' || L_HEADER_ID , 5 ) ;
321 END IF;
322
323 init := nextpos + 1.0;
324 j := j + 1.0;
325 nextpos := INSTR(l_record_ids,',',1,j) ;
326
327 END LOOP;
328
329 For I in 1..p_count Loop
330 IF l_header_line_tbl(I).exclude <> 'Y' THEN
331 K := K+1;
332 x_line_list_tbl(K).line_list := l_header_line_tbl(I).line_id || ',';
333 x_line_list_tbl(K).count := 1;
334 l_header_id := l_header_line_tbl(I).header_id;
335 L := I + 1;
336 IF L <= p_count THEN
337 For M in L..p_count Loop -- here I am not checking if M is excluded or not, as no cost difference
338 IF l_header_line_tbl(M).header_id = l_header_id THEN --same order
339 match_count := match_count + 1;
340 x_line_list_tbl(K).line_list := x_line_list_tbl(K).line_list || l_header_line_tbl(M).line_id || ',';
341 x_line_list_tbl(K).count := match_count;
342 l_header_line_tbl(M).exclude := 'Y'; --so we wont process it when the main loop comes to it
343 END IF;
344 End Loop;
345 match_count := 1; --reset for next order
346 END IF;
347 END IF;
348 End Loop;
349
350 /* for testing purpose only, to verify the list */
351 /*
352 For I in 1..K Loop
353 IF l_debug_level > 0 THEN
354 oe_debug_pub.add( 'K: ' || K || ' - ' || X_LINE_LIST_TBL ( I ) .LINE_LIST || ' COUNT: ' || X_LINE_LIST_TBL ( I ) .COUNT ) ;
355 END IF;
356 End Loop;
357 */
358
359 END Order_Boundary_Sorting;
360
361 -- Pack J
362 /*-------------------------------------------------------------------------------------
363 FUNCTION : Submit_Reservation_Request
364 DESCRIPTION: This api will call reserve order concurrent program along with the
365 parameters passed and will return the request id.
366 --------------------------------------------------------------------------------------*/
367
368 FUNCTION Submit_Reservation_Request
369 (p_selected_line_tbl IN OE_GLOBALS.selected_record_tbl, -- R12.MOAC
370 p_reservation_mode IN VARCHAR2 DEFAULT NULL,
371 p_percent IN NUMBER DEFAULT NULL,
372 p_reserve_run_type IN VARCHAR2,
373 p_reservation_set_Name IN VARCHAR2 DEFAULT NULL,
374 p_override_set IN VARCHAR2 DEFAULT 'N',
375 p_order_by IN VARCHAR2 DEFAULT NULL,
376 p_partial_preference IN VARCHAR2 DEFAULT 'N')
377 RETURN NUMBER
378 IS
379 l_request_id NUMBER;
380 l_set_id NUMBER;
381 --R12.MOAC
382 l_access_mode VARCHAR2(1);
383 l_org_id NUMBER;
384 --
385 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
386 --
387 BEGIN
388 IF l_debug_level > 0 THEN
389 oe_debug_pub.add( 'IN SUBMIT_RESERVATION_REQUEST ' ) ;
390 END IF;
391 --R12.MOAC Start
392 l_access_mode := mo_global.get_access_mode;
393 IF l_access_mode = 'S' THEN
394 l_org_id := mo_global.get_current_org_id;
395 ELSE
396 l_org_id := NULL;
397 END IF;
398
399 IF p_selected_line_tbl.COUNT > 0 THEN
400 SELECT oe_reservation_sets_s.nextval
401 INTO l_set_id
402 FROM dual;
403 -- selected rows are inserted into oe_rsv_set_details table without parent, and then the
404 -- concurrent program will delete these rows after processing.
405 FOR I IN 1..p_selected_line_tbl.COUNT LOOP
406 INSERT INTO oe_rsv_set_details
407 (reservation_set_id
408 ,line_id
409 ,header_id
410 ,creation_date
411 ,created_by
412 ,last_update_date
413 ,last_updated_by
414 ,last_update_login
415 )
416 VALUES
417 (-l_set_id
418 ,p_selected_line_tbl(I).id1
419 ,0
420 ,sysdate
421 ,FND_GLOBAL.USER_ID
422 ,sysdate
423 ,FND_GLOBAL.USER_ID
424 ,FND_GLOBAL.LOGIN_ID
425 );
426 END LOOP;
427
428 END IF;
429 --R12.MOAC End
430 l_request_id :=
431 FND_REQUEST.Submit_Request
432 ('ONT','OMRSVORD','','',FALSE,
433 l_org_id, --p_org_id
434 'N', --p_use_reservation_time_fence
435 NULL, --p_order_number_low
436 NULL, --p_order_number_high
437 NULL, --p_customer_id
438 NULL, --p_order_type
439 NULL, --p_line_type_id
440 NULL, --p_warehouse
441 NULL, --p_inventory_item_id
442 NULL, --p_request_date_low
443 NULL, --p_request_date_high
444 NULL, --p_schedule_ship_date_low
445 NULL, --p_schedule_ship_date_high
446 NULL, --p_schedule_arrival_date_low
447 NULL, --p_schedule_arrival_date_high
448 NULL, --p_ordered_date_low
449 NULL, --p_ordered_date_high
450 NULL, --p_demand_class_code
451 NULL, --p_planning_priority
452 NULL, --p_booked -- 3359603
453 p_reservation_mode, --p_reservation_mode
454 NULL, --Dummy Parameter
455 NULL, --Dummy Parameter
456 p_percent, --p_percent
457 NULL, --p_shipment_priority
458 p_reserve_run_type, --p_reserve_run_type
459 p_reservation_set_Name, --p_reserve_set_Name
460 p_override_set, --p_override_set
461 p_order_by, --p_order_by
462 -l_set_id, --p_selected_ids -- R12.MOAC
463 NULL, --Dummy Parameter
464 p_partial_preference); --p_partial_preference
465
466 IF (l_request_id > 0) THEN
467 COMMIT WORK;
468 END IF;
469 IF l_debug_level > 0 THEN
470 oe_debug_pub.add( 'EXITING SUBMIT_RESERVATION_REQUEST ' ) ;
471 END IF;
472 return l_request_id;
473
474 END Submit_Reservation_Request;
475
476 /*-----------------------------------------------------------------
477 PROCEDURE : Update_Reservation_Qty
478 DESCRIPTION : Will update the corrected qty of the lines of reservation set
479 ------------------------------------------------------------------*/
480 PROCEDURE Update_Reservation_Qty
481 (p_reservation_set IN VARCHAR2,
482 p_sch_line_tbl IN OE_SCH_ORGANIZER_UTIL.sch_line_tbl_type)
483 IS
484 CURSOR rsv_set_id IS
485 SELECT reservation_set_id
486 FROM oe_reservation_sets
487 WHERE Reservation_set_name =p_reservation_set;
488
489 l_set_id NUMBER;
490 --
491 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
492 --
493 BEGIN
494 OPEN rsv_set_id;
495 FETCH rsv_set_id INTO l_set_id;
496 CLOSE rsv_set_id;
497 IF l_debug_level > 0 THEN
498 oe_debug_pub.add( 'IN UPDATE_RESERVATION_QTY FOR SET:'||l_set_id ) ;
499 END IF;
500
501
502 FOR I IN 1..p_sch_line_tbl.COUNT
503 LOOP
504 Update Oe_Rsv_Set_Details
505 SET Corrected_Qty = p_sch_line_tbl(I).corrected_qty,
506 Corrected_Qty2 = p_sch_line_tbl(I).corrected_qty2, -- INVCONV
507 last_update_login = FND_GLOBAL.LOGIN_ID,
508 last_updated_by = FND_GLOBAL.USER_ID,
509 last_update_date = sysdate
510 WHERE Reservation_set_id = l_set_id
511 AND Line_Id = p_sch_line_tbl(I).line_id;
512
513 END LOOP;
514 IF l_debug_level > 0 THEN
515 oe_debug_pub.add( 'EXITING UPDATE_RESERVATION_QTY' ) ;
516 END IF;
517
518 END Update_Reservation_Qty;
519
520 /*--------------------------------------------------
521 FUNCTION : Reservation_Set_Processed
522 DESCRIPTION: This api will return true if the reservation set is already processed
523 ----------------------------------------------------*/
524 FUNCTION Reservation_Set_Processed
525 (p_reservation_set_name IN VARCHAR2)
526 RETURN BOOLEAN
527 IS
528 CURSOR set_processed IS
529 SELECT process_flag
530 FROM oe_reservation_sets
531 WHERE reservation_set_name = p_reservation_set_name;
532
533 l_process_flag VARCHAR2(1);
534 --
535 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
536 --
537 BEGIN
538 OPEN set_processed;
539 FETCH set_processed INTO l_process_flag;
540 CLOSE set_processed;
541 IF l_process_flag = 'Y' THEN
542 RETURN TRUE;
543 ELSE
544 RETURN FALSE;
545 END IF;
546
547 END Reservation_Set_Processed;
548
549 --R12.MOAC
550 PROCEDURE Insert_into_tmp_tab(p_line_id IN NUMBER)
551 IS
552 BEGIN
553 INSERT INTO OE_SCH_ID_LIST_TMP(line_id)
554 VALUES(p_line_id);
555
556 END Insert_into_tmp_tab;
557
558 PROCEDURE Insert_into_tmp_tab(p_line_tbl IN OE_GLOBALS.Selected_Record_Tbl)
559 IS
560 BEGIN
561 FOR I IN 1..p_line_tbl.COUNT LOOP
562 INSERT INTO OE_SCH_ID_LIST_TMP(line_id)
563 VALUES(p_line_tbl(I).id1);
564 END LOOP;
565 END Insert_into_tmp_tab;
566
567 PROCEDURE delete_tmp_tab
568 IS
569 BEGIN
570 DELETE FROM OE_SCH_ID_LIST_TMP;
571 END delete_tmp_tab;
572 /*--------------------------------------------------
573 PROCEDURE : Process_Schedule_Action
574 DESCRIPTION: This api will call oe_group_sch_util.schedule_multi_lines procedure per header_id
575 ----------------------------------------------------*/
576 PROCEDURE Process_Schedule_Action
577 (p_selected_line_tbl IN OE_GLOBALS.Selected_Record_Tbl,
578 p_sch_action IN VARCHAR2,
579 x_atp_tbl OUT NOCOPY oe_atp.atp_tbl_type,
580 x_return_status OUT NOCOPY VARCHAR2,
581 x_msg_count OUT NOCOPY NUMBER,
582 x_msg OUT NOCOPY VARCHAR2)
583 IS
584
585 l NUMBER;
586 k NUMBER :=2;
587 l_org_id NUMBER := -99;
588 l_count NUMBER;
589 l_access_mode VARCHAR2(1);
590 p_processed_table OE_GLOBALS.Number_Tbl_Type;
591 l_selected_tbl OE_GLOBALS.Selected_Record_Tbl;
592 l_atp_tbl OE_ATP.Atp_Tbl_Type;
593 l_return_status VARCHAR2(1);
594 l_msg_count NUMBER;
595 l_msg_data VARCHAR2(2000);
596 l_error_count NUMBER := 0;
597 --
598 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
599 --
600 BEGIN
601
602 IF l_debug_level > 0 THEN
603 oe_debug_pub.add( 'ENTERING Process_Schedule_Action ' , 1 ) ;
604 END IF;
605 /* MOAC Changes */
606 --Remember and re-set the access mode, if its Multi.
607 l_access_mode := mo_global.get_access_mode();
608 x_return_status := FND_API.G_RET_STS_SUCCESS;
609 l_count := p_selected_line_tbl.count;
610
611 IF l_debug_level > 0 THEN
612 oe_debug_pub.add( 'Lines selected '||l_count , 1 ) ;
613 END IF;
614 IF l_count > 1 THEN
615 FOR I IN 1..p_selected_line_tbl.count Loop
616
617 /* in the selected table of records, id1 is line_id and id2 is header_id */
618
619 IF NOT p_processed_table.exists(p_selected_line_tbl(I).id2) THEN
620 --p_processed_table(p_selected_line_tbl(I).id2) :='Y';
621 p_processed_table(p_selected_line_tbl(I).id2) :=p_selected_line_tbl(I).id2;
622 l_selected_tbl(1).id1 := p_selected_line_tbl(I).id1;
623 insert_into_tmp_tab(p_selected_line_tbl(I).id1);
624 /* MOAC Changes */
625 IF l_access_mode <> 'S' OR -- 4757862
626 l_org_id <> p_selected_line_tbl(I).org_id THEN
627 l_org_id := p_selected_line_tbl(I).org_id;
628 Mo_global.set_policy_context(p_access_mode => 'S',
629 p_org_id => l_org_id);
630 END IF;
631
632 l := I + 1;
633 IF l <= l_count THEN
634 FOR J IN l..l_count LOOP --loop for remained modified lines
635 IF p_selected_line_tbl(J).id2 = p_selected_line_tbl (I).id2 THEN
636 l_selected_tbl(K).id1 := p_selected_line_tbl(J).id1;
637 K:= K+1;
638 insert_into_tmp_tab(p_selected_line_tbl(J).id1);
639 END IF;
640 END LOOP;
641 END IF;
642
643 K := 2; --reset K
644 OE_GROUP_SCH_UTIL.Schedule_Multi_lines
645 (p_selected_line_tbl => l_selected_tbl,
646 p_line_count => l_selected_tbl.count,
647 p_sch_action => p_sch_action,
648 x_atp_tbl => l_atp_tbl,
649 x_return_status => l_return_status,
650 x_msg_count => l_msg_count,
651 x_msg_data => l_msg_data);
652
653 --exception handling--
654
655 l_error_count := l_error_count + l_msg_count;
656 -- clearing the table
657 l_selected_tbl.DELETE;
658
659
660 /* MOAC Changes */
661 IF l_access_mode <> 'S' THEN
662 Mo_global.set_policy_context(p_access_mode => l_access_mode
663 , p_org_id =>'');
664 END IF;
665 END IF; /* if header_id in p_processed_table */
666
667 END LOOP;
668 ELSE
669 l_selected_tbl(1).id1 := p_selected_line_tbl(1).id1;
670 insert_into_tmp_tab(p_selected_line_tbl(1).id1);
671 /* MOAC Changes */
672 IF l_access_mode <> 'S' AND
673 l_org_id <> p_selected_line_tbl(1).org_id THEN
674 l_org_id := p_selected_line_tbl(1).org_id;
675 Mo_global.set_policy_context(p_access_mode => 'S',
676 p_org_id => l_org_id);
677 END IF;
678 OE_GROUP_SCH_UTIL.Schedule_Multi_lines
679 (p_selected_line_tbl => l_selected_tbl,
680 p_line_count => l_selected_tbl.count,
681 p_sch_action => p_sch_action,
682 x_atp_tbl => l_atp_tbl,
683 x_return_status => l_return_status,
684 x_msg_count => l_msg_count,
685 x_msg_data => l_msg_data);
686 l_error_count := l_error_count + l_msg_count;
687 /* MOAC Changes */
688 IF l_access_mode <> 'S' THEN
689 Mo_global.set_policy_context(p_access_mode => l_access_mode
690 , p_org_id =>'');
691 END IF;
692 END IF;
693 x_msg_count := l_error_count;
694 IF l_debug_level > 0 THEN
695 oe_debug_pub.add( 'EXITING Process_Schedule_Action ' , 1 ) ;
696 END IF;
697 EXCEPTION
698 WHEN OTHERS THEN
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
701 OE_MSG_PUB.Add_Exc_Msg
702 ( G_PKG_NAME
703 , 'process_schedule_action');
704 END IF;
705
706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
707
708 END Process_Schedule_Action;
709
710
711 END OE_SCH_ORGANIZER_UTIL;