Automation Process for Data Uploading Through Flat Files

Automation process for data uploading through Flat files

Automation Process for Data uploading through Flat files BUSINESS INFORMATION WAREHOUSE

Version 1.1

5th February 2005

CONFIDENTIALITY © Wip Wipro ro Lim Limite ited, d, 200 2005, 5, All Rig Rights hts Res Reserv erved. ed. All the inf inform ormati ation on in th this is sit site e is pri privat vate, e, privileged and confidential. Users shall refrain from copying, distributing, misusing and / or disclosing the information to any third parties under any circumstances whatsoever.

Wipro Confidential

Page 1 of 36


Version Management: Version

Date

1.0 1.1

31/01/2005 5/02/2005

Wipro Confidential

Author(s)

J Praveen Kumar J Praveen Kumar

Summary of changes and/or approver (where necessary) Initial Document Changes after Review

Page 2 of 36


Version Management: Version

Date

1.0 1.1

31/01/2005 5/02/2005

Wipro Confidential

Author(s)

J Praveen Kumar J Praveen Kumar

Summary of changes and/or approver (where necessary) Initial Document Changes after Review

Page 2 of 36

Automation process for data uploading through Flat files Table Of Contents

1 2

Introduction...................................................................................................................4 Basic steps required to be carried out to upload data from flat file.............................. file........................ ......4 4 A. SPRO Settings.......................................................... .......................................... ...............................................4 .....4 B. Source System Creation................................................................ ..................... ............. ............. .....7 7 C. InfoSource Creation............................................................... ...................... .............. ................ .........11 .11 D. InfoPackage options................................................................... ...........................15 ...................... .....15 3 Business Requirement........................................................... ....................... ............... ................ .............. ......17 17 4 Data Flow Diagram............................................................... ...................................... ......................................18 18 5 BW Interface mechanism............................................................. ................. ......... ................ ............. .....19 19 6 File Transfer Mechanism......................................................... ................................... ........................... ........20 20 7 File Transfer Flow diagram......................................................... ........................ ............... ................ ....... 21 9 Settings in InfoPackage.............................................. .................................... ........................... ................. ............ ....25 25 10 Appendix...................................................................................................................26 A. Shell Script.............................................................. ....................................... .............................................. .......26 26 B. ABAP Program to trigger the Meta Chain............................................ Chain...................................................... ................ ......29 29 C. Routine in InfoPackage to generate File name dynamically........................... dynamically.................................29 ......29 D. ABAP program to upload Shell script on the Application server.........................30 server........................ .30

Wipro Confidential

Page 3 of 36


1

Introduction

Uploading data into the Business Warehouse system from flat files is one of the common requirements in many projects. If the number of such files is less and the frequency of data arrival is manageable (for example master data), we generally go for the manual uploading of the data. But if in a project the requirement is such that the data is coming in a large number of files and also very frequently (for example transaction data), then we must have some automation process in place to take data from these files and load into the BW system. This document discusses such a requirement we came across in our project and how we have implemented the automation process. 2

Basic steps required to be carried out to upload data from flat file

Before moving to the details of automation process, this section describes in brief the various steps involved in manual uploading of the data from flat file into Business Warehouse system. A. SPRO Settings Run the transaction SPRO and in the initial screen click on “SAP Reference IMG”.

Wipro Confidential

Page 4 of 36


The following screen will appear. Click on “Set options for uploading flat files”.

Wipro Confidential

Page 5 of 36


The following screen will appear where you can set various options for flat file uploading.

Thousand Separator

Thousand Separators can be used, for example, to make the number 345566345 clearer. The standard character is a comma (Example: 345,566,345). Select any separator so that a conversion can take place into the Business Information Warehouse format. Character for the decimal point

The character for displaying the decimal point with numbers that have places after the decimal point is set at period as standard (Example: 45.36). Should, for example, a comma be used as a decimal point character in the display of comma numbers (Example: 23,44),you must enter it here in order to make a conversion into the Business Information Warehouse format possible. Field Separator

The field separator in CSV files serves to assign the field entries to a table of the respective columns.

Wipro Confidential

Page 6 of 36


Field delimiter

