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;