/* SI VARIOS ABONOS EN EL DIA REPETIA EL VALOR TOTAL DE LA FACTURA */

SET TERM ^ ;



CREATE OR ALTER procedure CIERRE_FORMAPAGO (

    FECHA date,

    PUNTO integer,

    PREFIJO varchar(4),

    FP integer)

returns (

    CANT integer,

    TOTAL numeric(18,2))

AS

declare variable IDR INTEGER;

declare variable IDF INTEGER;

declare variable I INTEGER;

declare variable TOTALF NUMERIC(18,2);

declare variable TOTALR NUMERIC(18,2);

declare variable MONTO NUMERIC(18,2);

begin

CANT = 0;

TOTAL = 0;

FOR SELECT fact_id FROM FACTURAS F

    where FACT_FECHA = :FECHA AND ((F.PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'N' AND ptvt_id = :punto 

    into :IDF

    do

    begin

    for select R.RECA_ID, R.RECA_MONTO, D.rcde_abono FROM RECIBOS_CAJA R, RECIBOS_CAJA_DETALLE D

        WHERE R.RECA_ID = D.RECA_ID AND RECA_FECHA = :FECHA AND RECA_ANULADO = 'N' AND D.RCDE_TIPODOC = 31 AND D.RCDE_IDDOC = :IDF

        INTO :IDR, :TOTALR, :totalf

        DO

        BEGIN

        SELECT count(RCPA_ITEM), SUM(RCPA_MONTO) FROM RECIBOS_CAJA_PAGO P

            WHERE RECA_ID = :IDR AND FOPA_ID = :FP INTO :I, :MONTO;

        if (CANT IS NULL) then

            CANT = 0;

        CANT = CANT + I;

        if (MONTO IS NULL) then

            MONTO = 0;

        TOTAL = TOTAL + ROUND(MONTO * (TOTALF / TOTALR),2);

        END

    END

if (TOTAL is null) then

    TOTAL = 0;

suspend;

end

^



SET TERM ; ^



SET TERM ^ ;



ALTER PROCEDURE CIERRE_FACTURACION(

    FECHA DATE,

    PUNTO INTEGER,

    PREFIJO VARCHAR(4))

RETURNS (

    PREF VARCHAR(4),

    AUTORIZA VARCHAR(20),

    PUNTOVTA VARCHAR(60),

    NUMINI VARCHAR(8),

    NUMFIN VARCHAR(8),

    NROFAC INTEGER,

    HORAINI TIME,

    HORAFIN TIME,

    BASEFAC NUMERIC(18,2),

    IVAFAC NUMERIC(18,2),

    DEC2799F NUMERIC(18,2),

    TOTALFAC NUMERIC(18,2),

    NROITE INTEGER,

    NRODEV INTEGER,

    BASEDEV NUMERIC(18,2),

    IVADEV NUMERIC(18,2),

    DEC2799D NUMERIC(18,2),

    TOTALDEV NUMERIC(18,2),

    BASENETO NUMERIC(18,2),

    IVANETO NUMERIC(18,2),

    DEC2799N NUMERIC(18,2),

    TOTALNETO NUMERIC(18,2),

    BASEF0 NUMERIC(18,2),

    IVAF0 NUMERIC(18,2),

    DEC2799F0 NUMERIC(18,2),

    TOTALF0 NUMERIC(18,2),

    BASED0 NUMERIC(18,2),

    IVAD0 NUMERIC(18,2),

    DEC2799D0 NUMERIC(18,2),

    TOTALD0 NUMERIC(18,2),

    BASEF1 NUMERIC(18,2),

    IVAF1 NUMERIC(18,2),

    DEC2799F1 NUMERIC(18,2),

    TOTALF1 NUMERIC(18,2),

    BASED1 NUMERIC(18,2),

    IVAD1 NUMERIC(18,2),

    DEC2799D1 NUMERIC(18,2),

    TOTALD1 NUMERIC(18,2),

    BASEF2 NUMERIC(18,2),

    IVAF2 NUMERIC(18,2),

    DEC2799F2 NUMERIC(18,2),

    TOTALF2 NUMERIC(18,2),

    BASED2 NUMERIC(18,2),

    IVAD2 NUMERIC(18,2),

    DEC2799D2 NUMERIC(18,2),

    TOTALD2 NUMERIC(18,2),

    BASEF3 NUMERIC(18,2),

    IVAF3 NUMERIC(18,2),

    DEC2799F3 NUMERIC(18,2),

    TOTALF3 NUMERIC(18,2),

    BASED3 NUMERIC(18,2),

    IVAD3 NUMERIC(18,2),

    DEC2799D3 NUMERIC(18,2),

    TOTALD3 NUMERIC(18,2),

    BASEF4 NUMERIC(18,2),

    IVAF4 NUMERIC(18,2),

    DEC2799F4 NUMERIC(18,2),

    TOTALF4 NUMERIC(18,2),

    BASED4 NUMERIC(18,2),

    IVAD4 NUMERIC(18,2),

    DEC2799D4 NUMERIC(18,2),

    TOTALD4 NUMERIC(18,2),

    FORMAP1 NUMERIC(18,2),

    FORMAP2 NUMERIC(18,2),

    FORMAP3 NUMERIC(18,2),

    FORMAP4 NUMERIC(18,2),

    FORMAP5 NUMERIC(18,2),

    FORMAP6 NUMERIC(18,2),

    FORMAP7 NUMERIC(18,2),

    FORMAP8 NUMERIC(18,2),

    FORMAP9 NUMERIC(18,2),

    FORMAP10 NUMERIC(18,2),

    FORMAP11 NUMERIC(18,2),

    FORMAP12 NUMERIC(18,2),

    FORMAP13 NUMERIC(18,2),

    FORMAP14 NUMERIC(18,2),

    FORMAP15 NUMERIC(18,2),

    CANTFP1 INTEGER,

    CANTFP2 INTEGER,

    CANTFP3 INTEGER,

    CANTFP4 INTEGER,

    CANTFP5 INTEGER,

    CANTFP6 INTEGER,

    CANTFP7 INTEGER,

    CANTFP8 INTEGER,

    CANTFP9 INTEGER,

    CANTFP10 INTEGER,

    CANTFP11 INTEGER,

    CANTFP12 INTEGER,

    CANTFP13 INTEGER,

    CANTFP14 INTEGER,

    CANTFP15 INTEGER,

    CARTERA NUMERIC(18,2),

    RETES NUMERIC(18,2),

    VENDEDOR1 VARCHAR(60),

    VTAVEND1 NUMERIC(18,2),

    VENDEDOR2 VARCHAR(60),

    VTAVEND2 NUMERIC(18,2),

    VENDEDOR3 VARCHAR(60),

    VTAVEND3 NUMERIC(18,2),

    VENDEDOR4 VARCHAR(60),

    VTAVEND4 NUMERIC(18,2),

    VENDEDOR5 VARCHAR(60),

    VTAVEND5 NUMERIC(18,2),

    NROFACTANUL INTEGER,

    IVANUL NUMERIC(18,2),

    TOTALFACANUL NUMERIC(18,2))

AS

declare variable tarifa numeric(9,2);

declare variable basef numeric(18,2);

declare variable ivaf numeric(18,2);

declare variable dec2799af numeric(18,2);

declare variable totalf numeric(18,2);

declare variable basedv numeric(18,2);

declare variable ivad numeric(18,2);

declare variable dec2799ad numeric(18,2);

declare variable totald numeric(18,2);

declare variable i integer;

declare variable codven integer;

declare variable vended varchar(60);

declare variable f_id integer;

declare variable f_total numeric(18,2);

declare variable factor numeric(18,4);

declare variable ajuste integer;

begin

AJUSTE = 0;

if (PREFIJO <> '') then

  SELECT PREF_PRE, AUTO_NUMERO FROM AUTORIZACIONES WHERE PREF_PRE = :PREFIJO INTO :PREF, :autoriza;

SELECT PTVT_NOM FROM PUNTO_VENTA WHERE PTVT_ID = :punto INTO :puntovta;

  /* LAS VENTAS */

  SELECT MIN(FACT_NUMERO), MAX(FACT_NUMERO), COUNT(FACT_NUMERO), SUM(FACT_TOTAL - FACT_IVAMONTO + FACT_DEC2799), SUM(FACT_IVAMONTO), SUM(FACT_TOTAL), sum(fact_dec2799)

    FROM FACTURAS WHERE FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'N'

    INTO :NUMINI, :NUMFIN, :NROFAC, :BASENETO, :IVANETO, :TOTALNETO, :dec2799f;

  if (dec2799f is null) then

    dec2799f = 0;

  SELECT MIN(AUDI_HORA) FROM AUDITORIA WHERE TIDO_COD = 31 AND AUDI_COD = :PREFIJO || :NUMINI AND AUDI_OPER = 'I' INTO :HORAINI;

  SELECT MAX(AUDI_HORA) FROM AUDITORIA WHERE TIDO_COD = 31 AND AUDI_COD = :PREFIJO || :NUMFIN AND AUDI_OPER = 'I' INTO :HORAFIN;

  SELECT COUNT(FADE_ITEM), SUM(FADE_TOTAL-FADE_IVAMONTO), SUM(FADE_IVAMONTO), SUM(FADE_TOTAL)

    FROM facturas_detalle D, FACTURAS F

    WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT > 0 and FACT_ANULADO = 'N' and substring(d.arti_cod from 1 FOR 2) <> '.t'

    INTO :nroite, :BASEFAC, :IVAFAC, :TOTALFAC;

  totalfac = totalfac - dec2799f;

/*  ivafac = ivafac - dec2799f; */

  SELECT COUNT(FADE_ITEM), SUM(FADE_IVAMONTO-FADE_TOTAL), SUM(FADE_IVAMONTO*-1), SUM(FADE_TOTAL*-1)

    FROM facturas_detalle D, FACTURAS F

    WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT < 0 and FACT_ANULADO = 'N' and substring(d.arti_cod from 1 FOR 2) <> '.t'

    INTO :NRODEV, :BASEDEV, :IVADEV, :TOTALDEV;

  SELECT COUNT(DVDE_ITEM), SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL)

    FROM devoluciones_ventas_detalle D, devoluciones_ventas F

    WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DEVT_ANULADO = 'N' and substring(d.arti_cod from 1 FOR 2) <> '.t'

    INTO :I, :BASEDV, :IVAD, :TOTALD;

  select sum(devt_dec2799) FROM devoluciones_ventas WHERE DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DEVT_ANULADO = 'N' into :dec2799d;

  if (dec2799d is null) then

    dec2799d = 0;

  totald = totald - dec2799d;

/*  ivad = ivad - dec2799; */

  DEC2799N = DEC2799F - DEC2799D;

  SELECT COUNT(FACT_NUMERO),SUM(FACT_TOTAL - FACT_IVAMONTO), SUM(FACT_IVAMONTO) FROM FACTURAS

    WHERE FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'S'

    INTO :NROFACTANUL, :TOTALFACANUL, :IVANUL;

  if (nrofac is null) then

    nrofac = 0;

  if (NROFACTANUL is null) then

    NROFACTANUL = 0;

  if (TOTALFACANUL is null) then

    TOTALFACANUL = 0;

  if (IVANUL is null) then

    IVANUL = 0;

  if (baseneto is null) then

    baseneto = 0;

  if (ivaneto is null) then

    ivaneto = 0;

  if (totalneto is null) then

    totalneto = 0;

  if (nroite is null) then

    nroite = 0;

  if (basefac is null) then

    basefac = 0;

  if (ivafac is null) then

    ivafac = 0;

  if (totalfac is null) then

    totalfac = 0;

  if (nrodev is null) then

    nrodev = 0;

  if (basedev is null) then

    basedev = 0;

  if (ivadev is null) then

    ivadev = 0;

  if (totaldev is null) then

    totaldev = 0;

  if (I is null) then

    I = 0;

  if (basedv is null) then

    basedv = 0;

  if (ivad is null) then

    ivad = 0;

  if (totald is null) then

    totald = 0;

  NRODEV = NRODEV + I;

  BASENETO = BASENETO - BASEDV;

  IVANETO = IVANETO - IVAD;

  TOTALNETO = TOTALNETO - TOTALD;



  BASEDEV = BASEDEV + BASEDV;

  IVADEV = IVADEV + IVAD;

  TOTALDEV = TOTALDEV + TOTALD;



  /* POR TARIFA DE IVA */

  I = 0;

  BASEF0 = 0;

  BASED0 = 0;

  IVAF0 = 0;

  IVAD0 = 0;

  DEC2799F0 = 0;

  DEC2799D0 = 0;

  TOTALF0 = 0;

  TOTALD0 = 0;

  BASEF1 = 0;

  BASED1 = 0;

  IVAF1 = 0;

  IVAD1 = 0;

  DEC2799F1 = 0;

  DEC2799D1 = 0;

  TOTALF1 = 0;

  TOTALD1 = 0;

  BASEF2 = 0;

  BASED2 = 0;

  IVAF2 = 0;

  IVAD2 = 0;

  DEC2799F2 = 0;

  DEC2799D2 = 0;

  TOTALF2 = 0;

  TOTALD2 = 0;

  BASEF3 = 0;

  BASED3 = 0;

  IVAF3 = 0;

  IVAD3 = 0;

  DEC2799F3 = 0;

  DEC2799D3 = 0;

  TOTALF3 = 0;

  TOTALD3 = 0;

  BASEF4 = 0;

  BASED4 = 0;

  IVAF4 = 0;

  IVAD4 = 0;

  DEC2799F4 = 0;

  DEC2799D4 = 0;

  TOTALF4 = 0;

  TOTALD4 = 0;

  vtavend1 = 0;

  vtavend2 = 0;

  vtavend3 = 0;

  vtavend4 = 0;

  vtavend5 = 0;

  FOR SELECT TAIV_PORC FROM TARIFA_IVA ORDER BY TAIV_PORC INTO :TARIFA DO

    BEGIN

    I = I + 1;

    SELECT SUM(FADE_TOTAL-FADE_IVAMONTO), SUM(FADE_IVAMONTO), SUM(FADE_TOTAL) FROM facturas_detalle D, FACTURAS F

        WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT > 0 AND FADE_IVAPORC = :TARIFA and FACT_ANULADO = 'N'

        INTO :BASEF, :IVAF, :TOTALF;

    SELECT SUM(FADE_TOTAL-FADE_IVAMONTO), SUM(FADE_IVAMONTO), SUM(FADE_TOTAL) FROM facturas_detalle D, FACTURAS F

        WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT < 0 AND FADE_IVAPORC = :TARIFA and FACT_ANULADO = 'N'

        INTO :BASEDV, :IVAD, :TOTALD;

    if (basef is null) then

        basef = 0;

    if (ivaf is null) then

        ivaf = 0;

    if (totalf is null) then

        totalf = 0;

    if (basedv is null) then

        basedv = 0;

    if (ivad is null) then

        ivad = 0;

    if (totald is null) then

        totald = 0;

    SELECT SUM(FADE_IVAMONTO) FROM ARTICULO A, facturas_detalle D, FACTURAS F

        WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT > 0 AND FADE_IVAPORC = :TARIFA and FACT_ANULADO = 'N'

        AND D.ARTI_COD = A.ARTI_COD AND ((FADE_REFERENCIA <> '.t') or (FADE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

        INTO DEC2799af;

    if (dec2799af is null) then

        dec2799af = 0;

    SELECT SUM(FADE_IVAMONTO) FROM ARTICULO A, facturas_detalle D, FACTURAS F

        WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT < 0 AND FADE_IVAPORC = :TARIFA and FACT_ANULADO = 'N'

        AND D.ARTI_COD = A.ARTI_COD AND ((FADE_REFERENCIA <> '.t') or (FADE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

        INTO DEC2799ad;

    if (dec2799ad is null) then

        dec2799ad = 0;

    FACTOR = 1;

    SELECT AJCI_FACTOR FROM ajuste_cierre WHERE AJCI_PORC = :TARIFA AND AJCI_FECINI <= :FECHA AND AJCI_FECFIN >= :FECHA INTO :FACTOR;

    if (FACTOR IS null) then

        FACTOR = 1;

    if (FACTOR <> 1) then

        AJUSTE = 1;

    BASEF = BASEF * FACTOR;

    BASEDV = BASEDV * FACTOR;

    IVAF = IVAF * FACTOR;

    IVAD = IVAD * FACTOR;

    TOTALF = TOTALF * FACTOR;

    TOTALD = TOTALD * FACTOR;

    DEC2799af = DEC2799af * FACTOR;

    DEC2799ad = DEC2799ad * FACTOR;



    if (I = 1) then

        BEGIN

        BASEF0 = BASEF;

        BASED0 = :BASEDV;

        IVAF0 = IVAF;

        IVAD0 = IVAD;

        DEC2799F0 = DEC2799AF;

        DEC2799D0 = DEC2799AD;

        TOTALF0 = TOTALF;

        TOTALD0 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D0 = DEC2799D0 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED0 = BASED0 - BASEDV;

        IVAD0 = IVAD0 - IVAD;

        TOTALD0 = TOTALD0 - TOTALD;

        END

    if (I = 2) then

        BEGIN

        BASEF1 = BASEF;

        BASED1 = :BASEDV;

        IVAF1 = IVAF;

        IVAD1 = IVAD;

        DEC2799F1 = DEC2799AF;

        DEC2799D1 = DEC2799AD;

        TOTALF1 = TOTALF;

        TOTALD1 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D1 = DEC2799D1 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED1 = BASED1 - BASEDV;

        IVAD1 = IVAD1 - IVAD;

        TOTALD1 = TOTALD1 - TOTALD;

        END

    if (I = 3) then

        BEGIN

        BASEF2 = BASEF;

        BASED2 = :BASEDV;

        IVAF2 = IVAF;

        IVAD2 = IVAD;

        DEC2799F2 = DEC2799AF;

        DEC2799D2 = DEC2799AD;

        TOTALF2 = TOTALF;

        TOTALD2 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D2 = DEC2799D2 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED2 = BASED2 - BASEDV;

        IVAD2 = IVAD2 - IVAD;

        TOTALD2 = TOTALD2 - TOTALD;

        END

    if (I = 4) then

        BEGIN

        BASEF3 = BASEF;

        BASED3 = :BASEDV;

        IVAF3 = IVAF;

        IVAD3 = IVAD;

        DEC2799F3 = DEC2799AF;

        DEC2799D3 = DEC2799AD;

        TOTALF3 = TOTALF;

        TOTALD3 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D3 = DEC2799D3 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED3 = BASED3 - BASEDV;

        IVAD3 = IVAD3 - IVAD;

        TOTALD3 = TOTALD3 - TOTALD;

        END

    if (I = 5) then

        BEGIN

        BASEF4 = BASEF;

        BASED4 = :BASEDV;

        IVAF4 = IVAF;

        IVAD4 = IVAD;

        DEC2799F4 = DEC2799AF;

        DEC2799D4 = DEC2799AD;

        TOTALF4 = TOTALF;

        TOTALD4 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D4 = DEC2799D4 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED4 = BASED4 - BASEDV;

        IVAD4 = IVAD4 - IVAD;

        TOTALD4 = TOTALD4 - TOTALD;

        END

    END

  /* POR FORMA DE PAGO */

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 1) returning_values (CANTFP1, FORMAP1);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 2) returning_values (CANTFP2, FORMAP2);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 3) returning_values (CANTFP3, FORMAP3);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 4) returning_values (CANTFP4, FORMAP4);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 5) returning_values (CANTFP5, FORMAP5);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 6) returning_values (CANTFP6, FORMAP6);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 7) returning_values (CANTFP7, FORMAP7);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 8) returning_values (CANTFP8, FORMAP8);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 9) returning_values (CANTFP9, FORMAP9);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 10) returning_values (CANTFP10, FORMAP10);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 11) returning_values (CANTFP11, FORMAP11);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 12) returning_values (CANTFP12, FORMAP12);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 13) returning_values (CANTFP13, FORMAP13);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 14) returning_values (CANTFP14, FORMAP14);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 15) returning_values (CANTFP15, FORMAP15);

  cartera = 0;

  for SELECT FACT_ID FROM FACTURAS WHERE FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'N'

    into :f_id

    do

    begin

    EXECUTE procedure saldo_doc_cartera(31, :f_id, :fecha,  0) returning_values (f_total);

    CARTERA = :cartera + :F_TOTAL;

    end

  for SELECT DEVT_ID FROM devoluciones_ventas WHERE DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO and DEVT_ANULADO = 'N'

    into :f_id

    do

    begin

    EXECUTE procedure saldo_doc_cartera(33, :f_id, :FECHA, 0) returning_values (f_total);

    CARTERA = :cartera - :F_TOTAL;

    end

  /* LAS RETENCIONES */

  SELECT SUM(RCDE_RTFTE+RCDE_RTIVA+RCDE_RTICA) FROM recibos_caja_detalle D, RECIBOS_CAJA R, PUNTO_VENTA PT

    WHERE R.RECA_ID = D.RECA_ID AND PT.PTVT_ID = :punto AND RECA_FECHA = :FECHA AND PT.caja_id = R.caja_id AND ((PT.PREF_PRE = R.PREF_PRE) or (:PREFIJO = '')) AND RECA_ANULADO = 'N'

    INTO :RETES;

  if (RETES IS NULL) then

    RETES = 0;



  if (cartera is null) then

    cartera = 0;

  I = 0;

  FOR SELECT MAX(F.VEND_COD), MAX(VEND_NOMBRE), SUM(FACT_TOTAL - FACT_IVAMONTO) FROM FACTURAS F, VENDEDORES V

    WHERE F.VEND_COD = V.VEND_COD AND FACT_FECHA = :FECHA AND F.PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'N'

    GROUP BY F.VEND_COD ORDER BY F.VEND_COD

    INTO :CODVEN, :VENDED, :TOTALF

    DO

    BEGIN

    if (totalf IS NULL) then

        TOTALF = 0;

    SELECT SUM(DEVT_TOTAL - DEVT_IVAMONTO) FROM DEVOLUCIONES_VENTAS

        WHERE VEND_COD = :CODVEN AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and DEVT_ANULADO = 'N'

        INTO :totald;

    if (TOTALD IS NULL) then

        TOTALD = 0;

    if (I = 0) then

        BEGIN

        vendedor1 = VENDED;

        vtavend1 = TOTALF - TOTALD;

        END

    if (I = 1) then

        BEGIN

        vendedor2 = VENDED;

        vtavend2 = TOTALF - TOTALD;

        END

    if (I = 2) then

        BEGIN

        vendedor3 = VENDED;

        vtavend3 = TOTALF - TOTALD;

        END

    if (I = 3) then

        BEGIN

        vendedor4 = VENDED;

        vtavend4 = TOTALF - TOTALD;

        END

    if (I = 4) then

        BEGIN

        vendedor5 = VENDED;

        vtavend5 = TOTALF - TOTALD;

        END

    I = I + 1;

    END

  if (AJUSTE = 1) then

    BEGIN

    BASEFAC = BASEF0 + BASEF1 + BASEF2 + BASEF3 + BASEF4;

    IVAFAC = IVAF0 + IVAF1 + IVAF2 + IVAF3 + IVAF4;

    TOTALFAC = totalf0 + TOTALF1 + TOTALF2 + TOTALF3 + TOTALF4;

    BASEDEV = totald0 + TOTALD1 + TOTALD2 + TOTALD3 + TOTALD4;

    IVADEV = IVAD0 + IVAD1 + IVAD2 + IVAD3 + IVAD4;

    TOTALDEV = totald0 + TOTALD1 + totald2 + totald3 + totald4;

    BASENETO = basefac + basedev;

    IVANETO = ivafac + ivadev;

    if (totalneto <> 0) Then

        begin

        formap1 = formap1 * (totalfac + totaldev) / totalneto;

        formap2 = formap2 * (totalfac + totaldev) / totalneto;

        formap3 = formap3 * (totalfac + totaldev) / totalneto;

        formap4 = formap4 * (totalfac + totaldev) / totalneto;

        formap5 = formap5 * (totalfac + totaldev) / totalneto;

        formap6 = formap6 * (totalfac + totaldev) / totalneto;

        formap7 = formap7 * (totalfac + totaldev) / totalneto;

        formap8 = formap8 * (totalfac + totaldev) / totalneto;

        formap9 = formap9 * (totalfac + totaldev) / totalneto;

        formap10 = formap10 * (totalfac + totaldev) / totalneto;

        formap11 = formap11 * (totalfac + totaldev) / totalneto;

        formap12 = formap12 * (totalfac + totaldev) / totalneto;

        formap13 = formap13 * (totalfac + totaldev) / totalneto;

        formap14 = formap14 * (totalfac + totaldev) / totalneto;

        formap15 = formap15 * (totalfac + totaldev) / totalneto;

        vtavend1 = vtavend1 * (totalfac + totaldev) / totalneto;

        vtavend2 = vtavend2 * (totalfac + totaldev) / totalneto;

        vtavend3 = vtavend3 * (totalfac + totaldev) / totalneto;

        vtavend4 = vtavend4 * (totalfac + totaldev) / totalneto;

        vtavend5 = vtavend5 * (totalfac + totaldev) / totalneto;

        end

    TOTALNETO = totalfac + totaldev;

    END

  INSERT INTO AUDITORIA(AUDI_USUARIO, TIDO_COD, AUDI_IDDOC, AUDI_OPER, AUDI_COD)

    VALUES (USER, 123, :punto, 'I', (:formap1+:formap2+:formap3+:formap4+:formap5+:CARTERA));

  suspend;

