SQL for Position Managment

This is a good sample for obtaining your current SUP and reports_to. (Based upon position)

SELECT a.emplid
, a.position_nbr
, a.reports_to
, b.name
, b.first_name
, b.last_name
, b.middle_name
, c.phone
, c.extension
FROM ps_job a
, ps_names b
, ps_personal_phone c
, ps_position_data d
WHERE a.emplid = b.emplid
AND a.empl_status NOT IN ('R','T')
AND a.hr_status = 'A'
AND a.effdt = (
SELECT MAX(a1.effdt)
FROM ps_job a1
WHERE a1.emplid = a.emplid
AND a1.empl_rcd = a.empl_rcd
AND a1.effdt <= sysdate)
AND a.effseq = (
SELECT MAX(a2.effseq )
FROM ps_job a2
WHERE a2.emplid = a.emplid
AND a2.empl_rcd = a.empl_rcd
AND a2.effdt = a.effdt )
AND a.emplid = c.emplid
AND c.phone_type = 'WORK'
AND b.name_type = 'PRI'
AND b.effdt = (
SELECT MAX(b_ed.effdt)
FROM ps_names b_ED
WHERE b_ed.emplid = b.emplid
AND b_ed.name_type = b.name_type
AND b_ed.effdt <= sysdate)
AND a.position_nbr = d.position_nbr
AND d.eff_status = 'A'
AND d.effdt = (
SELECT MAX(d_ed.effdt)
FROM ps_position_data d_ED
WHERE d_ed.position_nbr = d.position_nbr
AND d_ed.eff_status = d.eff_status
AND d_ed.effdt <= sysdate)
AND A.POSITION_NBR <> A.REPORTS_TO

XMLP / App Engine / App Package

Step by Step creating a Application Engine, that uses App Package, to create *.XML files from SQL objects.

1. Initialize State
%Select(OPRID,RUN_CNTL_ID)
SELECT OPRID
, RUN_CNTL_ID
FROM PS_PRCSRUNCNTL
WHERE OPRID=%OperatorId
AND RUN_CNTL_ID = %RunControl

2. Get Run Control parms - (If Any)
3. Get the data from a custom view, and place/insert into a TAO (temp table)

%InsertSelect(DISTINCT, ASU_TL_SB_TAO, ASU_TL_APROV_VW a, process_instance = %processinstance)
FROM %Table(ASU_TL_APROV_VW) a
WHERE a.reports_to = %Bind(REPORTS_TO)
AND a.reported_status = %Bind(REPORTED_STATUS)
AND a.dur BETWEEN %Bind(PERIOD_BEGIN_DT) AND %Bind(PERIOD_END_DT)

4. Process App Package call

import ASU_TL_APPROV_PRINT:Report;

Component ASU_TL_APPROV_PRINT:Report &oReport;

&mval = String(ASU_TL_SBL_AET.PROCESS_INSTANCE.Value);
&oReport = create ASU_TL_APPROV_PRINT:Report();
&oReport.PublishReport(&mval, ASU_TL_SBL_AET.PROCESS_INSTANCE.Value);

App Package code: Report - OnExecute

import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
import PSXP_ENGINE:*;
import ASU_TL_APPROV_PRINT:Util:ReportUtil;

import %metadata:*;
import %metadata:XMLPubRptMgr:*;

class Report
property string PrintFormat get set;
property string PrintSort get set;
property string XMLOutputOption get set;
property string XMLOutputFilePath get set;


method Report();
method SQLWhereClause();
method PublishReport(&parmRptReqNbr As string, &nProcInst As number);

/*last change method PublishReport();*/
method DisplayReport(&parmRptReqNbr As string, &parmReqSeqNbr As number, &PARMREQRN As string, &TEMPLATE As string);
method PrintReport(&parmRptReqNbr As string, &parmPrinterPath As string);

private

instance Rowset &rs_Child, &rs_Main, &rs_temp, &rs_temp2, &rs_pa,;
instance string &template_use, &sRptID, &sRptReqNbr, &print_format, &print_sort, &xmlfile, &xmlfilepath, &sSQLWhereClause, &process_instance, &pa_code, &rowtype;
instance number &RN, &nReqSeqNbr, &ProcessInstance, &iCount, &a, &b, &c, &e, &g, &h, &i, &j, &k, &m, &n, &t, &req_seq_nbr, &rpt_nbr, &paCount, &numrows;
instance date &effdt;
instance XmlDoc &objXMLData;
instance XmlNode &objBaseNode, &objTermNode;
instance XmlDoc &objXSDSchema;
instance string &strFileOutput;
instance string &sProcessId, &sOutputDir, &sOutputFile;

