/* CHEQUEO CXPAGAR CON RCREE */

SET TERM ^ ;

CREATE OR ALTER procedure CHEQUEO_SALDOS_DOC_CXPAGAR (
    PRVINI varchar(20),
    PRVFIN varchar(20))
returns (
    ERROR varchar(60),
    TIPO integer,
    ID integer,
    PREF varchar(4),
    NUMERO varchar(8))
AS
declare variable FECHA DATE;
declare variable NIT VARCHAR(20);
declare variable RTFTE NUMERIC(18, 2);
declare variable RTIVA NUMERIC(18, 2);
declare variable RTICA NUMERIC(18, 2);
declare variable RCREE NUMERIC(18, 2);
declare variable MONTO NUMERIC(18, 2);
declare variable EXISTE CHAR(1);
declare variable IDR INTEGER;
declare variable EGREID INTEGER;
declare variable TRM NUMERIC(18, 2);
declare variable INGPAS INTEGER;
declare variable CANT INTEGER;
declare variable IDT INTEGER;
BEGIN
DELETE FROM anticipos_proveedor WHERE EGRE_ID = 0 AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN;
DELETE FROM saldos_doc_cxpagar WHERE TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN;

FOR SELECT FACO_ID, FACO_FECHA, TERC_NIT, FACO_RTFTEMONTO, FACO_rtivamonto, FACO_rticamonto, FACO_RTCREEM, FACO_TOTAL, FACO_TRM
    FROM FACTURAS_COMPRA WHERE FACO_ID <> 0 and faco_anulado = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN
    ORDER BY FACO_FECHA
    INTO :ID, :FECHA, :nit,  :RTFTE, :RTIVA, :RTICA, :RCREE, MONTO, :TRM
    DO
    INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_RCREE, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_ABRCREE, SDCP_DEBITO, SDCP_MONTO, SDCP_TRM)
        VALUES (21, :ID, :fecha, :nit, :rtfte, :rtiva, :rtica, :RCREE, 0, 0, 0, 0, 0, 'S', :MONTO, :TRM);

FOR SELECT NDPR_ID, NDPR_FECHA, TERC_NIT, NDPR_RTFTEMONTO, NDPR_rtivamonto, NDPR_rticamonto, NDPR_RTCREEM, ndpr_monto+ndpr_ivamonto, NDPR_TRM
    FROM notas_debito_proveedor WHERE NDPR_ID <> 0 and NDPR_anulado = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN
    ORDER BY NDPR_FECHA
    INTO :ID, :FECHA, :nit,  :RTFTE, :RTIVA, :RTICA, :RCREE, :MONTO, :TRM
    DO
    INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_RCREE, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_ABRCREE, SDCP_DEBITO, SDCP_MONTO, SDCP_TRM)
        VALUES (51, :ID, :fecha, :nit, :rtfte, :rtiva, :rtica, :RCREE, 0, 0, 0, 0, 0, 'S', :MONTO, :TRM);

FOR SELECT MANC_ID, MANC_FECHA, TERC_NIT, 0, 0, 0, MANC_APAGAR
    FROM manifiesto_carga M, vehiculos V WHERE V.vehi_cod = manc_vehiculo AND MANC_ID <> 0 and MANC_anulado = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN and manc_apagar > 0
    ORDER BY MANC_FECHA
    INTO :ID, :FECHA, :nit,  :RTFTE, :RTIVA, :RTICA, :MONTO
    DO
    INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_RCREE, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_ABRCREE, SDCP_DEBITO, SDCP_MONTO)
        VALUES (89, :ID, :fecha, :nit, :rtfte, :rtiva, :rtica, :RCREE, 0, 0, 0, 0, 0, 'S', :MONTO);

FOR SELECT DVCO_ID, DVCO_FECHA, TERC_NIT, DVCO_RTFTEMONTO, DVCO_rtivamonto, DVCO_rticamonto, DVCO_rtCREEM, dvco_total, DVCO_TRM
    FROM devoluciones_compras WHERE DVCO_ID <> 0 and DVCO_anulado = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN
    ORDER BY DVCO_FECHA
    INTO :ID, :FECHA, :nit,  :RTFTE, :RTIVA, :RTICA, :RCREE, :MONTO, :TRM
    DO
    INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_RCREE, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_ABRCREE, SDCP_DEBITO, SDCP_MONTO, SDCP_TRM)
        VALUES (24, :ID, :fecha, :nit, :rtfte*-1, :rtiva*-1, :rtica*-1, :rcree*-1, 0, 0, 0, 0, 0, 'N', :MONTO * -1, :TRM);

