View: ECE_CDMO_LINE_V

Product:	EC - e-Commerce Gateway
Description:	This view extracts line informationfor the outbound Credit/Debit Memo (812/CREADV/DEBADV) transaction.@rep:scope private@rep:product AR@rep:lifecycle active@rep:displayname Credit/Debit Memo Line View@rep:category BUSINESS_ENTITY AR_CREDIT_
Implementation/DBA Data:	APPS.ECE_CDMO_LINE_V

View Text

SELECT RCT.CUSTOMER_TRX_ID TRANSACTION_ID
, RCTL1.CUSTOMER_TRX_LINE_ID TRANSACTION_LINE_ID
, RCTL1.LINE_NUMBER LINE_NUMBER
, RCTL1.SALES_ORDER SALES_ORDER_NUMBER
, RCTL1.SALES_ORDER_REVISION SALES_ORDER_REVISION_NUMBER
, RCTL1.SALES_ORDER_LINE SALES_ORDER_LINE_NUMBER
, RCTL1.SALES_ORDER_DATE SALES_ORDER_DATE
, RCTL1.SALES_ORDER_SOURCE SALES_CHANNEL
, RCTL1.INVENTORY_ITEM_ID ITEM_ID
, NULL CUSTOMER_ITEM_NUMBER
, NULL CUSTOMER_ITEM_DESC
, RCTL1.DESCRIPTION ITEM_DESCRIPTION
, RCTL1.UOM_CODE UOM_CODE
, NVL(RCTL1.QUANTITY_ORDERED
, RCTL1.QUANTITY_INVOICED) ORDERED_QUANTITY
, NVL(RCTL1.QUANTITY_INVOICED
, RCTL1.QUANTITY_CREDITED) QUANTITY
, RCTL1.UNIT_STANDARD_PRICE UNIT_STANDARD_PRICE
, RCTL1.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
, NVL(RCTL1.EXTENDED_AMOUNT
, 0) LINE_AMOUNT
, RCTL1.REASON_CODE CREDIT_MEMO_REASON
, RCTL2.LINE_NUMBER CREDITED_LINE_NUMBER
, NULL SHIP_ORDER_STATUS_INT
, NULL TRANSACTION_REFERENCE_KEY
, RCTL1.INTERFACE_LINE_CONTEXT INTERFACE_LINE_CATEGORY
, RCTL1.INTERFACE_LINE_ATTRIBUTE1 INTERFACE_LINE_ATTRIBUTE1
, RCTL1.INTERFACE_LINE_ATTRIBUTE2 INTERFACE_LINE_ATTRIBUTE2
, RCTL1.INTERFACE_LINE_ATTRIBUTE3 INTERFACE_LINE_ATTRIBUTE3
, RCTL1.INTERFACE_LINE_ATTRIBUTE4 INTERFACE_LINE_ATTRIBUTE4
, RCTL1.INTERFACE_LINE_ATTRIBUTE5 INTERFACE_LINE_ATTRIBUTE5
, RCTL1.INTERFACE_LINE_ATTRIBUTE6 INTERFACE_LINE_ATTRIBUTE6
, RCTL1.INTERFACE_LINE_ATTRIBUTE7 INTERFACE_LINE_ATTRIBUTE7
, RCTL1.INTERFACE_LINE_ATTRIBUTE8 INTERFACE_LINE_ATTRIBUTE8
, RCTL1.INTERFACE_LINE_ATTRIBUTE9 INTERFACE_LINE_ATTRIBUTE9
, RCTL1.INTERFACE_LINE_ATTRIBUTE10 INTERFACE_LINE_ATTRIBUTE10
, RCTL1.INTERFACE_LINE_ATTRIBUTE11 INTERFACE_LINE_ATTRIBUTE11
, RCTL1.INTERFACE_LINE_ATTRIBUTE12 INTERFACE_LINE_ATTRIBUTE12
, RCTL1.INTERFACE_LINE_ATTRIBUTE13 INTERFACE_LINE_ATTRIBUTE13
, RCTL1.INTERFACE_LINE_ATTRIBUTE14 INTERFACE_LINE_ATTRIBUTE14
, RCTL1.INTERFACE_LINE_ATTRIBUTE15 INTERFACE_LINE_ATTRIBUTE15
, RCTL1.ATTRIBUTE_CATEGORY LINE_ATTRIBUTE_CATEGORY
, RCTL1.ATTRIBUTE1 LINE_ATTRIBUTE1
, RCTL1.ATTRIBUTE2 LINE_ATTRIBUTE2
, RCTL1.ATTRIBUTE3 LINE_ATTRIBUTE3
, RCTL1.ATTRIBUTE4 LINE_ATTRIBUTE4
, RCTL1.ATTRIBUTE5 LINE_ATTRIBUTE5
, RCTL1.ATTRIBUTE6 LINE_ATTRIBUTE6
, RCTL1.ATTRIBUTE7 LINE_ATTRIBUTE7
, RCTL1.ATTRIBUTE8 LINE_ATTRIBUTE8
, RCTL1.ATTRIBUTE9 LINE_ATTRIBUTE9
, RCTL1.ATTRIBUTE10 LINE_ATTRIBUTE10
, RCTL1.ATTRIBUTE11 LINE_ATTRIBUTE11
, RCTL1.ATTRIBUTE12 LINE_ATTRIBUTE12
, RCTL1.ATTRIBUTE13 LINE_ATTRIBUTE13
, RCTL1.ATTRIBUTE14 LINE_ATTRIBUTE14
, RCTL1.ATTRIBUTE15 LINE_ATTRIBUTE15
, NULL HEADER_TP_ATTRIBUTE1
, NULL HEADER_TP_ATTRIBUTE2
, NULL HEADER_TP_ATTRIBUTE3
, NULL HEADER_TP_ATTRIBUTE4
, NULL HEADER_TP_ATTRIBUTE5
, NULL HEADER_TP_ATTRIBUTE6
, NULL HEADER_TP_ATTRIBUTE7
, NULL HEADER_TP_ATTRIBUTE8
, NULL HEADER_TP_ATTRIBUTE9
, NULL HEADER_TP_ATTRIBUTE10
, NULL HEADER_TP_ATTRIBUTE11
, NULL HEADER_TP_ATTRIBUTE12
, NULL HEADER_TP_ATTRIBUTE13
, NULL HEADER_TP_ATTRIBUTE14
, NULL HEADER_TP_ATTRIBUTE15
, NULL LINE_TP_ATTRIBUTE1
, NULL LINE_TP_ATTRIBUTE2
, NULL LINE_TP_ATTRIBUTE3
, NULL LINE_TP_ATTRIBUTE4
, NULL LINE_TP_ATTRIBUTE5
, NULL LINE_TP_ATTRIBUTE6
, NULL LINE_TP_ATTRIBUTE7
, NULL LINE_TP_ATTRIBUTE8
, NULL LINE_TP_ATTRIBUTE9
, NULL LINE_TP_ATTRIBUTE10
, NULL LINE_TP_ATTRIBUTE11
, NULL LINE_TP_ATTRIBUTE12
, NULL LINE_TP_ATTRIBUTE13
, NULL LINE_TP_ATTRIBUTE14
, NULL LINE_TP_ATTRIBUTE15
, NULL INDUSTRY_ATTRIBUTE1
, NULL INDUSTRY_ATTRIBUTE2
, NULL INDUSTRY_ATTRIBUTE3
, NULL INDUSTRY_ATTRIBUTE4
, NULL INDUSTRY_ATTRIBUTE5
, NULL INDUSTRY_ATTRIBUTE6
, NULL INDUSTRY_ATTRIBUTE7
, NULL INDUSTRY_ATTRIBUTE8
, NULL INDUSTRY_ATTRIBUTE9
, NULL INDUSTRY_ATTRIBUTE10
, NULL INDUSTRY_ATTRIBUTE11
, NULL INDUSTRY_ATTRIBUTE12
, NULL INDUSTRY_ATTRIBUTE13
, NULL INDUSTRY_ATTRIBUTE14
, NULL INDUSTRY_ATTRIBUTE15
, NULL INDUSTRY_CONTEXT
, NULL ITEM_IDENTIFIER_TYPE
, NULL ORDERED_ITEM
, NULL PURCHASE_ORDER_LINE_NUMBER
, NULL PURCHASE_ORDER_NUMBER
, NULL PURCHASE_ORDER_RELEASE_NUMBER
, NULL CUST_MODEL_SERIAL_NUMBER
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, 'L' INVOICE_LINE_TYPE
, TO_NUMBER(NULL) INVOICE_LINE_REFERENCE
, NULL BILL_OF_LADING_NUMBER
, TO_NUMBER(NULL) GROSS_WEIGHT
, TO_NUMBER(NULL) NET_WEIGHT
, NULL WEIGHT_UOM_CODE_INT
, TO_NUMBER(NULL) VOLUME
, NULL VOLUME_UOM_CODE_INT
, TO_NUMBER(NULL) CUSTOMER_ITEM_ID
, NULL INVOICED_CUSTOMER_ITEM_ID
, TO_NUMBER(NULL) ORDERED_ITEM_ID
, NULL BOOKING_NUMBER
, NULL PACKING_SLIP_NUMBER
, TO_NUMBER(NULL) DELIVERY_ID
, TO_NUMBER(NULL) SHIPMENT_NUMBER
, NULL WAREHOUSE_NAME
, NULL WAREHOUSE_ADDRESS1
, NULL WAREHOUSE_ADDRESS2
, NULL WAREHOUSE_ADDRESS3
, NULL WAREHOUSE_CITY
, NULL WAREHOUSE_POSTAL_CODE
, NULL WAREHOUSE_COUNTRY
, NULL WAREHOUSE_REGION_1
, NULL WAREHOUSE_REGION_2
, NULL WAREHOUSE_REGION_3
, NULL FOB_LOCATION_CODE
, NULL FREIGHT_TERMS_CODE
, NULL ECE_TP_LOCATION_CODE
, NULL WAREHOUSE_CODE
, TO_NUMBER(NULL) SHIPPING_QUANTITY
, NULL SHIPPING_QUANTITY_UOM
, TO_NUMBER(NULL) SHIPPING_QUANTITY2
, TO_NUMBER(NULL) SHIPPED_QUANTITY2
, NULL ORDERED_QUANTITY_UOM2
FROM RA_CUSTOMER_TRX RCT
, RA_CUSTOMER_TRX_LINES RCTL2
, RA_CUSTOMER_TRX_LINES RCTL1
WHERE RCTL1.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL1.PREVIOUS_CUSTOMER_TRX_LINE_ID = RCTL2.CUSTOMER_TRX_LINE_ID (+)
AND RCTL1.LINE_TYPE != 'FREIGHT'
AND RCTL1.LINE_TYPE != 'TAX'
AND ((NVL(RCTL1.INTERFACE_LINE_CONTEXT
, 'X') <> FND_PROFILE.VALUE('ONT_SOURCE_CODE')) OR (NVL(LTRIM(RCTL1.INTERFACE_LINE_ATTRIBUTE6)
, 'X') = 'X')) UNION ALL SELECT RCT.CUSTOMER_TRX_ID TRANSACTION_ID
, RCTL1.CUSTOMER_TRX_LINE_ID TRANSACTION_LINE_ID
, RCTL1.LINE_NUMBER LINE_NUMBER
, RCTL1.SALES_ORDER SALES_ORDER_NUMBER
, RCTL1.SALES_ORDER_REVISION SALES_ORDER_REVISION_NUMBER
, RCTL1.SALES_ORDER_LINE SALES_ORDER_LINE_NUMBER
, RCTL1.SALES_ORDER_DATE SALES_ORDER_DATE
, RCTL1.SALES_ORDER_SOURCE SALES_CHANNEL
, RCTL1.INVENTORY_ITEM_ID ITEM_ID
, MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_ITEM_NUMBER
, MCI.CUSTOMER_ITEM_DESC CUSTOMER_ITEM_DESC
, RCTL1.DESCRIPTION ITEM_DESCRIPTION
, RCTL1.UOM_CODE UOM_CODE
, NVL(RCTL1.QUANTITY_ORDERED
, RCTL1.QUANTITY_INVOICED) ORDERED_QUANTITY
, NVL(RCTL1.QUANTITY_INVOICED
, RCTL1.QUANTITY_CREDITED) QUANTITY
, RCTL1.UNIT_STANDARD_PRICE UNIT_STANDARD_PRICE
, RCTL1.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
, NVL(RCTL1.EXTENDED_AMOUNT
, 0) LINE_AMOUNT
, RCTL1.REASON_CODE CREDIT_MEMO_REASON
, RCTL2.LINE_NUMBER CREDITED_LINE_NUMBER
, SOL.FLOW_STATUS_CODE SHIP_ORDER_STATUS_INT
, NULL TRANSACTION_REFERENCE_KEY
, RCTL1.INTERFACE_LINE_CONTEXT INTERFACE_LINE_CATEGORY
, RCTL1.INTERFACE_LINE_ATTRIBUTE1 INTERFACE_LINE_ATTRIBUTE1
, RCTL1.INTERFACE_LINE_ATTRIBUTE2 INTERFACE_LINE_ATTRIBUTE2
, RCTL1.INTERFACE_LINE_ATTRIBUTE3 INTERFACE_LINE_ATTRIBUTE3
, RCTL1.INTERFACE_LINE_ATTRIBUTE4 INTERFACE_LINE_ATTRIBUTE4
, RCTL1.INTERFACE_LINE_ATTRIBUTE5 INTERFACE_LINE_ATTRIBUTE5
, RCTL1.INTERFACE_LINE_ATTRIBUTE6 INTERFACE_LINE_ATTRIBUTE6
, RCTL1.INTERFACE_LINE_ATTRIBUTE7 INTERFACE_LINE_ATTRIBUTE7
, RCTL1.INTERFACE_LINE_ATTRIBUTE8 INTERFACE_LINE_ATTRIBUTE8
, RCTL1.INTERFACE_LINE_ATTRIBUTE9 INTERFACE_LINE_ATTRIBUTE9
, RCTL1.INTERFACE_LINE_ATTRIBUTE10 INTERFACE_LINE_ATTRIBUTE10
, RCTL1.INTERFACE_LINE_ATTRIBUTE11 INTERFACE_LINE_ATTRIBUTE11
, RCTL1.INTERFACE_LINE_ATTRIBUTE12 INTERFACE_LINE_ATTRIBUTE12
, RCTL1.INTERFACE_LINE_ATTRIBUTE13 INTERFACE_LINE_ATTRIBUTE13
, RCTL1.INTERFACE_LINE_ATTRIBUTE14 INTERFACE_LINE_ATTRIBUTE14
, RCTL1.INTERFACE_LINE_ATTRIBUTE15 INTERFACE_LINE_ATTRIBUTE15
, RCTL1.ATTRIBUTE_CATEGORY LINE_ATTRIBUTE_CATEGORY
, RCTL1.ATTRIBUTE1 LINE_ATTRIBUTE1
, RCTL1.ATTRIBUTE2 LINE_ATTRIBUTE2
, RCTL1.ATTRIBUTE3 LINE_ATTRIBUTE3
, RCTL1.ATTRIBUTE4 LINE_ATTRIBUTE4
, RCTL1.ATTRIBUTE5 LINE_ATTRIBUTE5
, RCTL1.ATTRIBUTE6 LINE_ATTRIBUTE6
, RCTL1.ATTRIBUTE7 LINE_ATTRIBUTE7
, RCTL1.ATTRIBUTE8 LINE_ATTRIBUTE8
, RCTL1.ATTRIBUTE9 LINE_ATTRIBUTE9
, RCTL1.ATTRIBUTE10 LINE_ATTRIBUTE10
, RCTL1.ATTRIBUTE11 LINE_ATTRIBUTE11
, RCTL1.ATTRIBUTE12 LINE_ATTRIBUTE12
, RCTL1.ATTRIBUTE13 LINE_ATTRIBUTE13
, RCTL1.ATTRIBUTE14 LINE_ATTRIBUTE14
, RCTL1.ATTRIBUTE15 LINE_ATTRIBUTE15
, OOH.TP_ATTRIBUTE1 HEADER_TP_ATTRIBUTE1
, OOH.TP_ATTRIBUTE2 HEADER_TP_ATTRIBUTE2
, OOH.TP_ATTRIBUTE3 HEADER_TP_ATTRIBUTE3
, OOH.TP_ATTRIBUTE4 HEADER_TP_ATTRIBUTE4
, OOH.TP_ATTRIBUTE5 HEADER_TP_ATTRIBUTE5
, OOH.TP_ATTRIBUTE6 HEADER_TP_ATTRIBUTE6
, OOH.TP_ATTRIBUTE7 HEADER_TP_ATTRIBUTE7
, OOH.TP_ATTRIBUTE8 HEADER_TP_ATTRIBUTE8
, OOH.TP_ATTRIBUTE9 HEADER_TP_ATTRIBUTE9
, OOH.TP_ATTRIBUTE10 HEADER_TP_ATTRIBUTE10
, OOH.TP_ATTRIBUTE11 HEADER_TP_ATTRIBUTE11
, OOH.TP_ATTRIBUTE12 HEADER_TP_ATTRIBUTE12
, OOH.TP_ATTRIBUTE13 HEADER_TP_ATTRIBUTE13
, OOH.TP_ATTRIBUTE14 HEADER_TP_ATTRIBUTE14
, OOH.TP_ATTRIBUTE15 HEADER_TP_ATTRIBUTE15
, SOL.TP_ATTRIBUTE1 LINE_TP_ATTRIBUTE1
, SOL.TP_ATTRIBUTE2 LINE_TP_ATTRIBUTE2
, SOL.TP_ATTRIBUTE3 LINE_TP_ATTRIBUTE3
, SOL.TP_ATTRIBUTE4 LINE_TP_ATTRIBUTE4
, SOL.TP_ATTRIBUTE5 LINE_TP_ATTRIBUTE5
, SOL.TP_ATTRIBUTE6 LINE_TP_ATTRIBUTE6
, SOL.TP_ATTRIBUTE7 LINE_TP_ATTRIBUTE7
, SOL.TP_ATTRIBUTE8 LINE_TP_ATTRIBUTE8
, SOL.TP_ATTRIBUTE9 LINE_TP_ATTRIBUTE9
, SOL.TP_ATTRIBUTE10 LINE_TP_ATTRIBUTE10
, SOL.TP_ATTRIBUTE11 LINE_TP_ATTRIBUTE11
, SOL.TP_ATTRIBUTE12 LINE_TP_ATTRIBUTE12
, SOL.TP_ATTRIBUTE13 LINE_TP_ATTRIBUTE13
, SOL.TP_ATTRIBUTE14 LINE_TP_ATTRIBUTE14
, SOL.TP_ATTRIBUTE15 LINE_TP_ATTRIBUTE15
, SOL.INDUSTRY_ATTRIBUTE1 INDUSTRY_ATTRIBUTE1
, SOL.INDUSTRY_ATTRIBUTE2 INDUSTRY_ATTRIBUTE2
, SOL.INDUSTRY_ATTRIBUTE3 INDUSTRY_ATTRIBUTE3
, SOL.INDUSTRY_ATTRIBUTE4 INDUSTRY_ATTRIBUTE4
, SOL.INDUSTRY_ATTRIBUTE5 INDUSTRY_ATTRIBUTE5
, SOL.INDUSTRY_ATTRIBUTE6 INDUSTRY_ATTRIBUTE6
, SOL.INDUSTRY_ATTRIBUTE7 INDUSTRY_ATTRIBUTE7
, SOL.INDUSTRY_ATTRIBUTE8 INDUSTRY_ATTRIBUTE8
, SOL.INDUSTRY_ATTRIBUTE9 INDUSTRY_ATTRIBUTE9
, SOL.INDUSTRY_ATTRIBUTE10 INDUSTRY_ATTRIBUTE10
, SOL.INDUSTRY_ATTRIBUTE11 INDUSTRY_ATTRIBUTE11
, SOL.INDUSTRY_ATTRIBUTE12 INDUSTRY_ATTRIBUTE12
, SOL.INDUSTRY_ATTRIBUTE13 INDUSTRY_ATTRIBUTE13
, SOL.INDUSTRY_ATTRIBUTE14 INDUSTRY_ATTRIBUTE14
, SOL.INDUSTRY_ATTRIBUTE15 INDUSTRY_ATTRIBUTE15
, SOL.INDUSTRY_CONTEXT INDUSTRY_CONTEXT
, SOL.ITEM_IDENTIFIER_TYPE ITEM_IDENTIFIER_TYPE
, SOL.ORDERED_ITEM ORDERED_ITEM
, SOL.CUSTOMER_LINE_NUMBER PURCHASE_ORDER_LINE_NUMBER
, RCT.PURCHASE_ORDER PURCHASE_ORDER_NUMBER
, NULL PURCHASE_ORDER_RELEASE_NUMBER
, SOL.CUST_MODEL_SERIAL_NUMBER CUST_MODEL_SERIAL_NUMBER
, SOL.SHIPPED_QUANTITY SHIPPED_QUANTITY
, DECODE(NVL(RCTL1.INTERFACE_LINE_ATTRIBUTE11
, 0)
, 0
, 'L'
, 'D') INVOICE_LINE_TYPE
, DECODE(RCTL1.INTERFACE_LINE_ATTRIBUTE11
, 0
, 0
, RCTL3.LINE_NUMBER) INVOICE_LINE_REFERENCE
, RCTL1.INTERFACE_LINE_ATTRIBUTE8 BILL_OF_LADING_NUMBER
, NVL(WND.GROSS_WEIGHT
, 0) GROSS_WEIGHT
, NVL(WND.NET_WEIGHT
, 0) NET_WEIGHT
, WND.WEIGHT_UOM_CODE WEIGHT_UOM_CODE_INT
, NVL(WND.VOLUME
, 0) VOLUME
, WND.VOLUME_UOM_CODE VOLUME_UOM_CODE_INT
, SOL.ORDERED_ITEM_ID CUSTOMER_ITEM_ID
, NULL INVOICED_CUSTOMER_ITEM_ID
, SOL.ORDERED_ITEM_ID ORDERED_ITEM_ID
, WND.BOOKING_NUMBER BOOKING_NUMBER
, WDI1.SEQUENCE_NUMBER PACKING_SLIP_NUMBER
, WND.DELIVERY_ID DELIVERY_ID
, SOL.SHIPMENT_NUMBER SHIPMENT_NUMBER
, HOUTL.NAME WAREHOUSE_NAME
, HRL.ADDRESS_LINE_1 WAREHOUSE_ADDRESS1
, HRL.ADDRESS_LINE_2 WAREHOUSE_ADDRESS2
, HRL.ADDRESS_LINE_3 WAREHOUSE_ADDRESS3
, HRL.TOWN_OR_CITY WAREHOUSE_CITY
, HRL.POSTAL_CODE WAREHOUSE_POSTAL_CODE
, HRL.COUNTRY WAREHOUSE_COUNTRY
, HRL.REGION_1 WAREHOUSE_REGION_1
, HRL.REGION_2 WAREHOUSE_REGION_2
, HRL.REGION_3 WAREHOUSE_REGION_3
, HRL.LOCATION_CODE FOB_LOCATION_CODE
, WND.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE
, HRL.ECE_TP_LOCATION_CODE ECE_TP_LOCATION_CODE
, MTP.ORGANIZATION_CODE WAREHOUSE_CODE
, SOL.SHIPPING_QUANTITY SHIPPING_QUANTITY
, SOL.SHIPPING_QUANTITY_UOM SHIPPING_QUANTITY_UOM
, SOL.SHIPPING_QUANTITY2 SHIPPING_QUANTITY2
, SOL.SHIPPED_QUANTITY2 SHIPPED_QUANTITY2
, SOL.ORDERED_QUANTITY_UOM2 ORDERED_QUANTITY_UOM2
FROM MTL_CUSTOMER_ITEMS MCI
, RA_CUSTOMER_TRX RCT
, RA_CUSTOMER_TRX_LINES RCTL2
, RA_CUSTOMER_TRX_LINES RCTL1
, RA_CUSTOMER_TRX_LINES RCTL3
, OE_ORDER_HEADERS OOH
, OE_ORDER_LINES SOL
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_LEGS WDL
, WSH_DOCUMENT_INSTANCES WDI
, WSH_DOCUMENT_INSTANCES WDI1
, HR_LOCATIONS_ALL HRL
, MTL_PARAMETERS MTP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS_TL HOUTL
WHERE RCTL1.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL1.PREVIOUS_CUSTOMER_TRX_LINE_ID = RCTL2.CUSTOMER_TRX_LINE_ID (+)
AND RCTL1.LINE_TYPE != 'FREIGHT'
AND RCTL1.LINE_TYPE != 'TAX'
AND RCTL3.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL1.INTERFACE_LINE_ATTRIBUTE6 = RCTL3.INTERFACE_LINE_ATTRIBUTE6
AND DECODE(NVL(RCTL3.INTERFACE_LINE_ATTRIBUTE11
, 0)
, 0
, 'L'
, 'D') = 'L'
AND ( ( RCTL1.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(SOL.LINE_ID) ) OR ( RCTL1.INTERFACE_LINE_ATTRIBUTE6 IN ( SELECT TO_CHAR(OPA.PRICE_ADJUSTMENT_ID)
FROM OE_PRICE_ADJUSTMENTS OPA WHERE OPA.LINE_ID=SOL.LINE_ID AND OPA.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND OPA.APPLIED_FLAG = 'Y' ) ) )
AND RCTL1.INTERFACE_LINE_ATTRIBUTE6 IS NOT NULL
AND RCTL1.SALES_ORDER = OOH.ORDER_NUMBER
AND SOL.ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID (+)
AND SOL.HEADER_ID = OOH.HEADER_ID
AND RCTL1.INTERFACE_LINE_ATTRIBUTE3 = WND.NAME(+)
AND WND.DELIVERY_ID = WDL.DELIVERY_ID(+)
AND WDL.DELIVERY_LEG_ID = WDI.ENTITY_ID(+)
AND WDI.ENTITY_NAME(+) = 'WSH_DELIVERY_LEGS'
AND WND.DELIVERY_ID = WDI1.ENTITY_ID(+)
AND WDI1.ENTITY_NAME(+) = 'WSH_NEW_DELIVERIES'
AND RCTL1.INTERFACE_LINE_ATTRIBUTE10 = TO_CHAR(HOU.ORGANIZATION_ID(+))
AND HOU.ORGANIZATION_ID = MTP.ORGANIZATION_ID(+)
AND HRL.LOCATION_ID(+) = HOU.LOCATION_ID
AND HOU.ORGANIZATION_ID = NVL(HRL.INVENTORY_ORGANIZATION_ID
, HOU.ORGANIZATION_ID)
AND HOU.ORGANIZATION_ID = HOUTL.ORGANIZATION_ID (+)
AND HOUTL.LANGUAGE(+) = USERENV('LANG')
AND RCTL1.INTERFACE_LINE_CONTEXT (+) = FND_PROFILE.VALUE('ONT_SOURCE_CODE')

Columns