Forum Stats

  • 3,836,748 Users
  • 2,262,180 Discussions
  • 7,900,094 Comments

Discussions

How to export Current POV data from BefImoprt Event Script

ORAHYPFDM
ORAHYPFDM Member Posts: 372 Blue Ribbon
edited Feb 23, 2016 5:31AM in Financial Data Management

How to export Current POV data from BefImoprt Event Script

Hello Experts,

I am importing data from SWL DB tables to HFM. In order to achieve this, I am Open Interface Adapter and trying to populate AIF table using BefImport Script.

When I am trying to load data for period APR15, data for MAY15, JUN15, JUL15 and so on(all periods) getting populated in AIF table. Because of this, when I export it, data for all periods getting loaded into HFM.

May I know How to achieve this purpose? I want to load only current POV data to HFM.

I am writing following script:

if (fdmContext["LOCNAME"] == "LOC_OIA_EXCHG_CLO"):

insertStmt = """

INSERT INTO AIF_OPEN_INTERFACE (

BATCH_NAME

,YEAR

,PERIOD

,PERIOD_NUM

,CURRENCY

,COL07

,COL08

,COL09

,AMOUNT

) VALUES (

?

,?

,?

,?

,?

,?

,?

,?

,?

)

"""

sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@dsad.dsde.com:1541:trt", "apps", "apps");

selectStmt = "select UG_HFM_GL_DAILY_RATES_T1.year,UG_HFM_GL_DAILY_RATES_T1.period_name,UG_HFM_GL_DAILY_RATES_T1.period_num,'[None]' AS CURRENCY,'Rate_Clo' AS SEGMENTN,UG_HFM_GL_DAILY_RATES_T1.from_currency, UG_HFM_GL_DAILY_RATES_T1.to_currency, UG_HFM_GL_DAILY_RATES_T1.conversion_rate from UG_HFM_GL_DAILY_RATES_T1 union all select UG_HFM_GL_DAILY_RATES_T1.year,UG_HFM_GL_DAILY_RATES_T1.period_name,UG_HFM_GL_DAILY_RATES_T1.period_num,'[None]' AS CURRENCY,'Rate_Avg' AS SEGMENTN,UG_HFM_GL_DAILY_RATES_T1.from_currency, UG_HFM_GL_DAILY_RATES_T1.to_currency, UG_HFM_GL_DAILY_RATES_T1.avg_rate from UG_HFM_GL_DAILY_RATES_T1"

stmt = sourceConn.prepareStatement(selectStmt)

stmtRS = stmt.executeQuery()

batchName = "Batch_" + str(fdmContext["LOCNAME"])

while(stmtRS.next()):

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

stmtRS.getString("PERIOD_NAME"),

stmtRS.getString("PERIOD_NUM"),

stmtRS.getString("CURRENCY"),

stmtRS.getString("FROM_CURRENCY"),

stmtRS.getString("TO_CURRENCY"),

stmtRS.getString("SEGMENTN"),

stmtRS.getBigDecimal("CONVERSION_RATE")]

  fdmAPI.executeDML(insertStmt, params, False)

  fdmAPI.commitTransaction()

stmtRS.close()

stmt.close()

sourceConn.close()

Tagged:

Answers

  • Balakumar7
    Balakumar7 Member Posts: 77
    edited Feb 22, 2016 12:19PM

    Hi,

    You can restrict your periods by choosing the correct period in the data load rule run execution window.

    Thanks,

    Bala

  • SH_INT
    SH_INT Member Posts: 3,192 Bronze Crown
    edited Feb 23, 2016 5:31AM

    You can restrict your periods via the data load rule as Balkumar7 suggested but for this to work you need to ensure that you that you are using explicit period maps. Your other option is to pass the year and period from the POV as filters to the SELECT statement in your BefImport script so you only insert the specific data you are interested in to the Open Interface table. fdmContext["PERIODKEY"] and fdmContext["PERIODNAME"] would return the current POV values for you to work with

This discussion has been closed.