select stmt to update database table

[Former Member]

Hi All,

when the option blockremov is pressed and check box is checked, I have to update likp table, with lifsk = ' '. Can you please help me with it.

when 'BLOCKREMOV'.

LOOP AT IT_OUTPUT.

IF ( IT_OUTPUT-CHECK = 'X' AND sy-ucomm = 'BLOCKREMOV' ).

UPDATE TABLE LIKP

WHERE VBELN = IT_OUTPUT-VBELN

TRANSPORTING LIFSK

WHERE LIFSK = ' '.

ENDIF.

ENDLOOP.

Thanks,

Veni.

[Former Member]

when 'BLOCKREMOV'.

LOOP AT IT_OUTPUT.

IF ( IT_OUTPUT-CHECK = 'X' AND sy-ucomm = 'BLOCKREMOV' ).

UPDATE TABLE set LIFSK = ' ' where vbeln = it_output-vbeln

ENDIF.

ENDLOOP.

[Former Member]

Hi,

Use the FM WS_DELIVERY_UPDATE to remove the delivery block instead of direct table update..

DATA: vbkok LIKE vbkok.

vbkok-vbeln_vl = p_vbeln.

vbkok-kzlsp = 'X'.

vbkok-lifsk = ' '.

CALL FUNCTION 'WS_DELIVERY_UPDATE'

EXPORTING

vbkok_wa = vbkok

delivery = p_vbeln

EXCEPTIONS

OTHERS = 1.

COMMIT WORK.

Thanks,

Naren

[messier31]

IT_OUTPUT_TMP

First thing is there is no need to check

sy-ucomm = 'BLOCKREMOV' in if condition,

since it has already been done using WHEN 'BLOCKREMOV'.

<b>1. First option is</b>

WHEN 'BLOCKREMOV'.

LOOP AT IT_OUTPUT WHERE CHECK = 'x'.

UPDATE LIKP SET LIFSK = ' '

WHERE VBELN = IT_OUTPUT-VBELN.

ENDLOOP.

<b>2. Second option</b> is for say if you don't want to hit database for each itab record..that is if u want to update database in single hit..

data : it_output_tmp type hashed table of likp

with unique key key1 key2 ...

WHEN 'BLOCKREMOV'.

it_output_tmp[] = it_output[].

delete it_output_tmp where check <> 'X'.

update likp from table it_output_tmp.

But in this second option you will have to take little

care..there few points that u shd take care of

a. Use this only u have all the key for table

b. If table record with key in internal table is

not found, same is inserted in database as

new record...

check that you have records with proper

existing key in internal table it_output

c. Also it will update all the fields from itab

work area..so u shd not modify other fields

else same will updated in database.

If you are taking care of this points then you can go

for 2nd option else go for 1st otion..

Enjoy SAP.

Pankaj Singh

[Former Member]

Hi,

when 'BLOCKREMOV'.

LOOP AT IT_OUTPUT.

IF IT_OUTPUT-CHECK = 'X'.

UPDATE LIKP SET LIFSK = ' '

WHERE VBELN = IT_OUTPUT-VBELN.

ENDIF.

ENDLOOP.

sy-lsind = sy-lsind - 1.

PERFORM DISPLAY_DATA.

endcase.

I did this and when I debug, it is not going in to IF condition. Please help me resolve this.

Thanks

Veni.

[messier31]

The only reason why it is not going in if is

it_output-check does'nt contain 'X'.

can you please debug and check what value is getting populated in it_output-check..

Also just look in database also chk whether it is updating any...

let us know status on this...

Enjoy SAP.

Pankaj Singh.

[Former Member]

Please use Function Module - WS_DELIVERY_UPDATE - any direct table updates to a core table, specifically, will put your database's referential integrity AT GREAT RISK.

Use the SAP FM above to make sure that the DB is not damaged.

[Former Member]

Hi,

when I executed the report I got 8 records, I Checked 1 of it and pressed Blockremov, it is not going in to the loop.

Please help.

when 'BLOCKREMOV'.

LOOP AT IT_OUTPUT WHERE CHECK = 'X'.

UPDATE LIKP SET LIFSK = ' '

WHERE VBELN = IT_OUTPUT-VBELN.

ENDLOOP.

sy-lsind = sy-lsind - 1.

