[Home] [Help]
PACKAGE: APPS.OE_ORDER_SCH_UTIL
Source
1 PACKAGE OE_ORDER_SCH_UTIL AS
2 /* $Header: OEXVSCHS.pls 120.0 2005/06/01 01:49:27 appldev noship $ */
3
4 OESCH_ACT_RESERVE CONSTANT VARCHAR2(30) := 'RESERVE';
5 OESCH_ACT_SCHEDULE CONSTANT VARCHAR2(30) := 'SCHEDULE';
6 OESCH_ACT_DEMAND CONSTANT VARCHAR2(30) := 'SCHEDULE';
7 OESCH_ACT_SOURCE CONSTANT VARCHAR2(30) := 'SOURCE';
8 OESCH_ACT_UNDEMAND CONSTANT VARCHAR2(30) := 'UNSCHEDULE';
9 OESCH_ACT_UNRESERVE CONSTANT VARCHAR2(30) := 'UNRESERVE';
10 OESCH_ACT_UNSCHEDULE CONSTANT VARCHAR2(30) := 'UNSCHEDULE';
11 OESCH_ACT_ATP_CHECK CONSTANT VARCHAR2(30) := 'ATP_CHECK';
12 OESCH_ACT_RESCHEDULE CONSTANT VARCHAR2(30) := 'RESCHEDULE';
13 OESCH_ACT_RES_TRANSFER CONSTANT VARCHAR2(30) := 'RES_TRANSFER';
14 OESCH_ACT_REDEMAND CONSTANT VARCHAR2(30) := 'REDEMAND';
15 OESCH_ACT_CHECK_SCHEDULING CONSTANT VARCHAR2(30) := 'CHECK_SCHEDULING';
16
17 OESCH_ENTITY_ORDER CONSTANT VARCHAR2(30) := 'ORDER';
18 OESCH_ENTITY_SET CONSTANT VARCHAR2(30) := 'SET';
19 OESCH_ENTITY_SHIP_SET CONSTANT VARCHAR2(30) := 'SHIP_SET';
20 OESCH_ENTITY_ARRIVAL_SET CONSTANT VARCHAR2(30) := 'ARRIVAL_SET';
21 OESCH_ENTITY_SMC CONSTANT VARCHAR2(30) := 'SHIP_MODEL_COMPLETE';
22 OESCH_ENTITY_ATO_CONFIG CONSTANT VARCHAR2(30) := 'ATO_CONFIG';
23 OESCH_ENTITY_CONFIGURATION CONSTANT VARCHAR2(30) := 'CONFIGURATION';
24 OESCH_ENTITY_O_LINE CONSTANT VARCHAR2(30) := 'ORDER_LINE';
25 OESCH_ENTITY_P_LINE CONSTANT VARCHAR2(30) := 'PARENT_LINE';
26
27 OESCH_ENTITY_LINE CONSTANT VARCHAR2(30) := 'LINE';
28
29 /* Valid Scheduling Status.
30 */
31
32 SCH_LEVEL_ONE CONSTANT VARCHAR2(30) := 'ONE';
33 SCH_LEVEL_TWO CONSTANT VARCHAR2(30) := 'TWO';
34 SCH_LEVEL_THREE CONSTANT VARCHAR2(30) := 'THREE';
35
36 /* Valid Scheduling Status.
37 */
38
39 OESCH_STATUS_BLANK CONSTANT VARCHAR2(30) := '';
40 OESCH_STATUS_RESERVED CONSTANT VARCHAR2(30) := 'RESERVED';
41 OESCH_STATUS_DEMANDED CONSTANT VARCHAR2(30) := 'SCHEDULED';
42 OESCH_STATUS_SCHEDULED CONSTANT VARCHAR2(30) := 'SCHEDULED';
43
44 /* Auto Schedule Flag
45 */
46
47 OESCH_PERFORM_SCHEDULING VARCHAR2(1) := 'Y';
48 OESCH_AUTO_SCH_FLAG VARCHAR2(30) := 'N';
49 OESCH_AUTO_SCH_FLAG_FROM_USER VARCHAR2(30) := 'N';
50
51 /* Cached Values
52 */
53
54 sch_cached_header_id NUMBER;
55 sch_cached_sch_level_code VARCHAR2(30);
56 sch_cached_sch_level_code_head VARCHAR2(30);
57 sch_cached_sch_level_code_line VARCHAR2(30);
58 sch_cached_order_type VARCHAR2(80);
59 sch_cached_line_type_id NUMBER;
60 sch_cached_line_type VARCHAR2(80);
61 MRP_SESSION_ID NUMBER := 0;
62
63 /* Group Scheduling Flag
64 */
65 OESCH_PERFORM_GRP_SCHEDULING VARCHAR2(1) := 'Y';
66
67 /* Flag to indicate if the schedule_parent_line to be called in post-write */
68 OESCH_SCH_POST_WRITE 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 TYPE number_arr IS TABLE OF number;
76 TYPE char30_arr IS TABLE OF varchar2(30);
77 TYPE char3_arr IS TABLE OF varchar2(3);
78 TYPE char1_arr IS TABLE of varchar2(1);
79 TYPE char2000_arr IS TABLE of varchar2(2000);
80 TYPE char1000_arr IS TABLE of varchar2(1000);
81 TYPE date_arr IS TABLE OF date;
82
83 TYPE request_rec_type is RECORD (
84 Header_Id NUMBER,
85 Line_Id NUMBER,
86 Config_Line_Id NUMBER,
87 Ato_Line_Id NUMBER,
88 Component_sequence_id NUMBER,
89 Component_Code VARCHAR2(1000),
90 Invoice_To_Org_Id NUMBER,
91 Inventory_Item_Id NUMBER,
92 Organization_Id NUMBER,
93 Identifier NUMBER,
94 Calling_Module NUMBER,
95 Sold_To_Org_Id NUMBER,
96 Ship_To_Org_Id NUMBER,
97 Destination_Time_Zone VARCHAR2(30),
98 Ordered_Quantity NUMBER,
99 Ordered_Quantity_uom VARCHAR2(3),
100 Requested_Ship_Date DATE,
101 Requested_Arrival_Date DATE,
102 Schedule_Date DATE,
103 Schedule_Arrival_Date DATE,
104 Earliest_Acceptable_Date DATE,
105 Latest_Acceptable_Date DATE,
106 Delivery_Lead_Time NUMBER,
107 Freight_Carrier VARCHAR2(30),
108 Ship_Method VARCHAR2(30),
109 Demand_Class VARCHAR2(30),
110 Ship_Set_Name VARCHAR2(30),
111 Arrival_Set_Name VARCHAR2(30),
112 Override_Flag VARCHAR2(1),
113 Action NUMBER,
114 Ship_Date DATE,
115 Available_Quantity NUMBER,
116 Group_Ship_Date DATE,
117 Group_Arrival_Date DATE,
118 Ship_From_Org_Id NUMBER,
119 Insert_Flag NUMBER,
120 Message VARCHAR2(2000)
121 );
122
123 TYPE sch_rec_type is RECORD
124 ( ship_from_org_id NUMBER
125 , schedule_date DATE
126 , shipping_method_code VARCHAR2(30)
127 , freight_carrier_code VARCHAR2(30)
128 , delivery_lead_time NUMBER
129 );
130
131 TYPE mrp_line_rec_type is RECORD
132 ( line_id NUMBER
133 , schedule_ship_date DATE
134 , schedule_arrival_date DATE
135 , ship_from_org_id NUMBER
136 , ship_method_code VARCHAR2(30)
137 );
138
139 TYPE Mrp_Line_Tbl_Type IS TABLE OF mrp_line_rec_type
140 INDEX BY BINARY_INTEGER;
141
142 Procedure Schedule_line
143 ( p_old_line_rec IN OE_ORDER_PUB.line_rec_type
144 , p_write_to_db IN VARCHAR2
145 , p_update_flag IN VARCHAR2 := FND_API.G_TRUE
146 , p_recursive_call IN VARCHAR2 := FND_API.G_TRUE
147 , p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
148 , x_atp_tbl OUT NOCOPY /* file.sql.39 change */ OE_ATP.atp_tbl_type
149 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
150
151 Procedure Update_line_record
152 ( p_line_tbl IN OE_ORDER_PUB.line_tbl_type
153 , p_x_new_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
154 , p_write_to_db IN VARCHAR2
155 , p_recursive_call IN VARCHAR2
156 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
157
158 FUNCTION Need_Scheduling
159 ( p_line_rec IN OE_ORDER_PUB.line_rec_type
160 , p_old_line_rec IN OE_ORDER_PUB.line_rec_type)
161 RETURN BOOLEAN;
162
163 Procedure Check_Item_Attribute
164 (p_line_rec IN OE_ORDER_PUB.line_rec_type);
165
166 FUNCTION Scheduling_Activity
167 (p_line_rec IN OE_ORDER_PUB.line_rec_type)
168 RETURN BOOLEAN;
169
170 Procedure Validate_Line
171 ( p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
172 , p_old_line_rec IN OE_ORDER_PUB.Line_Rec_Type
173 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
174
175 Procedure Process_request
176 ( p_old_line_rec IN OE_ORDER_PUB.line_rec_type
177 , p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
178 , x_out_atp_tbl OUT NOCOPY /* file.sql.39 change */ OE_ATP.Atp_Tbl_Type
179 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
180
181
182 FUNCTION Get_Lead_Time
183 ( p_ato_line_id IN NUMBER
184 , p_ship_from_org_id IN NUMBER)
185 RETURN NUMBER;
186
187 Procedure Create_Group_Request
188 ( p_line_rec IN OE_ORDER_PUB.line_rec_type
189 , p_old_line_rec IN OE_ORDER_PUB.line_rec_type
190 , x_group_req_rec OUT NOCOPY /* file.sql.39 change */ OE_GRP_SCH_UTIL.Sch_Group_Rec_Type
191 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
192 );
193
194
195 Procedure Load_INV_Request
196 ( p_line_rec IN Oe_Order_Pub.Line_Rec_Type
197 , p_quantity_to_reserve IN NUMBER
198 , p_quantity2_to_reserve IN NUMBER DEFAULT NULL-- INVCONV
199 , x_reservation_rec OUT NOCOPY /* file.sql.39 change */ Inv_Reservation_Global.Mtl_Reservation_Rec_Type);
200
201 FUNCTION Schedule_Attribute_Changed
202 ( p_line_rec IN Oe_Order_Pub.Line_Rec_Type
203 , p_old_line_rec IN Oe_Order_Pub.Line_Rec_Type)
204 RETURN BOOLEAN;
205
206 Procedure Unreserve_Line
207 ( p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
208 , p_quantity_to_unreserve IN NUMBER
209 , p_quantity2_to_unreserve IN NUMBER -- INVCONV
210 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
211
212 PROCEDURE Call_ATP
213 ( p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ
214 , x_atp_table OUT NOCOPY /* file.sql.39 change */ MRP_ATP_PUB.ATP_Rec_Typ
215 , x_atp_supply_demand OUT NOCOPY /* file.sql.39 change */ MRP_ATP_PUB.ATP_Supply_Demand_Typ
216 , x_atp_period OUT NOCOPY /* file.sql.39 change */ MRP_ATP_PUB.ATP_Period_Typ
217 , x_atp_details OUT NOCOPY /* file.sql.39 change */ MRP_ATP_PUB.ATP_Details_Typ
218 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
219 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
220 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER);
221
222 Procedure Load_MRP_Request
223 ( p_line_tbl IN Oe_Order_Pub.Line_Tbl_Type
224 , p_old_line_tbl IN Oe_Order_Pub.Line_Tbl_Type
225 := OE_ORDER_PUB.G_MISS_LINE_TBL
226 , x_atp_table OUT NOCOPY /* file.sql.39 change */ MRP_ATP_PUB.ATP_Rec_Typ
227 );
228
229 Procedure Load_Results
230 ( p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ
231 , p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
232 , x_atp_tbl OUT NOCOPY /* file.sql.39 change */ OE_ATP.ATP_tbl_Type
233 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
234
235 Procedure Insert_Into_Mtl_Sales_Orders
236 ( p_header_rec IN OE_ORDER_PUB.header_rec_type);
237
238 FUNCTION Get_mtl_sales_order_id(p_header_id IN NUMBER)
239 RETURN NUMBER;
240
241 /*
242 PROCEDURE Set_Auto_Sch_Flag
243 (p_value_from_user IN VARCHAR2 := FND_API.G_MISS_CHAR,
244 p_default_value IN VARCHAR2 := FND_API.G_MISS_CHAR);
245 */
246
247 PROCEDURE Set_Auto_Sch_Flag
248 (p_value_from_user IN VARCHAR2 := FND_API.G_MISS_CHAR);
249
250 PROCEDURE Build_Included_Items
251 (p_line_rec IN OE_ORDER_PUB.line_rec_type,
252 x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type);
253
254 Procedure SPLIT_SCHEDULING
255 ( p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
256 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
257
258 PROCEDURE SPLIT_RESERVATIONS
259 ( p_reserved_line_id IN NUMBER
260 , p_ordered_quantity IN NUMBER
261 , p_reserved_quantity IN NUMBER
262 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
263 );
264
265 -- added by fabdi 03/May/2001
266 -- added new parameter p_line_id as DEFAULT NULL
267 Procedure Query_Qty_Tree(p_org_id IN NUMBER,
268 p_item_id IN NUMBER,
269 p_line_id IN NUMBER DEFAULT NULL,
270 p_sch_date IN DATE DEFAULT NULL,
271 x_on_hand_qty OUT NOCOPY /* file.sql.39 change */ NUMBER,
272 x_avail_to_reserve OUT NOCOPY /* file.sql.39 change */ NUMBER,
273 x_on_hand_qty2 OUT NOCOPY /* file.sql.39 change */ NUMBER, -- INVCONV
274 x_avail_to_reserve2 OUT NOCOPY /* file.sql.39 change */ NUMBER -- INVCONV
275 );
276
277 Procedure Update_Results_from_backlog_wb
278 ( p_mrp_line_tbl IN mrp_line_tbl_type
279 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
280 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
281 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
282
283 FUNCTION Get_Session_Id
284 RETURN number;
285
286 FUNCTION Get_MRP_Session_Id
287 RETURN number;
288
289 Procedure Insert_Mandatory_Components
290 (p_order_number IN NUMBER,
291 p_ato_line_id IN NUMBER,
292 p_customer_name IN VARCHAR2,
293 p_customer_location IN VARCHAR2,
294 p_arrival_set_name IN VARCHAR2,
295 p_ship_set_name IN VARCHAR2,
296 p_ship_set_id IN NUMBER,
297 p_requested_ship_date IN DATE,
298 p_requested_arrival_date IN DATE,
299 p_session_id IN NUMBER,
300 p_instance_id IN NUMBER,
301 p_insert_code IN NUMBER,
302 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
303 );
304
305 Function Within_Rsv_Time_Fence(p_schedule_ship_date IN DATE)
306 RETURN BOOLEAN;
307
308 FUNCTION Get_Scheduling_Level( p_header_id IN NUMBER,
309 p_line_type_id IN NUMBER)
310 RETURN VARCHAR2;
311
312 Procedure Delete_Row(p_line_id IN NUMBER);
313
314 /* -- added by fabdi 03/May/2001 INVCONV not needed now
315 PROCEDURE get_process_query_quantities
316 ( p_org_id IN NUMBER
317 , p_item_id IN NUMBER
318 , p_line_id IN NUMBER
319 , x_on_hand_qty OUT NOCOPY NUMBER
320 , x_avail_to_reserve OUT NOCOPY NUMBER
321 );
322 -- end fabdi
323 */
324
325
326 --Added for the Bug 2097933
327 PROCEDURE Post_Forms_Commit
328 (x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
329 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
330 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
331
332 END OE_ORDER_SCH_UTIL;