instance ASU_TL_APPROV_PRINT:Util:ReportUtil &objReportUtil;
instance PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
instance XmlDoc &inXMLDoc;
instance string &test;
instance SQL &SQL_TA1, &SQL_TA2, &SQL_TA3, &SQL_TA4, &SQL_TA5;

method BuildDataRowset();
method GenSchema();
method CreateXMLDataFile();

end-class;

get PrintFormat
/+ Returns String +/
Return &print_format;
end-get;

set PrintFormat
/+ &NewValue as String +/
/* Default Print Format to Landscape if blank */
If &NewValue = "" Then
&print_format = "1"
Else
&print_format = &NewValue;
End-If;
end-set;

get PrintSort
/+ Returns String +/
Return &print_sort;
end-get;

set PrintSort
/+ &NewValue as String +/
end-set;

get XMLOutputOption
/+ Returns String +/
Return &xmlfile;
end-get;

set XMLOutputOption
/+ &NewValue as String +/
&xmlfile = &NewValue;
end-set;

get XMLOutputFilePath
/+ Returns String +/
Return &xmlfilepath;
end-get;

set XMLOutputFilePath
/+ &NewValue as String +/
&xmlfilepath = &NewValue;
end-set;

method Report

/* Set Report ID*/
/*CHANGE LINE
THIS IS THE REPORT NAME*/
&sRptID = "ASUTLAPV";
/* create Report Utility object */
&objReportUtil = create ASU_TL_APPROV_PRINT:Util:ReportUtil();
/*CHANGE LINE
THIS IS THE CHILD ROW SET RECORD*/
&rs_Child = CreateRowset(Record.ASU_TL_SB_TAO);

end-method;

method SQLWhereClause

end-method;

method GenSchema
Local string &sAdapter, &sDSQueryName;
Local XmlNode &objXSDSchemaBaseNode;
Local Rowset &rsResult;
Local Row &rowResult;

&rsResult = CreateRowset(Record.ASU_TL_SBSTAO);

/* Generate Schema Header XMLDoc */
&objXSDSchema = &objReportUtil.CreateSchemaHeader(&rsResult);
&objXSDSchemaBaseNode = &objXSDSchema.DocumentElement;

end-method;


/* This method will create the datasource XMLfile used by XMLP */
/* It will be stored on the app or process scheduler server */
method CreateXMLDataFile
/*CHANGE LINE (s)
THIS IS THE PARENT RECORD*/
Local Record &rec = CreateRecord(Record.ASU_TL_SBSTAO);
/*THIS IS THE CHILD RECORD*/
Local Record &recchild = CreateRecord(Record.ASU_TL_SB_TAO);

Local number &nRptNbr;
Local SQL &SQL_HDR, &sql_line;
Local boolean &svw1_exists, &svw2_exists, &term_exists;
Local string &emplid, &prev_emplid, &detailorg;

/* Setup XML file directory */
/*CHANGE LINE
THIS IS THE OUTPUT FILE NAME*/
&sOutputFile = &objReportUtil.GetOutputFile("ASU_TL_SBL.xml");
Local File &FILE = GetFile(&sOutputFile, "W", %FilePath_Absolute);
Local Record &recTemp;
/*CHANGE LINE
THIS SELECTS ALL THE HEADER ROWS*/
&SQL_HDR = CreateSQL("%SELECTALL(:1)");
/* THIS IS THE FILE LAYOUT*/
If &FILE.SetFileLayout(FileLayout.ASU_TL_APPROV_RPT) Then
/* grab all the data for top level */
&FILE.WriteLine("");
&SQL_HDR.Execute(&rec);
End-If;
While &SQL_HDR.Fetch(&rec)

&FILE.Open(&sOutputFile, "A", "UTF8", %FilePath_Absolute);
/* I use Fetch & WriteRecord because that allows me to fetch the rows one by one and not keep
the whole thing in memory as would be required using a RowSet*/
&FILE.WriteRecord(&rec);
/* grab the Level 1 child data for current top row */

/*CHANGE LINE
THIS IS THE SQL TO GET THE DATA FOR THE CHILD*/
&sql_line = CreateSQL("%selectall(:1)");


&sql_line.Execute(&recchild);
While &sql_line.Fetch(&recchild);
&FILE.WriteRecord(&recchild);
/*CHANGE LINE
THIS WRITES THE CHILD FOOTER*/
&FILE.WriteLine("");
End-While;
/*CHANGE LINE
THIS WRITES THE PARRENT FOOTER*/
&FILE.WriteLine("");



&FILE.Close();

End-While;

/* close SQL objects */
&SQL_HDR.Close();


