SAP Solutions: April 2017

Saturday, April 22, 2017

Insert/Update data into Database Table

1. Inserting Lines into Tables

The Open SQL statement for inserting data into a database table is:

INSERT INTO target lines.

It allows you to insert one or more lines into the database table target. You may specify the database table target either statically or dynamically.

Specifying a Database Table

To specify the database table statically, enter the following for target:

INSERT INTO dbtab [CLIENT SPECIFIED] lines.

where dbtab is the name of a database table defined in the ABAP Dictionary.

To specify the database table dynamically, enter the following for target:

INSERT INTO (name) [CLIENT SPECIFIED] lines.

where the field name contains the name of a database table defined in the ABAP Dictionary.

You can use the CLIENT SPECIFIED addition to disable automatic client handling.

Inserting a Single Line

To insert a single line into a database table, use the following:

INSERT INTO target VALUES wa.

You can also insert single lines using the following shortened form of the INSERT statement:

INSERT target FROM wa.

Using FROM instead of VALUE allows you to omit the INTO clause.

Inserting Several Lines

To insert several lines into a database table, use the following:

INSERT target FROM TABLE itab [ACCEPTING DUPLICATE KEYS].

This writes all lines of the internal table itabto the database table in one single operation. If one or more lines cannot be inserted because the database already contains a line with the same primary key, a runtime error occurs. You can prevent the runtime error occurring by using the addition ACCEPTING DUPLICATE KEYS.

Whenever you want to insert more than one line into a database table, it is more efficient to work with an internal table than to insert the lines one by one.

Examples

Adding single lines

TABLES spfli.

DATA wa TYPE spfli.

wa-carrid = 'LH'.
wa-cityfrom = 'WASHINGTON'.
...
INSERT INTO spfli VALUES wa.

wa-carrid = 'UA'.
wa-cityfrom = 'LONDON'.
...
INSERT spfli FROM wa.

spfli-carrid = 'LH'.
spfli-cityfrom = 'BERLIN'.
...
INSERT spfli.

This program inserts a single line into the database table SPFLI using each of the three possible variants of the INSERTstatement.

Instead of

INSERT spfli.

in the last line, you could also use the longer forms

INSERT spfli FROM spfli

INSERT INTO spfli VALUES spfli

here. The name SPFLI is therefore not unique.

These variations of the INSERT addition only work with table work areas that have been declared using TABLESand should therefore no longer be used.

DATA: itab TYPE HASHED TABLE OF spfli
WITH UNIQUE KEY carrid connid,
wa LIKE LINE OF itab.

wa-carrid = 'UA'. wa-connid = '0011'. wa-cityfrom = ...
INSERT wa INTO TABLE itab.

wa-carrid = 'LH'. wa-connid = '1245'. wa-cityfrom = ...
INSERT wa INTO TABLE itab.

wa-carrid = 'AA'. wa-connid = '4574'. wa-cityfrom = ...
INSERT wa INTO TABLE itab.

...

INSERT spfli FROM TABLE itab ACCEPTING DUPLICATE KEYS.

IF sy-subrc = 0.
...
ELSEIF sy-subrc = 4.
...
ENDIF.

This example fills a hashed table itab and inserts its contents into the database table SPFLI. The program examines the contents of sy-subrc to see if the operation was successful.

2. Updating Lines into Tables

Change a specific field in an SAP database table
Changing a values within a database table using the UPDATE command is very useful function to perform. Although if you want to update a number of fields in the table you may want to use the MODIFY command.

data: ld_ebeln type ekko-ebeln.

update EKKO set ERNAM = sy-uname
 where ebeln = ld_ebeln.

Before/After inserting/updating database table, you must lock/unlock database table using lock object (se11) or FM ENQUEUE_E_TABLE/DEQUEUE_E_TABLE

Printter Configuration (SPAD)

