VIEW: APPS.SO_LINES_CANCEL_V

Source

View Text - Preformatted

SELECT SOL.ROWID ROW_ID , SOL.LINE_ID , SOL.CREATION_DATE , SOL.CREATED_BY , SOL.LAST_UPDATE_DATE , SOL.LAST_UPDATED_BY , SOL.LAST_UPDATE_LOGIN , SOL.REQUEST_ID , SOL.PROGRAM_APPLICATION_ID , SOL.PROGRAM_ID , SOL.PROGRAM_UPDATE_DATE , SOL.SOURCE_LINE_ID , SOL.ORIGINAL_SYSTEM_LINE_REFERENCE , SOL.HEADER_ID , SOL.SHIPMENT_SCHEDULE_LINE_ID , SOL.PARENT_LINE_ID , SOL.ATO_LINE_ID , SOL.LINK_TO_LINE_ID , SOL.SERVICE_PARENT_LINE_ID , SOL.LINE_TYPE_CODE , SOL.OPTION_FLAG , SOL.OPEN_FLAG , SOL.CYCLE_ID , SOL.LINE_NUMBER , SOL.INVENTORY_ITEM_ID , SOL.COMPONENT_SEQUENCE_ID , SOL.ITEM_TYPE_CODE , SOL.ATO_FLAG , SOL.SHIP_MODEL_COMPLETE_FLAG , SOL.COMPONENT_CODE , SOL.COMPONENT_SORT_CODE , SOL.STANDARD_COMPONENT_FREEZE_DATE , SOL.CONFIGURATION_EDITED_FLAG , SOL.ORDERED_QUANTITY , SOL.CANCELLED_QUANTITY , SOL.UNIT_CODE , SOL.PRICE_LIST_ID , SOL.PRICING_METHOD_CODE , SOL.LIST_PRICE , SOL.SELLING_PRICE , SOL.LIST_PERCENT , SOL.SELLING_PERCENT , SOL.PERCENT_BASE_PRICE , SOL.PRICING_CONTEXT , SOL.PRICING_ATTRIBUTE1 , SOL.PRICING_ATTRIBUTE2 , SOL.PRICING_ATTRIBUTE3 , SOL.PRICING_ATTRIBUTE4 , SOL.PRICING_ATTRIBUTE5 , SOL.PRICING_ATTRIBUTE6 , SOL.PRICING_ATTRIBUTE7 , SOL.PRICING_ATTRIBUTE8 , SOL.PRICING_ATTRIBUTE9 , SOL.PRICING_ATTRIBUTE10 , SOL.PRICING_ATTRIBUTE11 , SOL.PRICING_ATTRIBUTE12 , SOL.PRICING_ATTRIBUTE13 , SOL.PRICING_ATTRIBUTE14 , SOL.PRICING_ATTRIBUTE15 , SOL.WAREHOUSE_ID , SOL.DEMAND_CLASS_CODE , SOL.SCHEDULE_DATE , SOL.SHIP_SET_NUMBER , SOL.SHIPPED_QUANTITY , SOL.DATE_REQUESTED_CURRENT , SOL.PROMISE_DATE , SOL.SHIP_TO_SITE_USE_ID , SOL.SHIP_TO_CONTACT_ID , SOL.SHIPMENT_PRIORITY_CODE , SOL.SHIP_METHOD_CODE , SOL.AGREEMENT_ID , SOL.COMMITMENT_ID , SOL.TERMS_ID , SOL.INVOICING_RULE_ID , SOL.ACCOUNTING_RULE_ID , SOL.INVOICE_COUNT , SOL.INVOICED_QUANTITY , SOL.QUANTITY_TO_INVOICE , SOL.REVENUE_AMOUNT , SOL.TAX_EXEMPT_NUMBER , SOL.TAX_EXEMPT_REASON_CODE , SOL.TRANSACTION_TYPE_CODE , SOL.TRANSACTION_REASON_CODE , SOL.TRANSACTION_COMMENTS , SOL.RETURN_REFERENCE_TYPE_CODE , SOL.RETURN_REFERENCE_ID , SOL.CREDIT_INVOICE_LINE_ID , SOL.INSPECTION_REQUIRED_FLAG , SOL.CUSTOMER_PRODUCT_ID , SOL.CP_SERVICE_ID , SOL.SERVICED_QUANTITY , SOL.SERVICE_DURATION , SOL.SERVICE_START_DATE , SOL.SERVICE_END_DATE , SOL.SERVICE_COTERMINATE_FLAG , SOL.SERVICE_PERIOD_CONVERSION_RATE , SOL.SERVICE_MASS_TXN_TEMP_ID , SOL.LATEST_ACCEPTABLE_DATE , SOL.LATEST_ACCEPTABLE_DATE_SVRID , SOL.TAX_CODE , SOL.ORG_ID , SOL.NUMBER_SVRID , SOL.ITEM_SVRID , SOL.ORDERED_QUANTITY_SVRID , SOL.UNIT_SVRID , SOL.PRICING_ATTRIBUTES_SVRID , SOL.LIST_PRICE_SVRID , SOL.SELLING_PRICE_SVRID , SOL.LIST_PERCENT_SVRID , SOL.SELLING_PERCENT_SVRID , SOL.WAREHOUSE_SVRID , SOL.DEMAND_CLASS_SVRID , SOL.SHIP_SET_NUMBER_SVRID , SOL.REQUEST_DATE_SVRID , SOL.PROMISE_DATE_SVRID , SOL.SCHEDULE_DATE_SVRID , SOL.SHIP_TO_SVRID , SOL.SHIP_TO_CONTACT_SVRID , SOL.SHIPMENT_PRIORITY_SVRID , SOL.FREIGHT_CARRIER_SVRID , SOL.AGREEMENT_SVRID , SOL.COMMITMENT_SVRID , SOL.PAYMENT_TERMS_SVRID , SOL.INVOICING_RULE_SVRID , SOL.ACCOUNTING_RULE_SVRID , SOL.TAX_EXEMPT_NUMBER_SVRID , SOL.TAX_EXEMPT_REASON_SVRID , SOL.TRANSACTION_TYPE_SVRID , SOL.TRANSACTION_REASON_SVRID , SOL.TRANSACTION_COMMENTS_SVRID , SOL.RETURN_REFERENCE_TYPE_SVRID , SOL.RETURN_REFERENCE_SVRID , SOL.CREDIT_INVOICE_LINE_SVRID , SOL.INSPECTION_REQUIRED_SVRID , SOL.CUSTOMER_PRODUCT_SVRID , SOL.CUSTOMER_PRODUCT_SERVICE_SVRID , SOL.SERVICE_DURATION_SVRID , SOL.SERVICE_START_DATE_SVRID , SOL.SERVICE_END_DATE_SVRID , SOL.SERVICE_PER_CONV_RATE_SVRID , SOL.SERVICE_COTERMINATE_SVRID , SOL.DF_SVRID , SOL.TAX_CODE_SVRID , SOL.S1 , SOL.S1_DATE , SOL.S2 , SOL.S2_DATE , SOL.S3 , SOL.S3_DATE , SOL.S4 , SOL.S4_DATE , SOL.S5 , SOL.S5_DATE , SOL.S6 , SOL.S6_DATE , SOL.S7 , SOL.S7_DATE , SOL.S8 , SOL.S8_DATE , SOL.S9 , SOL.S9_DATE , SOL.S10 , SOL.S10_DATE , SOL.S11 , SOL.S11_DATE , SOL.S12 , SOL.S12_DATE , SOL.S13 , SOL.S13_DATE , SOL.S14 , SOL.S14_DATE , SOL.S15 , SOL.S15_DATE , SOL.S16 , SOL.S16_DATE , SOL.S17 , SOL.S17_DATE , SOL.S18 , SOL.S18_DATE , SOL.S19 , SOL.S19_DATE , SOL.S20 , SOL.S20_DATE , SOL.S21 , SOL.S21_DATE , SOL.S22 , SOL.S22_DATE , SOL.S23 , SOL.S23_DATE , SOL.S24 , SOL.S24_DATE , SOL.S25 , SOL.S25_DATE , SOL.S26 , SOL.S26_DATE , SOL.S27 , SOL.S27_DATE , SOL.S28 , SOL.S28_DATE , SOL.S29 , SOL.S29_DATE , SOL.S30 , SOL.S30_DATE , SOL.CONTEXT , SOL.ATTRIBUTE1 , SOL.ATTRIBUTE2 , SOL.ATTRIBUTE3 , SOL.ATTRIBUTE4 , SOL.ATTRIBUTE5 , SOL.ATTRIBUTE6 , SOL.ATTRIBUTE7 , SOL.ATTRIBUTE8 , SOL.ATTRIBUTE9 , SOL.ATTRIBUTE10 , SOL.ATTRIBUTE11 , SOL.ATTRIBUTE12 , SOL.ATTRIBUTE13 , SOL.ATTRIBUTE14 , SOL.ATTRIBUTE15 , SOL.PRICING_DATE , OEXVWCAN.TOP_BILL_SEQUENCE_ID(SOL.LINE_ID, SOL.PARENT_LINE_ID) TOP_BILL_SEQUENCE_ID , DECODE(SOL.ITEM_TYPE_CODE, 'MODEL' , DECODE(SOL.COMPONENT_SORT_CODE, '' , 0, ((LENGTH(SOL.COMPONENT_SORT_CODE)/4)-1)), ((LENGTH(SOL.COMPONENT_SORT_CODE)/4)-1)) PLAN_LEVEL, DECODE((SOL.ORDERED_QUANTITY - GREATEST(NVL(SOL.SHIPPED_QUANTITY,0), NVL(SOL.INVOICED_QUANTITY,0)) - NVL(SOL.CANCELLED_QUANTITY,0)) , 0, DECODE(SOL.CANCELLED_QUANTITY,'','N','Y'),'N' ) LINE_CANCELLED , (SOL.ORDERED_QUANTITY - NVL(SOL.CANCELLED_QUANTITY,0)) NET_QUANTITY , DECODE(SOL.PARENT_LINE_ID, NULL, SOL.LINE_ID, SOL.PARENT_LINE_ID) REAL_PARENT_LINE_ID , SOL1.COMPONENT_CODE PARENT_COMPONENT_CODE , SOL1.COMPONENT_SEQUENCE_ID PARENT_COMPONENT_SEQUENCE_ID , SOL1.INVENTORY_ITEM_ID PARENT_INVENTORY_ITEM_ID , SOL2.COMPONENT_CODE TOP_COMPONENT_CODE , SOL2.COMPONENT_SEQUENCE_ID TOP_COMPONENT_SEQUENCE_ID , SOL2.INVENTORY_ITEM_ID TOP_INVENTORY_ITEM_ID , OEXVWCAN.SECURITY_OBJECT(SOL.PARENT_LINE_ID, SOL.SHIPMENT_SCHEDULE_LINE_ID, SOL.SERVICE_PARENT_LINE_ID, SOL.LINE_TYPE_CODE) SECURITY_OBJECT , OEXVWCAN.SHIPMENT_SCHEDULE_NUMBER(SOL.PARENT_LINE_ID, SOL.SERVICE_PARENT_LINE_ID, SOL.SHIPMENT_SCHEDULE_LINE_ID, SOL.LINE_NUMBER) SHIPMENT_SCHEDULE_NUMBER , OEXVWCAN.BASE_LINE_NUMBER(SOL.PARENT_LINE_ID, SOL.SERVICE_PARENT_LINE_ID, SOL.SHIPMENT_SCHEDULE_LINE_ID, SOL.LINE_NUMBER) BASE_LINE_NUMBER , OEXVWCAN.OPTION_LINE_NUMBER(SOL.PARENT_LINE_ID, SOL.SERVICE_PARENT_LINE_ID, SOL.SHIPMENT_SCHEDULE_LINE_ID, SOL.LINE_NUMBER) OPTION_LINE_NUMBER , OEXVWCAN.SUBTREE_EXISTS(SOL.LINE_ID, DECODE(SOL.PARENT_LINE_ID, NULL, SOL.LINE_ID, SOL.PARENT_LINE_ID)) SUBTREE_EXISTS , OEXVWCAN.OPEN_PICKING_SLIPS(SOL.LINE_ID, DECODE(SOL.PARENT_LINE_ID, NULL, SOL.LINE_ID, SOL.PARENT_LINE_ID), SOL.COMPONENT_CODE) OPEN_PICKING_SLIPS , OEXVWCAN.PRICE_ADJUST_EXISTS(SOL.HEADER_ID, SOL.LINE_ID) PRICE_ADJUST_EXISTS , MSIV.DESCRIPTION ITEM_DESCRIPTION , LPAD(' ' , 2*DECODE(SOL.ITEM_TYPE_CODE, 'MODEL' , DECODE(SOL.COMPONENT_SORT_CODE, '' , 0, ((LENGTH(SOL.COMPONENT_SORT_CODE)/4)-1)), ((LENGTH(SOL.COMPONENT_SORT_CODE)/4)-1)))|| MSI.CONCATENATED_SEGMENTS ITEM , DECODE(SOL.SERVICE_PARENT_LINE_ID, NULL, NULL, SOL.LINE_NUMBER) SERVICE_LINE_NUMBER , DECODE(SOL.PARENT_LINE_ID,'' , OEXVWCAN.IN_CONFIGURATION(SOL.PARENT_LINE_ID, SOL.ITEM_TYPE_CODE, SOL.SERVICE_PARENT_LINE_ID),'Y' ) IN_CONFIGURATION ,SOL.SOURCE_TYPE_CODE ,LUP.MEANING ,SOL.SOURCE_TYPE_CODE_SVRID ,SOL.PLANNING_PRIORITY ,SOL.PLANNING_PRIORITY_SVRID FROM SO_LINES SOL1, SO_LINES SOL2, MTL_SYSTEM_ITEMS_KFV MSI, MTL_SYSTEM_ITEMS_VL MSIV, SO_LINES SOL, SO_LOOKUPS LUP WHERE SOL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = FND_PROFILE.VALUE('SO_ORGANIZATION_ID') AND MSIV.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSIV.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND SOL.LINE_TYPE_CODE IN ('REGULAR','DETAIL','RETURN') AND SOL1.LINE_ID(+) = SOL.LINK_TO_LINE_ID AND SOL2.LINE_ID(+) = SOL.ATO_LINE_ID AND LUP.LOOKUP_TYPE (+) = 'SOURCE TYPE' AND LUP.LOOKUP_CODE(+) = SOL.SOURCE_TYPE_CODE