&FILE.Open(&sOutputFile, "A", "UTF8", %FilePath_Absolute);
&FILE.WriteLine("");
&FILE.Close();



end-method;

/* Output a transcript report request to the Repor Repository or to a Printer */
method PublishReport
/+ &parmRptReqNbr as String, +/
/+ &nProcInst as Number +/

Local PSXP_XMLGEN:RowSetDS &oRowSetDSDefn;
Local File &MYFILE;
Local number &nOutDestType;
Local string &rpt_template;
try
/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
%This.SQLWhereClause();

/* Create XML File */
WriteToLog(%ApplicationLogFence_Level1, "CreateXMLFile Start: " | String(%Datetime));
%This.CreateXMLDataFile();
WriteToLog(%ApplicationLogFence_Level1, "CreateXMLFile End: " | String(%Datetime));
/* create the report definition */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn("ASUTLSBL");
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&sOutputFile);

/* Create the PDF report if option is not XML ONLY */

If %This.XMLOutputOption <> "X" Then
/* generate the report document using the default format (PDF) on the report template */
Local string &tempFormat;
&tempFormat = &oRptDefn.GetDefaultOutputFormat();

&rpt_template = &template_use;
&oRptDefn.ProcessReport(&rpt_template, "", %Date, &tempFormat);

/* find destination (%OutDestType is only when this is called from an AE */
/* if called from from component peoplecode, the OutDestType should be given */
Local string &dir, &rrid;
Local number &pos;
&dir = GetEnv("PS_SERVDIR");
&pos = Find("prcs", &dir);

If &pos > 0 Then
/* This is being run on the process scheduler - this metavariable only available in AE */
&nOutDestType = %OutDestType;
Else
/* This is being run on the appserver - must send to Web (Report Manager) */
&nOutDestType = 6;

End-If;

/* send output to destination */
Evaluate &nOutDestType
When 3 /* Printer */
&oRptDefn.PrintOutput(%FilePath);
Break;
When 6 /* Web */
&sOutputDir = &oRptDefn.OutDestination;
SQLExec("SELECT contentid FROM PS_CDM_LIST where prcsinstance = :1", &nProcInst, &rrid);
MessageBox(0, "", 0, 0, &sRptID);
WriteToLog(%ApplicationLogFence_Error, &sRptID);

&objReportUtil.PostReport(&nProcInst, "", &sOutputDir, &rrid, "", "", Date(""), "");
Break;
End-Evaluate;

End-If;

/* Copy temporary XML file to user Output File Path */

If (%This.XMLOutputOption = "X" Or
%This.XMLOutputOption = "Y") And
%This.XMLOutputFilePath <> "" Then
&strFileOutput = &objReportUtil.writeOutputFile(RTrim(%This.XMLOutputFilePath) | "ASU_TL_SBL" | &parmRptReqNbr, ".xml");
MessageBox(0, "", 0, 0, &parmRptReqNbr);
MessageBox(0, "", 0, 0, "ABOVVE");
Else
/* Delete temporary XML file */
&objReportUtil.deleteDataXML();
End-If;


catch Exception &Err

WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;

end-method;

method DisplayReport
/+ &parmRptReqNbr as String, +/
/+ &parmReqSeqNbr as Number, +/
/+ &PARMREQRN as String, +/
/+ &TEMPLATE as String +/
try
&template_use = &TEMPLATE;

/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
&nReqSeqNbr = &parmReqSeqNbr;

/* Create XML File */
%This.CreateXMLDataFile();

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptID);
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&sOutputFile);
&oRptDefn.ProcessReport(&template_use, "", %Date, "");
CommitWork();
&oRptDefn.DisplayOutput();

/* Delete temporary XML file */
&objReportUtil.deleteDataXML();


catch Exception &Err
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;
end-method;

method PrintReport
/+ &parmRptReqNbr as String, +/
/+ &parmPrinterPath as String +/
rem Local PSXP_XMLGEN:RowSetDS &oRowSetDSDefn;
Local File &MYFILE;

/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
%This.SQLWhereClause();
/* &rs_Main = CreateRowset(Record.SSR_TSRSLT_HDR, &rs_pa, &rs_term);
&rs_Main.Fill(&sSQLWhereClause, &sRptReqNbr);*/
/*ravi version*/
&rs_Main = CreateRowset(Record.ASU_TL_TEW_HDR);
&rs_Main.Fill();
%This.BuildDataRowset();


/* Publish Output */

/* create the report definition */
rem Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
&sRptID = "ASUTLAPV";
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptID);
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataRowset(&rs_Main);

/* generate the report document using the default format (PDF) on the report template */
&oRptDefn.ProcessReport("", "", %Date, "");

