XCC_PJO_CB_BALANCES

Details

Schema: FUSION
Object owner: XCC
Object type: VIEW

Columns

Name
CONTROL_BUDGET_ID PERIOD_NAME CONTROL_BUDGET_NAME CONTROL_LEVEL_CODE LEDGER_ID PROJECT_ID CONTRACT_ID BUDGET_AMOUNT FUNDS_AVAILABLE_AMOUNT CONSUMED_AMOUNT REQUISITION_CONSUMED_AMT PURCHASE_ORDER_CONSUMED_AMT INVOICE_CONSUMED_AMT PROJECT_EXP_CONSUMED_AMT RECEIPTS_CONSUMED_AMT SPEND_AUTHORIZE_CONSUMED_AMT SEGMENT_VALUE1 SEGMENT_VALUE2 SEGMENT_VALUE3 SEGMENT_VALUE4 SEGMENT_VALUE5 SEGMENT_VALUE6 SEGMENT_VALUE7 INTERNAL_BUDGET_TYPE_CODE RESOURCE_SEGMENT_EXISTS LOWEST_RESOURCE_SEGMENT_EXISTS TOP_TASK_SEGMENT_EXISTS LOWEST_TASK_SEGMENT_EXISTS

Name

CONTROL_BUDGET_ID

PERIOD_NAME

CONTROL_BUDGET_NAME

CONTROL_LEVEL_CODE

LEDGER_ID

PROJECT_ID

CONTRACT_ID

BUDGET_AMOUNT

FUNDS_AVAILABLE_AMOUNT

CONSUMED_AMOUNT

REQUISITION_CONSUMED_AMT

PURCHASE_ORDER_CONSUMED_AMT

INVOICE_CONSUMED_AMT

PROJECT_EXP_CONSUMED_AMT

RECEIPTS_CONSUMED_AMT

SPEND_AUTHORIZE_CONSUMED_AMT

SEGMENT_VALUE1

SEGMENT_VALUE2

SEGMENT_VALUE3

SEGMENT_VALUE4

SEGMENT_VALUE5

SEGMENT_VALUE6

SEGMENT_VALUE7

INTERNAL_BUDGET_TYPE_CODE

RESOURCE_SEGMENT_EXISTS

LOWEST_RESOURCE_SEGMENT_EXISTS

TOP_TASK_SEGMENT_EXISTS

LOWEST_TASK_SEGMENT_EXISTS

Query

SQL_Statement
SELECT cb.control_budget_id, bal.period_name, cb.name control_budget_name, cb.control_level_code control_level_code, cb.ledger_id ledger_id, cb.project_id project_id, cb.pjc_contract_id contract_id, NVL(bal.budget_amount,0) budget_amount, NVL(bal.funds_available_amount,0) funds_available_amount, NVL(bal.budget_amount,0)-NVL(bal.funds_available_amount,0) consumed_amount, NVL(bal.APPROVED_COMMITMENT_AMOUNT,0) Requisition_Consumed_AMT, NVL(bal.APPROVED_OBLIGATION_AMOUNT,0) Purchase_order_Consumed_AMT, NVL(bal.ACCOUNTED_PAYABLES_AMOUNT,0) Invoice_Consumed_AMT, NVL(bal.ACCOUNTED_PROJECT_AMOUNT,0) Project_Exp_Consumed_AMT, NVL(bal.ACCOUNTED_RECEIPTS_AMOUNT,0) Receipts_Consumed_AMT, NVL(bal.APPROVED_AUTHORIZATION_AMOUNT, 0) Spend_Authorize_Consumed_AMT, ba.segment_value1 segment_value1, ba.segment_value2 segment_value2, ba.segment_value3 segment_value3, ba.segment_value4 segment_value4, ba.segment_value5 segment_value5, ba.segment_value6 segment_value6, ba.segment_value7 segment_value7, cb.internal_budget_type_code internal_budget_type_code, NVL(cbfs.pjc_resource_id_flag,'N') resource_segment_exists, NVL(cbfs.pjc_lowest_resource_id_flag,'N') lowest_resource_segment_exists, NVL(cbfs.pjc_task_id_flag,'N') top_task_segment_exists, NVL(cbfs.pjc_lowest_task_id_flag,'N') lowest_task_segment_exists FROM xcc_control_budgets cb, xcc_cb_flat_segments cbfs, xcc_balances_v bal, xcc_budget_accounts ba WHERE cb.parent_source_system = 'PPM' AND cb.status_code <> 'PERMANENTLY_CLOSED' AND cb.control_budget_id = cbfs.control_budget_id AND cb.control_budget_id = bal.control_budget_id AND ba.budget_code_combination_id = bal.budget_ccid

SQL_Statement

SELECT cb.control_budget_id,

bal.period_name,

cb.name control_budget_name,

cb.control_level_code control_level_code,

cb.ledger_id ledger_id,

cb.project_id project_id,

cb.pjc_contract_id contract_id,

NVL(bal.budget_amount,0) budget_amount,

NVL(bal.funds_available_amount,0) funds_available_amount,

NVL(bal.budget_amount,0)-NVL(bal.funds_available_amount,0) consumed_amount,

NVL(bal.APPROVED_COMMITMENT_AMOUNT,0) Requisition_Consumed_AMT,

NVL(bal.APPROVED_OBLIGATION_AMOUNT,0) Purchase_order_Consumed_AMT,

NVL(bal.ACCOUNTED_PAYABLES_AMOUNT,0) Invoice_Consumed_AMT,

NVL(bal.ACCOUNTED_PROJECT_AMOUNT,0) Project_Exp_Consumed_AMT,

NVL(bal.ACCOUNTED_RECEIPTS_AMOUNT,0) Receipts_Consumed_AMT,

NVL(bal.APPROVED_AUTHORIZATION_AMOUNT, 0) Spend_Authorize_Consumed_AMT,

ba.segment_value1 segment_value1,

ba.segment_value2 segment_value2,

ba.segment_value3 segment_value3,

ba.segment_value4 segment_value4,

ba.segment_value5 segment_value5,

ba.segment_value6 segment_value6,

ba.segment_value7 segment_value7,

cb.internal_budget_type_code internal_budget_type_code,

NVL(cbfs.pjc_resource_id_flag,'N') resource_segment_exists,

NVL(cbfs.pjc_lowest_resource_id_flag,'N') lowest_resource_segment_exists,

NVL(cbfs.pjc_task_id_flag,'N') top_task_segment_exists,

NVL(cbfs.pjc_lowest_task_id_flag,'N') lowest_task_segment_exists

FROM xcc_control_budgets cb,

xcc_cb_flat_segments cbfs,

xcc_balances_v bal,

xcc_budget_accounts ba

WHERE cb.parent_source_system = 'PPM'

AND cb.status_code <> 'PERMANENTLY_CLOSED'

AND cb.control_budget_id = cbfs.control_budget_id

AND cb.control_budget_id = bal.control_budget_id

AND ba.budget_code_combination_id = bal.budget_ccid