CSE_ASSET_TXN_HIST

Details

Schema: FUSION
Object owner: CSE
Object type: VIEW

Columns

Name
TRANSACTION_ID TRANSACTED_ASSET_ID ASSET_ID OBJECT_VERSION_NUMBER REQUEST_ID ITEM_ID ITEM_REVISION JOB_DEFINITION_NAME JOB_DEFINITION_PACKAGE SRC_SYSTEM_TYPE SRC_SYSTEM_ID SRC_SYSTEM_NAME SRC_TRANSACTION_DATE SRC_TRANSACTION_ID SRC_TRANSACTION_TYPE_ID SRC_TRANSACTION_TYPE SRC_TRANSACTION_QTY PROJECT_ID TASK_ID CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN QUANTITY OLD_QUANTITY SECONDARY_QUANTITY OLD_SECONDARY_QUANTITY ASSET_NUMBER OLD_ASSET_NUMBER CURRENT_LOCATION_CONTEXT OLD_CUR_LOCATION CURRENT_LOCATION_ID OLD_CUR_LOCATION_ID SERIAL_NUMBER OLD_SERIAL_NUMBER LOCATION_ORGANIZATION_ID OLD_LOCATION_ORG_ID LOT_NUMBER OLD_LOT_NUMBER SUBINVENTORY_CODE OLD_SUBINVENTORY_CODE INVENTORY_LOCATOR_ID OLD_INVENTORY_LOCATOR_ID SHIPMENT_DATE OLD_SHIPMENT_DATE UOM_CODE OLD_UOM_CODE SECONDARY_UOM_CODE OLD_SECONDARY_UOM_CODE WORK_ORDER_ID OLD_WORK_ORDER_ID WORK_ORDER_OPERATION_ID OLD_WORK_ORDER_OP_ID WORK_CENTER_ID OLD_WORK_CENTER_ID IOT_ENABLED_FLAG OLD_IOT_ENABLED_FLAG COMPETITOR_ASSET_FLAG OLD_COMPETITOR_ASSET_FLAG ITEM_ORGANIZATION_ID OLD_ITEM_ORGANIZATION_ID CONTACT_ID OLD_CONTACT_ID ACTIVE_END_DATE OLD_ACTIVE_END_DATE MAINTAINABLE_FLAG OLD_MAINTAINABLE_FLAG NEW_WO_ALLOWED_FLAG OLD_NEW_WO_FLAG DFLT_WO_TYPE OLD_DFLT_WO_TYPE DFLT_WO_SUB_TYPE OLD_DFLT_WO_SUB_TYPE CUSTOMER_ID OLD_CUSTOMER_ID CUSTOMER_SITE_PARTY_ID OLD_CUST_SITE_PARTY_ID CUSTOMER_SITE_ID OLD_CUST_SITE_ID CUSTOMER_BILLING_PARTY_ID OLD_CUST_BILLING_PARTY_ID CUSTOMER_BILLING_PARTY_SITE_ID OLD_CUST_BILLING_PARTY_SITE_ID CUSTOMER_ACCOUNT_ID OLD_CUST_ACCOUNT_ID CUSTOMER_ACCOUNT_SITE_ID OLD_CUST_ACCOUNT_SITE_ID CUSTOMER_ACCOUNT_SITE_USE_ID OLD_CUST_ACCOUNT_SITE_USE_ID SHIPMENT_ID OLD_SHIPMENT_ID CUSTOMER_ASSET_START_DATE OLD_CUST_ASSET_START_DATE CUSTOMER_ASSET_END_DATE OLD_CUST_ASSET_END_DATE SPLIT_FROM_ASSET_ID OLD_SPLIT_FROM_ASSET_ID INSTALLED_DATE OLD_INSTALLED_DATE ASSET_ATTRIBUTE_CHANGE ASSET_LOCATION_CHANGE INVENTORY_CHANGE SALES_ORDER_CHANGE WORK_ORDER_CHANGE ASSET_RELATIONSHIP_CHANGE

Name

TRANSACTION_ID

TRANSACTED_ASSET_ID

ASSET_ID

OBJECT_VERSION_NUMBER

REQUEST_ID

ITEM_ID

ITEM_REVISION

JOB_DEFINITION_NAME

JOB_DEFINITION_PACKAGE

SRC_SYSTEM_TYPE

SRC_SYSTEM_ID

