[Home] [Help]
30: , p_transaction_identifier IN NUMBER
31: ) IS
32:
33: p_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE := null;
34: p_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE := null;
35: p_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE :=null;
36: p_revision MTL_MATERIAL_TRANSACTIONS_TEMP.REVISION%TYPE := null;
37: p_qty MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_QUANTITY%TYPE := null;
38: p_uom MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_UOM%TYPE := null;
526: SELECT distinct(wph.parent_package_id), wph.lpn_id, wph1.content_volume_uom_code, wph1.content_volume,
527: wph1.gross_weight_uom_code, wph1.gross_weight, wph.inventory_item_id, wph1.parent_package_id,
528: wph1.pack_level, wph.header_id,wph.packaging_mode, wph1.tare_weight, wph1.tare_weight_uom_code,
529: msik.concatenated_segments container_item, lpn.license_plate_number, wph2.pack_level
530: FROM wms_packaging_hist wph, wms_packaging_hist wph1, wms_packaging_hist wph2, mtl_system_items_kfv msik,
531: WMS_LICENSE_PLATE_NUMBERS lpn
532: WHERE wph.rowid in (select rowid
533: from wms_packaging_hist
534: where pack_level = 0
545: SELECT /*+ rowid(WPH) */ distinct(wph.parent_package_id), wph.lpn_id, wph1.content_volume_uom_code, wph1.content_volume,
546: wph1.gross_weight_uom_code, wph1.gross_weight, wph.inventory_item_id, wph1.parent_package_id,
547: wph1.pack_level, wph.header_id,wph.packaging_mode, wph1.tare_weight, wph1.tare_weight_uom_code,
548: msik.concatenated_segments container_item,lpn.license_plate_number
549: FROM wms_packaging_hist wph, wms_packaging_hist wph1, mtl_system_items_kfv
550: msik, WMS_LICENSE_PLATE_NUMBERS lpn
551: WHERE wph.rowid in (select /*+ cardinality(1) */ rowid
552: from wms_packaging_hist
553: where pack_level = 0
649: FROM WMS_LICENSE_PLATE_NUMBERS lpn
650: , WMS_PACKAGING_HIST wph
651: , WMS_LICENSE_PLATE_NUMBERS plpn
652: , WMS_LICENSE_PLATE_NUMBERS olpn
653: , MTL_SYSTEM_ITEMS_KFV msik
654: , DUAL d
655: WHERE d.dummy = 'X'
656: AND lpn.license_plate_number (+) <> NVL('@@@',d.dummy)
657: AND lpn.lpn_id (+) = p_lpn_id
758: , mtlt. hold_date
759: , mtlt.expiration_action_code
760: , mtlt.supplier_lot_number
761: FROM mtl_parameters mp
762: , mtl_system_items_kfv msik
763: , mtl_transaction_lots_temp mtlt
764: , mtl_material_transactions_temp mmtt
765: , po_hazard_classes poh
766: , mtl_material_statuses_vl mmsv
867: , mln.hold_date
868: , mln.expiration_action_code
869: , mln.supplier_lot_number -- invconv fabdi end
870: FROM mtl_parameters mp
871: , mtl_system_items_kfv msik
872: , mtl_lot_numbers mln
873: , po_hazard_classes poh
874: , mtl_material_statuses_vl mmsv
875: WHERE msik.inventory_item_id = p_item_id
1008: -- p_rev..p_lot..p_qty, p_uom, p_cg., l_subinventory, l_locator_id.are always null
1009: -- remove nvl(.) for those parameters
1010: --Bug#8366557 Added hints to following cursor
1011: CURSOR c_lpn_item_content_cart(p_lpn_id NUMBER, p_package_id NUMBER, p_item_id NUMBER) IS
1012: SELECT /*+ ORDERED index(MMTT MTL_MATERIAL_TRANS_TEMP_U1) rowid(WPC) use_nl(WPC MMTT MSI CSG MILKFV) index(MSI MTL_SYSTEM_ITEMS_B_U1)*/
1013: wpc.organization_id organization_id
1014: , wpc.inventory_item_id inventory_item_id
1015: , wpc.revision revision
1016: , wpc.lot_number lot_number
1024: , l_secondary_transaction_qty secondary_quantity -- invconv fabdi
1025: , l_secondary_uom_code secondary_uom -- invconv fabdi
1026: FROM wms_packaging_hist wpc
1027: , mtl_material_transactions_temp mmtt
1028: , mtl_system_items msi
1029: , cst_cost_groups ccg
1030: , mtl_item_locations milkfv
1031: -- Bug 4137707, Do not need to include this where clause,
1032: -- This will be controlled when opening this cursor