VIEW: APPS.INVFV_PHYSICAL_INV_COUNTS

Source

View Text - Preformatted

SELECT MPT.TAG_ID, MPT.TAG_NUMBER, MPT.PHYSICAL_INVENTORY_ID, MPI.PHYSICAL_INVENTORY_NAME, MPT.ORGANIZATION_ID, MP.ORGANIZATION_CODE, HOU.NAME, '_LA:MPT.VOID_FLAG:MFG_LOOKUPS:MTL_VOID:MEANING', MPT.ADJUSTMENT_ID, MPT.INVENTORY_ITEM_ID, '_KF:INV:MSTK:MSI', MPT.TAG_QUANTITY, MPT.TAG_UOM, MPT.TAG_QUANTITY_AT_STANDARD_UOM, MPT.STANDARD_UOM, MPT.SUBINVENTORY, MPT.LOCATOR_ID, '_KF:INV:MTLL:MIL', MPT.LOT_NUMBER, MPT.LOT_EXPIRATION_DATE, MPT.REVISION, MPT.SERIAL_NUM, MPT.COUNTED_BY_EMPLOYEE_ID, NULL COUNTED_BY_EMPLOYEE_NAME, MPT.CREATED_BY, MPT.CREATION_DATE, MPT.LAST_UPDATED_BY, MPT.LAST_UPDATE_DATE FROM MTL_ITEM_LOCATIONS MIL, MTL_SYSTEM_ITEMS MSI, MTL_PHYSICAL_INVENTORIES MPI, HR_ALL_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP, MTL_PHYSICAL_INVENTORY_TAGS MPT WHERE MSI.INVENTORY_ITEM_ID (+)= MPT.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID (+)= MPT.ORGANIZATION_ID AND MPI.PHYSICAL_INVENTORY_ID = MPT.PHYSICAL_INVENTORY_ID AND MP.ORGANIZATION_ID = MPT.ORGANIZATION_ID AND HOU.ORGANIZATION_ID = MPT.ORGANIZATION_ID AND MIL.INVENTORY_LOCATION_ID (+) = MPT.LOCATOR_ID AND MIL.ORGANIZATION_ID (+) = MPT.ORGANIZATION_ID AND MPT.COUNTED_BY_EMPLOYEE_ID IS NULL AND '_SEC:MPT.ORGANIZATION_ID' IS NOT NULL /* ** GET the employee name if employee id is not null */ UNION ALL SELECT MPT.TAG_ID, MPT.TAG_NUMBER, MPT.PHYSICAL_INVENTORY_ID, MPI.PHYSICAL_INVENTORY_NAME, MPT.ORGANIZATION_ID, MP.ORGANIZATION_CODE, HOU.NAME, '_LA:MPT.VOID_FLAG:MFG_LOOKUPS:MTL_VOID:MEANING', MPT.ADJUSTMENT_ID, MPT.INVENTORY_ITEM_ID, '_KF:INV:MSTK:MSI', MPT.TAG_QUANTITY, MPT.TAG_UOM, MPT.TAG_QUANTITY_AT_STANDARD_UOM, MPT.STANDARD_UOM, MPT.SUBINVENTORY, MPT.LOCATOR_ID, '_KF:INV:MTLL:MIL', MPT.LOT_NUMBER, MPT.LOT_EXPIRATION_DATE, MPT.REVISION, MPT.SERIAL_NUM, MPT.COUNTED_BY_EMPLOYEE_ID, MEV.FULL_NAME COUNTED_BY_EMPLOYEE_NAME, MPT.CREATED_BY, MPT.CREATION_DATE, MPT.LAST_UPDATED_BY, MPT.LAST_UPDATE_DATE FROM MTL_EMPLOYEES_VIEW MEV, MTL_ITEM_LOCATIONS MIL, MTL_SYSTEM_ITEMS MSI, MTL_PHYSICAL_INVENTORIES MPI, HR_ALL_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP, MTL_PHYSICAL_INVENTORY_TAGS MPT WHERE MEV.EMPLOYEE_ID = MPT.COUNTED_BY_EMPLOYEE_ID AND MEV.ORGANIZATION_ID = MPT.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID (+)= MPT.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID (+)= MPT.ORGANIZATION_ID AND MPI.PHYSICAL_INVENTORY_ID = MPT.PHYSICAL_INVENTORY_ID AND MP.ORGANIZATION_ID = MPT.ORGANIZATION_ID AND HOU.ORGANIZATION_ID = MPT.ORGANIZATION_ID AND MIL.INVENTORY_LOCATION_ID (+) = MPT.LOCATOR_ID AND MIL.ORGANIZATION_ID (+) = MPT.ORGANIZATION_ID AND MPT.COUNTED_BY_EMPLOYEE_ID IS NOT NULL AND '_SEC:MPT.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY

