Nested INNER JOIN Statements May be Ineffective or inefficient | SAP Programming | ABAP

"Using several nested INNER JOIN statements can be inefficient and cause time out if the tables become too big in the future."

Joins here (in ABAP) are not those Native SQL Joins.  If you are talking about the Core RDBMS, which mean Oracle or SQL Server, then Undoubtedly Joins are the best.

In ABAP, these joins are first split by the ABAP processor and then sent to the database, with the increase in DATA in production system, these joins tend to give way if your database keeps growing larger and larger.

You should rather used "FOR ALL ENTRIES IN" (Tabular conditions), which is a much effecient way as far as performance is concerned.

For example :

DATA: BEGIN OF LINE,
        CARRID   TYPE SPFLI-CARRID,
        CONNID   TYPE SPFLI-CONNID,
        CITYFROM TYPE SPFLI-CITYFROM,
        CITYTO   TYPE SPFLI-CITYTO,
      END OF LINE,
      ITAB LIKE TABLE OF LINE.

LINE-CITYFROM = 'FRANKFURT'.
LINE-CITYTO   = 'BERLIN'.
APPEND LINE TO ITAB.

LINE-CITYFROM = 'NEW YORK'.
LINE-CITYTO   = 'SAN FRANCISCO'.
APPEND LINE TO ITAB.

SELECT CARRID CONNID CITYFROM CITYTO
INTO   CORRESPONDING FIELDS OF LINE
FROM   SPFLI
FOR ALL ENTRIES IN ITAB
WHERE  CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

  WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.

ENDSELECT.

read more...

Purpose of Using PACKAGE SIZE in Select Statement in SAP ABAP Programming

Package size can be used if you for example only want to finish processing a limited amount of data at a time due to lack of memory.

The example below read 50 records at a time from VBAK into an internal table, and selects the corresponding entries from vbap into an internal table. Then the two internal tables can be processed, and the next 50 records from VBAk can be read. Remember to reinitialize tha tables before the next read.



REPORT z_test.


TYPES:
BEGIN OF t_vbak,
vbeln LIKE vbak-vbeln,
erdat LIKE vbak-erdat,
END OF t_vbak,


BEGIN OF t_vbap,
posnr LIKE vbap-posnr,
matnr LIKE vbap-matnr,
meins LIKE vbap-meins,
END OF t_vbap,


BEGIN OF t_report,
vbeln LIKE vbak-vbeln,
erdat LIKE vbak-erdat,
posnr LIKE vbap-posnr,
matnr LIKE vbap-matnr,
meins LIKE vbap-meins,
END OF t_report.


DATA:
li_vbak TYPE t_vbak OCCURS 0,
l_vbak TYPE t_vbak,
li_vbap TYPE t_vbap OCCURS 0,
l_vbap TYPE t_vbap,
li_report TYPE t_report OCCURS 0,
l_report TYPE t_report.


START-OF-SELECTION.
SELECT vbeln erdat
FROM vbak
INTO TABLE li_vbak PACKAGE SIZE 50.
SELECT posnr matnr meins
FROM vbap
INTO TABLE li_vbap
FOR ALL ENTRIES IN li_vbak
WHERE vbeln = li_vbak-vbeln.
IF sy-subrc = 0.
* Now you have the two internal tables li_vbak and li_vbap filled
* with data.
* Do something with the data - remember to reinitialize internal
* tables
ENDIF.
ENDSELECT.

read more...

Usage of 'for all entries' in Select Statement | SAP ABAP Programming

FORM data_retrieval.

DATA: ld_color(1) TYPE c.

DATA: BEGIN OF T_VBAP OCCURS 0,
VBELN LIKE VBAP-VBELN,
MATNR LIKE VBAP-MATNR,
POSNR LIKE VBAP-POSNR,
END OF T_VBAP.

DATA: BEGIN OF T_VBFA OCCURS 0,
VBELV LIKE VBFA-VBELV,
VBELN LIKE VBFA-VBELN,
VBTYP_N LIKE VBFA-VBTYP_N,

END OF T_VBFA.

DATA: BEGIN OF T_VBAK OCCURS 0,
VBELN LIKE VBAK-VBELN,
IHREZ LIKE VBAK-IHREZ,
END OF T_VBAK.

