Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • May 18, 2015 at 08:39 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 25, 2015 at 07:55 AM

    Can anybody guide me for creating stored procedure

    Regards,

    K.Ramasamy

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • avatar image
    Former Member
    May 26, 2015 at 12:40 PM

    Hi Ramasamy,

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

    Regards,

    Isaac K.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 28, 2015 at 07:34 AM

    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

    Add comment
    10|10000 characters needed characters exceeded