topic Re: QUERY in Application Development and Automation Discussions

QUERY

Former Member — Tue, 05 Sep 2006 08:35:54 GMT

hi friends ,

could you help me how to do this query

Get KUNNR (ship to) from VBPA-KUNNR when VBPA-PARVW (partner function) =”SH” where VBPA-VBELN(order number) =VBAP-VBELN and if VBPA-POSNR (item number) ne ‘00000’ (item level exist) then get KUNNR when VBPA-PARVW=”SH” (partner function) where VBPA-VBELN &VBPA-POSNR =VBAP-VBELN &VBAP-POSNR .

Get VKORG from VBAK where VBAK-vBELN=VBAP-vBELN

Get MATNR from VBAP-MATNR

regards,

divya

Re: QUERY

Former Member — Tue, 05 Sep 2006 08:43:46 GMT

first u have to get SO data from VBAK+VBAP based on the Selections ,

check like this.

  select   a~vbeln
           a~vkorg
           a~vkbur
           a~vkgrp
           a~kunnr
           e~name1
           b~matnr
           b~arktx
           b~meins
           b~posnr
           b~kwmeng
           b~werks
           b~brgew
     from  ( vbak as a join vbap as b
     on    a~vbeln eq b~vbeln )
           inner join kna1 as e
     on    a~kunnr eq e~kunnr
     into  corresponding fields of table i_sub
     where a~vbeln in s_vbeln and
           a~audat in s_audat and
           a~auart in s_auart and
           a~vkorg in s_vkorg and
           a~vkbur in s_vkbur and
           a~vkgrp in s_vkgrp and
           a~kunnr in s_kunnr and
           b~werks in s_werks and
           b~matnr in s_matnr and
           b~posnr eq d~posnr and
           b~uepos eq '0' and
           b~abgru eq ' '.

then go for VBPA with VBELN or BREAK above Join

Regards

Prabhu

Re: QUERY

Former Member — Tue, 05 Sep 2006 09:00:22 GMT

good

select KUNNR from VBPA WHERE VBPA-PARVW = 'SH' AND VBPA-VBELN = VBAP-VBELN AND VBPA-POSNR NE '000000'.

select KUNNR from VBPA WHERE VBPA-PARVW = 'SH'AND VBPA-VBELN = VBAP-VBELN AND VBPA-POSNR = VBAP-POSNR.

select VKORG from VBAK where VBAK-VBELN = VBAP-VBELN .

thanks

mrutyun^

Re: QUERY

Former Member — Tue, 05 Sep 2006 09:11:35 GMT

Hi,

Do like this

DATA:BEGIN OF ITAB1 OCCURS 0,

VBELN LIKE VBAP-VBELN,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

END OF ITAB1.

DATA:BEGIN OF ITAB2 OCCURS 0,

VBELN LIKE VBPA-VBELN,

POSNR LIKE VBPA-POSNR,

PARVW LIKE VBPA-PARVW,

KUNNR LIKE VBPA-KUNNR,

END OF ITAB2.

DATA:BEGIN OF ITAB3 OCCURS 0,

VBELN LIKE VBAK-VBELN,

VKORG LIKE VBAK-VKORG,

END OF ITAB3.

SELECT VBELN POSNR MATNR FROM VBAP INTO TABLE ITAB1.

SELECT VBELN POSNR PARVW KUNNR FROM VBPA

INTO TABLE ITAB2

FOR ALL ENTRIES IN ITAB1

WHERE VBELN = ITAB1-VBELN AND

POSNR <> '00000' AND

POSNR = ITAB1-POSNR AND

PARVW = 'SH'.

SELECT VBELN VKORG FROM VBAK

INTO TABLE ITAB3

FOR ALL ENTRIES IN ITAB1

WHERE VBELN = ITAB1-VBELN.

Regards,

Sowjanya.