View: ASF_OPPTY_SUM_REPRT_OSI_V

View Text

SELECT V.LEAD_ID
, V.OPP_DESCRIPTION
, V.SALES_GROUP_NAME
, V.SALES_GROUP_ID
, V.SALES_REP_NAME
, V.SALESFORCE_ID
, V.CUSTOMER_NAME
, V.CUSTOMER_ID
, V.LEAD_NUMBER
, V.DECISION_DATE
, V.SALES_STAGE
, V.SALES_STAGE_ID
, V.STATUS
, V.STATUS_CODE
, V.CHANNEL_CODE
, V.WIN_PROBABILITY
, V.INTEREST_TYPE
, V.INTEREST_TYPE_ID
, V.PRIMARY_INTEREST_CODE
, V.PRIMARY_INTEREST_CODE_ID
, V.SECONDARY_INTEREST_CODE
, V.SECONDARY_INTEREST_CODE_ID
, V.LAST_UPDATE_DATE
, V.LAST_UPDATED_BY
, V.CREATION_DATE
, V.CREATED_BY
, V.CUSTOMER_CATEGORY
, V.CUSTOMER_CATEGORY_CODE
, V.SALES_CREDIT_ID
, V.ADDRESS_ID
, V.OPP_OPEN_STATUS_FLAG
, V.OPP_DELETED_FLAG
, V.PARTY_TYPE
, MC.CONVERSION_TYPE
, MC.TO_CURRENCY_CODE
, MC.CONVERSION_PERIOD_SET_NAME
, MC.CONVERSION_PERIOD_NAME
, MC.CONVERSION_PERIOD_TYPE
, MC.CONVERSION_DATE
, MC.FROM_CURRENCY_CODE
, MC.TOTAL_AMOUNT
, SUM(NVL(MC.SALES_CREDIT_AMOUNT
, 0))
, SUM(NVL(MC.WON_AMOUNT
, 0))
, SUM(NVL(MC.WEIGHTED_AMOUNT
, 0))
, MAX(MC.CONVERSION_STATUS_FLAG)
, NULL
, V.CREDIT_TYPE_ID
, V.WIN_LOSS_INDICATOR
, V.PARTNER_CUSTOMER_ID
, V.PARTNER_CUSTOMER_NAME
, V.PARENT_PROJECT
, V.PARTNER_ADDRESS_ID
, V.EMPLOYEE_PERSON_ID
, O.CONTRACT_NAME
, O.CONTRACT_NAME_ID
, O.CONTRACT_VEHICLE
, O.CONTRACT_VHCL_ID
, O.CONTRACT_TYPE
, O.OVM_CODE
, O.OVM_VALUE
, O.BOM_PERSON_ID
, O.LEGAL_PERSON_ID
, O.SENIOR_CONTR_PERSON_ID
, O.CONT_SPEC_PERSON_ID
, O.COMBINED_OVM_VALUE
, MAX(MC.CONVERSION_RATE_FOUND)
, V.ATTRIBUTE_CATEGORY
, V.ATTRIBUTE1
, V.ATTRIBUTE2
, V.ATTRIBUTE3
, V.ATTRIBUTE4
, V.ATTRIBUTE5
, V.ATTRIBUTE6
, V.ATTRIBUTE7
, V.ATTRIBUTE8
, V.ATTRIBUTE9
, V.ATTRIBUTE10
, V.ATTRIBUTE11
, V.ATTRIBUTE12
, V.ATTRIBUTE13
, V.ATTRIBUTE14
, V.ATTRIBUTE15
, V.OPPORTUNITY_LAST_UPDATED_NAME
, V.OPPORTUNITY_CREATED_BY
, V.OPPORTUNITY_CREATION_DATE
, V.OPPORTUNITY_CREATED_NAME
, V.CLOSE_REASON
, V.CLOSE_REASON_MEANING
FROM AS_SALES_CREDITS_DENORM V
, AS_MC_SALES_CREDITS_DEN MC
, AS_OSI_LEADS_DENORM O
WHERE V.SALES_CREDIT_ID = MC.SALES_CREDIT_ID
AND MC.TYPE = 'PERIOD'
AND V.LEAD_ID = O.LEAD_ID(+) GROUP BY V.LEAD_ID
, V.OPP_DESCRIPTION
, V.SALES_GROUP_NAME
, V.SALES_GROUP_ID
, V.SALES_REP_NAME
, V.SALESFORCE_ID
, V.CUSTOMER_NAME
, V.CUSTOMER_ID
, V.LEAD_NUMBER
, V.DECISION_DATE
, V.SALES_STAGE
, V.SALES_STAGE_ID
, V.STATUS
, V.PRIMARY_INTEREST_CODE
, V.PRIMARY_INTEREST_CODE_ID
, V.SECONDARY_INTEREST_CODE
, V.SECONDARY_INTEREST_CODE_ID
, V.LAST_UPDATE_DATE
, V.LAST_UPDATED_BY
, V.CREATION_DATE
, V.CREATED_BY
, V.CUSTOMER_CATEGORY
, V.EMPLOYEE_PERSON_ID
, O.CONTRACT_NAME
, O.CONTRACT_NAME_ID
, O.CONTRACT_VEHICLE
, O.CONTRACT_VHCL_ID
, O.CONTRACT_TYPE
, O.OVM_CODE
, O.OVM_VALUE
, O.BOM_PERSON_ID
, O.LEGAL_PERSON_ID
, O.SENIOR_CONTR_PERSON_ID
, O.CONT_SPEC_PERSON_ID
, O.COMBINED_OVM_VALUE
, V.ATTRIBUTE_CATEGORY
, V.ATTRIBUTE1
, V.ATTRIBUTE2
, V.ATTRIBUTE3
, V.ATTRIBUTE4
, V.ATTRIBUTE5
, V.ATTRIBUTE6
, V.ATTRIBUTE7
, V.ATTRIBUTE8
, V.ATTRIBUTE9
, V.ATTRIBUTE10
, V.ATTRIBUTE11
, V.ATTRIBUTE12
, V.ATTRIBUTE13
, V.ATTRIBUTE14
, V.ATTRIBUTE15
, V.OPPORTUNITY_LAST_UPDATED_NAME
, V.OPPORTUNITY_CREATED_BY
, V.OPPORTUNITY_CREATION_DATE
, V.OPPORTUNITY_CREATED_NAME
, V.CLOSE_REASON
, V.CLOSE_REASON_MEANING

Columns