VIEW: APPS.EAM_CHECKED_OUT_ASSETS_V

Source

View Text - Preformatted

SELECT cii.rowid row_id , cii.instance_id , cii.external_reference , cii.inventory_item_id , cii.inventory_revision , cii.inv_master_organization_id , cii.serial_number , cii.mfg_serial_number_flag , cii.lot_number , cii.quantity , cii.unit_of_measure , cii.accounting_class_code , cii.instance_condition_id , cii.instance_status_id , cii.customer_view_flag , cii.merchant_view_flag , cii.sellable_flag , cii.system_id , cii.instance_type_code , cii.active_start_date , cii.active_end_date , cii.location_id , cii.inv_organization_id , cii.inv_subinventory_name , cii.inv_locator_id , cii.pa_project_id , cii.pa_project_task_id , cii.in_transit_order_line_id , cii.wip_job_id , cii.po_order_line_id , cii.last_oe_order_line_id , cii.last_oe_rma_line_id , cii.last_po_po_line_id , cii.last_oe_po_number , cii.last_wip_job_id , cii.last_pa_project_id , cii.last_pa_task_id , cii.last_oe_agreement_id , cii.install_date , cii.manually_created_flag , cii.return_by_date , cii.actual_return_date , cii.creation_complete_flag , cii.completeness_flag , cii.context , cii.attribute1 , cii.attribute2 , cii.attribute3 , cii.attribute4 , cii.attribute5 , cii.attribute6 , cii.attribute7 , cii.attribute8 , cii.attribute9 , cii.attribute10 , cii.attribute11 , cii.attribute12 , cii.attribute13 , cii.attribute14 , cii.attribute15 , cii.attribute16 , cii.attribute17 , cii.attribute18 , cii.attribute19 , cii.attribute20 , cii.attribute21 , cii.attribute22 , cii.attribute23 , cii.attribute24 , cii.attribute25 , cii.attribute26 , cii.attribute27 , cii.attribute28 , cii.attribute29 , cii.attribute30 , cii.created_by , cii.creation_date , cii.last_updated_by , cii.last_update_date , cii.last_update_login , cii.object_version_number , cii.security_group_id , cii.last_txn_line_detail_id , cii.install_location_type_code , cii.install_location_id , cii.instance_usage_code , cii.owner_party_source_table , cii.owner_party_id , cii.owner_party_account_id , cii.last_vld_organization_id , cii.migrated_flag , cii.request_id , cii.program_application_id , cii.program_id , cii.program_update_date , cii.config_inst_hdr_id , cii.config_inst_rev_num , cii.config_inst_item_id , cii.config_valid_status , cii.last_purge_date , cii.network_asset_flag , cii.maintainable_flag , cii.pn_location_id , cii.asset_criticality_code , cii.category_id , cii.equipment_gen_object_id , cii.instantiation_flag , cii.linear_location_id , cii.operational_log_flag , cii.supplier_warranty_exp_date , cii.purchase_unit_price , cii.purchase_currency_code , cii.payables_unit_price , cii.payables_currency_code , cii.sales_unit_price , cii.sales_currency_code , cii.operational_status_code , cii.instance_description Asset_Desc , eaot.txn_date , eaot.user_id , cii.checkin_status , cii.instance_number Asset_Number , mp.maint_organization_id , cii.location_type_code FROM csi_item_instances cii, eam_asset_operation_txn eaot, mtl_parameters mp WHERE cii.instance_id = eaot.instance_id AND cii.last_vld_organization_id = mp.organization_id AND eaot.txn_type = 2 AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate AND nvl(cii.network_asset_flag,'N') <> 'Y' AND eaot.txn_date = ( SELECT max(txn_date) FROM eam_asset_operation_txn eaot1 WHERE eaot1.instance_id = cii.instance_id )

View Text - HTML Formatted