SRC_SYSTEM_NAME

SRC_TRANSACTION_DATE

SRC_TRANSACTION_ID

SRC_TRANSACTION_TYPE_ID

SRC_TRANSACTION_TYPE

SRC_TRANSACTION_QTY

PROJECT_ID

TASK_ID

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

QUANTITY

OLD_QUANTITY

SECONDARY_QUANTITY

OLD_SECONDARY_QUANTITY

ASSET_NUMBER

OLD_ASSET_NUMBER

CURRENT_LOCATION_CONTEXT

OLD_CUR_LOCATION

CURRENT_LOCATION_ID

OLD_CUR_LOCATION_ID

SERIAL_NUMBER

OLD_SERIAL_NUMBER

LOCATION_ORGANIZATION_ID

OLD_LOCATION_ORG_ID

LOT_NUMBER

OLD_LOT_NUMBER

SUBINVENTORY_CODE

OLD_SUBINVENTORY_CODE

INVENTORY_LOCATOR_ID

OLD_INVENTORY_LOCATOR_ID

SHIPMENT_DATE

OLD_SHIPMENT_DATE

UOM_CODE

OLD_UOM_CODE

SECONDARY_UOM_CODE

OLD_SECONDARY_UOM_CODE

WORK_ORDER_ID

OLD_WORK_ORDER_ID

WORK_ORDER_OPERATION_ID

OLD_WORK_ORDER_OP_ID

WORK_CENTER_ID

OLD_WORK_CENTER_ID

IOT_ENABLED_FLAG

OLD_IOT_ENABLED_FLAG

COMPETITOR_ASSET_FLAG

OLD_COMPETITOR_ASSET_FLAG

ITEM_ORGANIZATION_ID

OLD_ITEM_ORGANIZATION_ID

CONTACT_ID

OLD_CONTACT_ID

ACTIVE_END_DATE

OLD_ACTIVE_END_DATE

MAINTAINABLE_FLAG

OLD_MAINTAINABLE_FLAG

NEW_WO_ALLOWED_FLAG

OLD_NEW_WO_FLAG

DFLT_WO_TYPE

OLD_DFLT_WO_TYPE

DFLT_WO_SUB_TYPE

OLD_DFLT_WO_SUB_TYPE

CUSTOMER_ID

OLD_CUSTOMER_ID

CUSTOMER_SITE_PARTY_ID

OLD_CUST_SITE_PARTY_ID

CUSTOMER_SITE_ID

OLD_CUST_SITE_ID

CUSTOMER_BILLING_PARTY_ID

OLD_CUST_BILLING_PARTY_ID

CUSTOMER_BILLING_PARTY_SITE_ID

OLD_CUST_BILLING_PARTY_SITE_ID

CUSTOMER_ACCOUNT_ID

OLD_CUST_ACCOUNT_ID

CUSTOMER_ACCOUNT_SITE_ID

OLD_CUST_ACCOUNT_SITE_ID

CUSTOMER_ACCOUNT_SITE_USE_ID

OLD_CUST_ACCOUNT_SITE_USE_ID

SHIPMENT_ID

OLD_SHIPMENT_ID

CUSTOMER_ASSET_START_DATE

OLD_CUST_ASSET_START_DATE

CUSTOMER_ASSET_END_DATE

OLD_CUST_ASSET_END_DATE

SPLIT_FROM_ASSET_ID

OLD_SPLIT_FROM_ASSET_ID

INSTALLED_DATE

OLD_INSTALLED_DATE

ASSET_ATTRIBUTE_CHANGE

ASSET_LOCATION_CHANGE

INVENTORY_CHANGE

SALES_ORDER_CHANGE

WORK_ORDER_CHANGE

ASSET_RELATIONSHIP_CHANGE

Query

