Skip to Content
avatar image
Former Member

Store procedure

Como estan

Necesito saber de que manera realizar un sp que controle que no se puedan crear lotes repetidos para dos grupos de articulos, esto en las entradas de mercancia m uchas gracias por la ayuda

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Aug 28, 2011 at 02:21 AM

    Hola mi correo es

    sap_colombiayahoo.com;sap_colombiayahoo.es

    disculpa falta la arroba

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hola prueba este SP

      IF @transaction_type IN ('A','U') AND @object_type = 20
      BEGIN
      	IF EXISTS (	SELECT T0.ItemCode
      			FROM PDN1 T0
      			INNER JOIN OBTN T1 ON T1.ItemCode=T0.ItemCode
      			INNER JOIN OITM T2 ON T2.ItemCode=T0.ItemCode
      			WHERE T0.DocEntry=@list_of_cols_val_tab_del AND T2.ItmsGrpCod IN ('01','02')/*Aqui coloca los grupos de articulos que quieres*/ AND T1.DistNumber IN (	SELECT DISTINCT A.DistNumber
      																						FROM OBTN A
      																						WHERE A.ItemCode=T0.ItemCode	))
      	BEGIN
      		SELECT @error = -1001, @error_message = N'Lote ya existe, no puede crear lotes ya existentes'
      	END
      END
      

      Saludos

      El @list_of_cols_val_tab_del es el campo de llave que posee cada tabla, por ejemplo el campo llave de la tabla OCRD es el CardCode, se usa para hacer referencia al documento que estas guardando. Si te fijas en SP de arriba se usa el DocEntry como el campo llave ya que es el unico campo que es unico por documento.

      Revisa las tablas en el Management Studio los campos de llave de las tablas, ese es el campo que puedes relacionar con el @list_of_cols_val_tab_del.

  • Aug 27, 2011 at 07:13 AM
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 27, 2011 at 04:50 PM

    Yo ya identifique que en efecto en la tabla de Maestros de Numero de Serie puedo ingresar el mismo numero de serie para diferentes productos pero ahora quiero es que a travez del SP no me permite realizar esa asignacion de lostes aqui tengo mas o menos en sql pero no se adaptarlo al SP

    SELECT COUNT (*)

    FROM [dbo].[OBTN] T0

    WHERE T0.[DistNumber] = 'TTUTIU'

    group by t0.ItemCode,T0.SysNumber,T0.DistNumber,T0.MnfSerial,T0.LotNumber,

    T0.ExpDate,T0.MnfDate,T0.InDate

    having COUNT (*)>0

    LA CONSULTA ME MUESTRA LOS DOS REGISTROS DONDE SE HA UTILIZADO LA MISMA SERIE PARA DIFERENTES PRODUCTOS ES MAS ME GUSTARIA SABER PARA QUE ES LA OPCION DE @list_of_cols_val_tab_del no lo entiendo

    Gracias

    Add comment
    10|10000 characters needed characters exceeded

    • @objecttype

      2 (Business Partner)

      Since version 2005 it returns a string representing the type of object u2013 used to be a numerical value in previous versions.

      A full list of object types exposed through DI API can be found in the DI API documentation under the section Enumerations. The enumeration is called BoObjectTypes.

      @transaction_type

      U (update)

      It may have values of:

      ��

      A (where a record was added)

      ��

      U (where a record was updated)

      ��

      D (where a record was deleted),

      ��

      C (where a document was canceled)

      ��

      L (where a document was closed)

      @num_of_cols_in_key

      1

      Returns the number of columns or fields in the key to the record.

      A Business Partner, for example, has a key consisting of a single field (CardCode), so this variable would have a value of "1". A Special Prices object, however, has a key consisting of the CardCode and ItemCode, so for a Special Prices object this variable would have a value of "2".

      @list_of_key_cols_tab_del

      CardCode

      It returns a tab-delimited list of column names (field names) that represent the object key.

      For a Business Partner, this would be "CardCode". For a Special Prices object, it would be "CardCode ItemCode".

      @list_of_cols_val_tab_del

      C40001

      This returns a tab-delimited list of values required to retrieve the object from Business One.

      For example, a Business Partner record might have a value of "C40001". A Special Prices object would contain 2 fields separated by a tab character. For example: "V10005 ... A00001"

      a mi me paso alguien un documento si tu me das tu correo te lo paso tambien. 😀!