[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_SELECT_LIST =>'FACT.BACKLOG_NOT_STARTED "PJI_REP_MSR_1",
FACT.ACTIVE_BACKLOG "PJI_REP_MSR_2",
FACT.DORMANT_BACKLOG "PJI_REP_MSR_3",
FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_4",
FACT.PRIOR_TOTAL_ENDING_BACKLOG "PJI_REP_MSR_5",
FACT.CHANGE_PERCENTAGE "PJI_REP_MSR_6",
FACT.TOTAL_BOOKINGS_ITD "PJI_REP_MSR_7",
FACT.BACKLOG_PERCENT_OF_BOOKINGS "PJI_REP_MSR_8",
FACT.LOST_BACKLOG "PJI_REP_MSR_9",
FACT.REVENUE_AT_RISK "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_PB1'
, P_PLSQL_DRIVER => 'PJI_PMV_BACKLOG.PLSQLDriver_PB1'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> ');
P_SELECT_LIST => 'FACT.BEGINNING_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.ENDING_REVENUE_AT_RISK "PJI_REP_MSR_14",
FACT.ENDING_LOST_BACKLOG "PJI_REP_MSR_8",
FACT.ENDING_BACKLOG "PJI_REP_MSR_9",
FACT.PRIOR_YEAR "PJI_REP_MSR_10",
FACT.ENDING_BACKLOG "PJI_REP_MSR_13",
FACT.CHANGE "PJI_REP_MSR_11"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PB2'
, P_PLSQL_DRIVER => 'PJI_PMV_BACKLOG.PLSQLDriver_PB2'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> ');
SELECT PJI_REP_PB1( ORG_ID
, ORGANIZATION_ID
, TIME_ID
, TIME_KEY
, PROJECT_CLASS_ID
, SUM( BACKLOG_NOT_STARTED )
, SUM( ACTIVE_BACKLOG )
, SUM( DORMANT_BACKLOG )
, SUM( TOTAL_ENDING_BACKLOG )
, SUM( PRIOR_TOTAL_ENDING_BACKLOG )
, 0
, SUM( TOTAL_BOOKINGS_ITD )
, 0
, SUM( LOST_BACKLOG )
, SUM( REVENUE_AT_RISK )
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0)
BULK COLLECT INTO l_Total_AC_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
, DORMANT_BACKLOG_START BACKLOG_NOT_STARTED
, ACTIVE_BACKLOG
, DORMANT_BACKLOG_INACTIV DORMANT_BACKLOG
, DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, INITIAL_FUNDING_AMOUNT
+ ADDITIONAL_FUNDING_AMOUNT
+ FUNDING_ADJUSTMENT_AMOUNT
+ CANCELLED_FUNDING_AMOUNT TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, LOST_BACKLOG LOST_BACKLOG
, REVENUE_AT_RISK
, 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 /*+ 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 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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.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 ORG_ID
, '-1' ORGANIZATION_ID
, '-1' TIME_ID
, -1 TIME_KEY
, '-1' PROJECT_CLASS_ID
, 0 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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_PB1
( ORG_ID
, ORGANIZATION_ID
, TIME_ID
, TIME_KEY
, PROJECT_CLASS_ID
, SUM( BACKLOG_NOT_STARTED )
, SUM( ACTIVE_BACKLOG )
, SUM( DORMANT_BACKLOG )
, SUM( TOTAL_ENDING_BACKLOG )
, SUM( PRIOR_TOTAL_ENDING_BACKLOG )
, 0
, SUM( TOTAL_BOOKINGS_ITD )
, 0
, SUM( LOST_BACKLOG )
, SUM( REVENUE_AT_RISK )
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0 )
BULK COLLECT INTO l_Total_AC_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
, DORMANT_BACKLOG_START BACKLOG_NOT_STARTED
, ACTIVE_BACKLOG
, DORMANT_BACKLOG_INACTIV DORMANT_BACKLOG
, DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, INITIAL_FUNDING_AMOUNT
+ ADDITIONAL_FUNDING_AMOUNT
+ FUNDING_ADJUSTMENT_AMOUNT
+ CANCELLED_FUNDING_AMOUNT TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, LOST_BACKLOG LOST_BACKLOG
, REVENUE_AT_RISK
, 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 /*+ 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 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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.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 ORG_ID
, '-1' ORGANIZATION_ID
, '-1' TIME_ID
, -1 TIME_KEY
, '-1' PROJECT_CLASS_ID
, 0 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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 BACKLOG_NOT_STARTED
, 0 ACTIVE_BACKLOG
, 0 DORMANT_BACKLOG
, 0 TOTAL_ENDING_BACKLOG
, 0 PRIOR_TOTAL_ENDING_BACKLOG
, 0 CHANGE_PERCENTAGE
, 0 TOTAL_BOOKINGS_ITD
, 0 BACKLOG_PERCENT_OF_BOOKINGS
, 0 LOST_BACKLOG
, 0 REVENUE_AT_RISK
, 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_Total_AC_Backlog_Tab.DELETE(l_Top_Org_Index);
SELECT PJI_REP_PB2( ORG_ID
, ORGANIZATION_ID
, TIME_ID
, TIME_KEY
, PROJECT_CLASS_ID
, 0
, SUM( ORIGINAL_BOOKINGS )
, SUM( ADDITIONAL_BOOKINGS )
, SUM( BOOKINGS_ADJUSTMENTS )
, -SUM( CANCELLATIONS )
, SUM( TOTAL_NET_BOOKINGS )
, SUM( ACCRUED_REVENUE )
, SUM( PRIOR_YEAR )
, SUM( ENDING_LOST_BACKLOG )
, SUM( ENDING_BACKLOG )
, SUM( ENDING_REVENUE_AT_RISK)
, SUM( LOST_BACKLOG )
, SUM( BACKLOG )
, SUM( REVENUE_AT_RISK )
, 0 )
BULK COLLECT INTO l_Backlog_Trend_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
, 0
, 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
, 0 PRIOR_YEAR
, 0 ENDING_LOST_BACKLOG
, 0 ENDING_BACKLOG
, 0 ENDING_REVENUE_AT_RISK
, LOST_BACKLOG
, DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START BACKLOG
, REVENUE_AT_RISK REVENUE_AT_RISK
, 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
, 0 ORIGINAL_BOOKINGS
, 0 ADDITIONAL_BOOKINGS
, 0 BOOKINGS_ADJUSTMENTS
, 0 CANCELLATIONS
, 0 TOTAL_NET_BOOKINGS
, 0 ACCRUED_REVENUE
, DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START PRIOR_YEAR
, 0 ENDING_LOST_BACKLOG
, 0 ENDING_BACKLOG
, 0 ENDING_REVENUE_AT_RISK
, 0 LOST_BACKLOG
, 0 BACKLOG
, 0 REVENUE_AT_RISK
, 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.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 '-1' ORG_ID
, '-1' ORGANIZATION_ID
, NAME TIME_ID
, ORDER_BY_ID TIME_KEY
, '-1' PROJECT_CLASS_ID
, 0
, 0 ORIGINAL_BOOKINGS
, 0 ADDITIONAL_BOOKINGS
, 0 BOOKINGS_ADJUSTMENTS
, 0 CANCELLATIONS
, 0 TOTAL_NET_BOOKINGS
, 0 ACCRUED_REVENUE
, 0 PRIOR_YEAR
, 0 ENDING_LOST_BACKLOG
, 0 ENDING_BACKLOG
, 0 ENDING_REVENUE_AT_RISK
, 0 LOST_BACKLOG
, 0 BACKLOG
, 0 REVENUE_AT_RISK
, 0
FROM PJI_PMV_TIME_DIM_TMP
WHERE NAME <> '-1')
GROUP BY
ORG_ID
, ORGANIZATION_ID
, TIME_KEY
, TIME_ID
, PROJECT_CLASS_ID ORDER BY TIME_KEY ASC;
SELECT /*+ ORDERED */
SUM( DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START ), SUM(LOST_BACKLOG), SUM(REVENUE_AT_RISK)
INTO l_Ending_Backlog_itd, l_Ending_Lost_Backlog_itd, l_Ending_Revenue_at_Risk_itd
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;
SELECT /*+ ORDERED */ SUM( DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START )
INTO l_Ending_Prior_Backlog_itd
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.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;
SELECT PJI_REP_PB2( ORG_ID
, ORGANIZATION_ID
, TIME_ID
, TIME_KEY
, PROJECT_CLASS_ID
, 0
, SUM( ORIGINAL_BOOKINGS )
, SUM( ADDITIONAL_BOOKINGS )
, SUM( BOOKINGS_ADJUSTMENTS )
, -SUM( CANCELLATIONS )
, SUM( TOTAL_NET_BOOKINGS )
, SUM( ACCRUED_REVENUE )
, SUM( PRIOR_YEAR )
, SUM( ENDING_LOST_BACKLOG )
, SUM( ENDING_BACKLOG )
, SUM( ENDING_REVENUE_AT_RISK)
, SUM( LOST_BACKLOG )
, SUM( BACKLOG )
, SUM( REVENUE_AT_RISK )
, 0 )
BULK COLLECT INTO l_Backlog_Trend_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
, 0
, 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
, 0 PRIOR_YEAR
, 0 ENDING_LOST_BACKLOG
, 0 ENDING_BACKLOG
, 0 ENDING_REVENUE_AT_RISK
, LOST_BACKLOG
, DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START BACKLOG
, REVENUE_AT_RISK REVENUE_AT_RISK
, 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
, '-1' PROJECT_CLASS_ID
, 0
, 0 ORIGINAL_BOOKINGS
, 0 ADDITIONAL_BOOKINGS
, 0 BOOKINGS_ADJUSTMENTS
, 0 CANCELLATIONS
, 0 TOTAL_NET_BOOKINGS
, 0 ACCRUED_REVENUE
, DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START PRIOR_YEAR
, 0 ENDING_LOST_BACKLOG
, 0 ENDING_BACKLOG
, 0 ENDING_REVENUE_AT_RISK
, 0 LOST_BACKLOG
, 0 BACKLOG
, 0 REVENUE_AT_RISK
, 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.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 '-1' ORG_ID
, '-1' ORGANIZATION_ID
, NAME TIME_ID
, ORDER_BY_ID TIME_KEY
, '-1' PROJECT_CLASS_ID
, 0
, 0 ORIGINAL_BOOKINGS
, 0 ADDITIONAL_BOOKINGS
, 0 BOOKINGS_ADJUSTMENTS
, 0 CANCELLATIONS
, 0 TOTAL_NET_BOOKINGS
, 0 ACCRUED_REVENUE
, 0 PRIOR_YEAR
, 0 ENDING_LOST_BACKLOG
, 0 ENDING_BACKLOG
, 0 ENDING_REVENUE_AT_RISK
, 0 LOST_BACKLOG
, 0 BACKLOG
, 0 REVENUE_AT_RISK
, 0
FROM PJI_PMV_TIME_DIM_TMP
WHERE NAME <> '-1'
) FACT
GROUP BY
ORG_ID
, ORGANIZATION_ID
, TIME_KEY
, TIME_ID
, PROJECT_CLASS_ID ORDER BY TIME_KEY ASC;
SELECT /*+ ORDERED */ SUM( DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START), SUM(LOST_BACKLOG), SUM(REVENUE_AT_RISK)
INTO l_Ending_Backlog_itd,
l_Ending_Lost_Backlog_itd,
l_Ending_Revenue_at_Risk_itd
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;
SELECT /*+ ORDERED */
SUM( DORMANT_BACKLOG_INACTIV
+ ACTIVE_BACKLOG
+ DORMANT_BACKLOG_START)
INTO l_Ending_Prior_Backlog_itd
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.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;