DATA: BEGIN OF T_KNA1 OCCURS 0,
KUNNR LIKE KNA1-KUNNR,
NAME1 LIKE KNA1-NAME1,
END OF T_KNA1.

DATA: BEGIN OF T_MAKT OCCURS 0,
MATNR LIKE MAKT-MATNR,
MAKTX LIKE MAKT-MAKTX,
END OF T_MAKT.

SELECT likp~vbeln likp~lifex likp~bldat likp~wadat likp~wadat_ist likp~kodat likp~lfart
likp~kunnr likp~vstel lips~posnv lips~lfimg lips~vrkme lips~lgmng lips~meins
lips~werks lips~lgort lips~charg lips~vbelv lips~posnr lips~matnr
lips~vbeln LIPS~VGBEL LIPS~VGPOS vbup~kosta vbup~wbsta vbup~posnr vbup~vbeln
* VBAK~IHREZ VBAK~VBELN VBAP~VBELN
INTO CORRESPONDING FIELDS OF TABLE it_itab
FROM ( likp
INNER JOIN lips
ON lips~vbeln = likp~vbeln
INNER JOIN vbup
ON vbup~posnr = lips~posnr
and VBUP~VBELN = LIPS~VBELN )
* left outer join VBAK
* on VBAK~VBELN = LIPS~VGBEL
* inner join VBAP
* on VBAP~VBELN = VBAK~VBELN )
WHERE likp~vbeln IN so_vbeln
AND likp~lifex IN so_lifex
AND likp~lfart IN so_lfart
AND likp~kunnr IN so_kunnr
AND likp~vstel IN so_vstel
AND likp~bldat IN so_bldat
AND likp~wadat_ist IN so_wadat
AND vbup~kosta IN so_kosta
AND vbup~wbsta IN so_wbsta
AND LIPS~LFIMG NE 0.

SELECT VBELN IHREZ INTO TABLE T_VBAK
FROM VBAK
FOR ALL ENTRIES IN IT_ITAB
WHERE VBELN = IT_ITAB-VGBEL.
* APPEND T_VBAK.
* ENDSELECT.

SELECT VBELN MATNR POSNR INTO TABLE T_VBAP
FROM VBAP
FOR ALL ENTRIES IN IT_ITAB
WHERE VBELN = IT_ITAB-VGBEL AND
MATNR = IT_ITAB-MATNR AND
POSNR = IT_ITAB-VGPOS.
* APPEND T_VBAP.
* ENDSELECT.

SELECT VBELV VBELN VBTYP_N INTO TABLE T_VBFA
FROM VBFA
FOR ALL ENTRIES IN IT_ITAB
WHERE VBELV = IT_ITAB-VBELN AND
VBTYP_N = 'M' .

SELECT KUNNR NAME1 INTO TABLE T_KNA1
FROM KNA1
FOR ALL ENTRIES IN IT_ITAB
WHERE KUNNR = IT_ITAB-KUNNR.
* APPEND T_KNA1.
* ENDSELECT.

SELECT MATNR MAKTX INTO TABLE T_MAKT
FROM MAKT
FOR ALL ENTRIES IN IT_ITAB
WHERE MATNR = IT_ITAB-MATNR.
* APPEND T_MAKT.
* ENDSELECT.


*Populate field with color attributes

LOOP AT it_itab INTO wa_ITAB.

* Populate color variable with colour properties

* Char 1 = C (This is a color property)

* Char 2 = 3 (Color codes: 1 - 7)

* Char 3 = Intensified on/off ( 1 or 0 )

* Char 4 = Inverse display on/off ( 1 or 0 )

* i.e. wa_ekko-line_color = 'C410'
REFRESH color.
colourize 'VBELN' 0. " .
WA_ITAB-farbe = color[].

ld_color = ld_color + 1.

* Only 7 colours so need to reset color value

IF ld_color = 3. "8

ld_color = 1.

ENDIF.

