[Home] [Help]
View: PV_PARTNERS_LOV_V
View Text
SELECT PARTNER_PARTY.PARTY_ID ID1
, '#' ID2
, PARTNER_PARTY.PARTY_NUMBER DESCRIPTION
, PARTNER_PARTY.PARTY_NUMBER PARTY_NUMBER
, PARTNER_PARTY.PARTY_NAME NAME
, PARTNER_PARTY.PARTY_TYPE PARTY_TYPE
, PARTNER_PARTY.STATUS B_STATUS
, PARTNER_PARTY.STATUS STATUS
, SYSDATE START_DATE_ACTIVE
, DECODE(PARTNER_PARTY.STATUS
, 'I'
, SYSDATE
, 'D'
, SYSDATE) END_DATE_ACTIVE
, PARTNER_PARTY.COUNTRY COUNTRY
, PARTNER_PARTY.ADDRESS1 ADDRESS1
, PARTNER_PARTY.ADDRESS2 ADDRESS2
, PARTNER_PARTY.ADDRESS3 ADDRESS3
, PARTNER_PARTY.ADDRESS4 ADDRESS4
, PARTNER_PARTY.CITY CITY
, PARTNER_PARTY.POSTAL_CODE POSTAL_CODE
, PARTNER_PARTY.STATE STATE
, PARTNER_PARTY.PROVINCE PROVINCE
, PARTNER_PARTY.COUNTY COUNTY
FROM HZ_PARTIES PARTNER_PARTY
WHERE PARTNER_PARTY.PARTY_ID IN (SELECT PVPP.PARTNER_PARTY_ID
FROM PV_PARTNER_PROFILES PVPP
, HZ_RELATIONSHIPS HZR
, HZ_ORGANIZATION_PROFILES HZOP
, HZ_PARTIES VENDOR_PARTY
WHERE PVPP.PARTNER_ID = HZR.PARTY_ID
AND PVPP.INDIRECTLY_MANAGED_FLAG = 'N'
AND HZR.RELATIONSHIP_TYPE = 'PARTNER'
AND HZR.START_DATE <= SYSDATE
AND NVL(HZR.END_DATE
, SYSDATE) >= SYSDATE
AND HZR.OBJECT_ID = HZOP.PARTY_ID
AND HZOP.INTERNAL_FLAG = 'Y'
AND HZOP.EFFECTIVE_END_DATE IS NULL
AND HZOP.PARTY_ID = VENDOR_PARTY.PARTY_ID
AND VENDOR_PARTY.STATUS= 'A')
AND PARTNER_PARTY.STATUS = 'A'
Columns
Name |
ID1 |
ID2 |
DESCRIPTION |
PARTY_NUMBER |
NAME |
PARTY_TYPE |
B_STATUS |
STATUS |
START_DATE_ACTIVE |
END_DATE_ACTIVE |
COUNTRY |
ADDRESS1 |
ADDRESS2 |
ADDRESS3 |
ADDRESS4 |
CITY |
POSTAL_CODE |
STATE |
PROVINCE |
COUNTY |