Using Outer Joins AND Effective Dated Logic

How can you use outer joins on a table and still use effective dated logic? Something like this.

Note that the outer join will either return no rows, and thus a NULL value for EFFDT, or it will return a date and subsequent effective dated logic can be used.

AND C.BUSINESS_UNIT (+) = A.BUSINESS_UNIT
AND C.COMMON_ID (+) = A.COMMON_ID
AND C.SA_ID_TYPE (+) = A.SA_ID_TYPE
AND C.ITEM_NBR_CHARGE (+) = A.ITEM_NBR
AND
   (NULL = C.EFFDT
   OR
       (C.EFFDT =
           (SELECT MAX(C_ED.EFFDT)
           FROM sysadm.ps_ITEM_XREF C_ED
           WHERE C_ED.BUSINESS_UNIT = C.BUSINESS_UNIT
           AND C_ED.COMMON_ID = C.COMMON_ID
           AND C_ED.SA_ID_TYPE = C.SA_ID_TYPE
           AND C_ED.ITEM_NBR_CHARGE = C.ITEM_NBR_CHARGE
           AND C_ED.ITEM_NBR_PAYMENT = C.ITEM_NBR_PAYMENT
           AND C_ED.EFFDT <= SYSDATE)))

XML Publisher Example

Example of using PeopleCode to generate XML and then merge with a predefined RTF template.

/* GUSAFN02 - HEUP Project, Michael Nitschke 27.11.2007 */
import PSXP_RPTDEFNMANAGER:*;
import GU_EN_ENROLMENTS:*;

Declare Function DeleteLocalFile PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

/* Function to populate a node based on given SQL. 
   &node:     Create the node in the location you want and pass through to be populated.
   &recName: Record that node data will be based on.  Must also match data structure returned from &sql.
   &sql:     SQL that will populate &rec and the node. */
Function AddNodeFromSQL(&node As XmlNode, &recName As string, &sqlName As string)
 Local Record &rec = CreateRecord(@("Record." | &recName));
 Local SQL &sql = GetSQL(@("SQL." | &sqlName), &emplid);
 While &sql.Fetch(&rec)
    Local XmlNode &nodeRow = &node.AddElement(&node.NodeName | "Row");
    For &f = 1 To &rec.FieldCount
       Local XmlNode &nodeFld = &nodeRow.AddElement(&node.NodeName | "." | &rec.GetField(&f).Name);
       &nodeFld.NodeValue = &rec.GetField(&f).Value;
    End-For;
 End-While;
End-Function;

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

&emplid = %EmployeeId;
Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local XmlDoc &xmlDoc;
&xmlDoc = CreateXmlDoc("<?xml version='1.0'?><ACADEMIC_CHARGES/>");

/* Create header, config, programs, charges, unapplied charges and discounts xml elements, including data. */
Local XmlNode &nodeHeader = &xmlDoc.DocumentElement.AddElement("Header");
AddNodeFromSQL(&nodeHeader, "GU_STMT_HDR_DW", "GU_STMT_HDR");
Local XmlNode &nodeConfig = &xmlDoc.DocumentElement.AddElement("Config");
AddNodeFromSQL(&nodeConfig, "GU_STMT_CFG_DTL", "GU_STMT_CFG_DTL_AC");
Local XmlNode &nodeProgram = &xmlDoc.DocumentElement.AddElement("Program");
AddNodeFromSQL(&nodeProgram, "GU_STMT_PROG_DW", "GU_STMT_PROG");
Local XmlNode &nodeCharges = &xmlDoc.DocumentElement.AddElement("Charges");
AddNodeFromSQL(&nodeCharges, "GU_STMT_CHRG_DW", "GU_STMT_CHARGES");
Local XmlNode &nodeChargesGST = &xmlDoc.DocumentElement.AddElement("ChargesGST");
AddNodeFromSQL(&nodeChargesGST, "GU_STMT_CGST_DW", "GU_STMT_CHARGES_GST");
Local XmlNode &nodeUnappliedCharges = &xmlDoc.DocumentElement.AddElement("UnappliedPayments");
AddNodeFromSQL(&nodeUnappliedCharges, "GU_STMT_CHRG_DW", "GU_STMT_UNAPPLIED_PAYMENTS");
Local XmlNode &nodeDiscounts = &xmlDoc.DocumentElement.AddElement("Discounts");
AddNodeFromSQL(&nodeDiscounts, "GU_STMT_CHRG_DW", "GU_STMT_DISCOUNTS");
Local XmlNode &nodeMessages = &xmlDoc.DocumentElement.AddElement("Messages");
AddNodeFromSQL(&nodeMessages, "GU_STMT_MSG_DW", "GU_STMT_MESSAGES");

/* Create report definition and connect to the generated XML document. */
&rptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn("GU_ACAD_CHRG");
&rptDefn.Get();
&rptDefn.SetRuntimeDataXMLDoc(&xmlDoc);