FOR SELECT NCPR_ID, NCPR_FECHA, TERC_NIT, NCPR_RTFTEMONTO, NCPR_rtivamonto, NCPR_rticamonto, ncpr_rtcreem, ncpr_monto+ncpr_ivamonto, NCPR_TRM
    FROM notas_credito_proveedor WHERE NCPR_ID <> 0 and NCPR_anulado = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN
    ORDER BY NCPR_FECHA
    INTO :ID, :FECHA, :nit,  :RTFTE, :RTIVA, :RTICA, :RCREE, :MONTO, :TRM
    DO
    INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_RCREE, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_ABRCREE, SDCP_DEBITO, SDCP_MONTO, SDCP_TRM)
        VALUES (52, :ID, :fecha, :nit, :rtfte*-1, :rtiva*-1, :rtica*-1, :rcree*-1, 0, 0, 0, 0, 0, 'N', :MONTO * -1, :TRM);

FOR SELECT ANPR_ID, ANPR_FECHA, TERC_NIT, ANPR_RTFTEMONTO, ANPR_rtivamonto, ANPR_rticamonto, anpr_base+anpr_ivamonto, ANPR_TRM
    FROM anticipos_proveedor WHERE ANPR_ID <> 0 and ANPR_anulado = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN
    ORDER BY ANPR_FECHA
    INTO :ID, :FECHA, :nit,  :RTFTE, :RTIVA, :RTICA, :MONTO, :TRM
    DO
    INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_DEBITO, SDCP_MONTO, SDCP_TRM)
        VALUES (55, :ID, :fecha, :nit, :rtfte*-1, :rtiva*-1, :rtica*-1, 0, 0, 0, 0, 'N', :MONTO * -1, :TRM);

execute procedure LEE_configuracion('TRANSPORTE', 'GENERAL', 'TARIFA DE INGRESO POR PASAJES A CREDITO') returning_values (:ingpas);
FOR SELECT P.plpa_id, P.plpa_fecha, V.terc_nit, 0, 0, 0, D.plpa_valor, D.plpa_tiquete
    FROM planilla_pasajeros_det D, planilla_pasajeros P, VEHICULOS V
    WHERE P.plpa_id = D.plpa_id AND P.plpa_vehiculo = V.vehi_cod AND P.plpa_id <> 0 and p.PLPA_anulado = 'N' AND
    V.TERC_NIT >= :PRVINI AND V.TERC_NIT <= :PRVFIN
    ORDER BY P.plpa_fecha, P.plpa_id
    INTO :ID, :FECHA, :nit,  :RTFTE, :RTIVA, :RTICA, :MONTO, :idt
    DO
    BEGIN
    CANT = 0;
    SELECT TIQP_CANT FROM tiquete_pasajero WHERE TIQP_ID = :IDT and TIQP_FORMAP = 'R' INTO :CANT;
    if (CANT IS NULL) then
        CANT = 0;
    EXISTE = 'N';
    select 'S' from saldos_doc_cxpagar where sdcp_tiporef = 88 and
        sdcp_idref = :id and sdcp_fecha = :fecha into :EXISTE;
    if (Existe = 'S') then
        UPDATE saldos_doc_cxpagar
            SET SDCP_MONTO = SDCP_MONTO + (:monto-(:INGPAS*:cant))
            WHERE SDCP_TIPOREF = 88 AND SDCP_IDREF = :id AND SDCP_FECHA = :fecha;
    ELSE
        INSERT INTO SALDOS_DOC_CXPAGAR (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_DEBITO, SDCP_MONTO)
            VALUES (88, :id, :fecha, :nit, 0, 0, 0, 0, 0, 0, 0, 'S', (:monto-(:INGPAS*:CANT)));
    end

