1 PACKAGE inv_detail_util_pvt AS
2 /* $Header: INVVDEUS.pls 120.4.12010000.2 2008/11/10 21:37:15 musinha 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 );
176 --
177 -- *****************************************************************************
178 -- * Serial Number Detailing Related Procedures
179 -- *****************************************************************************
180 --
181 -- Description
182 -- Initialize the internal table that stores the serial numbers detailed
183 -- to empty
184 PROCEDURE init_output_serial_rows;
185 -- --------------------------------------------------------------------------
186 -- What does it do:
187 -- Given the item/organization, inventory controls, quantity for a autodetailed
188 -- row and also from/to serial number range info,
189 -- it fetches and populates available serial numbers into g_output_serial_rows.
190 -- --------------------------------------------------------------------------
191 --
192 PROCEDURE get_serial_numbers (
193 p_inventory_item_id IN NUMBER
194 , p_organization_id IN NUMBER
195 , p_revision IN VARCHAR2
196 , p_lot_number IN VARCHAR2
197 , p_subinventory_code IN VARCHAR2
198 , p_locator_id IN NUMBER
199 , p_required_sl_qty IN NUMBER
200 , p_from_range IN VARCHAR2
201 , p_to_range IN VARCHAR2
202 , p_unit_number IN VARCHAR2
203 , p_detail_any_serial IN NUMBER
204 , p_cost_group_id IN NUMBER
205 , p_transaction_type_id IN NUMBER
206 , x_available_sl_qty OUT NOCOPY NUMBER
207 , x_serial_index OUT NOCOPY NUMBER
208 , x_return_status OUT NOCOPY VARCHAR2
209 , x_msg_count OUT NOCOPY NUMBER
210 , x_msg_data OUT NOCOPY VARCHAR2
211 , p_demand_source_type_id IN NUMBER := null
212 , p_demand_source_header_id IN NUMBER := null
213 , p_demand_source_line_id IN NUMBER := null
214 );
215
216 --
217 -- --------------------------------------------------------------------------
218 -- What does it do:
219 -- Sees if the passed serial number exists in our memory structure,
220 -- g_output_serial_rows.
221 -- If found, x_found = TRUE, else FALSE.
222 -- --------------------------------------------------------------------------
223 PROCEDURE search_serial_numbers
224 ( p_inventory_item_id IN NUMBER
225 , p_organization_id IN NUMBER
226 , p_serial_number IN VARCHAR2
227 , x_found OUT NOCOPY BOOLEAN
228 , x_return_status OUT NOCOPY VARCHAR2
229 , x_msg_count OUT NOCOPY NUMBER
230 , x_msg_data OUT NOCOPY VARCHAR2
231 );
232 --
233 --
234 -- --------------------------------------------------------------------------
235 -- What does it do:
236 -- Adds the passed serial number to our memory structure for storing
237 -- detailed serial numbers.
238 -- --------------------------------------------------------------------------
239 PROCEDURE add_serial_number(
240 p_inventory_item_id IN NUMBER
241 ,p_organization_id IN NUMBER
242 ,p_serial_number IN VARCHAR2
243 ,x_serial_index OUT NOCOPY NUMBER
244 );
245
246 -- --------------------------------------------------------------------------
247 -- What does it do:
248 -- Locks the serial number passed in to keep it from being used by
249 -- other concurrent processes.
250 -- --------------------------------------------------------------------------
251 FUNCTION lock_serial_number(
252 p_inventory_item_id IN NUMBER
253 ,p_serial_number IN VARCHAR2
254 ) RETURN BOOLEAN;
255
256 -- *****************************************************************************
257 -- * Output generation
258 -- *****************************************************************************
259 --
260 -- Description
261 -- Initialize the output table to empty
262 PROCEDURE init_output_process_tbl;
263 --
264 -- Description
265 -- add a output record to the output table
266 PROCEDURE add_output ( p_output_process_rec IN g_output_process_rec_type);
267 --
268 -- Description
269 -- generate the suggestion records in transaction temporary tables
270 PROCEDURE process_output
271 (x_return_status OUT NOCOPY VARCHAR2,
272 p_request_line_rec IN g_request_line_rec_type,
273 p_request_context IN g_request_context_rec_type,
274 p_plan_tasks IN BOOLEAN DEFAULT FALSE
275 );
276 --
277 -- *****************************************************************************
278 -- * Some Useful Helpers
279 -- *****************************************************************************
280 -- compute the levels to start detailing using the transaction
281 -- request info and reservations
282 -- Added x_remaining_quantity as part of the bug fix for 2286454
283 PROCEDURE compute_pick_detail_level
284 (x_return_status OUT NOCOPY VARCHAR2,
285 p_request_line_rec IN g_request_line_rec_type,
286 p_request_context IN g_request_context_rec_type,
287 p_reservations IN inv_reservation_global.mtl_reservation_tbl_type,
288 x_detail_level_tbl IN OUT nocopy g_detail_level_tbl_type,
289 x_detail_level_tbl_size OUT NOCOPY NUMBER ,
290 x_remaining_quantity OUT NOCOPY NUMBER
291 );
292 --
293 -- Name : split_prefix_num
294 -- Function : Separates prefix and numeric part of a serial number
295 -- Pre-reqs : none
296 -- Parameters :
297 -- p_serial_number in varchar2
298 -- p_prefix in/out varchar2 the prefix
299 -- x_num out varchar2(30) the numeric portion
300 -- Notes : privat procedure for internal use only
301 -- needed only once serial numbers are supported
302 --
303 PROCEDURE split_prefix_num
304 ( p_serial_number IN VARCHAR2
305 ,p_prefix IN OUT NOCOPY VARCHAR2
306 ,x_num OUT NOCOPY VARCHAR2
307 );
308 --
309 -- Subtract two serial numbers and return the difference
310 FUNCTION subtract_serials
311 (p_operand1 IN VARCHAR2,
312 p_operand2 IN VARCHAR2
313 ) RETURN NUMBER;
314 --
315 FUNCTION get_lot_expiration_date
316 (p_organization_id IN NUMBER,
317 p_inventory_item_id IN NUMBER,
318 p_lot_number IN VARCHAR2)
319 RETURN DATE;
320
321 FUNCTION is_sub_loc_lot_trx_allowed(
322 p_transaction_type_id IN NUMBER
323 ,p_organization_id IN NUMBER
324 ,p_inventory_item_id IN NUMBER
325 ,p_subinventory_code IN VARCHAR2
326 ,p_locator_id IN NUMBER
330 FUNCTION is_serial_trx_allowed(
327 ,p_lot_number IN VARCHAR2
328 ) RETURN VARCHAR2;
329
331 p_transaction_type_id IN NUMBER
332 ,p_organization_id IN NUMBER
333 ,p_inventory_item_id IN NUMBER
334 ,p_serial_status IN NUMBER
335 ) RETURN VARCHAR2;
336
337 PROCEDURE build_sql (
338 x_return_status OUT NOCOPY VARCHAR2
339 ,x_sql_statement OUT NOCOPY LONG);
340 FUNCTION is_sub_loc_lot_reservable(
341 p_organization_id IN NUMBER
342 ,p_inventory_item_id IN NUMBER
343 ,p_subinventory_code IN VARCHAR2
344 ,p_locator_id IN NUMBER
345 ,p_lot_number IN VARCHAR2
346 ) RETURN BOOLEAN;
347 FUNCTION get_organization_code(
348 p_organization_id IN NUMBER
349 )
350 Return VARCHAR2;
351
352 -- LPN Status Project
353 FUNCTION is_onhand_status_trx_allowed(
354 p_transaction_type_id IN NUMBER
355 ,p_organization_id IN NUMBER
356 ,p_inventory_item_id IN NUMBER
357 ,p_subinventory_code IN VARCHAR2
358 ,p_locator_id IN NUMBER
359 ,p_lot_number IN VARCHAR2
360 ,p_lpn_id IN NUMBER
361 ) RETURN VARCHAR2;
362 -- LPN Status Project
363
364 PROCEDURE set_mo_transact_date (
365 p_date IN DATE);
366
367 PROCEDURE clear_mo_transact_date;
368
369 END inv_detail_util_pvt;
370
[Home] [Help]