/* Create output directories. */
&sDirSep = GetDirSeparator();
&RptOutputDir = GetEnv("PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | UuidGen();
&OutputDir = &RptOutputDir | &sDirSep | "RptInst";
&DataDir = &RptOutputDir | &sDirSep | "Data";
CreateDirectory(&OutputDir, %FilePath_Absolute);
CreateDirectory(&DataDir, %FilePath_Absolute);

/* Process. */
&rptDefn.OutDestination = &RptOutputDir;
&rptDefn.ProcessReport("", %Language_User, %Date, "");
CommitWork();

/* Display. */
&rptDefn.DisplayOutput();

/* Cleanup. */
DeleteLocalFile(&sOutputFile, %FilePath_Absolute);

Array example

Nice little example of using arrays in PeopleCode. Probably a more elegant, 2 dimension solution but this works just fine.

This example was required to handle overriding GL distribution by Elements, which are on level 3. and element can appear on level 3 under many level 2 rows. Code loops through all possible rows on level 3 and sums total for each unique Element found. Must add to 100.

/* Check that sum of each given element, across all pin rows, adds to 100%. */

/* 2 arrays to hold all elements and sum of their percentages. */
&arrElements = CreateArray();
&arrPercent = CreateArray();

/* Populate the arrays. */
Local Rowset &rsGP_GL_DIST_DTL = GetRowset(Scroll.GP_GL_DIST_DTL);
For &i = 1 To &rsGP_GL_DIST_DTL.ActiveRowCount
   Local Rowset &rsGP_GL_DIST_PIN = &rsGP_GL_DIST_DTL(&i).GetRowset(Scroll.GP_GL_DIST_PIN);
   
   /* Check there are actually any element rows (if one does they all do, vice-versa). Populate arrays if found.*/
   If &rsGP_GL_DIST_PIN(1).GP_GL_DIST_PIN.PIN_NUM.Value = 0 Then
      &hasElements = False;
      Break;
   Else
      &hasElements = True;
      For &j = 1 To &rsGP_GL_DIST_PIN.ActiveRowCount
         &element = &rsGP_GL_DIST_PIN(&j).GP_PIN_DESCR_VW.DESCR.Value;
         &percent = &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Value;
         &found = &arrElements.Find(&element);
         If &found = 0 Then
            &arrElements.Push(&element);
            &arrPercent.Push(&percent);
         Else
            &arrPercent [&found] = &arrPercent [&found] + &percent;
         End-If;
      End-For;
   End-If;
   
End-For;

/* Check for any elements that are <> 100% and advise with warning msg & highlighting the field. */
If &hasElements Then
   For &k = 1 To &arrPercent.Len
      
      &err = False;
      If &arrPercent [&k] <> 100 Then
         &err = True;
      End-If;
      
      For &i = 1 To &rsGP_GL_DIST_DTL.ActiveRowCount
         &rsGP_GL_DIST_PIN = &rsGP_GL_DIST_DTL(&i).GetRowset(Scroll.GP_GL_DIST_PIN);
         For &j = 1 To &rsGP_GL_DIST_PIN.ActiveRowCount
            If &rsGP_GL_DIST_PIN(&j).GP_PIN_DESCR_VW.DESCR.Value = &arrElements [&k] Then
               If &err Then
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.SetCursorPos(%Page);
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Style = "PSERROR";
                  Error "Sum percentages of Element '" | &arrElements [&k] | "' do not total 100%.";
               Else
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Style = "PSEDITBOX";
               End-If;
            End-If;
         End-For;
      End-For;
      
   End-For;
End-If;

Decode, If, Case statements in SQL

Two examples of evaluating 2 values and returning a given value based on which is earlier/bigger:

DECODE((SYSDATE - A.DUE_DT) - ABS(SYSDATE - A.DUE_DT), 0, 'OVERDUE', A.DUE_DT) AS DUE_DATE
CASE WHEN SYSDATE > A.DUE_DT THEN 'OVERDUE' ELSE to_char(A.DUE_DT) END AS DUE_DATE

Both do the same thing.

Related links:
http://www.4guysfromrolla.com/webtech/102704-1.shtml
http://www.sqlteam.com/article/the-case-for-case

Passing Parameters to SQR via the Process

10.01.2008, update: seems Blogger has lost the uplaoded images for this post. Perhaps they will come back?

Update the Parameter List of your process definition. I this case I have used a Record.Field value.





PeopleCode to set the value of the Field (GU_GRDLPS_RC_WK.DESCR).





In your SQR you pick up the parameter by using the Input statement:


input $procAcadOrg 'Academic Organisation to distribute report to: (Optional, Press ENTER to continue)'

Errors running Application Engines

If you get the following error:

"Message:
All Processing Suspended: Restart OPRID=S390765, RUNID=31-01-03112240, PI=2988

Description:
A pending process was found for the current Operator id, Run Control id, and Application Engine Program. This process either is running now or has abended. Please restart the pending process if it has abended, and allow it to run to completion, before submitting a new process request."

...you need to cleanup the dead rows in sysadm.ps_aeruncontrol.

PeopleTools > Application Engine > Manage Abends

Downloading from Grids - IE settings

Problem:
We found a problem when tring to download to spreadsheets from grids. IE wouldn't open the spreadsheet, just refreshed a few times and then nothing.

Resolution:
Tools/Internet Options/
Security/Custom Level/
Downloading/Automatic prompting for downloads - tick enable.