[Home] [Help]
PACKAGE: APPS.OE_SCHEDULE_UTIL
Source
1 PACKAGE OE_SCHEDULE_UTIL AS
2 /* $Header: OEXUSCHS.pls 120.12.12010000.1 2008/07/25 07:57:36 appldev ship $ */
3
4 -- scheduling actions
5
6 OESCH_ACT_RESERVE CONSTANT VARCHAR2(30) := 'RESERVE';
7 OESCH_ACT_SCHEDULE CONSTANT VARCHAR2(30) := 'SCHEDULE';
8 OESCH_ACT_DEMAND CONSTANT VARCHAR2(30) := 'SCHEDULE';
9 OESCH_ACT_SOURCE CONSTANT VARCHAR2(30) := 'SOURCE';
10 OESCH_ACT_UNDEMAND CONSTANT VARCHAR2(30) := 'UNSCHEDULE';
11 OESCH_ACT_UNRESERVE CONSTANT VARCHAR2(30) := 'UNRESERVE';
12 OESCH_ACT_UNSCHEDULE CONSTANT VARCHAR2(30) := 'UNSCHEDULE';
13 OESCH_ACT_ATP_CHECK CONSTANT VARCHAR2(30) := 'ATP_CHECK';
14 OESCH_ACT_RESCHEDULE CONSTANT VARCHAR2(30) := 'RESCHEDULE';
15 OESCH_ACT_RES_TRANSFER CONSTANT VARCHAR2(30) := 'RES_TRANSFER';
16 OESCH_ACT_REDEMAND CONSTANT VARCHAR2(30) := 'REDEMAND';
17 OESCH_ACT_CHECK_SCHEDULING CONSTANT VARCHAR2(30) := 'CHECK_SCHEDULING';
18
19
20 -- scheduling entitites
21
22 OESCH_ENTITY_ORDER CONSTANT VARCHAR2(30) := 'ORDER';
23 OESCH_ENTITY_SET CONSTANT VARCHAR2(30) := 'SET';
24 OESCH_ENTITY_SHIP_SET CONSTANT VARCHAR2(30) := 'SHIP_SET';
25 OESCH_ENTITY_ARRIVAL_SET CONSTANT VARCHAR2(30) := 'ARRIVAL_SET';
26 OESCH_ENTITY_SMC CONSTANT VARCHAR2(30) := 'SHIP_MODEL_COMPLETE';
27 OESCH_ENTITY_ATO_CONFIG CONSTANT VARCHAR2(30) := 'ATO_CONFIG';
28 OESCH_ENTITY_CONFIGURATION CONSTANT VARCHAR2(30) := 'CONFIGURATION';
29 OESCH_ENTITY_O_LINE CONSTANT VARCHAR2(30) := 'ORDER_LINE';
30 OESCH_ENTITY_P_LINE CONSTANT VARCHAR2(30) := 'PARENT_LINE';
31 OESCH_ENTITY_LINE CONSTANT VARCHAR2(30) := 'LINE';
32
33 --Caller
34 SCH_INTERNAL CONSTANT VARCHAR2(30) := 'INTERNAL';
35 SCH_EXTERNAL CONSTANT VARCHAR2(30) := 'EXTERNAL';
36
37 -- Valid Scheduling levels
38
39 SCH_LEVEL_ONE CONSTANT VARCHAR2(30) := 'ONE';
40 SCH_LEVEL_TWO CONSTANT VARCHAR2(30) := 'TWO';
41 SCH_LEVEL_THREE CONSTANT VARCHAR2(30) := 'THREE';
42 -- BUG 1955004
43 SCH_LEVEL_FOUR CONSTANT VARCHAR2(30) := 'FOUR';
44 SCH_LEVEL_FIVE CONSTANT VARCHAR2(30) := 'FIVE';
45 -- END 1955004
46
47 -- 6663462 Delayed_schedule
48
49 OE_Delayed_Schedule_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
50
51
52
53 -- Valid Scheduling Status.
54
55 OESCH_STATUS_SCHEDULED CONSTANT VARCHAR2(30) := 'SCHEDULED';
56
57 -- Auto Schedule Flag and globals
58
59 OESCH_PERFORM_SCHEDULING VARCHAR2(1) := 'Y';
60 OESCH_AUTO_SCH_FLAG VARCHAR2(30) := 'N';
61 OESCH_AUTO_SCH_FLAG_FROM_USER VARCHAR2(30) := 'N';
62 OESCH_PERFORM_GRP_SCHEDULING VARCHAR2(1) := 'Y';
63
64 OESCH_AUTO_SCHEDULE_PROFILE VARCHAR2(30) :=
65 FND_PROFILE.VALUE('ONT_AUTOSCHEDULE');
66
67 --Added for ER 6110708
68 OESCH_ITEM_IS_SUBSTITUTED VARCHAR2(1) := 'N';
69
70 /* Bug: 2097933..
71 This flag is to indicate if Reservation has been
72 performed on this line or not */
73 OESCH_PERFORMED_RESERVATION VARCHAR2(1) := 'N';
74
75 -- Scheduling action flag
76 G_LINE_ACTION VARCHAR2(30);
77
78
79 -- Cached Values
80
81 sch_cached_header_id NUMBER;
82 sch_cached_sch_level_code VARCHAR2(30);
83 sch_cached_sch_level_code_head VARCHAR2(30);
84 sch_cached_sch_level_code_line VARCHAR2(30);
85 sch_cached_order_type VARCHAR2(80);
86 sch_cached_line_type_id NUMBER;
87 sch_cached_line_type VARCHAR2(80);
88 MRP_SESSION_ID NUMBER := 0;
89 -- 3748723 : New cache variables for sales order id
90 sch_cached_mtl_order_type_id NUMBER;
91 sch_cached_mtl_order_type_name VARCHAR2(80);
92 sch_cached_mtl_sales_order_id NUMBER;
93 sch_cached_mtl_header_id NUMBER;
94 sch_cached_mtl_source_code VARCHAr2(40);
95
96 -- BUG 1955004
97 TYPE inactive_demand_rec_type IS RECORD
98 (line_id NUMBER
99 ,scheduling_level_code VARCHAR2(30));
100
101 TYPE OE_inactive_demand_tbl_type IS TABLE OF
102 inactive_demand_rec_type INDEX by BINARY_INTEGER;
103
104 OE_inactive_demand_tbl OE_inactive_demand_tbl_type;
105 --END 1955004
106
107 -- BUG 1282873
108 TYPE sch_rec_type IS RECORD
109 ( line_id NUMBER
110 ,attribute1 varchar2(30));
111
112 TYPE oe_sch_tbl_type is TABLE OF
113 sch_rec_type INDEX BY binary_integer;
114
115 OE_Override_Tbl OE_sch_Tbl_Type;
116 -- END 1282873
117 /* -- 3288805 --*/
118 G_HEADER_ID NUMBER := null; -- moved from body to spec.
119 G_DATE_TYPE VARCHAR2(30) := null; -- moved from body to spec.
120
121
122 PROCEDURE Schedule_line
123 ( p_old_line_rec IN OE_ORDER_PUB.line_rec_type
124 ,p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
125 ,p_caller IN VARCHAR2 := SCH_INTERNAL
126 ,x_return_status OUT NOCOPY VARCHAR2);
127
128
129
130 FUNCTION Need_Scheduling
131 ( p_line_rec IN OE_ORDER_PUB.line_rec_type
132 ,p_old_line_rec IN OE_ORDER_PUB.line_rec_type
133 ,x_line_action OUT NOCOPY VARCHAR2
134
135 ,x_auto_sch OUT NOCOPY VARCHAR2)
136
137 RETURN BOOLEAN;
138
139
140 PROCEDURE Validate_Line
141 ( p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
142 ,p_old_line_rec IN OE_ORDER_PUB.Line_Rec_Type
143 ,p_sch_action IN VARCHAR2
144 ,p_caller IN VARCHAR2 := SCH_EXTERNAL
145 ,x_return_status OUT NOCOPY VARCHAR2);
146
147
148 Procedure Process_request
149 ( p_old_line_rec IN OE_ORDER_PUB.line_rec_type
150 ,p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
151 ,p_caller IN VARCHAR2
152 ,p_sch_action IN VARCHAR2
153 ,x_return_status OUT NOCOPY VARCHAR2);
154
155
156
157 FUNCTION Get_Lead_Time
158 ( p_ato_line_id IN NUMBER
159 ,p_ship_from_org_id IN NUMBER)
160 RETURN NUMBER;
161
162
163 FUNCTION Get_Date_Type
164 ( p_header_id IN NUMBER)
165 RETURN VARCHAR2;
166
167
168 FUNCTION Get_Order_Number(p_header_id in number)
169 RETURN NUMBER;
170
171
172 Procedure Load_INV_Request
173 ( p_line_rec IN Oe_Order_Pub.Line_Rec_Type
174 ,p_quantity_to_reserve IN NUMBER
175 ,p_quantity2_to_reserve IN NUMBER DEFAULT NULL -- INVCONV
176 ,x_reservation_rec OUT NOCOPY Inv_Reservation_Global.Mtl_Reservation_Rec_Type);
177
178
179
180 Procedure Insert_Into_Mtl_Sales_Orders
181 ( p_header_rec IN OE_ORDER_PUB.header_rec_type);
182
183 FUNCTION Get_mtl_sales_order_id(p_header_id IN NUMBER,
184 p_order_type_id IN NUMBER DEFAULT NULL) --3745318 added a new parameter p_order_type_id
185 RETURN NUMBER;
186
187 FUNCTION Get_Scheduling_Level( p_header_id IN NUMBER,
188 p_line_type_id IN NUMBER)
189 RETURN VARCHAR2;
190
191 Procedure Process_Group_of_lines
192 ( p_x_old_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
193 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
194 ,p_caller IN VARCHAR2
195 ,x_return_status OUT NOCOPY VARCHAR2);
196
197
198 Procedure Process_line
199 ( p_old_line_rec IN OE_ORDER_PUB.line_rec_type
200 ,p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
201 ,p_caller IN VARCHAR2
202 ,p_call_prn IN BOOLEAN := TRUE
203 ,x_return_status OUT NOCOPY VARCHAR2);
204
205
206 -- BUG 1955004
207 Procedure Inactive_Demand_Scheduling
208 ( p_x_old_line_rec IN OE_ORDER_PUB.line_rec_type
209 ,p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
210 ,p_sch_action IN VARCHAR2 := NULL
211 ,x_return_status OUT NOCOPY VARCHAR2);
212
213 -- END 1955004
214
215 Procedure Load_MRP_request_from_tbl
216 ( p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type
217 ,p_old_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type
218 ,p_partial_set IN BOOLEAN := FALSE
219 ,p_sch_action IN VARCHAR2 := NULL
220 ,p_part_of_set IN VARCHAR2 DEFAULT 'N' --4405004
221 ,x_mrp_atp_rec OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ);
222
223
224 -- Added parameter p_old_line_tbl to support bug 1955004
225 Procedure Load_Results_from_tbl
226 ( p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ
227 , p_old_line_tbl IN OE_ORDER_PUB.line_tbl_type
228 , p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
229 , p_sch_action IN VARCHAR2 := NULL
230 , p_partial IN BOOLEAN := FALSE
231 , p_partial_set IN BOOLEAN := FALSE
232 , x_return_status OUT NOCOPY VARCHAR2);
233
234
235 Procedure ATP_Check
236 ( p_old_line_rec IN OE_ORDER_PUB.line_rec_type,
237 p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type,
238 p_validate IN VARCHAR2 := FND_API.G_TRUE,
239 x_atp_tbl OUT NOCOPY OE_ATP.atp_tbl_type,
240
241 x_return_status OUT NOCOPY VARCHAR2);
242
243
244 PROCEDURE Multi_ATP_Check
245 (p_old_line_tbl IN OE_ORDER_PUB.line_tbl_type,
246 p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type,
247 x_atp_tbl OUT NOCOPY OE_ATP.atp_tbl_type,
248
249 x_return_status OUT NOCOPY VARCHAR2);
250
251
252 Procedure Unreserve_Line
253 ( p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
254 ,p_old_ship_from_org_id IN NUMBER DEFAULT NULL -- 6628134
255 ,p_quantity_to_unreserve IN NUMBER
256 ,p_quantity2_to_unreserve IN NUMBER DEFAULT NULL-- INCONV
257 ,x_return_status OUT NOCOPY VARCHAR2);
258
259
260 Procedure Reserve_line
261 ( p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
262 ,p_quantity_to_reserve IN NUMBER
263 ,p_quantity2_to_reserve IN NUMBER DEFAULT NULL-- INCONV
264 ,p_rsv_update IN BOOLEAN DEFAULT FALSE -- To be passed as TRUE where there is an increase to the reserve quantity
265 ,x_return_status OUT NOCOPY VARCHAR2);
266
267
268 PROCEDURE Set_Auto_Sch_Flag
269 (p_value_from_user IN VARCHAR2 := FND_API.G_MISS_CHAR);
270
271 --Bug 5948059
272 --To return the value of OESCH_AUTO_SCH_FLAG
273 FUNCTION Get_Auto_Sch_Flag
274 RETURN VARCHAR2;
275
276 Procedure SPLIT_SCHEDULING
277 ( p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
278 , x_return_status OUT NOCOPY VARCHAR2);
279
280
281
282 TYPE mrp_line_rec_type is RECORD
283 ( line_id NUMBER
284 , schedule_ship_date DATE
285 , schedule_arrival_date DATE
286 , ship_from_org_id NUMBER
287 , ship_method_code VARCHAR2(30)
288 );
289
290 TYPE Mrp_Line_Tbl_Type IS TABLE OF mrp_line_rec_type
291 INDEX BY BINARY_INTEGER;
292
293 Procedure Update_Results_from_backlog_wb
294 ( p_mrp_line_tbl IN mrp_line_tbl_type
295 , x_msg_count OUT NOCOPY NUMBER
296
297 , x_msg_data OUT NOCOPY VARCHAR2
298
299 , x_return_status OUT NOCOPY VARCHAR2);
300
301
302 FUNCTION Get_Session_Id
303 RETURN number;
304
305 FUNCTION Get_MRP_Session_Id
306 RETURN number;
307
308 Procedure Insert_Mandatory_Components
309 (p_order_number IN NUMBER,
310 p_ato_line_id IN NUMBER,
311 p_customer_name IN VARCHAR2,
312 p_customer_location IN VARCHAR2,
313 p_arrival_set_name IN VARCHAR2,
314 p_ship_set_name IN VARCHAR2,
315 p_ship_set_id IN NUMBER,
316 p_requested_ship_date IN DATE,
317 p_requested_arrival_date IN DATE,
318 p_session_id IN NUMBER,
319 p_instance_id IN NUMBER,
320 p_insert_code IN NUMBER,
321 x_return_status OUT NOCOPY VARCHAR2
322
323 );
324
325 Function Within_Rsv_Time_Fence(p_schedule_ship_date IN DATE,
326 p_org_id IN NUMBER)
327 RETURN BOOLEAN;
328
329 Procedure Delete_Row(p_line_id IN NUMBER);
330
331 Procedure Call_MRP_ATP
332 ( p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.Line_Rec_Type
333 ,p_old_line_rec IN OE_ORDER_PUB.Line_Rec_Type
334 ,x_return_status OUT NOCOPY VARCHAR2);
335
336
337 Procedure Post_Forms_Commit
338 (x_return_status OUT NOCOPY VARCHAR2
339
340 ,x_msg_count OUT NOCOPY NUMBER
341
342 ,x_msg_data OUT NOCOPY VARCHAR2);
343
344
345 Procedure call_process_order
346 ( p_x_old_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
347 , p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
348 , p_control_rec IN OE_GLOBALS.control_rec_type
349 , p_caller IN VARCHAR2
350 , x_return_status OUT NOCOPY VARCHAR2);
351
352
353 PROCEDURE Handle_External_Lines
354 (p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type);
355
356 /*----------------------------------------------------------------
357 This record type will be used to remember old inventory item
358 values on the lines which is part of set. The related records will be
359 used in execution of set delayed request.
360 -----------------------------------------------------------------*/
361
362 TYPE Inventory_item_rec_type IS RECORD
363 ( line_id NUMBER
364 ,inventory_item_id NUMBER);
365
366 TYPE OE_Item_Tbl_Type is TABLE OF
367 Inventory_item_rec_type INDEX BY binary_integer;
368
369 OE_Item_Tbl OE_Item_Tbl_Type;
370
371 -- Start 2434807 --
372
373 G_ATP_TBL OE_ATP.atp_tbl_type; -- Moved from Package body to Scpec.
374
375 PROCEDURE Get_Atp_Table_Count(p_atp_tbl OUT NOCOPY OE_ATP.Atp_Tbl_Type,
376 p_atp_tbl_cnt OUT NOCOPY NUMBER);
377
378 -- End 2434807 --
379
380 Procedure Display_Sch_Errors
381 ( p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ
382 , p_line_tbl IN OE_ORDER_PUB.line_tbl_type
383 := OE_ORDER_PUB.G_MISS_LINE_TBL
384 , p_line_id IN NUMBER DEFAULT NULL);
385
386
387 FUNCTION Schedule_Attribute_Changed
388 ( p_line_rec IN Oe_Order_Pub.line_rec_type
389 , p_old_line_rec IN Oe_Order_Pub.line_rec_type)
390 RETURN BOOLEAN;
391
392 FUNCTION Set_Attr_Matched(p_set_ship_from_org_id IN NUMBER ,
393 p_line_ship_from_org_id IN NUMBER,
394 p_set_ship_to_org_id IN NUMBER,
395 p_line_ship_to_org_id IN NUMBER,
396 p_set_schedule_ship_date IN DATE,
397 p_line_schedule_ship_date IN DATE,
398 p_set_arrival_date IN DATE,
399 p_line_arrival_date IN DATE,
400 p_set_shipping_method_code IN VARCHAR2,
401 p_line_shipping_method_code IN VARCHAR2,
402 p_set_type IN VARCHAR2)
403 RETURN BOOLEAN;
404
405 FUNCTION Validate_ship_method
406 (p_new_ship_method IN VARCHAR2,
407 p_old_ship_method IN VARCHAR2,
408 p_ship_from_org_id IN NUMBER)
409 RETURN BOOLEAN;
410
411 -- Start 2595661
412 FUNCTION Get_Pick_Status (p_line_id IN NUMBER) RETURN BOOLEAN;
413
414 PROCEDURE Do_Unreserve (p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
415 ,p_quantity_to_unreserve IN NUMBER
416 ,p_quantity2_to_unreserve IN NUMBER DEFAULT NULL -- INVCONV
417 ,p_old_ship_from_org_id IN NUMBER DEFAULT NULL -- 5024936
418 ,x_return_status OUT NOCOPY VARCHAR2);
419
420 -- End 2595661
421
422 -- 2391781
423 /*----------------------------------------------------------------
424 This record type will be used to store modified scheduling attribute item
425 values on the lines which is part of set. The related records will be
426 used in cascading during group scheduling.
427 -----------------------------------------------------------------*/
428
429 TYPE cascade_sch_rec_type IS RECORD
430 ( line_id NUMBER
431 ,set_id NUMBER
432 ,attribute1 VARCHAR2(240):= NULL
433 ,attribute2 VARCHAR2(240):= NULL
434 ,date_attribute1 DATE := NULL);
435
436 TYPE OE_sch_Attrb_Tbl_Type is TABLE OF
437 cascade_sch_rec_type INDEX BY binary_integer;
438
439 OE_sch_Attrb_Tbl OE_sch_Attrb_Tbl_Type;
440
441 Procedure Schedule_Split_Lines
442 ( p_sch_set_tbl IN OE_ORDER_PUB.request_tbl_type
443 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
444
445 Procedure Update_PO(p_schedule_ship_date IN DATE,
446 p_source_document_id IN VARCHAR2,
447 p_source_document_line_id IN VARCHAR2);
448 -- Pack J
449 PROCEDURE Promise_Date_for_Sch_Action
450 (p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.Line_Rec_Type
451 ,p_sch_action IN VARCHAR2
452 ,P_header_id IN NUMBER DEFAULT NULL);
453
454 PROCEDURE Global_atp(p_line_id IN NUMBER);
455
456 FUNCTION Get_ATP_CHECK_Session_Id
457 RETURN number;
458
459 Procedure Cascade_Ship_Set_Attr
460 ( p_request_rec IN OE_Order_Pub.Request_Rec_Type
461 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
462
463 -- 4026758
464 Procedure Log_Delete_Set_Request
465 (p_header_id IN NUMBER,
466 p_line_id IN NUMBER,
467 p_set_id IN NUMBER,
468 x_return_status OUT NOCOPY VARCHAR2);
469
470 /* Added the following 2 procedures to fix the bug 6378240 */
471
472 PROCEDURE MRP_ROLLBACK
473 ( p_line_id IN NUMBER
474 ,p_schedule_action_code IN VARCHAR2
475 ,x_return_status OUT NOCOPY VARCHAR2);
476
477 PROCEDURE CALL_MRP_ROLLBACK
478 ( x_return_status OUT NOCOPY VARCHAR2);
479
480 /* Added the following procedure to fix the bug 6663462 */
481
482 Procedure DELAYED_SCHEDULE_LINES
483 ( x_return_status OUT NOCOPY VARCHAR2);
484
485 -- Added for ER 6110708
486 PROCEDURE IS_ITEM_SUBSTITUTED
487 (
488 p_application_id IN NUMBER
489 , p_entity_short_name IN VARCHAR2
490 , p_validation_entity_short_name IN VARCHAR2
491 , p_validation_tmplt_short_name IN VARCHAR2
492 , p_record_set_short_name IN VARCHAR2
493 , p_scope IN VARCHAR2
494 , x_result_out OUT NOCOPY NUMBER
495 );
496
497 -- Added for ER 6110708
498 PROCEDURE IS_LINE_PICKED
499 (
500 p_application_id IN NUMBER
501 , p_entity_short_name IN VARCHAR2
502 , p_validation_entity_short_name IN VARCHAR2
503 , p_validation_tmplt_short_name IN VARCHAR2
504 , p_record_set_short_name IN VARCHAR2
505 , p_scope IN VARCHAR2
506 , x_result_out OUT NOCOPY NUMBER
507 );
508
509
510 END OE_SCHEDULE_UTIL;