ECC Project Procurement, Supplier Analysis

Description
Categories: Enterprise Command Center
Columns: Record Type, Ecc Spec Id, Record Identifier, Sup Org Id, Parent Supplier Name, Supplier Number, Supplier Id, Supplier Name, End Date Active, Start Date Active ...
Imported from Enterprise Command Center
Dataset Key: po-supp-analysis
Query Procedure: po_supp_analysis_ecc_pkg.LOAD_ECC_DATA_FULL
Security Procedure: po_supp_analysis_ecc_pkg.GetFilterAttributeValues
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
Parameter