1 PACKAGE inv_detail_util_pvt AUTHID CURRENT_USER AS
2 /* $Header: INVVDEUS.pls 120.7.12020000.2 2013/01/31 22:02:56 sahmahes ship $ */
3 --
4 --
5 -- File : INVVDEUS.pls
6 -- Content : INV_DETAIL_UTIL_PVT package spec
7 -- Description : utlitities used by the detailing engine (both inv and wms versions)
8 -- Notes :
9 -- Modified : 10/22/99 bitang created
10 -- Modified : 04/04/2002 grao bug# 2286454
11 --
12 -- *****************************************************************************
13 -- * Detailing Request Information
14 -- *****************************************************************************
15 --
16 SUBTYPE g_request_line_rec_type IS mtl_txn_request_lines%ROWTYPE;
17 -- This record type stores some derived information, such as, revision
18 -- control code, etc., regarding the detailing request
19 --
20 TYPE g_request_context_rec_type IS RECORD
21 (
22 type_code NUMBER,
23 transfer_flag BOOLEAN,
24 transaction_action_id NUMBER,
25 transaction_source_type_id NUMBER,
26 item_revision_control NUMBER,
27 item_lot_control_code NUMBER,
28 item_serial_control_code NUMBER,
29 lot_expiration_date DATE,
30 primary_uom_code VARCHAR2(3),
31 secondary_uom_code VARCHAR2(3),
32 transaction_uom_code VARCHAR2(3),
33 pick_strategy_id NUMBER,
34 put_away_strategy_id NUMBER,
35 txn_header_id NUMBER,
36 txn_line_id NUMBER,
37 txn_line_detail NUMBER,
38 customer_id NUMBER,
39 customer_number VARCHAR2(30),
40 ship_to_location NUMBER,
41 shipment_number NUMBER,
42 freight_code VARCHAR2(30),
43 detail_serial BOOLEAN,
44 item_locator_control_code NUMBER,
45 org_locator_control_code NUMBER,
46 posting_flag VARCHAR2(1),
47 detail_any_serial NUMBER,
48 base_uom_code VARCHAR2(3),
49 unit_volume NUMBER,
50 volume_uom_code VARCHAR2(3),
51 unit_weight NUMBER,
52 weight_uom_code VARCHAR2(3),
53 wms_task_type NUMBER,
54 item_reservable_type NUMBER,
55 end_assembly_pegging_code NUMBER
56 );
57 --
58 --
59 -- The following record/table type is used to stores the levels
60 -- from which the detailing will starts.
61 -- The reason for having different levels is that the move order line
62 -- might specify a level (org,revision,lot_number,subinventory,locator)
63 -- while the reservations made for the same demand source might
64 -- exist in different level or levels.
65 -- [ Added the following two columns as a part of allocation of serial reserved items ]
66 TYPE g_detail_level_rec_type IS RECORD
67 (
68 revision VARCHAR2(3),
69 lot_number VARCHAR2(80),
70 subinventory_code VARCHAR2(30),
71 grade_code VARCHAR2(150),
72 locator_id NUMBER,
73 primary_quantity NUMBER,
74 transaction_quantity NUMBER,
75 secondary_quantity NUMBER,
76 reservation_id NUMBER,
77 lpn_id NUMBER,
78 serial_number VARCHAR2(30), /* FP: Bug 7268522 */ -- [ Added new column - serial_number ]
79 serial_resv_flag VARCHAR2(1) -- [ Added new column - serial_resv_flag]
80 );
81 TYPE g_detail_level_tbl_type IS TABLE OF g_detail_level_rec_type
82 INDEX BY BINARY_INTEGER;
83 --
84 --
85 -- *****************************************************************************
86 -- * Types Used For Serial Number Detailing
87 -- *****************************************************************************
88 --
89 -- The following defines record/table type and instance
90 -- to store serial number(s) used in serial number detailing
91 TYPE g_serial_row is RECORD
92 (
93 serial_identifier NUMBER,
94 inventory_item_id NUMBER,
95 organization_id NUMBER,
96 serial_number VARCHAR2(30)
97 );
98 --
99 TYPE g_serial_row_table is TABLE OF g_serial_row
100 INDEX BY BINARY_INTEGER;
101 --
102 TYPE numtabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
103 TYPE chartabtype30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
104
105 TYPE g_serial_row_table_rec is RECORD (
106 inventory_item_id NUMTABTYPE
107 , organization_id NUMTABTYPE
108 , serial_number CHARTABTYPE30
109 , serial_status NUMTABTYPE
110 );
111
112 -- Pointer to last row in g_output_serial_rows
113 g_serial_tbl_ptr NUMBER;
114 -- Stores serial numbers detailed
115 g_output_serial_rows g_serial_row_table;
116 --
117 --
118 -- *****************************************************************************
119 -- * Types Used For Generating Output
120 -- *****************************************************************************
121 --
122 -- The followings define the record/table type used in
123 -- generating detailing output
124 TYPE g_output_process_rec_type IS RECORD
125 (
126 revision VARCHAR2(3) ,
127 from_subinventory_code VARCHAR2(10) ,
128 from_locator_id NUMBER ,
129 to_subinventory_code VARCHAR2(10) ,
130 to_locator_id NUMBER ,
131 lot_number VARCHAR2(80) ,
132 lot_expiration_date DATE ,
133 serial_number_start VARCHAR2(30) ,
134 serial_number_end VARCHAR2(30) ,
135 transaction_quantity NUMBER ,
136 primary_quantity NUMBER ,
137 secondary_quantity NUMBER ,
138 grade_code VARCHAR2(150) ,
139 pick_rule_id NUMBER ,
140 put_away_rule_id NUMBER ,
141 reservation_id NUMBER ,
142 from_cost_group_id NUMBER ,
143 to_cost_group_id NUMBER ,
144 lpn_id NUMBER
145 );
146 TYPE g_output_process_tbl_type IS TABLE OF g_output_process_rec_type
147 INDEX BY BINARY_INTEGER;
148 g_output_process_tbl g_output_process_tbl_type;
149 g_output_process_tbl_size INTEGER;
150 --used to enabled WMS run time logging
151 g_insert_lot_flag NUMBER;
152 g_insert_serial_flag NUMBER;
153 g_transaction_header_id NUMBER;
154 g_mo_transaction_date DATE;
155 --
156 -- *****************************************************************************
157 -- * Procedures and Functions
158 -- *****************************************************************************
159 --
160 -- *****************************************************************************
161 -- * Validation Related Procedures
162 -- *****************************************************************************
163 --
164 -- Description
165 -- 1. validate move order line id
166 -- 2. fetch move order line into g_mtl_txn_request_lines_rec
167 -- 3. compute and return request context and request line record
168 --
169 PROCEDURE validate_and_init
170 (x_return_status OUT NOCOPY VARCHAR2,
171 p_request_line_id IN NUMBER,
172 p_suggest_serial IN VARCHAR2 DEFAULT fnd_api.g_false,
173 x_request_line_rec OUT NOCOPY g_request_line_rec_type,
174 x_request_context OUT NOCOPY g_request_context_rec_type,
175 p_wave_simulation_mode IN VARCHAR2 DEFAULT 'N'
176 );
177 --
178 -- *****************************************************************************
179 -- * Serial Number Detailing Related Procedures
180 -- *****************************************************************************
181 --
182 -- Description
183 -- Initialize the internal table that stores the serial numbers detailed
184 -- to empty
185 PROCEDURE init_output_serial_rows;
186 -- --------------------------------------------------------------------------
187 -- What does it do:
188 -- Given the item/organization, inventory controls, quantity for a autodetailed
189 -- row and also from/to serial number range info,
190 -- it fetches and populates available serial numbers into g_output_serial_rows.
191 -- --------------------------------------------------------------------------
192 --
193 PROCEDURE get_serial_numbers (
194 p_inventory_item_id IN NUMBER
195 , p_organization_id IN NUMBER
196 , p_revision IN VARCHAR2
197 , p_lot_number IN VARCHAR2
198 , p_subinventory_code IN VARCHAR2
199 , p_locator_id IN NUMBER
200 , p_required_sl_qty IN NUMBER
201 , p_from_range IN VARCHAR2
202 , p_to_range IN VARCHAR2
203 , p_unit_number IN VARCHAR2
204 , p_detail_any_serial IN NUMBER
205 , p_cost_group_id IN NUMBER
206 , p_transaction_type_id IN NUMBER
207 , x_available_sl_qty OUT NOCOPY NUMBER
208 , x_serial_index OUT NOCOPY NUMBER
209 , x_return_status OUT NOCOPY VARCHAR2
210 , x_msg_count OUT NOCOPY NUMBER
211 , x_msg_data OUT NOCOPY VARCHAR2
212 , p_demand_source_type_id IN NUMBER := null
213 , p_demand_source_header_id IN NUMBER := null
214 , p_demand_source_line_id IN NUMBER := null
215 );
216
217 --
218 -- --------------------------------------------------------------------------
219 -- What does it do:
220 -- Sees if the passed serial number exists in our memory structure,
221 -- g_output_serial_rows.
222 -- If found, x_found = TRUE, else FALSE.
223 -- --------------------------------------------------------------------------
224 PROCEDURE search_serial_numbers
225 ( p_inventory_item_id IN NUMBER
226 , p_organization_id IN NUMBER
227 , p_serial_number IN VARCHAR2
228 , x_found OUT NOCOPY BOOLEAN
229 , x_return_status OUT NOCOPY VARCHAR2
230 , x_msg_count OUT NOCOPY NUMBER
231 , x_msg_data OUT NOCOPY VARCHAR2
232 );
233 --
234 --
235 -- --------------------------------------------------------------------------
236 -- What does it do:
237 -- Adds the passed serial number to our memory structure for storing
238 -- detailed serial numbers.
239 -- --------------------------------------------------------------------------
240 PROCEDURE add_serial_number(
241 p_inventory_item_id IN NUMBER
242 ,p_organization_id IN NUMBER
243 ,p_serial_number IN VARCHAR2
244 ,x_serial_index OUT NOCOPY NUMBER
245 );
246
247 -- --------------------------------------------------------------------------
248 -- What does it do:
249 -- Locks the serial number passed in to keep it from being used by
250 -- other concurrent processes.
251 -- --------------------------------------------------------------------------
252 FUNCTION lock_serial_number(
253 p_inventory_item_id IN NUMBER
254 ,p_serial_number IN VARCHAR2
255 ) RETURN BOOLEAN;
256
257 -- *****************************************************************************
258 -- * Output generation
259 -- *****************************************************************************
260 --
261 -- Description
262 -- Initialize the output table to empty
263 PROCEDURE init_output_process_tbl;
264 --
265 -- Description
266 -- add a output record to the output table
267 PROCEDURE add_output ( p_output_process_rec IN g_output_process_rec_type);
268 --
269 -- Description
270 -- generate the suggestion records in transaction temporary tables
271 PROCEDURE process_output
272 (x_return_status OUT NOCOPY VARCHAR2,
273 p_request_line_rec IN g_request_line_rec_type,
274 p_request_context IN g_request_context_rec_type,
275 p_plan_tasks IN BOOLEAN DEFAULT FALSE
276 );
277 --
278 -- *****************************************************************************
279 -- * Some Useful Helpers
280 -- *****************************************************************************
281 -- compute the levels to start detailing using the transaction
282 -- request info and reservations
283 -- Added x_remaining_quantity as part of the bug fix for 2286454
284 PROCEDURE compute_pick_detail_level
285 ( x_return_status OUT NOCOPY VARCHAR2
286 , p_request_line_rec IN g_request_line_rec_type
287 , p_request_context IN g_request_context_rec_type
288 , p_reservations IN inv_reservation_global.mtl_reservation_tbl_type
289 , x_detail_level_tbl IN OUT NOCOPY g_detail_level_tbl_type
290 , x_detail_level_tbl_size OUT NOCOPY NUMBER
291 , x_remaining_quantity OUT NOCOPY NUMBER
292 );
293 -- Overloaded version for multi-UOM
294 PROCEDURE compute_pick_detail_level
295 ( x_return_status OUT NOCOPY VARCHAR2
296 , p_request_line_rec IN g_request_line_rec_type
297 , p_request_context IN g_request_context_rec_type
298 , p_reservations IN inv_reservation_global.mtl_reservation_tbl_type
299 , x_detail_level_tbl IN OUT NOCOPY g_detail_level_tbl_type
300 , x_detail_level_tbl_size OUT NOCOPY NUMBER
301 , x_remaining_quantity OUT NOCOPY NUMBER
302 , x_remaining_sec_qty OUT NOCOPY NUMBER
303 );
304 --
305 -- Name : split_prefix_num
306 -- Function : Separates prefix and numeric part of a serial number
307 -- Pre-reqs : none
308 -- Parameters :
309 -- p_serial_number in varchar2
310 -- p_prefix in/out varchar2 the prefix
311 -- x_num out varchar2(30) the numeric portion
312 -- Notes : privat procedure for internal use only
313 -- needed only once serial numbers are supported
314 --
315 PROCEDURE split_prefix_num
316 ( p_serial_number IN VARCHAR2
317 ,p_prefix IN OUT NOCOPY VARCHAR2
318 ,x_num OUT NOCOPY VARCHAR2
319 );
320 --
321 -- Subtract two serial numbers and return the difference
322 FUNCTION subtract_serials
323 (p_operand1 IN VARCHAR2,
324 p_operand2 IN VARCHAR2
325 ) RETURN NUMBER;
326 --
327 FUNCTION get_lot_expiration_date
328 (p_organization_id IN NUMBER,
329 p_inventory_item_id IN NUMBER,
330 p_lot_number IN VARCHAR2)
331 RETURN DATE;
332
333 FUNCTION is_sub_loc_lot_trx_allowed(
334 p_transaction_type_id IN NUMBER
335 ,p_organization_id IN NUMBER
336 ,p_inventory_item_id IN NUMBER
337 ,p_subinventory_code IN VARCHAR2
338 ,p_locator_id IN NUMBER
339 ,p_lot_number IN VARCHAR2
340 ) RETURN VARCHAR2;
341
342 FUNCTION is_serial_trx_allowed(
343 p_transaction_type_id IN NUMBER
344 ,p_organization_id IN NUMBER
345 ,p_inventory_item_id IN NUMBER
346 ,p_serial_status IN NUMBER
347 ) RETURN VARCHAR2;
348
349 PROCEDURE build_sql (
350 x_return_status OUT NOCOPY VARCHAR2
351 ,x_sql_statement OUT NOCOPY LONG);
352 FUNCTION is_sub_loc_lot_reservable(
353 p_organization_id IN NUMBER
354 ,p_inventory_item_id IN NUMBER
355 ,p_subinventory_code IN VARCHAR2
356 ,p_locator_id IN NUMBER
357 ,p_lot_number IN VARCHAR2
358 ) RETURN BOOLEAN;
359 FUNCTION get_organization_code(
360 p_organization_id IN NUMBER
361 )
362 Return VARCHAR2;
363
364 -- LPN Status Project
365 FUNCTION is_onhand_status_trx_allowed(
366 p_transaction_type_id IN NUMBER
367 ,p_organization_id IN NUMBER
368 ,p_inventory_item_id IN NUMBER
369 ,p_subinventory_code IN VARCHAR2
370 ,p_locator_id IN NUMBER
371 ,p_lot_number IN VARCHAR2
372 ,p_lpn_id IN NUMBER
373 ) RETURN VARCHAR2;
374 -- LPN Status Project
375
376 PROCEDURE set_mo_transact_date (
377 p_date IN DATE);
378
379 PROCEDURE clear_mo_transact_date;
380
381 END inv_detail_util_pvt;
382