end



^



SET TERM ; ^



/* CIERRE DIARIO CON I.NAL.CONSUMO */

SET TERM ^ ;



CREATE OR ALTER procedure CIERRE_FACTURACION (

    FECHA date,

    PUNTO integer,

    PREFIJO varchar(4))

returns (

    PREF varchar(4),

    AUTORIZA varchar(20),

    PUNTOVTA varchar(60),

    NUMINI varchar(8),

    NUMFIN varchar(8),

    NROFAC integer,

    HORAINI time,

    HORAFIN time,

    BASEFAC numeric(18,2),

    IVAFAC numeric(18,2),

    INALCF numeric(18,2),

    DEC2799F numeric(18,2),

    TOTALFAC numeric(18,2),

    NROITE integer,

    NRODEV integer,

    BASEDEV numeric(18,2),

    IVADEV numeric(18,2),

    INALCD numeric(18,2),

    DEC2799D numeric(18,2),

    TOTALDEV numeric(18,2),

    BASENETO numeric(18,2),

    IVANETO numeric(18,2),

    INALCN numeric(18,2),

    DEC2799N numeric(18,2),

    TOTALNETO numeric(18,2),

    BASEF0 numeric(18,2),

    IVAF0 numeric(18,2),

    DEC2799F0 numeric(18,2),

    TOTALF0 numeric(18,2),

    BASED0 numeric(18,2),

    IVAD0 numeric(18,2),

    DEC2799D0 numeric(18,2),

    TOTALD0 numeric(18,2),

    BASEF1 numeric(18,2),

    IVAF1 numeric(18,2),

    DEC2799F1 numeric(18,2),

    TOTALF1 numeric(18,2),

    BASED1 numeric(18,2),

    IVAD1 numeric(18,2),

    DEC2799D1 numeric(18,2),

    TOTALD1 numeric(18,2),

    BASEF2 numeric(18,2),

    IVAF2 numeric(18,2),

    DEC2799F2 numeric(18,2),

    TOTALF2 numeric(18,2),

    BASED2 numeric(18,2),

    IVAD2 numeric(18,2),

    DEC2799D2 numeric(18,2),

    TOTALD2 numeric(18,2),

    BASEF3 numeric(18,2),

    IVAF3 numeric(18,2),

    DEC2799F3 numeric(18,2),

    TOTALF3 numeric(18,2),

    BASED3 numeric(18,2),

    IVAD3 numeric(18,2),

    DEC2799D3 numeric(18,2),

    TOTALD3 numeric(18,2),

    BASEF4 numeric(18,2),

    IVAF4 numeric(18,2),

    DEC2799F4 numeric(18,2),

    TOTALF4 numeric(18,2),

    BASED4 numeric(18,2),

    IVAD4 numeric(18,2),

    DEC2799D4 numeric(18,2),

    TOTALD4 numeric(18,2),

    FORMAP1 numeric(18,2),

    FORMAP2 numeric(18,2),

    FORMAP3 numeric(18,2),

    FORMAP4 numeric(18,2),

    FORMAP5 numeric(18,2),

    FORMAP6 numeric(18,2),

    FORMAP7 numeric(18,2),

    FORMAP8 numeric(18,2),

    FORMAP9 numeric(18,2),

    FORMAP10 numeric(18,2),

    FORMAP11 numeric(18,2),

    FORMAP12 numeric(18,2),

    FORMAP13 numeric(18,2),

    FORMAP14 numeric(18,2),

    FORMAP15 numeric(18,2),

    CANTFP1 integer,

    CANTFP2 integer,

    CANTFP3 integer,

    CANTFP4 integer,

    CANTFP5 integer,

    CANTFP6 integer,

    CANTFP7 integer,

    CANTFP8 integer,

    CANTFP9 integer,

    CANTFP10 integer,

    CANTFP11 integer,

    CANTFP12 integer,

    CANTFP13 integer,

    CANTFP14 integer,

    CANTFP15 integer,

    CARTERA numeric(18,2),

    RETES numeric(18,2),

    VENDEDOR1 varchar(60),

    VTAVEND1 numeric(18,2),

    VENDEDOR2 varchar(60),

    VTAVEND2 numeric(18,2),

    VENDEDOR3 varchar(60),

    VTAVEND3 numeric(18,2),

    VENDEDOR4 varchar(60),

    VTAVEND4 numeric(18,2),

    VENDEDOR5 varchar(60),

    VTAVEND5 numeric(18,2),

    NROFACTANUL integer,

    IVANUL numeric(18,2),

    TOTALFACANUL numeric(18,2))

