Blocking of BP Reference Duplicate in AR Invoice

[mervz02]

Hi Experts,

Currently the Standard for blocking customer ref is not working for our SAP B1 9.3 version, Would like to add an SP Notification on it in A/R Invoice, i created a query for this its working fine but suddenly it block the whole A/R Invoice even the reference number is not yet added in the system this is my code

IF @object_type = '13' and @transaction_type = 'A' -- AR invoice

BEGIN

DECLARE @Invoice AS VARCHAR(15) DECLARE @Card AS VarChar (20) SELECT @invoice = NumatCard, @card = CardCode FROM dbo.OINV WHERE DocEntry = @list_of_cols_val_tab_del

-- AR Invoice to check if the invoice no exist --

If exists (SELECT T0.cardcode, T0.NumatCard FROM OINV T0 where T0.NumatCard = @invoice and T0.CardCode=@card)

begin SET @error = 10

SET @error_message = N'This invoice number already exist for this supplier.'

end

END ----

[Johan_Hakkesteegt · Accepted solution]

Hi Mervin,

Could you please test this:

IF @object_type = '13' and @transaction_type = 'A' -- AR invoice
 BEGIN
  DECLARE @Invoice AS VARCHAR(15) 
  DECLARE @Card AS VarChar (20) 

  SELECT @invoice = NumatCard
       , @card = CardCode 
  FROM dbo.OINV 
  WHERE DocEntry = @list_of_cols_val_tab_del

  -- AR Invoice to check if the invoice no exist --
  If exists (SELECT T0.cardcode, T0.NumatCard
             FROM OINV T0 
             WHERE T0.NumatCard = @invoice 
               and T0.CardCode = @card 
               and not T0.DocEntry = @list_of_cols_val_tab_del)
   begin 
    SET @error = 10
    SET @error_message = N'This invoice number already exists for this supplier.'
   end
 END

Regards,

Johan

[mervz02 · Accepted solution]

Hi Johan,

the code works fine but it only works if the same customer with the same bp reference, i want across all Business Partner/Customer not to be duplicated regardless of customer the BP Reference Number should be use once in all Customers.

[Johan_Hakkesteegt · Accepted solution]

Hi Mervin,

Ok, that is easy enough. Please try this:

IF @object_type ='13' and @transaction_type ='A'-- AR invoice
 BEGIN
  DECLARE @NumAtCard AS NVARCHAR(100)
  SELECT @NumAtCard = NumAtCard
  FROM dbo.OINV 
  WHERE DocEntry = @list_of_cols_val_tab_del

  -- AR Invoice to check if the invoice no exist 
  If exists(SELECT T0.NumatCard
            FROM OINV T0 
            WHERE T0.NumatCard = @NumatCard
               and not T0.DocEntry = @list_of_cols_val_tab_del)
   begin
    SET @error = 10
    SET @error_message = N'This BP invoice number already exists.'
   end
 END

However, if this invoice number is provided by the BP, how can you be sure that two different BPs will not, at some point, inadvertently use the same invoice number?

Regards,

Johan

[mervz02 · Accepted solution]

Hi Johan Thanks,

Already put it in the system for testing, by the way may i ask what is wrong in my code? is my logic incorrect?

[Johan_Hakkesteegt · Accepted solution]

Hi Mervin,

There is nothing really wrong with your code, but by also determining:

and T0.CardCode = @card

you limited the result to invoices for that BP only. The original error message confirmed that logic.

When I took out that line of code, the @card variable became obsolete. So my version of your code looks different only because I took that line and the variable out, and I changed the error message, because it was a bit misleading, and could have caused confusion, and it matched with the original logic, not with what you asked.

Then I added:

and not T0.DocEntry = @list_of_cols_val_tab_del

because without this, the sub query result also contained the invoice being entered at that moment. This is because the system uses the sql transaction method:

1. User enters data in GUI, and presses 'Add'
2. The client starts a new sql transaction
3. Data is entered in the OINV, INV1, etc tables
4. the stored procedure is run, and thus the logic in your code
5. the logic in your code, checks the OINV table (which at this point also contains the data the user just entered)
6. in case the error code is zero (0) the transaction is committed and the user is shown the green success message
7. however, if the error code is not zero (0), the transaction is rolled back (the data gets removed again from OINV, INV1, etc) and the user is shown the red error message.

Okay, and I tidied it up a bit, to make it easier to read/debug but that wasn't actually necessary.

Regards,

Johan

[mervz02 · Accepted solution]

Hi Johan,
Thank you for the explanation, now i get it, and where using it already, but there is instances that we cannot cancel the document (A/R Cancellation) even we change the BP Reference in A/R Invoice its says the BP Invoice number is already exist.

[Johan_Hakkesteegt · Accepted solution]

Hi Mervin,

Unfortunately our B1 version does not have the A/R Cancellation functionality yet, so I am not sure why this happens.

Can you please explain what happens when you cancel an invoice? Does the system create a "negative" invoice, or is a Credit Memo posted, or what?

Regards,

Johan

[mervz02 · Accepted solution]

Hi Johan,

When we cancel the document it creates automatically a cancellation document as well as respective journal entry to reflect the cancellation. its like credit memo but instead we cancel the document so that the goods or the documents will be return.

[Johan_Hakkesteegt · Accepted solution]

Hi Mervin,

Could you please open such a cancellation document, and check what database table is used (View > System Information > then hover mouse cursor over NumAtCard field, and look at the bottom left information bar)?

My guess is, that it is the OINV table. In which case we need to find out how a cancellation document is different from a normal invoice in that table. There is probably a field that determines which, or as I theorized earlier, it may be that the totals are negative.

Once we figure it out, we can adjust the query to not look at cancellation documents.

Regards,

Johan