[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, P_SELECT_LIST =>
' FACT.REVENUE "PJI_REP_MSR_1"
, FACT.PY_REVENUE "PJI_REP_MSR_2"
, FACT.REV_CHANGE_PERCENT "PJI_REP_MSR_3"
, FACT.MARGIN_PERCENT "PJI_REP_MSR_4"
, FACT.PY_MARGIN_PERCENT "PJI_REP_MSR_5"
, FACT.MAR_CHANGE_PERCENT "PJI_REP_MSR_6"
, FACT.MARGIN "PJI_REP_MSR_8"
, FACT.PY_MARGIN "PJI_REP_MSR_9"
, FACT.PY_REVENUE "PJI_REP_MSR_11"
, FACT.REVENUE "PJI_REP_MSR_10"
, FACT.PY_MARGIN_PERCENT "PJI_REP_MSR_12"
, FACT.MARGIN_PERCENT "PJI_REP_MSR_13" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PP5'
, P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', NULL'||
', NULL'||
', NULL'||
', <>'||
', <>'||
', NULL'
);
, P_SELECT_LIST =>
' FACT.REVENUE "PJI_REP_MSR_1"
, FACT.PY_REVENUE "PJI_REP_MSR_2"
, FACT.REV_CHANGE_PERCENT "PJI_REP_MSR_3"
, FACT.MARGIN_PERCENT "PJI_REP_MSR_4"
, FACT.PY_MARGIN_PERCENT "PJI_REP_MSR_5"
, FACT.MAR_CHANGE_PERCENT "PJI_REP_MSR_6"
, FACT.MARGIN "PJI_REP_MSR_8"
, FACT.PY_MARGIN "PJI_REP_MSR_9"
, FACT.REVENUE "PJI_REP_MSR_10"
, FACT.MARGIN_PERCENT "PJI_REP_MSR_11" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PP6'
, P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', :PJI_EXTRA_BND_01'||
', NULL'||
', NULL'||
', <>'||
', <>'||
', NULL'
);
, P_SELECT_LIST =>
' FACT.COST "PJI_REP_MSR_1"
, FACT.PY_COST "PJI_REP_MSR_2"
, FACT.CST_CHANGE_PERCENT "PJI_REP_MSR_3"
, FACT.COST "PJI_REP_MSR_4" '
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PP7'
, P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', NULL'||
', <>'||
', <>'||
', NULL'||
', NULL'||
', <> '
);
, P_SELECT_LIST =>
' FACT.COST "PJI_REP_MSR_1"
, FACT.PY_COST "PJI_REP_MSR_2"
, FACT.CST_CHANGE_PERCENT "PJI_REP_MSR_3"
, FACT.COST "PJI_REP_MSR_4" '
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PP8'
, P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', :PJI_EXTRA_BND_01'||
', <>'||
', <>'||
', NULL'||
', NULL'||
', <> '
);
** This table function is called from select statement
** generated by PJI engine. The function returns pl/sql table
** of records which have to be displayed in the pmv report.
** Following are the reports to which this function caters:
** 1. PJI_REP_PP5 - Project Profitability Trend
** 2. PJI_REP_PP6 - Project Profitability Cumulative Trend
** 3. PJI_REP_PP7 - Project Cost Trend
** 4. PJI_REP_PP8 - Project Cost Cumulative Trend
** ----------------------------------------------------------
*/
FUNCTION PLSQLDriver_PJI_REP_PP5(
p_Operating_Unit IN VARCHAR2 DEFAULT NULL
, p_Organization IN VARCHAR2
, p_Currency_Type IN VARCHAR2
, p_As_Of_Date IN NUMBER
, p_Period_Type IN VARCHAR2
, p_View_BY IN VARCHAR2
, p_Classifications IN VARCHAR2 DEFAULT NULL
, p_Class_Codes IN VARCHAR2 DEFAULT NULL
, p_Report_Type IN VARCHAR2 DEFAULT NULL
, p_Expenditure_Category IN VARCHAR2 DEFAULT NULL
, p_Expenditure_Type IN VARCHAR2 DEFAULT NULL
, p_Revenue_Category IN VARCHAR2 DEFAULT NULL
, p_Revenue_Type IN VARCHAR2 DEFAULT NULL
, p_Work_Type IN VARCHAR2 DEFAULT NULL
)RETURN PJI_REP_PP5_TBL
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT report_cost_type
INTO l_Report_Cost_Type
FROM pji_system_settings;
SELECT PJI_REP_PP5(
TIME_ID
, SUM( REVENUE )
, SUM( COST )
, SUM( REVENUE-COST )
, SUM( PY_REVENUE )
, SUM( PY_COST )
, SUM( PY_REVENUE-PY_COST )
, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, revenue revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
, 0 py_revenue
, 0 py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_fp_orgo_f_mv FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 revenue
, 0 cost
, revenue py_revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_fp_orgo_f_mv FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 revenue
, 0 cost
, 0 py_revenue
, 0 py_cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PP5(
TIME_ID
, SUM( REVENUE )
, SUM( COST )
, SUM( REVENUE-COST )
, SUM( PY_REVENUE )
, SUM( PY_COST )
, SUM( PY_REVENUE-PY_COST )
, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, revenue revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
, 0 py_revenue
, 0 py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_fp_clso_f_mv FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.project_org_id = HOU.id
AND FCT.project_organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.project_class_id = CLS.id
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 revenue
, 0 cost
, revenue py_revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_fp_clso_f_mv FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.project_org_id = HOU.id
AND FCT.project_organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.project_class_id = CLS.id
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 revenue
, 0 cost
, 0 py_revenue
, 0 py_cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PP5(
TIME_ID
, SUM( REVENUE )
, SUM( COST )
, SUM( REVENUE-COST )
, SUM( PY_REVENUE )
, SUM( PY_COST )
, SUM( PY_REVENUE-PY_COST )
, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, revenue revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
, 0 py_revenue
, 0 py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_et_rt_dim_tmp ET
, PJI_FP_ORGO_ET_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 revenue
, 0 cost
, revenue py_revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_et_rt_dim_tmp ET
, PJI_FP_ORGO_ET_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 revenue
, 0 cost
, 0 py_revenue
, 0 py_cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PP5(
TIME_ID
, SUM( REVENUE )
, SUM( COST )
, SUM( REVENUE-COST )
, SUM( PY_REVENUE )
, SUM( PY_COST )
, SUM( PY_REVENUE-PY_COST )
, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, null revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
, 0 py_revenue
, 0 py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_wt_dim_tmp WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.work_type_id = WT.id
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 revenue
, 0 cost
, null py_revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_wt_dim_tmp WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.work_type_id = WT.id
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 revenue
, 0 cost
, 0 py_revenue
, 0 py_cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PP5(
TIME_ID
, SUM( REVENUE )
, SUM( COST )
, SUM( REVENUE-COST )
, SUM( PY_REVENUE )
, SUM( PY_COST )
, SUM( PY_REVENUE-PY_COST )
, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, revenue revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
, 0 py_revenue
, 0 py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_pmv_et_rt_dim_tmp ET
, PJI_FP_CLSO_ET_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.project_org_id = HOU.id
AND FCT.project_organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.project_class_id = CLS.id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 revenue
, 0 cost
, revenue py_revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_pmv_et_rt_dim_tmp ET
, PJI_FP_CLSO_ET_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.project_org_id = HOU.id
AND FCT.project_organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.project_class_id = CLS.id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 revenue
, 0 cost
, 0 py_revenue
, 0 py_cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PP5(
TIME_ID
, SUM( REVENUE )
, SUM( COST )
, SUM( REVENUE-COST )
, SUM( PY_REVENUE )
, SUM( PY_COST )
, SUM( PY_REVENUE-PY_COST )
, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, null revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
, 0 py_revenue
, 0 py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_et_rt_dim_tmp ET
, pji_pmv_wt_dim_tmp WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 revenue
, 0 cost
, null py_revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_et_rt_dim_tmp ET
, pji_pmv_wt_dim_tmp WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 revenue
, 0 cost
, 0 py_revenue
, 0 py_cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PP5(
TIME_ID
, SUM( REVENUE )
, SUM( COST )
, SUM( REVENUE-COST )
, SUM( PY_REVENUE )
, SUM( PY_COST )
, SUM( PY_REVENUE-PY_COST )
, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, null revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
, 0 py_revenue
, 0 py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_pmv_wt_dim_tmp WT
, PJI_FP_CLSO_ET_WT_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.project_org_id = HOU.id
AND FCT.project_organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.project_class_id = CLS.id
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 revenue
, 0 cost
, null py_revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_pmv_wt_dim_tmp WT
, PJI_FP_CLSO_ET_WT_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.project_org_id = HOU.id
AND FCT.project_organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.project_class_id = CLS.id
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 revenue
, 0 cost
, 0 py_revenue
, 0 py_cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PP5(
TIME_ID
, SUM( REVENUE )
, SUM( COST )
, SUM( REVENUE-COST )
, SUM( PY_REVENUE )
, SUM( PY_COST )
, SUM( PY_REVENUE-PY_COST )
, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, null revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
, 0 py_revenue
, 0 py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_pmv_et_rt_dim_tmp ET
, pji_pmv_wt_dim_tmp WT
, PJI_FP_CLSO_ET_WT_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.project_org_id = HOU.id
AND FCT.project_organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.project_class_id = CLS.id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 revenue
, 0 cost
, null py_revenue
, DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
FROM
pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_pmv_et_rt_dim_tmp ET
, pji_pmv_wt_dim_tmp WT
, PJI_FP_CLSO_ET_WT_F_MV FCT
, pji_pmv_org_dim_tmp HOU
WHERE
FCT.project_org_id = HOU.id
AND FCT.project_organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND FCT.project_class_id = CLS.id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 revenue
, 0 cost
, 0 py_revenue
, 0 py_cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;