Komandiruočių logas Excelyje

Asmeninis komandiruočių logas

Kadangi esu turėjęs daug ir dažnai komandiruočių, susigalvojau sistemą kaip jas sekti. Kelis kartus labai pasiteisino, kai net po 2 metų, teko pateikti duomenis auditui ar mokesčiams.

Yra dvi dalys: Excel failas ir katalogai. Pradėsiu nuo katalogų – reikia susikurti dviejų lygių katalogus. Pirmas pagal metus, antras su pirmos komandiruotės dienos data.

image.png

Excelyje suvesdavau visą informaciją, kuri padėtų pilnai atsekti, kur kada važiavau, kokia kaina ir t.t. Yra šiek tiek automatizacijos (geltonai nudažyti stulpeliai), kuri padeda greičiau suregistruoti įvairius prašymus darbovietės sistemose. Dažniausiai tenka registruoti du kartus – prieš kelionę ir grįžus.

Atsisiųsti Excel failą

  1. Komandiruotės pradžios ir pabaigos datos
  2. Automatiškai suskaičiuotas dienų ir naktų kiekis. Dienų kiekis reikalingas dienpinigiams, naktų kiekis viešbučiui.
  3. “Perdiems RQ” tiesiog flag’as kad užpildytas dienpinigų prašymas darbovietės sistemoje.
  4. “HR Online” tiesiog flag’as kad užpildytas komandiruotės prašymas darbovietės sistemoje.
  5. “Expenses subm” tiesiog flag’as kad išlaidos už komandiruotę užregistruotos darbovietės sistemoje.
  6. “per diems” suskaičiuoti dienpinigiai.
  7. “Flight” pilna skrydžio kaina rezervacijos metu.
  8. “Taxi/train” suma visų viešojo transporto ir taxi išlaidų.
  9. “Hotel viešbučio” kaina.
  10. “Hotel westexpress” tiesiog flag’as, ar rezervarcija buvo atlikta per kelionių agentūrą, ar per vidinę rezervacijų sistemą.
  11. “Flight westexpress” tiesiog flag’as, ar rezervarcija buvo atlikta per kelionių agentūrą, ar per vidinę rezervacijų sistemą.
  12. “Perdiems label” automatiškai sugeneruotas label vidinei sistemai, kai reikai pateikti prašymą dienpinigiams. Skirtingos įmonės turi skirtingus formatus.
  13. “Expense label” automatiškai sugeneruotas label vidinei sistemai, kai reikai pateikti prašymą išlaidoms kompensuoti. Skirtingos įmonės turi skirtingus formatus.
  14. “TEM ID” įdentifikatorius, kuris susieja dienpinigus ir išlaidas vidinėse įmonės sistemose. Dažniausiai kažkoks ID suteikiamas pildant dienpinigių prašymą, kurį paskui reikia nurodyti pildant išlaidas. Skirtingos įmonės turi skirtingus formatus ir metodus.

Date in ISO8601 Format in Oracle DB

Below the code example how to get date format as ISO8601. First example is current time zone. Second example is valid if your database is actually in UTC time zone.

