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