ONT Credit Order Summary

SELECT
     	h.transactional_curr_code					Currency2,
     	h.conversion_rate 						Conversion_Rate,
     	h.conversion_type_code 						Conversion_Type_Code,
     	decode (substr(upper(:p_order_by),1,1),'W',wh.name,NULL) 	Warehouse_1,
     	decode (substr(upper(:p_order_by),1,1),'R',OTYPE.name,NULL) 	Return_Type_1,
                  decode (substr(upper(:p_order_by),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID ,NULL) 	Customer_id,
	  decode (substr(upper(:p_order_by),1,1),'C',PARTY.PARTY_NAME,NULL) 	Customer_Name_1,
     	wh.name 							Warehouse_2,
     	h.order_number 							Return_Number,
     	lu.meaning 							Open,
     	OTYPE.name 							Return_Type_2,
     	h.ordered_date 							Return_Date,
PARTY.PARTY_NAME customer_name_2,
CUST_ACCT.ACCOUNT_NUMBER Customer_number,
     	l.line_id 							Line_Id,
     	l.line_number 							Line_Number,
     	l.line_number || decode(l.shipment_number, null,null,'.'||l.shipment_number) ||
            decode(l.option_number,null,null,'.'||l.option_number) 	line_shipment_option_number,
     	l.item_identifier_type,
	L.INVENTORY_ITEM_ID,
	L.ORDERED_ITEM_ID,
	L.ORDERED_ITEM,
	&RP_ITEM_FLEX_ALL_SEG ITEM_FLEX,
      to_char(l.request_date,'DD-MON-YY') 							Expected_Receipt_Date,
      nvl(l.ordered_quantity,0)   					qty_authorized,
      decode(l.line_category_code, 'RETURN',
		nvl(l.shipped_quantity,0),0) 				qty_shipped,
      nvl(l.ordered_quantity,0)  * nvl(l.unit_selling_price,0) 		authorized_amount,
      decode(l.line_category_code, 'ORDER',0,
		trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id))
		- trunc(l.request_date)) 				Receipt_Days,
      decode(l.line_category_code,'ORDER',0,
		trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id))
		- trunc(h.ordered_date)) 				Return_Days,
      lu2.meaning 							Line_Open,
      LTYPE.name 								Line_Type,
      l.item_revision 							item_revision,
	ONT_OEXOEORS_XMLP_PKG.c_data_not_foundformula(h.transactional_curr_code) C_DATA_NOT_FOUND,
	ONT_OEXOEORS_XMLP_PKG.c_order_countformula() C_ORDER_COUNT,
   ONT_OEXOEORS_XMLP_PKG.Item_dspFormula (l.item_identifier_type , l.inventory_item_id, l.ordered_item_id,l.ordered_item,SI.ORGANIZATION_ID, l.inventory_item_id) Item_dsp,
	ONT_OEXOEORS_XMLP_PKG.rp_dummy_item_p rp_dummy_item,
	ONT_OEXOEORS_XMLP_PKG.c_actual_receipt_days(nvl ( l.ordered_quantity , 0 ), decode ( l.line_category_code , 'ORDER' , 0 , trunc ( om_reports_common_pkg.oexoeors_get_workflow_date ( l.line_id ) ) - trunc ( l.request_date ) )) C_ACTUAL_RECEIPT_DAYS,
	ONT_OEXOEORS_XMLP_PKG.c_actual_return_days(nvl ( l.ordered_quantity , 0 ), decode ( l.line_category_code , 'ORDER' , 0 , trunc ( om_reports_common_pkg.oexoeors_get_workflow_date ( l.line_id ) ) - trunc ( h.ordered_date ) )) C_ACTUAL_RETURN_DAYS,
	ONT_OEXOEORS_XMLP_PKG.c_authorized_amount_p(h.transactional_curr_code,nvl(l.ordered_quantity,0)  * nvl(l.unit_selling_price,0),h.conversion_type_code,h.ordered_date,h.conversion_rate) c_authorized_amount
FROM
	oe_order_headers_all h,
     	oe_order_lines_all l,
      	mtl_system_items_vl si,
     	oe_lookups lu,
     	oe_lookups lu2,
	HZ_PARTIES PARTY,
	HZ_CUST_ACCOUNTS CUST_ACCT,
        	oe_ship_from_orgs_v wh,
	OE_TRANSACTION_TYPES_TL OTYPE,
	OE_TRANSACTION_TYPES_TL LTYPE
WHERE
	    l.header_id = h.header_id
	AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
  	AND l.item_type_code(+) != 'INCLUDED'
  	AND h.booked_flag = 'Y'
  	AND l.booked_flag = 'Y'
  	AND nvl(h.cancelled_flag,'N') = 'N'
  	AND l.inventory_item_id = si.inventory_item_id
	AND nvl(si.organization_id,0) = :c_master_org
	AND H.ORDER_TYPE_ID = OTYPE.TRANSACTION_TYPE_ID
	AND L.LINE_TYPE_ID = LTYPE.TRANSACTION_TYPE_ID
	AND h.sold_to_org_id = CUST_ACCT.CUST_ACCOUNT_ID(+)
  	AND l.ship_from_org_id = wh.organization_id(+)
  	AND lu.lookup_type='YES_NO'
  	AND lu.lookup_code = nvl(h.open_flag,'N')
  	AND lu2.lookup_type = 'YES_NO'
  	AND lu2.lookup_code = nvl(l.open_flag,'N')
  	AND nvl(h.org_id,0) = nvl(:P_ORGANIZATION_ID_V,0)
  	AND nvl(l.org_id,0) =  nvl(:P_ORGANIZATION_ID_V,0)
	AND OTYPE.LANGUAGE = userenv('LANG')
	AND LTYPE.LANGUAGE = userenv('LANG')
&lp_customer_name
&lp_customer_number
&lp_return_type
&lp_return_line_type
&lp_line_category
&lp_warehouse
&lp_return_num
&lp_return_date
&lp_exp_rec_date
&lp_open_returns_only
&lp_return_days
&lp_rec_days
ORDER BY 1 ASC,6 ASC,7 ASC,4 ASC,5 ASC,13 ASC,9 ASC,16 ASC,15 ASC,31 ASC,17 ASC,19 ASC,20 ASC,21 ASC,18 ASC , h.transactional_curr_code , PARTY.PARTY_NAME , h.order_number , l.line_number