PERFORM DISPLAY_DATA.

endcase.

Thanks,

Veni.

[Former Member]

Hi

Check in debug if there are any records with CHECK = 'X'.

You can directly loop only on those records where CHECK - 'X'. and update table.

Regards,

Raj

[Former Member]

Hi

Remember to use FM for updation, instead of direct table update. (We shouldnt do direct update)

Coming to your problem, check in debug what is happening to the check field of selected row in PAI. Guess, you are loosing the checked record.

Regards,

Raj

[Former Member]

Hi,

I did this, but still not going in to loop stmt.

when 'BLOCKREMOV'.

DATA: vbkok LIKE vbkok.

LOOP AT IT_OUTPUT WHERE CHECK = 'X'.

vbkok-vbeln_vl = it_output-vbeln.

vbkok-kzlsp = 'X'.

vbkok-lifsk = ' '.

CALL FUNCTION 'WS_DELIVERY_UPDATE'

EXPORTING

vbkok_wa = vbkok

delivery = it_output-vbeln

EXCEPTIONS

OTHERS = 1.

COMMIT WORK.

ENDLOOP.

sy-lsind = sy-lsind - 1.

PERFORM DISPLAY_DATA.

endcase.

Thanks,

Veni.

[Former Member]

Hi,

This is my entire program, am I doing anything wrong. Please help.

Thanks

Veni.

REPORT ZSDR_DELIBLOCK
       no standard page heading line-size 255.

TABLES: LIKP.

DATA: Begin of IT_LIKP occurs 0,
      VBELN like LIKP-VBELN,
      LFDAT like LIKP-LFDAT,
      LIFSK LIKE LIKP-LIFSK,
      KUNNR like LIKP-KUNNR,
      KNKLI LIKE LIKP-KNKLI,
      NETWR like LIKP-NETWR,
      End of IT_LIKP.

DATA: Begin of IT_OUTPUT occurs 0,
      CHECK TYPE C,
      VBELN like LIKP-VBELN,
      LFDAT like LIKP-LFDAT,
      LIFSK LIKE LIKP-LIFSK,
      KUNNR like LIKP-KUNNR,
      KNKLI LIKE LIKP-KNKLI,
      NETWR like LIKP-NETWR,
      End of IT_OUTPUT.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: S_LFDAT FOR LIKP-LFDAT,
                S_KUNNR FOR LIKP-KUNNR.
PARAMETERS: P_LIFSK TYPE LIKP-LIFSK DEFAULT '01'.

SELECTION-SCREEN END OF BLOCK b1.

at selection-screen output.

  loop at screen.
    if screen-name = 'P_LIFSK'.
      screen-input = 0.
      modify screen.
    endif.
  endloop.

start-of-selection.
  PERFORM GET_DATA.
  PERFORM PROCESS_DATA.
  PERFORM DISPLAY_DATA.

at user-command.
  case sy-ucomm.
    when 'SELALL'.
      IT_OUTPUT-CHECK = 'X'.
      MODIFY IT_OUTPUT
      TRANSPORTING CHECK
      WHERE CHECK = ' '.
      sy-lsind = sy-lsind - 1.
      PERFORM DISPLAY_DATA.

    when 'DESALL'.
      IT_OUTPUT-CHECK = ' '.
      MODIFY IT_OUTPUT
      TRANSPORTING CHECK
      WHERE CHECK = 'X'.
      sy-lsind = sy-lsind - 1.
      PERFORM DISPLAY_DATA.

    when 'BLOCKREMOV'.
      DATA: vbkok LIKE vbkok.

      LOOP AT IT_OUTPUT WHERE CHECK = 'X'.
        vbkok-vbeln_vl = it_output-vbeln.
        vbkok-kzlsp = 'X'.
        vbkok-lifsk = ' '.

        CALL FUNCTION 'WS_DELIVERY_UPDATE'
             EXPORTING
                  vbkok_wa = vbkok
                  delivery = it_output-vbeln
             EXCEPTIONS
                  OTHERS   = 1.

        COMMIT WORK.
      ENDLOOP.
      sy-lsind = sy-lsind - 1.
      PERFORM DISPLAY_DATA.
  endcase.


