SQL - Algoritmo...

tengo un problemilla con una Query que se hace en una aplicación...

para mas detalles lo subiré en: http://www.fernandosanabria.eu/problema/enviar.doc

este es el resumen:

Hola,

Necesito solucionar esta incidencia:

Resumen modelo del negocio:

Este es un negocio dedicado a la venta de transporte de tipo terrestre.

Este es el modelo del proceso del negocio:


Este gráfico explica de que un “programa” tiene varios servicios , un servicio tiene varios circuitos; mediante un caso real lo explicarè mas a fondo:

Programa = Tour Europa

Servicio = Portugal – Andalucía

CIRCUITO CUPOS
MADRID – LISBOA
29

LISBOA – LISBOA
68
LISBOA – SEVILLA
61
SEVILLA – ALGECIRAS
60
ALGECIRAS – COSTA DEL SOL
56
COSTA DEL SOL – GRANADA
60
GRANADA – TOLEDO
63
TOLEDO - MADRID
63

EL ALGORITMO ACTUALMENTE ESTA ARROJANDO EL VALOR ULTIMO DEL CUPO QUE EN ESTE CASO ES “63” Y ESTA MAL DEBERÍA ARROJAR EL VALOR DEL CUPO MAXIMO QUE EN ESTE CASO DEBERIA SER “68”

LO KE PROPONGO ES CREAR UNA VARIABLE EN LA CUAL UNA VEZ QUE SUME LAS VARIABLES (YA LAS VERÀS CUALES SON...) GUARDE EL VALOR MAXIMO Y ME LO MUESTRE!!!

ESTE ES EL ALGORITMO COMPLETO:

CREATE OR REPLACE FUNCTION FUN_GET_CANT_OCUPADO_CUPO_DIA ( P_SERVICIO IN SGT_SERVICIO.COD_SERVICIO%TYPE , P_DIA IN SGT_CUPO_DIA.COD_CUPO_DIA%TYPE , P_CUPO IN SGT_CUPO.COD_CUPO%TYPE ) RETURN VARCHAR2
IS

V_Maximo NUMBER(5) ;
V_TotEtapa NUMBER(5) ;
V_AntEtapa SGT_OCUPACION_ETAPA_CRUCERO.COD_ETAPA_CRUCERO%TYPE ;
V_CodEtapa SGT_OCUPACION_ETAPA_CRUCERO.COD_ETAPA_CRUCERO%TYPE ;
V_AntReserva SGT_OCUPACION_ETAPA_CRUCERO.COD_RESERVA%TYPE ;
V_Reserva SGT_OCUPACION_ETAPA_CRUCERO.COD_RESERVA%TYPE ;
V_Ocupado SGT_OCUPACION_ETAPA_CRUCERO.OCUPADO%TYPE ;
V_Return VARCHAR2(5) ;
V_TipoServicio SGT_SERVICIO.COD_TIPO_SERVICIO%TYPE ;
V_IndTipo SGT_TIPO_SERVICIO.IND_TIPO%TYPE ;
V_Salida SGT_CUPO_DIA.FECHA%TYPE;
V_Edad SGT_CUPO.COD_EDAD_PASAJERO%TYPE ;
V_Caract SGT_CUPO.COD_CARAC_PASAJERO%TYPE ;
V_Clase SGT_CUPO.COD_CLASE_VUELO%TYPE ;
V_Valor SGT_CUPO.COD_VALOR_PARAMETRO_SERVICIO%TYPE ;

CURSOR C1 IS SELECT OCUPADO AS OCUPADO,
COD_ETAPA_TERRESTRE AS ETAPA,
COD_RESERVA AS RESERVA
FROM SGT_OCUPACION_ETAPA_TERRESTRE
WHERE TO_CHAR(FECHA,'DD/MM/YYYY' ) = TO_CHAR(V_Salida,'DD/MM/YYYY')
AND COD_SERVICIO = P_SERVICIO
ORDER BY COD_ETAPA_TERRESTRE ASC ,COD_RESERVA ASC ,VERSION_RESERVA DESC ;

CURSOR C2 IS SELECT OCUPADO AS OCUPADO,
COD_ETAPA_CRUCERO AS ETAPA,
COD_RESERVA AS RESERVA
FROM SGT_OCUPACION_ETAPA_CRUCERO
WHERE TO_CHAR(FECHA,'DD/MM/YYYY' ) = TO_CHAR(V_Salida,'DD/MM/YYYY')
AND NVL(COD_EDAD_PASAJERO, 0) = NVL(V_Edad, 0)
AND NVL(COD_CARAC_PASAJERO, 0) = NVL(V_Caract, 0)
AND NVL(COD_VALOR_PARAMETRO_SERVICIO, 0) = NVL(V_Valor, 0 )
--AND COD_CUPO IS NULL
--AND COD_SERVICIO_CUPO IS NULL
AND COD_SERVICIO = P_SERVICIO
ORDER BY COD_ETAPA_CRUCERO ASC ,COD_RESERVA ASC ,VERSION_RESERVA DESC ;

CURSOR C3 IS SELECT OCUPADO AS OCUPADO,
COD_RESERVA AS RESERVA
FROM SGT_OCUPACION_SERVICIO
WHERE TO_CHAR(FECHA,'DD/MM/YYYY' ) = TO_CHAR(V_Salida,'DD/MM/YYYY')
AND NVL(COD_CLASE_VUELO, 0) = NVL(V_Clase, 0 )
AND NVL(COD_VALOR_PARAMETRO_SERVICIO, 0) = NVL(V_Valor, 0 )
AND COD_SERVICIO = P_SERVICIO
ORDER BY COD_RESERVA ASC ,VERSION_RESERVA DESC ;

V_CursorT C1%ROWTYPE ;
V_CursorC C2%ROWTYPE ;
V_CursorS C3%ROWTYPE ;

BEGIN

V_Maximo := 0 ;
V_TotEtapa := 0 ;
V_AntEtapa := 0 ;
V_AntReserva := 0 ;

-- Recuperamos el tipo de servicio.
SELECT SGT_SERVICIO.COD_TIPO_SERVICIO
INTO V_TipoServicio
FROM SGT_SERVICIO
WHERE SGT_SERVICIO.COD_SERVICIO = P_SERVICIO ;

-- Recuperamos el indicatipo del tipo de servicio.
SELECT SGT_TIPO_SERVICIO.IND_TIPO
INTO V_IndTipo
FROM SGT_TIPO_SERVICIO
WHERE SGT_TIPO_SERVICIO.COD_TIPO_SERVICIO = V_TipoServicio ;

IF ( V_IndTipo IS NULL ) THEN
V_IndTipo := 'S' ;
END IF ;

-- Recuperamos el día mediante su código.
SELECT SGT_CUPO_DIA.FECHA
INTO V_Salida
FROM SGT_CUPO_DIA
WHERE SGT_CUPO_DIA.COD_CUPO_DIA = P_DIA ;

IF ( V_IndTipo = 'T' ) THEN
-- Calculamos la ocupación
OPEN C1 ;
LOOP
FETCH C1 INTO V_CursorT ;
EXIT WHEN C1%NOTFOUND ;

V_Ocupado := V_CursorT.OCUPADO ;
V_CodEtapa := V_CursorT.ETAPA ;
V_Reserva := V_CursorT.RESERVA ;

IF ( V_CodEtapa <> V_AntEtapa ) THEN
IF ( V_TotEtapa > V_Maximo ) THEN
V_Maximo := V_TotEtapa ;
END IF ;
V_TotEtapa := 0 ;
V_AntReserva := 0 ;
END IF ;

IF ( V_AntReserva <> V_Reserva ) THEN
V_TotEtapa := V_TotEtapa + V_Ocupado ;
END IF ;

V_AntEtapa := V_CodEtapa ;
V_AntReserva := V_Reserva ;

END LOOP ;
CLOSE C1 ;

ELSIF ( V_IndTipo = 'C' ) THEN

-- Recuperamos los valores del cupo.
SELECT SGT_CUPO.COD_EDAD_PASAJERO,
SGT_CUPO.COD_CARAC_PASAJERO,
SGT_CUPO.COD_CLASE_VUELO,
SGT_CUPO.COD_VALOR_PARAMETRO_SERVICIO
INTO V_Edad,
V_Caract,
V_Clase,
V_Valor
FROM SGT_CUPO
WHERE SGT_CUPO.COD_CUPO = P_CUPO ;

-- Calculamos la ocupación
OPEN C2 ;
LOOP
FETCH C2 INTO V_CursorC ;
EXIT WHEN C2%NOTFOUND ;

V_Ocupado := V_CursorC.OCUPADO ;
V_CodEtapa := V_CursorC.ETAPA ;
V_Reserva := V_CursorC.RESERVA ;

IF ( V_CodEtapa <> V_AntEtapa ) THEN
IF ( V_TotEtapa > V_Maximo ) THEN
V_Maximo := V_TotEtapa ;
END IF ;
V_TotEtapa := 0 ;
END IF ;

IF ( V_AntReserva <> V_Reserva ) THEN
V_TotEtapa := V_TotEtapa + V_Ocupado ;
END IF ;

V_AntEtapa := V_CodEtapa ;
V_AntReserva := V_Reserva ;

END LOOP ;
CLOSE C2 ;
ELSE
-- Recuperamos los valores del cupo.
SELECT SGT_CUPO.COD_CLASE_VUELO,
SGT_CUPO.COD_VALOR_PARAMETRO_SERVICIO
INTO V_Clase,
V_Valor
FROM SGT_CUPO
WHERE SGT_CUPO.COD_CUPO = P_CUPO ;

-- Calculamos la ocupación
OPEN C3 ;
LOOP
FETCH C3 INTO V_CursorS ;
EXIT WHEN C3%NOTFOUND ;

V_Ocupado := V_CursorS.OCUPADO ;
V_Reserva := V_CursorS.RESERVA ;

--IF ( V_AntReserva <> V_Reserva ) THEN
V_Maximo := V_Maximo + V_Ocupado ;
--END IF ;

V_AntReserva := V_Reserva ;

END LOOP ;
CLOSE C3 ;

END IF ;

V_Return := TO_CHAR( V_Maximo ) ;

RETURN V_Return ;

EXCEPTION
WHEN OTHERS
THEN RETURN '-' ;
END ;
/


Y ESTA ES LA PARTE A “ARREGLAR”:

IF ( V_IndTipo = 'T' ) THEN
-- Calculamos la ocupación
OPEN C1 ;
LOOP
FETCH C1 INTO V_CursorT ;
EXIT WHEN C1%NOTFOUND ;

V_Ocupado := V_CursorT.OCUPADO ;
V_CodEtapa := V_CursorT.ETAPA ;
V_Reserva := V_CursorT.RESERVA ;

IF ( V_CodEtapa <> V_AntEtapa ) THEN
IF ( V_TotEtapa > V_Maximo ) THEN
V_Maximo := V_TotEtapa ;
END IF ;
V_TotEtapa := 0 ;
V_AntReserva := 0 ;
END IF ;

IF ( V_AntReserva <> V_Reserva ) THEN
V_TotEtapa := V_TotEtapa + V_Ocupado ;
END IF ;

V_AntEtapa := V_CodEtapa ;
V_AntReserva := V_Reserva ;

END LOOP ;
CLOSE C1 ;

ESO ES TODO GRACIAS!!!