12 Replies Latest reply: Oct 27, 2013 9:06 PM by atsoi RSS

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

    atsoi

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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!