topic Re: Data extract in Application Development and Automation Discussions

Data extract

Former Member — Thu, 28 Dec 2006 20:33:12 GMT

Hello friends,

In my selection screen I have BUKRS, HKONT, BUDAT all from BSIS table and I need to access MSEG table.

With this data I also accessed BSEG and EKKO. From EKKO i get the MATNR but using MATNR to access MSEG would not be a good apporach.

Can anyone explain me the flow for this?

Thanks all,

Shreekant.

Re: Data extract

Former Member — Thu, 28 Dec 2006 20:40:55 GMT

Hi,

What kind of accounting document is this..Is it MSEG related accounting document or invoice related accounting document..

Please let me know ..

Thanks,

Naren

Re: Data extract

Former Member — Thu, 28 Dec 2006 20:44:33 GMT

It is releated to MSEG releated Accounting Document. Also In my question Isaid I can get MATNR from EKKO, but I dont see MATNR in EKKO.

Thanks,

Shreekant.

Re: Data extract

Former Member — Thu, 28 Dec 2006 20:49:21 GMT

HI Shreekanth,

If you have access to Purchase Order #, you can query MSEG based on the PO number. MSEG-EBELN( Purchase Order #).

When ever you post of Goods Receipt for a Purchase Order, it creates a Material Document( MSEG..) + an Accounting document ( BKPF..).

What does your Report do?

Regards,

-Venkat.

Re: Data extract

Former Member — Thu, 28 Dec 2006 20:51:58 GMT

Hi Shreekant,

You can get MATNR from EKPO but not from EKKO.

EKKO is the header data about PO,so it doesn't have information about materials.

EKPO will have materials data for a PO.

Thanks,

Vinay

Re: Data extract

Former Member — Thu, 28 Dec 2006 20:53:33 GMT

Hi,

Ok..Here are the steps..

1) based on the selection-screen BUKRS, HKONT, BUDAT get the data from BSIS and store it in the internal table T_BSIS.

2) For the corresponding accounting document number, bukrs and company code..Get the data (AWKEY & other data) from BKPF using FOR ALL ENTRIES..Store the results in the internal table T_BKPF.

3) Process the internal table T_BKPF.

Get the material document and year from T_BKPF-AWKEY.

And store the material document and year in the internal table T_AWKEY.

endprocess.

4) Using the material documents and year available in the internal table T_AWKEY get the material document details from the table MSEG....Store the results in the internal table T_MSEG.

5) Using the ebeln (PO) available in the internal table T_MSEG..Get the PO details from the table EKKO & EKPO..Store the results in the internal table T_PO.

Now you have the data in the following internal tables..

T_BKPF - Accounting document.

T_MSEG - Material documents

T_PO - PO details.

Hope this helps..

Thanks,

Naren

Re: Data extract

Former Member — Thu, 28 Dec 2006 20:55:35 GMT

Thanks Venkat,

Accessing MSEG only on PO # would be time consuming, I mean performance. DO you think i can tie up some more fields from BSIS or EKKo or BSEg with MSEG.

How would BKPF going to help me.

Shreekant

Re: Data extract

Former Member — Thu, 28 Dec 2006 20:56:23 GMT

Hi Shreekant,

Please check my reply...

Thanks,

Naren

Re: Data extract

Former Member — Thu, 28 Dec 2006 21:05:09 GMT

Thanks Naren for the clear explanation.

I have a question, Why did you say to access BKPF-AWKEY, I guess the first 10 char of AWKEY is same as the BKPF-BELNR and using BKPF-BELNR can i access MSEG, and GJAHR in BKPF wouldnt give the year.

Let me know am I right or wrong.

So i will be tying both this fields from BKPF to MSEG-MBLNR and MSEG-MJAHR.

Is this correct.

Again thanks for all the help,

Shreekant

Re: Data extract

Former Member — Thu, 28 Dec 2006 21:11:57 GMT

Hi,

Generally ..

If it is MSEG related accounting document...

BKPF-AWKEY = MKPF-MBLNR + MKPF-MJAHR.

So to get the material documents from the accounting documents..Split the AWKEY to get the material document and year..

Ok..I will explain you in detail with the code..

DATA: BEGIN OF T_AWKEY OCCURS 0,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

END OF T_AWKEY.

LOOP AT T_BKPF.

T_AWKEY = T_BKPF-AWKEY.

