Age: 160 Joined: 04 Oct 2007 Posts: 1218 Location: Санкт-Петербург
Posted: Thu Jun 07, 2012 11:32 am Post subject: Link between EKPO and LIPS
Как известно, напрямую связать некторые таблицы нельзя, так как не совпадает размерность полей позиций.
В следующем способе предлагается делать join через промежуточную таблицу.
1) Создаем таблицу ZEXXS_POSNR_LINK с 4 полями разных размерностей (4, 5, 6 байт).
Code:
Field Key Data element
MANDT X MANDT
BUZEI X MBLPO
EBELP EBELP
POSNR POSNR
2) Заполяем ее значениями
Code:
REPORT z_vga_test.
DATA: lt_data TYPE TABLE OF zexxs_posnr_link WITH HEADER LINE.
MODIFY zexxs_posnr_link FROM TABLE lt_data.
COMMIT WORK AND WAIT.
3) Через эту промежуточную таблицу уже можно делать join lips и ekbe
Code:
* Purchase Order
BEGIN OF ty_po,
vkorg TYPE vbak-vkorg,
vtweg TYPE vbak-vtweg,
ebeln TYPE ekko-ebeln,
ebelp TYPE ekpo-ebelp,
po_bedat TYPE ekko-bedat,
po_status TYPE zesddocflow-po_status,
po_soldto TYPE ekko-ekorg,
po_soldton TYPE t024e-ekotx,
po_shipto TYPE ekko-bukrs,
po_shipton TYPE t024e-ekotx,
po_matnr TYPE ekpo-matnr,
po_txz01 TYPE ekpo-txz01,
po_menge TYPE ekpo-menge,
po_meins TYPE ekpo-meins,
po_menge_base TYPE ekpo-menge,
po_meins_base TYPE ekpo-meins,
po_netwr TYPE ekpo-netwr,
po_brtwr TYPE ekpo-brtwr,
po_waerk TYPE komk-waerk,
po_budat TYPE mkpf-budat,
knumv TYPE ekko-knumv,
END OF ty_po,
* Delivery
BEGIN OF ty_delivery_po,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
vbeln TYPE lips-vbeln,
posnr TYPE lips-posnr,
wadat_ist TYPE likp-wadat_ist,
bldat TYPE likp-bldat,
lfimg TYPE lips-lfimg,
vrkme TYPE lips-vrkme,
werks TYPE lips-werks,
gbstk TYPE vbuk-gbstk,
wbstk TYPE vbuk-wbstk,
fkstk TYPE vbuk-fkstk,
lfdat TYPE likp-lfdat,
vehic TYPE char20, "tdline,
vkorg TYPE likp-vkorg,
vtweg TYPE lips-vtweg,
lgmng TYPE lips-lgmng,
meins TYPE lips-meins,
END OF ty_delivery_po.
DATA:
it_po TYPE SORTED TABLE OF ty_po
WITH NON-UNIQUE KEY ebeln ebelp,
it_delivery_po TYPE SORTED TABLE OF ty_delivery_po
WITH NON-UNIQUE KEY ebeln ebelp.
CHECK NOT it_po[] IS INITIAL.
SELECT ekbe~ebeln ekbe~ebelp
lips~vbeln lips~posnr lips~lfimg lips~vrkme lips~werks lips~vtweg
lips~lgmng lips~meins
likp~wadat_ist likp~bldat likp~lfdat likp~vkorg
vbuk~gbstk vbuk~wbstk vbuk~fkstk
INTO CORRESPONDING FIELDS OF TABLE it_delivery_po
FROM ekbe
INNER JOIN zexxs_posnr_link ON zexxs_posnr_link~buzei = ekbe~buzei
INNER JOIN lips ON lips~vbeln = ekbe~belnr
AND lips~posnr = zexxs_posnr_link~posnr
INNER JOIN likp ON likp~vbeln = lips~vbeln
INNER JOIN vbuk ON likp~vbeln = vbuk~vbeln
FOR ALL entries IN it_po
WHERE ekbe~ebeln = it_po-ebeln
AND ekbe~ebelp = it_po-ebelp
AND ekbe~vgabe = '8' "delivery
AND lips~werks IN so_werks
AND lips~uecha = '' " exclude batch split lines
AND likp~vbeln IN so_vbeld
AND likp~wadat_ist IN so_wadat
AND likp~lfdat IN so_lfdat.
FIELD-SYMBOLS: <fs> TYPE ty_delivery_po.
DATA: lt_dl TYPE TABLE OF ty_delivery_po WITH HEADER LINE.
LOOP AT it_delivery_po ASSIGNING <fs>
WHERE lfimg = 0. "possible batch split
MOVE-CORRESPONDING <fs> TO lt_dl.
SELECT posnr lfimg vrkme werks vtweg lgmng meins
INTO (lt_dl-posnr, lt_dl-lfimg, lt_dl-vrkme, lt_dl-werks, lt_dl-vtweg,
lt_dl-lgmng, lt_dl-meins)
FROM lips
WHERE vbeln = <fs>-vbeln
AND uecha = <fs>-posnr " only batch split lines
AND werks IN so_werks.
APPEND lt_dl.
ENDSELECT.
ENDLOOP.
LOOP AT lt_dl ASSIGNING <fs>.
INSERT <fs> INTO TABLE it_delivery_po.
ENDLOOP.
DELETE it_delivery_po WHERE lfimg IS INITIAL.
_________________ Молитва - это запрос разработчику на изменение кода программы.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
