Credit Limit SP Transnotification

[Former Member]

Hi, we want to setup a SP Trans-notification such that AFTER adding the delivery note or Invoice this formula should hold:

Credit Limit>= Account balance + Delivery balance.--- this is the end result we want.

The account balances and delivery balances are the presumed balances AFTER the document is added i.e. if the document is allowed to be added at all.

We wrote this query, which doesn't seem to be working all that well when using the copy from return or Copy Delivery to A/R Invoice. Kindly assist in correcting the query.

---------------------------------------------------------------------------------

---For Delivery Credit Limit ------------------

IF @object_type='15' and(@transaction_type='A' or @transaction_type='U')

Begin

  Declare @AccntBalDLN as Numeric(18,2)

  Declare @DlnDocTotal as Numeric(18,2)

  Declare @CreditLimitBp as Numeric(18,2)

  Declare @resultDln as Numeric(18,2)

  Set @DlnDocTotal = Isnull((Select Doctotal from ODLN T0 Where T0.DocEntry = @list_of_cols_val_tab_del),0)

  Set @AccntBalDLN = Isnull((Select (T1.Balance+t1.DNotesBal) from ODLN T0 Left Outer Join OCRD T1 On t0.CardCode = T1.CardCode Where T0.DocEntry = @list_of_cols_val_tab_del),0)-@DlnDocTotal

  Set @CreditLimitBp = Isnull((Select T1.CreditLine from ODLN T0 Left Outer Join OCRD T1 On t0.CardCode = T1.CardCode Where T0.DocEntry = @list_of_cols_val_tab_del ),0)

  IF(@CreditLimitBp<=(@AccntBalDLN+@DlnDocTotal))

  Begin

  Set @error='-1'

  Set @error_message = 'Amount Is greater Than Credit Limit  '

  End

End

-------------------------------------------------------

--------------For Invoice Credit Limit

IF @object_type='13' and(@transaction_type='A' or @transaction_type='U')

Begin

  Declare @AccntBalInv as Numeric(18,2)

  Declare @InvDocTotal as Numeric(18,2)

  Declare @BaseDocTotal as Numeric(18,2)

  Declare @CreditLimitcust as Numeric(18,2)

  If Exists(Select * From OINV T0  Where T0.GroupNum In (3,4,5,6,7,8) and T0.Docentry =@list_of_cols_val_tab_del)

  Begin

  Set @InvDocTotal = Isnull((Select Doctotal from OINV T0 Where T0.DocEntry = @list_of_cols_val_tab_del),0)

  Set @AccntBalInv = Isnull((Select (T1.Balance+T1.Dnotesbal) from OINV T0 Left Outer Join OCRD T1 On t0.CardCode = T1.CardCode Where T0.DocEntry = @list_of_cols_val_tab_del),0)---@InvDocTotal

  Set @BaseDocTotal = Isnull((Select top 1 T0.DocTotal from ODLN T0 Inner Join DLN1 T1 On T0.DocEntry = T1.DocEntry Left Outer Join INV1 T2 On T1.DocEntry = T2.BaseEntry and T1.LineNum = T2.BaseLine And T1.Objtype = T2.BaseType Where T2.DocEntry = @list_of_cols_val_tab_del),0)

  Set @CreditLimitcust = Isnull((Select T1.CreditLine from OINV T0 Left Outer Join OCRD T1 On t0.CardCode = T1.CardCode Where T0.DocEntry = @list_of_cols_val_tab_del ),0)

  IF(@CreditLimitcust<=(@AccntBalInv+@InvDocTotal-@BaseDocTotal))

  Begin

  Set @error='-1'

  Set @error_message = 'Amount Is greater Than Credit Limit  '

  End

  End

End

--

--------------------------------------------------------------------------------------------------------------------------------

[luisdarui]

Hi Veer,

With SAP NetWeaver it is recommended to use ABAP code instead writing stored procedures and triggers for such activity.

Regards, Luis