How to create variable record length target file in SAP BODS

[Former Member]

Hi All

I have a requirement to create target file which will have various record layout; meaning different record length (similar to cobol file format), but this is for the target. Please let me know what is the best practice and how to solution this requirment.

Thanks

Ash

[former_member106536 · Accepted solution]

Write each record out with 2 sequence numbers appended to the front.  Then create a new source file format which includes only the 2 sequence number fields and the remainder of the record as one long field (no trimming).  Read each file in and run it through a sort.  Strip the sequence number fields and write it back out as one field.

This will stack your records of different layouts in the correct sequence. 

If you need headers/footers, you can add a 3rd sequence number.  header = 1,detail = 2, footer = 3.

(probably need to lpad the sequences with zeros to get it to sort properly)

[Former Member · Accepted solution]

Hi Joshua

Thank you for the helpful feedback. I had question for you to clarify.

How should these 2 fields be populated? I mean for each record type, I will have "|" delimited file as below.

<seq_1>|<seq_2>|<all data fields concat'ed>

Since I will be stacking all the diff records for each customer and move to next customer records, how should I populate these seq number fields for each data file so that when I read and run them thru the sorter, they show in the correct order in the final output file as I showed in earlier messages above?

Much appreciated.

[former_member106536 · Accepted solution]

forget you're dealing with delimited files.  The first 3 fields will be fixed and the remainder will just ride along. (whatever those layouts happen to be)

so it should look like:

0001|0001|0001|header

0002|0001|0001|...

0002|0001|0002|...  detail

0002|0001|0003|...

0003|0001|0001|trailer

you can use the same thing for different sets of detail records with sub/grand totals, you just have to assign each section its own sequence to guarantee the correct output sequence.

usually we'll have a count in the trailer to validate the number of detail sets, and maybe a count indicating total number of records including headers.

[Former Member · Accepted solution]

Thanks Josh.

So if I were to have more detail records, it should look like below. Correct me if I am wrong please. And the sort should be on seq1, seq2, seq3?

0001|0001|0001|header

0002|0001|0001|...

0002|0001|0002|...  detail 1

0002|0001|0003|...

0003|0001|0001|...

0003|0001|0002|...  detail 2

0003|0001|0003|...

0004|0001|0001|...

0004|0001|0002|...  detail 3

0004|0001|0003|...

0005|0001|0001|trailer

[former_member106536 · Accepted solution]

Correct.  Its pretty easy once you get over the ugliness of these files. 

[Former Member · Accepted solution]

Hey Joshua,

Didnt get a chance to say Thank You for the help. Though, I haven't completed this job, but I did a quick POC and it seems to be a good solution.

Thanks again.

[former_member186897]

Hi Ashraf Shah,

Can you please elaborate your question with some example as what exactly you are looking for?

Regards,

MSA

[Former Member]

Hi Shahanshah

I have to create an output file with header, detail, and trailer records. The detail records themsevles are going to be different lengths. Sample file may look like below:

220700000000SA00    Wednesday     2014-12-12  ASA00034 334 000 (this is header)

220700000010SA10 AAb   00000+000000+ Akab xxxx bb   0000000000943 3433 (detail rec)

220700000010SA14 AAA  00034354 DDD 000000000+ (detail rec)

220700000010SA15 888e a88 00000000+            (detail rec)

220700000010SA10 AAb   00000+000000+ Akab xxxx bb   0000000000943 3433 (detail rec)

220700000010SA14 AAA  00034354 DDD 000000000+ (detail rec)

220700000010SA15 888e a88 00000000+            (detail rec)

220700000000SA99    Wednesday     2014-12-12  d334 000 (trailer is header)

If you notice, records are not the same length, but each record type is fixed length. Hope it is clear.

Ashraf

[Former Member]

Any thoughts?

[Former Member]

Hi Ashraf,

            Could you please show me the sample source data and your job flow

[former_member200473]

Hi Ashraf,

I have an alternative to this which may help you to accomplish your result.

Solution: just before the target flat file ,just concate all the fields with "tab" delimited ( for detail records) for example : field A+ "     "+field B + "     " + field C and so on and map it to one field and apply same for header fields .

Regards,

Shiva Sahu

[Former Member]

Hi Mike

Source is database tables. I have not started the job yet. Basically, I have a bunch of tables; each of these tables represents one of the detail record in the target file.These tables have a key column to match on to read data for each customer account.

My issue is that detail records are not the same length but each record type is fixed length.

Hope it is clear.

[Former Member]

Hi Shiva,

Thanks for your feedback. My issue is that I have 10 different detail records (each record type is fixed length).

For each customer account, I have to write to file the header record, the detail records in the exact order, then continue with next account and so on and then write the trailer record. I have given sample layout below. Highlighted text is the record identifier in this exmaple while the underlineds are account numbers. Fields are fixed length right padded with space or 0.

220700000000SA00    Wednesday     2014-12-12  ASA00034 334 000   ---> (this is header)

220700000010SA10 AAb   00000+000000+ Akab xxxx bb   0000000000943 3433 --> (detail rec)

220700000010SA14 AAA  00034354 DDD 000000000+    --> (detail rec)

220700000010SA15 888e a88 00000000+            --> (detail rec)

. . . . . remaining detail records

220700000012SA10 AAb   00000+000000+ Akab xxxx bb   0000000000943 3433 --> (detail rec)

220700000012SA14 AAA  00034354 DDD 000000000+   --> (detail rec)

220700000012SA15 888e a88 00000000+           -->  (detail rec)

. . . . . remaining detail records

220700000000SA99    Wednesday     2014-12-12  d334 000   --> (trailer is header)

[former_member200473]

Hi Ashraf,

I have 2 question to ask you.

1- Do you need field name also into your target file ( just in case of doubt) ?

2- what is the joining condition between header and detail records ?

Is possible for you show your input records ( header and details record ) in screen shot and expected output.

Regards,

Shiva Sahu

[Former Member]

Hi Shiva

1 - I don't need field names in the target file

2 - I just noticed I made an incorrect statement earlier about the header. It is not one per customer account. Rather, header and trailer records are only once per file and come from its own database table with no link to detail records. This is ok as the table always contain one row for each all the time so I can directly pull and write to file.

However, the detail records are linked by account id in each table. I have 10 tables, one for each record type. So getting the data is ok in BODS but my issue is just this changing length for each record type.

Thanks