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 a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

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