ONT Sales Order Acknowledgement

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