View Text - HTML Formatted

SELECT SOL.ROWID ROW_ID
, SOL.LINE_ID
, SOL.CREATION_DATE
, SOL.CREATED_BY
, SOL.LAST_UPDATE_DATE
, SOL.LAST_UPDATED_BY
, SOL.LAST_UPDATE_LOGIN
, SOL.REQUEST_ID
, SOL.PROGRAM_APPLICATION_ID
, SOL.PROGRAM_ID
, SOL.PROGRAM_UPDATE_DATE
, SOL.SOURCE_LINE_ID
, SOL.ORIGINAL_SYSTEM_LINE_REFERENCE
, SOL.HEADER_ID
, SOL.SHIPMENT_SCHEDULE_LINE_ID
, SOL.PARENT_LINE_ID
, SOL.ATO_LINE_ID
, SOL.LINK_TO_LINE_ID
, SOL.SERVICE_PARENT_LINE_ID
, SOL.LINE_TYPE_CODE
, SOL.OPTION_FLAG
, SOL.OPEN_FLAG
, SOL.CYCLE_ID
, SOL.LINE_NUMBER
, SOL.INVENTORY_ITEM_ID
, SOL.COMPONENT_SEQUENCE_ID
, SOL.ITEM_TYPE_CODE
, SOL.ATO_FLAG
, SOL.SHIP_MODEL_COMPLETE_FLAG
, SOL.COMPONENT_CODE
, SOL.COMPONENT_SORT_CODE
, SOL.STANDARD_COMPONENT_FREEZE_DATE
, SOL.CONFIGURATION_EDITED_FLAG
, SOL.ORDERED_QUANTITY
, SOL.CANCELLED_QUANTITY
, SOL.UNIT_CODE
, SOL.PRICE_LIST_ID
, SOL.PRICING_METHOD_CODE
, SOL.LIST_PRICE
, SOL.SELLING_PRICE
, SOL.LIST_PERCENT
, SOL.SELLING_PERCENT
, SOL.PERCENT_BASE_PRICE
, SOL.PRICING_CONTEXT
, SOL.PRICING_ATTRIBUTE1
, SOL.PRICING_ATTRIBUTE2
, SOL.PRICING_ATTRIBUTE3
, SOL.PRICING_ATTRIBUTE4
, SOL.PRICING_ATTRIBUTE5
, SOL.PRICING_ATTRIBUTE6
, SOL.PRICING_ATTRIBUTE7
, SOL.PRICING_ATTRIBUTE8
, SOL.PRICING_ATTRIBUTE9
, SOL.PRICING_ATTRIBUTE10
, SOL.PRICING_ATTRIBUTE11
, SOL.PRICING_ATTRIBUTE12
, SOL.PRICING_ATTRIBUTE13
, SOL.PRICING_ATTRIBUTE14
, SOL.PRICING_ATTRIBUTE15
, SOL.WAREHOUSE_ID
, SOL.DEMAND_CLASS_CODE
, SOL.SCHEDULE_DATE
, SOL.SHIP_SET_NUMBER
, SOL.SHIPPED_QUANTITY
, SOL.DATE_REQUESTED_CURRENT
, SOL.PROMISE_DATE
, SOL.SHIP_TO_SITE_USE_ID
, SOL.SHIP_TO_CONTACT_ID
, SOL.SHIPMENT_PRIORITY_CODE
, SOL.SHIP_METHOD_CODE
, SOL.AGREEMENT_ID
, SOL.COMMITMENT_ID
, SOL.TERMS_ID
, SOL.INVOICING_RULE_ID
, SOL.ACCOUNTING_RULE_ID
, SOL.INVOICE_COUNT
, SOL.INVOICED_QUANTITY
, SOL.QUANTITY_TO_INVOICE
, SOL.REVENUE_AMOUNT
, SOL.TAX_EXEMPT_NUMBER
, SOL.TAX_EXEMPT_REASON_CODE
, SOL.TRANSACTION_TYPE_CODE
, SOL.TRANSACTION_REASON_CODE
, SOL.TRANSACTION_COMMENTS
, SOL.RETURN_REFERENCE_TYPE_CODE
, SOL.RETURN_REFERENCE_ID
, SOL.CREDIT_INVOICE_LINE_ID
, SOL.INSPECTION_REQUIRED_FLAG
, SOL.CUSTOMER_PRODUCT_ID
, SOL.CP_SERVICE_ID
, SOL.SERVICED_QUANTITY
, SOL.SERVICE_DURATION
, SOL.SERVICE_START_DATE
, SOL.SERVICE_END_DATE
, SOL.SERVICE_COTERMINATE_FLAG
, SOL.SERVICE_PERIOD_CONVERSION_RATE
, SOL.SERVICE_MASS_TXN_TEMP_ID
, SOL.LATEST_ACCEPTABLE_DATE
, SOL.LATEST_ACCEPTABLE_DATE_SVRID
, SOL.TAX_CODE
, SOL.ORG_ID
, SOL.NUMBER_SVRID
, SOL.ITEM_SVRID
, SOL.ORDERED_QUANTITY_SVRID
, SOL.UNIT_SVRID
, SOL.PRICING_ATTRIBUTES_SVRID
, SOL.LIST_PRICE_SVRID
, SOL.SELLING_PRICE_SVRID
, SOL.LIST_PERCENT_SVRID
, SOL.SELLING_PERCENT_SVRID
, SOL.WAREHOUSE_SVRID
, SOL.DEMAND_CLASS_SVRID
, SOL.SHIP_SET_NUMBER_SVRID
, SOL.REQUEST_DATE_SVRID
, SOL.PROMISE_DATE_SVRID
, SOL.SCHEDULE_DATE_SVRID
, SOL.SHIP_TO_SVRID
, SOL.SHIP_TO_CONTACT_SVRID
, SOL.SHIPMENT_PRIORITY_SVRID
, SOL.FREIGHT_CARRIER_SVRID
, SOL.AGREEMENT_SVRID
, SOL.COMMITMENT_SVRID
, SOL.PAYMENT_TERMS_SVRID
, SOL.INVOICING_RULE_SVRID
, SOL.ACCOUNTING_RULE_SVRID
, SOL.TAX_EXEMPT_NUMBER_SVRID
, SOL.TAX_EXEMPT_REASON_SVRID
, SOL.TRANSACTION_TYPE_SVRID
, SOL.TRANSACTION_REASON_SVRID
, SOL.TRANSACTION_COMMENTS_SVRID
, SOL.RETURN_REFERENCE_TYPE_SVRID
, SOL.RETURN_REFERENCE_SVRID
, SOL.CREDIT_INVOICE_LINE_SVRID
, SOL.INSPECTION_REQUIRED_SVRID
, SOL.CUSTOMER_PRODUCT_SVRID
, SOL.CUSTOMER_PRODUCT_SERVICE_SVRID
, SOL.SERVICE_DURATION_SVRID
, SOL.SERVICE_START_DATE_SVRID
, SOL.SERVICE_END_DATE_SVRID
, SOL.SERVICE_PER_CONV_RATE_SVRID
, SOL.SERVICE_COTERMINATE_SVRID
, SOL.DF_SVRID
, SOL.TAX_CODE_SVRID
, SOL.S1
, SOL.S1_DATE
, SOL.S2
, SOL.S2_DATE
, SOL.S3
, SOL.S3_DATE
, SOL.S4
, SOL.S4_DATE
, SOL.S5
, SOL.S5_DATE
, SOL.S6
, SOL.S6_DATE
, SOL.S7
, SOL.S7_DATE
, SOL.S8
, SOL.S8_DATE
, SOL.S9
, SOL.S9_DATE
, SOL.S10
, SOL.S10_DATE
, SOL.S11
, SOL.S11_DATE
, SOL.S12
, SOL.S12_DATE
, SOL.S13
, SOL.S13_DATE
, SOL.S14
, SOL.S14_DATE
, SOL.S15
, SOL.S15_DATE
, SOL.S16
, SOL.S16_DATE
, SOL.S17
, SOL.S17_DATE
, SOL.S18
, SOL.S18_DATE
, SOL.S19
, SOL.S19_DATE
, SOL.S20
, SOL.S20_DATE
, SOL.S21
, SOL.S21_DATE
, SOL.S22
, SOL.S22_DATE
, SOL.S23
, SOL.S23_DATE
, SOL.S24
, SOL.S24_DATE
, SOL.S25
, SOL.S25_DATE
, SOL.S26
, SOL.S26_DATE
, SOL.S27
, SOL.S27_DATE
, SOL.S28
, SOL.S28_DATE
, SOL.S29
, SOL.S29_DATE
, SOL.S30
, SOL.S30_DATE
, SOL.CONTEXT
, SOL.ATTRIBUTE1
, SOL.ATTRIBUTE2
, SOL.ATTRIBUTE3
, SOL.ATTRIBUTE4
, SOL.ATTRIBUTE5
, SOL.ATTRIBUTE6
, SOL.ATTRIBUTE7
, SOL.ATTRIBUTE8
, SOL.ATTRIBUTE9
, SOL.ATTRIBUTE10
, SOL.ATTRIBUTE11
, SOL.ATTRIBUTE12
, SOL.ATTRIBUTE13
, SOL.ATTRIBUTE14
, SOL.ATTRIBUTE15
, SOL.PRICING_DATE
, OEXVWCAN.TOP_BILL_SEQUENCE_ID(SOL.LINE_ID
, SOL.PARENT_LINE_ID) TOP_BILL_SEQUENCE_ID
, DECODE(SOL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(SOL.COMPONENT_SORT_CODE
, ''
, 0
, ((LENGTH(SOL.COMPONENT_SORT_CODE)/4)-1))
, ((LENGTH(SOL.COMPONENT_SORT_CODE)/4)-1)) PLAN_LEVEL
, DECODE((SOL.ORDERED_QUANTITY - GREATEST(NVL(SOL.SHIPPED_QUANTITY
, 0)
, NVL(SOL.INVOICED_QUANTITY
, 0)) - NVL(SOL.CANCELLED_QUANTITY
, 0))
, 0
, DECODE(SOL.CANCELLED_QUANTITY
, ''
, 'N'
, 'Y')
, 'N' ) LINE_CANCELLED
, (SOL.ORDERED_QUANTITY - NVL(SOL.CANCELLED_QUANTITY
, 0)) NET_QUANTITY
, DECODE(SOL.PARENT_LINE_ID
, NULL
, SOL.LINE_ID
, SOL.PARENT_LINE_ID) REAL_PARENT_LINE_ID
, SOL1.COMPONENT_CODE PARENT_COMPONENT_CODE
, SOL1.COMPONENT_SEQUENCE_ID PARENT_COMPONENT_SEQUENCE_ID
, SOL1.INVENTORY_ITEM_ID PARENT_INVENTORY_ITEM_ID
, SOL2.COMPONENT_CODE TOP_COMPONENT_CODE
, SOL2.COMPONENT_SEQUENCE_ID TOP_COMPONENT_SEQUENCE_ID
, SOL2.INVENTORY_ITEM_ID TOP_INVENTORY_ITEM_ID
, OEXVWCAN.SECURITY_OBJECT(SOL.PARENT_LINE_ID
, SOL.SHIPMENT_SCHEDULE_LINE_ID
, SOL.SERVICE_PARENT_LINE_ID
, SOL.LINE_TYPE_CODE) SECURITY_OBJECT
, OEXVWCAN.SHIPMENT_SCHEDULE_NUMBER(SOL.PARENT_LINE_ID
, SOL.SERVICE_PARENT_LINE_ID
, SOL.SHIPMENT_SCHEDULE_LINE_ID
, SOL.LINE_NUMBER) SHIPMENT_SCHEDULE_NUMBER
, OEXVWCAN.BASE_LINE_NUMBER(SOL.PARENT_LINE_ID
, SOL.SERVICE_PARENT_LINE_ID
, SOL.SHIPMENT_SCHEDULE_LINE_ID
, SOL.LINE_NUMBER) BASE_LINE_NUMBER
, OEXVWCAN.OPTION_LINE_NUMBER(SOL.PARENT_LINE_ID
, SOL.SERVICE_PARENT_LINE_ID
, SOL.SHIPMENT_SCHEDULE_LINE_ID
, SOL.LINE_NUMBER) OPTION_LINE_NUMBER
, OEXVWCAN.SUBTREE_EXISTS(SOL.LINE_ID
, DECODE(SOL.PARENT_LINE_ID
, NULL
, SOL.LINE_ID
, SOL.PARENT_LINE_ID)) SUBTREE_EXISTS
, OEXVWCAN.OPEN_PICKING_SLIPS(SOL.LINE_ID
, DECODE(SOL.PARENT_LINE_ID
, NULL
, SOL.LINE_ID
, SOL.PARENT_LINE_ID)
, SOL.COMPONENT_CODE) OPEN_PICKING_SLIPS
, OEXVWCAN.PRICE_ADJUST_EXISTS(SOL.HEADER_ID
, SOL.LINE_ID) PRICE_ADJUST_EXISTS
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, LPAD(' '
, 2*DECODE(SOL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(SOL.COMPONENT_SORT_CODE
, ''
, 0
, ((LENGTH(SOL.COMPONENT_SORT_CODE)/4)-1))
, ((LENGTH(SOL.COMPONENT_SORT_CODE)/4)-1)))|| MSI.CONCATENATED_SEGMENTS ITEM
, DECODE(SOL.SERVICE_PARENT_LINE_ID
, NULL
, NULL
, SOL.LINE_NUMBER) SERVICE_LINE_NUMBER
, DECODE(SOL.PARENT_LINE_ID
, ''
, OEXVWCAN.IN_CONFIGURATION(SOL.PARENT_LINE_ID
, SOL.ITEM_TYPE_CODE
, SOL.SERVICE_PARENT_LINE_ID)
, 'Y' ) IN_CONFIGURATION
, SOL.SOURCE_TYPE_CODE
, LUP.MEANING
, SOL.SOURCE_TYPE_CODE_SVRID
, SOL.PLANNING_PRIORITY
, SOL.PLANNING_PRIORITY_SVRID
FROM SO_LINES SOL1
, SO_LINES SOL2
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_SYSTEM_ITEMS_VL MSIV
, SO_LINES SOL
, SO_LOOKUPS LUP
WHERE SOL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = FND_PROFILE.VALUE('SO_ORGANIZATION_ID')
AND MSIV.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSIV.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SOL.LINE_TYPE_CODE IN ('REGULAR'
, 'DETAIL'
, 'RETURN')
AND SOL1.LINE_ID(+) = SOL.LINK_TO_LINE_ID
AND SOL2.LINE_ID(+) = SOL.ATO_LINE_ID
AND LUP.LOOKUP_TYPE (+) = 'SOURCE TYPE'
AND LUP.LOOKUP_CODE(+) = SOL.SOURCE_TYPE_CODE