[Home] [Help]
| Product: | AHM - Hosting Manager(Obsolete) |
|---|---|
| Description: | This view gives following Suscriber Company information and contact information that is captured at the time of Registration. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT FU.USER_ID
, FU.USER_NAME
, HP.PARTY_NAME
, HCP.URL
, HOP.EMPLOYEES_TOTAL
, HP.KNOWN_AS
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.STATE
, HL.COUNTY
, HL.PROVINCE
, HL.POSTAL_CODE
, HL.COUNTRY
, HCP.PHONE_NUMBER
, HCP.PHONE_LINE_TYPE
, HP.PARTY_ID
, HOP.LINE_OF_BUSINESS
, HP.TOTAL_NUM_OF_ORDERS
, HP.VALIDATED_FLAG
, HP.PARTY_NUMBER
, HP.LAST_UPDATE_DATE
, HL.LOCATION_ID
, HL.LAST_UPDATE_DATE LOCATION_LAST_UPDATE
, HCP.CONTACT_POINT_ID
, HCP.LAST_UPDATE_DATE CONTACT_LAST_UPDATE
, HCP.OWNER_TABLE_ID
, HCP.CONTACT_POINT_TYPE
, ASA.PARTY_SECURITY_GROUP_ID
FROM HZ_PARTIES HP
, FND_USER FU
, HZ_RELATIONSHIPS HR
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HL
, HZ_CONTACT_POINTS HCP
, HZ_ORGANIZATION_PROFILES HOP
, AHM_SLOT_ALLOCATIONS ASA
WHERE FU.CUSTOMER_ID = HR.PARTY_ID
AND HP.PARTY_ID = ASA.PARTY_ID(+)
AND HR.OBJECT_ID = HP.PARTY_ID
AND HR.OBJECT_ID = HPS.PARTY_ID
AND HR.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HPS.IDENTIFYING_ADDRESS_FLAG = 'Y'
AND HR.PARTY_ID = HCP.OWNER_TABLE_ID
AND HCP.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND ((HCP.CONTACT_POINT_TYPE = 'PHONE'
AND ((HCP.PHONE_LINE_TYPE = 'GEN'
AND HCP.PRIMARY_FLAG = 'Y') OR (HCP.PHONE_LINE_TYPE = 'FAX'
AND HCP.PRIMARY_FLAG = 'N'))) OR (HCP.CONTACT_POINT_TYPE = 'WEB'
AND HCP.WEB_TYPE = 'HTTP'))
AND HR.OBJECT_ID = HOP.PARTY_ID
AND SYSDATE BETWEEN NVL(HOP.EFFECTIVE_START_DATE
, SYSDATE-1)
AND NVL(HOP.EFFECTIVE_END_DATE
, SYSDATE+1) ORDER BY HCP.CONTACT_POINT_TYPE
, HCP.PHONE_LINE_TYPE DESC