This discussion is archived
12 Replies Latest reply: Oct 27, 2013 7:06 PM by atsoi RSS

Loading CellText from FDM to HFM group level Account members (11.1.2.2)

atsoi Newbie
Currently Being Moderated

Hi all,


I am trying to load CellText using the Memo field in FDM to export to CellText in HFM. I have followed the previous post and instructions and taken this method for CellText loading: https://forums.oracle.com/thread/2412887.

 

The application I am working on requires CellText loading to group level Account members. For these specific accounts, I have loaded a value of "0" in the amount field and textual data in a Cell Text field to be loaded in to the Memo field. The script that maps the CellText to memo field works fine, and the validation stage displays no errors. However, upon export to HFM there are kick-outs stating; "Error: Data cannot be input for group level Account members."


Is there any way that the Memo fields can be loaded to group level Account members without loading the amount data?

 

Many thanks for your help!

 

Best regards,

 

Aaron

  • 1. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    Francisco Amores Pro
    Currently Being Moderated

    Hi,

     

    Cell texts are loaded into HFM when Adapter Action Script LOADB is executed (after .DAT is successfully loaded)

     

    If you just want to load cell texts but not data you can follow some different approaches. One of this could be to disable "Enable Cell Text Loading" and adjust your EXPORT action so the .DAT file generated contains only !Descriptions sections with your cell text information and not !DATA section. You can get cell texts in the same way it´s done in LOADB Action script.

     

    This should be working. If !DATA section is required you can add dummy record to be loaded.

     

    HTH

  • 2. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    atsoi Newbie
    Currently Being Moderated

    Hi HTH,

     

    Thank you for your reply.In our case, our import file will contain both rows of data that need to be loaded, and also rows of data with only CellText but with no numerical amount needed ("0" input in the amount field).


    Could you please go in to more detail of how dummy records can be loaded? Will this mean that I will be able to point to the CellText only data rows in the EXPORT action and edit the !DATA specifically for those rows?

     

    I will investigate your suggestions above regarding EXPORT section tomorrow and get back to you, thanks for your help!

     

    Best regards,

     

    Aaron

  • 3. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    Francisco Amores Pro
    Currently Being Moderated

    Ok, so you will be exporting also data besides cell texts.

    What about not exporting zeros to HFM. You can do it by adjusting the export action script (don´t write the line in the DAT file if the amount is zero)

    If you cannot disable zero loading because of business requirements then you can think of other ways of not including in your DAT file those lines.

    Cell texts will be then loaded by the API from memos as usual and not using !Descriptions tag in the DAT file

  • 4. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    atsoi Newbie
    Currently Being Moderated

    Thanks for getting back to me. I have been looking at this method of not exporting zeros to HFM and this should work as I am able to enable zero loading from the business requirements perspective.

    Looking in to the EXPORT action script, I had a couple of questions as to where to insert this logic and in what way, as I am not too familiar with the SQL coding.

    I've identified a section in the script I believe that the logic should be implemented to;

     

    Would it be correct in saying that an IF statement could be implemented at the append to DAT file stage, where all the rows of data with zero in them will not get appended?

     

     

    '*******************************************************************

    'DATA PROTECTION - Extract Protected Data & Append to DAT file

    '*******************************************************************

    If blnProtection Then

      Dim vaExtract

      Dim objRec

      For Each ObjRec In RES.PcolRecs

      vaExtract = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fDBExtractArray(CStr(API.DataWindow.Connection.PstrDirOutbox),objRec.colFlds.Item("Entity").varValue, CStr(objRec.colFlds.Item("Scenario").varValue), CStr(objRec.colFlds.Item("Year").varValue), objRec.colFlds.Item("Period").varValue, objRec.colFlds.Item("DataView").varValue, , CStr(objRec.colFlds.Item("Operator").varValue), CStr(objRec.colFlds.Item("Criteria").varValue), RES.FDMFalse)

      Set objHWReturn = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fDBAppendArrayToFile(CStr(strFile), vaExtract)

      'Check the result

      With ObjHWReturn

      If .PblnIsErr Then

      'Error during load

      objR.intResult = RES.FDMFalse

      objR.strErrDesc = .PstrErrDesc

      objR.lngErrCode = .PlngErrCode

      objR.varList = .PstrErrFile

      objR.blnIsErr = RES.FDMTrue

      'Destroy Collection

      Set RES.PcolRecs = Nothing

      Exit Function

      End If

      End With

      Next

      'Destroy Collection

      Set RES.PcolRecs = Nothing

    End If

     

     

    'Set the logging strings

    strIODesc = CStr(strFile)

    strEventDesc = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fLocalizeString("A_" & API.IntBlockMgr.IntegrationMgr.PstrSysKey & "_EX_EM_5", CStr(blnLoadLineItems),CStr(blnProtection),CStr(strTCat),CStr(strPerDesc),CStr(strTargYear))

    strErrDesc = CStr(objR.strErrDesc)

     

     

    'Log Action in transaction log

    API.DataWindow.DBTools.mLog 5005, API.DataWindow.Connection.PstrUserID, CDate(TStart), Now(), CStr(strIODesc), "OK", CStr(strEventDesc), CStr(strErrDesc)

     

     

    'Destroy objects

    Set objIntersect = Nothing

    If Not API.State.GblnBatchLoadActive Then API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fDisconnect

    If Not API.State.GblnBatchLoadActive Then Set API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon = Nothing

     

    It would be great if you could help me with exploring this issue, your assistance is much appreciated! Thanks!


    Best regards,

     

    Aaron

  • 5. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    Francisco Amores Pro
    Currently Being Moderated

    You don´t need to adjust DATA PROTECTION section.

     

    The easiest way is to find the line where the line is written to the file (you have two locations where this happens, one for single period and other for period range)

    check that the amount is not zero, otherwise skip the line.

     

    Regards

  • 6. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    atsoi Newbie
    Currently Being Moderated

    I have inserted an IF statement around the WriteLine function that does not write the line if the amount is 0. This seems to have generated the correct lines of data in the output DAT file.

    However I am now hitting a new error when I try and export from FDM, "Error: Stream Failed, Invalid file path provided!".

    The IF statement is the only change to the default code I have made and when reverted, this error goes away. Would you have any ideas on how to solve this?

     

    Thanks!

  • 8. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    atsoi Newbie
    Currently Being Moderated

    I have added the code below;

     

    Else

      'Line Item processing is not active

      strLine = strLine & API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.SetAmountToHFMuserPref(rsExport.Fields("Amt").Value)

      '*** Customization ***

      'Write the line to the file

      If API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.SetAmountToHFMuserPref(rsExport.Fields("Amt").Value) = 0 Then

      Else

      objFile.WriteLine(strLine)

      End If

    '*** End of Customization ***

      End If

     

    The original is below for your reference;

    Else

      'Line Item processing is not active

      strLine = strLine & API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.SetAmountToHFMuserPref(rsExport.Fields("Amt").Value)

      'Write the line to the file

      objFile.WriteLine(strLine)

      End If

  • 9. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    Francisco Amores Pro
    Currently Being Moderated

    I will need to check in my environment. Will replay asap

  • 10. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    atsoi Newbie
    Currently Being Moderated

    It seems that on export we get the error message; "Error: Stream Failed, Invalid file path provided!". However the data and CellText is successfully passed through to HFM. Would you know how we would be able to resolve the error?

  • 11. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    Francisco Amores Pro
    Currently Being Moderated

    Can you check the error log and see which line refers to?

  • 12. Re: Loading CellText from FDM to HFM group level Account members (11.1.2.2)
    atsoi Newbie
    Currently Being Moderated

    It seems that the previous error was an oversight on my part and was due to an incorrect POV in the loaded file. This method seems to have worked!

     

    Many thanks for your help!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points