VIEW: APPS.IGW_REPORT_BUDGET_PERSONNEL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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