wawrzyk3.blogspot.com: 2022

Tuesday, June 7, 2022

Google BigQuery - simple function to read XML tags with Regex

1. Simple SQL function to read XML tags from string using regex

CREATE OR REPLACE FUNCTION `PROJECT.TEST.readXML`(temp1 STRING, tag STRING) RETURNS STRING AS (
REGEXP_SUBSTR(temp1,CONCAT("<",tag,">(.*?)<\\/",tag,">"))
);

 

2. Usage of function

DECLARE example STRING DEFAULT "'<TELEGRAM><Equipment>Welding machine</Equipment><State>SENT</State></TELEGRAM>'";

SELECT 
TEST.readXML(example,"Equipment") as Equipment,
TEST.readXML(example,"State") as State,
TEST.readXML(example,"TELEGRAM") as TELEGRAM

Google BigQuery SQL - calculate production by each hour of the shift

1. Change the date of 3rd shift after midnight to day -1

SELECT  
    -- Change the date of 3rd shift after midnight to day -1
    IF (TIME(ProdDateTime) >= '00:00:00' and TIME(ProdDateTime) < '06:00:00',DATE_ADD(DATE(ProdDateTime), INTERVAL -1 DAY),DATE(ProdDateTime)) as Production_Date
...


2. Calculate the shift of production


SELECT  
    -- Calculate the shift
    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00', "1",
      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', "2","3"))
    as Shift

...

3. Calculate the time difference between the beginning of the shift and production time


SELECT
 -- Calculate the time difference between the begining of the shift and production date
IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00',TIME_DIFF(TIME(ProdDateTime),'06:00:00',HOUR)+1,
IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', TIME_DIFF(TIME(ProdDateTime),'14:00:00',HOUR)+1,
IF (TIME(ProdDateTime) >= '22:00:00' and TIME(ProdDateTime) <= '23:59:59', TIME_DIFF(TIME(ProdDateTime),'22:00:00',HOUR)+1,
TIME_DIFF(TIME(ProdDateTime),'00:00:00',HOUR)+1))) 
as HOUR_OF_PROD

...


4. Create the pivot table

WITH Production as
(
  SELECT  
    WorkCenter, SerialNumber, 
    -- Change the date of 3rd shift after midnight to day -1
    IF (TIME(ProdDateTime) >= '00:00:00' and TIME(ProdDateTime) < '06:00:00',DATE_ADD(DATE(ProdDateTime), INTERVAL -1 DAY),DATE(ProdDateTime)) as Production_Date,
    
    -- Calculate the shift
    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00', "1",
      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', "2","3"))
    as Shift,

    -- Calculate the time difference between the begining of the shift and production date
    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00',TIME_DIFF(TIME(ProdDateTime),'06:00:00',HOUR)+1,
      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', TIME_DIFF(TIME(ProdDateTime),'14:00:00',HOUR)+1,
      IF (TIME(ProdDateTime) >= '22:00:00' and TIME(ProdDateTime) <= '23:59:59', TIME_DIFF(TIME(ProdDateTime),'22:00:00',HOUR)+1,
      TIME_DIFF(TIME(ProdDateTime),'00:00:00',HOUR)+1))) 
    as HOUR_OF_PROD
FROM `PROJECT.TEST.TABLE`
)
select * from Production
PIVOT(COUNT(SerialNumber) FOR HOUR_OF_PROD IN (1,2,3,4,5,6,7,8))

5. Results

SAP - configure connection with external system with SAP BC and rfc idoc communication

1. SAP BC -> Adapters -> SAP -> Add sap server

(Previously create SAP user: SU01 tcode)

2. SAP BC -> Adapters -> SAP -> Choose SAP server -> Add listener to the server

(previously create PROGRAM_ID it in SMGW tcode)

3. Go to SAP BC-> Routing create routing rule for desired message typ

4. Create RFC connection in SM59 (test the connection)

5. Create the transnational RFC port in WE21 (add rfc destination from SM59)

6. Create a logical system in BD54

7.Create a partner profile with messages type in WE20

Thursday, January 27, 2022

Power shell - save sql query result to file

1. Simple script to run query and save it to file

$fileName = 'FILE_PATH'

$SQLServer = "SERVER_NAME"

$SQLDBName = "DATABASE_NAME"

$userid ="USER_NAME"

$password = "PASSWORD"

$delimiter = ";"

$SqlQuery = "SELECT * FROM TABLE_NAME";

#SQL Query

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $userid; Password = $password;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $SqlQuery

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

#Dataset and save to file

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$DataSet.Tables[0] | export-csv -Delimiter $delimiter -Path $fileName -NoTypeInformation

Powershell - upload file to ftp

1. Simple script for uploading files to ftp

$username = "FTP_USER"

$password = "FTP_PASSWORD"

$localFile = "LOCAL_FILE_PATH"

$remoteFile = "ftp://SERVER_ADDRESS/" + "FILENAME"

# Create FTP Rquest

$request = [System.Net.FtpWebRequest]::Create("$remoteFile")

$request = [System.Net.FtpWebRequest]$request

$request.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile

$request.Credentials = new-object System.Net.NetworkCredential($username, $password)

$request.UseBinary = $true

$request.UsePassive = $true

# Read the File

$fileContent = gc -en byte $localFile

$request.ContentLength = $fileContent.Length

$run = $request.GetRequestStream()

$run.Write($fileContent, 0, $fileContent.Length)

