Referral Information on TAM - Requiring referrals when applying for a position

Local Rowset &RS_Value;
&RS_Value = GetLevel0()(CurrentRowNumber()).GetRowset(Scroll.HRS_APP_PROFILE)(1).GetRowset(Scroll.HRS_AL_SRCE_I);

For &i = 1 To &RS_Value.ActiveRowCount

If &RS_Value(&i).HRS_AL_SRCE_I.HRS_SOURCE_ID.Value <> "" Or
&RS_Value(&i).HRS_AL_SRCE_I.HRS_SUBSOURCE_ID.Value <> "" Then
Error ("Please enter a referral source before continuing on.");
Else
&controller.nextPage();
&controller.hideunhideNextPrevious();
&controller.populateJobFamilies();

HRS_CECNFRM_MSG.HRS_SAVE_CONFM.Visible = False;
HRS_CECNFRM_MSG.HRS_APP_CHECK_ICN.Visible = False;
HRS_CECNFRM_MSG.HRS_APP_CHECK_ICN.Enabled = False;

End-If;

End-For;

Primary job / Peoplesoft - dealing with multiple jobs - JOB_INDICATOR

This solution is a great one to set the JOB_INDICATOR to primary. This logic is based upon max(annual_rt) and min empl_rcd if the annual_rt is the same for the jobs.

Currently, we have a AE process that will set all max current rows to N, and all future dated rows to N. Then we evaluate which jobs are primary.
Here is a sample:

/*** Update rows to N ***/

UPDATE PS_JOB A
SET A.job_indicator = 'N', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE A.job_indicator <> 'N'
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)


/*** Update future dated rows ***/
UPDATE PS_JOB A
SET A.job_indicator = 'N', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE A.job_indicator <> 'N'
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)

/*** Insert values into temp table for processing ***/
%InsertSelect(ASU_HR0022_TBL, JOB A, PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE), EMPLID = A.EMPLID, EMPL_RCD = A.EMPL_RCD, EFFDT = A.EFFDT, EFFSEQ = A.EFFSEQ, EMPL_STATUS = A.EMPL_STATUS, FTE = A.FTE,ANNUAL_RT = A.ANNUAL_RT, JOB_INDICATOR = 'P', ASU_OLD_JOB_IND = A.JOB_INDICATOR)
FROM PS_JOB A
WHERE 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.EMPL_STATUS NOT IN ('T','R')
AND A.ANNUAL_RT = (
SELECT MAX(A3.ANNUAL_RT)
FROM PS_JOB A3
WHERE A3.EMPLID = A.EMPLID
AND a3.empl_status NOT IN ('T','R')
AND a3.effdt = (
SELECT MAX(a3x.effdt)
FROM ps_job a3x
WHERE a3x.emplid = a3.emplid
AND a3x.empl_rcd = a3.empl_rcd
AND a3x.effdt <= sysdate)
AND a3.effseq = (
SELECT MAX(a3y.effseq)
FROM ps_job a3y
WHERE a3y.emplid = a3.emplid
AND a3y.empl_rcd = a3.empl_rcd
AND a3y.effdt = a3.effdt))
AND A.EMPL_RCD = (
SELECT MIN(A4.EMPL_RCD)
FROM PS_JOB A4
WHERE A4.EMPLID = A.EMPLID
AND a4.empl_status NOT IN ('T','R')
AND a4.annual_rt = a.annual_rt
AND a4.effdt = (
SELECT MAX(a4x.effdt)
FROM ps_job a4x
WHERE a4x.emplid = a4.emplid
AND a4x.empl_rcd = a4.empl_rcd
AND a4x.effdt <= sysdate)
AND a4.effseq = (
SELECT MAX(a4y.effseq)
FROM ps_job a4y
WHERE a4y.emplid = a4.emplid
AND a4y.empl_rcd = a4.empl_rcd
AND a4y.effdt = a4.effdt))

/*** Perform update ***/

