Fetch data from Ztable

[Former Member]

Hi all,

      Iam developing a smartform and i have an issue linking vbap table with  z table. I have to fetch date from z table linking sale order, Line item

and schedule line no. Since vbap  table has data with leading zeros for sale order no , line item and schedule line no , and z table has no leading zeros , I tried to link vbap table and the z table by deleting the leading zeros from vbap table but still it is not working.

I also tried using "shift left deleting leading '0" 

Can anyone help me..

Eg data for your ref:

vbap table :  vbeln                posnr             etenr

                 0000002547     00000010          0001

                 0000002547     00000020          0001

                 0000002547     00000020          0002

Z table:

                sale order           line item        schedule line No

                   2547                   10                    1

                   2547                   20                    1

                   2547                   20                    2

Regards,

Sam.

[davis_raja]

Use the Function Module conversion_exit_alpha_output.

[former_member209120]

Hi

try like this

shift wa-vbeln LEFT DELETING LEADING '0'.

select * from
          ztable
          into table it_ztable     
           where vbeln =  wa-vbeln.

or Use this function module

call function 'CONVERSION_EXIT_ALPHA_OUTPUT'
     exporting
       input  = wa-vbeln

     importing
       output = wa-vbeln

[Former Member]

Dear Samuel,

just use the same data elemets as your fields have in vbap  in your ztable.

goto se14.

give the table name.

press the edit button.

and then press the activate and adjust the table.

Your query will start fetching the data.

and another thing is this posnr have 6 length while you are showing round about 8 or 10 please check that as well.

Regards,

[Former Member]

Dear Zahid,

      The data elements are same for both the tables.

Regards,

Sam.

[former_member209120]

Hi  Samuel Dennis

Still you are not getting... share your code...

[Former Member]

DATA : it_jann TYPE TABLE OF tline,
        wa_jann TYPE tline,
        it_long TYPE TABLE OF tline,
        wa_long TYPE tline,
        long_text TYPE char255,
        xid TYPE thead-tdname,
        lncnt TYPE i,
        sono TYPE vbap-vbeln,
        lnno TYPE vbap-posnr,
        g_fmcdate TYPE zosrgtn-fmc_date,
        g_gtndate TYPE zosrgtn-gtn_date,
        testdat TYPE d,
        revlvx TYPE aeoi-revlv,
        andatx TYPE aeoi-andat,
        g_date(11) TYPE c,
        g_date1(11) TYPE c,
        g_date2(11) TYPE c,
        g_date3(11) TYPE c,
        g_date4(11) TYPE c,
        g_date5(11) TYPE c,
        g_date6(11) TYPE c,
        g_date7(11) TYPE c,
        g_date8(11) TYPE c,
        g_date9(11) TYPE c,
        g_date10(11) TYPE c,
        g_date11(11) TYPE c,
        g_text(1500) TYPE c.

DATA: wa_idate(11) TYPE c.

SELECT vbeln posnr matnr arktx  posex kwmeng kdmat
   INTO CORRESPONDING FIELDS OF TABLE it_css
   FROM vbap
   WHERE vbeln = saleord.