FORM get_data.
  SELECT VBELN LFDAT LIFSK KUNNR KNKLI NETWR
         FROM LIKP
         INTO TABLE IT_LIKP
         WHERE LFDAT IN S_LFDAT
            AND KUNNR IN S_KUNNR
            AND LIFSK = P_LIFSK.
ENDFORM.                    " get_data


FORM process_data.
  loop at it_LIKP.
    if sy-subrc = 0.
      move-corresponding it_LIKP to it_output.
      append it_output.
      clear it_output.
    endif.
  endloop.
ENDFORM.                    " process_data



FORM DISPLAY_DATA.

  format color col_heading intensified on.
  write:    /2 'Report ID  :',
            14 sy-repid,
            72 text-ttl,
            133 'Page:', '    ',
            sy-pagno left-justified.
  write:    /2 text-h12,
            14 sy-tcode,
            133 'Date:', sy-datum, ''.
  write:    /133 'Time:', sy-uzeit, '  ',
            73 'As Of:',
            80 sy-datum.
  new-line.
  uline.
  format color col_heading intensified off.

  format color col_group intensified on.
  set pf-status 'ZSTAT01'.
  write:/1 'Chk',
           5 'Delivery',
           18 'Delivery date',
           36 'Deli Block',
           48 'Ship-to party',
           65 'Cred. acct',
           86 'Net value      '.
  uline.
  format color col_group intensified off.

  loop at it_output.
    write:/1 it_output-CHECK as checkbox,
           5  it_output-VBELN,
          20 it_output-LFDAT,
          39 it_output-LIFSK,
          51 it_output-KUNNR,
          67 it_output-KNKLI,
          80 it_output-NETWR.
  endloop.
  uline.
ENDFORM.                    " DISPLAY_DATA

[Former Member]

Hi

Do you find the 'X' in the CHECK field of the selected rows of internal table? Is it a Char type field of length 1?

Strange that you say, there is an 'X', but control doesnt come into loop body...how come this can happen....

Regards,

Raj

[Former Member]

Hi Raj,

It is a char type field of length 1. when I select 1 of the records, there is a Right symbol in it.

Thanks,

Veni.

[Former Member]

Hi,

In the output, I noticed on the tool bar next to Tcode window, the save, left arrow, exit and cacel buttons are deactivated.

Am I doing anything wrong.

Thanks

Veni.

[Former Member]

Hi,

Check your modified code..

REPORT zsdr_deliblock

NO STANDARD PAGE HEADING LINE-SIZE 255.

TABLES: likp.

DATA: BEGIN OF it_likp OCCURS 0,

vbeln LIKE likp-vbeln,

lfdat LIKE likp-lfdat,

lifsk LIKE likp-lifsk,

kunnr LIKE likp-kunnr,

knkli LIKE likp-knkli,

netwr LIKE likp-netwr,

END OF it_likp.

DATA: BEGIN OF it_output OCCURS 0,

check TYPE c,

vbeln LIKE likp-vbeln,

lfdat LIKE likp-lfdat,

lifsk LIKE likp-lifsk,

kunnr LIKE likp-kunnr,

knkli LIKE likp-knkli,

netwr LIKE likp-netwr,

END OF it_output.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_lfdat FOR likp-lfdat,

s_kunnr FOR likp-kunnr.

PARAMETERS: p_lifsk TYPE likp-lifsk DEFAULT '01'.

SELECTION-SCREEN END OF BLOCK b1.

AT SELECTION-SCREEN OUTPUT.

LOOP AT SCREEN.

IF screen-name = 'P_LIFSK'.

screen-input = 0.

MODIFY SCREEN.

ENDIF.

ENDLOOP.

START-OF-SELECTION.

PERFORM get_data.

PERFORM process_data.

PERFORM display_data.

AT USER-COMMAND.

DATA: v_index TYPE syindex.

CASE sy-ucomm.

WHEN 'SELALL'.

DESCRIBE TABLE it_output.

DO sy-tfill TIMES.

v_index = sy-index + 6.

MODIFY LINE v_index FIELD VALUE it_output-check FROM 'X'.

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

WHEN 'DESALL'.

DESCRIBE TABLE it_output.

DO sy-tfill TIMES.

v_index = sy-index + 6.

MODIFY LINE v_index FIELD VALUE it_output-check FROM ' '.

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

WHEN 'BLOCKREMOV'.

DATA: vbkok LIKE vbkok.

DATA: v_check LIKE it_output-check.

DATA: v_vbeln LIKE it_output-vbeln.

DESCRIBE TABLE it_output.

DO sy-tfill TIMES.

CLEAR: v_check,

v_vbeln.

v_index = sy-index + 6.

READ LINE v_index FIELD VALUE it_output-check INTO v_check

it_output-vbeln INTO v_vbeln.

IF sy-subrc <> 0.

EXIT.

ENDIF.

CHECK v_check = 'X'.

vbkok-vbeln_vl = v_vbeln.

CALL FUNCTION 'WS_DELIVERY_UPDATE'

EXPORTING

vbkok_wa = vbkok

delivery = v_vbeln

EXCEPTIONS

OTHERS = 1.

COMMIT WORK.

ENDDO.

ENDCASE.

----

---

• FORM get_data *

----

---

• ........ *

----

---

FORM get_data.

SELECT vbeln lfdat lifsk kunnr knkli netwr

FROM likp

INTO TABLE it_likp

WHERE lfdat IN s_lfdat

AND kunnr IN s_kunnr

AND lifsk = p_lifsk.

ENDFORM. " get_data

----

---

• FORM process_data *

----

---

• ........ *

----

---

FORM process_data.

LOOP AT it_likp.

IF sy-subrc = 0.

MOVE-CORRESPONDING it_likp TO it_output.

APPEND it_output.

CLEAR it_output.

ENDIF.

ENDLOOP.

ENDFORM. " process_data

----

---

• FORM DISPLAY_DATA *

----

---

• ........ *

----

---

FORM display_data.

FORMAT COLOR COL_HEADING INTENSIFIED ON.

WRITE: /2 'Report ID :',

14 sy-repid,

72 text-ttl,

133 'Page:', ' ',

sy-pagno LEFT-JUSTIFIED.

WRITE: /2 text-h12,

14 sy-tcode,

133 'Date:', sy-datum, ''.

WRITE: /133 'Time:', sy-uzeit, ' ',

73 'As Of:',

80 sy-datum.

NEW-LINE.

ULINE.

FORMAT COLOR COL_HEADING INTENSIFIED OFF.

FORMAT COLOR COL_GROUP INTENSIFIED ON.

SET PF-STATUS 'ZSTAT01'.

WRITE:/1 'Chk',

5 'Delivery',

18 'Delivery date',

36 'Deli Block',

48 'Ship-to party',

65 'Cred. acct',

86 'Net value '.

ULINE.

FORMAT COLOR COL_GROUP INTENSIFIED OFF.

LOOP AT it_output.

WRITE:/1 it_output-check AS CHECKBOX,

5 it_output-vbeln,

20 it_output-lfdat,

39 it_output-lifsk,

51 it_output-kunnr,

67 it_output-knkli,

80 it_output-netwr.

ENDLOOP.

ULINE.

ENDFORM. " DISPLAY_DATA

Thanks,

Naren

[Former Member]

Hi Naren,

Now it is saying 'Specified table not recognized'. While assigning do I have to assign vbkok to different table.

Thanks

Veni.

[Former Member]

Hi,

VBKOK is already declared..

DATA: vbkok LIKE vbkok.

The code which I gave is working fine for me..Did you add any additional code??

Thanks,

Naren

[Former Member]

Hi Naren,

No, I copied the same code. When I execute it_output is comming up with 8 records and I checked 1 record and pressed Blockremov then it said ' Delivery 50614030 does not exist.' I went in to the table and checked it, the delivery number is there in it.

Then for samething I debuged it, then it said'specified table name not recognized', at line CALL FUNCTION 'WS_DELIVERY_UPDATE'.

Thanks

Veni.

[Former Member]

Hi,

CHeck the new code for 'BLOCKREMOV'.

WHEN 'BLOCKREMOV'.

DATA: vbkok LIKE vbkok.

DATA: v_check LIKE it_output-check.

DATA: v_vbeln LIKE it_output-vbeln.

DESCRIBE TABLE it_output.

DO sy-tfill TIMES.

CLEAR: v_check,

v_vbeln.

v_index = sy-index + 6.

READ LINE v_index FIELD VALUE it_output-check INTO v_check