The field delimiter serves to supply field entries in CSV files with special characters.The default value used for this is ". Example: "Smith;John" ,If the ; character were to function as a field separator then Smith;John would be spread across two fields. Using the field delimiter this field entry remains in one field. B. Source System Creation Run the transaction RSA1 and go to “Source System”.

Wipro Confidential

Page 7 of 36


Right click on the Source system and in the context menu choose “Create”.

Wipro Confidential

Page 8 of 36


Choose “File system (Manual Metadata, Data Using File Interface).

Wipro Confidential

Page 9 of 36


The following screen will appear. Here you need to give the source system name and corresponding text.

Wipro Confidential

Page 10 of 36


C. InfoSource Creation

Go to your application component and in the context menu choose “Create Infosource”.

In the popup screen, give the technical name and description of the InfiSource.

Wipro Confidential

Page 11 of 36


In the next screen, you create the communication structure by giving the InfoObjects which will be used to store the data coming through the flat file.

Wipro Confidential

Page 12 of 36


Expand the Transfer_structure/Transfer_Rules tab. Choose the source system for the flat file (created in the previous steps) and save. The system will automatically suggest Transfer Structure and Transfer Rules, save it.

Wipro Confidential

Page 13 of 36


In the DataSource/Trans. Structure tab, make sure that the sequence of InfoObjects is exactly same as the sequence of corresponding fields in the flat file. This sequence need not match in the communication structure, but it should be exactly same in Transfer Structure.

Wipro Confidential

Page 14 of 36


D. InfoPackage options In the “External Data” tab of InfoPackage, some settings are required to be done in order to upload data from a flat file.

Location of flat file - Depending upon whether the flat file is located at local machine or Application server, you need to select the appropriate radio button. While mentioning the name of the file, you need to give the entire path of the file location. The option of writing a routine is also provided to generate the file path dynamically. File type – Here you need to tell from what kind of file you are going to upload the data. By the use of an ASCII file, data can be uploaded at higher speed, but the limitation is that no data separator can be used and the data has to be available as a string in the format of the transfer structure. If the data is in the CSV format, then data separators are in place between the individual data fields, which are interpreted when loading. For CSV file, you get the option of giving “Data separator” and “Escape sign”. Other options - In order to make the numbers easier to comprehend, thousand separators can be used. The default value is a comma. You can select any separator you like so that a translation into the format of the Business Information Warehouse can take place. Wipro Confidential

Page 15 of 36


The character for a decimal display for numbers with places after the decimal point is set as a period by default. If, for example, a comma is used as the decimal character in the display of decimals (e.g.: 23,44), then this must be entered here in order to enable a calculation in the Business Information Warehouse format. If “Currency Conversion for External systems” option is selected, when data is loaded into a SAP BW, currency amounts are converted from an external format into the SAP BW format. If you do not set this selection, the currency amounts are displayed in the internal SAP BW format. You can mention the number of headers rows to be ignored while loading the data from a flat file; you have the option of hiding a maximum of 99 headers/remarks row. A blank entry means that every row of the file is taken into account. This is the default setting.

Wipro Confidential

Page 16 of 36


3

Business Requirement

The client is an Oil and Gas major. This project requires extraction of Purchase Order and Accounting Details data and generating custom reports. The client is using a database system to store its Master and Transaction data generated from day-to-day transactions. This database system stores a lot of PO and Accounting details, which are not required for posting of the transactions into R/3, hence while posting into the SAP, only a small chunk of the required data is taken from this database system and sent to R/3 through an interface. The same database system is used for storing the data from various other parties. The scope of this project involves bringing the PO and Accounting details data from R/3 and extracting the related detailed data from the database system into the Business Warehouse and generating custom reports on that data. Extracting the data from R/3 into BW system is done in conventional way. But for taking the data from database system, we have explored the options and suggested the use of DBConnect. But since the use of DBConnect allows direct access of the database system where data of multiple parties is being stored, this option could not be implemented keeping security guidelines in mind.. Then the option we have suggested was to send the data in the form of flat files. The entire master and transaction data from the database system is coming through flat files. Hence the number of flat files is large and also the files are coming daily. It was not feasible to manually upload data from these files into the BW system. Then to manage data uploading we have came up with an automation process.

Wipro Confidential

Page 17 of 36


4

