Skip to Content
author's profile photo Former Member
Former Member

SQL for AFPO

Hi friends!

I need an SQL which selects last production order for all materials. I try something with MAX( afpo~LTRMI ).

SQL should return list of unique material numbers, production order and date of last goods receipt for each one.

Thanks for your help!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

10 Answers

  • Posted on Jul 25, 2008 at 07:33 AM

    Tomislav,

    please forgive if i become a fool.i can suggest a logic cause i did the same with latest invoice number against delivery.

    but am not much in concept of production order.

    see first select all production order for material than sort decending with production order and materian both.than delete abjecent duplicate comaring matnr you will get the latest record of each material.

    hope this would help you.

    Amit.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 07:36 AM

    no ... I'm afraid that that's not a good solution. AFPO is pretty big table and I need a list of all materials. If I select all PO's for all materials ... this will slow down my aplication.

    Im loking for solution with MAX(), GROUP BY, ORDER BY ... 😊

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 07:54 AM

    Hi

    Try this...

    SELECT MATNR MAX( <prod_order> ) FROM AFPO INTO ( <matnr_var>, <order_var> )

    GROUP BY MATNR.

    Hope this would help you.

    Murthy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 08:04 AM

    you were close ... I try with this but than I don't have a PO# (aufnr). If I put AUFNR in select and in GROUP BY than i get all PO's and I only need the last one.

    You suggested max (AUNFR) but this is not 100% correct because some PO could be opened before than other which will be produced earlyer 😊

    Edited by: Tomislav Uroic on Jul 25, 2008 10:05 AM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 08:11 AM

    Hi Tomislav,

    Please paste your SQL here... I will try by tweaking ur SQL.

    Murthy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 08:16 AM
    SELECT DISTINCT matnr ltrmi aufnr
        INTO CORRESPONDING FIELDS OF TABLE i_afpo
        FROM afpo
        WHERE matnr IN s_matnr
          AND dwerk = p_werks
          AND lgort IN s_lgort
        ORDER BY ltrmi DESCENDING.
    
        delete ADJACENT DUPLICATES FROM i_afpo COMPARING matnr.

    This is workin' but ... with low performances ...

    Thanks in advance

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 09:21 AM

    Hi

    Try this...

    SELECT MATNR MAX( ltrmi ) FROM AFPO INTO CORRESPONDING FIELDS OF TABLE i_afpo

    WHERE matnr IN s_matnr AND dwerk = p_werks AND lgort IN s_lgort

    GROUP BY MATNR.

    Hope this would help you.

    Murthy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 09:28 AM

    Thank you for your time Murthy but with your example I don't have a PO#.

    Please pay attention on these three fileds: AUFNR, MATNR and LTRMI (I need all of them in my result)

    Unfortunately you cannot add AUFNR in SELECT without putting it in GROUP BY... and if you do this you will get all PO's for one material. It means no improvement 😔

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 09:48 AM

    Try this.

    SELECT MATNR LTRMI AUFNR FROM AFPO INTO CORRESPONDING FIELDS OF TABLE i_afpo

    WHERE LTRMI IN ( SELECT MAX( ltrmi ) FROM WHERE matnr IN s_matnr AND dwerk = p_werks

    AND lgort IN s_lgort

    GROUP BY MATNR ).

    Murthy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 25, 2008 at 11:17 AM

    Well I tried but in debug mode I still see a lot of selected PO's in i_afpo and incorrect result at the end

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.