VIEW: APPS.ECE_CDMO_LINE_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')