APPEND T_AWKEY.

CLEAR T_AWKEY.

ENDLOOP.

SELECT MBLNR MJAHR EBELN EBELP

FROM MSEG

INTO TABLE T_MSEG

FOR ALL ENTRIES IN T_AWKEY

WHERE MBLNR = T_AWKEY-MBLNR

AND MJAHR = T_AWKEY-MJAHR.

Hope this helps..

Please make sure to reward points for helpful answers..

Thanks,

Naren

Re: Data extract

Former Member — Thu, 28 Dec 2006 21:15:33 GMT

Thanks Naren,

It is of great help for me.

I understood the Logic and would be of very good help to me.

Thanks again.

Re: Data extract

Former Member — Fri, 29 Dec 2006 20:07:59 GMT

Naren,

Ihave written the code as you explained but when I execute this program m IT_MSEG returns with no value. However IT_BKPF has data in it.

Attached is the code,

IF NOT IT_BSIS_01[] IS INITIAL.

SELECT BUKRS BELNR GJAHR BUDAT AWKEY BLART

FROM BKPF

INTO TABLE IT_BKPF

FOR ALL ENTRIES IN IT_BSIS_01

WHERE BUKRS = IT_BSIS_01-BUKRS

AND BELNR = IT_BSIS_01-BELNR

AND GJAHR = IT_BSIS_01-GJAHR.

LOOP AT IT_BKPF.

IT_BKPF-AWKEY1 = IT_BKPF-AWKEY+0(10).

IT_BKPF-AWKEY2 = IT_BKPF-AWKEY+10(4).

MODIFY IT_BKPF TRANSPORTING AWKEY1 AWKEY2

WHERE BELNR = IT_BKPF-BELNR

AND GJAHR = IT_BKPF-GJAHR.

ENDLOOP.

SELECT MBLNR MJAHR ZEILE MATNR WERKS

CHARG LIFNR KUNNR DMBTR

FROM MSEG

INTO TABLE IT_MSEG

FOR ALL ENTRIES IN IT_BKPF

WHERE MBLNR = IT_BKPF-AWKEY1

AND MJAHR = IT_BKPF-AWKEY2.

ENDIF.

Naren Let me what is wrong.

Shreekant

Re: Data extract

Former Member — Fri, 29 Dec 2006 20:10:57 GMT

Hi,

Try this..

DATA: V_INDEX TYPE SYTABIX.

LOOP AT IT_BKPF.

V_INDEX = SY-TABIX.

IT_BKPF-AWKEY1 = IT_BKPF-AWKEY+0(10).

IT_BKPF-AWKEY2 = IT_BKPF-AWKEY+10(4).

MODIFY IT_BKPF INDEX V_INDEX

TRANSPORTING AWKEY1 AWKEY2.

ENDLOOP.

Also paste your code of your IT_BKPF internal table declaration..

Thanks,

Naren

Re: Data extract

Former Member — Fri, 29 Dec 2006 20:14:44 GMT

I tried this before and in the Debug mode i see values for both

IT_BKPF-AWKEY1

IT_BKPF-AWKEY2.

Even at the Endloop the sy-subrc = 0.

But the select statement after endloop fails.

I looked into the database in SE16 for Mseg values using values in BKPF and it retirned no values.

Do you think iy could be data problem or ?

Thanks,

Shreekant

Re: Data extract

Former Member — Fri, 29 Dec 2006 20:15:28 GMT

the complete code,

FORM F1000_GET_DATA .

SELECT *

FROM ZZMM0010

INTO TABLE IT_ZZMM0010.

SELECT HKONT BELNR XBLNR GJAHR BLART DMBTR

SGTXT ZUONR BUKRS BUDAT SHKZG BUZEI

FROM BSIS

INTO TABLE IT_BSIS

WHERE BUKRS EQ P_BUKRS

AND HKONT IN S_HKONT

AND BUDAT IN S_BUDAT.

IT_BSIS_01[] = IT_BSIS[].

DELETE IT_BSIS_01 WHERE BLART = 'RV'

OR BLART = 'SA'.

IF NOT IT_BSIS_01[] IS INITIAL.

SELECT BELNR BUZEI GJAHR BUKRS HKONT ZUONR

SGTXT KUNNR LIFNR MATNR MENGE

MEINS ERFME ERFMG EBELN

FROM BSEG

INTO TABLE IT_BSEG

