Lookup between 2 DSO's

[Former Member]

Hello all,

Please assist me.

I have the following scenario:

1- DSO(based on EKBE table)

Key fields: PO number, PO Item, Mat Doc num, Mat Doc year,Mat Doc item, ZEKKN, VGARE.

2- DSO(based on 0FI_AP_4)

Key fields:Company code, Fiscal year/period, Accounting document number, Account document posting line, Fiscal year variant ,Due date item number.

I have enhanced field AWKEY in 0FI_AP_4 extractor from BKPF.

AWKEY = Mat Doc + Mat DocYear.

Now I have Mat Doc + Mat Doc Year in DSO(0FI_AP_4).

I want to do a lookup on DSO(EKBE) and bring PO and PO Item and DMBTR and WBTR fields . My Driver would be 2nd DSO.

Can we write a start routine to get fields from 1st DSO and put in 2nd DSO. I would like to see Amounts paid(Vendor) for a PO in single report.

Appreciate if anyon can give a sample code.

Thanks,

Praveen

[Former Member · Accepted solution]

Hello,

lets suppose the first DSO name is ODS1.

it might look like that

TABLES: /BIC/AODS100. *DSO active data table

LOOP AT DATA_PACKAGE.

SELECT SINGLE DOC_NUM, ... INTO ( DATA_PACKAGE-DOC_NUM, ... ) FROM /BIC/AODS100

WHERE material = data_package-material and mat_year = data_package-mat_year.

MODIFY DATA_PACKAGE.

ENDLOOP.

[Former Member · Accepted solution]

Hi Ondrej,

Thank you.

I tried this:

data : lv_ebeln type /BI0/OIOI_EBELN,

lv_wrbtr type /BI0/OIOI_WRBTR,

lv_dmbtr type /BIC/OIZVALUELC.

Loop at SOURCE_PACKAGE assigning <SOURCE_FIELDS>.

Clear: lv_ebeln, lv_wrbtr, lv_dmbtr.

select OI_EBELN sum( OI_WRBTR ) sum( /BIC/ZVALUELC ) into

(lv_ebeln, lv_wrbtr, lv_dmbtr)

from /BIC/AZBW_EKBE00

where MAT_DOC = <SOURCE_FIELDS>-MAT_DOC

and DOC_YEAR = <SOURCE_FIELDS>-DOC_YEAR

group by OI_EBELN.

endselect.

if sy-subrc = 0.

<SOURCE_FIELDS>-OI_EBELN = lv_ebeln.

<SOURCE_FIELDS>-OI_WRBTR = lv_wrbtr.

<SOURCE_FIELDS>-/BIC/ZVALUELC = lv_dmbtr.

endif.

endloop.

I dont why it is not loading, it stays in yellow status.

[Former Member · Accepted solution]

Hi,

you have written the coding false. pls put

if sy-subrc = 0.

<SOURCE_FIELDS>-OI_EBELN = lv_ebeln.

<SOURCE_FIELDS>-OI_WRBTR = lv_wrbtr.

<SOURCE_FIELDS>-/BIC/ZVALUELC = lv_dmbtr.

endif.

between SELECT and ENDSELECT.

[Former Member · Accepted solution]

Hi,

pls put the following codes

if sy-subrc = 0.

<SOURCE_FIELDS>-OI_EBELN = lv_ebeln.

<SOURCE_FIELDS>-OI_WRBTR = lv_wrbtr.

<SOURCE_FIELDS>-/BIC/ZVALUELC = lv_dmbtr.

endif.

between SELECT and ENDSELECT.

[Former Member · Accepted solution]

Hi Gang,

Thank you very much for the reply. I will try.

Thanks,

Praveen

[Former Member · Accepted solution]

Hi Gang,

I tried but same issue. Transferred records are coming but no records are getting added.

Praveen

[Former Member · Accepted solution]

Hello,

I think you have to add MODIFY statement.

Otherwise data are not stored.

BR Ondrej

[Former Member · Accepted solution]

Hi,

what is the problem?

e.g . No data records were tranferred in Target cube or these data fields were not filled? which one?

[Former Member · Accepted solution]

TRY THIS:

Loop at SOURCE_PACKAGE assigning <SOURCE_FIELDS>.

Clear: lv_ebeln, lv_wrbtr, lv_dmbtr.

select OI_EBELN sum( OI_WRBTR ) sum( /BIC/ZVALUELC ) into

(lv_ebeln, lv_wrbtr, lv_dmbtr)

from /BIC/AZBW_EKBE00

where MAT_DOC = <SOURCE_FIELDS>-MAT_DOC

and DOC_YEAR = <SOURCE_FIELDS>-DOC_YEAR

group by OI_EBELN.

if sy-subrc = 0.

<SOURCE_FIELDS>-OI_EBELN = lv_ebeln.

<SOURCE_FIELDS>-OI_WRBTR = lv_wrbtr.

<SOURCE_FIELDS>-/BIC/ZVALUELC = lv_dmbtr.

endif.

pls delete the ENDSELECT.

endloop.

Edited by: gang qin on Mar 30, 2009 9:24 PM

[Former Member · Accepted solution]

Hi Gang,

It throws error.

I am thinking of using infoset based 2 DSO..

