Service Contract Query

SELECT DISTINCT

  okh.contract_number,

  okh.id contract_id,

  okh.cust_po_number,

  okl.bill_to_site_use_id ,

  okl.ship_to_site_use_id,

  okl.dnz_chr_id,

  ldi.id line_id ,

  ldi.cle_id line_detail_id ,

  lite.part_number service_part,

  lite.inventory_item_id service_item_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_code,

  hl.country,

  pmr.service_group,

  pmr.sr_status,

  pmr.sr_issue,

  pmr.problem_category,

  pmr.problem_description ,

  pmr.operating_system,

  'OTHERS' model_num,

  pmr.status_id,

  pmr.service_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 <2 )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 1=1

   AND 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 ldi.attribute9 IS NULL

   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              =  &contract_number