ECC Project Procurement, Supplier Analysis

select
x.*
from
(
 
SELECT * FROM
(
SELECT
v.*,
 
1 c_one,
 
document_type_dsp as C_DOCUMENT_TYPE_DSP,
 
(agmt_avg_item_rating * agmt_item_ratings_count) as C_AGMT_ITEM_RATINGS_TOTAL,
 
(agmt_avg_sup_rating  * agmt_sup_ratings_count)  as C_AGMT_SUPP_RATINGS_TOTAL,
 
CASE WHEN (shipment_ontime_flag IS NOT NULL ) AND order_status_code IN ('APPROVED', 'PO_STATUS_CLOSED',
          'PO_STATUS_FINALLY_CLOSED', 'PO_STATUS_CANCELED', 'PO_STATUS_FROZEN', 'PO_STATUS_ON_HOLD')
	 THEN quality_rating
     ELSE NULL
END as C_QUALITY_RATING,
 
CASE WHEN (shipment_ontime_flag='Y')  AND order_status_code IN ('APPROVED', 'PO_STATUS_CLOSED',
          'PO_STATUS_FINALLY_CLOSED', 'PO_STATUS_CANCELED', 'PO_STATUS_FROZEN', 'PO_STATUS_ON_HOLD')
	 THEN line_location_id
     ELSE NULL
END as C_ONTIME_LINE_LOCATIONS,
 
CASE WHEN (shipment_ontime_flag IS NOT NULL ) AND order_status_code IN ('APPROVED', 'PO_STATUS_CLOSED',
          'PO_STATUS_FINALLY_CLOSED', 'PO_STATUS_CANCELED', 'PO_STATUS_FROZEN', 'PO_STATUS_ON_HOLD')
	 THEN line_location_id
     ELSE NULL
END as C_LINE_LOCATIONS,
 
CASE WHEN order_status_code IN ('APPROVED', 'PO_STATUS_CLOSED', 'PO_STATUS_FINALLY_CLOSED',
                                'PO_STATUS_CANCELED', 'PO_STATUS_FROZEN', 'PO_STATUS_ON_HOLD')
	 THEN (quantity_invoiced * unit_price * price_compliance)
 
     ELSE NULL
END as C_PRICE_COMP_TOTAL,
 
CASE WHEN ( order_status_code IN ('APPROVED', 'PO_STATUS_CLOSED', 'PO_STATUS_FINALLY_CLOSED',
                                'PO_STATUS_CANCELED', 'PO_STATUS_FROZEN', 'PO_STATUS_ON_HOLD')
            AND	(quantity_invoiced * unit_price) <> 0
		  )
 
	 THEN (quantity_invoiced * unit_price)
 
     ELSE NULL
END as C_PRICE_TOTAL,
 
CASE WHEN ((neg_status_code <> 'COMPLETED') AND auction_status IN ('DRAFT', 'ACTIVE') AND (record_type = 'NEGOTIATION_DETAILS')) THEN
	         auction_header_id
     ELSE  NULL
END as C_ACTIVE_NEGOTIATIONS,
 
CASE WHEN ((bid_status = 'ACTIVE') AND (record_type = 'NEGOTIATION_DETAILS')) THEN
	         auction_header_id
     ELSE  NULL
END as C_PPT_NEGOTIATIONS,
 
CASE WHEN (AWARD_STATUS_CODE IN ('AWARDED', 'PARTIAL')AND (record_type = 'NEGOTIATION_DETAILS')) THEN
	         auction_header_id
     ELSE  NULL
END as C_AWARDED_NEGOTIATIONS,
 
NVL(item_category,approved_item_category) as C_ASL_ITEM_CATEGORY,
 
CASE WHEN (record_type = 'ORDER_DETAILS') THEN
	         NVL(item_name , item_description)
     ELSE  NULL
END as C_ITEM_NAME_DESC,
 
CASE WHEN (bid_status = 'ACTIVE') THEN
	         supplier_id
     ELSE  NULL
END as C_BID_SUPPLIER_ID,
 
CASE WHEN (order_status_code = 'APPROVED')
	 THEN contract_spend
     ELSE 0
END as C_CONTRACT_SPEND,
 
CASE WHEN (order_status_code = 'APPROVED')
	 THEN off_contract_spend
     ELSE 0
END as C_OFF_CONTRACT_SPEND,
 
CASE WHEN (order_status_code = 'APPROVED')
	 THEN shipment_amount
     ELSE 0
END as C_SHIPMENT_AMOUNT,
 
CASE
	WHEN (contract_spend <> 0) THEN 10
	WHEN (off_contract_spend <> 0) THEN 20
	ELSE 0
END as C_SPEND_TYPE,
 
CASE
	WHEN ( (purchase_order_hold = 'Y') AND (payment_all_hold = 'Y') AND (unmatched_invoices_hold = 'Y') ) THEN 45
	WHEN (                                   (payment_all_hold = 'Y') AND (unmatched_invoices_hold = 'Y') ) THEN 40
	WHEN ( (purchase_order_hold = 'Y') AND                                (unmatched_invoices_hold = 'Y') ) THEN 30
	WHEN ( (purchase_order_hold = 'Y') AND (payment_all_hold = 'Y')                                       ) THEN 20
	WHEN ( (unmatched_invoices_hold = 'Y') ) THEN 25
	WHEN ( (payment_all_hold = 'Y') )        THEN 15
	WHEN ( (purchase_order_hold = 'Y') )     THEN 5
	ELSE 0
END as C_HOLD_TYPE
 
FROM PO_ECC_SUPP_ANALYSIS_V v WHERE  LANGUAGE in ('US')
) PIVOT (
max(SUPPLIER_TYPE) as SUPPLIER_TYPE,
max(PURCHASE_ORDER_HOLD_MEANING) as PO_HOLD_MEANING,
max(PAYMENT_ALL_HOLD_MEANING) as PAYMENT_ALL_HOLD_MEANING,
max(UNMATCHED_INVOICES_MEANING) as UNMATCHED_INVOICES_MEANING,
max(SUP_PAYMENT_TERM) as SUP_PAYMENT_TERM,
max(SUP_COUNTRY_NAME) as SUP_COUNTRY_NAME,
max(SUP_ADDRESS_PURPOSE) as SUP_ADDRESS_PURPOSE,
max(SUP_ADDRESS_STATUS_MEANING) as SUP_ADDRESS_STATUS_MEANING,
max(SUP_CONTACT_STATUS) as SUP_CONTACT_STATUS,
max(SUP_OPERATING_UNIT) as SUP_OPERATING_UNIT,
max(SUP_PURCHASING_SITE) as SUP_PURCHASING_SITE,
max(SUP_RFQ_ONLY) as SUP_RFQ_ONLY,
max(SUP_PAY_SITE) as SUP_PAY_SITE,
max(SUP_PRIMARY_PAY_SITE) as SUP_PRIMARY_PAY_SITE,
max(SUP_FOB) as SUP_FOB,
max(COUNTRY_OF_ORIGIN) as COUNTRY_OF_ORIGIN,
max(PAY_GROUP_MEANING) as PAY_GROUP_MEANING,
max(SUP_SUPPLIER_CATEGORY_TYPE) as SUP_SUPPLIER_CATEGORY_TYPE,
max(SUP_GLOBAL_FLAG) as SUP_GLOBAL_FLAG,
max(SUP_OWNING_ORGANIZATION) as SUP_OWNING_ORGANIZATION,
max(SUP_APPR_LIST_BUSINESS_TYP) as SUP_APPR_LIST_BUSINESS_TYP,
max(SUPPLIER_STATUS) as SUPPLIER_STATUS,
max(SUPPLIER_CLASSIFICATION) as SUPPLIER_CLASSIFICATION,
max(ORDER_STATUS) as ORDER_STATUS,
max(LINE_TYPE) as LINE_TYPE,
max(PURCHASING_OU_NAME) as PURCHASING_OU_NAME,
max(UNIT_MEAS_LOOKUP_CODE) as UNIT_MEAS_LOOKUP_CODE,
max(AGREEMENT_STATUS) as AGREEMENT_STATUS,
max(AGREEMENT_ACTIVE_STATUS) as AGREEMENT_ACTIVE_STATUS,
max(AGREEMENT_OU_NAME) as AGREEMENT_OU_NAME,
max(PO_LINE_STATUS) as PO_LINE_STATUS,
max(AWARD_STATUS_MEANING) as AWARD_STATUS_MEANING,
max(NEG_LINE_STATUS) as NEG_LINE_STATUS,
max(DOCUMENT_TYPE_DSP) as DOCUMENT_TYPE_DSP,
max(C_DOCUMENT_TYPE_DSP) as C_DOCUMENT_TYPE_DSP,
max(DOCUMENT_TYPE_CODE) as DOCUMENT_TYPE_CODE,
max(NEGOTIATION_OU_NAME) as NEGOTIATION_OU_NAME,
max(PROJECT_OU) as PROJECT_OU,
max(APPROVED_SUPP_STATE) as APPROVED_SUPP_STATE,
max(APPR_SUPP_COUNTRY_NAME) as APPR_SUPP_COUNTRY_NAME,
max(OPERATING_UNIT) as OPERATING_UNIT
for LANGUAGE in ('US' "US"))
ORDER BY PARTY_ID , VENDOR_SITE_ID
) x
where
2=2