SELECT CII.ROWID ROW_ID
, CII.INSTANCE_ID
, CII.EXTERNAL_REFERENCE
, CII.INVENTORY_ITEM_ID
, CII.INVENTORY_REVISION
, CII.INV_MASTER_ORGANIZATION_ID
, CII.SERIAL_NUMBER
, CII.MFG_SERIAL_NUMBER_FLAG
, CII.LOT_NUMBER
, CII.QUANTITY
, CII.UNIT_OF_MEASURE
, CII.ACCOUNTING_CLASS_CODE
, CII.INSTANCE_CONDITION_ID
, CII.INSTANCE_STATUS_ID
, CII.CUSTOMER_VIEW_FLAG
, CII.MERCHANT_VIEW_FLAG
, CII.SELLABLE_FLAG
, CII.SYSTEM_ID
, CII.INSTANCE_TYPE_CODE
, CII.ACTIVE_START_DATE
, CII.ACTIVE_END_DATE
, CII.LOCATION_ID
, CII.INV_ORGANIZATION_ID
, CII.INV_SUBINVENTORY_NAME
, CII.INV_LOCATOR_ID
, CII.PA_PROJECT_ID
, CII.PA_PROJECT_TASK_ID
, CII.IN_TRANSIT_ORDER_LINE_ID
, CII.WIP_JOB_ID
, CII.PO_ORDER_LINE_ID
, CII.LAST_OE_ORDER_LINE_ID
, CII.LAST_OE_RMA_LINE_ID
, CII.LAST_PO_PO_LINE_ID
, CII.LAST_OE_PO_NUMBER
, CII.LAST_WIP_JOB_ID
, CII.LAST_PA_PROJECT_ID
, CII.LAST_PA_TASK_ID
, CII.LAST_OE_AGREEMENT_ID
, CII.INSTALL_DATE
, CII.MANUALLY_CREATED_FLAG
, CII.RETURN_BY_DATE
, CII.ACTUAL_RETURN_DATE
, CII.CREATION_COMPLETE_FLAG
, CII.COMPLETENESS_FLAG
, CII.CONTEXT
, CII.ATTRIBUTE1
, CII.ATTRIBUTE2
, CII.ATTRIBUTE3
, CII.ATTRIBUTE4
, CII.ATTRIBUTE5
, CII.ATTRIBUTE6
, CII.ATTRIBUTE7
, CII.ATTRIBUTE8
, CII.ATTRIBUTE9
, CII.ATTRIBUTE10
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, CII.ATTRIBUTE16
, CII.ATTRIBUTE17
, CII.ATTRIBUTE18
, CII.ATTRIBUTE19
, CII.ATTRIBUTE20
, CII.ATTRIBUTE21
, CII.ATTRIBUTE22
, CII.ATTRIBUTE23
, CII.ATTRIBUTE24
, CII.ATTRIBUTE25
, CII.ATTRIBUTE26
, CII.ATTRIBUTE27
, CII.ATTRIBUTE28
, CII.ATTRIBUTE29
, CII.ATTRIBUTE30
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN
, CII.OBJECT_VERSION_NUMBER
, CII.SECURITY_GROUP_ID
, CII.LAST_TXN_LINE_DETAIL_ID
, CII.INSTALL_LOCATION_TYPE_CODE
, CII.INSTALL_LOCATION_ID
, CII.INSTANCE_USAGE_CODE
, CII.OWNER_PARTY_SOURCE_TABLE
, CII.OWNER_PARTY_ID
, CII.OWNER_PARTY_ACCOUNT_ID
, CII.LAST_VLD_ORGANIZATION_ID
, CII.MIGRATED_FLAG
, CII.REQUEST_ID
, CII.PROGRAM_APPLICATION_ID
, CII.PROGRAM_ID
, CII.PROGRAM_UPDATE_DATE
, CII.CONFIG_INST_HDR_ID
, CII.CONFIG_INST_REV_NUM
, CII.CONFIG_INST_ITEM_ID
, CII.CONFIG_VALID_STATUS
, CII.LAST_PURGE_DATE
, CII.NETWORK_ASSET_FLAG
, CII.MAINTAINABLE_FLAG
, CII.PN_LOCATION_ID
, CII.ASSET_CRITICALITY_CODE
, CII.CATEGORY_ID
, CII.EQUIPMENT_GEN_OBJECT_ID
, CII.INSTANTIATION_FLAG
, CII.LINEAR_LOCATION_ID
, CII.OPERATIONAL_LOG_FLAG
, CII.SUPPLIER_WARRANTY_EXP_DATE
, CII.PURCHASE_UNIT_PRICE
, CII.PURCHASE_CURRENCY_CODE
, CII.PAYABLES_UNIT_PRICE
, CII.PAYABLES_CURRENCY_CODE
, CII.SALES_UNIT_PRICE
, CII.SALES_CURRENCY_CODE
, CII.OPERATIONAL_STATUS_CODE
, CII.INSTANCE_DESCRIPTION ASSET_DESC
, EAOT.TXN_DATE
, EAOT.USER_ID
, CII.CHECKIN_STATUS
, CII.INSTANCE_NUMBER ASSET_NUMBER
, MP.MAINT_ORGANIZATION_ID
, CII.LOCATION_TYPE_CODE
FROM CSI_ITEM_INSTANCES CII
, EAM_ASSET_OPERATION_TXN EAOT
, MTL_PARAMETERS MP
WHERE CII.INSTANCE_ID = EAOT.INSTANCE_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EAOT.TXN_TYPE = 2
AND NVL(CII.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND NVL(CII.NETWORK_ASSET_FLAG
, 'N') <> 'Y'
AND EAOT.TXN_DATE = ( SELECT MAX(TXN_DATE)
FROM EAM_ASSET_OPERATION_TXN EAOT1
WHERE EAOT1.INSTANCE_ID = CII.INSTANCE_ID )