SELECT to_char(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') AS ISO8601 FROM dual;
SELECT to_char(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS.FF6"Z"') AS ISO8601_UTC_format FROM dual;

Direct Links to SPM Incidents, Changes, Problems and Work Orders

Direct links to SPM items

Here is a list of hyperlink patterns and bolded text parts to create direct link to SPM (BMC Remedy IT SERVICE MANAGEMENT) items. Sorry, I don’t know which version of the software.

Change Request

https://[your domain]/arsys/forms/spm-app-prd/SHR%3ALandingConsole/Default+Administrator+View/?mode=search&F304255500=CHG%3AInfrastructure+Change&F1000000076=FormOpen&F303647600=SearchTicketWithQual&F304255610='1000000182'%3D%22CRQ000000243650%22

Incident

https://[your domain]/arsys/forms/spm-app-prd/SHR%3ALandingConsole/Default+Administrator+View/?mode=search&F304255500=HPD%3AHelp+Desk&F1000000076=FormOpenNoAppList&F303647600=SearchTicketWithQual&F304255610=%271000000161%27%3D%22INC000001964939%22

Work Order

https://[your domain]/arsys/forms/spm-app-prd/SHR%3ALandingConsole/Default+Administrator+View/?mode=search&F304255500=WOI%3AWorkOrder&F1000000076=FormOpenNoAppList&F303647600=SearchTicketWithQual&F304255610=%271000000182%27%3D%22WO0000001757117%22

Problem

https://[your domain]/arsys/forms/spm-app-prd/SHR%3ALandingConsole/Default+Administrator+View/?mode=search&F304255500=PBM%3AProblem+Investigation&F1000000076=FormOpenNoAppList&F303647600=SearchTicketWithQual&F304255610=%271000000232%27%3D%22PBI000000050210%22

Direct links to the consoles

Incident Management Console

https://[domain]/arsys/forms/spm-app-prd/HPD:Incident+Management+Console

Change Management Console

https://[domain]/arsys/forms/spm5-prd/CHG:Change%20Management%20Console

Work Order Console

http://[domain]/arsys/forms/spm-app-prd/WOI:WorkOrderConsole

Approval Console

https://[domain]/arsys/forms/spm-app-prd/Approval+Central/Default+Administrator+View/?cacheid=8ba20aad


SDLC Tools

Software Development Tools on Windows for ETL Developer

I will share my list of tools and how to setup on Windows 7. This page is under constant  change and reformatting.

Utilities

Windows Utilities

Windows environment variable editor Rapid Environment Editor portable

Windows uninstaller Revo Uninstaller Portable

Archiver 7-Zip

FAR Manager

Total Commander – windows file manager, FTP client and etc. Trial version, not allowed to use in commercial environment.

Host File Editor

Free Download Manager

Ditto Clipboard Manager

KeePass – Password manager

WinMerge – Compare text files

IrfanView – Image viewer and editor. Not allowed to use in commercial environment. Free alternative to use at work is JPEGView.

Agent Ransack/File Locator Lite – my favorite tool to search for files and file contents, especially for network drivers on Windows machines in corporate environments. Lite version allowed for commercial use.

Fonts

I used this blog to download fonts.

Editors

Text Editor – Notepad++,  Atom, Sublime

CSV Editor – CSVed

XML Editor – XMLSpy (paid)

Scripting

CygWin

It enables Linux command line (shell or bash) on Windows. Very convenient if whole ETL platform, such as Informatica, Oracle DB is deployed on Linux, but developer machines are running Windows.

Packages I have installed:

  • lynx – a text-based web browser.
  • wget – downloads files form web which supports http, https and ftp protocols.
  • curl – a tool to transfer data from or to a server. It is designed to work without user interaction.
  • rsync – handy tool for synchronizing stuff from one place to another (especially over the internet)
  • python3 – an interpreted programming language. I like to write shell scripts with it.
  • python3-setuptools – people use this when distributing their python stuff.
  • bzip2, tar, gzip
  • bash-completion – auto-complete for bash
  • vim, vim-common
  • tmux – a terminal multiplexer, allowing a user to access multiple separate terminal sessions inside a single terminal window or remote terminal session.
  • git – a distributed version control tool.
  • diffutils – is a package of several programs related to finding differences between files.
  • openssh – SSH client.
  • ca-certificates – allows your Cygwin environment to validate SSL certificates. Needed for git as well.
  • dos2unix – allows convert text files from windows EOL to linux and many other things.
  • chere – open Cygwin command prompt at any specific folder. Details Open Cygwin at a specific folder
  • libxml2 – XML library to work with XML files. I use it to query XML file with XPATH and format XML to canonical form in order to compare two simple XML files.

Command line call to install predefined packages from the list above:

setup-x86.exe --no-desktop --download --local-package-dir="C:\cygwin" --packages="lynx,wget,curl,rsync,python3,python3-setuptools,bzip2,tar,gzip,bash-completion,vim,vim-common,tmux,git,diffutils,openssh,ca-certificates,dos2unix,chere,libxml2"

Other useful links about Cygwin:

Context menu enhancers

Copying and sharing paths to files in local folders and network drives – Path Copy Copy

Allows copying Unix path format in Windows.

The Path Copy Copy contextual menu items
Copyright: https://pathcopycopy.github.io/

Set and Unset as Read-only to File and Folder Context Menu

Informatica Power Center Client Tools

Customizations

Categories list I changed font:

  • Expression editor – Consolas

  • SQL Editor – Consolas
  • All output windows – ProFont for Windows

WinMerge

WinMerge is an Open Source differencing and merging tool for Windows. WinMerge can compare both folders and files, presenting differences in a visual text format that is easy to understand and handle.

https://winmerge.org/downloads/?lang=en

Informatica Workflow is not Getting Scheduled

I just learned small detail, that even if you define a schedule in the Informatica Power Center Designer, it is not scheduled. You can check scheduling status in the Informatica Power Center Workflow Monitor. See – the status is empty.

Workflow scheduling status in the Workflow Monitor

Workflow scheduling status empty in the Workflow Monitor

And the worfklow schedule is defined as well.

Workflow schedule defined in Workflow Designer

Workflow schedule defined in Workflow Designer

The solution is that the workflow does not have Integration Service assigned. Once the service is assigned, it becomes Scheduled.

Assign Integration Service to the workflow

Assign Integration Service to the workflow

Now the status is correct – Scheduled

Workflow scheduling status in the Workflow Monitor

Workflow scheduling status “Scheduled” in the Workflow Monitor

ERwin How to Set Owner (Schema Prefix in Oracle Database)

Three options to get schema prefix in DDL SQL scripts generated in ERwin.

1. Global Setting

Erwin main menu Model -> Model Properties -> Set Default Owners…

2. Local Override in the Model

Open Physical view in the model

Right click on the table “Table Properties…” to open Oracle Table Editor. There is a column “Owner”. Press F2 and type in the name.

3. Local override in Forward Engineering Screen

In physical view open Forward Engineering dialog box.

At the bottom there is “Owner Override…” button.

DDL Generated Script

 

Installation of Power Exchange Client Tools

Client Exchange Navigator and plugins for Power Center Designer to import DB2 sources.

Prerequisites

  • You must and user account on the mainframe.
  • Close all Informatica Power Center client tools: Designer, Workflow Manager and etc.

Installation

Download setup from defined location and extract to your location. Run as Administrator  install.bat

Click Next

Enter licence. Get it from your colleague.

Accept default path. Click Next.

Review installation summary. Click Install.

Wait for installation complete. Click Done.

Restart Windows.

After restart, configure file dbmover.cfg file in these locations:

  • “C:\Informatica\PowerExchange10.1.0”
  • “C:\Informatica\PowerExchange10.1.0\bin32”

Verify if you can import Power Exchange source in the Designer.

Documentation

 

Power Exchange Documentation from Informatica

Installation of Data Integration Hub Client Plug-in for Power Center 10.1

This post describes how to install Data Integration Hub client plug-in for Power Center Designer version 10.1. After installation you will be able to create custom mappings and manipulate Data Integration Hub processes by using Data Integration Hub API.

Version of Power Center Client Tools and Data IntegrationHub (DIH) must match. In my case both Data Integration Hub Power Center Client 10.1.0

All Power Center client tools must be closed. Even better restart PC before installation.

In general these steps need to be completed:

  1. Installation of DIH client tool using DIH setup 10.1
  2. Downloading the patch from Informatica
  3. Fixing Informatica patch
  4. Registering the plug-ins

Installation of DcIH client tool using DIH setup 10.1

Installation screens are at the bottom of the post. Main screen is this one to enable only one option for plug-in.

Downloading the patch from Informatica

After installation Power Center Designer might crash as described here PowerCenter Designer Client crashes while opening mapping having DIH/DX transformation in PowerCenter 10.1

Download the patch and unzip the patch to DIH installtion directory. In my case to “C:\Informatica\DIH\DataIntegrationHub\powercenter”. Now it looks like this

Open the folder plugin101 and edit the file “DXPluginWow6432Node.reg” with text editor. As I’m using Informatica and DIH version 10.1, I have to edit last line in the file by adding the path to folder “plugin101”. Don’t forget double slash in the path

Before change [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Informatica\PowerMart Client Tools\10.1.0\Plugins\Informatica]
“DX”=”$DX_HOME$\\powercenter\\plugin100\\DXPlugin.dll”

After change [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Informatica\PowerMart Client Tools\10.1.0\Plugins\Informatica]
“DX”=”C:\\Informatica\\DIH\\DataIntegrationHub\\powercenter\\plugin100\\DXPlugin.dll”

Fixing Informatica patch

I think even the patch has a bug left, so you need to update the version number in the path to point to correct folder holding DLL file. Instead of pointing to version 10.0, we change it to 10.1

Before
change [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Informatica\PowerMart Client Tools\10.1.0\Plugins\Informatica]
“DX”=”C:\\Informatica\\DIH\\DataIntegrationHub\\powercenter\\plugin100\\DXPlugin.dll”

After
change [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Informatica\PowerMart Client Tools\10.1.0\Plugins\Informatica]
“DX”=”C:\\Informatica\\DIH\\DataIntegrationHub\\powercenter\\plugin101\\DXPlugin.dll”

Registering the plug-ins

After finished editing the Windows registry file, double click on it to add entries to Windows registry and register DIH extensions with Power Center Mapping Designer. Click Yes and OK. More relevant information at How To: Register DataExchange/Data Integration Hub plugin in powercenter?

Start Power Center Designer and you will see Advanced Transformation Toolbar with new DIH transformations. However they will be disabled.

Open repository used by Data Integration Hub and open mapping designer. The transformations become active. Otherwise transformations will be disabled in “regular” Power Center Repository.

Transformations added to the mapping designer canvas.

Installation Screens