3 Replies Latest reply on Nov 10, 2005 4:24 AM by 456893

    Publishing master -detail info from database(DBadap) , ending in xml file

    456893
      Hi,

      I have to create a solution where I will create an xml out of 3 tables' data which will be pushed to B2B OUT_QUEUE.The xml file will be Rosettanet 3A4 PIP(outbound PO).
      I need some help getting started...
      Which should be the common view?.. I think 3A4 DTD should work, same as Appl. view for subscribing AQ adapter

      I need to transfer the data as master-detail (PO header & lines & sublines) information from DBadapter. How to use iStudio to do the mapping since I only can use one table for one common view.
      Even if I add tables how to map with common view.I know array can use to map the detail information.But how to do it on database side? What contents should I define in application view for publishing side (DBAdapter)?

      What all setting needs to be done both in AQ adapter ini n B2B side for putting data into the B2B queues?

      Any help is appreciated!!

      Regards,

      MSri
        • 1. Re: Publishing master -detail info from database(DBadap) , ending in xml file
          416900
          MSri,

          1.Create 3 Application Data Types (on your DB Application Side), each based on each of your three tables (presumably Header, Line and Subline).

          2.Create another Application Data Type, based on the 3 Application DataTypes created above. i.e. bring them all together.

          (a) Create an Application View based on the Subline table. (Call it "Sublines"). Save.
          (b) Create an Application View based on your Line table. Now care....
          **** Under the last column of the imported table structure, choose Add.
          **** Change the Type from "String" to DataType
          **** Click anywhere in the "Create Application Data Type" window, and the Applications Data Type window appears. Select "Sublines".
          **** Give the "Sublines" datatype a name e.g. Sublines (what else!?!)
          Call the whole data type "Lines". Save.
          (c) Create an Applications View based on the Headers table. Again care....
          **** Similar to the steps in (b), this time choose "Lines" as the datatype.
          Call the whole data type "Header". Save.

          You should now have something like this....
          Header Details....String, Integer Types etc.
          More Header Details....String, Integer Types etc.
          More Header Details....String, Integer Types etc.
          More Header Details....String, Integer Types etc.
          (-) >> Line Details ... Lines datatype
          Line Details .... Strings, Integer Types etc.
          More Line Details .... Strings, Integer Types etc.
          More Line Details .... Strings, Integer Types etc.
          More Line Details .... Strings, Integer Types etc.
          (-) >>>>>>Sublines ... Subline datatype
          Subline Details.... Strings, Integer Types etc.
          More Subline Details.... Strings, Integer Types etc.
          More Subline Details.... Strings, Integer Types etc.
          3. Create your mappings from the new DB Application View to the Common View.

          4. iStudio will create (when you do the File > Export PL/SQL) a package containing one procedure with 2 functions. Export the code from iStudio, and compile in your source DB system.

          5. Call the Procedure, named "crMsg_[Common_View_Name]_OAI_V1"

          6. Pass the messageObjectID and aoID from the "Header" procedure call into each of the functions, together with the data from your tables. Lines first (cr_Lines_Lines), then sublines (cr_Subline_Sublines). (Lots of lovely loops and cursors!).

          7. Call the publish message.

          HTH
          Yan
          • 2. Re: Publishing master -detail info from database(DBadap) , ending in xml file
            461680
            Msri,
            The simple way to do this is to use the DTD the describes your XML and use that as the source of your application view. There are 2 ways to do this either create an application data type (right click-> new), and select import-> XML, select the DTD from wherever it lives on your disk. Then from the Publish event define application data typ select import application data type. When you generate the PL/SQL you will have a set of procedures that you have to call in the correct sequence from whatever code will perform the publish of the message. You populate the values of the procedures parameters with the data from your table.
            • 3. Re: Publishing master -detail info from database(DBadap) , ending in xml file
              456893
              Thnx a lot for the help!!

              I created datatypes using II approach (by hit n trial, I got this solution) & deleted the unwanted datatypes( not required to be passed in xml) from it.

              regards,
              MSri