VIEW: APPS.IGW_PI_V

Source

View Text - Preformatted

SELECT PA.PROPOSAL_ID, PA.PROPOSAL_NUMBER, PA.LEAD_ORGANIZATION_ID, pa.PROPOSAL_TYPE_code, PA.ORIGINAL_PROPOSAL_NUMBER, PA.AWARD_NUMBER, AP.PERSON_ID, AP.FULL_NAME, AP.LAST_NAME, AP.FIRST_NAME, AP.MIDDLE_NAMES, AP.TITLE, AP.SUFFIX, AP.LAST_NAME||decode(AP.FIRST_NAME,NULL,NULL,', ' ||AP.FIRST_NAME)||DECODE(AP.MIDDLE_NAMES,NULL,NULL,' ' ||AP.MIDDLE_NAMES), AP.FIRST_NAME||DECODE(AP.MIDDLE_NAMES,NULL,NULL,' ' ||MIDDLE_NAMES)||' '||AP.LAST_NAME||DECODE(SUFFIX,NULL,NULL,' ' ||SUFFIX), AP.DATE_OF_BIRTH, AP.NATIONAL_IDENTIFIER, AP.SEX, HL.MEANING, IGW_report_processing.GET_JOB_name(AP.PERSON_ID), IGW_report_processing.GET_PERSON_DEGREES(PP.PERSON_PARTY_ID, AP.PERSON_ID,PA.proposal_id ), LOC.ADDRESS_LINE_1, LOC.ADDRESS_LINE_2, LOC.ADDRESS_LINE_3, LOC.POSTAL_CODE, LOC.REGION_1, LOC.REGION_2, LOC.REGION_3, LOC.TOWN_OR_CITY, IGW_report_processing.get_phone_number(AP.PERSON_ID,'WF'), IGW_report_processing.get_phone_number(AP.PERSON_ID,'W1'), AP.EMAIL_ADDRESS FROM PER_PEOPLE_X AP, IGW_PROP_PERSONS PP, IGW_PROPOSALS_ALL PA, HR_LOOKUPS HL, PER_ASSIGNMENTS_X ASG, HR_LOCATIONS LOC WHERE PA.PROPOSAL_ID = PP.PROPOSAL_ID AND PP.PI_FLAG = 'Y' AND PP.PERSON_ID = AP.PERSON_ID AND PP.PERSON_ID = ASG.PERSON_ID(+) AND ASG.PRIMARY_FLAG(+) = 'Y' AND ASG.LOCATION_ID = LOC.LOCATION_ID(+) AND HL.LOOKUP_TYPE(+) = 'US_ETHNIC_GROUP' AND HL.LOOKUP_CODE(+) = AP.PER_INFORMATION1

View Text - HTML Formatted

SELECT PA.PROPOSAL_ID
, PA.PROPOSAL_NUMBER
, PA.LEAD_ORGANIZATION_ID
, PA.PROPOSAL_TYPE_CODE
, PA.ORIGINAL_PROPOSAL_NUMBER
, PA.AWARD_NUMBER
, AP.PERSON_ID
, AP.FULL_NAME
, AP.LAST_NAME
, AP.FIRST_NAME
, AP.MIDDLE_NAMES
, AP.TITLE
, AP.SUFFIX
, AP.LAST_NAME||DECODE(AP.FIRST_NAME
, NULL
, NULL
, '
, ' ||AP.FIRST_NAME)||DECODE(AP.MIDDLE_NAMES
, NULL
, NULL
, ' ' ||AP.MIDDLE_NAMES)
, AP.FIRST_NAME||DECODE(AP.MIDDLE_NAMES
, NULL
, NULL
, ' ' ||MIDDLE_NAMES)||' '||AP.LAST_NAME||DECODE(SUFFIX
, NULL
, NULL
, ' ' ||SUFFIX)
, AP.DATE_OF_BIRTH
, AP.NATIONAL_IDENTIFIER
, AP.SEX
, HL.MEANING
, IGW_REPORT_PROCESSING.GET_JOB_NAME(AP.PERSON_ID)
, IGW_REPORT_PROCESSING.GET_PERSON_DEGREES(PP.PERSON_PARTY_ID
, AP.PERSON_ID
, PA.PROPOSAL_ID )
, LOC.ADDRESS_LINE_1
, LOC.ADDRESS_LINE_2
, LOC.ADDRESS_LINE_3
, LOC.POSTAL_CODE
, LOC.REGION_1
, LOC.REGION_2
, LOC.REGION_3
, LOC.TOWN_OR_CITY
, IGW_REPORT_PROCESSING.GET_PHONE_NUMBER(AP.PERSON_ID
, 'WF')
, IGW_REPORT_PROCESSING.GET_PHONE_NUMBER(AP.PERSON_ID
, 'W1')
, AP.EMAIL_ADDRESS
FROM PER_PEOPLE_X AP
, IGW_PROP_PERSONS PP
, IGW_PROPOSALS_ALL PA
, HR_LOOKUPS HL
, PER_ASSIGNMENTS_X ASG
, HR_LOCATIONS LOC
WHERE PA.PROPOSAL_ID = PP.PROPOSAL_ID
AND PP.PI_FLAG = 'Y'
AND PP.PERSON_ID = AP.PERSON_ID
AND PP.PERSON_ID = ASG.PERSON_ID(+)
AND ASG.PRIMARY_FLAG(+) = 'Y'
AND ASG.LOCATION_ID = LOC.LOCATION_ID(+)
AND HL.LOOKUP_TYPE(+) = 'US_ETHNIC_GROUP'
AND HL.LOOKUP_CODE(+) = AP.PER_INFORMATION1