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 |