DBA Data[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;