fred84

SP_informix_proveedores

Jun 17th, 2021
2,070
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 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)
  2. returning CHAR(40),CHAR(40),CHAR(35),CHAR(13),CHAR(255),CHAR(40),CHAR(13),
  3. CHAR(60),CHAR(255),INTEGER,CHAR(35),CHAR(5),CHAR(18),DATE,DATE,CHAR(255),
  4. CHAR(13),DECIMAL(18,2),DECIMAL(18,2),DECIMAL(18,2),DECIMAL(18,2),DECIMAL(18,2),DECIMAL(18,2),
  5. INTEGER,INTEGER,CHAR(13),VARCHAR(35);
  6.  
  7. define ld_saldo, ld_saldo_e, ld_saldo_a DECIMAL(18,2);
  8. define ls_cuenta, ls_cod_cuen CHAR(35);
  9. define lda_fec_ini,lda_fec_fin DATE;
  10. define ls_tipo_mov CHAR(2);
  11. define ld_dml_dasi, ld_cml_dasi,ld_cre_me,ld_deb_me,ld_s_ante DECIMAL(18,2);
  12. define li_moneda, li_moneda_a ,li_cod_clpv,li_clpv_max,li_clpv_min INTEGER;
  13. define li_codigo,li_cont , li_aux INTEGER;
  14. define ls_num_depo CHAR(13);
  15. define ls_num_mayo CHAR(13);
  16. define ls_nom_cuen, ls_des_mone,ls_nom_zona,ls_nom_ciud,ls_ape_clpv CHAR(40);
  17. define ls_benf_dchc CHAR(50);
  18. define ls_det_asi CHAR(100);
  19. define ls_cod_char,ls_num_fact,ls_num_ch CHAR(18);
  20. define ls_nom_clpv,ls_obs,ls_detalle CHAR(255);
  21. define ls_ruc CHAR(13);
  22. define ls_rep CHAR(60);
  23. define ls_cod_tran CHAR(5);
  24. define ls_titulo VARCHAR(35);
  25.  
  26. let li_moneda = (SELECT pcon_mon_base FROM saepcon WHERE pcon_cod_empr = an_cod_empr);
  27.  
  28. IF ai_clpv_ini = 0 AND ai_clpv_fin = 0 THEN
  29.     let li_clpv_max = (SELECT MAX(clpv_cod_clpv) FROM saeclpv WHERE clpv_cod_empr = an_cod_empr);
  30.     let li_clpv_min = 1;
  31. ELSE
  32.     let li_clpv_max = ai_clpv_fin;
  33.     let li_clpv_min = ai_clpv_ini;
  34. END IF;
  35.  
  36. CREATE temp TABLE l_cxpa (
  37. zona_nom_zona CHAR(40),
  38. ciud_nom_ciud CHAR(40),
  39. clpv_cod_cuen CHAR(35),
  40. clpv_cod_char CHAR(10),
  41. clpv_nom_clpv CHAR(255),
  42. clpv_ape_clpv CHAR(40),
  43. clpv_ruc_clpv CHAR(13),
  44. clpv_rep_clpv CHAR(60),
  45. clpv_obs_clpv CHAR(255),
  46. clpv_cod_clpv INTEGER,
  47. grpv_cta_grpv CHAR(35),
  48. dmcp_cod_tran CHAR(5),
  49. dmcp_num_fac CHAR(18),
  50. dmcp_fec_ven DATE,
  51. dcmp_fec_emis DATE,
  52. dmcp_det_dcmp CHAR(255),
  53. dcmp_num_comp CHAR(13),
  54. dcmp_deb_ml DECIMAL(18,2),
  55. dcmp_cre_ml DECIMAL(18,2),
  56. dcmp_deb_mext DECIMAL(18,2),
  57. dcmp_cre_mext DECIMAL(18,2),
  58. saldo_ant_ml DECIMAL(18,2),
  59. saldo_ant_me DECIMAL(18,2),
  60. dmcp_mon_cta INTEGER,
  61. dmcp_mon_base INTEGER,
  62. dmcp_num_dchc CHAR(13),
  63. clpv_titu_clpv VARCHAR(35)
  64. );
  65.  
  66. INSERT INTO l_cxpa
  67. SELECT
  68. saezona.zona_nom_zona,
  69. saeciud.ciud_nom_ciud,
  70. saeclpv.clpv_cod_cuen,
  71. saeclpv.clpv_cod_char,
  72. saeclpv.clpv_nom_clpv,
  73. saeclpv.clpv_ape_clpv,
  74. saeclpv.clpv_ruc_clpv,
  75. saeclpv.clpv_rep_clpv,
  76. saeclpv.clpv_obs_clpv,
  77. saeclpv.clpv_cod_clpv,
  78. saegrpv.grpv_cta_grpv,
  79. saedmcp.dmcp_cod_tran,
  80. saedmcp.dmcp_num_fac,
  81. saedmcp.dmcp_fec_ven,
  82. saedmcp.dcmp_fec_emis,
  83. saedmcp.dmcp_det_dcmp,
  84. saedmcp.dcmp_num_comp,
  85. saedmcp.dcmp_deb_ml,
  86. saedmcp.dcmp_cre_ml,
  87. saedmcp.dmcp_deb_mext,
  88. saedmcp.dmcp_cre_mext,
  89. 0 AS saldo_ant_ml ,
  90. 0 AS saldo_ant_me ,
  91. saecuen.cuen_cod_mone,
  92. li_moneda,
  93. saedchc.dchc_num_dchc,
  94. MAX((SELECT saearpv.arpv_det_arpv FROM saearpv   WHERE     saearpv.arpv_cod_arpv = saeclpv.arpv_cod_arpv ))titulo
  95. FROM saeclpv,
  96. saezona,
  97.  OUTER saeciud,
  98. saegrpv,
  99. saedmcp,saecuen,OUTER saedchc
  100. WHERE ( saeciud.ciud_cod_ciud = saeclpv.clpv_cod_ciud ) AND
  101. ( saezona.zona_cod_zona = saeclpv.clpv_cod_zona ) AND
  102. ( saezona.zona_cod_empr = saeclpv.clpv_cod_empr ) AND
  103. ( saezona.zona_cod_sucu = saeclpv.clpv_cod_sucu ) AND
  104. ( saeclpv.grpv_cod_grpv = saegrpv.grpv_cod_grpv ) AND
  105. ( saeclpv.clpv_cod_empr = saegrpv.grpv_cod_empr) AND
  106. ( saeclpv.clpv_cod_clpv = saedmcp.clpv_cod_clpv ) AND
  107. ( saeclpv.clpv_cod_empr = saedmcp.dmcp_cod_empr ) AND
  108. ( saeclpv.clpv_cod_sucu = saedmcp.dmcp_cod_sucu ) AND
  109. ( saecuen.cuen_cod_empr = saedmcp.dmcp_cod_empr ) AND
  110. ( saecuen.cuen_cod_cuen = saegrpv.grpv_cta_grpv ) AND
  111. ( saedchc.dchc_cod_asto = saedmcp.dmcp_cod_asto ) AND
  112. ( saedchc.asto_cod_ejer = saedmcp.dmcp_cod_ejer ) AND
  113. ( saedchc.asto_cod_empr = saedmcp.dmcp_cod_empr ) AND
  114. ( saeclpv.clpv_clopv_clpv = 'PV') AND
  115. ( saegrpv.grpv_cod_empr = an_cod_empr ) AND
  116. ( saedmcp.dcmp_fec_emis BETWEEN ada_feci AND ada_fecf ) AND
  117. ( saeclpv.clpv_cod_zona||'' LIKE as_zona )AND
  118. ( saeclpv.grpv_cod_grpv LIKE ai_grupo )AND
  119. ( saeclpv.clpv_cod_clpv BETWEEN li_clpv_min AND li_clpv_max )
  120. GROUP BY saezona.zona_nom_zona,
  121. saeciud.ciud_nom_ciud,
  122. saeclpv.clpv_cod_cuen,
  123. saeclpv.clpv_cod_char,
  124. saeclpv.clpv_nom_clpv,
  125. saeclpv.clpv_ape_clpv,
  126. saeclpv.clpv_ruc_clpv,
  127. saeclpv.clpv_rep_clpv,
  128. saeclpv.clpv_obs_clpv,
  129. saeclpv.clpv_cod_clpv,
  130. saegrpv.grpv_cta_grpv,
  131. saedmcp.dmcp_cod_tran,
  132. saedmcp.dmcp_num_fac,
  133. saedmcp.dmcp_fec_ven,
  134. saedmcp.dcmp_fec_emis,
  135. saedmcp.dmcp_det_dcmp,
  136. saedmcp.dcmp_num_comp,
  137. saedmcp.dcmp_deb_ml,
  138. saedmcp.dcmp_cre_ml,
  139. saedmcp.dmcp_deb_mext,
  140. saedmcp.dmcp_cre_mext,
  141. saecuen.cuen_cod_mone,
  142. saedchc.dchc_num_dchc;
  143.  
  144. foreach
  145.  
  146. SELECT DISTINCT(clpv_cod_clpv) INTO li_codigo FROM saedmcp
  147.     WHERE dmcp_cod_empr = an_cod_empr
  148.     AND clpv_cod_clpv IS NOT NULL
  149.     AND dcmp_fec_emis < ada_feci
  150.     AND clpv_cod_clpv BETWEEN li_clpv_min AND li_clpv_max
  151.     GROUP BY 1
  152.     ORDER BY 1
  153.  
  154.     let li_cont = (SELECT COUNT(*) FROM l_cxpa
  155.     WHERE clpv_cod_clpv = li_codigo);
  156.  
  157.     IF li_cont = 0 THEN
  158.     INSERT INTO l_cxpa
  159.         SELECT saezona.zona_nom_zona,
  160.         saeciud.ciud_nom_ciud,
  161.         saeclpv.clpv_cod_cuen,
  162.         saeclpv.clpv_cod_char,
  163.         saeclpv.clpv_nom_clpv,
  164.         saeclpv.clpv_ape_clpv,
  165.         saeclpv.clpv_ruc_clpv,
  166.         saeclpv.clpv_rep_clpv,
  167.         saeclpv.clpv_obs_clpv,
  168.         saeclpv.clpv_cod_clpv,
  169.         saegrpv.grpv_cta_grpv,
  170.         'SA',
  171.         '0000000',
  172.         '00/00/0000',
  173.         '00/00/0000',
  174.         '',
  175.         '',
  176.         0,
  177.         0,
  178.         0,
  179.         0,
  180.         0,
  181.         0,
  182.         saecuen.cuen_cod_mone,
  183.         li_moneda,
  184.         '',
  185.      MAX((SELECT saearpv.arpv_det_arpv FROM saearpv   WHERE     saearpv.arpv_cod_arpv = saeclpv.arpv_cod_arpv ))titulo
  186.         FROM saeclpv,
  187.         saezona,
  188.          OUTER saeciud,
  189.         saegrpv,
  190.         saedmcp,saecuen,OUTER saedchc
  191.         WHERE ( saeciud.ciud_cod_ciud = saeclpv.clpv_cod_ciud ) AND
  192.         ( saezona.zona_cod_zona = saeclpv.clpv_cod_zona ) AND
  193.         ( saezona.zona_cod_empr = saeclpv.clpv_cod_empr ) AND
  194.         ( saezona.zona_cod_sucu = saeclpv.clpv_cod_sucu ) AND
  195.         ( saeclpv.grpv_cod_grpv = saegrpv.grpv_cod_grpv ) AND
  196.         ( saeclpv.clpv_cod_empr = saegrpv.grpv_cod_empr) AND
  197.         ( saeclpv.clpv_cod_clpv = saedmcp.clpv_cod_clpv ) AND
  198.         ( saeclpv.clpv_cod_empr = saedmcp.dmcp_cod_empr ) AND
  199.         ( saeclpv.clpv_cod_sucu = saedmcp.dmcp_cod_sucu ) AND
  200.         ( saecuen.cuen_cod_empr = saedmcp.dmcp_cod_empr ) AND
  201.         ( saecuen.cuen_cod_cuen = saegrpv.grpv_cta_grpv ) AND
  202.         ( saedchc.dchc_cod_asto = saedmcp.dmcp_cod_asto ) AND
  203.         ( saedchc.asto_cod_ejer = saedmcp.dmcp_cod_ejer ) AND
  204.         ( saedchc.asto_cod_empr = saedmcp.dmcp_cod_empr ) AND
  205.         ( saeclpv.clpv_clopv_clpv = 'PV') AND
  206.         ( saegrpv.grpv_cod_empr = an_cod_empr ) AND
  207.         ( saedmcp.dcmp_fec_emis < ada_feci  ) AND
  208.         ( saeclpv.clpv_cod_zona||'' LIKE as_zona ) AND
  209.         ( saeclpv.grpv_cod_grpv LIKE ai_grupo ) AND
  210.         ( saeclpv.clpv_cod_clpv = li_codigo )
  211.         GROUP BY saezona.zona_nom_zona,
  212.         saeciud.ciud_nom_ciud,
  213.         saeclpv.clpv_cod_cuen,
  214.         saeclpv.clpv_cod_char,
  215.         saeclpv.clpv_nom_clpv,
  216.         saeclpv.clpv_ape_clpv,
  217.         saeclpv.clpv_ruc_clpv,
  218.         saeclpv.clpv_rep_clpv,
  219.         saeclpv.clpv_obs_clpv,
  220.         saeclpv.clpv_cod_clpv,
  221.         saegrpv.grpv_cta_grpv,
  222.         saedmcp.dmcp_cod_tran,
  223.         saedmcp.dmcp_num_fac,
  224.         saedmcp.dmcp_fec_ven,
  225.         saedmcp.dcmp_fec_emis,
  226.         saedmcp.dmcp_det_dcmp,
  227.         saedmcp.dcmp_num_comp,
  228.         saecuen.cuen_cod_mone,
  229.         saedchc.dchc_num_dchc;
  230. END IF
  231. END foreach;
  232. foreach
  233.     SELECT saegrpv.grpv_cta_grpv,saeclpv.clpv_cod_clpv,saecuen.cuen_cod_mone,
  234. SUM(NVL(saedmcp.dcmp_deb_ml,0)) - SUM(NVL(saedmcp.dcmp_cre_ml,0)),
  235. SUM(NVL(saedmcp.dmcp_deb_mext,0)) - SUM(NVL(saedmcp.dmcp_cre_mext,0))
  236.     INTO ls_cuenta,li_cod_clpv,li_moneda_a,ld_saldo, ld_saldo_e
  237.     FROM saedmcp,saeclpv,saegrpv,saecuen
  238.     WHERE saeclpv.grpv_cod_grpv = saegrpv.grpv_cod_grpv
  239.     AND saeclpv.clpv_cod_empr = saegrpv.grpv_cod_empr
  240.     AND saeclpv.clpv_cod_clpv = saedmcp.clpv_cod_clpv
  241.     AND saeclpv.clpv_cod_empr = saedmcp.dmcp_cod_empr
  242.     AND saecuen.cuen_cod_cuen = saegrpv.grpv_cta_grpv
  243.     AND saecuen.cuen_cod_empr = saegrpv.grpv_cod_empr
  244.     AND saeclpv.clpv_clopv_clpv = 'PV'
  245.     AND dmcp_cod_empr = an_cod_empr
  246.     AND dcmp_fec_emis < ada_feci
  247.     AND saedmcp.clpv_cod_clpv BETWEEN li_clpv_min AND li_clpv_max
  248.     GROUP BY 1,2,3
  249.  
  250.     UPDATE l_cxpa
  251.     SET saldo_ant_ml = ld_saldo,
  252.     saldo_ant_me = ld_saldo_e
  253.     WHERE grpv_cta_grpv = ls_cuenta
  254.     AND clpv_cod_clpv = li_cod_clpv;
  255. END foreach;
  256.  
  257. foreach
  258.     SELECT DISTINCT(clpv_cod_clpv) ,zona_nom_zona,ciud_nom_ciud,clpv_cod_cuen, clpv_cod_char, clpv_nom_clpv, clpv_ape_clpv,
  259. clpv_ruc_clpv , clpv_rep_clpv ,clpv_obs_clpv ,  grpv_cta_grpv ,
  260. dmcp_cod_tran ,dmcp_num_fac , dmcp_fec_ven , dcmp_fec_emis , dmcp_det_dcmp ,
  261. dcmp_num_comp ,dcmp_deb_ml , dcmp_cre_ml ,dcmp_deb_mext ,dcmp_cre_mext ,
  262. saldo_ant_ml ,saldo_ant_me ,dmcp_mon_cta ,dmcp_mon_base ,dmcp_num_dchc,clpv_titu_clpv
  263.  INTO
  264.     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,
  265.     ls_obs,ls_cuenta,ls_cod_tran,ls_num_fact,lda_fec_fin,lda_fec_ini,ls_detalle,
  266.     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
  267.     FROM l_cxpa
  268.     WHERE dcmp_deb_ml<>0 OR
  269.             dcmp_cre_ml<>0 OR
  270.             dcmp_deb_mext<>0 OR
  271.             dcmp_cre_mext<>0 OR
  272.             saldo_ant_ml<>0 OR
  273.             saldo_ant_me<>0
  274.     RETURN ls_nom_zona,ls_nom_ciud,ls_cod_cuen,ls_cod_char,ls_nom_clpv,ls_ape_clpv,ls_ruc,
  275.     ls_rep,ls_obs,li_cod_clpv,ls_cuenta,ls_cod_tran,ls_num_fact,lda_fec_fin,lda_fec_ini,
  276.     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
  277.     WITH resume;
  278. END foreach;
  279. DROP TABLE l_cxpa;
  280. END PROCEDURE
  281.                                                                                                                                    
  282.                                                                                                                                                                                          
  283.  
RAW Paste Data