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