SP_informix_proveedores

CREATE PROCEDURE "informix".sp_r_estado_pv(an_cod_empr INTEGER, an_cod_sucu INTEGER, an_cod_ejer INTEGER,ada_feci DATE, ada_fecf DATE, as_zona CHAR(4), ai_grupo CHAR(10), ai_clpv_ini INTEGER,ai_clpv_fin INTEGER)
returning CHAR(40),CHAR(40),CHAR(35),CHAR(13),CHAR(255),CHAR(40),CHAR(13),
CHAR(60),CHAR(255),INTEGER,CHAR(35),CHAR(5),CHAR(18),DATE,DATE,CHAR(255),
CHAR(13),DECIMAL(18,2),DECIMAL(18,2),DECIMAL(18,2),DECIMAL(18,2),DECIMAL(18,2),DECIMAL(18,2),
INTEGER,INTEGER,CHAR(13),VARCHAR(35);

define ld_saldo, ld_saldo_e, ld_saldo_a DECIMAL(18,2);
define ls_cuenta, ls_cod_cuen CHAR(35);
define lda_fec_ini,lda_fec_fin DATE;
define ls_tipo_mov CHAR(2);
define ld_dml_dasi, ld_cml_dasi,ld_cre_me,ld_deb_me,ld_s_ante DECIMAL(18,2);
define li_moneda, li_moneda_a ,li_cod_clpv,li_clpv_max,li_clpv_min INTEGER;
define li_codigo,li_cont , li_aux INTEGER;
define ls_num_depo CHAR(13);
define ls_num_mayo CHAR(13);
define ls_nom_cuen, ls_des_mone,ls_nom_zona,ls_nom_ciud,ls_ape_clpv CHAR(40);
define ls_benf_dchc CHAR(50);
define ls_det_asi CHAR(100);
define ls_cod_char,ls_num_fact,ls_num_ch CHAR(18);
define ls_nom_clpv,ls_obs,ls_detalle CHAR(255);
define ls_ruc CHAR(13);
define ls_rep CHAR(60);
define ls_cod_tran CHAR(5);
define ls_titulo VARCHAR(35);

let li_moneda = (SELECT pcon_mon_base FROM saepcon WHERE pcon_cod_empr = an_cod_empr);

IF ai_clpv_ini = 0 AND ai_clpv_fin = 0 THEN
    let li_clpv_max = (SELECT MAX(clpv_cod_clpv) FROM saeclpv WHERE clpv_cod_empr = an_cod_empr);
    let li_clpv_min = 1;
ELSE
    let li_clpv_max = ai_clpv_fin;
    let li_clpv_min = ai_clpv_ini;
END IF;

CREATE temp TABLE l_cxpa (
zona_nom_zona CHAR(40),
ciud_nom_ciud CHAR(40),
clpv_cod_cuen CHAR(35),
clpv_cod_char CHAR(10),
clpv_nom_clpv CHAR(255),
clpv_ape_clpv CHAR(40),
clpv_ruc_clpv CHAR(13),
clpv_rep_clpv CHAR(60),
clpv_obs_clpv CHAR(255),
clpv_cod_clpv INTEGER,
grpv_cta_grpv CHAR(35),
dmcp_cod_tran CHAR(5),
dmcp_num_fac CHAR(18),
dmcp_fec_ven DATE,
dcmp_fec_emis DATE,
dmcp_det_dcmp CHAR(255),
dcmp_num_comp CHAR(13),
dcmp_deb_ml DECIMAL(18,2),
dcmp_cre_ml DECIMAL(18,2),
dcmp_deb_mext DECIMAL(18,2),
dcmp_cre_mext DECIMAL(18,2),
saldo_ant_ml DECIMAL(18,2),
saldo_ant_me DECIMAL(18,2),
dmcp_mon_cta INTEGER,
dmcp_mon_base INTEGER,
dmcp_num_dchc CHAR(13),
clpv_titu_clpv VARCHAR(35)
);

