B2B Integration

 View Only
  • 1.  SQL Outbound Translation map

    Posted Thu May 30, 2024 02:15 PM

    Hi,

    We are trying to Integrate Sterling Integrator with Net Suite database using ODBC Connection.The ODBC setup is done and Pool Connection is also done successfully.I have created the Outbound map.. We have two queries used one is at the header level and another one is at the line level.

    --HeadeQuery

    --HeaderRec(to pull the values)

        --field

    --Group(100 times)

         --GroupQuery

         --GroupRec

        --field

    When I try to run the map all the lines are printed but not grouped by.When i try to manipulate and play its bringing my application down..Is there a way to handle it..

    Output I am getting

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

    A|1|

    B|1|

    B|2|

    A|2|

    All I need is the below expected output

    A|1|

    B|1|

    A|2|

    B|2|

    How this can be achieved.Any help is appreciated..

    Thanks,

    Subathra Bairavan



    ------------------------------
    Subathra Bairavan
    ------------------------------


  • 2.  RE: SQL Outbound Translation map

    Posted Tue June 04, 2024 09:59 AM

    Sorry for my stupid answer, but have you tried to order the sql query to obtain that output?



    ------------------------------
    Fabrizio Allegrini
    ------------------------------



  • 3.  RE: SQL Outbound Translation map

    Posted Mon June 10, 2024 10:14 AM

    Hi Fabrizio,

    Yes I tried Group to on the SQL statement still its printing my header first and then the line details.I have raised the question to IBM support folks and they suggested me to use Key field concept.I haven't tried yet.I will let you know how it goes..



    ------------------------------
    Subathra Bairavan
    ------------------------------



  • 4.  RE: SQL Outbound Translation map

    Posted 16 days ago

    Hi Subathra,

    Are you selecting data from database (SQL in the input side of the map) or you are inserting into database (using SQL in the output side of the map)?

    Kind regards,
    Mirjana



    ------------------------------
    Mirjana Vojvodic
    ------------------------------



  • 5.  RE: SQL Outbound Translation map

    Posted 14 days ago

    Hi Mirjana,

    I am extracting the data from SQL(Input side) and writing them to the CSV file (Output side)

    Thanks,

    Subathra Bairavan



    ------------------------------
    Subathra Bairavan
    ------------------------------



  • 6.  RE: SQL Outbound Translation map

    Posted 11 days ago
      |   view attached

    Hello Subathra,


    I had this CSV structure in the input as below, let's imagine this is the same as you get from database.
    Source is not the same, but structure is, so I would like to discuss this example.

    N;10012;20031118;292.67;4

    N;10013;20031119;3627.46;3

    N;10014;20031120;11899.77;8

    N;10015;20031121;292.67;4

    S;4324;10012;101;23;19.98;2;39.96

    S;4325;10012;102;24;21.89;4;87.56

    S;4326;10012;103;46;115.23;1;115.23

    S;4327;10012;104;311;8.32;6;49.92

    S;4328;10013;105;54;12.43;2;24.86

    S;4329;10013;107;435;543.4;6;3260.4

    S;4330;10013;108;4526;342.2;1;342.2

    S;4331;10014;109;53;6.32;4;25.28

    S;4332;10014;110;3452;12.23;4;48.92

    S;4333;10014;111;54;12.43;23;285.89

    S;4334;10014;112;2;7.11;6;42.66

    S;4335;10014;113;76;34.43;42;1446.06

    S;4336;10014;114;4443;110.3;64;7059.2

    S;4337;10014;115;654;4.43;432;1913.76

    S;4338;10014;116;6544;98;11;1078

    S;4339;10015;117;23;19.98;2;39.96

    S;4340;10015;118;24;21.89;4;87.56

    S;4341;10015;119;46;115.23;1;115.23

    S;4342;10015;120;311;8.32;6;49.92

    I created TEMP structure in the input side of the map that will be Header One - Details, Header Two - Details, ... rather then All Headers - All Details.

    Map screenshot is attached.

    You cannot have TEMP structure in your map, but that might be done by extended rule in the Post Session, that can see both sides of the map and that will be executed after direct links. You have to have at least one direct link per group/record to get mapping, that will be dummy link to create any output, but Post Session will override it with correct tructure.

    Extended rule used in the INPUT side in my map is below, but you will have to modify for your map:

    integer header_count;

    integer details_count;

    integer temp_index_details;

    integer header_i;

    integer details_i;

    header_count = count($header[*]);

    details_count = count($details[*]);

    header_i = 1;

    details_i = 1;

    temp_index_details = 1;

    while ( header_count >= header_i ) do

       begin

          $TEMP_GROUP[header_i].#TEMP_invoice_ID_header = $header[header_i].#invoice_ID_header;

          $TEMP_GROUP[header_i].#TEMP_date_header = $header[header_i].#date_header;

          $TEMP_GROUP[header_i].#TEMP_count_details_header = $header[header_i].#count_details_header;

          $TEMP_GROUP[header_i].#TEMP_total_amount_header = $header[header_i].#total_amount_header;

             while ( details_count >= details_i ) do

                begin

                if ( $details[details_i].#invoice_ID_detail = $header[header_i].#invoice_ID_header ) then

                  begin

                                                                               $TEMP_details[header_i][temp_index_details].#TEMP_detail = $details[details_i].#detail;

                                               $TEMP_details[header_i][temp_index_details].#TEMP_invoice_ID_detail = $details[details_i].#invoice_ID_detail;

                                               $TEMP_details[header_i][temp_index_details].#TEMP_ID_detail = $details[details_i].#ID_detail;

                                               $TEMP_details[header_i][temp_index_details].#TEMP_unit_ID_detail = $details[details_i].#unit_ID_detail;

                                               $TEMP_details[header_i][temp_index_details].#TEMP_unit_price_detail = $details[details_i].#unit_price_detail;

                                               $TEMP_details[header_i][temp_index_details].#TEMP_qty_detail = $details[details_i].#qty_detail;

                                               $TEMP_details[header_i][temp_index_details].#TEMP_total_price_detail = $details[details_i].#total_price_detail;

                                                                            

                details_i = details_i + 1;  

                temp_index_details = temp_index_details + 1;

                                                                           

                end

    else

                begin                                   

                     details_i = details_i + 1;

                end                                                  

        end

                                                                           

       header_i = header_i + 1;

       details_i = 1;

       temp_index_details = 1;

                                                         

       end

    That is just an idea and similar example I have.

    Kind regards,

    Mirjana



    ------------------------------
    Mirjana Vojvodic
    ------------------------------



  • 7.  RE: SQL Outbound Translation map

    Posted 14 days ago

    Hi Fabrizio,

    Its not working with Key field concepts.I tried to use 2 maps one with data extract and another one map for Sorting.In that way it works.



    ------------------------------
    Subathra Bairavan
    ------------------------------