[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
:= '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''), '
|| 'null, '
|| '''N'', '
|| 'inventory_item_flag, '
|| '0,'
|| 'wms_deploy.get_item_client_name(msik.inventory_item_id),'
|| 'inventory_asset_flag,'
|| 'outside_operation_flag,'
--Bug No 3952081
--Additional Fields for Process Convergence
|| '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),'
|| 'stock_enabled_flag';
:= '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''), '
|| 'mcr.cross_reference, '
|| '''N'', '
|| 'inventory_item_flag, '
|| '0,'
|| 'wms_deploy.get_item_client_name(msik.inventory_item_id),'
|| 'inventory_asset_flag,'
|| 'outside_operation_flag,'
--Bug No 3952081
--Additional Fields for Process Convergence
|| '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),'
|| 'stock_enabled_flag';
:= 'select distinct msik.concatenated_segments,'
|| 'msik.inventory_item_id, msik.description,'
|| 'Nvl(msik.revision_qty_control_code,1),'
|| 'Nvl(msik.lot_control_code, 1),'
|| 'Nvl(msik.serial_number_control_code, 1),'
|| 'Nvl(msik.restrict_subinventories_code, 2),'
|| 'Nvl(msik.restrict_locators_code, 2),'
|| 'Nvl(msik.location_control_code, 1),'
|| ' msik.primary_uom_code,'
|| 'Nvl(msik.inspection_required_flag, ''N''),'
|| 'Nvl(msik.shelf_life_code, 1),'
|| 'Nvl(msik.shelf_life_days,0),'
|| 'Nvl(msik.allowed_units_lookup_code, 2),'
|| 'Nvl(msik.effectivity_control,1), '
|| '0, 0,'
|| 'Nvl(msik.default_serial_status_id,1), '
|| 'Nvl(msik.serial_status_enabled,''N''), '
|| 'Nvl(msik.default_lot_status_id,0), '
|| 'Nvl(msik.lot_status_enabled,''N''), '
|| 'null, '
|| '''N'', '
|| ' msik.inventory_item_flag, '
|| '0,'
|| 'wms_deploy.get_item_client_name(msik.inventory_item_id),'
|| ' msik.inventory_asset_flag,'
|| ' msik.outside_operation_flag,'
--Bug No 3952081
--Additional Fields for Process Convergence
|| 'NVL(msik.GRADE_CONTROL_FLAG,''N''),'
|| 'NVL(msik.DEFAULT_GRADE,''''),'
|| 'NVL(msik.EXPIRATION_ACTION_INTERVAL,0),'
|| 'NVL(msik.EXPIRATION_ACTION_CODE,''''),'
|| 'NVL(msik.HOLD_DAYS,0),'
|| 'NVL(msik.MATURITY_DAYS,0),'
|| 'NVL(msik.RETEST_INTERVAL,0),'
|| 'NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_VALIDATION_FLAG,''N''),'
|| 'NVL(msik.LOT_DIVISIBLE_FLAG,''Y''),'
|| 'NVL(msik.SECONDARY_UOM_CODE,''''),'
|| 'NVL(msik.SECONDARY_DEFAULT_IND,''''),'
|| 'NVL(msik.TRACKING_QUANTITY_IND,''P''),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_LOW,0)';
:= 'select distinct msik.concatenated_segments,'
|| 'msik.inventory_item_id, msik.description,'
|| 'Nvl(msik.revision_qty_control_code,1),'
|| 'Nvl(msik.lot_control_code, 1),'
|| 'Nvl(msik.serial_number_control_code, 1),'
|| 'Nvl(msik.restrict_subinventories_code, 2),'
|| 'Nvl(msik.restrict_locators_code, 2),'
|| 'Nvl(msik.location_control_code, 1),'
|| ' msik.primary_uom_code,'
|| 'Nvl(msik.inspection_required_flag, ''N''),'
|| 'Nvl(msik.shelf_life_code, 1),'
|| 'Nvl(msik.shelf_life_days,0),'
|| 'Nvl(msik.allowed_units_lookup_code, 2),'
|| 'Nvl(msik.effectivity_control,1), '
|| '0, 0,'
|| 'Nvl(msik.default_serial_status_id,1), '
|| 'Nvl(msik.serial_status_enabled,''N''), '
|| 'Nvl(msik.default_lot_status_id,0), '
|| 'Nvl(msik.lot_status_enabled,''N''), '
|| 'null, '
|| '''N'', '
|| ' msik.inventory_item_flag, '
|| '0,'
|| 'wms_deploy.get_item_client_name(msik.inventory_item_id),'
|| ' msik.inventory_asset_flag,'
|| ' msik.outside_operation_flag,'
--Additional Fields for Process Convergence
|| 'NVL(msik.GRADE_CONTROL_FLAG,''N''),'
|| 'NVL(msik.DEFAULT_GRADE,''''),'
|| 'NVL(msik.EXPIRATION_ACTION_INTERVAL,0),'
|| 'NVL(msik.EXPIRATION_ACTION_CODE,''''),'
|| 'NVL(msik.HOLD_DAYS,0),'
|| 'NVL(msik.MATURITY_DAYS,0),'
|| 'NVL(msik.RETEST_INTERVAL,0),'
|| 'NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_VALIDATION_FLAG,''N''),'
|| 'NVL(msik.LOT_DIVISIBLE_FLAG,''Y''),'
|| 'NVL(msik.SECONDARY_UOM_CODE,''''),'
|| 'NVL(msik.SECONDARY_DEFAULT_IND,''''),'
|| 'NVL(msik.TRACKING_QUANTITY_IND,''P''),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_LOW,0)';
AND moqd.locator_id in (select inventory_location_id from mtl_item_locations mil where mil.organization_id = ' || p_organization_id ||'
AND mil.physical_location_id = ' || p_locator_id || '
and mil.subinventory_code = ''' ||p_subinventory_code||''')
AND msik.concatenated_segments like :l_conc_seg ' || ' AND msik.organization_id = moqd.organization_id AND msik.inventory_item_id = moqd.inventory_item_id ' || l_where_clause;
SELECT concatenated_segments,
inventory_item_id,
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,
wms_deploy.get_item_client_name(inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl /* Bug 5581528 */
WHERE organization_id = p_organization_id
AND concatenated_segments LIKE nvl(l_concatenated_segments,concatenated_segments)
AND inventory_item_id in (select inventory_item_id from mtl_txn_request_lines where header_id =to_number(p_header_id))
--Changes for GTIN
UNION
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'),
mcr.cross_reference,
'N',
inventory_item_flag,
0,
wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT msik.concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0
, 0
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.organization_id = p_organization_id
AND msik.mtl_transactions_enabled_flag = 'Y'
AND ((p_transaction_action_id=3 AND EXISTS (SELECT 1
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id=msik.inventory_item_id
AND msib.organization_id = p_to_organization_id
AND msib.mtl_transactions_enabled_flag = 'Y'
AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)
AND (NVL(msik.serial_number_control_code,1) IN (2,5) OR nvl(msib.serial_number_control_code,1) IN (1,6))
AND (NVL(msik.revision_qty_control_code,1)=2 OR NVL(msib.revision_qty_control_code,1)=1)))
OR (p_transaction_action_id<>3 AND EXISTS (SELECT 1
FROM mtl_system_items_b msib1
WHERE msib1.inventory_item_id=msik.inventory_item_id
AND msib1.organization_id=p_to_organization_id
AND msib1.mtl_transactions_enabled_flag='Y')))
AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
-- Changes for GTIN
UNION
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, 2)
, 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, 0)
, NVL(serial_status_enabled, 'N')
, NVL(default_lot_status_id, 0)
, NVL(lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
WHERE msik.organization_id = p_organization_id
AND msik.mtl_transactions_enabled_flag = 'Y'
AND ((p_transaction_action_id=3 AND EXISTS (SELECT 1
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id=msik.inventory_item_id
AND msib.organization_id = p_to_organization_id
AND msib.mtl_transactions_enabled_flag ='Y'
AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)
AND (NVL(msik.serial_number_control_code,1) IN (2,5) OR nvl(msib.serial_number_control_code,1) IN (1,6))
AND (NVL(msik.revision_qty_control_code,1)=2 OR NVL(msib.revision_qty_control_code,1)=1)))
OR ((p_transaction_action_id <>3 AND EXISTS(SELECT 1
FROM mtl_system_items_b msib1
WHERE msib1.inventory_item_id=msik.inventory_item_id
AND msib1.organization_id=p_to_organization_id
AND msib1.mtl_transactions_enabled_flag='Y'))))
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id OR mcr.org_independent_flag = 'Y');
SELECT a.revision, a.effectivity_date, NVL(a.description, '')
FROM mtl_item_revisions a
WHERE a.organization_id = p_organization_id
AND a.inventory_item_id = p_inventory_item_id
/* Bug# 8912324: Commented the code below so as to allow unimplemented item
revisions for other transactions except misc issue/ receipt */
-- AND a.implementation_date is not null --BUG 7204523 Added to restrict the revisions that are not yet implemented.
AND a.revision LIKE (p_revision)
AND (p_planning_org_id IS NULL
OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
WHERE moqd.revision = a.revision
AND moqd.organization_id = a.organization_id
AND moqd.inventory_item_id = a.inventory_item_id
AND moqd.planning_organization_id = p_planning_org_id
AND moqd.planning_tp_type = p_planning_tp_type))
AND (p_owning_org_id IS NULL
OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
WHERE moqd.revision = a.revision
AND moqd.organization_id = a.organization_id
AND moqd.inventory_item_id = a.inventory_item_id
AND moqd.owning_organization_id = p_owning_org_id
AND moqd.owning_tp_type = p_owning_tp_type));
SELECT a.revision, a.effectivity_date, NVL(a.description, '')
FROM mtl_item_revisions a
WHERE a.organization_id = p_organization_id
AND a.inventory_item_id = p_inventory_item_id
AND a.implementation_date is not null --BUG 7204523 Added to restrict the revisions that are not yet implemented.
AND a.revision LIKE (p_revision)
AND (p_planning_org_id IS NULL
OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
WHERE moqd.revision = a.revision
AND moqd.organization_id = a.organization_id
AND moqd.inventory_item_id = a.inventory_item_id
AND moqd.planning_organization_id = p_planning_org_id
AND moqd.planning_tp_type = p_planning_tp_type))
AND (p_owning_org_id IS NULL
OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
WHERE moqd.revision = a.revision
AND moqd.organization_id = a.organization_id
AND moqd.inventory_item_id = a.inventory_item_id
AND moqd.owning_organization_id = p_owning_org_id
AND moqd.owning_tp_type = p_owning_tp_type));
SELECT (inv_ui_item_lovs.get_conversion_rate(uom_code,
p_Organization_Id,
p_Inventory_Item_Id)) uom_code_comp
, unit_of_measure
, description
, uom_class
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND uom_code LIKE (l_code)
ORDER BY inv_ui_item_lovs.conversion_order(inv_ui_item_lovs.get_conversion_rate(uom_code,
p_Organization_Id,
p_Inventory_Item_Id)) asc, Upper(uom_code);
SELECT (inv_ui_item_lovs.get_conversion_rate(uom_code,
p_organization_id,
Inventory_Item_Id)) uom_code_comp
, unit_of_measure
, description
, uom_class
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id(+) = p_item_id
AND NVL(uom_type, 3) = NVL(p_uom_type, 3)
AND uom_code LIKE (l_code)
ORDER BY inv_ui_item_lovs.conversion_order(inv_ui_item_lovs.get_conversion_rate(uom_code,
p_organization_id,
Inventory_Item_Id)) asc, Upper(uom_code);
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
WHERE msik.lot_split_enabled = 'Y'
AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND msik.organization_id = mln.organization_id
AND msik.inventory_item_id = mln.inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = p_organization_id
AND (NVL(msik.lot_status_enabled, 'N') = 'N'
OR NOT EXISTS (SELECT 1 FROM mtl_status_transaction_control
WHERE status_id = mln.status_id
AND transaction_type_id = p_transaction_type_id
AND is_allowed = 2))
--Changes for GTIN
UNION
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_lot_numbers mln,
mtl_cross_references mcr
WHERE msik.lot_split_enabled = 'Y'
AND msik.organization_id = mln.organization_id
AND msik.inventory_item_id = mln.inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = p_organization_id
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
AND (NVL(msik.lot_status_enabled, 'N') = 'N'
OR NOT EXISTS (SELECT 1 FROM mtl_status_transaction_control
WHERE status_id = mln.status_id
AND transaction_type_id = p_transaction_type_id
AND is_allowed = 2))
ORDER BY concatenated_segments;
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
WHERE msik.lot_merge_enabled = 'Y'
AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND msik.organization_id = mln.organization_id
AND msik.inventory_item_id = mln.inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = p_organization_id
AND (NVL(msik.lot_status_enabled, 'N') = 'N'
OR NOT EXISTS (SELECT 1 FROM mtl_status_transaction_control
WHERE status_id = mln.status_id
AND transaction_type_id = p_transaction_type_id
AND is_allowed = 2))
--Changes for GTIN
UNION
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_lot_numbers mln,
mtl_cross_references mcr
WHERE msik.lot_merge_enabled = 'Y'
AND msik.organization_id = mln.organization_id
AND msik.inventory_item_id = mln.inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = p_organization_id
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
AND (NVL(msik.lot_status_enabled, 'N') = 'N'
OR NOT EXISTS (SELECT 1 FROM mtl_status_transaction_control
WHERE status_id = mln.status_id
AND transaction_type_id = p_transaction_type_id
AND is_allowed = 2))
ORDER BY concatenated_segments;
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
WHERE msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND msik.organization_id = mln.organization_id
AND msik.inventory_item_id = mln.inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = p_organization_id
AND (NVL(msik.lot_status_enabled, 'N') = 'N'
OR NOT EXISTS (SELECT 1 FROM mtl_status_transaction_control
WHERE status_id = mln.status_id
AND transaction_type_id = p_transaction_type_id
AND is_allowed = 2))
--Changes for GTIN
UNION
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_lot_numbers mln,
mtl_cross_references mcr
WHERE msik.organization_id = mln.organization_id
AND msik.inventory_item_id = mln.inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = p_organization_id
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
AND (NVL(msik.lot_status_enabled, 'N') = 'N'
OR NOT EXISTS (SELECT 1 FROM mtl_status_transaction_control
WHERE status_id = mln.status_id
AND transaction_type_id = p_transaction_type_id
AND is_allowed = 2))
ORDER BY concatenated_segments;
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND msik.lot_control_code = 2
AND msik.organization_id = p_organization_id
--Changes for GTIN
UNION
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_cross_references mcr
WHERE msik.lot_control_code = 2
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
WHERE msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND msik.organization_id = mln.organization_id
AND msik.inventory_item_id = mln.inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = p_organization_id
--Changes for GTIN
UNION
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, null,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_lot_numbers mln,
mtl_cross_references mcr
WHERE msik.organization_id = mln.organization_id
AND msik.inventory_item_id = mln.inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = p_organization_id
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
SELECT NVL(msik.revision_qty_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.lot_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
INTO x_revision_qty_control_code
, x_serial_number_control_code
, x_restrict_subinventories_code
, x_restrict_locators_code
, x_location_control_code
, x_primary_uom_code
, x_shelf_life_code
, x_shelf_life_days
, x_allowed_units_lookup_code
, x_lot_status_enabled
, x_default_lot_status_id
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.lot_split_enabled = 'Y'
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id;
SELECT NVL(msik.revision_qty_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.lot_status_enabled, 'N') -- nsinghi bug#5475282
, NVL(msik.default_lot_status_id, 0) -- nsinghi bug#5475282
INTO x_revision_qty_control_code
, x_serial_number_control_code
, x_restrict_subinventories_code
, x_restrict_locators_code
, x_location_control_code
, x_primary_uom_code
, x_shelf_life_code
, x_shelf_life_days
, x_allowed_units_lookup_code
, x_lot_status_enabled -- nsinghi bug#5475282
, x_default_lot_status_id -- nsinghi bug#5475282
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.lot_merge_enabled = 'Y'
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id;
SELECT NVL(msik.revision_qty_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.lot_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
INTO x_revision_qty_control_code
, x_serial_number_control_code
, x_restrict_subinventories_code
, x_restrict_locators_code
, x_location_control_code
, x_primary_uom_code
, x_shelf_life_code
, x_shelf_life_days
, x_allowed_units_lookup_code
, x_lot_status_enabled
, x_default_lot_status_id
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.lot_translate_enabled = 'Y'
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id; /*Added bug4096035*/
SELECT NVL(msik.revision_qty_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
INTO x_revision_qty_control_code
, x_serial_number_control_code
, x_restrict_subinventories_code
, x_restrict_locators_code
, x_location_control_code
, x_primary_uom_code
, x_shelf_life_code
, x_shelf_life_days
, x_allowed_units_lookup_code
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id;
SELECT NVL(msik.revision_qty_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.lot_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
INTO x_revision_qty_control_code
, x_serial_number_control_code
, x_restrict_subinventories_code
, x_restrict_locators_code
, x_location_control_code
, x_primary_uom_code
, x_shelf_life_code
, x_shelf_life_days
, x_allowed_units_lookup_code
, x_lot_status_enabled
, x_default_lot_status_id
, x_GRADE_CONTROL_FLAG
, x_DEFAULT_GRADE
, x_EXPIRATION_ACTION_INTERVAL
, x_EXPIRATION_ACTION_CODE
, x_HOLD_DAYS
, x_MATURITY_DAYS
, x_RETEST_INTERVAL
, x_COPY_LOT_ATTRIBUTE_FLAG
, x_CHILD_LOT_FLAG
, x_CHILD_LOT_VALIDATION_FLAG
, x_LOT_DIVISIBLE_FLAG
, x_SECONDARY_UOM_CODE
, x_SECONDARY_DEFAULT_IND
, x_TRACKING_QUANTITY_IND
, x_DUAL_UOM_DEVIATION_HIGH
, x_DUAL_UOM_DEVIATION_LOW
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.lot_split_enabled = 'Y'
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id;
SELECT NVL(msik.revision_qty_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.lot_status_enabled, 'N') -- nsinghi bug#5475282
, NVL(msik.default_lot_status_id, 0), -- nsinghi bug#5475282
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
INTO x_revision_qty_control_code
, x_serial_number_control_code
, x_restrict_subinventories_code
, x_restrict_locators_code
, x_location_control_code
, x_primary_uom_code
, x_shelf_life_code
, x_shelf_life_days
, x_allowed_units_lookup_code
, x_lot_status_enabled -- nsinghi bug#5475282
, x_default_lot_status_id -- nsinghi bug#5475282
, x_GRADE_CONTROL_FLAG
, x_DEFAULT_GRADE
, x_EXPIRATION_ACTION_INTERVAL
, x_EXPIRATION_ACTION_CODE
, x_HOLD_DAYS
, x_MATURITY_DAYS
, x_RETEST_INTERVAL
, x_COPY_LOT_ATTRIBUTE_FLAG
, x_CHILD_LOT_FLAG
, x_CHILD_LOT_VALIDATION_FLAG
, x_LOT_DIVISIBLE_FLAG
, x_SECONDARY_UOM_CODE
, x_SECONDARY_DEFAULT_IND
, x_TRACKING_QUANTITY_IND
, x_DUAL_UOM_DEVIATION_HIGH
, x_DUAL_UOM_DEVIATION_LOW
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.lot_merge_enabled = 'Y'
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id;
SELECT NVL(msik.revision_qty_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.lot_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0),
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
INTO x_revision_qty_control_code
, x_serial_number_control_code
, x_restrict_subinventories_code
, x_restrict_locators_code
, x_location_control_code
, x_primary_uom_code
, x_shelf_life_code
, x_shelf_life_days
, x_allowed_units_lookup_code
, x_lot_status_enabled
, x_default_lot_status_id
, x_GRADE_CONTROL_FLAG
, x_DEFAULT_GRADE
, x_EXPIRATION_ACTION_INTERVAL
, x_EXPIRATION_ACTION_CODE
, x_HOLD_DAYS
, x_MATURITY_DAYS
, x_RETEST_INTERVAL
, x_COPY_LOT_ATTRIBUTE_FLAG
, x_CHILD_LOT_FLAG
, x_CHILD_LOT_VALIDATION_FLAG
, x_LOT_DIVISIBLE_FLAG
, x_SECONDARY_UOM_CODE
, x_SECONDARY_DEFAULT_IND
, x_TRACKING_QUANTITY_IND
, x_DUAL_UOM_DEVIATION_HIGH
, x_DUAL_UOM_DEVIATION_LOW
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.lot_translate_enabled = 'Y'
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id; /*Added bug4096035*/
SELECT NVL(msik.revision_qty_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
INTO x_revision_qty_control_code
, x_serial_number_control_code
, x_restrict_subinventories_code
, x_restrict_locators_code
, x_location_control_code
, x_primary_uom_code
, x_shelf_life_code
, x_shelf_life_days
, x_allowed_units_lookup_code
, x_GRADE_CONTROL_FLAG
, x_DEFAULT_GRADE
, x_EXPIRATION_ACTION_INTERVAL
, x_EXPIRATION_ACTION_CODE
, x_HOLD_DAYS
, x_MATURITY_DAYS
, x_RETEST_INTERVAL
, x_COPY_LOT_ATTRIBUTE_FLAG
, x_CHILD_LOT_FLAG
, x_CHILD_LOT_VALIDATION_FLAG
, x_LOT_DIVISIBLE_FLAG
, x_SECONDARY_UOM_CODE
, x_SECONDARY_DEFAULT_IND
, x_TRACKING_QUANTITY_IND
, x_DUAL_UOM_DEVIATION_HIGH
, x_DUAL_UOM_DEVIATION_LOW
FROM mtl_system_items_vl msik /* Bug 5581528 */
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id;
:= 'SELECT msik.concatenated_segments concatenated_segments'
|| ', msik.inventory_item_id'
|| ', msik.description'
|| ', NVL(msik.revision_qty_control_code, 1)'
|| ', NVL(msik.lot_status_enabled, ''N'')'
|| ', NVL(msik.serial_status_enabled, ''N'')'
|| ', NVL(msik.restrict_subinventories_code, 2)'
|| ', NVL(msik.restrict_locators_code, 2)'
|| ', NVL(msik.location_control_code, 1)'
|| ', msik.primary_uom_code'
|| ', NVL(msik.inspection_required_flag, 2)'
|| ', NVL(msik.shelf_life_code, 1)'
|| ', NVL(msik.shelf_life_days, 0)'
|| ', NVL(msik.allowed_units_lookup_code, 2)'
|| ', NVL(msik.effectivity_control, 1)'
|| ', 0 parentlpnid'
|| ', 0 quantity'
|| ', NVL(msik.default_serial_status_id, 0)'
|| ', NVL(msik.serial_status_enabled, ''N'')'
|| ', NVL(msik.default_lot_status_id, 0)'
|| ', NVL(msik.lot_status_enabled, ''N'')'
|| ', '''''
|| ', ''N'''
|| ', msik.inventory_item_flag'
|| ', 0'
|| ', wms_deploy.get_item_client_name(msik.inventory_item_id),'
--Bug No 3952081
--Additional Fields for Process Convergence
|| 'NVL(msik.GRADE_CONTROL_FLAG,''N''),'
|| 'NVL(msik.DEFAULT_GRADE,''''),'
|| 'NVL(msik.EXPIRATION_ACTION_INTERVAL,0),'
|| 'NVL(msik.EXPIRATION_ACTION_CODE,''''),'
|| 'NVL(msik.HOLD_DAYS,0),'
|| 'NVL(msik.MATURITY_DAYS,0),'
|| 'NVL(msik.RETEST_INTERVAL,0),'
|| 'NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_VALIDATION_FLAG,''N''),'
|| 'NVL(msik.LOT_DIVISIBLE_FLAG,''Y''),'
|| 'NVL(msik.SECONDARY_UOM_CODE,''''),'
|| 'NVL(msik.SECONDARY_DEFAULT_IND,''''),'
|| 'NVL(msik.TRACKING_QUANTITY_IND,''P''),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_LOW,0)';
:= 'SELECT msik.concatenated_segments concatenated_segments'
|| ', msik.inventory_item_id'
|| ', msik.description'
|| ', NVL(msik.revision_qty_control_code, 1)'
|| ', NVL(msik.lot_status_enabled, ''N'')'
|| ', NVL(msik.serial_status_enabled, ''N'')'
|| ', NVL(msik.restrict_subinventories_code, 2)'
|| ', NVL(msik.restrict_locators_code, 2)'
|| ', NVL(msik.location_control_code, 1)'
|| ', msik.primary_uom_code'
|| ', NVL(msik.inspection_required_flag, 2)'
|| ', NVL(msik.shelf_life_code, 1)'
|| ', NVL(msik.shelf_life_days, 0)'
|| ', NVL(msik.allowed_units_lookup_code, 2)'
|| ', NVL(msik.effectivity_control, 1)'
|| ', 0 parentlpnid'
|| ', 0 quantity'
|| ', NVL(msik.default_serial_status_id, 0)'
|| ', NVL(msik.serial_status_enabled, ''N'')'
|| ', NVL(msik.default_lot_status_id, 0)'
|| ', NVL(msik.lot_status_enabled, ''N'')'
|| ', mcr.cross_reference'
|| ', ''N'''
|| ', msik.inventory_item_flag'
|| ', 0'
|| ', wms_deploy.get_item_client_name(msik.inventory_item_id),'
--Bug No 3952081
--Additional Fields for Process Convergence
|| 'NVL(msik.GRADE_CONTROL_FLAG,''N''),'
|| 'NVL(msik.DEFAULT_GRADE,''''),'
|| 'NVL(msik.EXPIRATION_ACTION_INTERVAL,0),'
|| 'NVL(msik.EXPIRATION_ACTION_CODE,''''),'
|| 'NVL(msik.HOLD_DAYS,0),'
|| 'NVL(msik.MATURITY_DAYS,0),'
|| 'NVL(msik.RETEST_INTERVAL,0),'
|| 'NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_VALIDATION_FLAG,''N''),'
|| 'NVL(msik.LOT_DIVISIBLE_FLAG,''Y''),'
|| 'NVL(msik.SECONDARY_UOM_CODE,''''),'
|| 'NVL(msik.SECONDARY_DEFAULT_IND,''''),'
|| 'NVL(msik.TRACKING_QUANTITY_IND,''P''),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_LOW,0)';
SELECT DISTINCT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, wsh_delivery_details dd, wsh_delivery_assignments da, wsh_new_deliveries nd /* Bug 5581528 */
WHERE msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = dd.inventory_item_id
AND nd.delivery_id = p_delivery_id
AND nd.delivery_id = da.delivery_id
AND da.delivery_detail_id = dd.delivery_detail_id
AND (dd.inv_interfaced_flag = 'N' OR dd.inv_interfaced_flag IS NULL)
AND dd.released_status = 'Y'
AND nd.status_code NOT IN ('CO', 'CL', 'IT')
--Changes for GTIN
UNION
SELECT DISTINCT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
wsh_delivery_details dd,
wsh_delivery_assignments da,
wsh_new_deliveries nd,
mtl_cross_references mcr
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = dd.inventory_item_id
AND nd.delivery_id = p_delivery_id
AND nd.delivery_id = da.delivery_id
AND da.delivery_detail_id = dd.delivery_detail_id
AND (dd.inv_interfaced_flag = 'N' OR dd.inv_interfaced_flag IS NULL)
AND dd.released_status = 'Y'
AND nd.status_code NOT IN ('CO', 'CL', 'IT')
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
SELECT concatenated_segments
, inventory_item_id
, 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, 2)
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(default_serial_status_id, 0)
, NVL(serial_status_enabled, 'N')
, NVL(default_lot_status_id, 0)
, NVL(lot_status_enabled, 'N')
, ''
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
--FROM mtl_system_items_vl /* Bug 5581528 */
from mtl_system_items_vl msik /*bug7626228*/
WHERE organization_id = p_organization_id
AND concatenated_segments LIKE (p_concatenated_segments||l_append)
/*AND INV_MATERIAL_STATUS_GRP.loc_valid_for_item(p_locator_id,
p_organization_id,
inventory_item_id,
p_subinventory_code)='Y' --Bug# 2879164
AND INV_MATERIAL_STATUS_GRP.sub_valid_for_item (p_organization_id,
inventory_item_id,
p_subinventory_code)='Y' -- Bug 5500255*/
AND INV_MATERIAL_STATUS_GRP.sub_loc_valid_for_item(p_organization_id,msik.inventory_item_id,p_subinventory_code,p_locator_id,
msik.restrict_subinventories_code,msik.restrict_locators_code)='Y' -- bug7626228
AND msik.stock_enabled_flag = 'Y' -- Added for Bug 6310345
--Changes for GTIN
UNION
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, 2)
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(default_serial_status_id, 0)
, NVL(serial_status_enabled, 'N')
, NVL(default_lot_status_id, 0)
, NVL(lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
AND msik.stock_enabled_flag = 'Y' -- Added for Bug 6310345
ORDER BY concatenated_segments;
SELECT UNIQUE msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, mtl_physical_inventory_tags mpit /* Bug 5581528 */
WHERE msik.organization_id = p_organization_id
AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND msik.inventory_item_id = mpit.inventory_item_id
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.organization_id = p_organization_id
AND mpit.subinventory = p_subinventory_code
AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND NVL(mpit.void_flag, 2) = 2
AND (mpit.adjustment_id IN (SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL) OR mpit.adjustment_id IS NULL)--Bug#9772069
/*AND INV_MATERIAL_STATUS_GRP.loc_valid_for_item(p_locator_id,
p_organization_id,
msik.inventory_item_id,
p_subinventory_code)='Y' --Bug# 2879164
AND INV_MATERIAL_STATUS_GRP.sub_valid_for_item(p_organization_id,
msik.inventory_item_id,
p_subinventory_code)='Y' -- Bug 5500255*/
AND INV_MATERIAL_STATUS_GRP.sub_loc_valid_for_item(p_organization_id,msik.inventory_item_id,p_subinventory_code,p_locator_id,
msik.restrict_subinventories_code,msik.restrict_locators_code)='Y' -- bug7626228
AND msik.stock_enabled_flag = 'Y' -- Added for Bug 6310345
--Changes for GTIN
UNION
SELECT UNIQUE msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, mtl_physical_inventory_tags mpit, mtl_cross_references mcr /* Bug 5581528 */
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = mpit.inventory_item_id
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.organization_id = p_organization_id
AND mpit.subinventory = p_subinventory_code
AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND NVL(mpit.void_flag, 2) = 2
AND (mpit.adjustment_id IN (SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL) OR mpit.adjustment_id IS NULL)--Bug#9772069
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
AND msik.stock_enabled_flag = 'Y' -- Added for Bug 6310345
ORDER BY concatenated_segments;
SELECT revision
, effectivity_date
, NVL(description, '')
FROM mtl_item_revisions
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND revision LIKE (p_revision)
ORDER BY UPPER(revision);
SELECT UNIQUE mir.revision
, mir.effectivity_date
, NVL(mir.description, '')
FROM mtl_item_revisions mir, mtl_physical_inventory_tags mpit
WHERE mir.organization_id = p_organization_id
AND mir.inventory_item_id = p_inventory_item_id
AND mir.revision LIKE (p_revision)
AND mir.inventory_item_id = mpit.inventory_item_id
AND mpit.physical_inventory_id = p_physical_inventory_id
AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND NVL(mpit.void_flag, 2) = 2
AND mpit.adjustment_id IN (SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL)
ORDER BY UPPER(mir.revision);
SELECT (inv_ui_item_lovs.get_conversion_rate(uom_code,
p_Organization_Id,
p_Inventory_Item_Id)) uom_code_comp
, unit_of_measure
, description
, uom_class
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND uom_code LIKE (l_code)
ORDER BY inv_ui_item_lovs.conversion_order(inv_ui_item_lovs.get_conversion_rate(uom_code,
p_Organization_Id,
p_Inventory_Item_Id)) asc, Upper(uom_code);
SELECT DISTINCT concatenated_segments
, inventory_item_id
, 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, 2)
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(default_serial_status_id, 0)
, NVL(serial_status_enabled, 'N')
, NVL(default_lot_status_id, 0)
, NVL(lot_status_enabled, 'N')
, ''
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(inventory_item_id),
inventory_asset_flag, --5591433: Added inventory_asset_flag and outside_processing_flag.
outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl /* Bug 5581528 */
WHERE organization_id = p_organization_id
AND container_item_flag = 'Y'
AND mtl_transactions_enabled_flag = 'Y'
AND concatenated_segments LIKE (p_concatenated_segments||l_append)
--Changes for GTIN
UNION
SELECT DISTINCT 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, 2)
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(default_serial_status_id, 0)
, NVL(serial_status_enabled, 'N')
, NVL(default_lot_status_id, 0)
, NVL(lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
inventory_asset_flag, --5591433: Added inventory_asset_flag and outside_processing_flag.
outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_cross_references mcr
WHERE msik.organization_id = p_organization_id
AND msik.container_item_flag = 'Y'
AND msik.mtl_transactions_enabled_flag = 'Y'
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
SELECT NVL(serial_discrepancy_option, 2)
, NVL(container_discrepancy_option, 2)
INTO l_serial_discrepancy_option
, l_container_discrepancy_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id;
SELECT NVL(serial_count_option, 1)
INTO l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id;
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_cycle_count_items mcci
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = mcci.inventory_item_id
AND mcci.cycle_count_header_id = p_cycle_count_header_id
AND (msik.serial_number_control_code IN (1, 6)
OR (l_serial_count_option <> 1
AND serial_number_control_code NOT IN (1, 6)
)
)
AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
/*AND INV_MATERIAL_STATUS_GRP.loc_valid_for_item(p_locator_id,
p_organization_id,
msik.inventory_item_id,
p_subinventory_code)='Y' --Bug# 3188455 Added this for validating restricted Items to locator and subinventory
AND INV_MATERIAL_STATUS_GRP.sub_valid_for_item(p_organization_id,
msik.inventory_item_id,
p_subinventory_code)='Y' -- Bug 5500255*/
AND INV_MATERIAL_STATUS_GRP.sub_loc_valid_for_item(p_organization_id,msik.inventory_item_id,p_subinventory_code,p_locator_id,
msik.restrict_subinventories_code,msik.restrict_locators_code)='Y' -- Bug7626228
--Changes for GTIN
UNION
SELECT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_cycle_count_items mcci, mtl_cross_references mcr
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = mcci.inventory_item_id
AND mcci.cycle_count_header_id = p_cycle_count_header_id
AND (msik.serial_number_control_code IN (1, 6)
OR (l_serial_count_option <> 1
AND serial_number_control_code NOT IN (1, 6)
)
)
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
SELECT UNIQUE msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, ''
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_cycle_count_entries mcce
WHERE msik.organization_id = p_organization_id
AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND msik.inventory_item_id = mcce.inventory_item_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.organization_id = p_organization_id
-- The sub and loc have to match an existing cycle count entry
-- OR the entry contains an LPN and
-- container discrepancies are allowed
-- OR the item is serial controlled, the cycle count header allows
-- serial items and serial discrepancies are allowed
AND ((mcce.subinventory = p_subinventory_code
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR (mcce.parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
OR (l_serial_count_option <> 1
AND msik.serial_number_control_code NOT IN (1, 6)
AND l_serial_discrepancy_option = 1
)
)
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
--Changes for GTIN
UNION
SELECT UNIQUE msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
, NVL(msik.revision_qty_control_code, 1)
, NVL(msik.lot_control_code, 1)
, NVL(msik.serial_number_control_code, 1)
, NVL(msik.restrict_subinventories_code, 2)
, NVL(msik.restrict_locators_code, 2)
, NVL(msik.location_control_code, 1)
, msik.primary_uom_code
, NVL(msik.inspection_required_flag, 2)
, NVL(msik.shelf_life_code, 1)
, NVL(msik.shelf_life_days, 0)
, NVL(msik.allowed_units_lookup_code, 2)
, NVL(msik.effectivity_control, 1)
, 0 parentlpnid
, 0 quantity
, NVL(msik.default_serial_status_id, 0)
, NVL(msik.serial_status_enabled, 'N')
, NVL(msik.default_lot_status_id, 0)
, NVL(msik.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msik.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msik.GRADE_CONTROL_FLAG,'N'),
NVL(msik.DEFAULT_GRADE,''),
NVL(msik.EXPIRATION_ACTION_INTERVAL,0),
NVL(msik.EXPIRATION_ACTION_CODE,''),
NVL(msik.HOLD_DAYS,0),
NVL(msik.MATURITY_DAYS,0),
NVL(msik.RETEST_INTERVAL,0),
NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msik.CHILD_LOT_FLAG,'N'),
NVL(msik.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msik.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msik.SECONDARY_UOM_CODE,''),
NVL(msik.SECONDARY_DEFAULT_IND,''),
NVL(msik.TRACKING_QUANTITY_IND,'P'),
NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_cycle_count_entries mcce, mtl_cross_references mcr
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = mcce.inventory_item_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.organization_id = p_organization_id
-- The sub and loc have to match an existing cycle count entry
-- OR the entry contains an LPN and
-- container discrepancies are allowed
-- OR the item is serial controlled, the cycle count header allows
-- serial items and serial discrepancies are allowed
AND ((mcce.subinventory = p_subinventory_code
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR (mcce.parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
OR (l_serial_count_option <> 1
AND msik.serial_number_control_code NOT IN (1, 6)
AND l_serial_discrepancy_option = 1
)
)
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
SELECT revision
, effectivity_date
, NVL(description, '')
FROM mtl_item_revisions
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND revision LIKE (p_revision)
ORDER BY UPPER(revision);
SELECT UNIQUE mir.revision
, mir.effectivity_date
, NVL(mir.description, '')
FROM mtl_item_revisions mir, mtl_cycle_count_entries mcce
WHERE mir.organization_id = p_organization_id
AND mir.inventory_item_id = p_inventory_item_id
AND mir.revision LIKE (p_revision)
AND mir.inventory_item_id = mcce.inventory_item_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
ORDER BY UPPER(mir.revision);
SELECT (inv_ui_item_lovs.get_conversion_rate(uom_code,
p_organization_id,
p_inventory_item_id)) uom_code_comp
, unit_of_measure
, description
, uom_class
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND uom_code LIKE (l_code)
ORDER BY inv_ui_item_lovs.conversion_order(inv_ui_item_lovs.get_conversion_rate(uom_code,
p_Organization_Id,
p_Inventory_Item_Id)) asc, Upper(uom_code);
SELECT * FROM ( --BUG12820609 added to make function call only once
SELECT /*+ leading(b) */ DISTINCT a.concatenated_segments --BUG12820609 ordering of MTRL and MSI in from clause
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0 parent_lpn_id --BUG12820609 added alias else outer select won't run
, 0 quantity --BUG12820609 added alias else outer select won't run
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, ''
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('LPN', p_lpn_id, NULL, NULL, p_organization_id, b.inventory_item_id, a.primary_uom_code)) insp_qty --BUG12820609
, wms_deploy.get_item_client_name(a.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_txn_request_lines b , mtl_system_items_vl a /* Bug 5581528 */ --BUG12820609 changed order of MTRL and MSI
WHERE b.lpn_id = p_lpn_id
AND b.organization_id = p_organization_id
AND b.inspection_status is not null -- 8405606
AND b.organization_id = a.organization_id
AND b.inventory_item_id = a.inventory_item_id
AND a.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND Nvl(b.wms_process_flag,-1) <> 2
AND b.line_status <> 5 --BUG12820609 closed MTRL's do not matter in Inspect Item LOV
-- AND ((inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('LPN',p_lpn_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0) --BUG12820609 added outer select to reduce the call see where
) WHERE insp_qty > 0 --BUG12820609 added as function call was reduced to one
--Changes for GTIN
UNION
--BUG12820609 refer changes in above select, same done in below 2 select statements
SELECT * FROM (
SELECT /*+ leading(b) */ DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0 parent_lpn_id
, 0 quantity
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('LPN', p_lpn_id, NULL, NULL, p_organization_id, b.inventory_item_id, a.primary_uom_code)) insp_qty
, wms_deploy.get_item_client_name(a.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_txn_request_lines b, mtl_system_items_vl a, /* Bug 5581528 */
mtl_cross_references mcr
WHERE b.lpn_id = p_lpn_id
AND b.organization_id = p_organization_id
AND b.inspection_status is not null -- 8405606
AND b.organization_id = a.organization_id
AND b.inventory_item_id = a.inventory_item_id
AND Nvl(b.wms_process_flag,-1) <> 2
-- AND ((inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('LPN',p_lpn_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0)
AND a.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = a.organization_id
OR
mcr.org_independent_flag = 'Y')
AND b.line_status <> 5
) WHERE insp_qty > 0;
SELECT * FROM (
SELECT /*+ leading(b) */ DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0 parent_lpn_id
, 0 quantity
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, ''
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('LPN', p_lpn_id, NULL, NULL, p_organization_id, b.inventory_item_id, a.primary_uom_code)) insp_qty
, wms_deploy.get_item_client_name(a.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_txn_request_lines b, mtl_system_items_vl a /* Bug 5581528 */
WHERE b.lpn_id = p_lpn_id
AND b.organization_id = p_organization_id
AND b.inspection_status is not null -- 8405606
AND b.organization_id = a.organization_id
AND b.inventory_item_id = a.inventory_item_id
-- bug#13092838 AND a.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND Nvl(b.wms_process_flag,-1) <> 2
-- AND ((inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('LPN', p_lpn_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0)
AND b.line_status <> 5
) WHERE insp_qty > 0;
SELECT * FROM (
SELECT /*+ leading(b) */ DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0 parent_lpn_id
, 0 quantity
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, ''
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('LPN', p_lpn_id, NULL, NULL, p_organization_id, b.inventory_item_id, a.primary_uom_code)) insp_qty
, wms_deploy.get_item_client_name(a.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_txn_request_lines b, mtl_system_items_vl a /* Bug 5581528 */
WHERE b.lpn_id = p_lpn_id
AND b.organization_id = p_organization_id
AND b.inspection_status is not null -- 8405606
AND b.organization_id = a.organization_id
AND b.inventory_item_id = a.inventory_item_id
AND a.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND Nvl(b.wms_process_flag,-1) <> 2
-- AND ((inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('LPN', p_lpn_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0)
AND b.line_status <> 5
) WHERE insp_qty > 0;
SELECT DISTINCT a.revision
, a.effectivity_date
, NVL(a.description, '')
FROM mtl_item_revisions a, mtl_txn_request_lines b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND b.lpn_id = p_lpn_id
AND b.inspection_status is not null --8405606
AND a.organization_id = b.organization_id
AND a.inventory_item_id = b.inventory_item_id
AND a.revision = b.revision
AND a.revision LIKE (p_revision);
SELECT DISTINCT msi.concatenated_segments
, moq.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(moq.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
WHERE moq.organization_id = p_org_id
AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
AND moq.containerized_flag = 2
AND moq.inventory_item_id = msi.inventory_item_id
AND moq.organization_id = msi.organization_id
AND msi.concatenated_segments LIKE (p_item||l_append)
--Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments
, moq.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(moq.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
mtl_system_items_vl msi, /* Bug 5581528 */
mtl_cross_references mcr
WHERE moq.organization_id = p_org_id
AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
AND moq.containerized_flag = 2
AND moq.inventory_item_id = msi.inventory_item_id
AND moq.organization_id = msi.organization_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT msi.concatenated_segments
, moq.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(moq.inventory_item_id),
NVL(msi.GRADE_CONTROL_FLAG,'N'),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi /* Bug 5581528 */
-- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
WHERE moq.organization_id = p_org_id
AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
AND NVL(msi.container_item_flag, '@') = NVL(p_container_item_flag, NVL(msi.container_item_flag, '@'))
AND moq.inventory_item_id = msi.inventory_item_id
AND moq.organization_id = msi.organization_id
AND concatenated_segments LIKE (p_item||l_append)
--Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments
, moq.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(moq.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
mtl_system_items_vl msi, /* Bug 5581528 */
mtl_cross_references mcr
WHERE moq.organization_id = p_org_id
AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
AND NVL(msi.container_item_flag, '@') = NVL(p_container_item_flag, NVL(msi.container_item_flag, '@'))
AND moq.inventory_item_id = msi.inventory_item_id
AND moq.organization_id = msi.organization_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT msi.concatenated_segments
, wlc.inventory_item_id
, NVL(msi.description, '')
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, NVL(msi.primary_uom_code, '')
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, NVL(wlc.parent_lpn_id, 0)
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(wlc.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi, wms_lpn_contents wlc /* Bug 5581528 */
WHERE wlc.organization_id = p_org_id
AND wlc.parent_lpn_id = TO_NUMBER(p_lpn_id)
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = wlc.organization_id
AND msi.concatenated_segments LIKE (p_item||l_append)
--Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments
, wlc.inventory_item_id
, NVL(msi.description, '')
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, NVL(msi.primary_uom_code, '')
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, NVL(wlc.parent_lpn_id, 0)
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(wlc.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi, /* Bug 5581528 */
wms_lpn_contents wlc,
mtl_cross_references mcr
WHERE wlc.organization_id = p_org_id
AND wlc.parent_lpn_id = TO_NUMBER(p_lpn_id)
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = wlc.organization_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi /* Bug 5581528 */
WHERE msi.organization_id = p_org_id
AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
AND msi.concatenated_segments LIKE (p_item||l_append)
--Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi, /* Bug 5581528 */
mtl_cross_references mcr
WHERE msi.organization_id = p_org_id
AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
WHERE msi.organization_id = p_org_id
AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
AND msi.concatenated_segments LIKE (p_item||l_append)
AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND moq.locator_id = NVL(TO_NUMBER(p_locator_id), moq.locator_id)
AND moq.containerized_flag = 2
AND moq.inventory_item_id = msi.inventory_item_id
AND moq.organization_id = msi.organization_id
--Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
mtl_system_items_vl msi, /* Bug 5581528 */
mtl_cross_references mcr
WHERE msi.organization_id = p_org_id
AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND moq.locator_id = NVL(TO_NUMBER(p_locator_id), moq.locator_id)
AND moq.containerized_flag = 2
AND moq.inventory_item_id = msi.inventory_item_id
AND moq.organization_id = msi.organization_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT concatenated_segments
, inventory_item_id
, 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, 2)
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl /* Bug 5581528 */
WHERE organization_id = p_org_id
AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
AND concatenated_segments LIKE (p_item||l_append)
--Changes for GTIN
UNION
SELECT DISTINCT msik.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, 2)
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_cross_references mcr
WHERE msik.organization_id = p_org_id
AND NVL(msik.container_item_flag, 'N') = NVL(p_container_item_flag, NVL(msik.container_item_flag, 'N'))
AND NVL(msik.serial_number_control_code, 1) IN (1, 2, 5, 6)
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT (inv_ui_item_lovs.get_conversion_rate(wlc.uom_code,
p_Organization_Id,
p_Inventory_Item_Id)) uom_code_comp
, miuv.unit_of_measure
, miuv.description
, miuv.uom_class
FROM mtl_item_uoms_view miuv, wms_lpn_contents wlc
WHERE wlc.organization_id = p_organization_id
AND wlc.inventory_item_id = p_inventory_item_id
AND NVL(wlc.parent_lpn_id, 0) = NVL(p_lpn_id, NVL(wlc.parent_lpn_id, 0))
AND miuv.organization_id = wlc.organization_id
AND miuv.inventory_item_id = wlc.inventory_item_id
AND miuv.uom_code = wlc.uom_code
AND wlc.uom_code LIKE (l_code)
ORDER BY inv_ui_item_lovs.conversion_order(inv_ui_item_lovs.get_conversion_rate(wlc.uom_code,
p_Organization_Id,
p_Inventory_Item_Id)) asc, Upper(wlc.uom_code);
SELECT DISTINCT uom_code
, unit_of_measure
, description
, uom_class
FROM mtl_units_of_measure
WHERE uom_code LIKE (p_uom_code);
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, Nvl(b.item_description, a.description) -- Bug 14645109
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, ''
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('PO', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
WHERE b.to_organization_id = p_organization_id
AND b.po_header_id = p_source_id
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
--bug5708184,in PO Source,add the condition to match with po supplier item.
AND (a.concatenated_segments LIKE (p_concatenated_segments||l_append)
or exists (select pla.vendor_product_num
from po_lines_all pla
where pla.po_header_id=b.po_header_id and
pla.po_line_id=b.po_line_id and
pla.vendor_product_num like (p_concatenated_segments)))
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('PO',
p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
--Changes for GTIN
UNION
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, nvl(b.item_description, a.description) -- Bug 14645109
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('PO', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, /* Bug 5581528 */
rcv_transactions_v b,
mtl_cross_references mcr
WHERE b.to_organization_id = p_organization_id
AND b.po_header_id = p_source_id
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('PO', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
AND a.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = a.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, ''
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('INTSHIP', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
WHERE b.to_organization_id = p_organization_id
AND b.shipment_header_id = p_source_id
AND b.receipt_source_code <> 'VENDOR'
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
AND a.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('INTSHIP',
p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
--Changes for GTIN
UNION
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('INTSHIP', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, /* Bug 5581528 */
rcv_transactions_v b,
mtl_cross_references mcr
WHERE b.to_organization_id = p_organization_id
AND b.shipment_header_id = p_source_id
AND b.receipt_source_code <> 'VENDOR'
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('INTSHIP',
p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
AND a.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = a.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, ''
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('RMA', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
WHERE b.to_organization_id = p_organization_id
AND b.oe_order_header_id = p_source_id
AND b.receipt_source_code <> 'VENDOR'
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
AND a.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('RMA',
p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
--Changes for GTIN
UNION
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('RMA', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, /* Bug 5581528 */
rcv_transactions_v b,
mtl_cross_references mcr
WHERE b.to_organization_id = p_organization_id
AND b.oe_order_header_id = p_source_id
AND b.receipt_source_code <> 'VENDOR'
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('RMA',
p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
AND a.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = a.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, Nvl(b.item_description, a.description) -- Bug 14645109
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, ''
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('RECEIPT', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
WHERE b.to_organization_id = p_organization_id
AND b.shipment_header_id = TO_CHAR(p_source_id)
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
--bug5708184,in Receitp source,add the condition to match with po supplier item
AND (a.concatenated_segments LIKE (p_concatenated_segments||l_append)
or exists (select pla.vendor_product_num
from po_lines_all pla
where pla.po_header_id=b.po_header_id and
pla.po_line_id=b.po_line_id and
pla.vendor_product_num like (p_concatenated_segments)))
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('RECEIPT',
p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
--Changes for GTIN
UNION
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, Nvl(b.item_description, a.description) -- Bug 14645109
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('RECEIPT', p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, /* Bug 5581528 */
rcv_transactions_v b,
mtl_cross_references mcr
WHERE b.to_organization_id = p_organization_id
AND b.shipment_header_id = TO_CHAR(p_source_id)
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('RECEIPT',
p_source_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
AND a.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = a.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, ''
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('INTSHIP', shipment.shipment_header_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, /* Bug 5581528 */
rcv_transactions_v b,
(SELECT DISTINCT rsl.shipment_header_id
FROM po_requisition_lines pol,
rcv_shipment_lines rsl
WHERE pol.requisition_header_id = p_source_id
AND pol.requisition_line_id = rsl.requisition_line_id
) shipment
WHERE b.to_organization_id = p_organization_id
AND b.shipment_header_id = shipment.shipment_header_id
AND b.receipt_source_code <> 'VENDOR'
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
AND a.concatenated_segments LIKE (p_concatenated_segments||l_append)
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('INTSHIP',
shipment.shipment_header_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
--Changes for GTIN
UNION
SELECT DISTINCT a.concatenated_segments
, a.inventory_item_id
, a.description
, NVL(a.revision_qty_control_code, 1)
, NVL(a.lot_control_code, 1)
, NVL(a.serial_number_control_code, 1)
, NVL(a.restrict_subinventories_code, 2)
, NVL(a.restrict_locators_code, 2)
, NVL(a.location_control_code, 1)
, a.primary_uom_code
, NVL(a.inspection_required_flag, 2)
, NVL(a.shelf_life_code, 1)
, NVL(a.shelf_life_days, 0)
, NVL(a.allowed_units_lookup_code, 2)
, NVL(a.effectivity_control, 1)
, 0
, 0
, NVL(a.default_serial_status_id, 0)
, NVL(a.serial_status_enabled, 'N')
, NVL(a.default_lot_status_id, 0)
, NVL(a.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, a.inventory_item_flag
, (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('INTSHIP', shipment.shipment_header_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code))
, wms_deploy.get_item_client_name(a.inventory_item_id)
, a.inventory_asset_flag --5405993: Added inventory_asset_flag and outside_processing_flag.
, a.outside_operation_flag,
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(a.GRADE_CONTROL_FLAG,'N'),
NVL(a.DEFAULT_GRADE,''),
NVL(a.EXPIRATION_ACTION_INTERVAL,0),
NVL(a.EXPIRATION_ACTION_CODE,''),
NVL(a.HOLD_DAYS,0),
NVL(a.MATURITY_DAYS,0),
NVL(a.RETEST_INTERVAL,0),
NVL(a.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(a.CHILD_LOT_FLAG,'N'),
NVL(a.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(a.LOT_DIVISIBLE_FLAG,'Y'),
NVL(a.SECONDARY_UOM_CODE,''),
NVL(a.SECONDARY_DEFAULT_IND,''),
NVL(a.TRACKING_QUANTITY_IND,'P'),
NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
NVL(a.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl a, /* Bug 5581528 */
rcv_transactions_v b,
mtl_cross_references mcr,
(SELECT DISTINCT rsl.shipment_header_id
FROM po_requisition_lines pol,
rcv_shipment_lines rsl
WHERE pol.requisition_header_id = p_source_id
AND pol.requisition_line_id = rsl.requisition_line_id
) shipment
WHERE b.to_organization_id = p_organization_id
AND b.shipment_header_id = shipment.shipment_header_id
AND b.receipt_source_code <> 'VENDOR'
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND b.to_organization_id = a.organization_id
AND b.item_id = a.inventory_item_id
AND (inv_rcv_std_inspect_apis.get_inspection_qty_wrapper('INTSHIP',
shipment.shipment_header_id, NULL, NULL, p_organization_id, a.inventory_item_id, a.primary_uom_code)) > 0
AND a.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = a.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT a.revision
, a.effectivity_date
, NVL(a.description, '')
FROM mtl_item_revisions a, rcv_transactions_v b
WHERE b.to_organization_id = p_organization_id
AND b.item_id = p_inventory_item_id
AND b.po_header_id = p_source_id
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND a.organization_id = b.to_organization_id
AND a.inventory_item_id = b.item_id
AND a.revision = b.item_revision
AND a.revision LIKE (p_revision);
SELECT DISTINCT a.revision
, a.effectivity_date
, NVL(a.description, '')
FROM mtl_item_revisions a, rcv_transactions_v b
WHERE b.to_organization_id = p_organization_id
AND b.item_id = p_inventory_item_id
AND b.receipt_source_code <> 'VENDOR'
AND b.shipment_header_id = p_source_id
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND a.organization_id = b.to_organization_id
AND a.inventory_item_id = b.item_id
AND a.revision = b.item_revision
AND a.revision LIKE (p_revision);
SELECT DISTINCT a.revision
, a.effectivity_date
, NVL(a.description, '')
FROM mtl_item_revisions a, rcv_transactions_v b
WHERE b.to_organization_id = p_organization_id
AND b.item_id = p_inventory_item_id
AND b.receipt_source_code <> 'VENDOR'
AND b.oe_order_header_id = p_source_id
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND a.organization_id = b.to_organization_id
AND a.inventory_item_id = b.item_id
AND a.revision = b.item_revision
AND a.revision LIKE (p_revision);
SELECT DISTINCT a.revision
, a.effectivity_date
, NVL(a.description, '')
FROM mtl_item_revisions a, rcv_transactions_v b
WHERE b.to_organization_id = p_organization_id
AND b.item_id = p_inventory_item_id
AND b.shipment_header_id = TO_CHAR(p_source_id)
AND b.inspection_status_code = 'NOT INSPECTED'
AND b.routing_id = 2 /* Inspection routing */
AND a.organization_id = b.to_organization_id
AND a.inventory_item_id = b.item_id
AND a.revision = b.item_revision
AND a.revision LIKE (p_revision);
PROCEDURE get_cgupdate_item_lov(x_items OUT NOCOPY t_genref, p_org_id IN NUMBER, p_lpn_id IN NUMBER, p_item IN VARCHAR2) IS
l_cross_ref varchar2(204);
SELECT DISTINCT msi.concatenated_segments concatenated_segments
, moq.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, '0'
, '0'
, '0'
, msi.primary_uom_code
, '0'
, NVL(shelf_life_code, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, NVL(msi.default_serial_status_id, 0)
, NVL(msi.serial_status_enabled, 'N')
, NVL(msi.default_lot_status_id, 0)
, NVL(msi.lot_status_enabled, 'N')
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(moq.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
WHERE moq.containerized_flag = 2
AND moq.organization_id = p_org_id
AND moq.inventory_item_id = msi.inventory_item_id
AND msi.concatenated_segments LIKE (p_item||l_append)
AND msi.organization_id = p_org_id
--Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments concatenated_segments
, moq.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, '0'
, '0'
, '0'
, msi.primary_uom_code
, '0'
, NVL(shelf_life_code, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, NVL(msi.default_serial_status_id, 0)
, NVL(msi.serial_status_enabled, 'N')
, NVL(msi.default_lot_status_id, 0)
, NVL(msi.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(moq.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
mtl_system_items_vl msi, /* Bug 5581528 */
mtl_cross_references mcr
WHERE moq.containerized_flag = 2
AND moq.organization_id = p_org_id
AND moq.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_org_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
SELECT DISTINCT msi.concatenated_segments concatenated_segments
, wlc.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, '0'
, '0'
, '0'
, msi.primary_uom_code
, '0'
, NVL(shelf_life_code, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, NVL(msi.default_serial_status_id, 0)
, NVL(msi.serial_status_enabled, 'N')
, NVL(msi.default_lot_status_id, 0)
, NVL(msi.lot_status_enabled, 'N')
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(wlc.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
-- bug 5172851, wms_lpn_contents_v is replaced with
-- wms_lpn_contents for performance reason
FROM mtl_system_items_vl msi, wms_lpn_contents wlc /* Bug 5581528 */
WHERE msi.concatenated_segments LIKE (p_item||l_append)
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = p_org_id
AND wlc.parent_lpn_id = p_lpn_id
--Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments concatenated_segments
, wlc.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, '0'
, '0'
, '0'
, msi.primary_uom_code
, '0'
, NVL(shelf_life_code, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, NVL(msi.default_serial_status_id, 0)
, NVL(msi.serial_status_enabled, 'N')
, NVL(msi.default_lot_status_id, 0)
, NVL(msi.lot_status_enabled, 'N')
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(wlc.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi, /* Bug 5581528 */
-- bug 5172851, wms_lpn_contents_v is replaced with
-- wms_lpn_contents for performance reason
wms_lpn_contents wlc,
mtl_cross_references mcr
WHERE msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = p_org_id
AND wlc.parent_lpn_id = p_lpn_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
END get_cgupdate_item_lov;
SELECT DISTINCT wlc.revision
, mir.effectivity_date
, NVL(mir.description, '')
FROM mtl_item_revisions mir, wms_lpn_contents_v wlc
WHERE wlc.organization_id = p_organization_id
AND wlc.inventory_item_id = TO_NUMBER(p_inventory_item_id)
AND NVL(wlc.parent_lpn_id, '0') = NVL(TO_NUMBER(p_lpn_id), NVL(wlc.parent_lpn_id, '0'))
AND mir.organization_id = wlc.organization_id
AND mir.inventory_item_id = wlc.inventory_item_id
AND mir.revision = wlc.revision
AND wlc.revision LIKE (p_revision);
SELECT DISTINCT concatenated_segments
, inventory_item_id
, 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, 2)
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl /* Bug 5581528 */
WHERE organization_id = p_org_id
AND concatenated_segments LIKE (p_item||l_append)
--Changes for GTIN
UNION
SELECT DISTINCT msik.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, 2)
, NVL(shelf_life_code, 1)
, NVL(shelf_life_days, 0)
, NVL(allowed_units_lookup_code, 2)
, NVL(effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msik.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
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)
FROM mtl_system_items_vl msik, /* Bug 5581528 */
mtl_cross_references mcr
WHERE msik.organization_id = p_org_id
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_serial_numbers msn, mtl_system_items_vl msi /* Bug 5581528 */
WHERE msn.current_organization_id = p_org_id
AND msn.serial_number = p_serial
AND msn.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = msn.current_organization_id
AND msi.concatenated_segments LIKE (p_item||l_append)
--Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_serial_numbers msn,
mtl_system_items_vl msi, /* Bug 5581528 */
mtl_cross_references mcr
WHERE msn.current_organization_id = p_org_id
AND msn.serial_number = p_serial
AND msn.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = msn.current_organization_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi, wms_lpn_contents wlpnc /* Bug 5581528 */
WHERE wlpnc.parent_lpn_id = p_lpn_id
AND wlpnc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
AND wlpnc.organization_id = p_org_id
AND msi.organization_id = wlpnc.organization_id
AND msi.inventory_item_id = wlpnc.inventory_item_id
AND msi.concatenated_segments LIKE (p_item||l_append)
UNION
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi, mtl_serial_numbers msn /* Bug 5581528 */
WHERE msn.lpn_id = p_lpn_id
AND msn.last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
AND msn.current_organization_id = p_org_id
AND msi.organization_id = msn.current_organization_id
AND msi.inventory_item_id = msn.inventory_item_id
AND msi.concatenated_segments LIKE (p_item||l_append)
-- Changes for GTIN
UNION
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi, /* Bug 5581528 */
wms_lpn_contents wlpnc,
mtl_cross_references mcr
WHERE wlpnc.parent_lpn_id = p_lpn_id
AND wlpnc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
AND wlpnc.organization_id = p_org_id
AND msi.organization_id = wlpnc.organization_id
AND msi.inventory_item_id = wlpnc.inventory_item_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y')
UNION
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, mcr.cross_reference
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id),
--Bug No 3952081
--Additional Fields for Process Convergence
NVL(msi.GRADE_CONTROL_FLAG,'N'),
NVL(msi.DEFAULT_GRADE,''),
NVL(msi.EXPIRATION_ACTION_INTERVAL,0),
NVL(msi.EXPIRATION_ACTION_CODE,''),
NVL(msi.HOLD_DAYS,0),
NVL(msi.MATURITY_DAYS,0),
NVL(msi.RETEST_INTERVAL,0),
NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N'),
NVL(msi.CHILD_LOT_FLAG,'N'),
NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N'),
NVL(msi.LOT_DIVISIBLE_FLAG,'Y'),
NVL(msi.SECONDARY_UOM_CODE,''),
NVL(msi.SECONDARY_DEFAULT_IND,''),
NVL(msi.TRACKING_QUANTITY_IND,'P'),
NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
FROM mtl_system_items_vl msi, /* Bug 5581528 */
mtl_serial_numbers msn,
mtl_cross_references mcr
WHERE msn.lpn_id = p_lpn_id
AND msn.last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
AND msn.current_organization_id = p_org_id
AND msi.organization_id = msn.current_organization_id
AND msi.inventory_item_id = msn.inventory_item_id
AND msi.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msi.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT wlc.revision
, mir.effectivity_date
, NVL(mir.description, '')
FROM mtl_item_revisions mir, wms_lpn_contents_v wlc
WHERE wlc.organization_id = p_organization_id
AND wlc.inventory_item_id = TO_NUMBER(p_inventory_item_id)
AND NVL(wlc.parent_lpn_id, '0') = NVL(TO_NUMBER(p_lpn_id), NVL(wlc.parent_lpn_id, '0'))
AND wlc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
AND mir.organization_id = wlc.organization_id
AND mir.inventory_item_id = wlc.inventory_item_id
AND mir.revision = wlc.revision
AND wlc.revision LIKE (p_revision);
SELECT msi.concatenated_segments
, msi.description
, msi.inventory_item_id
FROM mtl_system_items_vl msi /* Bug 5581528 */
WHERE msi.organization_id = p_organization_id
AND msi.concatenated_segments LIKE (p_concatenated_segments)
AND msi.vehicle_item_flag = 'Y'
ORDER BY UPPER(msi.concatenated_segments);
SELECT DISTINCT muom.uom_code
, muom.unit_of_measure
, muom.description
, muom.uom_class
FROM mtl_units_of_measure muom, wms_license_plate_numbers wlpn
WHERE muom.uom_code LIKE (p_uom_text)
AND wlpn.lpn_id = p_lpn_id
AND muom.uom_code = wlpn.gross_weight_uom_code
UNION
SELECT DISTINCT muom.uom_code
, muom.unit_of_measure
, muom.description
, muom.uom_class
FROM mtl_units_of_measure muom, wsh_shipping_parameters wsp
WHERE wsp.organization_id = p_organization_id
AND wsp.weight_uom_class = muom.uom_class
AND muom.uom_code LIKE (p_uom_text);
SELECT DISTINCT miv.revision
, miv.effectivity_date
, NVL(miv.description, '')
FROM mtl_item_revisions miv, rcv_supply rs
WHERE miv.organization_id = p_organization_id
AND miv.inventory_item_id = p_inventory_item_id
AND rs.shipment_header_id(+) = p_shipment_header_id
AND (rs.shipment_header_id IS NULL
OR rs.shipment_header_id = p_shipment_header_id
)
AND rs.po_header_id(+) = p_po_header_id
AND (rs.po_header_id IS NULL
OR rs.po_header_id = p_po_header_id
)
AND rs.to_organization_id(+) = miv.organization_id
AND (rs.to_organization_id IS NULL
OR rs.to_organization_id = p_organization_id
)
AND rs.item_id(+) = miv.inventory_item_id
AND (rs.item_id IS NULL
OR rs.item_id = p_inventory_item_id
)
AND NVL(rs.item_revision, miv.revision) = miv.revision
AND miv.revision LIKE (p_revision);
SELECT DISTINCT miv.revision
, miv.effectivity_date
, NVL(miv.description, '')
FROM mtl_item_revisions miv, rcv_supply rs
WHERE miv.organization_id = p_organization_id
AND miv.inventory_item_id = p_inventory_item_id
AND rs.shipment_header_id(+) = p_shipment_header_id
AND (rs.shipment_header_id IS NULL
OR rs.shipment_header_id = p_shipment_header_id
)
AND rs.to_organization_id(+) = miv.organization_id
AND (rs.to_organization_id IS NULL
OR rs.to_organization_id = p_organization_id
)
AND rs.item_id(+) = miv.inventory_item_id
AND (rs.item_id IS NULL
OR rs.item_id = p_inventory_item_id
)
AND NVL(rs.item_revision, miv.revision) = miv.revision
AND miv.revision LIKE (p_revision);
SELECT DISTINCT miv.revision
, miv.effectivity_date
, NVL(miv.description, '')
FROM mtl_item_revisions miv, rcv_supply rs
WHERE miv.organization_id = p_organization_id
AND miv.inventory_item_id = p_inventory_item_id
AND rs.po_header_id(+) = p_po_header_id
AND (rs.po_header_id IS NULL
OR rs.po_header_id = p_po_header_id
)
AND rs.to_organization_id(+) = miv.organization_id
AND (rs.to_organization_id IS NULL
OR rs.to_organization_id = p_organization_id
)
AND rs.item_id(+) = miv.inventory_item_id
AND (rs.item_id IS NULL
OR rs.item_id = p_inventory_item_id
)
AND NVL(rs.item_revision, miv.revision) = miv.revision
AND miv.revision LIKE (p_revision);
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
:= 'SELECT msik.concatenated_segments concatenated_segments'
|| ', msik.inventory_item_id'
|| ', msik.description'
|| ', NVL(msik.revision_qty_control_code, 1)'
|| ', NVL(msik.lot_status_enabled, ''N'')'
|| ', NVL(msik.serial_status_enabled, ''N'')'
|| ', NVL(msik.restrict_subinventories_code, 2)'
|| ', NVL(msik.restrict_locators_code, 2)'
|| ', NVL(msik.location_control_code, 1)'
|| ', msik.primary_uom_code'
|| ', NVL(msik.inspection_required_flag, 2)'
|| ', NVL(msik.shelf_life_code, 1)'
|| ', NVL(msik.shelf_life_days, 0)'
|| ', NVL(msik.allowed_units_lookup_code, 2)'
|| ', NVL(msik.effectivity_control, 1)'
|| ', 0 parentlpnid'
|| ', 0 quantity'
|| ', NVL(msik.default_serial_status_id, 0)'
|| ', NVL(msik.serial_status_enabled, ''N'')'
|| ', NVL(msik.default_lot_status_id, 0)'
|| ', NVL(msik.lot_status_enabled, ''N'')'
|| ', '''''
|| ', ''N'''
|| ', msik.inventory_item_flag'
|| ', 0'
|| ', wms_deploy.get_item_client_name(msik.inventory_item_id),' -- Bug9369327
--Bug No 3952081
--Additional Fields for Process Convergence
|| 'NVL(msik.GRADE_CONTROL_FLAG,''N''),'
|| 'NVL(msik.DEFAULT_GRADE,''''),'
|| 'NVL(msik.EXPIRATION_ACTION_INTERVAL,0),'
|| 'NVL(msik.EXPIRATION_ACTION_CODE,''''),'
|| 'NVL(msik.HOLD_DAYS,0),'
|| 'NVL(msik.MATURITY_DAYS,0),'
|| 'NVL(msik.RETEST_INTERVAL,0),'
|| 'NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_VALIDATION_FLAG,''N''),'
|| 'NVL(msik.LOT_DIVISIBLE_FLAG,''Y''),'
|| 'NVL(msik.SECONDARY_UOM_CODE,''''),'
|| 'NVL(msik.SECONDARY_DEFAULT_IND,''''),'
|| 'NVL(msik.TRACKING_QUANTITY_IND,''P''),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_LOW,0)';
:= 'SELECT msik.concatenated_segments concatenated_segments'
|| ', msik.inventory_item_id'
|| ', msik.description'
|| ', NVL(msik.revision_qty_control_code, 1)'
|| ', NVL(msik.lot_status_enabled, ''N'')'
|| ', NVL(msik.serial_status_enabled, ''N'')'
|| ', NVL(msik.restrict_subinventories_code, 2)'
|| ', NVL(msik.restrict_locators_code, 2)'
|| ', NVL(msik.location_control_code, 1)'
|| ', msik.primary_uom_code'
|| ', NVL(msik.inspection_required_flag, 2)'
|| ', NVL(msik.shelf_life_code, 1)'
|| ', NVL(msik.shelf_life_days, 0)'
|| ', NVL(msik.allowed_units_lookup_code, 2)'
|| ', NVL(msik.effectivity_control, 1)'
|| ', 0 parentlpnid'
|| ', 0 quantity'
|| ', NVL(msik.default_serial_status_id, 0)'
|| ', NVL(msik.serial_status_enabled, ''N'')'
|| ', NVL(msik.default_lot_status_id, 0)'
|| ', NVL(msik.lot_status_enabled, ''N'')'
|| ', mcr.cross_reference'
|| ', ''N'''
|| ', msik.inventory_item_flag'
|| ', 0'
|| ', wms_deploy.get_item_client_name(msik.inventory_item_id),' -- Bug9369327
--Bug No 3952081
--Additional Fields for Process Convergence
|| 'NVL(msik.GRADE_CONTROL_FLAG,''N''),'
|| 'NVL(msik.DEFAULT_GRADE,''''),'
|| 'NVL(msik.EXPIRATION_ACTION_INTERVAL,0),'
|| 'NVL(msik.EXPIRATION_ACTION_CODE,''''),'
|| 'NVL(msik.HOLD_DAYS,0),'
|| 'NVL(msik.MATURITY_DAYS,0),'
|| 'NVL(msik.RETEST_INTERVAL,0),'
|| 'NVL(msik.COPY_LOT_ATTRIBUTE_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_FLAG,''N''),'
|| 'NVL(msik.CHILD_LOT_VALIDATION_FLAG,''N''),'
|| 'NVL(msik.LOT_DIVISIBLE_FLAG,''Y''),'
|| 'NVL(msik.SECONDARY_UOM_CODE,''''),'
|| 'NVL(msik.SECONDARY_DEFAULT_IND,''''),'
|| 'NVL(msik.TRACKING_QUANTITY_IND,''P''),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),'
|| 'NVL(msik.DUAL_UOM_DEVIATION_LOW,0)';
SELECT primary_uom_code,
SECONDARY_UOM_CODE
INTO l_primary_uom_code,
l_sec_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
SELECT uom_code_comp,
unit_of_measure,
description,
uom_class
FROM (SELECT ( inv_ui_item_lovs.Get_conversion_rate(uom_code, p_organization_id,
inventory_item_id,
Nvl( p_fulfillment_base,'P'))) uom_code_comp,
unit_of_measure,
description,
uom_class
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND Nvl(uom_type, 3) = Nvl(p_uom_type, 3)
AND uom_code LIKE ( l_code ))
WHERE ((Nvl(p_fulfillment_base,'P') = 'S'
AND inv_convert.is_uom_in_same_class( Substr(uom_code_comp, 1, Instr(uom_code_comp, '(') - 1), p_organization_id , p_item_id , Nvl(p_fulfillment_base,'P')) = 'Y')
OR (Nvl(p_fulfillment_base,'P') = 'P'))
ORDER BY inv_ui_item_lovs.conversion_order(inv_ui_item_lovs.Get_conversion_rate(Substr(uom_code_comp, 1,
Instr(uom_code_comp, '(') - 1),
p_organization_id,
p_item_id,
Nvl( p_fulfillment_base,'P'))) ASC, Upper(Substr(uom_code_comp, 1, Instr(uom_code_comp, '(') - 1));