DELIMITER $$

DROP PROCEDURE IF EXISTS `sva_grb_mae_cli` $$
CREATE DEFINER=`inforecl_basr001`@`%` PROCEDURE `sva_grb_mae_cli`(
	iCodCli	int(11),
	iCodEst	int(11),
	iCodSgt	int(11),
	iCodTcl	int(11),
	iCodGir	int(11),
	iRutPsp	varchar(255),
	iDigRut	varchar(1),
	iNomCli	varchar(255),
	iNomFts	varchar(255),
	iFecNac	datetime,
	iEjcVta	int(11),
	iEjcCbz	int(11),
	iTipLic	varchar(255),
	iNumLic	varchar(255),
	iFecLic	datetime,
	iEmiLic	varchar(255),
	iObsCli	varchar(1000),
	iFecAlt	datetime,
	iFecBaj	datetime,
  iOrgNeg  int(11)
)
BEGIN

      Declare existe Bool;
      Declare exides Bool;
      Declare reg Bool;
      Declare cod Int;

      

      Select count(1)>0 into exides from tab_mae_cli where tmc_cod_cli <> iCodCli and tmc_rut_psp = iRutPsp and tmc_rut_psp <> 1;
      If exides Then
          Select "1000" as cod_sal,
                 "El Rut o Pasaporte ingresado ya existe en otro cliente"  as des_sal;
      Else

          Select count(1)>0 into existe from tab_mae_cli where tmc_cod_cli = iCodCli;

          If existe Then
              Update tab_mae_cli
    		      Set    tec_cod_est = iCodEst,
										 tms_cod_sgt = iCodSgt,
										 ttc_cod_tcl = iCodTcl,
										 tgc_cod_gir = iCodGir,
										 tmc_rut_psp = if(iCodTcl=103, '1', iRutPsp),
										 tmc_dig_rut = if(iCodTcl=103, '9', iDigRut),
										 tmc_nom_cli = iNomCli,
										 tmc_nom_fts = iNomFts,
										 tmc_fec_nac = iFecNac,
										 tmc_ejc_vta = iEjcVta,
										 tmc_ejc_cbz = iEjcCbz,
										 tmc_tip_lic = iTipLic,
										 tmc_num_lic = iNumLic,
										 tmc_fec_lic = iFecLic,
										 tmc_emi_lic = iEmiLic,
										 tmc_obs_cli = iObsCli,
										 tmc_fec_alt = iFecAlt,
										 tmc_fec_baj = iFecBaj,
                     ton_cod_one = iOrgNeg
              Where  tmc_cod_cli = iCodCli;

              Set cod = iCodCli;
          Else

							Set cod = (select ifnull(max(tmc_cod_cli),99)+1 from tab_mae_cli);
							Set iCodCli = cod;

              Insert into tab_mae_cli(tmc_cod_cli,
																			tec_cod_est,
																			tms_cod_sgt,
																			ttc_cod_tcl,
																			tgc_cod_gir,
																			tmc_rut_psp,
																			tmc_dig_rut,
																			tmc_nom_cli,
																			tmc_nom_fts,
																			tmc_fec_nac,
																			tmc_ejc_vta,
																			tmc_ejc_cbz,
																			tmc_tip_lic,
																			tmc_num_lic,
																			tmc_fec_lic,
																			tmc_emi_lic,
																			tmc_obs_cli,
																			tmc_fec_alt,
																			tmc_fec_baj,
                                      ton_cod_one)
              Values                 (iCodCli,
																			iCodEst,
																			iCodSgt,
																			iCodTcl,
																			iCodGir,
																			if(iCodTcl=103, '1', iRutPsp),
																			if(iCodTcl=103, '9', iDigRut),
																			iNomCli,
																			iNomFts,
																			iFecNac,
																			iEjcVta,
																			iEjcCbz,
																			iTipLic,
																			iNumLic,
																			iFecLic,
																			iEmiLic,
																			iObsCli,
																			iFecAlt,
																			iFecBaj,
                                      iOrgNeg);

              
              insert into tab_cnv_cli
              ( tmc_cod_cli
              , tcc_ini_cnv
              , tcc_fin_cnv
              , tcc_ejc_vta
              , tcc_rut_rep
              , tcc_dig_rep
              , tcc_rep_lgl
              , ttt_cod_tar
              , tec_cod_est
              , tcc_mto_crd
              , tcc_pzo_vta
              , tcc_obs_cnv
              , tcc_ejc_atz
              , tcc_seg_pai
              , tcc_seg_sac
              , tcc_prc_dto
              , ttc_cod_ctg)
              values
              (
                iCodCli
              , iFecAlt
              , iFecAlt
              , iEjcVta
              , 0
              , ''
              , ''
              , 102
              , 101
              , 0
              , 65
              , 'Convenio plan básico'
              , iEjcVta
              , 0
              , 0
              , 0
              , 0
              );


              insert into tab_nub_nfc
              (  tmc_cod_cli
              ,  tfp_cod_fpg
              ,  tnn_num_fpg
              ,  tnn_fec_vto)
              values
              (  iCodCli
              ,  100
              ,  ''
              ,  '1900-01-01');


              insert into tab_nub_nfc
              (  tmc_cod_cli
              ,  tfp_cod_fpg
              ,  tnn_num_fpg
              ,  tnn_fec_vto)
              values
              (  iCodCli
              ,  101
              ,  ''
              ,  '1900-01-01');


              insert into tab_nub_nfc
              (  tmc_cod_cli
              ,  tfp_cod_fpg
              ,  tnn_num_fpg
              ,  tnn_fec_vto)
              values
              (  iCodCli
              ,  103
              ,  ''
              ,  '1900-01-01');


              insert into tab_nub_nfc
              (  tmc_cod_cli
              ,  tfp_cod_fpg
              ,  tnn_num_fpg
              ,  tnn_fec_vto)
              values
              (  iCodCli
              ,  104
              ,  ''
              ,  '1900-01-01');

              insert into tab_nub_ngc
              (  tmc_cod_cli
              ,  tmg_cod_gar)
              values
              (  iCodCli
              ,  102);


          End If;

          Select "0000" as cod_sal,
                 "OK"   as des_sal,
                 cod    as cod_idd;
      End If;

END $$

DELIMITER ;