View: IGW_PRPO_PROP_PERSONS_V

Product:	IGW - Grants Proposal
Description:	View displays a person's biographical information
Implementation/DBA Data:	APPS.IGW_PRPO_PROP_PERSONS_V

View Text

SELECT PPP.PROPOSAL_ID
, PPP.PERSON_ID
, PPP.PERSON_PARTY_ID PARTY_ID
, PPX.FIRST_NAME
, PPX.MIDDLE_NAMES
, PPX.LAST_NAME
, PPX.FULL_NAME
, PPX.TITLE
, PPX.PRE_NAME_ADJUNCT
, PPX.SUFFIX
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PPP.PROPOSAL_ROLE_CODE)
, IGW_REPORT_PROCESSING.GET_JOB_NAME(PPP.PERSON_ID)
, PPX.SEX
, PPX.DATE_OF_BIRTH
, PPX.NATIONAL_IDENTIFIER SSN
, LKUP1.MEANING
, LKUP2.MEANING
, PPP.KEY_PERSON_FLAG
, DECODE(PI_FLAG
, 'Y'
, '9P'
, DECODE(KEY_PERSON_FLAG
, 'Y'
, '9K'
, DECODE(PROPOSAL_ROLE_CODE
, '9S'
, '9S'
, NULL)))
, PPP.PERSON_SEQUENCE
, PPP.PERCENT_EFFORT
, AD.ADDRESS_LINE1
, AD.ADDRESS_LINE2
, AD.ADDRESS_LINE3
, AD.POSTAL_CODE
, AD.REGION_1
, AD.REGION_2
, AD.TOWN_OR_CITY
, AD.COUNTRY
, IGW_REPORT_PROCESSING.GET_PHONE_NUMBER(PPP.PERSON_ID
, 'WF')
, IGW_REPORT_PROCESSING.GET_PHONE_NUMBER(PPP.PERSON_ID
, 'W1')
, PPX.EMAIL_ADDRESS
, IGW_EXTENSION_PKG.PERSON_GOVT_ID(PPP.PERSON_ID)
FROM IGW_PROP_PERSONS PPP
, PER_PEOPLE_X PPX
, PER_ADDRESSES AD
, FND_LOOKUP_VALUES LKUP1
, FND_LOOKUP_VALUES LKUP2
WHERE PPX.PERSON_ID = PPP.PERSON_ID
AND PPP.PERSON_ID = AD.PERSON_ID (+)
AND AD.PRIMARY_FLAG(+) = 'Y'
AND AD.DATE_TO IS NULL
AND LKUP1.LOOKUP_CODE(+) = PPX.NATIONALITY
AND LKUP1.LOOKUP_TYPE(+) = 'NATIONALITY'
AND LKUP1.LANGUAGE(+) = USERENV('LANG')
AND NVL(LKUP1.ENABLED_FLAG
, 'Y') = 'Y'
AND NVL(LKUP1.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND (LKUP1.END_DATE_ACTIVE IS NULL OR LKUP1.END_DATE_ACTIVE >= SYSDATE)
AND LKUP2.LOOKUP_CODE(+) = PPX.PER_INFORMATION1
AND LKUP2.LOOKUP_TYPE(+) = 'US_ETHNIC_GROUP'
AND LKUP2.LANGUAGE(+) = USERENV('LANG')
AND NVL(LKUP2.ENABLED_FLAG
, 'Y') = 'Y'
AND NVL(LKUP2.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND (LKUP2.END_DATE_ACTIVE IS NULL OR LKUP2.END_DATE_ACTIVE >= SYSDATE) UNION SELECT PPP.PROPOSAL_ID
, PPP.PERSON_ID
, PPP.PERSON_PARTY_ID PARTY_ID
, HP.PERSON_FIRST_NAME FIRST_NAME
, HP.PERSON_MIDDLE_NAME MIDDLE_NAMES
, HP.PERSON_LAST_NAME LAST_NAME
, HP.PARTY_NAME FULL_NAME
, HP.PERSON_TITLE TITLE
, HP.PERSON_PRE_NAME_ADJUNCT PREFIX
, HP.PERSON_NAME_SUFFIX SUFFIX
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PPP.PROPOSAL_ROLE_CODE)
, NULL JOB_NAME
, HPF.GENDER SEX
, HPF.DATE_OF_BIRTH DATE_OF_BIRTH
, HP.JGZZ_FISCAL_CODE SSN
, NULL CITIZENSHIP
, NULL RACE
, PPP.KEY_PERSON_FLAG
, DECODE(PI_FLAG
, 'Y'
, '9P'
, DECODE(KEY_PERSON_FLAG
, 'Y'
, '9K'
, DECODE(PROPOSAL_ROLE_CODE
, '9S'
, '9S'
, NULL)))
, PPP.PERSON_SEQUENCE
, PPP.PERCENT_EFFORT
, HP.ADDRESS1 ADDRESS_LINE1
, HP.ADDRESS2 ADDRESS_LINE2
, HP.ADDRESS3 ADDRESS_LINE3
, HP.POSTAL_CODE
, HP.COUNTY
, HP.STATE
, HP.CITY TOWN_OR_CITY
, HP.COUNTRY
, NULL WORK_PHONE
, NULL WORK_FAX
, HP.EMAIL_ADDRESS
, NULL PERSON_GOVT_ID
FROM IGW_PROP_PERSONS PPP
, HZ_PARTIES HP
, HZ_PERSON_PROFILES HPF
WHERE PPP.PERSON_PARTY_ID= HP.PARTY_ID
AND PPP.PERSON_ID IS NULL
AND HP.PARTY_ID = HPF.PARTY_ID
AND HPF.EFFECTIVE_END_DATE IS NULL ORDER BY 20

Columns