# Close and dispose connection

$run.Close()

$run.Dispose()

ABAP - Editing segment, field values for multiple idocs

1. Data declarations

* Document number

DATA: L_DOCNUM LIKE EDIDC-DOCNUM.

* Idoc data, status tables
DATA: itab_edidd LIKE TABLE OF EDIDD WITH HEADER LINE,
itab_edidc LIKE TABLE OF EDIDC WITH HEADER LINE,
itab_edids LIKE TABLE OF EDI_DS40 WITH HEADER LINE.

* Variables for field positioning
DATA: P_SUM TYPE N LENGTH 4,
      I_POS type I,
      I_SUM type I,
      I_LEN type I.

2. Create select-option and parameters

tables EDIDC.

* Document number

select-options S_DOCNUM for EDIDC-DOCNUM
OBLIGATORY NO INTERVALS .

* Name of the segment

parameters P_SNAME(100) type C
OBLIGATORY .

* Value of the field
parameters P_SVALUE(999) type C
OBLIGATORY .

* Position of the field in the segment
parameters P_POS(004) type N
OBLIGATORY .

* Length of the field
parameters P_LEN(004) type N
OBLIGATORY .

3. Program

data: s_sel like line of S_DOCNUM.

* Loop over select options
loop at S_DOCNUM into s_sel.

  CLEAR L_DOCNUM.
  CLEAR itab_edidc.
  CLEAR itab_edidd.
  CLEAR itab_edidc.

* Asssign doc nbr
L_DOCNUM = s_sel-low.

IF L_DOCNUM IS NOT INITIAL.

  CALL FUNCTION 'EDI_DOCUMENT_OPEN_FOR_EDIT'
    EXPORTING
      DOCUMENT_NUMBER   = L_DOCNUM
*     ALREADY_OPEN      = 'N'
    IMPORTING
      IDOC_CONTROL      = itab_edidc
    TABLES
      IDOC_DATA         = itab_edidd
    EXCEPTIONS
     DOCUMENT_FOREIGN_LOCK               = 1
     DOCUMENT_NOT_EXIST                  = 2
     DOCUMENT_NOT_OPEN                   = 3
STATUS_IS_UNABLE_FOR_CHANGING = 4
     OTHERS = 5
            .
  IF SY-SUBRC <> 0.
      MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
          WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

    LOOP AT itab_edidd.
      IF itab_edidd-segnam = P_SNAME.
        CLEAR P_SUM.

*   Calculate position of field
        I_POS = P_POS.
        I_LEN = P_LEN.
        I_SUM = I_POS + I_LEN.
        P_SUM = I_SUM.

*   Changing field value
        CONCATENATE
        itab_edidd-SDATA(P_POS)
        P_SVALUE(P_LEN)
        itab_edidd-SDATA+P_SUM INTO itab_edidd-SDATA
        RESPECTING BLANKS.

* Modify table with segment contents
MODIFY itab_edidd.

        EXIT.
      ENDIF.
    ENDLOOP.

    CALL FUNCTION 'EDI_CHANGE_DATA_SEGMENTS'
      TABLES
        idoc_changed_data_range = itab_edidd
      EXCEPTIONS
        idoc_not_open           = 1
        data_record_not_exist   = 2
        OTHERS                  = 3.

   IF SY-SUBRC <> 0.
        MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.

   CALL FUNCTION 'EDI_CHANGE_CONTROL_RECORD'
      EXPORTING
        idoc_changed_control         = itab_edidc
      EXCEPTIONS
        idoc_not_open                = 1
        direction_change_not_allowed = 2
        OTHERS                       = 3.

   IF SY-SUBRC <> 0.
        MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.

*   Modify table with idoc status
      REFRESH itab_edids.
      itab_edids-docnum      = L_DOCNUM.
      itab_edids-status      = 51.
      itab_edids-repid       = sy-repid.
      itab_edids-mandt       = sy-mandt.
      itab_edids-tabnam      = 'EDI_DS'.
      itab_edids-stamqu      = 'SAP'.
      itab_edids-stamid      = 'B1'.
      itab_edids-stamno      = 999.
      itab_edids-stapa1      = ''.
      itab_edids-stapa2      = ''.
      itab_edids-logdat      = sy-datum.
      itab_edids-logtim      = sy-uzeit.
      APPEND itab_edids.

    CALL FUNCTION 'EDI_DOCUMENT_CLOSE_EDIT'
      EXPORTING
       DOCUMENT_NUMBER        = L_DOCNUM
       DO_COMMIT              = 'X'
       DO_UPDATE              = 'X'
       WRITE_ALL_STATUS       = 'X'
*    STATUS_75              = '30'
     TABLES
       STATUS_RECORDS         = itab_edids
    EXCEPTIONS
      IDOC_NOT_OPEN          = 1
      DB_ERROR               = 2
      OTHERS                 = 3.

   IF SY-SUBRC <> 0.
        MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
   ENDIF.

ENDIF.
endloop.

4. How to use selection screen

Example of modification of storage location in MBGMCR03 idoc (segment E1BP2017_GM_ITEM_CREATE).

Document number: provide all the document number (same type)

Segment name: E1BP2017_GM_ITEM_CREATE

Position of field STGE_LOC: 22 (Calculate all the previous fields in the segment 18+4)

Length field STGE_LOC: 4

Field value: name of storage location

wawrzyk3.blogspot.com