execute procedure LEE_configuracion('TRANSPORTE', 'GENERAL', 'TARIFA DE INGRESO POR REMESAS DE PASAJEROS') returning_values (:ingpas);
FOR SELECT P.plpa_id, P.plpa_fecha, V.terc_nit, 0, 0, 0, D.plpr_remesa
    FROM planilla_pasajeros_rem D, planilla_pasajeros P, VEHICULOS V WHERE P.plpa_id = D.plpa_id AND P.plpa_vehiculo = V.vehi_cod AND P.plpa_id <> 0 and P.PLPA_anulado = 'N' AND V.TERC_NIT >= :PRVINI AND V.TERC_NIT <= :PRVFIN
    ORDER BY P.plpa_fecha, P.plpa_id
    INTO :ID, :FECHA, :nit,  :RTFTE, :RTIVA, :RTICA, :idr
    DO
    BEGIN
    SELECT CGRM_TOTAL-CGRM_DOMICILIO-CGRM_SEGURO FROM carga_remesa WHERE CGRM_ID = :IDR INTO :MONTO;
    execute procedure redondee(MONTO * (100-ingpas) / 100, 0) returning_values (MONTO);
    if (MONTO <> 0) then
        BEGIN
        EXISTE = 'N';
        select 'S' from saldos_doc_cxpagar where sdcp_tiporef = 88 and
            sdcp_idref = :id and sdcp_fecha = :fecha into :EXISTE;
        if (Existe = 'S') then
            UPDATE saldos_doc_cxpagar
                SET SDCP_MONTO = SDCP_MONTO + :monto
                WHERE SDCP_TIPOREF = 88 AND SDCP_IDREF = :id AND SDCP_FECHA = :fecha;
        ELSE
            INSERT INTO SALDOS_DOC_CXPAGAR (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_DEBITO, SDCP_MONTO)
                VALUES (88, :id, :fecha, :nit, 0, 0, 0, 0, 0, 0, 0, 'S', :monto);
        end
    end

FOR SELECT APPD_TIPODOC, APPD_IDDOC, APPR_FECHA, TERC_NIT, APPD_APLICADO, APPD_RTFTE, APPD_RTIVA, APPD_RTICA, APPD_RCREE, APPR_TRM
    FROM APLICACION_PROVEEDOR_DETALLE D, aplicacion_provedor A
    WHERE A.APPR_ID = D.appr_id AND A.APPR_ID <> 0 AND APPR_ANULADO = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN
    ORDER BY APPR_FECHA
    INTO :TIPO, :ID, :FECHA, :NIT, :MONTO, :RTFTE, :RTIVA, :RTICA, :RCREE, :TRM
    DO
    BEGIN
    EXISTE = 'N';
    select 'S' from saldos_doc_cxpagar where sdcp_tiporef = :TIPO and sdcp_idref = :ID and sdcp_fecha = :fecha into :EXISTE;
    if (Existe = 'S') then
        begin
        /* actualice el saldo del dia */
        UPDATE saldos_doc_cxpagar
            SET SDCP_ABONO = SDCP_ABONO + :MONTO,
                SDCP_ABRTFTE = SDCP_ABRTFTE + :RTFTE,
                SDCP_ABRTIVA = SDCP_ABRTIVA + :RTIVA,
                SDCP_ABRTICA = SDCP_ABRTICA + :RTICA,
                SDCP_ABRCREE = SDCP_ABRCREE + :RCREE,
                SDCP_TRM = :TRM
            WHERE SDCP_TIPOREF = :TIPO AND SDCP_IDREF = :ID AND SDCP_FECHA = :fecha;
        END
    ELSE
        BEGIN
        /* Inserte un nuevo saldo diario */
        INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_RCREE, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_ABRCREE, SDCP_DEBITO, SDCP_MONTO, SDCP_TRM)
            VALUES (:TIPO, :ID, :fecha, :NIT, 0, 0, 0, 0, :MONTO, :rtfte, :rtiva, :rtica, :RCREE, 'N', 0, :TRM);
        END
    END

FOR SELECT E.EGRE_ID, EGDE_TIPODOC, EGDE_IDDOC, EGRE_FECHA, TERC_NIT, EGDE_ABONO+EGDE_DTOF, EGDE_RTFTE, EGDE_RTIVA, EGDE_RTICA, EGDE_RCREE, EGRE_TRM
    FROM EGRESOS_DETALLE D, EGRESOS E
    WHERE E.EGRE_ID = D.EGRE_ID AND E.EGRE_ID <> 0 AND EGRE_ANULADO = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN
    ORDER BY EGRE_FECHA
    INTO :IDR, :TIPO, :ID, :FECHA, :NIT, :MONTO, :RTFTE, :RTIVA, :RTICA, :RCREE, :TRM
    DO
    BEGIN
    EXISTE = 'N';
    if (TIPO = 55) then
        BEGIN
        SELECT EGRE_ID FROM anticipos_proveedor WHERE ANPR_ID = :ID INTO :EGREID;
        if (EGREID = IDR) then
            EXISTE = 'S';
        END
    if (EXISTE = 'N') then
        BEGIN
        select 'S' from saldos_doc_cxpagar where sdcp_tiporef = :TIPO and sdcp_idref = :ID and sdcp_fecha = :fecha into :EXISTE;
        if (Existe = 'S') then
            begin
            /* actualice el saldo del dia */
            UPDATE saldos_doc_cxpagar
                SET SDCP_ABONO = SDCP_ABONO + :MONTO,
                    SDCP_ABRTFTE = SDCP_ABRTFTE + :RTFTE,
                    SDCP_ABRTIVA = SDCP_ABRTIVA + :RTIVA,
                    SDCP_ABRTICA = SDCP_ABRTICA + :RTICA,
                    SDCP_ABRCREE = SDCP_ABRCREE + :RCREE,
                    SDCP_TRM = :TRM
                WHERE SDCP_TIPOREF = :TIPO AND SDCP_IDREF = :ID AND SDCP_FECHA = :fecha;
            END
        ELSE
            BEGIN
            /* Inserte un nuevo saldo diario */
            INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_RCREE, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_ABRCREE, SDCP_DEBITO, SDCP_MONTO, SDCP_TRM)
                VALUES (:TIPO, :ID, :fecha, :NIT, 0, 0, 0, 0, :MONTO, :rtfte, :rtiva, :rtica, :RCREE, 'N', 0, :TRM);
            END
        END
    END

