ONT Sales Order Acknowledgement

Description
Categories: BI Publisher
Application: Order Management
Source: Sales Order Acknowledgement (XML)
Short Name: OEXOEACK_XML
DB package: ONT_OEXOEACK_XMLP_PKG
select  
 l.line_number 
|| decode(l.shipment_number, null, null, '.'||l.shipment_number)
|| decode(l.option_number, null, null, '.'||l.option_number) 
line_number,
l.line_number l_number,
l.shipment_number l_ship_number,
l.option_number l_option_number,
l.ordered_quantity ,
l.pricing_quantity,
l.item_identifier_type,
--&rp_item_flex_all_seg item_flex,
l.inventory_item_id,
l.ordered_item_id,
l.ordered_item,
--Removed trunc function on the below three date fields for bug 17768119
l.request_date Request_Date,
l.promise_date           Promise_Date,
l.schedule_ship_date       Schedule_Date, 
--Changes for Bug 1776 complete
nvl(&lp_unit_of_measure, uom.unit_of_measure)       Unit,        
l.unit_selling_price                 Selling_Price,
NVL(l.tax_value,0) Tax_On_Line,
ship_party.party_name||decode(ship_loc.address4,null,decode(ship_loc.address3,null,decode(ship_loc.address2,null,decode(ship_loc.address1,null,null,','||ship_loc.address1),','||ship_loc.address1||','||ship_loc.address2),','||ship_loc.address1||','||ship_loc.address2||','||ship_loc.address3),','||ship_loc.address1||','||ship_loc.address2||','||ship_loc.address3||','||ship_loc.address4)||','||ship_loc.city||decode(ship_loc.city,null,null,decode(ship_loc.state,null,null,','||ship_loc.state))||decode(ship_loc.province,null,null,','||ship_loc.province)||' '||ship_loc.postal_code  line_s_city_st_zip,
bill_party.party_name||decode(bill_loc.address4,null,decode(bill_loc.address3,null,decode(bill_loc.address2,null,decode(bill_loc.address1,null,null,','||bill_loc.address1),','||bill_loc.address1||','||bill_loc.address2),','||bill_loc.address1||','||bill_loc.address2||','||bill_loc.address3),','||bill_loc.address1||','||bill_loc.address2||','||bill_loc.address3||','||bill_loc.address4)||','||bill_loc.city||decode(bill_loc.city,null,null,decode(bill_loc.state,null,null,','||bill_loc.state))||decode(bill_loc.province,null,null,','||bill_loc.province)||' '||bill_loc.postal_code line_b_city_st_zip, 
del_party.party_name||decode(del_loc.address4,null,decode(del_loc.address3,null,decode(del_loc.address2,null,decode(del_loc.address1,null,null,','||del_loc.address1),','||del_loc.address1||','||del_loc.address2),','||del_loc.address1||','||del_loc.address2||','||del_loc.address3),','||del_loc.address1||','||del_loc.address2||','||del_loc.address3||','||del_loc.address4)||','||del_loc.city||decode(del_loc.city,null,null,decode(del_loc.state,null,null,','||del_loc.state))||decode(del_loc.province,null,null,','||del_loc.province)||' '||del_loc.postal_code line_d_city_st_zip, 
l.ship_to_org_id  line_ship_to_org_id,
l.invoice_to_org_id line_bill_to_org_id,
l.deliver_to_org_id line_del_to_org_id,
l.header_id line_header_id,
l.line_id line_id_q_lines,
To_Char(l.Line_Id)  char_line_Id,
l.line_type_id,
a.name line_agreement,
l.cust_po_number line_po,
terms.name line_term,
sr.name line_salesrep,
lk1.meaning Line_Freight_Terms,
lk2.meaning Line_FOB,
f.carrier_name Line_Carrier,
ol.name Line_Type,
l.line_category_code,
l.user_item_description,
l.charge_periodicity_code,
l.contingency_id, 
	ONT_OEXOEACK_XMLP_PKG.c_charge_periodicityformula(l.charge_periodicity_code) C_CHARGE_PERIODICITY, 
	ONT_OEXOEACK_XMLP_PKG.g_grand_totalformula(l.charge_periodicity_code, fnd_number.canonical_to_number(:C_TAX_TOTAL), fnd_number.canonical_to_number(:C_LINE_RECUR_CHARGE), fnd_number.canonical_to_number(:CF_EXTENDED_PRICE), fnd_number.canonical_to_number(:C_svc_extended_price), fnd_number.canonical_to_number(:c_header_charge_periodicity), :C_precision) C_GRAND_TOTAL, 
	ONT_OEXOEACK_XMLP_PKG.g_precisionformula(:CURRENCY) CF_PRECISION, 
	ONT_OEXOEACK_XMLP_PKG.c_header_charge_periodicityfor(l.charge_periodicity_code, fnd_number.canonical_to_number(:c_header_charge), :C_precision) c_header_charge_periodicity, 
	--&Item_dsp Item_dsp, 
        ONT_OEXOEACK_XMLP_PKG.ITEM_DSPFORMULA(l.user_item_description,l.item_identifier_type, l.inventory_item_id, :c_master_org, l.ORDERED_ITEM_ID, l.ORDERED_ITEM, si.organization_id, si.inventory_item_id) Item_dsp,
	ONT_OEXOEACK_XMLP_PKG.c_accept_requiredformula(l.contingency_id) C_ACCEPT_REQUIRED, 
	ONT_OEXOEACK_XMLP_PKG.c_line_ship_to_custformula(l.ship_to_org_id, :HDR_SHIP_TO_ORG_ID, ship_party.party_name || decode ( ship_loc.address4 , null , decode ( ship_loc.address3 , null , decode ( ship_loc.address2 , null , decode ( ship_loc.address1 , null , null , ',' || ship_loc.address1 ) , ',' || ship_loc.address1 || ',' || ship_loc.address2 ) , ',' || ship_loc.address1 || ',' || ship_loc.address2 || ',' || ship_loc.address3 ) , ',' || ship_loc.address1 || ',' || ship_loc.address2 || ',' || ship_loc.address3 || ',' || ship_loc.address4 ) || ',' || ship_loc.city || decode ( ship_loc.city , null , null , decode ( ship_loc.state , null , null , ',' || ship_loc.state ) ) || decode ( ship_loc.province , null , null , ',' || ship_loc.province ) || ' ' || ship_loc.postal_code) C_LINE_SHIP_TO_CUST, 
	ONT_OEXOEACK_XMLP_PKG.c_extended_priceformula(l.unit_selling_price, :C_precision, l.pricing_quantity, l.line_category_code, l.ordered_quantity) C_EXTENDED_PRICE, 
	ONT_OEXOEACK_XMLP_PKG.c_line_del_to_custformula(l.deliver_to_org_id, :HDR_DEL_TO_ORG_ID, del_party.party_name || decode ( del_loc.address4 , null , decode ( del_loc.address3 , null , decode ( del_loc.address2 , null , decode ( del_loc.address1 , null , null , ',' || del_loc.address1 ) , ',' || del_loc.address1 || ',' || del_loc.address2 ) , ',' || del_loc.address1 || ',' || del_loc.address2 || ',' || del_loc.address3 ) , ',' || del_loc.address1 || ',' || del_loc.address2 || ',' || del_loc.address3 || ',' || del_loc.address4 ) || ',' || del_loc.city || decode ( del_loc.city , null , null , decode ( del_loc.state , null , null , ',' || del_loc.state ) ) || decode ( del_loc.province , null , null , ',' || del_loc.province ) || ' ' || del_loc.postal_code) C_Line_Del_To_Cust, 
	ONT_OEXOEACK_XMLP_PKG.c_line_bill_to_custformula(l.invoice_to_org_id, :HDR_BILL_TO_ORG_ID, bill_party.party_name || decode ( bill_loc.address4 , null , decode ( bill_loc.address3 , null , decode ( bill_loc.address2 , null , decode ( bill_loc.address1 , null , null , ',' || bill_loc.address1 ) , ',' || bill_loc.address1 || ',' || bill_loc.address2 ) , ',' || bill_loc.address1 || ',' || bill_loc.address2 || ',' || bill_loc.address3 ) , ',' || bill_loc.address1 || ',' || bill_loc.address2 || ',' || bill_loc.address3 || ',' || bill_loc.address4 ) || ',' || bill_loc.city || decode ( bill_loc.city , null , null , decode ( bill_loc.state , null , null , ',' || bill_loc.state ) ) || decode ( bill_loc.province , null , null , ',' || bill_loc.province ) || ' ' || bill_loc.postal_code) C_Line_Bill_To_Cust, 
	ONT_OEXOEACK_XMLP_PKG.c_line_salesrepformula(sr.name, :SALES_PERSON) C_Line_Salesrep, 
	ONT_OEXOEACK_XMLP_PKG.c_line_freight_termsformula(lk1.meaning, :FREIGHT_TERMS) C_Line_Freight_Terms, 
	ONT_OEXOEACK_XMLP_PKG.c_line_carrierformula(f.carrier_name, :CARRIER) C_Line_Carrier, 
	ONT_OEXOEACK_XMLP_PKG.c_line_fobformula(lk2.meaning, :FOB) C_Line_FOB, 
	ONT_OEXOEACK_XMLP_PKG.c_line_typeformula(l.line_type_id, ol.name) C_Line_Type, 
	ONT_OEXOEACK_XMLP_PKG.c_line_poformula(l.cust_po_number, :PURCHASE_ORDER) C_Line_PO, 
	ONT_OEXOEACK_XMLP_PKG.c_line_agreementformula(a.name, :AGREEMENT) C_Line_Agreement, 
	ONT_OEXOEACK_XMLP_PKG.c_line_termsformula(terms.name, :PAYMENT_TERMS) C_Line_Terms, 
	ONT_OEXOEACK_XMLP_PKG.c_fmt_tax_on_lineformula(l.line_category_code, fnd_number.canonical_to_number(:C_total_Line_tax), :C_precision) C_fmt_Tax_On_Line, 
	ONT_OEXOEACK_XMLP_PKG.c_line_categoryformula(l.line_category_code) C_Line_Category, 
	ONT_OEXOEACK_XMLP_PKG.c_tax_on_lineformula(l.line_category_code, fnd_number.canonical_to_number(:C_total_Line_tax), :C_precision) C_tax_on_line, 
	ONT_OEXOEACK_XMLP_PKG.c_ordered_quantity(l.line_category_code, l.ordered_quantity) C_ordered_Quantity, 
	ONT_OEXOEACK_XMLP_PKG.c_total_line_taxformula(l.tax_value, fnd_number.canonical_to_number(:S_Total_svc_tax)) C_total_Line_tax,
	ONT_OEXOEACK_XMLP_PKG.RP_dummy_item_p RP_dummy_item
