ONT Outstanding Holds

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Outstanding Holds Report (XML)
Short Name: OEXOHOHS_XML
DB package: ONT_OEXOHOHS_XMLP_PKG
select org.name customer_name,
       ho.name hold_name, 
       hs.hold_until_date,
       hs.hold_comment,
       h.order_number,
       h.ordered_date order_date,
       h.transactional_curr_code currency_code,
       nvl(l.ordered_quantity,0) * nvl(l.unit_selling_price,0) amount,
        oh.header_id,
        oh.line_id,
        oh.order_hold_id,
  l.item_identifier_type,
&rp_item_flex_all_seg item_flex,
l.inventory_item_id,
l.ordered_item_id,
l.ordered_item, 
	ONT_OEXOHOHS_XMLP_PKG.c_show_amountformula(oh.header_id, :C_line_or_order) C_show_amount, 
	ONT_OEXOHOHS_XMLP_PKG.c_line_or_orderformula(oh.order_hold_id) C_line_or_order, 
	ONT_OEXOHOHS_XMLP_PKG.c_amountformula(nvl ( l.ordered_quantity , 0 ) * nvl ( l.unit_selling_price , 0 )) C_amount,
	ONT_OEXOHOHS_XMLP_PKG.C_FORMATTED_FLEX_VALUE_p C_FORMATTED_FLEX_VALUE,
	ONT_OEXOHOHS_XMLP_PKG.Item_dspFormula(l.item_identifier_type,:c_master_org,l.inventory_item_id,l.ordered_item_id,l.ordered_item,SI.ORGANIZATION_ID) Item_dsp
from oe_sold_to_orgs_v org, 
       mtl_system_items_vl si, 
       oe_order_holds_all oh,
       oe_order_lines_all l,    
       oe_order_headers h, 
       oe_hold_definitions ho, 
       oe_hold_sources_all hs 
 where &P_where_flex
   &lp_customer_where
   &lp_hold_where
   and oh.header_id = h.header_id
   and h.sold_to_org_id = org.organization_id
   and  (h.cancelled_flag is null or h.cancelled_flag = 'N')
   and  h.open_flag='Y'
   and oh.hold_source_id = hs.hold_source_id
   and hs.hold_id = ho.hold_id
   and h.header_id = l.header_id(+)
 and l.open_flag = 'Y' 
   and l.line_id = nvl(oh.line_id,l.line_id)
   and l.service_reference_line_id is null
    and l.inventory_item_id = si.inventory_item_id
   and oh.hold_release_id is null
   and nvl(si.organization_id,0) = :c_master_org
and nvl(h.org_id,0)  = nvl(:p_org_id,0)
and nvl(l.org_id,0) = nvl(:p_org_id,0)
order by org.name,ho.name,h.order_number, si.description
Parameter Name SQL text Validation
Item Flex Code
 
Ledger Id
 
Number
Mixed Precision
 
Number
Item Display
 
LOV Oracle
Item (To)
 
Item (From)
 
Hold Name (To)
 
LOV Oracle
Hold Name (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle