[Home] [Help]
PACKAGE BODY: APPS.INV_REPLENISH_COUNT_PVT
Source
1 PACKAGE BODY inv_replenish_count_pvt AS
2 /* $Header: INVVRPCB.pls 120.1.12010000.2 2008/07/29 13:49:46 ptkumar ship $ */
3
4 /**
5 * Package : INV_REPLENISH_COUNT_PVT
6 * File : INVVRPCB.pls
7 * Content :
8 * Description :
9 * Notes :
10 * Modified : Mon Aug 25 12:17:54 GMT+05:30 2003
11 *
12 * Package Body for INV_REPLENISH_COUNT_PVT
13 * This file contains procedures and functions needed for
14 * Replenishment Count being used in the mobile WMS/INV applications.
15 * This package also includes APIs to process and report Count entries
16 * for a Replenishment Count.
17 **/
18
19 /**
20 * Globals constant holding the package name.
21 **/
22 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_REPLENISH_COUNT_PVT';
23 g_version_printed BOOLEAN := FALSE;
24 g_user_name fnd_user.user_name%TYPE := fnd_global.user_name;
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(p_message IN VARCHAR2, p_module IN VARCHAR2, p_level IN NUMBER) IS
33 BEGIN
34 IF NOT g_version_printed THEN
35 inv_log_util.TRACE('$Header: INVVRPCB.pls 120.1.12010000.2 2008/07/29 13:49:46 ptkumar ship $', g_pkg_name || '.' || p_module, 1);
36 g_version_printed := TRUE;
37 END IF;
38
39 inv_log_util.TRACE(g_user_name || ': ' || p_message, g_pkg_name || '.' || p_module, p_level);
40 EXCEPTION
41 WHEN OTHERS THEN
42 NULL;
43 END print_debug;
44
45 /**
46 * This Procedure is used to insert values into table mtl_replenish_lines.
47 * @param x_return_status Return Status
48 * @param x_msg_count Message Count
49 * @param x_msg_data Message Data
50 * @param p_organization_id Organization Id
51 * @param p_replenish_header_id Replenishment Count Header Id
52 * @param p_locator_id Locator Id
53 * @param p_item_id Item ID
54 * @param p_count_type_code Count Type Code
55 * @param p_count_quantity Count Quantity
56 * @param p_count_uom_code Count Uom Code
57 * @param p_primary_uom_code Primary Uom Code
58 * @param p_count_secondary_uom_code Secondary Uom Code<br>
59 * @param p_count_secondary_quantity Secondary Quantity<br>
60 **/
61 PROCEDURE insert_row(
62 x_return_status OUT NOCOPY VARCHAR2
63 , x_msg_count OUT NOCOPY NUMBER
64 , x_msg_data OUT NOCOPY VARCHAR2
65 , p_organization_id IN NUMBER
66 , p_replenish_header_id IN NUMBER
67 , p_locator_id IN NUMBER
68 , p_item_id IN NUMBER
69 , p_count_type_code IN NUMBER
70 , p_count_quantity IN NUMBER
71 , p_count_uom_code IN VARCHAR2
72 , p_primary_uom_code IN VARCHAR2
73 , p_count_secondary_uom_code IN VARCHAR2 -- INVCONV, NSRIVAST
74 , p_count_secondary_quantity IN NUMBER -- INVCONV, NSRIVAST
75 ) IS
76 l_proc CONSTANT VARCHAR2(30) := 'INSERT_ROW';
77 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
78 BEGIN
79 x_return_status := fnd_api.g_ret_sts_success;
80
81 IF l_trace_on = 1 THEN
82 print_debug(
83 'The input parameters are: '
84 || fnd_global.local_chr(10)
85 || ' p_organization_id : '
86 || p_organization_id
87 || fnd_global.local_chr(10)
88 || ' p_replenish_header_id : '
89 || p_replenish_header_id
90 || fnd_global.local_chr(10)
91 || ' p_locator_id : '
92 || p_locator_id
93 || fnd_global.local_chr(10)
94 || ' p_item_id : '
95 || p_item_id
96 || fnd_global.local_chr(10)
97 || ' p_count_type_code : '
98 || p_count_type_code
99 || fnd_global.local_chr(10)
100 || ' p_count_quantity : '
101 || p_count_quantity
102 || fnd_global.local_chr(10)
103 || ' p_count_uom_code : '
104 || p_count_uom_code
105 || fnd_global.local_chr(10)
106 || ' p_primary_uom_code : '
107 || p_primary_uom_code
108 || fnd_global.local_chr(10)
109 , l_proc
110 , 9
111 );
112 END IF;
113
114 fnd_message.set_name('INV', 'INV_RC_CREATED_FROM_MOBILE');
115
116 INSERT INTO mtl_replenish_lines
117 (
118 replenishment_line_id
119 , replenishment_header_id
120 , organization_id
121 , last_update_date
122 , last_updated_by
123 , creation_date
124 , created_by
125 , last_update_login
126 , locator_id
127 , inventory_item_id
128 , count_type_code
129 , count_quantity
130 , count_uom_code
131 , supply_quantity
132 , source_type
133 , source_organization_id
134 , source_subinventory
135 , reorder_quantity
136 , expense_account
137 , encumbrance_account
138 , REFERENCE
139 , error_flag
140 , primary_uom_count_quantity
141 , primary_uom_code
142 -- INCVONV, NSRIVAST
143 , secondary_uom_code
144 , secondary_uom_count_quantity
145 -- INCVONV, NSRIVAST
146 )
147 VALUES (
148 mtl_replenish_lines_s.NEXTVAL
149 , p_replenish_header_id
150 , p_organization_id
151 , SYSDATE
152 , fnd_global.user_id
153 , SYSDATE
154 , fnd_global.user_id
155 , fnd_global.login_id
156 , p_locator_id
157 , p_item_id
158 , p_count_type_code
159 , p_count_quantity
160 , p_count_uom_code
161 , NULL
162 , NULL
163 , NULL
164 , NULL
165 , NULL
166 , NULL
167 , NULL
168 , fnd_message.get
169 , NULL
170 , inv_convert.inv_um_convert(p_item_id, 6, p_count_quantity, p_count_uom_code, p_primary_uom_code, NULL, NULL)
171 , p_primary_uom_code
172 -- INCVONV, NSRIVAST
173 , p_count_secondary_uom_code
174 , p_count_secondary_quantity
175 -- INCVONV, NSRIVAST
176 );
177
178 IF (SQL%NOTFOUND) THEN
179 x_return_status := fnd_api.g_ret_sts_error;
180 ELSE
181 x_return_status := fnd_api.g_ret_sts_success;
182 END IF;
183 EXCEPTION
184 WHEN OTHERS THEN
185 x_return_status := fnd_api.g_ret_sts_error;
186 END insert_row;
187
188 /**
189 * This Procedure is used to update table mtl_replenish_lines.
190 * @param x_return_status Return Status
191 * @param x_msg_count Message Count
192 * @param x_msg_data Message Data
193 * @param p_item_id Item ID
194 * @param p_replenish_header_id Replenishment Count Header Id
195 * @param p_replenish_line_id Replenishment Count Line Id
196 * @param p_count_quantity Count Quantity
197 * @param p_primary_uom_code Primary Uom Code
198 * @param p_count_secondary_quantity Secondary Quantity<br>
199 **/
200 PROCEDURE update_row(
201 x_return_status OUT NOCOPY VARCHAR2
202 , x_msg_count OUT NOCOPY NUMBER
203 , x_msg_data OUT NOCOPY VARCHAR2
204 , p_item_id IN NUMBER
205 , p_replenish_header_id IN NUMBER
206 , p_replenish_line_id IN NUMBER
207 , p_count_quantity IN NUMBER
208 , p_count_uom_code IN VARCHAR2
209 , p_count_secondary_quantity IN NUMBER -- INVCONV, NSRIVAST
210 ) IS
211 l_proc CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
212 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
213 BEGIN
214 x_return_status := fnd_api.g_ret_sts_success;
215
216 IF l_trace_on = 1 THEN
217 print_debug(
218 'The input parameters are: '
219 || fnd_global.local_chr(10)
220 || ' p_replenish_header_id : '
221 || p_replenish_header_id
222 || fnd_global.local_chr(10)
223 || ' p_replenish_line_id : '
224 || p_replenish_line_id
225 || fnd_global.local_chr(10)
226 || ' p_count_quantity : '
227 || p_count_quantity
228 || fnd_global.local_chr(10)
229 || ' p_count_uom_code : '
230 || p_count_uom_code
231 || fnd_global.local_chr(10)
232 , l_proc
233 , 9
234 );
235 END IF;
236
237 -- Convert the count quantity into the item primary uom quantity.
238 UPDATE mtl_replenish_lines
239 SET last_update_date = SYSDATE
240 , last_updated_by = fnd_global.user_id
241 , last_update_login = fnd_global.login_id
242 , count_quantity = p_count_quantity
243 , count_uom_code = p_count_uom_code
244 , primary_uom_count_quantity =
245 inv_convert.inv_um_convert(p_item_id, 6, p_count_quantity, p_count_uom_code, primary_uom_code, NULL, NULL)
246 , secondary_uom_count_quantity = p_count_secondary_quantity -- INVCONV, NSRIVAST
247 WHERE replenishment_header_id = p_replenish_header_id
248 AND replenishment_line_id = p_replenish_line_id;
249
250 IF (SQL%NOTFOUND) THEN
251 x_return_status := fnd_api.g_ret_sts_error;
252 ELSE
253 x_return_status := fnd_api.g_ret_sts_success;
254 END IF;
255 EXCEPTION
256 WHEN OTHERS THEN
257 x_return_status := fnd_api.g_ret_sts_error;
258 END update_row;
259
260 /** This Procedure is used to fetch the Replenishment Count lines for the user input.
261 * @param x_return_status Return Status
262 * @param x_msg_count Message Count
263 * @param x_msg_data Message Data
264 * @param x_replenish_count_lines_lov Replenish Count Lines LOV
265 * @param p_replenish_header_id Replenishment Header Id
266 * @param p_use_loc_pick_seq Use Locator Picking Sequence or not
267 * @param p_organization_id Organization ID
268 * @param p_subinventory_code Subinventory Code
269 * @param p_planning_level Planning level of the subinventory
270 * @param p_quantity_tracked Qauntity Tracked Flag of the Subinventory
271 **/
272 PROCEDURE fetch_count_lines(
273 x_return_status OUT NOCOPY VARCHAR2
274 , x_msg_count OUT NOCOPY NUMBER
275 , x_msg_data OUT NOCOPY VARCHAR2
276 , x_replenish_count_lines OUT NOCOPY t_genref
277 , p_replenish_header_id IN NUMBER
278 , p_use_loc_pick_seq IN VARCHAR2
279 , p_organization_id IN NUMBER
280 , p_subinventory_code IN VARCHAR2
281 , p_planning_level IN NUMBER
282 , p_quantity_tracked IN NUMBER
283 ) IS
284 l_proc CONSTANT VARCHAR2(30) := 'FETCH_COUNT_LINES';
285 l_count NUMBER := 0;
286 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
287 BEGIN
288 x_return_status := fnd_api.g_ret_sts_success;
289
290 IF l_trace_on = 1 THEN
291 print_debug(
292 'The input parameters are: '
293 || fnd_global.local_chr(10)
294 || ' p_replenish_header_id : '
295 || p_replenish_header_id
296 || fnd_global.local_chr(10)
297 || ' p_use_loc_pick_seq : '
298 || p_use_loc_pick_seq
299 || fnd_global.local_chr(10)
300 || ' p_organization_id : '
301 || p_organization_id
302 || fnd_global.local_chr(10)
303 || ' p_subinventory_code : '
304 || p_subinventory_code
305 || fnd_global.local_chr(10)
306 || ' p_planning_level : '
307 || p_planning_level
308 || fnd_global.local_chr(10)
309 || ' p_quantity_tracked : '
310 || p_quantity_tracked
311 || fnd_global.local_chr(10)
312 , l_proc
313 , 9
314 );
315 END IF;
316
317 IF p_planning_level = 1 THEN -- PAR level Count
318 /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
319 'MTL_SYSTEM_ITEMS_VL'.*/
320 OPEN x_replenish_count_lines
321 FOR
322 SELECT msiv.inventory_item_id item_id
323 , msiv.concatenated_segments item
324 , mil.inventory_location_id locator_id
325 , inv_project.get_locsegs(mil.inventory_location_id, mil.organization_id) LOCATOR
326 , mrl.count_type_code count_type_code
327 , ml.meaning count_type
328 , mrl.replenishment_line_id replenishment_line_id
329 , msiv.description item_description
330 , msiv.primary_uom_code primary_uom_code
331 FROM mtl_item_locations mil, mtl_system_items_vl msiv, mtl_secondary_locators msl, mtl_replenish_lines mrl, mfg_lookups ml
332 WHERE msl.inventory_item_id = msiv.inventory_item_id
333 AND msl.organization_id = msiv.organization_id
334 AND msl.secondary_locator = mil.inventory_location_id
335 AND msl.organization_id = mil.organization_id
336 AND msl.organization_id = p_organization_id
337 AND msl.subinventory_code = p_subinventory_code
338 AND mrl.replenishment_header_id(+) = p_replenish_header_id
339 AND ml.lookup_type(+) = 'MTL_COUNT_TYPES'
340 AND ml.lookup_code(+) = mrl.count_type_code
341 AND mrl.organization_id(+) = msl.organization_id
342 AND mrl.inventory_item_id(+) = msl.inventory_item_id
343 -- AND mil.inventory_location_id = NVL(mil.physical_location_id, mil.inventory_location_id) -- Commented for Bug 6798138
344 AND mrl.locator_id(+) = msl.secondary_locator
345 AND(
346 mrl.count_type_code IS NULL
347 OR mrl.count_type_code = 2
348 OR(mrl.count_type_code = 1
349 AND p_quantity_tracked = 2
350 AND msl.maximum_quantity IS NOT NULL)
351 )
352 AND mrl.count_quantity IS NULL
353 AND mrl.error_flag IS NULL
354 ORDER BY DECODE(p_use_loc_pick_seq, 'YES', mil.picking_order, replenishment_line_id), item;
355 ELSE -- Subinventory Level Count
356 /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
357 'MTL_SYSTEM_ITEMS_VL'.*/
358 OPEN x_replenish_count_lines
359 FOR
360 SELECT mis.inventory_item_id item_id
361 , msiv.concatenated_segments item
362 , TO_NUMBER(NULL) locator_id
363 , NULL LOCATOR
364 , mrl.count_type_code count_type_code
365 , ml.meaning count_type
366 , mrl.replenishment_line_id replenishment_line_id
367 , msiv.description item_description
368 , msiv.primary_uom_code primary_uom_code
369 FROM mtl_system_items_vl msiv, mtl_item_sub_inventories mis, mtl_replenish_lines mrl, mfg_lookups ml
370 WHERE mis.inventory_item_id = msiv.inventory_item_id
371 AND mis.organization_id = msiv.organization_id
372 AND mis.organization_id = p_organization_id
373 AND mis.secondary_inventory = p_subinventory_code
374 AND mrl.replenishment_header_id(+) = p_replenish_header_id
375 AND ml.lookup_type(+) = 'MTL_COUNT_TYPES'
376 AND ml.lookup_code(+) = mrl.count_type_code
377 AND mrl.organization_id(+) = mis.organization_id
378 AND mrl.inventory_item_id(+) = mis.inventory_item_id
379 AND(
380 mrl.count_type_code IS NULL
381 OR mrl.count_type_code = 2
382 OR(mrl.count_type_code = 1
383 AND p_quantity_tracked = 2
384 AND mis.inventory_planning_code = 2)
385 )
386 AND mrl.count_quantity IS NULL
387 AND mrl.error_flag IS NULL
388 ORDER BY replenishment_line_id;
389 END IF;
390 EXCEPTION
391 WHEN OTHERS THEN
392 x_return_status := fnd_api.g_ret_sts_error;
393 END fetch_count_lines;
394
395 /** This procedure is used to get the Replenishment Count Name if the Subinventory and Organization passed
396 * as input has only one active Replenishment Count.
397 * @param x_return_status Return Status
398 * @param x_msg_count Message Count
399 * @param x_msg_data Message Data
400 * @param x_replenish_count_name Replenishment Count Name for the Subinventory and Organization passed
401 * if there exists only obe active Replenishment Count.
402 * NULL - Otherwise.
403 * @param p_organization_id Organization ID
404 * @param p_subinventory_code Subinventory Code
405 * @param p_planning_level Subinventory Planning Level
406 **/
407 PROCEDURE get_replenish_count_name(
408 x_return_status OUT NOCOPY VARCHAR2
409 , x_msg_count OUT NOCOPY NUMBER
410 , x_msg_data OUT NOCOPY VARCHAR2
411 , x_replenish_count_name OUT NOCOPY VARCHAR2
412 , p_organization_id IN NUMBER
413 , p_subinventory_code IN VARCHAR2
414 , p_planning_level IN NUMBER
415 ) IS
416 l_func_name CONSTANT VARCHAR2(30) := 'GET_REPLENISH_COUNT_NAME';
417 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
418 BEGIN
419 x_return_status := fnd_api.g_ret_sts_success;
420
421 IF l_trace_on = 1 THEN
422 print_debug(
423 'The input parameters are: '
424 || fnd_global.local_chr(10)
425 || ' p_organization_id : '
426 || p_organization_id
427 || fnd_global.local_chr(10)
428 || ' p_subinventory_code : '
429 || p_subinventory_code
430 || fnd_global.local_chr(10)
431 || ' p_planning_level : '
432 || p_planning_level
433 , l_func_name
434 , 9
435 );
436 END IF;
437
438 SELECT replenishment_count_name
439 INTO x_replenish_count_name
440 FROM mtl_replenish_headers mrh
441 WHERE mrh.organization_id = p_organization_id
442 AND mrh.subinventory_code = p_subinventory_code
443 AND mrh.process_status = 1
444 AND mrh.count_mode = 1
445 AND(
446 (
447 p_planning_level = 1
448 AND NOT EXISTS(SELECT 1
449 FROM mtl_replenish_lines mrl
450 WHERE mrl.replenishment_header_id = mrh.replenishment_header_id
451 AND mrl.locator_id IS NULL)
452 )
453 OR(
454 p_planning_level = 2
455 AND NOT EXISTS(SELECT 1
456 FROM mtl_replenish_lines mrl
457 WHERE mrl.replenishment_header_id = mrh.replenishment_header_id
458 AND mrl.locator_id IS NOT NULL)
459 )
460 );
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 x_replenish_count_name := NULL;
464 WHEN TOO_MANY_ROWS THEN
465 x_replenish_count_name := NULL;
466 WHEN OTHERS THEN
467 x_replenish_count_name := NULL;
468 x_return_status := fnd_api.g_ret_sts_error;
469 END get_replenish_count_name;
470
471
472
473 /** This procedure is used to check whether invalid and/or uncounted lines exist.
474 * @param x_return_status Return Status
475 * @param x_msg_count Message Count
476 * @param x_msg_data Message Data
477 * @param p_replenish_header_id Replenishment Count Header Id
478 * @param p_quantity_tracked Qauntity Tracked Flag of the Subinventory
479 * @param p_planning_level Planning level of the subinventory
480 * @param p_subinventory_code Subinventory Code
481 * @RETURN NUMBER 1 - Invalid and uncounted lines exist.
482 * 2 - Invalid but no uncounted lines exist.
483 * 3 - No invalid but uncounted lines exist.
484 * 4 - No invalid and no uncounted lines exist.
485 **/
486 FUNCTION invalid_uncounted_lines_exist(
487 x_return_status OUT NOCOPY VARCHAR2
488 , x_msg_count OUT NOCOPY NUMBER
489 , x_msg_data OUT NOCOPY VARCHAR2
490 , p_replenish_header_id IN NUMBER
491 , p_quantity_tracked IN NUMBER
492 , p_planning_level IN NUMBER
493 , p_subinventory_code IN VARCHAR2
494 )
495 RETURN NUMBER IS
496 l_func_name CONSTANT VARCHAR2(30) := 'invalid_uncounted_lines_exist';
497 l_record_exists NUMBER := 0;
498 l_error_record_exists NUMBER := 0;
499 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
500 BEGIN
501 x_return_status := fnd_api.g_ret_sts_success;
502
503 IF l_trace_on = 1 THEN
504 print_debug(
505 'The input parameters are: '
506 || fnd_global.local_chr(10)
507 || ' p_replenish_header_id : '
508 || p_replenish_header_id
509 || fnd_global.local_chr(10)
510 || ' p_quantity_tracked : '
511 || p_quantity_tracked
512 || fnd_global.local_chr(10)
513 || ' p_planning_level : '
514 || p_planning_level
515 || fnd_global.local_chr(10)
516 || ' p_subinventory_code : '
517 || p_subinventory_code
518 || fnd_global.local_chr(10)
519 , l_func_name
520 , 9
521 );
522 END IF;
523
524 BEGIN
525 SELECT 1
526 INTO l_record_exists
527 FROM DUAL
528 WHERE EXISTS(SELECT 1
529 FROM mtl_replenish_lines
530 WHERE replenishment_header_id = p_replenish_header_id
531 AND count_quantity IS NULL
532 AND error_flag IS NULL);
533 EXCEPTION
534 WHEN NO_DATA_FOUND THEN
535 l_record_exists := 0;
536 WHEN OTHERS THEN
537 x_return_status := fnd_api.g_ret_sts_error;
538 END;
539
540 BEGIN
541 SELECT 1
542 INTO l_error_record_exists
543 FROM DUAL
544 WHERE EXISTS(
545 SELECT 1
546 FROM mtl_replenish_lines mrl
547 WHERE replenishment_header_id = p_replenish_header_id
548 AND(
549 (count_type_code = 1
550 AND p_quantity_tracked = 1)
551 OR(
552 p_planning_level = 1
553 AND(
554 locator_id IS NULL
555 OR count_type_code = 3
556 OR NOT EXISTS(
557 SELECT maximum_quantity
558 FROM mtl_secondary_locators msl, mtl_item_locations mil, mtl_system_items msi
559 WHERE msl.inventory_item_id = mrl.inventory_item_id
560 AND msl.secondary_locator = mrl.locator_id
561 AND msl.organization_id = mrl.organization_id
562 AND msi.inventory_item_id = msl.inventory_item_id
563 AND msi.organization_id = msl.organization_id
564 AND mil.inventory_location_id = msl.secondary_locator
565 AND mil.organization_id = msl.organization_id
566 AND msi.inventory_item_flag = 'Y'
567 AND msi.stock_enabled_flag = 'Y'
568 AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE))
569 OR(
570 EXISTS(
571 SELECT maximum_quantity
572 FROM mtl_secondary_locators msl, mtl_item_locations mil, mtl_system_items msi
573 WHERE msl.inventory_item_id = mrl.inventory_item_id
574 AND msl.secondary_locator = mrl.locator_id
575 AND msl.organization_id = mrl.organization_id
576 AND msi.inventory_item_id = msl.inventory_item_id
577 AND msi.organization_id = msl.organization_id
578 AND mil.inventory_location_id = msl.secondary_locator
579 AND mil.organization_id = msl.organization_id
580 AND msi.inventory_item_flag = 'Y'
581 AND msi.stock_enabled_flag = 'Y'
582 AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
583 AND maximum_quantity IS NULL)
584 AND count_type_code IN(1, 4)
585 )
586 )
587 )
588 OR(
589 p_planning_level <> 1
590 AND(
591 locator_id IS NOT NULL
592 OR count_type_code = 4
593 OR(
594 NOT EXISTS(
595 SELECT mis.inventory_planning_code
596 FROM mtl_item_sub_inventories mis, mtl_system_items msi
597 WHERE mis.inventory_item_id = mrl.inventory_item_id
598 AND mis.secondary_inventory = p_subinventory_code
599 AND mis.organization_id = mrl.organization_id
600 AND msi.inventory_item_id = mis.inventory_item_id
601 AND msi.organization_id = mis.organization_id
602 AND msi.inventory_item_flag = 'Y'
603 AND msi.stock_enabled_flag = 'Y'
604 AND mis.inventory_planning_code IN(2, 6))
605 )
606 OR(
607 EXISTS(
608 SELECT mis.inventory_planning_code
609 FROM mtl_item_sub_inventories mis, mtl_system_items msi
610 WHERE mis.inventory_item_id = mrl.inventory_item_id
611 AND mis.secondary_inventory = p_subinventory_code
612 AND mis.organization_id = mrl.organization_id
613 AND msi.inventory_item_id = mis.inventory_item_id
614 AND msi.organization_id = mis.organization_id
615 AND msi.inventory_item_flag = 'Y'
616 AND msi.stock_enabled_flag = 'Y'
617 AND mis.inventory_planning_code = 6)
618 AND count_type_code IN(1, 3)
619 )
620 )
621 )
622 ));
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 l_error_record_exists := 0;
626 WHEN OTHERS THEN
627 x_return_status := fnd_api.g_ret_sts_error;
628 END;
629
630 IF l_trace_on = 1 THEN
631 print_debug('Error record exists : ' || l_error_record_exists, l_func_name, 9);
632 print_debug('Invalid record exists : ' || l_record_exists, l_func_name, 9);
633 END IF;
634
635 IF l_error_record_exists = 1
636 AND l_record_exists = 1 THEN
637 RETURN 1;
638 ELSIF l_error_record_exists = 1
639 AND l_record_exists <> 1 THEN
640 RETURN 2;
641 ELSIF l_error_record_exists <> 1
642 AND l_record_exists = 1 THEN
643 RETURN 3;
644 ELSE
645 RETURN 4;
646 END IF;
647 END invalid_uncounted_lines_exist;
648
649 /** This function returns if the Replenishment Count passed as input is a valid
650 * one for the passed subinventory planning level.
651 * @param x_return_status Return Status
652 * @param x_msg_count Message Count
653 * @param x_msg_data Message Data
654 * @param p_replenish_header_id Replenishment Count Header Id
655 * @param p_planning_level Subinventory Planning Level
656 * @RETURN NUMBER 1 - Count is valid.
657 * 2 - Count is invalid.
658 **/
659 FUNCTION is_count_valid(
660 x_return_status OUT NOCOPY VARCHAR2
661 , x_msg_count OUT NOCOPY NUMBER
662 , x_msg_data OUT NOCOPY VARCHAR2
663 , p_replenish_header_id IN NUMBER
664 , p_planning_level IN NUMBER
665 )
666 RETURN NUMBER IS
667 l_func_name CONSTANT VARCHAR2(30) := 'IS_COUNT_VALID';
668 l_count_valid NUMBER := 1;
669 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
670 BEGIN
671 x_return_status := fnd_api.g_ret_sts_success;
672
673 IF l_trace_on = 1 THEN
674 print_debug(
675 'The input parameters are: '
676 || fnd_global.local_chr(10)
677 || ' p_replenish_header_id : '
678 || p_replenish_header_id
679 || fnd_global.local_chr(10)
680 || ' p_planning_level : '
681 || p_planning_level
682 || fnd_global.local_chr(10)
683 , l_func_name
684 , 9
685 );
686 END IF;
687
688 SELECT 2
689 INTO l_count_valid
690 FROM DUAL
691 WHERE EXISTS(
692 SELECT 1
693 FROM mtl_replenish_lines
694 WHERE replenishment_header_id = p_replenish_header_id
695 AND((p_planning_level = 1
696 AND locator_id IS NULL)
697 OR(p_planning_level = 2
698 AND locator_id IS NOT NULL)));
699
700 RETURN l_count_valid;
701 EXCEPTION
702 WHEN NO_DATA_FOUND THEN
703 RETURN l_count_valid;
704 WHEN OTHERS THEN
705 x_return_status := fnd_api.g_ret_sts_error;
706 END is_count_valid;
707
708 /** This procedure submits the passed in Replenishment Count
709 * for processing and Reporting.
710 * @param x_return_status Return Status
711 * @param x_msg_count Message Count
712 * @param x_msg_data Message Data
713 * @param x_proces_request_id Process Request Id
714 * @param p_replenish_header_id Replenishment Count Header Id
715 * @param p_organization_id Organization Id<br>
716 **/
717 PROCEDURE process_report_count(
718 x_return_status OUT NOCOPY VARCHAR2
719 , x_msg_count OUT NOCOPY NUMBER
720 , x_msg_data OUT NOCOPY VARCHAR2
721 , x_process_request_id OUT NOCOPY NUMBER
722 , p_replenish_header_id IN NUMBER
723 , p_organization_id IN NUMBER
724 ) IS
725 l_proc CONSTANT VARCHAR2(30) := 'PROCESS_REPORT_COUNT';
726 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
727 BEGIN
728 x_return_status := fnd_api.g_ret_sts_success;
729
730 IF l_trace_on = 1 THEN
731 print_debug(
732 'The input parameters are: '
733 || fnd_global.local_chr(10)
734 || ' p_replenish_header_id : '
735 || p_replenish_header_id
736 || fnd_global.local_chr(10)
737 || ' p_organization_id : '
738 || p_organization_id
739 || fnd_global.local_chr(10)
740 , l_proc
741 , 9
742 );
743 END IF;
744
745 x_process_request_id := fnd_request.submit_request('INV', 'INCRPR', '', '', FALSE, TO_CHAR(2), TO_CHAR(p_replenish_header_id),'4', CHR(0));
746
747 IF x_process_request_id <= 0 THEN
748 x_return_status := fnd_api.g_ret_sts_error;
749 ELSE
750 UPDATE mtl_replenish_headers
751 SET process_status = 2
752 WHERE replenishment_header_id = p_replenish_header_id;
753
754 x_return_status := fnd_api.g_ret_sts_success;
755
756 IF l_trace_on = 1 THEN
757 print_debug('Process Request Id : ' || x_process_request_id, l_proc, 9);
758 END IF;
759 END IF;
760
761 COMMIT;
762
763 EXCEPTION
764 WHEN OTHERS THEN
765 x_return_status := fnd_api.g_ret_sts_error;
766 END process_report_count;
767 END inv_replenish_count_pvt;