VIEW: APPS.ICX_SO_LINES_VIEW_RETURNS_V

Source

View Text - Preformatted

SELECT /*+ INDEX(SH SO_HEADERS_U1) INDEX(SL SO_LINES_N24) */ SL.LINE_ID LINE_ID , SL.HEADER_ID HEADER_ID , SL.LINE_NUMBER LINE_NUMBER , UOM.UOM_CODE UOM_CODE , NVL(SL.ORDERED_QUANTITY,0)-NVL(SL.CANCELLED_QUANTITY,0) OPEN_QUANTITY , SL.CANCELLED_QUANTITY CANCELLED_QUANTITY , SL.SELLING_PRICE SELLING_PRICE , AL.MEANING RETURN_REASON , RCT.TRX_NUMBER CREDIT_TO_INVOICE , SL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , NVL(MSI.DESCRIPTION, 'NO DESCRIPTION AVAILABLE') ITEM_DESC , OE_QUERY.ITEM_CONC_SEG(SL.INVENTORY_ITEM_ID, FND_PROFILE.VALUE_SPECIFIC('SO_ORGANIZATION_ID')) ITEM , SL.PRICING_ATTRIBUTE1 PRICING_ATTRIBUTE1 , SL.PRICING_ATTRIBUTE2 PRICING_ATTRIBUTE2 , SL.PRICING_ATTRIBUTE3 PRICING_ATTRIBUTE3 , SL.PRICING_ATTRIBUTE4 PRICING_ATTRIBUTE4 , SL.PRICING_ATTRIBUTE5 PRICING_ATTRIBUTE5 , SL.PRICING_ATTRIBUTE6 PRICING_ATTRIBUTE6 , SL.PRICING_ATTRIBUTE7 PRICING_ATTRIBUTE7 , SL.PRICING_ATTRIBUTE8 PRICING_ATTRIBUTE8 , SL.PRICING_ATTRIBUTE9 PRICING_ATTRIBUTE9 , SL.PRICING_ATTRIBUTE10 PRICING_ATTRIBUTE10 , SL.PRICING_ATTRIBUTE11 PRICING_ATTRIBUTE11 , SL.PRICING_ATTRIBUTE12 PRICING_ATTRIBUTE12 , SL.PRICING_ATTRIBUTE13 PRICING_ATTRIBUTE13 , SL.PRICING_ATTRIBUTE14 PRICING_ATTRIBUTE14 , SL.PRICING_ATTRIBUTE15 PRICING_ATTRIBUTE15 , SL.PRICING_CONTEXT PRICING_CONTEXT , SL.ATTRIBUTE1 ATTRIBUTE1 , SL.ATTRIBUTE2 ATTRIBUTE2 , SL.ATTRIBUTE3 ATTRIBUTE3 , SL.ATTRIBUTE4 ATTRIBUTE4 , SL.ATTRIBUTE5 ATTRIBUTE5 , SL.ATTRIBUTE6 ATTRIBUTE6 , SL.ATTRIBUTE7 ATTRIBUTE7 , SL.ATTRIBUTE8 ATTRIBUTE8 , SL.ATTRIBUTE9 ATTRIBUTE9 , SL.ATTRIBUTE10 ATTRIBUTE10 , SL.ATTRIBUTE11 ATTRIBUTE11 , SL.ATTRIBUTE12 ATTRIBUTE12 , SL.ATTRIBUTE13 ATTRIBUTE13 , SL.ATTRIBUTE14 ATTRIBUTE14 , SL.ATTRIBUTE15 ATTRIBUTE15 , SL.CONTEXT CONTEXT , OE_QUERY.ACCEPTED_QTY(SL.LINE_ID) ACCEPTED_QUANTITY , SH.ORDER_NUMBER , OE_QUERY.ORDER_TYPE(SH.ORDER_TYPE_ID) ORDER_TYPE , SH.CURRENCY_CODE , SL.RETURN_REFERENCE_TYPE_CODE , SL.RETURN_REFERENCE_ID , SL.DATE_REQUESTED_CURRENT , 0 DUMMY_ID FROM SO_LINES SL, MTL_UNITS_OF_MEASURE UOM, AR_LOOKUPS AL , RA_CUSTOMER_TRX RCT, RA_CUSTOMER_TRX_LINES RCTL , MTL_SYSTEM_ITEMS MSI , SO_HEADERS SH WHERE UOM.UOM_CODE = NVL( SL.UNIT_CODE, 'EA' ) AND AL.LOOKUP_TYPE(+)='CREDIT_MEMO_REASON' AND AL.LOOKUP_CODE(+)=NVL(SL.TRANSACTION_REASON_CODE,'RETURN') AND RCT.CUSTOMER_TRX_ID(+) = RCTL.CUSTOMER_TRX_ID AND RCTL.CUSTOMER_TRX_LINE_ID(+) = SL.CREDIT_INVOICE_LINE_ID AND MSI.INVENTORY_ITEM_ID(+) = SL.INVENTORY_ITEM_ID AND MSI.INVENTORY_ITEM_ID(+) = SL.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID(+) = FND_PROFILE.VALUE_SPECIFIC('SO_ORGANIZATION_ID') AND SH.HEADER_ID = SL.HEADER_ID GROUP BY SL.LINE_ID, SL.HEADER_ID, SL.LINE_NUMBER, UOM.UOM_CODE , SL.SELLING_PRICE, SL.ORDERED_QUANTITY, SL.CANCELLED_QUANTITY , AL.MEANING, RCT.TRX_NUMBER , SL.PRICING_ATTRIBUTE1, SL.PRICING_ATTRIBUTE2, SL.PRICING_ATTRIBUTE3 , SL.PRICING_ATTRIBUTE4, SL.PRICING_ATTRIBUTE5, SL.PRICING_ATTRIBUTE6 , SL.PRICING_ATTRIBUTE7, SL.PRICING_ATTRIBUTE8, SL.PRICING_ATTRIBUTE9 , SL.PRICING_ATTRIBUTE10, SL.PRICING_ATTRIBUTE11, SL.PRICING_ATTRIBUTE12 , SL.PRICING_ATTRIBUTE13, SL.PRICING_ATTRIBUTE14 , SL.PRICING_ATTRIBUTE15, SL.PRICING_CONTEXT , SL.ATTRIBUTE1, SL.ATTRIBUTE2,SL.ATTRIBUTE3, SL.ATTRIBUTE4 , SL.ATTRIBUTE5, SL.ATTRIBUTE6, SL.ATTRIBUTE7, SL.ATTRIBUTE8 , SL.ATTRIBUTE9,SL.ATTRIBUTE10, SL.ATTRIBUTE11, SL.ATTRIBUTE12 , SL.ATTRIBUTE13, SL.ATTRIBUTE14, SL.ATTRIBUTE15, SL.CONTEXT , SL.INVENTORY_ITEM_ID, MSI.DESCRIPTION , SL.RETURN_REFERENCE_TYPE_CODE, SL.RETURN_REFERENCE_ID , SL.DATE_REQUESTED_CURRENT , SH.ORDER_TYPE_ID, SH.ORDER_NUMBER, SH.CURRENCY_CODE

