OKS Service Contracts by Status

SELECT
                 KHD.Id                                                 Contract_Id
                ,KCTS.Object1_Id1                                Rep_Name
                ,KHD.Sts_Code                                     Status_Code
                ,SUBSTR(KHD.Sts_Code,1,10)              Status_Code_SUBSTR
                ,KSTB.STE_Code                                  Status_Type
                ,SUBSTR(KSTB.STE_Code,1,10)           Status_Type_SUBSTR
                ,KSTT.Meaning                                     Status_Meaning
                ,PTYC.Party_Id                                     Customer_Id
                ,SUBSTR(PTYC.Party_Name,1,22)        Customer_Name_Substr
                ,PTYC.Party_Number                             Customer_Number
                ,KHD.Contract_Number_Modifier                Contract_Modifier
                ,SUBSTR(KHD.Contract_Number_Modifier,1,13)   Contract_Modifier_Substr
                ,SUBSTR(KHD.Contract_Number,1,20)            Contract_Number_Substr
                ,KHD.Start_Date                              Start_Date
                ,KHD.End_Date                                End_Date
                ,ROUND(OKS_Extwar_Util_Pvt.Round_Currency_Amt(KHD.Estimated_Amount,KHD.Currency_Code),0) Total_Value_Round
                ,KHD.Authoring_Org_Id                        Org_Id
                ,ORG.Name                                    Org_Name
                ,KHD.Currency_Code                           Currency_Code,
                OKS_OKSSUMCO_XMLP_PKG.cf_salesrep_nameformula(KCTS.Object1_Id1, KHD.Authoring_Org_Id) CF_salesrep_name,
                SUBSTR(KSTB.STE_Code,1,10) CF_StatusType_SUBSTR,
                SUBSTR(KHD.Sts_Code,1,10) CF_StatusCode_SUBSTR,
                OKS_OKSSUMCO_XMLP_PKG.cf_prodlinevalueformula(KHD.Id, KHD.Sts_Code, KHD.Currency_Code) CF_ProdLineValue_Round,
                OKS_OKSSUMCO_XMLP_PKG.cf_prodline_valueperdayformula(KHD.Id, KHD.Sts_Code, KHD.Currency_Code) CF_ProdLine_ValuePerDay,
                OKS_OKSSUMCO_XMLP_PKG.cf_prodline_annrateformula(OKS_OKSSUMCO_XMLP_PKG.cf_prodline_valueperdayformula(KHD.Id, KHD.Sts_Code, KHD.Currency_Code)) CF_ProdLine_AnnRate_Round,
                OKS_OKSSUMCO_XMLP_PKG.cf_reccountformula() CF_ProdLine_RecCount
            FROM
                 OKC_K_HEADERS_B KHD
                ,OKC_K_PARTY_ROLES_B KPRC
                ,HZ_PARTIES PTYC
                ,OKC_K_PARTY_ROLES_B KPRV
                ,OKC_Contacts KCTS
                ,OKC_Statuses_B KSTB
                ,OKC_Statuses_TL KSTT
                ,HR_ALL_ORGANIZATION_UNITS_TL ORG
                &P_CONTRACT_GROUP_FROM
              WHERE
                 KHD.Scs_Code            IN ('SERVICE','WARRANTY')
                AND KPRC.Dnz_Chr_Id         = KHD.Id
                AND KPRC.Rle_code           = 'CUSTOMER'
                AND KPRC.Jtot_Object1_Code  = 'OKX_PARTY'
                AND KPRC.Object1_Id1        = PTYC.Party_Id
                AND KPRC.Object1_Id2        = '#'
                AND KPRC.Chr_Id             = KHD.Id
                AND KPRC.Cle_Id             IS NULL
                AND KPRV.Dnz_Chr_Id         = KHD.Id
                AND KPRV.Rle_code           = 'VENDOR'
                AND KPRV.Jtot_Object1_Code  = 'OKX_OPERUNIT'
                AND KPRV.Chr_Id             = KHD.Id
                AND KPRV.Cle_Id             IS NULL
                AND KCTS.CPL_Id              (+) = KPRV.Id
                AND KCTS.CRO_Code         (+) =   :P_Vendor_Cont_Role
                AND KCTS.JTOT_Object1_Code  (+)= 'OKX_SALEPERS'
                AND KCTS.ID                     =  OKS_EXTWAR_UTIL_PVT.active_salesrep(KHD.ID, KPRV.ID,  KHD.authoring_org_id)
                AND KCTS.Object1_Id2       (+) = '#'
                AND KSTB.Code               = KHD.Sts_Code
                AND KSTT.Code               = KHD.Sts_Code
                AND KSTB.Code               = KSTT.Code
                AND KSTT.LANGUAGE     = USERENV('LANG')
                AND KHD.Authoring_Org_Id  = ORG.Organization_Id
                AND ORG.Language            = USERENV('LANG')
                &P_START_END_DATE_WHERE
                &P_REP_NAME_WHERE
                &P_CUSTOMER_NAME_WHERE
                &P_STATUS_CODE_WHERE
                &P_STATUS_TYPE_WHERE
                &P_ORG_ID_WHERE
                &P_CUSTOMER_NUMBER_WHERE
                &P_CURRENCY_CODE_WHERE
                &P_CONTRACT_GROUP_WHERE
              order by org_name, org_id, Currency_Code, CF_salesrep_name, Rep_Name,
              Status_Code, Status_Type, Status_Meaning, Status_Code_SUBSTR,
              Status_Type_SUBSTR, Customer_Name_Substr, Customer_Number,
              Contract_Number_Substr, Contract_Modifier, Contract_Modifier_substr,
              Contract_Id, Customer_Id, Start_Date, End_Date,
              ORG.Name ,KHD.Currency_Code, KSTB.Ste_Code, KHD.Sts_Code,
              PTYC.Party_Name, PTYC.Party_Number, KHD.Contract_Number,
              KHD.Contract_Number_Modifier