ONT Sales Order Acknowledgement
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Sales Order Acknowledgement
Application: Order Management
Source: Sales Order Acknowledgement (XML)
Short Name: OEXOEACK_XML
DB package: ONT_OEXOEACK_XMLP_PKG
Description: Sales Order Acknowledgement
Application: Order Management
Source: Sales Order Acknowledgement (XML)
Short Name: OEXOEACK_XML
DB package: ONT_OEXOEACK_XMLP_PKG
Run
ONT Sales Order Acknowledgement and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |