locked
Generate a Flat File from a View in Oracle DB RRS feed

  • Question

  • Hello Experts,

    We have a view in the Oracle DB is something like below,

    HEADER_TXT Test_Pro Submitter Transmission MEMBER_TXT EMP_ID_01 GEN DOB PS BENEFIT_TXT EMP_ID_02 Lay_Code Suffix TRAILER_TXT Record Count File_Date
     HEADER P LAB 16-Dec-16 MEMBER 1  
    M 19550502 
    N BENEFIT 1        MEA 17  TRAILER 20161216
     HEADER P LAB 16-Dec-16 MEMBER 1  
    M 19550502 
    N BENEFIT 1        DEN 33  TRAILER 20161216
     HEADER P LAB 16-Dec-16 MEMBER 2  
    F 19780502 
    N BENEFIT 2        MEA 33
     TRAILER 20161216
     HEADER P LAB 16-Dec-16 MEMBER    2  
    F 19780502 
    N BENEFIT 2        MEA 33  TRAILER 20161216

    Where each member/employee has 'n' benefits. Each records in the View has Header, Member,Benefit and Trailer. Each of them is seperated by the text like HEADER_TXT, MEMBER_TXT,BENEFIT_TXT,TRAILER_TXT.In the above table For EMP_ID_01 '1' has two BENEFITS. I will have to create a flat file from the view  like

                    Header Record

                                    Employee Data: Employee 0001 Detail

                                                    Dependents: Employee 0001 – Benefit 01 Detail

                                                    Dependents: Employee 0001 – Benefit 02 Detail

                                                    Dependents: Employee 0001 – Benefit 03 Detail, etc.

                    Trailer Record

                    Header Record 

                                    Employee Data: Employee 0002 Detail

                                                    Dependents: Employee 0002 – Benefit 01 Detail

                                                    Dependents: Employee 0002 – Benefit 02 Detail

                                                    Dependents: Employee 0002 – Benefit 03 Detail, etc.

                    Trailer Record

    I am not sure if this can be done. Can you guys please help me with this and suggest how to proceed with this.

    Monday, December 19, 2016 4:12 PM

Answers

  • Hi vdha,

    You can try the following,

    1) Read from the oracle db using an sp

    2) Have the structure of your flat file created  (Just a valid sample)

    3) Now generate the xsd for the Flatefile using the FF Wizard

    4) Create a map that will map the values from the Oracle DB to the FF schema that has been generated

    5) Decide if you wish to do some processing on the data or you just need the mapping then that will decide if you have to use the orchestration or just plain messaging only will do

    6) You have to create a custom pipeline that will assemble the ff schema message to the flat file message

    7) Create the send port and apply this pipeline as the send pipeline so that you can send the FF to the end system

    Regards


    Mandar Dharmadhikari

    • Marked as answer by vdha Wednesday, February 22, 2017 8:19 PM
    Tuesday, December 20, 2016 9:32 AM
    Moderator

All replies

  • Hi vdha,

    You can try the following,

    1) Read from the oracle db using an sp

    2) Have the structure of your flat file created  (Just a valid sample)

    3) Now generate the xsd for the Flatefile using the FF Wizard

    4) Create a map that will map the values from the Oracle DB to the FF schema that has been generated

    5) Decide if you wish to do some processing on the data or you just need the mapping then that will decide if you have to use the orchestration or just plain messaging only will do

    6) You have to create a custom pipeline that will assemble the ff schema message to the flat file message

    7) Create the send port and apply this pipeline as the send pipeline so that you can send the FF to the end system

    Regards


    Mandar Dharmadhikari

    • Marked as answer by vdha Wednesday, February 22, 2017 8:19 PM
    Tuesday, December 20, 2016 9:32 AM
    Moderator
  • Thanks Mandar. What I am confused is, since the records from the database are like

    Header details,Member01 details,Benefit01-Member01 details,Trailer details

    Header details,Member01 details,Benefit02-Member01 details,Trailer details

    Header details,Member02 details,Benefit01-Member02 details,Trailer details

    Header details,Member02 details,Benefit02-Member02 details,Trailer details

    Header details,Member02 details,Benefit03-Member02 details,Trailer details

    etc..

    What out output we will be sending should be

    Header detail

      Member 01 detail

         Benefit 01-Member 01 detail

         Benefit 02-Member 02 detail

      Member 02 detail

         Benefit 01-Member 02 detail

         Benefit 02-Member 02 detail

         Benefit 03-Member 01 detail

    Trailer

    I understand creating the flat file schema for the output. I am confused with the mapping, because the Benefit details of each member varies 1-n. Can you suggest if this doable through our maps.
    Tuesday, December 20, 2016 1:35 PM
  • I understand creating the flat file schema for the output. I am confused with the mapping, because the Benefit details of each member varies 1-n. Can you suggest if this doable through our maps.

    Can you please elaborate on this?? maybe a sample request response will be best, my suggestion is first setup the FF and generate the BizTalk FF schema, then we can work on the mapping pattern based upon the incoming data and what output is desired

    Regards


    Mandar Dharmadhikari

    Tuesday, December 20, 2016 1:38 PM
    Moderator