/* send output to destination */
rem &oRptDefn.PrintOutput(&parmPrinterPath);
&oRptDefn.PrintOutput("\\vdbux42:cc212hp_secure");

end-method;

method BuildDataRowset

try
/* Create Rowset to be converted to XML */
For &a = 1 To &rs_Main.ActiveRowCount
&req_seq_nbr = &rs_Main(&a).SSR_TSRSLT_HDR.REQUEST_SEQ_NBR.Value;
&rpt_nbr = &rs_Main(&a).SSR_TSRSLT_HDR.REPORT_NUMBER.Value;
&rs_pa = &rs_Main(&a).GetRowset(Scroll.SSR_TSRSLT_SVW1);

&paCount = 1;
End-For
catch Exception &Err
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;
end-method;


Here is the reportUtil:


import PSXP_XMLGEN:RowSetDS;
import PSXP_RPTDEFNMANAGER:*;

class ReportUtil;

method ReportUtil();
method CreateSchemaHeader(&rsResult As Rowset) Returns XmlDoc;
method AddRowSetSchema(&rsAdd As Rowset, &objBaseNode As XmlNode) Returns XmlNode;

method CreateXMLDataHeader(&rsResult As Rowset, &nProcessInstance As number) Returns XmlDoc;
method AddRowSetXMLData(&rsAdd As Rowset, &objBaseNode As XmlNode, &nNode As number, &NumRows As number, &nProcessInstance As number) Returns XmlNode;

method GetOutputFile(&parmFileName As string) Returns string;

method getRuntimeDistribution(&processInstanceId As number, &oRptViewerArray2 As array of PSXP_RPTDEFNMANAGER:ReportViewer) Returns array of PSXP_RPTDEFNMANAGER:ReportViewer;
method PostReport(&processInstanceId As number, &sDbName As string, &outDest As string, &sReportDescr As string, &folderName As string, &serverName As string, &dExpire As date, &sburstVal As string);

method getDefaultFolderName() Returns string;
method writeOutputFile(&strFileName As string, &strFileType As string) Returns string;
method deleteDataXML();

private
method ParseStringToXMLDoc(&sXmlStringIn As string) Returns XmlDoc;
method CheckDupeRowSetElement(&objBaseNode As XmlNode) Returns XmlNode;



instance PSXP_XMLGEN:RowSetDS &objRowSetDS;

instance string &sServDir, &sDirSep, &sOutputDir, &sOutputFile;
instance array of PSXP_RPTDEFNMANAGER:ReportViewer &oRptViewerArray;
instance PostReport &Post;

end-class;

method ReportUtil /* Constructor */

&objRowSetDS = create PSXP_XMLGEN:RowSetDS();

&sServDir = GetEnv("PS_SERVDIR");
&sDirSep = "/";
If Substring(&sServDir, 1, 1) <> "/" Then
&sDirSep = "\"
End-If;

end-method;

method CreateSchemaHeader
/+ &rsResult as Rowset +/
/+ Returns XmlDoc +/
Local string &sXSDString;
Local XmlDoc &objXSDSchema;

&sXSDString = &objRowSetDS.getXSDSchema(&rsResult);
&objXSDSchema = %This.ParseStringToXMLDoc(&sXSDString);

Return &objXSDSchema;

end-method;

method AddRowSetSchema
/+ &rsAdd as Rowset, +/
/+ &objBaseNode as XmlNode +/
/+ Returns XmlNode +/
Local XmlNode &objElementNode, &objThisXSDNode;
Local string &sRowSetName, &sRowsetXsdName, &sRowsetXsdType;

&sRowSetName = &objRowSetDS.getRowSetName(&rsAdd);

&sRowsetXsdName = "rs_" | &sRowSetName;

&objElementNode = &objBaseNode.GetChildNode(4).GetChildNode(1);
&objThisXSDNode = &objElementNode.AddElement("xsd:element");
&objThisXSDNode.AddAttribute("name", &sRowsetXsdName);
&sRowsetXsdType = &sRowsetXsdName | "type";
&objThisXSDNode.AddAttribute("type", &sRowsetXsdType);

&objRowSetDS.addXSDSchema(&objBaseNode As XmlNode, &rsAdd As Rowset);

/* Check for duplicate simpleType coming from &rsAdd */
&objBaseNode = %This.CheckDupeRowSetElement(&objBaseNode);

Return &objBaseNode;

end-method;


method CreateXMLDataHeader
/+ &rsResult as Rowset, +/
/+ &nProcessInstance as Number +/
/+ Returns XmlDoc +/
Local string &sXMLString;
Local XmlDoc &objXMLData;
/*
If &nProcessInstance = 0 Then
&rsResult = %This.AddSampleData(&rsResult);
End-If;
*/
&sXMLString = &objRowSetDS.getXMLData(&rsResult, "");
&objXMLData = %This.ParseStringToXMLDoc(&sXMLString);

