[Home] [Help]
2: /* $Header: PJIRF05B.pls 120.4 2005/10/11 18:10:45 appldev noship $ */
3:
4: /*
5: ** ----------------------------------------------------------
6: ** Procedure: Get_SQL_PJI_REP_PP5
7: ** This procedure returns sql statement generated by the base
8: ** engine api and view by as a out parameter for the report
9: ** PJI_REP_PP5.
10: ** ----------------------------------------------------------
5: ** ----------------------------------------------------------
6: ** Procedure: Get_SQL_PJI_REP_PP5
7: ** This procedure returns sql statement generated by the base
8: ** engine api and view by as a out parameter for the report
9: ** PJI_REP_PP5.
10: ** ----------------------------------------------------------
11: */
12: PROCEDURE Get_SQL_PJI_REP_PP5(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
13: , x_PMV_Sql OUT NOCOPY VARCHAR2
8: ** engine api and view by as a out parameter for the report
9: ** PJI_REP_PP5.
10: ** ----------------------------------------------------------
11: */
12: PROCEDURE Get_SQL_PJI_REP_PP5(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
13: , x_PMV_Sql OUT NOCOPY VARCHAR2
14: , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
15: IS
16: BEGIN
29: , FACT.PY_MARGIN_PERCENT "PJI_REP_MSR_12"
30: , FACT.MARGIN_PERCENT "PJI_REP_MSR_13" '
31: , P_SQL_STATEMENT => x_PMV_Sql
32: , P_PMV_OUTPUT => x_PMV_Output
33: , P_REGION_CODE => 'PJI_REP_PP5'
34: , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
35: , P_PLSQL_DRIVER_PARAMS => ' <
36: ', <
37: ', <
30: , FACT.MARGIN_PERCENT "PJI_REP_MSR_13" '
31: , P_SQL_STATEMENT => x_PMV_Sql
32: , P_PMV_OUTPUT => x_PMV_Output
33: , P_REGION_CODE => 'PJI_REP_PP5'
34: , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
35: , P_PLSQL_DRIVER_PARAMS => ' <
36: ', <
37: ', <
38: ', <
46: ', <
47: ', <
48: ', NULL'
49: );
50: END Get_SQL_PJI_REP_PP5;
51:
52: /*
53: ** ----------------------------------------------------------
54: ** Procedure: Get_SQL_PJI_REP_PP6
77: , FACT.MARGIN_PERCENT "PJI_REP_MSR_11" '
78: , P_SQL_STATEMENT => x_PMV_Sql
79: , P_PMV_OUTPUT => x_PMV_Output
80: , P_REGION_CODE => 'PJI_REP_PP6'
81: , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
82: , P_PLSQL_DRIVER_PARAMS => ' <
83: ', <
84: ', <
85: ', <
127: , FACT.CST_CHANGE_PERCENT "PJI_REP_MSR_3"
128: , FACT.COST "PJI_REP_MSR_4" '
129: , P_PMV_OUTPUT => x_PMV_Output
130: , P_REGION_CODE => 'PJI_REP_PP7'
131: , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
132: , P_PLSQL_DRIVER_PARAMS => ' <
133: ', <
134: ', <
135: ', <
168: , FACT.CST_CHANGE_PERCENT "PJI_REP_MSR_3"
169: , FACT.COST "PJI_REP_MSR_4" '
170: , P_PMV_OUTPUT => x_PMV_Output
171: , P_REGION_CODE => 'PJI_REP_PP8'
172: , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
173: , P_PLSQL_DRIVER_PARAMS => ' <
174: ', <
175: ', <
176: ', <
197: END Get_SQL_PJI_REP_PP8;
198:
199: /*
200: ** ----------------------------------------------------------
201: ** Function: PLSQLDriver_PJI_REP_PP5
202: ** This table function is called from select statement
203: ** generated by PJI engine. The function returns pl/sql table
204: ** of records which have to be displayed in the pmv report.
205: ** Following are the reports to which this function caters:
202: ** This table function is called from select statement
203: ** generated by PJI engine. The function returns pl/sql table
204: ** of records which have to be displayed in the pmv report.
205: ** Following are the reports to which this function caters:
206: ** 1. PJI_REP_PP5 - Project Profitability Trend
207: ** 2. PJI_REP_PP6 - Project Profitability Cumulative Trend
208: ** 3. PJI_REP_PP7 - Project Cost Trend
209: ** 4. PJI_REP_PP8 - Project Cost Cumulative Trend
210: ** ----------------------------------------------------------
208: ** 3. PJI_REP_PP7 - Project Cost Trend
209: ** 4. PJI_REP_PP8 - Project Cost Cumulative Trend
210: ** ----------------------------------------------------------
211: */
212: FUNCTION PLSQLDriver_PJI_REP_PP5(
213: p_Operating_Unit IN VARCHAR2 DEFAULT NULL
214: , p_Organization IN VARCHAR2
215: , p_Currency_Type IN VARCHAR2
216: , p_As_Of_Date IN NUMBER
225: , p_Revenue_Category IN VARCHAR2 DEFAULT NULL
226: , p_Revenue_Type IN VARCHAR2 DEFAULT NULL
227: , p_Work_Type IN VARCHAR2 DEFAULT NULL
228:
229: )RETURN PJI_REP_PP5_TBL
230: IS
231: PRAGMA AUTONOMOUS_TRANSACTION;
232: l_Total_Prj_Profitablity_Tab PJI_REP_PP5_TBL:=PJI_REP_PP5_TBL();
233: l_Parse_Class_Codes VARCHAR2(1);
228:
229: )RETURN PJI_REP_PP5_TBL
230: IS
231: PRAGMA AUTONOMOUS_TRANSACTION;
232: l_Total_Prj_Profitablity_Tab PJI_REP_PP5_TBL:=PJI_REP_PP5_TBL();
233: l_Parse_Class_Codes VARCHAR2(1);
234: l_Report_Cost_Type VARCHAR2(2);
235:
236: l_Revenue NUMBER:=0;
318: and (l_Convert_Work_Type = 'N')
319: THEN
320:
321:
322: SELECT PJI_REP_PP5(
323: TIME_ID
324: , SUM( REVENUE )
325: , SUM( COST )
326: , SUM( REVENUE-COST )
398:
399: THEN
400:
401:
402: SELECT PJI_REP_PP5(
403: TIME_ID
404: , SUM( REVENUE )
405: , SUM( COST )
406: , SUM( REVENUE-COST )
477:
478: ELSIF (l_Convert_Classification = 'N')
479: and (l_Convert_Work_Type = 'N')
480: THEN
481: SELECT PJI_REP_PP5(
482: TIME_ID
483: , SUM( REVENUE )
484: , SUM( COST )
485: , SUM( REVENUE-COST )
560: ELSIF (l_Convert_Classification = 'N')
561: and (l_Convert_Expenditure_Type = 'N')
562: and (l_Convert_Event_Revenue_Type = 'N')
563: THEN
564: SELECT PJI_REP_PP5(
565: TIME_ID
566: , SUM( REVENUE )
567: , SUM( COST )
568: , SUM( REVENUE-COST )
638: /* ----------------------------------- Case 5 truth table ------------------------------------- */
639:
640: ELSIF (l_Convert_Work_Type = 'N')
641: THEN
642: SELECT PJI_REP_PP5(
643: TIME_ID
644: , SUM( REVENUE )
645: , SUM( COST )
646: , SUM( REVENUE-COST )
723:
724: ELSIF (l_Convert_Classification = 'N')
725: THEN
726:
727: SELECT PJI_REP_PP5(
728: TIME_ID
729: , SUM( REVENUE )
730: , SUM( COST )
731: , SUM( REVENUE-COST )
809: ELSIF (l_Convert_Expenditure_Type = 'N')
810: and (l_Convert_Event_Revenue_Type = 'N')
811: THEN
812:
813: SELECT PJI_REP_PP5(
814: TIME_ID
815: , SUM( REVENUE )
816: , SUM( COST )
817: , SUM( REVENUE-COST )
890:
891: /* ----------------------------------- Case 8 truth table ------------------------------------- */
892:
893: ELSE
894: SELECT PJI_REP_PP5(
895: TIME_ID
896: , SUM( REVENUE )
897: , SUM( COST )
898: , SUM( REVENUE-COST )
1039:
1040: COMMIT;
1041: RETURN l_Total_Prj_Profitablity_Tab;
1042:
1043: END PLSQLDriver_PJI_REP_PP5;
1044:
1045:
1046:
1047: END PJI_PMV_PROFITABILITY_TREND;