eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: PJI_XTD_ACT_CMT_BY_TASK_V

Product:	PJI - Project Intelligence
Description:	PJI_XTD_ACT_CMT_BY_TASK_V is a view that displays actual cost, revenue totals, and commitments for PTD, QTD, YTD, and ITD by task. The view is mainly used by external project management systems that import actuals from Oracle Projects.
Implementation/DBA Data:	APPS.PJI_XTD_ACT_CMT_BY_TASK_V

View Text

SELECT PROJECT_ID
, TASK_ID
, CALENDAR_TYPE
, CALENDAR_ID
, PERIOD_NAME
, PERIOD_START_DATE
, PERIOD_END_DATE
, SUM(REVENUE * PTD_MASK) REVENUE_PTD
, SUM(RAW_COST * PTD_MASK) RAW_COST_PTD
, SUM(BURDENED_COST * PTD_MASK) BURDENED_COST_PTD
, SUM(BILLABLE_RAW_COST * PTD_MASK) BILLABLE_RAW_COST_PTD
, SUM(BILLABLE_BURDENED_COST * PTD_MASK) BILLABLE_BURDENED_COST_PTD
, SUM(BILL_LABOR_RAW_COST * PTD_MASK) BILL_LABOR_RAW_COST_PTD
, SUM(BILL_LABOR_BRDN_COST * PTD_MASK) BILL_LABOR_BRDN_COST_PTD
, SUM(BILL_LABOR_HRS * PTD_MASK) BILL_LABOR_HRS_PTD
, SUM(EQUIPMENT_RAW_COST * PTD_MASK) EQUIPMENT_RAW_COST_PTD
, SUM(EQUIPMENT_BRDN_COST * PTD_MASK) EQUIPMENT_BRDN_COST_PTD
, SUM(CAPITAL_RAW_COST * PTD_MASK) CAPITAL_RAW_COST_PTD
, SUM(CAPITAL_BURDENED_COST * PTD_MASK) CAPITAL_BURDENED_COST_PTD
, SUM(LABOR_RAW_COST * PTD_MASK) LABOR_RAW_COST_PTD
, SUM(LABOR_BRDN_COST * PTD_MASK) LABOR_BRDN_COST_PTD
, SUM(LABOR_HRS * PTD_MASK) LABOR_HOURS_PTD
, SUM(LABOR_REVENUE * PTD_MASK) LABOR_REVENUE_PTD
, SUM(EQUIPMENT_HOURS * PTD_MASK) EQUIPMENT_HOURS_PTD
, SUM(BILLABLE_EQUIPMENT_HOURS * PTD_MASK) BILLABLE_EQUIPMENT_HOURS_PTD
, SUM(SUP_INV_COMMITTED_COST * PTD_MASK) SUP_INV_COMMITTED_COST_PTD
, SUM(PO_COMMITTED_COST * PTD_MASK) PO_COMMITTED_COST_PTD
, SUM(PR_COMMITTED_COST * PTD_MASK) PR_COMMITTED_COST_PTD
, SUM(OTH_COMMITTED_COST * PTD_MASK) OTH_COMMITTED_COST_PTD
, SUM(CMT_BURDENED_COST * PTD_MASK) CMT_BURDENED_COST_PTD
, SUM(BILL_LABOR_HRS * PTD_MASK) BILLABLE_QUANTITY_PTD
, SUM(BILL_LABOR_HRS * PTD_MASK) BILLABLE_LABOR_HOURS_PTD
, SUM(LABOR_HRS * PTD_MASK) CMT_QUANTITY_PTD
, SUM(LABOR_HRS * PTD_MASK) LABOR_HRS_PTD
, SUM(LABOR_HRS * PTD_MASK) QUANTITY_PTD
, SUM(REVENUE * QTD_MASK) REVENUE_QTD
, SUM(RAW_COST * QTD_MASK) RAW_COST_QTD
, SUM(BURDENED_COST * QTD_MASK) BURDENED_COST_QTD
, SUM(BILLABLE_RAW_COST * QTD_MASK) BILLABLE_RAW_COST_QTD
, SUM(BILLABLE_BURDENED_COST * QTD_MASK) BILLABLE_BURDENED_COST_QTD
, SUM(BILL_LABOR_RAW_COST * QTD_MASK) BILL_LABOR_RAW_COST_QTD
, SUM(BILL_LABOR_BRDN_COST * QTD_MASK) BILL_LABOR_BRDN_COST_QTD
, SUM(BILL_LABOR_HRS * QTD_MASK) BILL_LABOR_HRS_QTD
, SUM(EQUIPMENT_RAW_COST * QTD_MASK) EQUIPMENT_RAW_COST_QTD
, SUM(EQUIPMENT_BRDN_COST * QTD_MASK) EQUIPMENT_BRDN_COST_QTD
, SUM(CAPITAL_RAW_COST * QTD_MASK) CAPITAL_RAW_COST_QTD
, SUM(CAPITAL_BURDENED_COST * QTD_MASK) CAPITAL_BURDENED_COST_QTD
, SUM(LABOR_RAW_COST * QTD_MASK) LABOR_RAW_COST_QTD
, SUM(LABOR_BRDN_COST * QTD_MASK) LABOR_BRDN_COST_QTD
, SUM(LABOR_HRS * QTD_MASK) LABOR_HOURS_QTD
, SUM(LABOR_REVENUE * QTD_MASK) LABOR_REVENUE_QTD
, SUM(EQUIPMENT_HOURS * QTD_MASK) EQUIPMENT_HOURS_QTD
, SUM(BILLABLE_EQUIPMENT_HOURS * QTD_MASK) BILLABLE_EQUIPMENT_HOURS_QTD
, SUM(SUP_INV_COMMITTED_COST * QTD_MASK) SUP_INV_COMMITTED_COST_QTD
, SUM(PO_COMMITTED_COST * QTD_MASK) PO_COMMITTED_COST_QTD
, SUM(PR_COMMITTED_COST * QTD_MASK) PR_COMMITTED_COST_QTD
, SUM(OTH_COMMITTED_COST * QTD_MASK) OTH_COMMITTED_COST_QTD
, SUM(CMT_BURDENED_COST * QTD_MASK) CMT_BURDENED_COST_QTD
, SUM(BILL_LABOR_HRS * QTD_MASK) BILLABLE_QUANTITY_QTD
, SUM(BILL_LABOR_HRS * QTD_MASK) BILLABLE_LABOR_HOURS_QTD
, SUM(LABOR_HRS * QTD_MASK) CMT_QUANTITY_QTD
, SUM(LABOR_HRS * QTD_MASK) LABOR_HRS_QTD
, SUM(LABOR_HRS * QTD_MASK) QUANTITY_QTD
, SUM(REVENUE * YTD_MASK) REVENUE_YTD
, SUM(RAW_COST * YTD_MASK) RAW_COST_YTD
, SUM(BURDENED_COST * YTD_MASK) BURDENED_COST_YTD
, SUM(BILLABLE_RAW_COST * YTD_MASK) BILLABLE_RAW_COST_YTD
, SUM(BILLABLE_BURDENED_COST * YTD_MASK) BILLABLE_BURDENED_COST_YTD
, SUM(BILL_LABOR_RAW_COST * YTD_MASK) BILL_LABOR_RAW_COST_YTD
, SUM(BILL_LABOR_BRDN_COST * YTD_MASK) BILL_LABOR_BRDN_COST_YTD
, SUM(BILL_LABOR_HRS * YTD_MASK) BILL_LABOR_HRS_YTD
, SUM(EQUIPMENT_RAW_COST * YTD_MASK) EQUIPMENT_RAW_COST_YTD
, SUM(EQUIPMENT_BRDN_COST * YTD_MASK) EQUIPMENT_BRDN_COST_YTD
, SUM(CAPITAL_RAW_COST * YTD_MASK) CAPITAL_RAW_COST_YTD
, SUM(CAPITAL_BURDENED_COST * YTD_MASK) CAPITAL_BURDENED_COST_YTD
, SUM(LABOR_RAW_COST * YTD_MASK) LABOR_RAW_COST_YTD
, SUM(LABOR_BRDN_COST * YTD_MASK) LABOR_BRDN_COST_YTD
, SUM(LABOR_HRS * YTD_MASK) LABOR_HOURS_YTD
, SUM(LABOR_REVENUE * YTD_MASK) LABOR_REVENUE_YTD
, SUM(EQUIPMENT_HOURS * YTD_MASK) EQUIPMENT_HOURS_YTD
, SUM(BILLABLE_EQUIPMENT_HOURS * YTD_MASK) BILLABLE_EQUIPMENT_HOURS_YTD
, SUM(SUP_INV_COMMITTED_COST * YTD_MASK) SUP_INV_COMMITTED_COST_YTD
, SUM(PO_COMMITTED_COST * YTD_MASK) PO_COMMITTED_COST_YTD
, SUM(PR_COMMITTED_COST * YTD_MASK) PR_COMMITTED_COST_YTD
, SUM(OTH_COMMITTED_COST * YTD_MASK) OTH_COMMITTED_COST_YTD
, SUM(CMT_BURDENED_COST * YTD_MASK) CMT_BURDENED_COST_YTD
, SUM(BILL_LABOR_HRS * YTD_MASK) BILLABLE_QUANTITY_YTD
, SUM(BILL_LABOR_HRS * YTD_MASK) BILLABLE_LABOR_HOURS_YTD
, SUM(LABOR_HRS * YTD_MASK) CMT_QUANTITY_YTD
, SUM(LABOR_HRS * YTD_MASK) LABOR_HRS_YTD
, SUM(LABOR_HRS * YTD_MASK) QUANTITY_YTD
, SUM(REVENUE * ITD_MASK) REVENUE_ITD
, SUM(RAW_COST * ITD_MASK) RAW_COST_ITD
, SUM(BURDENED_COST * ITD_MASK) BURDENED_COST_ITD
, SUM(BILLABLE_RAW_COST * ITD_MASK) BILLABLE_RAW_COST_ITD
, SUM(BILLABLE_BURDENED_COST * ITD_MASK) BILLABLE_BURDENED_COST_ITD
, SUM(BILL_LABOR_RAW_COST * ITD_MASK) BILL_LABOR_RAW_COST_ITD
, SUM(BILL_LABOR_BRDN_COST * ITD_MASK) BILL_LABOR_BRDN_COST_ITD
, SUM(BILL_LABOR_HRS * ITD_MASK) BILL_LABOR_HRS_ITD
, SUM(EQUIPMENT_RAW_COST * ITD_MASK) EQUIPMENT_RAW_COST_ITD
, SUM(EQUIPMENT_BRDN_COST * ITD_MASK) EQUIPMENT_BRDN_COST_ITD
, SUM(CAPITAL_RAW_COST * ITD_MASK) CAPITAL_RAW_COST_ITD
, SUM(CAPITAL_BURDENED_COST * ITD_MASK) CAPITAL_BURDENED_COST_ITD
, SUM(LABOR_RAW_COST * ITD_MASK) LABOR_RAW_COST_ITD
, SUM(LABOR_BRDN_COST * ITD_MASK) LABOR_BRDN_COST_ITD
, SUM(LABOR_HRS * ITD_MASK) LABOR_HOURS_ITD
, SUM(LABOR_REVENUE * ITD_MASK) LABOR_REVENUE_ITD
, SUM(EQUIPMENT_HOURS * ITD_MASK) EQUIPMENT_HOURS_ITD
, SUM(BILLABLE_EQUIPMENT_HOURS * ITD_MASK) BILLABLE_EQUIPMENT_HOURS_ITD
, SUM(SUP_INV_COMMITTED_COST * ITD_MASK) SUP_INV_COMMITTED_COST_ITD
, SUM(PO_COMMITTED_COST * ITD_MASK) PO_COMMITTED_COST_ITD
, SUM(PR_COMMITTED_COST * ITD_MASK) PR_COMMITTED_COST_ITD
, SUM(OTH_COMMITTED_COST * ITD_MASK) OTH_COMMITTED_COST_ITD
, SUM(CMT_BURDENED_COST * ITD_MASK) CMT_BURDENED_COST_ITD
, SUM(BILL_LABOR_HRS * ITD_MASK) BILLABLE_QUANTITY_ITD
, SUM(BILL_LABOR_HRS * ITD_MASK) BILLABLE_LABOR_HOURS_ITD
, SUM(LABOR_HRS * ITD_MASK) CMT_QUANTITY_ITD
, SUM(LABOR_HRS * ITD_MASK) LABOR_HRS_ITD
, SUM(LABOR_HRS * ITD_MASK) QUANTITY_ITD
FROM (SELECT FACT.PROJECT_ID PROJECT_ID
, FACT.PROJECT_ELEMENT_ID TASK_ID
, FACT.CALENDAR_TYPE CALENDAR_TYPE
, TIME.CALENDAR_ID CALENDAR_ID
, TIME.NAME PERIOD_NAME
, TIME.START_DATE PERIOD_START_DATE
, TIME.END_DATE PERIOD_END_DATE
, FACT.REVENUE REVENUE
, FACT.RAW_COST RAW_COST
, FACT.BRDN_COST BURDENED_COST
, FACT.BILL_RAW_COST BILLABLE_RAW_COST
, FACT.BILL_BRDN_COST BILLABLE_BURDENED_COST
, FACT.BILL_LABOR_RAW_COST BILL_LABOR_RAW_COST
, FACT.BILL_LABOR_BRDN_COST BILL_LABOR_BRDN_COST
, FACT.BILL_LABOR_HRS BILL_LABOR_HRS
, FACT.EQUIPMENT_RAW_COST EQUIPMENT_RAW_COST
, FACT.EQUIPMENT_BRDN_COST EQUIPMENT_BRDN_COST
, FACT.CAPITALIZABLE_RAW_COST CAPITAL_RAW_COST
, FACT.CAPITALIZABLE_BRDN_COST CAPITAL_BURDENED_COST
, FACT.LABOR_RAW_COST LABOR_RAW_COST
, FACT.LABOR_BRDN_COST LABOR_BRDN_COST
, FACT.LABOR_HRS LABOR_HOURS
, FACT.LABOR_REVENUE LABOR_REVENUE
, FACT.EQUIPMENT_HOURS EQUIPMENT_HOURS
, FACT.BILLABLE_EQUIPMENT_HOURS BILLABLE_EQUIPMENT_HOURS
, FACT.SUP_INV_COMMITTED_COST SUP_INV_COMMITTED_COST
, FACT.PO_COMMITTED_COST PO_COMMITTED_COST
, FACT.PR_COMMITTED_COST PR_COMMITTED_COST
, FACT.OTH_COMMITTED_COST OTH_COMMITTED_COST
, (NVL(FACT.SUP_INV_COMMITTED_COST
, 0) + NVL(FACT.PO_COMMITTED_COST
, 0) + NVL(FACT.PR_COMMITTED_COST
, 0) + NVL(FACT.OTH_COMMITTED_COST
, 0) ) CMT_BURDENED_COST
, FACT.BILL_LABOR_HRS BILLABLE_QUANTITY
, FACT.BILL_LABOR_HRS BILLABLE_LABOR_HOURS
, FACT.LABOR_HRS QUANTITY
, FACT.LABOR_HRS LABOR_HRS
, FACT.LABOR_HRS CMT_QUANTITY
, SIGN(BITAND(XTD.RECORD_TYPE_ID
, 256)) PTD_MASK
, SIGN(BITAND(XTD.RECORD_TYPE_ID
, 288)) QTD_MASK
, SIGN(BITAND(XTD.RECORD_TYPE_ID
, 352)) YTD_MASK
, SIGN(BITAND(XTD.RECORD_TYPE_ID
, 1376)) ITD_MASK
FROM PJI_FP_XBS_ACCUM_F FACT
, PJI_TIME_CAL_RPT_STRUCT_V XTD
, PJI_TIME_CAL_PERIOD_V TIME
, PA_PROJECTS_ALL PPA
, PJI_ORG_EXTR_INFO EXTR
WHERE PPA.ORG_ID = EXTR.ORG_ID
AND ((FACT.CALENDAR_TYPE = 'G'
AND XTD.CALENDAR_ID = EXTR.GL_CALENDAR_ID) OR (FACT.CALENDAR_TYPE = 'P'
AND XTD.CALENDAR_ID = EXTR.PA_CALENDAR_ID))
AND FACT.TIME_ID = XTD.TIME_ID
AND TIME.START_DATE = XTD.REPORT_DATE
AND TIME.CALENDAR_ID = XTD.CALENDAR_ID
AND FACT.RBS_AGGR_LEVEL = 'T'
AND FACT.RBS_VERSION_ID = -1
AND FACT.PRG_ROLLUP_FLAG = 'N'
AND FACT.CURRENCY_CODE = PPA.PROJFUNC_CURRENCY_CODE
AND BITAND(FACT.CURR_RECORD_TYPE_ID
, 4) = 4
AND FACT.PROJECT_ID = PPA.PROJECT_ID
AND FACT.PLAN_VERSION_ID = -1
AND FACT.PLAN_TYPE_ID = -1 ) GROUP BY PROJECT_ID
, TASK_ID
, CALENDAR_TYPE
, CALENDAR_ID
, PERIOD_NAME
, PERIOD_START_DATE
, PERIOD_END_DATE

