VIEW: APPS.ECE_INO_LINE_V

Source

View Text - Preformatted

SELECT RCT.CUSTOMER_TRX_ID Transaction_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.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, decode(sol.item_identifier_type, 'CUST', mci.customer_item_number, NULL) customer_item_number, decode(sol.item_identifier_type, 'CUST', mci.customer_item_desc, 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, 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', to_char(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 ra_customer_trx rct, ra_customer_trx_lines rctl1, ra_customer_trx_lines rctl2, ra_customer_trx_lines rctl3, oe_order_lines sol, wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_document_instances wdi, wsh_document_instances wdi1, mtl_customer_items mci, oe_order_headers ooh, 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 = sol.line_id 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 wdi.status !='CANCELLED' AND wdi1.entity_name(+) = 'WSH_NEW_DELIVERIES' AND rctl1.interface_line_attribute10 = 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') AND rctl1.interface_line_attribute1 = ooh.order_number UNION ALL SELECT rct.customer_trx_id transaction_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, decode(sol.item_identifier_type, 'CUST', mci.customer_item_number, NULL) customer_item_number, decode(sol.item_identifier_type, 'CUST', mci.customer_item_desc, 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, 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', to_char(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 ra_customer_trx rct, ra_customer_trx_lines rctl1, ra_customer_trx_lines rctl2, ra_customer_trx_lines rctl3, oe_order_lines sol, wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_document_instances wdi, wsh_document_instances wdi1, mtl_customer_items mci, oe_order_headers ooh, hr_locations_all hrl, mtl_parameters mtp, hr_all_organization_units hou, oe_price_adjustments opa, 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 = opa.price_adjustment_id AND opa.line_id = sol.line_id AND opa.list_line_type_code = 'FREIGHT_CHARGE' AND opa.applied_flag = 'Y' 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 wdi.status !='CANCELLED' AND wdi1.entity_name(+) = 'WSH_NEW_DELIVERIES' AND rctl1.interface_line_attribute10 = 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') AND rctl1.interface_line_attribute1 = ooh.order_number UNION ALL SELECT rct.customer_trx_id transaction_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, 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, 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, rct.purchase_order purchase_order_number, NULL purchase_order_release_number, NULL cust_model_serial_number, to_number(NULL) shipped_quantity, 'F' invoice_line_type, decode(rctl1.interface_line_attribute11, '0', '0', to_char(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, to_number(NULL) customer_item_id, NULL invoiced_customer_item_id, to_number(NULL) ordered_item_id, wnd.booking_number booking_number, wdi1.sequence_number packing_slip_number, wnd.delivery_id delivery_id, to_number(NULL) 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, 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 rctl1, ra_customer_trx_lines rctl2, ra_customer_trx_lines rctl3, wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_document_instances wdi, wsh_document_instances wdi1, oe_order_headers ooh, oe_price_adjustments opa, 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(rctl1.interface_line_attribute11, '0'), '0', 'L', 'D') = 'L' AND rctl1.interface_line_attribute6 = opa.price_adjustment_id AND opa.header_id = ooh.header_id AND opa.line_id IS NULL AND opa.list_line_type_code = 'FREIGHT_CHARGE' AND opa.applied_flag = 'Y' 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 wdi.status !='CANCELLED' 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') AND rctl1.interface_line_attribute1 = ooh.order_number

View Text - HTML Formatted

SELECT RCT.CUSTOMER_TRX_ID TRANSACTION_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.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
, DECODE(SOL.ITEM_IDENTIFIER_TYPE
, 'CUST'
, MCI.CUSTOMER_ITEM_NUMBER
, NULL) CUSTOMER_ITEM_NUMBER
, DECODE(SOL.ITEM_IDENTIFIER_TYPE
, 'CUST'
, MCI.CUSTOMER_ITEM_DESC
, 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
, 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'
, TO_CHAR(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 RA_CUSTOMER_TRX RCT
, RA_CUSTOMER_TRX_LINES RCTL1
, RA_CUSTOMER_TRX_LINES RCTL2
, RA_CUSTOMER_TRX_LINES RCTL3
, OE_ORDER_LINES SOL
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_LEGS WDL
, WSH_DOCUMENT_INSTANCES WDI
, WSH_DOCUMENT_INSTANCES WDI1
, MTL_CUSTOMER_ITEMS MCI
, OE_ORDER_HEADERS OOH
, 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 = SOL.LINE_ID
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 WDI.STATUS !='CANCELLED'
AND WDI1.ENTITY_NAME(+) = 'WSH_NEW_DELIVERIES'
AND RCTL1.INTERFACE_LINE_ATTRIBUTE10 = 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')
AND RCTL1.INTERFACE_LINE_ATTRIBUTE1 = OOH.ORDER_NUMBER UNION ALL SELECT RCT.CUSTOMER_TRX_ID TRANSACTION_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
, DECODE(SOL.ITEM_IDENTIFIER_TYPE
, 'CUST'
, MCI.CUSTOMER_ITEM_NUMBER
, NULL) CUSTOMER_ITEM_NUMBER
, DECODE(SOL.ITEM_IDENTIFIER_TYPE
, 'CUST'
, MCI.CUSTOMER_ITEM_DESC
, 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
, 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'
, TO_CHAR(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 RA_CUSTOMER_TRX RCT
, RA_CUSTOMER_TRX_LINES RCTL1
, RA_CUSTOMER_TRX_LINES RCTL2
, RA_CUSTOMER_TRX_LINES RCTL3
, OE_ORDER_LINES SOL
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_LEGS WDL
, WSH_DOCUMENT_INSTANCES WDI
, WSH_DOCUMENT_INSTANCES WDI1
, MTL_CUSTOMER_ITEMS MCI
, OE_ORDER_HEADERS OOH
, HR_LOCATIONS_ALL HRL
, MTL_PARAMETERS MTP
, HR_ALL_ORGANIZATION_UNITS HOU
, OE_PRICE_ADJUSTMENTS OPA
, 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 = OPA.PRICE_ADJUSTMENT_ID
AND OPA.LINE_ID = SOL.LINE_ID
AND OPA.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND OPA.APPLIED_FLAG = 'Y'
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 WDI.STATUS !='CANCELLED'
AND WDI1.ENTITY_NAME(+) = 'WSH_NEW_DELIVERIES'
AND RCTL1.INTERFACE_LINE_ATTRIBUTE10 = 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')
AND RCTL1.INTERFACE_LINE_ATTRIBUTE1 = OOH.ORDER_NUMBER UNION ALL SELECT RCT.CUSTOMER_TRX_ID TRANSACTION_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
, 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
, 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
, RCT.PURCHASE_ORDER PURCHASE_ORDER_NUMBER
, NULL PURCHASE_ORDER_RELEASE_NUMBER
, NULL CUST_MODEL_SERIAL_NUMBER
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, 'F' INVOICE_LINE_TYPE
, DECODE(RCTL1.INTERFACE_LINE_ATTRIBUTE11
, '0'
, '0'
, TO_CHAR(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
, TO_NUMBER(NULL) CUSTOMER_ITEM_ID
, NULL INVOICED_CUSTOMER_ITEM_ID
, TO_NUMBER(NULL) ORDERED_ITEM_ID
, WND.BOOKING_NUMBER BOOKING_NUMBER
, WDI1.SEQUENCE_NUMBER PACKING_SLIP_NUMBER
, WND.DELIVERY_ID DELIVERY_ID
, TO_NUMBER(NULL) 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
, 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 RCTL1
, RA_CUSTOMER_TRX_LINES RCTL2
, RA_CUSTOMER_TRX_LINES RCTL3
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_LEGS WDL
, WSH_DOCUMENT_INSTANCES WDI
, WSH_DOCUMENT_INSTANCES WDI1
, OE_ORDER_HEADERS OOH
, OE_PRICE_ADJUSTMENTS OPA
, 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(RCTL1.INTERFACE_LINE_ATTRIBUTE11
, '0')
, '0'
, 'L'
, 'D') = 'L'
AND RCTL1.INTERFACE_LINE_ATTRIBUTE6 = OPA.PRICE_ADJUSTMENT_ID
AND OPA.HEADER_ID = OOH.HEADER_ID
AND OPA.LINE_ID IS NULL
AND OPA.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND OPA.APPLIED_FLAG = 'Y'
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 WDI.STATUS !='CANCELLED'
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')
AND RCTL1.INTERFACE_LINE_ATTRIBUTE1 = OOH.ORDER_NUMBER