1 PACKAGE WMS_REPLENISHMENT_PVT AS
2 /* $Header: WMSREPVS.pls 120.6 2008/04/01 01:43:33 satkumar noship $ */
3
4 TYPE psrTyp IS RECORD (
5 attribute_name VARCHAR2(30),
6 priority NUMBER,
7 sort_order VARCHAR2(4));
8
9 TYPE psrTabTyp IS TABLE OF psrTyp INDEX BY BINARY_INTEGER;
10
11
12
13 TYPE CONSOL_ITEM_REPL_REC IS RECORD
14 (
15 Organization_id NUMBER,
16 Item_id NUMBER,
17 Repl_To_Subinventory_code VARCHAR2(10),
18 Repl_UOM_code VARCHAR2(3),
19 total_demand_qty NUMBER,
20 available_onhand_qty NUMBER,
21 open_mo_qty NUMBER,
22 final_replenishment_qty NUMBER,
23 date_required DATE
24 );
25 --ajith changed the IS TABLE OF CONSOL_ITEM_REPL_CUR to CONSOL_ITEM_REPL_REC
26 TYPE CONSOL_ITEM_REPL_TBL IS TABLE OF CONSOL_ITEM_REPL_REC INDEX BY BINARY_INTEGER;
27
28 -- to be used for bulk processing in the code
29 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
30 TYPE uom_tab IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
31 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
32 TYPE char_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
33 TYPE char1_tab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
34
35 -- The following types are used to cache the UOM conversions. The three keys to this
36 -- are the inventory item, from UOM code, and to UOM code. This will yield the conversion
37 -- rate by using a nested PLSQL table structure.
38 TYPE to_uom_code_tb IS TABLE OF NUMBER INDEX BY VARCHAR2(3);
39 TYPE from_uom_code_tb IS TABLE OF to_uom_code_tb INDEX BY VARCHAR2(3);
40 TYPE item_uom_conversion_tb IS TABLE OF from_uom_code_tb INDEX BY BINARY_INTEGER;
41
42 g_item_uom_conversion_tb item_uom_conversion_tb;
43
44 G_TRACE_ON NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),2);
45
46
47 FUNCTION get_conversion_rate(p_item_id IN NUMBER,
48 p_from_uom_code IN VARCHAR2,
49 p_to_uom_code IN VARCHAR2) RETURN NUMBER;
50
51
52 PROCEDURE DYNAMIC_REPLENISHMENT(p_org_id IN NUMBER,
53 P_Batch_id IN NUMBER,
54 p_Plan_Tasks IN VARCHAR2,
55 p_Release_Sequence_Rule_Id IN NUMBER,
56 P_repl_level IN NUMBER DEFAULT 1,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_data OUT NOCOPY VARCHAR2);
60
61 PROCEDURE PUSH_REPLENISHMENT(P_repl_level IN NUMBER DEFAULT 1,
62 p_Item_id IN NUMBER,
63 p_organization_id IN NUMBER,
64 p_ABC_assignment_group_id IN NUMBER, -- For ABC Compile Group
65 p_abc_class_id IN NUMBER, -- For Item Classification
66 p_Order_Type_id IN NUMBER,
67 p_Carrier_id IN NUMBER,
68 p_customer_class IN VARCHAR2,
69 p_customer_id IN NUMBER,
70 p_Ship_Method_code IN VARCHAR2,
71 p_Scheduled_Ship_Date_To IN NUMBER,
72 p_Scheduled_Ship_Date_From IN NUMBER,
73 p_Forward_Pick_Sub IN VARCHAR2,
74 p_repl_UOM IN VARCHAR2,
75 p_Repl_Lot_Size IN NUMBER,
76 p_Min_Order_lines_threshold IN NUMBER,
77 p_Min_repl_qty_threshold IN NUMBER,
78 p_max_NUM_items_for_repl IN NUMBER,
79 p_Sort_Criteria IN NUMBER,
80 p_Auto_Allocate IN VARCHAR2,
81 p_Plan_Tasks IN VARCHAR2,
82 p_Release_Sequence_Rule_Id IN NUMBER,
83 p_Create_Reservation IN VARCHAR2,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_msg_count OUT NOCOPY NUMBER,
86 x_msg_data OUT NOCOPY VARCHAR2);
87
88
89 PROCEDURE PROCESS_REPLENISHMENT(
90 p_Repl_level IN NUMBER,
91 p_repl_type IN NUMBER,
92 p_Repl_Lot_Size IN NUMBER,
93 P_consol_item_repl_tbl IN OUT NOCOPY CONSOL_ITEM_REPL_TBL, --ajith changed
94 p_Create_Reservation IN VARCHAR2,
95 p_Auto_Allocate IN VARCHAR2,
96 p_Plan_Tasks IN VARCHAR2,
97 x_return_status OUT NOCOPY VARCHAR2,
98 x_msg_count OUT NOCOPY NUMBER,
99 x_msg_data OUT NOCOPY VARCHAR2);
100
101
102
103
104 PROCEDURE CREATE_REPL_MOVE_ORDER(p_Repl_level IN NUMBER,
105 p_repl_type IN NUMBER,
106 p_consol_item_repl_tbl IN OUT NOCOPY CONSOL_ITEM_REPL_TBL,
107 p_Create_Reservation IN VARCHAR2,
108 p_Repl_Lot_Size IN NUMBER,
109 p_Auto_Allocate IN VARCHAR2,
110 p_Plan_Tasks IN VARCHAR2,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2);
114
115 PROCEDURE Get_Source_Sub_Dest_Loc_Info(p_Org_id IN NUMBER,
116 p_Item_id IN NUMBER,
117 p_Picking_Sub IN VARCHAR2,
118 x_source_sub OUT NOCOPY VARCHAR2,
119 x_src_pick_uom OUT NOCOPY VARCHAR2,
120 x_MAX_MINMAX_QUANTITY OUT nocopy NUMBER,
121 x_fixed_lot_multiple OUT nocopy NUMBER,
122 x_return_status OUT nocopy VARCHAR2) ;
123
124
125 PROCEDURE GET_OPEN_MO_QTY(p_Repl_level IN NUMBER,
126 p_repl_type IN NUMBER,
127 p_Create_Reservation IN VARCHAR2,
128 x_consol_item_repl_tbl IN OUT NOCOPY CONSOL_ITEM_REPL_TBL,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_count OUT NOCOPY NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2);
132
133
134 PROCEDURE GET_AVAILABLE_ONHAND_QTY(p_Repl_level IN NUMBER,
135 p_repl_type IN NUMBER,
136 p_Create_Reservation IN VARCHAR2,
137 x_consol_item_repl_tbl IN OUT NOCOPY CONSOL_ITEM_REPL_TBL,
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2);
141
142 FUNCTION get_available_capacity(p_quantity_function IN NUMBER,
143 p_organization_id IN NUMBER,
144 p_subinventory_code IN VARCHAR2,
145 p_locator_id IN NUMBER,
146 p_inventory_item_id IN NUMBER,
147 p_unit_volume IN NUMBER,
148 p_unit_volume_uom_code IN VARCHAR2,
149 p_unit_weight IN NUMBER,
150 p_unit_weight_uom_code IN VARCHAR2,
151 p_primary_uom IN VARCHAR2,
152 p_transaction_uom IN VARCHAR2,
153 p_base_uom IN VARCHAR2,
154 p_transaction_quantity IN NUMBER)
155 RETURN NUMBER;
156
157
158 PROCEDURE allocate_repl_move_order(
159 p_Quantity_function_id IN NUMBER,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_msg_count OUT NOCOPY NUMBER,
162 x_msg_data OUT nocopy VARCHAR2
163 );
164
165
166 PROCEDURE CREATE_RSV(p_replenishment_type IN NUMBER, -- 1- Stock Up/Push; 2- Dynamic
167 l_debug IN NUMBER,
168 l_organization_id IN NUMBER,
169 l_inventory_item_id IN NUMBER,
170 l_demand_type_id IN NUMBER,
171 l_demand_so_header_id IN NUMBER,
172 l_demand_line_id IN NUMBER,
173 l_split_wdd_id IN NUMBER,
174 l_primary_uom_code IN VARCHAR2,
175 l_supply_uom_code IN VARCHAR2,
176 l_atd_qty IN NUMBER,
177 l_atd_prim_qty IN NUMBER,
178 l_supply_type_id IN NUMBER,
179 l_supply_header_id IN NUMBER,
180 l_supply_line_id IN NUMBER,
181 l_supply_line_detail_id IN NUMBER,
182 l_supply_expected_time IN DATE,
183 l_demand_expected_time IN DATE,
184 l_subinventory_code IN VARCHAR2 DEFAULT NULL,
185 l_rsv_rec IN OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type,
186 l_serial_number IN OUT NOCOPY inv_reservation_global.serial_number_tbl_type,
187 l_to_serial_number IN OUT NOCOPY inv_reservation_global.serial_number_tbl_type,
188 l_quantity_reserved IN OUT NOCOPY NUMBER,
189 l_quantity_reserved2 IN OUT NOCOPY NUMBER,
190 l_rsv_id IN OUT NOCOPY NUMBER,
191 x_return_status IN OUT NOCOPY VARCHAR2,
192 x_msg_count IN OUT NOCOPY NUMBER,
193 x_msg_data IN OUT NOCOPY VARCHAR2);
194
195 PROCEDURE Get_to_Sub_For_Dynamic_Repl(P_Org_id IN NUMBER,
196 P_Item_id IN NUMBER,
197 P_PRIMARY_DEMAND_QTY IN NUMBER,
198 X_TO_SUBINVENTORY_CODE IN OUT NOCOPY VARCHAR2,
199 X_REPL_UOM_CODE OUT NOCOPY VARCHAR2);
200
201
202 PROCEDURE POPULATE_DYNAMIC_REPL_DEMAND(p_repl_level IN NUMBER,
203 p_org_id IN NUMBER,
204 P_Batch_id IN NUMBER,
205 p_Release_Sequence_Rule_Id IN NUMBER,
206 x_consol_item_repl_tbl OUT NOCOPY CONSOL_ITEM_REPL_TBL,
207 x_return_status OUT NOCOPY VARCHAR2,
208 x_msg_count OUT NOCOPY NUMBER,
209 x_msg_data OUT NOCOPY VARCHAR2);
210
211 PROCEDURE POPULATE_PUSH_REPL_DEMAND(p_repl_level IN NUMBER,
212 p_Item_id IN NUMBER,
213 p_organization_id IN NUMBER,
214 p_ABC_assignment_group_id IN NUMBER, -- For ABC Compile Group
215 p_abc_class_id IN NUMBER, -- For Item Classification
216 p_Order_Type_id IN NUMBER,
217 p_Carrier_id IN NUMBER,
218 p_customer_class IN VARCHAR2,
219 p_customer_id IN NUMBER,
220 p_Ship_Method_code IN VARCHAR2,
221 p_Scheduled_Ship_Date_To IN NUMBER,
222 p_Scheduled_Ship_Date_From IN NUMBER,
223 p_Forward_Pick_Sub IN VARCHAR2,
224 p_repl_UOM IN VARCHAR2,
225 p_Release_Sequence_Rule_Id IN NUMBER,
226 p_Min_Order_lines_threshold IN NUMBER,
227 p_Min_repl_qty_threshold IN NUMBER,
228 p_max_NUM_items_for_repl IN NUMBER,
229 p_Sort_Criteria IN NUMBER,
230 x_consol_item_repl_tbl OUT NOCOPY CONSOL_ITEM_REPL_TBL,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2);
234
235 --ajith changed the procedure name from POPULATE_REPL_DEMAND_NEXT_LEVEL to POPULATE_REPL_DEMAND_NEXT_LEV because only 30 chars allowed.
236 /*
237 PROCEDURE POPULATE_REPL_DEMAND_NEXT_LEV(P_replenishment_level IN NUMBER,
238 x_consol_item_repl_tbl OUT NOCOPY CONSOL_ITEM_REPL_TBL,
239 x_return_status OUT NOCOPY VARCHAR2,
240 x_msg_count OUT NOCOPY NUMBER,
241 x_msg_data OUT NOCOPY VARCHAR2);
242 */
243
244 FUNCTION GET_SORT_TRIP_STOP_DATE(P_delivery_detail_id IN NUMBER,
245 P_TRIP_STOP_DATE_SORT IN VARCHAR2)
246 RETURN NUMBER;
247
248
249 FUNCTION GET_SORT_INVOICE_VALUE(P_SOURCE_HEADER_ID IN NUMBER, P_INVOICE_VALUE_SORT VARCHAR2)
250 RETURN NUMBER;
251
252
253 FUNCTION Get_Expected_Time(p_demand_type_id in number,
254 p_source_header_id in number,
255 p_source_line_id in number,
256 p_delivery_line_id in number) RETURN DATE;
257
258
259 PROCEDURE UPDATE_DELIVERY_DETAIL (
260 p_delivery_detail_id IN NUMBER,
261 P_PRIMARY_QUANTITY IN NUMBER,
262 P_SPLIT_DELIVERY_DETAIL_ID IN NUMBER DEFAULT NULL,
263 p_split_source_line_id IN NUMBER DEFAULT NULL,
264 x_return_status OUT NOCOPY VARCHAR2
265 );
266
267
268 PROCEDURE ADJUST_ATR_FOR_ITEM (p_repl_level IN NUMBER
269 , p_repl_type IN NUMBER
270 , x_consol_item_repl_tbl IN OUT NOCOPY CONSOL_ITEM_REPL_TBL
271 , x_return_status OUT NOCOPY VARCHAR2
272 );
273
274 --Updates the replenishment_status of single passed delivery_detail_id
275 -- If p_repl_status = 'R' marks it RR
276 -- If p_repl_status = 'C' marks it RC
277 -- If p_repl_status = NULL - Reverts WDD to original status (Ready to release / backorder)
278 --
279 PROCEDURE update_wdd_repl_status (p_deliv_detail_id IN NUMBER
280 , p_repl_status IN VARCHAR2
281 , p_deliv_qty IN NUMBER DEFAULT NULL
282 , x_return_status OUT NOCOPY VARCHAR2
283 );
284
285
286 PROCEDURE Init_Rules(p_pick_seq_rule_id IN NUMBER
290 , x_trip_stop_date_sort OUT NOCOPY VARCHAR2
287 , x_order_id_sort OUT NOCOPY VARCHAR2
288 , x_INVOICE_VALUE_SORT OUT NOCOPY VARCHAR2
289 , x_SCHEDULE_DATE_SORT OUT NOCOPY VARCHAR2
291 , x_SHIPMENT_PRI_SORT OUT NOCOPY VARCHAR2
292 , x_ordered_psr OUT nocopy psrTabTyp
293 , x_api_status OUT NOCOPY VARCHAR2);
294
295 END WMS_REPLENISHMENT_PVT;
[Home] [Help]