[Home] [Help]
View: AHM_CUSTSUMMARY_V
Product: | AHM - Hosting Manager(Obsolete) |
Description: | This view gives a summary obout the party_id from ahm_party_rule_attributes, HZ_PARTIES, ahm_subscribed_svcs |
Implementation/DBA Data: |
Not implemented in this database
|
View Text
SELECT DISTINCT(A.PARTY_ID) PARTY_ID
, A.PARTY_NAME PARTY_NAME
, A.CREATION_DATE
, DECODE(B.STATUS
, 'S'
, 'SUBSCRIBED'
, 'R'
, 'REJECTED'
, 'A'
, 'APPROVED'
, 'Z'
, ' APPROVAL HALTED'
, 'X'
, ' REJECTION HALTED') STATUS
, C.RULE_VALUE "NUM_USERS"
, D.RULE_VALUE "GEOGRAPHY"
, E.RULE_VALUE "SLOT_TYPE"
, F.RULE_VALUE "SLA"
, G.EMAIL_ADDRESS "EMAIL"
, Z.PERSON_FIRST_NAME "FIRST_NAME"
, Z.PERSON_LAST_NAME "LAST_NAME"
, Z.PERSON_TITLE "TITLE"
, L.DB_NAME "ASSIGNED DB"
FROM HZ_PARTIES A
, AHM_SUBSCRIBED_SVCS B
, AHM_PARTY_RULE_ATTRIBUTES C
, AHM_PARTY_RULE_ATTRIBUTES D
, AHM_PARTY_RULE_ATTRIBUTES E
, AHM_PARTY_RULE_ATTRIBUTES F
, HZ_PARTIES Z
, HZ_CONTACT_POINTS G
, HZ_RELATIONSHIPS K
, AHM_HOST_DATABASES_VL L
, AHM_SLOT_ALLOCATIONS M
WHERE K.OBJECT_ID = A.PARTY_ID
AND G.OWNER_TABLE_ID = K.SUBJECT_ID
AND G.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND Z.PARTY_ID = K.SUBJECT_ID
AND G.CONTACT_POINT_TYPE = 'EMAIL'
AND G.PRIMARY_FLAG = 'Y'
AND A.PARTY_ID = B.PARTY_ID
AND B.PARTY_ID = C.PARTY_ID
AND C.PARTY_ID = D.PARTY_ID
AND D.PARTY_ID = E.PARTY_ID
AND E.PARTY_ID = F.PARTY_ID
AND C.RULE_ID = 3
AND D.RULE_ID = 1
AND E.RULE_ID = 0
AND F.RULE_ID = 2
AND B.STATUS = 'A'
AND A.PARTY_ID = M.PARTY_ID
AND M.DB_ID = L.DB_ID
Columns
Name |
PARTY_ID |
PARTY_NAME |
CREATION_DATE |
STATUS |
NUM_USERS |
GEOGRAPHY |
SLOT_TYPE |
SLA |
EMAIL |
FIRST_NAME |
LAST_NAME |
TITLE |
ASSIGNED_DB |