ECC Order Management, Return Lines

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: ont-rlines
Query Procedure: OE_ECC_UTIL_PVT.GET_ECC_RMA_DATA_LOAD_INFO
Security Procedure: oe_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC Order Management, Return Lines and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM (SELECT
		                                                    ECC_SPEC_ID,
		                                                    oel.ECC_LAST_UPDATE_DATE,oel.ORDER_NUMBER,oel.LINE_NUMBER,oel.LINE_ID,oel.HEADER_ID,oel.PRODUCT,oel.LINE_QUANTITY,
		                                                    oel.UNIT_SELLING_PRICE,oel.ORDER_UOM,oel.PRODUCT_DESCRIPTION,oel.LINE_TYPE,oel.PRICE_LIST,oel.PRICE_LIST_ID,oel.ORDER_DATE,
		                                                    oel.SHIP_FROM_ORG_ID,oel.WAREHOUSE,oel.SOURCE_TYPE,oel.SHIPMENT_METHOD,oel.CARRIER,oel.SHIPMENT_PRIORITY,oel.SHIPPING_INSTRUCTIONS,
		                                                    oel.PACKING_INSTRUCTIONS,oel.FREIGHT_TERMS,oel.SALESPERSON,oel.CUSTOMER,oel.CUSTOMER_NUMBER,
		                                                    oel.SHIP_TO_ORG_ID, oel.FULFILLMENT_DATE,oel.FLOW_STATUS_CODE, oel.LINE_STATUS,oel.ITEM_TYPE_CODE,oel.ORG_ID,oel.OPERATING_UNIT,
															oel.LINE_CATEGORY_CODE,oel.FULFILLED_FLAG,oel.LINE_TRAN_AMOUNT, oel.OPEN_FLAG,oel.TXN_CURRENCY,oel.TXN_CURRENCY_CODE,oel.FUNC_CURRENCY_CODE,
															oel.FUNC_CURRENCY,oel.LINE_AMOUNT,oel.SHIP_TO_SITE, oel.SHIP_TO_LOCATION,oel.SHIP_TO_STATE,oel.SHIP_TO_COUNTRY,
		                                                    oel.LINE_LAST_UPDATE_DATE,oel.PRIMARY_UOM,oel.INVENTORY_ITEM_ID,oel.SALES_CHANNEL,oel.ORDER_TYPE,oel.STATUS,oel.SHIPPED_QUANTITY,
		                                                    oel.FULFILLED_QUANTITY,oel.INVOICED_QUANTITY,oel.CUSTOMER_PO,oel.PAYMENT_TERM, oel.SUBINVENTORY,oel.TRANSACTIONAL_ORDER_TOTAL,
		                                                    oel.ORDER_TOTAL,oel.PARTY_ID,oel.CONVERSION_RATE,oel.CONVERSION_TYPE_CODE,oel.BOOKED_FLAG,oel.LANGUAGE_CODE,oel.HDR_LAST_UPDATE_DATE,
		                                                    oel.LINE_QUANTITY_1,oel.SHIPPED_QUANTITY_1,oel.CURRENCY,oel.CSR_USER_NAME,oel.return_reason, oel.ORDER_BOOKED_DATE,oel.ORDER_CREATION_DATE,
															oel.reference_line_id, oel.reference_header_id, oel.reference_type, oel.turn_around_time_days,
															oel.ORIG_ORDER_NUMBER, oel.ORIG_LINE_NUMBER, oel.ORIG_ORDER_DATE,
															oel.ORIG_FREIGHT_CARRIER_CODE, oel.ORIG_SHIPMENT_METHOD, oel.ORIG_SHIP_FROM_WAREHOUSE,oel.ORIG_LINE_TYPE, oel.ORIG_SALESPERSON,
															oel.total_sales, oel.monthly_sales, oel.total_sales_warehouse, oel.monthly_sales_warehouse, oel.total_sales_item, oel.monthly_sales_item,
															oel.total_sales_customer, oel.monthly_sales_customer, oel.total_sales_carrier, oel.monthly_sales_carrier,
		                                                    oel.entered_date,oel.returned_date,oel.month_returned
		                                                  FROM
		                                                    oe_ecc_rma_temp oel
		                                                  WHERE
		                                                    1 = 1
		                                                )
		                                                PIVOT (
		                                                    max(freight_terms) as freight_terms,
		                                                    max(product_description) as product_description,
		                                                    max(price_list) as price_list,
		                                                    max(line_type) as line_type,
		                                                    max(payment_term) as payment_term,
		                                                    max(line_status) as line_status,
		                                                    max(operating_unit) as operating_unit,
		                                                    max(sales_channel) as sales_channel,
		                                                    max(SHIPMENT_METHOD) as SHIPMENT_METHOD,
		                                                    max(order_type) as order_type,
		                                                    max(SOURCE_TYPE) as SOURCE_TYPE,
		                                                    max(TXN_CURRENCY) as TXN_CURRENCY,
		                                                    max(func_currency) as func_currency,
		                                                    max(ship_to_country) as ship_to_country,
		                                                    max(STATUS) as STATUS,
		                                                    MAX (return_reason ) AS return_reason,
		                                                    max(warehouse) as warehouse,
	                                                        max(SALESPERSON) as SALESPERSON,
	                                                        max(ORIG_SALESPERSON) as ORIG_SALESPERSON,
															max(ORIG_LINE_TYPE) as ORIG_LINE_TYPE,
															max(ORIG_FREIGHT_CARRIER_CODE) as ORIG_FREIGHT_CARRIER_CODE,
		                                                    max(ORIG_SHIPMENT_METHOD) as ORIG_SHIPMENT_METHOD,
		                                                    max(ORIG_SHIP_FROM_WAREHOUSE) as ORIG_SHIP_FROM_WAREHOUSE
		                                                    for language_code in ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV