Primero vamos a crear una consulta que devuelve una lista de meta_poa funcionales
CONSULTA
--************************************************************
-- FUNCION: Obtener los datos de la vista solicitada
-- VERSION: 1.0.1
--************************************************************SELECT
SELECT
MPE.META_POA
FROM
META_POA_EVALUACION MPE
INNER JOIN META_POA MP ON (MP.ID_META_POA = MPE.META_POA)
INNER JOIN OBJETIVO_POA OP ON (OP.ID_OBJETIVO_POA = MP.OBJETIVO_POA)
INNER JOIN PLAN_OPERATIVO PO ON (PO.ID_PLAN_OPERATIVO = OP.PLAN_OPERATIVO)
INNER JOIN UNIDAD_EJECUTORA UE_PO ON (UE_PO.ID_UNIDAD_EJECUTORA = PO.UNIDAD_EJECUTORA)
INNER JOIN EVALUACION_OPERATIVA EO ON (EO.PLAN_OPERATIVO = PO.ID_PLAN_OPERATIVO)
WHERE
PO.PERIODO_ANUAL = :periodoAnual
AND PO.ESTADO_FORMULACION_OPE IN (SELECT VALOR FROM PARAMETRO WHERE LLAVE = 'param_ppi_ppo_estadoFormulacionOpe_aprobado_unSoloNivel' OR LLAVE = 'param_ppi_ppo_estadoFormulacionOpe_aprobado_unidad_integradora')
AND PO.TIPO_PLAN_OPERATIVO = (SELECT VALOR FROM PARAMETRO WHERE LLAVE = 'param_ppi_ppo_tipoPlanOperativo_unidad')
AND EO.ESTADO_EVALUACION_POA IN (SELECT VALOR FROM PARAMETRO WHERE LLAVE = 'param_ppi_ppo_estadoEvaluacionPOA_aprobado_unSoloNivel' OR LLAVE = 'param_ppi_ppo_estadoEvaluacionPOA_aprobado_unidad_integradora')
AND MPE.ETAPA_PLAN = EO.ETAPA_PLAN
AND EO.FECHA_APROBACION = (
SELECT
MAX(EO.FECHA_APROBACION)
FROM
EVALUACION_OPERATIVA EO1
INNER JOIN PLAN_OPERATIVO PO1 ON (PO1.ID_PLAN_OPERATIVO = EO1.PLAN_OPERATIVO)
WHERE
PO1.ID_PLAN_OPERATIVO = PO.ID_PLAN_OPERATIVO
AND PO1.UNIDAD_EJECUTORA = PO.UNIDAD_EJECUTORA
)
ORDER BY
MPE.META_POA;
Como vemos debemos devolver un lista de meta_poas
Lo primero que creamos es la eliminación de datos
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
-- PASO 0: ELIMINACIÓN DE DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP TYPE PPI.META_POA_TABLE_TYPE;
DROP TYPE PPI.META_POA_TYPE;
DROP FUNCTION PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL;
DROP SYNONYM OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL;
Luego creamos un un tipo de dato metapoatype que es un objeto con un número
Lo ejecutamos una ventana independiente
NOTA: se debe poner el el esquema PPI
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 01: Crear TIPO de tipo de dato META_POA
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.META_POA_TYPE AS OBJECT (
META_POA NUMBER
);
GRANT EXECUTE ON PPI.META_POA_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
Luego en otra ventana creamos una tabla de ese tipo
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 02: Crear TABLA de tipo de dato MetaPoaType
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.META_POA_TABLE_TYPE AS TABLE OF META_POA_TYPE;
GRANT EXECUTE ON PPI.META_POA_TABLE_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
Quedando así:
Con esto podemos crear la función
NOTA: importante poner el esquema PPI
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 03: Crear FUNCIÓN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(
periodoAnual IN NUMBER
)
RETURN META_POA_TABLE_TYPE PIPELINED AS
BEGIN
FOR rec IN (
SELECT MPE.META_POA
FROM META_POA_EVALUACION MPE
INNER JOIN META_POA MP ON (MP.ID_META_POA = MPE.META_POA)
INNER JOIN OBJETIVO_POA OP ON (OP.ID_OBJETIVO_POA = MP.OBJETIVO_POA)
INNER JOIN PLAN_OPERATIVO PO ON (PO.ID_PLAN_OPERATIVO = OP.PLAN_OPERATIVO)
INNER JOIN UNIDAD_EJECUTORA UE_PO ON (UE_PO.ID_UNIDAD_EJECUTORA = PO.UNIDAD_EJECUTORA)
INNER JOIN EVALUACION_OPERATIVA EO ON (EO.PLAN_OPERATIVO = PO.ID_PLAN_OPERATIVO)
WHERE PO.PERIODO_ANUAL = periodoAnual
AND PO.ESTADO_FORMULACION_OPE IN (SELECT VALOR FROM PARAMETRO WHERE LLAVE IN ('param_ppi_ppo_estadoFormulacionOpe_aprobado_unSoloNivel', 'param_ppi_ppo_estadoFormulacionOpe_aprobado_unidad_integradora'))
AND PO.TIPO_PLAN_OPERATIVO = (SELECT VALOR FROM PARAMETRO WHERE LLAVE = 'param_ppi_ppo_tipoPlanOperativo_unidad')
AND EO.ESTADO_EVALUACION_POA IN (SELECT VALOR FROM PARAMETRO WHERE LLAVE IN ('param_ppi_ppo_estadoEvaluacionPOA_aprobado_unSoloNivel', 'param_ppi_ppo_estadoEvaluacionPOA_aprobado_unidad_integradora'))
AND MPE.ETAPA_PLAN = EO.ETAPA_PLAN
AND EO.FECHA_APROBACION = (
SELECT
MAX(EO.FECHA_APROBACION)
FROM
EVALUACION_OPERATIVA EO1
INNER JOIN PLAN_OPERATIVO PO1 ON (PO1.ID_PLAN_OPERATIVO = EO1.PLAN_OPERATIVO)
WHERE
PO1.ID_PLAN_OPERATIVO = PO.ID_PLAN_OPERATIVO
AND PO1.UNIDAD_EJECUTORA = PO.UNIDAD_EJECUTORA
)
ORDER BY MPE.META_POA
) LOOP
PIPE ROW (META_POA_TYPE(rec.META_POA));
END LOOP;
RETURN;
END OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL;
Luego debemos crearle el sinonimo y grants
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 04: GRANTS MetaPoa
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--***********************************************
-- GRANT'S
--***********************************************
GRANT EXECUTE ON PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
Y con esto podemos ejecutar un select que obtenga los datos
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 05: CONSULTAR DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * FROM TABLE(OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(202));
Y hacer fuciones de tablas, por ejemplo
--************************************************************
--SELECT PARA OBTENER LOS PERIODOS_ANUALES DE APORTES POR PERIODO ANUAL
--************************************************************
SELECT
DISTINCT
APD.PERIODO_ANUAL
FROM
META_POA_APORTE MPAP
INNER JOIN (SELECT *
FROM TABLE(OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(:periodoAnual))) METAS_FUNCIONAES on MPAP.META_POA = METAS_FUNCIONAES.META_POA
INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO)
INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO)
ORDER BY APD.PERIODO_ANUAL;
RESUMEN
--*******************************************************************************
--*******************************************************************************
-- FUNCIÓN: OBTENER_METAS_OPE_FUCIONALES_X_PERIODO_ANUAL
--*******************************************************************************
--*******************************************************************************
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
-- PASO 0: ELIMINACIÓN DE DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP TYPE PPI.META_POA_TABLE_TYPE;
DROP TYPE PPI.META_POA_TYPE;
DROP FUNCTION PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL;
DROP SYNONYM OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 01: Crear TIPO de tipo de dato META_POA
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.META_POA_TYPE AS OBJECT (
META_POA NUMBER
);
GRANT EXECUTE ON PPI.META_POA_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 02: Crear TABLA de tipo de dato MetaPoaType
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.META_POA_TABLE_TYPE AS TABLE OF META_POA_TYPE;
GRANT EXECUTE ON PPI.META_POA_TABLE_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 03: Crear FUNCIÓN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(
periodoAnual IN NUMBER
)
RETURN META_POA_TABLE_TYPE PIPELINED AS
BEGIN
FOR rec IN (
SELECT MPE.META_POA
FROM META_POA_EVALUACION MPE
INNER JOIN META_POA MP ON (MP.ID_META_POA = MPE.META_POA)
INNER JOIN OBJETIVO_POA OP ON (OP.ID_OBJETIVO_POA = MP.OBJETIVO_POA)
INNER JOIN PLAN_OPERATIVO PO ON (PO.ID_PLAN_OPERATIVO = OP.PLAN_OPERATIVO)
INNER JOIN UNIDAD_EJECUTORA UE_PO ON (UE_PO.ID_UNIDAD_EJECUTORA = PO.UNIDAD_EJECUTORA)
INNER JOIN EVALUACION_OPERATIVA EO ON (EO.PLAN_OPERATIVO = PO.ID_PLAN_OPERATIVO)
WHERE PO.PERIODO_ANUAL = periodoAnual
AND PO.ESTADO_FORMULACION_OPE IN (SELECT VALOR FROM PARAMETRO WHERE LLAVE IN ('param_ppi_ppo_estadoFormulacionOpe_aprobado_unSoloNivel', 'param_ppi_ppo_estadoFormulacionOpe_aprobado_unidad_integradora'))
AND PO.TIPO_PLAN_OPERATIVO = (SELECT VALOR FROM PARAMETRO WHERE LLAVE = 'param_ppi_ppo_tipoPlanOperativo_unidad')
AND EO.ESTADO_EVALUACION_POA IN (SELECT VALOR FROM PARAMETRO WHERE LLAVE IN ('param_ppi_ppo_estadoEvaluacionPOA_aprobado_unSoloNivel', 'param_ppi_ppo_estadoEvaluacionPOA_aprobado_unidad_integradora'))
AND MPE.ETAPA_PLAN = EO.ETAPA_PLAN
AND EO.FECHA_APROBACION = (
SELECT
MAX(EO.FECHA_APROBACION)
FROM
EVALUACION_OPERATIVA EO1
INNER JOIN PLAN_OPERATIVO PO1 ON (PO1.ID_PLAN_OPERATIVO = EO1.PLAN_OPERATIVO)
WHERE
PO1.ID_PLAN_OPERATIVO = PO.ID_PLAN_OPERATIVO
AND PO1.UNIDAD_EJECUTORA = PO.UNIDAD_EJECUTORA
)
ORDER BY MPE.META_POA
) LOOP
PIPE ROW (META_POA_TYPE(rec.META_POA));
END LOOP;
RETURN;
END OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 04: GRANTS MetaPoa
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--***********************************************
-- GRANT'S
--***********************************************
GRANT EXECUTE ON PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 05: CONSULTAR DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * FROM TABLE(OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(202));
Ahora vamos a crear una función que obtenga los distintos periodos anales de la tabla APORTE_PAE_DISTRIBUCION para las metatas funcionales (por lo uqe requiere el periodoAnual)
CONSULTA
--************************************************************
--SELECT PARA OBTENER LOS PERIODOS_ANUALES DE APORTES POR PERIODO ANUAL
--************************************************************
SELECT
DISTINCT
APD.PERIODO_ANUAL
FROM
META_POA_APORTE MPAP
INNER JOIN (SELECT *
FROM TABLE(PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(:periodoAnual))) METAS_FUNCIONAES on MPAP.META_POA = METAS_FUNCIONAES.META_POA
INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO)
INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO)
ORDER BY APD.PERIODO_ANUAL;
ELIMINACIÓN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
-- PASO 0: ELIMINACIÓN DE DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP TYPE PPI.PERIODO_ANUAL_TABLE_TYPE;
DROP TYPE PPI.PERIODO_ANUAL_TYPE;
DROP FUNCTION PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL;
DROP SYNONYM OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL;
TIPO PERIODO_ANUAL
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 01: Crear TIPO de tipo de dato PERIODO_ANUAL_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.PERIODO_ANUAL_TYPE AS OBJECT (
PERIODO_ANUAL NUMBER
);
GRANT EXECUTE ON PPI.PERIODO_ANUAL_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
SELECT * FROM TABLE(PPI.OBTENER_APD_X_PERIODDO_ANUAL(:periodoAnual))TABLA: PERIODO ANUAL
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 02: Crear TABLA de tipo de dato PERIODO_ANUAL_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.PERIODO_ANUAL_TABLE_TYPE AS TABLE OF PERIODO_ANUAL_TYPE;
GRANT EXECUTE ON PPI.PERIODO_ANUAL_TABLE_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
FUNCIÓN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 03: Crear FUNCIÓN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(
periodoAnual IN NUMBER
)
RETURN PERIODO_ANUAL_TABLE_TYPE PIPELINED AS
BEGIN
FOR rec IN (
SELECT
DISTINCT
APD.PERIODO_ANUAL
FROM
META_POA_APORTE MPAP
INNER JOIN (SELECT *
FROM TABLE(PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(periodoAnual))) METAS_FUNCIONALES on MPAP.META_POA = METAS_FUNCIONALES.META_POA
INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO)
INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO)
ORDER BY APD.PERIODO_ANUAL
) LOOP
PIPE ROW (PERIODO_ANUAL_TYPE(rec.PERIODO_ANUAL));
END LOOP;
RETURN;
END OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL;
SINONIMOS Y GRANTS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 04: GRANTS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
GRANT EXECUTE ON PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
CONSULTA
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 05: CONSULTAR DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(202));
RESUMEN
--*******************************************************************************
--*******************************************************************************
-- FUNCIÓN: OBTENER_PERIODOS_ANUALES_DISTRIBUCION_X_PERIODO_ANUAL
--*******************************************************************************
--*******************************************************************************
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
-- PASO 0: ELIMINACIÓN DE DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP TYPE PPI.PERIODO_ANUAL_TABLE_TYPE;
DROP TYPE PPI.PERIODO_ANUAL_TYPE;
DROP FUNCTION PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL;
DROP SYNONYM OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 01: Crear TIPO de tipo de dato PERIODO_ANUAL_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.PERIODO_ANUAL_TYPE AS OBJECT (
PERIODO_ANUAL NUMBER
);
GRANT EXECUTE ON PPI.PERIODO_ANUAL_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 02: Crear TABLA de tipo de dato PERIODO_ANUAL_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.PERIODO_ANUAL_TABLE_TYPE AS TABLE OF PERIODO_ANUAL_TYPE;
GRANT EXECUTE ON PPI.PERIODO_ANUAL_TABLE_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 03: Crear FUNCIÓN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(
periodoAnual IN NUMBER
)
RETURN PERIODO_ANUAL_TABLE_TYPE PIPELINED AS
BEGIN
FOR rec IN (
SELECT
DISTINCT
APD.PERIODO_ANUAL
FROM
META_POA_APORTE MPAP
INNER JOIN (SELECT *
FROM TABLE(PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(periodoAnual))) METAS_FUNCIONALES on MPAP.META_POA = METAS_FUNCIONALES.META_POA
INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO)
INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO)
ORDER BY APD.PERIODO_ANUAL
) LOOP
PIPE ROW (PERIODO_ANUAL_TYPE(rec.PERIODO_ANUAL));
END LOOP;
RETURN;
END OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 04: GRANTS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
GRANT EXECUTE ON PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 05: CONSULTAR DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(202));
ADICIONAL: crear una función que devuelva la consulta
La modificamos para que devuelva una string
CREATE OR REPLACE FUNCTION OBTENER_APD_X_PERIODDO_ANUAL(
periodoAnual IN NUMBER
)
--RETURN ANO_TABLE_TYPE PIPELINED AS
RETURN CLOB IS
-- Almacena los años en un arreglo
v_periodos PERIODO_ANUAL_TABLE_TYPE := PERIODO_ANUAL_TABLE_TYPE();
-- Variable para almacenar el año actual
v_periodo PERIODO_ANUAL_TYPE;
-- Almacena los años en un arreglo
v_anos ANO_TABLE_TYPE := ANO_TABLE_TYPE();
-- Variable para almacenar el año actual
v_ano ANO_TYPE;
-- Variable para construir la consulta PIVOT
v_sql CLOB;
-- Variable para almacenar el resultado de la consulta PIVOT
v_result SYS_REFCURSOR;
-- Variables para almacenar los valores dinámicos de las columnas del PIVOT
v_valor1 NUMBER;
v_valor2 NUMBER;
v_valor3 NUMBER;
v_valor4 NUMBER;
v_valor5 NUMBER;
BEGIN
-- Llenar el arreglo con los años
FOR rec IN (SELECT ANO, ID_PERIODO_ANUAL
FROM PERIODO_ANUAL
WHERE ID_PERIODO_ANUAL IN (SELECT PERIODO_ANUAL
FROM TABLE(OBTENER_PERIODOS_ANUALES_DISTRIBUCION_X_PERIDDO_ANUAL(periodoAnual)))
ORDER BY ANO)
LOOP
-- Utilizar el constructor del tipo ANO_TYPE
v_ano := ANO_TYPE(rec.ANO);
-- Utilizar el constructor del tipo ANO_TYPE
v_periodo := PERIODO_ANUAL_TYPE(rec.ID_PERIODO_ANUAL);
-- Añadir los resultados al arreglo de años
v_periodos.EXTEND;
v_periodos(v_periodos.LAST) := v_periodo;
-- Añadir los resultados al arreglo de años
v_anos.EXTEND;
v_anos(v_anos.LAST) := v_ano;
-- Retornar el objeto usando PIPE ROW
--PIPE ROW(v_periodo);
--PIPE ROW(v_ano);
END LOOP;
-- Construir la consulta PIVOT dinámicamente
v_sql := 'SELECT * FROM (SELECT APD.APORTE_ESTRATEGICO, APD.PERIODO_ANUAL, APD.DISTRIBUCION_PORCENTUAL ';
v_sql := v_sql || 'FROM META_POA_APORTE MPAP ';
v_sql := v_sql || 'INNER JOIN (SELECT * FROM TABLE(OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(:periodoAnual))) METAS_FUNCIONAES ';
v_sql := v_sql || 'ON MPAP.META_POA = METAS_FUNCIONAES.META_POA ';
v_sql := v_sql || 'INNER JOIN APORTE_ESTRATEGICO APE ON APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO ';
v_sql := v_sql || 'INNER JOIN APORTE_PAE_DISTRIBUCION APD ON APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO ';
v_sql := v_sql || 'WHERE APD.PERIODO_ANUAL IN (SELECT * FROM TABLE(OBTENER_PERIODOS_ANUALES_DISTRIBUCION_X_PERIDDO_ANUAL(:periodoAnual)))) ';
v_sql := v_sql || 'PIVOT (MAX(DISTRIBUCION_PORCENTUAL) FOR PERIODO_ANUAL IN (';
FOR i IN 1..v_anos.LAST LOOP
v_sql := v_sql || v_periodos(i).PERIODO_ANUAL || ' as "' || v_anos(i).ANO || '"';
IF i < v_anos.LAST THEN
v_sql := v_sql || ', ';
END IF;
END LOOP;
v_sql := v_sql || ')) ORDER BY APORTE_ESTRATEGICO';
-- Terminar la función
RETURN v_sql;
END OBTENER_APD_X_PERIODDO_ANUAL;
Y la mandamos a ejecutar asi
SET SERVEROUTPUT ON;
DECLARE
v_query_result VARCHAR2(4000);
BEGIN
v_query_result := PPI.OBTENER_APD_X_PERIODDO_ANUAL(:periodoAnual);
DBMS_OUTPUT.PUT_LINE('Resultado de la consulta SQL: ' || v_query_result);
END;
OTRA PARA RETORNAR SELECT
create or replace FUNCTION PPI.GENERAR_SELECT(parametro IN NUMBER)
RETURN VARCHAR2
AS
v_sql VARCHAR2(4000);
BEGIN
-- Construir la consulta SELECT dinámicamente
v_sql := 'SELECT * FROM (
SELECT
DISTINCT
APD.APORTE_ESTRATEGICO,
APD.PERIODO_ANUAL,
APD.DISTRIBUCION_PORCENTUAL
FROM
META_POA_APORTE MPAP
INNER JOIN (SELECT * FROM TABLE(OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(:periodoAnual))) METAS_FUNCIONALES on MPAP.META_POA = METAS_FUNCIONAES.META_POA
INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO)
INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO)
WHERE
APD.PERIODO_ANUAL IN (SELECT * FROM TABLE(OBTENER_PERIODOS_ANUALES_DISTRIBUCION_X_PERIDDO_ANUAL(:periodoAnual)))
)
PIVOT (
MAX(DISTRIBUCION_PORCENTUAL) FOR PERIODO_ANUAL IN (202 as "2023",222 as "2024",223 as "2025",224 as "2026",225 as "2027")
)
ORDER BY APORTE_ESTRATEGICO';
-- Devolver la consulta SELECT como una cadena de texto
RETURN v_sql;
END GENERAR_SELECT;
RESPALDO
create or replace FUNCTION OBTENER_DISTRIBUCION_PORCENTUAL_X_APORTE (APORTE_ESTRATEGICO_PARAM IN NUMBER) RETURN SYS_REFCURSOR
IS
RESULTADO SYS_REFCURSOR;
SQL_DINAMICO VARCHAR2(4000);
BEGIN
SQL_DINAMICO := 'SELECT APORTE_ESTRATEGICO, ';
FOR R IN (SELECT DISTINCT PERIODO_ANUAL FROM APORTE_PAE_DISTRIBUCION ORDER BY PERIODO_ANUAL)
LOOP
SQL_DINAMICO := SQL_DINAMICO || 'MAX(CASE WHEN PERIODO_ANUAL = ''' || R.PERIODO_ANUAL || ''' THEN DISTRIBUCION_PORCENTUAL END) AS "' || R.PERIODO_ANUAL || '", ';
END LOOP;
SQL_DINAMICO := RTRIM(SQL_DINAMICO, ', ') || ' FROM APORTE_PAE_DISTRIBUCION WHERE APORTE_ESTRATEGICO = :APORTE_ESTRATEGICO_PARAM GROUP BY APORTE_ESTRATEGICO';
OPEN RESULTADO FOR SQL_DINAMICO USING APORTE_ESTRATEGICO_PARAM;
RETURN RESULTADO;
END;
FUNCION OBTENER_APD_X_PERIODDO_ANUAL
Ejemplo de consulta SQL para pruebas
--************************************************************
--SELECT PARA OBTENER LA DISTRIBUCION PORCENTUAL X PERIODO ANUAL
--************************************************************
SELECT * FROM (
SELECT
DISTINCT
APD.APORTE_ESTRATEGICO,
APD.PERIODO_ANUAL,
APD.DISTRIBUCION_PORCENTUAL
FROM
META_POA_APORTE MPAP
INNER JOIN (SELECT * FROM TABLE(PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(:periodoAnual))) METAS_FUNCIONALES on MPAP.META_POA = METAS_FUNCIONALES.META_POA
INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO)
INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO)
WHERE
APD.PERIODO_ANUAL IN (SELECT * FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(:periodoAnual)))
)
PIVOT (
MAX(DISTRIBUCION_PORCENTUAL) FOR PERIODO_ANUAL IN (202 as "2023",222 as "2024",223 as "2025",224 as "2026",225 as "2027")
)
ORDER BY APORTE_ESTRATEGICO;
Primero creamos un tipo de datos para el año
--*****************************************************
--COMPONENTE REQUERIDO: AÑO
--*****************************************************
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
-- PASO 0: ELIMINACIÓN DE DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP TYPE PPI.ANO_TABLE_TYPE;
DROP TYPE PPI.ANO_TYPE;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 01: Crear TIPO de tipo de dato APD_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.ANO_TYPE AS OBJECT (
ANO NUMBER(4)
);
GRANT EXECUTE ON PPI.ANO_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 02: Crear TABLA de tipo de dato ANO_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.ANO_TABLE_TYPE AS TABLE OF ANO_TYPE;
GRANT EXECUTE ON PPI.ANO_TABLE_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
Luego creamos la función de OBTENER_APD_X_PERIODO_ANUAL
--*****************************************************
--FUNCIÓN: OBTENER_APD_X_PERIODDO_ANUAL
--*****************************************************
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
-- PASO 0: ELIMINACIÓN DE DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP TYPE PPI.APD_TABLE_TYPE;
DROP TYPE PPI.APD_TYPE;
DROP FUNCTION PPI.OBTENER_APD_X_PERIODO_ANUAL;
DROP SYNONYM OBTENER_APD_X_PERIODO_ANUAL;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 01: Crear TIPO de tipo de dato APD_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE APD_TYPE AS OBJECT (
APORTE_ESTRATEGICO NUMBER,
ANNO_1 NUMBER,
ANNO_2 NUMBER,
ANNO_3 NUMBER,
ANNO_4 NUMBER,
ANNO_5 NUMBER
);
GRANT EXECUTE ON PPI.APD_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 02: Crear TABLA de tipo de dato APD_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.APD_TABLE_TYPE AS TABLE OF APD_TYPE;
GRANT EXECUTE ON PPI.APD_TABLE_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 03: Crear FUNCIÓN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION PPI.OBTENER_APD_X_PERIODO_ANUAL(
--periodoAnual: parametro de ingreso
periodoAnual IN NUMBER
)
--RETURN: tabla virtual de tipo APD_TABLE_TYPE
RETURN APD_TABLE_TYPE PIPELINED AS
--***************************************************************
--VARIABLES
--***************************************************************
-- Almacena los años en un arreglo
v_periodos PERIODO_ANUAL_TABLE_TYPE := PERIODO_ANUAL_TABLE_TYPE();
-- Variable para almacenar el año actual
v_periodo PERIODO_ANUAL_TYPE;
-- Almacena los años en un arreglo
v_anos ANO_TABLE_TYPE := ANO_TABLE_TYPE();
-- Variable para almacenar el año actual
v_ano ANO_TYPE;
-- Variable para construir la consulta PIVOT
v_sql CLOB;
-- Variable para almacenar el resultado de la consulta PIVOT
v_result SYS_REFCURSOR;
-- Variables para almacenar los valores dinámicos de las columnas del PIVOT
v_aporte NUMBER;
v_anno_1 NUMBER;
v_anno_2 NUMBER;
v_anno_3 NUMBER;
v_anno_4 NUMBER;
v_anno_5 NUMBER;
BEGIN
--***************************************************************
--OBTENCIÓN DE DATOS
--***************************************************************
-- Ejecutar consulta del ID_PERIODO_ANUAL y ANO para almacenarlos en arreglos para crear el PIVOT
FOR rec IN (SELECT ANO, ID_PERIODO_ANUAL
FROM PERIODO_ANUAL
WHERE ID_PERIODO_ANUAL IN (SELECT PERIODO_ANUAL
FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(periodoAnual)))
ORDER BY ANO)
LOOP
-- Utilizar el constructor del tipo ANO_TYPE
v_ano := ANO_TYPE(rec.ANO);
-- Utilizar el constructor del tipo ANO_TYPE
v_periodo := PERIODO_ANUAL_TYPE(rec.ID_PERIODO_ANUAL);
-- Añadir los resultados al arreglo de años
v_periodos.EXTEND;
v_periodos(v_periodos.LAST) := v_periodo;
-- Añadir los resultados al arreglo de años
v_anos.EXTEND;
v_anos(v_anos.LAST) := v_ano;
END LOOP;
--**************************************************************
--CONSULTA DINÁMICA
--**************************************************************
v_sql := 'SELECT * FROM (SELECT APD.APORTE_ESTRATEGICO, APD.PERIODO_ANUAL, APD.DISTRIBUCION_PORCENTUAL ';
v_sql := v_sql || 'FROM META_POA_APORTE MPAP ';
v_sql := v_sql || 'INNER JOIN (SELECT * FROM TABLE(PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(' || periodoAnual || '))) METAS_FUNCIONAES ';
v_sql := v_sql || 'ON MPAP.META_POA = METAS_FUNCIONAES.META_POA ';
v_sql := v_sql || 'INNER JOIN APORTE_ESTRATEGICO APE ON APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO ';
v_sql := v_sql || 'INNER JOIN APORTE_PAE_DISTRIBUCION APD ON APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO ';
v_sql := v_sql || 'WHERE APD.PERIODO_ANUAL IN (SELECT * FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(' || periodoAnual || ')))) ';
v_sql := v_sql || 'PIVOT (MAX(DISTRIBUCION_PORCENTUAL) FOR PERIODO_ANUAL IN (';
FOR i IN 1..v_anos.LAST LOOP
v_sql := v_sql || v_periodos(i).PERIODO_ANUAL || ' as "' || v_anos(i).ANO || '"';
IF i < v_anos.LAST THEN
v_sql := v_sql || ', ';
END IF;
END LOOP;
v_sql := v_sql || ')) ORDER BY APORTE_ESTRATEGICO';
-- Ejecutar la consulta PIVOT
OPEN v_result FOR v_sql;
--**************************************************************
--RECUPERAR Y FORMATEAAR LOS DATOS
--**************************************************************
LOOP
FETCH v_result INTO v_aporte,v_anno_1, v_anno_2, v_anno_3, v_anno_4, v_anno_5;
EXIT WHEN v_result%NOTFOUND;
PIPE ROW(APD_TYPE(v_aporte,v_anno_1, v_anno_2, v_anno_3, v_anno_4, v_anno_5));
END LOOP;
CLOSE v_result;
RETURN;
END OBTENER_APD_X_PERIODO_ANUAL;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 04: GRANTS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--***********************************************
-- GRANT'S
--***********************************************
GRANT EXECUTE ON PPI.OBTENER_APD_X_PERIODO_ANUAL TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 05: CONSULTAR DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * FROM TABLE(PPI.OBTENER_APD_X_PERIODO_ANUAL(202));
RESUMEN
Funcion de programacion
consulta SQL
--************************************************************
--SELECT PARA OBTENER PROGRAMACION X PERIODO ANUAL
--************************************************************
SELECT * FROM (
SELECT
DISTINCT
APD.APORTE_ESTRATEGICO APORTE_ESTRATEGICO,
APD.PERIODO_ANUAL PERIODO_ANUAL,
APD.DISTRIBUCION_PORCENTUAL*(1 / CAN_APO.CANTIDAD_APORTES) PROGRAMACION,
CAN_APO.CANTIDAD_APORTES CANTIDAD_APORTES_X_ACCION,
(1 / CAN_APO.CANTIDAD_APORTES) REPRESENTATIVIDAD
FROM
META_POA_APORTE MPAP
INNER JOIN (SELECT * FROM TABLE(PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(:periodoAnual))) METAS_FUNCIONALES on MPAP.META_POA = METAS_FUNCIONALES.META_POA
INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO)
INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO)
INNER JOIN ACCION_ESTRATEGICA ACE ON (ACE.ID_ACCION_ESTRATEGICA = APE.ACCION_ESTRATEGICA)
INNER JOIN (SELECT ACCION_ESTRATEGICA, COUNT(*) AS CANTIDAD_APORTES FROM PPI.APORTE_ESTRATEGICO GROUP BY ACCION_ESTRATEGICA) CAN_APO ON CAN_APO.ACCION_ESTRATEGICA = ACE.ID_ACCION_ESTRATEGICA
WHERE
APD.PERIODO_ANUAL IN (SELECT * FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(:periodoAnual)))
)
PIVOT (
MAX(PROGRAMACION) FOR PERIODO_ANUAL IN (202 as "2023",222 as "2024",223 as "2025",224 as "2026",225 as "2027")
)
ORDER BY APORTE_ESTRATEGICO;
RESUMEN
--*******************************************************************************
--*******************************************************************************
-- FUNCIÓN: OBTENER_PROGRAMACION_X_PERIODO_ANUAL
--*******************************************************************************
--*******************************************************************************
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
-- PASO 0: ELIMINACIÓN DE DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP TYPE PPI.PROGRAMACION_TABLE_TYPE;
DROP TYPE PPI.PROGRAMACION_TYPE;
DROP FUNCTION PPI.OBTENER_PROGRAMACION_X_PERIODO_ANUAL;
DROP SYNONYM OBTENER_PROGRAMACION_X_PERIODO_ANUAL;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 01: Crear TIPO de tipo de dato APD_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.PROGRAMACION_TYPE AS OBJECT (
APORTE_ESTRATEGICO NUMBER,
CANTIDAD_APORTES NUMBER,
REPRESENTATIVIDAD NUMBER,
PROGRAMACION_1 NUMBER,
PROGRAMACION_2 NUMBER,
PROGRAMACION_3 NUMBER,
PROGRAMACION_4 NUMBER,
PROGRAMACION_5 NUMBER
);
GRANT EXECUTE ON PPI.PROGRAMACION_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 02: Crear TABLA de tipo de dato PROGRAMACION_TYPE
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE PPI.PROGRAMACION_TABLE_TYPE AS TABLE OF PROGRAMACION_TYPE;
GRANT EXECUTE ON PPI.PROGRAMACION_TABLE_TYPE TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 03: Crear FUNCIÓN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION PPI.OBTENER_PROGRAMACION_X_PERIODO_ANUAL(
--periodoAnual: parametro de ingreso
periodoAnual IN NUMBER
)
--RETURN: tabla virtual de tipo APD_TABLE_TYPE
RETURN PROGRAMACION_TABLE_TYPE PIPELINED AS
--***************************************************************
--VARIABLES
--***************************************************************
-- Almacena los años en un arreglo
v_periodos PERIODO_ANUAL_TABLE_TYPE := PERIODO_ANUAL_TABLE_TYPE();
-- Variable para almacenar el año actual
v_periodo PERIODO_ANUAL_TYPE;
-- Almacena los años en un arreglo
v_anos ANO_TABLE_TYPE := ANO_TABLE_TYPE();
-- Variable para almacenar el año actual
v_ano ANO_TYPE;
-- Variable para construir la consulta PIVOT
v_sql CLOB;
-- Variable para almacenar el resultado de la consulta PIVOT
v_result SYS_REFCURSOR;
-- Variables para almacenar los valores dinámicos de las columnas del PIVOT
v_aporte NUMBER;
v_cantidad_aporte NUMBER;
v_representatividad NUMBER;
v_programacion_1 NUMBER;
v_programacion_2 NUMBER;
v_programacion_3 NUMBER;
v_programacion_4 NUMBER;
v_programacion_5 NUMBER;
BEGIN
--***************************************************************
--OBTENCIÓN DE DATOS
--***************************************************************
-- Ejecutar consulta del ID_PERIODO_ANUAL y ANO para almacenarlos en arreglos para crear el PIVOT
FOR rec IN (SELECT ANO, ID_PERIODO_ANUAL
FROM PERIODO_ANUAL
WHERE ID_PERIODO_ANUAL IN (SELECT PERIODO_ANUAL
FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(periodoAnual)))
ORDER BY ANO)
LOOP
-- Utilizar el constructor del tipo ANO_TYPE
v_ano := ANO_TYPE(rec.ANO);
-- Utilizar el constructor del tipo ANO_TYPE
v_periodo := PERIODO_ANUAL_TYPE(rec.ID_PERIODO_ANUAL);
-- Añadir los resultados al arreglo de años
v_periodos.EXTEND;
v_periodos(v_periodos.LAST) := v_periodo;
-- Añadir los resultados al arreglo de años
v_anos.EXTEND;
v_anos(v_anos.LAST) := v_ano;
END LOOP;
v_sql := 'SELECT * FROM ( ';
v_sql := v_sql || 'SELECT ';
v_sql := v_sql || 'DISTINCT ';
v_sql := v_sql || 'APD.APORTE_ESTRATEGICO APORTE_ESTRATEGICO, ';
v_sql := v_sql || 'APD.PERIODO_ANUAL PERIODO_ANUAL, ';
v_sql := v_sql || 'APD.DISTRIBUCION_PORCENTUAL*(1 / CAN_APO.CANTIDAD_APORTES) PROGRAMACION, ';
v_sql := v_sql || 'CAN_APO.CANTIDAD_APORTES CANTIDAD_APORTES_X_ACCION, ';
v_sql := v_sql || '(1 / CAN_APO.CANTIDAD_APORTES) REPRESENTATIVIDAD';
v_sql := v_sql || ' FROM ';
v_sql := v_sql || 'META_POA_APORTE MPAP ';
v_sql := v_sql || 'INNER JOIN (SELECT * FROM TABLE(PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(' || periodoAnual || '))) METAS_FUNCIONALES on MPAP.META_POA = METAS_FUNCIONALES.META_POA ';
v_sql := v_sql || 'INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO) ';
v_sql := v_sql || 'INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO) ';
v_sql := v_sql || 'INNER JOIN ACCION_ESTRATEGICA ACE ON (ACE.ID_ACCION_ESTRATEGICA = APE.ACCION_ESTRATEGICA) ';
v_sql := v_sql || 'INNER JOIN (SELECT ACCION_ESTRATEGICA, COUNT(*) AS CANTIDAD_APORTES FROM PPI.APORTE_ESTRATEGICO GROUP BY ACCION_ESTRATEGICA) CAN_APO ON CAN_APO.ACCION_ESTRATEGICA = ACE.ID_ACCION_ESTRATEGICA ';
v_sql := v_sql || 'WHERE ';
v_sql := v_sql || 'APD.PERIODO_ANUAL IN (SELECT * FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(' || periodoAnual || '))) ';
v_sql := v_sql || ') ';
v_sql := v_sql || 'PIVOT ( ';
v_sql := v_sql || 'MAX(PROGRAMACION) FOR PERIODO_ANUAL IN ( ';
-- CONSTRUIR PIVOTEO
FOR i IN 1..v_anos.LAST LOOP
v_sql := v_sql || v_periodos(i).PERIODO_ANUAL || ' as "' || v_anos(i).ANO || '"';
IF i < v_anos.LAST THEN
v_sql := v_sql || ', ';
END IF;
END LOOP;
v_sql := v_sql || ') ';
v_sql := v_sql || ') ';
v_sql := v_sql || 'ORDER BY APORTE_ESTRATEGICO';
DBMS_OUTPUT.PUT_LINE('DEBUG: v_sql: ' || v_sql);
-- Ejecutar la consulta PIVOT
OPEN v_result FOR v_sql;
--**************************************************************
--RECUPERAR Y FORMATEAAR LOS DATOS
--**************************************************************
LOOP
FETCH v_result INTO v_aporte,v_cantidad_aporte, v_representatividad, v_programacion_1, v_programacion_2, v_programacion_3, v_programacion_4, v_programacion_5;
EXIT WHEN v_result%NOTFOUND;
PIPE ROW(PROGRAMACION_TYPE(v_aporte,v_cantidad_aporte, v_representatividad, v_programacion_1, v_programacion_2, v_programacion_3, v_programacion_4, v_programacion_5));
END LOOP;
CLOSE v_result;
RETURN;
END OBTENER_PROGRAMACION_X_PERIODO_ANUAL;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 04: GRANTS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--***********************************************
-- GRANT'S
--***********************************************
GRANT EXECUTE ON PPI.OBTENER_PROGRAMACION_X_PERIODO_ANUAL TO WWW_SIGESA,ANALISTA, ANALISTA_SIGESA;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
--PASO 05: CONSULTAR DATOS
--+++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * FROM TABLE(PPI.OBTENER_PROGRAMACION_X_PERIODO_ANUAL(202));
ADEMAS TENEMOS UNA FUNCIÓN PARA LA CONSULTA
CREATE OR REPLACE FUNCTION PPI.OBTENER_PROGRAMACION_X_PERIODO_ANUAL_CONSULTA(
--periodoAnual: parametro de ingreso
periodoAnual IN NUMBER
)
--RETURN: tabla virtual de tipo APD_TABLE_TYPE
RETURN CLOB IS
--***************************************************************
--VARIABLES
--***************************************************************
-- Almacena los años en un arreglo
v_periodos PERIODO_ANUAL_TABLE_TYPE := PERIODO_ANUAL_TABLE_TYPE();
-- Variable para almacenar el año actual
v_periodo PERIODO_ANUAL_TYPE;
-- Almacena los años en un arreglo
v_anos ANO_TABLE_TYPE := ANO_TABLE_TYPE();
-- Variable para almacenar el año actual
v_ano ANO_TYPE;
-- Variable para construir la consulta PIVOT
v_sql CLOB;
-- Variable para almacenar el resultado de la consulta PIVOT
v_result SYS_REFCURSOR;
-- Variables para almacenar los valores dinámicos de las columnas del PIVOT
v_aporte NUMBER;
v_programacion_1 NUMBER;
v_programacion_2 NUMBER;
v_programacion_3 NUMBER;
v_programacion_4 NUMBER;
v_programacion_5 NUMBER;
BEGIN
--***************************************************************
--OBTENCIÓN DE DATOS
--***************************************************************
-- Ejecutar consulta del ID_PERIODO_ANUAL y ANO para almacenarlos en arreglos para crear el PIVOT
FOR rec IN (SELECT ANO, ID_PERIODO_ANUAL
FROM PERIODO_ANUAL
WHERE ID_PERIODO_ANUAL IN (SELECT PERIODO_ANUAL
FROM TABLE(OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(periodoAnual)))
ORDER BY ANO)
LOOP
-- Utilizar el constructor del tipo ANO_TYPE
v_ano := ANO_TYPE(rec.ANO);
-- Utilizar el constructor del tipo ANO_TYPE
v_periodo := PERIODO_ANUAL_TYPE(rec.ID_PERIODO_ANUAL);
-- Añadir los resultados al arreglo de años
v_periodos.EXTEND;
v_periodos(v_periodos.LAST) := v_periodo;
-- Añadir los resultados al arreglo de años
v_anos.EXTEND;
v_anos(v_anos.LAST) := v_ano;
END LOOP;
v_sql := 'SELECT * FROM ( ';
v_sql := v_sql || 'SELECT ';
v_sql := v_sql || 'DISTINCT ';
v_sql := v_sql || 'APD.APORTE_ESTRATEGICO APORTE_ESTRATEGICO, ';
v_sql := v_sql || 'APD.PERIODO_ANUAL PERIODO_ANUAL, ';
v_sql := v_sql || 'APD.DISTRIBUCION_PORCENTUAL*(1 / CAN_APO.CANTIDAD_APORTES) PROGRAMACION, ';
v_sql := v_sql || 'CAN_APO.CANTIDAD_APORTES CANTIDAD_APORTES_X_ACCION, ';
v_sql := v_sql || '(1 / CAN_APO.CANTIDAD_APORTES) REPRESENTATIVIDAD';
v_sql := v_sql || ' FROM ';
v_sql := v_sql || 'META_POA_APORTE MPAP ';
v_sql := v_sql || 'INNER JOIN (SELECT * FROM TABLE(PPI.OBTENER_METAS_OPE_FUNCIONALES_X_PERIODO_ANUAL(' || periodoAnual || '))) METAS_FUNCIONALES on MPAP.META_POA = METAS_FUNCIONALES.META_POA ';
v_sql := v_sql || 'INNER JOIN APORTE_ESTRATEGICO APE ON (APE.ID_APORTE_ESTRATEGICO = MPAP.APORTE_ESTRATEGICO) ';
v_sql := v_sql || 'INNER JOIN APORTE_PAE_DISTRIBUCION APD ON (APD.APORTE_ESTRATEGICO = APE.ID_APORTE_ESTRATEGICO) ';
v_sql := v_sql || 'INNER JOIN ACCION_ESTRATEGICA ACE ON (ACE.ID_ACCION_ESTRATEGICA = APE.ACCION_ESTRATEGICA) ';
v_sql := v_sql || 'INNER JOIN (SELECT ACCION_ESTRATEGICA, COUNT(*) AS CANTIDAD_APORTES FROM PPI.APORTE_ESTRATEGICO GROUP BY ACCION_ESTRATEGICA) CAN_APO ON CAN_APO.ACCION_ESTRATEGICA = ACE.ID_ACCION_ESTRATEGICA ';
v_sql := v_sql || 'WHERE ';
v_sql := v_sql || 'APD.PERIODO_ANUAL IN (SELECT * FROM TABLE(PPI.OBTENER_PERIODO_ANUAL_DISTRIBUCION_X_PERIODO_ANUAL(' || periodoAnual || '))) ';
v_sql := v_sql || ') ';
v_sql := v_sql || 'PIVOT ( ';
v_sql := v_sql || 'MAX(PROGRAMACION) FOR PERIODO_ANUAL IN ( ';
-- CONSTRUIR PIVOTEO
FOR i IN 1..v_anos.LAST LOOP
v_sql := v_sql || v_periodos(i).PERIODO_ANUAL || ' as "' || v_anos(i).ANO || '"';
IF i < v_anos.LAST THEN
v_sql := v_sql || ', ';
END IF;
END LOOP;
v_sql := v_sql || ') ';
v_sql := v_sql || ') ';
v_sql := v_sql || 'ORDER BY APORTE_ESTRATEGICO';
DBMS_OUTPUT.PUT_LINE('DEBUG: v_sql: ' || v_sql);
RETURN v_sql;
END OBTENER_PROGRAMACION_X_PERIODO_ANUAL_CONSULTA;
DBMS_OUTPUT.PUT_LINE
Esta linea
DBMS_OUTPUT.PUT_LINE('DEBUG: v_sql: ' || v_sql);
Nos permite consultar el resultado de la consulta para saber si esta bien
para probarla dentro de la función le damos ejecutar y nos muestra el resultado