View Text - HTML Formatted

SELECT /*+ INDEX(SH SO_HEADERS_U1) INDEX(SL SO_LINES_N24) */ SL.LINE_ID LINE_ID
, SL.HEADER_ID HEADER_ID
, SL.LINE_NUMBER LINE_NUMBER
, UOM.UOM_CODE UOM_CODE
, NVL(SL.ORDERED_QUANTITY
, 0)-NVL(SL.CANCELLED_QUANTITY
, 0) OPEN_QUANTITY
, SL.CANCELLED_QUANTITY CANCELLED_QUANTITY
, SL.SELLING_PRICE SELLING_PRICE
, AL.MEANING RETURN_REASON
, RCT.TRX_NUMBER CREDIT_TO_INVOICE
, SL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, NVL(MSI.DESCRIPTION
, 'NO DESCRIPTION AVAILABLE') ITEM_DESC
, OE_QUERY.ITEM_CONC_SEG(SL.INVENTORY_ITEM_ID
, FND_PROFILE.VALUE_SPECIFIC('SO_ORGANIZATION_ID')) ITEM
, SL.PRICING_ATTRIBUTE1 PRICING_ATTRIBUTE1
, SL.PRICING_ATTRIBUTE2 PRICING_ATTRIBUTE2
, SL.PRICING_ATTRIBUTE3 PRICING_ATTRIBUTE3
, SL.PRICING_ATTRIBUTE4 PRICING_ATTRIBUTE4
, SL.PRICING_ATTRIBUTE5 PRICING_ATTRIBUTE5
, SL.PRICING_ATTRIBUTE6 PRICING_ATTRIBUTE6
, SL.PRICING_ATTRIBUTE7 PRICING_ATTRIBUTE7
, SL.PRICING_ATTRIBUTE8 PRICING_ATTRIBUTE8
, SL.PRICING_ATTRIBUTE9 PRICING_ATTRIBUTE9
, SL.PRICING_ATTRIBUTE10 PRICING_ATTRIBUTE10
, SL.PRICING_ATTRIBUTE11 PRICING_ATTRIBUTE11
, SL.PRICING_ATTRIBUTE12 PRICING_ATTRIBUTE12
, SL.PRICING_ATTRIBUTE13 PRICING_ATTRIBUTE13
, SL.PRICING_ATTRIBUTE14 PRICING_ATTRIBUTE14
, SL.PRICING_ATTRIBUTE15 PRICING_ATTRIBUTE15
, SL.PRICING_CONTEXT PRICING_CONTEXT
, SL.ATTRIBUTE1 ATTRIBUTE1
, SL.ATTRIBUTE2 ATTRIBUTE2
, SL.ATTRIBUTE3 ATTRIBUTE3
, SL.ATTRIBUTE4 ATTRIBUTE4
, SL.ATTRIBUTE5 ATTRIBUTE5
, SL.ATTRIBUTE6 ATTRIBUTE6
, SL.ATTRIBUTE7 ATTRIBUTE7
, SL.ATTRIBUTE8 ATTRIBUTE8
, SL.ATTRIBUTE9 ATTRIBUTE9
, SL.ATTRIBUTE10 ATTRIBUTE10
, SL.ATTRIBUTE11 ATTRIBUTE11
, SL.ATTRIBUTE12 ATTRIBUTE12
, SL.ATTRIBUTE13 ATTRIBUTE13
, SL.ATTRIBUTE14 ATTRIBUTE14
, SL.ATTRIBUTE15 ATTRIBUTE15
, SL.CONTEXT CONTEXT
, OE_QUERY.ACCEPTED_QTY(SL.LINE_ID) ACCEPTED_QUANTITY
, SH.ORDER_NUMBER
, OE_QUERY.ORDER_TYPE(SH.ORDER_TYPE_ID) ORDER_TYPE
, SH.CURRENCY_CODE
, SL.RETURN_REFERENCE_TYPE_CODE
, SL.RETURN_REFERENCE_ID
, SL.DATE_REQUESTED_CURRENT
, 0 DUMMY_ID
FROM SO_LINES SL
, MTL_UNITS_OF_MEASURE UOM
, AR_LOOKUPS AL
, RA_CUSTOMER_TRX RCT
, RA_CUSTOMER_TRX_LINES RCTL
, MTL_SYSTEM_ITEMS MSI
, SO_HEADERS SH
WHERE UOM.UOM_CODE = NVL( SL.UNIT_CODE
, 'EA' )
AND AL.LOOKUP_TYPE(+)='CREDIT_MEMO_REASON'
AND AL.LOOKUP_CODE(+)=NVL(SL.TRANSACTION_REASON_CODE
, 'RETURN')
AND RCT.CUSTOMER_TRX_ID(+) = RCTL.CUSTOMER_TRX_ID
AND RCTL.CUSTOMER_TRX_LINE_ID(+) = SL.CREDIT_INVOICE_LINE_ID
AND MSI.INVENTORY_ITEM_ID(+) = SL.INVENTORY_ITEM_ID
AND MSI.INVENTORY_ITEM_ID(+) = SL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID(+) = FND_PROFILE.VALUE_SPECIFIC('SO_ORGANIZATION_ID')
AND SH.HEADER_ID = SL.HEADER_ID GROUP BY SL.LINE_ID
, SL.HEADER_ID
, SL.LINE_NUMBER
, UOM.UOM_CODE
, SL.SELLING_PRICE
, SL.ORDERED_QUANTITY
, SL.CANCELLED_QUANTITY
, AL.MEANING
, RCT.TRX_NUMBER
, SL.PRICING_ATTRIBUTE1
, SL.PRICING_ATTRIBUTE2
, SL.PRICING_ATTRIBUTE3
, SL.PRICING_ATTRIBUTE4
, SL.PRICING_ATTRIBUTE5
, SL.PRICING_ATTRIBUTE6
, SL.PRICING_ATTRIBUTE7
, SL.PRICING_ATTRIBUTE8
, SL.PRICING_ATTRIBUTE9
, SL.PRICING_ATTRIBUTE10
, SL.PRICING_ATTRIBUTE11
, SL.PRICING_ATTRIBUTE12
, SL.PRICING_ATTRIBUTE13
, SL.PRICING_ATTRIBUTE14
, SL.PRICING_ATTRIBUTE15
, SL.PRICING_CONTEXT
, SL.ATTRIBUTE1
, SL.ATTRIBUTE2
, SL.ATTRIBUTE3
, SL.ATTRIBUTE4
, SL.ATTRIBUTE5
, SL.ATTRIBUTE6
, SL.ATTRIBUTE7
, SL.ATTRIBUTE8
, SL.ATTRIBUTE9
, SL.ATTRIBUTE10
, SL.ATTRIBUTE11
, SL.ATTRIBUTE12
, SL.ATTRIBUTE13
, SL.ATTRIBUTE14
, SL.ATTRIBUTE15
, SL.CONTEXT
, SL.INVENTORY_ITEM_ID
, MSI.DESCRIPTION
, SL.RETURN_REFERENCE_TYPE_CODE
, SL.RETURN_REFERENCE_ID
, SL.DATE_REQUESTED_CURRENT
, SH.ORDER_TYPE_ID
, SH.ORDER_NUMBER
, SH.CURRENCY_CODE