VIEW: APPS.PA_BUDGET_SUMMARY_V

Source

View Text - Preformatted

SELECT project_id , budget_version_id BASELINE_BUDGET_VERSION_ID , sum (budget) budget , sum (commitments) commitments , sum (actuals) actuals , sum (funds_available) funds_available , sum (funds_consumed) funds_consumed , sum (po_commitments) po_commitments , sum (ap_commitments) ap_commitments , sum (req_commitments) req_commitments FROM ( SELECT pbv.project_id , pbv.budget_version_id , sum (nvl (budget_period_to_date, 0)) budget , sum (nvl (encumb_period_to_date, 0)) commitments , sum (nvl (actual_period_to_date, 0)) actuals , sum (nvl (budget_period_to_date, 0) - (nvl (encumb_period_to_date, 0) + nvl (actual_period_to_date, 0))) funds_available , sum (nvl (encumb_period_to_date, 0) + nvl (actual_period_to_date, 0)) funds_consumed , nvl (sum (decode (balance_type, 'PO' , nvl (encumb_period_to_date, 0))), 0) po_commitments , nvl (sum (decode (balance_type, 'AP' , nvl (encumb_period_to_date, 0))), 0) ap_commitments , nvl (sum (decode (balance_type, 'REQ' , nvl (encumb_period_to_date, 0))), 0) req_commitments FROM pa_bc_balances pb , pa_budget_versions pbv , pa_budget_types pbt WHERE pb.budget_version_id = pbv.budget_version_id AND pbt.budget_type_code = pbv.budget_type_code AND pbv.budget_status_code = 'B' AND pbt.budget_amount_code = 'C' AND pbv.current_flag = 'Y' GROUP BY pbv.project_id , pbv.budget_version_id UNION ALL SELECT project_id , budget_version_id , 0 budget , sum ((nvl (accounted_dr, 0) - nvl (accounted_cr, 0)) * decode (document_type, 'EXP' , 0, 1)) commitments , sum ((nvl (accounted_dr, 0) - nvl (accounted_cr, 0)) * decode (document_type, 'EXP' , 1, 0)) actuals , 0-(SUM (accounted_dr - accounted_cr)) funds_available , sum (accounted_dr - accounted_cr) funds_consumed , sum (CASE document_type WHEN 'PO' THEN accounted_dr - accounted_cr ELSE 0 END) po_commitments , sum (CASE document_type WHEN 'AP' THEN accounted_dr - accounted_cr ELSE 0 END) ap_commitments , sum (CASE document_type WHEN 'REQ' THEN accounted_dr - accounted_cr ELSE 0 END) req_commitments FROM pa_bc_packets WHERE status_code IN ('A', 'C') AND balance_posted_flag = 'N' GROUP BY project_id , budget_version_id ) GROUP BY project_id , budget_version_id

View Text - HTML Formatted

SELECT PROJECT_ID
, BUDGET_VERSION_ID BASELINE_BUDGET_VERSION_ID
, SUM (BUDGET) BUDGET
, SUM (COMMITMENTS) COMMITMENTS
, SUM (ACTUALS) ACTUALS
, SUM (FUNDS_AVAILABLE) FUNDS_AVAILABLE
, SUM (FUNDS_CONSUMED) FUNDS_CONSUMED
, SUM (PO_COMMITMENTS) PO_COMMITMENTS
, SUM (AP_COMMITMENTS) AP_COMMITMENTS
, SUM (REQ_COMMITMENTS) REQ_COMMITMENTS
FROM ( SELECT PBV.PROJECT_ID
, PBV.BUDGET_VERSION_ID
, SUM (NVL (BUDGET_PERIOD_TO_DATE
, 0)) BUDGET
, SUM (NVL (ENCUMB_PERIOD_TO_DATE
, 0)) COMMITMENTS
, SUM (NVL (ACTUAL_PERIOD_TO_DATE
, 0)) ACTUALS
, SUM (NVL (BUDGET_PERIOD_TO_DATE
, 0) - (NVL (ENCUMB_PERIOD_TO_DATE
, 0) + NVL (ACTUAL_PERIOD_TO_DATE
, 0))) FUNDS_AVAILABLE
, SUM (NVL (ENCUMB_PERIOD_TO_DATE
, 0) + NVL (ACTUAL_PERIOD_TO_DATE
, 0)) FUNDS_CONSUMED
, NVL (SUM (DECODE (BALANCE_TYPE
, 'PO'
, NVL (ENCUMB_PERIOD_TO_DATE
, 0)))
, 0) PO_COMMITMENTS
, NVL (SUM (DECODE (BALANCE_TYPE
, 'AP'
, NVL (ENCUMB_PERIOD_TO_DATE
, 0)))
, 0) AP_COMMITMENTS
, NVL (SUM (DECODE (BALANCE_TYPE
, 'REQ'
, NVL (ENCUMB_PERIOD_TO_DATE
, 0)))
, 0) REQ_COMMITMENTS
FROM PA_BC_BALANCES PB
, PA_BUDGET_VERSIONS PBV
, PA_BUDGET_TYPES PBT
WHERE PB.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
AND PBT.BUDGET_TYPE_CODE = PBV.BUDGET_TYPE_CODE
AND PBV.BUDGET_STATUS_CODE = 'B'
AND PBT.BUDGET_AMOUNT_CODE = 'C'
AND PBV.CURRENT_FLAG = 'Y' GROUP BY PBV.PROJECT_ID
, PBV.BUDGET_VERSION_ID UNION ALL SELECT PROJECT_ID
, BUDGET_VERSION_ID
, 0 BUDGET
, SUM ((NVL (ACCOUNTED_DR
, 0) - NVL (ACCOUNTED_CR
, 0)) * DECODE (DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) COMMITMENTS
, SUM ((NVL (ACCOUNTED_DR
, 0) - NVL (ACCOUNTED_CR
, 0)) * DECODE (DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, 0-(SUM (ACCOUNTED_DR - ACCOUNTED_CR)) FUNDS_AVAILABLE
, SUM (ACCOUNTED_DR - ACCOUNTED_CR) FUNDS_CONSUMED
, SUM (CASE DOCUMENT_TYPE WHEN 'PO' THEN ACCOUNTED_DR - ACCOUNTED_CR ELSE 0 END) PO_COMMITMENTS
, SUM (CASE DOCUMENT_TYPE WHEN 'AP' THEN ACCOUNTED_DR - ACCOUNTED_CR ELSE 0 END) AP_COMMITMENTS
, SUM (CASE DOCUMENT_TYPE WHEN 'REQ' THEN ACCOUNTED_DR - ACCOUNTED_CR ELSE 0 END) REQ_COMMITMENTS
FROM PA_BC_PACKETS
WHERE STATUS_CODE IN ('A'
, 'C')
AND BALANCE_POSTED_FLAG = 'N' GROUP BY PROJECT_ID
, BUDGET_VERSION_ID ) GROUP BY PROJECT_ID
, BUDGET_VERSION_ID