Contracts Query in Oracle APPS

 SELECT DISTINCT okh.contract_number

      ,okh.id contract_id

  ,okh.cust_po_number

  ,okl.bi ll_to_site_use_id 

  ,okl.ship_to_site_use_id

  ,okl.dnz_chr_id

  ,ldi.id line_id 

  ,ldi.cle_i d line_detail_id 

  ,lite.part_number service_part

  ,lite.inventory_item_id service_ite m_id

  ,lite.service_level 

  ,mld.part_number scanner_part

  ,mld.inventory_item_id scanner_item_ id

  ,mld.organization_id 

  ,ldi.start_date

  ,ldi.end_date

  ,c.serial_number

  ,c.instance_id

  ,c.inv_organization_id 

  ,hz.party_id

  ,hz.cust_account_id

  ,hcas.party_site_id

  ,hp.party_name

  ,hl.address1

  ,hl.address2 

  ,hl.address3

  ,hl.address4

  ,hl.city

  ,hl.state

  ,hl.county

  ,hl.postal_cod e

  ,hl.country

  ,pmr.service_group

  ,pmr.sr_status

  ,pmr.sr_issue

  ,pmr.problem_categor y

  ,pmr.problem_description 

  ,pmr.operating_system

  ,pmr.status_id

  ,pmr.servi ce_group_id

  ,pmr.urgency_id 

  ,pmr.rule_id

  ,pmr.pm_type

  ,(SELECT cip.party_id 

          FROM csi_i_parties cip 

         WHERE cip.instance_id = c.instance_ id 

           AND cip.relationship_type_code = 'SHIP_TO' 

           AND ROWNUM = 1) ib_party_id 

  FROM okc_k_headers_all_b okh

      ,okc_k_lines_b okl

  ,okc_k_items lni

  ,inf_item_categories_mv lite

  ,okc_k_lines_b ldi

  ,okc_k_items ild

  ,inf_item_categories_mv mld

  ,csi_item_instances c

  ,oksf_pm_rule_headers pmr

  ,okc_k_party_roles_b pr

  ,hz_cust_accounts hz

  ,hz_cust_acct_sites_all hcas

  ,hz_cust_site_uses_all hcsu

  ,hz_parties hp

  ,hz_party_sites hps

  ,hz_locations hl 

 WHERE okh.id = okl.dnz_chr_id 

   AND okh.scs_code = 'SERVICE' 

   AND ldi.lse_id = 9 

   AND okl.lse_id = 1 

   AND okh.sts_code = 'ACTIVE' 

   AND okl.sts_code = 'ACTIVE' 

   AND okl.id = ldi.cle_id 

   AND lni.cle_id = okl.id 

   AND lni.jtot_object1_code = 'OKX_SERVICE' 

   AND lite.inventory_item_id = lni.object1_id1 

   AND ldi.sts_code = 'ACTIVE'

   AND ild.cle_id = ldi.id 

   AND ild.jtot_object1_code = 'OKX_CUSTPROD' 

   AND ild.object1_id1 = TO_CHAR(c.instance_id) 

   AND c.inventory_item_id = mld.inventory_item_id 

   AND pr.dnz_chr_id(+) = okh.id 

   AND hz.party_id(+) = pr.object1_id1 

   AND pr.cle_id IS NULL

   AND pr.rle_code = 'CUSTOMER' 

   AND pr.jtot_object1_code = 'OKX_PARTY' 

   AND okl.ship_to_site_use_id = hcsu.site_use_id 

   AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id 

   AND hcas.cust_account_id = hz.cust_account_id 

   AND hz.party_id = hp.party_id 

   AND hl.location_id = hps.location_id 

   AND hps.party_site_id = hcas.party_site_id 

   AND hp.party_id = hps.party_id 

   AND okh.contract_number = NVL(pmr.contract_number,okh.contract_number)

   AND lite.inventory_item_id = pmr.service_item_id 

   AND NVL(pmr.active_flag,'N') = 'Y' 

   AND NVL (pmr.end_date, SYSDATE + 1) >= SYSDATE 

   AND okh.contract_number = '1234567890'