Forum Stats

  • 3,759,066 Users
  • 2,251,495 Discussions
  • 7,870,479 Comments

Discussions

Period Source Mapping in FDMEE

745408
745408 Member Posts: 133
edited Jul 21, 2015 10:00AM in Financial Data Management

Hello,

I am using a custom SQL DB as a source for myFDMEE and stuck in few query , wondering if anyone can suggest ?

1- for period Mapping, do i need to have a calender (Period) pre-defined  in SQL ? if yes then how can i do that ?

2-I have data columns (Jan-De) In source Table, so in the BefImport Script how should i insert values (Jan-Dec) into AIF Amount Columns ?  i can insert Jan column value into AMOUNT column of AIF open Interface Table when selecting "Period Mapping Type" as None .

Please suggest if anyone have any idea . Much Appreciated !

Thanks

Tagged:
ORAHYPFDM

Best Answer

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown
    edited May 5, 2015 6:36AM Accepted Answer

    Hi,

    open interface adapter table does not have multiple columns for periods so data is stored in a similar way than FDMEE.

    there are two approaches:

    1) you only keep current period data in open interface table.

    2) you keep all data for all periods in the open interface table

    For 1)

    you can make your befimport script to import data from your custom sql table only for the POV period.

    you can get the period from fdmContext and use it as a filter in your SQL query to get the data

    with this option you can use None in period mapping type as you don't need to use source period mappings: you have current POV period data in your open interface table

    For 2)

    this is typically used when the open interface table is populated from an external source where they load data for all periods into the table. THen you need to extract only your current pov period data.

    For that you can use Source Period Mappings.

    With this option you don't need any befimport script as the interface table is populated already.

    If you want to insert data for all periods into the open interface table, you need to PIVOT the table so:

    Acc1;1;2;3;4;5;6;7;8;9;10;11;12 where you have 12  values (example 1 to 12) is converted into:

    Acc1;Jan;1

    Acc1;Feb:2

    Acc1;Mar;3....

    then you can use source period mappings to make the open interface adapter import only the data for the periods you set in the data load rule execution (either the pov period or a range of periods)

    i hope that clarifies.

    745408

Answers

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown
    edited May 5, 2015 6:36AM Accepted Answer

    Hi,

    open interface adapter table does not have multiple columns for periods so data is stored in a similar way than FDMEE.

    there are two approaches:

    1) you only keep current period data in open interface table.

    2) you keep all data for all periods in the open interface table

    For 1)

    you can make your befimport script to import data from your custom sql table only for the POV period.

    you can get the period from fdmContext and use it as a filter in your SQL query to get the data

    with this option you can use None in period mapping type as you don't need to use source period mappings: you have current POV period data in your open interface table

    For 2)

    this is typically used when the open interface table is populated from an external source where they load data for all periods into the table. THen you need to extract only your current pov period data.

    For that you can use Source Period Mappings.

    With this option you don't need any befimport script as the interface table is populated already.

    If you want to insert data for all periods into the open interface table, you need to PIVOT the table so:

    Acc1;1;2;3;4;5;6;7;8;9;10;11;12 where you have 12  values (example 1 to 12) is converted into:

    Acc1;Jan;1

    Acc1;Feb:2

    Acc1;Mar;3....

    then you can use source period mappings to make the open interface adapter import only the data for the periods you set in the data load rule execution (either the pov period or a range of periods)

    i hope that clarifies.

    745408
  • 745408
    745408 Member Posts: 133
    edited May 14, 2015 5:36AM

    Thanks Amores ! this is helpful.

    However to achive this , I brought the souce data in single amount column , and in BefImport intigration script , i inserted period, period number, year..UDA 1..UDAn..and Amount..so this code populated the AIF table and i also got data for each month

    Thanks

    Vivek

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown
    edited May 14, 2015 5:55AM

    Cool,

    please then close this thread to mark it as answered so others can see its feedback.

    Thanks

  • 745408
    745408 Member Posts: 133
    edited May 14, 2015 6:13AM

    Thanks Amores for your quick suggetions ! it really helps a lot !

  • 900570
    900570 Member Posts: 3
    edited Jul 21, 2015 10:00AM

    Hi Amores,

    I dont have the period mappings defined and using the first method because i am updating limited data in the AIF_OPEN_INTERFACE table based on the POV period.

    All the process goes fine but there is no data in the tadataseg_T, not sure what does wrong.

    Using following script

    import java.text.SimpleDateFormat as SimpleDateFormat

    import java.sql as sql

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

      strPOVPer=fdmContext["PERIODNAME"]

      strPer= str(fdmContext["PERIODNAME"])

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

      insertStmt = """

      INSERT INTO AIF_OPEN_INTERFACE (

      Amount

      ,Batch_Name

      ,COL01

      ,COL02

      ,COL03

      ,Currency

      ,Period

      ) VALUES (

      ?

      ,?

      ,?

      ,?

      ,?

      ,?

      ,?

      )

      """

      sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@vmohsards020.oracleoutsourcing.com:10710:dardsi", "Apps", "W9cQG0lc");

      # Limiting number of rows to 5 during the test runs.

      selectStmt = "SELECT * From XXARC_EXCH_RATES_FDMEE_HP_VW Where CONVERSION_MONTH = '" + strPer + "'"

      stmt = sourceConn.prepareStatement(selectStmt)

      RS = stmt.executeQuery()

      while(RS.next()):

      params = [RS.getBigDecimal("Conversion_Rate"), batchName, RS.getString("From_Currency"), RS.getString("To_Currency"), RS.getString("Rate_Type"), "localCurr", strPOVPer]

      fdmAPI.executeDML(insertStmt, params, False)

      fdmAPI.commitTransaction()

      RS.close()

      sourceConn.close()

    i get following warning message

    Warning: No records exist for Period 'Apr-2015'

    ORAHYPFDM
This discussion has been closed.