Tks,

Praveen Tati

[Former Member · Accepted solution]

which error throwed? pls let me know

try the following codes:

SELECT

.

.

.

.

.

.

Exit

endselect.

[Former Member · Accepted solution]

This is the error I am getting:

Inconsistent input parameter (parameter: <unknown>, value <unknown>

Error while extracting from source 0FIAP_O03 (type DataStore)

[Former Member · Accepted solution]

this kind of error i have faced. It happened wenn you debug in DTP.

pls try to direct execute without debugging

Edited by: gang qin on Mar 30, 2009 10:11 PM

[Former Member · Accepted solution]

Thanks for the help Gang.

I am trying to load with:

Loop at SOURCE_PACKAGE assigning <SOURCE_FIELDS>.

Clear: lv_ebeln, lv_wrbtr, lv_dmbtr.

select OI_EBELN sum( OI_WRBTR ) sum( /BIC/ZVALUELC ) into

(lv_ebeln, lv_wrbtr, lv_dmbtr)

from /BIC/AZBW_EKBE00

where MAT_DOC = <SOURCE_FIELDS>-MAT_DOC

and DOC_YEAR = <SOURCE_FIELDS>-DOC_YEAR

group by OI_EBELN.

if sy-subrc = 0.

<SOURCE_FIELDS>-OI_EBELN = lv_ebeln.

<SOURCE_FIELDS>-OI_WRBTR = lv_wrbtr.

<SOURCE_FIELDS>-/BIC/ZVALUELC = lv_dmbtr.

endif.

exit.

endselect.

endloop.

It is getting stuck .. at start routine.

[Former Member · Accepted solution]

what does it mean -- it got stuck?

[Former Member · Accepted solution]

I started the load. Under details, i see

Transformation start

start routine.

and then nothing happens.

Thanks

[Former Member · Accepted solution]

It is no possible. it means that no data record were transferred in Target? How many data records are there in source system?

[Former Member · Accepted solution]

Yes, transferred records but nothing is added.

There some records in thousands in source DSO.

[Former Member · Accepted solution]

Hi Gang,

I tried with some records under filter option in DTP and it is loading fine.

Are you sure that

if sy-subrc = 0.

<SOURCE_FIELDS>-OI_EBELN = lv_ebeln.

<SOURCE_FIELDS>-OI_WRBTR = lv_wrbtr.

<SOURCE_FIELDS>-/BIC/ZVALUELC = lv_dmbtr.

endif.

should be used between select and endselect.

-

---

Loop at SOURCE_PACKAGE assigning <SOURCE_FIELDS>.

Clear: lv_ebeln, lv_wrbtr, lv_dmbtr.

select OI_EBELN sum( OI_WRBTR ) sum( /BIC/ZVALUELC ) into

(lv_ebeln, lv_wrbtr, lv_dmbtr)

from /BIC/AZBW_EKBE00

where MAT_DOC = <SOURCE_FIELDS>-MAT_DOC

and DOC_YEAR = <SOURCE_FIELDS>-DOC_YEAR

group by OI_EBELN.

if sy-subrc = 0.

<SOURCE_FIELDS>-OI_EBELN = lv_ebeln.

<SOURCE_FIELDS>-OI_WRBTR = lv_wrbtr.

<SOURCE_FIELDS>-/BIC/ZVALUELC = lv_dmbtr.

endif.

endselect.

-

---

Thank you very much

[Former Member · Accepted solution]

Praveen,

--> Instead of coding why cant you create one more ods and load data from both ODS's required fields ...? by defining proper key's...??

--> Due to SELECT statement for every record creating performance issues. Instead of SELECT, create an internal table and use READ for internal table.

Srini

[Former Member · Accepted solution]

Hi Neelam,

I am using 0FI_AP_4 since I need to show the open and closed items.

I also want to show the PO number which are open and closed.

I have PO number in one DSO and 0FI_AP_4 doesnt have PO number.

My only option would be doing a lookup or creating infoset on 2 DSO's.

I wanted to do a lookup since infoset query performance is not good.

Hello Gang, Thank you very much for ur help.

The code works now. I think there was problem with key figure units. I have also slightly modifed the code. Here it is:

data : lv_ebeln type /BI0/OIOI_EBELN,

lv_wrbtr type /BIC/OIZWRBTR,

lv_dmbtr type /BIC/OIZVALUELC.

Loop at SOURCE_PACKAGE assigning <SOURCE_FIELDS>.

Clear: lv_ebeln, lv_wrbtr, lv_dmbtr.

select single OI_EBELN /BIC/ZWRBTR /BIC/ZVALUELC

into (lv_ebeln, lv_wrbtr, lv_dmbtr)

from /BIC/AZBWEKBE00

where MAT_DOC = <SOURCE_FIELDS>-MAT_DOC.

if sy-subrc = 0.

<SOURCE_FIELDS>-OI_EBELN = lv_ebeln.

<SOURCE_FIELDS>-/BIC/ZWRBTR = lv_wrbtr.

<SOURCE_FIELDS>-/BIC/ZVALUELC = lv_dmbtr.

endif.

endloop.

[Former Member]

Thanks..

[Former Member]

i am happy to see that your problem has been solved