INSERT INTO l_cxpa
SELECT
saezona.zona_nom_zona,
saeciud.ciud_nom_ciud,
saeclpv.clpv_cod_cuen,
saeclpv.clpv_cod_char,
saeclpv.clpv_nom_clpv,
saeclpv.clpv_ape_clpv,
saeclpv.clpv_ruc_clpv,
saeclpv.clpv_rep_clpv,
saeclpv.clpv_obs_clpv,
saeclpv.clpv_cod_clpv,
saegrpv.grpv_cta_grpv,
saedmcp.dmcp_cod_tran,
saedmcp.dmcp_num_fac,
saedmcp.dmcp_fec_ven,
saedmcp.dcmp_fec_emis,
saedmcp.dmcp_det_dcmp,
saedmcp.dcmp_num_comp,
saedmcp.dcmp_deb_ml,
saedmcp.dcmp_cre_ml,
saedmcp.dmcp_deb_mext,
saedmcp.dmcp_cre_mext,
0 AS saldo_ant_ml ,
0 AS saldo_ant_me ,
saecuen.cuen_cod_mone,
li_moneda,
saedchc.dchc_num_dchc,
MAX((SELECT saearpv.arpv_det_arpv FROM saearpv   WHERE     saearpv.arpv_cod_arpv = saeclpv.arpv_cod_arpv ))titulo
FROM saeclpv,
saezona,
 OUTER saeciud,
saegrpv,
saedmcp,saecuen,OUTER saedchc
WHERE ( saeciud.ciud_cod_ciud = saeclpv.clpv_cod_ciud ) AND
( saezona.zona_cod_zona = saeclpv.clpv_cod_zona ) AND
( saezona.zona_cod_empr = saeclpv.clpv_cod_empr ) AND
( saezona.zona_cod_sucu = saeclpv.clpv_cod_sucu ) AND
( saeclpv.grpv_cod_grpv = saegrpv.grpv_cod_grpv ) AND
( saeclpv.clpv_cod_empr = saegrpv.grpv_cod_empr) AND
( saeclpv.clpv_cod_clpv = saedmcp.clpv_cod_clpv ) AND
( saeclpv.clpv_cod_empr = saedmcp.dmcp_cod_empr ) AND
( saeclpv.clpv_cod_sucu = saedmcp.dmcp_cod_sucu ) AND
( saecuen.cuen_cod_empr = saedmcp.dmcp_cod_empr ) AND
( saecuen.cuen_cod_cuen = saegrpv.grpv_cta_grpv ) AND
( saedchc.dchc_cod_asto = saedmcp.dmcp_cod_asto ) AND
( saedchc.asto_cod_ejer = saedmcp.dmcp_cod_ejer ) AND
( saedchc.asto_cod_empr = saedmcp.dmcp_cod_empr ) AND
( saeclpv.clpv_clopv_clpv = 'PV') AND
( saegrpv.grpv_cod_empr = an_cod_empr ) AND
( saedmcp.dcmp_fec_emis BETWEEN ada_feci AND ada_fecf ) AND
( saeclpv.clpv_cod_zona||'' LIKE as_zona )AND
( saeclpv.grpv_cod_grpv LIKE ai_grupo )AND
( saeclpv.clpv_cod_clpv BETWEEN li_clpv_min AND li_clpv_max )
GROUP BY saezona.zona_nom_zona,
saeciud.ciud_nom_ciud,
saeclpv.clpv_cod_cuen,
saeclpv.clpv_cod_char,
saeclpv.clpv_nom_clpv,
saeclpv.clpv_ape_clpv,
saeclpv.clpv_ruc_clpv,
saeclpv.clpv_rep_clpv,
saeclpv.clpv_obs_clpv,
saeclpv.clpv_cod_clpv,
saegrpv.grpv_cta_grpv,
saedmcp.dmcp_cod_tran,
saedmcp.dmcp_num_fac,
saedmcp.dmcp_fec_ven,
saedmcp.dcmp_fec_emis,
saedmcp.dmcp_det_dcmp,
saedmcp.dcmp_num_comp,
saedmcp.dcmp_deb_ml,
saedmcp.dcmp_cre_ml,
saedmcp.dmcp_deb_mext,
saedmcp.dmcp_cre_mext,
saecuen.cuen_cod_mone,
saedchc.dchc_num_dchc;

foreach

