QUERY TO GET THE HEADER LEVEL FEILDS OF SERVICE CONTRACTS FORM

SELECT chrb.id "CHRB_ID" ,
      chrb.contract_number "CONTRACT_NUMBER" ,
      CHRB.CONTRACT_NUMBER_MODIFIER "LS_MODIFIER_NUMBER" ,
      CHRB.STS_CODE "LS_STS_CODE" ,
      CHRB.START_DATE "START_DATE" ,
      CHRB.END_DATE "END_DATE" ,
      CHRT.SHORT_DESCRIPTION "SHORT_DESCRIPTION" ,
      CHRB.ESTIMATED_AMOUNT "ESTIMATED_AMOUNT" ,
      CHRT.COGNOMEN "KNOWN_AS" ,
      CHRB.AUTHORING_ORG_ID "AUTHORING_ORG_ID" ,
      CHRB.CURRENCY_CODE "CURRENCY_CODE" ,
      RR1.name "ACCT_RULE_CODE" ,
      CHRB.CUST_PO_NUMBER "CUST_PO_NUMBER" ,
      CHRB.CUST_PO_NUMBER_REQ_YN "CUST_PO_NUMBER_REQ_YN" ,
      CHRB.PAYMENT_INSTRUCTION_TYPE "PAYMENT_INSTRUCTION_TYPE" ,
      KHR.SERVICE_PO_NUMBER "PO_NUMBER" ,
      NVL(KHR.AR_INTERFACE_YN,'Y') "AR_INT_FLAG" ,
      CHRB.BILL_TO_SITE_USE_ID "BILL_TO_SITE_USE_ID" ,
      CHRB.SHIP_TO_SITE_USE_ID "SHIP_TO_SITE_USE_ID" ,
      'HOLX_UK2_OU' "LS_OPERATING_UNIT" ,
      (SELECT PRICE_LIST_ID
      FROM OE_PRICE_LISTS
      WHERE UPPER(name) = 'UK2 SUBSIDIARY PRICE LIST (GBP)'--GIVE YOUR PRICE LIST NAME HERE
      ) "PRICE_LIST_ID" ,
    CHRB.INV_RULE_ID "INVOICING_RULE_ID" ,
    CHRB.PAYMENT_TERM_ID "PAYMENT_TERM_ID" ,
    OKP.PDF_ID "APPROVAL_WORKFLOW_ID" ,
    CHRB.QCL_ID "QCL_ID" ,
    CHRB.RENEWAL_TYPE_CODE "RENEWAL_TYPE_CODE" ,
    (SELECT NAME
    FROM RA_CUST_TRX_TYPES_ALL RCTA
    WHERE cust_trx_type_id=KHR.INV_TRX_TYPE
    AND rcta.org_id       ='g_uk_org_id'--GIVE YOUR ORGANIZATION NAME HERE
    ) "TRANSACTION_TYPE_CODE" ,
    KHR.INV_TRX_TYPE "TRANSACTION_TYPE_ID" ,
    (SELECT OKGT.ID
    FROM OKC_K_GRPINGS OKG ,
      OKC_K_GROUPS_TL OKGT
    WHERE OKG.INCLUDED_CHR_ID = CHRB.ID
    AND OKGT.ID               =OKG.CGP_PARENT_ID
    AND OKGT.LANGUAGE         =USERENV('LANG')
    ) "GRP_ID" ,
    chrb.scs_code "SCS_CODE" ,
    okprb.object1_id1 "HDR_PARTY_ID" ,
    (SELECT xsv.SALESREP_ID
    FROM XXHOL_SALESREPS_V xsv
    WHERE upper(ltrim(rtrim(xsv.salesrep_name))) IN
      (SELECT upper(ltrim(rtrim(xsalesrepv.salesrep_name)))
      FROM XXHOL_SALESREPS_V xsalesrepv,
        OKS_K_SALES_CREDITS B
      WHERE B.CHR_ID = CHRB.ID
      AND B.CTC_ID   = xsalesrepv.SALESREP_ID
--      AND B.CLE_ID IS NULL
      )
    AND org_id  = g_uk2_org_id
    AND rownum <= 1
    ) "SALESREP_ID"
    --;select distinct OKLB.*
  FROM okc_k_headers_all_b chrb,
    okc_k_party_roles_b okprb,
    -- OKC_K_LINES_B OKLB ,
    OKS_K_HEADERS_B KHR,
    OKC_K_HEADERS_TL CHRT ,
    OKC_K_PROCESSES OKP ,
    RA_RULES RR1 ,
    HR_OPERATING_UNITS HOU,
    HZ_CUST_SITE_USES_ALL HCSUA,
    HZ_CUST_SITE_USES_ALL HCSUAS
  WHERE 1 =1
    --AND chrb.ID             = OKLB.CHR_ID
  AND CHRB.ID         = CHRT.ID
  AND CHRB.ID         = KHR.CHR_ID
  AND OKP.CHR_ID      =CHRB.id
  AND RR1.RULE_ID     =KHR.ACCT_RULE_ID
  AND okprb.dnz_chr_id=chrb.id
  AND OKPRB.rle_code  ='CUSTOMER'
  AND CHRB.STS_CODE  IN ('QA_HOLD', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
  AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(CHRB.START_DATE,SYSDATE)) AND TRUNC(NVL( CHRB.END_DATE, SYSDATE))
  AND TRUNC(NVL(CHRB.DATE_TERMINATED, SYSDATE))    >= TRUNC(SYSDATE)
  AND TRUNC(NVL(CHRB.DATETIME_CANCELLED, SYSDATE)) >= TRUNC(SYSDATE)
  AND CHRT.LANGUAGE                                 =USERENV('LANG')
  AND ( HCSUA.SITE_USE_ID                           = CHRB.BILL_TO_SITE_USE_ID
  AND hcsua.site_use_code                           ='BILL_TO' )
  AND ( HCSUAS.SITE_USE_ID                          = CHRB.SHIP_TO_SITE_USE_ID
  AND hcsuas.site_use_code                          ='SHIP_TO' )
  AND HCSUA.ORG_ID                                  = HOU.ORGANIZATION_ID
--  AND HOU.ORGANIZATION_ID                           = 'g_org_id'--GIVE YOUR ORGANIZAITON NAME HERE
  AND chrb.org_id          =hou.organization_id
  AND (chrb.CONTRACT_NUMBER = 'p_contract_number');--GIVE YOUR SERVICE CONTRACT NUMBER IN PLACE OF p_contract_number