MATERIALIZED VIEW: APPS.OKI_SRM_002_MV

Source

Select /* 12.0: bug#4526784 */ SERVICE_ITEM_CATEGORY_ID
,CLASS_CODE
,SERVICE_ITEM_ORG_ID
,AUTHORING_ORG_ID
,CUSTOMER_PARTY_ID
,den.prg_id                                         RG_ID
,den.parent_prg_id                                  PRG_ID
,-999                                               RESOURCE_ID
,den.umarker                                        UMARKER
,STS_CODE
,TRN_CODE
,CLE_CREATION_DATE
,DATE_SIGNED
,DATE_CANCELLED
,START_DATE
,DATE_TERMINATED
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,EXPECTED_CLOSE_DATE
,P_GRACE_END_DATE
,WIN_PERCENT
,RENEWAL_FLAG
,P_TERM_FLAG
,EXP_RENEWAL_FLAG
,SUM(TRN_BILLED_VALUE_F) TRN_BILLED_VALUE_F
,SUM(TRN_BILLED_VALUE_G) TRN_BILLED_VALUE_G
,SUM(TRN_BILLED_VALUE_SG) TRN_BILLED_VALUE_SG
,SUM(TRN_BILLED_VALUE_A) TRN_BILLED_VALUE_A
,SUM(PRICE_NEGOTIATED_F) PRICE_NEGOTIATED_F
,SUM(PRICE_NEGOTIATED_G) PRICE_NEGOTIATED_G
,SUM(PRICE_NEGOTIATED_SG) PRICE_NEGOTIATED_SG
,SUM(PRICE_NEGOTIATED_A) PRICE_NEGOTIATED_A
,SUM(P_PRICE_NEGOTIATED_F) P_PRICE_NEGOTIATED_F
,SUM(P_PRICE_NEGOTIATED_G) P_PRICE_NEGOTIATED_G
,SUM(P_PRICE_NEGOTIATED_SG) P_PRICE_NEGOTIATED_SG
,SUM(P_PRICE_NEGOTIATED_A) P_PRICE_NEGOTIATED_A
,COUNT(TRN_BILLED_VALUE_F) C_TRN_BILLED_VALUE_F
,COUNT(TRN_BILLED_VALUE_G) C_TRN_BILLED_VALUE_G
,COUNT(TRN_BILLED_VALUE_SG) C_TRN_BILLED_VALUE_SG
,COUNT(TRN_BILLED_VALUE_A) C_TRN_BILLED_VALUE_A
,COUNT(PRICE_NEGOTIATED_F) C_PRICE_NEGOTIATED_F
,COUNT(PRICE_NEGOTIATED_G) C_PRICE_NEGOTIATED_G
,COUNT(PRICE_NEGOTIATED_SG) C_PRICE_NEGOTIATED_SG
,COUNT(PRICE_NEGOTIATED_A) C_PRICE_NEGOTIATED_A
,COUNT(P_PRICE_NEGOTIATED_F) C_P_PRICE_NEGOTIATED_F
,COUNT(P_PRICE_NEGOTIATED_G) C_P_PRICE_NEGOTIATED_G
,COUNT(P_PRICE_NEGOTIATED_SG) C_P_PRICE_NEGOTIATED_SG
,COUNT(P_PRICE_NEGOTIATED_A) C_P_PRICE_NEGOTIATED_A
,COUNT(*) C_TOTAL
,EFFECTIVE_TERM_DATE
,EFFECTIVE_EXPIRE_DATE
FROM
OKI_SRM_001_MV CLE,
OKI_RS_GROUP_MV den
WHERE CLE.resource_group_id = den.rg_id
GROUP BY
 SERVICE_ITEM_CATEGORY_ID
,CLASS_CODE
,SERVICE_ITEM_ORG_ID
,AUTHORING_ORG_ID
,CUSTOMER_PARTY_ID
,den.prg_id
,den.parent_prg_id
,den.umarker
,STS_CODE
,TRN_CODE
,CLE_CREATION_DATE
,DATE_SIGNED
,DATE_CANCELLED
,START_DATE
/*,END_DATE */
,DATE_TERMINATED
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,EXPECTED_CLOSE_DATE
,P_GRACE_END_DATE
,WIN_PERCENT
,RENEWAL_FLAG
,P_TERM_FLAG
,EXP_RENEWAL_FLAG
, EFFECTIVE_TERM_DATE
, EFFECTIVE_EXPIRE_DATE