ONT Lines on Credit Check Hold

select 	 
acct_site.cust_account_id          Customer_Id,                                 
	c.name                                       Customer_Name,
	c.customer_number                  Customer_Number,
	loc.address1                                Address1,
	loc.city                                         City,
	loc.state                                       State,
	h.transactional_curr_code        Currency1,
	h.order_number                         Order_Number,
	h.ordered_date                          Order_Date,
                   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.ordered_item ,
       --l.inventory_item_id ,
       --l.ordered_item_id ,
	   l.ordered_item ORDERED_ITEM1,
       l.inventory_item_id INVENTORY_ITEM_ID1,
       l.ordered_item_id ORDERED_ITEM_ID1,
	  &rp_item_flex_all_seg item_flex,
                   nvl(l.ordered_quantity,0) ordered_quantity,
                   nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0) extended_selling_price,
	oh.creation_date                        Date_Placed_On_Hold,
	round(sysdate - oh.creation_date)              Days_On_Hold, 
	ONT_OEXOECCL_XMLP_PKG.c_addressformula(loc.address1, loc.city, loc.state) C_ADDRESS, 
	ONT_OEXOECCL_XMLP_PKG.c_data_not_foundformula(c.name) C_DATA_NOT_FOUND, 
	ONT_OEXOECCL_XMLP_PKG.Item_DspFormula(l.item_identifier_type,l.ordered_item_id ,l.ordered_item,SI.ORGANIZATION_ID,SI.INVENTORY_ITEM_ID) item_dsp,
	ONT_OEXOECCL_XMLP_PKG.inventory_item_id_p inventory_item_id,
	ONT_OEXOECCL_XMLP_PKG.ordered_item_p ordered_item,
	ONT_OEXOECCL_XMLP_PKG.ordered_item_id_p ordered_item_id
from 	oe_hold_sources_all hs,
	oe_order_holds_all oh,
	oe_order_headers_all h,
	oe_order_lines_all l,
	oe_order_types_v ot, 
                   oe_line_types_v lt,
HZ_CUST_SITE_USES_ALL su,
	hz_cust_acct_sites_all acct_site,
	hz_locations loc,
	hz_loc_assignments loc_assign,
	hz_party_sites party_site,
                  mtl_system_items si,
	oe_sold_to_orgs_v c
where hs.hold_id = 1
and hs.hold_entity_code = 'O'
and hs.released_flag = 'N'
and hs.hold_entity_id = h.header_id
and oh.hold_source_id = hs.hold_source_id
and nvl(hs.org_id,0) = nvl(oh.org_id,0)
and nvl(oh.org_id,0) = nvl(l.org_id,0)
and nvl(l.org_id,0) = nvl(h.org_id,0)
and nvl(h.org_id,0) = nvl(:lp_org_id,0)
and oh.line_id = l.line_id
and oh.hold_release_id is null
and h.header_id = oh.header_id
and h.header_id = l.header_id
and h.order_type_id = ot.order_type_id(+)
and l.line_type_id = lt.line_type_id(+)
and nvl(h.cancelled_flag, 'N') = 'N'
and l.inventory_item_id = si.inventory_item_id 
and nvl(si.organization_id,0) =  :RP_DUMMY_ITEM
and acct_site.cust_account_id = c.customer_id(+)
and su.CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and party_site.location_id = loc.location_id
and loc.location_id = loc_assign.location_id
and nvl(acct_site.org_id, -99 ) = nvl(loc_assign.org_id, -99)
and h.invoice_to_org_id = su.site_use_id(+)
&lp_customer_name
&lp_customer_number
&lp_currency_code
&lp_order_number
&lp_order_type
&lp_line_type
&lp_date_hold_applied
GROUP BY 
                  acct_site.cust_account_id,
	c.name,
	c.customer_number,
                  loc.address1,
                  loc.city,
                  loc.state,
	h.transactional_curr_code,
	h.order_number,
	h.ordered_date,
                   l.line_number,
                   l.shipment_number,
                   l.option_number,
                   nvl(l.ordered_quantity,0),
                   l.unit_selling_price,
 	oh.creation_date,
	sysdate - oh.creation_date,
                   l.item_identifier_type,
                   l.ordered_item,
	l.inventory_item_id,
	l.ordered_item_id,
                   si.segment1,
                   si.segment2,
	si.segment3,
	si.segment4,
	si.segment5,
	si.segment6,
	si.segment7,
	si.segment8,
	si.segment9,
	si.segment10,
	si.segment11,
	si.segment12,
	si.segment13,
	si.segment14,
	si.segment15,
	si.segment16,
	si.segment17,
	si.segment18,
	si.segment19,
	si.segment20,
SI.ORGANIZATION_ID,
SI.INVENTORY_ITEM_ID
having
                  sum(nvl(l.ordered_quantity,0)) > 0
				  ORDER BY 2 
  ASC,1 ASC,7 ASC,8 ASC