Data Flow Diagram

The following data flow diagram shows the flow of data between Database System, R/3 and Business Warehouse system.

Database System

CSV Files

BW System Invoice Posting

SAP-R/3

Data Modeling

Report Generation

Reports

Standard Data Source

Wipro Confidential

Page 18 of 36


5

BW Interface mechanism

It has been decided that the following steps in the data uploading automation process will be taken care by the client. 1. The file is first sent to a directory in the BW system which is a secure file location 2. The Files from database will have proper naming convention and a dedicated file system or a directory be created on an interface server or on BW servers for storing the files 3. A shell script will be written which would transfer the files. The file transfer from database should happen to a dedicated directory of BW server with a secured FTP/SSH. 4. When the file is sent to the specific directory, the ITM monitor will move the file to the SAP inbound processing directory, rename the file, and trigger file-ready. 5. An inbound job is then triggered to accept the file. 6. The above steps are based on the assumption that the SFTP servers are always ready to accept a data file especially during the agreed upon frequency The responsibility of placing the file at the pre-decided directory lies with the client in our project.

Wipro Confidential

Page 19 of 36


6

File Transfer Mechanism The process of transfer of CSV files to SAP BW can be summarized in the following steps 1. All the CSV files are placed at the pre-decided directory on the BW server. 2. A shell script has been written to check the pre-decided directory for the presence of files of that particular day. Date of the file is included in the file name to distinguish between files of different days. By the means of a job (Job1), this shell script has been scheduled to run at every hour to check for the files. 3. If the script locates the files, then it moves those files to a second directory and triggers a custom defined event (Event1). Movement of files to a second directory is necessary because the files come daily and subsequent processes should take place only once a day for the set of files. If we let those files remain in the main directory itself, then the shell script, which runs at every hour will find those files again and again and the subsequent processes will be triggered every time. 4. A second job (Job2) has been defined to be executed after event Event1. Hence as soon as the shell script triggers the event, Job2 gets executed. 5. Job2 in turn runs an ABAP program. 6. The ABAP program calls a Function module RSPC_API_CHAIN_START and in the export parameter it passes the name of the Meta Chain which contains process chains for uploading the data from the incoming flat files. 7. The process chain then begins to upload master and transaction data into the BW system. 8. Routines have been written for the file name in “external data” tab of InfoPackages.

Wipro Confidential

Page 20 of 36


7

File Transfer Flow diagram

Wipro Confidential

Page 21 of 36


Job1 Triggers the Shell script at every Hour

Shell script Searches for the files, if found, moves them to other directory and triggers event Event1

Event1 Triggers Job2

Job2 Runs an ABAP Program

ABAP Program Executes Function Module RSPC_API_CHAIN_START, which in turn starts the Meta Chain

Meta Chain Starts process chains to upload data from various CSV files

8

Technical Details

This section discusses the technical aspects involved in the design and how to create various components in the system. Wipro Confidential

Page 22 of 36


Job1 is used for triggering the shell script every hour. The Step list of this is shown below.

ZSCR is a custom OS command. Below shown is screen shot from transaction SM49 which shows the details of this OS command. The complete path of the shell script (given in the Appendix) is passed as the parameter to the OS command. Hence Job1 will execute the OS command ZSCR every hour, which in turn will run the shell script.

Wipro Confidential

Page 23 of 36


The shell script searches for the files and if found it unzips them with the command “gunzip”, untars them with the command “tar –xvf” and then moves them to another directory. ( It has been pre decided that the files will be sent in “.tar.gz” format. Hence these files needs to be converted into CSV format before BW can use them.) After moving the files, the shell script triggers a custom event (Event1 in our discussion above). In the shell script command “sapevt” is used to trigger the event. The parameters to be passes are the system name and system number. Below shown is the way this command has been used in the shell script. Here Z_TRIGGER_CUSTOMER_PROCESS_CHAIN is Event1, WR1 is the system name and system number is 20.

Job2 has been defined with the start condition as after event Event1. Hence as soon as the shell script triggers the event Event1, Job2 starts running. The step list of Job2 is shown below.

This job executes the ABAP program Z_TRIGGER_RSPC_BACKGROUND. The ABAP program in turn calls a Function Module RSPC_API_CHAIN_START and in the export parameter passes the name of the meta chain containing the process chains to upload data from different files.

