GMS_AWARD_FUNDING_BY_PERIOD_V

Details

• Schema: FUSION

• Object owner: GMS

• Object type: VIEW

Columns

Name
AWARD_ID BUDGET_PERIOD_ID START_DATE END_DATE PTD_LC_FUND_AMOUNT ITD_LC_FUND_AMOUNT PTD_FUND_AMOUNT ITD_FUND_AMOUNT PTD_LC_FUND_ALLOCATE_AMOUNT ITD_LC_FUND_ALLOCATE_AMOUNT PTD_FUND_ALLOCATE_AMOUNT ITD_FUND_ALLOCATE_AMOUNT

Query

SQL_Statement

select T.AWARD_ID, T.id as BUDGET_PERIOD_ID, T.START_DATE, T.END_DATE, F.PTD_LC_FUND_AMOUNT as PTD_LC_FUND_AMOUNT, SUM(F.PTD_LC_FUND_AMOUNT) over (partition by F.AWARD_ID order by T.START_DATE rows unbounded preceding) as ITD_LC_FUND_AMOUNT, F.PTD_FUND_AMOUNT as PTD_FUND_AMOUNT, SUM(F.PTD_FUND_AMOUNT) over (partition by F.AWARD_ID order by T.START_DATE rows unbounded preceding) as ITD_FUND_AMOUNT, G.PTD_LC_FUND_ALLOCATE_AMOUNT as PTD_LC_FUND_ALLOCATE_AMOUNT, SUM(G.PTD_LC_FUND_ALLOCATE_AMOUNT) over (partition by G.AWARD_ID order by T.START_DATE rows unbounded preceding) as ITD_LC_FUND_ALLOCATE_AMOUNT, G.PTD_FUND_ALLOCATE_AMOUNT as PTD_FUND_ALLOCATE_AMOUNT, SUM(G.PTD_FUND_ALLOCATE_AMOUNT) over (partition by G.AWARD_ID order by T.START_DATE rows unbounded preceding) as ITD_FUND_ALLOCATE_AMOUNT from (select a.AWARD_ID, SUM(a.LC_DIRECT_FUNDING_AMOUNT + a.LC_INDIRECT_FUNDING_AMOUNT) PTD_LC_FUND_AMOUNT, SUM(a.DIRECT_FUNDING_AMOUNT + a.INDIRECT_FUNDING_AMOUNT) PTD_FUND_AMOUNT, a.BUDGET_PERIOD_ID from (SELECT A.ID AS BUDGET_PERIOD_ID, A.AWARD_ID, A.BUDGET_PERIOD, A.START_DATE, A.END_DATE, NVL(B.LC_DIRECT_FUNDING_AMOUNT, 0) AS LC_DIRECT_FUNDING_AMOUNT, NVL(B.LC_INDIRECT_FUNDING_AMOUNT, 0) AS LC_INDIRECT_FUNDING_AMOUNT, NVL(B.DIRECT_FUNDING_AMOUNT, 0) AS DIRECT_FUNDING_AMOUNT, NVL(B.INDIRECT_FUNDING_AMOUNT, 0) AS INDIRECT_FUNDING_AMOUNT, NVL(B.ID, 0) AS FUNDING_ID FROM GMS_AWARD_BUDGET_PERIODS A, GMS_AWARD_FUNDINGS_B B WHERE A.ID = B.BUDGET_PERIOD_ID(+)) a group by a.AWARD_ID, a.BUDGET_PERIOD_ID ) F, (select B2.AWARD_ID, SUM(A2.LC_FUNDING_AMOUNT) PTD_LC_FUND_ALLOCATE_AMOUNT, SUM(A2.FUNDING_AMOUNT) PTD_FUND_ALLOCATE_AMOUNT, B2.BUDGET_PERIOD_ID, B2.START_DATE, B2.END_DATE from GMS_AWARD_FUND_ALLOCATIONS A2, (SELECT A.ID AS BUDGET_PERIOD_ID, A.AWARD_ID, A.BUDGET_PERIOD, A.START_DATE, A.END_DATE, NVL(B.LC_DIRECT_FUNDING_AMOUNT, 0) AS LC_DIRECT_FUNDING_AMOUNT, NVL(B.LC_INDIRECT_FUNDING_AMOUNT, 0) AS LC_INDIRECT_FUNDING_AMOUNT, NVL(B.DIRECT_FUNDING_AMOUNT, 0) AS DIRECT_FUNDING_AMOUNT, NVL(B.INDIRECT_FUNDING_AMOUNT, 0) AS INDIRECT_FUNDING_AMOUNT, NVL(B.ID, 0) AS FUNDING_ID FROM GMS_AWARD_BUDGET_PERIODS A, GMS_AWARD_FUNDINGS_B B WHERE A.ID = B.BUDGET_PERIOD_ID(+)) B2 where A2.FUNDING_ID(+) = B2.FUNDING_ID group by B2.AWARD_ID, B2.BUDGET_PERIOD_ID, B2.START_DATE, B2.END_DATE ) G, GMS_AWARD_BUDGET_PERIODS T where F.BUDGET_PERIOD_ID = T.id and G.BUDGET_PERIOD_ID = T.id order by T.AWARD_ID, T.START_DATE