View: SO_LINES_RMA_V

Product:	OE - Order Entry
Description:
Implementation/DBA Data:	APPS.SO_LINES_RMA_V

View Text

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(+)

Columns