Return &objXMLData;

end-method;

method ParseStringToXMLDoc
/+ &sXmlStringIn as String +/
/+ Returns XmlDoc +/

Local XmlDoc &objXMLDocOut;
Local boolean &booReturn;

&objXMLDocOut = CreateXmlDoc("");

&booReturn = &objXMLDocOut.ParseXmlString(&sXmlStringIn);
If &booReturn Then
Return &objXMLDocOut;

rem Else;
rem throw create SCC_COMMGEN:Util:CommGenException(14015, 999, "999 Error creating XMLDoc" | "", "", "", "", "", "");
End-If;
end-method;

method AddRowSetXMLData
/+ &rsAdd as Rowset, +/
/+ &objBaseNode as XmlNode, +/
/+ &nNode as Number, +/
/+ &NumRows as Number, +/
/+ &nProcessInstance as Number +/
/+ Returns XmlNode +/
Local XmlNode &objThisXMLNode;
Local string &sRowSetName, &sRowsetXsdName;
Local number &nRowCount;

&sRowSetName = &objRowSetDS.getRowSetName(&rsAdd);

&sRowsetXsdName = "rs_" | &sRowSetName;

&objThisXMLNode = &objBaseNode.GetChildNode(&nNode).AddElement(&sRowsetXsdName);
&objThisXMLNode.AddAttribute("rowsetname", &sRowSetName);
&objThisXMLNode.AddAttribute("numrows", String(&NumRows));

/*
If &nProcessInstance = 0 Then
&rsAdd = %This.AddSampleData(&rsAdd);
End-If;
*/
&objRowSetDS.addXMLData(&objThisXMLNode As XmlNode, &rsAdd As Rowset);

/* If 1 return the current node passed in else return the child node */
/*
If &nOption = 1 Then
Return &objBaseNode;
Else
Return &objThisXMLNode;
End-If;
*/
Return &objBaseNode;
rem Return &objThisXMLNode;
end-method;


method CheckDupeRowSetElement
/+ &objBaseNode as XmlNode +/
/+ Returns XmlNode +/
Local array of XmlNode &arrBaseElementList;
Local XmlNode &removedNode;
Local string &sAttributeValue1, &sAttributeValue2;
Local number &i, &j;

&arrBaseElementList = &objBaseNode.GetElementsByTagName("xsd:simpleType");

For &i = 1 To &arrBaseElementList.Len
&sAttributeValue1 = &arrBaseElementList [&i].GetAttributeValue("name");
For &j = &i + 1 To &arrBaseElementList.Len
&sAttributeValue2 = &arrBaseElementList [&j].GetAttributeValue("name");
If &sAttributeValue1 = &sAttributeValue2 Then
&removedNode = &objBaseNode.RemoveChildNode(&arrBaseElementList [&j]);
End-If;
End-For;
End-For;

Return &objBaseNode;
end-method;

method GetOutputFile
/+ &parmFileName as String +/
/+ Returns String +/

Local string &sProcessId;
/* Create a file to hold the XML data */
&sProcessId = UuidGen();

CreateDirectory(&sServDir | "/files/XMLP/" | &sProcessId, %FilePath_Absolute);
CreateDirectory(&sServDir | "/files/XMLP/" | &sProcessId | "/XMLData", %FilePath_Absolute);
&sOutputDir = &sServDir | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | &sProcessId;
&sOutputFile = &sOutputDir | &sDirSep | "XMLData" | &sDirSep | &parmFileName;

Return &sOutputFile
end-method;


method getDefaultFolderName
/+ Returns String +/
Local string &foldername;

/* mdu ICE 1520060000 */
SQLExec("SELECT PSRF_FOLDER_NAME FROM PSRF_FLIST_TBL WHERE PSRF_IS_DEFAULT = 'Y'", &foldername);


Return &foldername;
end-method;

method PostReport
/+ &processInstanceId as Number, +/
/+ &sDbName as String, +/
/+ &outDest as String, +/
/+ &sReportDescr as String, +/
/+ &folderName as String, +/
/+ &serverName as String, +/
/+ &dExpire as Date, +/
/+ &sburstVal as String +/

rem Local PostReport &Post;
Local string &sUserType, &sUserId, &distNode, &os, &sFolderName, &sServerName, &sOutDestFormat;
Local boolean &bRet;
Local integer &iViewer;
Local number &RptInstance;
Local string &sDistNodeURL, &sFileName, &sRptURL;


WriteToLog(%ApplicationLogFence_Level1, "PostReportDistribute : Process Instance : " | &processInstanceId | " DBName : " | &sDbName | "Destination: " | &outDest | " ReportDescr : " | &sReportDescr);

/* create postReportObject */
&Post = SetPostReport();
&Post.ProcessName = "XMLP"; /* do not change. used by Report Search view */
&Post.ProcessType = "XML Publisher";

&outDest = &outDest | "/RptInst";
&outDest = Substitute(&outDest, "/", &sDirSep);
&Post.SourceReportPath = &outDest;

/*
If (&sburstVal <> "") Then
&sburstVal = " [" | &sburstVal | "] ";
Else
&sburstVal = " ";
End-If;
*/

rem &Post.ReportDescr = %This.ID | &sburstVal | &sReportDescr;
&Post.ReportDescr = &sReportDescr;

If (&processInstanceId <> 0) Then
/* get servername, foldername from scheduler request table */
SQLExec("SELECT PSRF_FOLDER_NAME, SERVERNAMERUN FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", &processInstanceId, &sFolderName, &sServerName);
Else
/* get servername from report node */;
SQLExec("SELECT DISTNODENAME, OPSYS from PS_CDM_DIST_NODE", &distNode, &os);
/* WinMessage(&distNode);*/
SQLExec("SELECT SERVERNAME from PS_SERVERDEFN WHERE DISTNODENAME = :1 and OPSYS = :2", &distNode, &os, &sServerName);
End-If;

/* if method parameters exist for folder and server, otherwise use default values */
If &folderName = "" Then
&folderName = &sFolderName;
End-If;
If &folderName = "" Then
&folderName = %This.getDefaultFolderName();
End-If;
&Post.ReportFolder = &folderName;

If &serverName = "" Then
&serverName = &sServerName;
End-If;
&Post.ServerName = &serverName;

If All(&dExpire) Then
&Post.ExpirationDate = &dExpire;
Else
/* &Post.ExpirationDate = %DATE;*/
End-If;

&sOutDestFormat = "PDF";
&Post.OutDestFormat = &sOutDestFormat;
WriteToLog(%ApplicationLogFence_Level1, "The value is" | &processInstanceId);
If (&processInstanceId <> 0) Then
WriteToLog(%ApplicationLogFence_Level1, "Posting");
&Post.ProcessInstance = &processInstanceId;
End-If;

/* set distribution options */
Local string &sDistIdType_User, &sDistIdType_Role;
&oRptViewerArray = %This.getRuntimeDistribution(&processInstanceId, &oRptViewerArray);

If &oRptViewerArray <> Null Then
For &iViewer = 1 To &oRptViewerArray.Len
&sUserType = &oRptViewerArray [&iViewer].Type;
&sUserId = &oRptViewerArray [&iViewer].ID;
If (&sUserType = "2") Then
&sUserType = &sDistIdType_User;
End-If;
If (&sUserType = "3") Then
&sUserType = &sDistIdType_Role;
End-If;
WriteToLog(%ApplicationLogFence_Level1, "PostReportDistribute : usertype " | &sUserType | " userid " | &sUserId);
&Post.AddDistributionOption(&sUserType, &sUserId);
End-For;
Else
&Post.AddDistributionOption("2", %UserId);
REM &Post.AddDistributionOption("User", "PS");
rem WriteToLog(%ApplicationLogFence_Warning, "*** Warning! Report access is not set.");
End-If;

/* do post */
&Post.Put();

&RptInstance = &Post.ReportId;
&processInstanceId = &Post.ProcessInstance;


If (&RptInstance > 0) Then
MessageBox(0, "", 63, 119, "Successfully processed request with Rpt. ID %1 for Process %2 to post from directory %3", &RptInstance, &Post.ProcessName, &Post.SourceReportPath);
Else
MessageBox(0, "", 63, 122, "Not successful for process request for Process %1 to post from directory %2", &Post.ProcessName, &Post.SourceReportPath);
End-If;

If &RptInstance > 0 Then
WriteToLog(%ApplicationLogFence_Level1, "Publish : Report Instance : " | &RptInstance | " Process Instance : " | &processInstanceId);

/* formulate the report file url */
/* cannot be directly obtained from database (PS_CDM_LIST) since commit is not done yet.*/

<*
/*get distribution node url */
SQLExec("select C.URL from PSPRCSRQST A, PS_SERVERDEFN B, PS_CDM_DIST_NODE C WHERE A.SERVERNAMERUN = B.SERVERNAME AND B.DISTNODENAME = C.DISTNODENAME", &sDistNodeURL);
If %This.UseBurstValueAsOutputFileName Then
&sFileName = &sburstVal;
Else
&sFileName = &ID;
End-If;
*>
&sFileName = &sReportDescr;
&sFileName = &sFileName | "." | Lower(&sOutDestFormat);