Wipro Confidential

Page 24 of 36


9

Settings in InfoPackage

Since the file name contains date to distinguish between files from different days, we can not hard code the file name. Instead we need to write the routine to generate the file name dynamically on the basis of date. Below shown is the screen shot of “External Data” tab of InfoPackage.

Wipro Confidential

Page 25 of 36


The routine is included in the Appendix.

10

Appendix A.

Shell Script

#!/bin/sh year=`date +%Y` month=`date +%m` Wipro Confidential

Page 26 of 36


day=`date +%d` exten=".tar.gz" exten1=".tar" exten2=".csv" exten3=".CSV" dir="$year$month$day" dat="$year$month$day$exten" dat1="$year$month$day$exten1" dat2="$year$month$day$exten2" dat3="$year$month$day$exten3" echo "$dat" filename=`ls /bwglobal/WR1/in/Raw grep $dat` echo "$filename" if [ $filename ] then echo "File found : $filename" gunzip /bwglobal/WR1/in/Raw/ERS_ELANCE_$dat tar -xvf /bwglobal/WR1/in/Raw/ERS_ELANCE_$dat1 mv /usr/sap/WR1/DVEBMGS20/work/ERS_ADDRESS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ADDRESS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_CATEGORY_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CATEGORY_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_CODES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CODES_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_COMMODITY_$dat2 /bwglobal/WS1/Archive/Processed/ERS_COMMODITY_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_CONTINGENT_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CONTINGENT_DETAILS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_CONTINGENT_STAFF_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CONTINGENT_STAFF_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_CONTRACT_PCR_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CONTRACT_PCR_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_COSTCENTER_$dat2 /bwglobal/WS1/Archive/Processed/ERS_COSTCENTER_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_EXPENSES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_EXPENSES_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_EXPENSE_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_EXPENSE_DETAILS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_INVOICES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_INVOICES_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_INVOICE_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_INVOICE_DETAILS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_LOCATIONS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_LOCATIONS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_MA_$dat2 /bwglobal/WS1/Archive/Processed/ERS_MA_$dat3 Wipro Confidential

Page 27 of 36


mv /usr/sap/WR1/DVEBMGS20/work/ERS_MA_BUDGET_$dat2 /bwglobal/WS1/Archive/Processed/ERS_MA_BUDGET_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_MA_CATEGORIES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_MA_CATEGORIES_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_MA_ORGS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_MA_ORGS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_ORDERS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ORDERS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_ORDERS_ALL_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ORDERS_ALL_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_ORDER_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ORDER_DETAILS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_ORGANIZATION_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ORGANIZATION_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_POSITION_$dat2 /bwglobal/WS1/Archive/Processed/ERS_POSITION_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_POSITION_AVG_$dat2 /bwglobal/WS1/Archive/Processed/ERS_POSITION_AVG_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_POSITION_RATECARD_$dat2 /bwglobal/WS1/Archive/Processed/ERS_POSITION_RATECARD_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_PO_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_PO_DETAILS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_PROPOSAL_$dat2 /bwglobal/WS1/Archive/Processed/ERS_PROPOSAL_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_PROPOSAL_LINE_ITEM_$dat2 /bwglobal/WS1/Archive/Processed/ERS_PROPOSAL_LINE_ITEM_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_REQUEST_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_REQUEST_DETAILS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_ROLES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ROLES_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_ROLE_CATEGORIES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ROLE_CATEGORIES_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_ROLE_USERS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ROLE_USERS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_CATEGORY_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_CATEGORY_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_LOCATION_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_LOCATION_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_POSITION_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_POSITION_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_SCORECARD_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_SCORECARD_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_SYS_CODES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SYS_CODES_$dat3 Wipro Confidential

Page 28 of 36


mv /usr/sap/WR1/DVEBMGS20/work/ERS_TIMESHEETS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_TIMESHEETS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_TIMESHEET_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_TIMESHEET_DETAILS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_UDF_DEFINITIONS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_UDF_DEFINITIONS_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_UDF_VALUES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_UDF_VALUES_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_USER_$dat2 /bwglobal/WS1/Archive/Processed/ERS_USER_$dat3 mv /usr/sap/WR1/DVEBMGS20/work/ERS_WORKFLOW_ITEMS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_WORKFLOW_ITEMS_$dat3 sapevt Z_TRIGGER_CUSTOMER_PROCESS_CHAIN name=WR1 nr=20 fi B.

