[Home] [Help]
View: PV_PARTNER_CONTACTS_V
View Text
SELECT HZR.PARTY_ID ID1
, '#' ID2
, HZP.STATUS B_STATUS
, HZP.STATUS STATUS
, HZR.OBJECT_ID PARTY_ID
, '#' PARTY_ID2
, HZP.PARTY_NUMBER PARTY_NUMBER
, HZP.PARTY_NAME NAME
, HZP.PARTY_NAME DESCRIPTION
, HZP.PARTY_TYPE PARTY_TYPE
, SYSDATE START_DATE_ACTIVE
, DECODE(HZP.STATUS
, 'I'
, SYSDATE
, 'D'
, SYSDATE) END_DATE_ACTIVE
, PVPP.PARTNER_PARTY_ID PARTNER_PARTY_ID
FROM HZ_RELATIONSHIPS HZR
, HZ_PARTIES HZP
, (SELECT DISTINCT PARTNER_PARTY_ID
FROM PV_PARTNER_PROFILES) PVPP
WHERE PVPP.PARTNER_PARTY_ID = HZR.OBJECT_ID
AND HZR.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND DIRECTIONAL_FLAG = 'F'
AND HZR.START_DATE <= SYSDATE
AND NVL(HZR.END_DATE
, SYSDATE) >= SYSDATE
AND HZR.SUBJECT_ID = HZP.PARTY_ID
AND EXISTS (SELECT '1'
FROM HZ_ORG_CONTACTS OC
WHERE OC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID)
Columns
Name |
ID1 |
ID2 |
B_STATUS |
STATUS |
PARTY_ID |
PARTY_ID2 |
PARTY_NUMBER |
NAME |
DESCRIPTION |
PARTY_TYPE |
START_DATE_ACTIVE |
END_DATE_ACTIVE |
PARTNER_PARTY_ID |