FOR SELECT R.reca_id, D.rccr_tipodoc, D.rccr_iddoc, RECA_FECHA, TERC_NIT, D.rccr_abono+D.rccr_dtof, D.rccr_rtfte, D.rccr_rtiva, D.rccr_rtica, D.rccr_rcree, RECA_TRM
    FROM recibos_caja_crucec D, recibos_caja R
    WHERE R.reca_id = D.reca_id AND R.reca_id <> 0 AND RECA_ANULADO = 'N' AND TERC_NIT >= :PRVINI AND TERC_NIT <= :PRVFIN
    ORDER BY RECA_FECHA
    INTO :IDR, :TIPO, :ID, :FECHA, :NIT, :MONTO, :RTFTE, :RTIVA, :RTICA, :RCREE, :TRM
    DO
    BEGIN
    EXISTE = 'N';
    select 'S' from saldos_doc_cxpagar where sdcp_tiporef = :TIPO and sdcp_idref = :ID and sdcp_fecha = :fecha into :EXISTE;
    if (Existe = 'S') then
        begin
        /* actualice el saldo del dia */
        UPDATE saldos_doc_cxpagar
            SET SDCP_ABONO = SDCP_ABONO + :MONTO,
            SDCP_ABRTFTE = SDCP_ABRTFTE + :RTFTE,
            SDCP_ABRTIVA = SDCP_ABRTIVA + :RTIVA,
            SDCP_ABRTICA = SDCP_ABRTICA + :RTICA,
            SDCP_ABRCREE = SDCP_ABRCREE + :RCREE,
            SDCP_TRM = :TRM
            WHERE SDCP_TIPOREF = :TIPO AND SDCP_IDREF = :ID AND SDCP_FECHA = :fecha;
        END
    ELSE
        BEGIN
        /* Inserte un nuevo saldo diario */
        INSERT INTO saldos_doc_cxpagar (SDCP_TIPOREF, SDCP_IDREF, SDCP_FECHA, TERC_NIT, SDCP_RTEFTE, SDCP_RTIVA, SDCP_RTICA, SDCP_RCREE, SDCP_ABONO, SDCP_ABRTFTE, SDCP_ABRTIVA, SDCP_ABRTICA, SDCP_ABRCREE, SDCP_DEBITO, SDCP_MONTO, SDCP_TRM)
            VALUES (:TIPO, :ID, :fecha, :NIT, 0, 0, 0, 0, :MONTO, :rtfte, :rtiva, :rtica, :RCREE, 'N', 0, :TRM);
        END
    END

for select anpr_id, anpr_base+anpr_ivamonto, PREF_PRE, ANPR_NUMERO, ANPR_TRM from anticipos_proveedor
    where anpr_anulado = 'N' and egre_id not in (select egre_id from egresos where egre_anulado = 'N')
    into :ID, :MONTO, :PREF, :NUMERO, :TRM
    do
    begin
    FECHA = cast('now' as date);
/*    execute procedure saldo_doc_cxpagar(55, :id, :FECHA) returning_values (:RTFTE);*/
    if ((MONTO = RTFTE) or (RTFTE IS NULL)) then
        DELETE FROM anticipos_proveedor WHERE ANPR_ID = :ID;
    ELSE
        BEGIN
        TIPO = 55;
        ERROR = 'DOCUMENTO ANULADO CON ABONOS';
        SUSPEND;
        END
    end
END^

SET TERM ; ^



