VIEW: APPS.INVFV_PHYSICAL_ADJUSTMENTS

Source

View Text - Preformatted

SELECT MPA.ADJUSTMENT_ID, MPA.ORGANIZATION_ID, MP.ORGANIZATION_CODE, HOU.NAME, MPA.PHYSICAL_INVENTORY_ID, MPI.PHYSICAL_INVENTORY_NAME, MPA.INVENTORY_ITEM_ID, '_KF:INV:MSTK:MSI', /* KEY FLEX */ MPA.SYSTEM_QUANTITY, MPA.COUNT_QUANTITY, MPA.ADJUSTMENT_QUANTITY, MPA.REVISION, MPA.SUBINVENTORY_NAME, MPA.LOCATOR_ID, '_KF:INV:MTLL:MIL', MPA.LOT_NUMBER, MPA.LOT_EXPIRATION_DATE, MPA.SERIAL_NUMBER, MPA.ACTUAL_COST, '_LA:MPA.APPROVAL_STATUS:MFG_LOOKUPS:MTL_APPROVALS:MEANING', /* LOOKUP */ MPA.APPROVED_BY_EMPLOYEE_ID, NULL APPROVED_BY_EMPLOYEE_NAME, MPA.GL_ADJUST_ACCOUNT, '_KF:SQLGL:GL#:GLC', /* KEY FLEX */ MPA.CREATED_BY, MPA.CREATION_DATE, MPA.LAST_UPDATED_BY, MPA.LAST_UPDATE_DATE FROM GL_CODE_COMBINATIONS GLC, MTL_ITEM_LOCATIONS MIL, MTL_SYSTEM_ITEMS MSI, HR_ALL_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP, MTL_PHYSICAL_INVENTORIES MPI, MTL_PHYSICAL_ADJUSTMENTS MPA WHERE MP.ORGANIZATION_ID = MPA.ORGANIZATION_ID AND HOU.ORGANIZATION_ID = MPA.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = MPA.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = MPA.ORGANIZATION_ID AND GLC.CODE_COMBINATION_ID (+) = MPA.GL_ADJUST_ACCOUNT AND MPI.PHYSICAL_INVENTORY_ID = MPA.PHYSICAL_INVENTORY_ID AND MIL.INVENTORY_LOCATION_ID (+) = MPA.LOCATOR_ID AND MIL.ORGANIZATION_ID (+) = MPA.ORGANIZATION_ID AND MPA.APPROVED_BY_EMPLOYEE_ID IS NULL AND '_SEC:MPA.ORGANIZATION_ID' IS NOT NULL /*-- -- Get the Employee name if EMPLOYEE_ID IS NOT NULL*/ UNION ALL SELECT MPA.ADJUSTMENT_ID, MPA.ORGANIZATION_ID, MP.ORGANIZATION_CODE, HOU.NAME, MPA.PHYSICAL_INVENTORY_ID, MPI.PHYSICAL_INVENTORY_NAME, MPA.INVENTORY_ITEM_ID, '_KF:INV:MSTK:MSI', /* KEY FLEX */ MPA.SYSTEM_QUANTITY, MPA.COUNT_QUANTITY, MPA.ADJUSTMENT_QUANTITY, MPA.REVISION, MPA.SUBINVENTORY_NAME, MPA.LOCATOR_ID, '_KF:INV:MTLL:MIL', MPA.LOT_NUMBER, MPA.LOT_EXPIRATION_DATE, MPA.SERIAL_NUMBER, MPA.ACTUAL_COST, '_LA:MPA.APPROVAL_STATUS:MFG_LOOKUPS:MTL_APPROVALS:MEANING', /* LOOKUP */ MPA.APPROVED_BY_EMPLOYEE_ID, MEV.FULL_NAME APPROVED_BY_EMPLOYEE_NAME, MPA.GL_ADJUST_ACCOUNT, '_KF:SQLGL:GL#:GLC', /* KEY FLEX */ MPA.CREATED_BY, MPA.CREATION_DATE, MPA.LAST_UPDATED_BY, MPA.LAST_UPDATE_DATE FROM MTL_EMPLOYEES_VIEW MEV, MTL_ITEM_LOCATIONS MIL, GL_CODE_COMBINATIONS GLC, MTL_SYSTEM_ITEMS MSI, HR_ALL_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP, MTL_PHYSICAL_INVENTORIES MPI, MTL_PHYSICAL_ADJUSTMENTS MPA WHERE MP.ORGANIZATION_ID = MPA.ORGANIZATION_ID AND HOU.ORGANIZATION_ID = MPA.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = MPA.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = MPA.ORGANIZATION_ID AND GLC.CODE_COMBINATION_ID (+) = MPA.GL_ADJUST_ACCOUNT AND MPI.PHYSICAL_INVENTORY_ID = MPA.PHYSICAL_INVENTORY_ID AND MEV.EMPLOYEE_ID = MPA.APPROVED_BY_EMPLOYEE_ID AND MEV.ORGANIZATION_ID = MPA.ORGANIZATION_ID AND MIL.INVENTORY_LOCATION_ID (+) = MPA.LOCATOR_ID AND MIL.ORGANIZATION_ID (+) = MPA.ORGANIZATION_ID AND MPA.APPROVED_BY_EMPLOYEE_ID IS NOT NULL AND '_SEC:MPA.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY

View Text - HTML Formatted

SELECT MPA.ADJUSTMENT_ID
, MPA.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOU.NAME
, MPA.PHYSICAL_INVENTORY_ID
, MPI.PHYSICAL_INVENTORY_NAME
, MPA.INVENTORY_ITEM_ID
, '_KF:INV:MSTK:MSI'
, /* KEY FLEX */ MPA.SYSTEM_QUANTITY
, MPA.COUNT_QUANTITY
, MPA.ADJUSTMENT_QUANTITY
, MPA.REVISION
, MPA.SUBINVENTORY_NAME
, MPA.LOCATOR_ID
, '_KF:INV:MTLL:MIL'
, MPA.LOT_NUMBER
, MPA.LOT_EXPIRATION_DATE
, MPA.SERIAL_NUMBER
, MPA.ACTUAL_COST
, '_LA:MPA.APPROVAL_STATUS:MFG_LOOKUPS:MTL_APPROVALS:MEANING'
, /* LOOKUP */ MPA.APPROVED_BY_EMPLOYEE_ID
, NULL APPROVED_BY_EMPLOYEE_NAME
, MPA.GL_ADJUST_ACCOUNT
, '_KF:SQLGL:GL#:GLC'
, /* KEY FLEX */ MPA.CREATED_BY
, MPA.CREATION_DATE
, MPA.LAST_UPDATED_BY
, MPA.LAST_UPDATE_DATE
FROM GL_CODE_COMBINATIONS GLC
, MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, HR_ALL_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, MTL_PHYSICAL_INVENTORIES MPI
, MTL_PHYSICAL_ADJUSTMENTS MPA
WHERE MP.ORGANIZATION_ID = MPA.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MPA.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MPA.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MPA.ORGANIZATION_ID
AND GLC.CODE_COMBINATION_ID (+) = MPA.GL_ADJUST_ACCOUNT
AND MPI.PHYSICAL_INVENTORY_ID = MPA.PHYSICAL_INVENTORY_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MPA.LOCATOR_ID
AND MIL.ORGANIZATION_ID (+) = MPA.ORGANIZATION_ID
AND MPA.APPROVED_BY_EMPLOYEE_ID IS NULL
AND '_SEC:MPA.ORGANIZATION_ID' IS NOT NULL /*-- -- GET THE EMPLOYEE NAME IF EMPLOYEE_ID IS NOT NULL*/ UNION ALL SELECT MPA.ADJUSTMENT_ID
, MPA.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOU.NAME
, MPA.PHYSICAL_INVENTORY_ID
, MPI.PHYSICAL_INVENTORY_NAME
, MPA.INVENTORY_ITEM_ID
, '_KF:INV:MSTK:MSI'
, /* KEY FLEX */ MPA.SYSTEM_QUANTITY
, MPA.COUNT_QUANTITY
, MPA.ADJUSTMENT_QUANTITY
, MPA.REVISION
, MPA.SUBINVENTORY_NAME
, MPA.LOCATOR_ID
, '_KF:INV:MTLL:MIL'
, MPA.LOT_NUMBER
, MPA.LOT_EXPIRATION_DATE
, MPA.SERIAL_NUMBER
, MPA.ACTUAL_COST
, '_LA:MPA.APPROVAL_STATUS:MFG_LOOKUPS:MTL_APPROVALS:MEANING'
, /* LOOKUP */ MPA.APPROVED_BY_EMPLOYEE_ID
, MEV.FULL_NAME APPROVED_BY_EMPLOYEE_NAME
, MPA.GL_ADJUST_ACCOUNT
, '_KF:SQLGL:GL#:GLC'
, /* KEY FLEX */ MPA.CREATED_BY
, MPA.CREATION_DATE
, MPA.LAST_UPDATED_BY
, MPA.LAST_UPDATE_DATE
FROM MTL_EMPLOYEES_VIEW MEV
, MTL_ITEM_LOCATIONS MIL
, GL_CODE_COMBINATIONS GLC
, MTL_SYSTEM_ITEMS MSI
, HR_ALL_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, MTL_PHYSICAL_INVENTORIES MPI
, MTL_PHYSICAL_ADJUSTMENTS MPA
WHERE MP.ORGANIZATION_ID = MPA.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MPA.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MPA.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MPA.ORGANIZATION_ID
AND GLC.CODE_COMBINATION_ID (+) = MPA.GL_ADJUST_ACCOUNT
AND MPI.PHYSICAL_INVENTORY_ID = MPA.PHYSICAL_INVENTORY_ID
AND MEV.EMPLOYEE_ID = MPA.APPROVED_BY_EMPLOYEE_ID
AND MEV.ORGANIZATION_ID = MPA.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MPA.LOCATOR_ID
AND MIL.ORGANIZATION_ID (+) = MPA.ORGANIZATION_ID
AND MPA.APPROVED_BY_EMPLOYEE_ID IS NOT NULL
AND '_SEC:MPA.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY