View: IGW_PRPO_PERSON_DETAILS_V

Product:	IGW - Grants Proposal
Description:	View displays details about personnel included in budget lines
Implementation/DBA Data:	APPS.IGW_PRPO_PERSON_DETAILS_V

View Text

SELECT IBC.BUDGET_PERIOD_ID
, PPX.LAST_NAME
, PPX.FIRST_NAME
, PPX.MIDDLE_NAMES
, PPP.PROPOSAL_ROLE_CODE
, DECODE(PPP.PROPOSAL_ROLE_CODE
, 'AM'
, 'PRINCIPAL INVESTIGATOR'
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PROPOSAL_ROLE_CODE)) PROPOSAL_ROLE
, PBPD.PERIOD_TYPE_CODE
, DECODE(PBPD.PERIOD_TYPE_CODE
, 'AP'
, 'ACADEMIC'
, 'CY'
, 'CALENDAR'
, 'SP'
, 'SUMMER') PERIOD_TYPE
, IBC.BUDGET_CATEGORY_CODE
, IBC.BUDGET_CATEGORY
, PBPD.START_DATE PERIOD_BEGINNING_DATE
, PBPD.END_DATE PERIOD_END_DATE
, PBP.APPOINTMENT_TYPE_CODE APPOINTMENT_TYPE
, TO_NUMBER(PBP.APPOINTMENT_TYPE_CODE) * PBPD.PERCENT_EFFORT/100 PERSON_MONTHS
, 'DO' PERCENT_DOLLAR_FLAG
, PBP.CALCULATION_BASE BASE_SALARY
, PBPD.PERCENT_EFFORT
, TO_NUMBER(NULL) FRINGE_BENEFITS
, TO_NUMBER(NULL) FRINGE_PERCENTAGE
, PBPD.LINE_ITEM_ID
, PBPD.SALARY_REQUESTED
, PBP.PROPOSAL_ID
, PBP.PERSON_ID
, PBP.PARTY_ID FROM IGW_BUDGETS PB
, IGW_BUDGET_CATEGORY_V IBC
, IGW_BUDGET_PERSONNEL_DETAILS PBPD
, IGW_BUDGET_PERSONS PBP
, PER_PEOPLE_X PPX
, IGW_PROP_PERSONS PPP
WHERE PB.PROPOSAL_ID = IBC.PROPOSAL_ID AND PB.FINAL_VERSION_FLAG = 'Y' AND IBC.LINE_ITEM_ID = PBPD.LINE_ITEM_ID AND PB.PROPOSAL_ID = PBPD.PROPOSAL_ID AND PB.PROPOSAL_ID = PBPD.PROPOSAL_ID AND PB.VERSION_ID = PBPD.VERSION_ID AND PBPD.PROPOSAL_ID = PBP.PROPOSAL_ID AND PBPD.VERSION_ID = PBP.VERSION_ID AND PBPD.APPOINTMENT_TYPE_CODE = PBP.APPOINTMENT_TYPE_CODE AND PBPD.PERSON_ID = PBP.PERSON_ID AND PBP.PERSON_ID = PPX.PERSON_ID AND PB.PROPOSAL_ID = PPP.PROPOSAL_ID AND PPP.PERSON_ID = PBP.PERSON_ID UNION SELECT IBC.BUDGET_PERIOD_ID
, PPX.LAST_NAME
, PPX.FIRST_NAME
, PPX.MIDDLE_NAMES
, PPP.PROPOSAL_ROLE_CODE
, DECODE(PPP.PROPOSAL_ROLE_CODE
, 'AM'
, 'PRINCIPAL INVESTIGATOR'
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PROPOSAL_ROLE_CODE)) ROLE
, PBPD.PERIOD_TYPE_CODE
, DECODE(PBPD.PERIOD_TYPE_CODE
, 'AP'
, 'ACADEMIC'
, 'CY'
, 'CALENDAR'
, 'SP'
, 'SUMMER') PERIOD_TYPE
, IBC.BUDGET_CATEGORY_CODE
, IBC.BUDGET_CATEGORY
, PBPD.START_DATE PERIOD_BEGINNING_DATE
, PBPD.END_DATE PERIOD_END_DATE
, PBP.APPOINTMENT_TYPE_CODE APPOINTMENT_TYPE
, TO_NUMBER(PBP.APPOINTMENT_TYPE_CODE) * PBPD.PERCENT_EFFORT/100 PERSON_MONTHS
, 'P1' PERCENT_DOLLAR_FLAG
, PBP.CALCULATION_BASE BASE_SALARY
, PBPD.PERCENT_EFFORT
, IGW_BUDGET_INTEGRATION.GET_EB_COST_PERSONNEL(PBPD.BUDGET_PERSONNEL_DETAIL_ID) FRINGE_BENEFITS
, IGW_BUDGET_INTEGRATION.GET_EB_COST_PERSONNEL(PBPD.BUDGET_PERSONNEL_DETAIL_ID)/ DECODE(PBPD.SALARY_REQUESTED
, 0
, 1
, PBPD.SALARY_REQUESTED)* 100 FRINGE_PERCENTAGE
, PBPD.LINE_ITEM_ID
, PBPD.SALARY_REQUESTED
, PBP.PROPOSAL_ID
, PBP.PERSON_ID
, PBP.PARTY_ID FROM IGW_BUDGETS PB
, IGW_BUDGET_CATEGORY_V IBC
, IGW_BUDGET_PERSONNEL_DETAILS PBPD
, IGW_BUDGET_PERSONS PBP
, PER_PEOPLE_X PPX
, IGW_PROP_PERSONS PPP WHERE PB.PROPOSAL_ID = IBC.PROPOSAL_ID AND PB.FINAL_VERSION_FLAG = 'Y' AND IBC.LINE_ITEM_ID = PBPD.LINE_ITEM_ID AND PB.PROPOSAL_ID = PBPD.PROPOSAL_ID AND PB.VERSION_ID = PBPD.VERSION_ID AND PBPD.PROPOSAL_ID = PBP.PROPOSAL_ID AND PBPD.VERSION_ID = PBP.VERSION_ID AND PBPD.APPOINTMENT_TYPE_CODE = PBP.APPOINTMENT_TYPE_CODE AND PBPD.PERSON_ID = PBP.PERSON_ID AND PBP.PERSON_ID = PPX.PERSON_ID AND PB.PROPOSAL_ID = PPP.PROPOSAL_ID AND PPP.PERSON_ID = PBP.PERSON_ID UNION SELECT IBC.BUDGET_PERIOD_ID
, HP.PERSON_LAST_NAME LAST_NAME
, HP.PERSON_FIRST_NAME FIRST_NAME
, HP.PERSON_MIDDLE_NAME MIDDLE_NAMES
, PPP.PROPOSAL_ROLE_CODE
, DECODE(PPP.PROPOSAL_ROLE_CODE
, 'AM'
, 'PRINCIPAL INVESTIGATOR'
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PROPOSAL_ROLE_CODE)) PROPOSAL_ROLE
, PBPD.PERIOD_TYPE_CODE
, DECODE(PBPD.PERIOD_TYPE_CODE
, 'AP'
, 'ACADEMIC'
, 'CY'
, 'CALENDAR'
, 'SP'
, 'SUMMER') PERIOD_TYPE
, IBC.BUDGET_CATEGORY_CODE
, IBC.BUDGET_CATEGORY
, PBPD.START_DATE PERIOD_BEGINNING_DATE
, PBPD.END_DATE PERIOD_END_DATE
, PBP.APPOINTMENT_TYPE_CODE APPOINTMENT_TYPE
, TO_NUMBER(PBP.APPOINTMENT_TYPE_CODE) * PBPD.PERCENT_EFFORT/100 PERSON_MONTHS
, 'DO' PERCENT_DOLLAR_FLAG
, PBP.CALCULATION_BASE BASE_SALARY
, PBPD.PERCENT_EFFORT
, TO_NUMBER(NULL) FRINGE_BENEFITS
, TO_NUMBER(NULL) FRINGE_PERCENTAGE
, PBPD.LINE_ITEM_ID
, PBPD.SALARY_REQUESTED
, PBP.PROPOSAL_ID
, PBP.PERSON_ID
, PBP.PARTY_ID FROM IGW_BUDGETS PB
, IGW_BUDGET_CATEGORY_V IBC
, IGW_BUDGET_PERSONNEL_DETAILS PBPD
, IGW_BUDGET_PERSONS PBP
, HZ_PARTIES HP
, IGW_PROP_PERSONS PPP
WHERE PB.PROPOSAL_ID = IBC.PROPOSAL_ID AND PB.FINAL_VERSION_FLAG = 'Y' AND IBC.LINE_ITEM_ID = PBPD.LINE_ITEM_ID AND PB.PROPOSAL_ID = PBPD.PROPOSAL_ID AND PB.PROPOSAL_ID = PBPD.PROPOSAL_ID AND PB.VERSION_ID = PBPD.VERSION_ID AND PBPD.PROPOSAL_ID = PBP.PROPOSAL_ID AND PBPD.VERSION_ID = PBP.VERSION_ID AND PBPD.APPOINTMENT_TYPE_CODE = PBP.APPOINTMENT_TYPE_CODE AND PBPD.PARTY_ID = PBP.PARTY_ID AND PBP.PARTY_ID = HP.PARTY_ID AND PB.PROPOSAL_ID = PPP.PROPOSAL_ID AND PPP.PERSON_PARTY_ID = PBP.PARTY_ID
AND PBP.PERSON_ID IS NULL UNION SELECT IBC.BUDGET_PERIOD_ID
, HP.PERSON_LAST_NAME LAST_NAME
, HP.PERSON_FIRST_NAME FIRST_NAME
, HP.PERSON_MIDDLE_NAME MIDDLE_NAMES
, PPP.PROPOSAL_ROLE_CODE
, DECODE(PPP.PROPOSAL_ROLE_CODE
, 'AM'
, 'PRINCIPAL INVESTIGATOR'
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PROPOSAL_ROLE_CODE)) ROLE
, PBPD.PERIOD_TYPE_CODE
, DECODE(PBPD.PERIOD_TYPE_CODE
, 'AP'
, 'ACADEMIC'
, 'CY'
, 'CALENDAR'
, 'SP'
, 'SUMMER') PERIOD_TYPE
, IBC.BUDGET_CATEGORY_CODE
, IBC.BUDGET_CATEGORY
, PBPD.START_DATE PERIOD_BEGINNING_DATE
, PBPD.END_DATE PERIOD_END_DATE
, PBP.APPOINTMENT_TYPE_CODE APPOINTMENT_TYPE
, TO_NUMBER(PBP.APPOINTMENT_TYPE_CODE) * PBPD.PERCENT_EFFORT/100 PERSON_MONTHS
, 'P1' PERCENT_DOLLAR_FLAG
, PBP.CALCULATION_BASE BASE_SALARY
, PBPD.PERCENT_EFFORT
, IGW_BUDGET_INTEGRATION.GET_EB_COST_PERSONNEL(PBPD.BUDGET_PERSONNEL_DETAIL_ID) FRINGE_BENEFITS
, IGW_BUDGET_INTEGRATION.GET_EB_COST_PERSONNEL(PBPD.BUDGET_PERSONNEL_DETAIL_ID)/ DECODE(PBPD.SALARY_REQUESTED
, 0
, 1
, PBPD.SALARY_REQUESTED)* 100 FRINGE_PERCENTAGE
, PBPD.LINE_ITEM_ID
, PBPD.SALARY_REQUESTED
, PBP.PROPOSAL_ID
, PBP.PERSON_ID
, PBP.PARTY_ID FROM IGW_BUDGETS PB
, IGW_BUDGET_CATEGORY_V IBC
, IGW_BUDGET_PERSONNEL_DETAILS PBPD
, IGW_BUDGET_PERSONS PBP
, HZ_PARTIES HP
, IGW_PROP_PERSONS PPP WHERE PB.PROPOSAL_ID = IBC.PROPOSAL_ID AND PB.FINAL_VERSION_FLAG = 'Y' AND IBC.LINE_ITEM_ID = PBPD.LINE_ITEM_ID AND PB.PROPOSAL_ID = PBPD.PROPOSAL_ID AND PB.VERSION_ID = PBPD.VERSION_ID AND PBPD.PROPOSAL_ID = PBP.PROPOSAL_ID AND PBPD.VERSION_ID = PBP.VERSION_ID AND PBPD.APPOINTMENT_TYPE_CODE = PBP.APPOINTMENT_TYPE_CODE AND PBPD.PARTY_ID = PBP.PARTY_ID AND PBP.PARTY_ID = HP.PARTY_ID AND PB.PROPOSAL_ID = PPP.PROPOSAL_ID AND PPP.PERSON_PARTY_ID = PBP.PARTY_ID
AND PBP.PERSON_ID IS NULL ORDER BY 1

Columns