UPDATE PS_JOB A
SET A.job_indicator = 'P', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE EXISTS (
SELECT 'X'
FROM PS_ASU_HR0022_TBL B
WHERE B.emplid = A.emplid
AND B.EMPL_RCD = A.EMPL_RCD
AND B.EFFDT = A.EFFDT
AND B.EFFSEQ = A.EFFSEQ
AND B.JOB_INDICATOR = 'P')
AND A.JOB_INDICATOR <> 'P'


/*** Peoplecode for report ***/

/*** Keep in mind, you will need to create a function for your own environment's path - J.McMahon ***/

Declare Function GetFilePathServer PeopleCode ASU_FILEPATH_WK.ECFILELISTPATH FieldFormula;

Local Record &Readrec, &RecLine, &Readrec2, &RecLine2;
Local File &MYFILE;
Local SQL &SQL2, &SQL3;
Local string &Filename;

GetFilePathServer(&outFilePath, &inFilePath, ASU_HR0022_AET.DBNAME);

If ASU_HR0022_AET.PROCESS_INSTANCE > 0 Then
If ASU_HR0022_AET.DBNAME = "ASUSAPRD" Then
&Filename = &outFilePath | "HR/" | "logs/ASU_PRIM_JOB_PRD.txt";
Else
&Filename = &outFilePath | "HR/" | "logs/ASU_PRIM_JOB.txt";
End-If;
Else
&Filename = "R:\temp\ASU_PRIM_JOB.txt";
End-If;

&MYFILE = GetFile(&Filename, "W", %FilePath_Absolute);

If &MYFILE.IsOpen Then
&MYFILE.WriteLine(" ");
&MYFILE.WriteLine(" ");
&MYFILE.WriteLine("Process Instance: " | ASU_HR0022_AET.PROCESS_INSTANCE | " - ASU_HR0022 - Primary Job Status Report");

Local number &ErrorCount;
&ErrorCount = 0;

SQLExec("select ASU_FILLER_N5 from SYSADM.PS_ASU_HR0022_VW", &ErrorCount);

&MYFILE.WriteLine(" ");
&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(" ");

/* Error Report */
&MYFILE.WriteLine(&ErrorCount | " - Errors found during this run");
&MYFILE.WriteLine(" ");

If &ErrorCount > 0 Then
&MYFILE.WriteLine(" EMPLID " | Char(44) | " Count ");
&Readrec = CreateRecord(Record.ASU_HR0022_VW_T);
&SQL2 = CreateSQL("SELECT * FROM SYSADM.PS_ASU_HR0022_VW");
While &SQL2.Fetch(&Readrec)
&MYFILE.WriteLine(&Readrec.EMPLID.Value | Char(44) | &Readrec.ASU_FILLER_N5.Value);
End-While;
&MYFILE.WriteLine(" ");

End-If;


&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(" ");

/* Success Report */

Local number &changedCount;
&changedCount = 0;

SQLExec("select COUNT(*) FROM PS_ASU_HR0022_TBL where process_instance = :1", ASU_HR0022_AET.PROCESS_INSTANCE, &changedCount);

&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(&changedCount | " - JOB records updated during this run - (INTO Temp Table - PS_ASU_HR0022_TBL");


&MYFILE.WriteLine(" ");
&MYFILE.WriteLine("JOB DETAILS:");
&MYFILE.WriteLine(" ");

