[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_SELECT_LIST =>
'FACT.TOTAL_BOOKINGS "PJI_REP_MSR_1",
FACT.BOOKINGS_CHANGE "PJI_REP_MSR_2",
FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_3",
FACT.BACKLOG_CHANGE "PJI_REP_MSR_4",
FACT.BTB_RATIO "PJI_REP_MSR_5",
FACT.BTB_CHANGE "PJI_REP_MSR_6",
FACT.PRIOR_TOTAL_BOOKINGS "PJI_REP_MSR_7",
FACT.PRIOR_TOTAL_ENDING_BACKLOG "PJI_REP_MSR_8",
FACT.BTB_TOTAL_BOOKINGS "PJI_REP_MSR_9",
FACT.BTB_REVENUE "PJI_REP_MSR_10",
FACT.PRIOR_BTB_TOTAL_BOOKINGS "PJI_REP_MSR_11",
FACT.PRIOR_BTB_REVENUE "PJI_REP_MSR_12",
FACT.TOTAL_BOOKINGS "PJI_REP_MSR_13",
FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_14",
FACT.PRIOR_YEAR_BTB_RATIO "PJI_REP_MSR_16",
FACT.CURRENT_YEAR_BTB_RATIO "PJI_REP_MSR_15",
FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_13",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_14",
FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_15",
FACT.PJI_REP_TOTAL_16 "PJI_REP_TOTAL_16"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PBB1'
, P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS_BACKLOG.PLSQLDriver_PJI_REP_PBB1'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>');
P_SELECT_LIST => 'FACT.PROJECT_ID "PJI_REP_MSR_28",
FACT.PROJECT_NAME "VIEWBY",
FACT.PROJECT_NUMBER "PJI_REP_MSR_2",
FACT.URL_PARAMETERS01 "PJI_REP_MSR_27",
FACT.URL_PARAMETERS01 "PJI_REP_MSR_30",
FACT.PRIMARY_CUSTOMER "PJI_REP_MSR_3",
FACT.PROJECT_TYPE "PJI_REP_MSR_4",
FACT.ORGANIZATION "PJI_REP_MSR_5",
FACT.PROJECT_MANAGER "PJI_REP_MSR_6",
FACT.PROJECT_START_DATE "PJI_REP_MSR_7",
FACT.XTD_ORIG_BKNG_AMT "PJI_REP_MSR_8",
FACT.XTD_ADDT_BKNG_AMT "PJI_REP_MSR_9",
FACT.XTD_TOTAL_BOOKINGS "PJI_REP_MSR_10",
FACT.ITD_TOTAL_BOOKINGS "PJI_REP_MSR_11",
FACT.BACKLOG_NOT_STARTED "PJI_REP_MSR_12",
FACT.ACTIVE_BACKLOG "PJI_REP_MSR_13",
FACT.DORMANT_BACKLOG "PJI_REP_MSR_14",
FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_15",
FACT.PROJECT_CLOSE_DATE "PJI_REP_MSR_16",
FACT.LOST_BACKLOG "PJI_REP_MSR_17",
FACT.REVENUE_AT_RISK "PJI_REP_MSR_18",
FACT.XTD_ADJT_BKNG_AMT "PJI_REP_MSR_19",
FACT.XTD_CANC_BKNG_AMT "PJI_REP_MSR_20",
FACT.XTD_REVENUE "PJI_REP_MSR_21",
FACT.ITD_ORIG_BKNG_AMT "PJI_REP_MSR_22",
FACT.ITD_ADDT_BKNG_AMT "PJI_REP_MSR_23",
FACT.ITD_ADJT_BKNG_AMT "PJI_REP_MSR_24",
FACT.ITD_CANC_BKNG_AMT "PJI_REP_MSR_25",
FACT.ITD_REVENUE "PJI_REP_MSR_26",
FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PBB2'
, P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS_BACKLOG.PLSQLDriver_PJI_REP_PBB2'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>');
P_SELECT_LIST => 'FACT.BACKLOG "PJI_REP_MSR_1",
FACT.ORIGINAL_BOOKINGS "PJI_REP_MSR_2",
FACT.ADDITIONAL_BOOKINGS "PJI_REP_MSR_3",
FACT.BOOKINGS_ADJUSTMENTS "PJI_REP_MSR_4",
FACT.CANCELLATIONS "PJI_REP_MSR_5",
FACT.TOTAL_NET_BOOKINGS "PJI_REP_MSR_6",
FACT.ACCRUED_REVENUE "PJI_REP_MSR_7",
FACT.REVENUE_AT_RISK_ITD "PJI_REP_MSR_8",
FACT.LOST_BACKLOG_ITD "PJI_REP_MSR_9",
FACT.BACKLOG_ITD "PJI_REP_MSR_10",
FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PBB3'
, P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS_BACKLOG.PLSQLDriver_PBB3'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> ');
P_SELECT_LIST => 'FACT.PROJECT_ID "PJI_REP_MSR_18",
FACT.PROJECT_NAME "VIEWBY",
FACT.PROJECT_NUMBER "PJI_REP_MSR_2",
FACT.URL_PARAMETERS01 "PJI_REP_MSR_20",
FACT.URL_PARAMETERS01 "PJI_REP_MSR_30",
FACT.PRIMARY_CUSTOMER "PJI_REP_MSR_3",
FACT.PROJECT_TYPE "PJI_REP_MSR_4",
FACT.ORGANIZATION "PJI_REP_MSR_5",
FACT.PROJECT_MANAGER "PJI_REP_MSR_6",
FACT.PROJECT_START_DATE "PJI_REP_MSR_7",
FACT.BEGINNING_BACKLOG "PJI_REP_MSR_8",
FACT.XTD_ORIG_BKNG_AMT "PJI_REP_MSR_9",
FACT.XTD_ADDT_BKNG_AMT "PJI_REP_MSR_10",
FACT.XTD_ADJT_BKNG_AMT "PJI_REP_MSR_11",
FACT.XTD_CANC_BKNG_AMT "PJI_REP_MSR_12",
FACT.XTD_TOTAL_BOOKINGS "PJI_REP_MSR_13",
FACT.XTD_REVENUE "PJI_REP_MSR_14",
FACT.REVENUE_AT_RISK "PJI_REP_MSR_15",
FACT.LOST_BACKLOG "PJI_REP_MSR_16",
FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_17",
FACT.PJI_REP_TOTAL_14 "PJI_REP_TOTAL_14",
FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_11",
FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_12",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_13",
FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10",
FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PBB4'
, P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS_BACKLOG.PLSQLDriver_PJI_REP_PBB2'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', ''N''');
** This 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_PBB1 - Project Bookings & Backlog Summary
** ----------------------------------------------------------
*/
FUNCTION PLSQLDriver_PJI_REP_PBB1(
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_Comparator_Type IN VARCHAR2 DEFAULT NULL
)RETURN PJI_REP_PBB1_TBL
IS
PRAGMA AUTONOMOUS_TRANSACTION;
** as of date as per comparator type selected
** in the pmv report.
*/
l_BTB_C_As_Of_Date :=PJI_PMV_ENGINE.Convert_AS_OF_DATE(p_As_Of_Date, p_Period_Type, p_Comparator_Type);
SELECT NVL(book_to_bill_days,0)
INTO l_BTB_Days
FROM
pji_system_settings;
SELECT PJI_REP_PBB1( ORG_ID
, ORGANIZATION_ID
, TIME_ID
, TIME_KEY
, PROJECT_CLASS_ID
, SUM( TOTAL_BOOKINGS )
, SUM( PRIOR_TOTAL_BOOKINGS )
, 0
, SUM( TOTAL_ENDING_BACKLOG )
, SUM( PRIOR_TOTAL_ENDING_BACKLOG )
, 0
, SUM( BTB_TOTAL_BOOKINGS )
, SUM( BTB_REVENUE )
, 0
, SUM( PRIOR_BTB_TOTAL_BOOKINGS )
, SUM( PRIOR_BTB_REVENUE )
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0,0,0,0,0)
BULK COLLECT INTO l_Total_Bookings_Backlog_Tab
FROM
( SELECT /*+ ORDERED */
HOU.name org_id
, HORG.name organization_id
, TIME.name time_id
, -1 time_key
, '-1' project_class_id
, (FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount + FCT.cancelled_funding_amount) total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_ac_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 */
HOU.name org_id
, HORG.name organization_id
, TIME.name time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, (FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount +FCT.cancelled_funding_amount) prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_tcmp_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_ac_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 */
HOU.name org_id
, HORG.name organization_id
, TIME.name time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, (FCT.dormant_backlog_start+FCT.active_backlog+FCT.dormant_backlog_inactiv) total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_itd_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_ac_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 TIME.comparator_type = 'I'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
HOU.name org_id
, HORG.name organization_id
, TIME.name time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, (FCT.dormant_backlog_start+FCT.active_backlog+FCT.dormant_backlog_inactiv) prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_itd_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_ac_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 TIME.comparator_type = 'D'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
HOU.name org_id
, HORG.name organization_id
, '-1' time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount btb_total_bookings
, FCT.revenue btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_orgz_dim_tmp HORG
, pji_ac_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 BETWEEN p_As_Of_Date-l_BTB_Days AND p_As_Of_Date
AND FCT.PERIOD_TYPE_ID = 1
AND FCT.CALENDAR_TYPE = l_Calendar_Type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
HOU.name org_id
, HORG.name organization_id
, '-1' time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount prior_btb_total_bookings
, FCT.revenue prior_btb_revenue
FROM pji_pmv_orgz_dim_tmp HORG
, pji_ac_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 BETWEEN l_BTB_C_As_Of_Date-l_BTB_Days AND l_BTB_C_As_Of_Date
AND FCT.PERIOD_TYPE_ID = 1
AND FCT.CALENDAR_TYPE = l_Calendar_Type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT name org_id
, '-1' organization_id
, '-1' time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_org_dim_tmp
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id
, name organization_id
, '-1' time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_orgz_dim_tmp
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id
, '-1' organization_id
, name time_id
, order_by_id time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
ORG_ID
, ORGANIZATION_ID
, TIME_KEY
, TIME_ID
, PROJECT_CLASS_ID;
SELECT PJI_REP_PBB1( ORG_ID
, ORGANIZATION_ID
, TIME_ID
, TIME_KEY
, PROJECT_CLASS_ID
, SUM( TOTAL_BOOKINGS )
, SUM( PRIOR_TOTAL_BOOKINGS )
, 0
, SUM( TOTAL_ENDING_BACKLOG )
, SUM( PRIOR_TOTAL_ENDING_BACKLOG )
, 0
, SUM( BTB_TOTAL_BOOKINGS )
, SUM( BTB_REVENUE )
, 0
, SUM( PRIOR_BTB_TOTAL_BOOKINGS )
, SUM( PRIOR_BTB_REVENUE )
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0,0,0,0,0)
BULK COLLECT INTO l_Total_Bookings_Backlog_Tab
FROM
( SELECT /*+ ORDERED */
HOU.name org_id
, HORG.name organization_id
, TIME.name time_id
, -1 time_key
, CLS.name project_class_id
, (FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount) total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_time_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_ac_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 */
HOU.name org_id
, HORG.name organization_id
, TIME.name time_id
, -1 time_key
, CLS.name project_class_id
, 0 total_bookings
, (FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount) prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_tcmp_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_ac_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 */
HOU.name org_id
, HORG.name organization_id
, TIME.name time_id
, -1 time_key
, CLS.name project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, (FCT.dormant_backlog_start+FCT.active_backlog+FCT.dormant_backlog_inactiv) total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_itd_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_ac_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 TIME.comparator_type = 'I'
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 */
HOU.name org_id
, HORG.name organization_id
, TIME.name time_id
, -1 time_key
, CLS.name project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, (FCT.dormant_backlog_start+FCT.active_backlog+FCT.dormant_backlog_inactiv) prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_itd_dim_tmp TIME
, pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_ac_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 TIME.comparator_type = 'D'
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 */
HOU.name org_id
, HORG.name organization_id
, '-1' time_id
, -1 time_key
, CLS.name project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount btb_total_bookings
, FCT.revenue btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_ac_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 BETWEEN p_As_Of_Date-l_BTB_Days AND p_As_Of_Date
AND FCT.PERIOD_TYPE_ID = 1
AND FCT.CALENDAR_TYPE = l_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 */
HOU.name org_id
, HORG.name organization_id
, '-1' time_id
, -1 time_key
, CLS.name project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount prior_btb_total_bookings
, FCT.revenue prior_btb_revenue
FROM pji_pmv_orgz_dim_tmp HORG
, pji_pmv_cls_dim_tmp CLS
, pji_ac_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 BETWEEN l_BTB_C_As_Of_Date-l_BTB_Days AND l_BTB_C_As_Of_Date
AND FCT.PERIOD_TYPE_ID = 1
AND FCT.CALENDAR_TYPE = l_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 org_id
, '-1' organization_id
, '-1' time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_org_dim_tmp
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id
, name organization_id
, '-1' time_id
, -1 time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_orgz_dim_tmp
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id
, '-1' organization_id
, name time_id
, order_by_id time_key
, '-1' project_class_id
, 0 total_bookings
, 0 prior_total_bookings
, 0
, 0 total_ending_backlog
, 0 prior_total_ending_backlog
, 0
, 0 btb_total_bookings
, 0 btb_revenue
, 0
, 0 prior_btb_total_bookings
, 0 prior_btb_revenue
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1'
UNION ALL
SELECT '-1' ORG_ID
,'-1' ORGANIZATION_ID
,'-1' TIME_ID
, -1 TIME_KEY
, NAME PROJECT_CLASS_ID
, 0 TOTAL_BOOKINGS
, 0 PRIOR_TOTAL_BOOKINGS
, 0
, 0 TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0
, 0 BTB_TOTAL_BOOKINGS
, 0 BTB_REVENUE
, 0
, 0 PRIOR_BTB_TOTAL_BOOKINGS
, 0 PRIOR_BTB_REVENUE
FROM PJI_PMV_CLS_DIM_TMP
WHERE NAME <> '-1'
)
GROUP BY
ORG_ID
, ORGANIZATION_ID
, TIME_KEY
, TIME_ID
, PROJECT_CLASS_ID;
l_Total_Bookings_Backlog_Tab.DELETE(l_Top_Org_Index);
** 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_PBB2 - PROJECT BOOKINGS & BACKLOG SUMMARY
** 2. PJI_REP_PBB4 - PROJECT BOOKINGS & BACKLOG SUMMARY
** ----------------------------------------------------------
*/
FUNCTION PLSQLDRIVER_PJI_REP_PBB2 (
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_RUN_REVENUE_AT_RISK IN VARCHAR2 DEFAULT 'N'
)
RETURN PJI_REP_PBB2_TBL
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT PJI_REP_PBB2 (PROJECT_ID,
PROJECT_NAME,
PROJECT_NUMBER,
PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_Of_Date,p_Period_Type),
PRIMARY_CUSTOMER,
PROJECT_TYPE,
ORGANIZATION,
PROJECT_MANAGER,
TRUNC (PROJECT_START_DATE),
TRUNC (PROJECT_CLOSE_DATE),
SUM (ORIGINAL_BOOKINGS_AMOUNT),
SUM (ADDITIONAL_BOOKINGS_AMOUNT),
SUM (ADJUSTMENT_BOOKINGS_AMOUNT),
-SUM (CANCELLED_BOOKINGS_AMOUNT),
SUM (REVENUE),
0,
SUM (ITD_ORIGINAL_BOOKINGS_AMOUNT),
SUM (ITD_ADDITIONAL_BOOKINGS_AMOUNT),
SUM (ITD_ADJUSTMENT_BOOKINGS_AMOUNT),
-SUM (ITD_CANCELLED_BOOKINGS_AMOUNT),
SUM (ITD_REVENUE),
0,
SUM (BACKLOG_NOT_STARTED),
SUM (ACTIVE_BACKLOG),
SUM (DORMANT_BACKLOG),
SUM (BEGINNING_BACKLOG),
SUM (LOST_BACKLOG),
SUM (
BACKLOG_NOT_STARTED
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG),
SUM (REVENUE_AT_RISK),
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0)
BULK COLLECT INTO L_TOTAL_BOOKINGS_BACKLOG_TAB
FROM (SELECT /*+ NO_MERGE(FCT) */
FCT.PROJECT_ID,
NULL PROJECT_NAME,
FCT.PROJECT_ID PROJECT_NUMBER,
NULL PRIMARY_CUSTOMER,
NULL PROJECT_TYPE,
FCT.ORGANIZATION_ID ORGANIZATION,
NULL PROJECT_MANAGER,
NULL PROJECT_START_DATE,
NULL PROJECT_CLOSE_DATE,
FCT.ORIGINAL_BOOKINGS_AMOUNT ORIGINAL_BOOKINGS_AMOUNT,
FCT.ADDITIONAL_BOOKINGS_AMOUNT ADDITIONAL_BOOKINGS_AMOUNT,
FCT.ADJUSTMENT_BOOKINGS_AMOUNT ADJUSTMENT_BOOKINGS_AMOUNT,
FCT.CANCELLED_BOOKINGS_AMOUNT CANCELLED_BOOKINGS_AMOUNT,
FCT.REVENUE REVENUE,
0,
FCT.ITD_ORIGINAL_BOOKINGS_AMOUNT ITD_ORIGINAL_BOOKINGS_AMOUNT,
FCT.ITD_ADDITIONAL_BOOKINGS_AMOUNT ITD_ADDITIONAL_BOOKINGS_AMOUNT,
FCT.ITD_ADJUSTMENT_BOOKINGS_AMOUNT ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
FCT.ITD_CANCELLED_BOOKINGS_AMOUNT ITD_CANCELLED_BOOKINGS_AMOUNT,
FCT.ITD_REVENUE ITD_REVENUE,
0,
FCT.BACKLOG_NOT_STARTED BACKLOG_NOT_STARTED,
FCT.DORMANT_BACKLOG DORMANT_BACKLOG,
FCT.ACTIVE_BACKLOG ACTIVE_BACKLOG,
FCT.BEGINNING_BACKLOG BEGINNING_BACKLOG,
FCT.LOST_BACKLOG LOST_BACKLOG,
FCT.REVENUE_AT_RISK REVENUE_AT_RISK,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM (SELECT /*+ ORDERED */
FCT.PROJECT_ID PROJECT_ID,
FCT.PROJECT_ORGANIZATION_ID ORGANIZATION_ID,
FCT.INITIAL_FUNDING_AMOUNT ORIGINAL_BOOKINGS_AMOUNT,
FCT.ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_BOOKINGS_AMOUNT,
FCT.FUNDING_ADJUSTMENT_AMOUNT ADJUSTMENT_BOOKINGS_AMOUNT,
FCT.CANCELLED_FUNDING_AMOUNT CANCELLED_BOOKINGS_AMOUNT,
FCT.REVENUE REVENUE,
0,
0 ITD_ORIGINAL_BOOKINGS_AMOUNT,
0 ITD_ADDITIONAL_BOOKINGS_AMOUNT,
0 ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
0 ITD_CANCELLED_BOOKINGS_AMOUNT,
0 ITD_REVENUE,
0,
0 BACKLOG_NOT_STARTED,
0 DORMANT_BACKLOG,
0 ACTIVE_BACKLOG,
-(NVL(DORMANT_BACKLOG_START
+ DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG, 0)) BEGINNING_BACKLOG,
0 LOST_BACKLOG,
0 REVENUE_AT_RISK,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_TIME_DIM_TMP TIME,
PJI_PMV_ORGZ_DIM_TMP TORG,
PJI_AC_PROJ_F FCT,
PJI_PMV_ORG_DIM_TMP TOU
WHERE FCT.PROJECT_ORG_ID = TOU.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_ORGANIZATION_ID = TORG.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
FCT.PROJECT_ID PROJECT_ID,
FCT.PROJECT_ORGANIZATION_ID ORGANIZATION_ID,
0 ORIGINAL_BOOKINGS_AMOUNT,
0 ADDITIONAL_BOOKINGS_AMOUNT,
0 ADJUSTMENT_BOOKINGS_AMOUNT,
0 CANCELLED_BOOKINGS_AMOUNT,
0 REVENUE,
0,
FCT.INITIAL_FUNDING_AMOUNT ITD_ORIGINAL_BOOKINGS_AMOUNT,
FCT.ADDITIONAL_FUNDING_AMOUNT ITD_ADDITIONAL_BOOKINGS_AMOUNT,
FCT.FUNDING_ADJUSTMENT_AMOUNT ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
FCT.CANCELLED_FUNDING_AMOUNT ITD_CANCELLED_BOOKINGS_AMOUNT,
FCT.REVENUE ITD_REVENUE,
0,
DORMANT_BACKLOG_START BACKLOG_NOT_STARTED,
DORMANT_BACKLOG_INACTIV DORMANT_BACKLOG,
ACTIVE_BACKLOG ACTIVE_BACKLOG,
NVL(DORMANT_BACKLOG_START
+ DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG,0) BEGINNING_BACKLOG,
LOST_BACKLOG LOST_BACKLOG,
REVENUE_AT_RISK REVENUE_AT_RISK,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_ITD_DIM_TMP TIME,
PJI_PMV_ORGZ_DIM_TMP TORG,
PJI_AC_PROJ_F FCT,
PJI_PMV_ORG_DIM_TMP TOU
WHERE FCT.PROJECT_ORG_ID = TOU.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_ORGANIZATION_ID = TORG.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id) FCT
WHERE 1 = 1) FCT
GROUP BY PROJECT_ID,
PROJECT_NAME,
PROJECT_NUMBER,
PRIMARY_CUSTOMER,
PROJECT_TYPE,
ORGANIZATION,
PROJECT_MANAGER,
PROJECT_START_DATE,
PROJECT_CLOSE_DATE;
DELETE pji_pmv_prj_dim_tmp;
INSERT INTO pji_pmv_prj_dim_tmp (id, name)
SELECT DISTINCT prj.project_id, '-1' name
FROM
pji_project_classes PJM
, pji_pmv_cls_dim_tmp PTM
, pji_pmv_orgz_dim_tmp org
, pa_projects_all prj
WHERE
pjm.project_class_id = ptm.id
AND prj.project_id = pjm.project_id
AND prj.carrying_out_organization_id = org.ID;
SELECT PJI_REP_PBB2 (PROJECT_ID,
PROJECT_NAME,
PROJECT_NUMBER,
PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_Of_Date,p_Period_Type),
PRIMARY_CUSTOMER,
PROJECT_TYPE,
ORGANIZATION,
PROJECT_MANAGER,
TRUNC (PROJECT_START_DATE),
TRUNC (PROJECT_CLOSE_DATE),
SUM (ORIGINAL_BOOKINGS_AMOUNT),
SUM (ADDITIONAL_BOOKINGS_AMOUNT),
SUM (ADJUSTMENT_BOOKINGS_AMOUNT),
-SUM (CANCELLED_BOOKINGS_AMOUNT),
SUM (REVENUE),
0,
SUM (ITD_ORIGINAL_BOOKINGS_AMOUNT),
SUM (ITD_ADDITIONAL_BOOKINGS_AMOUNT),
SUM (ITD_ADJUSTMENT_BOOKINGS_AMOUNT),
-SUM (ITD_CANCELLED_BOOKINGS_AMOUNT),
SUM (ITD_REVENUE),
0,
SUM (BACKLOG_NOT_STARTED),
SUM (ACTIVE_BACKLOG),
SUM (DORMANT_BACKLOG),
SUM (BEGINNING_BACKLOG),
SUM (LOST_BACKLOG),
SUM (
BACKLOG_NOT_STARTED
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG
),
SUM (REVENUE_AT_RISK),
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0 )
BULK COLLECT INTO L_TOTAL_BOOKINGS_BACKLOG_TAB
FROM (SELECT /*+ NO_MERGE(FCT) */
FCT.PROJECT_ID,
NULL PROJECT_NAME,
FCT.PROJECT_ID PROJECT_NUMBER,
NULL PRIMARY_CUSTOMER,
NULL PROJECT_TYPE,
FCT.ORGANIZATION_ID ORGANIZATION,
NULL PROJECT_MANAGER,
NULL PROJECT_START_DATE,
NULL PROJECT_CLOSE_DATE,
FCT.ORIGINAL_BOOKINGS_AMOUNT ORIGINAL_BOOKINGS_AMOUNT,
FCT.ADDITIONAL_BOOKINGS_AMOUNT ADDITIONAL_BOOKINGS_AMOUNT,
FCT.ADJUSTMENT_BOOKINGS_AMOUNT ADJUSTMENT_BOOKINGS_AMOUNT,
FCT.CANCELLED_BOOKINGS_AMOUNT CANCELLED_BOOKINGS_AMOUNT,
FCT.REVENUE REVENUE,
0,
FCT.ITD_ORIGINAL_BOOKINGS_AMOUNT ITD_ORIGINAL_BOOKINGS_AMOUNT,
FCT.ITD_ADDITIONAL_BOOKINGS_AMOUNT ITD_ADDITIONAL_BOOKINGS_AMOUNT,
FCT.ITD_ADJUSTMENT_BOOKINGS_AMOUNT ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
FCT.ITD_CANCELLED_BOOKINGS_AMOUNT ITD_CANCELLED_BOOKINGS_AMOUNT,
FCT.ITD_REVENUE ITD_REVENUE,
0,
FCT.BACKLOG_NOT_STARTED BACKLOG_NOT_STARTED,
FCT.DORMANT_BACKLOG DORMANT_BACKLOG,
FCT.ACTIVE_BACKLOG ACTIVE_BACKLOG,
FCT.BEGINNING_BACKLOG BEGINNING_BACKLOG,
FCT.LOST_BACKLOG LOST_BACKLOG,
FCT.REVENUE_AT_RISK REVENUE_AT_RISK,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM (SELECT /*+ ORDERED */
FCT.PROJECT_ID PROJECT_ID,
FCT.PROJECT_ORGANIZATION_ID ORGANIZATION_ID,
FCT.INITIAL_FUNDING_AMOUNT
ORIGINAL_BOOKINGS_AMOUNT,
FCT.ADDITIONAL_FUNDING_AMOUNT
ADDITIONAL_BOOKINGS_AMOUNT,
FCT.FUNDING_ADJUSTMENT_AMOUNT
ADJUSTMENT_BOOKINGS_AMOUNT,
FCT.CANCELLED_FUNDING_AMOUNT
CANCELLED_BOOKINGS_AMOUNT,
FCT.REVENUE REVENUE,
0,
0 ITD_ORIGINAL_BOOKINGS_AMOUNT,
0 ITD_ADDITIONAL_BOOKINGS_AMOUNT,
0 ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
0 ITD_CANCELLED_BOOKINGS_AMOUNT,
0 ITD_REVENUE,
0,
0 BACKLOG_NOT_STARTED,
0 DORMANT_BACKLOG,
0 ACTIVE_BACKLOG,
-(NVL(DORMANT_BACKLOG_START
+ DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG, 0)) BEGINNING_BACKLOG,
0 LOST_BACKLOG,
0 REVENUE_AT_RISK,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_TIME_DIM_TMP TIME,
PJI_PMV_PRJ_DIM_TMP TPRJ,
PJI_AC_PROJ_F FCT,
PJI_PMV_ORG_DIM_TMP TOU
WHERE FCT.PROJECT_ORG_ID = TOU.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_ID = TPRJ.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
FCT.PROJECT_ID PROJECT_ID,
FCT.PROJECT_ORGANIZATION_ID ORGANIZATION_ID,
0 ORIGINAL_BOOKINGS_AMOUNT,
0 ADDITIONAL_BOOKINGS_AMOUNT,
0 ADJUSTMENT_BOOKINGS_AMOUNT,
0 CANCELLED_BOOKINGS_AMOUNT,
0 REVENUE,
0 XTD_TOTAL_BOOKINGS,
FCT.INITIAL_FUNDING_AMOUNT ITD_ORIGINAL_BOOKINGS_AMOUNT,
FCT.ADDITIONAL_FUNDING_AMOUNT ITD_ADDITIONAL_BOOKINGS_AMOUNT,
FCT.FUNDING_ADJUSTMENT_AMOUNT ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
FCT.CANCELLED_FUNDING_AMOUNT ITD_CANCELLED_BOOKINGS_AMOUNT,
FCT.REVENUE ITD_REVENUE,
0,
DORMANT_BACKLOG_START BACKLOG_NOT_STARTED,
DORMANT_BACKLOG_INACTIV DORMANT_BACKLOG,
ACTIVE_BACKLOG ACTIVE_BACKLOG,
NVL(DORMANT_BACKLOG_START
+ DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG, 0) BEGINNING_BACKLOG,
LOST_BACKLOG LOST_BACKLOG,
REVENUE_AT_RISK REVENUE_AT_RISK,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_ITD_DIM_TMP TIME,
PJI_PMV_PRJ_DIM_TMP TPRJ,
PJI_AC_PROJ_F FCT,
PJI_PMV_ORG_DIM_TMP TOU
WHERE FCT.PROJECT_ORG_ID = TOU.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_ID = TPRJ.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id) FCT
WHERE 1 = 1) FCT
GROUP BY PROJECT_ID,
PROJECT_NAME,
PROJECT_NUMBER,
PRIMARY_CUSTOMER,
PROJECT_TYPE,
ORGANIZATION,
PROJECT_MANAGER,
PROJECT_START_DATE,
PROJECT_CLOSE_DATE;
** RECORDS ONLY, DELETE THE RECORDS WITH REVENUE AT RISK
** LESS THAN EQUAL TO ZERO.
** THE PL/SQL APPROACH WAS CHOOSEN BECAUSE OF PERFORMANCE
** REASONS.
*/
IF P_RUN_REVENUE_AT_RISK = 'Y'
THEN
IF L_TOTAL_BOOKINGS_BACKLOG_TAB.COUNT > 0
THEN
FOR I IN
L_TOTAL_BOOKINGS_BACKLOG_TAB.FIRST .. L_TOTAL_BOOKINGS_BACKLOG_TAB.LAST
LOOP
IF L_TOTAL_BOOKINGS_BACKLOG_TAB (I).REVENUE_AT_RISK <= 0
THEN
L_TOTAL_BOOKINGS_BACKLOG_TAB.DELETE (I);
** AFTER THE DELETE OPERATION.
*/
L_ACTUAL_ROWCOUNT := L_TOTAL_BOOKINGS_BACKLOG_TAB.FIRST;
SELECT NAME,
SEGMENT1,
PROJECT_TYPE,
START_DATE,
CLOSED_DATE
INTO X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_NAME,
X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_NUMBER,
X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_TYPE,
X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_START_DATE,
X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_CLOSE_DATE
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID =
X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_NUMBER;
SELECT NAME
INTO X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).ORGANIZATION
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE LANGUAGE = USERENV ('LANG')
AND ORGANIZATION_ID =
X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).ORGANIZATION;
** 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_PBB3 - PROJECT BOOKINGS ACTIVITY
** ----------------------------------------------------------
*/
FUNCTION PLSQLDRIVER_PBB3 (
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
)
RETURN PJI_REP_PBB3_TBL
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT PJI_REP_PBB3 (
ORG_ID,
ORGANIZATION_ID,
TIME_ID,
TIME_KEY,
PROJECT_CLASS_ID,
SUM (ORIGINAL_BOOKINGS),
SUM (ADDITIONAL_BOOKINGS),
SUM (BOOKINGS_ADJUSTMENTS),
-SUM (CANCELLATIONS),
SUM (TOTAL_NET_BOOKINGS),
SUM (ACCRUED_REVENUE),
SUM (LOST_BACKLOG),
SUM (LOST_BACKLOG_ITD),
SUM (BACKLOG),
SUM (BACKLOG_ITD),
SUM (REVENUE_AT_RISK),
SUM (REVENUE_AT_RISK_ITD),
0,
0,
0,
0,
0,
0,
0,
0,
0,
0 )
BULK COLLECT INTO L_AC_BOOKINGS_BACKLOG_TAB
FROM (SELECT /*+ ORDERED */
HOU.NAME ORG_ID, HORG.NAME ORGANIZATION_ID,
TIME.NAME TIME_ID,
DECODE (P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY,
'-1' PROJECT_CLASS_ID,
INITIAL_FUNDING_AMOUNT ORIGINAL_BOOKINGS,
ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_BOOKINGS,
FUNDING_ADJUSTMENT_AMOUNT BOOKINGS_ADJUSTMENTS,
CANCELLED_FUNDING_AMOUNT CANCELLATIONS,
INITIAL_FUNDING_AMOUNT
+ ADDITIONAL_FUNDING_AMOUNT
+ FUNDING_ADJUSTMENT_AMOUNT
+ CANCELLED_FUNDING_AMOUNT TOTAL_NET_BOOKINGS,
REVENUE ACCRUED_REVENUE, LOST_BACKLOG LOST_BACKLOG,
0 LOST_BACKLOG_ITD,
DORMANT_BACKLOG_START
+ DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG BACKLOG,
0 BACKLOG_ITD, REVENUE_AT_RISK REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_TIME_DIM_TMP TIME,
PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_AC_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 */
HOU.NAME ORG_ID, HORG.NAME ORGANIZATION_ID,
TIME.NAME TIME_ID,
DECODE (P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY,
'-1' PROJECT_CLASS_ID, 0 ORIGINAL_BOOKINGS,
0 ADDITIONAL_BOOKINGS, 0 BOOKINGS_ADJUSTMENTS,
0 CANCELLATIONS, 0 TOTAL_NET_BOOKINGS,
0 ACCRUED_REVENUE, 0 LOST_BACKLOG,
LOST_BACKLOG LOST_BACKLOG_ITD, 0 BACKLOG,
DORMANT_BACKLOG_START
+ DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG BACKLOG_ITD,
0 REVENUE_AT_RISK,
REVENUE_AT_RISK REVENUE_AT_RISK_ITD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_ITD_DIM_TMP TIME,
PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_AC_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 NAME ORG_ID, '-1' ORGANIZATION_ID, '-1' TIME_ID,
-1 TIME_KEY, '-1' PROJECT_CLASS_ID,
0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,0,0,0,0,0,0,0,0,0
FROM PJI_PMV_ORG_DIM_TMP
WHERE NAME <> '-1'
UNION ALL
SELECT '-1' ORG_ID, NAME ORGANIZATION_ID, '-1' TIME_ID,
-1 TIME_KEY, '-1' PROJECT_CLASS_ID,
0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,0,0,0,0,0,0,0,0,0
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE NAME <> '-1'
UNION ALL
SELECT '-1' ORG_ID, '-1' ORGANIZATION_ID, NAME TIME_ID,
ID TIME_KEY, '-1' PROJECT_CLASS_ID,
0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,0,0,0,0,0,0,0,0,0
FROM PJI_PMV_TIME_DIM_TMP
WHERE NAME <> '-1')
GROUP BY ORG_ID, ORGANIZATION_ID, TIME_KEY, TIME_ID, PROJECT_CLASS_ID;
SELECT PJI_REP_PBB3 (
ORG_ID,
ORGANIZATION_ID,
TIME_ID,
TIME_KEY,
PROJECT_CLASS_ID,
SUM (ORIGINAL_BOOKINGS),
SUM (ADDITIONAL_BOOKINGS),
SUM (BOOKINGS_ADJUSTMENTS),
-SUM (CANCELLATIONS),
SUM (TOTAL_NET_BOOKINGS),
SUM (ACCRUED_REVENUE),
SUM (LOST_BACKLOG),
SUM (LOST_BACKLOG_ITD),
SUM (BACKLOG),
SUM (BACKLOG_ITD),
SUM (REVENUE_AT_RISK),
SUM (REVENUE_AT_RISK_ITD),
0,
0,
0,
0,
0,
0,
0,
0,
0,
0 )
BULK COLLECT INTO L_AC_BOOKINGS_BACKLOG_TAB
FROM (SELECT /*+ ORDERED */
HOU.NAME ORG_ID,
HORG.NAME ORGANIZATION_ID,
TIME.NAME TIME_ID,
DECODE (P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY,
CLS.NAME PROJECT_CLASS_ID,
INITIAL_FUNDING_AMOUNT ORIGINAL_BOOKINGS,
ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_BOOKINGS,
FUNDING_ADJUSTMENT_AMOUNT BOOKINGS_ADJUSTMENTS,
CANCELLED_FUNDING_AMOUNT CANCELLATIONS,
INITIAL_FUNDING_AMOUNT
+ ADDITIONAL_FUNDING_AMOUNT
+ FUNDING_ADJUSTMENT_AMOUNT
+ CANCELLED_FUNDING_AMOUNT TOTAL_NET_BOOKINGS,
REVENUE ACCRUED_REVENUE, LOST_BACKLOG LOST_BACKLOG,
0 LOST_BACKLOG_ITD,
DORMANT_BACKLOG_START
+ DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG BACKLOG,
0 BACKLOG_ITD, REVENUE_AT_RISK REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_TIME_DIM_TMP TIME,
PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_PMV_CLS_DIM_TMP CLS,
PJI_AC_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 */
HOU.NAME ORG_ID, HORG.NAME ORGANIZATION_ID,
TIME.NAME TIME_ID,
DECODE (P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY,
CLS.NAME PROJECT_CLASS_ID, 0 ORIGINAL_BOOKINGS,
0 ADDITIONAL_BOOKINGS, 0 BOOKINGS_ADJUSTMENTS,
0 CANCELLATIONS, 0 TOTAL_NET_BOOKINGS,
0 ACCRUED_REVENUE, 0 LOST_BACKLOG,
LOST_BACKLOG LOST_BACKLOG_ITD, 0 BACKLOG,
DORMANT_BACKLOG_START
+ DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG BACKLOG_ITD,
0 REVENUE_AT_RISK,
REVENUE_AT_RISK REVENUE_AT_RISK_ITD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_ITD_DIM_TMP TIME,
PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_PMV_CLS_DIM_TMP CLS,
PJI_AC_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 NAME ORG_ID, '-1' ORGANIZATION_ID, '-1' TIME_ID,
-1 TIME_KEY, '-1' PROJECT_CLASS_ID,
0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_ORG_DIM_TMP
WHERE NAME <> '-1'
UNION ALL
SELECT '-1' ORG_ID, NAME ORGANIZATION_ID, '-1' TIME_ID,
-1 TIME_KEY, '-1' PROJECT_CLASS_ID,
0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE NAME <> '-1'
UNION ALL
SELECT '-1' ORG_ID, '-1' ORGANIZATION_ID, NAME TIME_ID,
ID TIME_KEY, '-1' PROJECT_CLASS_ID,
0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_TIME_DIM_TMP
WHERE NAME <> '-1'
UNION ALL
SELECT '-1' ORG_ID, '-1' ORGANIZATION_ID, '-1' TIME_ID,
-1 TIME_KEY, NAME PROJECT_CLASS_ID,
0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
0 REVENUE_AT_RISK_ITD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM PJI_PMV_CLS_DIM_TMP
WHERE NAME <> '-1') FACT
GROUP BY ORG_ID, ORGANIZATION_ID, TIME_KEY, TIME_ID, PROJECT_CLASS_ID;
l_ac_bookings_backlog_tab.DELETE(l_Top_Org_Index);