CONCATENATE 'C' ld_color '10' INTO wa_ITAB-line_color.
WA_ITAB-NAME1 = ''.
WA_ITAB-MAKTX = ''.
WA_ITAB-IHREZ = ''.
WA_ITAB-VBELV = ''.
READ TABLE T_KNA1 WITH KEY KUNNR = WA_ITAB-KUNNR.
IF SY-SUBRC = 0.
WA_ITAB-NAME1 = T_KNA1-NAME1.
ENDIF.
READ TABLE T_MAKT WITH KEY MATNR = WA_ITAB-MATNR.
IF SY-SUBRC = 0.
WA_ITAB-MAKTX = T_MAKT-MAKTX.
ENDIF.
READ TABLE T_VBAK WITH KEY VBELN = WA_ITAB-VGBEL.
IF SY-SUBRC = 0.
WA_ITAB-IHREZ = T_VBAK-IHREZ.
ENDIF.
READ TABLE T_VBFA WITH KEY VBELV = WA_ITAB-VBELN.
IF SY-SUBRC = 0.
WA_ITAB-VBELVA = T_VBFA-VBELN.
ENDIF.
* READ TABLE T_VBAP WITH KEY VBELN = WA_ITAB-VGBEL
* POSNR = WA_ITAB-VGPOS
* MATNR = WA_ITAB-MATNR.
* IF SY-SUBRC = 0.
* WA_ITAB-IHREZ = T_VBAK-IHREZ.
* ENDIF.



* wa_ekko-line_color = 'C410'.

MODIFY it_itab FROM wa_itab.

ENDLOOP.

ENDFORM. " data_retrieval

read more...

"SY-UCOMM and OK_CODE in Dialog Program | SAP ABAP Programming

What is the difference between SY-UCOMM and OK_CODE in Dialog Program?

sy-ucomm is for doing the functions what the user wishes to do at that particular event. You use it in menus and other place . this mainly in using

ok_code is generally used in screen as of I have used.  You will define the function in the screen. and you can use it in the main program.

ok_code acts just as a temporary variable that stores the value of sy-ucomm.


When user interacts with the screen elements, the function code that you have assigned is filled in the sy-ucomm field which is turn gets reflected in OK_CODE.

In your ABAP programs, you should work with the OK_CODE field instead of SY-UCOMM. There are two reasons for this: Firstly, the ABAP program has full control over fields declared within it, and secondly, you should never change the value of an ABAP system field. However, you should also always initialize the OK_CODE field in an ABAP program for the following reason:

In the same way that the OK_CODE field in the ABAP program and the system field SY-UCOMM receive the contents of the corresponding screen fields in the PAI event, their contents are also assigned to the OK_CODE screen field and system field SYST-UCOMM in the PBO event. Therefore, you must clear the OK_CODE field in the ABAP program to ensure that the function code of a screen is not already filled in the PBO event with an unwanted value. This is particularly important when the next PAI event can be triggered with an empty function code (for example, using ENTER). Empty function codes do not affect SY-UCOMM or the OK_CODE field, and consequently, the old field contents are transported.
   

read more...

Asset Master Data Upload | SAP FICO | Financial Modules | The Financial Statement Closing Tcodes | LSMW

1. If the change of the field can be put into an algorithm (e.g., enter XYZ as a super number for all assets in class 1234), you can use a mass change rule. You first define the mass change rule in the same way as you would define an asset substitution (IF something, THEN something else), then you go into the mass change transaction, select the right assets, the proper change rule ... and hit Execute.

2. Very often, it is easier to prepare the data in Excel and use transaction AS02 for the upload. Here, I can recommend transaction LSMW - you simulate (and record) the AS02 transaction on one fixed asset master, then follow all the steps proposed by the system (define fixed values, variables, etc.) and finally upload the Excel (.csv). System will automatically generate a batch input for all assets. It may take some time for you if you do it for the first time, but it is the best way if you would otherwise need to define complex algorithms.

So - use option 1 if you have a simple algorithm for many assets, and option 2 if "each case is different" or if you feel more comfortable in Excel than ABAP. Option 2 is simple, but of course takes some time to generate the upload and then run the batch sessions (while option 1 changes everything instantly in a matter of seconds).

Here are the details:

I. To use mass change:

1. First, define a mass change rule in transaction OA02 (Accounting - Financial accounting - Fixed assets - Environment - Mass change rule). For each company code, you can define one rule (or a sequence of them). However, all of them will always be executed at the same time. Therefore, if you need to define different rules for different assets, you will have to do this repeatedly. Technically, this is done in the same way as AA substititution.

