SAP ABAP: Simple way to create ALV (REUSE_ALV_GRID_DISPLAY example)

1. Create report with internal table based on table spfli
2. Use function module REUSE_ALV_GRID_DISPLAY 

REPORT TEST.


data gs_spfli type spfli.
data it_spfli TYPE STANDARD TABLE OF spfli.

* select options
SELECT-OPTIONS so_carr for gs_spfli-carrid.


SELECT * FROM spfli INTO TABLE it_spfli WHERE carrid in so_carr.



CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
 EXPORTING
*   I_INTERFACE_CHECK                 = ' '
*   I_BYPASSING_BUFFER                = ' '
*   I_BUFFER_ACTIVE                   = ' '
*   I_CALLBACK_PROGRAM                = ' '
*   I_CALLBACK_PF_STATUS_SET          = ' '
*   I_CALLBACK_USER_COMMAND           = ' '
*   I_CALLBACK_TOP_OF_PAGE            = ' '
*   I_CALLBACK_HTML_TOP_OF_PAGE       = ' '
*   I_CALLBACK_HTML_END_OF_LIST       = ' '
    I_STRUCTURE_NAME                  = 'spfli' "Structure name
*   I_BACKGROUND_ID                   = ' '
*   I_GRID_TITLE                      =
*   I_GRID_SETTINGS                   =
*   IS_LAYOUT                         =
*   IT_FIELDCAT                       =
*   IT_EXCLUDING                      =
*   IT_SPECIAL_GROUPS                 =
*   IT_SORT                           =
*   IT_FILTER                         =
*   IS_SEL_HIDE                       =
*   I_DEFAULT                         = 'X'
*   I_SAVE                            = ' '
*   IS_VARIANT                        =
*   IT_EVENTS                         =
*   IT_EVENT_EXIT                     =
*   IS_PRINT                          =
*   IS_REPREP_ID                      =
*   I_SCREEN_START_COLUMN             = 0
*   I_SCREEN_START_LINE               = 0
*   I_SCREEN_END_COLUMN               = 0
*   I_SCREEN_END_LINE                 = 0
*   I_HTML_HEIGHT_TOP                 = 0
*   I_HTML_HEIGHT_END                 = 0
*   IT_ALV_GRAPHICS                   =
*   IT_HYPERLINK                      =
*   IT_ADD_FIELDCAT                   =
*   IT_EXCEPT_QINFO                   =
*   IR_SALV_FULLSCREEN_ADAPTER        =
* IMPORTING
*   E_EXIT_CAUSED_BY_CALLER           =
*   ES_EXIT_CAUSED_BY_USER            =
  TABLES
    T_OUTTAB                          = it_spfli "Internal table
* EXCEPTIONS
*   PROGRAM_ERROR                     = 1
*   OTHERS                            = 2
          .
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.

SAP ABAP: Simple class object example with execute

1. Report with static and class method execution

REPORT TEST_CALC. TYPES: RES TYPE P DECIMALS 4. *Class definition CLASS CCALC DEFINITION. PUBLIC SECTION. *Class methods METHODS ADD IMPORTING VALUE(CI1) TYPE I VALUE(CI2) TYPE I RETURNING VALUE(CRESULT) TYPE RES. METHODS SUBSTRACT IMPORTING VALUE(CI1) TYPE I VALUE(CI2) TYPE I RETURNING VALUE(CRESULT) TYPE RES. *Static methods CLASS-METHODS POWER IMPORTING VALUE(CI1) TYPE I VALUE(CTO2) TYPE I RETURNING VALUE(CRESULT) TYPE RES. PRIVATE SECTION. ENDCLASS. *Class implementation CLASS CCALC IMPLEMENTATION. METHOD ADD. CRESULT = CI1 + CI2. ENDMETHOD. METHOD SUBSTRACT. CRESULT = CI1 - CI2. ENDMETHOD. METHOD POWER. CRESULT = 1. DO CTO2 TIMES. CRESULT = CRESULT * CI1. ENDDO. ENDMETHOD. ENDCLASS. PARAMETERS: I1 TYPE I, I2 TYPE I. DATA: RESULT TYPE P DECIMALS 4, OCALC TYPE REF TO CCALC. START-OF-SELECTION. *Static method execution example RESULT = CCALC=>POWER( EXPORTING CI1 = I1 CTO2 = I2 ). WRITE: 'POWER result: ' , RESULT. * Class method execution example with object creation CREATE OBJECT OCALC. RESULT = OCALC->ADD( EXPORTING CI1 = I1 CI2 = I2 ). WRITE: 'ADD result: ' , RESULT. RESULT = OCALC->SUBSTRACT( EXPORTING CI1 = I1 CI2 = I2 ). WRITE : 'SUBSTRACT result: ' , RESULT.

2. Selection screen

 3. In order to add description to parameters I1 and I2 go to: Go to->Text element->Selection text


4. Result screen

SSIS SQL Server Integration Services - Using variable in SSIS Send Email Task, Send email to dynamic address

1. Create variables for FromLine, ToLine, Subject and Source

f.e EmailFrom, Email, EmailSource, EmailSubject


2. Fill variables (you can do it dynamically at script task)
3. Fill send email task as below

