Blog

Created on Monday, 24 September 2007 19:10

Written by Larry Grey

So, you have a journal with several hundred lines in it, and you want to find the lines that hit a particular account, fund code, and program code. You have a query that tells you it's there, but you want to pull up the journal entry itself to review it there.

This is the culmination of the last two posts with a few extra tidbits, such as array handling, rowset handling, and using variables to pick the field you want to use.

This posting shows how to pass any chartfield combination on the URL for accessing the journal entry page, allowing the navigation to the journal line(s) that contain those chartfield values. The code is written against Financials 8.9, but should work for previous versions.

Using a variable as a field reference?

Yes. One of the key items in the solution is the ability to evaluate a variable to get a fieldname in a record.field reference (this is how I'm able to take the list of chartfields from the BU_LED_CF_VW and use them). For those who aren't familiar with this, you can use @("RECORDNAME." | &VariableName) to do this. We often wrap this syntax with GetRecord or GetField to make it more error-proof in some of our other products.

Solution to the problem

The code does the following:

• It matches up the valid chartfields with parameter names passed on the command line
• It determines whether the journal is editable.
• If it is editable, then it uses the SetCursorPos function
• If it isn't editable, then it sets the work fields that the adjust_line_scroll() function uses

Here's the code (it should be added in the page activate Peoplecode after the PeopleSoft-delivered page activate PeopleCode in the JOURNAL_ENTRY2_IE page).

/* Grey Sparling - Call function to return journal lines with chartfield values */
Declare Function adjust_line_scroll PeopleCode FUNCLIB_GL.JOURNAL_LINE FieldFormula;

/* Grey Sparling - Code to drill to row with chartfield values passed in as a parameter */
Local Rowset &RS_CFS = GetRowset(Scroll.BU_LED_CF_VW);
Local string &sChartField;
Local number &sNumParameters = 0;
Local boolean &PassedParameters = False;
Local array of string &CF_Params;
Local array of string &CF_ParamValues;
Local number &m;

&CF_Params = CreateArray(&sChartField);
&CF_ParamValues = CreateArray(&sChartField);

/* Grey Sparling - Build Array with all the parameters passed in the Request Object */
For &l = 1 To &RS_CFS.ActiveRowCount;


• &CF_Params [&sNumParameters + 1] = &sChartField;
  &PassedParameters = True;
  &sNumParameters = &sNumParameters + 1;
  
&sChartField = &RS_CFS(&l).BU_LED_CF_VW.CHARTFIELD.Value;
&CF_ParamValues [&sNumParameters + 1] = %Request.GetParameter(&sChartField);

If All(&CF_ParamValues [&sNumParameters + 1]) Then
End-If;

End-For;

/* Grey Sparling - Use the parameters in the array to find the appropriate set of Journal Lines */
If &PassedParameters Then

• • @("JRNL_PANELS_WRK." | &CF_Params [&m]) = &CF_ParamValues [&m];
  JRNL_HEADER.JRNL_HDR_STATUS = "P"Or
  JRNL_HEADER.JRNL_HDR_STATUS = "U"Then
  /* Grey Sparling - Journal is read only */
  /* Grey Sparing - Loop through parameters array and set them in work record for searching */
  For &m = 1 To &sNumParameters;End-For;
  
  adjust_line_scroll();
  • • • &rowTest.GetRecord(Record.JRNL_LN).GetField(@("Field." | &CF_Params [&m])).SetCursorPos(%Page);
        &sFoundCombination = False;
      If &rowTest.GetRecord(Record.JRNL_LN).GetField(@("Field." | &CF_Params [&m])).Value = &CF_ParamValues [&m] ThenElseEnd-If;
      
      Break;
    Local Row &rowTest = &rsJrnlLines.GetRow(&j);
    &sFoundCombination = True;
    
    /* Grey Sparling - Loop through fields in array to see if current row matches all passed parameters */
    For &m = 1 To &sNumParameters;End-For;
    
    If &sFoundCombination ThenEnd-If;
    
  /* Grey Sparling - Journal is editable */
  Local Rowset &rsJrnlLines = GetLevel0().GetRow(1).GetRowset(Scroll.JRNL_LN);
  Local number &j;
  
  /* Grey Sparling - Loop through Journal Lines in Panel Buffer to find first one that matches combination of parameters */
  For &j = 1 To &rsJrnlLines.ActiveRowCountEnd-For;
If JRNL_HEADER.JRNL_HDR_STATUS = "D"OrElseEnd-If;

End-If;

Labels: Drilling, PeopleCode