VIEW: APPS.SO_LINES_RMA_V

Source

View Text - Preformatted

SELECT LIN.ROWID ROW_ID , LIN.LINE_ID , LIN.CREATION_DATE , LIN.CREATED_BY , LIN.LAST_UPDATE_DATE , LIN.LAST_UPDATED_BY , LIN.LAST_UPDATE_LOGIN , LIN.REQUEST_ID , LIN.PROGRAM_APPLICATION_ID , LIN.PROGRAM_ID , LIN.PROGRAM_UPDATE_DATE , LIN.HEADER_ID , LIN.PARENT_LINE_ID , LIN.LINK_TO_LINE_ID , LIN.LINE_TYPE_CODE , LIN.OPTION_FLAG , LIN.OPEN_FLAG , LIN.CYCLE_ID , LIN.LINE_NUMBER , LIN.INVENTORY_ITEM_ID , MTL.DESCRIPTION ITEM_DESCRIPTION , MTL.BOM_ITEM_TYPE , LIN.COMPONENT_SEQUENCE_ID , LIN.ITEM_TYPE_CODE , SOITM.MEANING ITEM_TYPE , LIN.ATO_FLAG , LIN.COMPONENT_CODE , LIN.COMPONENT_SORT_CODE , LIN.ORDERED_QUANTITY , LIN.CANCELLED_QUANTITY , OE_QUERY.RECEIVED_QTY(LIN.LINE_ID) RECEIVED_QUANTITY , OE_QUERY.ACCEPTED_QTY(LIN.LINE_ID) ACCEPTED_QUANTITY , OE_QUERY.RECEIVED_DATE(LIN.LINE_ID, LIN.S29_DATE) ACTUAL_RECEIPT_DATE , OE_QUERY.ACCEPTED_DATE(LIN.LINE_ID, LIN.S29_DATE) ACTUAL_ACCEPTED_DATE , LIN.UNIT_CODE , LIN.PRICE_LIST_ID , SPL.ROUNDING_FACTOR ROUNDING_FACTOR , OE_QUERY.GET_PRICE_ADJ_TOTAL( LIN.HEADER_ID, LIN.LINE_ID) PRICE_ADJUSTMENTS_TOTAL , LIN.PRICING_METHOD_CODE , LIN.LIST_PRICE , LIN.LIST_PERCENT , LIN.SELLING_PRICE , LIN.SELLING_PERCENT , LIN.WAREHOUSE_ID , MTLPRT.ORGANIZATION_CODE WAREHOUSE_CODE , LIN.SHIPPED_QUANTITY , LIN.DATE_REQUESTED_CURRENT , LIN.SHIP_TO_SITE_USE_ID , LIN.TERMS_ID , LIN.INVOICED_QUANTITY , DECODE(LIN.CREDIT_INVOICE_LINE_ID, NULL, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE', DECODE (LIN.OPEN_FLAG, 'Y' , RCTL.TAX_EXEMPT_FLAG, NULL), 'PO', SOH.TAX_EXEMPT_FLAG, 'ORDER' ,SOH.TAX_EXEMPT_FLAG, ORD.TAX_EXEMPT_FLAG), NULL) TAX_EXEMPT_FLAG , DECODE(LIN.CREDIT_INVOICE_LINE_ID, NULL, OE_QUERY.GET_TAX_EXEMPT_FLAG( LIN.RETURN_REFERENCE_TYPE_CODE, RCTL.TAX_EXEMPT_FLAG, SOH.TAX_EXEMPT_FLAG, ORD.TAX_EXEMPT_FLAG, LIN.OPEN_FLAG), NULL) TAX_EXEMPT_FLAG_DISPLAY , DECODE(LIN.CREDIT_INVOICE_LINE_ID, NULL, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE', DECODE (LIN.OPEN_FLAG, 'Y' , RCTL.TAX_EXEMPT_NUMBER, NULL), 'PO', SOH.TAX_EXEMPT_NUM, 'ORDER' ,SOH.TAX_EXEMPT_NUM, ORD.TAX_EXEMPT_NUM), NULL) TAX_EXEMPT_NUMBER_DISPLAY , DECODE(LIN.CREDIT_INVOICE_LINE_ID , NULL, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE' , DECODE (LIN.OPEN_FLAG, 'Y', RCTL.TAX_EXEMPT_REASON_CODE, NULL), 'PO' , SOH.TAX_EXEMPT_REASON_CODE, 'ORDER' ,SOH.TAX_EXEMPT_REASON_CODE, ORD.TAX_EXEMPT_REASON_CODE), NULL) TAX_EXEMPT_REASON , DECODE(LIN.CREDIT_INVOICE_LINE_ID, NULL, OE_QUERY.GET_TAX_EXEMPT_REASON( LIN.RETURN_REFERENCE_TYPE_CODE, RCTL.TAX_EXEMPT_REASON_CODE, SOH.TAX_EXEMPT_REASON_CODE, ORD.TAX_EXEMPT_REASON_CODE, LIN.OPEN_FLAG), NULL) TAX_EXEMPT_REASON_DISPLAY , LIN.TAX_EXEMPT_NUMBER , LIN.TAX_EXEMPT_REASON_CODE , DECODE(LIN.CREDIT_INVOICE_LINE_ID, NULL, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE', DECODE (LIN.OPEN_FLAG, 'Y' , TAX.TAX_CODE, NULL), 'PO', SL1.TAX_CODE, 'ORDER' , SL1.TAX_CODE, LIN.TAX_CODE), NULL) TAX_CODE_DISPLAY , LIN.TAX_CODE , LIN.TRANSACTION_REASON_CODE , ARL.MEANING RETURN_REASON_NAME , LIN.RETURN_REFERENCE_TYPE_CODE , SOREF.MEANING RETURN_REFERENCE_TYPE_NAME , LIN.RETURN_REFERENCE_ID , LIN.CREDIT_INVOICE_LINE_ID , CRD.TRX_NUMBER CREDIT_TO_INVOICE , LIN.INSPECTION_REQUIRED_FLAG , LIN.RETURN_FOR_REPAIR_FLAG , DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE', RCT.TRX_NUMBER, 'PO' , SOH.PURCHASE_ORDER_NUM, 'ORDER' ,SOH.ORDER_NUMBER, NULL) REFERENCE_NUMBER , DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE' , RCT.CUSTOMER_TRX_ID , NULL, NULL, SOH.HEADER_ID) REFERENCE_HEADER_ID , DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE' , RCTL.LINE_NUMBER, NULL, NULL, DECODE(SL1.SHIPMENT_SCHEDULE_LINE_ID, NULL, DECODE(SL1.PARENT_LINE_ID, NULL, SL1.LINE_NUMBER, SL2.LINE_NUMBER), SL3.LINE_NUMBER)) REFERENCE_LINE_NUMBER , TO_NUMBER(DECODE (LIN.RETURN_REFERENCE_TYPE_CODE , 'INVOICE' , NULL, NULL, NULL, DECODE(SL1.PARENT_LINE_ID, NULL, DECODE(SL1.SHIPMENT_SCHEDULE_LINE_ID, NULL,NULL, SL1.LINE_NUMBER), SL2.LINE_NUMBER))) REFERENCE_SHIPMENT_NUMBER , TO_NUMBER(DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE' , NULL, NULL, NULL, DECODE(SL1.OPTION_FLAG,'Y' ,SL1.LINE_NUMBER, NULL))) REFERENCE_OPTION_NUMBER , DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'PO', SL1.CREATION_DATE, 'ORDER' , SL1.CREATION_DATE, NULL) REFERENCE_CREATION_DATE , DECODE (LIN.RETURN_REFERENCE_TYPE_CODE, 'INVOICE' , RCTL.QUANTITY_INVOICED, NULL, NULL, SL1.ORDERED_QUANTITY) ORIGINAL_ORDERED_QUANTITY , LIN.PRICING_CONTEXT , LIN.PRICING_ATTRIBUTE1 , LIN.PRICING_ATTRIBUTE2 , LIN.PRICING_ATTRIBUTE3 , LIN.PRICING_ATTRIBUTE4 , LIN.PRICING_ATTRIBUTE5 , LIN.PRICING_ATTRIBUTE6 , LIN.PRICING_ATTRIBUTE7 , LIN.PRICING_ATTRIBUTE8 , LIN.PRICING_ATTRIBUTE9 , LIN.PRICING_ATTRIBUTE10 , LIN.PRICING_ATTRIBUTE11 , LIN.PRICING_ATTRIBUTE12 , LIN.PRICING_ATTRIBUTE13 , LIN.PRICING_ATTRIBUTE14 , LIN.PRICING_ATTRIBUTE15 , LIN.S1 , LIN.S1_DATE , LIN.S2 , LIN.S2_DATE , LIN.S3 , LIN.S3_DATE , LIN.S4 , LIN.S4_DATE , LIN.S5 , LIN.S5_DATE , LIN.S6 , LIN.S6_DATE , LIN.S7 , LIN.S7_DATE , LIN.S8 , LIN.S8_DATE , LIN.S9 , LIN.S9_DATE , LIN.S10 , LIN.S10_DATE , LIN.S11 , LIN.S11_DATE , LIN.S12 , LIN.S12_DATE , LIN.S13 , LIN.S13_DATE , LIN.S14 , LIN.S14_DATE , LIN.S15 , LIN.S15_DATE , LIN.S16 , LIN.S16_DATE , LIN.S17 , LIN.S17_DATE , LIN.S18 , LIN.S18_DATE , LIN.S19 , LIN.S19_DATE , LIN.S20 , LIN.S20_DATE , LIN.S21 , LIN.S21_DATE , LIN.S22 , LIN.S22_DATE , LIN.S23 , LIN.S23_DATE , LIN.S24 , LIN.S24_DATE , LIN.S25 , LIN.S25_DATE , LIN.S26 , LIN.S26_DATE , LIN.S27 , LIN.S27_DATE , LIN.S28 , LIN.S28_DATE , LIN.S29 , LIN.S29_DATE , LIN.S30 , LIN.S30_DATE , LIN.CONTEXT , LIN.ATTRIBUTE1 , LIN.ATTRIBUTE2 , LIN.ATTRIBUTE3 , LIN.ATTRIBUTE4 , LIN.ATTRIBUTE5 , LIN.ATTRIBUTE6 , LIN.ATTRIBUTE7 , LIN.ATTRIBUTE8 , LIN.ATTRIBUTE9 , LIN.ATTRIBUTE10 , LIN.ATTRIBUTE11 , LIN.ATTRIBUTE12 , LIN.ATTRIBUTE13 , LIN.ATTRIBUTE14 , LIN.ATTRIBUTE15 , LIN.NUMBER_SVRID , LIN.ITEM_SVRID , LIN.ORDERED_QUANTITY_SVRID , LIN.UNIT_SVRID , LIN.PRICING_ATTRIBUTES_SVRID , LIN.LIST_PRICE_SVRID , LIN.SELLING_PRICE_SVRID , LIN.LIST_PERCENT_SVRID , LIN.SELLING_PERCENT_SVRID , LIN.WAREHOUSE_SVRID , LIN.DEMAND_CLASS_SVRID , LIN.SHIP_SET_NUMBER_SVRID , LIN.REQUEST_DATE_SVRID , LIN.PROMISE_DATE_SVRID , LIN.SCHEDULE_DATE_SVRID , LIN.SHIP_TO_SVRID , LIN.SHIP_TO_CONTACT_SVRID , LIN.SHIPMENT_PRIORITY_SVRID , LIN.FREIGHT_CARRIER_SVRID , LIN.AGREEMENT_SVRID , LIN.COMMITMENT_SVRID , LIN.PAYMENT_TERMS_SVRID , LIN.INVOICING_RULE_SVRID , LIN.ACCOUNTING_RULE_SVRID , LIN.TAX_EXEMPT_NUMBER_SVRID , LIN.TAX_EXEMPT_REASON_SVRID , LIN.TRANSACTION_TYPE_SVRID , LIN.TRANSACTION_REASON_SVRID , LIN.TRANSACTION_COMMENTS_SVRID , LIN.RETURN_REFERENCE_TYPE_SVRID , LIN.RETURN_REFERENCE_SVRID , LIN.CREDIT_INVOICE_LINE_SVRID , LIN.INSPECTION_REQUIRED_SVRID , LIN.CUSTOMER_PRODUCT_SVRID , LIN.CUSTOMER_PRODUCT_SERVICE_SVRID , LIN.SERVICE_DURATION_SVRID , LIN.SERVICE_START_DATE_SVRID , LIN.SERVICE_END_DATE_SVRID , LIN.SERVICE_PER_CONV_RATE_SVRID , LIN.SERVICE_COTERMINATE_SVRID , LIN.DF_SVRID , LIN.TAX_CODE_SVRID , LIN.LATEST_ACCEPTABLE_DATE_SVRID , LIN.ATO_LINE_ID , LIN.ACCOUNTING_RULE_ID , LIN.AGREEMENT_ID , LIN.COMMITMENT_ID , LIN.CONFIGURATION_EDITED_FLAG , LIN.CUSTOMER_PRODUCT_ID , LIN.CP_SERVICE_ID , LIN.DEMAND_CLASS_CODE , LIN.SHIP_METHOD_CODE , LIN.INVOICE_COUNT , LIN.QUANTITY_TO_INVOICE , LIN.INVOICING_RULE_ID , LIN.LATEST_ACCEPTABLE_DATE , LIN.ORIGINAL_SYSTEM_LINE_REFERENCE , LIN.PERCENT_BASE_PRICE , LIN.PRICING_DATE , LIN.REVENUE_AMOUNT , LIN.SCHEDULE_DATE , LIN.SERVICE_COTERMINATE_FLAG , LIN.SERVICE_DURATION , LIN.SERVICE_END_DATE , LIN.SERVICE_MASS_TXN_TEMP_ID , LIN.SERVICE_PARENT_LINE_ID , LIN.SERVICE_PERIOD_CONVERSION_RATE , LIN.SERVICE_START_DATE , LIN.SERVICED_QUANTITY , LIN.SHIP_MODEL_COMPLETE_FLAG , LIN.SHIP_SET_NUMBER , LIN.SHIP_TO_CONTACT_ID , LIN.SOURCE_LINE_ID , LIN.STANDARD_COMPONENT_FREEZE_DATE , LIN.TRANSACTION_COMMENTS , LIN.TRANSACTION_TYPE_CODE , LIN.PROMISE_DATE , LIN.SHIPMENT_PRIORITY_CODE , LIN.SHIPMENT_SCHEDULE_LINE_ID , LIN.SOURCE_TYPE_CODE , SO3.MEANING SOURCE_TYPE , LIN.SOURCE_TYPE_CODE_SVRID , LIN.PLANNING_PRIORITY , LIN.PLANNING_PRIORITY_SVRID FROM SO_LINES LIN /* RETURN LINE */ , SO_HEADERS ORD /* RETURN ORDER */ , RA_CUSTOMER_TRX RCT /* INVOICE REFERENCE */ , RA_CUSTOMER_TRX_LINES RCTL /* INVOICE REFERENCE LINE */ , AR_VAT_TAX TAX /* INVOICE TAX CODE */ , SO_HEADERS SOH /* PO/ORDER REFERENCE ORDER */ , SO_LINES SL1 /* PO/ORDER REFERENCE LINE */ , SO_LINES SL2 /* PO/ORDER REFERENCE OPTION LINE */ , SO_LINES SL3 /* PO/ORDER REFERENCE SHIPMENT LINE */ , RA_CUSTOMER_TRX CRD /* CREDIT TO INVOICE */ , RA_CUSTOMER_TRX_LINES CRDL /* CREDIT TO INVOICE LINE */ , AR_LOOKUPS ARL /* RETURN REASON NAME */ , SO_PRICE_LISTS SPL /* PRICE LIST */ , SO_LOOKUPS SOITM /* ITEM TYPE */ , SO_LOOKUPS SOREF /* RETURN REFERENCE TYPE NAME */ , MTL_SYSTEM_ITEMS_VL MTL /* ITEM DESCRPTION */ , MTL_PARAMETERS MTLPRT /* WAREHOUSE */ , SO_LOOKUPS SO3 /* SOURCE TYPE */ WHERE LIN.HEADER_ID = ORD.HEADER_ID AND LIN.RETURN_REFERENCE_ID = SL1.LINE_ID(+) AND SL1.HEADER_ID = SOH.HEADER_ID(+) AND NVL(SL1.SHIPMENT_SCHEDULE_LINE_ID,0)= SL3.LINE_ID(+) AND NVL(SL1.PARENT_LINE_ID,0) = SL2.LINE_ID(+) AND LIN.RETURN_REFERENCE_ID = RCTL.CUSTOMER_TRX_LINE_ID(+) AND RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID(+) AND RCTL.VAT_TAX_ID = TAX.VAT_TAX_ID (+) AND LIN.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND LIN.WAREHOUSE_ID = MTLPRT.ORGANIZATION_ID AND LIN.PRICE_LIST_ID = SPL.PRICE_LIST_ID (+) AND MTL.ORGANIZATION_ID = FND_PROFILE.Value('SO_ORGANIZATION_ID') AND LIN.ITEM_TYPE_CODE = SOITM.LOOKUP_CODE(+) AND SOITM.LOOKUP_TYPE(+) = 'ITEM TYPE' AND LIN.RETURN_REFERENCE_TYPE_CODE = SOREF.LOOKUP_CODE(+) AND SOREF.LOOKUP_TYPE(+) = 'REFERENCE_TYPE' AND LIN.TRANSACTION_REASON_CODE = ARL.LOOKUP_CODE(+) AND ARL.LOOKUP_TYPE(+) = 'CREDIT_MEMO_REASON' AND LIN.CREDIT_INVOICE_LINE_ID = CRDL.CUSTOMER_TRX_LINE_ID(+) AND CRDL.CUSTOMER_TRX_ID = CRD.CUSTOMER_TRX_ID(+) AND SO3.LOOKUP_TYPE(+) = 'SOURCE TYPE' AND LIN.SOURCE_TYPE_CODE = SO3.LOOKUP_CODE(+)

