VIEW: APPS.AHL_PART_CHANGES_V

Source

View Text - Preformatted

SELECT PCH.part_change_id, PCH.OBJECT_VERSION_NUMBER, PCH.LAST_UPDATE_DATE, PCH.LAST_UPDATED_BY, PCH.CREATION_DATE, PCH.CREATED_BY , PCH.LAST_UPDATE_LOGIN, PCH.UNIT_CONFIG_HEADER_ID, PCH.REMOVAL_CODE, PCH.MC_RELATIONSHIP_ID, PCH.REMOVAL_REASON_ID, PCH.WORKORDER_OPERATION_ID, PCH.REMOVAL_DATE, PCH.STATUS_ID, PCH.ATTRIBUTE_CATEGORY, PCH.ATTRIBUTE1, PCH.ATTRIBUTE2, PCH.ATTRIBUTE3, PCH.ATTRIBUTE4, PCH.ATTRIBUTE5, PCH.ATTRIBUTE6, PCH.ATTRIBUTE7, PCH.ATTRIBUTE8, PCH.ATTRIBUTE9, PCH.ATTRIBUTE10, PCH.ATTRIBUTE11, PCH.ATTRIBUTE12, PCH.ATTRIBUTE13, PCH.ATTRIBUTE14, PCH.ATTRIBUTE15, PCH.part_change_type, decode(PCH.part_change_type, 'R', PCI.Part_Change_id, PCH.Part_Change_id), decode(PCH.part_change_type, 'R', PCI.installed_instance_id, PCH.installed_instance_id) Installed_Instance_id, PCH.removed_instance_id, decode(PCH.part_change_type, 'R', PCI.issue_mtl_txn_id, PCH.issue_mtl_txn_id) Issue_Mtl_Txn_Id, PCH.return_mtl_txn_id, PCH.Quantity Removed_Quantity, decode(PCH.part_change_type,'R',PCI.Quantity,PCH.Quantity) Installed_Quantity, decode(PCH.part_change_type, 'R', PCI.installation_date, PCH.installation_date) Installation_Date from (select PC.*, WOH.workorder_id from ahl_part_changes PC, ahl_workorder_operations WOH where pc.workorder_operation_id = woh.workorder_operation_id and pc.mc_relationship_id is not null) PCH, (select a.installed_instance_id, a.issue_mtl_txn_id, a.part_change_id, a.last_update_date, wo.workorder_id, a.mc_relationship_id, a.quantity, a.part_change_type, a.installation_date from ahl_part_changes a, ahl_workorder_operations wo where a.workorder_operation_id = wo.workorder_operation_id and part_change_type <> 'R' and a.mc_relationship_id is not null) PCI WHERE pch.workorder_id = pci.workorder_id(+) and PCH.mc_relationship_id = PCI.mc_relationship_id(+) and PCH.last_update_date <= PCI.last_update_date(+) and decode(PCH.part_change_type,'R','R','A') = decode(PCI.part_change_type(+),'I','R','B') UNION ALL SELECT PCH.part_change_id, PCH.OBJECT_VERSION_NUMBER, PCH.LAST_UPDATE_DATE, PCH.LAST_UPDATED_BY, PCH.CREATION_DATE, PCH.CREATED_BY , PCH.LAST_UPDATE_LOGIN, PCH.UNIT_CONFIG_HEADER_ID, PCH.REMOVAL_CODE, to_number(NULL), PCH.REMOVAL_REASON_ID, PCH.WORKORDER_OPERATION_ID, PCH.REMOVAL_DATE, PCH.STATUS_ID, PCH.ATTRIBUTE_CATEGORY, PCH.ATTRIBUTE1, PCH.ATTRIBUTE2, PCH.ATTRIBUTE3, PCH.ATTRIBUTE4, PCH.ATTRIBUTE5, PCH.ATTRIBUTE6, PCH.ATTRIBUTE7, PCH.ATTRIBUTE8, PCH.ATTRIBUTE9, PCH.ATTRIBUTE10, PCH.ATTRIBUTE11, PCH.ATTRIBUTE12, PCH.ATTRIBUTE13, PCH.ATTRIBUTE14, PCH.ATTRIBUTE15, PCH.part_change_type, decode(PCH.part_change_type, 'R', PCI.Part_Change_id, PCH.Part_Change_id), decode(PCH.part_change_type, 'R', PCI.installed_instance_id, PCH.installed_instance_id) Installed_Instance_id, PCH.removed_instance_id, decode(PCH.part_change_type, 'R', PCI.issue_mtl_txn_id, PCH.issue_mtl_txn_id) Issue_Mtl_Txn_Id, PCH.return_mtl_txn_id, PCH.Quantity Removed_Quantity, decode(PCH.part_change_type,'R',PCI.Quantity,PCH.Quantity) Installed_Quantity, decode(PCH.part_change_type, 'R', PCI.installation_date, PCH.installation_date) Installation_Date from (select PC.*, WOH.workorder_id from ahl_part_changes PC, ahl_workorder_operations WOH where pc.workorder_operation_id = woh.workorder_operation_id and pc.mc_relationship_id is null) PCH, (select a.installed_instance_id, a.issue_mtl_txn_id, a.part_change_id, a.last_update_date, wo.workorder_id, a.quantity, a.part_change_type, a.installation_date from ahl_part_changes a, ahl_workorder_operations wo where a.workorder_operation_id = wo.workorder_operation_id and part_change_type <> 'R' and a.mc_relationship_id is null) PCI WHERE pch.workorder_id = pci.workorder_id(+) and PCH.removed_instance_id = PCI.installed_instance_id(+) and PCH.last_update_date <= PCI.last_update_date(+) and decode(PCH.part_change_type,'R','R','A') = decode(PCI.part_change_type(+),'I','R','B')

