VIEW: APPS.POA_REP_SUP_SCORE_V

Source

View Text - Preformatted

SELECT Organization.All_All_PK_Key, Organization.Oper_Operating_Unit_PK_Key, Organization.Orga_Organization_PK_Key, Item.All_All_PK_Key, Item.Ci11_Category_PK_Key, Item.Item_Item_Number_PK_Key, Supplier.All_All_PK_Key, Supplier.PTP4_TPartner_PK_Key, Supplier.TPRT_Trade_Partner_PK_Key, Supplier.TPLO_TPartner_Loc_PK_Key, Time.All_All_PK_Key, Time.ECYR_CAL_YEAR_PK_KEY, Time.ECQR_CAL_QTR_PK_KEY, Time.Mnth_Month_PK_Key, DUNS.All_All_PK_Key, DUNS.Glbl_Global_Ult_PK_Key, DUNS.Dome_Domestic_Ult_PK_Key, DUNS.Prnt_Parent_PK_Key, DUNS.Hqtr_HQ_PK_Key, DUNS.DNMR_DUNS_Num_PK_Key, UNSPSC.Sgmt_Segment_PK_Key, UNSPSC.Fmly_Family_PK_Key, UNSPSC.Clas_Class_PK_Key, UNSPSC.Cmdt_Commodity_PK_Key, UNSPSC.Fnct_Function_PK_Key, UNSPSC.All_All_PK_Key, sum(qty_ordered_b), avg(price_g), avg(target_price_g), sum(Amt_Purchased), avg(nvl(price_score,25)), avg(nvl(DELIVERY_SCORE,25)), avg(nvl(QUALITY_SCORE,25)), avg(nvl(SURVEY_SCORE,25)), avg(nvl(PRICE_SCORE,25) + nvl(DELIVERY_SCORE,25) + nvl(QUALITY_SCORE,25) + nvl(SURVEY_SCORE,25)) FROM POA_REP_CSTM_MSR_V Cstm_Msr, EDW_Organization_M Organization, EDW_TRD_Partner_M Supplier, EDW_Time_M Time, EDW_Items_M Item, EDW_DUNS_M DUNS, EDW_UNSPSC_M UNSPSC WHERE Cstm_Msr.Ship_To_Org_FK_Key = Organization.Orga_Organization_PK_Key and Cstm_Msr.Supplier_Site_FK_Key= Supplier.TPLO_TPartner_Loc_PK_Key and Cstm_Msr.Eval_Date_FK_Key = Time.CDay_Cal_Day_PK_Key and Cstm_Msr.Item_FK_Key = Item.Irev_Item_Revision_PK_Key and Cstm_Msr.DUNS_FK_Key = DUNS.DNMR_DUNS_NUM_PK_Key and Cstm_Msr.UNSPSC_FK_Key = UNSPSC.Fnct_Function_PK_Key group by Organization.All_All_PK_Key, Organization.Oper_Operating_Unit_PK_Key, Organization.Orga_Organization_PK_Key, Item.All_All_PK_Key, Item.Ci11_Category_PK_Key, Item.Item_Item_Number_PK_Key, Supplier.All_All_PK_Key, Supplier.PTP4_TPartner_PK_Key, Supplier.TPRT_Trade_Partner_PK_Key, Supplier.TPLO_TPartner_Loc_PK_Key, Time.All_All_PK_Key, Time.ECYR_CAL_YEAR_PK_KEY, Time.ECQR_CAL_QTR_PK_KEY, Time.Mnth_Month_PK_Key, DUNS.All_All_PK_Key, DUNS.Glbl_Global_Ult_PK_Key, DUNS.Dome_Domestic_Ult_PK_Key, DUNS.Prnt_Parent_PK_Key, DUNS.Hqtr_HQ_PK_Key, DUNS.DNMR_DUNS_Num_PK_Key, UNSPSC.Sgmt_Segment_PK_Key, UNSPSC.Fmly_Family_PK_Key, UNSPSC.Clas_Class_PK_Key, UNSPSC.Cmdt_Commodity_PK_Key, UNSPSC.Fnct_Function_PK_Key, UNSPSC.All_All_PK_Key

View Text - HTML Formatted

