Saturday, June 25, 2011

Oracle Version


Requirement is simple; you want to retrieve/recover the Version Information of your currently installed Oracle Database. Let us discuss most of the ways of doing so in Oracle & SQL Server.
Here are some of the ways to get it:

1) DBA_REGISTRY (Table) 

     SELECT * FROM DBA_REGISTRY;

SELECT comp_id, comp_name, version, status, namespace, control FROM DBA_REGISTRY WHERE comp_name LIKE '%Oracle%' ORDER BY comp_name;

This will give you the db version along with all the other oracle components installed.
Here is the result:
COMP_ID
COMP_NAME
VERSION
STATUS
NAMESPACE
CONTROL
CATALOG
Oracle Database Catalog Views
9.1.0.3.0
VALID
SERVER
SYS
CATPROC
Oracle Database Packages and Types
9.1.0.3.0
INVALID
SERVER
SYS

2)      V$VERSION (View)

SELECT * FROM V$VERSION

This is the result:
BANNER
Oracle Database 9G Enterprise Edition Release 9.1.0.3.0 - 64bi
PL/SQL Release 9.1.0.3.0 - Production
NLSRTL Version 9.1.0.3.0 - Production






3)      DBMS_DB_VERSION.VERSION (Procedure)

In case you are using SQL Assistant from Oracle you need the Server output to be set.

SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);
EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.RELEASE);

In case you are using 3rd Party Vendors for SQL Assistant as Benthic Golden Software you can have an output like this:
Press F10 or select View à DBMS OUTPUT Window
Then run the above code by F7. You will have the DBMS_OUTPUT.PUT_LINE generated output in the following window:

The result of the above query will be as:


4)      DBMS_UTILITY.DB_VERSION (Function)
DECLARE
mydb_version       varchar2 (200);
mydb_compatibility varchar2 (200);
BEGIN
dbms_utility.db_version( mydb_version, mydb_compatibility );
dbms_output.put_line( mydb_version );
dbms_output.put_line( mydb_compatibility );
END;
Here is the result using Benthic Golden Software.
 5)      DATABASE_PROPERTIES (Table)

SELECT * FROM database_properties WHERE property_name LIKE '%RDBMS%';

Here is the result:
PROPERTY_NAME
PROPERTY_VALUE
DESCRIPTION
NLS_RDBMS_VERSION
9.2.0.3.0
RDBMS version for NLS parameters


SQL Server Version Information
1)     
            1) SERVERPROPERTY()
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
GO

The result will be something like this:
10.0.1600.1
CTP
Developer Edition



SERVERPROPERTY can be used to extract other data as well including COLLATION, BUILD etc.
Refer to the following link for more details:

 2) @@VERSION
SELECT @@VERSION

The result will be something like this:
Microsoft SQL Server 2008 (SP1) - 10.0.2411.0 (X64)   Feb 21 2009
1:08:36   Copyright (c) 1988-2008 Microsoft Corporation Express
Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600)






Refer to the following link for more details:

Saturday, June 11, 2011

Automate SAP R/3 Login with QTP

Test automation for SAP R/3 applications using QTP has become quite an interesting topic and in absolute demand by most companies implementing SAP these days worldwide. As a result, it has become necessary to have, in hand, a ready library of functions which assist to implement QTP scripting for SAP R/3 application quickly and easily.

In this section, we will discuss how to login to SAP R/3 application using QTP and Vbscript when the user credentials are provided through an external spreadsheet. The steps towards building such script would be;
1)       Close any existing excel process running on the system.
2)       Open the spreadsheet where user credentials have been provided and read the 
           credentials.
3)       Read the details like Server name, Client details and Language from the spreadsheet
4)       Login to SAP.
5)       Quit Excel process and close all open objects.

Following sections helps to understand the code used for each steps.
1)       Close any existing excel process running on the system.

The  SystemUtil.CloseProcessByName(“Excel.exe”) method can be used to do this. One needs to note that SystemUtil is a QTP reserved object and can be used only with QTP.

