VIEW: APPS.SO_ORDER_CANCELLATIONS_V

Source

View Text - Preformatted

SELECT SOC.ROWID ROW_ID , SOC.HEADER_ID , SOC.LINE_ID , SOC.CREATED_BY , SOC.CREATION_DATE , SOC.LAST_UPDATED_BY , SOC.LAST_UPDATE_DATE , SOC.LAST_UPDATE_LOGIN , SOC.PROGRAM_APPLICATION_ID , SOC.PROGRAM_ID , SOC.PROGRAM_UPDATE_DATE , SOC.REQUEST_ID , SOC.CANCEL_CODE , SOC.CANCELLED_BY , SOC.CANCEL_DATE , SOC.CANCELLED_QUANTITY , SOC.CANCEL_COMMENT , SOC.STATUS , SOC.PROCESSED_FLAG , SOC.CONTEXT , SOC.ATTRIBUTE1 , SOC.ATTRIBUTE2 , SOC.ATTRIBUTE3 , SOC.ATTRIBUTE4 , SOC.ATTRIBUTE5 , SOC.ATTRIBUTE6 , SOC.ATTRIBUTE7 , SOC.ATTRIBUTE8 , SOC.ATTRIBUTE9 , SOC.ATTRIBUTE10 , SOC.ATTRIBUTE11 , SOC.ATTRIBUTE12 , SOC.ATTRIBUTE13 , SOC.ATTRIBUTE14 , SOC.ATTRIBUTE15 , SOH.ORDER_NUMBER , SOT.NAME ORDER_TYPE_NAME , SOL.MEANING CANCEL_MEANING , LPAD(' ',2*DECODE(SL.ITEM_TYPE_CODE, 'MODEL',DECODE(SL.COMPONENT_SORT_CODE, '', 0,((LENGTH(SL.COMPONENT_SORT_CODE)/4)-1)), ((LENGTH(SL.COMPONENT_SORT_CODE)/4)-1)))|| MSI.CONCATENATED_SEGMENTS ITEM , OEXVWCAN.BASE_LINE_NUMBER(SL.PARENT_LINE_ID, SL.SERVICE_PARENT_LINE_ID, SL.SHIPMENT_SCHEDULE_LINE_ID, SL.LINE_NUMBER) BASE_LINE_NUMBER , OEXVWCAN.SHIPMENT_SCHEDULE_NUMBER(SL.PARENT_LINE_ID, SL.SERVICE_PARENT_LINE_ID, SL.SHIPMENT_SCHEDULE_LINE_ID, SL.LINE_NUMBER) SHIPMENT_SCHEDULE_NUMBER , OEXVWCAN.OPTION_LINE_NUMBER(SL.PARENT_LINE_ID, SL.SERVICE_PARENT_LINE_ID, SL.SHIPMENT_SCHEDULE_LINE_ID, SL.LINE_NUMBER) OPTION_LINE_NUMBER , SL.ITEM_TYPE_CODE FROM SO_LOOKUPS SOL, SO_ORDER_TYPES SOT, SO_HEADERS SOH, SO_ORDER_CANCELLATIONS SOC, MTL_SYSTEM_ITEMS_KFV MSI, SO_LINES SL WHERE SOC.HEADER_ID = SOH.HEADER_ID AND SOL.LOOKUP_TYPE = 'CANCEL_CODE' AND SOL.LOOKUP_CODE = SOC.CANCEL_CODE AND SOH.ORDER_TYPE_ID = SOT.ORDER_TYPE_ID AND SL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = FND_PROFILE.VALUE('SO_ORGANIZATION_ID') AND SOC.LINE_ID = SL.LINE_ID UNION ALL SELECT SOC.ROWID ROW_ID , SOC.HEADER_ID , SOC.LINE_ID , SOC.CREATED_BY , SOC.CREATION_DATE , SOC.LAST_UPDATED_BY , SOC.LAST_UPDATE_DATE , SOC.LAST_UPDATE_LOGIN , SOC.PROGRAM_APPLICATION_ID , SOC.PROGRAM_ID , SOC.PROGRAM_UPDATE_DATE , SOC.REQUEST_ID , SOC.CANCEL_CODE , SOC.CANCELLED_BY , SOC.CANCEL_DATE , SOC.CANCELLED_QUANTITY , SOC.CANCEL_COMMENT , SOC.STATUS , SOC.PROCESSED_FLAG , SOC.CONTEXT , SOC.ATTRIBUTE1 , SOC.ATTRIBUTE2 , SOC.ATTRIBUTE3 , SOC.ATTRIBUTE4 , SOC.ATTRIBUTE5 , SOC.ATTRIBUTE6 , SOC.ATTRIBUTE7 , SOC.ATTRIBUTE8 , SOC.ATTRIBUTE9 , SOC.ATTRIBUTE10 , SOC.ATTRIBUTE11 , SOC.ATTRIBUTE12 , SOC.ATTRIBUTE13 , SOC.ATTRIBUTE14 , SOC.ATTRIBUTE15 , SOH.ORDER_NUMBER , SOT.NAME ORDER_TYPE_NAME , SOL.MEANING CANCEL_MEANING , NULL , TO_NUMBER (NULL) , TO_NUMBER (NULL) , TO_NUMBER (NULL) , NULL FROM SO_LOOKUPS SOL, SO_ORDER_TYPES SOT, SO_HEADERS SOH, SO_ORDER_CANCELLATIONS SOC WHERE SOC.HEADER_ID = SOH.HEADER_ID AND SOL.LOOKUP_TYPE = 'CANCEL_CODE' AND SOL.LOOKUP_CODE = SOC.CANCEL_CODE AND SOH.ORDER_TYPE_ID = SOT.ORDER_TYPE_ID AND SOC.LINE_ID IS NULL

