ONT Comprehensive Order Detail

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Comprehensive Order Detail Report (XML)
Short Name: OEXOECOD_XML
DB package: ONT_OEXOECOD_XMLP_PKG
select   distinct h.header_id header_id10,
                       h.order_number,
                       ot.name Order_Type,
                       sold_to_org.name customer_name,
                       sold_to_org.customer_number,
                       h.cust_po_number purchase_order,
                   sold_party.PERSON_LAST_NAME || DECODE(sold_party.PERSON_FIRST_NAME, NULL,
            NULL, ', '|| sold_party.PERSON_FIRST_NAME) || DECODE(sold_arl.meaning,NULL, NULL, ' ' ||sold_arl.meaning)  CONTACT,
                       h.sold_to_contact_id contact_id,
                       h.creation_date,
                       h.ordered_date order_date,
                       h.booked_date booked_date,
            sum(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)))  order_value,
            sum(decode(l.line_category_code,'RETURN',nvl(l.unit_list_price,0)*nvl(l.ordered_quantity,0)*(-1),nvl(l.unit_list_price,0)*nvl(l.ordered_quantity,0)))  order_list,
                       h.transactional_curr_code currency1,
                       h.conversion_rate,
                       h.conversion_type_code,
                      h.request_date hdr_request_date,
                       h.flow_status_code order_status,
                       h.tax_exempt_reason_code tax_exempt_reason,
                       h.tax_exempt_number tax_exempt_number,
                       ar_lk.meaning tax_type,
                     lk.meaning open_order,
                      u.user_name entered_by,
                      qp.name price_list,
                      t.name terms,
                      acc_rule.name accounting_rule,
                      h.accounting_rule_duration,
                      inv_rule.name invoicing_rule,
                      sr.name salesrep,
                      agree.name agreement,
                       party_s.party_name ship_customer,
                 loc.address1 s_address1,
                      loc.address2 s_address2,
                      loc.address3 s_address3,
                      loc.city || ' , '||loc.state || ' , '||loc.postal_code || ' , '||loc.country s_address4,
                  party_b.party_name    bill_customer,
                    billorg.address_line_1                   b_address1,
                    billorg.address_line_2                   b_address2,
                    billorg.address_line_3                   b_address3,
                    billorg.town_or_city || ', '||billorg.state || ', '||billorg.postal_code || ', '||billorg.country b_address4,
                      h.ship_to_org_id hdr_ship_site_use_id,
                      h.invoice_to_org_id,
                      loc.country,
                ONT_OEXOECOD_XMLP_PKG.contact_phone(h.sold_to_contact_id) C_contact_phone,
                ONT_OEXOECOD_XMLP_PKG.c_use_currencyformula(:C_base_currency, h.transactional_curr_code) C_USE_CURRENCY,
                ONT_OEXOECOD_XMLP_PKG.c_gl_conv_rateformula(h.transactional_curr_code, :C_base_currency, h.conversion_rate, h.ordered_date, h.conversion_type_code) C_gl_conv_rate,
                ONT_OEXOECOD_XMLP_PKG.c_fc_order_valueformula(ONT_OEXOECOD_XMLP_PKG.c_gl_conv_rateformula(h.transactional_curr_code, :C_base_currency, h.conversion_rate, h.ordered_date, h.conversion_type_code), h.header_id) C_fc_order_value,
                ONT_OEXOECOD_XMLP_PKG.c_ship_hdr_address4formula(loc.city || ' , ' || loc.state || ' , ' || loc.postal_code || ' , ' || loc.country, h.ship_to_org_id) C_ship_hdr_address4,
                ONT_OEXOECOD_XMLP_PKG.c_bill_hdr_address4formula(billorg.town_or_city || ', ' || billorg.state || ', ' || billorg.postal_code || ', ' || billorg.country, h.invoice_to_org_id) C_Bill_hdr_address4,
                ONT_OEXOECOD_XMLP_PKG.cf_initial_due_totalformula(h.header_id) CF_INITIAL_DUE_TOTAL,
                /*ONT_OEXOECOD_XMLP_PKG.cf_initial_due_balanceformula(:CF_INITIAL_DUE_TOTAL, :CS_PREPAID_AMOUNT) CF_INITIAL_DUE_BALANCE,*/
                ONT_OEXOECOD_XMLP_PKG.cf_authorized_amountformula(h.header_id) CF_AUTHORIZED_AMOUNT,
                ONT_OEXOECOD_XMLP_PKG.CP_STD_PRECISION_p CP_STD_PRECISION,
                ONT_OEXOECOD_XMLP_PKG.CP_EXT_PRECISION_p CP_EXT_PRECISION,
                ONT_OEXOECOD_XMLP_PKG.CP_MIN_ACCT_UNIT_p CP_MIN_ACCT_UNIT,
                ONT_OEXOECOD_XMLP_PKG.CP_COMMITMENT_p CP_COMMITMENT
            from oe_order_headers_all h,
                      oe_order_lines_all l,
                      oe_transaction_types_tl ot,
                oe_transaction_types_tl ol,
                      oe_sold_to_orgs_v sold_to_org,
                   hz_party_sites party_site,
                      hz_locations loc,
                      hz_cust_acct_sites_all acct_site,
                      hz_cust_site_uses_all acct_site_uses,
                      oe_invoice_to_orgs_v billorg,
                      hz_cust_account_roles    sold_roles,
                      hz_parties                sold_party,
                      hz_cust_accounts     sold_acct,
                      hz_relationships        sold_rel,
                      ar_lookups                 sold_arl,
                      qp_price_lists_v qp,
                      ra_terms_tl t,
                      ra_rules acc_rule,
                      ra_rules inv_rule,
                      hz_parties party_s,
                      hz_cust_accounts cust_acct_s,
                      hz_parties party_b,
                      hz_cust_accounts cust_acct_b,
                      ra_salesreps_all sr,
                      oe_agreements agree,
                      fnd_user u,
                      ar_lookups ar_lk,
                      oe_lookups lk
            where (h.cancelled_flag='N' or h.cancelled_flag is null )
            and l.line_type_id=ol.transaction_type_id
            and cust_acct_s.party_id = party_s.party_id(+)
            and cust_acct_s.cust_account_id(+) = acct_site.cust_account_id
            and cust_acct_b.party_id = party_b.party_id(+)
            and cust_acct_b.cust_account_id(+) = billorg.customer_id
            and h.order_type_id = ot.transaction_type_id
            and  ot.language = userenv('LANG')
            and   h.sold_to_org_id = sold_to_org.organization_id(+)
            and   h.invoice_to_org_id = billorg.organization_id(+)
            and acct_site.party_site_id = party_site.party_site_id(+)
            and   loc.location_id(+) = party_site.location_id
            and  acct_site_uses.cust_acct_site_id = acct_site.cust_acct_site_id(+)
            and  acct_site_uses.site_use_code(+) = 'SHIP_TO'
            and  h.ship_to_org_id = acct_site_uses.site_use_id(+)
            AND    h.sold_to_contact_id  = sold_roles.cust_account_role_id(+)
            AND    sold_roles.party_id    = sold_rel.party_id(+)
            AND    sold_roles.role_type(+)   = 'CONTACT'
            AND    sold_roles.cust_account_id  = sold_acct.cust_account_id(+)
            AND    nvl(sold_rel.object_id,-1)    = nvl(sold_acct.party_id,-1)
            AND    sold_rel.subject_id              = sold_party.party_id(+)
            AND    sold_arl.lookup_type(+)      = 'CONTACT_TITLE'
            AND    sold_arl.lookup_code(+)      = sold_party.person_pre_name_adjunct
            and  h.salesrep_id = sr.salesrep_id (+)
            and sr.org_id(+) = :lp_organization_id
            and  ar_lk.lookup_code(+) = h.tax_exempt_flag
            and  ar_lk.lookup_type(+) = 'TAX_CONTROL_FLAG'
            and  lk.lookup_code(+) = h.open_flag
            and  lk.lookup_type(+) = 'YES_NO'
            and  u.user_id(+) = h.created_by
            and  h.header_id = l.header_id
            and l.item_type_code != 'INCLUDED'
            and nvl(l.org_id,0) = nvl(:lp_organization_id,0)
            and nvl(h.org_id,0) = nvl(:lp_organization_id,0)
            and h.price_list_id = qp.price_list_id(+)
            and t.language(+) = userenv('LANG')
            and h.payment_term_id = t.term_id(+)
            and h.accounting_rule_id = acc_rule.rule_id(+)
            and h.invoicing_rule_id = inv_rule.rule_id(+)
            and h.agreement_id = agree.agreement_id(+)
            &C_open_orders_where
            &lp_order_num
            &lp_customer_number
            &lp_order_type
            &lp_order_date
            &lp_customer_name
            &lp_salesrep_name
            &lp_entered_by
            &lp_order_category
            &lp_line_type
            &lp_line_category
            group by h.header_id,
                       h.order_number,
                       ot.name,
                       Sold_To_org.name,
                       sold_to_org.customer_number,
                       h.cust_po_number,
             sold_party.PERSON_LAST_NAME || DECODE(sold_party.PERSON_FIRST_NAME, NULL,
            NULL, ', '|| sold_party.PERSON_FIRST_NAME) ||DECODE(sold_arl.meaning,NULL,
            NULL, ' '||sold_arl.meaning) ,
                       h.sold_to_contact_id,
                       h.creation_date,
                       h.ordered_date,
                       h.booked_date,
                       h.transactional_curr_code,
                       h.conversion_rate,
                       h.conversion_type_code,
                       h.request_date,
                       h.flow_status_code,
                       h.tax_exempt_reason_code,
                       h.tax_exempt_number,
                      ar_lk.meaning,
                       lk.meaning,
                       u.user_name,
                       qp.name,
                       t.name,
                      acc_rule.name,
                      h.accounting_rule_duration,
                       Inv_Rule.name,
                      sr.name,
                      Agree.name,
                   party_b.party_name,
                    billorg.address_line_1 ,
                    billorg.address_line_2 ,
                    billorg.address_line_3 ,
                    billorg.town_or_city || ', '||billorg.state || ', '||billorg.postal_code || ', '||billorg.country ,
                    party_s.party_name      ,
            loc.address1,
                      loc.address2 ,
                      loc.address3  ,
                      loc.city || ' , '||loc.state || ' , '||loc.postal_code || ' , '||loc.country ,
                      h.ship_to_org_id,
                      h.invoice_to_org_id,
                      loc.country
            order by  &lp_order_by
            ot.name,
            h.order_number
Parameter Name SQL text Validation
Ledger Id
 
Number
Show End Customer and Installed Base Details
 
LOV Oracle
Item Display
 
LOV Oracle
Show in Ledger Currency
 
LOV Oracle
Show Price Adjustments
 
LOV Oracle
Show Sales Credits
 
LOV Oracle
Show Open Orders Only
 
LOV Oracle
Line Category
 
LOV Oracle
Order Category
 
LOV Oracle
Entered By (To)
 
LOV Oracle
Entered By (From)
 
LOV Oracle
Salesperson Name (To)
 
LOV Oracle
Salesperson Name (From)
 
LOV Oracle
Customer Number (To)
 
LOV Oracle
Customer Number (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Order Date (To)
 
Date
Order Date (From)
 
Date
Order Number (To)
 
Number
Order Number (From)
 
Number
Line Type (To)
 
LOV Oracle
Line Type (From)
 
LOV Oracle
Order Type (To)
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Sort By
 
LOV Oracle