&sRptURL = &sDistNodeURL | "/" | &RptInstance | "/" | &sFileName;
WriteToLog(%ApplicationLogFence_Level1, "distnode URL = " | &sDistNodeURL | " report URL : " | &sRptURL);
/* WinMessage("distnode URL = " | &sDistNodeURL | " report URL : " | &sRptURL);*/

Else
WriteToLog(%ApplicationLogFence_Error, "*** Warning! Report could not be posted");
End-If;

end-method;


method getRuntimeDistribution
/+ &processInstanceId as Number, +/
/+ &oRptViewerArray2 as Array of PSXP_RPTDEFNMANAGER:ReportViewer +/
/+ Returns Array of PSXP_RPTDEFNMANAGER:ReportViewer +/
Local PSXP_RPTDEFNMANAGER:ReportViewer &oRptViewer;
Local SQL &sqlDist;
Local string &distIdType, &distId, &sUserType;

&sqlDist = CreateSQL("SELECT DISTIDTYPE, DISTID FROM PS_PRCSRQSTDIST WHERE PRCSINSTANCE = :1", &processInstanceId);
While &sqlDist.Fetch(&distIdType, &distId);
If &distIdType = "2" Then
&sUserType = "USER";
Else
&sUserType = "ROLE";
End-If;
&oRptViewer = create PSXP_RPTDEFNMANAGER:ReportViewer(&sUserType, &distId);
WriteToLog(%ApplicationLogFence_Level1, "Distribution : IdType = " | &sUserType | " Id = " | &distId);

If (&oRptViewerArray2 = Null) Or
(&oRptViewerArray2.Len = 0) Then
&oRptViewerArray2 = CreateArray(&oRptViewer);
Else
&oRptViewerArray2.Push(&oRptViewer);
End-If;

End-While;
&sqlDist.Close();

Return &oRptViewerArray2;

end-method;

method writeOutputFile
/+ &strFileName as String, +/
/+ &strFileType as String +/
/+ Returns String +/
Local File &oFile, &oOutputDataXMLFile;
Local string &sFilePath, &sInput;

&sFilePath = &strFileName | &strFileType;
&oFile = GetFile(&sOutputFile, "R", %FilePath_Absolute);
If &oFile.IsOpen Then
&oOutputDataXMLFile = GetFile(&sFilePath, "W", "UTF8", %FilePath_Absolute);
If &oOutputDataXMLFile.IsOpen Then
&sInput = &oFile.GetString( True);
&oOutputDataXMLFile.WriteLine(&sInput);
&oOutputDataXMLFile.Close();
End-If;
&oFile.Close();
End-If;

Return &sFilePath;
end-method;

method deleteDataXML

Local string &sOutDest, &sXmlFile;
Local integer &i;
Local File &oFile;

&oFile = GetFile(&sOutputFile, "R", %FilePath_Absolute);
If &oFile.IsOpen Then
&oFile.Delete();
WriteToLog(%ApplicationLogFence_Level1, "Deleted Data file: " | &sOutputFile);
End-If;
RemoveDirectory(&sOutputDir, %FilePath_Absolute + %Remove_Subtree);
end-method;


5. You have to create your *.rtf template and place all the Data Source (XML File) within XMLP.
6. Don't forget the file layout within your AE.
7. Reports will show up within XMLP Report Manager

Update Trace definitions for better performance in PRD

-- Statement to select current date/time

SELECT to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;

-- Statement to select current DB

SELECT name from v$database;


--Select all process definitions that have a -TRACE flag
select * from PS_PRCSDEFN
WHERE PARMLIST LIKE '-TRACE%';

--Remove Trace Flags on Process Definitions


update ps_prcsdefn
set parmlist = ' ',parmlisttype = '0'
WHERE PARMLIST LIKE '-TRACE%';

commit;

Audits for Projects and Records - FInd null or blank descrs

Here is some sample SQL to find blank descrs within projects and records...etc. There should be some useful names to custom objects.

select
recname,
recdescr,
descrlong
from psrecdefn
where recname LIKE '%ASU%' -- This would be your custom naming convention
and recdescr in (' ','')


select
projectname,
projectdescr,
lastupddttm,
lastupdoprid,
descrlong
from PSPROJECTDEFN
where projectname like '%ASU%' --This would be your custom naming convention
and projectdescr in (' ', '')

Sample Pell and FA Disbursements query

Here is a good sample for FA disbursements: (Pell, Perkins...etc)

