FOR ALL ENTRIES -> CDHDR / CDPOS Tables

Former Member · ‎07-03-2008

Hi,

I know there have been a lot of threads concerning the "for all entries" statement. Nevertheless I got a problem downloading the change document tables CDHDR/CDPOS with this statement and I haven't found anything in this forum regarding this special issue.

While trying the for all entries statement in the below manner for downloading first CDHDR and then the CDPOS for all entries of the selected CDHDR it took 3 days and didn't finish, so we aborted the ABAP. The JOIN statement doesn't work in a 4.6c environment for the Cluster tables, hence this isn't an option.

Can you give me some advise of how to improve downloading those tables in this way:

1. Download CDHDR with limitation of fields OBJECTCLAS and UDATE

2. Then Download CDPOS for all CHANGENR out of the first selection of CDHDR

This was the way I tried it:

SELECT
ACT_CHNGNO  CHANGE_IND  CHANGENR  MANDANT  OBJECTCLAS  OBJECTID  PLANCHNGNR  TCODE  UDATE  USERNAME  UTIME  WAS_PLANND APPENDING CORRESPONDING FIELDS OF TABLE T_CDHDR FROM CDHDR WHERE UDATE IN  s_UDAT1 AND OBJECTCLAS IN  s_OBJE0.
ENDSELECT.

SELECT 
CHANGENR  CHNGIND  CUKY_NEW  CUKY_OLD  FNAME  MANDANT  OBJECTCLAS  OBJECTID  TABKEY  TABNAME  UNIT_NEW  UNIT_OLD  VALUE_NEW  VALUE_OLD INTO gs_CDPOS FROM CDPOS CLIENT SPECIFIED for all entries in  T_CDHDR WHERE OBJECTCLAS IN  s_OBJE2 AND MANDANT EQ  T_CDHDR-MANDANT AND OBJECTCLAS EQ  T_CDHDR-OBJECTCLAS AND CHANGENR EQ  T_CDHDR-CHANGENR.
ENDSELECT.

Former Member · ‎07-03-2008

Use Function Module

CHANGEDOCUMENT_READ_HEADERS Change document: Read change document header

CHANGEDOCUMENT_READ_POSITIONS Change document: Read change document items

Please reward if useful.

former_member182371 · ‎07-03-2008

Hi,

have a look at this SCN weblog:

Best regards.

ThomasZloch · ‎07-03-2008

You can't join cluster tables in the newest version either.

I had similar problems with these huge tables in the past. As soon as you want to narrow down CDHDR by a UDATE range across all OBJECTIDs with tediously selecting all relevant OBJECTIDs first from the application tables, you're in trouble, because there is no secondary index on OBJECTCLAS / UDATE delivered by SAP.

