ONT Lines on Credit Check Hold

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Lines on Credit Check Hold Report (XML)
Short Name: OEXOECCL_XML
DB package: ONT_OEXOECCL_XMLP_PKG
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
Parameter Name SQL text Validation
Ledger Id
 
Number
Currency Code
 
LOV Oracle
Hold Applied Date (To)
 
Date
Hold Applied Date (From)
 
Date
Item Display
 
LOV Oracle
Order Number
 
Number
Line Type
 
LOV Oracle
Order Type
 
LOV Oracle
Customer Number
 
LOV Oracle
Customer Name
 
LOV Oracle