The journey of a process automation Business Value Add tool

Hi there,
Recently I read a book (forgot the name though) on ruby on rails. And that inspired me writing my next development in ABAP as a story. (ALL comments are welcome and if anyone wants my help just write his/her email id I will surely contact, I update the blog weekly).
Hmmm.. then
I have been shifted to control-M from SAP-ABAP for my project. While working for the team basically I have to schedule (To make it rosier let's say "automate") the jobs/programs that are being developed my fellow ABAPers. To be true the job kinda suck for a guy like me. The job description..
1) Take the requirement of how the jobs have to be setup from a central team that manages the process flow.
2) Check if all the abap program names and variants are valid or not (belive me this is the worst part).
3) Put some logic (uff finally) while creating some relevant jobname. (it involves basically concatenating two fields with some standard "confidential" suffix)
4) Update the massive excel sheet with that job name.
5) If there are some glitches say abap name is not there then mark my comment and send it back to the central team for clarification.

I always use to dream of some software that would do these tasks for me. Then came a hyped kind of thing- "Business Value Add on" . Hmm finally, my chance to fulfil my dream of creating a standalone utility as well as making my work eaiser.
Ok.. So finally the action began from this point.
well to automate the process the first task was jotting downt the requirement of our tool.
1. Create a dialogue to input file.
I named this function module as FILE_BROWSER.
for browsing files in SAP you can use a standard function module called 'F4_FILENAME'
It has the following parameters.
Exporting
program_name = syst-cprog
dynpro_number = syst-dynnr
field_name = ' '
importing
file_name = fname.
fname is my variable that will store the name of file in it.

2. To read Excel file.
To read file you have two choices
a). GUI_UPDLOAD.
b) ALSM_EXCEL_TO_INTERNAL_TABLE
I used b) because that's exclusively for excel file and is pretty easy to use as compared to a).

The pattern of ALSM_EXCEL_TO_INTERNAL_TABLE is as followes. (I named this form as GET_EXCEL.
form GET_EXCEL.
data : l_file type rlgrap-filename,
l_firstrow type i value 1,
l_firstcol type i value 1,
l_rowsize type i value 4000,
l_columnnumber type i value 40,
l_file = p_ufile.
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
Exporting
filename = l_file
i_begin_col = l_firstcol
i_begin_row = l_firstrow
i_end_col = l_columnnumber
i_end_row = l_rowsize
Tables
intern = t_tab
Exceptions
inconsistent_parameters = 1
upload_ole = 2
others = 3.

if sy-subrc <> 0.
write: 'load not sucessful'.

ENDFORM.
**Good programming practice say you pass all the parameters via variables. Hence you will see some data declaration in the begining. l_ Stands for local data declaration. Well here it takes
a) The first row from which you want to start (1 in my case)
b) The first column (again 1)
c) Number of rows (I took 4000 to be safer side)
d) Number of columns (My excel sheet has 40)
e) An internal table t_tab whose type is like alsmex_tabline (you can check it in attributes column in function module)
That's pretty much it.
Wohla.... there you go all the data has been successfully transfered. (Phew.. finally).

But hey wait... The data that we have got is not like what we expected!!!!!

This graphical image will give you the better idea.
So next task.
3) Convert the data back to the form that we want.
First we created one internal table called t_tab_excel and one work are with name w_tab_excel
the data fields of this internal table is same as that of our excel sheet.
I named this function module as EXCEL_REINCARNATION

Form EXCEL_REINCARNATION
loop at t_tab into w_tab.
condense w_tab-value.
case w_tab-col.
when 1.
w_tab_excel-name = w_tab-value.
when 2.
w_tab_excel-class = w_tab-value.
when 3.
w_tab_excel-age = w_tab-value.
when 4.
w_tab_excel-marks = w_tab-value.
At end of row.
append w_tab_excel to t_tab_excel.
clear w_tab_excel.
endat.
endloop.
Endform.
Here we used an AT END OF ROW event to tell the SAP system when to update one row. The t_tab table is sorted with row column.
4) Concatenate the two fields and tell the program to do the "logic" for you .

Well I assume this was the easiest part. Although it uses one concept of "field symbols".

To put it in easier words. We use when we have to alter one field of our internal table.

So here we make a field symbol to just like our work are

FIELD-SYMBOLS: TYPE table structure name.

now loop at (our internal table that we got) and use ASSIGNING keyword for field symbol

something like

LOOP AT t_tab ASSIGNING .

and then perform the concatenation thing.

CONCATENATE -fieldname ',' INTO g_finalfield.

MOVE g_finalfield TO -final.

** Note g prefix. That is the standard we follow when defining some global field.

Edit: In the testing phase I have got one errror. The variant name has spaces in between. Say

'test for country' for making a job in control-M the job should not have spaces in between. For that I have used condense statement in combination with NO-GAP for all the fields on the first loop where I am populating the values.

CONDENSE string NO-GAPS.

(I can share the whole code but I am using this a lot of time hence I have defined this field in global data)

So using this we need not worry about appending and that other tedious stuff.

And we get a ful fledged table with everything we wanted.

5) To check for the abapname and variants given in the sheet to be existing.

Well it was also pretty easy if you keep in mind some points

SAP basically store all (more or less all) the information in tables. So (right !!!) we have a table that contains the information about all the abap name and variants associtated with them. The table name is VARI AND TRDIR.

So bascially we have to compare our table entries for ABAP name and variant with the entries in VARI table and TRDIR table. Step are as follows:

1) Create a table t_vari similar to vari table.

2) Select all entries from our table containing entries as well as in standard table

SELECT * INTO TABLE t_vari

FROM vari

FOR ALL ENTRIES IN t_tab_excel

WHERE report = t_tab_excel-abapname.

SELECT * FROM trdir INTO TABLE t_trdir

FOR ALL ENTRIES IN t_tab_excel

WHERE name = t_tab_excel-abapname.

ELSE.

some custom message

*** Good coding practice.. WE used for all entries and not the whole table so that we only get relevant information hence making process less perfomance exhaustive.

**Also we have checked for our table to be initial or not so that we should not pass empty table.

Always make a point to check this when firing some query on standard tables.