ONT Outstanding Holds
Description
- Categories
- BI Publisher
- Application
- Order Management
- Source
- Outstanding Holds Report (XML)
- Short Name
- OEXOHOHS_XML
- DB package
- ONT_OEXOHOHS_XMLP_PKG
Imported from BI Publisher
Outstanding Holds Report
Outstanding Holds Report
SQL 48 lines
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 |
Parameters 9
| Parameter | Type | List of values SQL |
|---|---|---|
| Operating Unit | LOV | |
| Customer Name (From) | LOV Oracle | |
| Customer Name (To) | LOV Oracle | |
| Hold Name (From) | LOV Oracle | |
| Hold Name (To) | LOV Oracle | |
| Item (From) | Char | |
| Item (To) | Char | |
| Item Display | LOV Oracle | |
| Mixed Precision | Number |