VIEW: APPS.POA_REP_CSTM_MSR_V

Source

View Text - Preformatted

SELECT DATE_DIM_FK_KEY, SUPPLIER_SITE_FK_KEY, ITEM_FK_KEY, to_number(NULL), ship_to_org_fk_key, duns_fk_key, unspsc_fk_key, sum(NVL(qty_ordered_b,0)), avg(NVL(price_g,0)), avg(NVL(target_price_g,0)), sum(AMT_PURCHASED_G), 25 * (DECODE((SUM(NVL(Qty_Ordered_B,0)* DECODE(Price_G, 0,0,1))), 0,1, SUM(NVL(Qty_Ordered_B,0) * DECODE(Price_G, 0,0, LEAST(NVL(Target_Price_G,0)/Price_G,1)))/ (SUM(NVL(Qty_Ordered_B,0)* DECODE(Price_G, 0,0,1))))), 25 * (DECODE(SUM(NVL(Qty_Received_B,0) + NVL(Qty_Past_Due_B,0)), 0, 1, SUM(NVL(Qty_Received_B,0) - NVL(Qty_Early_Receipt_B,0) - NVL(Qty_Late_Receipt_B,0)) / SUM(NVL(Qty_Received_B,0) + NVL(Qty_Past_Due_B,0)))), 25 * (DECODE(SUM(NVL(Qty_Received_B,0)), 0,1, 1- SUM(NVL(Qty_Rejected_B,0)) / SUM(NVL(Qty_Received_B,0)))), to_number(NULL) FROM POA_EDW_SUP_PERF_F group by DATE_DIM_FK_KEY, SUPPLIER_SITE_FK_KEY, ITEM_FK_KEY, ship_to_org_fk_key, duns_fk_key, unspsc_fk_key UNION ALL SELECT EVAL_DATE_FK_KEY, SUPPLIER_SITE_FK_KEY, ITEM_FK_KEY, EVALUATION_ID, OPERATING_UNIT_FK_KEY, duns_fk_key, unspsc_fk_key, to_number(NULL), to_number(NULL), to_number(NULL), to_number(NULL), to_number(NULL), to_number(NULL), to_number(NULL), 25 * AVG(NVL(weighted_score,1))/100 FROM POA_EDW_CSTM_MSR_F group by EVAL_DATE_FK_KEY, SUPPLIER_SITE_FK_KEY, ITEM_FK_KEY, EVALUATION_ID, OPERATING_UNIT_FK_KEY, duns_fk_key, unspsc_fk_key

View Text - HTML Formatted

SELECT DATE_DIM_FK_KEY
, SUPPLIER_SITE_FK_KEY
, ITEM_FK_KEY
, TO_NUMBER(NULL)
, SHIP_TO_ORG_FK_KEY
, DUNS_FK_KEY
, UNSPSC_FK_KEY
, SUM(NVL(QTY_ORDERED_B
, 0))
, AVG(NVL(PRICE_G
, 0))
, AVG(NVL(TARGET_PRICE_G
, 0))
, SUM(AMT_PURCHASED_G)
, 25 * (DECODE((SUM(NVL(QTY_ORDERED_B
, 0)* DECODE(PRICE_G
, 0
, 0
, 1)))
, 0
, 1
, SUM(NVL(QTY_ORDERED_B
, 0) * DECODE(PRICE_G
, 0
, 0
, LEAST(NVL(TARGET_PRICE_G
, 0)/PRICE_G
, 1)))/ (SUM(NVL(QTY_ORDERED_B
, 0)* DECODE(PRICE_G
, 0
, 0
, 1)))))
, 25 * (DECODE(SUM(NVL(QTY_RECEIVED_B
, 0) + NVL(QTY_PAST_DUE_B
, 0))
, 0
, 1
, SUM(NVL(QTY_RECEIVED_B
, 0) - NVL(QTY_EARLY_RECEIPT_B
, 0) - NVL(QTY_LATE_RECEIPT_B
, 0)) / SUM(NVL(QTY_RECEIVED_B
, 0) + NVL(QTY_PAST_DUE_B
, 0))))
, 25 * (DECODE(SUM(NVL(QTY_RECEIVED_B
, 0))
, 0
, 1
, 1- SUM(NVL(QTY_REJECTED_B
, 0)) / SUM(NVL(QTY_RECEIVED_B
, 0))))
, TO_NUMBER(NULL)
FROM POA_EDW_SUP_PERF_F GROUP BY DATE_DIM_FK_KEY
, SUPPLIER_SITE_FK_KEY
, ITEM_FK_KEY
, SHIP_TO_ORG_FK_KEY
, DUNS_FK_KEY
, UNSPSC_FK_KEY UNION ALL SELECT EVAL_DATE_FK_KEY
, SUPPLIER_SITE_FK_KEY
, ITEM_FK_KEY
, EVALUATION_ID
, OPERATING_UNIT_FK_KEY
, DUNS_FK_KEY
, UNSPSC_FK_KEY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 25 * AVG(NVL(WEIGHTED_SCORE
, 1))/100
FROM POA_EDW_CSTM_MSR_F GROUP BY EVAL_DATE_FK_KEY
, SUPPLIER_SITE_FK_KEY
, ITEM_FK_KEY
, EVALUATION_ID
, OPERATING_UNIT_FK_KEY
, DUNS_FK_KEY
, UNSPSC_FK_KEY