View Text - HTML Formatted

SELECT PCH.PART_CHANGE_ID
, PCH.OBJECT_VERSION_NUMBER
, PCH.LAST_UPDATE_DATE
, PCH.LAST_UPDATED_BY
, PCH.CREATION_DATE
, PCH.CREATED_BY
, PCH.LAST_UPDATE_LOGIN
, PCH.UNIT_CONFIG_HEADER_ID
, PCH.REMOVAL_CODE
, PCH.MC_RELATIONSHIP_ID
, PCH.REMOVAL_REASON_ID
, PCH.WORKORDER_OPERATION_ID
, PCH.REMOVAL_DATE
, PCH.STATUS_ID
, PCH.ATTRIBUTE_CATEGORY
, PCH.ATTRIBUTE1
, PCH.ATTRIBUTE2
, PCH.ATTRIBUTE3
, PCH.ATTRIBUTE4
, PCH.ATTRIBUTE5
, PCH.ATTRIBUTE6
, PCH.ATTRIBUTE7
, PCH.ATTRIBUTE8
, PCH.ATTRIBUTE9
, PCH.ATTRIBUTE10
, PCH.ATTRIBUTE11
, PCH.ATTRIBUTE12
, PCH.ATTRIBUTE13
, PCH.ATTRIBUTE14
, PCH.ATTRIBUTE15
, PCH.PART_CHANGE_TYPE
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.PART_CHANGE_ID
, PCH.PART_CHANGE_ID)
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.INSTALLED_INSTANCE_ID
, PCH.INSTALLED_INSTANCE_ID) INSTALLED_INSTANCE_ID
, PCH.REMOVED_INSTANCE_ID
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.ISSUE_MTL_TXN_ID
, PCH.ISSUE_MTL_TXN_ID) ISSUE_MTL_TXN_ID
, PCH.RETURN_MTL_TXN_ID
, PCH.QUANTITY REMOVED_QUANTITY
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.QUANTITY
, PCH.QUANTITY) INSTALLED_QUANTITY
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.INSTALLATION_DATE
, PCH.INSTALLATION_DATE) INSTALLATION_DATE
FROM (SELECT PC.*
, WOH.WORKORDER_ID
FROM AHL_PART_CHANGES PC
, AHL_WORKORDER_OPERATIONS WOH
WHERE PC.WORKORDER_OPERATION_ID = WOH.WORKORDER_OPERATION_ID
AND PC.MC_RELATIONSHIP_ID IS NOT NULL) PCH
, (SELECT A.INSTALLED_INSTANCE_ID
, A.ISSUE_MTL_TXN_ID
, A.PART_CHANGE_ID
, A.LAST_UPDATE_DATE
, WO.WORKORDER_ID
, A.MC_RELATIONSHIP_ID
, A.QUANTITY
, A.PART_CHANGE_TYPE
, A.INSTALLATION_DATE
FROM AHL_PART_CHANGES A
, AHL_WORKORDER_OPERATIONS WO
WHERE A.WORKORDER_OPERATION_ID = WO.WORKORDER_OPERATION_ID
AND PART_CHANGE_TYPE <> 'R'
AND A.MC_RELATIONSHIP_ID IS NOT NULL) PCI
WHERE PCH.WORKORDER_ID = PCI.WORKORDER_ID(+)
AND PCH.MC_RELATIONSHIP_ID = PCI.MC_RELATIONSHIP_ID(+)
AND PCH.LAST_UPDATE_DATE <= PCI.LAST_UPDATE_DATE(+)
AND DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, 'R'
, 'A') = DECODE(PCI.PART_CHANGE_TYPE(+)
, 'I'
, 'R'
, 'B') UNION ALL SELECT PCH.PART_CHANGE_ID
, PCH.OBJECT_VERSION_NUMBER
, PCH.LAST_UPDATE_DATE
, PCH.LAST_UPDATED_BY
, PCH.CREATION_DATE
, PCH.CREATED_BY
, PCH.LAST_UPDATE_LOGIN
, PCH.UNIT_CONFIG_HEADER_ID
, PCH.REMOVAL_CODE
, TO_NUMBER(NULL)
, PCH.REMOVAL_REASON_ID
, PCH.WORKORDER_OPERATION_ID
, PCH.REMOVAL_DATE
, PCH.STATUS_ID
, PCH.ATTRIBUTE_CATEGORY
, PCH.ATTRIBUTE1
, PCH.ATTRIBUTE2
, PCH.ATTRIBUTE3
, PCH.ATTRIBUTE4
, PCH.ATTRIBUTE5
, PCH.ATTRIBUTE6
, PCH.ATTRIBUTE7
, PCH.ATTRIBUTE8
, PCH.ATTRIBUTE9
, PCH.ATTRIBUTE10
, PCH.ATTRIBUTE11
, PCH.ATTRIBUTE12
, PCH.ATTRIBUTE13
, PCH.ATTRIBUTE14
, PCH.ATTRIBUTE15
, PCH.PART_CHANGE_TYPE
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.PART_CHANGE_ID
, PCH.PART_CHANGE_ID)
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.INSTALLED_INSTANCE_ID
, PCH.INSTALLED_INSTANCE_ID) INSTALLED_INSTANCE_ID
, PCH.REMOVED_INSTANCE_ID
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.ISSUE_MTL_TXN_ID
, PCH.ISSUE_MTL_TXN_ID) ISSUE_MTL_TXN_ID
, PCH.RETURN_MTL_TXN_ID
, PCH.QUANTITY REMOVED_QUANTITY
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.QUANTITY
, PCH.QUANTITY) INSTALLED_QUANTITY
, DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, PCI.INSTALLATION_DATE
, PCH.INSTALLATION_DATE) INSTALLATION_DATE
FROM (SELECT PC.*
, WOH.WORKORDER_ID
FROM AHL_PART_CHANGES PC
, AHL_WORKORDER_OPERATIONS WOH
WHERE PC.WORKORDER_OPERATION_ID = WOH.WORKORDER_OPERATION_ID
AND PC.MC_RELATIONSHIP_ID IS NULL) PCH
, (SELECT A.INSTALLED_INSTANCE_ID
, A.ISSUE_MTL_TXN_ID
, A.PART_CHANGE_ID
, A.LAST_UPDATE_DATE
, WO.WORKORDER_ID
, A.QUANTITY
, A.PART_CHANGE_TYPE
, A.INSTALLATION_DATE
FROM AHL_PART_CHANGES A
, AHL_WORKORDER_OPERATIONS WO
WHERE A.WORKORDER_OPERATION_ID = WO.WORKORDER_OPERATION_ID
AND PART_CHANGE_TYPE <> 'R'
AND A.MC_RELATIONSHIP_ID IS NULL) PCI
WHERE PCH.WORKORDER_ID = PCI.WORKORDER_ID(+)
AND PCH.REMOVED_INSTANCE_ID = PCI.INSTALLED_INSTANCE_ID(+)
AND PCH.LAST_UPDATE_DATE <= PCI.LAST_UPDATE_DATE(+)
AND DECODE(PCH.PART_CHANGE_TYPE
, 'R'
, 'R'
, 'A') = DECODE(PCI.PART_CHANGE_TYPE(+)
, 'I'
, 'R'
, 'B')