it_output-vbeln INTO v_vbeln.

IF sy-subrc <> 0.

EXIT.

ENDIF.

CHECK v_check = 'X'.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = v_vbeln

IMPORTING

OUTPUT = v_vbeln

.

vbkok-vbeln_vl = v_vbeln.

vbkok-kzlsp = 'X'.

vbkok-lifsk = ' '.

CALL FUNCTION 'WS_DELIVERY_UPDATE'

EXPORTING

vbkok_wa = vbkok

delivery = v_vbeln

EXCEPTIONS

OTHERS = 1.

COMMIT WORK.

Please let me know if you have any questions..

Thanks,

Naren

[Former Member]

Hi Naren,

Thank you. Now the block is removed for the choosen delivery num. But 2 more problems.

1) After removing the block , the screen is not refreshing.

2) If I select, SELALL or DESALL, All the records are replacing with first record(7 times same first record), with cheked or unchecked.

please help me out resolve above 2 problems.

Thanks

Veni.

[Former Member]

Veni - directly updating SAP tables is playing with fire. When Standard SAP functions update the tables, normally other tables are updated as well. You would be far better off to consider one of these:

BAPI_INB_DELIVERY_CHANGE       Change inbound delivery             
BAPI_OUTB_DELIVERY_CHANGE      BAPI for Change to Outbound Delivery

Rob

[Former Member]

Hi,

Check this code ...For the AT USER-COMMAND...

AT USER-COMMAND.

DATA: v_index TYPE syindex.

CASE sy-ucomm.

WHEN 'SELALL'.

DESCRIBE TABLE it_output.

DO sy-tfill TIMES.

v_index = sy-index + 6.

DATA: wa LIKE it_output.

READ LINE v_index FIELD VALUE it_output-check.

MODIFY LINE v_index FIELD VALUE it_output-check FROM 'X'.

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

WHEN 'DESALL'.

DESCRIBE TABLE it_output.

DO sy-tfill TIMES.

v_index = sy-index + 6.

READ LINE v_index FIELD VALUE it_output-check.

MODIFY LINE v_index FIELD VALUE it_output-check FROM ' '.

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

WHEN 'BLOCKREMOV'.

DATA: vbkok LIKE vbkok.

DATA: v_check LIKE it_output-check.

DATA: v_vbeln LIKE it_output-vbeln.

DESCRIBE TABLE it_output.

DO sy-tfill TIMES.

CLEAR: v_check,

v_vbeln.

v_index = sy-index + 6.

READ LINE v_index FIELD VALUE it_output-check INTO v_check

it_output-vbeln INTO v_vbeln.

IF sy-subrc <> 0.

EXIT.

ENDIF.

CHECK v_check = 'X'.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = v_vbeln

IMPORTING

output = v_vbeln.

vbkok-vbeln_vl = v_vbeln.

vbkok-kzlsp = 'X'.

vbkok-lifsk = ' '.

CALL FUNCTION 'WS_DELIVERY_UPDATE'

EXPORTING

vbkok_wa = vbkok

delivery = v_vbeln

EXCEPTIONS

OTHERS = 1.

COMMIT WORK.

DELETE IT_OUTPUT WHERE VBELN = v_vbeln.

ENDDO.

PERFORM display_data.

ENDCASE.

Hope this helps..

Thanks,

Naren

[Former Member]

Thank you everyone for your help.

Rob, I will definitely consider your suggestion, actually when my lead gave this req, I told him that I will do it with BDC or BAPI, then he told me to directly update the table with select stmts. I will show this to him and tell the consequences also. Thanks a lot.

Thank you Naren. It is working fine now. I really appreciate your help.

Regards,

Veni.

[Former Member]

Hi,

You are using FM WS_DELIVERY_UPDATE to remove the block and not using the direct table update..You don't have to change the code again for a BAPI..

THanks,

Naren

[Former Member]

Naren - The problem with the FM is that it is neither documented nor released by SAP as shown in the 4.7 attributes. This means that SAP does not support it for user use. While it may work, there is no guarantee that in the future SAP won't change it substantially or remove it entirely.

There's also no guarantee that it is an entire LUW. Many SAP function modules (I don't know about this one) have to be run in a sequence to form the LUW.

The BAPI is an entire LUW and SAP will support it in future releases.

Rob