PC2

&Pswd = Decrypt("", ZIN_CEO_RPT_AET.PASSWORD.Value);
SQLExec("SELECT A.PRCSINSTANCE FROM PSPRCSRQST A WHERE A.PRCSNAME = 'HR' AND A.RUNSTATUS = 9 AND A.JOBINSTANCE = (SELECT JOBINSTANCE FROM PSPRCSRQST WHERE PRCSNAME = 'ZINEORPT' AND RUNSTATUS = 7 AND OPRID = :1 AND RUNCNTLID = :2)", %OperatorId, ZIN_CEO_RPT_AET.RUN_CNTL_ID.Value, &Prcs_instance);
&outfile1 = GetURL(URL.ZIN_PAYPREFIX) | "rep_" | &Prcs_instance | ".csv";
&zipfile = GetURL(URL.ZIN_PAYPREFIX) | "rep_" | &Prcs_instance | ".zip";
&zip_cmd = "zip -P " | &Pswd | " -j -m  " | "'" | &zipfile | "'" | " " | "'" | &outfile1 | "'";
&StatusCode = Exec(&zip_cmd, %Exec_Synchronous + %FilePath_Absolute);
/*Copy file onto process monitor */
SQLExec("SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE  PRCSINSTANCE = :1", ZIN_CEO_RPT_AET.PROCESSINSTANCE.Value, &Prcs_dir);
&prcsfile = &Prcs_dir | "/" | "Report_" | &Prcs_instance | ".zip";
&cp_cmd = "cp " | &zipfile | " " | &prcsfile;
Warning (&cp_cmd);
&Cp_Status = Exec(&cp_cmd, %Exec_Synchronous + %FilePath_Absolute);
&ArchiveSQL = CreateSQL("SELECT P.PRCSINSTANCE FROM PSPRCSRQST P WHERE P.PRCSNAME = 'HR' AND P.RUNDTTM <= SYSDATE - 90");
While &ArchiveSQL.FETCH(&Prcs_id);
Warning ("Archiving Process Instance is : " | &Prcs_id);
&rmfile = GetURL(URL.ZIN_PAYPREFIX) | "CEO_Report_" | &Prcs_id | ".zip";
&rm_cmd = "rm " | &rmfile;
Warning ("Remove command is : " | &rm_cmd);
&Arch_Sts = Exec(&rm_cmd, %Exec_Synchronous + %FilePath_Absolute);
If &Arch_Sts = 0 Then
      Warning ("Archiving  Successful");
   Else
      Warning &Arch_Sts;
      Warning ("Archiving   is not successful");
   End-If;
  
End-While;


!**************************************************************************
!* Procedure : Secure-file
!* Description: Secure-file
!**************************************************************************
begin-procedure Secure-file
let $zipfile = '{PAYPREFIX}' || 'CEO_Report_'|| $prcs_process_instance || '.zip'
Let $zip_cmd = 'zip -P ' || $Pswd   || ' -j -m  ' || '''' || $zipfile || '''' || ' ' || '''' || $outfile1 || ''''  
   
! show $zip_cmd

    call SYSTEM using $zip_cmd #move_status

 If #move_status<> 0
  show 'Securing CEO report is unsuccessful'                           
 else
  show 'Securing CEO report is successful'                           
 end-if
BEGIN-SELECT
R.PRCSOUTPUTDIR
 let $prcs_dir = &R.PRCSOUTPUTDIR
FROM PSPRCSPARMS R
WHERE R.PRCSINSTANCE = $prcs_process_instance
END-SELECT
let $prcsfile = $prcs_dir ||'/'|| 'CEO_Report_'|| $prcs_process_instance || '.zip'
let $cp_cmd = 'cp ' || $zipfile || ' ' || $prcsfile
! show $cp_cmd
call system using $cp_cmd #cp_status
   
 if #cp_status <> 0
  show 'File copy is unsuccessful'
 else
  show 'File copy is successful'
 end-if

end-procedure Secure-file
!**************************************************************************
!* Procedure : Archive-old-files
!* Description: Archive files older than 90 days
!**************************************************************************
begin-Procedure Archive-old-files
BEGIN-SELECT
P.PRCSINSTANCE
 let $prcs_inst = EDIT(&P.PRCSINSTANCE,'9999999')

 display 'Archive Processs instance is : '   noline
    display $prcs_inst

 let $rmfile = '{PAYPREFIX}' || 'CEO_Report_' || $prcs_inst ||'.zip'

 let $rm_cmd = 'rm ' || $rmfile
! show $rm_cmd
 call system using $rm_cmd #rm_status

 if #rm_status <> 0
  display 'Archive of file : ' noline
  display $rmfile  noline
  display ' is unsuccessful'
 else
  display 'Archive of file : ' noline
  display $rmfile  noline
  display ' is successful'
 end-if
FROM PSPRCSRQST P
WHERE P.PRCSNAME = 'HR173FC' AND P.RUNDTTM <= SYSDATE - 90
! Process archives files older than 90 days
END-SELECT
end-Procedure Archive-old-files

