ECC Projects, Projects: Investment Turn Summary

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: pa-ds-invest-turn
Query Procedure: PA_ECC_INVTURN_LOAD_PKG.LOAD_FULL_INVEST_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
x.*
from
(
select * from (
SELECT
RECORD_TYPE,
ECC_SPEC_ID,
PROJECT_ID,
PROJECT_NAME,
PROJECT_NUMBER,
ORG_ID,
OPERATING_UNIT,
SET_OF_BOOKS_ID,
PROJFUNC_CURRENCY_CODE,
PROJECT_START_DATE,
PROJECT_COMPLETION_DATE,
PROJECT_TYPE,
PROJECT_TYPE_CLASS_CODE,
PROJECT_STATUS_CODE,
PROJECT_STATUS_NAME,
PROJECT_SYSTEM_STATUS_CODE,
GL_PERIOD_NAME,
GL_PERIOD_NAME_FMT,
ITD_FLAG,
GL_PERIOD_START_DATE,
GL_PERIOD_END_DATE,
PERIOD_NUM,
QUARTER_NUM,
PERIOD_YEAR,
PROJECT_CUSTOMER,
REVENUE_ACCRUAL_METHOD,
INVOICE_METHOD,
NEXT_INVOICE_DATE,
AC_BUDGET_VERSION_ID,
AC_FIN_PLAN_TYPE_ID,
COST_BUDGET,
COST_BUDGET PLANNED_CASH_OUTFLOW,
COST_BUDGET_ITD,
COST_ACTUAL,
COST_ACTUAL_ITD,
COST_BUDGET_ACTUAL,
COST_BUDGET_ACTUAL_ITD,
AR_BUDGET_VERSION_ID,
AR_FIN_PLAN_TYPE_ID,
REVENUE_BUDGET,
REVENUE_BUDGET PLANNED_CASH_INFLOW,
REVENUE_BUDGET_ITD,
REVENUE_ACTUAL,
REVENUE_ACTUAL_ITD,
REVENUE_BUDGET_ACTUAL,
REVENUE_BUDGET_ACTUAL_ITD,
APPROVED_INVOICES,
INVOICES_GENERATED,
BILLING_ACCURACY_DIVISOR,
INVOICE_CREDITS,
(NVL(APPROVED_INVOICES,0) + NVL(INVOICE_CREDITS,0)) BILLING_ACCURACY_NUMRTR,
RECEIPTS_AMOUNT,
RECEIPTS_AMOUNT_CUMULATIVE,
(
SELECT DISTINCT (nvl(RECEIPTS_AMOUNT_CUMULATIVE,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) CONST_RECEIPT_VALUE_ITD,
RECEIPT_AMOUNT_ITD,
PERIOD_END_AR_BALANCE,
PERIOD_START_AR_BALANCE,
INVENTORY_DOLLAR_DAYS,
IDD_CUMULATIVE,
DAYS_IN_MNTH,
AR_BALANCE,
PROJECT_ITD_DAYS,
(nvl(ATTRIBUTE6,0)+nvl(ATTRIBUTE7,0)) ACTUAL_CASH_OUTFLOW,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
CASH_OUTFLOW,
CASH_INFLOW,
ROUND((nvl(CASH_VELOCITY,0)/DAYS_IN_MNTH),2) CASH_VELOCITY,
nvl(CASH_VELOCITY,0) CASH_VELOCITY_METRIC,
ROUND ((COST_BUDGET/DAYS_IN_MNTH),2) PLANNED_CASH_VELOCITY,
BILLABLE_COST,
NON_BILLABLE_COST,
(SELECT pap.PERIOD_NAME FROM pa_periods_all pap
WHERE pap.org_id = summary.org_id and pap.CURRENT_PA_PERIOD_FLAG='Y') CURRENT_REPORTING_PERIOD,
(SELECT gps.PERIOD_NAME FROM gl_period_statuses gps
WHERE gps.set_of_books_id = summary.set_of_books_id
AND gps.application_id = 101
AND SYSDATE between gps.start_date and gps.end_date
AND gps.adjustment_period_flag = 'N' ) CURRENT_GL_PERIOD,
(CASE WHEN TRUNC(SYSDATE) BETWEEN GL_PERIOD_START_DATE AND GL_PERIOD_END_DATE THEN 'Y' ELSE 'N' END) CURRENT_GL_PERIOD_FLAG,
ATTRIBUTE1,
ATTRIBUTE1 BUDGET_SOURCE,
(nvl(COST_BUDGET_ACTUAL,0) - nvl(REVENUE_BUDGET_ACTUAL,0)) FUNDING_GAP_PERIOD,
(nvl(COST_BUDGET_ACTUAL_ITD,0) - nvl(REVENUE_BUDGET_ACTUAL_ITD,0)) FUNDING_GAP_ITD,
(
SELECT DISTINCT (nvl(COST_BUDGET_ITD,0) - nvl(REVENUE_BUDGET_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) CONST_PLAN_FUND_GAP_ITD,
(
SELECT DISTINCT (nvl(EXP_RAW_COST_ITD,0) - nvl(REVENUE_ACTUAL_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) CONST_ACT_FUND_GAP_ITD,
(
SELECT DISTINCT (nvl(COST_BUDGET_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) PROJ_COST_BUDGET_ITD,
(
SELECT DISTINCT (nvl(REVENUE_BUDGET_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) PROJ_REVENUE_BUDGET_ITD,
(
SELECT DISTINCT (nvl(COST_ACTUAL_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) PROJ_COST_ACTUAL_ITD,
(
SELECT DISTINCT (nvl(REVENUE_ACTUAL_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) PROJ_REVENUE_ACTUAL_ITD,
(
SELECT DISTINCT (nvl(EXP_RAW_COST_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) EXP_RAW_COST_ITD,
(
SELECT DISTINCT (nvl(EXP_BURDENED_COST_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) EXP_BURDENED_COST_ITD,
(
SELECT DISTINCT (nvl(INVOICE_AMOUNT_ITD,0))
FROM PA_ECC_INVEST_TURN_SUMMARY const_itd
WHERE const_itd.PROJECT_ID = summary.PROJECT_ID
AND TRUNC(SYSDATE) BETWEEN const_itd.GL_PERIOD_START_DATE AND const_itd.GL_PERIOD_END_DATE
) INVOICE_AMOUNT_ITD,
(CASE WHEN NVL(REVENUE_BUDGET_ACTUAL,0) <> 0 THEN nvl(ATTRIBUTE9,0)/REVENUE_BUDGET_ACTUAL ELSE 0 END) PERIOD_DEBT_BURDEN,
(CASE WHEN NVL(REVENUE_BUDGET,0) <> 0 THEN nvl(APPROVED_INVOICES,0)/REVENUE_BUDGET ELSE 0 END) ACTUAL_PLANNED_INVOICE,
(CASE WHEN NVL(APPROVED_INVOICES,0) <> 0 THEN (NVL(APPROVED_INVOICES,0) + NVL(INVOICE_CREDITS,0))/APPROVED_INVOICES ELSE 0 END) BILLING_ACCURACY,
(CASE WHEN (NVL(REVENUE_ACTUAL,0) + NVL(BILLABLE_COST,0)) <> 0 THEN nvl(APPROVED_INVOICES,0)/(NVL(REVENUE_ACTUAL,0) + NVL(BILLABLE_COST,0)) ELSE 0 END) BILLING_COMPLETENESS,
nvl(PROCURE_TO_CONS_DAYS,0)+nvl(CONS_TO_ACCEPT_DAYS,0)+nvl(ACCEPT_TO_INVOICE_DAYS,0)+nvl(INVOICE_TO_CASH_DAYS,0) TOTAL_CONSUMPTION_DAYS,
nvl(PROCURE_TO_CONS_DAYS,0) PROCURE_TO_CONS_DAYS,
nvl(CONS_TO_ACCEPT_DAYS,0) CONS_TO_ACCEPT_DAYS,
nvl(ACCEPT_TO_INVOICE_DAYS,0) ACCEPT_TO_INVOICE_DAYS,
nvl(INVOICE_TO_CASH_DAYS,0) INVOICE_TO_CASH_DAYS,
(NVL(REVENUE_ACTUAL,0) + NVL(BILLABLE_COST,0) + NVL(NON_BILLABLE_COST,0)) OE_EXPENDITURES,
(NVL(REVENUE_ACTUAL,0) + NVL(BILLABLE_COST,0)) OE_BILL_EXPENDITURES,
REVENUE_ACTUAL OE_ACCEPTED_PROGRESS,
INVOICES_GENERATED OE_BILLED,
ACCEPTED_INVOICES OE_ACCEPTED_BILLED,
RECEIPTS_AMOUNT OE_RECEIPTS_APPLIED,
PERIOD_START_WIP,
PERIOD_END_WIP,
PERIOD_END_WIP-PERIOD_START_WIP IOI,
DECODE(NVL(COST_ACTUAL,0),0,0,((PERIOD_START_WIP+PERIOD_END_WIP)/2)* (DAYS_IN_MNTH/COST_ACTUAL) ) DII,
ROUND (DECODE (greatest(PROJECT_COMPLETION_DATE,sysdate), PROJECT_COMPLETION_DATE, sysdate-PROJECT_START_DATE, PROJECT_COMPLETION_DATE-PROJECT_START_DATE)) TD_SINCE_PROJECTSTART,
PERIOD_END_AR_BALANCE OUTSTANDING_RECEIPTS ,
LANGUAGE
FROM PA_ECC_INVEST_TURN_SUMMARY summary where language in ('US'))PIVOT (
max(OPERATING_UNIT) as OPERATING_UNIT,
max(ATTRIBUTE1) as ATTRIBUTE1
for LANGUAGE in ('US' "US"))
) x
where
2=2
Download
 
Related Blitz Reports
CAC WIP Material Usage with Configuration and Lot Variances CAC Manufacturing Variance Detail CAC Manufacturing Variance Summary CAC WIP Material Usage Variance CAC ICP PII WIP Material Usage Variance ECC Projects, Projects Costing: Transactions, SQL1 ECC Projects, Projects Costing: Transactions, SQL2 ECC Projects, Projects Billing: Budget ECC Projects, Projects Costing: Transactions, SQL3
Blitz Report™

Blitz Report™ provides multiple benefits: