VIEW: APPS.PV_PARTNERS_LOV_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'