ECC Projects, Projects Billing: Budget

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Projects Billing Revenue Budget Details
Dataset Key: pa-ds-bill-budget
Query Procedure: PA_ECC_BILL_LOAD_PKG.LOAD_FULL_BUDGET_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
select
x.*
from
(
select * from (
SELECT
RECORD_TYPE,
ECC_SPEC_ID,
PROJECT_ID,
PROJECT_NAME,
PROJECT_NUMBER,
BGT.ORG_ID,
OPERATING_UNIT,
PROJECT_ORGANIZATION,
PROJFUNC_CURRENCY_CODE,
PROJECT_START_DATE,
PROJECT_COMPLETION_DATE,
FUNDING_TASK_ID,
FUNDING_TASK_ID FUNDING_TASK_ID_RI,
FUNDING_TASK_NUMBER,
FUNDING_TASK_NUMBER FUNDING_TASK_NUMBER_RI,
FUNDING_TASK_NAME,
BUDGET_VERSION_ID,
BUDGET_TYPE_CODE,
BUDGET_TYPE,
VERSION_NUMBER,
VERSION_NAME,
VERSION_DESCRIPTION,
BUDGET_ENTRY_METHOD_CODE,
BUDGET_ENTRY_METHOD,
VERSION_RAW_COST,
VERSION_BURDENED_COST,
VERSION_REVENUE,
BUDGET_LINE_ID,
RESOURCE_ASSIGNMENT_ID,
TASK_ID,
TASK_NUMBER,
TASK_NUMBER TASK_NUMBER_RI,
TASK_NAME,
TASK_START_DATE,
TASK_COMPLETION_DATE,
BUDGET_LINE_START_DATE,
BUDGET_LINE_END_DATE,
GL_PERIOD_NAME,
CHART_PERIOD_NAME,
REVENUE_BUDGET,
REVENUE_BUDGET REVENUE_BUDGET_ITD,
ITD_FLAG,
(CASE WHEN TRUNC(SYSDATE) between gps.start_date and gps.end_date
   then 'Y' else 'N' end) current_period_flag,
CHART_DISPLAY_FLAG,
EXP_REV_GL_PERIOD,
EXP_REV_GL_PERIOD_FMT,
PROJ_PERIOD_REVENUE,
PROJ_PERIOD_EXPENDITURE,
(nvl(PROJ_PERIOD_REVENUE, 0) - nvl(PROJ_PERIOD_EXPENDITURE, 0)) CASH_FLOW,
PROJECT_ID||':'||FUNDING_TASK_ID DS_ASSOC_PRJ_TSK_ATTR,
PROJECT_ID||':'||FUNDING_TASK_ID||':'||GL_PERIOD_NAME DS_ASSOC_PRJ_TSK_PER_ATTR,
gps.start_date GL_PERIOD_START_DATE,
nvl(actual_billing, 0) actual_billing,
decode(nvl(bgt.funding_task_id, 0),
         0, decode(nvl(bgt.task_id, 0), 0, nvl(actual_billing, 0), 0),
		 actual_billing) actual_billing_rt,
decode(sign(revenue_budget_pt - (nvl(actual_billing, 0) + nvl(unaccrued_bill_amt, 0) + nvl(ready_to_bill_amt, 0) + nvl(bill_hold_amt, 0))),
    -1, 0,
        (revenue_budget_pt - (nvl(actual_billing, 0) + nvl(unaccrued_bill_amt, 0) + nvl(ready_to_bill_amt, 0) + nvl(bill_hold_amt, 0)))) unprogressed_budget,
revenue_budget_pt,
IMP.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
BUDGET_RECORD_TYPE,
COST_BUDGET_VERSION_ID,
COST_BUDGET_TYPE_CODE,
COST_BUDGET_TYPE,
COST_VERSION_NUMBER,
COST_VERSION_NAME,
COST_VERSION_DESCRIPTION,
COST_BUDGET_ENTRY_METHOD_CODE,
COST_BUDGET_ENTRY_METHOD,
COST_BUDGET,
ACTUAL_RAW_COST,
ACTUAL_BURDENED_COST,
PENDING_PO_RECEIPTS,
PENDING_VENDOR_INVOICES,
PENDING_COST_SOURCE,
UNPROGRESSED_COST,
LANGUAGE
FROM PA_ECC_BILL_BUDGET BGT, GL_PERIOD_STATUSES GPS,
     PA_IMPLEMENTATIONS_ALL IMP
where BGT.ORG_ID = IMP.ORG_ID
  AND IMP.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
  AND GPS.APPLICATION_ID = 101
  AND BGT.GL_PERIOD_NAME = GPS.PERIOD_NAME
  AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
  AND language in ('US'))PIVOT (
max(OPERATING_UNIT) as OPERATING_UNIT
for LANGUAGE in ('US' "US"))
) x
where
2=2