BIP – Query to extract Person Address Details

select

distinct

PAPF.PERSON_NUMBER,

PAPF.PERSON_ID,

PPAUF.PERSON_ADDR_USAGE_ID "ADDRESS_ID",

TO_CHAR(PA.EFFECTIVE_START_DATE,'YYYY/MM/DD')"EFFECTIVE_START_DATE",

TO_CHAR(PA.EFFECTIVE_END_DATE,'YYYY/MM/DD')"EFFECTIVE_END_DATE",

PPAUF.ADDRESS_TYPE,

PA.ADDRESS_LINE_1,

PA.ADDRESS_LINE_2,

PA.ADDRESS_LINE_3

from

PER_ADDRESSES_F PA,

PER_ALL_PEOPLE_F PAPF,

PER_PERSON_ADDR_USAGES_F PPAUF

where

  PAPF.PERSON_ID = PPAUF.PERSON_ID(+)

AND PPAUF.ADDRESS_ID = PA.ADDRESS_ID(+)