VIEW: APPS.IGS_PE_SS_ADDR_V

Source

View Text - Preformatted

SELECT ps.party_id person_id, ps.party_site_id party_site_id, ps.location_id location_id, igsps.start_date start_dt, igsps.end_date end_dt, l.address1 addr_line_1, l.address2 addr_line_2, l.address3 addr_line_3, l.address4 addr_line_4, l.city city, l.state state, l.province province, l.county county, l.country country_cd, l.address_style, l.address_lines_phonetic, l.delivery_point_code, ft.territory_short_name country, l.postal_code postal_code, hu.site_use_type site_use_type, to_date(NULL) date_last_verified, ps.identifying_address_flag correspondence, NULL other_details_1, NULL other_details_2, NULL other_details_3, ps.created_by created_by, ps.creation_date creation_date, ps.last_updated_by last_updated_by, l.last_update_date last_update_date, ps.last_update_login last_update_login, ps.request_id request_id, ps.program_application_id program_application_id, ps.program_id program_id, ps.program_update_date program_update_date, ps.status,lk.meaning, l.object_version_number location_ovn, ps.object_version_number party_site_ovn FROM hz_party_sites ps, hz_locations l, hz_party_site_uses hu, fnd_territories_vl ft, igs_pe_hz_pty_sites igsps, fnd_lookup_values lk WHERE ps.location_id = l.location_id AND hu.party_site_id = ps.party_site_id AND ft.territory_code = l.country AND ps.party_site_id = igsps.party_site_id(+) AND ps.status in ('A', 'I') AND ps.status = lk.lookup_code AND lk.lookup_type = 'REGISTRY_STATUS' AND lk.view_application_id = '222' AND lk.language = userenv('LANG') and lk.security_group_id = 0

View Text - HTML Formatted

SELECT PS.PARTY_ID PERSON_ID
, PS.PARTY_SITE_ID PARTY_SITE_ID
, PS.LOCATION_ID LOCATION_ID
, IGSPS.START_DATE START_DT
, IGSPS.END_DATE END_DT
, L.ADDRESS1 ADDR_LINE_1
, L.ADDRESS2 ADDR_LINE_2
, L.ADDRESS3 ADDR_LINE_3
, L.ADDRESS4 ADDR_LINE_4
, L.CITY CITY
, L.STATE STATE
, L.PROVINCE PROVINCE
, L.COUNTY COUNTY
, L.COUNTRY COUNTRY_CD
, L.ADDRESS_STYLE
, L.ADDRESS_LINES_PHONETIC
, L.DELIVERY_POINT_CODE
, FT.TERRITORY_SHORT_NAME COUNTRY
, L.POSTAL_CODE POSTAL_CODE
, HU.SITE_USE_TYPE SITE_USE_TYPE
, TO_DATE(NULL) DATE_LAST_VERIFIED
, PS.IDENTIFYING_ADDRESS_FLAG CORRESPONDENCE
, NULL OTHER_DETAILS_1
, NULL OTHER_DETAILS_2
, NULL OTHER_DETAILS_3
, PS.CREATED_BY CREATED_BY
, PS.CREATION_DATE CREATION_DATE
, PS.LAST_UPDATED_BY LAST_UPDATED_BY
, L.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS.REQUEST_ID REQUEST_ID
, PS.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PS.PROGRAM_ID PROGRAM_ID
, PS.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, PS.STATUS
, LK.MEANING
, L.OBJECT_VERSION_NUMBER LOCATION_OVN
, PS.OBJECT_VERSION_NUMBER PARTY_SITE_OVN
FROM HZ_PARTY_SITES PS
, HZ_LOCATIONS L
, HZ_PARTY_SITE_USES HU
, FND_TERRITORIES_VL FT
, IGS_PE_HZ_PTY_SITES IGSPS
, FND_LOOKUP_VALUES LK
WHERE PS.LOCATION_ID = L.LOCATION_ID
AND HU.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND FT.TERRITORY_CODE = L.COUNTRY
AND PS.PARTY_SITE_ID = IGSPS.PARTY_SITE_ID(+)
AND PS.STATUS IN ('A'
, 'I')
AND PS.STATUS = LK.LOOKUP_CODE
AND LK.LOOKUP_TYPE = 'REGISTRY_STATUS'
AND LK.VIEW_APPLICATION_ID = '222'
AND LK.LANGUAGE = USERENV('LANG')
AND LK.SECURITY_GROUP_ID = 0