ROI period to post showing prior period

This question has suggested answer(s)

Not sure if anyone has run into this or has an answer but I have a client where AR is set to 01-2014 but a report set up under a custom report control shows 12-2013 as the posting period.  The custom report control says to pull the posting period from AR and the AR setup table has 01-2014 in both period fields.  All the standard AR reports that display the posting period on the reports tab display correctly.  Further, this client has two application databases under the same system database, both are in 01-2014 for AR, and the other application database shows 01-2014 correctly for the exact same report.

Clearly, ROI is pulling 12-2013 from somewhere and it must not be ARSETUP but I cannot figure out where it pulls it from that still has 12-2013.

All Replies
  • Did you check the setting for the report in Report Control Maintenance? You can select the module to pull the period from in there.

    Butch Adams
    www.conexussg.com

  • Butch,

    Thanks but already checked that.  Also, as I said, this client has two application databases under one system database and the report control is in the system database and the period displays correctly in the second application database.  the report in question is pulling the period from the AR Setup (or so the report control definition says).

  • Rick,

    I realized that after I fired that off and got to thinking about what other inconsistency could come up. It almost sounds like a join problem in the report.

    Butch Adams
    www.conexussg.com

  • Butch, I realized, by you responses, that I did not define the issue well enough.  It is not the report itself that exhibits the problem.  Rather, it is the ROI screen.  It is pre-filling the from and to posting periods with 12-2013 even though AR is in 01-2014 but it is only doing this for one of the two application databases.  Since the report control is in the system database and, therefore, shared, the issue cannot be in the report control.

    What it feels like is that SL has, somehow, cached, the current AR period and is not looking at ARSETUP each time.  However, another AR report that also shows the current posting period on the ROI screen is correctly showing 01-2014 so I have sort of run out of things to look at.

    Given that it is now the end of January and the client will be moving to 02-2014 in the not too distant future, I may wait and see if the issue resolves itself when AR is closed for January.  The client, for now, is just changing the period fields on the ROI screen.  I was more curious if anyone new if there was another table when the current AR period is kept so that programs, like the ROI code, do not have to fetch it out of the ARSETUP table each time (e.g., something similar to the PES table that holds a bunch of "global" values and is populated when SWIM launches.

  • SL 2011 appears to use a stored procedure called fetch_modulepernbr to pull posting periods from the various setup files (I determined this by running a SQL trace as the ROI screen opens).  I executed that stored procedure in both the failing database and the working database and the returned AR period number in both is the correct 01-2014.  So, that is about as far as I can take it.  At this point, only someone who has access to the ROI screen code can determine what it does after executing that stored procedure.  I tried to open the ROI screen with Customization Manager but, unfortunately, that screen is block as being non-customizable.

    Carolyn, if you happen to be following this post, you appear that you might have access to the code or someone who does.  I am interested in the code that actually populates the perpost field on the ROI screen.  Since that is an optional field and can be either a single field or a from/to pair of fields, I am assuming that the ROI code looks at the report control and exposes one or both fields depending on the report control.

  • So, this situation has become most interesting.  A little more background might be helpful.

    My company develops specialized software for a vertical industry (Long term Healthcare) and we have an add-on module to SL that is cataloged under its own module ID (RB for resident billing).  This problem is with any of the reports under that module that have the posting period fields on the ROI screen.  Resident Billing is basically a very specialized invoice generation process that feeds into AR.  Consequently, the reports under this module that want to provide the posting period field on the ROI screen pull the posting period from AR setup as defined on the report control screen.  This has worked fine for years and works fine for all of our clients using 6.5, 7.0 and 2011 except for this one client and this one of their two application databases.

    Now for the details of what I have found.

    As indicated earlier, the client has two application databases under one system database and only one of the databases exhibits this problem.  Since both databases are under one system database, the issue cannot be with the report control since that is shared.  Further, all RB report in this one database exhibit this problem and the very same reports in the other application database do not.  Reports under AR show the correct posting period on the ROI screen in both application databases.  In an attempt to run this down, I temporarily changed ARSetup to have the current posting period be 02-2014 and the ROI screen continued to show 12-2013.  I reset ARSetup back to 01-2014 and then temporarily changed GLSetup to 01-2014 and now the ROI screen shows 01-2014.  Clearly, ROI is pulling the posting period from GLSetup as opposed to ARSetup for some reason even though the report control says to pull it from ARSetup.  However, if the second application database, which also has GLSetup still indicating 12-2013, I do not have the problem so that contradicts the premise that I just made.  I set GLSetup back to 12-2013 in the failing database and the ROI screen fails again showing 12-2013.

    This tells me that the issue is within the particular application database.  However, as indicated in a prior post, when I ran the SQL trace, I saw that the stored procedure fetch_modulepernbr is called to determine the posting periods for each of the modules that have posting periods.  If I execute this stored procedure out of SQL Manager it returns the correct posting period for AR.  This tells me that there is some logic in the ROI code that takes the results from this stored procedure and applies some more logic and, for some reason, decides, in this database and not the other, that it needs to use the current GL posting period and not the current AR posting period but only in this particular application database.

    I am thinking that the issue is, somehow, related to fact that the report is in a module that is not an SL standard module (though it is defined in the module table).  But, it has to be something unique to this particular database since it does not do this in the other application database and it does not do this in any of my other client databases.

    This issue has become a rather interesting conflict in findings.  It appears that only someone with access to the program logic of ROI can determine what is unique in this situation.  This probably means that this issue needs to be presented as an SL incident and my company pay for the incident charge but I really am not in favor of paying that charge under these circumstances.

    By the way, this client used to be on version 6.5 and upgraded to SL 2011 and this application database never exhibited this problem under 6.5.  Further, I am pretty sure, but cannot state for certain, that this problem was not there after moving to SL 2011 but the problem is that, in most cases, they keep GL in the same period as AR so it could have been there and the combination of the AR and GL periods were such that it did not show itself.  GL is being held at 12-2013 because of the year-end needs on the GL side.

    I sure wish that I could just take a peek at the ROI code that exists between the execution of that stored procedure and the setting of the posting period field on the screen.  I guess that is the programmer in me.

  • This was a bit of an unusual circumstance but I though anyone reading this thread might be interested in the cause.  The user had checked the prevent posting to prior fiscal years option on the GL setup screen in the database that was exhibiting this issue but had not in the database that was not exhibiting the issue.  when we unchecked that option, the ROI screen displayed correctly.

    Why this option was having this affect is unknown and pretty illogical but I suspect it had something to do with the fact that the failing reports were part of a non-SL module.