ABAP Program to trigger the Meta Chain *&---------------------------------------------------------------------* *& Report Z_TRIGGER_RSPC_BACKGROUND *& * *&---------------------------------------------------------------------* *& * *& * *&---------------------------------------------------------------------* REPORT Z_TRIGGER_RSPC_BACKGROUND

*

.

data: ws_RSPC_LOGID type RSPC_LOGID. CALL FUNCTION 'RSPC_API_CHAIN_START' EXPORTING i_chain = 'ZP_MAIN1' * I_T_VARIABLES = * I_SYNCHRONOUS = * I_SIMULATE = IMPORTING E_LOGID = ws_RSPC_LOGID EXCEPTIONS FAILED =1 OTHERS =2 . IF sy-subrc <> 0. * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4. ENDIF.

C.

Routine in InfoPackage to generate File name dynamically program conversion_routine. * Type pools used by conversion program type-pools: rsarc, rsarr, rssm. tables: rsldpsel. * Global code used by conversion rules *$*$ begin of global - insert your declaration only below this line *-* * TABLES: ... * DATA: ...

Wipro Confidential

Page 29 of 36

Automation process for data uploading through Flat files *$*$ end of global - insert your declaration only before this line *-* * ------------------------------------------------------------------form compute_flat_file_filename changing p_filename like rsldpsel-filename p_subrc like sy-subrc. * Insert source code to current selection field *$*$ begin of routine - insert your code only below this line *-* concatenate '/bwglobal/WS1/Archive/Processed/ERS_PO_DETAILS_' sy-datum '.CSV' into p_filename. * p_filename = *.... p_subrc = 0. *$*$ end of routine - insert your code only before this line endform.

D.

*-*

ABAP program to upload Shell script on the Application server *&---------------------------------------------------------------------* *& Report YSERVER_PROD *& * *&---------------------------------------------------------------------* *& * *& * *&---------------------------------------------------------------------*

*

REPORT YSERVER_PROD . data: fname_raw(60) value '/bwglobal/WR1/in/Raw/elance_script.sh'. types: begin of ty_tbl, line(1024) type c, end of ty_tbl. data: i_data type standard table of ty_tbl with header line. data: itab type standard table of ty_tbl with header line. data: s_data type ty_tbl. open dataset fname_raw for output in text mode encoding default. clear i_data. refresh i_data. clear s_data. s_data = '#!/bin/sh'. transfer s_data to fname_raw. clear s_data. s_data = 'year=`date +%Y`'. transfer s_data to fname_raw. clear s_data. s_data = 'month=`date +%m`'. transfer s_data to fname_raw. clear s_data. s_data = 'day=`date +%d`'. transfer s_data to fname_raw. clear s_data. s_data = 'exten=".tar.gz"'.

Wipro Confidential

Page 30 of 36

Automation process for data uploading through Flat files transfer s_data to fname_raw. clear s_data. s_data = 'exten1=".tar"'. transfer s_data to fname_raw. clear s_data. s_data = 'exten2=".csv"'. transfer s_data to fname_raw. clear s_data. s_data = 'exten3=".CSV"'. transfer s_data to fname_raw. clear s_data. s_data = 'dir="$year$month$day"'. transfer s_data to fname_raw. clear s_data. s_data = 'dat="$year$month$day$exten"'. transfer s_data to fname_raw. clear s_data. s_data = 'dat1="$year$month$day$exten1"'. transfer s_data to fname_raw. clear s_data. s_data = 'dat2="$year$month$day$exten2"'. transfer s_data to fname_raw. clear s_data. s_data = 'dat3="$year$month$day$exten3"'. transfer s_data to fname_raw. clear s_data. s_data = 'echo "$dat"'. transfer s_data to fname_raw. clear s_data. s_data = 'filename=`ls /bwglobal/WR1/in/Raw | grep $dat`'. transfer s_data to fname_raw. clear s_data. s_data = 'echo "$filename"'. transfer s_data to fname_raw. clear s_data. s_data = 'if [ $filename ]'. transfer s_data to fname_raw. clear s_data. s_data = 'then'. transfer s_data to fname_raw. clear s_data. s_data = 'echo "File found : $fil ename"'. transfer s_data to fname_raw. clear s_data. *Unzipping the file s_data = 'gunzip /bwglobal/WR1/in/Raw/ERS_ELANCE_$dat'.

