
/****** Object:  StoredProcedure [dbo].[PCONTPEND]    Script Date: 02/08/2017 13:08:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER Procedure [dbo].[PCONTPEND]
                (@ejer smallint = null)
AS  
/*
    Date: July 28,2016


*/
	Declare @id_ejer integer

	/* ====================================
	   Faltaban instrucciones en esta 
	   area
	   ====================================
	*/
	If (@ejer is null)
		Select @id_ejer = id_ejer 
		   From conttabl
	Else
		Select @id_ejer = id_ejer 
		   From contejer 
		   Where ejercicio=@ejer
	
/* .....................................
    Region 01
    Datos a incluir en seleccion 
   .....................................
*/
	Declare @dt int

	Select @dt = id_conc 
	   From contconc 
	   Where concepto = 'dt'


	Select id_diar, plan2.id_cuenta, doc,
		   fec_val, importe, debito, 
		   referencia, descrip, factura,
		   tasa, diar.id_diam, diam.id_mone,
		   id_dato, 
		   cheque, diam.id_empr, diar.id_conc
	  Into #T_diar
	  From contdiar diar 
		   inner join contdiam diam on diar.id_diam = diam.id_diam
		   inner join contplan plan2 on diar.id_cuenta = plan2.id_cuenta
		   inner join contestr estr on plan2.id_estr = estr.id_estr
		   inner join conttcta tcta on estr.id_tcta = tcta.id_tcta
	  Where id_ejer = @id_ejer
		and ( (tcta.cod_tcta in ('01','02','14','17','18') )
		 or plan2.id_cuenta 
		 in (Select id_cuenta 
				From contemce 
					 inner join contcesp on contemce.id_cesp = contcesp.id_cesp
				Where contcesp.cod_cesp = '18') )
		and plan2.id_cuenta 
		not in (Select id_cuenta 
				   From contemce 
						inner join contcesp on contemce.id_cesp = contcesp.id_cesp
				   Where contcesp.cod_cesp = '03')

/* .................................................
   Region 02
   Datos iniciales de las facturas pEndientes 
   .................................................
*/
	Select id_cuenta, doc, 
		   saldo_mb = Convert(Numeric(16,6), 
					  Sum((importe* tasa) * 
					  Case When debito = 1 
						   Then 1 
						   Else -1 End ))
	  Into #T_sal_mb
	  From #T_diar
	  Group by id_cuenta, doc
	  Having Abs(Sum((importe*tasa)*
			(Case When debito = 1 
				  Then 1 
				  Else -1 End)))>=0.0001

	Select #T_diar.id_cuenta, #T_diar.doc, 
		   fec_ini = Min(fec_val), saldo_mb,
		   id_empr, 
		   saldo = Round(Sum(importe * 
						 Case When debito = 1 
							  Then 1 
							  Else -1 End),6)
	  Into #T_Ini
	  From #T_diar 
		   inner join #T_sal_mb on #T_diar.id_cuenta = #T_sal_mb.id_cuenta 
				  and #T_diar.doc = #T_sal_mb.doc
	  Where (id_conc is null or id_conc != @dt ) 
	  Group by #T_diar.id_cuenta, 
			   #T_diar.doc, id_empr, saldo_mb

	Select #T_Ini.id_cuenta, #T_Ini.Doc, 
		   id_ini = Min(id_diar)
	  Into #T_ini_ini
	  From #T_Ini 
		   inner join #T_diar P on #T_Ini.id_cuenta = P.id_cuenta 
			 and #T_Ini.doc = P.doc 
			 and P.Fec_Val = #T_Ini.fec_ini
	  Where p.id_conc is null 
		 or p.id_conc != @dt 
	  Group by #T_Ini.id_cuenta , #T_Ini.doc

	Select P.id_cuenta, P.Doc, i.Fec_ini, 
		   descrip , referencia, cheque, 
		   factura, imp_ini = importe, 
		   imp_ini_mb = importe * tasa, 
		   id_mone_ini = id_mone, id_mone,
		   id_dato, i.id_empr,
		   tasa_ini = tasa, origen = id_dato,
		   saldo_mb, ii.id_ini, i.saldo
	  Into #T_ini2
	  From #T_Ini i 
		   inner join #T_ini_ini ii on i.id_cuenta = ii.id_cuenta 
			 and i.doc = ii.doc
		   inner join #T_diar P on ii.id_cuenta = P.id_cuenta 
			 and ii.id_ini  = p.id_diar