FOR ALL ENTRIES IN IT_BSIS_01

WHERE BELNR = IT_BSIS_01-BELNR

AND BUKRS = P_BUKRS.

SELECT EBELN LIFNR

FROM EKKO

INTO TABLE IT_EKKO

FOR ALL ENTRIES IN IT_BSEG

WHERE EBELN = IT_BSEG-EBELN.

SELECT BUKRS BELNR GJAHR BUDAT AWKEY BLART

FROM BKPF

INTO TABLE IT_BKPF

FOR ALL ENTRIES IN IT_BSIS_01

WHERE BUKRS = IT_BSIS_01-BUKRS

AND BELNR = IT_BSIS_01-BELNR

AND GJAHR = IT_BSIS_01-GJAHR.

LOOP AT IT_BKPF.

IT_BKPF-AWKEY1 = IT_BKPF-AWKEY+0(10).

IT_BKPF-AWKEY2 = IT_BKPF-AWKEY+10(4).

MODIFY IT_BKPF TRANSPORTING AWKEY1 AWKEY2

WHERE BELNR = IT_BKPF-BELNR

AND GJAHR = IT_BKPF-GJAHR.

ENDLOOP.

SELECT MBLNR MJAHR ZEILE MATNR WERKS

CHARG LIFNR KUNNR DMBTR

FROM MSEG

INTO TABLE IT_MSEG

FOR ALL ENTRIES IN IT_BKPF

WHERE MBLNR = IT_BKPF-AWKEY1

AND MJAHR = IT_BKPF-AWKEY2.

ENDIF.

ENDFORM. " F1000_GET_DATA

Re: Data extract

Former Member — Fri, 29 Dec 2006 20:22:34 GMT

Hi,

Check in the internal table IT_BKPF if the values are populated for the columns AWKEY1 & AWKEY2...Before the MSEG select..

SELECT MBLNR MJAHR ZEILE MATNR WERKS

CHARG LIFNR KUNNR DMBTR

FROM MSEG

INTO TABLE IT_MSEG

FOR ALL ENTRIES IN IT_BKPF

WHERE MBLNR = IT_BKPF-AWKEY1

AND MJAHR = IT_BKPF-AWKEY2.

Thanks,

Naren

Re: Data extract

Former Member — Fri, 29 Dec 2006 20:26:22 GMT

Yes they do populate before the MSEG select Statement. Also I have pasted the complete code.

Shreekant.

Re: Data extract

Former Member — Fri, 29 Dec 2006 20:33:59 GMT

Hi,

The code looks good..Except for the modify part..Please check the changes marked in bold....

Also are you sure you were able to get the record in SE16 in the table MSEG for the corresponding AWKEY1 & AWKEY2 values in the internal table IT_BKPF..

FORM F1000_GET_DATA .

SELECT *

FROM ZZMM0010

INTO TABLE IT_ZZMM0010.

SELECT HKONT BELNR XBLNR GJAHR BLART DMBTR

SGTXT ZUONR BUKRS BUDAT SHKZG BUZEI

FROM BSIS

INTO TABLE IT_BSIS

WHERE BUKRS EQ P_BUKRS

AND HKONT IN S_HKONT

AND BUDAT IN S_BUDAT.

IT_BSIS_01[] = IT_BSIS[].

DELETE IT_BSIS_01 WHERE BLART = 'RV'

OR BLART = 'SA'.

IF NOT IT_BSIS_01[] IS INITIAL.

SELECT BELNR BUZEI GJAHR BUKRS HKONT ZUONR

SGTXT KUNNR LIFNR MATNR MENGE

MEINS ERFME ERFMG EBELN

FROM BSEG

INTO TABLE IT_BSEG

FOR ALL ENTRIES IN IT_BSIS_01

WHERE BELNR = IT_BSIS_01-BELNR

AND BUKRS = P_BUKRS.

SELECT EBELN LIFNR

FROM EKKO

INTO TABLE IT_EKKO

FOR ALL ENTRIES IN IT_BSEG

WHERE EBELN = IT_BSEG-EBELN.

SELECT BUKRS BELNR GJAHR BUDAT AWKEY BLART

FROM BKPF

INTO TABLE IT_BKPF

FOR ALL ENTRIES IN IT_BSIS_01

WHERE BUKRS = IT_BSIS_01-BUKRS

