04-08-2009 10:00 AM
hy Experts.
Please Help me For this Query, i got error on it.
I wnt to take KONV-KBETR (RATE FIELD) for particular SO number. and i used following query but it gives error i cant relate VBAK & KONV table.
Please do needful
SELECT KBETR KNUMV FROM KONV INTO (ITAB-KBETR, ITAB-KNUMV) WHERE VBAK-KNUMV = KONV-KNUMV AND VBAK-VBELN IN VBELN.
Thnks
Bhavesh Panchal.
04-08-2009 10:04 AM
Hi,
try this:
Select kbetr knumv
from konv join vbak on vbakknumv = konvknumv
into table itab
where vbeln in vbeln.
Regards,
Leo.
04-08-2009 10:04 AM
Hi,
try this:
Select kbetr knumv
from konv join vbak on vbakknumv = konvknumv
into table itab
where vbeln in vbeln.
Regards,
Leo.
04-08-2009 10:13 AM
hy
thnks for reply
but still i try ur query
Select kbetr knumv
from konv join vbak on vbakknumv = konvknumv
into table itab
where vbeln in vbeln
but still i getting error VBK unknown.
Tnks for Help
04-08-2009 10:08 AM
if it_vbak is not initial.
Select KBETR KNUMV From KONV into table it_konv for all entries in it_vbak where
knumv = it_vbak-knumv.
endif.
You can even try like this..Hope it helps.
04-08-2009 10:11 AM
modifY ur query like this:
DATA: TEMP TYPE KONV-KBETR.
SELECT VBELN KNUMV
FROM VBAK
INTO (ITAB1-VBELN ITAB1-KNUMV)
WHERE VBELN IN VBELN
SELECT KBETR KNUMV
FROM KONV
INTO (ITAB-KBETR, ITAB-KNUMV)
WHERE KNUMV = VBAK-KNUMV
TEMP = TEMP + ITAB-KBETR (instead of collect statement, we can use this to accumulate the rate for a particular SO)
ENDSELECT.
(BEFORE CLEARING temp, move these values, VBELN, KNUMV AND temp INTO DIFF. internal table)
clear: temp
ENDSELECT.
04-08-2009 10:27 AM
thnks for reply but i still getting error Declare Temp statements it give the Expected TEMP.
Thnks
04-08-2009 10:38 AM
copy the below code and execute the program
tables: vbak, konv.
types: begin of ty_vbak,
vbeln type vbak-vbeln,
knumv type knov-knumv,
kbetr type knov-kbetr,
end of ty_vbak.
data: it_vbak type table of ty_vbak,
wa-vbak type table of ty_vbak,
TEMP TYPE KONV-KBETR.
start-of-selection.
SELECT VBELN KNUMV
FROM VBAK
INTO (wa_vbak-VBELN, wa_vbak-KNUMV)
WHERE VBELN IN VBELN
SELECT KBETR
FROM KONV
INTO wa_vbak-KBETR
WHERE KNUMV = wa_VBAK-KNUMV
collect wa_vbak.
ENDSELECT.
end-of-selection.
loop at it_vbak into wa_vbak.
write:/ wa_vbak-vbeln, wa_vbak-knumv, wa_vbak-kbetr.
clear: wa_vbak.
endloop.
04-08-2009 10:41 AM
Hi,
You can proceed in the following manner for fetching rate for a particular sales order:
Declare a internal table with required fields of KONV and VBAK. Also declare one internal table with required field for output e.g lt_out.
SELECT vbeln knumv FROM vbak INTO TABLE lt_vbak
WHERE vbeln IN s_vbeln.
if sy-subrc EQ 0.
SELECT knumv kposn kbetr FROM konv INTO lt_konv
FOR ALL ENTERIES IN lt_vbak
WHERE knumv = lt_vbak-knumv.
ENDIF.
LOOP AT lt_vbak INTO wa_vbak.
READ TABLE lt_konv INTO wa_konv WITH KEY knumv = wa_vbak-knumv.
if sy-subrc eq 0.
wa_out-vbeln = wa_vbak-vbeln.
wa_out-kbetr = wa_knumv-kbetr.
APPEND wa_out TO lt_out.
ENDLOOP.
In this way both the tables can be linked. Field KNUMV is the link between both the tables.
Hope this solves your problem.
Regards,
Brajvir
04-08-2009 10:39 AM
Hi,
We cannot join VBAK and KONV as one is a transparent table and other is a cluster table.
We cannot write inner join between a cluster table and a transparent table. System will generate an error.
Regards.
04-08-2009 10:47 AM
04-08-2009 12:39 PM
DECLARE IN GLOBAL DEFINATIONS
IT_KONV TYPE STANDARD TABLE OF KONV
AND SAME FOR VBAK
select * from konv appending table itab_lt_konv1
where knumv EQ WA_VBAK-KNUMV .
READ TABLE ITAB_LT_KONV1 INTO WA_KONV WITH KEY KNUMV = IT_VBAK-KNUMV.
LV_KBETR = WA_KONV-KBETR.
or take this code.
select *
from konv
into table it_konv
for all entries in it_vbrk
where knumv = it_vbrk-knumv
AND KINAK = SPACE .
read table it_konv with key knumv = it_vbak-knumv "included discount
kschl = 'ZDIS'.
it_final-kbetr = it_konv-kbetr / 10.
it_final-kwert = it_konv-kwert .
04-09-2009 6:36 AM
Hello Thnks For Reply,
but still i cant take solution. i am making a Sales order Rports. i got all field but i cant fetch filed for RATE and Pending Order Value.
Please check following Code. if need to change u can.
Thnks
Bhavesh Panchal.
REPORT ZTESTCODE.
TYPE-POOLS:slis.
TABLES: VBEP , VBAP , VBPA , VBKD , VBAK , LIKP , LIPS , VBUP , VBBE, KONV, KNA1.
DATA :BEGIN OF itab OCCURS 0,
VBELN LIKE VBAK-VBELN,
POSNR LIKE VBAP-POSNR,
EDATU LIKE VBEP-EDATU,
KNUMV LIKE VBAK-KNUMV,
WMENG LIKE VBEP-WMENG,
BMENG LIKE VBEP-BMENG,
LFIMG LIKE LIPS-LFIMG,
OMENG LIKE VBBE-OMENG,
POSAR LIKE VBAP-POSAR,
NETWR LIKE VBAP-NETWR,
NTGEW LIKE VBAP-NTGEW,
KBETR LIKE KOMV-KBETR,
KUNNR LIKE VBAK-KUNNR,
NAME1 LIKE KNA1-NAME1,
*BRGEW LIKE VBAP-BRGEW,
BSTKD LIKE VBKD-BSTKD,
BSTDK LIKE VBKD-BSTDK,
LFSTA LIKE VBUP-LFSTA,
*KNUMV LIKE VBAK-KNUMV,
*posnr LIKE LIPS-POSNR,
*NETWR LIKE VBAK-NETWR,
BRGEW LIKE VBAP-BRGEW,
LFDAT LIKE LIKP-LFDAT,
*NTGEW LIKE LIKP-NTGEW,
DELIVERY LIKE LIPS-VBELN,
WEIGHT like VBAP-NTGEW,
END OF itab.
*variable for Report ID
DATA: v_repid LIKE sy-repid .
*declaration for fieldcatalog
DATA: i_fieldcat TYPE slis_t_fieldcat_alv,
wa_fieldcat TYPE slis_fieldcat_alv.
DATA: it_listheader TYPE slis_t_listheader.
declartion for layout
DATA: alv_layout TYPE slis_layout_alv.
*Title displayed when the alv list is displayed
*DATA: i_title_main TYPE lvc_title VALUE 'FIRST LIST DISPLAYED'.
DATA: i_title_main TYPE lvc_title VALUE 'Reports : Bhavesh Pacnhal'.
INITIALIZATION.
v_repid = sy-repid.
PERFORM build_fieldcatlog.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS : VBELN FOR VBAK-VBELN ,
KUNNR FOR VBPA-KUNNR ,
EDATU for ITAB-EDATU.
SELECTION-SCREEN: END OF BLOCK b1.
START-OF-SELECTION.
PERFORM data_retrieval.
PERFORM build_listheader USING it_listheader.
PERFORM display_alv_report.
FORM build_fieldcatlog.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'VBELN'.
wa_fieldcat-seltext_m = 'SalesOrderNo'.
wa_fieldcat-outputlen = '12'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
Field for Customer Name.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'NAME1'.
wa_fieldcat-seltext_m = 'Customer Name'.
wa_fieldcat-outputlen = '30'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
Field For Po Number.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'BSTKD'.
wa_fieldcat-seltext_m = 'PO No'.
wa_fieldcat-outputlen = '20'.
append wa_fieldcat to i_fieldcat.
clear wa_fieldcat.
Field For Order Value
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'NETWR'.
wa_fieldcat-seltext_m = 'Order Value '.
wa_fieldcat-outputlen = '12'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
Field For Po Date.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'BSTDK'.
wa_fieldcat-seltext_m = 'PO Date'.
wa_fieldcat-outputlen = '20'.
append wa_fieldcat to i_fieldcat.
clear wa_fieldcat.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'POSNR'.
wa_fieldcat-seltext_m = 'Item No'.
wa_fieldcat-outputlen = '20'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'EDATU'.
wa_fieldcat-seltext_m = 'Delivery Date'.
wa_fieldcat-outputlen = '12'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
wa_fieldcat-tabname = 'VBAP'.
wa_fieldcat-fieldname = 'KWMENG'.
wa_fieldcat-seltext_m = 'Order QTY VABP.'.
wa_fieldcat-outputlen = '14'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
Field For Order Qty.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'WMENG'.
wa_fieldcat-seltext_m = 'Ord.QT.WMEN.'.
wa_fieldcat-outputlen = '20'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
Field For Order Qty.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'BMENG'.
wa_fieldcat-seltext_m = 'Ord.QT.BMEN.'.
wa_fieldcat-outputlen = '20'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
field For Delivery Qty.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'LFMING'.
wa_fieldcat-seltext_m = 'DEL QTY.'.
wa_fieldcat-outputlen = '20'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
Field For Open Qty from ITAB.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'OMENG'.
wa_fieldcat-seltext_m = 'OPEN QTY.'.
wa_fieldcat-outputlen = '20'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
Field For Pending Weight from ITAB.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'BRGEW'.
wa_fieldcat-seltext_m = 'Pending Weight'.
wa_fieldcat-outputlen = '12'.
append wa_fieldcat to i_fieldcat.
clear wa_fieldcat.
Field For netweight.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'NTGEW'.
wa_fieldcat-seltext_m = 'Net Weight'.
wa_fieldcat-outputlen = '20'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
Field For Actual Delivery.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'LFDAT'.
wa_fieldcat-seltext_m = 'Actual Delivery'.
wa_fieldcat-outputlen = '12'.
append wa_fieldcat to i_fieldcat.
clear wa_fieldcat.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'KBETR'.
wa_fieldcat-seltext_m = 'RATE'.
wa_fieldcat-outputlen = '20'.
append wa_fieldcat to i_fieldcat.
clear wa_fieldcat.
Field For Actual Delivery.
wa_fieldcat-tabname = 'LIPS'.
wa_fieldcat-fieldname = 'POSNR'.
wa_fieldcat-seltext_m = 'Delivery ITEM'.
wa_fieldcat-outputlen = '20'.
append wa_fieldcat to i_fieldcat.
clear wa_fieldcat.
*
Field For Pending Value.
VBUP-LFSTA
wa_fieldcat-tabname = 'VBUP'.
wa_fieldcat-fieldname = 'LFSTA'.
wa_fieldcat-seltext_m = 'Pending Value'.
wa_fieldcat-outputlen = '20'.
append wa_fieldcat to i_fieldcat.
clear wa_fieldcat.
Field For pending weight.
wa_fieldcat-tabname = 'ITAB'.
wa_fieldcat-fieldname = 'ITAB-OMENG * KOMV-KBETR'.
wa_fieldcat-seltext_m = 'Pending VALUE'.
wa_fieldcat-outputlen = '20'.
APPEND wa_fieldcat TO i_fieldcat.
CLEAR wa_fieldcat.
*
ENDFORM. "BUILD_FIELDCATLOG
FORM data_retrieval.
SELECT VBELN NETWR KUNNR FROM VBAK INTO (ITAB-VBELN,ITAB-NETWR,ITAB-KUNNR) WHERE VBELN IN VBELN .
SELECT POSNR POSAR BRGEW FROM VBAP INTO (ITAB-POSNR,ITAB-POSAR,ITAB-BRGEW) WHERE VBELN = ITAB-VBELN .
SELECT EDATU FROM VBEP INTO (ITAB-EDATU) WHERE VBELN = ITAB-VBELN AND POSNR = ITAB-POSNR.
SELECT NAME1 FROM KNA1 INTO (ITAB-NAME1) WHERE KUNNR = ITAB-KUNNR .
SELECT BSTKD BSTDK FROM VBKD INTO (ITAB-BSTKD,ITAB-BSTDK) WHERE VBELN = ITAB-VBELN AND POSNR = ITAB-POSNR.
SELECT VBELN LFIMG FROM LIPS INTO (ITAB-DELIVERY, ITAB-LFIMG) WHERE VGBEL = ITAB-VBELN AND VGPOS = ITAB-POSNR.
SELECT LFDAT NTGEW FROM LIKP INTO (ITAB-LFDAT,ITAB-NTGEW) WHERE VBELN = ITAB-DELIVERY .
SELECT WMENG BMENG FROM VBEP INTO (ITAB-WMENG,ITAB-BMENG) WHERE VBELN IN VBELN .
SELECT SINGLE OMENG FROM VBBE INTO (ITAB-OMENG) WHERE VBELN IN VBELN.
SELECT SINGLE NETWR FROM VBAK INTO (ITAB-NETWR) WHERE VBELN IN VBELN.
SELECT KNUMV FROM VBAK INTO (ITAB-KNUMV) WHERE VBELN IN VBELN.
*SELECT KBETR KNUMV FROM KONV INTO (ITAB-KBETR, ITAB-KNUMV) WHERE KNUMV = VBAK-KNUMV.
*assign VAR1 = itab-kbetr.
*append itab.
*ENDSELECT.
SELECT KBETR KNUMV FROM KONV INTO (ITAB-KBETR, ITAB-KNUMV)WHERE KNUMV = VBAK-KNUMV.
ITAB-KBETR = KONV-KBETR.
ENDSELECT.
APPEND ITAB.
ENDSELECT.
ENDSELECT.
ENDSELECT.
ENDSELECT.
ENDSELECT.
ENDSELECT.
ENDSELECT.
ENDSELECT.
ENDSELECT.
*ENDSELECT.
ENDFORM. "data_retrieval
FORM build_listheader USING it_listheader TYPE slis_t_listheader.
DATA HLINE TYPE SLIS_LISTHEADER.
DATA: ls_line TYPE slis_listheader.
bhavesh
HLINE-INFO = 'report Developed by Bhavesh'.
HLINE-TYP = 'H'.
Header
Bhavesh
CLEAR ls_line.
ls_line-typ = 'H'.
LS_LINE-KEY: not used for this type
ls_line-info = 'Sales ORDER Report'.
APPEND ls_line TO it_listheader.
bhavesh
***Selection
CLEAR ls_line.
ls_line-typ = 'S'.
ls_line-key = 'Key 1'.
ls_line-info = 'SFEL'.
APPEND ls_line TO it_listheader.
ls_line-key = 'Key 2'.
ls_line-info = 'SFEL'.
APPEND ls_line TO it_listheader.
***Action
CLEAR ls_line.
ls_line-typ = 'A'.
LS_LINE-KEY: not used for this type
ls_line-info = 'Status list'.
APPEND ls_line TO it_listheader.
ENDFORM. "BUILD_LISTHEADER
*ENDFORM. "build_listheader
FORM display_alv_report.
v_repid = sy-repid.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = v_repid
I_CALLBACK_PF_STATUS_SET = 'STATUS'(002)
i_callback_user_command = 'USER_COMMAND'
i_callback_top_of_page = 'TOP_OF_PAGE'
i_grid_title = 'SFEL SALES EXPEDITING REPORT'
i_background_id = 'ALV_BACKGROUND'
I_GRID_SETTINGS =
IS_LAYOUT = LAYO
IS_LAYOUT = ALV_LAYOUT
it_fieldcat = i_fieldcat[]
IT_EXCLUDING =
IT_SPECIAL_GROUPS =
IT_SORT =
IT_FILTER =
IS_SEL_HIDE =
i_default = 'ZLAY1'
I_SAVE = 'A'
is_variant = i_variant
IT_EVENTS = V_EVENTS
TABLES
t_outtab = itab[]
EXCEPTIONS
PROGRAM_ERROR = 1
OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
ENDFORM. "display_alv_report
FORM top_of_page.
CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
EXPORTING
it_list_commentary = it_listheader[].
i_logo = 'ENJOYSAP_LOGO'.
I_END_OF_LIST_GRID =
ENDFORM. "TOP_OF_PAGE
04-09-2009 6:58 AM
hi,
i m giving u a sample code.
if u dont mind . ur code is very criticle to understand. use this code and write ur logic its very easy
instead of ur select queries. write select queries like this.
SELECT * " for order types
"ZIO1 ZI07 Zi08 to be added
FROM vbak
INTO TABLE it_vbak
where vbeln = SO_VBELN
KUNNR = VBAK-KUNNR
EDATU = VBAK-ERDAT
and auart in ('ZIO1' , 'ZI07' , 'ZI08' , 'ZIR1') .
IF NOT it_vbak[] IS INITIAL.
SELECT *
FROM vbpa
INTO TABLE it_vbpa
FOR ALL ENTRIES IN it_vbak
WHERE vbeln = it_vbak-vbeln
AND posnr = space.
select *
from konv
into table it_konv
for all entries in it_vbak
where knumv = it_vbak-knumv
AND KINAK = SPACE .
SELECT *
FROM vbap
INTO TABLE it_vbap
FOR ALL ENTRIES IN it_vbak
WHERE vbeln = it_vbak-vbeln
and PSTYV ne 'ZIO1'.
select *
from vbkd
into table it_vbkd
for all entries in it_vbaP
where vbeln = it_vbaP-vbeln.
* and posnr = IT_VBAP-POSNR.
ENDIF.
then do looping like this.
loop at IT_VBAP.
READ TABLE it_vbak with key vbeln = it_vbap-vbeln.
if sy-subrc = 0.
it_final-vbeln = it_vbak-vbeln. " so number
it_final-audat = it_vbak-audat. " SO Date
it_final-kunnr_billto = it_vbak-kunnr. " bill to party
it_final-vdatu = it_vbak-vdatu.
read table it_konv with key knumv = it_vbak-knumv "included discount
KPOSN = it_vbap-posnr
kschl = 'ZBPR'.
if sy-subrc = 0.
it_final-kbetr = it_konv-kbetr / 10.
"included Discount rate
it_final-kbetr = it_final-kbetr .
it_final-kwert = it_konv-kwert .
"included Discount Amount
it_final-dis_ind = 'EXC'. "discount indicator
endif.
append it_final .
ENDLOOP.
LIKE THIS READ ALL TABLES DATA
one more thing u cant use ur itab-date
EDATU for ITAB-EDATU
u have to use EDATU = VBAK-ERDAT LIKE THIS ONLY.
Hope u will change ur code like this. then data wil definately fetched.
FOR PENDING ORDER VALUE.
write this code in ur loop endloop.
SELECT *
from vbup
into table it_vbup
FOR ALL ENTRIES IN IT_VBAP
where vbeln = it_vbap-vbeln
and posnr = it_vbap-POSNR.
SELECT *
from vbfa
into table it_vbfa
FOR ALL ENTRIES IN IT_VBAP
where VBELV = it_vbap-vbeln
and POSNV = it_vbap-posnr.
Loop at it_vbap into wa_vbap where vbeln = it_vbak-vbeln .
*BREAK MTABAP.
MOVE-CORRESPONDING wa_vbap to it_VBAPVB.
append it_VBAPVB.
endloop.
loop at it_vbEp where vbeln = it_vbak-vbeln.
MOVE-CORRESPONDING it_vbep to IT_VBEPVB.
append it_VBEPVB.
ENDLOOP.
CALL FUNCTION 'RV_SCHEDULE_CHECK_DELIVERIES'
EXPORTING
fbeleg = it_vbap-vbeln
fposnr = it_vbap-Posnr
* FVERRECHNUNG = ' '
* FS073_ALT = ' '
* IF_NO_SORT = ' '
tables
fvbfa = it_vbfa
fvbup = it_vbup
fxvbep = it_VBEPVB
* FVBLB =
fvbap = it_VBAPVB
EXCEPTIONS
FEHLER_BEI_LESEN_FVBUP = 1
FEHLER_BEI_LESEN_FXVBEP = 2
OTHERS = 3
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
LOOP AT it_VBEPVB WHERE POSNR = IT_VBAP-POSNR.
IT_FINAL-LV_DQTY = IT_FINAL-LV_DQTY + it_VBEPVB-VSMNG.
ENDLOOP.
IT_FINAL-LV_PQTY = IT_FINAL-LV_OQTY - IT_FINAL-LV_DQTY .
free : IT_VBEPVB[] , it_VBAPVB[] , it_vbup[] ,it_vbFA[].
in this way u will get pending qty in IT_FINAL-LV_PQTY .
THEN multiply this value with vbap-netwr .
or konv-kwert for perticular condtion (basic price. i.e. in our case its 'ZBPR')
04-09-2009 9:06 AM
04-09-2009 9:09 AM
04-09-2009 9:45 AM