Checking production order status

[Former Member]

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
         jcds~udate
         afpo~posnr  afpo~wemng afpo~amein afpo~matnr
         mara~matkl
         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
    INTO CORRESPONDING FIELDS OF TABLE gt_data
*   INTO TABLE GT_DATA
   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 ).

[RichHeilman]

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

Regards,

Rich Heilman

[Former Member]

Hi Rich,

Performance is fine. As you can see Iam selecting date jcds~udate. For this there are 3 possibilities.

1)for a given order number if you have status 'I0012' only

in JCDS table - program working fine.

2)for a given order number if you have status 'I0045' only

in JCDS table - program working fine.

3)for a given order number if you have status both 'I0012' and 'I0045' then I have to choose the date from JCDS table in a way that for which status(either 'I0012' or 'I0045') the date is first - Need help(for this my program selecting both the dates but I need only one which comes first).

I hope you understand the problem.

regards,

chandu.

[Peter_Inotai]

What about selection max(jcds~udate)?

I'd definitely make more smaller select statement, because it will be quite difficult to maintain such a gigantic select statements.

Peter