lncnt = 0.

   SELECT a~vbeln a~posnr b~etenr b~wmeng
     FROM vbap AS a INNER JOIN
     vbep AS b ON a~vbeln = b~vbeln
     INTO CORRESPONDING FIELDS OF TABLE
     it_css WHERE a~vbeln = saleord
     AND a~posnr = b~posnr.

     SELECT a~vbeln a~posnr b~s_o_no b~line_item b~fmc_date
     b~gtn_date   FROM vbap AS a INNER JOIN zosrgtn AS b
     ON a~vbeln = b~s_o_no
     INTO CORRESPONDING FIELDS OF TABLE it_css2
     where  a~vbeln = saleord and  a~posnr = b~line_item.

   LOOP AT it_css INTO wa_css.
     wa_css-vbeln = saleord.

     SELECT etenr wmeng edatu
     INTO (wa_css-etenr, wa_css-wmeng, wa_css-edatu)
     FROM vbep
     WHERE vbeln =  wa_css-vbeln
     AND posnr = wa_css-posnr AND etenr = wa_css-etenr.
     ENDSELECT.

     SHIFT wa_css-etenr LEFT DELETING LEADING '0'.
     wa_css-quan = wa_css-wmeng.

     SHIFT wa_css-quan LEFT DELETING LEADING '0'.

     SELECT matnr posex FROM vbap
       INTO (wa_css-matnr ,wa_css-posex)
       WHERE vbeln = wa_css-vbeln
       AND posnr = wa_css-posnr.
     ENDSELECT.

     SELECT SINGLE vbeln audat kunnr bstnk bstdk vdatu
       INTO CORRESPONDING FIELDS OF wa_css
       FROM vbak
       WHERE vbeln = wa_css-vbeln.

*    sono = wa_css-vbeln.

*    SHIFT sono LEFT DELETING LEADING '0'.

     SELECT SINGLE inco2
       FROM vbkd
       INTO wa_css-inco2
       WHERE vbeln = wa_css-vbeln.

     SELECT SINGLE vsnmr_v
       FROM vbak
       INTO wa_css-vsnmr_v
       WHERE vbeln = wa_css-vbeln.

     SELECT SINGLE zterm FROM vbkd
       INTO CORRESPONDING FIELDS OF wa_css
       WHERE vbeln = wa_css-vbeln.

     SELECT SINGLE vtext FROM tvzbt
       INTO wa_css-vtext
       WHERE zterm = wa_css-zterm AND spras = 'E'.

     xid = wa_css-vbeln.
     CONCATENATE xid wa_css-posnr INTO xid.
*    wa_css-posnr = wa_css-posnr / 10.
     CLEAR it_jann.
     CLEAR long_text.
     CLEAR wa_jann-tdline.
     CALL FUNCTION 'READ_TEXT'
       EXPORTING
         id              = 'ZFOR'
         language        = 'E'
         name            = xid
         object          = 'VBBP'
       TABLES
         lines           = it_jann
       EXCEPTIONS
         id              = 1
         language        = 2
         name            = 3
         not_found       = 4
         object          = 5
         reference_check = 6.

     LOOP AT it_jann INTO wa_jann WHERE tdline NE ' '.
       long_text = wa_jann-tdline .
     ENDLOOP.

     wa_css-forecast = long_text.
     g_text = ' '.

     CALL FUNCTION 'READ_TEXT'
       EXPORTING
         id              = '0001'
         language        = 'E'
         name            = xid
         object          = 'VBBP'
       TABLES
         lines           = it_long
       EXCEPTIONS
         id              = 1
         language        = 2
         name            = 3
         not_found       = 4
         object          = 5
         reference_check = 6.

     LOOP AT it_long INTO wa_long WHERE tdline NE ' '.
       long_text = wa_long-tdline .
       CONCATENATE g_text long_text INTO g_text SEPARATED BY ' '.
     ENDLOOP.
     wa_css-longdec = g_text."LONG_TEXT.   10.08

     CLEAR it_jann.
     CLEAR long_text.
     CLEAR wa_jann-tdline.
     CALL FUNCTION 'READ_TEXT'
       EXPORTING
         id              = 'ZMOD'
         language        = 'E'
         name            = xid
         object          = 'VBBP'
       TABLES
         lines           = it_jann
       EXCEPTIONS
         id              = 1
         language        = 2
         name            = 3
         not_found       = 4
         object          = 5
         reference_check = 6.

     LOOP AT it_jann INTO wa_jann WHERE tdline NE ' '.
       long_text = wa_jann-tdline .
     ENDLOOP.
     wa_css-delimode = long_text.

     CLEAR it_jann.
     CLEAR long_text.
     CLEAR wa_jann-tdline.
     CALL FUNCTION 'READ_TEXT'
       EXPORTING
         id              = 'ZREM'
         language        = 'E'
         name            = xid
         object          = 'VBBP'
       TABLES
         lines           = it_jann
       EXCEPTIONS
         id              = 1
         language        = 2
         name            = 3
         not_found       = 4
         object          = 5
         reference_check = 6.

     LOOP AT it_jann INTO wa_jann WHERE tdline NE ' '.
       long_text = wa_jann-tdline .
     ENDLOOP.

