[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mrh.replenishment_count_name
, mrh.replenishment_header_id
, mrh.subinventory_code
, mrh.count_date
, mrh.supply_cutoff_date
, mrh.requisition_approval_type
, mrh.process_status
, mrh.process_mode
, mrh.count_mode
, mrh.error_flag
, mrh.request_id
, mrh.delivery_location_id
, mrh.default_line_items
, mrh.default_count_type_code
, ml.meaning default_count_type
FROM mtl_replenish_headers mrh
, mtl_secondary_inventories msi
, mfg_lookups ml
WHERE mrh.organization_id = p_organization_id
AND mrh.subinventory_code = NVL(p_subinventory, mrh.subinventory_code)
AND msi.secondary_inventory_name = mrh.subinventory_code
AND msi.organization_id = mrh.organization_id
AND mrh.process_status = 1
AND mrh.count_mode = 1
AND ml.lookup_type = 'MTL_COUNT_TYPES'
AND mrh.default_count_type_code = ml.lookup_code
AND mrh.replenishment_count_name LIKE p_replenish_count
AND((msi.planning_level = 1
AND NOT EXISTS(SELECT 1
FROM mtl_replenish_lines mrl
WHERE mrl.replenishment_header_id = mrh.replenishment_header_id
AND mrl.locator_id IS NULL
)
)
OR(msi.planning_level = 2
AND NOT EXISTS(SELECT 1
FROM mtl_replenish_lines mrl
WHERE mrl.replenishment_header_id = mrh.replenishment_header_id
AND mrl.locator_id IS NOT NULL
)
)
)
ORDER BY mrh.replenishment_count_name;
SELECT msi.secondary_inventory_name
, msi.locator_type
, msi.description
, msi.asset_inventory
, msi.quantity_tracked
, msi.planning_level
, msi.enable_locator_alias
FROM mtl_secondary_inventories msi
WHERE msi.organization_id = p_organization_id
AND secondary_inventory_name LIKE p_subinventory
AND TRUNC(NVL(disable_date, SYSDATE + 1)) > TRUNC(SYSDATE)
AND NVL(subinventory_type, 1) = 1
AND(( msi.planning_level = 2
AND EXISTS(SELECT 1
FROM mtl_item_sub_inventories mis
WHERE mis.organization_id = msi.organization_id
AND mis.secondary_inventory = msi.secondary_inventory_name
)
)
OR( msi.planning_level = 1
AND EXISTS(SELECT 1
FROM mtl_secondary_locators msl
WHERE msl.organization_id = msi.organization_id
AND msl.subinventory_code = msi.secondary_inventory_name
)
)
)
AND EXISTS(SELECT 1
FROM mtl_replenish_headers mrh
WHERE mrh.subinventory_code = msi.secondary_inventory_name
AND mrh.organization_id= msi.organization_id
AND mrh.count_mode = 1
AND mrh.process_status = 1
)
ORDER BY secondary_inventory_name;
SELECT milk.inventory_location_id
, INV_PROJECT.GET_LOCATOR(milk.inventory_location_id, milk.organization_id) -- Bug 6798138
, milk.description
FROM mtl_item_locations_kfv milk
WHERE milk.organization_id = p_organization_id
AND milk.subinventory_code = p_subinventory
-- AND milk.inventory_location_id = NVL(milk.physical_location_id, milk.inventory_location_id) -- Commented for Bug 6798138
AND NVL(milk.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND milk.concatenated_segments LIKE(p_locator||'%')
AND EXISTS( SELECT 1
FROM mtl_secondary_locators msl
, mtl_replenish_lines mrl
WHERE msl.secondary_locator = milk.inventory_location_id
AND msl.organization_id = milk.organization_id
AND mrl.replenishment_header_id = p_replenish_header_id
AND mrl.locator_id = msl.secondary_locator
AND mrl.inventory_item_id = msl.inventory_item_id
AND mrl.organization_id = msl.organization_id
AND mrl.count_quantity IS NULL
AND mrl.error_flag IS NULL
AND (mrl.count_type_code IS NULL
OR mrl.count_type_code = 2
OR (mrl.count_type_code = 1 AND p_qty_tracked = 2 AND msl.maximum_quantity IS NOT NULL)
)
)
ORDER BY milk.picking_order, milk.concatenated_segments;
SELECT milk.inventory_location_id
, INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id)
, milk.description
FROM mtl_item_locations_kfv milk
WHERE milk.organization_id = p_organization_id
AND milk.subinventory_code = p_subinventory
AND milk.inventory_location_id = NVL(milk.physical_location_id, milk.inventory_location_id)
AND NVL(milk.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND milk.alias = p_alias
AND EXISTS( SELECT 1
FROM mtl_secondary_locators msl
, mtl_replenish_lines mrl
WHERE msl.secondary_locator = milk.inventory_location_id
AND msl.organization_id = milk.organization_id
AND mrl.replenishment_header_id = p_replenish_header_id
AND mrl.locator_id = msl.secondary_locator
AND mrl.inventory_item_id = msl.inventory_item_id
AND mrl.organization_id = msl.organization_id
AND mrl.count_quantity IS NULL
AND mrl.error_flag IS NULL
AND (mrl.count_type_code IS NULL
OR mrl.count_type_code = 2
OR (mrl.count_type_code = 1 AND p_qty_tracked = 2 AND msl.maximum_quantity IS NOT NULL)
)
)
ORDER BY milk.picking_order, milk.concatenated_segments;
SELECT concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(revision_qty_control_code, 1)
, NVL(lot_control_code, 1)
, NVL(serial_number_control_code, 1)
, NVL(restrict_subinventories_code, 2)
, NVL(restrict_locators_code, 2)
, NVL(location_control_code, 1)
, primary_uom_code
, NVL(inspection_required_flag, 'N')
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, 0
, 0
, NVL(default_serial_status_id, 1)
, NVL(serial_status_enabled, 'N')
, NVL(default_lot_status_id, 0)
, NVL(lot_status_enabled, 'N')
, ''
, 'N'
, inventory_item_flag
, 0,
-- , inventory_asset_flag, '',
--Additional Fields for Process Convergence, INVCONV , NSRIVAST
NVL(GRADE_CONTROL_FLAG,'N'),
NVL(DEFAULT_GRADE,''),
NVL(EXPIRATION_ACTION_INTERVAL,0),
NVL(EXPIRATION_ACTION_CODE,''),
NVL(HOLD_DAYS,0),
NVL(MATURITY_DAYS,0),
NVL(RETEST_INTERVAL,0),
NVL(COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(CHILD_LOT_FLAG,'N'),
NVL(CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(LOT_DIVISIBLE_FLAG,'Y'),
NVL(SECONDARY_UOM_CODE,''),
NVL(SECONDARY_DEFAULT_IND,''),
NVL(TRACKING_QUANTITY_IND,'P'),
NVL(DUAL_UOM_DEVIATION_HIGH,0),
NVL(DUAL_UOM_DEVIATION_LOW,0)
-- INVCONV , NSRIVAST, END
FROM mtl_system_items_kfv msik
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_flag = 'Y'
AND msik.stock_enabled_flag = 'Y'
AND msik.concatenated_segments LIKE p_item
AND ((p_locator_id IS NULL
AND EXISTS(SELECT 1
FROM mtl_item_sub_inventories mis
, mtl_replenish_lines mrl
WHERE mis.organization_id = msik.organization_id
AND mis.secondary_inventory = p_subinventory
AND mis.inventory_item_id = msik.inventory_item_id
AND mrl.replenishment_header_id = p_replenish_header_id
AND mrl.inventory_item_id = mis.inventory_item_id
AND mrl.count_quantity IS NULL
AND mrl.error_flag IS NULL
AND (mrl.count_type_code = 2
OR (mrl.count_type_code = 1 AND p_qty_tracked = 2 AND mis.inventory_planning_code = 2)
)
)
)
OR (p_locator_id IS NOT NULL
AND EXISTS(SELECT 1
FROM mtl_secondary_locators msl
, mtl_replenish_lines mrl
WHERE msl.secondary_locator = p_locator_id
AND msl.inventory_item_id = msik.inventory_item_id
AND msl.organization_id = msik.organization_id
AND mrl.replenishment_header_id = p_replenish_header_id
AND mrl.locator_id = msl.secondary_locator
AND mrl.inventory_item_id = msl.inventory_item_id
AND mrl.count_quantity IS NULL
AND mrl.error_flag IS NULL
AND (mrl.count_type_code = 2
OR (mrl.count_type_code = 1 AND p_qty_tracked = 2 AND msl.maximum_quantity IS NOT NULL)
)
)
)
)
ORDER BY concatenated_segments;
SELECT lookup_code
, meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_COUNT_TYPES'
AND lookup_code IN (1,2)
AND enabled_flag = 'Y'
AND meaning LIKE p_count_type
AND TRUNC(NVL(end_date_active, SYSDATE + 1)) > TRUNC(SYSDATE)
AND TRUNC(NVL(start_date_active, SYSDATE - 1)) <= TRUNC(SYSDATE)
ORDER BY meaning;
SELECT lookup_code
, meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_COUNT_TYPES'
AND lookup_code = 2
AND enabled_flag = 'Y'
AND meaning LIKE p_count_type
AND TRUNC(NVL(end_date_active, SYSDATE + 1)) > TRUNC(SYSDATE)
AND TRUNC(NVL(start_date_active, SYSDATE - 1)) <= TRUNC(SYSDATE)
ORDER BY meaning;