OKS Service Contract Forecast Management

select
              kh.id                          contract_id,
              ct.object1_id1           sr_salesrep_id,
              kh.currency_code      currency_code,
              pr2.object1_id1          hz_party_id,
              kh.contract_number    contract_number,
              kh.contract_number_modifier   contract_number_modifier,
              ks.ste_code      status_type,
              kh.sts_code      status_code,
              kr.last_update_date    last_update_date,
              kh.start_date                start_date,
              kh.end_date                 end_date,
              kr.Est_Rev_Date  forecast_date,
              kr.Est_Rev_Percent forecast_per,
             kh.authoring_org_id org_id,
                OKS_OKSSCOFM_XMLP_PKG.cf_salesrep_nameformula(ct.object1_id1, kh.authoring_org_id) CF_salesrep_name,
                OKS_OKSSCOFM_XMLP_PKG.cf_party_nameformula(pr2.object1_id1) CF_party_name,
                OKS_OKSSCOFM_XMLP_PKG.cf_contract_valueformula(kh.id) CF_CONTRACT_VALUE,
                OKS_OKSSCOFM_XMLP_PKG.cf_rev_recformula(kh.id, OKS_OKSSCOFM_XMLP_PKG.cf_contract_valueformula(kh.id)) CF_REV_REC,
                OKS_OKSSCOFM_XMLP_PKG.cf_forecast_revformula(OKS_OKSSCOFM_XMLP_PKG.cf_contract_valueformula(kh.id), kr.Est_Rev_Percent, OKS_OKSSCOFM_XMLP_PKG.cf_rev_recformula(kh.id, OKS_OKSSCOFM_XMLP_PKG.cf_contract_valueformula(kh.id))) CF_forecast_rev,
                OKS_OKSSCOFM_XMLP_PKG.cf_contract_forecastformula(OKS_OKSSCOFM_XMLP_PKG.cf_contract_valueformula(kh.id), kr.Est_Rev_Percent) CF_CONTRACT_FORECAST
            from
              oks_k_headers_b  kr,
              okc_contacts     ct,
              okc_statuses_b     ks,
              okc_k_party_roles_b  pr2,
              okc_k_party_roles_b  pr,
              okc_k_headers_b   kh
            where
                   kh.scs_code  IN ('SERVICE','WARRANTY')
            and  kh.id = pr.dnz_chr_id
            and  pr.cle_id is null
            and  kh.id=pr.chr_id
            and  ct.cpl_id  = pr.id
            and  pr.rle_code = 'VENDOR'
            and  ct.cro_code  IN ( 'SALESPERSON', 'SUP_SALES')
            and  ct.ID  = OKS_EXTWAR_UTIL_PVT.active_salesrep(kh.ID,pr.ID, kh.authoring_org_id)
            and  kh.id = pr2.dnz_chr_id
            and  pr2.cle_id is null
            and  kh.id=pr2.chr_id
            and  pr2.rle_code  = 'CUSTOMER'
            and  ks.code  = kh.sts_code
            and kr.chr_id(+) = kh.id
            and   ks.ste_code = 'ENTERED'
            &p_where_clause
            &p_contract_group_where
            --ORDER BY 3 ASC,14 ASC , kh.estimated_amount Desc
            order by
               currency_code, org_id, CF_salesrep_name, CF_CONTRACT_VALUE desc, kh.estimated_amount Desc