ESB- Flat file records to different DB tables based on first field
660195May 7 2009 — edited Jul 1 2009Hi,
I have a flat file which has records of six diff types. There are six diff tables corresponding to each of these records in the DB. My requirement is when ever such a flatfile comes in, all the records should be inserted into their corresponding tables based on their record identifiers.
Here is the same file:
000|SEC-US-MF|1234|POPOC|679|15-JAN-2009 01:04:48|SSS
100|226312||7130667|||13-JAN-2009 03:05:21|0||27||STANDARD|1.5%/30 NET 90|90|1.5|30|USD||||||N|282954105||SHELTON105|||||||||ELANCE-SSPF|||||||||LIVONIA BILL TO|238||Setzer|Pamela|||9000|1234|E|||||||||||||105||||1234|SEC-US-MF|15-JAN-2009 01:04:36|591785|Pamela|Setzer||||||APPROVED|13-JAN-2009 03:05:29||||N|N||||N|OPEN||||||||US||13-JAN-09|1|N||||||||||||||||Y||||N|Y||||||||||7130667-980064|||||||||New PO|ALL|||||
200|1|9000|N|Each|EA||||221170-153731-0-1-4|1|Mike Hopper|||15-JAN-2009 01:04:37||MRO|||||||||||||||||||||8011170000|||||7130667||18585180|1234|13-JAN-2009 03:05:21|226312|||80.11.17.00.00|||||1|10|REJECT|||||||MISC7||Y||FIXED|OPEN||||||||||||N|||||||||||||||
300|1|Each|15-JAN-2009 04:00:00||9000|0|0||||||N||||||||||||||||||PRA-PRODUCTS|PRA|4001 FAIRVIEW||SALEM|97302|US||OR||||1-800-547-0252|7130667||18585180|16529041|1234|13-JAN-2009 03:05:21|15-JAN-2009 01:03:40|0|0|0||PRA-PRODUCTS||1|N||||||N|N|10|REJECT|NONE|N|15|30|WARNING|0|100|PRA-PRODUCTS|STANDARD|CLOSED FOR RECEIVING|N||||||591785||||||||||Y|13-JAN-09||||N|P|||||||||||
400|1|350|7203|60075|000|0000|2734|||||||||||||||||||||||||Petersen|Mary M||9060|||Petersen|Mary M||208037547|PRA-PRODUCTS||9000|0||7130667||18585180|16529041|1234|13-JAN-2009 03:05:21|15-JAN-2009 01:04:08||16930995|||||||||||||||||1|9000||0|0||208037547|||0|||EXPENSE|SUPRA-PRODUCTS INV ORG|35072036007500000002734|10000002030000000000000|35072036007500000002734||N||PRA-PRODUCTS|SALEM|97302|OR|EXPENSE||||||||N||||N|N||||||||||0|0|0|||||||
100|226355||7136890|||14-JAN-2009 02:46:38|0||28||STANDARD|2%/20 NET 45|45|2|20|USD||||||N|C25962101|CLARK INC|TAMPA101|||||||||OASIS|||||||||LIVONIA BILL TO|9911||Furrow|Mary|||1100|1234|E||||||||||AHORSLEY@CLARK.COM|OASIS||101||||1234|SEC-US-MF|15-JAN-2009 01:04:34|590978|Mary|Furrow|N/AN/A|||||APPROVED|14-JAN-2009 02:46:45||||N|N||||N|OPEN||||||||US||14-JAN-09|1|N||||||||||||||||Y||||N|Y|||||||||GESTPOA|7136890-24772|||||||||New PO/Release|ALL|||||
200|1|1100|N|Each|EA|||||1|Renewal of early Calibration agreement|||15-JAN-2009 01:04:37||MRO|||||||||||||||||||||7315210000|||||7136890||18596861|1234|14-JAN-2009 02:46:38|226355|||73.15.21.00.00|||||1|10|WARNING|||||||||||FIXED|OPEN||||||||||||N|||||||||||||||
300|1|Each|14-JAN-2009 04:00:00||1100|0|0||||||N||||||||||||||||||GLOBAL HQTRS|8985 Town|||Bradenton|34202|US||FL|||||7136890||18596861|16541696|1234|14-JAN-2009 02:46:38|15-JAN-2009 01:03:40|0|0|0||GLOBAL HQTRS||1|N||||||N|Y|10|WARNING|WARNING|N|15|30|WARNING|0|0|GLOBAL|STANDARD|OPEN|N||||||||||||||||Y|14-JAN-09||||N|P|||||||||||
400|1|200|2900|61090|300|0000|0000|||||||||||||||||||||||||Loparev|Mark||14716|||Loparev|Mark||218011185|GLOBAL HQTRS||1100|0||7136890||18596861|16541696|1234|14-JAN-2009 02:46:38|15-JAN-2009 01:04:32||16944101|||||||||||||||||1|1100||0|0||218011185|||0|||EXPENSE|GLOBAL|20029006109030000000000|10000002030000000000000|20029006109030000000000||N||GLOBAL HQTRS|Bradenton|34202|FL|EXPENSE||||||||N||||N|N||||||||||0|0|0|||||||
999|SSS|1234|88|158|158|158
Kindly suggest how this can be achieved?
Thanks
RV
Edited by: user10236075 on May 7, 2009 1:12 PM