SAP BW - Object requested is currently locked by user

SAP BW - Object requested is currently locked by user

SAP BW, SAP

1. Go to SM12, view list with desired username

2. Select all by CTRL + A

3. Delete locks

TSQL: PIVOT table with hours

TSQL: PIVOT table with hours

Transact-SQL

1. Table containg names and time

Columns: Name, CreatedOn

Table: RegisterTime

2. Query

SELECT * FROM

(SELECT Name, DATEPART(HOUR, CreatedOn) AS RcpHour 
          FROM RegisterTime) sourceTable
 PIVOT (COUNT(RcpHour) FOR RcpHour IN 
         ( [0],  [1],  [2],  [3],  [4],  [5], 
           [6],  [7],  [8],  [9], [10], [11], 
          [12], [13], [14], [15], [16], [17], 
          [18], [19], [20], [21], [22], [23])) as pvtTable

SAP: Stock movement (313,315)

SAP: Stock movement

1. Check current reference stock with transaction i.e.. MB53

Reference: 123456

Plant: PLA1

2. Go into MB1B - Transfer Posting

Reference: 123456

Plant: PLA1

Movement: 313

Storage location: SOUR

3. Fill material, receiving location, quantity and confirm.

Document posted message should appear.

4. Go to MB1B again fill data and confirm.

Reference: 123456

Plant: PLA1

Movement: 315

Storage location: DEST

ORACLE: Microsoft Access x64 connect to x86 oracle data source (workaround)

ORACLE: Microsoft Access x64 connect to x86 oracle data source (workaround)

Microsoft Access, Oracle

1. Install oracle drivers

2. Fill tnsnames.ora oracle file with connection to your server

Location i.e. : 

\oracle\product\10.2.0\client_1\network\admin

C:\app\oracle\product\11.2.0\client_1\Network\Admin

3. Add odbc connection 

C:\Windows\syswow64\odbcad32.exe

User: Oracle user name

Server: Server name from TNS names

4. Create symbolic link, junction to C:\Program Files (x86)

cmd

mklink /J "C:\Program Files (x86)\" "Program_Files_86"

5. Create shortcut "Micorsoft Access (x86) to Microsoft Access application with path below:

Location i.e. : 

 C:\Program_Files_86\Microsoft Office\OFFICE11\MSACCESS.exe

 C:\Program_Files_86\Microsoft Office\OFFICE12\MSACCESS.exe

6. Start Microsoft Access from shortcut and run odbc connection

SAP/ABAP: Delete dupliacates from grid (alv, lv)

SAP/ABAP: Delete dupliacates from grid (alv, lv)

ABAP/SQ02

1. Data code section

Declare alv as '%G00[]'

data: alv_t type char100 value '%G00[]'.
field-symbols: <table> type any table.

2. End of selection code section

- assign alv to any table

- sort table

- delete duplicates

assign (alv_t) to <table>.
if <table> is assigned.
  sort <table>.
  delete adjacent duplicates from <table>.
endif.

ORACLE: scheduled task (oracle scheduler)

ORACLE - Scheduled task (Oracle scheduler)

Oracle, PL/SQL

1. Create a program

Procedure name: READ_FILES_PROC

begin
dbms_scheduler.create_program(
       program_name => 'READ_FILES_PROG'
      ,program_type => 'STORED_PROCEDURE'
      ,program_action => 'READ_FILES_PROC'
      ,number_of_arguments => 0
      ,enabled => true
      ,comments => 'Read files from directory' );
end;

2. Create a schedule

Additional parameters info:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN12412

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name     => 'READ_FILES_SCH',
  start_date        => TO_TIMESTAMP('2014-06-28 06:15:00','YYYY-MM-DD HH24:Mi:SS'),
  end_date          => TO_DATE('9999-12-31','YYYY-MM-DD'),
  repeat_interval   => 'FREQ=MINUTELY; INTERVAL=15',
  comments          => 'Every 15 minutes');
END;

3. Create a job

begin
  dbms_scheduler.create_job(
       job_name => 'READ_FILES_JOB'
     , program_name =>'READ_FILES_PROG'
     , schedule_name =>'READ_FILES_SCH'
     , enabled => TRUE
     , comments => 'Read files every 15 minutes');
end;

4. Enable log

begin
DBMS_SCHEDULER.SET_ATTRIBUTE('READ_FILES_JOB','logging_level',DBMS_SCHEDULER.LOGGING_FULL);
end;

5.  Restart job and read log

Disable job

begin
DBMS_SCHEDULER.DISABLE('READ_FILES_JOB',TRUE);
end;

Enable job

begin
DBMS_SCHEDULER.ENABLE('READ_FILES_JOB');
end;

Display log

select * from dba_scheduler_jobs where owner like 'SCHEMA NAME';

SAP/ABAP: Connection to external database

SAP/ABAP: Connection to external database

SAP, ORACLE, ABAP

1. Transaction code: ST04 - Add connection

2. Press “Add DB Entry” and provide database information

3. Transaction code: DBCO and provide database password

Example INSERT INTO database

DATA:

     CONNECTION LIKE DBCON-CON_NAME VALUE 'TEST'.

Code section: Record processing:

EXEC SQL.

CONNECT TO :CONNECTION

ENDEXEC.

IF SY-SUBRC = 0.

EXEC SQL.

    SET CONNECTION :CONNECTION

ENDEXEC.

EXEC SQL.

INSERT INTO Table1 (Name)

VALUES ('Adam')

ENDEXEC.

EXEC SQL.

COMMIT

ENDEXEC.

EXEC SQL.

DISCONNECT :CONNECTION

ENDEXEC.

ENDIF.

Example SELECT FROM database

TYPES:

BEGIN OF TY_TABLE1,

NAME1 TYPE C LENGTH 40

END OF TY_TABLE1.

DATA:

C1 LIKE DBCON-CON_NAME VALUE 'TEST',

S_TABLE1 TYPE TY_TABLE1,

Z_NAME TYPE C LENGTH 40.

Code section: Record processing:

EXEC SQL.

     CONNECT TO :C1

ENDEXEC.

  IF SY-SUBRC = 0.

     EXEC SQL.

       SET CONNECTION :C1

     ENDEXEC.

     EXEC SQL.

       open dbcursor for select distinct Name

       from Table1

     ENDEXEC.

     DO.

       CLEAR S_TABLE1 .

       EXEC SQL.

         FETCH NEXT dbcursor INTO :S_TABLE1

       ENDEXEC.

       if sy-subrc = 0.

         Z_NAME = S_TABLE1-NAME1.

       else.

         exit.

       endif.

     ENDDO.

     EXEC SQL.

       close dbcursor

     ENDEXEC.

     EXEC SQL.

       DISCONNECT :C1

     ENDEXEC.

   ENDIF.