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;