Product: | IGW - Grants Proposal (Obsolete) |
---|---|
Description: | View to display personnel and their information for the first budget period |
Implementation/DBA Data: | Not implemented in this database |
SELECT SUBSTR(HZ.PERSON_LAST_NAME||DECODE(HZ.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '||HZ.PERSON_FIRST_NAME)|| DECODE(HZ.PERSON_MIDDLE_NAME
, NULL
, NULL
, '
, '||HZ.PERSON_MIDDLE_NAME)
, 1
, 50) FULL_NAME
, PPP.PROPOSAL_ROLE_CODE
, PPP.KEY_PERSON_FLAG
, DECODE(PPP.PROPOSAL_ROLE_CODE
, 'AM'
, 'PRINCIPAL INVESTIGATOR'
, IGW_REPORT_PROCESSING.GET_PROPOSAL_ROLE(PROPOSAL_ROLE_CODE)) ROLE
, PBP.APPOINTMENT_TYPE_CODE APPOINTMENT_TYPE
, PBPD.PERCENT_EFFORT
, PBP.CALCULATION_BASE BASE_SALARY
, PBPD.LINE_ITEM_ID
, PBPD.BUDGET_PERSONNEL_DETAIL_ID
, MONTHS_BETWEEN(PBPD.END_DATE+1
, PBPD.START_DATE) MONTHS_WORKED
, ROUND( PBPD.SALARY_REQUESTED)
, ROUND( IGW_BUDGET_INTEGRATION.GET_EB_COST_PERSONNEL(BUDGET_PERSONNEL_DETAIL_ID)) FRINGE_BENEFITS
, ROUND(SALARY_REQUESTED) + ROUND( IGW_BUDGET_INTEGRATION.GET_EB_COST_PERSONNEL(BUDGET_PERSONNEL_DETAIL_ID)) TOTAL
, PBP.PROPOSAL_ID
, PBP.VERSION_ID
, PBP.PERSON_ID
, PBP.PARTY_ID
, PPP.PI_FLAG
, PPP.PERSON_SEQUENCE
FROM IGW_BUDGETS PB
, IGW_BUDGET_PERSONNEL_DETAILS PBPD
, IGW_BUDGET_PERSONS PBP
, HZ_PARTIES HZ
, IGW_PROP_PERSONS PPP
WHERE PB.PROPOSAL_ID = PBPD.PROPOSAL_ID
AND PB.FINAL_VERSION_FLAG = 'Y'
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 = HZ.PARTY_ID
AND PPP.PERSON_PARTY_ID = PBP.PARTY_ID
AND PPP.PROPOSAL_ID = IGW_REPORT_PROCESSING.GET_PROPOSAL_ID
AND PBPD.PROPOSAL_ID = IGW_REPORT_PROCESSING.GET_PROPOSAL_ID
AND PBPD.BUDGET_PERIOD_ID = IGW_REPORT_PROCESSING.GET_PERIOD_ID