[Home] [Help]
| Product: | CUN - Network Logistics - NATS (Obsolete) |
|---|---|
| Description: | This view selects details of Non Serialized NL tracked Items. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT CNSU.NON_SERIALIZED_UNIT_ID
, CNSU.ITEM_ID
, ITEM.CONCATENATED_SEGMENTS INV_ITEM
, ITEM.DESCRIPTION
, CNSU.ORGANIZATION_ID
, INV_ORG.NAME INV_ORGANIZATION_NAME
, CNSU.QUANTITY
, CNSU.STATUS
, FND_LOOK.MEANING STATUS_NAME
, FND_LOOK.DESCRIPTION STATUS_DESCRIPTION
, CNSU.SUBINVENTORY
, CNSU.LOCATOR_ID
, LOC.CONCATENATED_SEGMENTS LOCATOR
, CNSU.REVISION
, CNSU.LOT_NUMBER
, CNSU.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
, CNSU.HZ_LOCATION_ID
, CUL.NETWORK_LOCATION_CODE
, CUL.DESCRIPTION LOC_DESCRIPTION
, CNSU.PROJECT_ID
, PROJ.SEGMENT1 PROJECT_NUMBER
, CNSU.TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, NULL SERIAL_NUMBER
, NULL VENDOR_SERIAL_NUMBER
, CNSU.NETWORK_NUMBER
, CNSU.VENDOR_ID
, VEND.VENDOR_NAME VENDOR_NAME
, CNSU.ASSET_GROUPING_ELEMENT1
, CNSU.ASSET_GROUPING_ELEMENT2
, CNSU.ASSET_GROUPING_ELEMENT3
, CNSU.LAST_TRANSACTION_ID
, CNSU.REFERENCE_DATA
, CNSU.CREATION_DATE
, CNSU.CREATED_BY
, CNSU.LAST_UPDATE_DATE
, CNSU.LAST_UPDATED_BY
, CNSU.LAST_UPDATE_LOGIN
FROM CUN_NON_SERIALIZED_UNITS CNSU
, 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 = CNSU.ITEM_ID
AND ITEM.ORGANIZATION_ID = CNSU.ORGANIZATION_ID
AND INV_ORG.ORGANIZATION_ID(+) = CNSU.ORGANIZATION_ID
AND LOC.INVENTORY_LOCATION_ID(+) = CNSU.LOCATOR_ID
AND LOC.ORGANIZATION_ID(+) = CNSU.ORGANIZATION_ID
AND FND_LOOK.LOOKUP_TYPE(+)='CUN_UNIT_STATUS'
AND FND_LOOK.LOOKUP_CODE(+)=CNSU.STATUS
AND FND_LOOK.LANGUAGE = USERENV('LANG')
AND VEND.VENDOR_ID(+) = CNSU.VENDOR_ID
AND TASK.TASK_ID(+) = CNSU.TASK_ID
AND PROJ.PROJECT_ID(+) = CNSU.PROJECT_ID
AND CUL.CUN_LOCATION_ID(+) = CNSU.HZ_LOCATION_ID
AND FA_DIST_HIST.DISTRIBUTION_ID(+)=CNSU.FA_DISTRIBUTION_ID
AND FA_DIST_HIST.ASSET_ID = FA_ADD.ASSET_ID(+)
AND FA_DIST_HIST.LOCATION_ID = FA.LOCATION_ID(+)