AS

declare variable tarifa numeric(9,2);

declare variable basef numeric(18,2);

declare variable ivaf numeric(18,2);

declare variable dec2799af numeric(18,2);

declare variable totalf numeric(18,2);

declare variable basedv numeric(18,2);

declare variable ivad numeric(18,2);

declare variable inalc numeric(18,2);

declare variable dec2799ad numeric(18,2);

declare variable totald numeric(18,2);

declare variable i integer;

declare variable codven integer;

declare variable vended varchar(60);

declare variable f_id integer;

declare variable f_total numeric(18,2);

declare variable factor numeric(18,4);

declare variable ajuste integer;

begin

AJUSTE = 0;

if (PREFIJO <> '') then

  SELECT PREF_PRE, AUTO_NUMERO FROM AUTORIZACIONES WHERE PREF_PRE = :PREFIJO INTO :PREF, :autoriza;

SELECT PTVT_NOM FROM PUNTO_VENTA WHERE PTVT_ID = :punto INTO :puntovta;

  /* LAS VENTAS */

  SELECT MIN(FACT_NUMERO), MAX(FACT_NUMERO), COUNT(FACT_NUMERO), SUM(FACT_TOTAL - FACT_IVAMONTO + FACT_DEC2799), SUM(FACT_IVAMONTO), SUM(FACT_TOTAL), sum(fact_dec2799)

    FROM FACTURAS WHERE FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'N'

    INTO :NUMINI, :NUMFIN, :NROFAC, :BASENETO, :IVANETO, :TOTALNETO, :dec2799f;

  if (dec2799f is null) then

    dec2799f = 0;

  SELECT MIN(AUDI_HORA) FROM AUDITORIA WHERE TIDO_COD = 31 AND AUDI_COD = :PREFIJO || :NUMINI AND AUDI_OPER = 'I' INTO :HORAINI;

  SELECT MAX(AUDI_HORA) FROM AUDITORIA WHERE TIDO_COD = 31 AND AUDI_COD = :PREFIJO || :NUMFIN AND AUDI_OPER = 'I' INTO :HORAFIN;

  SELECT COUNT(FADE_ITEM), SUM(FADE_TOTAL-FADE_IVAMONTO-FADE_INALCM), SUM(FADE_IVAMONTO), SUM(FADE_INALCM), SUM(FADE_TOTAL)

    FROM facturas_detalle D, FACTURAS F

    WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT > 0 and FACT_ANULADO = 'N' and substring(d.arti_cod from 1 FOR 2) <> '.t'

    INTO :nroite, :BASEFAC, :IVAFAC, :INALCF, :TOTALFAC;

  totalfac = totalfac - dec2799f;

