GMS_XCC_P13_BUDGET_BAL_V

Details

• Schema: FUSION

• Object owner: GMS

• Object type: VIEW

Columns

Name

BUDGET_PERIOD_ID START_DATE AWARD_ID PROJECT_ID BUDGET_PERIOD SEGMENT_VALUE1 SEGMENT_VALUE2 TOP_RESOURCE RESOURCE_SEGMENT_EXISTS 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.BUDGET_PERIOD, ARG.segment_value1, ARG.segment_value2, ARG.top_resource, ARG.RESOURCE_SEGMENT_EXISTS, ARG.budget_amount as ptd_budget_amount, sum(ARG.budget_amount) OVER (PARTITION BY ARG.award_id, ARG.project_id, ARG.segment_value1, ARG.segment_value2 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_value1, ARG.segment_value2 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_value1, ARG.segment_value2 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_value1, ARG.segment_value2 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_value1, ARG.segment_value2 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_value1, ARG.segment_value2 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_value1, ARG.segment_value2 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_value1, ARG.segment_value2 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_value1, ARG.segment_value2 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, byBP.BUDGET_PERIOD, byBP.segment_value1, byBP.segment_value2, substr(byBP.segment_value2, INSTRB(byBP.segment_value2,':',1)+1) top_resource, byBP.RESOURCE_SEGMENT_EXISTS, byBP.control_budget_id, bybal.period_name, 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.RESOURCE_SEGMENT_EXISTS, b.control_budget_id FROM GMS_PROJECT_BUDGET_PERIODS_V a, (SELECT DISTINCT di.segment_value1, di.segment_value2, di.RESOURCE_SEGMENT_EXISTS, di.project_id, di.contract_id, di.control_budget_id FROM xcc_pjo_cb_balances_v di, XCC_CONTROL_BUDGETS X WHERE di.internal_budget_type_code IS NULL AND X.PROJECT_ID =di.PROJECT_ID AND X.CONTROL_BUDGET_ID =di.CONTROL_BUDGET_ID AND X.SOUrce_budget_system_code = 'PPM' and X.status_code = 'IN_USE') b WHERE a.project_id = b.project_id(+) ) byBP, xcc_pjo_cb_balances_v bybal WHERE byBP.segment_value1 = bybal.segment_value1(+) AND nvl(byBP.segment_value2, 0) = nvl(bybal.segment_value2(+), 0) AND byBP.BUDGET_PERIOD = bybal.PERIOD_NAME(+) and byBP.control_budget_id = bybal.control_budget_id(+) and byBP.RESOURCE_SEGMENT_EXISTS = bybal.RESOURCE_SEGMENT_EXISTS(+) and bybal.contract_id(+) = -98 and byBP.project_id = bybal.project_id(+) and bybal.internal_budget_type_code(+) is null) ARG ORDER BY ARG.segment_value1, ARG.segment_value2, ARG.start_date