Tables that hold the detailed financial planning slice data:[left] [left] ODF_SSL_BFT_DTL_ABFT = Benefit for Actuals
ODF_SSL_BFT_DTL_BBFT = Benefit for Budget
ODF_SSL_BFT_DTL_BFT = Benefit
ODF_SSL_CST_DTL_COST PCOST -- Planned Cost
ODF_SSL_CST_DTL_REV PCOST -- Planned Revenue
ODF_SSL_CST_DTL_UNITS PCOST -- Planned Units
[left] Example Query: [left] The following query provides columns for the Transaction Class, Resource Role and Charge Code values (if they exist). If the plan is not grouped by a particular type of attribute, the value will be null. [left] The query has commented statements so that the query can be adapted for MSSQL or Oracle[left] The query can be adapted to display Planned Cost, Planned Revenue or Planned Units by switching the table in the FROM statement.[left] The query can generate a narrow set of results if you uncomment the criteria in the where clause to select a specific project id (internal id) or specific plan id (internal id).[left] [left] [left] SELECT I.ID PROJECT_ID,
I.NAME PROJECT_NAME,
P.ID PLAN_ID ,
P.NAME PLAN_NAME,
D.TRANSCLASS_ID,
D.PRROLE_ID,
D.PRCHARGECODE_ID,
PCOST.START_DATE,
-- PCOST.SLICE, -- raw slice data
[left] -- For MSSQL
-- PCOST.SLICE*(DATEDIFF(DAY, PCOST.START_DATE, PCOST.FINISH_DATE)) PLANNED_COST_AMOUNT
[left] -- For Oracle
ROUND(PCOST.SLICE*(to_date(PCOST.FINISH_DATE) - to_date(PCOST.START_DATE)),2) PLANNED_COST_AMOUNT[left] [left] FROM[left] [left] ODF_SSL_CST_DTL_COST PCOST, -- Planned Cost
[left] -- ODF_SSL_CST_DTL_REV PCOST, -- Planned Revenue
-- ODF_SSL_CST_DTL_UNITS PCOST, -- Planned Units
[left] FIN_COST_PLAN_DETAILS D,
FIN_PLANS P,
INV_INVESTMENTS I
WHERE PCOST.PRJ_OBJECT_ID = D.ID
AND D.PLAN_ID = P.ID
--AND P.OBJECT_ID = ??? -- internal project id
--AND D.PLAN_ID = ??? -- internal detailed financial plan id
AND UPPER(P.OBJECT_CODE) ='PROJECT'
AND P.OBJECT_ID = I.ID
ORDER BY P.ID,
P.NAME,
PCOST.START_DATE,
D.TRANSCLASS_ID,
D.PRROLE_ID,
D.PRCHARGECODE_ID
[left] [left]