/*  ivafac = ivafac - dec2799f; */

  SELECT COUNT(FADE_ITEM), SUM(FADE_IVAMONTO+FADE_INALCM-FADE_TOTAL), SUM(FADE_IVAMONTO*-1), SUM(FADE_INALCM*-1), SUM(FADE_TOTAL*-1)

    FROM facturas_detalle D, FACTURAS F

    WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT < 0 and FACT_ANULADO = 'N' and substring(d.arti_cod from 1 FOR 2) <> '.t'

    INTO :NRODEV, :BASEDEV, :IVADEV, :INALCD, :TOTALDEV;

  SELECT COUNT(DVDE_ITEM), SUM(DVDE_TOTAL-DVDE_IVAMONTO-DVDE_INACM), SUM(DVDE_IVAMONTO), SUM(DVDE_INACM), SUM(DVDE_TOTAL)

    FROM devoluciones_ventas_detalle D, devoluciones_ventas F

    WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DEVT_ANULADO = 'N' and substring(d.arti_cod from 1 FOR 2) <> '.t'

    INTO :I, :BASEDV, :IVAD, :INALC, :TOTALD;

  select sum(devt_dec2799) FROM devoluciones_ventas WHERE DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DEVT_ANULADO = 'N' into :dec2799d;

  if (dec2799d is null) then

    dec2799d = 0;

  totald = totald - dec2799d;

