5 Replies Latest reply on May 23, 2017 9:06 AM by 3243283

    FDMEE BefImport to Open Interface Issue

    3243283

      Dear All,

       

      I am having a bad time with this (see script at the bottom):

       

      If I manually set the variable PoVAnno to 2016 (PoVAnno = "2016") everything is working fine.

       

      Instead, If I try to parametrize it as follows:

       

      PovPer = str(fdmContext["PERIODNAME"])

      PoVAnno = str(PovPer[6:10])

       

      Everything seems to be messed up:

       

      1. OIT is empty;

      2. TDATASEG empty  too as consequence;

      3. FDMEE Process Log shows that "no data exists for period...." while

      4...ODI shows that everything is fine!

       

      Please HELP!!!

      and by the way, does anybody know how the "PERIODNAME" is encoded? Is it depending on the language of the users setings or the browser?

       

      Matny thanks!!!

       

      if fdmContext["LOCNAME"] == "XXXXXX":

              import java.sql as sql

              batchName = "XXXXX"

              Scen = str(fdmContext["CATNAME"])

              PovPer = str(fdmContext["PERIODNAME"])

              PovPerKey = str(fdmContext["PERIODKEY"])

              PoVAnno = str(PovPer[6:10])

              #Tentativo2 --> PovAnno = str(PovPerKey[6:10])

              #Tentativo3 --> PovAnno = int(PovPerKey[:4])

              PoVPeriodo = PovPer[:3]

              if PoVPeriodo=='Gen':

                      Mese='Jan'

                      MeseN='01'

              elif PoVPeriodo=='Feb':

                      Mese='Feb'

                      MeseN='02'

              elif PoVPeriodo=='Mar':

                      Mese='Mar'

                      MeseN='03'

              elif PoVPeriodo=='Apr':

                      Mese='Apr'

                      MeseN='04'

              elif PoVPeriodo=='Mag':

                      Mese='May'

                      MeseN='05'

              elif PoVPeriodo=='Giu':

                      Mese='Jun'

                      MeseN='06'

              elif PoVPeriodo=='Lug':

                      Mese='Jul'

                      MeseN='07'

              elif PoVPeriodo=='Ago':

                      Mese='Aug'

                      MeseN='08'

              elif PoVPeriodo=='Set':

                      Mese='Sep'

                      MeseN='09'

              elif PoVPeriodo=='Ott':

                      Mese='Oct'

                      MeseN='10'

              elif PoVPeriodo=='Nov':

                      Mese='Nov'

                      MeseN='11'

              else:

                      Mese='Dic'

                      MeseN='12'

              insertStmt = """

                          INSERT INTO AIF_OPEN_INTERFACE (

                          BATCH_NAME

                          ,COL01

                          ,AMOUNT

                          ,COL02

                          ,COL03

                          ,COL04

                  ,YEAR

                  ,COL06

                          ,PERIOD

                  ,PERIOD_NUM

                          ) VALUES (

                          ?

                          ,?

                          ,?

                          ,?

                          ,?

                          ,?

                          ,?

                  ,?

                  ,?

                  ,?

                          )

                          """

              sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=YYYY)(HOST=YYYYYYYYYYYYYY)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TTTTT)))", "OOOOO", "OOOOOO");

              selectStmt = "SELECT SUBSTR(ACCOUNT,6,70) AS CONTO,AMOUNT AS IMPORTO,ENTITY,ICP,C1,YEAR,C3 FROM TTTTTTTT.TTTTTTT where ENTITY IN ('AAA','BBB','cccc') AND SCENARIO = '"+Scen+"' AND PERIOD = '"+Mese+"' AND YEAR = '"+PoVAnno+"'"

              stmt = sourceConn.prepareStatement(selectStmt)

              stmtRS = stmt.executeQuery()

              while(stmtRS.next()):

                          params=[batchName, stmtRS.getString("CONTO"),

                                  stmtRS.getBigDecimal("IMPORTO"),

                                  stmtRS.getString("ENTITY"),

                                  stmtRS.getString("ICP"),

                                  stmtRS.getString("C1"),

                                  stmtRS.getString("YEAR"),

                                  stmtRS.getString("C3"),

                                  PoVPeriodo,

                                  MeseN]

                          fdmAPI.executeDML(insertStmt, params, False)

              fdmAPI.commitTransaction()

              stmtRS.close()

              stmt.close()

              sourceConn.close()