5 Replies Latest reply on Mar 8, 2017 10:15 PM by ksdesh

    FDMEE: Period Key Issue

    3150497

      Hi, 

       

      We have a requirement to load the amounts along with the units in the Amount fields suffixing "_U" to the account field.

       

      For example, if we have a record with Account 3000 with amount 5000 and has 3 units then the result records should be 2 records one with 3000 with amount 5000 and other with 3000_U with amount 3. Below is the code we were using and it seems like it doesn't like the period key.

       

      Any help with this will be greatly appreciated!

       

      ============================================================================================================================

      SCRIPT

      ============================================================================================================================

       

      global pvartemp1
      def Sonic_UnitAmt2(strField, strRecord):
         _ret = None
         #------------------------------------------------------------------
         #Financial Data Management DataPump Import Script
         #Created By:        XXXX
         #Date Created:      22/2/2017
         #------------------------------------------------------------------
         strField =strField.strip()
      #    strField =strField.rstrip('-')
         strAccount = strRecord[9:13]
         strAcccount = strAccout.strip()
         strEntity = strRecord[1:4]+strRecord[8:9]
         strEntity = strEntity.strip()
         strICP = '[None]'
         strBrand = strRecord[1:4]
         strBrand =strBrand.strip()
         strDeptRlfwd = strRecord[9:13]
         strDeptRlfwd = strDeptRlfwd.strip()
         strDesc =  strRecord[14:39]
         strDesc = strDesc.strip()
         strSource = 'GL'
         strUD3 = '[None]'
         strCode = strEntity
         intUnit = strRecord[95:103]
         intUnit =intUnit.strip()

         lngPartKey = fdmContext["LOCKEY"]
         lngCatKey = fdmContext["CATKEY"]
         dtePerKey = str(fdmContext["PERIODKEY"])
      #    strLoadID = fdmContext["LOADID"]
         strRuleID = fdmContext["RULEID"]
         pvartemp2 = pvartemp1 + '_' + strAccount
         strAccountU = strAccount + '_U'

      #Temporary FDMEE database table#####################
      #    strTableName = 'TDATASEG_T'
         #global variable
         pvartemp1 = ''
         global pvartemp1
        
         if strField!= '' and strAccount!= '':
             strBrandSQL = 'SELECT tSonicBrandMap.Brand From tSonicBrandMap Where tSonicBrandMap.Code = ' + strCode
      #    strBrandSQL = 'BMW' #fdmAPI.fdmContext["BRAND"]
      #        rsBrandSQL=fdmeeAPI.executeQuery(strBrandSQL, None)
      #        rsBrandSQL = DW.DataAccess.farsKeySet(strBrandSQL)
             rsBrandSQL = fdmAPI.executeQuery(strBrandSQL, None)
             if rsBrandSQL.next():
                 pvartemp1 = rsBrandSQL.getString(1)
             else:
                 pvartemp1 = ''
             fdmAPI.logDebug(str(pvartemp1))
             rsBrandSQL.close()
      #Check for a valid Unit Account and add the _U if necessary ....Routine re-writes the record.
         stmt = """
                INSERT INTO [HYPFDMEE_PRD].[dbo].[TDATASEG_T] (
                 CURKEY
                 ,DATAVIEW
                 ,CALCACCTTYPE
                 ,AMOUNT
                 ,DESC1
                 ,ACCOUNT
                 ,ENTITY
                 ,ICP
                 ,UD1
                 ,UD2
                 ,UD3
                 ,UD4
                 )
               VALUES(
               ?
               ,?
               ,?
               ,?
               ,?
               ,?
               ,?
               ,?
               ,?
               ,?
               ,?
               ,?
              )

      ===============================================================================================================

      ERROR RECORD

      ===============================================================================================================

       

      the error is as below

      2017-02-28 17:05:44,816 DEBUG [AIF]: EPMFDM-140274:Message - compiling script:Sonic_UnitAmt2(strField,strRecord)
      2017-02-28 17:05:44,817 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=DESC1:ImpFieldText=null:StartPos=15:SourceClName=DESC1
      2017-02-28 17:05:44,818 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=SKIP:ImpFieldText=CO:StartPos=1:SourceClName=Skip
      2017-02-28 17:05:44,818 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=SKIP:ImpFieldText=PAGE:StartPos=1:SourceClName=Skip
      2017-02-28 17:05:44,818 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ACCOUNT:ImpFieldText=null:StartPos=10:SourceClName=ACCOUNT
      2017-02-28 17:05:44,819 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD1:ImpFieldText=null:StartPos=10:SourceClName=UD1
      2017-02-28 17:05:44,819 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD3:ImpFieldText=[None]:StartPos=1:SourceClName=UD3
      2017-02-28 17:05:44,819 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD4:ImpFieldText=GL:StartPos=1:SourceClName=UD4
      2017-02-28 17:05:44,819 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD2:ImpFieldText=null:StartPos=8:SourceClName=UD2
      2017-02-28 17:05:44,820 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD2:ImpFieldText=null:StartPos=10:SourceClName=UD2
      2017-02-28 17:05:44,820 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ENTITY:ImpFieldText=null:StartPos=2:SourceClName=ENTITY
      2017-02-28 17:05:44,820 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ENTITY:ImpFieldText=null:StartPos=9:SourceClName=ENTITY
      2017-02-28 17:05:44,821 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ICP:ImpFieldText=null:StartPos=10:SourceClName=ICP
      2017-02-28 17:05:44,821 INFO  [AIF]: EPMFDM-140274:Message - File Name bmw02@bmw02_139@ACTUAL@JAN-2017@RR2.txt
      periodKey2017-01-31
      2017-02-28 17:05:44,826 DEBUG [AIF]: EPMFDM-140274:Message - Error Evaluating scriptnullorg.python.core.PyException.fillInStackTrace(PyException.java:70)
      java.lang.Throwable.<init>(Throwable.java:181)
      java.lang.Exception.<init>(Exception.java:29)

      ============================================================================================================================

       

      Thanks!

       

       

       

       

       

       

       

      ThinkFDM USER1211 JohnGoodwin Dayalan Punniyamoorthy Chuggans-Oracle TomBlakeley

        • 1. Re: FDMEE: Period Key Issue
          Francisco Amores

          I wouldn't insert records from an import script....that's not a good practice

           

          It's better to insert new records in the AftImport event script

           

          Error below is typically raised when the script returns nothing after evaluating it with the parameters passed from each line

           

          2017-02-28 17:05:44,826 DEBUG [AIF]: EPMFDM-140274:Message - Error Evaluating scriptnullorg.python.core.PyException.fillInStackTrace(PyException.java:70)

          • 2. Re: FDMEE: Period Key Issue
            3150497

            Hi Francisco,

             

            Thanks for the reply. The script is somehow skipping the record, we are not sure why this is happening. Can you please share your thoughts.as we don't understand why the script is skipping all records. Example of the script records is below:

             

             

            [SKIP FIELD] CO # ACCT.... DESCRIPTION.............. BALANCE-FORWARD CURRENT-MONTH UNIT-MTD CLOSING-BALANCE UNIT-YTD

            [BLANK]

            [SKIP FROM SCRIPT ]  139    14000 SLS-328                                     133813.00-        3     133813.00         3

            • 3. Re: FDMEE: Period Key Issue
              Francisco Amores

              a good way of tracing this is to have that function in a py file and then use python or notepad++ plugin PyNpp to test it.

              Also make sure that your function returns an amount for the original line. Otherwise the script returns nothing and it fails as you can see

              1 person found this helpful
              • 4. Re: FDMEE: Period Key Issue
                3150497

                Hi Francisco and other Experts,

                 

                Thank you so much! Your reply earlier was very helpful in pointing us to right direction. I have made changes to the sql statement as below however the script still fails to return values

                INSERT INTO [HYPFDMEE_PRD].[dbo].[TDATASEG_T] (

                           CURKEY

                           ,DATAVIEW

                           ,CALCACCTTYPE

                           ,AMOUNT

                           ,DESC1

                           ,ACCOUNT

                           ,ENTITY

                           ,ICP

                           ,UD1

                           ,UD2

                           ,UD3

                           ,UD4

                           ,PARTITIONKEY

                           ,CATKEY

                           ,PERIODKEY

                           ,RULE_ID

                           ,LOADID

                           ,VALID_FLAG

                           )

                         VALUES(

                         ?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                         ,?

                        )

                       

                         """

                 

                   params = [

                          'USD'

                         ,'YTD'

                         ,'9'

                         ,intUnit

                         ,strDesc

                         ,strAccountU

                         ,strEntity

                         ,strICP

                         ,pvartemp2

                         ,strDeptRlfwd

                         ,strUD3

                         ,strSource

                         ,lngPartKey

                         ,lngCatKey

                         ,dtePerKey  

                         ,strRuleID

                         ,strLoadID

                         ,'Y'

                        ]

                               

                I have tried debugging and noticed that it fails at fdmAPI.executeDML(stmt, params, False) and also noticed that the period key value in log is DEBUG [AIF]: dtePerKey Tue Jan 31 00:00:00 EST 2017

                Am I miss something? The same code executes fine in Eclipse IDE without any issues. Please shed some light and once again thank you for your expert advice.

                1 person found this helpful
                • 5. Re: FDMEE: Period Key Issue
                  ksdesh

                  If your fdmAPI.executeDML is in a function , then you need to pass the fdmAPI to that function. I have had similar issues, where the code runs in eclipse, but not in FDMEE event scripts. Works fine when I pass the fdmAPI as a parameter to the function.

                  1 person found this helpful