VIEW: APPS.CSI_INST_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT T.TRANSACTION_ID TRANSACTION_ID, V.INSTANCE_ID INSTANCE_ID, T.TRANSACTION_DATE TRASACTION_DATE, T.SOURCE_TRANSACTION_DATE SOURCE_TRANSACTION_DATE, T.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID, T.TXN_SUB_TYPE_ID TXN_SUB_TYPE_ID, T.SOURCE_GROUP_REF_ID SOURCE_GROUP_REF_ID, T.SOURCE_GROUP_REF SOURCE_GROUP_REF, T.SOURCE_HEADER_REF_ID SOURCE_HEADER_REF_ID, T.SOURCE_HEADER_REF SOURCE_HEADER_REF, T.SOURCE_LINE_REF_ID SOURCE_LINE_REF_ID, T.SOURCE_LINE_REF SOURCE_LINE_REF, T.SOURCE_DIST_REF_ID1 SOURCE_DIST_REF_ID1, T.SOURCE_DIST_REF_ID2 SOURCE_DIST_REF_ID2, T.INV_MATERIAL_TRANSACTION_ID INV_MATERIAL_TRANSACTION_ID, T.TRANSACTION_QUANTITY TRANSACTION_QUANTITY, T.TRANSACTION_UOM_CODE TRANSACTION_UOM_CODE, T.TRANSACTED_BY TRANSACTED_BY, T.TRANSACTION_STATUS_CODE TRANSACTION_STATUS_CODE, T.TRANSACTION_ACTION_CODE TRANSACTION_ACTION_CODE, T.MESSAGE_ID MESSAGE_ID, T.SPLIT_REASON_CODE SPLIT_REASON_CODE, T.CONTEXT CONTEXT, T.ATTRIBUTE1 ATTRIBUTE1, T.ATTRIBUTE2 ATTRIBUTE2, T.ATTRIBUTE3 ATTRIBUTE3, T.ATTRIBUTE4 ATTRIBUTE4, T.ATTRIBUTE5 ATTRIBUTE5, T.ATTRIBUTE6 ATTRIBUTE6, T.ATTRIBUTE7 ATTRIBUTE7, T.ATTRIBUTE8 ATTRIBUTE8, T.ATTRIBUTE9 ATTRIBUTE9, T.ATTRIBUTE10 ATTRIBUTE10, T.ATTRIBUTE11 ATTRIBUTE11, T.ATTRIBUTE12 ATTRIBUTE12, T.ATTRIBUTE13 ATTRIBUTE13, T.ATTRIBUTE14 ATTRIBUTE14, T.ATTRIBUTE15 ATTRIBUTE15, T.CREATED_BY CREATED_BY, T.CREATION_DATE CREATION_DATE, T.LAST_UPDATED_BY LAST_UPDATED_BY, T.LAST_UPDATE_DATE LAST_UPDATE_DATE, T.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, T.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, CII.INSTANCE_NUMBER INSTANCE_NUMBER, MSI.DESCRIPTION DESCRIPTION, MSIKFV.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS, FU.USER_NAME TXN_USER_NAME FROM CSI_TRANSACTIONS T, CSI_ITEM_INSTANCES CII, MTL_SYSTEM_ITEMS MSI, FND_USER FU, MTL_SYSTEM_ITEMS_KFV MSIKFV, ( SELECT DISTINCT TRANSACTION_ID, INSTANCE_ID FROM ( SELECT CIIH.TRANSACTION_ID TRANSACTION_ID, CIIH.INSTANCE_ID INSTANCE_ID FROM CSI_ITEM_INSTANCES_H CIIH UNION ALL SELECT CIVH.TRANSACTION_ID TRANSACTION_ID, CIV.INSTANCE_ID INSTANCE_ID FROM CSI_IEA_VALUES_H CIVH, CSI_IEA_VALUES CIV WHERE CIV.ATTRIBUTE_VALUE_ID = CIVH.ATTRIBUTE_VALUE_ID UNION ALL SELECT CIRH.TRANSACTION_ID TRANSACTION_ID, CIR.SUBJECT_ID INSTANCE_ID FROM CSI_II_RELATIONSHIPS_H CIRH, CSI_II_RELATIONSHIPS CIR WHERE CIRH.RELATIONSHIP_ID = CIR.RELATIONSHIP_ID UNION ALL SELECT CIRH.TRANSACTION_ID TRANSACTION_ID, CIR.OBJECT_ID INSTANCE_ID FROM CSI_II_RELATIONSHIPS_H CIRH, CSI_II_RELATIONSHIPS CIR WHERE CIRH.RELATIONSHIP_ID = CIR.RELATIONSHIP_ID UNION ALL SELECT CIPH.TRANSACTION_ID TRANSACTION_ID, CIP.INSTANCE_ID INSTANCE_ID FROM CSI_I_PARTIES_H CIPH, CSI_I_PARTIES CIP WHERE CIPH.INSTANCE_PARTY_ID = CIP.INSTANCE_PARTY_ID UNION ALL SELECT CIAAH.TRANSACTION_ID TRANSACTION_ID, CIAA.INSTANCE_ID INSTANCE_ID FROM CSI_I_ASSETS_H CIAAH, CSI_I_ASSETS CIAA WHERE CIAAH.INSTANCE_ASSET_ID = CIAA.INSTANCE_ASSET_ID UNION ALL SELECT CIAH.TRANSACTION_ID TRANSACTION_ID, CIP.INSTANCE_ID INSTANCE_ID FROM CSI_IP_ACCOUNTS_H CIAH, CSI_IP_ACCOUNTS CIA, CSI_I_PARTIES CIP WHERE CIAH.IP_ACCOUNT_ID = CIA.IP_ACCOUNT_ID AND CIA.INSTANCE_PARTY_ID = CIP.INSTANCE_PARTY_ID UNION ALL SELECT CIOAH.TRANSACTION_ID TRANSACTION_ID, CIOA.INSTANCE_ID INSTANCE_ID FROM CSI_I_ORG_ASSIGNMENTS_H CIOAH, CSI_I_ORG_ASSIGNMENTS CIOA WHERE CIOAH.INSTANCE_OU_ID = CIOA.INSTANCE_OU_ID UNION ALL SELECT CIVLH.TRANSACTION_ID TRANSACTION_ID, CIVL.INSTANCE_ID INSTANCE_ID FROM CSI_I_VERSION_LABELS_H CIVLH, CSI_I_VERSION_LABELS CIVL WHERE CIVLH.VERSION_LABEL_ID = CIVL.VERSION_LABEL_ID UNION ALL SELECT CIPAH.TRANSACTION_ID TRANSACTION_ID, CIPA.INSTANCE_ID INSTANCE_ID FROM CSI_I_PRICING_ATTRIBS_H CIPAH, CSI_I_PRICING_ATTRIBS CIPA WHERE CIPAH.PRICING_ATTRIBUTE_ID = CIPA.PRICING_ATTRIBUTE_ID ) ) V WHERE T.TRANSACTION_ID = V.TRANSACTION_ID AND T.LAST_UPDATED_BY = FU.USER_ID AND CII.INSTANCE_ID = V.INSTANCE_ID AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND CII.LAST_VLD_ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID AND CII.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID

View Text - HTML Formatted

SELECT T.TRANSACTION_ID TRANSACTION_ID
, V.INSTANCE_ID INSTANCE_ID
, T.TRANSACTION_DATE TRASACTION_DATE
, T.SOURCE_TRANSACTION_DATE SOURCE_TRANSACTION_DATE
, T.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID
, T.TXN_SUB_TYPE_ID TXN_SUB_TYPE_ID
, T.SOURCE_GROUP_REF_ID SOURCE_GROUP_REF_ID
, T.SOURCE_GROUP_REF SOURCE_GROUP_REF
, T.SOURCE_HEADER_REF_ID SOURCE_HEADER_REF_ID
, T.SOURCE_HEADER_REF SOURCE_HEADER_REF
, T.SOURCE_LINE_REF_ID SOURCE_LINE_REF_ID
, T.SOURCE_LINE_REF SOURCE_LINE_REF
, T.SOURCE_DIST_REF_ID1 SOURCE_DIST_REF_ID1
, T.SOURCE_DIST_REF_ID2 SOURCE_DIST_REF_ID2
, T.INV_MATERIAL_TRANSACTION_ID INV_MATERIAL_TRANSACTION_ID
, T.TRANSACTION_QUANTITY TRANSACTION_QUANTITY
, T.TRANSACTION_UOM_CODE TRANSACTION_UOM_CODE
, T.TRANSACTED_BY TRANSACTED_BY
, T.TRANSACTION_STATUS_CODE TRANSACTION_STATUS_CODE
, T.TRANSACTION_ACTION_CODE TRANSACTION_ACTION_CODE
, T.MESSAGE_ID MESSAGE_ID
, T.SPLIT_REASON_CODE SPLIT_REASON_CODE
, T.CONTEXT CONTEXT
, T.ATTRIBUTE1 ATTRIBUTE1
, T.ATTRIBUTE2 ATTRIBUTE2
, T.ATTRIBUTE3 ATTRIBUTE3
, T.ATTRIBUTE4 ATTRIBUTE4
, T.ATTRIBUTE5 ATTRIBUTE5
, T.ATTRIBUTE6 ATTRIBUTE6
, T.ATTRIBUTE7 ATTRIBUTE7
, T.ATTRIBUTE8 ATTRIBUTE8
, T.ATTRIBUTE9 ATTRIBUTE9
, T.ATTRIBUTE10 ATTRIBUTE10
, T.ATTRIBUTE11 ATTRIBUTE11
, T.ATTRIBUTE12 ATTRIBUTE12
, T.ATTRIBUTE13 ATTRIBUTE13
, T.ATTRIBUTE14 ATTRIBUTE14
, T.ATTRIBUTE15 ATTRIBUTE15
, T.CREATED_BY CREATED_BY
, T.CREATION_DATE CREATION_DATE
, T.LAST_UPDATED_BY LAST_UPDATED_BY
, T.LAST_UPDATE_DATE LAST_UPDATE_DATE
, T.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, T.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, CII.INSTANCE_NUMBER INSTANCE_NUMBER
, MSI.DESCRIPTION DESCRIPTION
, MSIKFV.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS
, FU.USER_NAME TXN_USER_NAME
FROM CSI_TRANSACTIONS T
, CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS MSI
, FND_USER FU
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, ( SELECT DISTINCT TRANSACTION_ID
, INSTANCE_ID
FROM ( SELECT CIIH.TRANSACTION_ID TRANSACTION_ID
, CIIH.INSTANCE_ID INSTANCE_ID
FROM CSI_ITEM_INSTANCES_H CIIH UNION ALL SELECT CIVH.TRANSACTION_ID TRANSACTION_ID
, CIV.INSTANCE_ID INSTANCE_ID
FROM CSI_IEA_VALUES_H CIVH
, CSI_IEA_VALUES CIV
WHERE CIV.ATTRIBUTE_VALUE_ID = CIVH.ATTRIBUTE_VALUE_ID UNION ALL SELECT CIRH.TRANSACTION_ID TRANSACTION_ID
, CIR.SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS_H CIRH
, CSI_II_RELATIONSHIPS CIR
WHERE CIRH.RELATIONSHIP_ID = CIR.RELATIONSHIP_ID UNION ALL SELECT CIRH.TRANSACTION_ID TRANSACTION_ID
, CIR.OBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS_H CIRH
, CSI_II_RELATIONSHIPS CIR
WHERE CIRH.RELATIONSHIP_ID = CIR.RELATIONSHIP_ID UNION ALL SELECT CIPH.TRANSACTION_ID TRANSACTION_ID
, CIP.INSTANCE_ID INSTANCE_ID
FROM CSI_I_PARTIES_H CIPH
, CSI_I_PARTIES CIP
WHERE CIPH.INSTANCE_PARTY_ID = CIP.INSTANCE_PARTY_ID UNION ALL SELECT CIAAH.TRANSACTION_ID TRANSACTION_ID
, CIAA.INSTANCE_ID INSTANCE_ID
FROM CSI_I_ASSETS_H CIAAH
, CSI_I_ASSETS CIAA
WHERE CIAAH.INSTANCE_ASSET_ID = CIAA.INSTANCE_ASSET_ID UNION ALL SELECT CIAH.TRANSACTION_ID TRANSACTION_ID
, CIP.INSTANCE_ID INSTANCE_ID
FROM CSI_IP_ACCOUNTS_H CIAH
, CSI_IP_ACCOUNTS CIA
, CSI_I_PARTIES CIP
WHERE CIAH.IP_ACCOUNT_ID = CIA.IP_ACCOUNT_ID
AND CIA.INSTANCE_PARTY_ID = CIP.INSTANCE_PARTY_ID UNION ALL SELECT CIOAH.TRANSACTION_ID TRANSACTION_ID
, CIOA.INSTANCE_ID INSTANCE_ID
FROM CSI_I_ORG_ASSIGNMENTS_H CIOAH
, CSI_I_ORG_ASSIGNMENTS CIOA
WHERE CIOAH.INSTANCE_OU_ID = CIOA.INSTANCE_OU_ID UNION ALL SELECT CIVLH.TRANSACTION_ID TRANSACTION_ID
, CIVL.INSTANCE_ID INSTANCE_ID
FROM CSI_I_VERSION_LABELS_H CIVLH
, CSI_I_VERSION_LABELS CIVL
WHERE CIVLH.VERSION_LABEL_ID = CIVL.VERSION_LABEL_ID UNION ALL SELECT CIPAH.TRANSACTION_ID TRANSACTION_ID
, CIPA.INSTANCE_ID INSTANCE_ID
FROM CSI_I_PRICING_ATTRIBS_H CIPAH
, CSI_I_PRICING_ATTRIBS CIPA
WHERE CIPAH.PRICING_ATTRIBUTE_ID = CIPA.PRICING_ATTRIBUTE_ID ) ) V
WHERE T.TRANSACTION_ID = V.TRANSACTION_ID
AND T.LAST_UPDATED_BY = FU.USER_ID
AND CII.INSTANCE_ID = V.INSTANCE_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND CII.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID