SAP BODS – Multiple Files Generation with STATIC predefined Format in Text File.

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.

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.

###########################  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’);

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

$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

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

$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);

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

Print($TargetFileName);

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