SELECT DISTINCT(clpv_cod_clpv) INTO li_codigo FROM saedmcp
    WHERE dmcp_cod_empr = an_cod_empr
    AND clpv_cod_clpv IS NOT NULL
    AND dcmp_fec_emis < ada_feci
    AND clpv_cod_clpv BETWEEN li_clpv_min AND li_clpv_max
    GROUP BY 1
    ORDER BY 1

    let li_cont = (SELECT COUNT(*) FROM l_cxpa
    WHERE clpv_cod_clpv = li_codigo);

    IF li_cont = 0 THEN
    INSERT INTO l_cxpa
        SELECT saezona.zona_nom_zona,
        saeciud.ciud_nom_ciud,
        saeclpv.clpv_cod_cuen,
        saeclpv.clpv_cod_char,
        saeclpv.clpv_nom_clpv,
        saeclpv.clpv_ape_clpv,
        saeclpv.clpv_ruc_clpv,
        saeclpv.clpv_rep_clpv,
        saeclpv.clpv_obs_clpv,
        saeclpv.clpv_cod_clpv,
        saegrpv.grpv_cta_grpv,
        'SA',
        '0000000',
        '00/00/0000',
        '00/00/0000',
        '',
        '',
        0,
        0,
        0,
        0,
        0,
        0,
        saecuen.cuen_cod_mone,
        li_moneda,
        '',
     MAX((SELECT saearpv.arpv_det_arpv FROM saearpv   WHERE     saearpv.arpv_cod_arpv = saeclpv.arpv_cod_arpv ))titulo
        FROM saeclpv,
        saezona,
         OUTER saeciud,
        saegrpv,
        saedmcp,saecuen,OUTER saedchc
        WHERE ( saeciud.ciud_cod_ciud = saeclpv.clpv_cod_ciud ) AND
        ( saezona.zona_cod_zona = saeclpv.clpv_cod_zona ) AND
        ( saezona.zona_cod_empr = saeclpv.clpv_cod_empr ) AND
        ( saezona.zona_cod_sucu = saeclpv.clpv_cod_sucu ) AND
        ( saeclpv.grpv_cod_grpv = saegrpv.grpv_cod_grpv ) AND
        ( saeclpv.clpv_cod_empr = saegrpv.grpv_cod_empr) AND
        ( saeclpv.clpv_cod_clpv = saedmcp.clpv_cod_clpv ) AND
        ( saeclpv.clpv_cod_empr = saedmcp.dmcp_cod_empr ) AND
        ( saeclpv.clpv_cod_sucu = saedmcp.dmcp_cod_sucu ) AND
        ( saecuen.cuen_cod_empr = saedmcp.dmcp_cod_empr ) AND
        ( saecuen.cuen_cod_cuen = saegrpv.grpv_cta_grpv ) AND
        ( saedchc.dchc_cod_asto = saedmcp.dmcp_cod_asto ) AND
        ( saedchc.asto_cod_ejer = saedmcp.dmcp_cod_ejer ) AND
        ( saedchc.asto_cod_empr = saedmcp.dmcp_cod_empr ) AND
        ( saeclpv.clpv_clopv_clpv = 'PV') AND
        ( saegrpv.grpv_cod_empr = an_cod_empr ) AND
        ( saedmcp.dcmp_fec_emis < ada_feci  ) AND
        ( saeclpv.clpv_cod_zona||'' LIKE as_zona ) AND
        ( saeclpv.grpv_cod_grpv LIKE ai_grupo ) AND
        ( saeclpv.clpv_cod_clpv = li_codigo )
        GROUP BY saezona.zona_nom_zona,
        saeciud.ciud_nom_ciud,
        saeclpv.clpv_cod_cuen,
        saeclpv.clpv_cod_char,
        saeclpv.clpv_nom_clpv,
        saeclpv.clpv_ape_clpv,
        saeclpv.clpv_ruc_clpv,
        saeclpv.clpv_rep_clpv,
        saeclpv.clpv_obs_clpv,
        saeclpv.clpv_cod_clpv,
        saegrpv.grpv_cta_grpv,
        saedmcp.dmcp_cod_tran,
        saedmcp.dmcp_num_fac,
        saedmcp.dmcp_fec_ven,
        saedmcp.dcmp_fec_emis,
        saedmcp.dmcp_det_dcmp,
        saedmcp.dcmp_num_comp,
        saecuen.cuen_cod_mone,
        saedchc.dchc_num_dchc;