Columns

Name
PROJECT_ID
TASK_ID
CALENDAR_TYPE
CALENDAR_ID
PERIOD_NAME
PERIOD_START_DATE
PERIOD_END_DATE
REVENUE_PTD
RAW_COST_PTD
BURDENED_COST_PTD
BILLABLE_RAW_COST_PTD
BILLABLE_BURDENED_COST_PTD
BILL_LABOR_RAW_COST_PTD
BILL_LABOR_BRDN_COST_PTD
BILL_LABOR_HRS_PTD
EQUIPMENT_RAW_COST_PTD
EQUIPMENT_BRDN_COST_PTD
CAPITAL_RAW_COST_PTD
CAPITAL_BURDENED_COST_PTD
LABOR_RAW_COST_PTD
LABOR_BRDN_COST_PTD
LABOR_HOURS_PTD
LABOR_REVENUE_PTD
EQUIPMENT_HOURS_PTD
BILLABLE_EQUIPMENT_HOURS_PTD
SUP_INV_COMMITTED_COST_PTD
PO_COMMITTED_COST_PTD
PR_COMMITTED_COST_PTD
OTH_COMMITTED_COST_PTD
CMT_BURDENED_COST_PTD
BILLABLE_QUANTITY_PTD
BILLABLE_LABOR_HOURS_PTD
CMT_QUANTITY_PTD
LABOR_HRS_PTD
QUANTITY_PTD
REVENUE_QTD
RAW_COST_QTD
BURDENED_COST_QTD
BILLABLE_RAW_COST_QTD
BILLABLE_BURDENED_COST_QTD
BILL_LABOR_RAW_COST_QTD
BILL_LABOR_BRDN_COST_QTD
BILL_LABOR_HRS_QTD
EQUIPMENT_RAW_COST_QTD
EQUIPMENT_BRDN_COST_QTD
CAPITAL_RAW_COST_QTD
CAPITAL_BURDENED_COST_QTD
LABOR_RAW_COST_QTD
LABOR_BRDN_COST_QTD
LABOR_HOURS_QTD
LABOR_REVENUE_QTD
EQUIPMENT_HOURS_QTD
BILLABLE_EQUIPMENT_HOURS_QTD
SUP_INV_COMMITTED_COST_QTD
PO_COMMITTED_COST_QTD
PR_COMMITTED_COST_QTD
OTH_COMMITTED_COST_QTD
CMT_BURDENED_COST_QTD
BILLABLE_QUANTITY_QTD
BILLABLE_LABOR_HOURS_QTD
CMT_QUANTITY_QTD
LABOR_HRS_QTD
QUANTITY_QTD
REVENUE_YTD
RAW_COST_YTD
BURDENED_COST_YTD
BILLABLE_RAW_COST_YTD
BILLABLE_BURDENED_COST_YTD
BILL_LABOR_RAW_COST_YTD
BILL_LABOR_BRDN_COST_YTD
BILL_LABOR_HRS_YTD
EQUIPMENT_RAW_COST_YTD
EQUIPMENT_BRDN_COST_YTD
CAPITAL_RAW_COST_YTD
CAPITAL_BURDENED_COST_YTD
LABOR_RAW_COST_YTD
LABOR_BRDN_COST_YTD
LABOR_HOURS_YTD
LABOR_REVENUE_YTD
EQUIPMENT_HOURS_YTD
BILLABLE_EQUIPMENT_HOURS_YTD
SUP_INV_COMMITTED_COST_YTD
PO_COMMITTED_COST_YTD
PR_COMMITTED_COST_YTD
OTH_COMMITTED_COST_YTD
CMT_BURDENED_COST_YTD
BILLABLE_QUANTITY_YTD
BILLABLE_LABOR_HOURS_YTD
CMT_QUANTITY_YTD
LABOR_HRS_YTD
QUANTITY_YTD
REVENUE_ITD
RAW_COST_ITD
BURDENED_COST_ITD
BILLABLE_RAW_COST_ITD
BILLABLE_BURDENED_COST_ITD
BILL_LABOR_RAW_COST_ITD
BILL_LABOR_BRDN_COST_ITD
BILL_LABOR_HRS_ITD
EQUIPMENT_RAW_COST_ITD
EQUIPMENT_BRDN_COST_ITD
CAPITAL_RAW_COST_ITD
CAPITAL_BURDENED_COST_ITD
LABOR_RAW_COST_ITD
LABOR_BRDN_COST_ITD
LABOR_HOURS_ITD
LABOR_REVENUE_ITD
EQUIPMENT_HOURS_ITD
BILLABLE_EQUIPMENT_HOURS_ITD
SUP_INV_COMMITTED_COST_ITD
PO_COMMITTED_COST_ITD
PR_COMMITTED_COST_ITD
OTH_COMMITTED_COST_ITD
CMT_BURDENED_COST_ITD
BILLABLE_QUANTITY_ITD
BILLABLE_LABOR_HOURS_ITD
CMT_QUANTITY_ITD
LABOR_HRS_ITD
QUANTITY_ITD