However if one needs to achieve this using vbscript, then the following code may be used. This block of code may also be used to close multiple processes.

myComputer = "."

‘Enter the list of processes in the array. These processes will be closed by the block of code.
myArrayOfTargetProcess = Array("EXCEL.EXE")

Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" &myComputer & "\root\cimv2")
Set CollectionOfProcesses = objWMIService.ExecQuery("SELECT * FROM Win32_Process")

For Each IndividualProcess in CollectionOfProcesses
 For Each TargetProcess In myArrayOfTargetProcess
                                If LCase(IndividualProcess.Name) = LCase(TargetProcess) Then
                                intReturn = IndividualProcess.Terminate
 End If
                  Next
Next

2)       Open the spreadsheet where user credentials have been provided and read the   
          credentials.  For this, use the following code block.

    PathForLoginSpreadSheet = <enter the path for login spread sheet>
       'Create an excel application                   
                       Set  myExcelForLogin = CreateObject( "Excel.Application")
                      ‘Open the file in the specified location                     
                      myExcelForLogin.WorkBooks.open(PathForLoginSpreadSheet)
                      Set ExcelWorkBook = myExcelForLogin.ActiveWorkbook
                      Set ExcelLoginSheet = ExcelWorkBook.Worksheets("Login")

3)       Read the details like Server name, Client details and Language from the spreadsheet

Let’s assume that the data is entered in the second row of the excel sheet. Data for “Server”, “Client”, “User”, “Password” and “Language” are in the 2nd, 3rd, 4th, 5th and 6th column of the spreadsheet respectively.
This code is in continuation with the previous blocks.

With ExcelLoginSheet
               
                ‘Read the values for “Server”, “Client”, “User”, “Password” and “Language”
                SAPR3ServerName = .Cells(intRow,2)
                SAPR3Client = .Cells(intRow,3)
                SAPUserName=.Cells(intRow,4)
                SAPPassword=.Cells(intRow,5)
                UserLanguage = .Cells(intRow,6)
                               
End With                    
               
4)      Login to SAP.

Use the statement below:

SAPGuiUtil.AutoLogon SAPR3ServerName, SAPR3Client, SAPUserName, SAPPassword, UserLanguage

Please note that SAPGuiUtil.AutoLogon is a QTP reserved keyword.

5)      Quit all created objects.

    myExcelForLogin.Quit
                Set ExcelWorkBook =Nothing
    Set ExcelLoginSheet=Nothing
                Set myExcelForLogin =Nothing

Friday, June 10, 2011

Automatic Replenishment ~ Warehouse Management (WM) in SAP



This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock.
We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.  

Step 1:
Material Master has to be maintained with Warehouse Views.
Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained.



Configuration Changes:
Step 2:
Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types.


Movement type 320 is configured as the following and saved. We can define SRC for the Source storage bin so that no interim storage bin is taken into consideration.


Step 3:
Replenishment Control is defined for the storage type under Logistics Execution ->Warehouse management->Activities->Transfers-> Define Replenishment Control for Storage Type and saved.



Dedicated printers can be set up under Logistics Execution ->Warehouse management->Activities->Define Print Control for a particular storage type and saved





Step 4:
One job needs to be scheduled for each report. The report jobnames are randomly defined:

Report                 
~~~~~~~     
RLAUTA10          
RLLNACH1          
 .
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Tuesday, May 17, 2011

Hierarchical Query – Oracle 10g & SQL Server

The issue is simple.
You want a migration of your database from Oracle to SQL Server.
Hierarchical queries with parent child relationship inside the same table can be a pretty interesting area. In SQL Server 2000, we do not have any straightaway method to do so (unlike in Oracle 10g), along with customized output, but the pain is reduced in higher versions of SQL Server (SQL Server 2005 & 2008). Let us look at the basics quiet SIMPLY.
Oracle 10g
I will create the FAMILY table to explain this. However, there is a couple of assumption.
a)      The data in the FAMILY table does not belong to a person having fallen in love multiple times, resulting in multiple marriages, further resulting in multiple families. So, Sukhendu’s father and Rajkumar’s father is not the same person.
b)      Here null as parent_id means simply I am using it for this example. Definitely the Greater Grand Father also had his father.
However, be informed that Oracle can take care of a person having multiple families as well.

Person_ID
Person
Parent_ID
5
Sukhendu’sGreaterGrandFather
null
4
Sukhendu’sGreatGrandFather
5
3
Sukhendu’sGrandFather
4
2
Sukhendu’sFather
3
1
Sukhendu
2
0
Sukhendu’sBrother
2
55
Rajkumar’sGreaterGrandFather
null
44
Rajkumar’sGreatGrandFather
55
33
Rajkumar’sGrandFather
44
22
Rajkumar’sFather
33
11
Rajkumar
22
99
Rajkumar’sSister
22


LEVEL
Do have a look at this query.
SELECT person_id, LPAD (' ', LEVEL*3) ||Person as Person, parent_id, LEVEL
FROM family
START WITH parent_id IS NULL
CONNECT BY PRIOR person_id = parent_id;


Result:

Person_ID
Person
Parent_ID
LEVEL
5
Sukhendu’sGreaterGrandFather
null
1
4
      Sukhendu’sGreatGrandFather
5
2
3
         Sukhendu’sGrandFather
4
3
2
            Sukhendu’sFather
3
4
1
               Sukhendu
2
5
0
               Sukhendu’sBrother
2
5
55
Rajkumar’sGreaterGrandFather
null
1
44
      Rajkumar’sGreatGrandFather
55
2
33
         Rajkumar’sGrandFather
44
3
22
            Rajkumar’sFather
33
4
11
               Rajkumar
22
5
99
               Rajkumar’sSister
22
5


The topmost in the hierarchy gets the LEVEL 1 with its children designated as per hierarchy. LPAD introduces the blank space for tabbing the names as per LEVEL.
START WITH defines the root row(s) of the hierarchy.
CONNECT BY defines the relationship between parent and child in the hierarchy.
PRIOR is a unary operator. PRIOR can be on either side of the equality operator. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
SYS_CONNECT_BY_PATH function gives you the path from root to node (or topmost to bottom most in the hierarchy).

SYS_CONNECT_BY_PATH

SELECT person_id, LPAD (' ', LEVEL*3) ||Person as Person, LEVEL, SYS_CONNECT_BY_PATH (person,'/') PATH
FROM family
START WITH parent_id IS NULL
CONNECT BY PRIOR person_id = parent_id;


Person_ID
Person
LEVEL
PATH
5
Sukhendu’sGreaterGrandFather
1
/ Sukhendu’sGreaterGrandFather
4
      Sukhendu’sGreatGrandFather
2
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
3
         Sukhendu’sGrandFather
3
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
2
            Sukhendu’sFather
4
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
1
               Sukhendu
5
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/
Sukhendu
0
               Sukhendu’sBrother
5
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
55
Rajkumar’sGreaterGrandFather
1
/ Rajkumar’sGreaterGrandFather
44
      Rajkumar’sGreatGrandFather
2
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
33
         Rajkumar’sGrandFather
3
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather
22
            Rajkumar’sFather
4
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather
11
               Rajkumar
5
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar
99
               Rajkumar’sSister
5
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar’sSister


CONNECT_BY_ISLEAF

SELECT person, CONNECT_BY_ISLEAF ‘isLEAF?’, SYS_CONNECT_BY_PATH (person,'/') path 
from family
START WITH parent_id IS NULL
CONNECT BY PRIOR person_id = parent_id;


Person
isLEAF?
PATH
Sukhendu’sGreaterGrandFather
0
/ Sukhendu’sGreaterGrandFather
      Sukhendu’sGreatGrandFather
0
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
         Sukhendu’sGrandFather
0
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
            Sukhendu’sFather
0
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
               Sukhendu
1
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/
Sukhendu
               Sukhendu’sBrother
1
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
Rajkumar’sGreaterGrandFather
0
/ Rajkumar’sGreaterGrandFather
      Rajkumar’sGreatGrandFather