&MYFILE.WriteLine("Process Instance" | Char(44) | " EMPLID " | Char(44) | " Empl Rcd " | Char(44) | " Effdt " | Char(44) | " Effseq " | Char(44) | "Empl Status" | Char(44) | " FTE " | Char(44) | " Annual Rt " | Char(44) | " Job Indicator " | Char(44) | "Old Job Ind");
&Readrec2 = CreateRecord(Record.ASU_HR0022_TMP);
&SQL3 = CreateSQL("SELECT A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, %DateOut(A.EFFDT), A.EFFSEQ, A.EMPL_STATUS, A.FTE, A.ANNUAL_RT, A.JOB_INDICATOR, A.ASU_OLD_JOB_IND FROM PS_ASU_HR0022_TBL A WHERE A.PROCESS_INSTANCE = :1", ASU_HR0022_AET.PROCESS_INSTANCE);
While &SQL3.Fetch(&Readrec2)
&MYFILE.WriteLine(&Readrec2.PROCESS_INSTANCE.Value | Char(44) | &Readrec2.EMPLID.Value | Char(44) | &Readrec2.EMPL_RCD.Value | Char(44) | &Readrec2.EFFDT.Value | Char(44) | &Readrec2.EFFSEQ.Value | Char(44) | &Readrec2.EMPL_STATUS.Value | Char(44) | &Readrec2.FTE.Value | Char(44) | &Readrec2.ANNUAL_RT.Value | Char(44) | &Readrec2.JOB_INDICATOR.Value | Char(44) | &Readrec2.ASU_OLD_JOB_IND.Value);
End-While;
End-If;

&MYFILE.Close();

Creating Audit records - AUDIT_PERS_NID (sample)

I wanted to create a post about audits and how to work with audits within Peoplesoft. There are many different types of audits you can place within peoplesoft. I am going to discuss the main two.

(Database level audits and online audits)

Sample: Need to create a audit for when users change or update SSN (National_id)

Record that will be audited: PS_PERS_NID

Audit Record created: PS_AUDIT_PERS_NID
(Make sure all keys are removed from the audit record)

Add fields: AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN (Make fields required, check auto-update for AUDIT_STAMP)










Create Database Trigger:

CREATE OR REPLACE TRIGGER PERS_NID_TR
AFTER INSERT OR UPDATE OR DELETE ON PS_PERS_NID
FOR EACH ROW
DECLARE
V_AUDIT_OPRID VARCHAR2(64);
BEGIN
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);
IF INSERTING
THEN
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'A',:NEW.EMPLID,:NEW.COUNTRY,:NEW.NATIONAL_ID_TYPE,:NEW.NATIONAL_ID,:NEW.SSN_KEY_FRA,:NEW.PRIMARY_NID,:NEW.TAX_REF_ID_SGP,:NEW.LASTUPDDTTM,:NEW.LASTUPDOPRID);
ELSE
IF DELETING
THEN
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'D',:OLD.EMPLID,:OLD.COUNTRY,:OLD.NATIONAL_ID_TYPE,:OLD.NATIONAL_ID,:OLD.SSN_KEY_FRA,:OLD.PRIMARY_NID,:OLD.TAX_REF_ID_SGP,:OLD.LASTUPDDTTM,:OLD.LASTUPDOPRID);
ELSE
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'K',:OLD.EMPLID,:OLD.COUNTRY,:OLD.NATIONAL_ID_TYPE,:OLD.NATIONAL_ID,:OLD.SSN_KEY_FRA,:OLD.PRIMARY_NID,:OLD.TAX_REF_ID_SGP,:OLD.LASTUPDDTTM,:OLD.LASTUPDOPRID);
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'N',:NEW.EMPLID,:NEW.COUNTRY,:NEW.NATIONAL_ID_TYPE,:NEW.NATIONAL_ID,:NEW.SSN_KEY_FRA,:NEW.PRIMARY_NID,:NEW.TAX_REF_ID_SGP,:NEW.LASTUPDDTTM,:NEW.LASTUPDOPRID);
END IF;
END IF;
END PERS_NID_TR;
/


With a online audit, you would add this audit record to the record properties of PS_PERS_NID and migrate to PRD with those options you selected.

How to hide fields within subpage / page

Here is a great sample of Page.Activate peoplecode that will hide fields within a page or subpage. You can also use a group box around a subpage to hide all the fields within that group box.

