none
Storing EDI Purchase Order in Database,how many columns should be there practically? RRS feed

  • Question

  • hi all

    i have an EDI application,uptill now i am able to take EDIFACT 850 PO file as input,i have mapped it with my xml file and outputed it in a folder which again an EDIFACT file but what i want to do is to store that PO in database but i am not able to figure it out how?? some of my question are as follows

    1) do i need to use updategram?? so that i will map my incoming EDI xml with my updategram? or anything else?

    2) how many column can be practically be there in Database table? as EDI file is containing planty of TAGS like PRE,DTM etc

    thxs

    Tuesday, April 13, 2010 12:56 PM

All replies

  • This is really a business question (answered by your business analyst), as no one on this forum understands what you are getting from your source system, nor what the business wants to see going forward. Yes there are many items that CAN be in the PO, but if I were you, I would capture the following:

    1. PO Number

    2. Item

    3. Customer

    4. Quantity

    5. Price

     

     


    Eric Stott [http://blog.biztalk-info.com] - Mark as Answer if this reply does.
    Wednesday, April 14, 2010 6:22 AM
  • Hi,

    1) You can use an updategram or a stored proc, also depends on which database you are storing this information on.

    2) As many as you want, but you have to figure out what the requirement is around which fields are required from business perspective. I would go by what Eric mentioned as a starter.

    Cheers

    Bali

    MCTS: BizTalk Server

    ----------------------------------------------------------

    Mark As Answer or Vote As Helpful if this helps.

    Saturday, April 24, 2010 5:53 AM
  • Hi Daivagna,

    You cannot use updategram in BTS 2009 because updategram is not available in  BTS 2009.

    The no of column is depending upon your project. Like it may have n columns if the PO has n tags. Or (n-x) or (n+x) columns if your PO is created by multiplying some other columns(like Total=Price*quantity).

    You need consult your business analyst regarding the meaning of the tags. According to this you can map the tags to the respective columns and can store your PO in the database using a SP.

     

    I think this will help you out.

    Regards

    Elango

    Mark As Answer or Vote As Helpful if My Reply Does.

    Tuesday, April 27, 2010 11:17 AM
  • "You cannot use updategram in BTS 2009 because updategram is not available in  BTS 2009."

    There has not been any SQL implementation of BizTalk that has been deprecated in 2009. You can use an updategram. I am using one right now in 2009.


    Eric Stott [http://blog.biztalk-info.com] - Mark as Answer if this reply does.
    • Proposed as answer by DPS Bali Monday, May 14, 2012 11:25 AM
    Tuesday, April 27, 2010 12:53 PM
  • I agree with Eric,

    The following are the features of the Microsoft BizTalk Adapter for SQL Server. For reference excerpt:

    This SQL adapter which is also a part of the BizTalk Adapter Pack 2.0 can be used to connect BizTalk applications to SQL Server. The adapter replaces the current native SQL adapter and offers many more features.

    • Insert/Update/Delete/Select operations on tables and views
    • Invoke (SQL and CLR) Stored procedures, scalar valued functions , table value functions
    • Supports execution of generic T-SQL statements, via the ExecuteReader(), ExecuteNonQuery() and ExecuteScalar() operations
    • Configurable polling – polling statement, polling interval, receive multiple result sets
    • Support for x86 and x64 platforms
    • Support for new data types in SQL2005 and SQL2008 (notably FILESTREAM, varbinary (max))
    • Support for UDTs
    • Ability to pass table value parameters
    • Composite operations - invoke operations on multiple tables and call any number of stored procedures in the same transaction
    • SQL query notifications
    • Ability to directly call stored procedures which used the ‘for xml’ style supported by the BizTalk 2006 SQL adapter to ease backward compatibility


    Regards,
    Bali
    MCTS: BizTalk Server
    --------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    • Proposed as answer by DPS Bali Monday, May 14, 2012 11:25 AM
    Tuesday, April 27, 2010 5:06 PM
  • To add to Eric's field's, I would recommend the following:

    At the Order Header level:

     

    • Order Id / Number
    • Order date
    • Delivery address (possibly coded)
    • Expected delivery date
    • Special instructions (if any)

    and at the Line level (there can be many lines to an order):

    • Line number
    • Part number
    • Suppliers part number
    • Production description
    • Quantity
    • Price
    • Expected delivery date (can be different on each line)

    I would also recommend using BAM to capture this data, rather than a custom SQL adapter solution - it'll be much cleaner and you can add/remove fields as required, without the need to re-work your solution.

     


    Nick Heppleston - Independent UK BizTalk Consultant (http://www.modhul.com)

    Please mark as answered if this answers your question.

    Looking for a fast and efficient way to archive EDI messages in BizTalk? Try Atomic-Scope's new BizTalk Message Archiving Pipeline Component. Download a free 14-day trial!

    Friday, May 7, 2010 3:29 PM