&sFilePrefix = &TLO_Common.GetPSImportInputFileNameFormat(ZIN_TLO_IMP_AET.ZIN_TLO_IMPORTS.Value);
&ErrLogFile = &TLO_Common.GetTCCArchivePath() | "\" | &sFilePrefix | "ErrLog_" | String(ZIN_TLO_IMP_AET.PROCESS_INSTANCE.Value) | "_" | DateTimeToLocalizedString(%Datetime, "yyyyMMdd_HHmmss") | ".csv";
&SummLogFile = &TLO_Common.GetTCCArchivePath() | "\" | &sFilePrefix | "SummLog_" | String(ZIN_TLO_IMP_AET.PROCESS_INSTANCE.Value) | "_" | DateTimeToLocalizedString(%Datetime, "yyyyMMdd_HHmmss") | ".csv";

!***************************************************************************
! Procedure: Text-Convert
! Function:  Convert ascii character
!***************************************************************************
begin-procedure Text-Convert($input, :$output)
Let #length = length($input)
Let $output = $input
Let #i = 1
While #i <= #length
 Let $val = substr($input,#i,1)
 Let #char = ascii($val)
 Let $char_val = ''
 do Get-Translate($val, $char_val)
 if $char_val <> ''
 Let $output = replace($output,$val,$char_val)
 end-if
 Add 1 to #i
end-while
end-procedure !Text-Convert

!***************************************************************************
! Procedure: Get-Translate
! Function:  Translate for non-english characters
!***************************************************************************
begin-procedure Get-Translate($val, :$char_val)
BEGIN-SELECT
CHARACTER_VALUE   &char_val
 Let $char_val = &char_val
! show '$char_val - ' $char_val
FROM PS_ZIN_SPLCHAR_MAP
WHERE (SYMBOLICID) = $val
end-select
end-procedure !Get-Translate
grep -i -e union *.sq* > Analysis.txt
lpad(to_char(#ESPP_Qtrly_Total_Participants),8,'0')
Let $ESPP_Qtrly_Not_usedtr= rpad($Blank,1874,' ')
--------------------------------------------------------------------
begin-procedure Format-Where-Clause
  move  'IN (''' to $WhereClause
  let #start_pos = 0
  let #count = 0
  while 1 = 1
    FIND $comma in $RptParm #start_pos #comma_loc
    if #comma_loc = -1
 if #count = 0
           let $SlctParm = rtrim(ltrim($RptParm,' '),' ')
           concat $SlctParm  with $WhereClause
           concat ''''           with $WhereClause
           let $WhereClause = $WhereClause || ')'
 else
           EXTRACT $SlctParm from $RptParm #start_pos #parm_len_last
           let $SlctParm = rtrim(ltrim($SlctParm,' '),' ')
           concat $SlctParm  with $WhereClause
           concat ''''           with $WhereClause
           let $WhereClause = $WhereClause || ')'
 end-if
 BREAK
    end-if
    let #parm_len = #comma_loc - #start_pos
    EXTRACT $SlctParm from $RptParm #start_pos #parm_len
    let $SlctParm = rtrim(ltrim($SlctParm,' '),' ')
    concat $SlctParm  with $WhereClause
    concat ''''           with $WhereClause
    concat ','''           with $WhereClause
    let #start_pos = #comma_loc + 1
    let #count = #count + 1
  
  end-while
end-procedure
----------------------------------------------------
%TruncateTable(%table(_TMP))
---------------------------------------------------
SELECT EMPLID, E."SUPERVISOR_ID"
  FROM (
 SELECT J1.EMPLID
 , J1.EMPL_RCD
 , J1.EMPL_STATUS
 , J1.POSITION_NBR
 , J1.REPORTS_TO
 , J2.EMPLID AS "SUPERVISOR_ID"
  FROM PS_JOB J1
  , PS_JOB J2
 WHERE J1.EFFDT = (
 SELECT MAX(EFFDT)
  FROM PS_JOB
 WHERE EMPLID = J1.EMPLID
   AND EMPL_RCD = J1.EMPL_RCD
   AND EFFDT <= SYSDATE)
   AND J1.EFFSEQ = (
 SELECT MAX(EFFSEQ)
  FROM PS_JOB
 WHERE EMPLID = J1.EMPLID
   AND EMPL_RCD = J1.EMPL_RCD
   AND EFFDT = J1.EFFDT)
   AND J1.REPORTS_TO = J2.POSITION_NBR
   AND J1.REPORTS_TO IS NOT NULL
   AND J1.REPORTS_TO <> ' '
   AND J2.EFFDT = (
 SELECT MAX(EFFDT)
  FROM PS_JOB
 WHERE EMPLID = J2.EMPLID
   AND EMPL_RCD = J2.EMPL_RCD
   AND EFFDT <= SYSDATE)
   AND J2.EFFSEQ = (
 SELECT MAX(EFFSEQ)
  FROM PS_JOB
 WHERE EMPLID = J2.EMPLID
   AND EMPL_RCD = J2.EMPL_RCD
   AND EFFDT = J2.EFFDT)
   and J2.HR_STATUS ='A') E START WITH E.EMPLID = (
 SELECT EMPLID
  FROM PS_ZIN_WHOS_R_CEO CEO
 WHERE CEO.EFFDT = (
 SELECT MAX(CEO1.EFFDT)
  FROM PS_ZIN_WHOS_R_CEO CEO1
 WHERE CEO1.EFFDT <= sysdate)) CONNECT BY NOCYCLE PRIOR E.EMPLID = E."SUPERVISOR_ID"
   AND LEVEL <= 13;