CSE_ASSET_TXN_REL_HIST

Details

Schema: FUSION
Object owner: CSE
Object type: VIEW

Columns

Name
TRANSACTION_ID PARENT_ASSET_ID PARENT_ASSET_NUMBER CHILD_ASSET_ID CHILD_ASSET_NUMBER CHANGE_TYPE SRC_TRANSACTION_DATE SRC_TRANSACTION_ID WORK_ORDER_ID ASSET_ID SRC_TRANSACTION_TYPE_ID

Name

TRANSACTION_ID

PARENT_ASSET_ID

PARENT_ASSET_NUMBER

CHILD_ASSET_ID

CHILD_ASSET_NUMBER

CHANGE_TYPE

SRC_TRANSACTION_DATE

SRC_TRANSACTION_ID

WORK_ORDER_ID

ASSET_ID

SRC_TRANSACTION_TYPE_ID

Query

SQL_Statement
SELECT cse_txn.transaction_id, cse_rel_hist.object_id parent_asset_id, parent_asset.asset_number AS parent_asset_number, cse_rel_hist.subject_id child_asset_id, child_asset.asset_number AS child_asset_number, DECODE(cse_rel_hist.active_end_date,NULL,'ORA_CSE_CHG_INSTALLED','ORA_CSE_CHG_REMOVED') AS change_type, cse_txn.src_transaction_date, cse_txn.src_transaction_id, cse_tx_assets.work_order_id, cse_tx_assets.asset_id, cse_txn.src_transaction_type_id FROM cse_transactions cse_txn, cse_transacted_assets cse_tx_assets, cse_asset_relationships_hist cse_rel_hist, cse_transacted_relationships cse_tx_rel, cse_assets_b parent_asset, cse_assets_b child_asset WHERE cse_tx_assets.transaction_id = cse_txn.transaction_id AND cse_tx_rel.transaction_id = cse_txn.transaction_id AND cse_rel_hist.transacted_relationship_id = cse_tx_rel.transacted_relationship_id AND ((cse_rel_hist.object_id = cse_tx_assets.asset_id AND cse_rel_hist.object_type = 'A') OR (cse_rel_hist.subject_id = cse_tx_assets.asset_id AND cse_rel_hist.subject_type = 'A')) AND cse_rel_hist.relationship_type_code = 'ORA_COMPONENT' AND cse_rel_hist.potential_flag = 'N' AND parent_asset.asset_id = cse_rel_hist.object_id AND child_asset.asset_id = cse_rel_hist.subject_id UNION SELECT cse_txn.transaction_id, cse_rel_hist.object_id parent_asset_id, parent_asset.asset_number AS parent_asset_number, cse_rel_hist.subject_id child_asset_id, child_asset.asset_number AS child_asset_number, 'ORA_CSE_CHG_REMOVED' AS change_type, cse_txn.src_transaction_date, cse_txn.src_transaction_id, work_order.work_order_id, cse_tx_assets.asset_id, cse_txn.src_transaction_type_id FROM cse_transactions cse_txn, cse_transacted_assets cse_tx_assets, cse_asset_relationships_hist cse_rel_hist, inv_material_txns inv_mtl_txn, wie_work_orders_b work_order, inv_transaction_types_b inv_misc_receipt, cse_assets_b parent_asset, cse_assets_b child_asset WHERE cse_tx_assets.transaction_id = cse_txn.transaction_id AND ((cse_rel_hist.object_id = cse_tx_assets.asset_id AND cse_rel_hist.object_type = 'A') OR (cse_rel_hist.subject_id = cse_tx_assets.asset_id AND cse_rel_hist.subject_type = 'A')) AND cse_rel_hist.relationship_type_code = 'ORA_COMPONENT' AND cse_rel_hist.potential_flag = 'N' AND cse_txn.src_transaction_id = inv_mtl_txn.transaction_id AND inv_mtl_txn.transaction_source_id = work_order.WORK_ORDER_ID AND cse_txn.src_transaction_type_id = inv_misc_receipt.transaction_type_id AND cse_txn.src_transaction_type_id = '42' AND cse_txn.src_system_id = 401 AND parent_asset.asset_id = cse_rel_hist.object_id AND child_asset.asset_id = cse_rel_hist.subject_id

SQL_Statement

SELECT

cse_txn.transaction_id,

cse_rel_hist.object_id parent_asset_id,

parent_asset.asset_number AS parent_asset_number,

cse_rel_hist.subject_id child_asset_id,

child_asset.asset_number AS child_asset_number,

DECODE(cse_rel_hist.active_end_date,NULL,'ORA_CSE_CHG_INSTALLED','ORA_CSE_CHG_REMOVED') AS change_type,

cse_txn.src_transaction_date,

cse_txn.src_transaction_id,

cse_tx_assets.work_order_id,

cse_tx_assets.asset_id,

cse_txn.src_transaction_type_id

FROM

cse_transactions cse_txn,

cse_transacted_assets cse_tx_assets,

cse_asset_relationships_hist cse_rel_hist,

cse_transacted_relationships cse_tx_rel,

cse_assets_b parent_asset,

cse_assets_b child_asset

WHERE

cse_tx_assets.transaction_id = cse_txn.transaction_id

AND cse_tx_rel.transaction_id = cse_txn.transaction_id

AND cse_rel_hist.transacted_relationship_id = cse_tx_rel.transacted_relationship_id

AND ((cse_rel_hist.object_id = cse_tx_assets.asset_id AND cse_rel_hist.object_type = 'A')

OR (cse_rel_hist.subject_id = cse_tx_assets.asset_id AND cse_rel_hist.subject_type = 'A'))

AND cse_rel_hist.relationship_type_code = 'ORA_COMPONENT'

AND cse_rel_hist.potential_flag = 'N'

AND parent_asset.asset_id = cse_rel_hist.object_id

AND child_asset.asset_id = cse_rel_hist.subject_id

UNION