SQL for AFPO

[Former Member]

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!

[former_member181995]

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.

[Former Member]

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 ...

[former_member787646]

Hi

Try this...

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

GROUP BY MATNR.

Hope this would help you.

Murthy

[Former Member]

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

[former_member787646]

Hi Tomislav,

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

Murthy

[Former Member]

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

[former_member787646]

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

[Former Member]

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

[former_member787646]

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

[Former Member]

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