Product: | PV - Partner Management |
---|---|
Description: | |
Implementation/DBA Data: | APPS.PV_PARTNERS_ALL_V |
SELECT INTERNAL.PARTY_ID
, INTERNAL.PARTY_NAME
, PARTNER.PARTY_ID
, PARTNER.PARTY_NAME
, HZOP.INTERNAL_FLAG
, HZR.RELATIONSHIP_ID
, HZR.RELATIONSHIP_CODE
, PVPP.PARTNER_PROFILE_ID
, PVPP.LAST_UPDATE_DATE
, PVPP.CREATION_DATE
, PVPP.OBJECT_VERSION_NUMBER
, HZR.PARTY_ID
, PVPP.TARGET_REVENUE_AMT
, PVPP.ACTUAL_REVENUE_AMT
, PVPP.TARGET_REVENUE_PCT
, PVPP.ACTUAL_REVENUE_PCT
, PVPP.ORIG_SYSTEM_REFERENCE
, PVPP.ORIG_SYSTEM_TYPE
, PVPP.CAPACITY_SIZE
, PVPP.CAPACITY_AMOUNT
, PVPP.AUTO_MATCH_ALLOWED_FLAG
, PVPP.PURCHASE_METHOD
, PVPP.CM_ID
, PVPP.PH_SUPPORT_REP
, PVPP.LEAD_SHARING_STATUS
, PVPP.LEAD_SHARE_APPR_FLAG
, PVPP.PARTNER_LEVEL
, PVPP.PREFERRED_VAD_ID
, PVPP.PARTNER_RESOURCE_ID
, PVPP.PARTNER_GROUP_ID
, PVPP.PARTNER_RESOURCE_NUMBER
, PVPP.PARTNER_GROUP_NUMBER
, PVPP.PARTNER_PARTY_ID
FROM PV_PARTNER_PROFILES PVPP
, HZ_PARTIES INTERNAL
, HZ_RELATIONSHIPS HZR
, HZ_ORGANIZATION_PROFILES HZOP
, HZ_PARTIES PARTNER
WHERE HZR.PARTY_ID = PVPP.PARTNER_ID(+)
AND HZR.SUBJECT_ID = PVPP.PARTNER_PARTY_ID(+)
AND HZR.OBJECT_ID = INTERNAL.PARTY_ID
AND HZR.STATUS ='A'
AND HZR.START_DATE <= SYSDATE
AND NVL(HZR.END_DATE
, SYSDATE) >= SYSDATE
AND HZR.RELATIONSHIP_TYPE IN ('PARTNER'
, 'PARTNER_MANAGED_CUSTOMER')
AND HZR.SUBJECT_ID = PARTNER.PARTY_ID
AND HZR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZR.SUBJECT_TYPE= 'ORGANIZATION'
AND HZR.OBJECT_TYPE ='ORGANIZATION'
AND INTERNAL.PARTY_TYPE='ORGANIZATION'
AND PARTNER.PARTY_TYPE ='ORGANIZATION'
AND HZOP.PARTY_ID = INTERNAL.PARTY_ID
AND (PVPP.PARTNER_ID IS NOT NULL OR (NVL(HZOP.INTERNAL_FLAG
, 'N') = 'Y'))
AND HZOP.EFFECTIVE_END_DATE IS NULL