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