from oe_order_lines_all l,
     oe_line_types_v ol, 
     mtl_units_of_measure uom,
     mtl_system_items si,
  HZ_CUST_SITE_USES_ALL ship_su, 
 HZ_CUST_SITE_USES_ALL bill_su,
 HZ_CUST_SITE_USES_ALL del_su,
     wsh_carriers_v f,
     WSH_CARRIER_SHIP_METHODS_V CSM, 
     oe_agreements_v a,
     oe_lookups lk1,
     ar_lookups lk2,
     ra_salesreps sr, 
     ra_terms terms,
     hz_party_sites bill_party_site, 
     -- hz_loc_assignments bill_loc_assign, --bug 9744274
     hz_locations bill_loc, 
     hz_cust_acct_sites_all bill_acct_site,
     hz_parties bill_party,
     hz_cust_accounts bill_cust_acct,
     hz_party_sites ship_party_site, 
     -- hz_loc_assignments ship_loc_assign, --bug 9744274
     hz_locations ship_loc, 
     hz_cust_acct_sites_all ship_acct_site,
     hz_parties ship_party,
     hz_cust_accounts ship_cust_acct,
     hz_party_sites del_party_site, 
     -- hz_loc_assignments del_loc_assign, --bug 9744274
     hz_locations del_loc, 
     hz_cust_acct_sites_all del_acct_site,
     hz_parties del_party,
     hz_cust_accounts del_cust_acct