SELECT
AA.NATIONAL_ID,
',',
A.EMPLID,
',',
S.NAME,
',',
F.CAMPUS_FA,
',',
D.FEDERAL_ID,
',',
B.DISBURSED_BALANCE,
',',
SUM(B.DISBURSED_BALANCE)
FROM PS_PERS_NID AA,
PS_STDNT_AWARDS A,
PS_STDNT_AWRD_DISB B,
PS_DISB_ID_TBL C,
PS_ITEM_TYPE_FA D,
PS_STDNT_AWRD_ACTV E,
PS_STDNT_FA_TERM F,
PS_NAMES S
WHERE
A.EMPLID = AA.EMPLID
AND A.EMPLID = S.EMPLID
AND B.DISBURSED_BALANCE > 0
AND B.EMPLID = A.EMPLID
AND B.INSTITUTION = A.INSTITUTION
AND B.ITEM_TYPE = A.ITEM_TYPE
AND B.ACAD_CAREER = A.ACAD_CAREER
AND C.INSTITUTION = B.INSTITUTION
AND C.AID_YEAR = B.AID_YEAR
AND C.ACAD_CAREER = B.ACAD_CAREER
AND C.DISBURSEMENT_PLAN = A.DISBURSEMENT_PLAN
AND C.DISBURSEMENT_ID = B.DISBURSEMENT_ID
AND D.SETID = A.SETID
AND D.ITEM_TYPE = A.ITEM_TYPE
AND D.AID_YEAR = A.AID_YEAR
AND D.EFFDT =
(SELECT MAX(D1.EFFDT)
FROM PS_ITEM_TYPE_FA D1
WHERE D1.SETID = D.SETID
AND D1.ITEM_TYPE = D.ITEM_TYPE
AND D1.AID_YEAR = D.AID_YEAR
AND D1.EFFDT <= SYSDATE
)
AND D.DISBURSE_METHOD = 'A'
AND D.EFF_STATUS = 'A'
AND D.FA_SOURCE = 'F'
AND D.AGGREGATE_AREA IN ('FSEOG','PELL','PERKINS')
AND E.EMPLID = B.EMPLID
AND E.INSTITUTION = B.INSTITUTION
AND E.AID_YEAR = B.AID_YEAR
AND E.ITEM_TYPE = B.ITEM_TYPE
AND E.ACAD_CAREER = B.ACAD_CAREER
AND E.DISBURSEMENT_ID = B.DISBURSEMENT_ID
AND E.AWARD_DISB_ACTION = 'P'
AND E.DISB_AMOUNT > 0
AND E.ACTION_DTTM =
(SELECT MAX(E1.ACTION_DTTM)
FROM PS_STDNT_AWRD_ACTV E1
WHERE E1.EMPLID = E.EMPLID
AND E1.INSTITUTION = E.INSTITUTION
AND E1.AID_YEAR = E.AID_YEAR
AND E1.ITEM_TYPE = E.ITEM_TYPE
AND E1.ACAD_CAREER = E.ACAD_CAREER
AND E1.DISBURSEMENT_ID = E.DISBURSEMENT_ID
AND TRUNC(E1.ACTION_DTTM) <= SYSDATE
)
AND TRUNC(E.ACTION_DTTM) BETWEEN TO_DATE('01-JAN-2007','DD-MON-YYYY') AND TO_DATE('10-JAN-2008','DD-MON-YYYY')
AND F.EMPLID = A.EMPLID
AND F.INSTITUTION = A.INSTITUTION
AND F.STRM = C.STRM
AND F.AID_YEAR = A.AID_YEAR
AND F.EFFDT =
(SELECT MAX(F1.EFFDT)
FROM PS_STDNT_FA_TERM F1
WHERE F1.EMPLID = F.EMPLID
AND F1.INSTITUTION = F.INSTITUTION
AND F1.AID_YEAR = F.AID_YEAR
AND F1.STRM = F.STRM
AND F1.EFFDT <= SYSDATE
)
AND F.EFFSEQ =
(SELECT MAX(C1.EFFSEQ)
FROM PS_STDNT_FA_TERM C1
WHERE C1.EMPLID = F.EMPLID
AND C1.INSTITUTION = F.INSTITUTION
AND C1.AID_YEAR = F.AID_YEAR
AND C1.STRM = F.STRM
AND C1.EFFDT = F.EFFDT)
AND ROWNUM < 100
GROUP BY AA.NATIONAL_ID, A.EMPLID, S.NAME, F.CAMPUS_FA, D.FEDERAL_ID, B.DISBURSED_BALANCE
ORDER BY AA.NATIONAL_ID, A.EMPLID, S.NAME, D.FEDERAL_ID