I need a FMS for Auto G/L account for Creating a customer & Restriction of Wrong GL Account Stored Procedure

[Former Member]

Hi ,

Our company having branches like Bangalore , Chennai , Mumbai , Pune

1)Auto FMS query required as mentioned below...

If the customer is from Bangalore region then customer will be created with foreign name Bangalore & G/L Account for Accounts receivable, AR Down payment clearing account must be selected for Bangalore.

2)Stored Procedure is required as mentioned below.

If the customer is selected with foreign name Bangalore & Accounts receivable & AR Down payment clearing account is selected for Pune , Mumbai , Chennai then customer should not able to create..

Pls find the attached image below for reference

Regards,

K.Ramasamy

[former_member1269712]

Hi K.Ramasamy,

Why you are creating SP??

Same feature is there in standard system. Have you checked with Advance GL Determination, if not please try it your problem will resolve and no need to write and maintain it.

Regards,

Sachin

[isaac_kalii]

Hi Ramasamy,

Did solution work for you? Please close thread with correct/helpful answer if so.

Regards,

Isaac K.

[Former Member]

Can anybody guide me for creating stored procedure

Regards,

K.Ramasamy

[isaac_kalii]

Hi Ramasamy,

Please try the below code:

IF @transaction_type IN ('A') AND @Object_type = '2'

BEGIN

IF EXISTS(SELECT docentry FROM ocrd WHERE DebPayAcct NOT IN ('input the account code') and right(cardcode,6) = right(@list_of_cols_val_tab_del,6))

BEGIN

SELECT @Error = 1, @error_message = 'Invalid Account Selected'

END

END

NOTE: On the section 'right(cardcode,6)' you  can specify the number of characters that you use in your master data.

Regards,

Isaac K.

[former_member212181]

Hi Ramasamy,

You can use below query (by making changes in your actual Account codes) for Formatted search.

Declare @CtrlAcct varchar (20), @ForeignNam varchar (100)

Select  Top 1 @CtrlAcct = $[$56.0.0]

Select  @ForeignNam = $[$128.0.0]

Select

  Case @ForeignNam

  When 'Bangalore' then '140000'

  When 'Chennai' then '140010'

  When 'Mumbai' then '140030'

  When 'Pune' then '140060'

  else @CtrlAcct

  end [CtrlAcct]

I am not familiar with writing TN. Hope someone else will help you.

Thanks

Unnikrishnan

[Former Member]

Thanks for ur response Unni