add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, January 17

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

No comments: