[Home] [Help]
PACKAGE BODY: APPS.MTL_SECONDARY_INVENTORIES_PKG
Source
1 PACKAGE BODY mtl_secondary_inventories_pkg AS
2 /* $Header: INVSDSUB.pls 120.4 2011/12/21 06:32:36 skommine ship $ */
3 FUNCTION check_unique(x_rowid IN OUT nocopy VARCHAR2, x_secondary_inventory_name VARCHAR2, x_organization_id NUMBER)
4 RETURN NUMBER IS
5 dummy NUMBER;
6 BEGIN
7 SELECT COUNT(1)
8 INTO dummy
9 FROM mtl_secondary_inventories
10 WHERE organization_id = x_organization_id
11 AND secondary_inventory_name = x_secondary_inventory_name
12 AND((x_rowid IS NULL)
13 OR(ROWID <> x_rowid));
14
15 IF (dummy >= 1) THEN
16 RETURN 1;
17 ELSE
18 RETURN 0;
19 END IF;
20 END check_unique;
21
22 PROCEDURE commit_row IS
23 BEGIN
24 COMMIT;
25 END commit_row;
26
27 PROCEDURE rollback_row IS
28 BEGIN
29 ROLLBACK;
30 END rollback_row;
31
32 /* WMS Enhancements
33 Accomodated the Status_ID, Default_Loc_Status_ID, Default_Cost_Group_ID
34 and LPN_Controlled_Flag in the following procedure. */
35 PROCEDURE insert_row(
36 x_rowid IN OUT nocopy VARCHAR2
37 , x_secondary_inventory_name VARCHAR2
38 , x_organization_id NUMBER
39 , x_last_update_date DATE
40 , x_last_updated_by NUMBER
41 , x_creation_date DATE
42 , x_created_by NUMBER
43 , x_last_update_login NUMBER
44 , x_description VARCHAR2
45 , x_disable_date DATE
46 , x_inventory_atp_code NUMBER
47 , x_availability_type NUMBER
48 , x_reservable_type NUMBER
49 , x_locator_type NUMBER
50 , x_picking_order NUMBER
51 , x_dropping_order NUMBER
52 , x_material_account NUMBER
53 , x_material_overhead_account NUMBER
54 , x_resource_account NUMBER
55 , x_overhead_account NUMBER
56 , x_outside_processing_account NUMBER
57 , x_quantity_tracked NUMBER
58 , x_asset_inventory NUMBER
59 , x_source_type NUMBER
60 , x_source_subinventory VARCHAR2
61 , x_source_organization_id NUMBER
62 , x_requisition_approval_type NUMBER
63 , x_expense_account NUMBER
64 , x_encumbrance_account NUMBER
65 , x_attribute_category VARCHAR2
66 , x_attribute1 VARCHAR2
67 , x_attribute2 VARCHAR2
71 , x_attribute6 VARCHAR2
68 , x_attribute3 VARCHAR2
69 , x_attribute4 VARCHAR2
70 , x_attribute5 VARCHAR2
72 , x_attribute7 VARCHAR2
73 , x_attribute8 VARCHAR2
74 , x_attribute9 VARCHAR2
75 , x_attribute10 VARCHAR2
76 , x_attribute11 VARCHAR2
77 , x_attribute12 VARCHAR2
78 , x_attribute13 VARCHAR2
79 , x_attribute14 VARCHAR2
80 , x_attribute15 VARCHAR2
81 , x_preprocessing_lead_time NUMBER
82 , x_processing_lead_time NUMBER
83 , x_postprocessing_lead_time NUMBER
84 , x_demand_class VARCHAR2
85 , x_project_id NUMBER
86 , x_task_id NUMBER
87 , x_subinventory_usage NUMBER
88 , x_notify_list_id NUMBER
89 , x_depreciable_flag NUMBER
90 , x_location_id NUMBER
91 , x_status_id NUMBER
92 , x_default_loc_status_id NUMBER
93 , x_lpn_controlled_flag NUMBER
94 , x_default_cost_group_id NUMBER
95 /* As per bug 1584641 */
96 --, X_pick_methodology NUMBER
97 , x_pick_uom_code VARCHAR2
98 , x_cartonization_flag NUMBER
99 , x_planning_level NUMBER DEFAULT 2
100 , x_default_count_type_code NUMBER DEFAULT 2
101 , x_subinventory_type NUMBER DEFAULT 1--RCVLOCATORSSUPPORT
102 , x_enable_bulk_pick VARCHAR2 DEFAULT 'N'
103 , x_enable_locator_alias VARCHAR2 DEFAULT 'N'
104 , x_enforce_alias_uniqueness VARCHAR2 DEFAULT 'N'
105 , x_enable_opp_cyc_count_flag VARCHAR2 DEFAULT 'N' -- Added for Opp Cyc Counting bug#9248808
106 , x_opp_cyc_count_header_id NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
107 , x_opp_cyc_count_quantity NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
108 , x_opp_cyc_count_days NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
109
110 ) IS
111 CURSOR c IS
112 SELECT ROWID
113 FROM mtl_secondary_inventories
114 WHERE organization_id = x_organization_id
115 AND secondary_inventory_name = x_secondary_inventory_name;
116 BEGIN
117 INSERT INTO mtl_secondary_inventories
118 (
119 secondary_inventory_name
120 , organization_id
121 , last_update_date
122 , last_updated_by
123 , creation_date
124 , created_by
125 , last_update_login
126 , description
127 , disable_date
128 , inventory_atp_code
129 , availability_type
130 , reservable_type
131 , locator_type
132 , picking_order
133 , dropping_order
134 , material_account
135 , material_overhead_account
136 , resource_account
137 , overhead_account
138 , outside_processing_account
139 , quantity_tracked
140 , asset_inventory
141 , source_type
142 , source_subinventory
143 , source_organization_id
144 , requisition_approval_type
145 , expense_account
146 , encumbrance_account
147 , attribute_category
148 , attribute1
149 , attribute2
150 , attribute3
151 , attribute4
152 , attribute5
153 , attribute6
154 , attribute7
155 , attribute8
156 , attribute9
157 , attribute10
158 , attribute11
159 , attribute12
160 , attribute13
161 , attribute14
162 , attribute15
163 , preprocessing_lead_time
164 , processing_lead_time
165 , postprocessing_lead_time
166 , demand_class
167 , project_id
168 , task_id
169 , subinventory_usage
170 , notify_list_id
171 , depreciable_flag
172 , location_id
173 , status_id
174 , default_loc_status_id
175 , lpn_controlled_flag
176 , default_cost_group_id
177 /* As per bug 1584641 */
178 -- ,pick_methodology
179 , pick_uom_code
180 , cartonization_flag
181 , planning_level
182 , default_count_type_code
183 , subinventory_type
184 , enable_bulk_pick
185 , enable_locator_alias
186 , enforce_alias_uniqueness
187 , enable_opp_cyc_count -- Added for Opp Cyc Counting bug#9248808
188 , opp_cyc_count_header_id -- Added for Opp Cyc Counting bug#9248808
189 , opp_cyc_count_quantity -- Added for Opp Cyc Counting bug#9248808
190 , opp_cyc_count_days -- Added for Opp Cyc Counting bug#9248808
191 )
192 VALUES (
193 x_secondary_inventory_name
194 , x_organization_id
198 , x_created_by
195 , x_last_update_date
196 , x_last_updated_by
197 , x_creation_date
199 , x_last_update_login
200 , x_description
201 , x_disable_date
202 , x_inventory_atp_code
203 , x_availability_type
204 , x_reservable_type
205 , x_locator_type
206 , x_picking_order
207 , x_dropping_order
208 , x_material_account
209 , x_material_overhead_account
210 , x_resource_account
211 , x_overhead_account
212 , x_outside_processing_account
213 , x_quantity_tracked
214 , x_asset_inventory
215 , x_source_type
216 , x_source_subinventory
217 , x_source_organization_id
218 , x_requisition_approval_type
219 , x_expense_account
220 , x_encumbrance_account
221 , x_attribute_category
222 , x_attribute1
223 , x_attribute2
224 , x_attribute3
225 , x_attribute4
226 , x_attribute5
227 , x_attribute6
228 , x_attribute7
229 , x_attribute8
230 , x_attribute9
231 , x_attribute10
232 , x_attribute11
233 , x_attribute12
234 , x_attribute13
235 , x_attribute14
236 , x_attribute15
237 , x_preprocessing_lead_time
238 , x_processing_lead_time
239 , x_postprocessing_lead_time
240 , x_demand_class
241 , x_project_id
242 , x_task_id
243 , x_subinventory_usage
244 , x_notify_list_id
245 , x_depreciable_flag
246 , x_location_id
247 , x_status_id
248 , x_default_loc_status_id
249 , x_lpn_controlled_flag
250 , x_default_cost_group_id
251 /* As per bug 1584641 */
252 --, X_pick_methodology
253 , x_pick_uom_code
254 , x_cartonization_flag
255 , x_planning_level
256 , x_default_count_type_code
257 , x_subinventory_type
258 , x_enable_bulk_pick
259 , x_enable_locator_alias
260 , x_enforce_alias_uniqueness
261 , x_enable_opp_cyc_count_flag -- Added for Opp Cyc Counting bug#9248808
262 , x_opp_cyc_count_header_id -- Added for Opp Cyc Counting bug#9248808
263 , x_opp_cyc_count_quantity -- Added for Opp Cyc Counting bug#9248808
264 , x_opp_cyc_count_days -- Added for Opp Cyc Counting bug#9248808
265 );
266
267 /* WMS Material Status Enhancements
268 This Procedure Caters to the insertion of records in the
269 table MTL_MATERIAL_STATUS_HISTORY. */
270 /* Commenting this code because for status history we want to capture from where
271 status was updated (Desktop or Mobile) and so we will make a call to this procedure
272 explicitly instead of calling it indirectly
273 Bug # 1695432
274 IF (INV_INSTALL.ADV_INV_INSTALLED(p_Organization_ID => NULL))
275 AND (X_Status_ID IS NOT NULL) THEN
276 Status_History ( X_Organization_ID,
277 NULL,
278 NULL,
279 NULL,
280 2,
281 X_Status_ID,
282 X_Secondary_Inventory_Name,
283 NULL,
284 X_Creation_Date ,
285 X_Created_By,
286 X_Last_Updated_By,
287 X_Last_Update_Date,
288 X_Last_Update_Login,
289 'Y',
290 'Y');
291 END IF;
292 */
293 OPEN c;
294 FETCH c INTO x_rowid;
295
296 IF (c%NOTFOUND) THEN
297 CLOSE c;
298 RAISE NO_DATA_FOUND;
299 END IF;
300
301 CLOSE c;
302 END insert_row;
303
304 /* WMS Enhancements
305 Accomodated the Status_ID, Default_Loc_Status_ID, Default_Cost_Group_ID
306 and LPN_Controlled_Flag in the following procedure. */
307 PROCEDURE lock_row(
308 x_rowid VARCHAR2
309 , x_secondary_inventory_name VARCHAR2
310 , x_organization_id NUMBER
311 , x_description VARCHAR2
312 , x_disable_date DATE
313 , x_inventory_atp_code NUMBER
314 , x_availability_type NUMBER
315 , x_reservable_type NUMBER
316 , x_locator_type NUMBER
317 , x_picking_order NUMBER
318 , x_dropping_order NUMBER
319 , x_material_account NUMBER
320 , x_material_overhead_account NUMBER
321 , x_resource_account NUMBER
322 , x_overhead_account NUMBER
323 , x_outside_processing_account NUMBER
324 , x_quantity_tracked NUMBER
325 , x_asset_inventory NUMBER
326 , x_source_type NUMBER
327 , x_source_subinventory VARCHAR2
328 , x_source_organization_id NUMBER
329 , x_requisition_approval_type NUMBER
330 , x_expense_account NUMBER
331 , x_encumbrance_account NUMBER
332 , x_attribute_category VARCHAR2
333 , x_attribute1 VARCHAR2
337 , x_attribute5 VARCHAR2
334 , x_attribute2 VARCHAR2
335 , x_attribute3 VARCHAR2
336 , x_attribute4 VARCHAR2
338 , x_attribute6 VARCHAR2
339 , x_attribute7 VARCHAR2
340 , x_attribute8 VARCHAR2
341 , x_attribute9 VARCHAR2
342 , x_attribute10 VARCHAR2
343 , x_attribute11 VARCHAR2
344 , x_attribute12 VARCHAR2
345 , x_attribute13 VARCHAR2
346 , x_attribute14 VARCHAR2
347 , x_attribute15 VARCHAR2
348 , x_preprocessing_lead_time NUMBER
349 , x_processing_lead_time NUMBER
350 , x_postprocessing_lead_time NUMBER
351 , x_demand_class VARCHAR2
352 , x_project_id NUMBER
353 , x_task_id NUMBER
354 , x_subinventory_usage NUMBER
355 , x_notify_list_id NUMBER
356 , x_depreciable_flag NUMBER
357 , x_location_id NUMBER
358 , x_status_id NUMBER
359 , x_default_loc_status_id NUMBER
360 , x_lpn_controlled_flag NUMBER
361 , x_default_cost_group_id NUMBER
362 /* As per bug 1584641 */
363 --, X_pick_methodology NUMBER
364 , x_pick_uom_code VARCHAR2
365 , x_cartonization_flag NUMBER
366 , x_planning_level NUMBER DEFAULT 2
367 , x_default_count_type_code NUMBER DEFAULT 2
368 , x_subinventory_type NUMBER DEFAULT 1 --RCVLOCATORSSUPPORT
369 , x_enable_bulk_pick VARCHAR2 DEFAULT 'N'
370 , x_enable_locator_alias VARCHAR2 DEFAULT 'N'
371 , x_enforce_alias_uniqueness VARCHAR2 DEFAULT 'N'
372 , x_enable_opp_cyc_count_flag VARCHAR2 DEFAULT 'N' -- Added for Opp Cyc Counting bug#9248808
373 , x_opp_cyc_count_header_id NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
374 , x_opp_cyc_count_quantity NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
375 , x_opp_cyc_count_days NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
376 ) IS
377 CURSOR c IS
378 SELECT *
379 FROM mtl_secondary_inventories
380 WHERE ROWID = x_rowid
381 FOR UPDATE OF organization_id NOWAIT;
382
383 recinfo c%ROWTYPE;
384 record_changed EXCEPTION;
385 BEGIN
386 OPEN c;
387 FETCH c INTO recinfo;
388
389 IF (c%NOTFOUND) THEN
390 CLOSE c;
391 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
392 app_exception.raise_exception;
393 END IF;
394
395 CLOSE c;
396
397 IF NOT(
398 (recinfo.secondary_inventory_name = x_secondary_inventory_name)
399 AND(recinfo.organization_id = x_organization_id)
400 AND((recinfo.description = x_description)
401 OR((recinfo.description IS NULL)
402 AND(x_description IS NULL)))
403 AND((recinfo.disable_date = x_disable_date)
404 OR((recinfo.disable_date IS NULL)
405 AND(x_disable_date IS NULL)))
406 AND(recinfo.inventory_atp_code = x_inventory_atp_code)
407 AND(recinfo.availability_type = x_availability_type)
408 AND(recinfo.reservable_type = x_reservable_type)
409 AND((recinfo.locator_type = x_locator_type)
410 OR((recinfo.locator_type IS NULL)
411 AND(x_locator_type IS NULL)))
412 AND((recinfo.picking_order = x_picking_order)
413 OR((recinfo.picking_order IS NULL)
414 AND(x_picking_order IS NULL)))
415 AND((recinfo.dropping_order = x_dropping_order)
416 OR((recinfo.dropping_order IS NULL)
417 AND(x_dropping_order IS NULL)))
418 AND((recinfo.material_account = x_material_account)
419 OR((recinfo.material_account IS NULL)
420 AND(x_material_account IS NULL)))
421 AND(
422 (recinfo.material_overhead_account = x_material_overhead_account)
423 OR((recinfo.material_overhead_account IS NULL)
424 AND(x_material_overhead_account IS NULL))
425 )
426 AND((recinfo.resource_account = x_resource_account)
427 OR((recinfo.resource_account IS NULL)
428 AND(x_resource_account IS NULL)))
429 AND((recinfo.overhead_account = x_overhead_account)
430 OR((recinfo.overhead_account IS NULL)
431 AND(x_overhead_account IS NULL)))
432 AND(
433 (recinfo.outside_processing_account = x_outside_processing_account)
434 OR((recinfo.outside_processing_account IS NULL)
435 AND(x_outside_processing_account IS NULL))
436 )
437 AND(recinfo.quantity_tracked = x_quantity_tracked)
438 AND(recinfo.asset_inventory = x_asset_inventory)
439 AND(recinfo.depreciable_flag = x_depreciable_flag)
440 AND((recinfo.source_type = x_source_type)
441 OR((recinfo.source_type IS NULL)
442 AND(x_source_type IS NULL)))
443 AND(
444 (recinfo.source_subinventory = x_source_subinventory)
445 OR((recinfo.source_subinventory IS NULL)
446 AND(x_source_subinventory IS NULL))
447 )
448 AND(
449 (recinfo.source_organization_id = x_source_organization_id)
450 OR((recinfo.source_organization_id IS NULL)
451 AND(x_source_organization_id IS NULL))
452 )
453 AND(
454 (recinfo.requisition_approval_type = x_requisition_approval_type)
458 AND((recinfo.expense_account = x_expense_account)
455 OR((recinfo.requisition_approval_type IS NULL)
456 AND(x_requisition_approval_type IS NULL))
457 )
459 OR((recinfo.expense_account IS NULL)
460 AND(x_expense_account IS NULL)))
461 AND(
462 (recinfo.encumbrance_account = x_encumbrance_account)
463 OR((recinfo.encumbrance_account IS NULL)
464 AND(x_encumbrance_account IS NULL))
465 )
466 AND(
467 (recinfo.attribute_category = x_attribute_category)
468 OR((recinfo.attribute_category IS NULL)
469 AND(x_attribute_category IS NULL))
470 )
471 ) THEN
472 RAISE record_changed;
473 END IF;
474
475 IF NOT(
476 ((recinfo.attribute1 = x_attribute1)
477 OR((recinfo.attribute1 IS NULL)
478 AND(x_attribute1 IS NULL)))
479 AND((recinfo.attribute2 = x_attribute2)
480 OR((recinfo.attribute2 IS NULL)
481 AND(x_attribute2 IS NULL)))
482 AND((recinfo.attribute3 = x_attribute3)
483 OR((recinfo.attribute3 IS NULL)
484 AND(x_attribute3 IS NULL)))
485 AND((recinfo.attribute4 = x_attribute4)
486 OR((recinfo.attribute4 IS NULL)
487 AND(x_attribute4 IS NULL)))
488 AND((recinfo.attribute5 = x_attribute5)
489 OR((recinfo.attribute5 IS NULL)
490 AND(x_attribute5 IS NULL)))
491 AND((recinfo.attribute6 = x_attribute6)
492 OR((recinfo.attribute6 IS NULL)
493 AND(x_attribute6 IS NULL)))
494 AND((recinfo.attribute7 = x_attribute7)
495 OR((recinfo.attribute7 IS NULL)
496 AND(x_attribute7 IS NULL)))
497 AND((recinfo.attribute8 = x_attribute8)
498 OR((recinfo.attribute8 IS NULL)
499 AND(x_attribute8 IS NULL)))
500 AND((recinfo.attribute9 = x_attribute9)
501 OR((recinfo.attribute9 IS NULL)
502 AND(x_attribute9 IS NULL)))
503 AND((recinfo.attribute10 = x_attribute10)
504 OR((recinfo.attribute10 IS NULL)
505 AND(x_attribute10 IS NULL)))
506 AND((recinfo.attribute11 = x_attribute11)
507 OR((recinfo.attribute11 IS NULL)
508 AND(x_attribute11 IS NULL)))
509 AND((recinfo.attribute12 = x_attribute12)
510 OR((recinfo.attribute12 IS NULL)
511 AND(x_attribute12 IS NULL)))
512 AND((recinfo.attribute13 = x_attribute13)
513 OR((recinfo.attribute13 IS NULL)
514 AND(x_attribute13 IS NULL)))
515 AND((recinfo.attribute14 = x_attribute14)
516 OR((recinfo.attribute14 IS NULL)
517 AND(x_attribute14 IS NULL)))
518 AND((recinfo.attribute15 = x_attribute15)
519 OR((recinfo.attribute15 IS NULL)
520 AND(x_attribute15 IS NULL)))
521 AND(
522 (recinfo.preprocessing_lead_time = x_preprocessing_lead_time)
523 OR((recinfo.preprocessing_lead_time IS NULL)
524 AND(x_preprocessing_lead_time IS NULL))
525 )
526 AND(
527 (recinfo.processing_lead_time = x_processing_lead_time)
528 OR((recinfo.processing_lead_time IS NULL)
529 AND(x_processing_lead_time IS NULL))
530 )
531 AND(
532 (recinfo.postprocessing_lead_time = x_postprocessing_lead_time)
533 OR((recinfo.postprocessing_lead_time IS NULL)
534 AND(x_postprocessing_lead_time IS NULL))
535 )
536 AND((recinfo.demand_class = x_demand_class)
537 OR((recinfo.demand_class IS NULL)
538 AND(x_demand_class IS NULL)))
539 AND((recinfo.project_id = x_project_id)
540 OR((recinfo.project_id IS NULL)
541 AND(x_project_id IS NULL)))
542 AND((recinfo.task_id = x_task_id)
543 OR((recinfo.task_id IS NULL)
544 AND(x_task_id IS NULL)))
545 AND(
546 (recinfo.subinventory_usage = x_subinventory_usage)
547 OR((recinfo.subinventory_usage IS NULL)
548 AND(x_subinventory_usage IS NULL))
549 )
550 AND((recinfo.notify_list_id = x_notify_list_id)
551 OR((recinfo.notify_list_id IS NULL)
552 AND(x_notify_list_id IS NULL)))
553 AND((recinfo.location_id = x_location_id)
554 OR((recinfo.location_id IS NULL)
555 AND(x_location_id IS NULL)))
556 AND((recinfo.status_id = x_status_id)
557 OR((recinfo.status_id IS NULL)
558 AND(x_status_id IS NULL)))
559 AND(
560 (recinfo.default_loc_status_id = x_default_loc_status_id)
561 OR((recinfo.default_loc_status_id IS NULL)
562 AND(x_default_loc_status_id IS NULL))
563 )
564 AND(
565 (recinfo.lpn_controlled_flag = x_lpn_controlled_flag)
566 OR((recinfo.lpn_controlled_flag IS NULL)
567 AND(x_lpn_controlled_flag IS NULL))
568 )
569 /* As per bug 1584641 */
570 --AND ( (Recinfo.pick_methodology = X_pick_methodology)
571 -- OR ( (Recinfo.pick_methodology IS NULL)
572 -- AND (X_pick_methodology IS NULL)))
573 AND((recinfo.pick_uom_code = x_pick_uom_code)
577 OR((recinfo.cartonization_flag IS NULL)
574 OR((recinfo.pick_uom_code IS NULL)
575 AND(x_pick_uom_code IS NULL)))
576 AND((recinfo.cartonization_flag = x_cartonization_flag)
578 AND(x_cartonization_flag IS NULL)))
579 AND((recinfo.planning_level = x_planning_level)
580 OR((recinfo.planning_level IS NULL)
581 AND(x_planning_level IS NULL)))
582 AND((recinfo.default_count_type_code = x_default_count_type_code)
583 OR((recinfo.default_count_type_code IS NULL)
584 AND(x_default_count_type_code IS NULL))
585 )
586 AND((recinfo.subinventory_type = x_subinventory_type)
587 OR((recinfo.subinventory_type IS NULL)
588 AND(x_subinventory_type IS NULL))
589 ) --RCVLOCATORSSUPPORT
590 AND((recinfo.enable_bulk_pick = x_enable_bulk_pick)
591 OR((recinfo.enable_bulk_pick IS NULL)
592 AND(x_enable_bulk_pick IS NULL))
593 )
594 AND((recinfo.enable_locator_alias = x_enable_locator_alias)
595 OR((recinfo.enable_locator_alias IS NULL)
596 AND(x_enable_locator_alias IS NULL))
597 )
598 AND((recinfo.enforce_alias_uniqueness = x_enforce_alias_uniqueness)
599 OR((recinfo.enforce_alias_uniqueness IS NULL)
600 AND(x_enforce_alias_uniqueness IS NULL))
601 )
602 -- Added for Opp Cyc Counting bug#9248808
603 AND((recinfo.enable_opp_cyc_count = x_enable_opp_cyc_count_flag)
604 OR((recinfo.enable_opp_cyc_count IS NULL)
605 AND(x_enable_opp_cyc_count_flag IS NULL))
606 ) --RCVLOCATORSSUPPORT
607 AND((recinfo.opp_cyc_count_header_id = x_opp_cyc_count_header_id)
608 OR((recinfo.opp_cyc_count_header_id IS NULL)
609 AND(x_opp_cyc_count_header_id IS NULL))
610 )
611 AND((recinfo.opp_cyc_count_quantity = x_opp_cyc_count_quantity)
612 OR((recinfo.opp_cyc_count_quantity IS NULL)
613 AND(x_opp_cyc_count_quantity IS NULL))
614 )
615 AND((recinfo.opp_cyc_count_days = x_opp_cyc_count_days)
616 OR((recinfo.opp_cyc_count_days IS NULL)
617 AND(x_opp_cyc_count_days IS NULL))
618 )
619
620 -- Added for Opp Cyc Counting bug#9248808
621 ) THEN
622 RAISE record_changed;
623 END IF;
624 EXCEPTION
625 WHEN record_changed THEN
626 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
627 app_exception.raise_exception;
628 WHEN OTHERS THEN
629 RAISE;
630 END lock_row;
631
632 /* WMS Enhancements
633 Accomodated the Status_ID, Default_Loc_Status_ID, Default_Cost_Group_ID
634 and LPN_Controlled_Flag in the following procedure. */
635 PROCEDURE update_row(
636 x_rowid VARCHAR2
637 , x_secondary_inventory_name VARCHAR2
638 , x_organization_id NUMBER
639 , x_last_update_date DATE
640 , x_last_updated_by NUMBER
641 , x_last_update_login NUMBER
642 , x_description VARCHAR2
643 , x_disable_date DATE
644 , x_inventory_atp_code NUMBER
645 , x_availability_type NUMBER
646 , x_reservable_type NUMBER
647 , x_locator_type NUMBER
648 , x_picking_order NUMBER
649 , x_dropping_order NUMBER
650 , x_material_account NUMBER
651 , x_material_overhead_account NUMBER
652 , x_resource_account NUMBER
653 , x_overhead_account NUMBER
654 , x_outside_processing_account NUMBER
655 , x_quantity_tracked NUMBER
656 , x_asset_inventory NUMBER
657 , x_source_type NUMBER
658 , x_source_subinventory VARCHAR2
659 , x_source_organization_id NUMBER
660 , x_requisition_approval_type NUMBER
661 , x_expense_account NUMBER
662 , x_encumbrance_account NUMBER
663 , x_attribute_category VARCHAR2
664 , x_attribute1 VARCHAR2
665 , x_attribute2 VARCHAR2
666 , x_attribute3 VARCHAR2
667 , x_attribute4 VARCHAR2
668 , x_attribute5 VARCHAR2
669 , x_attribute6 VARCHAR2
670 , x_attribute7 VARCHAR2
671 , x_attribute8 VARCHAR2
672 , x_attribute9 VARCHAR2
673 , x_attribute10 VARCHAR2
674 , x_attribute11 VARCHAR2
675 , x_attribute12 VARCHAR2
676 , x_attribute13 VARCHAR2
677 , x_attribute14 VARCHAR2
678 , x_attribute15 VARCHAR2
679 , x_preprocessing_lead_time NUMBER
680 , x_processing_lead_time NUMBER
681 , x_postprocessing_lead_time NUMBER
682 , x_demand_class VARCHAR2
683 , x_project_id NUMBER
684 , x_task_id NUMBER
685 , x_subinventory_usage NUMBER
686 , x_notify_list_id NUMBER
687 , x_depreciable_flag NUMBER
688 , x_location_id NUMBER
689 , x_status_id NUMBER
690 , x_default_loc_status_id NUMBER
691 , x_lpn_controlled_flag NUMBER
692 , x_default_cost_group_id NUMBER
693 /* As per bug 1584641 */
694 -- ,X_pick_methodology NUMBER
695 , x_pick_uom_code VARCHAR2
696 , x_cartonization_flag NUMBER
697 , x_planning_level NUMBER DEFAULT 2
698 , x_default_count_type_code NUMBER DEFAULT 2
702 , x_enforce_alias_uniqueness VARCHAR2 DEFAULT 'N'
699 , x_subinventory_type NUMBER DEFAULT 1--RCVLOCATORSSUPPORT
700 , x_enable_bulk_pick VARCHAR2 DEFAULT 'N'
701 , x_enable_locator_alias VARCHAR2 DEFAULT 'N'
703 , x_enable_opp_cyc_count_flag VARCHAR2 DEFAULT 'N' -- Added for Opp Cyc Counting bug#9248808
704 , x_opp_cyc_count_header_id NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
705 , x_opp_cyc_count_quantity NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
706 , x_opp_cyc_count_days NUMBER DEFAULT NULL -- Added for Opp Cyc Counting bug#9248808
707 ) IS
708 l_status_id NUMBER;
709 BEGIN
710 SELECT status_id
711 INTO l_status_id
712 FROM mtl_secondary_inventories
713 WHERE ROWID = x_rowid;
714
715 UPDATE mtl_secondary_inventories
716 SET secondary_inventory_name = x_secondary_inventory_name
717 , organization_id = x_organization_id
718 , last_update_date = x_last_update_date
719 , last_updated_by = x_last_updated_by
720 , last_update_login = x_last_update_login
721 , description = x_description
722 , disable_date = x_disable_date
723 , inventory_atp_code = x_inventory_atp_code
724 , availability_type = x_availability_type
725 , reservable_type = x_reservable_type
726 , locator_type = x_locator_type
727 , picking_order = x_picking_order
728 , dropping_order = x_dropping_order
729 , material_account = x_material_account
730 , material_overhead_account = x_material_overhead_account
731 , resource_account = x_resource_account
732 , overhead_account = x_overhead_account
733 , outside_processing_account = x_outside_processing_account
734 , quantity_tracked = x_quantity_tracked
735 , asset_inventory = x_asset_inventory
736 , source_type = x_source_type
737 , source_subinventory = x_source_subinventory
738 , source_organization_id = x_source_organization_id
739 , requisition_approval_type = x_requisition_approval_type
740 , expense_account = x_expense_account
741 , encumbrance_account = x_encumbrance_account
742 , attribute_category = x_attribute_category
743 , attribute1 = x_attribute1
744 , attribute2 = x_attribute2
745 , attribute3 = x_attribute3
746 , attribute4 = x_attribute4
747 , attribute5 = x_attribute5
748 , attribute6 = x_attribute6
749 , attribute7 = x_attribute7
750 , attribute8 = x_attribute8
751 , attribute9 = x_attribute9
752 , attribute10 = x_attribute10
753 , attribute11 = x_attribute11
754 , attribute12 = x_attribute12
755 , attribute13 = x_attribute13
756 , attribute14 = x_attribute14
757 , attribute15 = x_attribute15
758 , preprocessing_lead_time = x_preprocessing_lead_time
759 , processing_lead_time = x_processing_lead_time
760 , postprocessing_lead_time = x_postprocessing_lead_time
761 , demand_class = x_demand_class
762 , project_id = x_project_id
763 , task_id = x_task_id
764 , subinventory_usage = x_subinventory_usage
765 , notify_list_id = x_notify_list_id
766 , depreciable_flag = x_depreciable_flag
767 , location_id = x_location_id
768 , status_id = x_status_id
769 , default_loc_status_id = x_default_loc_status_id
770 , lpn_controlled_flag = x_lpn_controlled_flag
771 , default_cost_group_id = x_default_cost_group_id
772 /* As per bug 1584641 */
773 -- , pick_methodology = X_pick_methodology
774 , pick_uom_code = x_pick_uom_code
775 , cartonization_flag = x_cartonization_flag
776 , planning_level = x_planning_level
777 , default_count_type_code = x_default_count_type_code
778 , subinventory_type = x_subinventory_type
779 , enable_bulk_pick = x_enable_bulk_pick
780 , enable_locator_alias = x_enable_locator_alias
781 , enforce_alias_uniqueness = x_enforce_alias_uniqueness
782 , enable_opp_cyc_count = x_enable_opp_cyc_count_flag -- Added for Opp Cyc Counting bug#9248808
783 , opp_cyc_count_header_id = x_opp_cyc_count_header_id -- Added for Opp Cyc Counting bug#9248808
784 , opp_cyc_count_quantity = x_opp_cyc_count_quantity -- Added for Opp Cyc Counting bug#9248808
785 , opp_cyc_count_days = x_opp_cyc_count_days -- Added for Opp Cyc Counting bug#9248808
786 WHERE ROWID = x_rowid;
787
788 /* WMS Material Status Enhancements
789 This Procedure Caters to the insertion of records in the
790 table MTL_MATERIAL_STATUS_HISTORY. */
791 /* Commenting this code because for status history we want to capture from where
792 status was updated (Desktop or Mobile) and so we will make a call to this procedure
793 explicitly instead of calling it indirectly
794 Bug # 1695432
795
796 IF (INV_INSTALL.ADV_INV_INSTALLED(P_Organization_ID => NULL)) AND
797 (X_Status_ID IS NOT NULL) AND
798 (X_Status_ID <> l_status_id) THEN
799 Status_History ( X_Organization_ID,
800 NULL,
801 NULL,
802 NULL,
803 2,
804 X_Status_ID,
805 X_Secondary_Inventory_Name,
806 NULL,
807 X_Last_Update_Date,
808 X_Last_Updated_By,
809 X_Last_Updated_By,
810 X_Last_Update_Date,
811 X_Last_Update_Login,
812 NULL
813 );
814 END IF;
815 */
816 IF (SQL%NOTFOUND) THEN
817 RAISE NO_DATA_FOUND;
818 END IF;
822 BEGIN
819 END update_row;
820
821 PROCEDURE delete_row(x_rowid VARCHAR2) IS
823 DELETE FROM mtl_secondary_inventories
824 WHERE ROWID = x_rowid;
825
826 IF (SQL%NOTFOUND) THEN
827 RAISE NO_DATA_FOUND;
828 END IF;
829 END delete_row;
830
831 /* WMS Material Status Enhancements
832 This Procedure Caters to the insertion of records in the
833 table MTL_MATERIAL_STATUS_HISTORY. */
834 PROCEDURE status_history(
835 x_organization_id NUMBER
836 , x_inventory_item_id NUMBER
837 , x_lot_number VARCHAR2
838 , x_serial_number VARCHAR2
839 , x_update_method NUMBER
840 , x_status_id NUMBER
841 , x_zone_code VARCHAR2
842 , x_locator_id NUMBER
843 , x_creation_date DATE
844 , x_created_by NUMBER
845 , x_last_updated_by NUMBER
846 , x_last_update_date DATE
847 , x_last_update_login NUMBER
848 , x_initial_status_flag VARCHAR2 DEFAULT NULL
849 , x_from_mobile_apps_flag VARCHAR2 DEFAULT NULL
850 , x_pending_status NUMBER DEFAULT 0 --ERES Deferred
851 ) IS
852 p_status inv_material_status_pub.mtl_status_update_rec_type;
853 BEGIN
854 p_status.organization_id := x_organization_id;
855 p_status.inventory_item_id := x_inventory_item_id;
856 p_status.lot_number := x_lot_number;
857 p_status.serial_number := x_serial_number;
858 p_status.update_method := x_update_method;
859 p_status.status_id := x_status_id;
860 p_status.zone_code := x_zone_code;
861 p_status.locator_id := x_locator_id;
862 p_status.creation_date := x_creation_date;
863 p_status.created_by := x_created_by;
864 /* p_Status.Last_Updated_By := X_Last_Updated_By; */
865 p_status.last_update_date := x_last_update_date;
866 p_status.last_update_login := x_last_update_login;
867 p_status.initial_status_flag := x_initial_status_flag;
868 p_status.from_mobile_apps_flag := x_from_mobile_apps_flag;
869 p_status.pending_status := x_pending_status;
870 -- Bug# 1695432
871 inv_material_status_pkg.insert_status_history(p_status);
872 END status_history;
873
874 FUNCTION get_miss_num
875 RETURN NUMBER IS
876 BEGIN
877 RETURN fnd_api.g_miss_num;
878 END;
879 END mtl_secondary_inventories_pkg;