GMS_XCC_BUDGET_BALANCE_V

Details

• Schema: FUSION

• Object owner: GMS

• Object type: VIEW

Columns

Name

BUDGET_PERIOD_ID START_DATE AWARD_ID PROJECT_ID FUNDING_SOURCE_ID BUDGET_PERIOD SEGMENT_VALUE1 SEGMENT_VALUE2 SEGMENT_VALUE3 TOP_RESOURCE SEGMENT_VALUE4 SEGMENT_VALUE5 SEGMENT_VALUE6 SEGMENT_VALUE7 PTD_BUDGET_AMOUNT ITD_TOTAL_BUDGET PTD_FUNDS_AVAILABLE_AMOUNT ITD_FUNDS_AVAILABLE_AMOUNT PTD_CONSUMED_AMOUNT ITD_CONSUMED_AMOUNT PTD_REQU_CONSUMED_AMT ITD_REQU_CONSUMED_AMT PTD_PUR_ORDER_CONSUMED_AMT ITD_PUR_ORDER_CONSUMED_AMT PTD_INV_CONSUMED_AMT ITD_INV_CONSUMED_AMT PTD_PROJ_EXP_CONSUMED_AMT ITD_PROJ_EXP_CONSUMED_AMT PTD_RECEIPTS_CONSUMED_AMT ITD_RECEIPTS_CONSUMED_AMT PTD_SPEND_AUTH_CONSUMED_AMT ITD_SPEND_AUTH_CONSUMED_AMT

Query

SQL_Statement

select ARG.budget_period_id, ARG.start_date, ARG.award_id, ARG.project_id, ARG.funding_source_id, ARG.BUDGET_PERIOD, ARG.segment_value1, ARG.segment_value2, ARG.segment_value3, ARG.top_resource, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7, ARG.budget_amount as ptd_budget_amount, sum(ARG.budget_amount) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) AS itd_total_budget, ARG.funds_available_amount as ptd_funds_available_amount, sum(ARG.funds_available_amount) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) as itd_funds_available_amount, ARG.consumed_amount as ptd_consumed_amount, sum(ARG.consumed_amount) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) as itd_consumed_amount, ARG.Requisition_Consumed_AMT as ptd_Requ_Consumed_AMT, sum(ARG.Requisition_Consumed_AMT) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) as itd_Requ_Consumed_AMT, ARG.Purchase_order_Consumed_AMT as ptd_Pur_order_Consumed_AMT, sum(ARG.Purchase_order_Consumed_AMT) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) as itd_Pur_order_Consumed_AMT, ARG.Invoice_Consumed_AMT as ptd_Inv_Consumed_AMT, sum(ARG.Invoice_Consumed_AMT) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) as itd_Inv_Consumed_AMT, ARG.Project_Exp_Consumed_AMT as ptd_Proj_Exp_Consumed_AMT, sum(ARG.Project_Exp_Consumed_AMT) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) as itd_Proj_Exp_Consumed_AMT, ARG.Receipts_Consumed_AMT as ptd_Receipts_Consumed_AMT, sum(ARG.Receipts_Consumed_AMT) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) as itd_Receipts_Consumed_AMT, ARG.SPEND_AUTHORIZE_CONSUMED_AMT as ptd_SPEND_AUTH_CONSUMED_AMT, sum(ARG.SPEND_AUTHORIZE_CONSUMED_AMT) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value3,ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7 ORDER BY ARG.start_date ROWS UNBOUNDED PRECEDING) as itd_SPEND_AUTH_CONSUMED_AMT from ( SELECT byBP.budget_period_id, byBP.start_date, byBP.award_id, byBP.project_id, byBP.xcc_project_id, fs.funding_source_id, byBP.BUDGET_PERIOD, byBP.segment_value1, byBP.segment_value2, byBP.segment_value3, byBP.segment_value4, byBP.segment_value5, byBP.segment_value6, byBP.segment_value7, substr(decode(byBP.segment_value7, NULL, byBP.segment_value3, byBP.segment_value5), INSTRB(decode(byBP.segment_value7, NULL, byBP.segment_value3, byBP.segment_value5),':',1)+1) top_resource, nvl(bybal.budget_amount, 0) budget_amount, NVL(bybal.funds_available_amount,0) funds_available_amount, NVL(bybal.consumed_amount, 0) consumed_amount, NVL(bybal.Requisition_Consumed_AMT,0) Requisition_Consumed_AMT, NVL(bybal.Purchase_order_Consumed_AMT,0) Purchase_order_Consumed_AMT, NVL(bybal.Invoice_Consumed_AMT,0) Invoice_Consumed_AMT, NVL(bybal.Project_Exp_Consumed_AMT,0) Project_Exp_Consumed_AMT, NVL(bybal.Receipts_Consumed_AMT,0) Receipts_Consumed_AMT, NVL(bybal.SPEND_AUTHORIZE_CONSUMED_AMT,0) SPEND_AUTHORIZE_CONSUMED_AMT FROM (SELECT a.budget_period_id, a.start_date, a.award_id, a.project_id, b.project_id as xcc_project_Id, a.budget_period, b.segment_value1, b.segment_value2, b.segment_value3, b.segment_value4, b.segment_value5, b.segment_value6, b.segment_value7 FROM GMS_PROJECT_BUDGET_PERIODS_V a, (SELECT DISTINCT di.segment_value1, di.segment_value2, di.segment_value3, di.segment_value4, di.segment_value5, di.segment_value6, di.segment_value7, di.project_id, di.contract_id, di.internal_budget_type_code FROM XCC_pjo_cb_balances_v di where di.internal_budget_type_code IN ('A_P_TR_FS_TRACK', 'P_TT_LT_TR_LR_FS_TRK_SUM', 'P_TT_LT_TR_LR_FS_TRK_PRD') ) b WHERE a.award_id = b.contract_id(+) and a.project_id = b.project_id(+) ) byBP, XCC_pjo_cb_balances_v bybal, GMS_FUNDING_SOURCES_B fs WHERE byBP.segment_value1 = bybal.segment_value1(+) AND byBP.segment_value2 = bybal.segment_value2(+) AND byBP.segment_value3 = bybal.segment_value3(+) AND byBP.segment_value4 = bybal.segment_value4(+) AND nvl(byBP.segment_value5, 1) = nvl(bybal.segment_value5(+), 1) AND nvl(byBP.segment_value6, 1) = nvl(bybal.segment_value6(+), 1) AND nvl(byBP.segment_value7, 1) = nvl(bybal.segment_value7(+), 1) AND byBP.BUDGET_PERIOD = bybal.PERIOD_NAME(+) and byBP.award_id = bybal.contract_id(+) and byBP.project_id = bybal.project_id(+) and bybal.internal_budget_type_code(+) IN ('A_P_TR_FS_TRACK', 'P_TT_LT_TR_LR_FS_TRK_SUM', 'P_TT_LT_TR_LR_FS_TRK_PRD') and decode(byBP.segment_value7, NULL, byBP.segment_value4, byBP.segment_value7) = fs.funding_source_number order by byBP.start_date) ARG ORDER BY ARG.segment_value1, ARG.segment_value2, ARG.segment_value3, ARG.segment_value4, ARG.segment_value5, ARG.segment_value6, ARG.segment_value7, ARG.start_date