--Guv: Este paquete fue hecho para corregir los valores de las columnas Cant_Recibida
--     y CantRecepcion de la tabla com_det_orden

Create or Replace Package OCSx IS

   Procedure CorrigeValores;

END Ocsx;
/

Create or Replace Package Body OCSx IS
	
   Function CantFactLineaOrden(pCia in Varchar2, pSuc in Varchar2, pProv in Varchar2, pFact in Varchar2,
                               pNumLinea in Number, pOrdenCompra in Number) Return Number Is
      vCant Com_Det_Fac_Orden.Cantidad%Type;                              
	Begin
		Select Cantidad
	  	Into vCant
	  From Com_Det_Fac_Orden
    Where Cod_Cia       = pCia
      And Sucursal      = pSuc
      And Cod_Proveedor = pProv
    	And Num_Factura   = pFact
     	And Num_Linea     = pNumLinea 
     	And Num_Orden     = pOrdenCompra;		
     	
		Return vCant;
	Exception
		When No_Data_Found Then
			Return 0;
	End CantFactLineaOrden;

	Procedure ActOcFactura(pCia in Varchar2, pSuc in Varchar2, pProv in Varchar2, pFact in Varchar2, 
	                       pArticulo in Varchar2, pNumLinea in Number, pOrdenCompra in Number, 
	                       pMedida in Varchar2, pCantidad in Number) Is
	Begin
		Update Com_Det_Fac_Orden
     	Set Cod_Articulo = pArticulo,
     	    Medida       = pMedida,
     			Cantidad     = Cantidad + pCantidad      	    
    Where Cod_Cia       = pCia
      And Sucursal      = pSuc
      And Cod_Proveedor = pProv
     	And Num_Factura   = pFact
     	And Num_Linea     = pNumLinea 
     	And Num_Orden     = pOrdenCompra;				
     	
		If Sql%NotFound Then     	
			Insert Into Com_Det_Fac_Orden(Cod_cia, Sucursal, Num_Factura, Num_Linea, Cod_Proveedor, 
		  	                            Num_orden, Cod_Articulo, Medida, Cantidad)
    												Values (pCia, pSuc, pFact, pNumLinea, pProv, pOrdenCompra, 
        														pArticulo, pMedida, pCantidad);
		End If;        														
	End ActOcFactura;	
	
	Procedure ActDetOrden(pCia in Varchar2, pSuc in Varchar2, pOrdenCompra in Number, 
	                      pArticulo in Varchar2, pCantRecibida in Number, pCantRecepcion in Number, 
	                      pMedida in Varchar2, pPrecio in Number, pImpVta in Number, pDescuento in Number, 
	                      pImpCons in Number) Is
	Begin
		Update Com_Det_Orden
  		Set CantRecepcion = CantRecepcion + pCantRecepcion, 
  		  	Cant_Recibida = Cant_Recibida + pCantRecibida
  	Where Cod_Cia      = pCia
  		And Sucursal     = pSuc
  		And Num_Orden    = pOrdenCompra
  		And Cod_Articulo = pArticulo;
  			
		If Sql%NotFound Then
			Insert Into Com_Det_Orden(Cod_Cia, Sucursal, Num_Orden, Cod_Articulo,
			 	                        Cod_Artic_Prov, Medida, Cantidad, Cant_Recibida, 
			   	                      Precio_Unitario, Imp_Venta, Descuento, Imp_Consumo, 
			     	                    Observaciones, CantRecepcion)
			      	           Values(pCia, pSuc, pOrdenCompra, pArticulo, Null, pMedida, 
			         	                 0, pCantRecibida, pPrecio, pImpVta, pDescuento, pImpCons, 
			           	               'Recibido no ordenado', pCantRecepcion);
		End If;		
	End ActDetOrden;

	Procedure ActOrdenCompra(pCia in Varchar2, pSuc in Varchar2, pProv in Varchar2, 
  	                       pFact in Varchar2, pArticulo in Varchar2, pNumLinea in Number, 
    	                     pCantRecepcion in Number, pCantRecibida in Number, 
      	                   pMedida in Varchar2, pPrecio in Number, pImpVta in Number, 
        	                 pDescuento in Number, pImpCons in Number) IS
                         
		Cursor OcsArt Is Select D.Num_Orden, D.CantRecepcion, D.Cant_Recibida, D.Cantidad
	                   From Com_Enc_Fac_Orden Fo, Com_Enc_Orden E, Com_Det_Orden D
  	                   Where Fo.Cod_Cia       = pCia
    	                     And Fo.Sucursal      = pSuc
      	                     And Fo.Cod_Proveedor = pProv
        	             And Fo.Num_factura   = pFact
                             And E.Cod_Cia        = Fo.Cod_Cia
                             And E.Sucursal       = Fo.Sucursal
                             And E.Num_Orden      = Fo.Num_Orden
                             And E.Estado         = 'D'
          	             And D.Cod_Cia        = pCia
            	             And D.Sucursal       = pSuc
              	             And D.Num_Orden      = Fo.Num_Orden
                	     And D.Cod_Articulo   = pArticulo
                  	     And ((pCantRecepcion > 0 Or (pCantRecepcion < 0 And D.CantRecepcion > 0))
                    	      Or (pCantRecibida > 0 Or (pCantRecibida < 0 And D.Cant_Recibida > 0)))
                     	     And (pCantRecibida > 0 
                       	          Or Exists(Select 1 From Com_Det_Fac_Orden Dfo
                            	            Where Dfo.Cod_Cia       = pCia
                              	              And Dfo.Sucursal      = pSuc
                                	      And Dfo.Cod_Proveedor = pProv
                                  	      And Dfo.Num_Factura   = pFact
                                    	      And Dfo.Num_Linea     = pNumLinea
                                    	      And Dfo.Num_Orden     = Fo.Num_Orden
	                                      And Dfo.Cantidad      > 0))                                    	
  	                 Order by Decode(pCantRecibida, 0, D.CantRecepcion, D.Cantidad - D.Cant_Recibida) Desc;
                   
		Cursor Ocs Is Select Fo.Num_Orden
                              From Com_Enc_Fac_Orden Fo, Com_Enc_Orden E
  			      Where Fo.Cod_Cia       = pCia
  			        And Fo.Sucursal      = pSuc
  				And Fo.Cod_Proveedor = pProv
  				And Fo.Num_Factura   = pFact
                                And E.Cod_Cia        = Fo.Cod_Cia
                                And E.Sucursal       = Fo.Sucursal
                                And E.Num_Orden      = Fo.Num_Orden
                                And E.Estado         = 'D'
  			      Order by Fo.Num_Orden;
  							
		vCantRecepcion Com_Det_Orden.CantRecepcion%Type := pCantRecepcion;
		vCantRecibida  Com_Det_Orden.CantRecepcion%Type := pCantRecibida;
		vCantActRecep  Com_Det_Orden.CantRecepcion%Type; 
		vCantActRecib  Com_Det_Orden.CantRecepcion%Type; 
	
	BEGIN		
  	For Oa in OcsArt
  	Loop  		
	  	If vCantRecibida >= 0 Then
  			If Oa.Cantidad > Oa.Cant_Recibida Then
  				vCantActRecib := Least(Oa.Cantidad - Oa.Cant_Recibida, vCantRecibida);
  			Else
	  			vCantActRecib := vCantRecibida;
  			End If;
  		Else
	  		vCantActRecib := Greatest(vCantRecibida, -Oa.Cant_Recibida);
  			vCantActRecib := Greatest(vCantActRecib, -CantFactLineaOrden(pCia, pSuc, pProv, pFact,
        	                                                           pNumLinea, Oa.Num_Orden));
  		End If;  	
  	
  		If vCantRecepcion >= 0 Then
	  		If Oa.Cant_Recibida + vCantActRecib > Oa.CantRecepcion Then
  				vCantActRecep := Least(Oa.Cant_Recibida + vCantActRecib-Oa.CantRecepcion, vCantRecepcion);
  			Else
	  			vCantActRecep := 0;
  			End If;
  		Else
	  		vCantActRecep := Greatest(vCantRecepcion, -Oa.CantRecepcion);	
  		End If;

			ActDetOrden(pCia, pSuc, Oa.Num_Orden, pArticulo,  vCantActRecib, 
			            vCantActRecep, Null, Null, Null, Null, Null);
  		
  		If (Oa.Cant_Recibida < Oa.Cantidad And Oa.Cant_Recibida + vCantActRecib >= Oa.Cantidad
  		  	And Oa.CantRecepcion + vCantActRecep = 0)
  		  	Or (Oa.CantRecepcion > 0 And Oa.CantRecepcion + vCantActRecep = 0
  		      	And Oa.Cant_Recibida + vCantActRecib >= Oa.Cantidad) Then					
  		      	
  				Update Com_Enc_Orden E
	  				Set Estado = 'L'
  				Where Cod_Cia   = pCia
	  				And Sucursal  = pSuc	
  					And Num_Orden = Oa.Num_Orden
  					And Not Exists(Select 1 From Com_Det_Orden D
  				               	 Where D.Cod_Cia       = pCia
  				               	   And D.Sucursal      = pSuc
  				               	 	 And D.Num_Orden     = Oa.Num_Orden
  				               	 	 And (D.Cant_Recibida < D.Cantidad
  				               	        Or D.CantRecepcion > 0));
  		End If;
  	
  		If vCantActRecib != 0 Then
	  		ActOcFactura(pCia, pSuc, pProv, pFact, pArticulo, pNumLinea, 
  		  	           Oa.Num_Orden, pMedida, vCantActRecib);
	  	End If;
  	
  		vCantRecepcion := vCantRecepcion - vCantActRecep;
  		vCantRecibida := vCantRecibida - vCantActRecib;
  	
  		Exit When vCantRecepcion = 0 And vCantRecibida = 0;
  	End Loop;
  
  	If vCantRecibida > 0 Then  
  		For Oa in Ocs
  		Loop
				ActDetOrden(pCia, pSuc, Oa.Num_Orden, pArticulo,  vCantRecibida, 
			  	          vCantRecepcion, pMedida, pPrecio, pImpVta, pDescuento, 
	                  pImpCons);  		
  		
  			ActOcFactura(pCia, pSuc, pProv, pFact, pArticulo, pNumLinea, 
  	  		           Oa.Num_Orden, pMedida, vCantRecibida);  		
  			Exit;
  		End Loop;
  	End If;
	End ActOrdenCompra;

	Procedure ObtArticuloDetFact(pCia in Varchar2, pSuc in Varchar2, pProv in Varchar2, 
                               pFact in Varchar2, pArticuloFin in Varchar2, pFecVence in Date, 
                               pNumLinea out Number, pArticulo out Varchar2) Is
				
		Cursor Arts Is Select Num_Linea, Cod_Articulo			
									 From Com_Det_Factura
									 Where Cod_Cia            = pCia
										 And Sucursal           = pSuc
										 And Cod_Proveedor      = pProv
										 And Num_Factura        = pFact			 
										 And Cod_Articulo_Final = pArticuloFin
										 And (Fec_Vence Is Null Or Fec_Vence = pFecVence)
									 Order by Decode(Fec_Vence, pFecVence, 1, 2);
	Begin
		Open Arts;
		Fetch Arts Into pNumLinea, pArticulo;
		Close Arts;
	End ObtArticuloDetFact;                                 

	Function ObtArticuloDetNacio(pCia in Varchar2, pSuc in Varchar2, pNacio in Number, 
	                             pArticuloFin in Varchar2, pFecVence in Date) Return Varchar2 Is
		Cursor Arts Is Select Cod_Articulo			
									 From Com_Det_Nacionalizacion
									 Where Cod_Cia             = pCia
									   And Sucursal            = pSuc			
										 And Num_Nacionalizacion = pNacio
										 And Cod_Articulo_Final = pArticuloFin
										 And (Fec_Vence Is Null Or Fec_Vence = pFecVence)
									 Order by Decode(Fec_Vence, pFecVence, 1, 2);	                             
	                             
		vArticulo Com_Det_Nacionalizacion.Cod_Articulo%Type;	                             
	Begin
		Open Arts;
		Fetch Arts Into vArticulo;
		Close Arts;
			     
		Return vArticulo;			     
	End ObtArticuloDetNacio;

   Procedure Facturas Is
      Cursor Facs Is Select F.Cod_Cia, F.Sucursal, F.Cod_Proveedor, F.Num_Factura, 
                            F.Tipo_Compra, F.Cod_Bodega
                     From Com_Enc_Factura F
                     Where F.Estado In ('N', 'L')
                       And Exists(Select 1 From Com_Enc_Fac_Orden Fo, Com_Enc_Orden E
                                  Where Fo.Cod_Cia       = F.Cod_Cia
                                    And Fo.Sucursal      = F.Sucursal
                                    And Fo.Num_Factura   = F.Num_Factura
                                    And Fo.Cod_Proveedor = F.Cod_Proveedor
                                    And E.Cod_Cia        = Fo.Cod_Cia
                                    And E.Sucursal       = Fo.Sucursal
                                    And E.Num_Orden      = Fo.Num_Orden
                                    And E.Estado         = 'D');

      Cursor Det(pCia in Varchar2, pSuc in Varchar2, pProv in Varchar2, pFact in Varchar2) 
                 Is Select Cod_Articulo, Num_Linea, Cantidad, Medida, 
                           Precio_Unitario_Fob, Imp_Venta, Descuento, Imp_Consumo
                    From Com_Det_Factura
                    Where Cod_Cia       = pCia
                      And Sucursal      = psuc
                      And Num_Factura   = pFact
                      And Cod_Proveedor = pProv;

      vCantRecepcion Com_Det_Orden.CantRecepcion%Type := 0;                                
   Begin
      For F in Facs
      Loop
         For D in Det(F.Cod_Cia, F.Sucursal, F.Cod_Proveedor, F.Num_Factura)
         Loop
            If F.Tipo_Compra = 'N' Or F.Cod_Bodega Is Null Then
     	       vCantRecepcion := D.Cantidad;
            Else
               vCantRecepcion := 0;
            End If;

            ActOrdenCompra(F.Cod_Cia, F.Sucursal, F.Cod_Proveedor, F.Num_Factura, 
                           D.Cod_Articulo, D.Num_Linea, vCantRecepcion, D.Cantidad, 
                           D.Medida, D.Precio_Unitario_Fob, D.Imp_Venta, D.Descuento, 
                           D.Imp_Consumo);
         End Loop;
      End Loop;
   End Facturas;

   Procedure Nacionalizaciones Is
      Cursor Nacios Is Select N.Cod_Cia, N.Sucursal, N.Cod_Proveedor, N.Num_Factura,
                              N.Num_Nacionalizacion
                       From Com_Enc_Nacionalizacion N
                       Where N.Estado = 'N'
                         And N.Cod_Bodega Is Not Null
                         And Exists(Select 1 From Com_Enc_Fac_Orden Fo, Com_Enc_Orden E
                                    Where Fo.Cod_Cia       = N.Cod_Cia
                                      And Fo.Sucursal      = N.Sucursal
                                      And Fo.Num_Factura   = N.Num_Factura
                                      And Fo.Cod_Proveedor = N.Cod_Proveedor
                                      And E.Cod_Cia        = Fo.Cod_Cia
                                      And E.Sucursal       = Fo.Sucursal
                                      And E.Num_Orden      = Fo.Num_Orden
                                      And E.Estado         = 'D');

      Cursor Det(pCia in Varchar2, pSuc in Varchar2, pNacio in Number) Is 
                       Select Cod_Articulo, Fec_Vence, Cantidad, Medida
                       From Com_Det_Nacionalizacion
                       Where Cod_Cia             = pCia
                         And Sucursal            = pSuc
                         And Num_Nacionalizacion = pNacio;

      vArticulo Com_Det_Factura.Cod_Articulo%Type;                
      vNumLinea Com_Det_Factura.Num_Linea%Type;
   Begin                 
      For N in Nacios
      Loop
         For D in Det(N.Cod_Cia, N.Sucursal, N.Num_Nacionalizacion)
         Loop
            ObtArticuloDetFact(N.Cod_Cia, N.Sucursal, N.Cod_Proveedor, N.Num_Factura, 
                               D.Cod_Articulo, D.Fec_Vence, vNumLinea, vArticulo);

            If vNumLinea Is Not Null Then
       	       ActOrdenCompra(N.Cod_Cia, N.Sucursal, N.Cod_Proveedor, N.Num_Factura, 
                              vArticulo, vNumLinea, -D.Cantidad, 0, D.Medida, Null, 
                              Null, Null, Null);
            End If;
         End Loop;
      End Loop;
   End Nacionalizaciones;

   Procedure RecFacturas Is
      Cursor Recs Is Select R.Cod_Cia, R.Sucursal, R.Proveedor, R.Referencia, R.Documento
                     From Com_Enc_Recibo R
                     Where R.Estado = 'A'
                       And R.Origen = 'F'
                       And Exists(Select 1 From Com_Enc_Factura F
                                  Where F.Cod_Cia       = R.Cod_Cia
                                    And F.Sucursal      = R.Sucursal
                                    And F.Num_Factura   = R.Referencia
                                    And F.Cod_Proveedor = R.Proveedor
                                    And F.Cod_Bodega Is Null)
                       And Exists(Select 1 From Com_Enc_Fac_Orden Fo, Com_Enc_Orden E
                                  Where Fo.Cod_Cia       = R.Cod_Cia
                                    And Fo.Sucursal      = R.Sucursal
                                    And Fo.Num_Factura   = R.Referencia
                                    And Fo.Cod_Proveedor = R.Proveedor
                                    And E.Cod_Cia        = Fo.Cod_Cia
                                    And E.Sucursal       = Fo.Sucursal
                                    And E.Num_Orden      = Fo.Num_Orden
                                    And E.Estado         = 'D');

      Cursor Det(pCia in Varchar2, pSuc in Varchar2, pDoc in Number)
                 Is Select Cod_Articulo, Fec_Vence, Cantidad, Medida
                    From Com_Det_Recibo
                    where Cod_Cia   = pCia
                      And Sucursal  = pSuc
                      And Documento = pDoc;

      vArticulo Com_Det_Factura.Cod_Articulo%Type;                
      vNumLinea Com_Det_Factura.Num_Linea%Type;
   Begin
      For R in Recs
      Loop
         For D in Det(R.Cod_Cia, R.Sucursal, R.Documento)
         Loop
   	    ObtArticuloDetFact(R.Cod_Cia, R.Sucursal, R.Proveedor, R.Referencia, 
                                          D.Cod_Articulo, D.Fec_Vence, vNumLinea, vArticulo);

            If vNumLinea Is Not Null Then  	
               ActOrdenCompra(R.Cod_Cia, R.Sucursal, R.Proveedor, R.Referencia, 
                                  vArticulo, vNumLinea, -D.Cantidad, 0, D.Medida, Null, 
                                  Null, Null, Null);
            End If;
         End Loop;
      End Loop;


   End RecFacturas;

   Procedure RecNacionalizaciones Is
      Cursor Recs Is Select R.Cod_Cia, R.Sucursal, R.Proveedor, R.Referencia, 
                            R.Documento, N.Num_Factura
                     From Com_Enc_Recibo R, Com_Enc_Nacionalizacion N
                     Where R.Estado              = 'A'
                       And R.Origen              = 'N'
                       And N.Cod_Cia             = R.Cod_Cia
                       And N.Sucursal            = R.Sucursal
                       And N.Num_Nacionalizacion = R.Referencia
                       And N.Cod_Bodega Is Null
                       And Exists(Select 1 From Com_Enc_Fac_Orden Fo, Com_Enc_Orden E
                                  Where Fo.Cod_Cia       = N.Cod_Cia
                                    And Fo.Sucursal      = N.Sucursal
                                    And Fo.Num_Factura   = N.Num_Factura
                                    And Fo.Cod_Proveedor = N.Cod_Proveedor
                                    And E.Cod_Cia        = Fo.Cod_Cia
                                    And E.Sucursal       = Fo.Sucursal
                                    And E.Num_Orden      = Fo.Num_Orden
                                    And E.Estado         = 'D');

      Cursor Det(pCia in Varchar2, pSuc in Varchar2, pDoc in Number)
                 Is Select Cod_Articulo, Fec_Vence, Cantidad, Medida
                    From Com_Det_Recibo
                    where Cod_Cia   = pCia
                      And Sucursal  = pSuc
                      And Documento = pDoc;

      vArticulo Com_Det_Factura.Cod_Articulo%Type;                
      vNumLinea Com_Det_Factura.Num_Linea%Type;
   Begin
      For R in Recs
      Loop
         For D in Det(R.Cod_Cia, R.Sucursal, R.Documento)
         Loop
	    vArticulo := ObtArticuloDetNacio(R.Cod_Cia, R.Sucursal, R.Referencia, D.Cod_Articulo, D.Fec_Vence);

	    ObtArticuloDetFact(R.Cod_Cia, R.Sucursal, R.Proveedor, R.Num_Factura, vArticulo, 
  	                       D.Fec_Vence, vNumLinea, vArticulo);
  	
            If vNumLinea Is Not Null Then
       	       ActOrdenCompra(R.Cod_Cia, R.Sucursal, R.Proveedor, R.Num_Factura, vArticulo, vNumLinea, 
    	                      -D.Cantidad, 0, D.Medida, Null, Null, Null, Null);
            End If;
         End Loop;
      End Loop;
   End RecNacionalizaciones;

   Procedure CorrigeValores Is
   Begin
      Update Com_Det_Orden D
         Set Cant_Recibida = 0, 
             CantRecepcion = 0
      Where Exists(Select 1 From Com_Enc_Orden E
                   Where E.Cod_Cia   = D.Cod_Cia
                     And E.Sucursal  = D.Sucursal
                     And E.Num_Orden = D.Num_Orden
                     And E.Estado    = 'D');

      Delete from Com_Det_Fac_Orden D
      Where Exists(Select 1 From Com_Enc_Orden E
                   Where E.Cod_Cia   = D.Cod_Cia
                     And E.Sucursal  = D.Sucursal
                     And E.Num_Orden = D.Num_Orden
                     And E.Estado    = 'D');

      Facturas;
      Nacionalizaciones;
      RecFacturas;
      RecNacionalizaciones;
   End CorrigeValores;
     
END OCSx;
/