4. Fill expression tab as below

SAP ABAP: Get email from table with username

1. Create email variable at data section

DATA: L_SMTP_ADDR type adr6-SMTP_ADDR.


2. Get username from ADR6 and USR21 tables

select ADR6~SMTP_ADDR INTO L_SMTP_ADDR FROM
  USR21 LEFT OUTER JOIN ADR6 On
   USR21~addrnumber =  ADR6~addrnumber
                and USR21~persnumber =  ADR6~persnumber
  WHERE USR21~BNAME = sy-uname.
  ENDSELECT.

SSIS SQL Server Integration Services - execute query based on flat file csv datasource

SSIS SQL Server Integration Services - execute oracle query based on flat file csv datasource

1. Create variable to store file path

f.e. 

FilePath with value C:\file.txt

File.txt will be ";" separeted flat file. I will use second column of file with material numbers.

2. Create variable to store query you wish to execute

f.e Query which will later be referenced as User::Query

3. Create script task to read file contents and prepare query

4. Create Execute SQL Task to run query created inside previous script task, using variable User::Query

// Script

public void Main() { // TODO: Add your code here //get file path string FilePath = Dts.Variables["User::FilePath"].Value.ToString(); string temp; string email = "przemyslaw.wawrzyczek@valeo.com"; string content = ""; int i = 0; bool contentExists = false ; // Open file reader StreamReader sr = new StreamReader(FilePath); // Loop while end of file, read line while ((temp = sr.ReadLine()) != null) { // Split into columns string[] columns = temp.Split(';'); // Skip header if (i > 0) { //Get distinct values if (!content.Contains(columns[1])) { // add value content += "'" + columns[1] + "',"; email = columns[12].ToString(); contentExists = true; } } else { // open bracket content = "("; } i++; } //remove last coma content = content.TrimEnd(','); //close bracket content += ")"; if (contentExists) { // create query and pass it into ssis variable Dts.Variables["User::Query"].Value = "BEGIN DELETE FROM TABLE WHERE material_number in " + content + "; COMMIT; END;"; Dts.Variables["User::Email"].Value = email; content = content.Replace("'", ""); Dts.Variables["User::EmailSubject"].Value += " " + content; Dts.TaskResult = (int)ScriptResults.Success; } else { Dts.Variables["User::Query"].Value = ""; Dts.TaskResult = (int)ScriptResults.Failure; } }

Power Shell: Remove files, subfolders older than X days

Power Shell: Remove files, subfolders older than X days

1. Power shell script to remove files and subfolders older than 30 days from directory "E:\directory\

Get-ChildItem -path "E:\directory\" -recurse | where-object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)} | Remove-Item -Recurse -Force

2. Schedule script: E:\RemoveFiles.ps1

Action: Start a program

Program: PowerShell.exe

Add arguments (optiona): -ExecutionPolicy Bypass E:\RemoveFiles.ps1

ORACLE RMAN: Delete archivelogs older than X hours batch

ORACLE RMAN: Delete archive logs older than X hours

Oracle, RMAN

1. Create .bat script to start rman script

rman @C:\RMAN_delete.rman

2. Create rman script at location from previous script. Script is deleting archive logs older than hours without prompting.

CONNECT TARGET SYS/SysPassword

RUN

{

DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'sysdate-12/24';

}

EXIT;

3. Schedule .bat script at windows scheduler

4. Additionally setup retention policy at RMAN as below

SAP PU: Delete purchase requisition

SAP PU: Delete purchase requisition

SAP PU ME52

Single requisition

1. Go into ME52

2. Select item and press delete

3. Check deletion at transaction MD04 or SE16N (table: EBAN, deletion indicator not equal to 'X' expample below)

SSIS SQL Server Integration Services - convert string to number, replace special characters

SSIS SQL Server Integration Services - convert string to number, replace special characters

Type conversion

1. Use data conversion component 
   
2. Select desired type from data conversion component 
   
3. 
   

Replace special characters

1. Use delivered column component

2. Use formula expression to replace special charaters f.e REPLACE( [Target qty] , "-","0" )  replaces "-" with 0

SSIS SQL Server Integration Services - limit number of rows

SSIS SQL Server Integration Services - limit number of rows

SSIS Microsoft Sql server

1. Use Row Sampling component from toolbox

2. Double click at Row Sampling component to set number of rows

 3. Set Sampling selected output to redirect selected number of rows

4. Set Sampling unselected output to redirect all rows available

Google docs - Google Sheet - Microsoft Excel - remove polish letters from string

Google docs - Google Sheet - remove polish letters from string

Simple formula to remove polish letters from cell.

G.Docs

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Lower(G2);"ą";"a");"ć";"c");"ę";"e");"ł";"l");"ń";"n");"ó";"o");"ś";"s");"ź";"z");"ż";"z")


Excel

=PODSTAW(PODSTAW(PODSTAW(PODSTAW(PODSTAW(PODSTAW(PODSTAW(PODSTAW(PODSTAW(LITERY.MAŁE(G2);"ą";"a");"ć";"c");"ę";"e");"ł";"l");"ń";"n");"ó";"o");"ś";"s");"ź";"z");"ż";"z")

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';