3 Replies Latest reply on Jun 14, 2019 8:50 PM by 2623642

    Unable to debug query statement in BefImport.py

    Javi Martinez

      Hello everybody:

       

      I have been working sometimes in the past with BefImport.py and AIF_OPEN_INTERFACE table. Until now I have been able to detect errors when queries didn't work, but this time it is becoming impossible.

       

      I have this code and it works:

       

      if fdmContext["RULENAME"] == "HYP_PL":

          fdmAPI.logInfo("======================================================================")

          fdmAPI.logInfo("Custom Script: Begin")

          fdmAPI.logInfo("======================================================================")

          fdmAPI.logDebug(fdmContext["LOCNAME"])

          vIni = fdmAPI.getPOVStartPeriod(fdmContext["LOADID"])

          vTer = fdmAPI.getPOVEndPeriod(fdmContext["LOADID"])

          fdmAPI.logInfo("=================vDate =====================================================")

          import java.sql as sql

          batchName = str(fdmContext["RULENAME"])

         

          fdmAPI.logDebug("BefImport - Batch: %s" % batchName)

         

          # Set variables

          VarPeriodName = fdmContext["PERIODNAME"]

          VarAnioName = VarPeriodName[-4:]

          VarFYName = VarAnioName[-2:]

          VarFYPrevName = str(int(VarFYName)-1)

          VarAnioNum = int(VarAnioName)

          fdmAPI.logDebug("VarPeriodName: %s" % VarPeriodName)

          fdmAPI.logDebug("VarAnioName: %s" % VarAnioName)

          fdmAPI.logDebug("VarAnioNum: %d" % VarAnioNum)

         

          deleteStmt = "DELETE FROM AIF_OPEN_INTERFACE WHERE BATCH_NAME = 'HYP_PL'"

          insertStmt = """INSERT INTO AIF_OPEN_INTERFACE (BATCH_NAME,YEAR,PERIOD,AMOUNT,COL01,COL02,COL03,COL04,COL05,COL06,COL07,COL08)

          VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"""

         

          selectStmt = "SELECT BATCH_NAME, YEARR, PERIOD, AMOUNT, ACCOUNT, CURRENCY, 'N/A' AS DEPENDENCY, ENTITY, 'N/A' AS FLOW, ICP, 'N/A' AS SECTION, VERSION FROM TEST"

         

          try:

              sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@XXXXXXXX", "YYYY", "ZZZZZ");

              fdmAPI.logDebug("BefImport - Connected OK")

              fdmAPI.logDebug("Deleting AIF_OPEN_INTERFACE table where BATCH_NAME is " + batchName)

              fdmAPI.logInfo(deleteStmt)

              fdmAPI.executeDML(deleteStmt,[], False)

              fdmAPI.commitTransaction()

              fdmAPI.logDebug("Recovering data...")

              fdmAPI.logInfo(selectStmt)

              stmt = sourceConn.prepareStatement(selectStmt)

              stmtRS = stmt.executeQuery()

              fdmAPI.logDebug("Inserting new data in AIF_OPEN_INTERFACE table with BATCH_NAME: " + batchName)

              while(stmtRS.next()):

                  params = [ batchName,

                  stmtRS.getInt("YEARR"),

                  stmtRS.getString("PERIOD"),

                  stmtRS.getBigDecimal("AMOUNT"),

                  stmtRS.getString("ACCOUNT"),

                  stmtRS.getString("CURRENCY"),

                  stmtRS.getString("DEPENDENCY"),

                  stmtRS.getString("ENTITY"),

                  stmtRS.getString("FLOW"),

                  stmtRS.getString("ICP"),

                  stmtRS.getString("SECTION"),

                  stmtRS.getString("VERSION")]

                  fdmAPI.executeDML(insertStmt, params, False)

              fdmAPI.commitTransaction()

          except BaseException, err:

              errMsg = "Error importing data: " + err

              fdmAPI.logDebug(errMsg)

              sourceConn.close()

          finally:

              fdmAPI.logInfo("======================================================================")

              fdmAPI.logInfo("Custom Script: end")

              fdmAPI.logInfo("======================================================================")

       

      But when changing select statement to this:

       

          selectStmt = "SELECT BATCH_NAME, YEARR, PERIOD, AMOUNT, ACCOUNT, CURRENCY, 'N/A' AS DEPENDENCY, ENTITY, 'N/A' AS FLOW, ICP, 'N/A' AS SECTION, VERSION FROM TEST_V"

       

       

      It does not work. TEST is a table, TEST_V is a view whose content is the same as the one in TEST. Furthermore, TEST has been created using CREATE TABLE TEST AS SELECT .... FROM TEST_V. Is there any identified problem with views?. Moreover, script execution fails but message in except clause does not show up in log. I have tried with some exception types as Exception, SQLException,... but they do not work. I have also tried to test directly with the original query (the one behind the view) but fails.

       

      I have two problems: querying from a view does not work; and I am not able to debug the error

       

      Could somebody help me?.

       

      Thank you

       

      Regards

        • 1. Re: Unable to debug query statement in BefImport.py
          user6692921

          Your exception code could be better. This is what I use (import sys and traceback):

           

                      except:

                          fdmAPI.logError("**  Error information: %s %s" % (sys.exc_info()[0], sys.exc_info()[1]))

                          strExcType, strExcValue, strExcTB = sys.exc_info()

                          File, ErrorLineNo, Module, ErrorLineText = traceback.extract_tb(strExcTB)[-1]

           

                          fdmAPI.logError("**  Error on line: [%s] %s" % (ErrorLineNo, ErrorLineText))

                          fdmAPI.logError("**  fdmContext:")

                          for key in sorted(fdmContext):

                              fdmAPI.logError("**    [%s] - %s" % (str(key), str(fdmContext[key])))

                          fdmAPI.logError("**  Variables:")

                          for key, value in sorted(locals().items(), key=lambda s: (s[0].lower(), s[1])):

                              if key != 'fdmContext':

                                  fdmAPI.logError("**    [%s] - %s" % (str(key), str(value)))

          • 2. Re: Unable to debug query statement in BefImport.py
            Javi Martinez

            Hello:

             

             

            Thank you so much. You have save my day. Even my week .  Thanks to your code I have managed to find out the error. It was due to a date comparison that worked in SQL Developer but not when executing with FDMEE. I have fixed it and now everything works flawlessly

             

             

            By the way, your were rigth, my code could be improved.

             

             

            Once again, thank you.

             

             

            Regards

            • 3. Re: Unable to debug query statement in BefImport.py
              2623642

              I too have similar issue. The script works fine in SQL Developer but not in FDMEE:

               

              This script works fine in SQL Developer and FDMEE

              SELECT glc.segment1 Co,

                'ACCUM' Account,

                DECODE(glc.segment1,'2422',(SUM(NVL(ael.accounted_dr,0)) - SUM(NVL(ael.accounted_cr,0))- 100000),

                (SUM(NVL(ael.accounted_dr,0))- SUM(NVL(ael.accounted_cr,0)) ))

                Entries FROM apps.xla_ae_headers aeh, apps.xla_ae_lines ael, apps.gl_code_combinations glc

                WHERE aeh.ae_header_id = ael.ae_header_id AND ael.code_combination_id = glc.code_combination_id

                AND (glc.segment3 BETWEEN '170000' AND '170120' OR glc.segment3 = '170300' OR glc.segment3 = '186600' OR glc.segment3 = '187030' OR glc.segment3 = '284500')

                AND aeh.ledger_id = 2023

                AND ael.accounting_class_code = 'ASSET'

                AND aeh.application_id = 140

                AND ael.accounting_date BETWEEN '01-JAN-2019' AND '31-JAN-2019'

                group by glc.segment1;

               

              The moment I replace the hard coded value "100000" to a query from one of the view, it throws an error in FDMEE (The script has failed to execute:). The script works fine in SQL Developer though.

               

              select glc.segment1 Co, 'ACCUM' Account,

                                  decode(glc.segment1,'2422',(sum(nvl(ael.accounted_dr,0)) - sum(nvl(ael.accounted_cr,0))- (select sum(nvl(fmav.deprn_reserve,0)) from

                                  apps.fa_mass_additions_v fmav where fmav.book_type_code = 'XXX' and fmav.context = 'Conversion')),

                                  (sum(nvl(ael.accounted_dr,0)) - sum(nvl(ael.accounted_cr,0)) )) Entries

                                  from apps.xla_ae_headers aeh, apps.xla_ae_lines ael, apps.gl_code_combinations glc

                                  where aeh.ae_header_id = ael.ae_header_id

                                  and ael.code_combination_id = glc.code_combination_id

                                  and (glc.segment3 between '170000' and '170120' or glc.segment3 = '170300' or glc.segment3 = '186600' or glc.segment3 = '187030' or glc.segment3 = '284500')

                                  and aeh.ledger_id = 2023

                                  and ael.accounting_class_code = 'ASSET'

                                  and aeh.application_id = 140

                                  and ael.accounting_date between '01-JAN-2019' and '31-JAN-2019'

                                  group by glc.segment1;

               

               

              What could be possible issue with this?