SQL_Statement
SELECT transaction_id, transacted_asset_id, asset_id, object_version_number, request_id, item_id, item_revision, job_definition_name, job_definition_package, src_system_type, src_system_id, CASE WHEN src_system_type = 'ORA_INTERNAL' THEN ( SELECT application_name FROM fnd_application_vl WHERE application_id = src_system_id ) WHEN src_system_type = 'ORA_EXTERNAL' THEN ( SELECT orig_system FROM hz_orig_systems_b WHERE orig_system_id = src_system_id ) END AS src_system_name, src_transaction_date, src_transaction_id, src_transaction_type_id, CASE WHEN src_system_type = 'ORA_INTERNAL' AND src_system_id = 401 THEN ( SELECT transaction_type_name FROM inv_transaction_types_vl WHERE transaction_type_id = src_transaction_type_id ) WHEN src_system_type = 'ORA_INTERNAL' AND src_system_id = 10008 THEN ( SELECT meaning FROM fnd_lookups WHERE lookup_type = 'ORA_CSE_TRANSACTION_TYPE' AND lookup_code = src_transaction_type_id ) WHEN src_system_type = 'ORA_INTERNAL' AND src_system_id = 10049 THEN ( SELECT meaning FROM fnd_lookups WHERE lookup_type = 'RCV_TRANSACTION_TYPE' AND lookup_code = src_transaction_type_id ) WHEN src_system_type = 'ORA_INTERNAL' AND ( src_system_id = 10493 OR src_system_id = 10531 ) THEN ( SELECT meaning FROM fnd_lookups WHERE lookup_type IN ( 'ORA_WIE_OP_TRANSACTION_TYPE', 'ORA_WIE_MTL_TXN_TYPE', 'ORA_WIE_TRANSACTION_TYPES', 'ORA_CSE_TRANSACTION_TYPE' ) AND lookup_code = src_transaction_type_id ) END AS src_transaction_type, src_transaction_qty, project_id, task_id, created_by, creation_date, last_updated_by, last_update_date, last_update_login, quantity, old_quantity, secondary_quantity, old_secondary_quantity, asset_number, old_asset_number, current_location_context, old_cur_location, current_location_id, old_cur_location_id, serial_number, old_serial_number, location_organization_id, old_location_org_id, lot_number, old_lot_number, subinventory_code, old_subinventory_code, inventory_locator_id, old_inventory_locator_id, shipment_date, old_shipment_date, uom_code, old_uom_code, secondary_uom_code, old_secondary_uom_code, work_order_id, old_work_order_id, work_order_operation_id, old_work_order_op_id, work_center_id, old_work_center_id, iot_enabled_flag, old_iot_enabled_flag, competitor_asset_flag, old_competitor_asset_flag, item_organization_id, old_item_organization_id, contact_id, old_contact_id, active_end_date, old_active_end_date, maintainable_flag, old_maintainable_flag, new_wo_allowed_flag, old_new_wo_flag, dflt_wo_type, old_dflt_wo_type, dflt_wo_sub_type, old_dflt_wo_sub_type, customer_id, old_customer_id, customer_site_party_id, old_cust_site_party_id, customer_site_id, old_cust_site_id, customer_billing_party_id, old_cust_billing_party_id, customer_billing_party_site_id, old_cust_billing_party_site_id, customer_account_id, old_cust_account_id, customer_account_site_id, old_cust_account_site_id, customer_account_site_use_id, old_cust_account_site_use_id, shipment_id, old_shipment_id, customer_asset_start_date, old_cust_asset_start_date, customer_asset_end_date, old_cust_asset_end_date, split_from_asset_id, old_split_from_asset_id, installed_date, old_installed_date, CASE WHEN ( nvl(quantity,-1) != nvl(old_quantity,-1) OR nvl(secondary_quantity,-1) != nvl(old_secondary_quantity,-1) OR nvl(serial_number,'X') != nvl(old_serial_number,'X') OR nvl(asset_number,'X') != nvl(old_asset_number,'X') OR nvl(iot_enabled_flag,'X') != nvl(old_iot_enabled_flag,'X') OR nvl(competitor_asset_flag,'X') != nvl(old_competitor_asset_flag,'X') OR nvl(item_organization_id,-1) != nvl(old_item_organization_id,-1) OR nvl(contact_id,-1) != nvl(old_contact_id,-1) OR nvl(active_end_date,SYSDATE) != nvl(old_active_end_date,SYSDATE) OR nvl(maintainable_flag,'X') != nvl(old_maintainable_flag,'X') OR nvl(new_wo_allowed_flag,'X') != nvl(old_new_wo_flag,'X') OR nvl(dflt_wo_type,'X') != nvl(old_dflt_wo_type,'X') OR nvl(dflt_wo_sub_type,'X') != nvl(old_dflt_wo_sub_type,'X') OR nvl(customer_id,-1) != nvl(old_customer_id,-1) OR nvl(customer_site_party_id,-1) != nvl(old_cust_site_party_id,-1) OR nvl(customer_site_id,-1) != nvl(old_cust_site_id,-1) OR nvl(customer_billing_party_id,-1) != nvl(old_cust_billing_party_id,-1) OR nvl(customer_billing_party_site_id,-1) != nvl(old_cust_billing_party_site_id,-1) OR nvl(customer_account_id,-1) != nvl(old_cust_account_id,-1) OR nvl(customer_account_site_id,-1) != nvl(old_cust_account_site_id,-1) OR nvl(customer_account_site_use_id,-1) != nvl(old_cust_account_site_use_id,-1) OR nvl(shipment_id,-1) != nvl(old_shipment_id,-1) OR nvl(customer_asset_start_date,SYSDATE) != nvl(old_cust_asset_start_date,SYSDATE) OR nvl(customer_asset_end_date,SYSDATE) != nvl(old_cust_asset_end_date,SYSDATE) OR nvl(installed_date,SYSDATE) != nvl(old_installed_date,SYSDATE)) THEN 'Y' END AS asset_attribute_change, CASE WHEN ( nvl(current_location_context,'X') != nvl(old_cur_location,'X') OR nvl(current_location_id,-1) != nvl(old_cur_location_id,-1) OR nvl(work_center_id,-1) != nvl(old_work_center_id,-1) OR nvl(work_order_id,-1) != nvl(old_work_order_id,-1) OR nvl(work_order_operation_id,-1) != nvl(old_work_order_op_id,-1) OR nvl(subinventory_code,'X') != nvl(old_subinventory_code,'X') OR nvl(inventory_locator_id,-1) != nvl(old_inventory_locator_id,-1) ) THEN 'Y' END AS asset_location_change, CASE WHEN src_system_id = 401 THEN 'Y' END AS inventory_change, CASE WHEN ( src_system_id = 10008 OR src_system_id = 10049 ) THEN 'Y' END AS sales_order_change, CASE WHEN ( ( src_system_id = 10493 OR src_system_id = 10531 ) AND ( work_order_id != nvl(old_work_order_id,-1) ) ) THEN 'Y' END AS work_order_change, CASE WHEN ( EXISTS ( SELECT 1 FROM cse_asset_relationships_hist cse_rel_hist, cse_transacted_relationships cse_rel WHERE ( cse_rel_hist.object_id = ASSET_TXN_OUTER.asset_id ) AND cse_rel_hist.transacted_relationship_id = cse_rel.transacted_relationship_id AND cse_rel.transaction_id = ASSET_TXN_OUTER.transaction_id ) ) THEN 'Y' WHEN ( EXISTS ( SELECT 1 FROM cse_asset_relationships_hist cse_rel_hist, cse_transacted_relationships cse_rel WHERE ( cse_rel_hist.subject_id = ASSET_TXN_OUTER.asset_id ) AND cse_rel_hist.transacted_relationship_id = cse_rel.transacted_relationship_id AND cse_rel.transaction_id = ASSET_TXN_OUTER.transaction_id ) ) THEN 'Y' END AS asset_relationship_change FROM ( SELECT cse_hist.transacted_asset_id, cse_hist.asset_id, cse_txn.object_version_number, cse_txn.transaction_id, cse_txn.request_id, cse_txn.item_id, cse_txn.item_revision, cse_txn.job_definition_name, cse_txn.job_definition_package, cse_txn.src_system_type, cse_txn.src_system_id, cse_txn.src_transaction_date, cse_txn.src_transaction_id, cse_txn.src_transaction_type_id, cse_txn.src_transaction_qty, cse_txn.project_id, cse_txn.task_id, cse_txn.created_by, cse_txn.creation_date, cse_txn.last_updated_by, cse_txn.last_update_date, cse_txn.last_update_login, cse_hist.quantity, LAG(cse_hist.quantity) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_quantity, cse_hist.secondary_quantity, LAG(cse_hist.secondary_quantity) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_secondary_quantity, cse_hist.asset_number, LAG(cse_hist.asset_number) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_asset_number, cse_hist.serial_number, LAG(cse_hist.serial_number) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_serial_number, cse_hist.location_organization_id, LAG(cse_hist.location_organization_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_location_org_id, cse_hist.lot_number, LAG(cse_hist.lot_number) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_lot_number, cse_hist.subinventory_code, LAG(cse_hist.subinventory_code) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_subinventory_code, cse_hist.inventory_locator_id, LAG(cse_hist.inventory_locator_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC ,cse_txn.src_system_id ASC ) AS old_inventory_locator_id, cse_hist.shipment_date, LAG(cse_hist.shipment_date) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_shipment_date, cse_hist.uom_code, LAG(cse_hist.uom_code) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_uom_code, cse_hist.secondary_uom_code, LAG(cse_hist.secondary_uom_code) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_secondary_uom_code, cse_hist.current_location_context, LAG(cse_hist.current_location_context) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cur_location, cse_hist.current_location_id, LAG(cse_hist.current_location_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cur_location_id, cse_hist.work_order_id, LAG(cse_hist.work_order_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_work_order_id, cse_hist.work_order_operation_id, LAG(cse_hist.work_order_operation_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_work_order_op_id, cse_hist.work_center_id, LAG(cse_hist.work_center_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_work_center_id, cse_hist.iot_enabled_flag, LAG(cse_hist.iot_enabled_flag) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_iot_enabled_flag, cse_hist.competitor_asset_flag, LAG(cse_hist.competitor_asset_flag) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_competitor_asset_flag, cse_hist.item_organization_id, LAG(cse_hist.item_organization_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_item_organization_id, cse_hist.contact_id, LAG(cse_hist.contact_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_contact_id, cse_hist.active_end_date, LAG(cse_hist.active_end_date) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_active_end_date, cse_hist.maintainable_flag, LAG(cse_hist.maintainable_flag) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_maintainable_flag, cse_hist.new_wo_allowed_flag, LAG(cse_hist.new_wo_allowed_flag) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_new_wo_flag, cse_hist.dflt_wo_type, LAG(cse_hist.dflt_wo_type) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_dflt_wo_type, cse_hist.dflt_wo_sub_type, LAG(cse_hist.dflt_wo_sub_type) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_dflt_wo_sub_type, cse_hist.customer_id, LAG(cse_hist.customer_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_customer_id, cse_hist.customer_site_party_id, LAG(cse_hist.customer_site_party_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_site_party_id, cse_hist.customer_site_id, LAG(cse_hist.customer_site_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_site_id, cse_hist.customer_billing_party_id, LAG(cse_hist.customer_billing_party_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_billing_party_id, cse_hist.customer_billing_party_site_id, LAG(cse_hist.customer_billing_party_site_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_billing_party_site_id, cse_hist.customer_account_id, LAG(cse_hist.customer_account_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_account_id, cse_hist.customer_account_site_id, LAG(cse_hist.customer_account_site_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_account_site_id, cse_hist.customer_account_site_use_id, LAG(cse_hist.customer_account_site_use_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_account_site_use_id, cse_hist.shipment_id, LAG(cse_hist.shipment_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_shipment_id, cse_hist.customer_asset_start_date, LAG(cse_hist.customer_asset_start_date) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_asset_start_date, cse_hist.customer_asset_end_date, LAG(cse_hist.customer_asset_end_date) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_cust_asset_end_date, cse_hist.split_from_asset_id, LAG(cse_hist.split_from_asset_id) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_split_from_asset_id, cse_hist.installed_date, LAG(cse_hist.installed_date) OVER( PARTITION BY cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC ) AS old_installed_date FROM cse_assets_hist cse_hist, cse_transactions cse_txn, cse_transacted_assets cse_tx_assets WHERE cse_tx_assets.transaction_id = cse_txn.transaction_id AND cse_hist.transacted_asset_id = cse_tx_assets.transacted_asset_id AND cse_tx_assets.asset_id = cse_hist.asset_id ORDER BY cse_txn.src_transaction_date ASC,cse_txn.src_system_id ASC) ASSET_TXN_OUTER

SQL_Statement

SELECT

transaction_id,

transacted_asset_id,

asset_id,

object_version_number,

request_id,

item_id,

item_revision,

job_definition_name,

job_definition_package,

src_system_type,

src_system_id,

CASE

WHEN src_system_type = 'ORA_INTERNAL' THEN (

SELECT

application_name

FROM

fnd_application_vl

WHERE

application_id = src_system_id

)

WHEN src_system_type = 'ORA_EXTERNAL' THEN (

SELECT

orig_system

FROM

hz_orig_systems_b

WHERE

orig_system_id = src_system_id

)

END AS src_system_name,

src_transaction_date,

src_transaction_id,

src_transaction_type_id,

CASE

WHEN src_system_type = 'ORA_INTERNAL'

AND src_system_id = 401 THEN (

SELECT

transaction_type_name

FROM

inv_transaction_types_vl

WHERE

transaction_type_id = src_transaction_type_id

)

WHEN src_system_type = 'ORA_INTERNAL'

AND src_system_id = 10008

THEN (

SELECT

meaning

FROM

fnd_lookups

WHERE

lookup_type = 'ORA_CSE_TRANSACTION_TYPE'

AND lookup_code = src_transaction_type_id

)

WHEN src_system_type = 'ORA_INTERNAL'

AND src_system_id = 10049

THEN (

SELECT

meaning

FROM

fnd_lookups

WHERE

lookup_type = 'RCV_TRANSACTION_TYPE'

AND lookup_code = src_transaction_type_id

)

WHEN src_system_type = 'ORA_INTERNAL'

AND ( src_system_id = 10493

OR src_system_id = 10531 ) THEN (

SELECT

meaning

FROM

fnd_lookups

WHERE

lookup_type IN (

'ORA_WIE_OP_TRANSACTION_TYPE',

'ORA_WIE_MTL_TXN_TYPE',

'ORA_WIE_TRANSACTION_TYPES',

'ORA_CSE_TRANSACTION_TYPE'

)

AND lookup_code = src_transaction_type_id

)

END AS src_transaction_type,

src_transaction_qty,

project_id,

task_id,

created_by,

creation_date,

last_updated_by,

last_update_date,

last_update_login,

quantity,

old_quantity,

secondary_quantity,

old_secondary_quantity,

asset_number,

old_asset_number,

current_location_context,

old_cur_location,

current_location_id,

old_cur_location_id,

serial_number,

old_serial_number,

location_organization_id,

old_location_org_id,

lot_number,

old_lot_number,

subinventory_code,

old_subinventory_code,

inventory_locator_id,

old_inventory_locator_id,

shipment_date,

old_shipment_date,

uom_code,

old_uom_code,

secondary_uom_code,

old_secondary_uom_code,

work_order_id,

old_work_order_id,

work_order_operation_id,

old_work_order_op_id,

work_center_id,

old_work_center_id,

iot_enabled_flag,

old_iot_enabled_flag,

competitor_asset_flag,

old_competitor_asset_flag,

item_organization_id,

old_item_organization_id,

contact_id,

old_contact_id,

active_end_date,

old_active_end_date,

maintainable_flag,

old_maintainable_flag,

new_wo_allowed_flag,

old_new_wo_flag,

dflt_wo_type,

old_dflt_wo_type,

dflt_wo_sub_type,

old_dflt_wo_sub_type,

customer_id,

old_customer_id,

customer_site_party_id,

old_cust_site_party_id,

customer_site_id,

old_cust_site_id,

customer_billing_party_id,

old_cust_billing_party_id,

customer_billing_party_site_id,

old_cust_billing_party_site_id,

customer_account_id,

old_cust_account_id,

customer_account_site_id,

old_cust_account_site_id,

customer_account_site_use_id,

old_cust_account_site_use_id,

shipment_id,

old_shipment_id,

customer_asset_start_date,

old_cust_asset_start_date,

customer_asset_end_date,

old_cust_asset_end_date,

split_from_asset_id,

old_split_from_asset_id,

installed_date,

old_installed_date,

CASE

WHEN ( nvl(quantity,-1) != nvl(old_quantity,-1)

OR nvl(secondary_quantity,-1) != nvl(old_secondary_quantity,-1)

OR nvl(serial_number,'X') != nvl(old_serial_number,'X')

OR nvl(asset_number,'X') != nvl(old_asset_number,'X')

OR nvl(iot_enabled_flag,'X') != nvl(old_iot_enabled_flag,'X')

OR nvl(competitor_asset_flag,'X') != nvl(old_competitor_asset_flag,'X')

OR nvl(item_organization_id,-1) != nvl(old_item_organization_id,-1)

OR nvl(contact_id,-1) != nvl(old_contact_id,-1)

OR nvl(active_end_date,SYSDATE) != nvl(old_active_end_date,SYSDATE)

OR nvl(maintainable_flag,'X') != nvl(old_maintainable_flag,'X')

OR nvl(new_wo_allowed_flag,'X') != nvl(old_new_wo_flag,'X')

OR nvl(dflt_wo_type,'X') != nvl(old_dflt_wo_type,'X')

OR nvl(dflt_wo_sub_type,'X') != nvl(old_dflt_wo_sub_type,'X')

OR nvl(customer_id,-1) != nvl(old_customer_id,-1)

OR nvl(customer_site_party_id,-1) != nvl(old_cust_site_party_id,-1)

OR nvl(customer_site_id,-1) != nvl(old_cust_site_id,-1)

OR nvl(customer_billing_party_id,-1) != nvl(old_cust_billing_party_id,-1)

OR nvl(customer_billing_party_site_id,-1) != nvl(old_cust_billing_party_site_id,-1)

OR nvl(customer_account_id,-1) != nvl(old_cust_account_id,-1)

OR nvl(customer_account_site_id,-1) != nvl(old_cust_account_site_id,-1)

OR nvl(customer_account_site_use_id,-1) != nvl(old_cust_account_site_use_id,-1)

OR nvl(shipment_id,-1) != nvl(old_shipment_id,-1)

OR nvl(customer_asset_start_date,SYSDATE) != nvl(old_cust_asset_start_date,SYSDATE)

OR nvl(customer_asset_end_date,SYSDATE) != nvl(old_cust_asset_end_date,SYSDATE)

OR nvl(installed_date,SYSDATE) != nvl(old_installed_date,SYSDATE)) THEN 'Y'

END AS asset_attribute_change,

CASE

WHEN ( nvl(current_location_context,'X') != nvl(old_cur_location,'X')

OR nvl(current_location_id,-1) != nvl(old_cur_location_id,-1)

OR nvl(work_center_id,-1) != nvl(old_work_center_id,-1)

OR nvl(work_order_id,-1) != nvl(old_work_order_id,-1)

OR nvl(work_order_operation_id,-1) != nvl(old_work_order_op_id,-1)

OR nvl(subinventory_code,'X') != nvl(old_subinventory_code,'X')

OR nvl(inventory_locator_id,-1) != nvl(old_inventory_locator_id,-1) ) THEN 'Y'

END AS asset_location_change,

CASE

WHEN src_system_id = 401 THEN 'Y'

END AS inventory_change,

CASE

WHEN ( src_system_id = 10008

OR src_system_id = 10049 ) THEN 'Y'

END AS sales_order_change,

CASE

WHEN ( ( src_system_id = 10493

OR src_system_id = 10531 )

AND ( work_order_id != nvl(old_work_order_id,-1) ) ) THEN 'Y'

END AS work_order_change,

CASE

WHEN ( EXISTS (

SELECT

1

FROM

cse_asset_relationships_hist cse_rel_hist,

cse_transacted_relationships cse_rel

WHERE

( cse_rel_hist.object_id = ASSET_TXN_OUTER.asset_id )

AND cse_rel_hist.transacted_relationship_id = cse_rel.transacted_relationship_id

AND cse_rel.transaction_id = ASSET_TXN_OUTER.transaction_id

) ) THEN 'Y'

WHEN ( EXISTS (

SELECT

1

FROM

cse_asset_relationships_hist cse_rel_hist,

cse_transacted_relationships cse_rel

WHERE

( cse_rel_hist.subject_id = ASSET_TXN_OUTER.asset_id )

AND cse_rel_hist.transacted_relationship_id = cse_rel.transacted_relationship_id

AND cse_rel.transaction_id = ASSET_TXN_OUTER.transaction_id

) ) THEN 'Y'

END AS asset_relationship_change

FROM

(

SELECT

cse_hist.transacted_asset_id,

cse_hist.asset_id,

cse_txn.object_version_number,

cse_txn.transaction_id,

cse_txn.request_id,

cse_txn.item_id,

cse_txn.item_revision,

cse_txn.job_definition_name,

cse_txn.job_definition_package,

cse_txn.src_system_type,

cse_txn.src_system_id,

cse_txn.src_transaction_date,

cse_txn.src_transaction_id,

cse_txn.src_transaction_type_id,

cse_txn.src_transaction_qty,

cse_txn.project_id,

cse_txn.task_id,

cse_txn.created_by,

cse_txn.creation_date,

cse_txn.last_updated_by,

cse_txn.last_update_date,

cse_txn.last_update_login,

cse_hist.quantity,

LAG(cse_hist.quantity) OVER(