SAP BODS – Multiple Files Generation with STATIC pre-defined Format in Text File.

Business Requirement:

Generate Multiple Output Text files separately for Each Country from a single Input file.

Constraints:

  1. Before Generating Multiple Country wise files, BODS has to create a FOLDER with file-name format on run-time.
  2. Output Text file has a specified format, into which data has to be generated on the basis of country.
  3. BODS Job has to be running every 12 Min & search for the File in Input Folder, if not found wait spooning for few minutes & abort the job.
  4. There might be multiple Input files, So, need to create a BATCH wise load mechanism.
  5. Already processed files should be ignored & job has to be terminated.

Example: Each Output file should have below HEADER Starting Lines under which data has be loaded with proper line-spacing.

Requirement File Format:- 1

Table:          MAEX

Displayed Fields:   8 of   8 Fixed Columns: 4  List Width 0250

——————————————————————————————

| |MANDT|MATNR               |ALAND|GEGRU|ALNUM           |EMBGR           |PMAST|SECGK  |

—————————————————————————————–————————————

Requirement File Format:- 2

Table:          MARC                                            

Displayed Fields:   4 of   4 Fixed Columns:3  List Width 0500  

———————————————————       

| |MANDT|MATNR |WERKS |STAWN              |       

  ————————————————————————-       



Maintained a READ-ME Document for USERS to follow the INPUT files placing:

  1. Place file using GTS_HTS_ECCN_LOAD_TEMPLATE.xlsx format with a filename that BEGINS with “HTSECCN_RTP_”.  Sheet name should be “Sheet1”.

   For example:

                                HTSECCN_RTP_2013_03_27.xlsx

                                HTSECCN_RTP_a_few_new_IP_materials.xlsx

Note: Don’t use SPACES or PERIODS in the input filename.

  1. A Data Services job will be scheduled to scan for these files.  Upon finding one, it will process the file and create an output directory with the following format:

                OUT_<Timestamp>_<Filename>

                For Example:

                                OUT_20130327071422_HTSECCN_RTP_2013_03_27

                                OUT_20130327071422_HTSECCN_RTP_a_few_new_IP_materials

Note: The job checks every 5 minutes for new data.

In the directory, the job will place all non-blank HTS and ECCN files that can be generated from the input file.  It will also move the input file to this directory for confirmation that it was processed.

for %a in (*) do find /v /c “~@!@#(*$Q” %a

Output:-


Before generating Files, separate folder has to be created via BODS & generate files inside the folder as below.

/wp-content/uploads/2015/04/1_681448.gif

Output File should look like –

Requirement File Format:- 1

Table:          MAEX

Displayed Fields:   8 of 8  Fixed Columns:                 4  List Width 0250

——————————————————————————————

| |MANDT|MATNR               |ALAND|GEGRU|ALNUM           |EMBGR           |PMAST|SECGK  |

—————————————————————————————————————————–

| |110  |8001316.000000      |US |EA   |EAR99           |                |     | |

| |110  |8004904.000000      |US |EA   |EAR99           |                |     | |

| |110  |9000020.000000      |US |EA   |EAR99           |                |     | |

Requirement File Format:- 2

Table:          MARC                                            

Displayed Fields:   4 of 4  Fixed Columns:3  List Width 0500  

———————————————————       

| |MANDT|MATNR               |WERKS |STAWN              |       

————————————————————————-       

| |200  |8001316.000000      |0102 |90318000           |

| |200  |8004904.000000      |0102 |90318000           |

Solution Implemented:-


Maintained Header Format Folder, where STATIC HEADER format files are stored in TEXT file format. Which is used by BODS to read the STATIC HEADER into a Input File with DELETE before Load & then, this Input File is again used a TARGET file in next flow to load the actual source input excel file data as an append mechanism without Enabling DELETE Option.

/wp-content/uploads/2015/04/2_681479.gif

###########################  Variables Declaration Required inside the Job as per requirement to satisfy the Logic.###########################

$G_Error= ‘N’;

$G_Date = to_char(sysdate(),’yyyymmdd’);

Print($G_Date);

$G_Email_Message = ”; # will be assigned in next flows.

$G_Email_Header = ”;  # will be assigned in next flows.

$G_Email_Recepients = ‘DL-IT-BODS-Developers@xyz.com‘;

$HTSMANDT = ‘200  ‘; # Always keep 2 blank spaces after the 3 digit number.

$HTSWERKS = ‘0102  ‘; # Always keep 2 blank spaces after the 4 digit number.

$ECCNMANDT = ‘110  ‘; # Always keep 2 blank spaces after the 3 digit number.

$SRC_HeaderFilePath = ‘\\\ xyz.com\shared\CA002\IT\GTS\HTS-ECCN_Watch_Folder\HTS-ECCN_Header_Formats’;

$SourceFilePath = ‘\\\ xyz.com\shared\CA002\IT\GTS\HTS-ECCN_Watch_Folder’;

$SourceFileName = ”;  # will be assigned in next flows.

$TargetFilePath = ”;  # will be assigned in next flows.

$TargetFileName = ”;  # will be assigned in next flows.

$GV_FILENAME = ”;     # will be assigned in next flows.

$GV_FILENAME_KEY = 0;  # will be assigned in next flows.

$GV_FolderName = ”;   # will be assigned in next flows.

$KEY = 0;              # will be assigned in next flows

$FLAG = 0;             # will be assigned in next flows

$GV_STATUSFLAG = ”;   # will be assigned in next flows

$GV_Query = null;      # will be assigned in next flows

$GV_UpdateQuery = ”;  # will be assigned in next flows

$GV_Curr_Batch_No = 0; # will be assigned in next flows

$GV_Batch_No = 0;      # will be assigned in next flows

$GV_FN = null;         # will be assigned in next flows

Spooling Mechanism:


$Flag = wait_for_file( ‘\\\ xyz.com\shared\CA002\IT\GTS\HTS-ECCN_Watch_Folder\HTSECCN_RTP_*.xlsx’,600000,300000,5);

Once the BODS Job receives input source file in required input format, before processing file data, i have used Command functionality calling from BODS Script  to capture all filenames to a Temporary File for BATCH processing to run sequentially.

Print(‘File Exists’);

EXEC (‘cmd.exe’,’DIR /B “\\\xyz.com\shared\CA002\IT\GTS\HTS-ECCN_Watch_Folder\HTSECCN_RTP_*.xlsx” > \\\xyz.com\shared\CA002\IT\GTS\HTS-ECCN_Watch_Folder\HTS-ECCN_Header_Formats\HTS_ECCN_FileList.txt’,8);

Print(‘File Names copied to – HTS_ECCN_FileList.txt’);

/wp-content/uploads/2015/04/3_681480.gif

Load the FileNames into a Staging Landing Table for iteration process.

/wp-content/uploads/2015/04/4_681481.gif

$GV_FILENAME_KEY = sql(‘BODS_APPLICATIONS’,’select max(“ID”) from DBO.GTS_FILELIST_LNDG’);

Print(‘File Number Processing – ‘||$GV_FILENAME_KEY);

$GV_FILENAME = sql(‘BODS_APPLICATIONS’,’select distinct FILENAME from DBO.GTS_FILELIST_LNDG where “ID”=[$GV_FILENAME_KEY]’);

Print(‘Processing File – ‘||$GV_FILENAME);

Select 1 file at any point of time & before processing data compare with BATCH table to check if that file is New or Already processed. If exists – Terminate the Job.

$GV_Query = ‘select distinct FILENAME from dbo.GTS_FILELIST_LNDG where FILENAME in (select distinct FILENAME from dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment where status in(\’COMP\’,\’AVAL\’)) and “ID”=[$GV_FILENAME_KEY]’;

$GV_FN = sql(‘BODS_APPLICATIONS’,$GV_Query);

print(‘If any existing files – ‘||$GV_FN);

