topic Re: Selecting from change pointer internal table... in Application Development and Automation Discussions

Selecting from change pointer internal table...

Former Member — Wed, 12 Apr 2006 15:20:03 GMT

hello everyone,

I have the following in my code:

data: begin of i_matl_details occurs 0,

matnr type mara-matnr, "Material Number

bstme type mara-bstme, "Purchase Order Unit of Measure

vpreh type mara-vpreh, "Comparison Price Unit

matkl type mara-matkl, "Material Group

meins type mara-meins, "Base Unit of Measure

mfrnr type mara-mfrnr, "Number of a Manufacturer

mfrpn type mara-mfrpn, "Manufacturer Part Number

maktx type makt-maktx, "Material Description (Short)

plifz type marc-plifz, "Planned Delivery Time in Days

po_text like tline-tdline, "Purchase Order Text int_notes like tline-tdline, "Internal Comment

end of i_matl_details.

CALL FUNCTION 'CHANGEDOCUMENT_READ_HEADERS'

EXPORTING

DATE_OF_CHANGE = p_b_date

OBJECTCLASS = 'MATERIAL'

TIME_OF_CHANGE = p_b_time

DATE_UNTIL = p_e_date

TIME_UNTIL = p_e_time

TABLES

I_CDHDR = i_pt_cdhdr

EXCEPTIONS

NO_POSITION_FOUND = 1

WRONG_ACCESS_TO_ARCHIVE = 2

TIME_ZONE_CONVERSION_ERROR = 3

OTHERS = 4.

select mara_{matnr mara}bstme mara_{vpreh mara}matkl mara_{meins mara}mfrnr mara~mfrpn

makt_{maktx marc}plifz

from mara

join marc on marc_{matnr = mara}matnr

join makt on makt_{matnr = mara}matnr

and spras = sy-langu

into table i_matl_details

where mara~mtart in s_matl.

Currently, this code is selecting material details based on material type.

I am using the change pointer function module to read changes only so that only deltas are loaded. Therefore, the selection code above would be wrong because even though I am reading changes, I am not selecting from it. I need to select all changed material from internal table i_pt_cdhdr.

How can I use the 'for all entries' statement so that selection is based on material type and material is selected only from the change pointer internal table in the function module?

Thanks in advance.

Regards,

Fred.

Re: Selecting from change pointer internal table...

Former Member — Wed, 12 Apr 2006 15:23:02 GMT

hi Fred,

check this out ....

SELECT likp_{vbeln likp}lifex likp_{bldat likp}wadat likp_{wadat_ist likp}kodat likp~lfart

likp_{kunnr likp}vstel lips_{posnv lips}lfimg lips_{vrkme lips}lgmng lips~meins

lips_{werks lips}lgort lips_{charg lips}vbelv lips_{posnr lips}matnr

lips_{vbeln LIPS}VGBEL LIPS_{VGPOS vbup}kosta vbup_{wbsta vbup}posnr vbup~vbeln

VBAK_{IHREZ VBAK}VBELN VBAP~VBELN

INTO CORRESPONDING FIELDS OF TABLE it_itab

FROM ( likp

INNER JOIN lips

ON lips_{vbeln = likp}vbeln

INNER JOIN vbup

ON vbup_{posnr = lips}posnr

and VBUP_{VBELN = LIPS}VBELN )

left outer join VBAK
on VBAK_{VBELN = LIPS}VGBEL
inner join VBAP
on VBAP_{VBELN = VBAK}VBELN )

WHERE likp~vbeln IN so_vbeln

AND likp~lifex IN so_lifex

AND likp~lfart IN so_lfart

AND likp~kunnr IN so_kunnr

AND likp~vstel IN so_vstel

AND likp~bldat IN so_bldat

AND likp~wadat_ist IN so_wadat

AND vbup~kosta IN so_kosta

AND vbup~wbsta IN so_wbsta

AND LIPS~LFIMG NE 0.

SELECT VBELN IHREZ INTO TABLE T_VBAK

FROM VBAK

FOR ALL ENTRIES IN IT_ITAB

WHERE VBELN = IT_ITAB-VGBEL.

APPEND T_VBAK.
ENDSELECT.

SELECT VBELN MATNR POSNR INTO TABLE T_VBAP

FROM VBAP

FOR ALL ENTRIES IN IT_ITAB

WHERE VBELN = IT_ITAB-VGBEL AND

MATNR = IT_ITAB-MATNR AND

POSNR = IT_ITAB-VGPOS.

APPEND T_VBAP.
ENDSELECT.

SELECT VBELV VBELN VBTYP_N INTO TABLE T_VBFA

FROM VBFA

FOR ALL ENTRIES IN IT_ITAB

WHERE VBELV = IT_ITAB-VBELN AND

VBTYP_N = 'M' .

SELECT KUNNR NAME1 INTO TABLE T_KNA1

FROM KNA1

FOR ALL ENTRIES IN IT_ITAB

WHERE KUNNR = IT_ITAB-KUNNR.

APPEND T_KNA1.
ENDSELECT.

SELECT MATNR MAKTX INTO TABLE T_MAKT

FROM MAKT

FOR ALL ENTRIES IN IT_ITAB

WHERE MATNR = IT_ITAB-MATNR.

