Paquete que lleva la parametrización de la aplicación

PARAM_APLICACION.sql

CREATE OR REPLACE PACKAGE PSPGEN.PARAM_APLICACION
AS
   FUNCTION WEB_URL_BASE RETURN VARCHAR2;
   FUNCTION WEB_DEFAULT_PAGE RETURN VARCHAR2;
   FUNCTION URL_ACCESO RETURN VARCHAR2;
   FUNCTION DATABASE_OWNER RETURN VARCHAR2;
   FUNCTION APP_NAME RETURN VARCHAR2;
   FUNCTION LINK_APP RETURN VARCHAR2;
   FUNCTION LINK_APP_EMAIL RETURN VARCHAR2;
   FUNCTION REPORT_WEBSERVICE_URL RETURN VARCHAR2;
   FUNCTION REPORT_SERVER RETURN VARCHAR2;
   FUNCTION REPORT_CONNECTION RETURN VARCHAR2;
   FUNCTION REPORT_JOBID_URL RETURN VARCHAR2;
   FUNCTION WEB_AVISO RETURN VARCHAR2;
   PROCEDURE PINTAR_WEB_AVISO;
   FUNCTION CHAMELEON_WS_URL RETURN VARCHAR2;
   FUNCTION CHAMELEON_APP RETURN VARCHAR2;
   FUNCTION CHAMELEON_USER RETURN VARCHAR2;
   FUNCTION CHAMELEON_PASSWORD RETURN VARCHAR2;
   FUNCTION MIN_SESION_ACTIVA RETURN VARCHAR2;
END PARAM_APLICACION;
/

CREATE OR REPLACE PACKAGE BODY PSPGEN.PARAM_APLICACION
AS
    FUNCTION get_param_reg RETURN param_aplicaciones%ROWTYPE IS
        param_reg param_aplicaciones%ROWTYPE;
    BEGIN    
        SELECT  *
        INTO    param_reg
        FROM    param_aplicaciones
        WHERE   server_name = OWA_UTIL.get_cgi_env ('SERVER_NAME')
        AND     dad = OWA_UTIL.GET_OWA_SERVICE_PATH;
        
        RETURN  param_reg;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN param_reg;
    END get_param_reg;

    FUNCTION WEB_URL_BASE RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        RETURN  param_reg.WEB_URL_BASE;
    END WEB_URL_BASE;  
    
    FUNCTION WEB_DEFAULT_PAGE RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        RETURN  param_reg.WEB_DEFAULT_PAGE;
    END WEB_DEFAULT_PAGE;  
                
    FUNCTION URL_ACCESO RETURN VARCHAR2 IS   
    BEGIN
        RETURN  WEB_URL_BASE || WEB_DEFAULT_PAGE;
    END URL_ACCESO;   
 
    FUNCTION DATABASE_OWNER RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        RETURN  param_reg.DATABASE_OWNER || '.';
    END DATABASE_OWNER;
        
    FUNCTION APP_NAME RETURN VARCHAR2 IS
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        IF param_reg.APP_ENTORNO = 'PROD' THEN
            RETURN  param_reg.APP_NAME;
        ELSE
            RETURN  param_reg.APP_NAME || '(' || param_reg.APP_ENTORNO || ')';
        END IF;
    END;
    
    FUNCTION LINK_APP RETURN VARCHAR2 IS   
    BEGIN
        RETURN  '<a href="' || URL_ACCESO || '">' || APP_NAME || '</a>';
    END LINK_APP;  
    
    FUNCTION LINK_APP_EMAIL RETURN VARCHAR2 IS   
    BEGIN
        RETURN  APP_NAME || '(' || URL_ACCESO || ')';
    END LINK_APP_EMAIL;   
    
    FUNCTION REPORT_WEBSERVICE_URL RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        RETURN  param_reg.REPORT_WEBSERVICE_URL;
    END REPORT_WEBSERVICE_URL;     

    FUNCTION REPORT_SERVER RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        RETURN  param_reg.REPORT_SERVER;
    END REPORT_SERVER; 
    
    FUNCTION REPORT_CONNECTION RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        RETURN  param_reg.REPORT_CONNECTION;
    END REPORT_CONNECTION; 
        
    FUNCTION REPORT_JOBID_URL RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        RETURN  param_reg.REPORT_JOBID_URL;
    END REPORT_JOBID_URL;   
    
    FUNCTION WEB_AVISO RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        IF  param_reg.WEB_AVISO IS NOT NULL 
        AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(param_reg.WEB_AVISO_DESDE, SYSDATE)) 
                               AND TRUNC(NVL(param_reg.WEB_AVISO_HASTA, SYSDATE)) 
        THEN
            RETURN param_reg.WEB_AVISO;
        ELSE
            RETURN NULL;
        END IF;
    END WEB_AVISO;    
    
    PROCEDURE PINTAR_WEB_AVISO IS  
    BEGIN
        IF WEB_AVISO IS NOT NULL THEN
            htp.p(pspgen.pintarparrafo(pspgen.objetoparrafo (WEB_AVISO, 2)));
            RETURN ;
        ELSE
            NULL;
        END IF;       
    EXCEPTION
        WHEN OTHERS THEN
            NULL;     
    END PINTAR_WEB_AVISO;    
    
   FUNCTION CHAMELEON_WS_URL RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        IF param_reg.CHAMELEON_WS_URL IS NOT NULL THEN
            RETURN  param_reg.CHAMELEON_WS_URL;
        ELSE
            RETURN 'http://S72V09-AESA.aviacion.fomento.es:333/SetupChamaleon/Service.asmx';
        END IF;
    END CHAMELEON_WS_URL;
    
   FUNCTION CHAMELEON_APP RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        IF param_reg.CHAMELEON_APP IS NOT NULL THEN
            RETURN  param_reg.CHAMELEON_APP;
        ELSE
            RETURN 'RMA1';
        END IF;
    END CHAMELEON_APP;
    
   FUNCTION CHAMELEON_USER RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        IF param_reg.CHAMELEON_USER IS NOT NULL THEN
            RETURN  param_reg.CHAMELEON_USER;
        ELSE
            RETURN 'RMA654';
        END IF;
    END CHAMELEON_USER;
    
   FUNCTION CHAMELEON_PASSWORD RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        IF param_reg.CHAMELEON_PASSWORD IS NOT NULL THEN
            RETURN  param_reg.CHAMELEON_PASSWORD;
        ELSE
            RETURN '65468798';
        END IF;
    END CHAMELEON_PASSWORD;    
    
    FUNCTION MIN_SESION_ACTIVA RETURN VARCHAR2 IS   
        param_reg param_aplicaciones%ROWTYPE := get_param_reg; 
    BEGIN
        RETURN  param_reg.MIN_SESION_ACTIVA;
    END MIN_SESION_ACTIVA;                   
END PARAM_APLICACION;
/