[Home] [Help]
PACKAGE BODY: APPS.INV_REPLENISH_COUNT_LOVS
Source
1 PACKAGE BODY inv_replenish_count_lovs AS
2 /* $Header: INVRPCLB.pls 120.2.12010000.2 2008/07/29 12:54:49 ptkumar ship $ */
3
4 /** Package : INV_REPLENISH_COUNT_LOVS
5 * File : INVRPCLB.pls
6 * Content :
7 * Description :
8 * Notes :
9 * Modified : Mon Aug 25 12:17:54 GMT+05:30 2003
10 *
11 * Body of package inv_replenish_count_lovs
12 * This file contains Replenishment Count LOVS being used by the
13 * mobile WMS/INV applications. It is being called from java
14 * LOV beans to populate the LOV.
15 *
16 **/
17
18 /**
19 * Global constant holding the package name
20 **/
21 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_REPLENISH_COUNT_LOVS';
22 g_version_printed BOOLEAN := FALSE;
23 g_user_name fnd_user.user_name%TYPE := fnd_global.user_name;
24 g_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
25
26 /**
27 * This Procedure is used to print the Debug Messages to log file.
28 * @param p_message Debug Message
29 * @param p_module Module
30 * @param p_level Debug Level
31 **/
32 PROCEDURE print_debug(
33 p_message IN VARCHAR2
34 , p_module IN VARCHAR2
35 , p_level IN NUMBER) IS
36 BEGIN
37 IF NOT g_version_printed THEN
38 inv_log_util.trace('$Header: INVRPCLB.pls 120.2.12010000.2 2008/07/29 12:54:49 ptkumar ship $', g_pkg_name|| '.' || p_module, 1);
39 g_version_printed := TRUE;
40 END IF;
41 inv_log_util.TRACE(g_user_name || ': ' || p_message, g_pkg_name || '.' || p_module, p_level);
42 EXCEPTION
43 WHEN OTHERS THEN
44 NULL;
45 END print_debug;
46
47 /**
48 * This procedure returns valid Replenishment Count Names for counting in mobile.
49 * @param x_replenish_count_lov Returns LOV rows as a reference cursor
50 * @param p_replenish_count Restricts LOV SQL to the user input Count Name
51 * @param p_organization_id Organization ID
52 * @param p_subinventory Subinventory Code
53 **/
54 PROCEDURE get_replenish_count_lov(
55 x_replenish_count_lov OUT NOCOPY t_genref
56 , p_replenish_count IN VARCHAR2
57 , p_organization_id IN NUMBER
58 , p_subinventory IN VARCHAR2
59 ) IS
60 l_proc CONSTANT VARCHAR2(30) := 'GET_REPLENISH_COUNT_LOV';
61 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
62 BEGIN
63 IF l_trace_on = 1 THEN
64 print_debug('The input parameters are: '
65 || fnd_global.local_chr(10)
66 || ' p_replenish_count: '
67 || p_replenish_count
68 || fnd_global.local_chr(10)
69 || ', p_organization_id: '
70 || p_organization_id
71 || ', p_subinventory: '
72 || p_subinventory
73 || fnd_global.local_chr(10)
74 , l_proc
75 , 9
76 );
77 END IF;
78
79 OPEN x_replenish_count_lov FOR
80 SELECT mrh.replenishment_count_name
81 , mrh.replenishment_header_id
82 , mrh.subinventory_code
83 , mrh.count_date
84 , mrh.supply_cutoff_date
85 , mrh.requisition_approval_type
86 , mrh.process_status
87 , mrh.process_mode
88 , mrh.count_mode
89 , mrh.error_flag
90 , mrh.request_id
91 , mrh.delivery_location_id
92 , mrh.default_line_items
93 , mrh.default_count_type_code
94 , ml.meaning default_count_type
95 FROM mtl_replenish_headers mrh
96 , mtl_secondary_inventories msi
97 , mfg_lookups ml
98 WHERE mrh.organization_id = p_organization_id
99 AND mrh.subinventory_code = NVL(p_subinventory, mrh.subinventory_code)
100 AND msi.secondary_inventory_name = mrh.subinventory_code
101 AND msi.organization_id = mrh.organization_id
102 AND mrh.process_status = 1
103 AND mrh.count_mode = 1
104 AND ml.lookup_type = 'MTL_COUNT_TYPES'
105 AND mrh.default_count_type_code = ml.lookup_code
106 AND mrh.replenishment_count_name LIKE p_replenish_count
107 AND((msi.planning_level = 1
108 AND NOT EXISTS(SELECT 1
109 FROM mtl_replenish_lines mrl
110 WHERE mrl.replenishment_header_id = mrh.replenishment_header_id
111 AND mrl.locator_id IS NULL
112 )
113 )
114 OR(msi.planning_level = 2
115 AND NOT EXISTS(SELECT 1
116 FROM mtl_replenish_lines mrl
117 WHERE mrl.replenishment_header_id = mrh.replenishment_header_id
118 AND mrl.locator_id IS NOT NULL
119 )
120 )
121 )
122 ORDER BY mrh.replenishment_count_name;
123 END get_replenish_count_lov;
124
125 /**
126 * This procedure returns valid Subinventories which have atleast one Min Max planned or
127 * PAR level planned item defined in the Item subinventories form.
128 * @param x_replenish_count_subs_lov Returns LOV rows as a reference cursor
129 * @param p_subinventory Subinventory Code
130 * @param p_organization_id Organization ID
131 **/
132 PROCEDURE get_replenish_count_subs_lov(
133 x_replenish_count_subs_lov OUT NOCOPY t_genref
134 , p_subinventory IN VARCHAR2
135 , p_organization_id IN NUMBER) IS
136
137 l_proc CONSTANT VARCHAR2(30) := 'GET_REPLENISH_COUNT_SUBS_LOV';
138 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
139 BEGIN
140 IF l_trace_on = 1 THEN
141 print_debug(
142 'The input parameters are: '
143 || fnd_global.local_chr(10)
144 || ', p_subinventory: '
145 || p_subinventory
146 || fnd_global.local_chr(10)
147 || ', p_organization_id: '
148 || p_organization_id
149 , l_proc
150 , 9
151 );
152 END IF;
153
154 OPEN x_replenish_count_subs_lov FOR
155 SELECT msi.secondary_inventory_name
156 , msi.locator_type
157 , msi.description
158 , msi.asset_inventory
159 , msi.quantity_tracked
160 , msi.planning_level
161 , msi.enable_locator_alias
162 FROM mtl_secondary_inventories msi
163 WHERE msi.organization_id = p_organization_id
164 AND secondary_inventory_name LIKE p_subinventory
165 AND TRUNC(NVL(disable_date, SYSDATE + 1)) > TRUNC(SYSDATE)
166 AND NVL(subinventory_type, 1) = 1
167 AND(( msi.planning_level = 2
168 AND EXISTS(SELECT 1
169 FROM mtl_item_sub_inventories mis
170 WHERE mis.organization_id = msi.organization_id
171 AND mis.secondary_inventory = msi.secondary_inventory_name
172 )
173 )
174 OR( msi.planning_level = 1
175 AND EXISTS(SELECT 1
176 FROM mtl_secondary_locators msl
177 WHERE msl.organization_id = msi.organization_id
178 AND msl.subinventory_code = msi.secondary_inventory_name
179 )
180 )
181 )
182 AND EXISTS(SELECT 1
183 FROM mtl_replenish_headers mrh
184 WHERE mrh.subinventory_code = msi.secondary_inventory_name
185 AND mrh.organization_id= msi.organization_id
186 AND mrh.count_mode = 1
187 AND mrh.process_status = 1
188 )
189 ORDER BY secondary_inventory_name;
190 END get_replenish_count_subs_lov;
191
192 /**
193 * This procedure returns all the valid locators belonging to the Replenishment Count Header Id passed.
194 * @param x_replenish_count_locator_kff Returns LOV rows as a reference cursor
195 * @param p_locator Restricts LOV SQL to this user input Locator
196 * @param p_replenish_header_id Replenishment Count Header ID
197 * @param p_organization_id Organization ID
198 * @param p_subinventory Subinventory Code
199 * @param p_qty_tracked Quantity Tracked Subinventory
200 **/
201 PROCEDURE get_replenish_count_locs_kff(
202 x_replenish_count_locator_kff OUT NOCOPY t_genref
203 , p_locator IN VARCHAR2
204 , p_replenish_header_id IN NUMBER
205 , p_organization_id IN NUMBER
206 , p_subinventory IN VARCHAR2
207 , p_qty_tracked IN NUMBER
208 ) IS
209 l_proc CONSTANT VARCHAR2(30) := 'GET_REPLENISH_COUNT_LOCS_KFF';
210 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
211 BEGIN
212 IF l_trace_on = 1 THEN
213 print_debug(
214 'The input parameters are: '
215 || fnd_global.local_chr(10)
216 || ' p_locator : '
217 || p_locator
218 || fnd_global.local_chr(10)
219 || ', p_replenish_header_id : '
220 || p_replenish_header_id
221 || fnd_global.local_chr(10)
222 || ', p_organization_id : '
223 || p_organization_id
224 || fnd_global.local_chr(10)
225 || ', p_subinventory : '
226 || p_subinventory
227 , l_proc
228 , 9
229 );
230 END IF;
231
232 OPEN x_replenish_count_locator_kff FOR
233 SELECT milk.inventory_location_id
234 , INV_PROJECT.GET_LOCATOR(milk.inventory_location_id, milk.organization_id) -- Bug 6798138
235 , milk.description
236 FROM mtl_item_locations_kfv milk
237 WHERE milk.organization_id = p_organization_id
238 AND milk.subinventory_code = p_subinventory
239 -- AND milk.inventory_location_id = NVL(milk.physical_location_id, milk.inventory_location_id) -- Commented for Bug 6798138
240 AND NVL(milk.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
241 AND milk.concatenated_segments LIKE(p_locator||'%')
242 AND EXISTS( SELECT 1
243 FROM mtl_secondary_locators msl
244 , mtl_replenish_lines mrl
245 WHERE msl.secondary_locator = milk.inventory_location_id
246 AND msl.organization_id = milk.organization_id
247 AND mrl.replenishment_header_id = p_replenish_header_id
248 AND mrl.locator_id = msl.secondary_locator
249 AND mrl.inventory_item_id = msl.inventory_item_id
250 AND mrl.organization_id = msl.organization_id
251 AND mrl.count_quantity IS NULL
252 AND mrl.error_flag IS NULL
253 AND (mrl.count_type_code IS NULL
254 OR mrl.count_type_code = 2
255 OR (mrl.count_type_code = 1 AND p_qty_tracked = 2 AND msl.maximum_quantity IS NOT NULL)
256 )
257 )
258 ORDER BY milk.picking_order, milk.concatenated_segments;
259 END get_replenish_count_locs_kff;
260
261 PROCEDURE get_replenish_count_locs_kff(
262 x_replenish_count_locator_kff OUT NOCOPY t_genref
263 , p_locator IN VARCHAR2
264 , p_replenish_header_id IN NUMBER
265 , p_organization_id IN NUMBER
266 , p_subinventory IN VARCHAR2
267 , p_qty_tracked IN NUMBER
268 , p_alias IN VARCHAR2
269 ) IS
270 l_proc CONSTANT VARCHAR2(30) := 'GET_REPLENISH_COUNT_LOCS_KFF';
271 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
272 BEGIN
273
274 IF p_alias IS NULL THEN
275 get_replenish_count_locs_kff(
276 x_replenish_count_locator_kff => x_replenish_count_locator_kff
277 , p_locator => p_locator
278 , p_replenish_header_id => p_replenish_header_id
279 , p_organization_id => p_organization_id
280 , p_subinventory => p_subinventory
281 , p_qty_tracked => p_qty_tracked
282 );
283 RETURN;
284 END IF;
285 IF l_trace_on = 1 THEN
286 print_debug(
287 'The input parameters are: '
288 || fnd_global.local_chr(10)
289 || ' p_locator : '
290 || p_locator
291 || fnd_global.local_chr(10)
292 || ', p_replenish_header_id : '
293 || p_replenish_header_id
294 || fnd_global.local_chr(10)
295 || ', p_organization_id : '
296 || p_organization_id
297 || fnd_global.local_chr(10)
298 || ', p_subinventory : '
299 || p_subinventory
300 || fnd_global.local_chr(10)
301 || ', p_alias : '
302 || p_alias
303 , l_proc
304 , 9
305 );
306 END IF;
307
308 OPEN x_replenish_count_locator_kff FOR
309 SELECT milk.inventory_location_id
310 , INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id)
311 , milk.description
312 FROM mtl_item_locations_kfv milk
313 WHERE milk.organization_id = p_organization_id
314 AND milk.subinventory_code = p_subinventory
315 AND milk.inventory_location_id = NVL(milk.physical_location_id, milk.inventory_location_id)
316 AND NVL(milk.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
317 AND milk.alias = p_alias
318 AND EXISTS( SELECT 1
319 FROM mtl_secondary_locators msl
320 , mtl_replenish_lines mrl
321 WHERE msl.secondary_locator = milk.inventory_location_id
322 AND msl.organization_id = milk.organization_id
323 AND mrl.replenishment_header_id = p_replenish_header_id
324 AND mrl.locator_id = msl.secondary_locator
325 AND mrl.inventory_item_id = msl.inventory_item_id
326 AND mrl.organization_id = msl.organization_id
327 AND mrl.count_quantity IS NULL
328 AND mrl.error_flag IS NULL
329 AND (mrl.count_type_code IS NULL
330 OR mrl.count_type_code = 2
331 OR (mrl.count_type_code = 1 AND p_qty_tracked = 2 AND msl.maximum_quantity IS NOT NULL)
332 )
333 )
334 ORDER BY milk.picking_order, milk.concatenated_segments;
335 END get_replenish_count_locs_kff;
336
337 /**
338 * This procedure returns all the valid items belonging to the Replenishment Count Header Id passed.
339 * @param x_replenish_count_items_lov Returns LOV rows as a reference cursor
340 * @param p_item Restricts LOV SQL to this user input Item
341 * @param p_replenish_header_id Replenishment Count Header ID
342 * @param p_organization_id Organization ID
343 * @param p_subinventory Subinventory Code
344 * @param p_locator_id Locator Id
345 * @param p_qty_tracked Quantity Tracked Subinventory
346 **/
347 PROCEDURE get_replenish_count_items_lov(
348 x_replenish_count_items_lov OUT NOCOPY t_genref
349 , p_item IN VARCHAR2
350 , p_replenish_header_id IN NUMBER
351 , p_organization_id IN NUMBER
352 , p_subinventory IN VARCHAR2
353 , p_locator_id IN NUMBER
354 , p_qty_tracked IN NUMBER
355 ) IS
356 l_proc CONSTANT VARCHAR2(30) := 'GET_REPLENISH_COUNT_ITEMS_LOV';
357 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
358 BEGIN
359 IF l_trace_on = 1 THEN
360 print_debug(
361 'The input parameters are: '
362 || fnd_global.local_chr(10)
363 || ' p_item : '
364 || p_item
365 || fnd_global.local_chr(10)
366 || ', p_organization_id: '
367 || p_organization_id
368 || fnd_global.local_chr(10)
369 || ', p_subinventory: '
370 || p_subinventory
371 || fnd_global.local_chr(10)
372 || ', p_locator_id : '
373 || p_locator_id
374 , l_proc
375 , 9
376 );
377 END IF;
378
379 OPEN x_replenish_count_items_lov FOR
380 SELECT concatenated_segments
381 , msik.inventory_item_id
382 , msik.description
383 , NVL(revision_qty_control_code, 1)
384 , NVL(lot_control_code, 1)
385 , NVL(serial_number_control_code, 1)
386 , NVL(restrict_subinventories_code, 2)
387 , NVL(restrict_locators_code, 2)
388 , NVL(location_control_code, 1)
389 , primary_uom_code
390 , NVL(inspection_required_flag, 'N')
391 , NVL(shelf_life_code, 1)
392 , NVL(shelf_life_days, 0)
393 , NVL(allowed_units_lookup_code, 2)
394 , NVL(effectivity_control, 1)
395 , 0
396 , 0
397 , NVL(default_serial_status_id, 1)
398 , NVL(serial_status_enabled, 'N')
399 , NVL(default_lot_status_id, 0)
400 , NVL(lot_status_enabled, 'N')
401 , ''
402 , 'N'
403 , inventory_item_flag
404 , 0,
405 -- , inventory_asset_flag, '',
406 --Additional Fields for Process Convergence, INVCONV , NSRIVAST
407 NVL(GRADE_CONTROL_FLAG,'N'),
408 NVL(DEFAULT_GRADE,''),
409 NVL(EXPIRATION_ACTION_INTERVAL,0),
410 NVL(EXPIRATION_ACTION_CODE,''),
411 NVL(HOLD_DAYS,0),
412 NVL(MATURITY_DAYS,0),
413 NVL(RETEST_INTERVAL,0),
414 NVL(COPY_LOT_ATTRIBUTE_FLAG,'N'),
415 NVL(CHILD_LOT_FLAG,'N'),
416 NVL(CHILD_LOT_VALIDATION_FLAG,'N'),
417 NVL(LOT_DIVISIBLE_FLAG,'Y'),
418 NVL(SECONDARY_UOM_CODE,''),
419 NVL(SECONDARY_DEFAULT_IND,''),
420 NVL(TRACKING_QUANTITY_IND,'P'),
421 NVL(DUAL_UOM_DEVIATION_HIGH,0),
422 NVL(DUAL_UOM_DEVIATION_LOW,0)
423 -- INVCONV , NSRIVAST, END
424 FROM mtl_system_items_kfv msik
425 WHERE msik.organization_id = p_organization_id
426 AND msik.inventory_item_flag = 'Y'
427 AND msik.stock_enabled_flag = 'Y'
428 AND msik.concatenated_segments LIKE p_item
429 AND ((p_locator_id IS NULL
430 AND EXISTS(SELECT 1
431 FROM mtl_item_sub_inventories mis
432 , mtl_replenish_lines mrl
433 WHERE mis.organization_id = msik.organization_id
434 AND mis.secondary_inventory = p_subinventory
435 AND mis.inventory_item_id = msik.inventory_item_id
436 AND mrl.replenishment_header_id = p_replenish_header_id
437 AND mrl.inventory_item_id = mis.inventory_item_id
438 AND mrl.count_quantity IS NULL
439 AND mrl.error_flag IS NULL
440 AND (mrl.count_type_code = 2
441 OR (mrl.count_type_code = 1 AND p_qty_tracked = 2 AND mis.inventory_planning_code = 2)
442 )
443 )
444 )
445 OR (p_locator_id IS NOT NULL
446 AND EXISTS(SELECT 1
447 FROM mtl_secondary_locators msl
448 , mtl_replenish_lines mrl
449 WHERE msl.secondary_locator = p_locator_id
450 AND msl.inventory_item_id = msik.inventory_item_id
451 AND msl.organization_id = msik.organization_id
452 AND mrl.replenishment_header_id = p_replenish_header_id
453 AND mrl.locator_id = msl.secondary_locator
454 AND mrl.inventory_item_id = msl.inventory_item_id
455 AND mrl.count_quantity IS NULL
456 AND mrl.error_flag IS NULL
457 AND (mrl.count_type_code = 2
458 OR (mrl.count_type_code = 1 AND p_qty_tracked = 2 AND msl.maximum_quantity IS NOT NULL)
459 )
460 )
461 )
462 )
463 ORDER BY concatenated_segments;
464 END get_replenish_count_items_lov;
465
466 /**
467 * This procedure returns the Replenishment Count Types allowed for the passed in
468 * input combination.
469 * @param x_replenish_count_types_lov Returns LOV rows as a reference cursor
470 * @param p_count_type Count Type
471 * @param p_qty_tracked Quantity Tracked Subinventory
472 * @param p_inventory_planning_level Planning Level of the Subinventory
473 * @param p_par_level PAR level for the Locator Item
474 **/
475 /**---------------------------------------------------------------------
476 * Parameters value passed
477 * ---------------------------------------------------------------------
478 * p_quantity_tracked 1(Check) 1 2 2
479 *
480 * p_inventory_planning_level 1(PAR) 2(Sub) 1 2
481 *
482 * p_par_level - NULL - NOT NULL
483 * ---------------------------------------------------------------------
484 * Count Types Returned Order Qty Order Qty Onhand Qty Onhand Qty
485 * Order Qty Order Qty
486 *----------------------------------------------------------------------
487 **/
488 PROCEDURE get_replenish_count_types_lov(
489 x_replenish_count_types_lov OUT NOCOPY t_genref
490 , p_count_type IN VARCHAR2
491 , p_qty_tracked IN NUMBER
492 , p_inventory_planning_level IN NUMBER
493 , p_par_level IN NUMBER
494 ) IS
495 l_proc CONSTANT VARCHAR2(30) := 'GET_REPLENISH_COUNT_TYPES_LOV';
496 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
497 BEGIN
498 IF l_trace_on = 1 THEN
499 print_debug(
500 'The input parameters are: '
501 || fnd_global.local_chr(10)
502 || ' p_count_type : '
503 || p_count_type
504 || fnd_global.local_chr(10)
505 || ', p_qty_tracked : '
506 || p_qty_tracked
507 || fnd_global.local_chr(10)
508 || ', p_inventory_planning_level : '
509 || p_inventory_planning_level
510 || fnd_global.local_chr(10)
511 || ', p_par_level : '
512 || p_par_level
513 , l_proc
514 , 9
515 );
516 END IF;
517
518 IF (p_qty_tracked = 2 AND p_inventory_planning_level = 2 )
519 OR (p_qty_tracked = 2 AND p_inventory_planning_level = 1 AND p_par_level IS NOT NULL)THEN
520 OPEN x_replenish_count_types_lov FOR
521 SELECT lookup_code
522 , meaning
523 FROM mfg_lookups
524 WHERE lookup_type = 'MTL_COUNT_TYPES'
525 AND lookup_code IN (1,2)
526 AND enabled_flag = 'Y'
527 AND meaning LIKE p_count_type
528 AND TRUNC(NVL(end_date_active, SYSDATE + 1)) > TRUNC(SYSDATE)
529 AND TRUNC(NVL(start_date_active, SYSDATE - 1)) <= TRUNC(SYSDATE)
530 ORDER BY meaning;
531 ELSE
532 OPEN x_replenish_count_types_lov FOR
533 SELECT lookup_code
534 , meaning
535 FROM mfg_lookups
536 WHERE lookup_type = 'MTL_COUNT_TYPES'
537 AND lookup_code = 2
538 AND enabled_flag = 'Y'
539 AND meaning LIKE p_count_type
540 AND TRUNC(NVL(end_date_active, SYSDATE + 1)) > TRUNC(SYSDATE)
541 AND TRUNC(NVL(start_date_active, SYSDATE - 1)) <= TRUNC(SYSDATE)
542 ORDER BY meaning;
543 END IF;
544 END get_replenish_count_types_lov;
545 END inv_replenish_count_lovs;