View: CUN_TRANSACTIONS_V

View Text

SELECT CTR.TRANSACTION_ID
, CTR.TRANSACTION_DATE
, CTR.TRANSACTED_BY
, PER.FULL_NAME
, CTR.TRANSACTION_TYPE
, FND_LOOK_TXN_TYPE.MEANING
, FND_LOOK_TXN_TYPE.DESCRIPTION
, CTR.TRANSACTION_QUANTITY
, CTR.TRANSACTION_STATUS
, FND_LOOK_TXN_STATUS.MEANING TXN_STATUS_NAME
, FND_LOOK_TXN_STATUS.DESCRIPTION TXN_STATUS_DESCRIPTION
, CTR.ITEM_ID
, ITEM.CONCATENATED_SEGMENTS INV_ITEM
, ITEM.DESCRIPTION ITEM_DESCRIPTION
, CTR.ORGANIZATION_ID
, INV_ORG.NAME INV_ORGANIZATION_NAME
, CTR.QUANTITY_INVOICED
, CTR.STATUS
, FND_LOOK_STATUS.MEANING STATUS_NAME
, FND_LOOK_STATUS.DESCRIPTION STATUS_DESCRIPTION
, CTR.SUBINVENTORY
, CTR.LOCATOR_ID
, LOC.CONCATENATED_SEGMENTS LOCATOR
, CTR.REVISION
, CTR.SERIAL_NUMBER
, CTR.VENDOR_SERIAL_NUMBER
, CTR.LOT_NUMBER
, CTR.FA_DISTRIBUTION_ID
, FA_ADD.ASSET_NUMBER
, FA_ADD.DESCRIPTION ASSET_DESCRIPTION
, CUL.CUN_LOCATION_ID
, CUL.DESCRIPTION LOC_DESCRIPTION
, CTR.PO_DISTRIBUTION_ID
, POD.DISTRIBUTION_NUM
, CTR.PROJECT_ID
, PROJ.SEGMENT1 PROJECT_NUMBER
, CTR.TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, CTR.WORK_ORDER_NUMBER
, CTR.RELATED_TRANSACTION_ID
, CTR.LAST_TRANSACTION_ID
, CTR.PO_HEADER_ID
, PO.SEGMENT1 PO_NUMBER
, CTR.PO_LINE_ID
, CTR.RECEIPT_LINE_ID
, CTR.EXPENDITURE_ITEM_ID
, CTR.AP_INVOICE_ID
, AP_INV.INVOICE_NUM
, CTR.AP_INVOICE_DISTRIBUTION_ID
, AP_INV_DIST.DISTRIBUTION_LINE_NUMBER
, CTR.VENDOR_ID
, VEND.VENDOR_NAME VENDOR_NAME
, CTR.RCV_TRANSACTION_ID
, CTR.MTL_TRANSACTION_ID
, CTR.RTV_REASON_CODE
, CTR.RMA_NUMBER
, CTR.SHIPMENT_HEADER_ID
, RCV_HEAD.SHIPMENT_NUM SHIPMENT_NUMBER
, CTR.SHIPPED_DATE
, CTR.RECEIPT_DATE
, CTR.RECEIPT_SOURCE_CODE
, CTR.INSTALLED_DATE
, CTR.IN_SERVICE_DATE
, CTR.EFFECTIVE_DATE
, CTR.RETIRED_DATE
, CTR.FREIGHT_CARRIER_CODE
, CTR.WAYBILL_AIRBILL_NUM
, CTR.NETWORK_NUMBER
, CTR.MESSAGE_ID
, CTR.ASSET_GROUPING_ELEMENT1
, CTR.ASSET_GROUPING_ELEMENT2
, CTR.ASSET_GROUPING_ELEMENT3
, CTR.REFERENCE_DATA
FROM CUN_TRANSACTIONS CTR
, MTL_SYSTEM_ITEMS_KFV ITEM
, HR_ORGANIZATION_UNITS INV_ORG
, MTL_ITEM_LOCATIONS_KFV LOC
, FND_LOOKUP_VALUES FND_LOOK_STATUS
, FND_LOOKUP_VALUES FND_LOOK_TXN_TYPE
, FND_LOOKUP_VALUES FND_LOOK_TXN_STATUS
, PO_VENDORS VEND
, PO_HEADERS PO
, PO_DISTRIBUTIONS POD
, PA_TASKS TASK
, PA_PROJECTS_ALL PROJ
, CUN_LOCATIONS_V CUL
, FA_DISTRIBUTION_HISTORY FA_DIST_HIST
, FA_ADDITIONS FA_ADD
, AP_INVOICES AP_INV
, AP_INVOICE_DISTRIBUTIONS AP_INV_DIST
, RCV_SHIPMENT_HEADERS RCV_HEAD
, PER_PEOPLE_F PER
WHERE 1=1
AND ITEM.INVENTORY_ITEM_ID = CTR.ITEM_ID
AND ITEM.ORGANIZATION_ID = CTR.ORGANIZATION_ID
AND INV_ORG.ORGANIZATION_ID(+) = CTR.ORGANIZATION_ID
AND LOC.INVENTORY_LOCATION_ID(+) = CTR.LOCATOR_ID
AND LOC.ORGANIZATION_ID(+) = CTR.ORGANIZATION_ID
AND FND_LOOK_TXN_STATUS.LOOKUP_TYPE= 'CUN_TRANSACTION_STATUS'
AND FND_LOOK_TXN_STATUS.LOOKUP_CODE(+) = CTR.TRANSACTION_STATUS
AND FND_LOOK_TXN_STATUS.LANGUAGE=USERENV('LANG')
AND FND_LOOK_TXN_TYPE.LOOKUP_TYPE = 'CUN_TRANSACTION_TYPE'
AND FND_LOOK_TXN_TYPE.LOOKUP_CODE(+)= CTR.TRANSACTION_TYPE
AND FND_LOOK_TXN_TYPE.LANGUAGE=USERENV('LANG')
AND FND_LOOK_STATUS.LOOKUP_TYPE= 'CUN_UNIT_STATUS'
AND FND_LOOK_STATUS.LOOKUP_CODE(+) =CTR.STATUS
AND FND_LOOK_STATUS.LANGUAGE=USERENV('LANG')
AND PO.PO_HEADER_ID(+)=CTR.PO_HEADER_ID
AND CTR.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)
AND CTR.AP_INVOICE_ID = AP_INV.INVOICE_ID(+)
AND CTR.AP_INVOICE_DISTRIBUTION_ID = AP_INV_DIST.INVOICE_DISTRIBUTION_ID(+)
AND RCV_HEAD.SHIPMENT_HEADER_ID(+) = CTR.SHIPMENT_HEADER_ID
AND VEND.VENDOR_ID(+) = CTR.VENDOR_ID
AND TASK.TASK_ID(+) = CTR.TASK_ID
AND PROJ.PROJECT_ID(+) = CTR.PROJECT_ID
AND CUL.CUN_LOCATION_ID(+) = CTR.HZ_LOCATION_ID
AND FA_DIST_HIST.DISTRIBUTION_ID(+)=CTR.FA_DISTRIBUTION_ID
AND FA_DIST_HIST.ASSET_ID = FA_ADD.ASSET_ID(+)
AND PER.PERSON_ID(+) = CTR.TRANSACTED_BY

Columns