AND BELNR = IT_BSIS_01-BELNR

AND GJAHR = IT_BSIS_01-GJAHR.

LOOP AT IT_BKPF.

IT_BKPF-AWKEY1 = IT_BKPF-AWKEY+0(10).

IT_BKPF-AWKEY2 = IT_BKPF-AWKEY+10(4).

MODIFY IT_BKPF TRANSPORTING AWKEY1 AWKEY2

WHERE BELNR = IT_BKPF-BELNR

AND GJAHR = IT_BKPF-GJAHR

AND BUKRS = IT_BKPF-BUKRS.

ENDLOOP.

SELECT MBLNR MJAHR ZEILE MATNR WERKS

CHARG LIFNR KUNNR DMBTR

FROM MSEG

INTO TABLE IT_MSEG

FOR ALL ENTRIES IN IT_BKPF

WHERE MBLNR = IT_BKPF-AWKEY1

AND MJAHR = IT_BKPF-AWKEY2.

ENDIF.

ENDFORM. " F1000_GET_DATA

Thanks,

Naren

Re: Data extract

Former Member — Fri, 29 Dec 2006 21:25:55 GMT

Hi,

What kind of accounting document is this..Is it MSEG related accounting document or invoice related accounting document..

Please let me know ..

Thanks,

Naren

Naren I replied you it is releated to MSEG. However if it is releated to Invoice would it be a different approach?

Anyways Thanks a lot for all the effort you have taken and the Help..

Thanks,

Shreekant

Re: Data extract

Former Member — Fri, 29 Dec 2006 22:11:59 GMT

Hi,

If it is invoice related accounting document..To get the MSEG documents check the following..Use the internal table IT_EKKO instead of IT_BKPF to get the MSEG documents...Changes are marked in bold..

FORM F1000_GET_DATA .

SELECT *

FROM ZZMM0010

INTO TABLE IT_ZZMM0010.

SELECT HKONT BELNR XBLNR GJAHR BLART DMBTR

SGTXT ZUONR BUKRS BUDAT SHKZG BUZEI

FROM BSIS

INTO TABLE IT_BSIS

WHERE BUKRS EQ P_BUKRS

AND HKONT IN S_HKONT

AND BUDAT IN S_BUDAT.

IT_BSIS_01[] = IT_BSIS[].

DELETE IT_BSIS_01 WHERE BLART = 'RV'

OR BLART = 'SA'.

IF NOT IT_BSIS_01[] IS INITIAL.

SELECT BELNR BUZEI GJAHR BUKRS HKONT ZUONR

SGTXT KUNNR LIFNR MATNR MENGE

MEINS ERFME ERFMG EBELN

FROM BSEG

INTO TABLE IT_BSEG

FOR ALL ENTRIES IN IT_BSIS_01

WHERE BELNR = IT_BSIS_01-BELNR

AND BUKRS = P_BUKRS.

SELECT EBELN LIFNR

FROM EKKO

INTO TABLE IT_EKKO

FOR ALL ENTRIES IN IT_BSEG

WHERE EBELN = IT_BSEG-EBELN.

SELECT BUKRS BELNR GJAHR BUDAT AWKEY BLART

FROM BKPF

INTO TABLE IT_BKPF

FOR ALL ENTRIES IN IT_BSIS_01

WHERE BUKRS = IT_BSIS_01-BUKRS

AND BELNR = IT_BSIS_01-BELNR

AND GJAHR = IT_BSIS_01-GJAHR.

LOOP AT IT_BKPF.

IT_BKPF-AWKEY1 = IT_BKPF-AWKEY+0(10).

IT_BKPF-AWKEY2 = IT_BKPF-AWKEY+10(4).

MODIFY IT_BKPF TRANSPORTING AWKEY1 AWKEY2

WHERE BELNR = IT_BKPF-BELNR

AND GJAHR = IT_BKPF-GJAHR

AND BUKRS = IT_BKPF-BUKRS.

ENDLOOP.

SELECT MBLNR MJAHR ZEILE MATNR WERKS

CHARG LIFNR KUNNR DMBTR

FROM MSEG

INTO TABLE IT_MSEG

FOR ALL ENTRIES IN IT_EKKO

WHERE EBELN = IT_EKKO-EBELN.

ENDIF.

ENDFORM. " F1000_GET_DATA

Please make sure to reward points for helpful answers..

Thanks,

Nare