topic Re: Fetch data from Ztable in Application Development and Automation Discussions

Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 08:51:39 GMT

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.

Re: Fetch data from Ztable

davis_raja — Wed, 20 Nov 2013 08:55:07 GMT

Use the Function Module conversion_exit_alpha_output.

Re: Fetch data from Ztable

former_member209120 — Wed, 20 Nov 2013 08:56:10 GMT

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

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 09:05:28 GMT

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,

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 09:08:41 GMT

Dear Zahid,

The data elements are same for both the tables.

Regards,

Sam.

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 09:08:52 GMT

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

Re: Fetch data from Ztable

former_member219162 — Wed, 20 Nov 2013 09:11:15 GMT

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.

Re: Fetch data from Ztable

Sudhakargadde — Wed, 20 Nov 2013 09:28:00 GMT

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

Re: Fetch data from Ztable

former_member209120 — Wed, 20 Nov 2013 09:30:47 GMT

Hi Samuel Dennis

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

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 09:36:09 GMT

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.

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 09:45:41 GMT

Hi Ramesh,

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

Rgds,

sam.

Re: Fetch data from Ztable

former_member209120 — Wed, 20 Nov 2013 09:49:11 GMT

Where you defined it_css

Re: Fetch data from Ztable

former_member209120 — Wed, 20 Nov 2013 09:51:05 GMT

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.

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 09:54:11 GMT

Ramesh,

I tried them long back but still its not working.

I have declared it_css in the global def.

Re: Fetch data from Ztable

former_member209120 — Wed, 20 Nov 2013 10:02:01 GMT

Past full code or debug.

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 10:28:16 GMT

Hi,

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

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 10:39:19 GMT

Hi samuel,

Please provide me the snap shot of your ztable.

Regards,

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 10:44:34 GMT

Hi Zahid,

You want the snap shot of se11 or se16

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 10:50:41 GMT

Re: Fetch data from Ztable

Former Member — Wed, 20 Nov 2013 11:54:12 GMT

Samuel,

the snap shot of the of fields with data elements.

Regards,