2. Once the rule is defined, you need to select assets for mass change. Go to transaction AR01 (Accounting - Financial accounting - Fixed assets - Environment - Worklist - Generate). Enter selection criteria like on any standard AA report, and run Execute. I cannot simulate this now on my system (we do not have AA), but it should ask what you want to do (mass change, mass retirement, etc.) and ask for a name of the worklist (write anything - e.g., Super number change). The system will show you a list of selected assets in a report format. Check it and make sure that it is correct. You may add or remove single assets from this (now cannot tell you how, but it is possible). If you are happy with the list, save it.

3. The last step is execution of the change. Go to transaction AR31 (same path as above), execute the report and if you are happy with the list (same as in step 2), press Save. System will show you a log of change (what went through, which errors occured). You are done.

II. LSMW
LSMW is a transaction, which will let you mass upload almost any other transaction (FB01, FD01, AS91, etc.). You do it in the following way:
1. Enter into transaction LSMW (no menu path), possibly in a development / test system
2. First, define a project, subproject and object (relatively formal task).
3. On the horizontal menu, go to Goto -> Recordings. Click on "Create recording".
4. Enter the name and description of recording (freely definable).
5. I cannot simulate this now in my system, but SAP will ask you about transaction code (AS02) and will take you into the AS02 transaction. In this step, do the change manually. Make sure you "touch" (change / write something) in all fields which you want to update.
6. Once you are done, save the recording and return on the start page of LSMW. Click "Execute".
7. You will see a list of tasks. Do them one by one. They should be relatively simple (and I cannot help you with them). Important is step 3 (maintain source fields) - here you define the columns of your excel (csv) file in the particular order. In step 5 (field mapping and conversion rule), you assign file columns to SAP fields (note that you will see only those fields which you "touched" during recording). You need to go through all the steps. In the end, you get (and run) a batch session.
8. If you did this in a development system, transport the LSMW object (menu Extras - Export project, Extras - Import project). You have to do it via file, not via standard transports of configuration (it is perhaps more simple).

Make sure you test it first in development or quality system!

Fiscal Year Variant

Assuming that you are newly implemention with go live date of Sept 1st 2006 and Fiscal year January - December, what will be the best way?
Would it be better to define a Shortened Fiscal year for Sept-Dec 2006 and then regular Fiscal years OR Define a full Fiscal year for the entire 2006 and future FYs

Normally, the best way is to keep it simple and leave the first year "normal" (12 + 4 months starting January). You convert old data as of August 31 (unless the client requests opening balance as of December 31, 2005 and then monthly movements - I have experienced both options) and then start normal bookings in month 9 on September 1.

We never tried shortened fiscal year in this context and it is surely not needed here. It just adds complexity.

If we create the normal Jan-Dec Fiscal Year for 2006 also, how will we handle the assets when we go live in Oct 2006? Do we load the book value at that time or we load the book value for January 2006 and depreciate in the system for January to September

The ordinary way is to load fixed assets which exist on September 30 (if you go live with AA module on October 1) with trans. AS91.
- First, you need to set in configuration that the last closed fiscal year for legacy data upload is 31.12.2005 and the last period for depreciation will be 9/2006. (Do it in IMG: Financial accounting - Asset accounting - Asset data transfer - Parameters for data transfer - Date specifications)

Within trans. AS91, you enter the following values:
- acquisition value and accummulated depreciation as of 01 January 2006
- all asset movements (acquisitions, retirements, but not ordinary depreciations) between January 1 and September 30 with their respective dates
- already posted depreciation in 2006 until September 30

Then you enter GL movements per month (either you temporarily change the asset reconciliation accounts for direct booking with trans. OAMK, then book with FB01 and finally reset with OAK5, or you book there directly with trans. OASV). This way, your asset accounts (acquisition value and accummulated depreciation) are the same as asset module as of September 30.

You can also let the system recalculate the depreciation from the beginning of the year, but then you may end up with a different value as of September 30 than your legacy system calculated. So, I would not recommend it unless there is no reliable source data. 

read more...

Process Fixed Asset Depreciation | Forecast | Financial Modules | FICO SAP

Every asset transaction immediately causes a change of the forecasted depreciation. However, it does not immediately cause an update of the depreciation and value adjustment accounts for the balance sheet and profit and loss statements. The planned depreciation is posted to the general ledger when you run the periodic depreciation posting run. This posting run uses a batch input session to post the planned depreciation for each posting level for each individual asset as a lump sum amount.

