Association and Navigation not working for code based OData service

[marcin_czarny_nype]

Hi Experts,

My problem is that, when I'm using

http://<destination>:port>/sap/opu/odata/sap/NameOfService/Resources('000001')/?$expand=NameOfNavigationProperty

instead of getting details of resource with ID 000001 I'm getting complete list resources and all details.

I created my OData services in SEGW using Import and DDIC Structure. First Entity Type it's Zres and it was created based on Zres table which looks like:

Second Entity Type it's Zrbook and it was created based on Zrbook table which looks like:

Afterwards I created Entity Sets: 'Resources' based on Zres entity type and Bookings based on Zrbook entity type and I implemented method RESOURCES_GET_ENTITYSET, RESOURCES_GET_ENTITY,  BOOKINGS_GET_ENTITYSET, BOOKINGS_GET_ENTITY.

An in addition I would like to show my implemetation of this methods:

method RESOURCES_GET_ENTITYSET.

   DATA: lt_resource TYPE TABLE OF zres,
         ls_resource LIKE LINE OF lt_resource,
         ls_entity   LIKE LINE OF et_entityset.

   SELECT * FROM zres INTO TABLE lt_resource.


   LOOP AT lt_resource INTO  ls_resource.

     ls_entity-resid    = ls_resource-resid.
     ls_entity-type    = ls_resource-type.
     ls_entity-name    = ls_resource-name.
     ls_entity-descr    = ls_resource-descr.

     APPEND ls_entity TO et_entityset.
   ENDLOOP.

endmethod.

method RESOURCES_GET_ENTITY.

 DATA:  ls_key_tab   TYPE /iwbep/s_mgw_name_value_pair,
         lv_resid   TYPE zres-resid,
         ls_resource TYPE  zres.


 READ TABLE it_key_tab WITH KEY name = 'Resid' INTO ls_key_tab.

   lv_resid = ls_key_tab-value.


 SELECT SINGLE * FROM zres INTO ls_resource WHERE resid = lv_resid.

   IF sy-subrc = 0.

     er_entity-resid = ls_resource-resid.
     er_entity-type  = ls_resource-type.
     er_entity-name     = ls_resource-name.
     er_entity-descr     = ls_resource-descr.

    ENDIF.

endmethod.

method BOOKINGS_GET_ENTITYSET.

   DATA: lt_booking TYPE TABLE OF zrbook,
         ls_booking LIKE LINE OF lt_booking,
         ls_entity   LIKE LINE OF et_entityset. 

   SELECT * FROM zrbook INTO TABLE lt_booking.


   LOOP AT lt_booking INTO  ls_booking.

     ls_entity-resid  = ls_booking-resid.
     ls_entity-bookid    = ls_booking-bookid.
     ls_entity-bdate = ls_booking-bdate.
     ls_entity-stime  = ls_booking-stime.
     ls_entity-etime     = ls_booking-etime.
     ls_entity-booker     = ls_booking-booker.

     APPEND ls_entity TO et_entityset.
   ENDLOOP.

endmethod.

method BOOKINGS_GET_ENTITY.

 DATA:  ls_key_tab   TYPE /iwbep/s_mgw_name_value_pair,
         lv_bookid   TYPE zrbook-bookid,
         ls_booking TYPE  zrbook. 

 READ TABLE it_key_tab WITH KEY name = 'Bookid' INTO ls_key_tab.

   lv_bookid = ls_key_tab-value.

 SELECT SINGLE * FROM zrbook INTO ls_booking WHERE bookid = lv_bookid.
 IF sy-subrc = 0. 

     er_entity-resid  = ls_booking-resid.
     er_entity-bookid    = ls_booking-bookid.
     er_entity-bdate = ls_booking-bdate.
     er_entity-stime  = ls_booking-stime.
     er_entity-etime     = ls_booking-etime.
     er_entity-booker     = ls_booking-booker.

 ENDIF.

   endmethod.

My metadata file looks like this:

I have based my configuration on SALESORDERXX service from demo Gateway System where 'Resources' corresponds to the 'SOHeader' from known SCN example and 'Bookings' corresponds to the 'SOItem' from the same example. Otherwise, you can imagine that 'Resources' corresponds 'SalesOrder' and 'Bookings' corresponds to 'SalesOrderItem' from this example:

So, I have associations between Resources and Bookings 1:N. 1 resource can has a lot of bookings (one room has a lot of bookings, one car has a lot of bookings, etc.)

So, I expected that using

http://<destination>:port>/sap/opu/odata/sap/NameOfService/Resources('000001')/?$expand=NameOfNavigationProperty I get bookings for resource with ID=000001.

Unfortunetly, in results I received all resources and all bookings:

Resid 000001 and bookings with ID 00000001

Resid 000001 and bookings with ID 00000002

Resid 000002 and bookings with ID 00000005

Resid 000003 and bookings with ID 00000006

Resid 000004 and bookings with ID 00000007

Resid 000004 and bookings with ID 00000008

I remind you that I would receive only bookings with ID 00000001 and 00000002 for resources with ID 000001, only bookings with ID 00000005 for resource with ID 000002, etc, etc.

Can you explain to me what am I doing wrong?

Thanks in advance!

Regards

Marcin

[AshwinDutt · Accepted solution]

Hello Marcin,

Inside the method BOOKINGS_GET_ENTITYSET you have not written code to read the IT_KEY_TAB.

Write code to read the key i.e., Property 'Resid' has to be read and should be passed it to your logic to get Bookings based on Resid.

1. READ TABLE it_key_tab WITH KEY name = 'Resid' INTO ls_key_tab.
2. lv_resid = ls_key_tab-value. 

After changes Use the below URL->

/Resources('000001')?$expand=ResBook

Regards,

Ashwin

[marcin_czarny_nype]

Thanks Ashwin, it's solved my problem!

Using your advice I modified my 'BOOKINGS GET_ENTITYSET' method and it works as I expect.

method BOOKINGS_GET_ENTITYSET.

   DATA: lt_booking TYPE TABLE OF zrbook,
         ls_booking LIKE LINE OF lt_booking,
         ls_entity   LIKE LINE OF et_entityset,
         ls_key_tab   TYPE /iwbep/s_mgw_name_value_pair,
         lv_resid   TYPE zres-resid.

READ TABLE it_key_tab WITH KEY name = 'Resid' INTO ls_key_tab.
   lv_resid = ls_key_tab-value.

   SELECT * FROM zrbook INTO TABLE lt_booking.

   LOOP AT lt_booking INTO  ls_booking where resid = lv_resid.

     ls_entity-resid  = ls_booking-resid.
     ls_entity-bookid    = ls_booking-bookid.
     ls_entity-bdate = ls_booking-bdate.
     ls_entity-stime  = ls_booking-stime.
     ls_entity-etime     = ls_booking-etime.
     ls_entity-booker     = ls_booking-booker.

     APPEND ls_entity TO et_entityset.
   ENDLOOP.
endmethod.

Regards

Marcin