SHIFT wa_css-vbeln LEFT DELETING LEADING '0'.
SHIFT wa_css-posnr LEFT DELETING LEADING '0'.

SELECT fmc_date gtn_date FROM zosrgtn
       INTO (wa_css-fmc_date, wa_css-gtn_date)"CORRESPONDING FIELDS OF TABLE it_css
       WHERE s_o_no  = wa_css-vbeln
       AND line_item = wa_css-posnr
       AND s_l_no    = wa_css-etenr.
ENDSELECT.

     wa_css-rem = long_text.
     wa_css-slno = lncnt.
     SHIFT wa_css-slno LEFT DELETING LEADING '0'.
     g_date = wa_css-fmcdate.
     g_date3 = wa_css-gtndate.

     g_date = wa_css-fmcdate+4(2).
     g_date1 = wa_css-fmcdate+6(2).
     g_date2 = wa_css-fmcdate+0(4).

     g_date3 = wa_css-gtndate+4(2).
     g_date4 = wa_css-gtndate+6(2).
     g_date5 = wa_css-gtndate+0(4).

     g_date9 = wa_css-bstdk+0(4).

     CALL FUNCTION 'CONVERSION_EXIT_LDATE_OUTPUT'
       EXPORTING
         input  = wa_css-fmcdate
       IMPORTING
         output = wa_css-fmcmonth.

     CONCATENATE g_date1 '-'
     wa_css-fmcmonth+4(3) '-' g_date2 INTO wa_css-fmcmonth1.

     CALL FUNCTION 'CONVERSION_EXIT_LDATE_OUTPUT'
       EXPORTING
         input  = wa_css-gtndate
       IMPORTING
         output = wa_css-gtnmonth.

     CONCATENATE g_date4 '-'
     wa_css-gtnmonth+4(3) '-' g_date5 INTO wa_css-gtnmonth1.

     g_date6 = wa_css-audat+0(4).
     g_date7 = wa_css-audat+4(2).
     g_date8 = wa_css-audat+6(2).
     CONCATENATE g_date6 g_date7 g_date8 INTO wa_css-audat2.

     CALL FUNCTION 'CONVERSION_EXIT_LDATE_OUTPUT'
       EXPORTING
         input  = wa_css-audat2
       IMPORTING
         output = wa_css-audat1.

     CONCATENATE g_date8 '-'
     wa_css-audat1+4(3) '-' g_date6 INTO wa_css-audat3.

     g_date9  = wa_css-bstdk+0(4).
     g_date10 = wa_css-bstdk+4(2).
     g_date11 = wa_css-bstdk+6(2).
     CONCATENATE g_date9 g_date10 g_date11 INTO wa_css-bstdk2.

     CALL FUNCTION 'CONVERSION_EXIT_LDATE_OUTPUT'
       EXPORTING
         input  = wa_css-bstdk2
       IMPORTING
         output = wa_css-bstdk1.

     CONCATENATE g_date11 '-'
     wa_css-bstdk1+4(3) '-' g_date9 INTO wa_css-bstdk3.

     g_date = wa_css-edatu+4(2).
     g_date1 = wa_css-edatu+6(2).
     g_date2 = wa_css-edatu+0(4).

     CALL FUNCTION 'CONVERSION_EXIT_LDATE_OUTPUT'
       EXPORTING
         input  = wa_css-edatu
       IMPORTING
         output = wa_css-edatu1.

     CONCATENATE g_date1 '-'
     wa_css-edatu1+4(3) '-' g_date2 INTO wa_css-edatu2.

     MODIFY it_css FROM wa_css.
   ENDLOOP.
  

[Former Member]

Hi Ramesh,

   Hope you have found the line which i have got stuck.

Rgds,

sam.

[former_member209120]

Hi

Where you defined it_css

[former_member209120]

Here

SELECT fmc_date gtn_date FROM zosrgtn
        INTO (wa_css-fmc_date, wa_css-gtn_date)"CORRESPONDING FIELDS OF TABLE it_css
        WHERE s_o_no  = wa_css-vbeln
        AND line_item = wa_css-posnr
        AND s_l_no    = wa_css-etenr.
 ENDSELECT.

Try like this

SHIFT wa_css-vbeln LEFT DELETING LEADING '0'.
SHIFT wa_css-posnr LEFT DELETING LEADING '0'.
SHIFT wa_css-etenr LEFT DELETING LEADING '0'.

SELECT fmc_date gtn_date FROM zosrgtn
        INTO (wa_css-fmc_date, wa_css-gtn_date)"CORRESPONDING FIELDS OF TABLE it_css
        WHERE s_o_no  = wa_css-vbeln
        AND line_item = wa_css-posnr
        AND s_l_no    = wa_css-etenr.
ENDSELECT.

[Former Member]

Ramesh,

    I tried them long back but still its not working.

I have declared it_css in the global def.

[former_member209120]

Hi

Past full code or debug.

[Former Member]

Hi,

    since this is a smartform, I have pasted the full coding part.

[Former Member]

Hi samuel,

        Please provide me the snap shot of your ztable.

Regards,

[Former Member]

Hi Zahid,

   You want the snap shot of se11 or se16

[Former Member]

[Former Member]

Samuel,

         the snap shot of the of fields with data elements.

Regards,

[Former Member]

Dear Samuel Dennis,

         I have create the table with the name zvbap and enter some values in it it has been automaitcally createing the leading zeros in the table. why it is not coming with you it is the problem of upload program which has been updating the data in the table. Here is the snap shot.

data: BEGIN OF itab OCCURS 0,

       vbeln type vbap-vbeln,

       posnr type vbap-posnr,

       etenr type eket-etenr,

   END OF itab.

  data: it_ztab type STANDARD TABLE OF zvbap WITH HEADER LINE.

  data: it_vbap type STANDARD TABLE OF vbap WITH HEADER LINE.

         select * from vbap INTO table it_vbap where vbeln = '0002545876'.

LOOP AT it_vbap.

SELECT SINGLE * from zvbap into  it_ztab where vbeln = it_vbap-vbeln and posnr = it_vbap-posnr.

IF sy-subrc eq 0.

WRITE😕 it_ztab-vbeln, '       ', it_ztab-posnr, '       ', it_ztab-etenr.

ENDIF.

ENDLOOP.

regards,

IN YOUR SCENARIO.

data: BEGIN OF itab OCCURS 0,

       vbeln type vbap-vbeln,

       posnr type vbap-posnr,

       etenr type eket-etenr,

   END OF itab.

data: BEGIN OF zitab OCCURS 0,

       saleord type vbap-vbeln,

       linitem TYPE vbap-pOsnr,

       schline type eket-etenr,

   END OF zitab.

itab-vbeln = '0002545876'.

itab-posnr = '000010'.

itab-ETENR = '0001'.

APPEND itab.

clear: itab.

itab-vbeln = '0002545876'.

itab-posnr = '000020'.

itab-ETENR = '0001'.

APPEND itab.

clear: itab.

itab-vbeln = '0002545876'.

itab-posnr = '20'.

itab-ETENR = '2'.

APPEND itab.

clear: itab.

zitab-SALEORD = '2545876'.

zitab-LINITEM = '10'.

zitab-SCHLINE = '1'.

APPEND zitab.

