Product: | CUN - Network Logistics - NATS (Obsolete) |
---|---|
Description: | This view selects details of Serialized NL tracked Items. |
Implementation/DBA Data: | Not implemented in this database |
SELECT CSU.SERIALIZED_UNIT_ID
, CSU.ITEM_ID
, ITEM.CONCATENATED_SEGMENTS INV_ITEM
, ITEM.DESCRIPTION
, CSU.ORGANIZATION_ID
, INV_ORG.NAME INV_ORGANIZATION_NAME
, CSU.QUANTITY
, CSU.STATUS
, FND_LOOK.MEANING STATUS_NAME
, FND_LOOK.DESCRIPTION STATUS_DESCRIPTION
, CSU.SUBINVENTORY
, CSU.LOCATOR_ID
, LOC.CONCATENATED_SEGMENTS LOCATOR
, CSU.REVISION
, CSU.LOT_NUMBER
, CSU.FA_DISTRIBUTION_ID
, FA_DIST_HIST.BOOK_TYPE_CODE
, FA_DIST_HIST.LOCATION_ID FA_DIST_LOCATION_ID
, FA.CONCATENATED_SEGMENTS
, FA_DIST_HIST.CODE_COMBINATION_ID
, FA_ADD.ASSET_ID
, FA_ADD.ASSET_NUMBER
, FA_ADD.DESCRIPTION ASSET_DESCRIPTION
, FA_ADD.TAG_NUMBER
, FA_ADD.SERIAL_NUMBER
, FA_ADD.ASSET_TYPE
, FA_ADD.CURRENT_UNITS
, FA_ADD.CATEGORY_TYPE
, FA_ADD.MANUFACTURER_NAME
, CSU.HZ_LOCATION_ID
, CUL.NETWORK_LOCATION_CODE
, CUL.DESCRIPTION LOC_DESCRIPTION
, CSU.PROJECT_ID
, PROJ.SEGMENT1 PROJECT_NUMBER
, CSU.TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, CSU.SERIAL_NUMBER SERIAL_NUMBER1
, CSU.VENDOR_SERIAL_NUMBER
, CSU.NETWORK_NUMBER
, CSU.VENDOR_ID
, VEND.VENDOR_NAME VENDOR_NAME
, CSU.ASSET_GROUPING_ELEMENT1
, CSU.ASSET_GROUPING_ELEMENT2
, CSU.ASSET_GROUPING_ELEMENT3
, CSU.LAST_TRANSACTION_ID
, CSU.REFERENCE_DATA
, CSU.CREATION_DATE
, CSU.CREATED_BY
, CSU.LAST_UPDATE_DATE
, CSU.LAST_UPDATED_BY
, CSU.LAST_UPDATE_LOGIN
FROM CUN_SERIALIZED_UNITS CSU
, MTL_SYSTEM_ITEMS_KFV ITEM
, HR_ORGANIZATION_UNITS INV_ORG
, MTL_ITEM_LOCATIONS_KFV LOC
, PO_VENDORS VEND
, PA_TASKS TASK
, PA_PROJECTS_ALL PROJ
, CUN_LOCATIONS_V CUL
, FA_DISTRIBUTION_HISTORY FA_DIST_HIST
, FA_ADDITIONS_V FA_ADD
, FA_LOCATIONS_KFV FA
, FND_LOOKUP_VALUES FND_LOOK
WHERE 1=1
AND ITEM.INVENTORY_ITEM_ID = CSU.ITEM_ID
AND ITEM.ORGANIZATION_ID = CSU.ORGANIZATION_ID
AND INV_ORG.ORGANIZATION_ID(+) = CSU.ORGANIZATION_ID
AND LOC.INVENTORY_LOCATION_ID(+) = CSU.LOCATOR_ID
AND LOC.ORGANIZATION_ID(+) = CSU.ORGANIZATION_ID
AND FND_LOOK.LOOKUP_TYPE(+)='CUN_UNIT_STATUS'
AND FND_LOOK.LOOKUP_CODE(+)=CSU.STATUS
AND FND_LOOK.LANGUAGE = USERENV('LANG')
AND VEND.VENDOR_ID(+) = CSU.VENDOR_ID
AND TASK.TASK_ID(+) = CSU.TASK_ID
AND PROJ.PROJECT_ID(+) = CSU.PROJECT_ID
AND CUL.CUN_LOCATION_ID(+) = CSU.HZ_LOCATION_ID
AND FA_DIST_HIST.DISTRIBUTION_ID(+)=CSU.FA_DISTRIBUTION_ID
AND FA_DIST_HIST.ASSET_ID = FA_ADD.ASSET_ID(+)
AND FA_DIST_HIST.LOCATION_ID = FA.LOCATION_ID(+)