APPEND T_MAKT.
ENDSELECT.

hope it helps,

Regards,

Santosh

Re: Selecting from change pointer internal table...

Former Member — Wed, 12 Apr 2006 15:26:10 GMT

Change your select statement as follows:

select mara_{matnr mara}bstme mara_{vpreh mara}matkl mara_{meins mara}mfrnr mara~mfrpn

makt_{maktx marc}plifz

from mara

join marc on marc_{matnr = mara}matnr

join makt on makt_{matnr = mara}matnr

and spras = sy-langu

for all entries in i_pt_cdhdr

into table i_matl_details

where mara~mtart in s_matl

and mara-matnr = i_pt_cdhdr-OBJECTCLAS. "I think this has the matnr value.

Regards,

Ravi

Re: Selecting from change pointer internal table...

Former Member — Wed, 12 Apr 2006 15:37:55 GMT

Take a look at this.


TABLES: mara.

PARAMETERS: p_b_date LIKE sy-datum,
            p_b_time LIKE sy-uzeit,
            p_e_date LIKE sy-datum,
            p_e_time LIKE sy-uzeit.

SELECT-OPTIONS: s_matl FOR mara-matkl.

DATA: BEGIN OF i_matl_details OCCURS 0,
        matnr   TYPE mara-matnr, "Material Number
        bstme   TYPE mara-bstme, "Purchase Order Unit of Measure
        vpreh   TYPE mara-vpreh, "Comparison Price Unit
        matkl   TYPE mara-matkl, "Material Group
        meins   TYPE mara-meins, "Base Unit of Measure
        mfrnr   TYPE mara-mfrnr, "Number of a Manufacturer
        mfrpn   TYPE mara-mfrpn, "Manufacturer Part Number
        maktx   TYPE makt-maktx, "Material Description (Short)
        plifz   TYPE marc-plifz, "Planned Delivery Time in Days
        po_text LIKE tline-tdline, "Purchase Order Text int_notes like tline-tdline, "Internal Comment
END OF i_matl_details.

DATA: BEGIN OF i_updtd_materials OCCURS 0,
        matnr LIKE mara-matnr.
DATA: END OF i_updtd_materials.

DATA: BEGIN OF i_pt_cdhdr OCCURS 0.
        INCLUDE STRUCTURE  cdhdr.
DATA: END OF i_pt_cdhdr.

START-OF-SELECTION.

  CALL FUNCTION 'CHANGEDOCUMENT_READ_HEADERS'
    EXPORTING
      date_of_change             = p_b_date
      objectclass                = 'MATERIAL'
      time_of_change             = p_b_time
      date_until                 = p_e_date
      time_until                 = p_e_time
    TABLES
      i_cdhdr                    = i_pt_cdhdr
    EXCEPTIONS
      no_position_found          = 1
      wrong_access_to_archive    = 2
      time_zone_conversion_error = 3
      OTHERS                     = 4.

  SORT i_pt_cdhdr BY objectid.

  DELETE ADJACENT DUPLICATES FROM i_pt_cdhdr COMPARING objectid.

  LOOP AT i_pt_cdhdr.
    MOVE i_pt_cdhdr-objectid TO i_updtd_materials-matnr.
    APPEND i_updtd_materials.
    CLEAR i_updtd_materials.
  ENDLOOP.

  SELECT mara~matnr mara~bstme mara~vpreh
         mara~matkl mara~meins mara~mfrnr
         mara~mfrpn makt~maktx marc~plifz
    FROM mara INNER JOIN marc ON marc~matnr = mara~matnr
              INNER JOIN makt ON makt~matnr = mara~matnr
    INTO TABLE i_matl_details FOR ALL ENTRIES IN i_updtd_materials
   WHERE mara~matnr EQ i_updtd_materials-matnr
     AND mara~mtart IN s_matl
     AND makt~spras EQ sy-langu.

Re: Selecting from change pointer internal table...

Former Member — Wed, 12 Apr 2006 15:38:33 GMT

Hi Ravi,

I changed the select statement according to what you told me, but I am getting the following error:

Field list without INTO clause is not allowed . allowed. . allowed. allowed. . allowed. allowed. allowed. allowed. allowed.

I don't know what its referring to?

Regards,

Fred.

Re: Selecting from change pointer internal table...

Former Member — Wed, 12 Apr 2006 15:47:16 GMT

Basically, in Ravi's code, the 'for all entries' line should be after the 'into' line.

But you still cannot use it that way, because, it will give you a syntax error. The length and type of i_pt_cdhdr-objectid and mara-matnr should be the same. That is the reason why I used another internal table in my code. Also, there can be several changes within a period of time for the same material. So there will be several records in i_pt_cdhdr that are essentially for the same material. But you don't need to know how many times it changed, all you want to know is, if it has changed and then download it. So you need to eliminate all the duplicates before you do the SELECT to be efficient.

Re: Selecting from change pointer internal table...

Former Member — Wed, 12 Apr 2006 17:08:13 GMT

Hi Srinivas,

The logic you posted works perfect. Thanks for the code and for the explanation.

Appreciate it.

Regards,

Fred.

Re: Selecting from change pointer internal table...

Former Member — Wed, 12 Apr 2006 17:12:15 GMT

Glad it helped.