/*  ivad = ivad - dec2799; */

  DEC2799N = DEC2799F - DEC2799D;

  SELECT COUNT(FACT_NUMERO),SUM(FACT_TOTAL - FACT_IVAMONTO), SUM(FACT_IVAMONTO) FROM FACTURAS

    WHERE FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'S'

    INTO :NROFACTANUL, :TOTALFACANUL, :IVANUL;

  if (nrofac is null) then

    nrofac = 0;

  if (NROFACTANUL is null) then

    NROFACTANUL = 0;

  if (TOTALFACANUL is null) then

    TOTALFACANUL = 0;

  if (IVANUL is null) then

    IVANUL = 0;

  if (baseneto is null) then

    baseneto = 0;

  if (ivaneto is null) then

    ivaneto = 0;

  if (totalneto is null) then

    totalneto = 0;

  if (nroite is null) then

    nroite = 0;

  if (basefac is null) then

    basefac = 0;

  if (ivafac is null) then

    ivafac = 0;

  if (inalcf is null) then

    inalcf = 0;

  if (totalfac is null) then

    totalfac = 0;

  if (nrodev is null) then

    nrodev = 0;

  if (basedev is null) then

    basedev = 0;

  if (ivadev is null) then

    ivadev = 0;

  if (inalcd is null) then

    inalcd = 0;

  if (totaldev is null) then

    totaldev = 0;

  if (I is null) then

    I = 0;

  if (basedv is null) then

    basedv = 0;

  if (ivad is null) then

    ivad = 0;

  if (inalc is null) then

    inalc = 0;

  if (totald is null) then

    totald = 0;

  NRODEV = NRODEV + I;

  BASENETO = BASENETO - BASEDV;

  IVANETO = IVANETO - IVAD - INALC;

  TOTALNETO = TOTALNETO - TOTALD;



  BASEDEV = BASEDEV + BASEDV;

  IVADEV = IVADEV + IVAD;

  INALCD = INALCD + INALC;

  INALCN = INALCF - INALCD;

  TOTALDEV = TOTALDEV + TOTALD;



  /* POR TARIFA DE IVA */

  I = 0;

  BASEF0 = 0;

  BASED0 = 0;

  IVAF0 = 0;

  IVAD0 = 0;

  DEC2799F0 = 0;

  DEC2799D0 = 0;

  TOTALF0 = 0;

  TOTALD0 = 0;

  BASEF1 = 0;

  BASED1 = 0;

  IVAF1 = 0;

  IVAD1 = 0;

  DEC2799F1 = 0;

  DEC2799D1 = 0;

  TOTALF1 = 0;

  TOTALD1 = 0;

  BASEF2 = 0;

  BASED2 = 0;

  IVAF2 = 0;

  IVAD2 = 0;

  DEC2799F2 = 0;

  DEC2799D2 = 0;

  TOTALF2 = 0;

  TOTALD2 = 0;

  BASEF3 = 0;

  BASED3 = 0;

  IVAF3 = 0;

  IVAD3 = 0;

  DEC2799F3 = 0;

  DEC2799D3 = 0;

  TOTALF3 = 0;

  TOTALD3 = 0;

  BASEF4 = 0;

  BASED4 = 0;

  IVAF4 = 0;

  IVAD4 = 0;

  DEC2799F4 = 0;

  DEC2799D4 = 0;

  TOTALF4 = 0;

  TOTALD4 = 0;

  vtavend1 = 0;

  vtavend2 = 0;

  vtavend3 = 0;

  vtavend4 = 0;

  vtavend5 = 0;

  FOR SELECT TAIV_PORC FROM TARIFA_IVA ORDER BY TAIV_PORC INTO :TARIFA DO

    BEGIN

    I = I + 1;

    SELECT SUM(FADE_TOTAL-FADE_IVAMONTO), SUM(FADE_IVAMONTO), SUM(FADE_TOTAL) FROM facturas_detalle D, FACTURAS F

        WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT > 0 AND FADE_IVAPORC = :TARIFA and FACT_ANULADO = 'N'

        INTO :BASEF, :IVAF, :TOTALF;

    SELECT SUM(FADE_TOTAL-FADE_IVAMONTO), SUM(FADE_IVAMONTO), SUM(FADE_TOTAL) FROM facturas_detalle D, FACTURAS F

        WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT < 0 AND FADE_IVAPORC = :TARIFA and FACT_ANULADO = 'N'

        INTO :BASEDV, :IVAD, :TOTALD;

    if (basef is null) then

        basef = 0;

    if (ivaf is null) then

        ivaf = 0;

    if (totalf is null) then

        totalf = 0;

    if (basedv is null) then

        basedv = 0;

    if (ivad is null) then

        ivad = 0;

    if (totald is null) then

        totald = 0;

    SELECT SUM(FADE_IVAMONTO) FROM ARTICULO A, facturas_detalle D, FACTURAS F

        WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT > 0 AND FADE_IVAPORC = :TARIFA and FACT_ANULADO = 'N'

        AND D.ARTI_COD = A.ARTI_COD AND ((FADE_REFERENCIA <> '.t') or (FADE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

        INTO DEC2799af;

    if (dec2799af is null) then

        dec2799af = 0;

    SELECT SUM(FADE_IVAMONTO) FROM ARTICULO A, facturas_detalle D, FACTURAS F

        WHERE D.FACT_ID = F.FACT_ID AND FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) AND FADE_CANT < 0 AND FADE_IVAPORC = :TARIFA and FACT_ANULADO = 'N'

        AND D.ARTI_COD = A.ARTI_COD AND ((FADE_REFERENCIA <> '.t') or (FADE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

        INTO DEC2799ad;

    if (dec2799ad is null) then

        dec2799ad = 0;

    FACTOR = 1;

    SELECT AJCI_FACTOR FROM ajuste_cierre WHERE AJCI_PORC = :TARIFA AND AJCI_FECINI <= :FECHA AND AJCI_FECFIN >= :FECHA INTO :FACTOR;

    if (FACTOR IS null) then

        FACTOR = 1;

    if (FACTOR <> 1) then

        AJUSTE = 1;

    BASEF = BASEF * FACTOR;

    BASEDV = BASEDV * FACTOR;

    IVAF = IVAF * FACTOR;

    IVAD = IVAD * FACTOR;

    TOTALF = TOTALF * FACTOR;

    TOTALD = TOTALD * FACTOR;

    DEC2799af = DEC2799af * FACTOR;

    DEC2799ad = DEC2799ad * FACTOR;



    if (I = 1) then

        BEGIN

        BASEF0 = BASEF;

        BASED0 = :BASEDV;

        IVAF0 = IVAF;

        IVAD0 = IVAD;

        DEC2799F0 = DEC2799AF;

        DEC2799D0 = DEC2799AD;

        TOTALF0 = TOTALF;

        TOTALD0 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D0 = DEC2799D0 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED0 = BASED0 - BASEDV;

        IVAD0 = IVAD0 - IVAD;

        TOTALD0 = TOTALD0 - TOTALD;

        END

    if (I = 2) then

        BEGIN

        BASEF1 = BASEF;

        BASED1 = :BASEDV;

        IVAF1 = IVAF;

        IVAD1 = IVAD;

        DEC2799F1 = DEC2799AF;

        DEC2799D1 = DEC2799AD;

        TOTALF1 = TOTALF;

        TOTALD1 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D1 = DEC2799D1 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED1 = BASED1 - BASEDV;

        IVAD1 = IVAD1 - IVAD;

        TOTALD1 = TOTALD1 - TOTALD;

        END

    if (I = 3) then

        BEGIN

        BASEF2 = BASEF;

        BASED2 = :BASEDV;

        IVAF2 = IVAF;

        IVAD2 = IVAD;

        DEC2799F2 = DEC2799AF;

        DEC2799D2 = DEC2799AD;

        TOTALF2 = TOTALF;

        TOTALD2 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D2 = DEC2799D2 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED2 = BASED2 - BASEDV;

        IVAD2 = IVAD2 - IVAD;

        TOTALD2 = TOTALD2 - TOTALD;

        END

    if (I = 4) then

        BEGIN

        BASEF3 = BASEF;

        BASED3 = :BASEDV;

        IVAF3 = IVAF;

        IVAD3 = IVAD;

        DEC2799F3 = DEC2799AF;

        DEC2799D3 = DEC2799AD;

        TOTALF3 = TOTALF;

        TOTALD3 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D3 = DEC2799D3 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED3 = BASED3 - BASEDV;

        IVAD3 = IVAD3 - IVAD;

        TOTALD3 = TOTALD3 - TOTALD;

        END

    if (I = 5) then

        BEGIN

        BASEF4 = BASEF;

        BASED4 = :BASEDV;

        IVAF4 = IVAF;

        IVAD4 = IVAD;

        DEC2799F4 = DEC2799AF;

        DEC2799D4 = DEC2799AD;

        TOTALF4 = TOTALF;

        TOTALD4 = TOTALD;

        SELECT SUM(DVDE_TOTAL-DVDE_IVAMONTO), SUM(DVDE_IVAMONTO), SUM(DVDE_TOTAL) FROM devoluciones_ventas_detalle D, devoluciones_ventas F

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            INTO :BASEDV, :IVAD, :TOTALD;

        SELECT SUM(DVDE_IVAMONTO) FROM devoluciones_ventas_detalle D, devoluciones_ventas F, ARTICULO A

            WHERE D.DEVT_ID = F.DEVT_ID AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND DVDE_IVAPORC = :TARIFA and DEVT_ANULADO = 'N'

            AND D.ARTI_COD = A.ARTI_COD AND ((DVDE_REFERENCIA <> '.t') or (DVDE_REFERENCIA IS NULL)) AND A.arti_dec2799 = 'S'

            INTO :DEC2799AD;

        if (dec2799ad is null) then

            dec2799ad = 0;

        DEC2799D4 = DEC2799D4 + DEC2799AD;

        if (basedv is null) then

            basedv = 0;

        if (ivad is null) then

            ivad = 0;

        if (totald is null) then

            totald = 0;

        BASED4 = BASED4 - BASEDV;

        IVAD4 = IVAD4 - IVAD;

        TOTALD4 = TOTALD4 - TOTALD;

        END

    END

  /* POR FORMA DE PAGO */

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 1) returning_values (CANTFP1, FORMAP1);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 2) returning_values (CANTFP2, FORMAP2);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 3) returning_values (CANTFP3, FORMAP3);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 4) returning_values (CANTFP4, FORMAP4);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 5) returning_values (CANTFP5, FORMAP5);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 6) returning_values (CANTFP6, FORMAP6);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 7) returning_values (CANTFP7, FORMAP7);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 8) returning_values (CANTFP8, FORMAP8);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 9) returning_values (CANTFP9, FORMAP9);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 10) returning_values (CANTFP10, FORMAP10);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 11) returning_values (CANTFP11, FORMAP11);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 12) returning_values (CANTFP12, FORMAP12);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 13) returning_values (CANTFP13, FORMAP13);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 14) returning_values (CANTFP14, FORMAP14);

  EXECUTE PROCEDURE cierre_formapago(:FECHA, :PUNTO, :PREFIJO, 15) returning_values (CANTFP15, FORMAP15);

  cartera = 0;

  for SELECT FACT_ID FROM FACTURAS WHERE FACT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'N'

    into :f_id

    do

    begin

    EXECUTE procedure saldo_doc_cartera(31, :f_id, :fecha,  0) returning_values (f_total);

    CARTERA = :cartera + :F_TOTAL;

    end

  for SELECT DEVT_ID FROM devoluciones_ventas WHERE DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO and DEVT_ANULADO = 'N'

    into :f_id

    do

    begin

    EXECUTE procedure saldo_doc_cartera(33, :f_id, :FECHA, 0) returning_values (f_total);

    CARTERA = :cartera - :F_TOTAL;

    end

  /* LAS RETENCIONES */

  SELECT SUM(RCDE_RTFTE+RCDE_RTIVA+RCDE_RTICA) FROM recibos_caja_detalle D, RECIBOS_CAJA R, PUNTO_VENTA PT

    WHERE R.RECA_ID = D.RECA_ID AND PT.PTVT_ID = :punto AND RECA_FECHA = :FECHA AND PT.caja_id = R.caja_id AND ((PT.PREF_PRE = R.PREF_PRE) or (:PREFIJO = '')) AND RECA_ANULADO = 'N'

    INTO :RETES;

  if (RETES IS NULL) then

    RETES = 0;



  if (cartera is null) then

    cartera = 0;

  I = 0;

  FOR SELECT MAX(F.VEND_COD), MAX(VEND_NOMBRE), SUM(FACT_TOTAL - FACT_IVAMONTO) FROM FACTURAS F, VENDEDORES V

    WHERE F.VEND_COD = V.VEND_COD AND FACT_FECHA = :FECHA AND F.PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and FACT_ANULADO = 'N'

    GROUP BY F.VEND_COD ORDER BY F.VEND_COD

    INTO :CODVEN, :VENDED, :TOTALF

    DO

    BEGIN

    if (totalf IS NULL) then

        TOTALF = 0;

    SELECT SUM(DEVT_TOTAL - DEVT_IVAMONTO) FROM DEVOLUCIONES_VENTAS

        WHERE VEND_COD = :CODVEN AND DEVT_FECHA = :FECHA AND PTVT_ID = :PUNTO AND ((PREF_PRE = :PREFIJO) or (:PREFIJO = '')) and DEVT_ANULADO = 'N'

        INTO :totald;

    if (TOTALD IS NULL) then

        TOTALD = 0;

    if (I = 0) then

        BEGIN

        vendedor1 = VENDED;

        vtavend1 = TOTALF - TOTALD;

        END

    if (I = 1) then

        BEGIN

        vendedor2 = VENDED;

        vtavend2 = TOTALF - TOTALD;

        END

    if (I = 2) then

        BEGIN

        vendedor3 = VENDED;

        vtavend3 = TOTALF - TOTALD;

        END

    if (I = 3) then

        BEGIN

        vendedor4 = VENDED;

        vtavend4 = TOTALF - TOTALD;

        END

    if (I = 4) then

        BEGIN

        vendedor5 = VENDED;

        vtavend5 = TOTALF - TOTALD;

        END

    I = I + 1;

    END

  if (AJUSTE = 1) then

    BEGIN

    BASEFAC = BASEF0 + BASEF1 + BASEF2 + BASEF3 + BASEF4;

    IVAFAC = IVAF0 + IVAF1 + IVAF2 + IVAF3 + IVAF4;

    TOTALFAC = totalf0 + TOTALF1 + TOTALF2 + TOTALF3 + TOTALF4;

    BASEDEV = totald0 + TOTALD1 + TOTALD2 + TOTALD3 + TOTALD4;

    IVADEV = IVAD0 + IVAD1 + IVAD2 + IVAD3 + IVAD4;

    TOTALDEV = totald0 + TOTALD1 + totald2 + totald3 + totald4;

    BASENETO = basefac + basedev;

    IVANETO = ivafac + ivadev;

    if (totalneto <> 0) Then

        begin

        formap1 = formap1 * (totalfac + totaldev) / totalneto;

        formap2 = formap2 * (totalfac + totaldev) / totalneto;

        formap3 = formap3 * (totalfac + totaldev) / totalneto;

        formap4 = formap4 * (totalfac + totaldev) / totalneto;

        formap5 = formap5 * (totalfac + totaldev) / totalneto;

        formap6 = formap6 * (totalfac + totaldev) / totalneto;

        formap7 = formap7 * (totalfac + totaldev) / totalneto;

        formap8 = formap8 * (totalfac + totaldev) / totalneto;

        formap9 = formap9 * (totalfac + totaldev) / totalneto;

        formap10 = formap10 * (totalfac + totaldev) / totalneto;

        formap11 = formap11 * (totalfac + totaldev) / totalneto;

        formap12 = formap12 * (totalfac + totaldev) / totalneto;

        formap13 = formap13 * (totalfac + totaldev) / totalneto;

        formap14 = formap14 * (totalfac + totaldev) / totalneto;

        formap15 = formap15 * (totalfac + totaldev) / totalneto;

        vtavend1 = vtavend1 * (totalfac + totaldev) / totalneto;

        vtavend2 = vtavend2 * (totalfac + totaldev) / totalneto;

        vtavend3 = vtavend3 * (totalfac + totaldev) / totalneto;

        vtavend4 = vtavend4 * (totalfac + totaldev) / totalneto;

        vtavend5 = vtavend5 * (totalfac + totaldev) / totalneto;

        end

    TOTALNETO = totalfac + totaldev;

    END

  INSERT INTO AUDITORIA(AUDI_USUARIO, TIDO_COD, AUDI_IDDOC, AUDI_OPER, AUDI_COD)

    VALUES (USER, 123, :punto, 'I', (:formap1+:formap2+:formap3+:formap4+:formap5+:CARTERA));

  suspend;

end

^



SET TERM ; ^