Every printer in transaction SPAD has a "device type" assigned. Device types used by the spooler for printing support only one single specific output character set. All text from the form has to be converted (using SAP's built-in character conversion mechanism) to this output character set.

A character set can typically support either a single language (e.g. Shift-JIS which supports only Japanese) or a set of languages (e.g. ISO 8859-1, which supports Western-European languages). It is possible that a given language (such as German) can be supported by several output character sets, e.g. you may use either ISO 8895-1 (Latin-1) or ISO 8859-2 (Latin-2) to represent German text. This is so because both character sets contain the special characters used in German.

Example: HPLJ4000 is a HP LaserJet device type supporting the ISO 8859-1 (Latin-1) character set. ISO 8859-1 can be used to represent e.g. Dutch, English, French, German, Spanish, Swedish but NOT Russian or Japanese.

As a consequence, it is ok to use HPLJ4000 to print English, German French etc. but not for Japanese or Russian.

Notes:
The spool information is stored in the database table TSP03D (Spool: Device description)

If you export to PDF file, you should choose the printer with device type as 'PDFUC' (I solved the Turkish characters problem when exporting to pdf)

Thursday, April 20, 2017

Designing a template in Smartform

Step1. Go to Tx- SMARTFORMS and create a smartform and double click on the main window.

Step2. Navigate along the highlighted path to create a template.

Step3. Click on the Details.

Step4. The below screen appears.

Step5. Provide a name as T1 .The total width of the template is 20 CM. Provide a width of 5 cm for each column and from and to [1 to 3]. Means we have 3 fixed rows with each row having 4 columns of 5 cm width.

Step6. Click on Table painter.

Step7. Click on the Pattern button.

Step8. Click on Display Framed Pattern button.

Step9. Double click on the required pattern.

Step10. The template looks like as below. Activate the form and test it.

Step11. Choose the test button.

Step12. Click on the Execute button.

Step13. Click on the Print preview button.

Step14. So here is how the template appears. Go back.

Step15. Now we have to provide values or texts to each cell of the template. Right click on the template and create ->text.

Step16. Provide the text as RED under general attributes tab and then go to Output Options tab.

Step17. So here you provide the line number and column number.

Step18. Similarly create some more texts under template and provide the text and the line number and column number for each text.

Step19..Activate the form and test it and at last we have the below output of the template.

---------------------------------------------------------------------------------------------------------------------------------

Step-by-step approach in creating a PO Layout using Smart Forms

By Nageshwar, Satyam Computers

Smart Forms:

Transaction Code for Create Smart Forms: SMARTFORMS

Transaction Code for Create Styles (Paragraph Formats, Character Formats, etc.,) Used in Smart Forms: SMARTSTYLES

Steps to be followed while creating Smart Forms:

1. Enter the Form Name (e.x. ZEKKO_EKPO) and Press Create Button.

2. Enter Form Description and Select the “Output options” Tab, then enter the Page Format (e.x. DINA4) and Style (e.x.SF_STYLE_01).

3. Double click the Form interface Left Sub-tree.

4. Enter the Import Parameters (The values that passed from the Print Program into the Form) (e.x. Company Code)

Parameter name Type assignment Reference type Default value Pass value

COMPANY_CODE TYPE EKKO-BUKRS R

5. Double click the Global definitions from the Left Sub-tree.

Assume that we are using two tables EKKO and EKPO in this example:

6. Click the “Types” Tab.

7. Enter the Code as following:

8. Click the “Global data” Tab.

Declare the Internal Tables and Work Areas as follows:

9. Double click the “%PAGE1 New Page” in the Left Sub-tree.

10. Change the %PAGE1 to “FIRST” or any name and Enter the Description (e.x. First Page).

11. Select the Next Page as “FIRST”

12. Select the “Output options” Tab and select the format of the page (Portrait or Landscape) and all other print attributes.

13. Select the “Background Picture” Tab, and Enter the Name, Object and Id of the image file. (Before this step, go to SE78 and import the image first) for the Image which has to be appeared in the Background of the Script.

14. If you want to add any LOGO in the First Page of the Script then right click the “FIRST New Page” in the Left Sub-tree from the Menu option “Create” à select the “Graphic”.

15. Double click the “%Graphic1 New Graphic 1” in the Left Sub-tree.

16. Give the Name and Description (e.x. Graphic = “COMP_LOGO” and Description = “LOGO WINDOW”) for the New Graphic Inserted.

17. Enter Name, Object and ID for the Graphic image and Select the Color grid screen (BCOL) option button in the “General attributes” Tab.

Click - Output options Tab. Set Left and Upper Margins as given:

18. Right click the “FIRST New Page” in the Left Sub-tree and from the Menu option “Create” and select the “Window”.

Double click the “%WINDOW1 New Window1” in the Left Sub-tree and enter the Window name and Description (e.x. Window = “ADD_WIN” and Description = “Address Window”)

Set the Left and Upper margins for the ADD_WIN as follows:

19. Right click “ADD_WIN Address Window” in Left Sub-tree and from the Menu option “Create” and select the “Program Lines”.

20. Enter the Program Lines name and description (e.x. Program Lines = “ADD_FETCH” and Description = “FETCHING COMPANY ADDRESS”)

21. Enter the Input Parameter as COMPANY_CODE and Export Parameter as V_ADRNR in the “General attributes” Tab and write the select query as following:

SELECT SINGLE ADRNR

INTO V_ADRNR

FROM T001

WHERE BUKRS EQ COMPANY_CODE.

(Note: Before writing the select query first declare the Variable V_ADRNR in the Global definitions area as follows:

V_ADRNR TYPE T001-ADRNR.

22. Right click the “ADD_WIN Address Window” in the Left Sub-tree and from the Menu option “Create” àselect the “Address”.

23. Double click the “%ADDRESS1 New address1” and change the Address name and description (e.x. Address = COMP_ADD and Description = “COMPANY ADDRESS”).

24. Select the Type of the address in the “General attributes” Tab. (Since we are retrieving the Organization address, we selected the first radio button) and in the Address number text box pass the V_ADRNR Variable as &V_ADRNR&.

25. Set the Main window attributes like this: (Setting of window co-ordinates)

26. Right click the “MAIN Main Window” in the Left Sub-tree and from the Menu option “Create” à select the “Program Lines”.

27. Enter the name and description (e.x. Program Lines = “SELECT_RECORD” and Description = “SELECTING RECORDS”).

28. Enter the Input Parameter as COMPANY_CODE and Output Parameter as I_EKKO and I_EKPO in the “General attributes” Tab and write the select query as following:

SELECT EBELN

BUKRS

INTO TABLE I_EKKO

FROM EKKO

UP TO 10 ROWS

WHERE BUKRS EQ COMPANY_CODE.

IF SY-SUBRC EQ 0.

SORT I_EKKO BY EBELN.

SELECT EBELN

EBELP

MENGE

NETPR

INTO TABLE I_EKPO

FROM EKPO

FOR ALL ENTRIES IN I_EKKO

WHERE EBELN EQ I_EKKO-EBELN.

IF SY-SUBRC EQ 0.

SORT I_EKPO BY EBELN EBELP.

ENDIF.

29. Right click “MAIN Main Window” in the Left Sub-tree and from the Menu option “Create” à select the “Complex Section”.

30. Enter the Section name and description (e.x. Section = “PO_LOOP” and Description = “PURCHASE ORDER NUMBER LOOP”) and check the “Repeat processing” check box and select the “Output type” as Unstructured (i.e. select the Unstructured radio button) in the “General attributes” Tab.

31. Select the “Data” Tab.

32. Enter the Internal Table Name as I_EKKO and Work area name as WA_EKKO and sort by the BUKRS and EBELN fields.

33. Right click “PO_LOOP PURCHASE ORDER NUMBER LOOP” in Left Sub-tree and from the Menu option “Create” and select the “Text”.

34. Enter the Text name and description (e.x. Text = “PO_NO_DISPLAY” and Description = “PURCHASE ORDER NUMBER DISPLAY”) and enter the values as in the screen shot

Click the Text Editor

Display the values of Company Code and Purchase Order Number as shown below:

Press Back Button, then Press Save and Check Buttons

35. Right click the “PO_LOOP PURCHASE ORDER NUMBER LOOP” in the Left Sub-tree and from the Menu option “Create” and select the “Template”.

36. Double click on “%TEMPLATE1 New template” and enter the Template name and description (e.x. Template Name = “OI_HEAD” and Description = “ORDER ITEM HEADER”).

Before press save or enter click the “Table Painter Button” which will take you to following screen.

To avoid the “cross line” in the Editor click the “Settings Button”

which will result in the following popup, in which you un-check the “Cross” Check Box and press enter.

37. Split the Cell into Four or into any number of columns you want to display in the script by right clicking the “%C1”.

and press “Back” Button.

38. Select the cv cv of the table to be displayed in the script by the clicking “Select pattern” Button

Selected table pattern

39. Inorder to Display the “Column Text“ for the table columns, insert the Text as shown below:

40. Right click the “OI_HEAD ORDER ITEM HEADER” and from the Menu path “Create” à Select the “Text” .

41. Enter the Text name and description (e.x. Text = “HEAD1” and Description = “COLUMN1”) and enter the values as in the screen shot (i.e. as explained in the point no 34).

42. In order to match the Template Column with the inserted Text “HEAD1 COLUMN1”, select the “Output options” Tab and Enter the Line Number and Column Number in which the above Text has to be printed in the form

Repeat Step No.40 & 41 for all the remaining column headings.

43. The Form is ready with all data upto Table Header. Inorder to populate data under each column header, insert a table.

44. Insert the Table under “PO_LOOP PURCHASE ORDER NUMBER LOOP” by right clicking the same.

45. Enter Table Name and Description (e.x. Table Name = “OI_DISPLAY” and Description = “ORDER ITEM DISPLAY”)

46. Match the column width with the “OI_HEAD ORDER ITEM HEADER” Template (refer step no 38 screet shot no 2) and Select the “Default” radio button and “No page break” check box. Click the “Select pattern” push button to select the table pattern to be displayed.

47.

Click the “Data” and populate the Item details by looping the Internal Table I_EKPO into the Work Area WA_EKPO sorted by EBELN and EBELP as shown in the following screen shot

48. Inorder to Display the values for each column, we have to Insert Separate Text for each column under the Table “OI_DISPLAY ORDER ITEM DISPLAY” by Right clicking the same. Enter the Name and Description for the Each Text Inserted under the Table.

Column 1 for Line Item of each Purchase Order – Populate the values for each column as explained in the step no 34.

Inorder to Match the Item values with the Template Header, In the Inserted Text “ORD_ITM_COL COLUMN1” Click the “Output options” and Select the “New line” Check box and Select the “Line type” as “Detail” and also check the “New cell” Check box.

49. Similarly add one Text for the remaining columns.

For the Third Column Text also You need only to select the “New cell” Check box.

50. Before Inserting the 4th Text for the 4th Column, we have to calculate the Amount by Multiplying the Quantity and Unit Price and for the same we have to insert “Program Lines” by Right clicking “OI_DISPLAY ORDER ITEM DISPLAY”

51. Similarly calculate the subtotal by adding the totals and calculate the grand total by adding the subtotals.

Before doing the calculations, declare variables for total, subtotal and grand total in Global definitions.

InputParameters: WA_EKPO-MENGE,WA_EKPO-NETPR & OutputParameters: V_TOTAL,V_SUBTOTAL, V_GRDTOTAL.

52. Now add the 4th Text to display the 4th column in the Form.

For the Fourth Column Text also You need only to select the “New cell” Check box.

53. In order to display the Subtotals (which has been created earlier i.e. refer to step no 52) of all the Line Items for each Purchase Order, we have to Insert a separate Text Under the Table OI_DISPLAY ORDER ITEM DISPLAY” by right clicking the same.

54. Enter the Name and Description for the Inserted Text (e.x. Text = “SUB_TOTAL_DISPLAY” and Description = “SUB_TOTAL_DISPLAY” and populated the value for subtotal.

55. After each purchase order, the Variable V_SUBTOTAL has to be refreshed, In order the clear the subtotal variable we have to insert “Program Lines” by right clicking the “OI_DISPLAY ORDER ITEM DISPLAY”.

56. Enter Name and Description for the Program Lines (%CODE1 New program lines 1) Inserted (e.x. Program lines = “CLR_VAR” and Description = “CLEAR SUBTOTAL VARIABLE”. Input parameters = “V_SUBTOTAL”. Clear the variable as shown below:

57. Now, we have to display the Grand Total (i.e. summation of all the subtotals). Insert “Text” under the “PO_LOOP PURCHASE ORDER NUMBER LOOP” by right clicking the same.

Text “%TEXT1 New text1” gets inserted. Now enter the name and description for the same and also populated the value for grand total

Text = “GRD_TOT_DISP”, Description = “GRAND TOTAL DISPLAY” and Variable V_GRDTOTAL has been populated to display the grand total in the form.

58. Inorder to display the Current Page Number and Total Number of pages in the form we need a separate window and to obtain the same right click the “FIRST First Page” and the insert window.

Window “%WINDOW1 New window 1” get inserted under the first page.

59. Enter the Window name and description (e.x. Window = “PG_WIN” and Description “PAGE NUMBER WINDOW”)

Set the window co-ordinates for page number window “PG_WIN” as given below:

60. In order to display the page numbers we have to insert a “Text” by right clicking the “PG_WIN PAGE NUMBER WINDOW”.

Text “%TEXT1 New text 1” gets inserted and now enter the text name and description.

Text = “PG_DISP”, Description = “PAGE NUMBER DISPLAY” and Page Number gets displayed from the Transparent Table “SFSY”.

61. General Screen Shot of Entire Left Sub-tree:

62. Form output:

63. SOME MORE FURTHER MODIFICATIONS / ADDITIONAL OPTIONS IN THE ABOVE CREATED FORM:

Condition-1: Create second page and company logo should get printed only in the first page and not in the second page. Similarly, company address window should get printed only in the last page and not in other pages.

To create the second page right click the “FIRST First Page” in the left sub-tree.

64. Another page gets inserted with name “%PAGE1 New page 1”.

65. Enter Page name and description for the inserted new page (e.x. Page = “SECOND” and Description = “Second Page”) and set the next page as inserted second page and select the mode as increase counter as shown below:

66. Now change the “FIRST First Page”s Next page as “SECOND” and change the mode as Initialize counter.

67. Now copy the Main, Company Address, Page No windows from “FIRST First Page “ to “SECOND Second Page”.

68. Arrange all the windows under the Main window in both first and second page. In order to restrict the company logo to be printed only in the first page, go to the first page and double click the “LOGO_WIN LOGO WINDOW” and select the “Conditions” tab and Check the “only on first page” Check Box.

69. Output showing the main window got printed only in the first page and not in the second page and so on ….

First Page: With LOGO

Second Page: Without LOGO

70. In order to restrict the Company address to be printed only in the last page and not in the remaining pages, double click the “ADD_WIN ADDRESS WINDOW” in the first page and Check the “only after end of main window” Check Box. Repeat the same process for the second page also.

71. Output showing the address window got printed only in the last page and not in the first page and so on ….

First Page: without company address

Last page (second page): with company address window

72. Condition-2: Purchase order’s whose value is more than 5000 only should get displayed / printed.

Inorder to achieve the same, right click the “PO_LOOP PURCHASE ORDER NUMBER LOOP” and select the “Loop”.

73. “%LOOP1 New loop 1” gets inserted

74. Enter Loop name and description.

75. Select the “Data” Tab Loop the Internal I_EKPO as shown below:

76. In order to check the subtotal value for each purchase order (i.e. purchase order value should be more than 5000), Insert a program lines under the new loop inserted.

77. “%CODE1 New program lines 1” gets inserted

78. Enter the name and description for Program lines. Before calculating the subtotal declare two variables one for total and another for subtotal in Global definitions. Calculate the Subtotal as shown below:

Input parameters: “WA_EKPO-MENGE, WA_EKPO-NETPR, V_TOT”; Output Parameters: “V_VALUE”.

79. Now we calculated the subtotal for each purchase order. Inorder to restrict the display of purchase orders whose values are more than 5000, we need to insert “Alternate” and for the same right click the “PO_LOOP PURCHASE ORDER NUMBER LOOP” and from the menu path create à select the “Alternate”.

80. “%CONDITION1 New alternate 1” gets inserted.

81. Enter Alternate name and description. Select the “General attributes” Tab and Enter the condition of subtotal > 5000 as shown:

82. Now, move “PO_NO_DISPLAY PURCHASE ORDER NUMBER DISPLAY, OI_HEAD ORDER ITEM HEADER, OI_DISPLAY ORDER ITEM DISPLAY, SUB_TOTAL_DISPLAY DISPLAY SUBTOTAL and CLR_VAR New program lines” under the “TRUE” part.

83. In the “FALSE” part (i.e. If subtotal value is less than 5000, then clear the variable), insert program lines and code as shown:

84. Only those purchase order whose value is more than 5000 gets displayed – First Page.

Only those purchase order whose value is more than 5000 gets displayed – Second Page.

85. Condition-3 Each Purchase Order should get printed in a new page. Logo should get printed in the first page along with the first purchase order and similarly, Grand total should get printed in the last page with the last purchase order.

86. Inorder to achieve this, when the first successful purchase order whose value is more than 5000 is found then increase one counter variable by 1 (this is required because the “New page command” should not get triggered in the first page itself”) and for the same we have to insert a “Program lines” under the “TRUE” part of “CHK_COND SUBTOTAL CONDITION”.

Before increasing the counter variable first declare the counter variable in the “Global definitions” part.

Output parameters: V_COUNTER.

87. Inorder to avoid triggering “New page command” in the first page, we have to insert “Command” in the “TRUE” part of “CHK_COND SUBTOTAL CONDITION” by right clicking the same as shown below:

88. “%COMMAND1 New command 1” gets inserted.

89. Enter name and description for the inserted Alternative. Select “General attributes” Tab and check whether for the counter variable is more than 1.

90. If the counter variable is more than 1 means, we got the second successful purchase order whose value is more than 5000. Hence, we can trigger new page command here and for the same right click the “TRUE” part in “NEW_PG NEW PAGE COMMAND” as shown:

“%COMMAND1 New command 1” gets inserted

91. Enter the name and description for Command and select the “General attributes” Tab and Check the “Go to new page” and select the page as “SECOND” as shown below: