yangtingkun 发表于 2012-2-2 17:41
看看视图的定义
BTW:这个SQL还包括全文检索？
这是我们产品中的定义，还不确定客户有没有修改过。
按我们的产品，是不会有并行查询的
对了，怎么知道这个SQL是否有全文检索？
CREATE OR REPLACE FORCE VIEW PM_PROJECT_LIST_V
(
USER_ID,
PROJECT_ID,
PROJECT_NO,
PROJECT_TYPE,
STATE_ID,
INTEGRATION_MODE,
PROGRAM_ID,
OVERALL_PROJECT_HEALTH,
HEALTH_OVERRIDE_DESC_HOVER,
PROJECT_SCHEDULE_HEALTH,
PERCENT_COMPLETE,
PROJECT_NAME,
STATUS_ID,
STATE_NAME,
REGION_ID,
SCHEDULED_START_DATE,
SCHEDULED_FINISH_DATE,
PROJECT_WORKPLAN_ID,
PROJECT_MANAGER_LIST,
RESOURCE_FULL_NAME_LIST,
RESOURCE_FULL_NAME_LIST_HOVER,
BUDGET_BSE,
BUDGET_LCL,
BUDGET_CURRENCY_ID,
PROJECT_CURRENCY_ID,
PLANNED_LABOR_COST_LCL,
PLANNED_NON_LABOR_COST_LCL,
PLANNED_COST_TOTAL_LCL,
ACTUAL_LABOR_COST_LCL,
ACTUAL_NON_LABOR_COST_LCL,
ACTUAL_COST_TOTAL_LCL,
PLANNED_LABOR_COST_BSE,
PLANNED_NON_LABOR_COST_BSE,
PLANNED_COST_TOTAL_BSE,
ACTUAL_LABOR_COST_BSE,
ACTUAL_NON_LABOR_COST_BSE,
ACTUAL_COST_TOTAL_BSE,
PLANNED_VALUE_BSE,
EARNED_VALUE_BSE,
COST_VARIANCE_BSE,
SCHEDULE_VARIANCE_BSE,
COST_PERF_INDEX_BSE,
SCHED_PERF_INDEX_BSE,
PLANNED_VALUE_LCL,
EARNED_VALUE_LCL,
COST_VARIANCE_LCL,
SCHEDULE_VARIANCE_LCL,
COST_PERF_INDEX_LCL,
SCHED_PERF_INDEX_LCL,
BASELINE_LABOR_COST_LCL,
BASELINE_NON_LABOR_COST_LCL,
BASELINE_COST_TOTAL_LCL,
BASELINE_LABOR_COST_BSE,
BASELINE_NON_LABOR_COST_BSE,
BASELINE_COST_TOTAL_BSE,
COST_HEALTH,
PROJECT_STATUS_CODE
)
AS
SELECT USER_ID,
PROJECT_ID,
PROJECT_NO,
PROJECT_TYPE,
STATE_ID,
INTEGRATION_MODE,
PROGRAM_ID,
OVERALL_PROJECT_HEALTH,
HEALTH_OVERRIDE_DESC_HOVER,
PROJECT_SCHEDULE_HEALTH,
PERCENT_COMPLETE,
PROJECT_NAME,
STATUS_ID,
STATE_NAME,
REGION_ID,
SCHEDULED_START_DATE,
SCHEDULED_FINISH_DATE,
PROJECT_WORKPLAN_ID,
PROJECT_MANAGER_LIST,
RESOURCE_FULL_NAME_LIST,
RESOURCE_FULL_NAME_LIST_HOVER,
DECODE (
CAN_ACCESS_BUDGET,
'Y', TO_CHAR (
(SELECT kcst_cost_util.get_budget_plan_total (BUDGET_ID)
FROM DUAL)),
'')
AS BUDGET_BSE,
DECODE (
CAN_ACCESS_BUDGET,
'Y', TO_CHAR (
(SELECT kcst_cost_util.get_budget_plan_total_lcl (
BUDGET_ID)
FROM DUAL)),
'')
AS BUDGET_LCL,
BUDGET_CURRENCY_ID,
PROJECT_CURRENCY_ID,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_PLAN_CAP_LABOR_LCL + T_PLAN_OP_LABOR_LCL)
/ 10000),
NULL),
'')
AS PLANNED_LABOR_COST_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_PLAN_CAP_NON_LABOR_LCL
+ T_PLAN_OP_NON_LABOR_LCL)
/ 10000),
NULL),
'')
AS PLANNED_NON_LABOR_COST_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_PLAN_CAP_LABOR_LCL
+ T_PLAN_CAP_NON_LABOR_LCL
+ T_PLAN_OP_LABOR_LCL
+ T_PLAN_OP_NON_LABOR_LCL)
/ 10000),
NULL),
'')
AS PLANNED_COST_TOTAL_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_ACT_CAP_LABOR_LCL + T_ACT_OP_LABOR_LCL)
/ 10000),
NULL),
'')
AS ACTUAL_LABOR_COST_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_ACT_CAP_NON_LABOR_LCL
+ T_ACT_OP_NON_LABOR_LCL)
/ 10000),
NULL),
'')
AS ACTUAL_NON_LABOR_COST_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_ACT_CAP_LABOR_LCL
+ T_ACT_CAP_NON_LABOR_LCL
+ T_ACT_OP_LABOR_LCL
+ T_ACT_OP_NON_LABOR_LCL)
/ 10000),
NULL),
'')
AS ACTUAL_COST_TOTAL_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_PLAN_CAP_LABOR_BSE + T_PLAN_OP_LABOR_BSE)
/ 10000),
NULL),
'')
AS PLANNED_LABOR_COST_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_PLAN_CAP_NON_LABOR_BSE
+ T_PLAN_OP_NON_LABOR_BSE)
/ 10000),
NULL),
'')
AS PLANNED_NON_LABOR_COST_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_PLAN_CAP_LABOR_BSE
+ T_PLAN_CAP_NON_LABOR_BSE
+ T_PLAN_OP_LABOR_BSE
+ T_PLAN_OP_NON_LABOR_BSE)
/ 10000),
NULL),
'')
AS PLANNED_COST_TOTAL_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_ACT_CAP_LABOR_BSE + T_ACT_OP_LABOR_BSE)
/ 10000),
NULL),
'')
AS ACTUAL_LABOR_COST_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_ACT_CAP_NON_LABOR_BSE
+ T_ACT_OP_NON_LABOR_BSE)
/ 10000),
NULL),
'')
AS ACTUAL_NON_LABOR_COST_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(T_ACT_CAP_LABOR_BSE
+ T_ACT_CAP_NON_LABOR_BSE
+ T_ACT_OP_LABOR_BSE
+ T_ACT_OP_NON_LABOR_BSE)
/ 10000),
NULL),
'')
AS ACTUAL_COST_TOTAL_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (IS_COST_ENABLED,
'true', TO_CHAR ( (PLANNED_VALUE_BSE) / 10000),
NULL),
'')
AS PLANNED_VALUE_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (IS_COST_ENABLED,
'true', TO_CHAR ( (EARNED_VALUE_BSE) / 10000),
NULL),
'')
AS EARNED_VALUE_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(EARNED_VALUE_BSE
- (T_ACT_CAP_LABOR_BSE
+ T_ACT_OP_LABOR_BSE
+ T_ACT_CAP_NON_LABOR_BSE
+ T_ACT_OP_NON_LABOR_BSE))
/ 10000),
NULL),
'')
AS COST_VARIANCE_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(EARNED_VALUE_BSE - PLANNED_VALUE_BSE)
/ 10000),
NULL),
'')
AS SCHEDULE_VARIANCE_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (IS_COST_ENABLED,
'true', TO_CHAR (ROUND (CPI, 2)),
NULL),
'')
AS COST_PERF_INDEX_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (IS_COST_ENABLED,
'true', TO_CHAR (ROUND (SPI, 2)),
NULL),
'')
AS SCHED_PERF_INDEX_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (IS_COST_ENABLED,
'true', TO_CHAR ( (PLANNED_VALUE_LCL) / 10000),
NULL),
'')
AS PLANNED_VALUE_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (IS_COST_ENABLED,
'true', TO_CHAR ( (EARNED_VALUE_LCL) / 10000),
NULL),
'')
AS EARNED_VALUE_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(EARNED_VALUE_LCL
- (T_ACT_CAP_LABOR_LCL
+ T_ACT_OP_LABOR_LCL
+ T_ACT_CAP_NON_LABOR_LCL
+ T_ACT_OP_NON_LABOR_LCL))
/ 10000),
NULL),
'')
AS COST_VARIANCE_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(EARNED_VALUE_LCL - PLANNED_VALUE_LCL)
/ 10000),
NULL),
'')
AS SCHEDULE_VARIANCE_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (IS_COST_ENABLED,
'true', TO_CHAR (ROUND (CPI, 2)),
NULL),
'')
AS COST_PERF_INDEX_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (IS_COST_ENABLED,
'true', TO_CHAR (ROUND (SPI, 2)),
NULL),
'')
AS SCHED_PERF_INDEX_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(B_PLAN_CAP_LABOR_LCL + B_PLAN_OP_LABOR_LCL)
/ 10000),
NULL),
'')
AS BASELINE_LABOR_COST_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(B_PLAN_CAP_NON_LABOR_LCL
+ B_PLAN_OP_NON_LABOR_LCL)
/ 10000),
NULL),
'')
AS BASELINE_NON_LABOR_COST_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(B_PLAN_CAP_LABOR_LCL
+ B_PLAN_OP_LABOR_LCL
+ B_PLAN_CAP_NON_LABOR_LCL
+ B_PLAN_OP_NON_LABOR_LCL)
/ 10000),
NULL),
'')
AS BASELINE_COST_TOTAL_LCL,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(B_PLAN_CAP_LABOR_BSE + B_PLAN_OP_LABOR_BSE)
/ 10000),
NULL),
'')
AS BASELINE_LABOR_COST_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(B_PLAN_CAP_NON_LABOR_BSE
+ B_PLAN_OP_NON_LABOR_BSE)
/ 10000),
NULL),
'')
AS BASELINE_NON_LABOR_COST_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', TO_CHAR (
(B_PLAN_CAP_LABOR_BSE
+ B_PLAN_OP_LABOR_BSE
+ B_PLAN_CAP_NON_LABOR_BSE
+ B_PLAN_OP_NON_LABOR_BSE)
/ 10000),
NULL),
'')
AS BASELINE_COST_TOTAL_BSE,
DECODE (
CAN_ACCESS_COST,
'Y', DECODE (
IS_COST_ENABLED,
'true', KNTA_EXCEPTION_UTIL.GET_COST_HEALTH (PROJECT_ID),
NULL),
'')
AS COST_HEALTH,
PROJECT_STATUS_CODE
FROM (SELECT u.user_id AS USER_ID,
prj.project_id AS PROJECT_ID,
prj.pfm_request_id AS PROJECT_NO,
pt.source_id AS PROJECT_TYPE,
prj.status AS STATE_ID,
(SELECT pm_utils.get_project_attribute_value (
'msp.integrationMode',
prj.project_id)
FROM DUAL)
AS INTEGRATION_MODE,
prog.program_id AS PROGRAM_ID,
pr.overall_health_indicator AS OVERALL_PROJECT_HEALTH,
pr.OVERRIDE_DESCRIPTION AS HEALTH_OVERRIDE_DESC_HOVER,
pr.schedule_health_indicator AS PROJECT_SCHEDULE_HEALTH,
-- Defect #184880: Percent Complete when formatted in java for the overview page rounds 0.5 down, whereas oracle rounds it up, so here we need to
-- check if the percent complete is x.5, and if it is, we subtract then round to get the same results as in java.
DECODE (ta.perc_complete - FLOOR (ta.perc_complete),
0.5, ROUND (ta.perc_complete - 0.5, 0),
ROUND (ta.perc_complete, 0))
AS PERCENT_COMPLETE,
prj.project_name AS PROJECT_NAME,
req.status_id AS STATUS_ID,
status.status_name AS STATE_NAME,
prj.region_id AS REGION_ID,
start_period.long_name AS scheduled_start_date,
end_period.long_name AS scheduled_finish_date,
wp.project_id AS PROJECT_WORKPLAN_ID,
kfpp.prj_financial_summary_id AS BUDGET_ID,
(SELECT pm_utils.get_project_managers (prj.project_id)
FROM DUAL)
AS PROJECT_MANAGER_LIST,
(SELECT pm_utils.Find_Full_Names (
(SELECT pm_utils.get_project_managers (
prj.project_id)
FROM DUAL))
FROM DUAL)
AS RESOURCE_FULL_NAME_LIST,
(SELECT pm_utils.Find_Full_Names (
(SELECT pm_utils.get_project_managers (
prj.project_id)
FROM DUAL))
FROM DUAL)
|| '#@#'
|| (SELECT KDRV_MULTI.Find_User_Emails (
(SELECT pm_utils.get_project_managers (
prj.project_id)
FROM DUAL))
FROM DUAL)
|| '#@#'
|| (SELECT KDRV_MULTI.Find_User_PhoneNumbers (
(SELECT pm_utils.get_project_managers (
prj.project_id)
FROM DUAL))
FROM DUAL)
AS RESOURCE_FULL_NAME_LIST_HOVER,
(SELECT kcst_cost_util.get_fs_lcl_currency (
kfpp.prj_financial_summary_id)
FROM DUAL)
AS BUDGET_CURRENCY_ID,
'-999' AS PROJECT_CURRENCY_ID,
(SELECT kcst_budget_security.can_user_access_budget (
u.user_id,
kfpp.prj_financial_summary_id)
FROM DUAL)
AS CAN_ACCESS_BUDGET,
(SELECT pm_utils.Can_User_Access_Cost (u.user_id,
prj.project_id)
FROM DUAL)
AS CAN_ACCESS_COST,
(SELECT pm_utils.get_attribute_value ('CE.enableFM',
wp.root_task_id)
FROM DUAL)
AS IS_COST_ENABLED,
wtc.plan_cap_labor_lcl AS T_PLAN_CAP_LABOR_LCL,
wtc.plan_op_labor_lcl AS T_PLAN_OP_LABOR_LCL,
wtc.plan_cap_non_labor_lcl AS T_PLAN_CAP_NON_LABOR_LCL,
wtc.plan_op_non_labor_lcl AS T_PLAN_OP_NON_LABOR_LCL,
wtc.act_cap_labor_lcl AS T_ACT_CAP_LABOR_LCL,
wtc.act_op_labor_lcl AS T_ACT_OP_LABOR_LCL,
wtc.act_cap_non_labor_lcl AS T_ACT_CAP_NON_LABOR_LCL,
wtc.act_op_non_labor_lcl AS T_ACT_OP_NON_LABOR_LCL,
wtc.plan_cap_labor_bse AS T_PLAN_CAP_LABOR_BSE,
wtc.plan_op_labor_bse AS T_PLAN_OP_LABOR_BSE,
wtc.plan_cap_non_labor_bse AS T_PLAN_CAP_NON_LABOR_BSE,
wtc.plan_op_non_labor_bse AS T_PLAN_OP_NON_LABOR_BSE,
wtc.act_cap_labor_bse AS T_ACT_CAP_LABOR_BSE,
wtc.act_op_labor_bse AS T_ACT_OP_LABOR_BSE,
wtc.act_cap_non_labor_bse AS T_ACT_CAP_NON_LABOR_BSE,
wtc.act_op_non_labor_bse AS T_ACT_OP_NON_LABOR_BSE,
wtc.planned_value_bse AS PLANNED_VALUE_BSE,
wtc.earned_value_bse AS EARNED_VALUE_BSE,
wtc.planned_value_lcl AS PLANNED_VALUE_LCL,
wtc.earned_value_lcl AS EARNED_VALUE_LCL,
wtc.CPI AS CPI,
wtc.SPI AS SPI,
btc.plan_cap_labor_lcl AS B_PLAN_CAP_LABOR_LCL,
btc.plan_op_labor_lcl AS B_PLAN_OP_LABOR_LCL,
btc.plan_cap_non_labor_lcl AS B_PLAN_CAP_NON_LABOR_LCL,
btc.plan_op_non_labor_lcl AS B_PLAN_OP_NON_LABOR_LCL,
btc.plan_cap_labor_bse AS B_PLAN_CAP_LABOR_BSE,
btc.plan_op_labor_bse AS B_PLAN_OP_LABOR_BSE,
btc.plan_cap_non_labor_bse AS B_PLAN_CAP_NON_LABOR_BSE,
btc.plan_op_non_labor_bse AS B_PLAN_OP_NON_LABOR_BSE,
req.status_code AS PROJECT_STATUS_CODE
FROM PM_PROJECTS prj,
PM_PROJECT_TYPES pt,
PM_PROJECT_ROLLUP pr,
PM_WORK_PLANS wp,
PM_WORK_PLANS baseline,
WP_TASK_COSTS wtc,
WP_TASK_COSTS btc,
PGM_PROGRAM_CONTENT prog,
PFM_LIFECYCLE_PARENT_ENTITY lce,
KCRT_FG_PFM_PROJECT kfpp,
PPM_FISCAL_PERIODS start_period,
PPM_FISCAL_PERIODS end_period,
WP_TASK_ACTUALS ta,
WP_TASKS wt,
WP_TASKS bt,
KCRT_REQUESTS req,
KCRT_STATUSES status,
KNTA_USERS u -- NOTE: When accessing this view, the where clause should always specify [SYS.USER_ID] = U.USER_ID, else this view will be VERY slow
WHERE prj.rollup_id = pr.rollup_id
AND wp.project_id(+) = prj.project_id
AND baseline.project_id(+) = prj.project_id
AND wp.entity_type(+) = 'WORK_PLAN'
AND baseline.entity_type(+) = 'BASE_LINE'
AND baseline.is_active_flag(+) = 'Y'
AND wt.task_id(+) = wp.root_task_id
AND bt.task_id(+) = baseline.root_task_id
AND wtc.task_cost_id(+) = wt.task_cost_id
AND btc.task_cost_id(+) = bt.task_cost_id
AND wt.task_actuals_id = ta.actuals_id(+)
AND lce.project_req_id(+) = prj.pfm_request_id
AND prog.content_id(+) = lce.lifecycle_id
AND prj.project_type_id = pt.project_type_id
AND kfpp.prj_project_id(+) = prj.project_id
AND start_period.fiscal_period_id(+) =
prj.start_date_period
AND end_period.fiscal_period_id(+) = prj.finish_date_period
AND req.request_id(+) = prj.pfm_request_id
AND status.status_id = req.status_id
AND (SELECT pm_utils.Can_User_Access_Project (
u.user_id,
prj.project_id)
FROM DUAL) = 'Y');
|