The calculation and scheduling of depreciation, interest and revaluation are automatically controlled by keys in the system, or you can control them manually using a special posting transaction. In both cases, planned depreciation from Asset Accounting must be periodically posted to the corresponding asset and expense accounts of the general ledger. You carry out this posting using a batch input session. In addition to the various depreciation types, interest and revaluation, this batch input session also posts the allocation and writing off of special reserves.

When the system posts depreciation, it creates collective documents. It does not create separate documents for each asset.

Depreciation Posting Run is done via transaction code 'AFAB'.

The program creates batch input sessions for posting depreciation and interest to the G/L accounts in Financial Accounting and/or to Controlling.

Fields Description :-

Company code  : Your Company Code

Fiscal Year   : Your fiscal year

Posting period : Your depreciation period

Reason for posting run (choose one)

Planned posting run  : X (default)

List assets : (tick if you want to see the detail)

Test run : (tick if you run in test mode else untick for production run)

Main asset number : (you can specify certain asset number if you click repeat run or test run)

Note :
Click the execute button if this is a test run.
Click the menu bar -> Program -> Execute in background if this is a production run.

You should get this message :
Background job was scheduled for program RABUCH00 and print out the output.

Release and Process the Batch Input Session in transaction code 'SM35'.

Process Session RABUCH00
X - Display errors only
Additional functions
X - Dynpro standard size

Click the Process button

If there is error, the system will pop up the error message.

Correct the error and recreate the session in transaction 'AFBD'.

read more...

PP - Controlling data for your Bill of Materials

You define the default values for BOM in OS28. - Based quantity - BOM Status - Size Unit - Var-size item unit You control the Material type allowed for the BOM header in transaction OS24. In transaction OS27, you define the central settings for your BOM management system. This are one-time settings for BOM management, which should not be change after production.

Low date When you create or process BOMs, the default date is the date defined in your system as the earliest processing date (01/01/1900 in the standard system). BOM validity maintenance Tick if want the user to specify a valid-from date when maintaining BOMs. If not tick the BOM created as of the current date or the system low date (1/1/1990). EC management active Tick if you want to make historical changes to BOMs with reference to a change number. (This means that the status of the BOM both before and after the change is stored in the system.) In this step, you only specify whether engineering change management can be used for maintaining bills of material. In transaction OS25, Configure history requirement for BOMs, you define whether you want to make the use of engineering change management a requirement in certain cases. Hist. reqmt variant Use this indicator to control when history requirement applies to new variants or alternatives in a BOM group. Sub-item documentn You use the Sub-item documentation indicator to define whether changes to sub-items are documented. Header Create BOM header with parameter validity TECHINCAL TYPE Mult. BOM inactive Tick if no multiple BOMs can be maintained in this R/3 system. Variant BOM inactive Tick if no variant BOMs can be maintained in this R/3 system BILL OF MATERIAL ITEM BOM item explosion type If you create a BOM item with reference to a change number and change the valid-from date (in the change header or alternative date), then this can change the sequence of validity periods of a BOM item. The indicator BOM item explosion type specifies whether and according to what rules, the system determines a unique change status, when you display, evaluate or change a BOM to a date in the overlap period. Description variable-sized item This indicator controls which text is displayed in BOM processing for a variable-sized item. Repeated effectivity via external item ID This indicator ensures that an item that already exists under an external item ID can be used. General settings Unit 'piece' In the standard system, this field contains a unit of measure for "piece". You can change this unit. This unit is the base unit of measure (BOM header) or the component unit of measure. Base unit of measure for: Equipment BOM Functional location BOM Document structure Standard BOM Component unit of measure for: Text item Document item Non-stock item without material master record CAD active If you want to maintain BOMs from a CAD system, set this indicator. This activates an additional field in the BOM header and BOM item, which tells you whether the BOM header or BOM item was maintained from a CAD system. If you deactivate engineering change management, changes to bills of material are not recorded. The modification parameters defined here apply to all BOM categories. The following settings are made in the standard R/3 System: 1. Validity date maintenance and engineering change management are active. 2. The current system date is the default valid-from date. 3. CAD is active. 4. Explosion type for BOM item: blank.

read more...