SELECT ORGANIZATION.ALL_ALL_PK_KEY
, ORGANIZATION.OPER_OPERATING_UNIT_PK_KEY
, ORGANIZATION.ORGA_ORGANIZATION_PK_KEY
, ITEM.ALL_ALL_PK_KEY
, ITEM.CI11_CATEGORY_PK_KEY
, ITEM.ITEM_ITEM_NUMBER_PK_KEY
, SUPPLIER.ALL_ALL_PK_KEY
, SUPPLIER.PTP4_TPARTNER_PK_KEY
, SUPPLIER.TPRT_TRADE_PARTNER_PK_KEY
, SUPPLIER.TPLO_TPARTNER_LOC_PK_KEY
, TIME.ALL_ALL_PK_KEY
, TIME.ECYR_CAL_YEAR_PK_KEY
, TIME.ECQR_CAL_QTR_PK_KEY
, TIME.MNTH_MONTH_PK_KEY
, DUNS.ALL_ALL_PK_KEY
, DUNS.GLBL_GLOBAL_ULT_PK_KEY
, DUNS.DOME_DOMESTIC_ULT_PK_KEY
, DUNS.PRNT_PARENT_PK_KEY
, DUNS.HQTR_HQ_PK_KEY
, DUNS.DNMR_DUNS_NUM_PK_KEY
, UNSPSC.SGMT_SEGMENT_PK_KEY
, UNSPSC.FMLY_FAMILY_PK_KEY
, UNSPSC.CLAS_CLASS_PK_KEY
, UNSPSC.CMDT_COMMODITY_PK_KEY
, UNSPSC.FNCT_FUNCTION_PK_KEY
, UNSPSC.ALL_ALL_PK_KEY
, SUM(QTY_ORDERED_B)
, AVG(PRICE_G)
, AVG(TARGET_PRICE_G)
, SUM(AMT_PURCHASED)
, AVG(NVL(PRICE_SCORE
, 25))
, AVG(NVL(DELIVERY_SCORE
, 25))
, AVG(NVL(QUALITY_SCORE
, 25))
, AVG(NVL(SURVEY_SCORE
, 25))
, AVG(NVL(PRICE_SCORE
, 25) + NVL(DELIVERY_SCORE
, 25) + NVL(QUALITY_SCORE
, 25) + NVL(SURVEY_SCORE
, 25))
FROM POA_REP_CSTM_MSR_V CSTM_MSR
, EDW_ORGANIZATION_M ORGANIZATION
, EDW_TRD_PARTNER_M SUPPLIER
, EDW_TIME_M TIME
, EDW_ITEMS_M ITEM
, EDW_DUNS_M DUNS
, EDW_UNSPSC_M UNSPSC
WHERE CSTM_MSR.SHIP_TO_ORG_FK_KEY = ORGANIZATION.ORGA_ORGANIZATION_PK_KEY
AND CSTM_MSR.SUPPLIER_SITE_FK_KEY= SUPPLIER.TPLO_TPARTNER_LOC_PK_KEY
AND CSTM_MSR.EVAL_DATE_FK_KEY = TIME.CDAY_CAL_DAY_PK_KEY
AND CSTM_MSR.ITEM_FK_KEY = ITEM.IREV_ITEM_REVISION_PK_KEY
AND CSTM_MSR.DUNS_FK_KEY = DUNS.DNMR_DUNS_NUM_PK_KEY
AND CSTM_MSR.UNSPSC_FK_KEY = UNSPSC.FNCT_FUNCTION_PK_KEY GROUP BY ORGANIZATION.ALL_ALL_PK_KEY
, ORGANIZATION.OPER_OPERATING_UNIT_PK_KEY
, ORGANIZATION.ORGA_ORGANIZATION_PK_KEY
, ITEM.ALL_ALL_PK_KEY
, ITEM.CI11_CATEGORY_PK_KEY
, ITEM.ITEM_ITEM_NUMBER_PK_KEY
, SUPPLIER.ALL_ALL_PK_KEY
, SUPPLIER.PTP4_TPARTNER_PK_KEY
, SUPPLIER.TPRT_TRADE_PARTNER_PK_KEY
, SUPPLIER.TPLO_TPARTNER_LOC_PK_KEY
, TIME.ALL_ALL_PK_KEY
, TIME.ECYR_CAL_YEAR_PK_KEY
, TIME.ECQR_CAL_QTR_PK_KEY
, TIME.MNTH_MONTH_PK_KEY
, DUNS.ALL_ALL_PK_KEY
, DUNS.GLBL_GLOBAL_ULT_PK_KEY
, DUNS.DOME_DOMESTIC_ULT_PK_KEY
, DUNS.PRNT_PARENT_PK_KEY
, DUNS.HQTR_HQ_PK_KEY
, DUNS.DNMR_DUNS_NUM_PK_KEY
, UNSPSC.SGMT_SEGMENT_PK_KEY
, UNSPSC.FMLY_FAMILY_PK_KEY
, UNSPSC.CLAS_CLASS_PK_KEY
, UNSPSC.CMDT_COMMODITY_PK_KEY
, UNSPSC.FNCT_FUNCTION_PK_KEY
, UNSPSC.ALL_ALL_PK_KEY