VIEW: APPS.POA_PORTAL_PURCHASES_TP_V

Source

View Text - Preformatted

SELECT PDIST.SUPPLIER_PK_KEY, PDIST.supplier_name, NVL(Current_Purch,0), NVL(Previous_Purch,0), NVL(Current_Purch,0)/NVL(tot_purch,1)*100, NVL(Score,100) from ( select DIST.SUPPLIER_PK_KEY, DIST.supplier_name, sum(DIST.sum_purchases) Current_Purch, to_number(NULL) Previous_Purch, to_number(NULL) Score from POA_PORTAL_PDIST DIST WHERE quarter = 'C' AND DIST.sum_purchases <> 0 AND SUM_PURCHASES IS NOT NULL group by SUPPLIER_PK_KEY, supplier_name UNION ALL select DIST.SUPPLIER_PK_KEY, DIST.supplier_name, to_number(NULL) Current_Purch, sum(DIST.sum_purchases) Previous_Purch, to_number(NULL) Score from POA_PORTAL_PDIST DIST where quarter = 'L' AND DIST.sum_purchases <> 0 AND SUM_PURCHASES IS NOT NULL group by SUPPLIER_PK_KEY, supplier_name UNION ALL select RISK.SUPPLIER_PK_KEY, RISK.supplier_name, 0, 0, Avg(nvl(RISK.Price_Score,25) + nvl(RISK.Quality_Score,25) + nvl(RISK.Delivery_Score,25) + nvl(RISK.Service_Score,25)) Score from POA_PORTAL_SUP_RISK RISK group by SUPPLIER_PK_KEY, supplier_name) PDIST, (select sum(sum_purchases) tot_purch from POA_PORTAL_PDIST where quarter = 'C')

View Text - HTML Formatted

SELECT PDIST.SUPPLIER_PK_KEY
, PDIST.SUPPLIER_NAME
, NVL(CURRENT_PURCH
, 0)
, NVL(PREVIOUS_PURCH
, 0)
, NVL(CURRENT_PURCH
, 0)/NVL(TOT_PURCH
, 1)*100
, NVL(SCORE
, 100)
FROM ( SELECT DIST.SUPPLIER_PK_KEY
, DIST.SUPPLIER_NAME
, SUM(DIST.SUM_PURCHASES) CURRENT_PURCH
, TO_NUMBER(NULL) PREVIOUS_PURCH
, TO_NUMBER(NULL) SCORE
FROM POA_PORTAL_PDIST DIST
WHERE QUARTER = 'C'
AND DIST.SUM_PURCHASES <> 0
AND SUM_PURCHASES IS NOT NULL GROUP BY SUPPLIER_PK_KEY
, SUPPLIER_NAME UNION ALL SELECT DIST.SUPPLIER_PK_KEY
, DIST.SUPPLIER_NAME
, TO_NUMBER(NULL) CURRENT_PURCH
, SUM(DIST.SUM_PURCHASES) PREVIOUS_PURCH
, TO_NUMBER(NULL) SCORE
FROM POA_PORTAL_PDIST DIST
WHERE QUARTER = 'L'
AND DIST.SUM_PURCHASES <> 0
AND SUM_PURCHASES IS NOT NULL GROUP BY SUPPLIER_PK_KEY
, SUPPLIER_NAME UNION ALL SELECT RISK.SUPPLIER_PK_KEY
, RISK.SUPPLIER_NAME
, 0
, 0
, AVG(NVL(RISK.PRICE_SCORE
, 25) + NVL(RISK.QUALITY_SCORE
, 25) + NVL(RISK.DELIVERY_SCORE
, 25) + NVL(RISK.SERVICE_SCORE
, 25)) SCORE
FROM POA_PORTAL_SUP_RISK RISK GROUP BY SUPPLIER_PK_KEY
, SUPPLIER_NAME) PDIST
, (SELECT SUM(SUM_PURCHASES) TOT_PURCH
FROM POA_PORTAL_PDIST
WHERE QUARTER = 'C')