[Home] [Help]
PACKAGE: APPS.GMI_RESERVATION_UTIL
Source
1 PACKAGE GMI_Reservation_Util AUTHID CURRENT_USER AS
2 /* $Header: GMIURSVS.pls 115.35 2004/02/16 19:48:45 pkanetka ship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIURSVS.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private utilities relating to OPM |
13 | reservation. |
14 | |
15 | - Check_Missing |
16 | - Validation_for_Query |
17 | - Validation_Before_Allocate |
18 | - Get_Default_Lot |
19 | - Create_Default_Lot |
20 | - Get_Allocation |
21 | - Get_OPMUOM_from_AppsUOM |
22 | - Get_AppsUOM_from_OPMUOM |
23 | - Get_Org_from_SO_Line |
24 | - Get_OPM_item_from_Apps |
25 | - Reallocate |
26 | - Transfer_Msg_Stack moved to OE_MSG_PUB OEXUMSGS,B.pls |
27 | - Get_DefaultLot_from_ItemCtl |
28 | - PrintLn |
29 | - Validation_ictran_rec |
30 | - Set_Pick_Lots |
31 | - Create_Empty_Default_Lot |
32 | - Default_Lot_Exist |
33 | |
34 | HISTORY |
35 | 07-MAR-2000 odaboval Created |
36 | 27-APR-2000 mpetrosi Removed Transfer_Msg_Stack to OE_MSG_PUB |
37 | 13-SEP-2002 HW - BUG#:2536589 Added 2 new procedures for Bill-Only |
38 | functionality. Update_opm_trxns and find_lot_id |
39 | Oct, 2002 HW added new procedures to support WSH.H Harmonization |
40 | projects. Validate_lot_number and line_allocated |
41 | Nov, 2002 HW BUG#:2654963 Added p_delivery_detail_id to proc |
42 | line_allocated |
43 | Nov, 2002 HW bug#2677054 - WSH.I project |
44 +=========================================================================+
45 API Name : GMI_Reservation_Util
46 Type : Private
47 Function : This package contains Private Utilities procedures used to
48 OPM reservation process.
49 -
50 Pre-reqs : N/A
51 Parameters: Per function
52
53 Current Vers : 1.0
54
55 */
56
57 /*
58 ================================================================================
59 Define the ic_tran_pnd record and table and global memory table
60 ================================================================================
61 */
62 G_DEFAULT_LOCT CONSTANT VARCHAR2(16):= FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
63
64 /* SUBTYPE transaction_record IS ic_tran_pnd%ROWTYPE; */
65
66 TYPE ic_item_mst_rec IS RECORD
67 ( item_id ic_item_mst.item_id%TYPE
68 , inventory_item_id mtl_system_items.inventory_item_id%TYPE
69 , item_no ic_item_mst.item_no%TYPE
70 , whse_item_id ic_item_mst.whse_item_id%TYPE
71 , item_um ic_item_mst.item_um%TYPE
72 , item_um2 ic_item_mst.item_um2%TYPE
73 , dualum_ind ic_item_mst.dualum_ind%TYPE
74 , alloc_class ic_item_mst.alloc_class%TYPE
75 , noninv_ind ic_item_mst.noninv_ind%TYPE
76 , deviation_lo ic_item_mst.deviation_lo%TYPE
77 , deviation_hi ic_item_mst.deviation_lo%TYPE
78 , grade_ctl ic_item_mst.grade_ctl%TYPE
79 , inactive_ind ic_item_mst.inactive_ind%TYPE
80 , lot_ctl ic_item_mst.lot_ctl%TYPE
81 , lot_indivisible ic_item_mst.lot_indivisible%TYPE
82 , loct_ctl ic_item_mst.loct_ctl%TYPE);
83
84
85 TYPE l_ic_tran_rec_tbl IS TABLE OF GMI_TRANS_ENGINE_PUB.ictran_rec
86 INDEX BY BINARY_INTEGER;
87
88 ic_tran_rec_tbl l_ic_tran_rec_tbl;
89
90
91 PROCEDURE Check_Missing
92 ( p_event IN VARCHAR2
93 , p_rec_to_check IN INV_Reservation_Global.mtl_reservation_rec_type
94 , x_return_status OUT NOCOPY VARCHAR2
95 , x_msg_count OUT NOCOPY NUMBER
96 , x_msg_data OUT NOCOPY VARCHAR2
97 );
98
99 PROCEDURE Validation_for_Query
100 ( p_query_input IN inv_reservation_global.mtl_reservation_rec_type
101 , x_opm_um OUT NOCOPY VARCHAR2
102 , x_apps_um OUT NOCOPY VARCHAR2
103 , x_ic_item_mst_rec OUT NOCOPY GMI_Reservation_Util.ic_item_mst_rec
104 , x_return_status OUT NOCOPY VARCHAR2
105 , x_msg_count OUT NOCOPY NUMBER
106 , x_msg_data OUT NOCOPY VARCHAR2
107 , x_error_code OUT NOCOPY NUMBER /* Bug 2168710 - Added parameter */
108 );
109
110 PROCEDURE Validation_before_Allocate
111 ( p_mtl_rsv_rec IN INV_Reservation_Global.mtl_reservation_rec_type
112 , x_allocation_rec OUT NOCOPY GMI_Auto_Allocate_PUB.gmi_allocation_rec
113 , x_ic_item_mst_rec OUT NOCOPY GMI_Reservation_Util.ic_item_mst_rec
114 , x_orgn_code OUT NOCOPY VARCHAR2
115 , x_return_status OUT NOCOPY VARCHAR2
116 , x_msg_count OUT NOCOPY NUMBER
117 , x_msg_data OUT NOCOPY VARCHAR2
118 );
119
120 PROCEDURE Get_Default_Lot
121 ( x_ic_tran_pnd_index OUT NOCOPY BINARY_INTEGER
122 , x_return_status OUT NOCOPY VARCHAR2
123 , x_msg_count OUT NOCOPY NUMBER
124 , x_msg_data OUT NOCOPY VARCHAR2
125 );
126
127 PROCEDURE Create_Default_Lot
128 ( p_allocation_rec IN GMI_AUTO_ALLOCATE_PUB.gmi_allocation_rec
129 , p_ic_item_mst_rec IN GMI_Reservation_Util.ic_item_mst_rec
130 , p_orgn_code IN VARCHAR2
131 , p_trans_id IN NUMBER DEFAULT NULL
132 , x_return_status OUT NOCOPY VARCHAR2
133 , x_msg_count OUT NOCOPY NUMBER
134 , x_msg_data OUT NOCOPY VARCHAR2
135 );
136
137 PROCEDURE Get_Allocation
138 ( p_trans_id IN NUMBER
139 , x_ic_tran_pnd_index OUT NOCOPY BINARY_INTEGER
140 , x_return_status OUT NOCOPY VARCHAR2
141 , x_msg_count OUT NOCOPY NUMBER
142 , x_msg_data OUT NOCOPY VARCHAR2
143 );
144
145 PROCEDURE Get_OPMUOM_from_AppsUOM(
146 p_Apps_UOM IN VARCHAR2
147 , x_OPM_UOM OUT NOCOPY VARCHAR2
148 , x_return_status OUT NOCOPY VARCHAR2
149 , x_msg_count OUT NOCOPY NUMBER
150 , x_msg_data OUT NOCOPY VARCHAR2
151 );
152
153 PROCEDURE Get_AppsUOM_from_OPMUOM(
154 p_OPM_UOM IN VARCHAR2
155 , x_Apps_UOM OUT NOCOPY VARCHAR2
156 , x_return_status OUT NOCOPY VARCHAR2
157 , x_msg_count OUT NOCOPY NUMBER
158 , x_msg_data OUT NOCOPY VARCHAR2
159 );
160
161 PROCEDURE Get_Org_from_SO_Line
162 ( p_oe_line_id IN NUMBER
163 , x_organization_id OUT NOCOPY NUMBER
164 , x_return_status OUT NOCOPY VARCHAR2
165 , x_msg_count OUT NOCOPY NUMBER
166 , x_msg_data OUT NOCOPY VARCHAR2
167 );
168
169 PROCEDURE Get_OPM_item_from_Apps
170 ( p_organization_id IN NUMBER
171 , p_inventory_item_id IN NUMBER
172 , x_ic_item_mst_rec OUT NOCOPY GMI_Reservation_Util.ic_item_mst_rec
173 , x_return_status OUT NOCOPY VARCHAR2
174 , x_msg_count OUT NOCOPY NUMBER
175 , x_msg_data OUT NOCOPY VARCHAR2
176 );
177
178 PROCEDURE Reallocate
179 ( p_query_input IN inv_reservation_global.mtl_reservation_rec_type
180 , x_allocated_trans OUT NOCOPY NUMBER
181 , x_allocated_qty OUT NOCOPY NUMBER
182 , x_return_status OUT NOCOPY VARCHAR2
183 , x_msg_count OUT NOCOPY NUMBER
184 , x_msg_data OUT NOCOPY VARCHAR2
185 );
186
187 PROCEDURE Get_DefaultLot_from_ItemCtl
188 ( p_organization_id IN NUMBER
189 , p_inventory_item_id IN NUMBER
190 , x_default_lot_index OUT NOCOPY NUMBER
191 , x_return_status OUT NOCOPY VARCHAR2
192 , x_msg_count OUT NOCOPY NUMBER
193 , x_msg_data OUT NOCOPY VARCHAR2
194 );
195
196 PROCEDURE PrintLn
197 ( p_msg IN VARCHAR2
198 , p_file_name IN VARCHAR2 DEFAULT '0'
199 );
200
201 PROCEDURE Validation_ictran_rec
202 ( p_ic_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
203 , x_ic_tran_rec OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
204 , x_return_status OUT NOCOPY VARCHAR2
205 , x_msg_count OUT NOCOPY NUMBER
206 , x_msg_data OUT NOCOPY VARCHAR2
207 );
208
209 PROCEDURE Set_Pick_Lots
210 ( p_ic_tran_rec IN OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
211 , p_mo_line_id IN NUMBER
212 , p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE
213 , x_return_status OUT NOCOPY VARCHAR2
214 , x_msg_count OUT NOCOPY NUMBER
215 , x_msg_data OUT NOCOPY VARCHAR2
216 );
217
218 PROCEDURE Create_Empty_Default_Lot
219 ( p_ic_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
220 , p_organization_id IN NUMBER
221 , x_default_lot_index OUT NOCOPY BINARY_INTEGER
222 , x_return_status OUT NOCOPY VARCHAR2
223 , x_msg_count OUT NOCOPY NUMBER
224 , x_msg_data OUT NOCOPY VARCHAR2
225 );
226
227 PROCEDURE Default_Lot_Exist
228 ( p_line_id IN NUMBER
229 , p_item_id IN NUMBER
230 , x_trans_id OUT NOCOPY NUMBER
231 , x_return_status OUT NOCOPY VARCHAR2
232 , x_msg_count OUT NOCOPY NUMBER
233 , x_msg_data OUT NOCOPY VARCHAR2
234 );
235
236 Procedure create_dflt_lot_from_scratch
237 ( p_whse_code IN VARCHAR2
238 , p_line_id IN NUMBER
239 , p_item_id IN NUMBER
240 , p_qty1 IN NUMBER
241 , p_qty2 IN NUMBER
242 , x_return_status OUT NOCOPY VARCHAR2
243 , x_msg_count OUT NOCOPY NUMBER
244 , x_msg_data OUT NOCOPY VARCHAR2
245 );
246
247 Procedure create_transaction_for_rcv
248 ( p_whse_code IN VARCHAR2
249 , p_transaction_id IN NUMBER
250 , p_line_id IN NUMBER
251 , p_item_id IN NUMBER
252 , p_lot_id IN NUMBER
253 , p_location IN VARCHAR2
254 , p_qty1 IN NUMBER
255 , p_qty2 IN NUMBER
256 , x_return_status OUT NOCOPY VARCHAR2
257 , x_msg_count OUT NOCOPY NUMBER
258 , x_msg_data OUT NOCOPY VARCHAR2
259 ) ;
260
261 PROCEDURE balance_default_lot
262 ( p_ic_default_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
263 , p_opm_item_id IN NUMBER
264 , x_return_status OUT NOCOPY VARCHAR2
265 , x_msg_count OUT NOCOPY NUMBER
266 , x_msg_data OUT NOCOPY VARCHAR2
267 );
268
269 Procedure get_OPM_account ( v_dest_org_id IN NUMBER,
270 v_apps_item_id IN NUMBER,
271 v_vendor_site_id IN number,
272 x_cc_id OUT NOCOPY NUMBER,
273 x_ac_id OUT NOCOPY NUMBER) ;
274
275 Procedure check_OPM_trans_for_so_line
276 ( p_so_line_id IN NUMBER,
277 p_new_delivery_detail_id IN NUMBER,
278 x_return_status OUT NOCOPY VARCHAR2) ;
279
280 FUNCTION Get_Opm_converted_qty
281 (
282 p_apps_item_id IN NUMBER,
283 p_organization_id IN NUMBER,
284 p_apps_from_uom IN VARCHAR2,
285 p_apps_to_uom IN VARCHAR2,
286 p_original_qty IN NUMBER,
287 p_lot_id IN NUMBER DEFAULT 0
288 ) RETURN NUMBER;
289
290 Procedure query_staged_flag
291 ( x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2,
294 x_staged_flag OUT NOCOPY VARCHAR2,
295 p_reservation_id IN NUMBER);
296
297 Procedure find_default_lot
298 ( x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2,
301 x_reservation_id OUT NOCOPY VARCHAR2,
302 p_line_id IN NUMBER) ;
303 procedure check_lot_loct_ctl (
304 p_inventory_item_id IN NUMBER
305 ,p_mtl_organization_id IN NUMBER
306 ,x_ctl_ind OUT NOCOPY VARCHAR2) ;
307
308 PROCEDURE split_trans_from_om
309 ( p_old_source_line_id IN NUMBER,
310 p_new_source_line_id IN NUMBER,
311 p_qty_to_split IN NUMBER,
312 p_qty2_to_split IN NUMBER,
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_count OUT NOCOPY NUMBER,
315 x_msg_data OUT NOCOPY VARCHAR2);
316
317
318 -- HW OPM BUG#:2536589 New procedure
319 PROCEDURE update_opm_trxns(
320 p_trans_id IN NUMBER,
321 p_inventory_item_id IN NUMBER,
322 p_organization_id IN NUMBER,
323 x_return_status OUT NOCOPY VARCHAR2,
324 x_msg_count OUT NOCOPY NUMBER,
325 x_msg_data OUT NOCOPY VARCHAR2);
326 -- HW OPM BUG#:2536589 New procedure
327 PROCEDURE find_lot_id (
328 p_trans_id IN NUMBER,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2);
332
333 -- HW OPM -added for Harmonization project for WSH.I
334 PROCEDURE validate_lot_number (
335 p_inventory_item_id IN NUMBER,
336 p_organization_id IN NUMBER,
337 p_lot_number IN VARCHAR2,
338 x_return_status OUT NOCOPY VARCHAR2);
339
340
341 -- HW BUG#:2654963 Added p_delivery_detail_id
342 PROCEDURE line_allocated (
343 p_inventory_item_id IN NUMBER,
344 p_organization_id IN NUMBER,
345 p_line_id IN NUMBER,
346 p_delivery_detail_id IN NUMBER DEFAULT NULL,
347 check_status OUT NOCOPY NUMBER,
348 x_return_status OUT NOCOPY VARCHAR2);
349
350 -- HW Added for bug#2677054 - WSH.I project
351 PROCEDURE is_line_allocated (
352 p_inventory_item_id IN NUMBER,
353 p_organization_id IN NUMBER,
354 p_delivery_detail_id IN NUMBER DEFAULT NULL,
355 check_status OUT NOCOPY NUMBER,
356 x_return_status OUT NOCOPY VARCHAR2);
357
358 -- PK Added for Bug 3055126 - 11.5.10 WSH.J
359 PROCEDURE validate_opm_quantities(
360 p_inventory_item_id IN NUMBER,
361 p_organization_id IN NUMBER,
362 p_quantity IN OUT NOCOPY NUMBER,
363 p_quantity2 IN OUT NOCOPY NUMBER,
364 p_lot_number IN VARCHAR2,
365 p_sublot_number IN VARCHAR2,
366 x_check_status OUT NOCOPY NUMBER,
367 x_return_status OUT NOCOPY VARCHAR2);
368
369 END GMI_Reservation_Util;
370