[Home] [Help]
MATERIALIZED VIEW: APPS.PJI_FP_CLS_ET_WT_F_MV
Source
select /* 12.0: bug#4526784 */
fpw.TIME_ID,
fpw.PROJECT_ORGANIZATION_ID,
fpw.CALENDAR_TYPE,
fpw.PERIOD_TYPE_ID,
cls.PROJECT_CLASS_ID,
fpw.CURR_RECORD_TYPE_ID,
fpw.CURRENCY_CODE,
fpw.PROJECT_ORG_ID,
fpw.EXP_EVT_TYPE_ID,
fpw.WORK_TYPE_ID,
count(*) CNT,
sum (fpw.RAW_COST *
cls.CODE_PERCENTAGE / 100) RAW_COST,
count(fpw.RAW_COST *
cls.CODE_PERCENTAGE / 100) RAW_COST_CNT,
sum (fpw.BURDENED_COST *
cls.CODE_PERCENTAGE / 100) BURDENED_COST,
count(fpw.BURDENED_COST *
cls.CODE_PERCENTAGE / 100) BURDENED_COST_CNT,
sum (fpw.BILL_RAW_COST *
cls.CODE_PERCENTAGE / 100) BILL_RAW_COST,
count(fpw.BILL_RAW_COST *
cls.CODE_PERCENTAGE / 100) BILL_RAW_COST_CNT,
sum (fpw.BILL_BURDENED_COST *
cls.CODE_PERCENTAGE / 100) BILL_BURDENED_COST,
count(fpw.BILL_BURDENED_COST *
cls.CODE_PERCENTAGE / 100) BILL_BURDENED_COST_CNT,
sum (fpw.LABOR_RAW_COST *
cls.CODE_PERCENTAGE / 100) LABOR_RAW_COST,
count(fpw.LABOR_RAW_COST *
cls.CODE_PERCENTAGE / 100) LABOR_RAW_COST_CNT,
sum (fpw.LABOR_BURDENED_COST *
cls.CODE_PERCENTAGE / 100) LABOR_BURDENED_COST,
count(fpw.LABOR_BURDENED_COST *
cls.CODE_PERCENTAGE / 100) LABOR_BURDENED_COST_CNT,
sum (fpw.BILL_LABOR_RAW_COST *
cls.CODE_PERCENTAGE / 100) BILL_LABOR_RAW_COST,
count(fpw.BILL_LABOR_RAW_COST *
cls.CODE_PERCENTAGE / 100) BILL_LABOR_RAW_COST_CNT,
sum (fpw.BILL_LABOR_BURDENED_COST *
cls.CODE_PERCENTAGE / 100) BILL_LABOR_BURDENED_COST,
count(fpw.BILL_LABOR_BURDENED_COST *
cls.CODE_PERCENTAGE / 100) BILL_LABOR_BURDENED_COST_CNT,
sum (decode(PROJECT_TYPE_CLASS,
'C', RAW_COST, 0) *
cls.CODE_PERCENTAGE / 100) CAPITAL_RAW_COST,
count(decode(PROJECT_TYPE_CLASS,
'C', RAW_COST, 0) *
cls.CODE_PERCENTAGE / 100) CAPITAL_RAW_COST_CNT,
sum (decode(PROJECT_TYPE_CLASS,
'C', BURDENED_COST, 0) *
cls.CODE_PERCENTAGE / 100) CAPITAL_BRDN_COST,
count(decode(PROJECT_TYPE_CLASS,
'C', BURDENED_COST, 0) *
cls.CODE_PERCENTAGE / 100) CAPITAL_BRDN_COST_CNT,
sum (decode(PROJECT_TYPE_CLASS,
'B', RAW_COST, 0) *
cls.CODE_PERCENTAGE / 100) CONTRACT_RAW_COST,
count(decode(PROJECT_TYPE_CLASS,
'B', RAW_COST, 0) *
cls.CODE_PERCENTAGE / 100) CONTRACT_RAW_COST_CNT,
sum (decode(PROJECT_TYPE_CLASS,
'B', BURDENED_COST, 0) *
cls.CODE_PERCENTAGE / 100) CONTRACT_BRDN_COST,
count(decode(PROJECT_TYPE_CLASS,
'B', BURDENED_COST, 0) *
cls.CODE_PERCENTAGE / 100) CONTRACT_BRDN_COST_CNT,
sum (fpw.CAPITALIZABLE_RAW_COST *
cls.CODE_PERCENTAGE / 100) CAPITALIZABLE_RAW_COST,
count(fpw.CAPITALIZABLE_RAW_COST *
cls.CODE_PERCENTAGE / 100) CAPITALIZABLE_RAW_COST_CNT,
sum (fpw.CAPITALIZABLE_BRDN_COST *
cls.CODE_PERCENTAGE / 100) CAPITALIZABLE_BRDN_COST,
count(fpw.CAPITALIZABLE_BRDN_COST *
cls.CODE_PERCENTAGE / 100) CAPITALIZABLE_BRDN_COST_CNT,
sum (1) PROJECT_COUNT,
count(1) PROJECT_COUNT_CNT,
sum (fpw.LABOR_HRS *
cls.CODE_PERCENTAGE / 100) LABOR_HRS,
count(fpw.LABOR_HRS *
cls.CODE_PERCENTAGE / 100) LABOR_HRS_CNT,
sum (fpw.BILL_LABOR_HRS *
cls.CODE_PERCENTAGE / 100) BILL_LABOR_HRS,
count(fpw.BILL_LABOR_HRS *
cls.CODE_PERCENTAGE / 100) BILL_LABOR_HRS_CNT,
sum (fpw.QUANTITY *
cls.CODE_PERCENTAGE / 100) QUANTITY,
count(fpw.QUANTITY *
cls.CODE_PERCENTAGE / 100) QUANTITY_CNT,
sum (fpw.BILL_QUANTITY *
cls.CODE_PERCENTAGE / 100) BILL_QUANTITY,
count(fpw.BILL_QUANTITY *
cls.CODE_PERCENTAGE / 100) BILL_QUANTITY_CNT
from
PJI.PJI_FP_PROJ_ET_WT_F fpw,
PJI.PJI_PROJECT_CLASSES cls
where
fpw.PROJECT_ID = cls.PROJECT_ID
group by
fpw.TIME_ID,
fpw.PROJECT_ORGANIZATION_ID,
fpw.CALENDAR_TYPE,
fpw.PERIOD_TYPE_ID,
cls.PROJECT_CLASS_ID,
fpw.CURR_RECORD_TYPE_ID,
fpw.CURRENCY_CODE,
fpw.PROJECT_ORG_ID,
fpw.EXP_EVT_TYPE_ID,
fpw.WORK_TYPE_ID