I think this is one of the occasions where a new secondary index (on OBJECTCLAS and UDATE) can be justified, depending on how important this query is in your environment, and how narrow your UDATE selection is (you don't want changes from a full year, do you?)

Thomas

kesavadas_thekkillath · ‎07-03-2008

 THis prg might b usefull to u

*
*  Mass display or print Purchase Order History
*
*  You can request report by :
*  1.  Change date
*  2.  User Name
*  3.  Purchase Order Number
*  4.  Vendor Code
*
* Written by : SAP Basis, ABAP Programming and Other IMG Stuff
*              <a href="http://www.sap-img.com" TARGET="test_blank">http://www.sap-img.com</a>
*
REPORT ZPOCHANGE LINE-SIZE 132 NO STANDARD PAGE HEADING
                 LINE-COUNT 065(001)
                 MESSAGE-ID VR.

TABLES: DD04T,
        CDHDR,
        CDPOS,
        DD03L,
        DD41V,
        T685T,
        VBPA,
        TPART,
        KONVC,
        EKKO.

SELECT-OPTIONS: XUDATE FOR CDHDR-UDATE,
                XNAME  FOR CDHDR-USERNAME,
                XEBELN FOR EKKO-EBELN,
                XLIFNR FOR EKKO-LIFNR.

SELECTION-SCREEN SKIP.
* TEXT-001 - Sorting Sequence
SELECTION-SCREEN BEGIN OF BLOCK BLK1 WITH FRAME TITLE TEXT-001.
PARAMETERS: SUDATE RADIOBUTTON GROUP R1,
            SNAME  RADIOBUTTON GROUP R1,
            SOBID  RADIOBUTTON GROUP R1.
SELECTION-SCREEN END OF BLOCK BLK1.

DATA: WFLAG,
      WCHANGENR LIKE CDHDR-CHANGENR.

DATA: INDTEXT(60) TYPE C.

DATA: BEGIN OF ICDHDR OCCURS 50.
        INCLUDE STRUCTURE CDHDR.
DATA: END OF ICDHDR.

DATA: BEGIN OF ICDSHW OCCURS 50.
        INCLUDE STRUCTURE CDSHW.
DATA: END OF ICDSHW.

DATA: BEGIN OF EKKEY,
        EBELN LIKE EKET-EBELN,
        EBELP LIKE EKET-EBELP,
        ETENR LIKE EKET-ETENR,

      END OF EKKEY.

DATA: BEGIN OF ITAB OCCURS 50,
        BEGIN OF EKKEY,
          EBELN LIKE EKET-EBELN,
          EBELP LIKE EKET-EBELP,

          ETENR LIKE EKET-ETENR,
        END OF EKKEY,
        CHANGENR LIKE CDHDR-CHANGENR,
        UDATE    LIKE CDHDR-UDATE,
        UTIME    LIKE CDHDR-UTIME,
        USERNAME LIKE CDHDR-USERNAME,
        CHNGIND  LIKE CDSHW-CHNGIND,
        FTEXT    LIKE CDSHW-FTEXT,
        OUTLEN   LIKE CDSHW-OUTLEN,
        F_OLD    LIKE CDSHW-F_OLD,
        F_NEW    LIKE CDSHW-F_NEW,
      END OF ITAB.

DATA: OLD_OBJECTID LIKE CDHDR-OBJECTID.

FIELD-SYMBOLS: <F_OLD>, <F_NEW>.

SELECT * FROM EKKO WHERE EBELN IN XEBELN AND
                         LIFNR IN XLIFNR.
  CLEAR CDHDR.
  CLEAR CDPOS.
  CDHDR-OBJECTCLAS = 'EINKBELEG'.
  CDHDR-OBJECTID   = EKKO-EBELN.
  PERFORM GETCHGDOCS.
ENDSELECT.

IF SUDATE = 'X'.
  SORT ITAB BY UDATE EKKEY-EBELN CHANGENR EKKEY-EBELP
               EKKEY-ETENR.
ELSEIF SNAME = 'X'.
  SORT ITAB BY USERNAME EKKEY-EBELN CHANGENR EKKEY-EBELP
               EKKEY-ETENR.
ELSE.
  SORT ITAB BY EKKEY-EBELN CHANGENR EKKEY-EBELP EKKEY-ETENR.
ENDIF.

LOOP AT ITAB.
  CLEAR: INDTEXT, EKKEY.
  CASE ITAB-CHNGIND.
    WHEN 'U'.
        INDTEXT(50) = ITAB-FTEXT.
        INDTEXT+51  = TEXT-020.
        CONDENSE INDTEXT.
    WHEN 'D'.
        INDTEXT = TEXT-021.
    WHEN 'E'.
        INDTEXT(5) = ITAB-FTEXT.
        INDTEXT+51 = TEXT-021.
        CONDENSE INDTEXT.
      WHEN 'I'.
        INDTEXT = TEXT-022.
    ENDCASE.
    RESERVE 4 LINES.
    IF WCHANGENR NE ITAB-CHANGENR.
      WCHANGENR = ITAB-CHANGENR.
      EKKEY = ITAB-EKKEY.
      WRITE:/ ITAB-UDATE UNDER 'Change Date',
              ITAB-UTIME UNDER 'Time',
              ITAB-USERNAME UNDER 'User Name',
              ITAB-EKKEY-EBELN UNDER 'PO No',
              ITAB-EKKEY-EBELP UNDER 'Item',
              ITAB-EKKEY-ETENR UNDER 'Sch No',
              INDTEXT     UNDER 'Changes'.
    ELSEIF ITAB-EKKEY NE EKKEY.
      WRITE:/ ITAB-EKKEY-EBELP UNDER 'Item',
              ITAB-EKKEY-ETENR UNDER 'Sch No',
              INDTEXT     UNDER 'Changes'.

    ENDIF.

    CASE ITAB-CHNGIND.
      WHEN 'U'.
        ASSIGN ITAB-F_OLD(ITAB-OUTLEN) TO <F_OLD>.
        ASSIGN ITAB-F_NEW(ITAB-OUTLEN) TO <F_NEW>.
        WRITE: / TEXT-023  UNDER 'Changes',
                 <F_OLD>.
        WRITE: / TEXT-024 UNDER 'Changes',
                 <F_NEW>.
      WHEN 'E'.
        ASSIGN ITAB-F_OLD(ITAB-OUTLEN) TO <F_OLD>.
        WRITE: TEXT-023 UNDER 'Changes',
               <F_OLD>.
    ENDCASE.
    SKIP.
ENDLOOP.


TOP-OF-PAGE.
WRITE:/ SY-DATUM,SY-UZEIT,
       50 'P U R C H A S E  O R D E R   H I S T O R Y',
      120 'Page', SY-PAGNO.
WRITE: / SY-REPID,
         60 'Purchase Orders Changes'.
SKIP.
ULINE.
IF SUDATE = 'X'.
  WRITE:/001 'Change Date',
         014 'Time',
         024 'User Name',
         038 'PO No',
         050 'Item',
         057 'Sch No',
         065 'Changes'.
ELSEIF SOBID = 'X'.
  WRITE:/001 'PO No',
         013 'Item',
         020 'Sch No',
         028 'Change Date',
         041 'Time',
         051 'User Name',
         065 'Changes'.
ELSE.
  WRITE:/001 'User Name',
         015 'Change Date',
         028 'Time',
         038 'PO No',
         050 'Item',
         057 'Sch No',
         065 'Changes'.
ENDIF.
ULINE.

FORM GETCHGDOCS.
  CALL FUNCTION 'CHANGEDOCUMENT_READ_HEADERS'
       EXPORTING
            DATE_OF_CHANGE    = CDHDR-UDATE
            OBJECTCLASS       = CDHDR-OBJECTCLAS
            OBJECTID          = CDHDR-OBJECTID
            TIME_OF_CHANGE    = CDHDR-UTIME
            USERNAME          = CDHDR-USERNAME
       TABLES
            I_CDHDR           = ICDHDR
       EXCEPTIONS
            NO_POSITION_FOUND = 1
            OTHERS            = 2.

  CHECK SY-SUBRC EQ 0.
  DELETE ICDHDR WHERE CHANGE_IND EQ 'I'.
  CHECK NOT ICDHDR[] IS INITIAL.
  LOOP AT ICDHDR.
    CHECK ICDHDR-UDATE IN XUDATE.
    CHECK ICDHDR-USERNAME IN XNAME.
    CALL FUNCTION 'CHANGEDOCUMENT_READ_POSITIONS'
                 EXPORTING  CHANGENUMBER       = ICDHDR-CHANGENR
                 IMPORTING  HEADER             = CDHDR
                 TABLES     EDITPOS            = ICDSHW
                 EXCEPTIONS NO_POSITION_FOUND  = 1
                            OTHERS             = 2.
    CHECK SY-SUBRC EQ 0.
    LOOP AT ICDSHW.
      CHECK ICDSHW-TEXT_CASE EQ SPACE.
      MOVE-CORRESPONDING ICDSHW TO ITAB.
      MOVE-CORRESPONDING ICDHDR TO ITAB.
      MOVE ICDSHW-TABKEY+3 TO ITAB-EKKEY.
      APPEND ITAB.
    ENDLOOP.
  ENDLOOP.
ENDFORM.
*
* END OF PROGRAM

Former Member · ‎07-03-2008

Thanks so far...

So I understand "CHANGEDOCUMENT_READ_POSITIONS" seems to be the way to go, right?

But why is it faster than a usual SELECT statement with FOR ALL ENTRIES? What is it doing different to be the better alternative for those very large tables?

Sebastian

christine_evans · ‎07-03-2008

There is no guarantee that using any SAP standard function will be faster than an efficiently written SELECT whether it uses FAE or not. If you really feel you need to know how this function module works then you should go and study it.