View Text - HTML Formatted

SELECT MPT.TAG_ID
, MPT.TAG_NUMBER
, MPT.PHYSICAL_INVENTORY_ID
, MPI.PHYSICAL_INVENTORY_NAME
, MPT.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOU.NAME
, '_LA:MPT.VOID_FLAG:MFG_LOOKUPS:MTL_VOID:MEANING'
, MPT.ADJUSTMENT_ID
, MPT.INVENTORY_ITEM_ID
, '_KF:INV:MSTK:MSI'
, MPT.TAG_QUANTITY
, MPT.TAG_UOM
, MPT.TAG_QUANTITY_AT_STANDARD_UOM
, MPT.STANDARD_UOM
, MPT.SUBINVENTORY
, MPT.LOCATOR_ID
, '_KF:INV:MTLL:MIL'
, MPT.LOT_NUMBER
, MPT.LOT_EXPIRATION_DATE
, MPT.REVISION
, MPT.SERIAL_NUM
, MPT.COUNTED_BY_EMPLOYEE_ID
, NULL COUNTED_BY_EMPLOYEE_NAME
, MPT.CREATED_BY
, MPT.CREATION_DATE
, MPT.LAST_UPDATED_BY
, MPT.LAST_UPDATE_DATE
FROM MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, MTL_PHYSICAL_INVENTORIES MPI
, HR_ALL_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, MTL_PHYSICAL_INVENTORY_TAGS MPT
WHERE MSI.INVENTORY_ITEM_ID (+)= MPT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID (+)= MPT.ORGANIZATION_ID
AND MPI.PHYSICAL_INVENTORY_ID = MPT.PHYSICAL_INVENTORY_ID
AND MP.ORGANIZATION_ID = MPT.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MPT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MPT.LOCATOR_ID
AND MIL.ORGANIZATION_ID (+) = MPT.ORGANIZATION_ID
AND MPT.COUNTED_BY_EMPLOYEE_ID IS NULL
AND '_SEC:MPT.ORGANIZATION_ID' IS NOT NULL /* ** GET THE EMPLOYEE NAME IF EMPLOYEE ID IS NOT NULL */ UNION ALL SELECT MPT.TAG_ID
, MPT.TAG_NUMBER
, MPT.PHYSICAL_INVENTORY_ID
, MPI.PHYSICAL_INVENTORY_NAME
, MPT.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOU.NAME
, '_LA:MPT.VOID_FLAG:MFG_LOOKUPS:MTL_VOID:MEANING'
, MPT.ADJUSTMENT_ID
, MPT.INVENTORY_ITEM_ID
, '_KF:INV:MSTK:MSI'
, MPT.TAG_QUANTITY
, MPT.TAG_UOM
, MPT.TAG_QUANTITY_AT_STANDARD_UOM
, MPT.STANDARD_UOM
, MPT.SUBINVENTORY
, MPT.LOCATOR_ID
, '_KF:INV:MTLL:MIL'
, MPT.LOT_NUMBER
, MPT.LOT_EXPIRATION_DATE
, MPT.REVISION
, MPT.SERIAL_NUM
, MPT.COUNTED_BY_EMPLOYEE_ID
, MEV.FULL_NAME COUNTED_BY_EMPLOYEE_NAME
, MPT.CREATED_BY
, MPT.CREATION_DATE
, MPT.LAST_UPDATED_BY
, MPT.LAST_UPDATE_DATE
FROM MTL_EMPLOYEES_VIEW MEV
, MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, MTL_PHYSICAL_INVENTORIES MPI
, HR_ALL_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, MTL_PHYSICAL_INVENTORY_TAGS MPT
WHERE MEV.EMPLOYEE_ID = MPT.COUNTED_BY_EMPLOYEE_ID
AND MEV.ORGANIZATION_ID = MPT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+)= MPT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID (+)= MPT.ORGANIZATION_ID
AND MPI.PHYSICAL_INVENTORY_ID = MPT.PHYSICAL_INVENTORY_ID
AND MP.ORGANIZATION_ID = MPT.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MPT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MPT.LOCATOR_ID
AND MIL.ORGANIZATION_ID (+) = MPT.ORGANIZATION_ID
AND MPT.COUNTED_BY_EMPLOYEE_ID IS NOT NULL
AND '_SEC:MPT.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY