CREATE OR REPLACE PROCEDURE P_INS_TBL_ALICUOTA_MENSUAL (AS_PERIODO IN VARCHAR2 ) IS TYPE t_nro_id IS TABLE OF TBL_ALICUOTA.NRO_ID%TYPE; TYPE t_periodo IS TABLE OF TBL_ALICUOTA.PERIODO%TYPE; TYPE t_nro_dni IS TABLE OF TBL_ALICUOTA.NRO_DNI%TYPE; TYPE t_nro_edad IS TABLE OF TBL_ALICUOTA.NRO_EDAD%TYPE; TYPE t_mto_tran IS TABLE OF TBL_ALICUOTA.MTO_TRANS%TYPE; TYPE t_acu_mensual IS TABLE OF TBL_ALICUOTA.ACU_MENSUAL%TYPE; TYPE integer_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER; TYPE number_type IS TABLE OF NUMBER(15, 8) INDEX BY PLS_INTEGER; L_NRO_ID t_nro_id; L_PERIODO t_periodo; L_DOCUMENTO t_nro_dni; L_EDAD t_nro_edad; L_CUOTA integer_type; L_TRANSFERENCIA t_mto_tran; L_ALICUOTA t_acu_mensual; L_RESERVA number_type; --RESERVA LS_DNI VARCHAR2(15); LN_EDAD NUMBER ; LN_RESERVA NUMBER ; TYPE t_documentom IS TABLE OF TBL_ALICUOTA_MENSUAL.DOCUMENTO%TYPE; TYPE t_edadm IS TABLE OF TBL_ALICUOTA_MENSUAL.EDAD%TYPE; TYPE t_cuotam IS TABLE OF TBL_ALICUOTA_MENSUAL.CUOTA%TYPE; TYPE t_alicuotam IS TABLE OF TBL_ALICUOTA_MENSUAL.ALICUOTA%TYPE; TYPE t_reservam IS TABLE OF TBL_ALICUOTA_MENSUAL.RESERVA%TYPE; L_DOCUMENTOM t_documentom; L_EDADM t_edadm; L_CUOTAM t_cuotam; L_ALICUOTAM t_alicuotam; L_RESERVAM t_reservam; CURSOR CUR_RESERVA IS SELECT PERIODO, DOCUMENTO, EDAD, CUOTA, TRANSFERENCIA, ALICUOTA, RESERVA FROM TBL_ALICUOTA_MENSUAL WHERE PERIODO = AS_PERIODO ORDER BY ID_ALI_MEN, PERIODO, DOCUMENTO, EDAD, CUOTA ASC; BEGIN SELECT --SEQ_ID_ALI_MEN.NEXTVAL, -->ID_ALI_MEN NRO_ID, -->NRO_ID PERIODO, -->PERIODO NRO_DNI, -->DOCUMENTO NRO_EDAD, -->EDAD ROW_NUMBER() OVER (PARTITION BY PERIODO, NRO_DNI, NRO_EDAD ORDER BY NRO_EDAD ASC), -->CUOTA MTO_TRANS, -->TRANSFERENCIA ACU_MENSUAL, -->ALICUOTA CASE ROW_NUMBER() OVER (PARTITION BY PERIODO, NRO_DNI, NRO_EDAD ORDER BY NRO_EDAD ASC) WHEN 1 THEN MTO_TRANS - ACU_MENSUAL ELSE 0 END -->RESERVA BULK COLLECT INTO L_NRO_ID, L_PERIODO, L_DOCUMENTO, L_EDAD, L_CUOTA, L_TRANSFERENCIA, L_ALICUOTA, L_RESERVA FROM TBL_ALICUOTA, TABLE(generate_series(1,12)) WHERE PERIODO = AS_PERIODO; FORALL i IN 1..L_NRO_ID.COUNT INSERT INTO TBL_ALICUOTA_MENSUAL ( ID_ALI_MEN, NRO_ID, PERIODO, DOCUMENTO, EDAD, CUOTA, TRANSFERENCIA, ALICUOTA, RESERVA ) VALUES ( SEQ_ID_ALI_MEN.NEXTVAL, L_NRO_ID(i), L_PERIODO(i), L_DOCUMENTO(i), L_EDAD(i), L_CUOTA(i), L_TRANSFERENCIA(i), L_ALICUOTA(i), L_RESERVA(i) ); COMMIT; --------------------------------------------------------------------------------- --ACTUALIZAR RESERVA --------------------------------------------------------------------------------- FOR S IN CUR_RESERVA LOOP LS_DNI := S.DOCUMENTO; LN_EDAD := S.EDAD; IF S.CUOTA > 1 THEN SELECT DOCUMENTO, RESERVA BULK COLLECT INTO L_DOCUMENTOM, L_RESERVAM FROM TBL_ALICUOTA_MENSUAL WHERE PERIODO = AS_PERIODO AND DOCUMENTO = LS_DNI AND EDAD = LN_EDAD AND CUOTA = S.CUOTA - 1; FORALL r IN 1..L_DOCUMENTOM.COUNT UPDATE TBL_ALICUOTA_MENSUAL SET RESERVA = L_RESERVAM(r) - S.ALICUOTA WHERE PERIODO = AS_PERIODO AND DOCUMENTO = LS_DNI AND EDAD = LN_EDAD AND CUOTA = S.CUOTA; COMMIT; END IF; END LOOP; END;