OKS Service Contracts by Status

Description
Categories: BI Publisher, Service
Application: Service Contracts
Source: Service Contracts by Status Report (XML)
Short Name: OKSSUMCO_XML
DB package: OKS_OKSSUMCO_XMLP_PKG
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
Parameter Name SQL text Validation
Detail Report
 
LOV Oracle
Currency Code
 
LOV Oracle
Status Type
 
LOV Oracle
Status Code
 
LOV Oracle
To End Date
 
Date
From End Date
 
Date
To Start Date
 
Date
From Start Date
 
Date
Customer Name
 
LOV Oracle
Customer Number
 
LOV Oracle
Sales Rep Name
 
LOV Oracle
Operating Unit
 
LOV Oracle
Contract Group
 
LOV Oracle
Ask a question