[Home] [Help]
493:
494: /****************************************************
495: * RA2: CURRENT AVAILABLE RESOURCES REPORT FUNCTIONS
496: ****************************************************/
497: PROCEDURE GET_SQL_PJI_REP_RA2 (P_PAGE_PARAMETER_TBL IN BIS_PMV_PAGE_PARAMETER_TBL
498: , X_PMV_SQL OUT NOCOPY VARCHAR2
499: , X_PMV_OUTPUT OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
500: IS
501: BEGIN
522: FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_6",
523: FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_7"'
524: ,P_SQL_STATEMENT => X_PMV_SQL
525: ,P_PMV_OUTPUT => X_PMV_OUTPUT,
526: P_REGION_CODE => 'PJI_REP_RA2',
527: P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA2',
528: P_PLSQL_DRIVER_PARAMS => '<
529: '<
530: '<
529: '<
530: '<
531: '<
532: '<
533: END GET_SQL_PJI_REP_RA2;
534:
535: FUNCTION PLSQLDRIVER_RA2 (
536: P_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
537: P_ORGANIZATION IN VARCHAR2,
537: P_ORGANIZATION IN VARCHAR2,
538: P_THRESHOLD IN NUMBER,
539: P_AS_OF_DATE IN NUMBER,
540: P_VIEW_BY IN VARCHAR2
541: ) RETURN PJI_REP_RA2_TBL
542: IS
543: PRAGMA AUTONOMOUS_TRANSACTION;
544: L_CUR_AVL_RES_TBL PJI_REP_RA2_TBL := PJI_REP_RA2_TBL();
545: TYPE T_WEEK_IDS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
540: P_VIEW_BY IN VARCHAR2
541: ) RETURN PJI_REP_RA2_TBL
542: IS
543: PRAGMA AUTONOMOUS_TRANSACTION;
544: L_CUR_AVL_RES_TBL PJI_REP_RA2_TBL := PJI_REP_RA2_TBL();
545: TYPE T_WEEK_IDS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
546: TYPE T_WEEK_END_DATE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
547: L_THRESHOLD NUMBER := P_THRESHOLD;
548: L_AS_OF_DATE NUMBER := P_AS_OF_DATE;
645: END;
646: END IF;
647:
648:
649: SELECT PJI_REP_RA2(
650: ORGANIZATION_ID
651: ,SUM(CURR_AVL)
652: ,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W0',ID,p_operating_unit,l_threshold)
653: ,SUM(TOTAL_RESOURCES)