View: PA_ALLOC_TXN_ACCUM_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_ALLOC_TXN_ACCUM_V

View Text

SELECT ACCUM.TXN_ACCUM_ID
, ACCUM.PROJECT_ID
, ACCUM.TASK_ID
, ACCUM.PA_PERIOD
, ACCUM.GL_PERIOD
, ACCUM.TOT_REVENUE
, ACCUM.TOT_RAW_COST
, ACCUM.TOT_BURDENED_COST
, ACCUM.TOT_QUANTITY
, ACCUM.TOT_LABOR_HOURS
, ACCUM.TOT_BILLABLE_RAW_COST
, ACCUM.TOT_BILLABLE_BURDENED_COST
, ACCUM.TOT_BILLABLE_QUANTITY
, ACCUM.TOT_BILLABLE_LABOR_HOURS
, ACCUM.I_TOT_REVENUE
, ACCUM.I_TOT_RAW_COST
, ACCUM.I_TOT_BURDENED_COST
, ACCUM.I_TOT_QUANTITY
, ACCUM.I_TOT_LABOR_HOURS
, ACCUM.I_TOT_BILLABLE_RAW_COST
, ACCUM.I_TOT_BILLABLE_BURDENED_COST
, ACCUM.I_TOT_BILLABLE_QUANTITY
, ACCUM.I_TOT_BILLABLE_LABOR_HOURS
, 'PA' RECORD_SOURCE
FROM PA_TXN_ACCUM ACCUM
, PA_PROJECTS_ALL PRJ
WHERE PRJ.PJI_SOURCE_FLAG IS NULL
AND ACCUM.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT -1 TXN_ACCUM_ID
, FACT.PROJECT_ID
, FACT.PROJECT_ELEMENT_ID TASK_ID
, FACT.PA_PERIOD
, FACT.GL_PERIOD
, SUM(FACT.REVENUE) TOT_REVENUE
, SUM(FACT.RAW_COST) TOT_RAW_COST
, SUM(FACT.BRDN_COST) TOT_BURDENED_COST
, SUM(FACT.LABOR_HRS) TOT_QUANTITY
, SUM(FACT.LABOR_HRS) TOT_LABOR_HOURS
, SUM(FACT.BILL_RAW_COST) TOT_BILLABLE_RAW_COST
, SUM(FACT.BILL_BRDN_COST) TOT_BILLABLE_BURDENED_COST
, SUM(FACT.BILL_LABOR_HRS) TOT_BILLABLE_QUANTITY
, SUM(FACT.BILL_LABOR_HRS) TOT_BILLABLE_LABOR_HOURS
, SUM(0) I_TOT_REVENUE
, SUM(0) I_TOT_RAW_COST
, SUM(0) I_TOT_BURDENED_COST
, SUM(0) I_TOT_QUANTITY
, SUM(0) I_TOT_LABOR_HOURS
, SUM(0) I_TOT_BILLABLE_RAW_COST
, SUM(0) I_TOT_BILLABLE_BURDENED_COST
, SUM(0) I_TOT_BILLABLE_QUANTITY
, SUM(0) I_TOT_BILLABLE_LABOR_HOURS
, 'PJI' RECORD_SOURCE
FROM ( SELECT -1 TXN_ACCUM_ID
, FACT.PROJECT_ID
, FACT.PROJECT_ELEMENT_ID
, PACAL.NAME PA_PERIOD
, NULL GL_PERIOD
, FACT.REVENUE
, FACT.RAW_COST
, FACT.BRDN_COST
, FACT.LABOR_HRS
, FACT.BILL_RAW_COST
, FACT.BILL_BRDN_COST
, FACT.BILL_LABOR_HRS
FROM PJI_FP_XBS_ACCUM_F FACT
, PA_PROJECTS_ALL PROJ
, PJI_ORG_EXTR_INFO ORG_INFO
, PJI_TIME_CAL_PERIOD_V PACAL
WHERE FACT.PROJECT_ID = PROJ.PROJECT_ID
AND PROJ.PJI_SOURCE_FLAG = 'Y'
AND PROJ.ORG_ID = ORG_INFO.ORG_ID
AND PACAL.CALENDAR_ID = ORG_INFO.PA_CALENDAR_ID
AND FACT.CALENDAR_TYPE = 'P'
AND FACT.PRG_ROLLUP_FLAG = 'N'
AND BITAND(FACT.CURR_RECORD_TYPE_ID
, 8) = 8
AND FACT.PLAN_VERSION_ID = -1
AND FACT.RBS_AGGR_LEVEL = 'T'
AND FACT.TIME_ID = PACAL.CAL_PERIOD_ID UNION ALL SELECT -1 TXN_ACCUM_ID
, FACT.PROJECT_ID
, FACT.PROJECT_ELEMENT_ID
, NULL PA_PERIOD
, GL.NAME GL_PERIOD
, FACT.REVENUE
, FACT.RAW_COST
, FACT.BRDN_COST
, FACT.LABOR_HRS
, FACT.BILL_RAW_COST
, FACT.BILL_BRDN_COST
, FACT.BILL_LABOR_HRS
FROM PJI_FP_XBS_ACCUM_F FACT
, PA_PROJECTS_ALL PROJ
, PJI_ORG_EXTR_INFO ORG_INFO
, PJI_TIME_CAL_PERIOD_V GL
WHERE FACT.PROJECT_ID = PROJ.PROJECT_ID
AND PROJ.PJI_SOURCE_FLAG = 'Y'
AND PROJ.ORG_ID = ORG_INFO.ORG_ID
AND GL.CALENDAR_ID = ORG_INFO.GL_CALENDAR_ID
AND FACT.CALENDAR_TYPE = 'G'
AND FACT.PRG_ROLLUP_FLAG = 'N'
AND BITAND(FACT.CURR_RECORD_TYPE_ID
, 8) = 8
AND FACT.PLAN_VERSION_ID = -1
AND FACT.RBS_AGGR_LEVEL = 'T'
AND FACT.TIME_ID = GL.CAL_PERIOD_ID ) FACT GROUP BY PROJECT_ID
, PROJECT_ELEMENT_ID
, PA_PERIOD
, GL_PERIOD

Columns