ONT Unbooked Orders Detail
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Unbooked Orders Detail Report
Application: Order Management
Source: Unbooked Orders Detail Report (XML)
Short Name: OEXOEUBD_XML
DB package: ONT_OEXOEUBD_XMLP_PKG
Description: Unbooked Orders Detail Report
Application: Order Management
Source: Unbooked Orders Detail Report (XML)
Short Name: OEXOEUBD_XML
DB package: ONT_OEXOEUBD_XMLP_PKG
Run
ONT Unbooked Orders Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
select h.order_number, h.ordered_date, ot.name order_type, UPPER(REPLACE(SUBSTR(ppf_mgr.email_address, 1, DECODE(INSTR(ppf_mgr.email_address, '.'), 0, LENGTH(ppf_mgr.email_address), INSTR(ppf_mgr.email_address, '.') -1)), '@', '_')) manager, fu.user_name, l.item_identifier_type, decode (l.item_identifier_type, 'INT', &rp_item_flex_all_seg, 'CUST', ci.customer_item_number, l.ordered_item) Item, msi.description Internal_Item_Desc, DECODE(l.line_category_code,'RETURN', nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0) * -1, nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0)) line_amt , h.transactional_curr_code, h.conversion_rate Conversion_rate , h.conversion_type_code Conversion_Type_Code , substrb(bill_to_PARTY.PARTY_NAME,1,50) bill_to, substrb(sold_to_PARTY.PARTY_NAME,1,50) sold_to, h.cust_po_number, l.charge_periodicity_code, ONT_OEXOEUBD_XMLP_PKG.c_precisionformula(h.transactional_curr_code) C_precision, ONT_OEXOEUBD_XMLP_PKG.c_recur_chargesformula(l.charge_periodicity_code) C_recur_charge_periodicity, ONT_OEXOEUBD_XMLP_PKG.c_line_amtformula(h.transactional_curr_code, DECODE ( l.line_category_code , 'RETURN' , nvl ( l.unit_selling_price , 0 ) * nvl ( l.ordered_quantity , 0 ) * - 1 , nvl ( l.unit_selling_price , 0 ) * nvl ( l.ordered_quantity , 0 ) ), h.conversion_type_code, h.ordered_date, h.conversion_rate) C_line_amt --, &Item_dsp Item_Dsp from fnd_user fu, mtl_system_items_vl msi, oe_order_lines_all l, oe_order_headers_all h, oe_order_types_v ot, mtl_customer_items ci, hz_cust_site_uses_all bill_site, hz_cust_acct_sites_all acct_site, hz_party_sites party_site, -- hz_loc_assignments loc_assign, --bug 10050232 hz_locations loc, hz_parties bill_to_party, hz_cust_accounts bill_to_cust_acct , hz_parties sold_to_party, hz_cust_accounts sold_to_cust_acct , per_all_people_f ppf_mgr, per_all_assignments_f paf, per_all_people_f ppf where h.header_id = l.header_id AND bill_to_CUST_ACCT.PARTY_ID = bill_to_PARTY.PARTY_ID (+) AND sold_to_CUST_ACCT.PARTY_ID = sold_to_PARTY.PARTY_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 (+) --bug 10050232 -- AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99) --bug 10050232 and l.inventory_item_id = msi.inventory_item_id and h.order_type_id(+) = ot.order_type_id and h.created_by = fu.user_id AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE(+) , SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE(+) , SYSDATE+1) and fu.employee_id = ppf.person_id(+) and ppf.person_id = paf.person_id(+) AND SYSDATE BETWEEN NVL(PAF.EFFECTIVE_START_DATE(+) , SYSDATE) AND NVL(PAF.EFFECTIVE_END_DATE(+) , SYSDATE+1) and paf.supervisor_id = ppf_mgr.person_id(+) AND SYSDATE BETWEEN NVL(PPF_MGR.EFFECTIVE_START_DATE(+) , SYSDATE) AND NVL(PPF_MGR.EFFECTIVE_END_DATE(+) , SYSDATE+1) and nvl(l.booked_flag, 'N') ='N' and (l.cancelled_flag ='N' or l.cancelled_flag is null) and nvl(msi.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0) and l.ordered_item_id = ci.customer_item_id(+) and l.open_flag ='Y' --and nvl(l.org_id,0) = nvl(:p_organization_id,0) and nvl(l.org_id,0) = nvl(:p_organization_id_v,0) --and nvl(h.org_id,0) = nvl(:p_organization_id,0) and nvl(h.org_id,0) = nvl(:p_organization_id_v,0) and l.sold_to_org_id = sold_to_CUST_ACCT.CUST_ACCOUNT_ID(+) and bill_site.CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id(+) and acct_site.cust_account_id = bill_to_CUST_ACCT.CUST_ACCOUNT_ID(+) and l.invoice_to_org_id= bill_site.site_use_id(+) &lp_created_by &lp_order_date &lp_order_type &lp_manager UNION ALL select h.order_number, h.ordered_date, ot.name order_type, UPPER(REPLACE(SUBSTR(ppf_mgr.email_address, 1, DECODE(INSTR(ppf_mgr.email_address, '.'), 0, LENGTH(ppf_mgr.email_address), INSTR(ppf_mgr.email_address, '.') -1)), '@', '_')) manager, fu.user_name, NULL item_identifier_type, ' 'item, ' 'Internal_Item_Desc, 0 line_amt, h.transactional_curr_code, h.conversion_rate Conversion_rate , h.conversion_type_code Conversion_Type_Code , substrb(bill_to_PARTY.PARTY_NAME,1,50) bill_to, substrb(sold_to_PARTY.PARTY_NAME,1,50) sold_to, h.cust_po_number, NULL charge_periodicity_code, ONT_OEXOEUBD_XMLP_PKG.c_precisionformula(h.transactional_curr_code) C_precision, ONT_OEXOEUBD_XMLP_PKG.c_recur_chargesformula(NULL) C_recur_charge_periodicity, --ONT_OEXOEUBD_XMLP_PKG.c_line_amtformula(h.transactional_curr_code, DECODE ( l.line_category_code , 'RETURN' , nvl ( l.unit_selling_price , 0 ) * nvl ( l.ordered_quantity , 0 ) * - 1 , nvl ( l.unit_selling_price , 0 ) * nvl ( l.ordered_quantity , 0 ) ), h.conversion_type_code, h.ordered_date, h.conversion_rate) C_line_amt ONT_OEXOEUBD_XMLP_PKG.c_line_amtformula(h.transactional_curr_code, 0,h.conversion_type_code,h.ordered_date,h.conversion_rate) C_line_amt --, &Item_dsp Item_Dsp from oe_order_headers_all h, oe_order_types_v ot, hz_cust_site_uses_all bill_site, hz_party_sites party_site, -- hz_loc_assignments loc_assign, -- bug 10050232 hz_locations loc, hz_cust_acct_sites_all acct_site, hz_parties bill_to_party, hz_cust_accounts bill_to_cust_acct , hz_parties sold_to_party, hz_cust_accounts sold_to_cust_acct , per_all_people_f ppf_mgr, per_all_assignments_f paf, per_all_people_f ppf, fnd_user fu where h.created_by = fu.user_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 (+) --bug 10050232 -- AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99) --bug 10050232 AND bill_to_cust_acct.party_id = bill_to_party.party_id (+) AND sold_to_cust_acct.party_id = sold_to_party.party_id (+) and h.booked_flag ='N' and (h.cancelled_flag ='N' or h.cancelled_flag is null) and h.open_flag = 'Y' and h.order_type_id(+) = ot.order_type_id and fu.employee_id = ppf.person_id(+) AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE(+) , SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE(+) , SYSDATE+1) and ppf.person_id = paf.person_id(+) AND SYSDATE BETWEEN NVL(PAF.EFFECTIVE_START_DATE(+) , SYSDATE) AND NVL(PAF.EFFECTIVE_END_DATE(+) , SYSDATE+1) and paf.supervisor_id = ppf_mgr.person_id(+) AND SYSDATE BETWEEN NVL(PPF_MGR.EFFECTIVE_START_DATE(+) , SYSDATE) AND NVL(PPF_MGR.EFFECTIVE_END_DATE(+) , SYSDATE+1) and h.sold_to_org_id = sold_to_CUST_ACCT.CUST_ACCOUNT_ID (+) and bill_site. CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id (+) and acct_site.cust_account_id = bill_to_CUST_ACCT.CUST_ACCOUNT_ID(+) and h.invoice_to_org_id= bill_site.site_use_id(+) --and nvl(h.org_id,0) = nvl(:p_organization_id,0) and nvl(h.org_id,0) = nvl(:p_organization_id_v,0) and not exists (select 'X' from oe_order_lines_all b where h.header_id = b.header_id --and nvl(b.org_id,0) = nvl(:p_organization_id,0) ) and nvl(b.org_id,0) = nvl(:p_organization_id_v,0) ) &lp_created_by &lp_order_date &lp_order_type &lp_manager &lp_order_by |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Operating Unit |
|
LOV | |
| Sort By |
|
LOV Oracle | |
| Created By (From) |
|
LOV Oracle | |
| Created By (To) |
|
LOV Oracle | |
| Order Date (From) |
|
Date | |
| Order Date (To) |
|
Date | |
| Order Type (From) |
|
LOV Oracle | |
| Order Type (To) |
|
LOV Oracle | |
| Manager (From) |
|
LOV Oracle | |
| Manager (To) |
|
LOV Oracle | |
| Use Ledger Currency |
|
LOV Oracle |
