VIEW: APPS.IGW_PRPO_PROP_PERSONS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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