OKS Service Contract Forecast Management

Description
Categories: BI Publisher, Service
Application: Service Contracts
Source: Service Contract Forecast Management Report (XML)
Short Name: OKSSCOFM_XML
DB package: OKS_OKSSCOFM_XMLP_PKG
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
Parameter Name SQL text Validation
Revenue Recognized Date
 
Date
Forecast End Period
 
Date
Forecast Start Period
 
Date
Maximum Contract Value
 
Number
Minimum Contract Value
 
Number
Currency Code
 
LOV Oracle
Status Code
 
LOV Oracle
Sales Rep
 
LOV Oracle
Operating Unit
 
LOV Oracle
Contract Group
 
LOV Oracle