topic Re: SQL for AFPO in Application Development and Automation Discussions

SQL for AFPO

Former Member — Fri, 25 Jul 2008 07:30:34 GMT

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!

Re: SQL for AFPO

Former Member — Fri, 25 Jul 2008 07:33:49 GMT

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.

Re: SQL for AFPO

Former Member — Fri, 25 Jul 2008 07:36:54 GMT

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

Re: SQL for AFPO

former_member787646 — Fri, 25 Jul 2008 07:54:17 GMT

Try this...

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

GROUP BY MATNR.

Hope this would help you.

Murthy

Re: SQL for AFPO

Former Member — Fri, 25 Jul 2008 08:04:36 GMT

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

Re: SQL for AFPO

former_member787646 — Fri, 25 Jul 2008 08:11:17 GMT

Hi Tomislav,

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

Murthy

Re: SQL for AFPO

Former Member — Fri, 25 Jul 2008 08:16:48 GMT

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

Re: SQL for AFPO

former_member787646 — Fri, 25 Jul 2008 09:21:04 GMT

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

Re: SQL for AFPO

Former Member — Fri, 25 Jul 2008 09:28:08 GMT

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

Re: SQL for AFPO

former_member787646 — Fri, 25 Jul 2008 09:48:08 GMT

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

Re: SQL for AFPO

Former Member — Fri, 25 Jul 2008 11:17:13 GMT

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