View Text - HTML Formatted

SELECT LIN.ROWID ROW_ID
, LIN.LINE_ID
, LIN.CREATION_DATE
, LIN.CREATED_BY
, LIN.LAST_UPDATE_DATE
, LIN.LAST_UPDATED_BY
, LIN.LAST_UPDATE_LOGIN
, LIN.REQUEST_ID
, LIN.PROGRAM_APPLICATION_ID
, LIN.PROGRAM_ID
, LIN.PROGRAM_UPDATE_DATE
, LIN.HEADER_ID
, LIN.PARENT_LINE_ID
, LIN.LINK_TO_LINE_ID
, LIN.LINE_TYPE_CODE
, LIN.OPTION_FLAG
, LIN.OPEN_FLAG
, LIN.CYCLE_ID
, LIN.LINE_NUMBER
, LIN.INVENTORY_ITEM_ID
, MTL.DESCRIPTION ITEM_DESCRIPTION
, MTL.BOM_ITEM_TYPE
, LIN.COMPONENT_SEQUENCE_ID
, LIN.ITEM_TYPE_CODE
, SOITM.MEANING ITEM_TYPE
, LIN.ATO_FLAG
, LIN.COMPONENT_CODE
, LIN.COMPONENT_SORT_CODE
, LIN.ORDERED_QUANTITY
, LIN.CANCELLED_QUANTITY
, OE_QUERY.RECEIVED_QTY(LIN.LINE_ID) RECEIVED_QUANTITY
, OE_QUERY.ACCEPTED_QTY(LIN.LINE_ID) ACCEPTED_QUANTITY
, OE_QUERY.RECEIVED_DATE(LIN.LINE_ID
, LIN.S29_DATE) ACTUAL_RECEIPT_DATE
, OE_QUERY.ACCEPTED_DATE(LIN.LINE_ID
, LIN.S29_DATE) ACTUAL_ACCEPTED_DATE
, LIN.UNIT_CODE
, LIN.PRICE_LIST_ID
, SPL.ROUNDING_FACTOR ROUNDING_FACTOR
, OE_QUERY.GET_PRICE_ADJ_TOTAL( LIN.HEADER_ID
, LIN.LINE_ID) PRICE_ADJUSTMENTS_TOTAL
, LIN.PRICING_METHOD_CODE
, LIN.LIST_PRICE
, LIN.LIST_PERCENT
, LIN.SELLING_PRICE
, LIN.SELLING_PERCENT
, LIN.WAREHOUSE_ID
, MTLPRT.ORGANIZATION_CODE WAREHOUSE_CODE
, LIN.SHIPPED_QUANTITY
, LIN.DATE_REQUESTED_CURRENT
, LIN.SHIP_TO_SITE_USE_ID
, LIN.TERMS_ID
, LIN.INVOICED_QUANTITY
, DECODE(LIN.CREDIT_INVOICE_LINE_ID
, NULL
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, DECODE (LIN.OPEN_FLAG
, 'Y'
, RCTL.TAX_EXEMPT_FLAG
, NULL)
, 'PO'
, SOH.TAX_EXEMPT_FLAG
, 'ORDER'
, SOH.TAX_EXEMPT_FLAG
, ORD.TAX_EXEMPT_FLAG)
, NULL) TAX_EXEMPT_FLAG
, DECODE(LIN.CREDIT_INVOICE_LINE_ID
, NULL
, OE_QUERY.GET_TAX_EXEMPT_FLAG( LIN.RETURN_REFERENCE_TYPE_CODE
, RCTL.TAX_EXEMPT_FLAG
, SOH.TAX_EXEMPT_FLAG
, ORD.TAX_EXEMPT_FLAG
, LIN.OPEN_FLAG)
, NULL) TAX_EXEMPT_FLAG_DISPLAY
, DECODE(LIN.CREDIT_INVOICE_LINE_ID
, NULL
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, DECODE (LIN.OPEN_FLAG
, 'Y'
, RCTL.TAX_EXEMPT_NUMBER
, NULL)
, 'PO'
, SOH.TAX_EXEMPT_NUM
, 'ORDER'
, SOH.TAX_EXEMPT_NUM
, ORD.TAX_EXEMPT_NUM)
, NULL) TAX_EXEMPT_NUMBER_DISPLAY
, DECODE(LIN.CREDIT_INVOICE_LINE_ID
, NULL
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, DECODE (LIN.OPEN_FLAG
, 'Y'
, RCTL.TAX_EXEMPT_REASON_CODE
, NULL)
, 'PO'
, SOH.TAX_EXEMPT_REASON_CODE
, 'ORDER'
, SOH.TAX_EXEMPT_REASON_CODE
, ORD.TAX_EXEMPT_REASON_CODE)
, NULL) TAX_EXEMPT_REASON
, DECODE(LIN.CREDIT_INVOICE_LINE_ID
, NULL
, OE_QUERY.GET_TAX_EXEMPT_REASON( LIN.RETURN_REFERENCE_TYPE_CODE
, RCTL.TAX_EXEMPT_REASON_CODE
, SOH.TAX_EXEMPT_REASON_CODE
, ORD.TAX_EXEMPT_REASON_CODE
, LIN.OPEN_FLAG)
, NULL) TAX_EXEMPT_REASON_DISPLAY
, LIN.TAX_EXEMPT_NUMBER
, LIN.TAX_EXEMPT_REASON_CODE
, DECODE(LIN.CREDIT_INVOICE_LINE_ID
, NULL
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, DECODE (LIN.OPEN_FLAG
, 'Y'
, TAX.TAX_CODE
, NULL)
, 'PO'
, SL1.TAX_CODE
, 'ORDER'
, SL1.TAX_CODE
, LIN.TAX_CODE)
, NULL) TAX_CODE_DISPLAY
, LIN.TAX_CODE
, LIN.TRANSACTION_REASON_CODE
, ARL.MEANING RETURN_REASON_NAME
, LIN.RETURN_REFERENCE_TYPE_CODE
, SOREF.MEANING RETURN_REFERENCE_TYPE_NAME
, LIN.RETURN_REFERENCE_ID
, LIN.CREDIT_INVOICE_LINE_ID
, CRD.TRX_NUMBER CREDIT_TO_INVOICE
, LIN.INSPECTION_REQUIRED_FLAG
, LIN.RETURN_FOR_REPAIR_FLAG
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, RCT.TRX_NUMBER
, 'PO'
, SOH.PURCHASE_ORDER_NUM
, 'ORDER'
, SOH.ORDER_NUMBER
, NULL) REFERENCE_NUMBER
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, RCT.CUSTOMER_TRX_ID
, NULL
, NULL
, SOH.HEADER_ID) REFERENCE_HEADER_ID
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, RCTL.LINE_NUMBER
, NULL
, NULL
, DECODE(SL1.SHIPMENT_SCHEDULE_LINE_ID
, NULL
, DECODE(SL1.PARENT_LINE_ID
, NULL
, SL1.LINE_NUMBER
, SL2.LINE_NUMBER)
, SL3.LINE_NUMBER)) REFERENCE_LINE_NUMBER
, TO_NUMBER(DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, NULL
, NULL
, NULL
, DECODE(SL1.PARENT_LINE_ID
, NULL
, DECODE(SL1.SHIPMENT_SCHEDULE_LINE_ID
, NULL
, NULL
, SL1.LINE_NUMBER)
, SL2.LINE_NUMBER))) REFERENCE_SHIPMENT_NUMBER
, TO_NUMBER(DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, NULL
, NULL
, NULL
, DECODE(SL1.OPTION_FLAG
, 'Y'
, SL1.LINE_NUMBER
, NULL))) REFERENCE_OPTION_NUMBER
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'PO'
, SL1.CREATION_DATE
, 'ORDER'
, SL1.CREATION_DATE
, NULL) REFERENCE_CREATION_DATE
, DECODE (LIN.RETURN_REFERENCE_TYPE_CODE
, 'INVOICE'
, RCTL.QUANTITY_INVOICED
, NULL
, NULL
, SL1.ORDERED_QUANTITY) ORIGINAL_ORDERED_QUANTITY
, LIN.PRICING_CONTEXT
, LIN.PRICING_ATTRIBUTE1
, LIN.PRICING_ATTRIBUTE2
, LIN.PRICING_ATTRIBUTE3
, LIN.PRICING_ATTRIBUTE4
, LIN.PRICING_ATTRIBUTE5
, LIN.PRICING_ATTRIBUTE6
, LIN.PRICING_ATTRIBUTE7
, LIN.PRICING_ATTRIBUTE8
, LIN.PRICING_ATTRIBUTE9
, LIN.PRICING_ATTRIBUTE10
, LIN.PRICING_ATTRIBUTE11
, LIN.PRICING_ATTRIBUTE12
, LIN.PRICING_ATTRIBUTE13
, LIN.PRICING_ATTRIBUTE14
, LIN.PRICING_ATTRIBUTE15
, LIN.S1
, LIN.S1_DATE
, LIN.S2
, LIN.S2_DATE
, LIN.S3
, LIN.S3_DATE
, LIN.S4
, LIN.S4_DATE
, LIN.S5
, LIN.S5_DATE
, LIN.S6
, LIN.S6_DATE
, LIN.S7
, LIN.S7_DATE
, LIN.S8
, LIN.S8_DATE
, LIN.S9
, LIN.S9_DATE
, LIN.S10
, LIN.S10_DATE
, LIN.S11
, LIN.S11_DATE
, LIN.S12
, LIN.S12_DATE
, LIN.S13
, LIN.S13_DATE
, LIN.S14
, LIN.S14_DATE
, LIN.S15
, LIN.S15_DATE
, LIN.S16
, LIN.S16_DATE
, LIN.S17
, LIN.S17_DATE
, LIN.S18
, LIN.S18_DATE
, LIN.S19
, LIN.S19_DATE
, LIN.S20
, LIN.S20_DATE
, LIN.S21
, LIN.S21_DATE
, LIN.S22
, LIN.S22_DATE
, LIN.S23
, LIN.S23_DATE
, LIN.S24
, LIN.S24_DATE
, LIN.S25
, LIN.S25_DATE
, LIN.S26
, LIN.S26_DATE
, LIN.S27
, LIN.S27_DATE
, LIN.S28
, LIN.S28_DATE
, LIN.S29
, LIN.S29_DATE
, LIN.S30
, LIN.S30_DATE
, LIN.CONTEXT
, LIN.ATTRIBUTE1
, LIN.ATTRIBUTE2
, LIN.ATTRIBUTE3
, LIN.ATTRIBUTE4
, LIN.ATTRIBUTE5
, LIN.ATTRIBUTE6
, LIN.ATTRIBUTE7
, LIN.ATTRIBUTE8
, LIN.ATTRIBUTE9
, LIN.ATTRIBUTE10
, LIN.ATTRIBUTE11
, LIN.ATTRIBUTE12
, LIN.ATTRIBUTE13
, LIN.ATTRIBUTE14
, LIN.ATTRIBUTE15
, LIN.NUMBER_SVRID
, LIN.ITEM_SVRID
, LIN.ORDERED_QUANTITY_SVRID
, LIN.UNIT_SVRID
, LIN.PRICING_ATTRIBUTES_SVRID
, LIN.LIST_PRICE_SVRID
, LIN.SELLING_PRICE_SVRID
, LIN.LIST_PERCENT_SVRID
, LIN.SELLING_PERCENT_SVRID
, LIN.WAREHOUSE_SVRID
, LIN.DEMAND_CLASS_SVRID
, LIN.SHIP_SET_NUMBER_SVRID
, LIN.REQUEST_DATE_SVRID
, LIN.PROMISE_DATE_SVRID
, LIN.SCHEDULE_DATE_SVRID
, LIN.SHIP_TO_SVRID
, LIN.SHIP_TO_CONTACT_SVRID
, LIN.SHIPMENT_PRIORITY_SVRID
, LIN.FREIGHT_CARRIER_SVRID
, LIN.AGREEMENT_SVRID
, LIN.COMMITMENT_SVRID
, LIN.PAYMENT_TERMS_SVRID
, LIN.INVOICING_RULE_SVRID
, LIN.ACCOUNTING_RULE_SVRID
, LIN.TAX_EXEMPT_NUMBER_SVRID
, LIN.TAX_EXEMPT_REASON_SVRID
, LIN.TRANSACTION_TYPE_SVRID
, LIN.TRANSACTION_REASON_SVRID
, LIN.TRANSACTION_COMMENTS_SVRID
, LIN.RETURN_REFERENCE_TYPE_SVRID
, LIN.RETURN_REFERENCE_SVRID
, LIN.CREDIT_INVOICE_LINE_SVRID
, LIN.INSPECTION_REQUIRED_SVRID
, LIN.CUSTOMER_PRODUCT_SVRID
, LIN.CUSTOMER_PRODUCT_SERVICE_SVRID
, LIN.SERVICE_DURATION_SVRID
, LIN.SERVICE_START_DATE_SVRID
, LIN.SERVICE_END_DATE_SVRID
, LIN.SERVICE_PER_CONV_RATE_SVRID
, LIN.SERVICE_COTERMINATE_SVRID
, LIN.DF_SVRID
, LIN.TAX_CODE_SVRID
, LIN.LATEST_ACCEPTABLE_DATE_SVRID
, LIN.ATO_LINE_ID
, LIN.ACCOUNTING_RULE_ID
, LIN.AGREEMENT_ID
, LIN.COMMITMENT_ID
, LIN.CONFIGURATION_EDITED_FLAG
, LIN.CUSTOMER_PRODUCT_ID
, LIN.CP_SERVICE_ID
, LIN.DEMAND_CLASS_CODE
, LIN.SHIP_METHOD_CODE
, LIN.INVOICE_COUNT
, LIN.QUANTITY_TO_INVOICE
, LIN.INVOICING_RULE_ID
, LIN.LATEST_ACCEPTABLE_DATE
, LIN.ORIGINAL_SYSTEM_LINE_REFERENCE
, LIN.PERCENT_BASE_PRICE
, LIN.PRICING_DATE
, LIN.REVENUE_AMOUNT
, LIN.SCHEDULE_DATE
, LIN.SERVICE_COTERMINATE_FLAG
, LIN.SERVICE_DURATION
, LIN.SERVICE_END_DATE
, LIN.SERVICE_MASS_TXN_TEMP_ID
, LIN.SERVICE_PARENT_LINE_ID
, LIN.SERVICE_PERIOD_CONVERSION_RATE
, LIN.SERVICE_START_DATE
, LIN.SERVICED_QUANTITY
, LIN.SHIP_MODEL_COMPLETE_FLAG
, LIN.SHIP_SET_NUMBER
, LIN.SHIP_TO_CONTACT_ID
, LIN.SOURCE_LINE_ID
, LIN.STANDARD_COMPONENT_FREEZE_DATE
, LIN.TRANSACTION_COMMENTS
, LIN.TRANSACTION_TYPE_CODE
, LIN.PROMISE_DATE
, LIN.SHIPMENT_PRIORITY_CODE
, LIN.SHIPMENT_SCHEDULE_LINE_ID
, LIN.SOURCE_TYPE_CODE
, SO3.MEANING SOURCE_TYPE
, LIN.SOURCE_TYPE_CODE_SVRID
, LIN.PLANNING_PRIORITY
, LIN.PLANNING_PRIORITY_SVRID
FROM SO_LINES LIN /* RETURN LINE */
, SO_HEADERS ORD /* RETURN ORDER */
, RA_CUSTOMER_TRX RCT /* INVOICE REFERENCE */
, RA_CUSTOMER_TRX_LINES RCTL /* INVOICE REFERENCE LINE */
, AR_VAT_TAX TAX /* INVOICE TAX CODE */
, SO_HEADERS SOH /* PO/ORDER REFERENCE ORDER */
, SO_LINES SL1 /* PO/ORDER REFERENCE LINE */
, SO_LINES SL2 /* PO/ORDER REFERENCE OPTION LINE */
, SO_LINES SL3 /* PO/ORDER REFERENCE SHIPMENT LINE */
, RA_CUSTOMER_TRX CRD /* CREDIT TO INVOICE */
, RA_CUSTOMER_TRX_LINES CRDL /* CREDIT TO INVOICE LINE */
, AR_LOOKUPS ARL /* RETURN REASON NAME */
, SO_PRICE_LISTS SPL /* PRICE LIST */
, SO_LOOKUPS SOITM /* ITEM TYPE */
, SO_LOOKUPS SOREF /* RETURN REFERENCE TYPE NAME */
, MTL_SYSTEM_ITEMS_VL MTL /* ITEM DESCRPTION */
, MTL_PARAMETERS MTLPRT /* WAREHOUSE */
, SO_LOOKUPS SO3 /* SOURCE TYPE */
WHERE LIN.HEADER_ID = ORD.HEADER_ID
AND LIN.RETURN_REFERENCE_ID = SL1.LINE_ID(+)
AND SL1.HEADER_ID = SOH.HEADER_ID(+)
AND NVL(SL1.SHIPMENT_SCHEDULE_LINE_ID
, 0)= SL3.LINE_ID(+)
AND NVL(SL1.PARENT_LINE_ID
, 0) = SL2.LINE_ID(+)
AND LIN.RETURN_REFERENCE_ID = RCTL.CUSTOMER_TRX_LINE_ID(+)
AND RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID(+)
AND RCTL.VAT_TAX_ID = TAX.VAT_TAX_ID (+)
AND LIN.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND LIN.WAREHOUSE_ID = MTLPRT.ORGANIZATION_ID
AND LIN.PRICE_LIST_ID = SPL.PRICE_LIST_ID (+)
AND MTL.ORGANIZATION_ID = FND_PROFILE.VALUE('SO_ORGANIZATION_ID')
AND LIN.ITEM_TYPE_CODE = SOITM.LOOKUP_CODE(+)
AND SOITM.LOOKUP_TYPE(+) = 'ITEM TYPE'
AND LIN.RETURN_REFERENCE_TYPE_CODE = SOREF.LOOKUP_CODE(+)
AND SOREF.LOOKUP_TYPE(+) = 'REFERENCE_TYPE'
AND LIN.TRANSACTION_REASON_CODE = ARL.LOOKUP_CODE(+)
AND ARL.LOOKUP_TYPE(+) = 'CREDIT_MEMO_REASON'
AND LIN.CREDIT_INVOICE_LINE_ID = CRDL.CUSTOMER_TRX_LINE_ID(+)
AND CRDL.CUSTOMER_TRX_ID = CRD.CUSTOMER_TRX_ID(+)
AND SO3.LOOKUP_TYPE(+) = 'SOURCE TYPE'
AND LIN.SOURCE_TYPE_CODE = SO3.LOOKUP_CODE(+)