END IF
END foreach;
foreach
    SELECT saegrpv.grpv_cta_grpv,saeclpv.clpv_cod_clpv,saecuen.cuen_cod_mone,
SUM(NVL(saedmcp.dcmp_deb_ml,0)) - SUM(NVL(saedmcp.dcmp_cre_ml,0)),
SUM(NVL(saedmcp.dmcp_deb_mext,0)) - SUM(NVL(saedmcp.dmcp_cre_mext,0))
    INTO ls_cuenta,li_cod_clpv,li_moneda_a,ld_saldo, ld_saldo_e
    FROM saedmcp,saeclpv,saegrpv,saecuen
    WHERE saeclpv.grpv_cod_grpv = saegrpv.grpv_cod_grpv
    AND saeclpv.clpv_cod_empr = saegrpv.grpv_cod_empr
    AND saeclpv.clpv_cod_clpv = saedmcp.clpv_cod_clpv
    AND saeclpv.clpv_cod_empr = saedmcp.dmcp_cod_empr
    AND saecuen.cuen_cod_cuen = saegrpv.grpv_cta_grpv
    AND saecuen.cuen_cod_empr = saegrpv.grpv_cod_empr
    AND saeclpv.clpv_clopv_clpv = 'PV'
    AND dmcp_cod_empr = an_cod_empr
    AND dcmp_fec_emis < ada_feci
    AND saedmcp.clpv_cod_clpv BETWEEN li_clpv_min AND li_clpv_max
    GROUP BY 1,2,3

    UPDATE l_cxpa
    SET saldo_ant_ml = ld_saldo,
    saldo_ant_me = ld_saldo_e
    WHERE grpv_cta_grpv = ls_cuenta
    AND clpv_cod_clpv = li_cod_clpv;
END foreach;

foreach
    SELECT DISTINCT(clpv_cod_clpv) ,zona_nom_zona,ciud_nom_ciud,clpv_cod_cuen, clpv_cod_char, clpv_nom_clpv, clpv_ape_clpv,
clpv_ruc_clpv , clpv_rep_clpv ,clpv_obs_clpv ,  grpv_cta_grpv ,
dmcp_cod_tran ,dmcp_num_fac , dmcp_fec_ven , dcmp_fec_emis , dmcp_det_dcmp ,
dcmp_num_comp ,dcmp_deb_ml , dcmp_cre_ml ,dcmp_deb_mext ,dcmp_cre_mext ,
saldo_ant_ml ,saldo_ant_me ,dmcp_mon_cta ,dmcp_mon_base ,dmcp_num_dchc,clpv_titu_clpv
 INTO
    li_cod_clpv,ls_nom_zona,ls_nom_ciud,ls_cod_cuen,ls_cod_char,ls_nom_clpv,ls_ape_clpv,ls_ruc,ls_rep,
    ls_obs,ls_cuenta,ls_cod_tran,ls_num_fact,lda_fec_fin,lda_fec_ini,ls_detalle,
    ls_num_mayo,ld_dml_dasi, ld_cml_dasi,ld_deb_me,ld_cre_me,ld_saldo_a,ld_saldo_e,li_moneda_a,li_moneda,ls_num_ch,ls_titulo
    FROM l_cxpa
    WHERE dcmp_deb_ml<>0 OR
            dcmp_cre_ml<>0 OR
            dcmp_deb_mext<>0 OR
            dcmp_cre_mext<>0 OR
            saldo_ant_ml<>0 OR
            saldo_ant_me<>0
    RETURN ls_nom_zona,ls_nom_ciud,ls_cod_cuen,ls_cod_char,ls_nom_clpv,ls_ape_clpv,ls_ruc,
    ls_rep,ls_obs,li_cod_clpv,ls_cuenta,ls_cod_tran,ls_num_fact,lda_fec_fin,lda_fec_ini,
    ls_detalle,ls_num_mayo,ld_dml_dasi, ld_cml_dasi,ld_deb_me,ld_cre_me,ld_saldo_a,ld_saldo_e,li_moneda_a,li_moneda,ls_num_ch,ls_titulo
    WITH resume;
END foreach;
DROP TABLE l_cxpa;
END PROCEDURE