&admin = "N";
SQLExec("select 'Y' from psroleuser where roleuser = :1 and rolename = 'RoleName'", %OperatorId, &admin);
If &admin = "Y" Then
DERIVED_TL_WEEK.TL_TA_CALC_PB.Visible = True;
End-If;

/*** Within Sub Page ***/
TL_LINK_WRK.TL_TEXT_LBL5.Visible = False;
TL_LINK_WRK.TL_TEXT_LBL1.Visible = False;
TL_LINK_WRK.TL_TEXT_LBL2.Visible = False;

SQL for Savings Bonds Interface

Here is a sample of a savings bond outbound interface.
(This would be your population query within a AE step)

SELECT NVL(A.EMPLID
, ' ')
, L.CHECK_DT
, 'B'
, NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN DED.SSN ELSE (
SELECT NID2.NATIONAL_ID
FROM PS_DEP_BENEF_NID NID2
WHERE NID2.EMPLID = A.EMPLID
AND NID2.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
FROM PS_NAMES NAM
WHERE NAM.EMPLID = DED.EMPLID
AND NAM.EFFDT = (
SELECT MAX(NAM_ED.EFFDT)
FROM PS_NAMES NAM_ED
WHERE NAM.EMPLID = NAM_ED.EMPLID
AND NAM.NAME_TYPE = NAM_ED.NAME_TYPE
AND NAM_ED.EFFDT <= SYSDATE) ) ELSE (
SELECT NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
FROM PS_DEPENDENT_BENEF NAM
WHERE NAM.EMPLID = A.EMPLID
AND NAM.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT (CASE WHEN AD.COUNTRY = 'USA' THEN '0' ELSE '1' END)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT >= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT (CASE WHEN DEPAD.COUNTRY = 'USA' THEN '0' ELSE '1' END)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), '0') , (CASE WHEN B.BOND_DLVY_ID IN ('EE', ' ') THEN '0' ELSE '1' END) ,
NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS1
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS1
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN DED.NAME ELSE (
SELECT NAM.NAME
FROM PS_DEPENDENT_BENEF NAM
WHERE NAM.EMPLID = A.EMPLID
AND NAM.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS2
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS2
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS3
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS3
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.CITY
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.CITY
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.STATE
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.STATE
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT SUBSTR(AD.POSTAL
,1
,5)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT SUBSTR(DEPAD.POSTAL
,1
,5)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT SUBSTR(AD.POSTAL
,7
,10)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT SUBSTR(DEPAD.POSTAL
,7
,10)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , B.BOND_OTH_REG_TYPE, (CASE WHEN B.BOND_OTH_REG_TYPE = 'C' THEN 'OR'
WHEN B.BOND_OTH_REG_TYPE = 'B' THEN 'POD' ELSE ' ' END) , NVL((CASE WHEN B.BOND_OTH_REG_TYPE IN ('C', 'B')
AND B.BOND_OTH_REG_ID IN ('EE') THEN (
SELECT NVL(NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
, ' ')
FROM PS_NAMES NAM
, PS_ASU_DED_INTRFAC DED2
WHERE B.EMPLID = DED2.EMPLID
AND DED2.DEDCD = 'SAVBND'
AND NAM.EMPLID = B.EMPLID
AND NAM.EFFDT = (
SELECT MAX(NAM_ED.EFFDT)
FROM PS_NAMES NAM_ED
WHERE NAM.EMPLID = NAM_ED.EMPLID
AND NAM.NAME_TYPE = NAM_ED.NAME_TYPE
AND NAM_ED.EFFDT <= SYSDATE) ) WHEN B.BOND_OTH_REG_TYPE IN ('C','B')
AND B.BOND_OTH_REG_ID NOT IN ('EE')THEN (
SELECT NVL(D.FIRST_NAME||' '||substr(D.MIDDLE_NAME
, 1
,1)||' '||D.LAST_NAME
, ' ')
FROM PS_DEPENDENT_BENEF D
WHERE D.DEPENDENT_BENEF = B.BOND_OTH_REG_ID
AND B.EMPLID = D.EMPLID) END), ' ') , (CASE WHEN B.BOND_ID = '100' THEN '4' WHEN B.BOND_ID = '200' THEN '5' WHEN B.BOND_ID =
'500' THEN '6' WHEN B.BOND_ID = '1K' THEN '7' WHEN B.BOND_ID = '5K' THEN '8' WHEN B.BOND_ID = '10K' THEN '9' ELSE '0' END) ,
L.BOND_PURCH_UNITS, A.COMPANY, NVL(A.LOCATION, ' ') , NVL(DED.SSN, ' '), DED.PAY_END_DT, DED.PAY_END_DT, ' ', ' ', '9999999999'

FROM PS_ASU_DED_INTRFAC DED, PS_JOB A, PS_BOND_SPEC_DATA B, PS_BOND_LOG L
WHERE 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.EMPL_STATUS IN ('A','L','P','S')
AND DED.EMPLID = A.EMPLID
AND DED.DEDCD = 'SAVBND' /* Add your dedcd here */
AND DED.DED_CLASS = 'A'
AND A.JOB_INDICATOR = 'P'
AND B.EMPLID = A.EMPLID
AND B.COMPANY = 'Your Company Here' /* Add your company here */
AND B.EFFDT = (
SELECT MAX(B1.EFFDT)
FROM PS_BOND_SPEC_DATA B1
WHERE B1.EMPLID = B.EMPLID
AND B1.COMPANY = B.COMPANY
AND B1.PRIORITY = B.PRIORITY
AND B1.EFFDT <= SYSDATE)
AND B.EMPLID = L.EMPLID
AND B.BOND_ID = L.BOND_ID
AND B.BOND_OWNER_ID = L.BOND_OWNER_ID
AND L.BOND_PURCH_UNITS >= 1
AND L.PAY_END_DT = DED.PAY_END_DT

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

Decode Peoplecode - decodepc.java

decodePC.sqr as written by David L. Price has been rewritten to decodePC.java and expanded. It's general purpose is decoding PeopleCode stored as BLOB. The PeopleSoft database is accessed via a JDBC connection therefore there is no need to use Application Designer to look at PeopleCode, Application Engine programs, SQL, and XSLT. decodePC.java can read Application Engine programs and Application Packages and output them to a text file so you can get on with solving business problems.

Example of compiling decodePC.java with Oracle JDBC on a Unix box from a command prompt in the directory where decodePC.java exists:

javac -classpath /directorypath/ojdbc14.jar ./decodePC.java

Example of running decodePC.class with Oracle JDBC on a Unix box from a command prompt in the directory where decodePC.class exists:

java -classpath .:/directorypath/ojdbc14.jar decodePC

Download the code here from the site!

Oracle Disk Reads Script / Oracle Tuning Efforts

Here are some good scripts for Oracle tuning.

REM This SQL script will produce a current listing of
REM suspect SQL statements based on a specified
REM level of disk reads against the database instance.

SELECT DISK_READS, SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS >= &DISK_READS
ORDER BY DISK_READS DESC
/

REM This SQL script will display all current terminal
REM sessions attached to a specific database instance.

spool c:\temp\session.lis

SELECT a.sid sessionno,
substr(a.username,1,10) user_name,
substr(a.schemaname,1,10) schema,
substr(a.osuser,1,15) client_user,
substr(a.client_info,1,25) dbms_client_info,
substr(a.machine,1,15) wk_station,
substr(a.program,1,31) program_name,
a.status,
to_char(a.logon_time,'DD-MON-YYYY HH24:MM:SS') logontime,
b.name command_action
FROM v$session a, sys.audit_actions b
WHERE a.command = b.action
/

spool off


REM This SQL script will produce a current listing of
REM column statistics for a specific table.

col num_distinct format 999,999,999
col num_nulls format 999,999,999


SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME = upper('&TABLE_NAME')
ORDER BY COLUMN_NAME
/


REM This SQL script will change the SQL prompt to
REM refer to the instance being used at the time.
REM
REM This will eliminate the need to execute the
REM statement:
REM
REM SELECT name from v$database;


set termout off

column x new_value y

SELECT rtrim(instance, chr(0)) x
FROM v$thread
/

set sqlprompt '&Y>'
set termout on

REM
REM Encountering: SP2-0110: Cannot create save file "afiedt.buf"
REM
REM The user may not have permission to edit in that
REM specific directory.
REM

set editfile C:\TEMP\AFIEDT.BUF

REM
REM Set line and page size lengths
REM

set linesize 100
set pagesize 80

Peoplesoft Tools Tables

PSST0101 blog site had started listing out the tools tables. I wanted to post this listing on my blog as well. I have also provided the link to the orginal blog.

Enjoy

Projects

PSPROJECTDEFN — Project header table
PSPROJECTITEM — Definitions in the project
Fields

PSDBFIELD — Fields in the system
PSXLATITEM — Translate Values
Records

PSRECDEFN — Record header table
PSRECFIELD — Fields in the record (subrecords not expanded)
PSRECFIELDALL — Fields in the record (subrecords expanded)
PSKEYDEFN — Indexes
Pages

(Note: Pages still have the name panels in the PeopleTools table names)

PSPNLDEFN — Page header table
PSPNLFIELD — Page controls
PSPNLHTMLAREA — Static HTML Areas on Pages
Components

(Note: Components still have the name panel group in the PeopleTools table names)

PSPNLGRPDEFN — Component header table
PSPNLGROUP — Pages in the components
Menus

PSMENUDEFN — Menu header table
PSMENUITEM — Items (components) on the menu
Security

PSCLASSDEFN — Permission List header table
PSAUTHITEM — Menu items granted security by permission lists
PSROLEDEFN — Role header table
PSROLECLASS — Permission Lists in roles
PSOPERDEFN — User ID header table
PSROLEUSER — Roles granted to users
Portal

PSPRSMDEFN — Content References and Folders
Change Control

PSCHGCTLHIST — shows history of locked definitions with project name, incident, and description
PSCHGCTLLOCK — shows definitions that are currently locked
Application Engine


PSAEAPPLDEFN — header record; 1 row per app engine
PSAEAPPLSTATE — state records assigned to app engines
PSAEAPPLTEMPTBL — temp tables assigned to app engines
PSAESECTDEFN — sections
PSAESTEPDEFN — steps
PSAESTEPMSGDEFN
PSAESTMTDEFN — actions
HTML Definitions


PSCONTDEFN — header record; last update time, etc.
PSCONTENT — stores actual text in the HTML definition

Excellent Backup tool for source code

Here is a great site and software tool to backup source code. You can specify only certain file extensions *.sqr, *.sql, *.js etc. With this backup tool, you can also schedule backups to copy only the changes to the network share or jump drive.

This site is developed by a colleague of mine and his software is top shelf.
http://www.glaciermicrosystems.com/products/products.html

PS Queries / a user can not see / Security

This query was developed to capture a listing of queries that a user does not have access to.

SELECT DISTINCT A.OPRID, A.QRYNAME, A.DESCR, B.RECNAME
FROM PSQRYDEFN A, PSQRYRECORD B
WHERE A.OPRID = B.OPRID AND
A.QRYNAME = B.QRYNAME AND
A.QRYTYPE = 1
and not exists (select 'x' from PSROLECLASS D , PSROLEUSER E , PSQRYACCLSTRECS F
WHERE D.ROLENAME = E.ROLENAME AND
F.CLASSID = D.CLASSID AND
F.VERSION = ( SELECT VERSION FROM PSVERSION D WHERE D.OBJECTTYPENAME = 'QAL') AND
E.ROLEUSER = A.OPRID and
f.recname = b.recname)
and a.oprid = 'Place OPRID here'