$GV_Query = ‘select distinct STATUS from dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment where FILENAME in (\'[$GV_FN]\’)’;

$GV_STATUSFLAG = sql(‘BODS_APPLICATIONS’,$GV_Query);

print(‘$GV_STATUSFLAG : {$GV_STATUSFLAG}’);

if ($GV_FN is null)

begin

$G_Error= ‘N’;

$GV_Query = ‘Select Max(batch_no) from dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment’;

$GV_Curr_Batch_No = sql(‘BODS_APPLICATIONS’,$GV_Query);

   if($GV_Curr_Batch_No = 0 or $GV_Curr_Batch_No is null)

   begin

                $GV_Query = ‘Insert into dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment values (1,\”||$GV_FILENAME||’\’,\’AVAL\’,\’\’)’;

                print(‘$GV_Query —>’||$GV_Query);

                sql(‘BODS_APPLICATIONS’,$GV_Query);

                $GV_Batch_No = 1;

                print(‘$GV_Batch_No —>’ ||$GV_Batch_No);

   end

   else

   begin

                $GV_Batch_No = $GV_Curr_Batch_No + 1;

                $GV_Query = ‘insert into dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment values (‘||$GV_Batch_No||’,\”||$GV_FILENAME||’\’,\’AVAL\’,\’\’)’;

                print(‘$GV_Query —>’||$GV_Query);

                sql(‘BODS_APPLICATIONS’,$GV_Query);           

                print(‘$GV_Batch_No —>’ ||$GV_Batch_No);

   end

end

else

begin

$G_Error= ‘Y’;

$GV_Query = ‘select distinct Batch_No from dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment where FILENAME in (\'[$GV_FN]\’)’;

$GV_Batch_No = sql(‘BODS_APPLICATIONS’,$GV_Query);

print(‘$GV_Batch_No : {$GV_Batch_No}’);

end

/wp-content/uploads/2015/04/5_681483.gif

$G_Error= ‘N’;

$GV_FILENAME = sql(‘BODS_APPLICATIONS’,’select distinct FILENAME from dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment where “BATCH_NO”=[$GV_Batch_No]’);

Print(‘Extraction started for File  – ‘||$GV_FILENAME);

Print(‘Creating todays Folder …’);

#$GV_FolderName = ‘OUT’||’_’||to_char(sysdate(),’YYYYMMDD’)||”||replace_substr( to_char(sysdate(),’hh24:mi:ss’),’:’,”)||’_’||word_ext($GV_FILENAME,1,’.xlsx’);

$GV_FolderName = ‘OUT’||’_’||to_char(sysdate(),’YYYYMMDD’)||”||replace_substr(to_char(sysdate(),’hh24:mi:ss’),’:’,”)||’_’||$GV_FILENAME;

$GV_FolderName = replace_substr( $GV_FolderName,’.xlsx’,”);

Print(‘Extraction started for folder name  – ‘||$GV_FolderName);

EXEC (‘cmd.exe’,’MD [$SourceFilePath]\[$GV_FolderName]’,8);

Print(‘Folder created –  ‘||$GV_FolderName);

$TargetFilePath = ‘[$SourceFilePath]\[$GV_FolderName]’;

$SourceFileName = $GV_FILENAME;

/wp-content/uploads/2015/04/6_681482.gif

/wp-content/uploads/2015/04/7_681484.gif

$KEY = sql(‘BODS_APPLICATIONS’,’select max(“KEY”) from DBO.GTS_HTS_ECCN_CTRY_CODES’);

Print($KEY);

$G_Count = sql(‘BODS_APPLICATIONS’,’select Count(*) from DBO.GTS_HTS_ECCN_CTRY_CODES’);

$GTS_TYPE = sql(‘BODS_APPLICATIONS’,’select distinct GTS_TYPE from DBO.GTS_HTS_ECCN_CTRY_CODES where “KEY”=[$KEY]’);

Print(‘Extraction started for GTS Type – ‘||$GTS_TYPE);

$CTRY_CODE = sql(‘BODS_APPLICATIONS’,’select CTRY_CODE from DBO.GTS_HTS_ECCN_CTRY_CODES where “KEY”=[$KEY]’);

Print(‘Extraction started for Country – ‘||$CTRY_CODE);

/wp-content/uploads/2015/04/8_681488.gif

$TargetFileName = (‘Product’||’_’||$GTS_TYPE||’_’||’Input’||’_’||$CTRY_CODE||’_’||$G_Date||’.txt’);

Print($TargetFileName);

/wp-content/uploads/2015/04/9_681489.gif

/wp-content/uploads/2015/04/10_681490.gif

Print(‘File Generated – ‘||$TargetFileName);

$KEY = $KEY – 1;

Once the File is completely Processed, Input File is ARCHIVED with below process & update the BATCH Assignment Table with status of file processed.

If($G_Error = ‘N’)

begin

Print(‘GTS_GlobalTrade Conversion Job has Completed for File — ‘||$GV_FILENAME);

Print(‘Moving todays Input File to Folder … – ‘||$GV_FolderName);

EXEC (‘cmd.exe’,’Move [$SourceFilePath]\[$GV_FILENAME] “[$SourceFilePath]\[$GV_FolderName]\”‘,8);

Print(‘Input File Moved Successfully — ‘||$GV_FILENAME);

$GV_UpdateQuery = ‘update dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment set STATUS =  \’COMP\’ where BATCH_NO = [$GV_Batch_No]’;

sql(‘BODS_APPLICATIONS’,$GV_UpdateQuery);

$GV_UpdateQuery = ‘update dbo.GTS_TRADEDATA_HTS_ECCN_Batch_Assignment set LoadDate =  getdate() where BATCH_NO = [$GV_Batch_No]’;

sql(‘BODS_APPLICATIONS’,$GV_UpdateQuery);

end

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Kolli Srinivas

    Hi Naveen,

    Could you please elaborate how to generate the flat file with HEADER as given below?

    Table:          MAEX

    Displayed Fields:   8 of   8 Fixed Columns: 4  List Width 0250

    ——————————————————————————————

    | |MANDT|MATNR               |ALAND|GEGRU|ALNUM           |EMBGR           |PMAST|SECGK  |

    —————————————————————————————–————————————

    Also it would be appreciated if you can share the specific atl file for this job privately to me to understand in more detailed.

    (0) 
    1. Naveen Kumar Post author

      Hi Srinivas,

      This is static file as i said, I took the header information from the user, how they require in the output file considering the length of each character. They i copied in a Static file using this as a Master file for my data population for each column here.

      (0) 

Leave a Reply