0
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
         Rajkumar’sGrandFather
0
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather
            Rajkumar’sFather
0
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather
               Rajkumar
1
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar
               Rajkumar’sSister
1
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar’sSister


CONNECT_BY_ROOT
SELECT person, CONNECT_BY_ROOT personOrigin’, SYS_CONNECT_BY_PATH (person,'/') path
FROM
family
START WITH
parent_id is null
CONNECT BY PRIOR person_id = parent_id;


Person
Origin
PATH
Sukhendu’sGreaterGrandFather
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather
      Sukhendu’sGreatGrandFather
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
         Sukhendu’sGrandFather
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
            Sukhendu’sFather
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
               Sukhendu
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/
Sukhendu
               Sukhendu’sBrother
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
Rajkumar’sGreaterGrandFather
Rajkumar’sGreaterGrandFather
/ Rajkumar’sGreaterGrandFather
      Rajkumar’sGreatGrandFather
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
         Rajkumar’sGrandFather
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather
            Rajkumar’sFather
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather
               Rajkumar
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar
               Rajkumar’sSister
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar’sSister


CONNECT_BY_ISCYCLE

Now let us assume that, Rajkumar is the father of his real father Rajkumar’sFather (whose real name is XYZ suppose). I know it’s hard to imagine, but hypothetically this definitely happens when your father is aged and looking up to you for all kind of support, guidance and warmth you got during childhood. Then you do the parental role. If this kind of looping occurs in your table then it can be fairly achieved through the pseudocolumn, CONNECT_BY_ISCYCLE, which will evaluate to"1", in these circumstances ( due to looping inside the table as explained later), along with NOCYCLE.  Let me redefine the family table as FAMILY_1 and eliminate the Sukhendu subgroup.

UPDATE family_1
SET parent_id = 11 ~ Rajkumar
WHERE person_id = 22 ~ Rajkumar’sFather


Person_ID
Person
Parent_ID
isLOOP?
55
Rajkumar’sGreaterGrandFather
null
0
44
Rajkumar’sGreatGrandFather
55
0
33
Rajkumar’sGrandFather
44
0
22
Rajkumar’sFather
11
0
11
Rajkumar
22
1
88
Rajkumar’sDaughter
11
0
99
Rajkumar’sSister
22
0


The query for the above table is as follows:

SELECT person_id, person, parent_id, CONNECT_BY_ISCYCLE ‘isLOOP?’
FROM family_1
START WITH parent_id IS NULL
CONNECT BY NOCYCLE PRIOR person_id = parent_id;

SQL SERVER 2000

In SQL Server 2000 the derivation of a hierarchical result set is simple, but the customizations which are often required for a hierarchical display of data are very much difficult.

SELECT f1.person_id, f1.person, f1.parent_id, f2.person AS PARENT
FROM family f1
LEFT JOIN family f2 ON f1.parent_id = f2.person_id

SQL SERVER 2005

We are very much aware of Common Table Expressions (CTE) introduced in the later versions of SQL Server 2000. These work very efficiently in case of recursive queries. Although there can be much complex implementation of the CTE, the simplest can take care of the hierarchy issue with lot more customizations than in SQL Server 2000. We can address the complications later but for the time being let us just look at the query which can handle the same with the LEVEL description.           

WITH FamilyHierarchy (parent_id, person_id, person, LEVEL)
AS
(
-- Identity Description
    SELECT f.parent_id, f.person_id, f.person,
        0 AS LEVEL
    FROM family AS f
    WHERE parent_id IS NULL
    UNION ALL
-- Hierarchical Identity Description
    SELECT f.parent_id, f.person_id, f.person,
        LEVEL + 1
    FROM family AS f
    INNER JOIN FamilyHierarchy AS fa
        ON f.parent_id = fa.person_id
)
-- Statement that executes the CTE
SELECT fa.parent_id, fa.person_id, fa.person, LEVEL
FROM FamilyHierarchy AS fa
;