I wanted to list some of the tools I have created recently in work place
1. Traversing SQR using PowerShell:
I have created a PowerShell process to traverse through a given SQR and provide process flow. It not only traverses through SQR, but also SQCs inside it. It will avoid infinite loops(a function being called from within).
2. Download SQR:
This script is designed to copy SQR files from all the instances (both NT and UNIX) to local machine When triggered, command prompt asks for SQR name as input. Script creates a folder with today's date along with SQR name. Ex: 20170116 - xxxhr401.sqr under: C:\Ujwal\. Copies SQRs from sqr/custom folder to the above newly created folder with appropriate naming convertion. Ex: hrnew90_Unix_xxxhr401.sqr. Openes WinMerge and shows compare results between SQR file from HRNEW90(Unix) with PSHRPRD (Unix). Openes PSHRPRD(Unix) file in Notepad++. It creates a LogFile.txt under newly created folder
3. Migrate SQR to all the instances:
This script is designed to copy SQR files from local machine to all the instances.
a. When triggered, command prompt asks for folder name and SQR name as inputs
b. Script creates a folder with name: Migration_Dev. Ex: Migration_Dev20170116 under: the specified folder
c. Backs up SQR versions from all the instances to above newly created folder with appropriate naming convertion. Ex: hrnew90_Unix_xxxhr401.sqr. This works same as download SQR
d. It creates a LogFile.txt under the given folder. And log file is opened in notepad++
4. PLSQL procedure to fetch process related information:
I have created a PLSQL procedure which will fetch below information, with process name as input
a. URL for the run control page (based on the instance we run in)
b. Navigation
c. process description
below information for all the previous runs
d. operator id and run control id
e. Begin, end times along with how long did it run for
f. server (NT/Unix)
g. Status along with status code
Output is something like this (Copy below result to a notepad++ for more readability)
Description: XXXBN645 - XXXBN645 - Cobra Interface
Output Destination: %%Log/Output Directory%%
Parameter List: -CT ORACLE -CS -CD xxxxprd -CA ACCESSID -CAP ACCESSPSWD -RP XXXBN645 -I 1723100 -R XXXbn645b -CO HRBATCH -OT 6 -OP "D:\psoft\hr90\xxxxprd\appserv\prcs\xxxxprd\log_output\SQR_XXXBN645_1723100" -OF 2 -LG ENG
Job Instance: 0
Process Instance: 1723100
Oprid: HRBATCH-System - Batch Processing
Run Control ID: XXXbn645b
Begin Time: 22-JAN-17 11:38:07 Sunday
End Time: 22-JAN-17 11:38:22 Sunday
Duration: 0 minutes
Server: PSNT
Run Status: 9(Success)
Job Instance: 0; Process Instance: 1723100; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645b ; Begin Time: 22-JAN-17 11:38:07 Sunday ; End Time: 22-JAN-17 11:38:22 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1723099; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645a ; Begin Time: 22-JAN-17 11:38:06 Sunday ; End Time: 22-JAN-17 11:38:22 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1721162; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645b ; Begin Time: 15-JAN-17 11:39:58 Sunday ; End Time: 15-JAN-17 11:40:14 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1721161; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645a ; Begin Time: 15-JAN-17 11:39:58 Sunday ; End Time: 15-JAN-17 11:40:14 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1719024; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645b ; Begin Time: 08-JAN-17 11:41:01 Sunday ; End Time: 08-JAN-17 11:41:17 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1719022; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645a ; Begin Time: 08-JAN-17 11:41:01 Sunday ; End Time: 08-JAN-17 11:42:07 Sunday ; Duration: 1 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1716832; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645a ; Begin Time: 01-JAN-17 12:10:36 Sunday ; End Time: 01-JAN-17 12:10:52 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1716833; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645b ; Begin Time: 01-JAN-17 12:10:36 Sunday ; End Time: 01-JAN-17 12:10:52 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1715152; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645a ; Begin Time: 25-DEC-16 11:35:48 Sunday ; End Time: 25-DEC-16 11:36:02 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
Job Instance: 0; Process Instance: 1715153; Oprid: HRBATCH-System - Batch Processing ; Run Control ID: XXXbn645b ; Begin Time: 25-DEC-16 11:35:47 Sunday ; End Time: 25-DEC-16 11:36:02 Sunday ; Duration: 0 minutes; Server: PSNT; Run Status: 9(Success)
5. MailMerge to create documentation of processes
Mailmerge is a simple tool to create template based reports. In this case, I have used it to gather general information regarding all the processes we run. for exaple, process navigation, most used run control id, records and fields used in run control page etc. I have gathered all these information into an excel and used mailmerge to create word document, in a more readable format. I have used different color font different information. For instance, I have used italic with light blue color font whereas process name was bold red. I dont have any coding for this.
6. Screenshots being taken using iMacro
This is not completed yet, but below is the procedure to accomplish
the above PLSQL has to be updated to loop through all the processes which are run in last 2 years.
a. Fetch URL for each process run control page
b. fetch run control record name (this is used in imacro)
c. fetch most used run control id (for instance a process is run 48 times in last 2 years 40 times with runcontrolid aaaa and 8 times bbbb. we will consider aaaa as our run ctrol id)
d. create iMacro script to open each componenet and take screen shot. name of the screen shot can be given with component/process name
e. create mailmerge to create a detailed document with screenshots we have taken from iMacro along with other information
below is code for each of the above-mentioned functionalities(sencitive information has been replaced accordingly)
1. Traversing SQR using PowerShell (this is a powershell script):
param (
[string]$BaseNavigation = "C:\Users\isdvurd\Desktop\Ujwal\Work GE\20160823 - SQR Processes",
[string]$outputFileName = "ProgramFlow_Output_5_TAX960ST.txt",
[string]$SQRsPath = "T:\Ujwal\20170124 - CompareSQRs UnixNt\Unix",
[string]$CustSQRsPath = "T:\Ujwal\20170124 - CompareSQRs UnixNt\Unix",
[string]$SQRName = "TAX960ST.sqr"
)
#This Powershell process will read through a single SQR process to get process flow. It will read all the SQCs involved in the process flow.
#Output is procedure names, with hierarchy and indentation
#If a procedure is already covered, it is not going to be repeated.
#
$global:AllProcesses1 = ""
$global:SQRsPath = $SQRsPath
$global:CustSQRsPath = $CustSQRsPath
cd $BaseNavigation
$OutputFile = ($BaseNavigation + "\" + $outputFileName)
Clear-Content $OutputFile
Add-Content $OutputFile ("SQR: " + $SQRName)
$StartTime = Get-Date
write-host Start Time: (Get-Date)
Add-Content $OutputFile ("Start Time" + $StartTime)
function getprocessnameSQR($In_filename, $In_FunctionName, $OutputFile, $AllProcesses, $Level){
$BeginProcedure = ("begin-procedure " + $In_FunctionName)
$Level = $Level + 1 # to add level
$Indentation = ($Indentation + " ") # Indentation
Add-Content $OutputFile $File
$Flag = "N"
If($In_filename -match ("^" + $BeginProcedure)) #Check if procedure definition exists in SQR
{
foreach ($Line1 in $In_filename) #Fetch SQR Row by row
{
If($Line1.trim() -match ("^" + $BeginProcedure)){ #If procedure exists, write to output
$Flag = "Y" #Flag is maintained to check if procedure definition closes (form begin-rpcedure till end-procedure)
write-host $Indentation $Line1.trim()
Add-Content $OutputFile ([string]$Level + $Indentation + $Line1.trim())
}
if ($Flag -eq "Y" ) # if with in the procedure, then only proceed
{
if ($Line1.trim() -match "^do "){ #check if any procedure is called
write-host $Indentation $Line1.trim()
Add-Content $OutputFile ([string]$Level + $Indentation + $Line1.trim())
$FunctionName = (($Line1.trim() -split '\(')[0].trim() -split '\s+')[1] #seperate procedure name from line line
write-host $Indentation $FunctionName
if ($global:AllProcesses1 -notmatch $FunctionName)
{ #fetch procedure only if it is not fetched previously
$global:AllProcesses1 = ($global:AllProcesses1 + " " + $FunctionName)
#------------------------------------------------------------------------------------
IF ($In_filename -MATCH ("BEGIN-PROCEDURE " + $FunctionName)) #if procedure definition exists in SQR, then call appropriate function
{
#getprocessnameSQR $sqrFile $FunctionName $OutputFile $AllProcesses $Level
getprocessnameSQR $In_filename $FunctionName $OutputFile $AllProcesses $Level
#break
}
ELSE
{
foreach ($Line2 in $In_filename -match "#include ") #Fetch all the lines starting wiht #include. This is to find SQCs being referred in SQR
{
If($Line2.tostring().trim() -match "^#include ") #Makesure that the line is not commented
{
$SQCNames = ($Line2.tostring().trim() -split '\''')[1] #get SQC name from line
$FilExists = Test-Path ($global:SQRsPath + "\" + $SQCNames) #check if file exists in Delivered SQR path
if(Test-Path ($global:SQRsPath + "\" + $SQCNames)) #if exists, proceed with below step
{
$FoundInSQC = get-content ($global:SQRsPath + "\" + $SQCNames) | select-string ("BEGIN-PROCEDURE " + $FunctionName) #check if procedure definition is present in the specific SQC
if($FoundInSQC -match "^begin-procedure") #if exists, proceed to fetch procedure definition
{
$sqcFile = get-content ($global:SQRsPath + "\" + $SQCNames)
Add-Content $OutputFile ([string]$Level + $Indentation + "SQC Name: " + $SQCNames)
getprocessnameSQC $sqcFile $FunctionName $OutputFile $AllProcesses $Level $SQCNames
#break
}
}
else #check if file exists in Custom SQR path
{
if(Test-Path ($global:CustSQRsPath + "\" + $SQCNames)) #if exists, proceed with below step
{
$FoundInSQC = get-content ($global:CustSQRsPath + "\" + $SQCNames) | select-string ("BEGIN-PROCEDURE " + $FunctionName) #check if procedure definition is present in the specific SQC
if($FoundInSQC -match "^begin-procedure") #if exists, proceed to fetch procedure definition
{
$sqcFile = get-content ($global:CustSQRsPath + "\" + $SQCNames)
Add-Content $OutputFile ([string]$Level + $Indentation + "SQC Name: "+$SQCNames)
getprocessnameSQC $sqcFile $FunctionName $OutputFile $AllProcesses $Level
#break
}
}
}
}
}
}
#----------------------------------------------------------------------------------------------
#getprocessnameSQR $In_filename $FunctionName $OutputFile $AllProcesses $Level
#break
}
else{
write-host ([string]$Level + $Indentation + $FunctionName + " is covered above")
Add-Content $OutputFile ([string]$Level + " " + $Indentation + "Procedure: " + $FunctionName + " is covered above")
}
}
If($Line1.trim() -match "^End-procedure") # if end-procedure is encountered
{
write-host $Indentation $Line1.trim()
Add-Content $OutputFile ([string]$Level + $Indentation + $Line1.trim())
$Flag = "N" #to indicate that procedure is completed
break
}
else #
{
if($Line.trim() -match "^begin-SELECT|^end-SELECT|^begin-SQL|^end-SQL")
{
Add-Content $OutputFile ( $Line.trim())
}
else{
$Table = $Line.trim() | Select-String -Pattern "PS.*?\s|PS.*?$|PS.*?\,|PS.*?!" -AllMatches | % { $_.Matches } | % { $_.Value }
if ($Table){
Add-Content $OutputFile (" " + [string]$Level + $Indentation + "Table Names: " + $Table)
}
If($Line.trim() -match "PS"){
Add-Content $OutputFile (" " + [string]$Level + $Indentation + "Complete Line: " + $Line.trim())
}
}
}
}
}
}
}
function getprocessnameSQC($In_filename, $In_FunctionName, $OutputFile, $AllProcesses, $Level){
$BeginProcedure = ("begin-procedure " + $In_FunctionName)
$Level = $Level + 1
$Indentation = ($Indentation + " ")
$Flag = "N"
foreach ($Line1 in $In_filename){
#$containsWord = $Line1 | %{$_ -match ("^" + $BeginProcedure) }
If($Line1.trim() -match ("^" + $BeginProcedure)){
$Flag = "Y"
write-host $Indentation $Line1.trim()
Add-Content $OutputFile ([string]$Level + $Indentation + $Line1.trim())
}
if ($Flag -eq "Y" ){
if ($Line1.trim() -match "^do "){
write-host $Indentation $Line1.trim()
Add-Content $OutputFile ([string]$Level + $Indentation + $Line1.trim())
$FunctionName = (($Line1.trim() -split '\(')[0].trim() -split '\s+')[1]
write-host $Indentation $FunctionName
if ($global:AllProcesses1 -notmatch $FunctionName)
{
$global:AllProcesses1 = ($global:AllProcesses1 + " " + $FunctionName)
If($In_filename -match ("^" + "begin-procedure " + $FunctionName))
{
getprocessnameSQC $In_filename $FunctionName $OutputFile $AllProcesses $Level
}
else
{
foreach ($Line2 in $In_filename -match "#include ")
{
If($Line2.tostring().trim() -match "^#include ")
{
if($Line2.tostring().trim() -match '''')
{
$SQCNames = ($Line2.tostring().trim() -split '\''')[1]
$FilExists = Test-Path ($global:SQRsPath + "\" + $SQCNames)
if(Test-Path ($global:SQRsPath + "\" + $SQCNames))
{
$FoundInSQC = get-content ($global:SQRsPath + "\" + $SQCNames) | select-string ("^BEGIN-PROCEDURE " + $FunctionName)
if($FoundInSQC -match "^begin-procedure")
{
$sqcFile = get-content ($global:SQRsPath + "\" + $SQCNames)
Add-Content $OutputFile ([string]$Level + $Indentation + "SQC Name: " + $SQCNames)
getprocessnameSQC $sqcFile $FunctionName $OutputFile $AllProcesses $Level
#break
}
}
else
{
if(Test-Path ($global:CustSQRsPath + "\" + $SQCNames))
{
$FoundInSQC = get-content ($global:CustSQRsPath + "\" + $SQCNames) | select-string ("BEGIN-PROCEDURE " + $FunctionName)
if($FoundInSQC -match "^begin-procedure")
{
$sqcFile = get-content ($global:CustSQRsPath + "\" + $SQCNames)
Add-Content $OutputFile ([string]$Level + $Indentation + "SQC Name: " + $SQCNames)
getprocessnameSQC $sqcFile $FunctionName $OutputFile $AllProcesses $Level
#break
}
}
}
}
}
}
}
#break
}
else{
write-host ([string]$Level + $Indentation + $FunctionName + " is covered above")
Add-Content $OutputFile ([string]$Level + " " + $Indentation + "Procedure: " + $FunctionName + " is covered above")
}
}
If($Line1.trim() -match "^End-procedure") {
write-host $Indentation $Line1.trim()
Add-Content $OutputFile ([string]$Level + $Indentation + $Line1.trim())
break
}
else
{
if($Line.trim() -match "^begin-SELECT|^end-SELECT|^begin-SQL|^end-SQL")
{
Add-Content $OutputFile ([string]$Level + $Indentation + $Line.trim())
}
else{
$Table = $Line.trim() | Select-String -Pattern "PS.*?\s|PS.*?$|PS.*?\,|PS.*?!" -AllMatches | % { $_.Matches } | % { $_.Value }
if ($Table){
Add-Content $OutputFile (" " + [string]$Level + $Indentation + "Table Names: " + $Table)
}
If($Line.trim() -match "PS"){
write-host " " $Line.trim()
Add-Content $OutputFile (" " + [string]$Level + $Indentation + "Complete Line: " + $Line.trim())
}
}
}
}
}
}
$sqrFile = Get-Content ($CustSQRsPath + "\" + $SQRName)
if(Test-Path ($CustSQRsPath + "\" + $SQRName))
{
$sqrFile = Get-Content ($CustSQRsPath + "\" + $SQRName)
}
else
{
if(Test-Path ($SQRsPath + "\" + $SQRName))
{
$sqrFile = Get-Content ($SQRsPath + "\" + $SQRName)
}
}
#$SQRName = "gex960lc.sqr"
$AllProcesses = ""
$global:AllProcesses1 = "" #to store procedure names. so that an individual procedure is not repeated.
foreach ($Line in $sqrFile) # Loop through each line in SQR
{
If($Line.trim() -match "^do ") #check if any procedure is called
{
write-host $Line.trim()
Add-Content $OutputFile $Line.trim()
$FunctionName = (($Line.trim() -split '\(')[0].trim() -split '\s+')[1] #Extract procedure name from line
$Level = 0 #to indicate iteration of a procedure
$Indentation = "" #to assign indentation to the output report
if ($global:AllProcesses1 -notmatch $FunctionName) #fetch a procedure only if it is not fetched previously
{
$global:AllProcesses1 = ($global:AllProcesses1 + " " + $FunctionName) # Add to the variable for future reference
IF ($sqrFile -MATCH ("BEGIN-PROCEDURE " + $FunctionName)) #if procedure definition exists in SQR, then call appropriate function
{
getprocessnameSQR $sqrFile $FunctionName $OutputFile $AllProcesses $Level
#break
}
ELSE
{
foreach ($Line2 in $sqrFile -match "#include ") #Fetch all the lines starting wiht #include. This is to find SQCs being referred in SQR
{
If($Line2.tostring().trim() -match "^#include ") #Makesure that the line is not commented
{
$SQCNames = ($Line2.tostring().trim() -split '\''')[1] #get SQC name from line
$FilExists = Test-Path ($SQRsPath + "\" + $SQCNames) #check if file exists in Delivered SQR path
if(Test-Path ($SQRsPath + "\" + $SQCNames)) #if exists, proceed with below step
{
$FoundInSQC = get-content ($SQRsPath + "\" + $SQCNames) | select-string ("BEGIN-PROCEDURE " + $FunctionName) #check if procedure definition is present in the specific SQC
if($FoundInSQC -match "^begin-procedure") #if exists, proceed to fetch procedure definition
{
$sqcFile = get-content ($SQRsPath + "\" + $SQCNames)
Add-Content $OutputFile ("SQC Name: " + $SQCNames)
getprocessnameSQC $sqcFile $FunctionName $OutputFile $AllProcesses $Level $SQCNames
#break
}
}
else #check if file exists in Custom SQR path
{
if(Test-Path ($CustSQRsPath + "\" + $SQCNames)) #if exists, proceed with below step
{
$FoundInSQC = get-content ($CustSQRsPath + "\" + $SQCNames) | select-string ("BEGIN-PROCEDURE " + $FunctionName) #check if procedure definition is present in the specific SQC
if($FoundInSQC -match "^begin-procedure") #if exists, proceed to fetch procedure definition
{
$sqcFile = get-content ($CustSQRsPath + "\" + $SQCNames)
Add-Content $OutputFile ("SQC Name: "+$SQCNames)
getprocessnameSQC $sqcFile $FunctionName $OutputFile $AllProcesses $Level
#break
}
}
}
}
}
}
}
else #if procedure is alrady fetched then dont fetch again
{
write-host ($FunctionName + " is covered above")
Add-Content $OutputFile (" " + "Procedure: " + $FunctionName + " is covered above")
}
}
else #fetch other than Do statemsts.
{
if($Line.trim() -match "^begin-SELECT|^end-SELECT|^begin-SQL|^end-SQL") #fetch Begin SQLs/selects
{
Add-Content $OutputFile ($Line.trim())
}
else{
$Table = $Line.trim() | Select-String -Pattern "PS.*?\s|PS.*?$|PS.*?\,|PS.*?!" -AllMatches | % { $_.Matches } | % { $_.Value } #Get Table Names
if ($Table){
Add-Content $OutputFile (" " + "Table Names: " + $Table)
}
If($Line.trim() -match "PS"){
write-host " " $Line.trim()
Add-Content $OutputFile (" " + "Complete Line: " + $Line.trim())
}
}
}
}
write-host all processes: $global:AllProcesses1
Add-Content $OutputFile ("All processes: " + $global:AllProcesses1)
$EndTime = Get-Date
write-host End Time: (Get-Date)
Add-Content $OutputFile ("End Time" + $EndTime)
$elapsedTime = $EndTime - $StartTime
write-host Elapsed Time: ($EndTime - $StartTime)
Add-Content $OutputFile ("Elapsed Time: " +$elapsedTime)
2. Download SQR (this is a .bat file):
rem This script is designed to copy SQR files from all the instances (both NT and UNIX) to T drive.
rem 1. When triggered, command prompt needs SQR name as input
rem 2. Script creates a folder with today's date along with SQR name. Ex: 20170116 - gexhr401.sqr under: T:\Ujwal\
rem 3. Copies SQRs from sqr/custom folder to the above newly created folder with appropriate naming convertion. Ex: hrnew90_Unix_gexhr401.sqr
rem 4. Openes WinMerge and shows compare results between SQR file from HRNEW90(Unix) with PSHRPRD (Unix).
rem 5. Openes PSHRPRD(Unix) file in Notepad++
rem 6. It creates a LogFile.txt under newly created folder
@echo off
for /F "usebackq tokens=1,2 delims==" %%i in (`wmic os get LocalDateTime /VALUE 2^>NUL`) do if '.%%i.'=='.LocalDateTime.' set ldt=%%j
set ldt=%ldt:~0,4%%ldt:~4,2%%ldt:~6,2%
echo Local date is [%ldt%]
SET /P SQR=Enter SQR Name:
rem SET SQR=gexpy010.sqr
set FolderName=T:\Ujwal\%ldt% - %SQR%
echo Start time: %TIME%>>LogFile.txt
set Path_pshrstat=cd /apps/hr/hrms90/pshrstat/sqr/custom
set command_pshrstat_unix=get %SQR% pshrstat_Unix_%SQR%
set Path_pshrdev=cd /apps/hr/hrms90/pshrdev/sqr/custom
set command_pshrdev_unix=get %SQR% pshrdev_Unix_%SQR%
set Path_hrnew90=cd /apps/hr/hrms90/hrnew90/sqr/custom
set command_hrnew90_unix=get %SQR% hrnew90_Unix_%SQR%
set Path_pshrstg=cd /apps/hr/hrms90/pshrstg/sqr/custom
set command_pshrstg_unix=get %SQR% pshrstg_Unix_%SQR%
set Path_pshrtax=cd /apps/hr/hrms90/pshrtax/sqr/custom
set command_pshrtax_unix=get %SQR% pshrtax_Unix_%SQR%
set Path_pshrprd=cd /apps/hr/hrms90/pshrprd/sqr/custom
set command_pshrprd_unix=get %SQR% pshrprd_Unix_%SQR%
IF EXIST "%FolderName%" GOTO NOWINDIR
mkdir "%FolderName%"
cd "%FolderName%"
echo SQR name is: %SQR%>LogFile.txt
echo Create "%FolderName%">>LogFile.txt
:NOWINDIR
cd "%FolderName%"
echo open servername>ftp_test.txt
echo userid>>ftp_test.txt
echo password>>ftp_test.txt
echo binary>>ftp_test.txt
echo %Path_pshrstat%>>LogFile.txt
echo %Path_pshrstat%>>ftp_test.txt
echo %command_pshrstat_unix%>>LogFile.txt
echo %command_pshrstat_unix%>>ftp_test.txt
echo %Path_pshrdev%>>LogFile.txt
echo %Path_pshrdev%>>ftp_test.txt
echo %command_pshrdev_unix%>>LogFile.txt
echo %command_pshrdev_unix%>>ftp_test.txt
echo %Path_hrnew90%>>LogFile.txt
echo %Path_hrnew90%>>ftp_test.txt
echo %command_hrnew90_unix%>>LogFile.txt
echo %command_hrnew90_unix%>>ftp_test.txt
echo %Path_pshrstg%>>LogFile.txt
echo %Path_pshrstg%>>ftp_test.txt
echo %command_pshrstg_unix%>>LogFile.txt
echo %command_pshrstg_unix%>>ftp_test.txt
echo %Path_pshrtax%>>LogFile.txt
echo %Path_pshrtax%>>ftp_test.txt
echo %command_pshrtax_unix%>>LogFile.txt
echo %command_pshrtax_unix%>>ftp_test.txt
echo disconnect>>ftp_test.txt
echo bye>>ftp_test.txt
ftp -i -s:ftp_test.txt>>LogFile.txt
echo open servername>ftp_prd.txt
echo userid>>ftp_prd.txt
echo password>>ftp_prd.txt
echo binary>>ftp_prd.txt
echo %Path_pshrprd%>>LogFile.txt
echo %Path_pshrprd%>>ftp_prd.txt
echo %command_pshrprd_unix%>>LogFile.txt
echo %command_pshrprd_unix%>>ftp_prd.txt
echo disconnect>>ftp_prd.txt
echo bye>>ftp_prd.txt
ftp -i -s:ftp_prd.txt>>LogFile.txt
set Command_pshrstat=Z:\pshrstat\sqr\custom\%SQR% "%FolderName%\pshrstat_NT_%SQR%"
set Command_pshrdev=Z:\pshrdev\sqr\custom\%SQR% "%FolderName%\pshrdev_NT_%SQR%"
set Command_hrnew90=Z:\hrnew90\sqr\custom\%SQR% "%FolderName%\hrnew90_NT_%SQR%"
set Command_pshrstg=Z:\pshrstg\sqr\custom\%SQR% "%FolderName%\pshrstg_NT_%SQR%"
set Command_pshrtax=Z:\pshrtax\sqr\custom\%SQR% "%FolderName%\pshrtax_NT_%SQR%"
set Command_pshrprd=\\pshrkdcweb01\psprod\psoft\hr90\pshrprd\sqr\custom\%SQR% "%FolderName%\migrate_%ldt%\pshrprd_NT_%SQR%"
echo %Command_pshrstat%>>LogFile.txt
copy %Command_pshrstat%>>LogFile.txt
echo %Command_pshrdev%>>LogFile.txt
copy %Command_pshrdev%>>LogFile.txt
echo %Command_hrnew90%>>LogFile.txt
copy %Command_hrnew90%>>LogFile.txt
echo %Command_pshrstg%>>LogFile.txt
copy %Command_pshrstg%>>LogFile.txt
echo %Command_pshrtax%>>LogFile.txt
copy %Command_pshrtax%>>LogFile.txt
echo %Command_pshrprd%>>LogFile.txt
copy %Command_pshrprd%>>LogFile.txt
CALL C:\Tools\WinMergePortable\WinMergePortable.exe hrnew90_Unix_%SQR% pshrprd_Unix_%SQR%
call C:\Tools\Notepad++Portable\Notepad++Portable.exe pshrprd_Unix_%SQR%
rem start winword "C:\Users\isdvurd\Desktop\Ujwal\Work GE\20160823 - SQR Processes\Mailmerge\Process Details_1.docx"
echo Start time: %TIME%>>LogFile.txt
3. Migrate SQR to all the instances (this is a bat file):
rem This script is designed to copy SQR files from local machine to all the development instances.
rem 1. When triggered, command prompt needs folder name and SQR name as inputs
rem 2. Script creates a folder with name: Migration_Dev. Ex: Migration_Dev20170116 under: the specified folder
rem 3. Backs up SQR versions from all the instances to above newly created folder with appropriate naming convertion. Ex: hrnew90_Unix_gexhr401.sqr
rem 4. It creates a LogFile.txt under the given folder
@echo off
for /F "usebackq tokens=1,2 delims==" %%i in (`wmic os get LocalDateTime /VALUE 2^>NUL`) do if '.%%i.'=='.LocalDateTime.' set ldt=%%j
set ldt=%ldt:~0,4%%ldt:~4,2%%ldt:~6,2%h
echo Local date is [%ldt%]
SET /P FolderName=Folder Path:
SET /P SQR=Enter SQR Name:
rem SET SQR=gexpy010.sqr
rem set FolderName=T:\Ujwal\%ldt% - %SQR%
set Path_pshrstat=cd /apps/hr/hrms90/pshrstat/sqr/custom
set command_pshrstat_unix=get %SQR% pshrstat_Unix_%SQR%
set Path_pshrdev=cd /apps/hr/hrms90/pshrdev/sqr/custom
set command_pshrdev_unix=get %SQR% pshrdev_Unix_%SQR%
set Path_hrnew90=cd /apps/hr/hrms90/hrnew90/sqr/custom
set command_hrnew90_unix=get %SQR% hrnew90_Unix_%SQR%
set Path_pshrstg=cd /apps/hr/hrms90/pshrstg/sqr/custom
set command_pshrstg_unix=get %SQR% pshrstg_Unix_%SQR%
set Path_pshrtax=cd /apps/hr/hrms90/pshrtax/sqr/custom
set command_pshrtax_unix=get %SQR% pshrtax_Unix_%SQR%
IF EXIST "%FolderName%\Migration_Dev%ldt%" GOTO NOWINDIR
echo Create "%FolderName%\Migration_Dev%ldt%"
mkdir "%FolderName%\Migration_Dev%ldt%"
:NOWINDIR
cd "%FolderName%\Migration_Dev%ldt%"
echo Start time: %TIME%>>"%FolderName%\LogFile.txt"
echo open servername>ftp_dev.txt
echo userid>>ftp_dev.txt
echo password>>ftp_dev.txt
echo binary>>ftp_dev.txt
echo %Path_pshrstat%>>ftp_dev.txt
echo %command_pshrstat_unix%>>ftp_dev.txt
echo %Path_pshrdev%>>ftp_dev.txt
echo %command_pshrdev_unix%>>ftp_dev.txt
echo %Path_hrnew90%>>ftp_dev.txt
echo %command_hrnew90_unix%>>ftp_dev.txt
echo %Path_pshrstg%>>ftp_dev.txt
echo %command_pshrstg_unix%>>ftp_dev.txt
echo %Path_pshrtax%>>ftp_dev.txt
echo %command_pshrtax_unix%>>ftp_dev.txt
echo disconnect>>ftp_dev.txt
echo bye>>ftp_dev.txt
ftp -i -s:ftp_dev.txt>>"%FolderName%\LogFile.txt"
set Command_pshrstat=Z:\pshrstat\sqr\custom\%SQR% "%FolderName%\Migration_Dev%ldt%\pshrstat_NT_%SQR%"
set Command_pshrdev=Z:\pshrdev\sqr\custom\%SQR% "%FolderName%\Migration_Dev%ldt%\pshrdev_NT_%SQR%"
set Command_hrnew90=Z:\hrnew90\sqr\custom\%SQR% "%FolderName%\Migration_Dev%ldt%\hrnew90_NT_%SQR%"
set Command_pshrstg=Z:\pshrstg\sqr\custom\%SQR% "%FolderName%\Migration_Dev%ldt%\pshrstg_NT_%SQR%"
copy %Command_pshrstat%
copy %Command_pshrdev%
copy %Command_hrnew90%
copy %Command_pshrstg%
echo downloaded all the files
cd "%FolderName%"
set Path_pshrstat=cd /apps/hr/hrms90/pshrstat/sqr/custom
set command_pshrstat_unix=put %SQR%
set Path_pshrdev=cd /apps/hr/hrms90/pshrdev/sqr/custom
set command_pshrdev_unix=put %SQR%
set Path_hrnew90=cd /apps/hr/hrms90/hrnew90/sqr/custom
set command_hrnew90_unix=put %SQR%
set Path_pshrstg=cd /apps/hr/hrms90/pshrstg/sqr/custom
set command_pshrstg_unix=put %SQR%
echo open servername>ftp_dev.txt
echo userid>>ftp_dev.txt
echo password>>ftp_dev.txt
echo binary>>ftp_dev.txt
echo %Path_pshrstat%>>ftp_dev.txt
echo %command_pshrstat_unix%>>ftp_dev.txt
echo %Path_pshrdev%>>ftp_dev.txt
echo %command_pshrdev_unix%>>ftp_dev.txt
echo %Path_hrnew90%>>ftp_dev.txt
echo %command_hrnew90_unix%>>ftp_dev.txt
echo %Path_pshrstg%>>ftp_dev.txt
echo %command_pshrstg_unix%>>ftp_dev.txt
echo disconnect>>ftp_dev.txt
echo bye>>ftp_dev.txt
ftp -i -s:ftp_dev.txt>>"%FolderName%\LogFile.txt"
set Command_pshrstat="%FolderName%\%SQR%" Z:\pshrstat\sqr\custom\
set Command_pshrdev="%FolderName%\%SQR%" Z:\pshrdev\sqr\custom\
set Command_hrnew90="%FolderName%\%SQR%" Z:\hrnew90\sqr\custom\
set Command_pshrstg="%FolderName%\%SQR%" Z:\pshrstg\sqr\custom\
set Command_pshrtax="%FolderName%\%SQR%" Z:\pshrtax\sqr\custom\
echo %Command_pshrstat%>>"%FolderName%\LogFile.txt"
copy %Command_pshrstat%>>"%FolderName%\LogFile.txt"
echo %Command_pshrdev%>>"%FolderName%\LogFile.txt"
copy %Command_pshrdev%>>"%FolderName%\LogFile.txt"
echo %Command_hrnew90%>>"%FolderName%\LogFile.txt"
copy %Command_hrnew90%>>"%FolderName%\LogFile.txt"
echo %Command_pshrstg%>>"%FolderName%\LogFile.txt"
copy %Command_pshrstg%>>"%FolderName%\LogFile.txt"
echo %Command_pshrtax%>>"%FolderName%\LogFile.txt"
copy %Command_pshrtax%>>"%FolderName%\LogFile.txt"
echo uploaded to all the locations
echo End time: %TIME%>>"%FolderName%\LogFile.txt"
call C:\Tools\Notepad++Portable\Notepad++Portable.exe "%FolderName%\LogFile.txt"
4. PLSQL procedure to fetch process related information (this is a PLSQL procedure in Oracle):
CREATE OR replace PROCEDURE gex_get_me_details ( process_name IN VARCHAR2 ) AS
CURSOR run_details IS
SELECT prcsinstance ,
oprid ,
(
SELECT oprdefndesc
FROM psoprdefn@pshrprd x
WHERE x.oprid = a.oprid
) oprdefndesc ,
runcntlid ,
to_char ( enddttm,'DD-MON-RR HH24:MI:SS Day' ) enddttm ,
to_char ( begindttm,'DD-MON-RR HH24:MI:SS Day' ) begindttm ,
extract ( minute FROM ( enddttm - begindttm ) ) minutes ,
mainjobinstance ,
servernamerun ,
recurname ,
runstatus ,
(
SELECT xlatshortname
FROM psxlatitem
WHERE fieldname = 'RUNSTATUS'
AND fieldvalue = a.runstatus
) run_status_desc
FROM psprcsrqst@pshrprd a
WHERE prcsname = process_name
AND a.begindttm IS NOT NULL
-- and rownum <=10
ORDER BY a.begindttm DESC;
names_t run_details%ROWTYPE;
TYPE names_ntt
IS
TABLE OF names_t%TYPE; -- must use type
run_details_results NAMES_NTT;
pia_navigation VARCHAR2 ( 500 ) ;
pia_url VARCHAR2 ( 500 ) ;
process_descr VARCHAR2 ( 500 ) ;
parmlist_out VARCHAR2 ( 500 ) ;
outdest_out VARCHAR2 ( 500 ) ;
BEGIN
SELECT replace ( navigation,'',' > ' ) ,
decode (
(
SELECT upper ( ora_database_name )
FROM dual
) ,
'Instance1','http:///psp/instance1' ,
'instance2','http:///psp/instance2' ,
'instance3','http:///psp/instance3' ,
'instance4','http:///psp/instance4' ,
'instance5','http:///psp/instance5' ,
'instance6','http:///psp/instance6' ,
' ' )
||url
INTO pia_navigation,
pia_url
FROM (
SELECT sys_connect_by_path ( a.portal_label,'>>' ) navigation ,
'/EMPLOYEE/HRMS/c/'
|| a.portal_uri_seg1
|| '.'
|| a.portal_uri_seg2
|| '.'
|| a.portal_uri_seg3 url ,
a.portal_uri_seg1 menu_name ,
a.portal_uri_seg2 component_name ,
a.portal_objname portal_objname ,
a.portal_prntobjname portal_prntobjname ,
a.portal_uri_seg3 portal_uri_seg3 ,
a.portal_reftype portal_reftype ,
a.portal_label portal_label
FROM (
SELECT DISTINCT a.portal_name ,
a.portal_label ,
a.portal_objname ,
a.portal_prntobjname ,
a.portal_uri_seg1 ,
a.portal_uri_seg2 ,
a.portal_uri_seg3 ,
a.portal_reftype
FROM psprsmdefn@pshrprd a
WHERE portal_name = 'EMPLOYEE'
AND a.portal_uri_seg1 NOT LIKE '% - %'
AND portal_objname <> portal_prntobjname
AND NOT EXISTS
(
SELECT 'X'
FROM psprsmsysattrvl@pshrprd
WHERE portal_name = a.portal_name
AND portal_reftype = a.portal_reftype
AND portal_objname = a.portal_objname
AND portal_attr_nam = 'PORTAL_HIDE_FROM_NAV'
AND a.portal_objname NOT IN ( 'CO_NAVIGATION_COLLECTIONS',
'PORTAL_BASE_DATA' )
)
) a
WHERE portal_uri_seg2 LIKE
(
SELECT DISTINCT pnlgrpname
FROM ps_prcsdefnpnl@pshrprd
WHERE prcsname = process_name
)
START WITH a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR a.portal_objname = a.portal_prntobjname
)
WHERE navigation NOT LIKE '%Navigation Collections%';
dbms_output.Put_line ( 'URL is:' ) ;
dbms_output.Put_line ( pia_url ) ;
dbms_output.Put_line ( 'Navigation: '
|| pia_navigation ) ;
SELECT descr
INTO process_descr
FROM ps_prcsdefn@pshrprd
WHERE prcsname = process_name;
dbms_output.Put_line ( ' ' ) ;
dbms_output.Put_line ( 'Description: '
|| process_name
|| ' - '
|| process_descr ) ;
SELECT parmlist,
outdest
INTO parmlist_out,
outdest_out
FROM psprcsparms@pshrprd
WHERE prcsinstance =
(
SELECT max ( prcsinstance )
FROM psprcsrqst@pshrprd x
WHERE prcsname = process_name
) ;
dbms_output.Put_line ( 'Output Destination: '
||outdest_out ) ;
dbms_output.Put_line ( 'Parameter List: '
||parmlist_out ) ;
OPEN run_details;
FETCH run_details bulk collect
INTO run_details_results;
CLOSE run_details;
FOR indx IN 1..10
LOOP
IF indx = 1 THEN
dbms_output.Put_line ( 'Job Instance: '
||Run_details_results ( indx ) .mainjobinstance ) ;
dbms_output.Put_line ( 'Process Instance: '
||Run_details_results ( indx ) .prcsinstance ) ;
dbms_output.Put_line ( 'Oprid: '
||Run_details_results ( indx ) .oprid
|| '-'
|| Run_details_results ( indx ) .oprdefndesc ) ;
--DBMS_OUTPUT.PUT_LINE('Oprid: '||Run_Details_Results(indx).OPRID || '-'||Run_Details_Results(indx).oprdefndesc);
dbms_output.Put_line ( 'Run Control ID: '
||Run_details_results ( indx ) .runcntlid ) ;
dbms_output.Put_line ( 'Begin Time: '
||Run_details_results ( indx ) .begindttm ) ;
dbms_output.Put_line ( 'End Time: '
||Run_details_results ( indx ) .enddttm ) ;
dbms_output.Put_line ( 'Duration: '
||Run_details_results ( indx ) .minutes
|| ' minutes' ) ;
dbms_output.Put_line ( 'Server: '
||Run_details_results ( indx ) .servernamerun ) ;
dbms_output.Put_line ( 'Run Status: '
||Run_details_results ( indx ) .runstatus
||'('
||Run_details_results ( indx ) .run_status_desc
||')' ) ;
END IF;
dbms_output.Put_line ( 'Job Instance: '
||Run_details_results ( indx ) .mainjobinstance
|| '; Process Instance: '
||Run_details_results ( indx ) .prcsinstance
|| '; Oprid: '
||Run_details_results ( indx ) .oprid
|| '-'
|| Rpad ( Run_details_results ( indx ) .oprdefndesc,27 )
|| '; Run Control ID: '
|| Rpad ( Run_details_results ( indx ) .runcntlid,15 )
|| '; Begin Time: '
||Run_details_results ( indx ) .begindttm
|| '; End Time: '
||Run_details_results ( indx ) .enddttm
|| '; Duration: '
||Run_details_results ( indx ) .minutes
|| ' minutes; Server: '
||Run_details_results ( indx ) .servernamerun
|| '; Run Status: '
||Run_details_results ( indx ) .runstatus
||'('
||Run_details_results ( indx ) .run_status_desc
||')' ) ;
END LOOP;
END;
EXECUTE gex_get_me_details ( 'SCRTY_SJTUPD' ) ;
6. Screenshots being taken using iMacro (this is iMacro script run in mozilla firefox)
VERSION BUILD=9030808 RECORDER=FX
SET !TIMEOUT_PAGE 120
TAB T=1
URL GOTO=about:home
URL GOTO=http://SETUP_HRMS.SCRTY_SJT_RC.GBL
TAG POS=1 TYPE=TD ATTR=HEIGHT:25&&WIDTH:54%&&TXT:
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:login ATTR=ID:userid CONTENT=HRBATCH
SET !ENCRYPTION NO
TAG POS=1 TYPE=INPUT:PASSWORD FORM=ID:login ATTR=ID:pwd CONTENT=hrmsbtch
TAG POS=1 TYPE=INPUT:SUBMIT FORM=ID:login ATTR=NAME:Submit
FRAME NAME="TargetContent"
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:win0 ATTR=ID:RUN_CNTL_SCRTY3_RUN_CNTL_ID CONTENT=sjt_trans
TAG POS=1 TYPE=INPUT:BUTTON FORM=NAME:win0 ATTR=ID:#ICSearch
SAVEAS TYPE=PNG FOLDER=C:\Users\isdvurd\Documents\iMacros\Downloads FILE=SCRTY_SJT_RC
URL GOTO=http:///MANAGE_ANNUAL_TAX_RPTG_US.RUN_TAX960ST.USA
FRAME NAME="TargetContent"
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:win0 ATTR=ID:PRCSRUNCNTL_RUN_CNTL_ID CONTENT=tax960st_y_pa
TAG POS=1 TYPE=INPUT:BUTTON FORM=NAME:win0 ATTR=ID:#ICSearch
SAVEAS TYPE=PNG FOLDER=C:\Users\isdvurd\Documents\iMacros\Downloads FILE=RUN_TAX960ST
