Skip to main content

Insert itemgroup in Material requirement list

Answered
Luis Abellaneda
  • Edited

Can I add Item group to material requirement list (Production > Report WO)?

I think I can use an UDF but I don't know how to set up.

2

Comments

16 comments

Date Votes
  • Official comment
    Martin Heigl
    • Edited

    With template administration

     

    create new template

    with new field

     

    and insert sql statement for getting the item group name

     

    SQL Command

    (select "ItmsGrpNam" from "OITB" where "OITB"."ItmsGrpCod"="OITM"."ItmsGrpCod")

     

    with window settings you can move this column on correct place

     

    Result:

     

    regards

    M.Heigl

  • Luis Abellaneda

    Ok, thanks, it works very well :)

  • Luis Abellaneda

    Hi,

    Come to this topic again because this is not working in MRP templates. How can we add information from fields in the OITM or other tables in the MRP Order recommendatio list? I tried to add the SQL sentence to the details of UDF1, but it is not working:

  • Martin Heigl

    Hi Luis

    this is the wrong place. You try to insert sql statement in window settings. But you must do this in template settings. See my answer before.

     

    regards

    Martin

  • Luis Abellaneda

    Hello Martin,

    In Material requirement was possible to do that beacuse on template administration we already hace the fields available. In MRP templates, we do not have any fields tab:

  • Martin Heigl

    Hi Luis,

    yes, i see. We don't have template fields in the MRP recommendation - top list.
    It's possible to fill the fields manually. But this will be slow.
    Or to manipulate the SQL statement behind this list directly. i will check, if i find a solution for this.

     

    regards

    Martin

  • Luis Abellaneda

    Hello,

    Thanks for your answer Martin. We have a similar problem in other Beas window: in external production – Deliver Provisions, we do not have templates available.

    If we can add SQL statements to Formulas in field detail, this can be solved, but we donot know how to join information that are not already extracted in the actual template.

  • João Antonio

    Luís
    I did a procedure to feed these fields into the MRP.
    This tip for helping you.
    Create a script to update the udf1 field with the result you want, and insert this script at the end of the MRP calculation.

     

     

  • Luis Abellaneda

    Hi Joao,

    You mean a script in Beas TN or in MRP window?

    I hope Martin or anyone else in Boyum would provide a solution over the standard functionality.

    Thanks!!

  • Martin Heigl

    In Standard this is not possible

    One concept (but not nice) is to change the SQL statement in behind the window

    You can replace an sql fragment to another. Rule: must return same type (string, number, date)

    It is not possible to remove or add additional fields.

    In debug log you see follow entries (here External production - deliver provisions

    in form_readsql the sql statement is saved in variable <sys_sql>
    and here you can change it

    Example script

    global function form_readsql
    messagebox=<sys_sql>
    end global

    if you open the form, you see the real sql statement. Note: This is not always exact what you see in debug log, because this statement is BEFORE the online MSSQL/HANA converter was started.

    In follow example we replace "OWHS"."Street" to "OPOR"."NumAtCard"

    global function form_readsql
    tools=globalreplace=sys_sql="OWHS"."Street"="OPOR"."NumAtCard"
    end global

    In window setting self you can replace the title
    NOTE: Without guarantee that it's working in next versions (but in > 90% it will work in next version)

    This is not working in every form. Example MRP: We've different Lists in one form and Lists without SQL statement in backend. 

    regards
    Martin

  • Luis Abellaneda

    Hi Martin,

    This could solve one of my problems, but 2 questions:

    - Can we use SQL statements. in you example, if I want to extract a data from a line un POR1, can we use min()?

    - How can we do something similar un MRP?

  • Martin Heigl

    you can do everything
    you can also insert a sub select command (note: performance - special if a group command inside)
    you can work with min/max
    Only the sql command must be correct after changing. 

    MRP: 
    need exact information what you want to change

  • Luis Abellaneda

    Hi Martin,

    I want to know the stock from item in the standard warehouse of that item. I tried this kind of query within the field, but I cannot get that information:

    SELECT T1.[OnHand] FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] WHERE T1.[WhsCode] = T0.[DfltWH]

     

  • Martin Heigl
    • Edited

    this list is saved in dw_2
    and some fields changed before retrieve

    In debug level i found only one place : dw_2_load
    Idea: Load table definition, change, save back
    And then the follow script can continue normal

    In this example i replace field user1
    Note: in HANA you must check the name of the field

    with messagebox=<dw_2.tableselect> 
    you can see always the sql statement in simple way.

     

    global function dw_2_load
    setvar=ls_sql=<dw_2.tableselect>
    tools=globalreplace=ls_sql=beas_mrp.user1=(SELECT "OnHand" FROM "OITW" WHERE "OITW"."ItemCode" ="OITM"."ItemCode" and "OITW"."WhsCode" = "OITM"."DfltWH")
    dw_2.tableselect=<ls_sql>
    end global
  • Luis Abellaneda

    Hi Martin,

    Thank you very much, but this is not working. UDF1 is not showing the stock:

  • Martin Heigl

    please check, if "beas_mrp.user1" is inside the sql statement 
    example with 

    messagebox=<dw_2.tableselect> 

    and check inside the debug log

     

Please sign in to leave a comment.

Didn't find what you were looking for?

New post