Wipro Confidential

Page 31 of 36

Automation process for data uploading through Flat files transfer s_data to fname_raw. clear s_data. *Untaring the file s_data = 'tar -xvf /bwglobal/WR1/in/Raw/ERS_ELANCE_$dat1'. transfer s_data to fname_raw. clear s_data. *Moving the files to archive directory s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_ADDRESS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ADDRESS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_CATEGORY_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CATEGORY_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_CODES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CODES_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_COMMODITY_$dat2 /bwglobal/WS1/Archive/Processed/ERS_COMMODITY_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_CONTINGENT_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CONTINGENT_DETAILS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_CONTINGENT_STAFF_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CONTINGENT_STAFF_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_CONTRACT_PCR_$dat2 /bwglobal/WS1/Archive/Processed/ERS_CONTRACT_PCR_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_COSTCENTER_$dat2 /bwglobal/WS1/Archive/Processed/ERS_COSTCENTER_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_EXPENSES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_EXPENSES_$dat3'.

Wipro Confidential

Page 32 of 36

Automation process for data uploading through Flat files transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_EXPENSE_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_EXPENSE_DETAILS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_INVOICES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_INVOICES_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_INVOICE_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_INVOICE_DETAILS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_LOCATIONS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_LOCATIONS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_MA_$dat2 /bwglobal/WS1/Archive/Processed/ERS_MA_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_MA_BUDGET_$dat2 /bwglobal/WS1/Archive/Processed/ERS_MA_BUDGET_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_MA_CATEGORIES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_MA_CATEGORIES_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_MA_ORGS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_MA_ORGS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_ORDERS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ORDERS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_ORDERS_ALL_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ORDERS_ALL_$dat3'. transfer s_data to fname_raw.

Wipro Confidential

Page 33 of 36

Automation process for data uploading through Flat files clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_ORDER_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ORDER_DETAILS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_ORGANIZATION_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ORGANIZATION_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_POSITION_$dat2 /bwglobal/WS1/Archive/Processed/ERS_POSITION_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_POSITION_AVG_$dat2 /bwglobal/WS1/Archive/Processed/ERS_POSITION_AVG_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_POSITION_RATECARD_$dat2 /bwglobal/WS1/Archive/Processed/ERS_POSITION_RATECARD_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_PO_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_PO_DETAILS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_PROPOSAL_$dat2 /bwglobal/WS1/Archive/Processed/ERS_PROPOSAL_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_PROPOSAL_LINE_ITEM_$dat2 /bwglobal/WS1/Archive/Processed/ERS_PROPOSAL_LINE_ITEM_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_REQUEST_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_REQUEST_DETAILS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_ROLES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ROLES_$dat3'. transfer s_data to fname_raw. clear s_data.

Wipro Confidential

Page 34 of 36

Automation process for data uploading through Flat files s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_ROLE_CATEGORIES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ROLE_CATEGORIES_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_ROLE_USERS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_ROLE_USERS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_CATEGORY_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_CATEGORY_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_LOCATION_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_LOCATION_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_POSITION_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_POSITION_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_SUPPLIER_SCORECARD_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SUPPLIER_SCORECARD_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_SYS_CODES_$dat2 /bwglobal/WS1/Archive/Processed/ERS_SYS_CODES_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_TIMESHEETS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_TIMESHEETS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_TIMESHEET_DETAILS_$dat2 /bwglobal/WS1/Archive/Processed/ERS_TIMESHEET_DETAILS_$dat3'. transfer s_data to fname_raw. clear s_data. s_data = 'mv /usr/sap/WR1/DVEBMGS20/work/ERS_UDF_DEFINITIONS_$dat2

Wipro Confidential

Page 35 of 36

Automation Process for Data Uploading Through Flat Files

Recommend Documents