View: IGW_PROPOSAL_PERSONS_V

Product:	IGW - Grants Proposal
Description:	View displays details of personnel involved in the proposal
Implementation/DBA Data:	APPS.IGW_PROPOSAL_PERSONS_V

View Text

SELECT PPA.PROPOSAL_ID
, PPA.PROPOSAL_NUMBER
, PPP.PERSON_ID
, PPP.PERSON_PARTY_ID
, PPP.PERSON_SEQUENCE
, PPP.PROPOSAL_ROLE_CODE
, PAPF.FIRST_NAME
, PAPF.MIDDLE_NAMES
, PAPF.LAST_NAME
, PAPF.FULL_NAME
, PAPF.TITLE
, PAPF.SUFFIX
, PAPF.LAST_NAME||DECODE(PAPF.FIRST_NAME
, NULL
, NULL
, '
, ' ||PAPF.FIRST_NAME)||DECODE(PAPF.MIDDLE_NAMES
, NULL
, NULL
, ' ' ||PAPF.MIDDLE_NAMES)
, PAPF.FIRST_NAME||DECODE(PAPF.MIDDLE_NAMES
, NULL
, NULL
, ' ' ||PAPF.MIDDLE_NAMES)||' ' ||PAPF.LAST_NAME||DECODE(PAPF.SUFFIX
, NULL
, NULL
, ' '||PAPF.SUFFIX)
, HAOU.NAME
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PPP.PROPOSAL_ROLE_CODE)
, IGW_REPORT_PROCESSING.GET_PERSON_DEGREES(PPP.PERSON_ID
, PPP.PERSON_PARTY_ID
, PPA.PROPOSAL_ID)
, IGW_REPORT_PROCESSING.GET_JOB_NAME(PPP.PERSON_ID)
, PAPF.DATE_OF_BIRTH
, PAPF.NATIONAL_IDENTIFIER SSN
, PPP.PI_FLAG
, PPP.KEY_PERSON_FLAG
, PPP.PERCENT_EFFORT
FROM HR_ALL_ORGANIZATION_UNITS HAOU
, IGW_PROP_PERSONS PPP
, PER_PEOPLE_X PAPF
, IGW_PROPOSALS_ALL PPA
WHERE HAOU.ORGANIZATION_ID(+) = PPP.PERSON_ORGANIZATION_ID
AND PPP.PROPOSAL_ID = PPA.PROPOSAL_ID
AND PAPF.PERSON_ID = PPP.PERSON_ID UNION SELECT PPA.PROPOSAL_ID
, PPA.PROPOSAL_NUMBER
, PPP.PERSON_ID
, HPP.PARTY_ID PERSON_PARTY_ID
, PPP.PERSON_SEQUENCE
, PPP.PROPOSAL_ROLE_CODE
, HPP.PERSON_FIRST_NAME FIRST_NAME
, HPP.PERSON_MIDDLE_NAME MIDDLE_NAMES
, HPP.PERSON_LAST_NAME LAST_NAME
, HPP.PERSON_NAME FULL_NAME
, HPP.PERSON_PRE_NAME_ADJUNCT TITLE
, HPP.PERSON_NAME_SUFFIX SUFFIX
, HPP.PERSON_LAST_NAME||DECODE(HPP.PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' ||HPP.PERSON_FIRST_NAME)||DECODE(HPP.PERSON_MIDDLE_NAME
, NULL
, NULL
, '
, ' ||HPP.PERSON_MIDDLE_NAME)
, HPP.PERSON_FIRST_NAME||DECODE(HPP.PERSON_MIDDLE_NAME
, NULL
, NULL
, ' '||HPP.PERSON_MIDDLE_NAME)||''||HPP.PERSON_LAST_NAME||DECODE(HPP.PERSON_NAME_SUFFIX
, NULL
, NULL
, ' ' ||HPP.PERSON_NAME_SUFFIX)
, IGW_REPORT_PROCESSING.GET_ORG_PARTY_NAME(PPP.ORG_PARTY_ID
, PPP.PERSON_ORGANIZATION_ID) NAME
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PPP.PROPOSAL_ROLE_CODE)
, IGW_REPORT_PROCESSING.GET_PERSON_DEGREES(PPP.PERSON_ID
, HPP.PARTY_ID
, PPA.PROPOSAL_ID)
, HPP.PERSON_TITLE
, HPP.DATE_OF_BIRTH
, HPP.JGZZ_FISCAL_CODE SSN
, 'N' PI_FLAG
, KEY_PERSON_FLAG
, PPP.PERCENT_EFFORT
FROM IGW_PROPOSALS_ALL PPA
, IGW_PROP_PERSONS PPP
, HZ_PARTIES HP
, HZ_PERSON_PROFILES HPP
WHERE PPA.PROPOSAL_ID = PPP.PROPOSAL_ID
AND PPP.PERSON_ID IS NULL
AND PPP.PERSON_PARTY_ID = HP.PARTY_ID
AND HP.PARTY_TYPE = 'PERSON'
AND HP.STATUS = 'A'
AND HP.PARTY_ID = HPP.PARTY_ID
AND HPP.EFFECTIVE_END_DATE IS NULL

Columns