ONT Comprehensive Order Detail

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