View Text - HTML Formatted

SELECT SOC.ROWID ROW_ID
, SOC.HEADER_ID
, SOC.LINE_ID
, SOC.CREATED_BY
, SOC.CREATION_DATE
, SOC.LAST_UPDATED_BY
, SOC.LAST_UPDATE_DATE
, SOC.LAST_UPDATE_LOGIN
, SOC.PROGRAM_APPLICATION_ID
, SOC.PROGRAM_ID
, SOC.PROGRAM_UPDATE_DATE
, SOC.REQUEST_ID
, SOC.CANCEL_CODE
, SOC.CANCELLED_BY
, SOC.CANCEL_DATE
, SOC.CANCELLED_QUANTITY
, SOC.CANCEL_COMMENT
, SOC.STATUS
, SOC.PROCESSED_FLAG
, SOC.CONTEXT
, SOC.ATTRIBUTE1
, SOC.ATTRIBUTE2
, SOC.ATTRIBUTE3
, SOC.ATTRIBUTE4
, SOC.ATTRIBUTE5
, SOC.ATTRIBUTE6
, SOC.ATTRIBUTE7
, SOC.ATTRIBUTE8
, SOC.ATTRIBUTE9
, SOC.ATTRIBUTE10
, SOC.ATTRIBUTE11
, SOC.ATTRIBUTE12
, SOC.ATTRIBUTE13
, SOC.ATTRIBUTE14
, SOC.ATTRIBUTE15
, SOH.ORDER_NUMBER
, SOT.NAME ORDER_TYPE_NAME
, SOL.MEANING CANCEL_MEANING
, LPAD(' '
, 2*DECODE(SL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(SL.COMPONENT_SORT_CODE
, ''
, 0
, ((LENGTH(SL.COMPONENT_SORT_CODE)/4)-1))
, ((LENGTH(SL.COMPONENT_SORT_CODE)/4)-1)))|| MSI.CONCATENATED_SEGMENTS ITEM
, OEXVWCAN.BASE_LINE_NUMBER(SL.PARENT_LINE_ID
, SL.SERVICE_PARENT_LINE_ID
, SL.SHIPMENT_SCHEDULE_LINE_ID
, SL.LINE_NUMBER) BASE_LINE_NUMBER
, OEXVWCAN.SHIPMENT_SCHEDULE_NUMBER(SL.PARENT_LINE_ID
, SL.SERVICE_PARENT_LINE_ID
, SL.SHIPMENT_SCHEDULE_LINE_ID
, SL.LINE_NUMBER) SHIPMENT_SCHEDULE_NUMBER
, OEXVWCAN.OPTION_LINE_NUMBER(SL.PARENT_LINE_ID
, SL.SERVICE_PARENT_LINE_ID
, SL.SHIPMENT_SCHEDULE_LINE_ID
, SL.LINE_NUMBER) OPTION_LINE_NUMBER
, SL.ITEM_TYPE_CODE
FROM SO_LOOKUPS SOL
, SO_ORDER_TYPES SOT
, SO_HEADERS SOH
, SO_ORDER_CANCELLATIONS SOC
, MTL_SYSTEM_ITEMS_KFV MSI
, SO_LINES SL
WHERE SOC.HEADER_ID = SOH.HEADER_ID
AND SOL.LOOKUP_TYPE = 'CANCEL_CODE'
AND SOL.LOOKUP_CODE = SOC.CANCEL_CODE
AND SOH.ORDER_TYPE_ID = SOT.ORDER_TYPE_ID
AND SL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = FND_PROFILE.VALUE('SO_ORGANIZATION_ID')
AND SOC.LINE_ID = SL.LINE_ID UNION ALL SELECT SOC.ROWID ROW_ID
, SOC.HEADER_ID
, SOC.LINE_ID
, SOC.CREATED_BY
, SOC.CREATION_DATE
, SOC.LAST_UPDATED_BY
, SOC.LAST_UPDATE_DATE
, SOC.LAST_UPDATE_LOGIN
, SOC.PROGRAM_APPLICATION_ID
, SOC.PROGRAM_ID
, SOC.PROGRAM_UPDATE_DATE
, SOC.REQUEST_ID
, SOC.CANCEL_CODE
, SOC.CANCELLED_BY
, SOC.CANCEL_DATE
, SOC.CANCELLED_QUANTITY
, SOC.CANCEL_COMMENT
, SOC.STATUS
, SOC.PROCESSED_FLAG
, SOC.CONTEXT
, SOC.ATTRIBUTE1
, SOC.ATTRIBUTE2
, SOC.ATTRIBUTE3
, SOC.ATTRIBUTE4
, SOC.ATTRIBUTE5
, SOC.ATTRIBUTE6
, SOC.ATTRIBUTE7
, SOC.ATTRIBUTE8
, SOC.ATTRIBUTE9
, SOC.ATTRIBUTE10
, SOC.ATTRIBUTE11
, SOC.ATTRIBUTE12
, SOC.ATTRIBUTE13
, SOC.ATTRIBUTE14
, SOC.ATTRIBUTE15
, SOH.ORDER_NUMBER
, SOT.NAME ORDER_TYPE_NAME
, SOL.MEANING CANCEL_MEANING
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
FROM SO_LOOKUPS SOL
, SO_ORDER_TYPES SOT
, SO_HEADERS SOH
, SO_ORDER_CANCELLATIONS SOC
WHERE SOC.HEADER_ID = SOH.HEADER_ID
AND SOL.LOOKUP_TYPE = 'CANCEL_CODE'
AND SOL.LOOKUP_CODE = SOC.CANCEL_CODE
AND SOH.ORDER_TYPE_ID = SOT.ORDER_TYPE_ID
AND SOC.LINE_ID IS NULL