where l.line_type_id = ol.line_type_id(+)
    and (l.service_reference_line_id is null or l.service_reference_type_code <> 'ORDER' 
            or (    l.service_reference_type_code = 'ORDER' 
                and not exists (select header_id 
                                from   oe_order_lines_all 
                                where line_id = l.service_reference_line_id 
                                and header_id  = l.header_id) 
       )) 
    and l.order_quantity_uom = uom.uom_code(+)
    and l.inventory_item_id = si.inventory_item_id
    and nvl(si.organization_id,0) = :c_master_org 
    and l.ship_to_org_id = ship_su.site_use_id(+)
    and ship_su.CUST_ACCT_SITE_ID  = ship_acct_site.cust_acct_site_id(+)
    and ship_acct_site.party_site_id = ship_party_site.party_site_id(+)
    and ship_acct_site.cust_account_id = ship_cust_acct.cust_account_id(+)
    and ship_loc.location_id(+) =  ship_party_site.location_id
    -- and ship_loc.location_id =  ship_loc_assign.location_id(+) --bug 9744274
    -- and NVL ( ship_acct_site.org_id , - 99 ) = nvl ( ship_loc_assign.org_id , - 99 ) --bug 9744274
    and ship_cust_acct.party_id = ship_party.party_id
    and l.invoice_to_org_id = bill_su.site_use_id(+)
    and bill_su.CUST_ACCT_SITE_ID  = bill_acct_site.cust_acct_site_id(+)
    and bill_acct_site.party_site_id = bill_party_site.party_site_id(+)
    and bill_acct_site.cust_account_id = bill_cust_acct.cust_account_id(+)
    and bill_loc.location_id(+) =  bill_party_site.location_id
    -- and bill_loc.location_id =  bill_loc_assign.location_id(+) --bug 9744274
    -- and NVL ( bill_acct_site.org_id , - 99 ) = nvl ( bill_loc_assign.org_id , - 99 ) --bug 9744274
    and bill_cust_acct.party_id = bill_party.party_id
    and l.deliver_to_org_id = del_su.site_use_id(+)
    and del_su.CUST_ACCT_SITE_ID  = del_acct_site.cust_acct_site_id(+)
    and del_acct_site.party_site_id = del_party_site.party_site_id(+)
    and del_acct_site.cust_account_id = del_cust_acct.cust_account_id(+)
    and del_loc.location_id(+) =  del_party_site.location_id
    -- and del_loc.location_id =  del_loc_assign.location_id(+) --bug 9744274
    -- and NVL ( del_acct_site.org_id , - 99 ) = nvl ( del_loc_assign.org_id , - 99 ) --bug 9744274
    and del_cust_acct.party_id = del_party.party_id(+)
    and terms.term_id(+) = l.payment_term_id
    and sr.salesrep_id(+) = l.salesrep_id
    and a.agreement_id(+) = l.agreement_id 
    and lk1.lookup_code(+) = l.freight_terms_code
    and lk1.lookup_type(+) = 'FREIGHT_TERMS'
    and lk2.lookup_code(+) = l.fob_point_code
    and lk2.lookup_type(+) = 'FOB'              
    and CSM.ship_method_code(+) = l.shipping_method_code
    and nvl(CSM.organization_id(+),0) = :c_master_org
    and f.freight_code(+) = CSM.freight_code
&lp_line_category
&lp_schedule_date 
 and l.header_id=:header_id_orders
ORDER BY l.line_number,
l.shipment_number,
nvl(l.option_number,0),
nvl(l.component_number,0)
Parameter Name SQL text Validation
Item Display
 
LOV Oracle
Booked Status
 
LOV Oracle
Order Type
 
LOV Oracle
Order Number (From)
 
Number
Order Number ( To)
 
Number
Order Date ( From)
 
Date
Order Date (To)
 
Date
Invoice To Customer Name ( From)
 
LOV Oracle
Invoice To Customer Name (To)
 
LOV Oracle
Ship To Customer Name ( From)
 
LOV Oracle
Ship To Customer Name (To)
 
LOV Oracle
Schedule Date (From)
 
Date
Schedule Date (To)
 
Date
Request Date (From)
 
Date
Request Date (To)
 
Date
Promise Date (From)
 
Date
Promise Date (To)
 
Date
Order Category
 
LOV Oracle
Line Category
 
LOV Oracle
Salesperson
 
LOV Oracle
Created By
 
LOV Oracle
Open Orders Only
 
LOV Oracle
Deliver To Customer Name(From)
 
LOV Oracle
Deliver To Customer Name (To)
 
LOV Oracle
Show Header Attachments