clear: zitab.

zitab-SALEORD = '2545876'.

zitab-LINITEM = '20'.

zitab-SCHLINE = '1'.

APPEND zitab.

clear: zitab.

zitab-SALEORD = '2545876'.

zitab-LINITEM = '20'.

zitab-SCHLINE = '2'.

APPEND itab.

clear: itab.

LOOP AT  itab.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

   EXPORTING

     INPUT         = itab-VBELN

  IMPORTING

    OUTPUT        = itab-VBELN

           .

READ TABLE  ZITAB WITH KEY SALEORD = ITAB-VBELN   LINITEM = ITAB-POSNR.

   IF SY-SUBRC EQ 0.

     WRITE😕 ZITAB-SALEORD , '  ', ZITAB-LINITEM.

     ENDIF.

ENDLOOP.

REGARDS,

[Former Member]

Hi Samuel,

As mentioned in above two reply’s FM CONVERSION_EXIT_ALPHA_OUTPUT removes leading zeros and FM CONVERSION_EXIT_ALPHA_INPUT inserts the leading zeros. During updation of Custom table insert leading zeros similar to VBAP and try link both the tables. Also use same data elements used in VBAP.

Thanks

Siddhu

[former_member219162]

Hello Samuel,

Can you try this out;

Let's say the VBELN from VBAP is in variable: VB_VBELN

and VBELN from Z- table is in variable: Z_VBELN

SHIFT VB_VBELN LEFT DELETING LEADING '0'.    (this statement would delete all Zeroes in prefix)

Z_VBELN = VB_VBELN.

now, if you have something like this;

Z_VBELN is of 8 characters and it has leading Zeroes, then you need to add one more line to code;

Unpack Z_VBELN to Z_VBELN.

[Sudhakargadde]

Hi samuel,

1)in Ztable also you maintained the Sales order Data element as (VBELN_VA) if not change it to this then adjust the table with using SE14(Data Base Utility).

2)or convert the sales order values using the function modules

CONVERSION_EXIT_ALPHA_INPUT      Conversion exit ALPHA, external->internal

CONVERSION_EXIT_ALPHA_OUTPUT   Conversion exit ALPHA, internal->external

Regards

Sudhakar

[Former Member]

Dear all,

    Thank you so much for all your replies.

The issue has been solved.

Regards,

Sam.

[former_member209120]

Good then close this thread with your solution....

[Former Member]

Hi,

  In my code i did the following modifications and also changed the main table from vbap to vbep since vbap has no etenr.

Thanks everybody for all your reply.

SELECT vbeln posnr etenr from vbep
   INTO (wa_css-vbeln , wa_css-posnr, wa_css-etenr)
   WHERE vbeln = wa_css-vbeln
   AND posnr   = wa_css-posnr
   AND etenr   = WA_css-etenr.
ENDSELECT.

SHIFT wa_css-vbeln LEFT DELETING LEADING '000000'.

SELECT SINGLE s_o_no fmc_date gtn_date from zosrgtn
   into (wa_css-s_o_no, wa_css-fmc_date , wa_css-gtn_date)
   where s_o_no = wa_css-vbeln
   and  line_item = wa_css-posnr.

SELECT  s_o_no fmc_date gtn_date from zosrgtn
   into (wa_css-s_o_no, wa_css-fmc_date , wa_css-gtn_date)
   where s_o_no = wa_css-vbeln
   and   line_item = wa_css-posnr
   and   s_l_no  = wa_css-etenr.
ENDSELECT.

Regards,

Sam.

[Former Member]

Hi Samuel,

Instead of removing leading zeroes compare both by adding leading zeroes in your ztable entries using the

'CONVERSION_EXIT_ALPHA_INPUT' for conversion into sap internal format.

Or use 'CONVERSION_EXIT_ALPHA_OUTPUT' for converting SAP internal format to output format i.e. without leading zeroes.

Hope this helps.

Regards,

Sheetal.