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

Checking production order status

Hi all,

I have to select the ordernumbers(aufnr)and date from JCDS table in a way that,

I have to check the status in jcds table with status

'delivered'(I0012) or 'Technically completed'(I0045) and if you have both status for a given order number then i have to check the dates for both status and select the one which is first.

for ex :

status 'I0012' date 27.07.2001

status 'I0045' date 12.09.2005

then i have to select only status 'I0012' because it is

first in date.

please help me.

and now I have following select statement which selects both dates.

SELECT aufk~aufnr  aufk~auart aufk~werks aufk~prctr
         afko~plnbez afko~gamng afko~gasmg afko~aufpl afko~dispo
         afpo~posnr  afpo~wemng afpo~amein afpo~matnr
         afvj~aplzl  afvj~lmnga afvj~xmnga afvj~umrez afvj~umren
    FROM aufk
    JOIN afko ON afko~aufnr = aufk~aufnr
    JOIN jcds ON jcds~objnr = aufk~objnr
             AND jcds~chgnr = '001'
    JOIN afpo ON afpo~aufnr = aufk~aufnr
    JOIN mara ON mara~matnr = afpo~matnr
    JOIN afvv AS afvj ON afvj~aufpl = afko~aufpl
   WHERE aufk~aufnr  IN s_aufnr
     AND aufk~auart  IN s_auart
     AND aufk~werks  IN s_werks
     AND aufk~prctr  IN s_prctr
     AND afko~dispo  IN s_dispo
     AND jcds~udate  IN s_budat
     AND afpo~posnr   = '0001'
     AND afpo~matnr  IN s_matnr
     AND mara~matkl  IN s_matkl
     AND ( afvj~lmnga > 0 OR afvj~xmnga > 0 )
     AND afvj~aplzl IN ( select MIN( aplzl )
                           FROM afvv
                          WHERE aufpl = afko~aufpl
                            AND ( lmnga > 0 OR xmnga > 0 )
                            AND exists ( SELECT *
                                           FROM afvc
                                          WHERE aufpl = afvv~aufpl
                                            AND aplzl = afvv~aplzl
                                            AND steus <> 'YP12'    )
     AND ( jcds~stat = 'I0012' OR jcds~stat = 'I0045' )
     AND exists ( SELECT * FROM jest
                          WHERE objnr = aufk~objnr
                            AND ( stat = 'I0012' OR stat = 'I0045' )
                            AND inact = space ).

Add comment
10|10000 characters needed characters exceeded

1 Answer

  • Posted on Oct 06, 2005 at 12:29 PM

    Yikes.......nice select statement. How is performance? I probably seperate the checking of the status tables.


    Rich Heilman

    Add comment
    10|10000 characters needed characters exceeded