Problem with Debit Memo in sp_TransactionNotification

[Former Member]

Hello.

I need block the Invoice creation if not have a Sales Order previus. I modified the sp_TransactionNotification:

declare @error int

declare @error_message nvarchar (200)

DECLARE @DocEntry as nvarchar(255)

select @error = 0

select @error_message = N'Ok'

IF @object_type = '13' AND @transaction_type= 'A' AND

BEGIN

SET @DocEntry = @list_of_cols_val_tab_del

IF (SELECT count(*) FROM dbo.RDR1 T0

INNER JOIN dbo.ORDR T1 ON T1.DocEntry = T0.DocEntry

WHERE T0.TrgetEntry = @DocEntry) = 0

BEGIN

SET @Error = 1

SET @error_message = 'ALA-001 No Existe Pedido Previo'

END

END

but the problem is that block Credit Memo creation too. I know that the object type for the invoice and the credit memo is the same, but ¿how make reference at the DocSubType in the sp_TransactionNotification.

thanks in advance.

Oscar

[former_member583013 · Accepted solution]

Oscar,

From my understanding of your different message posts I have modified your Stored Procedure.

In your business is the process flow Sales Order > AR Invoice?

Or are your processing a Delivery before AR Invoice?

Please check the following:

DECLARE @DocSubType as nvarchar(2)

IF @object_type = '13' AND @transaction_type= 'A'

BEGIN

SELECT @DocSubType = DocSubType FROM dbo.OINV

WHERE DocEntry = @list_of_cols_val_tab_del

IF @DocSubType != 'DN'

BEGIN

IF (SELECT COUNT(T1.DocNum) FROM dbo.RDR1 T0

INNER JOIN dbo.ORDR T1 ON T1.DocEntry = T0.DocEntry

WHERE T0.TrgetEntry = @list_of_cols_val_tab_del ) = 0

BEGIN

SET @Error = 1

SET @error_message = 'ALA-001 No Existe Pedido Previo'

END

END

END

Suda

[Former Member · Accepted solution]

Thanks at all.

Suda thanks again.

Oscar

[former_member583013 · Accepted solution]

Oscar,

I am glad it worked,

For the user to see the message use the syntax. Instead of 2 SET STATEMENTS use the one below

SELECT @Error = 1, @error_message = 'ALA-001 No Existe Pedido Previo'

[former_member186095]

I wonder why you said that objtype of credit memo as same as invoice.

If you refer to A/R credit memo, the object type is 14, meanwhile the object type of invoice (OINV) is 13.

The Sp_TN you have made did not touch OINV.

Try to use this one:

IF @transaction_type in ('A, 'U') AND @object_type = '13'
BEGIN
IF EXISTS (SELECT T0.DocNum from
[dbo].[INV1]  T0 inner join DLN1 T1 on t1.docentry =
 t0.baseentry inner join  RDR1 T2 on t2.docentry =
 t1.baseentry inner join oinv t3 on t3.docentry = 
t0.docentry
where t0.basetype = '15' and t1.basetype = '17'
AND T0.ItemCode = T1.ItemCode AND T0.BaseLine = 
T1.LineNum
and T0.DOCENTRY = @list_of_cols_val_tab_del
begin
select @Error = 1, @error_message = 'ALA-001
No Existe Pedido Previo'
end
end

Rgds,

[Former Member]

Hi.

if you read the message it's a Debit Memo not A/R Credit Memo, I know that the credit memo is diferent, but if you see the table OINV, SAP BO create a record with the same information that a invoice.

the field DocSubType make the diference.

Thanks.

[Former Member]

sorry the problem is with the Debit Memo creation.

[former_member186095]

In my B1 database localization, the objtype of purchase credit memo is 19. Here, debit memo = purchase credit memo.

So, what is your database localization ?

[Former Member]

well.

the problem is that the A/R invoice and the A/R Debit Memo is the same, the same @object_type = '13' and the same @transaction_type= 'A'. I need differentiate in the sp_TransactionNotification when is A/R invoice or A/R Debit Memo, there is a field named DocSubType whith following values:

'--' A/R Invoice

'DN' A/R Debit Memo

'IE' A/R Invoice Exempt

'IB' A/R Bill

'EB' A/R Exempt Bill

'IX' A/R Export Invoice

'IR' A/R Invoice & Receipt

'RI' A/R Reserve Invoice

I need validate these values in sp_TransactionNotification, actually this is my code:

--declare @DocEntry int

DECLARE @DocEntry as nvarchar(255)

DECLARE @DocSubType as nvarchar(2)

select @error = 0

select @error_message = N'Ok'

-- VALIDA QUE LA FACTURA TENGA UN PEDIDO PREVIO

IF @object_type = '13' AND @transaction_type= 'A'

BEGIN

SET @DocEntry = @list_of_cols_val_tab_del

SET @DocSubType = @list_of_cols_val_tab_del

IF (SELECT count(*) FROM dbo.RDR1 T0

INNER JOIN dbo.ORDR T1 ON T1.DocEntry = T0.DocEntry

WHERE T0.TrgetEntry = @DocEntry) = 0 and @DocSubType != 'ND'

BEGIN

SET @Error = 1

SET @error_message = 'ALA-001 No Existe Pedido Previo'

END

END

please help me, thanks in advance

[former_member186095]

Oscar,

could you provide me the result from this query :

select lawsset from CINF and

select distinct versionnum from oinv ? you could run one by one or both.

Rgds,

[Former Member]

Hi, Jimmy

lawsset GT

version number from oinv

6.80.319.31

6.80.319.23

6.80.318.10

rgds