VIEW: APPS.IGW_PROPOSAL_PERSONS_V

Source

View Text - Preformatted

SELECT ipa.proposal_id, ipa.proposal_number, hp.person_identifier, ipp.person_party_id, ipp.proposal_role_code, ppx.first_name, ppx.middle_names, ppx.last_name, ppx.full_name, ppx.title, ppx.suffix, ppx.last_name ||decode(ppx.first_name,null,null,', ' ||ppx.first_name)||decode(ppx.middle_names,null,null,' ' ||ppx.middle_names), ppx.first_name||decode(ppx.middle_names,null,null,' ' ||ppx.middle_names)||' ' ||ppx.last_name ||DECODE(ppx.suffix,null,null,' '||ppx.suffix), 'org_party_name', 'proposal_role', 'person_degrees', 'job_name', ppx.date_of_birth, ppx.national_identifier, decode(ipa.proposal_manager_id, ipp.person_party_id, 'Y', 'N'), ipp.key_person_flag, 10, ppx.sex, ppx.email_address, '1800', '1800', hl.address_line_1, hl.address_line_2, hl.address_line_3, hl.town_or_city, hl.region_2, hl.postal_code from igw_proposals_all ipa, igw_prop_persons ipp, hz_parties hp, per_people_x ppx, per_assignments_x pax, hr_locations hl where ipp.proposal_id = ipa.proposal_id and ipp.person_party_id = hp.party_id and hp.party_type = 'PERSON' and hp.status = 'A' and hp.created_by_module = 'HR API' and ppx.person_id = hp.person_identifier and pax.person_id(+) = ppx.person_id and pax.primary_flag(+) = 'Y' and pax.location_id = hl.location_id(+) UNION SELECT ipa.proposal_id , ipa.proposal_number , null, hpp.party_id person_party_id , ipp.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), 'org_party_name', 'role', 'degrees','job_title', hpp.date_of_Birth , hpp.jgzz_fiscal_code, decode(ipa.proposal_manager_id, ipp.person_party_id, 'Y', 'N'), key_person_flag , 10, decode(hpp.gender, 'MALE', 'M', 'FEMALE', 'F'), hp.email_address, hp.primary_phone_area_code || hp.primary_phone_number, '1800', hp.address1, hp.address2, hp.address3, hp.city, hp.state, hp.postal_code FROM igw_proposals_all ipa , igw_prop_persons ipp , hz_parties hp, hz_person_profiles hpp where ipp.proposal_id = ipa.proposal_id and ipp.person_party_id = hp.party_id and hp.party_type = 'PERSON' and hp.status = 'A' and hp.created_by_module <> 'HR API' AND hp.party_id = hpp.party_id AND hpp.effective_end_date is null

View Text - HTML Formatted

SELECT IPA.PROPOSAL_ID
, IPA.PROPOSAL_NUMBER
, HP.PERSON_IDENTIFIER
, IPP.PERSON_PARTY_ID
, IPP.PROPOSAL_ROLE_CODE
, PPX.FIRST_NAME
, PPX.MIDDLE_NAMES
, PPX.LAST_NAME
, PPX.FULL_NAME
, PPX.TITLE
, PPX.SUFFIX
, PPX.LAST_NAME ||DECODE(PPX.FIRST_NAME
, NULL
, NULL
, '
, ' ||PPX.FIRST_NAME)||DECODE(PPX.MIDDLE_NAMES
, NULL
, NULL
, ' ' ||PPX.MIDDLE_NAMES)
, PPX.FIRST_NAME||DECODE(PPX.MIDDLE_NAMES
, NULL
, NULL
, ' ' ||PPX.MIDDLE_NAMES)||' ' ||PPX.LAST_NAME ||DECODE(PPX.SUFFIX
, NULL
, NULL
, ' '||PPX.SUFFIX)
, 'ORG_PARTY_NAME'
, 'PROPOSAL_ROLE'
, 'PERSON_DEGREES'
, 'JOB_NAME'
, PPX.DATE_OF_BIRTH
, PPX.NATIONAL_IDENTIFIER
, DECODE(IPA.PROPOSAL_MANAGER_ID
, IPP.PERSON_PARTY_ID
, 'Y'
, 'N')
, IPP.KEY_PERSON_FLAG
, 10
, PPX.SEX
, PPX.EMAIL_ADDRESS
, '1800'
, '1800'
, HL.ADDRESS_LINE_1
, HL.ADDRESS_LINE_2
, HL.ADDRESS_LINE_3
, HL.TOWN_OR_CITY
, HL.REGION_2
, HL.POSTAL_CODE
FROM IGW_PROPOSALS_ALL IPA
, IGW_PROP_PERSONS IPP
, HZ_PARTIES HP
, PER_PEOPLE_X PPX
, PER_ASSIGNMENTS_X PAX
, HR_LOCATIONS HL
WHERE IPP.PROPOSAL_ID = IPA.PROPOSAL_ID
AND IPP.PERSON_PARTY_ID = HP.PARTY_ID
AND HP.PARTY_TYPE = 'PERSON'
AND HP.STATUS = 'A'
AND HP.CREATED_BY_MODULE = 'HR API'
AND PPX.PERSON_ID = HP.PERSON_IDENTIFIER
AND PAX.PERSON_ID(+) = PPX.PERSON_ID
AND PAX.PRIMARY_FLAG(+) = 'Y'
AND PAX.LOCATION_ID = HL.LOCATION_ID(+) UNION SELECT IPA.PROPOSAL_ID
, IPA.PROPOSAL_NUMBER
, NULL
, HPP.PARTY_ID PERSON_PARTY_ID
, IPP.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)
, 'ORG_PARTY_NAME'
, 'ROLE'
, 'DEGREES'
, 'JOB_TITLE'
, HPP.DATE_OF_BIRTH
, HPP.JGZZ_FISCAL_CODE
, DECODE(IPA.PROPOSAL_MANAGER_ID
, IPP.PERSON_PARTY_ID
, 'Y'
, 'N')
, KEY_PERSON_FLAG
, 10
, DECODE(HPP.GENDER
, 'MALE'
, 'M'
, 'FEMALE'
, 'F')
, HP.EMAIL_ADDRESS
, HP.PRIMARY_PHONE_AREA_CODE || HP.PRIMARY_PHONE_NUMBER
, '1800'
, HP.ADDRESS1
, HP.ADDRESS2
, HP.ADDRESS3
, HP.CITY
, HP.STATE
, HP.POSTAL_CODE
FROM IGW_PROPOSALS_ALL IPA
, IGW_PROP_PERSONS IPP
, HZ_PARTIES HP
, HZ_PERSON_PROFILES HPP
WHERE IPP.PROPOSAL_ID = IPA.PROPOSAL_ID
AND IPP.PERSON_PARTY_ID = HP.PARTY_ID
AND HP.PARTY_TYPE = 'PERSON'
AND HP.STATUS = 'A'
AND HP.CREATED_BY_MODULE <> 'HR API'
AND HP.PARTY_ID = HPP.PARTY_ID
AND HPP.EFFECTIVE_END_DATE IS NULL