/* ................................................. 
   Region 03
   Datos finales de las facturas pEndientes 
   .................................................
*/
	Select id_cuenta, doc, 
		   fec_fin = Max(fec_val)
	  Into #T_Fin
	  From #T_diar
	  Where id_conc is null 
		 or id_conc != @dt
	  Group by id_cuenta, doc
	  Having Abs(Sum(importe * 
					  Case When debito = 1 
						   Then 1 
						   Else -1 End * tasa))>=0.001

	Select #T_Fin.id_cuenta, 
		   #T_Fin.Doc, id_fin = Max(id_diar)
	  Into #T_fin_fin
	  From #T_Fin 
		   inner join #T_diar P on #T_Fin.id_cuenta = P.id_cuenta 
			 and #T_Fin.doc = P.doc 
			 and P.Fec_Val = #T_Fin.fec_fin
	  Where p.id_conc is null 
		 or p.id_conc != @dt
	  Group by #T_Fin.id_cuenta , 
			   #T_Fin.doc

	Select P.id_cuenta, P.Doc, 
		   i.Fec_fin, descrip , 
		   referencia, cheque, 
		   factura, imp_fin = importe, 
		   imp_fin_mb = importe * tasa, 
		   id_mone_fin = id_mone, id_mone,
		   tasa_fin = tasa
	  Into #T_Fin2
	  From #T_Fin i 
		   inner join #T_fin_fin ii on i.id_cuenta = ii.id_cuenta 
			 and i.doc = ii.doc
		   inner join #T_diar P on ii.id_cuenta = P.id_cuenta 
			 and ii.id_fin  = p.id_diar

/* .................................................
   Region 04
   Busco si la factura mantiene el id_mone inicial 
   .................................................
*/
Select i.id_cuenta, i.doc, 
       id_mone_empr = empr.id_mone, 
       id_mone_fac = Case When 
                     Exists(Select d.id_cuenta,d.doc
                               From #T_diar d 
                               Where d.id_cuenta = i.id_cuenta 
                                 and d.doc = i.doc
                                 and (d.id_conc is null 
                                      or d.id_conc != @dt)                                         
                               Group by d.id_cuenta,d.doc
                               having Count(Distinct(id_mone))> 1)
                          Then 1
                          Else 0
                     End
  Into #T_mone_fac
  From #T_Ini2 i 
       inner join contempr empr on i.id_empr = empr.id_empr 

/*  .................................................
    Region 05  UniEndo todos los datos 
	.................................................
*/
	Select i.id_cuenta, i.Doc, 
		   i.Fec_ini, i.descrip, i.referencia, 
		   i.cheque , i.factura, i.id_empr, 
		   i.imp_ini, i.imp_ini_mb, i.id_mone_ini, 
		   id_mone = Case When id_mone_fac = 1 
						  Then id_mone_empr 
						  Else i.id_mone_ini End , 
		   i.id_dato, i.tasa_ini, 
		   i.origen, f.fec_fin, f.imp_fin, 
		   f.imp_fin_mb, i.saldo_mb, 
		   id_diar_ini = i.id_ini,
		   saldo = Case When (m.id_mone_fac = 0 
							  and i.id_mone_ini != m.id_mone_empr)  
						Then i.saldo 
						Else i.saldo_mb End
	Into #T_Finn
	From #T_Ini2 i 
		 inner join #T_Fin2 f on i.id_cuenta = f.id_cuenta 
		   and i.doc = f.doc
		 inner join #T_mone_fac m on i.id_cuenta = m.id_cuenta 
		   and i.doc = m.doc

	DELETE CONTPEnd 
	   Where id_ejer <= @id_ejer

/* ....................................................
   Region 06
   Insert Contpend
   ....................................................
*/
	Insert ContPEnd 
	  Select  id_cuenta, Doc, @id_ejer, 
			  Fec_ini, descrip, referencia, 
			  cheque, factura, imp_ini, 
			  imp_ini_mb, imp_fin, 
			  imp_fin_mb, saldo, saldo_mb,
			  id_mone_ini, id_mone, 
			  fec_fin, id_empr, id_dato,
			  tasa_ini, origen, id_diar_ini
		 From #T_Finn 

Return













