SAP ABAP: DATA RETRIVAL IN ABAP ERP,My SAP,ABAP,FICO,CRM,BW

Showing posts with label DATA RETRIVAL IN ABAP. Show all posts

SAP Data Warehousing Concepts

SAP Data Warehousing Concepts deals with data extraction and usage as per t the needs. In transaction processing, we are constantly filling specialized tables that are optimized for displaying the thousands of different steps of our business processes.For example, sales and distribution document items flow in from sales documents and quotation documents. These become delivery header data, which then becomes invoice header data and is sent on as accounts receivable information.The tables in the Business Information Warehouse are different in that they do not process transactions. Instead, all information for a specific business process in BW is gathered together and analyzed.

Data that has been loaded into BW from various source systems, is stored in BW in the form of star schemas. This type of table assignment is ideal for reporting purposes.The dimensions answer question such as "Who?" "What?" and "When?" The facts provide answers to questions such as; “how much money, how many people, how much did we pay”?

Example: Sales

Star Schema

InfoCubes produce a multi-dimensional data model on the database server of the Business Information Warehouse. This allows the facts to be grouped in separate fact tables, and the dimensions to be grouped in separate dimension tables. Both types of table are interconnected. Individual dimension values can be subdivided further into master data tables. In this way, master data tables, text tables, hierarchy data tables, and dimension tables, are grouped in a star- like formation around one central fact table. This structure is called an extended star schema. In an analysis, the data from the smaller tables at the edge of the star schema, is collected together first, before the corresponding data in the fact table is accessed using the keys.

Structuring information in a data warehouse according to this star schema guarantees the efficiency of the reporting process, and provides a flexible solution that can be easily adjusted to changing business requirements.When you create an InfoCube, you choose the key figures and characteristics that you are going to require in your analysis. You have to group your characteristics together in a time dimension, a unit dimension, and a number of other dimension categories. On the basis of your entries, the system generates a star schema on the database automatic ally.

SAP BW: Extended Star Schema

The BW extended star schema differs from the classic star schema. The BW star schema is divided into a solution-dependent part (fact tables and dimension tables = InfoCube) and a solution-independent part (master data tables, text tables, and hierarchy tables) that is also used by other InfoCubes.

Specific Characteristics of the BW Star Schema

When designing the dimensions of an InfoCube, you must put a lot of thought into which characteristics you are going to use, and how you are going to arrange them in the dimension table. This is an important part of the data modeling process, since the choices you make here have a significant impact on the size, performance, and usability of the InfoCube data. The attribute tables, hierarchy tables, and text tables are not included in the InfoCube. This means that this data is maintained separately, and can be used across different InfoCubes.

Master Data and InfoCubes

Attributes are fields that describe an InfoObject. These attributes are used to display additional information within a workbook to make the results more meaningful.You are not able to navigate with this attribute. An attribute-type master data table can be used by any InfoCube that accesses this

InfoObject.

MDM/Star Schema and BW

Common data warehouse terminology corresponds, for the most part, with BW terminology.In some cases, BW uses special terminology to describe various objects and processes.

Granularity of Data

Granularity is a term used to describe the level of detail.If data has a high level of granularity, it means that the data is highly detailed and there are many characteristics describing the key figures. The level of granularity by customer, for example, is less detailed than by customer, by material. The level of granularity that a set of data has determines how far you are able to drill-down on the data. Granularity also affects the size of the database. Data that is stored by customer, by month is much more summarized than by customer, by day, by document, by document line item. A year’s volume of data for the first case would therefore be much smaller than for the second.

From Data Model to Database

You use the term star schema, when you are talking about table structures conceptually, or from the perspective of data modeling. We talk about InfoCubes when we are referring to the actual set of tables where data is stored. In BW InfoCubes are used to create queries.

InfoCube

The fact table and the relevant dimension tables of an InfoCube are connected with one another by the dimension keys. A dimension key is provided by the system, one per characteristic combination in a dimension table.When you execute a query, the OLAP processor checks in the dimension tables of the InfoCube that you want to evaluate, to see if they contain the characteristic combinations required by the selection.The dimension keys determined in this way, lead you to the information you need in the fact table.

InfoCube: Multi-Dimensional Analysis

From the characteristics available in the dimensions of the InfoCube, you choose the characteristics that you want to use in your query.For these characteristics, a subset of data, called the Query Cache, is selected by the OLAP Processor and stored in the memory. This improves the query performance. From the data in the Query Cache, even more detailed views of the data can be generated. Filter values are used in the query to generate these more detailed views. This enables you to analyze specific divisions or regions.

Profitability management database stvructure

SAP Profitability management realignment

SAP integration planning cycle

SAP Business Information warehouse overview

SAP BW explorer

ERP Master Data and SAP Data Warehouse Concept

ERP has some basic data called master data and here in this post we are also going to discuss about data warehouse concept and what are the organization levels.On master data other dependent data will be available and it is called master data.

Organizational Parts : A company’s enterprise structure is mapped to SAP purposes using organizational elements. They are used to represent the enterprise construction by way of legal and enterprise-associated purposes. Organizational parts embrace authorized firm entities, crops, storage areas, gross sales workplaces, and profit centers. The simple examples are :

1.The highest-degree factor of all organizational components is the Client. The Consumer represents the enterprise orheadquarters group.

2. A Company Code is a unit included within the balance sheet of a legally-independent enterprise and is the central organizational aspect of Financial Accounting.

3. In the context of Sales and Distribution, the Sales Organization is the central organizational element that controls the terms of sale to the customer. Division is normally used to symbolize product line.

4. In the context of Production Planning, Plant is the central organizational unit. A Plant can manufacture product, distribute product, or provide a service.

5.In Inventory Administration, materials stocks could be differentiated inside one plant in response to Storage Location.

6.Organizational components may be assigned to a single software equivalent to Sales Organization assigned to Sales and Distribution, or to a number of purposes comparable to Plant assigned to Supplies Administration and Production Planning.

The highest-degree component of all organizational elements is the client. The shopper could be an enterprise group with several subsidiaries. All the enterprise knowledge in an SAP System implementation is break up into at least the consumer area, and often into decrease stage organizational constructions as well.

7.Versatile organizational elements in the SAP System allow extra complicated enterprise constructions to be represented. If there are rather a lot of organizational components, the authorized and organizational structure of an enterprise may be introduced in different views.

8.By linking the organizational elements, the separate enterprise areas will be integrated and the
construction of the entire enterprise represented in the SAP System. This links are outlined in Customizing.

9.When defining the organizational elements, bear in mind that they outline the construction for how data is to be entered, tracked, and extracted from the SAP system.

ERP Master Data: Knowledge which is used lengthy-time period within the SAP System for several business processes.Grasp information is created centrally and can be utilized by all functions and all approved users. Examples of master information in SAP embrace prospects, materials, and vendors.

1. A customer grasp comprises key data that defines the business relationship between a company and its customer. The master data is used to assist execution of key enterprise processes corresponding to customer requests, deliveries, invoices, and payments.

2. Master information additionally has an organizational side as the data is organized into views that are assigned to organizational elements.The customer master is organized into three views which are every situated at a unique organizational stage: Basic Data (Client), Monetary Accounting Information (Firm Code), and Sales Data (Sales Space).

3.Information on the shopper level can be utilized by all firm codes. The customer account number is assigned on this level. Meaning the same customer has an express accounts receivable number in all firm codes from a monetary view.

4.Transactions: Utility applications which execute business processes in the SAP System comparable to making a buyer order, posting an incoming cost, or approving a leave request.

5.Doc: A data document that is generated when a transaction is carried out.

6.When creating an order for a buyer, you need to take transport agreements, delivery and payment conditions, and so on, with business companions into consideration. To avoid re-entering this information every time for every activity associated to these business partners, relevant information for the activity from the master document of the enterprise companion is simply copied.

7.In the same method, the material grasp document stores info, such as the price per unit of amount, and inventory per storage location that is processed during order entry. This idea is legitimate for processing information for each master document included within the activity.

8. When performing each transaction, applicable organizational components should be assigned. Assignments to the enterprise construction within the doc are generated in addition to the information stored for the customer and material.

9.The doc generated by the transaction comprises all relevant pre-outlined data from the master knowledge and organizational elements.

10. A doc is generated for every transaction carried out within the SAP System.

DATA WAREHOUSE CONCEPT

1. While you are using the transactions within the Logistics purposes, the Logistics Information System (LIS) updates related information. You might also update information from other techniques in the LIS.

2.The LIS aggregates and shops this info within the knowledge warehouse. Data could be aggregated on a qualitative as well as on a quantitative foundation:

3. quantitative discount by aggregating on period level
4. qualitative reduction by selecting particular key figures
5.You'll have the opportunity to then use the tools in the Sales Information System (SIS) to investigate this aggregated information. The aggregation results in an improvement of the response occasions and of the quality of the ensuing reports.

SAP BW permits the evaluation of data from operative SAP functions as properly as all different business purposes and external data sources reminiscent of databases, online companies, and the Internet.

1.Administrator Workbench (AWB) functions allow you to control, monitor, and maintain all knowledge procurement processes.

2.SAP BW enables Online Analytical Processing (OLAP) for staging information from large quantities of operative and historical data. OLAP expertise permits multi-dimensional analyses in accordance with various business perspectives.

3.The BW server, which is preconfigured by Business Content material for core areas and processes, allows you to examine the relationships in every space within your company. Business Content material gives focused info to firms, divided into roles. This helps your workers to hold out their tasks. In addition to roles, Enterprise Content contains other preconfigured objects such as InfoCubes,queries, key figures, and characteristics. These objects facilitate the implementation of SAP BW.

4.The Business Explorer (BEx) part gives users with in depth analysis options.

5.BEx is the SAP BW part that provides versatile reporting and evaluation tools that you should utilize for strategic evaluation and supporting the choice-making process in your company. Workers with access authorization can analyze historic and present information at differing levels of element and from completely different perspectives.

6.BEx permits a wide spectrum of customers to access info in SAP BW. This could be done in Enterprise Portal from an iView that you can name alongside the functions where you extract the data, within the Internet or Intranet (Net Application Design), or utilizing a cell system. Internet Utility Design permits you to implement generic OLAP navigation in Web purposes and in Business Intelligence cockpits for both easy and extremely-individual scenarios. Highly individual scenarios with customerdefined user interface components could be realized using normal markup languages (HTML) for example. Net Utility Design encompasses a wide spectrum of interactive Internet-based Business Intelligence scenarios you could modify to fit your requirements using commonplace Net technology.

7. Portal Integration consists of

(1) single level of entry
(2) position-primarily based staging of knowledge,
(3) personalization,
(4) publication of iViews, and
(5) integration of unstructured data.

Query,Reporting, and Analysis include

(1) query design utilizing the BEx Analyzer,
(2) multi-dimensional (OLAP) evaluation,
(3) geographical analysis,
(4) advert-hoc reporting, and
(5) alerts.

Internet Utility Design contains

(1) interactive analytical content,
(2) data cockpits and dashboards,
(3) basis for creating analytical functions,
(4) creation of iViews for a portal, and
(5) wizard-help.

abap report on back order processing in sap
ERP basis and sap netweaver overview
MYSAP ERP advantages and main features

SAP ABAP Programming Report on Missing Parts Info System

SAP ABAP programming report on missing parts info system provides a quick recap of material shortages based on reservations that were not fully committed on their requirements date during an availability check. As such, you can use this report to monitor critical components or orders, and to reallocate materials based on inventory availability.

You must check the availability before running this report, since it is based on the results of availability checking. These checks are typically made during order creation or release, but may also be invoked manually. If there is a shortage, the order header will carry the status MSPT and the shortage will be noted in the missing Parts Info listing.

Availability checking parameters must be defined in configuration and may be referenced on various levels including material, order type, and MRP group. Two standard profiles are delivered which provide the basic organization of the report . Additional profiles can be created where needed.

Backorder processing is the key function which may be called up from the primary display of missing parts. It allows you to change the allocation of components. Menu options also provide for branching to the stock overview and stock/requirements list, as well as for display or change of an order.

The first screen provides filters to limit the selection of missing parts from reservations or orders based on single values, ranges, or multiple selection functions for:

Plant
Material
MRP controller
Requirements date
Sales order
Production Order number
Production scheduler (orders only)

At a minimum, the plant must be entered. In addition, the profile which specifies grouping either by material or order must be entered.

The primary display lists the missing parts—grouped as specified by the profile—and provides the fields listed below (see Example 1). Additions and changes to these fields are possible from the View menu and include:

Material and description
Plant
MRP controller
Requirements date
Requirements quantity
Committed quantity
Storage location
Reservation number
Order number

To access the first screen for this report, choose Logistics → Production → Production control → Control → Information system → Missing parts info system.

1. Enter 1000 in Plant and any other criteria to narrow the selection process.
2. Choose a profile to display the data by material or by manufacturing order.
3. Choose Execute.

The first screen of the report shows data according to the display-bymaterial profile:

A Requirement date and quantity
B Quantity committed to date
C Reservation number

The first screen of the report can also show data according to the alternate profile, display-by-manufacturing order:

D From either profile, additional fields may be chosen or substituted using the Fields icon.

E The resulting popup window lets you add or delete additional fields.

Materiel document for mm report in abap programming

LSMW Step by Step in SAP ABAP part Three

In the previous posts we have introduced LSMW and step one and next few steps. Here we are going to deal with further steps of LSMW.

Step Six : Maintain Fixed Values, translations, user-defined Routines

If it is needed to convert a number of legacy fields in a particular way before assigning it to the R/3 field, the conversion rule may be written in the form of a routine and may be re-used.

You may now go back to the previous step and assign these routines to the appropriate fields.

Step seven IN LSMW : Specify Files

Here the file name for the legacy data is to be specified. The file can reside in either user PC or in the Application Server. For background processing, the input file must be located in the Application Server. Access to presentation server files is only possible when you are working online.

For last two (Read & Convert Data), file names are defaulted by SAP and are respectively __

Step eight : Assign Files

Here the source structures are mapped to the files.

SAP ABAP LSMW program execution :

All the design/configuration steps are now over. The programs for Read & Conversion are now generated by R/3. You may view these but SAP does not allow changing the programs directly.

You can modify and regenerate the programs from within the LSMW. The programs are /1CADMC/SAP_LSMW_READ_<8> and /1CADMC/SAP_LSMW_CONV_<8> respectively, where <8> is generated by the system.

Next steps are

Prepare the legacy source file : There are various ways of creating the input file for the conversion program from legacy system data. The simplest way is to create a tab delimited text file. If the migration object has more one structures, each will be identified by distinct value of record type identifier (STYPE). This field exists in all the LS & R/3 structures.

It is also possible to combine different files during the data import. If, for example, the data for SAP routing originates in several different files (such as one for the routing, one for associated texts) in your legacy system, you can use the LSM Workbench to easily merge such files into one source file during import.

Read the legacy data from file using the read program : an intermediate file (*.read) is created .

Convert the data using the conversion program which converts the "intermediate file" into the input file for the batch or direct input program (second "intermediate file" - *.conv). The conversion program expects a sequential file stored on the application server as input, which has to meet certain format rules. These rules are defined by the file description and the conversion rules for data migration.

Finally the corresponding Batch or Direct Input program is to be executed. Sometimes these load programs offer test run. Direct Input programs may sometimes use logical file. Logical Path & File may be created using the transaction FILE.

Creation of customer and vendor accounts
SAP Controlling Event Based Posting Period end closing

Planning and budgeting with sap controlling

Order Summarization in sap controlling

Seheduling manager in sap controlling

SAP Report painter basics

Report painter strecture

LSMW Step by Step in SAP ABAP part Two

Legacy System Migration work bench(LSMW) for SAP ABAP is introduced in earlier post and in previous post we had a discussion regarding LSMW step one. This post is in continuation with that .

LSMW Step two : Maintain Source Structure

SAP has comprehensive documentation of the load programs (Direct or Batch Input) how the information is to be structured in one or more levels for the business object under consideration.

The Legacy data must be organized in similar fashion in the input file. Data for more that one structures may be available in a single file. Before completing the step, it is recommended to go through the SAP documentation. The source/legacy structures to be mentioned here are only identifiers and do not need to reside in the database dictionary.

Here is a screen shot of displaying source structure.

LSMW Step three : Maintain Source Fields

In this step, the source/legacy fields must be entered in each structures. You can use any of the following copy facilities, otherwise, the source field may also be added individually.

In case of multiple structures in the same file, you need to set the value of record type identifier (STYPE) in this step.

LSMW Step Four :Maintain Structure relation

The source/legacy structures must be related to the SAP R/3 structures.

LSMW Step Five : Maintain Field Mapping & Conversion Rules

In this step, source/legacy fields in each structure are assigned to R/3 fields. It may be done individually for each R/3 field, which being time-consuming for complex structures or we can use automatic mapping utility (Extras > Auto-Field mapping).

We can specify the conversion rule to be used to convert an LS field into the corresponding R/3 field. For this, use predefined conversion rules or create your own conversion rules in the editor.

You may use the buttons ‘Initial’, ‘Constant’, ‘Move’ & ‘Fixed Value’ to assign different values. You may also assign different rules by using the button ‘Rule’. automatic mapping utility (Extras > Auto-Field mapping).

On completion of this step, SAP generates the conversion program. Menu Extras > Display Variants will give further details of the generated programs in the LSMW screens. For further flexibility, you can create global data if you need R/3 table contents and/or variables for the conversion rules and create your own routines.

SAP reports in live systems

SAP Product cost planning

Material quantity with cost strecture

SAP Costing strecture part two

SAP Controlling costing run

LSMW Step by Step in SAP ABAP part one

LSMW of SAP ABAP is introduced in the previous post and here we are going to deal step by step.

To launch the tool, run the transaction LSMW from SAP.Main Design steps Once you open any existing / new object, the following screen is shown. It details the major steps of completing & running a LSMW object. Design steps are from 1 to 8. Execution steps are from 9. Before you may run the read & convert program, each of design steps is to be finished one by one.

The steps viewed can also be customized using the option Extras > Personal Menu. On selecting and step and closing the window, the step screen (shown above) will change. A few of these options however depend on further attributes set/selected in subsequent steps. Normally the main steps are shown and it can be reset by the button shown in the figure. Therefore the step number shown in the previous figure is subject to change.

Step one in LSMW : Maintain Object Attribute
In this step, we configure the basic attributes of data load – e.g. one time or periodic data transfer, which object to load (Vendor Master, Material Master) and its method. SAP provides all the fundamental objects and their associated programs either Direct Input or Batch Input. In few objects, one or more options may be available and you may need to choose which one to go for.

Further, ‘Intermediate Document’ (Idoc) may also be used. If the R/3 System does not provide
any suitable batch or direct input program, you can use the batch input recorder to create a user specific class of migration objects.

A few objects provided by SAP. The full list may be viewed from the drop-down help.

If Idoc is to be used, the information of File Port, Partner type & Partner Number must be provided. The configuration screen may be invoked from the menu Settings > Idoc Inbound Processing in the initial screen of LSMW.

LSMW in SAP ABAP

LSMW means Legacy System Migration Work Bench .SAP offers the Legacy System Migration (LSM) Workbench. The LSM Workbench is a SAP tool that facilitates the process of data transfer from non-SAP system (also called Legacy system) without additional programming to do data conversion. Wecan define the rules for the conversion. The LSM Workbench then generates an ABAP program and thus supports an important step in the process of data transfer.

It is a cross-application component (CA) of the SAP R/3 System and, therefore, is independent from the platform. The tool has interfaces with the Data Transfer Center and with batch input and direct input processing in R/3. The tool can be used in each of the different R/3 releases.

By combining the Data Transfer (DX) Workbench and the Legacy System Migration (LSM) Workbench in SAP Basis component Release 4.6, SAP has made substantial progress towards tackling one of the most costly and time-consuming implementation activities - the migration of legacy systems and the data transfer from ERP systems that are being replaced.

Data migration with the DX Workbench and the LSM Workbench guarantees maximum quality and consistency of your data in the SAP business solution. When data is imported, the system performs the same checks as it does during online entry. The update in your database is performed through the Standard Batch Input Program, Standard Direct Input Program and BAPIs.

Features of LSMW:

Instead of individual tables or field contents, the tool transfers complete business data objects (also called object class) such as Material Master, Supplier Master data. A migration object class is a unit combined from the business point of view, which can be used to transfer the data of all the legacy systems defined in the LSMW to the R/3 System.

The migration object class comprises the R/3 structures as well as the program used for data import. The batch and direct input technique is used to ensure consistency of data. For each migration object, a batch or direct input program has to be available in the SAP R/3 System.

The LSMW main functions are :

Definition of the legacy system structures and fields
Definition of object dependencies and assignment of conversion rules
The structure and field relationships between the legacy system and the R/3 System are defined in data mapping. The way how data is being processed during migration is determined by the conversion rules.
Data conversion
From the object dependencies, the LSMW generates conversion programs that translate the legacy system data.
Data import
Batch or direct input is used to import the data to the SAP R/3 System.

The additional functions of LSMW are :

Spreadsheet interface
Legacy system data in spreadsheet format can be processed.
Host interface
Legacy system data in a structured data format (that is, with record identifiers and correct sequence) can be processed.
Batch input recorder
The LSMW allows you to use the batch input recorder (shipped with the SAP R/3 standard system) in order to create user-specific classes of migration objects.

Automatic check functions
This function generates and performs value checks against check tables and fixed values specified in the Data Dictionary.

Constraints

The LSMW supports a single and complete transfer of data (initial data load) and also offers a restricted support of permanent interfaces. Thus, a periodic transfer of data is possible. It, however, does not include any functions for monitoring of permanent interfaces. The tool does not support any data export interfaces (outbound interfaces).

DATA BASE ACCESS FROM UNIX FILE

PROGRAM TO LOAD A DATABASE TABLE FROM A UNIX FILE

report zmjud001 no standard page heading.

tables: z_mver.

parameters: test(60) lower case default '/dir/judit.txt'.
data: begin of unix_intab occurs 100,
field(53),
end of unix_intab.
data: msg(60).

***open the unix file

open dataset test for input in text mode message msg.
if sy-subrc <> 0.
write: / msg.
exit.
endif.

***load the unix file into an internal table

do.
read dataset test into unix_intab.
if sy-subrc ne 0.
exit.
else.
append unix_intab.
endif.
enddo.

close dataset test.

***to process the data. load the database table

loop at unix_intab.
z_mver-mandt = sy-mandt.
z_mver-matnr = unix_intab-field(10).
translate z_mver-matnr to upper case.
z_mver-werks = unix_intab-field+10(4).
translate z_mver-werks to upper case.
z_mver-gjahr = sy-datum(4).
z_mver-perkz = 'M'.

z_mver-mgv01 = unix_intab-field+14(13).
z_mver-mgv02 = unix_intab-field+27(13).
z_mver-mgv03 = unix_intab-field+40(13).
* to check the data on the screen (this is just for checking purpose)
write: / z_mver-mandt, z_mver-matnr, z_mver-werks, z_mver-gjahr,
z_mver-perkz, z_mver-mgv01,
z_mver-mgv02, z_mver-mgv03.

insert z_mver client specified.

*if the data already had been in table z_mver then sy-subrc will not be
*equal with zero. (this can be *interesting for you - (this list is
*not necessary but it maybe useful for you)
if sy-subrc ne 0.
write:/ z_mver-matnr, z_mver-werks.
endif.
endloop.

NOTES:

1. This solution is recommended only if the database table is NOT a standard SAP database table .
2. In the above mentioned unix file record's size is 53 bytes.
Every record in the unix file as the same size:

10 bytes for material
04 bytes for plant
13 bytes for corrected consumption for January
13 bytes for corrected consumption for February
13 bytes for corrected consumption for March
3. Table Z_MVER

This table was created to store the consumption values for first quarter of the year.
Fields Data Element
mandt mandt
matnr matnr
werks werks
gjahr gjahr
perkz perkz
mgv01 mgvbr
mgv02 mgvbr
mgv03 mgvbr

RELATED POST

DATA BASE DIALOG
SAP Financial Assets and Liabilities
SAP Financial Profit and Loss

Data Formatting and Control Level Processing Lesson Thirty Two

You can use control level processing to create structured lists. Control levels are determined by the contents of the fields that are to be displayed. there is a control level change whenever the content of a field changes. This means that there is no point in creating control levels unless the data are sorted.The data to be displayed must be saved temporarily if you want to use control level processing. You can also use internal tables and intermediate data sets.You can use an array fetch in a SELECT statement to fill an internal table in one go.

You can use the APPEND statement to insert table entries at the end of an internal table. The variant of the APPEND statement on the slide is permitted only for standard or sorted tables. After an APPEND statement, system field SY-TABIX contains the index value of the newly inserted table entry.

You use the COLLECT statement to generate unique or compressed data sets. The contents of the work area of the internal table are recorded as a new entry at the end of the table or are added to an existing entry. The latter occurs when the internal table already contains an entry with the same key field values as those currently in the work area. The numeric fields that do not belong to the key are added to the corresponding fields of the existing entry.When the COLLECT statement is used, all the fields that are not part of the key must be numeric.The SORT statement sorts the entries in internal table in ascending order. If the addition BY ..., is missing, then the key assigned when the table was defined is used.

If addition BY ... is used, then fields , , ... are used as sort keys. The fields can be of any type.

You can use the additions ASCENDING and DESCENDING with the SORT statement to determine whether the fields are sorted in ascending (default) or descending order.

You can use the loop statement LOOP AT ... ENDLOOP to process an internal table. The data records in the internal table are processed sequentially.The CONTINUE statement can be used to prematurely exit the current loop pass and skip to the next pass.The EXIT statement can be used to exit loop processing.

At the end of loop processing (after ENDLOOP), return value sy-subrc indicates whether the loop was passed or not.

SY-SUBRC = 0: The loop was passed at least once
SY-SUBRC = 4: The loop was not passed because no entry was available.

You can use special control structures for control level processing. All the structures begin with AT and end with ENDAT. These control structures can only be used within a LOOP.The statement blocks AT FIRST and AT LAST are run exactly once: at the first AT FIRST and at the last AT LAST loop.The statements within AT NEW ... ENDAT are executed when the value of field changes within the current LOOP (start of a control level) or the value of one of the fields in the table definition (further to the left).

The statements within AT END OF ... ENDAT are executed when the value of field changes during the next LOOP (end of a control level) or the value of one of the fields in the table definition (further to the left).At entry of the control level (directly after AT), - all fields with the same character types after (to the right of) the current control level key are filled with "*" - all other fields after (to the right of) the current control level key are set to default values.

When a control structure is exited (at ENDAT), all fields of the query area are filled with the data from the current loop pass.The SUM statement supplies the respective group totals in the query area of the LOOP in all fields of TYPE I, F and P.

The control level structure in internal tables is static. It corresponds exactly to the sequence of columns in the internal table (from left to right). In particular, the control level structure for internal tables is independent of the criteria used to sort the internal table. The table must be sorted according to the internal table fields.

When you implement control level processing, you must follow the sequence of individual control levels within the LOOP as illustrated in the slide. The sequence follows the sequence of fields in the internal table and is therefore also the sort sequence.

The processing block between AT FIRST and ENDAT is executed before processing of the single lines begins. The processing block AT LAST and ENDAT is executed after all single lines have been processed.

RELATED POSTS

LESSON 33 SAVING LIST AND BACK GROUND PROCESSING
SAP Financial Payment Cards

Programming Data Retrieval in SAP ABAP Lesson Thirty

Whenever a logical database cannot supply your program with all necessary data, you must program database access directly into the program itself. This can be done using either Open SQL or Native SQL statements.Open SQL statements offer several advantages. These include being able to program independent of your underlying database, access to a syntax check, and the use of a local SAP buffer.

Native SQL statements are bound into a program using
EXEC SQL [PERFORMING form.
.
ENDEXEC

Pay attention to the following when programming Native SQL:
Try not to use update operations (INSERT, DELETE, UPDATE)
Group EXEC SQL statements together (in an include) in order to be able to alter them centrally for different database systems
Restrict yourself to Standard SQL

In order to optimize performance, choose your SQL statements carefully when accessing several (dependent) tables at a time.
To insure optimal database performance:

Follow these general rules:
Keep the amount of selected data as small as possible (use WHERE conditions, for
example)

Keep data transfer between the application server and the database to a minimum (use field lists, for example)

Reduce the number of database inquiries if possible (use table joins instead of nested SELECT statements, for example)

Reduce search size (this optimizes your database index)

Minimize database server load (use SAP buffers, for example).

Always subject programs containing SQL statements to an SQL trace. Which processing sequence is chosen by the Optimizer? Are indices used? If so, are the right ones used?

Is a FULL TABLE SCAN performed? Based on the results of this analysis, you should reprogram your SQL statements (WHERE) conditions, create a database index, or buffer the tables better. To start the SQL trace, use menu path GDA-1.

You can create database views in the ABAP Dictionary. Views (aggregate objects) are application specific and allow you to work with multiple database tables. The link is mapped in an INNER JOIN LOGIC (see slide on INNER JOIN).

From Release 4.0 you can buffer database views. You can then read from views using the SAP buffer on the relevant application server. The same rules apply when buffering views as when buffering tables.

Database view advantages:

Central maintenance
Accessible to all users
Only one SELECT statement is required in the program
One disadvantage of the view is its low flexibility.

In a join, the tables (base tables) are combined to form one results table. The join conditions are applied to this results table. The resulting composite for an inner join logic contains only those records for which matching records exist in each base table.

Join conditions are not limited to key fields.

If columns from two tables have the same name, then you have to ensure that the field labels are unique by prefixing the table name or a table alias.

A table join is generally the most efficient way to read from the database. The database is responsible for deciding which table is read first and which index is used (DB Optimizer).

At LEFT OUTER JOIN, results tables can also contain entries from the designated left hand table without the presence of corresponding data records (join conditions) from the table on the right. These table fields are filled by the database with null values and are then initialized according to ABAP type.

It makes sense to use a LEFT OUTER JOIN when data from the table on the left is needed for which there are no corresponding entries in the table on the right.

The following limitations apply for the Left Outer Join:

you can only have a table or a view to the right of the JOIN operator, you cannot have another join statement
Only AND can be used as a logical operator in an ON condition.
every comparison in the ON condition must contain a field from the table on the right.
if the FROM clause contains an Outer Join, then all ON conditions must contain at least one 'true' JOIN condition (a condition that contains a field from tab1 and a field from tab2).

FOR ALL ENTRIES works with a database in a quantity-oriented manner. Initially all data is collected in an internal table. Make sure that this table contains at least one entry (query sy-subrc or DESCRIBE), otherwise the subsequent transaction will be carried out without any restrictions).

SELECT...FOR ALL ENTRIES IN is treated like a SELECT statement with an external OR condition. The system only selects those table entries that meet the logical condition .
Using FOR ALL ENTRIES is recommended when data is not being read from the database, that is, it is already available in the program, for example, if the user has input the data. Otherwise a join is recommended.

The easiest technical option for reading from multiple (dependent) tables is to use nested SELECT statements. The biggest disadvantage of this method is that for every data record contained in the external loop a SELECT statement is run using the database. This leads to a considerably worse performance in client/server systems.
LESSON 31 SAP QUARY ADMINSTRATION
Data Depreciation Areas
Asset Allocation Information System
Legacy Data Transfer

Logical Data Bases Lesson Twenty Eight

In general, the system reads data that will appear in a list from the database.You can use OPEN SQL or NATIVE SQL statements to read data from the database.The use of a logical database provides you with an alternative to having to program database accesses individually. Logical databases retrieve data records and make them available to ABAP programs.

The same logical database can be the data source for several Quick Views, queries, and programs. In the Quick View, the LDB can be specified directly as a data source. A query works with the logical database when the functional area that generated the query is defined with a logical database. In the case of type 1 programs, the LDB is entered in the attributes or called using function module LDB_PROCESS. See appendix for information on how to use the function module.

Logical databases offer several advantages:

The system generates a selection screen. The use of selection screen versions or variants provides the required flexibility.The user does not have to know the exact structure of the tables involved (especially the foreign key dependencies); the data is made available in the correct order at GET events.

Performance improvements within logical databases directly affect all programs linked to the logical database, without having to change the programs themselves.

Maintenance can be performed at a central location.

Authorization checks can also be performed centrally.

A logical database is an ABAP program that reads predefined data from the database and makes it available to other programs.

A hierarchical structure determines the order in which the data is supplied to the programs. A logical database also provides a selection screen that checks user entries and conducts error dialogs. These can be extended in programs.

SAP provides some 200 logical databases in Release 4.6. The names of logical databases have been extended to 20 places in Release 4.0 (namespace prefix max. 10 characters).

In the case of executable programs, you can enter a logical database in the attributes.

Use the NODES statement to specify the nodes of the logical database that You want to use in the program. NODES allocates the appropriate storage space for the node - that is, a work area or a table area depending on the node type.

The logical database makes the data records available for the corresponding GET events.
The sequence in which these events are processed is determined by the structure of the logical database.

Logical databases are made up of several sub-objects. The structure determines the hierarchy, and thus the read sequence of the data records.

Node names can contain up to 14 characters. There are four different node types.

Table (type T): The node name is the name of a transparent table (this type corresponds to the concept prior to Release 4.0A). The table name must be identical to the node name. Deep types (complex) are not allowed.

DDIC type (type S): Any node name is possible. It is assigned a structure or a table type from the Dictionary. The node name can differ from the type name. Deep structures are possible.

Type groups (type C): The node type is defined in a type group. The name of the type group must be maintained in the "Type group" field. You should generally prefer DDIC types, as the other applications that use the logical database (such as SAP Query) can access them (short texts, and so on).

Dynamic nodes (type A): These nodes do not have a fixed type; they are not classified until the program runtime. Which types are generally allowed is determined when the structure is created.

Nodes are declared using language element NODES.

Processing blocks are always allocated to an event. A processing block is closed by the next event key word, the start of form routines, or by the end of the program.

The START-OF-SELECTION event is triggered before control is given to the read routine of the logical database. The END-OF-SELECTION event is triggered after all GET events have been processed - that is, all data records have been read and processed.

The GET event is triggered whenever the logical database supplies data for this node. This means that GET events are processed several times, and that data has already been read from the database for these events. The sequence in which the GET events are processed is determined by the structure of the logical database.

The GET LATE event is triggered when all subordinate nodes of node have been processed, before the data is read for the next ; that is, whenever a hierarchy level has been completed.

At the start of the event, the system automatically adds a line feed and configures the default formats (for example, INTENSIFIED ON).

CHECK statements end the current processing block.

STOP statements end program processing. However, in contrast to the EXIT statement, the processing block END-OF-SELECTION is processed first (if it exists).

If there is a STOP statement within the END-OF-SELECTION processing block, program processing ends immediately and a list is displayed.

The EXIT statement exits the program and displays the list.

You can also use the REJECT statement. The data record is not processed further. Processing continues on the same hierarchy level when the next data record is read. REJECT, unlike the CHECK statement, can also be used within a subroutine.

Use the selection include dbsel to define selection screens for logical databases. The addition FOR NODE assigns selections to individual logical nodes. The appearance of a selection screen thus directly depends on the NODES statement contained within your program.

A field selection can be defined for the individual nodes. To do this, you have to specify the addition FIELD SELECTION FOR NODE in the SELECTION-SCREEN statement. You can then use GET FIELDS to restrict the amount of data returned.

You can designate individual nodes for dynamic selection using the addition DYNAMIC SELECTIONS FOR NODE. The Dynamic selection pushbutton then appears on your selection screen. You can determine which selection fields can be set by choosing a particular selection view yourself (type: CUS) or by using the selection view delivered by SAP (type: SAP).

With large logical databases you can define several selection screen versions. Each selection screen version contains a subset of your selection criteria (language element: EXCLUDE). Specify the name of a selection screen version in the program attributes.

When you enter a logical database in the attributes of your type 1 program, the system processes the selection screen of the logical database. The concrete characteristics of the selection screen depend upon the node specified in the NODES statement. If you specify a node of type T (table), you can also declare the table work area with the TABLES statement.

If you address only subordinate nodes (in the hierarchy) of the logical database in the program (for example sflight), the selection screen criteria for the superior node in the hierarchy (spfli) also appear. You can thus restrict the dataset to be read so that it meets your specific requirements.

Note: A logical database always reads in accordance with its structure. This means that if you only need data from a node deep in the hierarchy, you will achieve better performance by programming the access yourself. This avoids unnecessary reading of the database.

If the logical database supports dynamic selections, the pushbutton for Dynamic selections appears on the selection screen. When the user presses this button, a second selection screen is displayed.
This screen allows the user to select additional database fields. The system transfers the selections directly to the logical database program and therefore to the database (dynamic selections).

he selection view determines which fields are displayed on the selection screen. Create your own view with type CUS, and have it override the view with type SAP.

Database program sapdb for logical database is a collection of subroutines, each of which is performed for specific events. For example, subroutine is processed once at the start of the database program. This program can be used to define default values for the selection screen of the LDB.

Other subroutines also exist that are processed during events PBO (Process Before Output) and PAI (Process After Input) of the selection screen. Checks, such as authorization checks (AUTHORITY-CHECK), are usually performed during event PAI.

The database accesses (SELECT statements) are programmed in the put_ subroutines. These subroutines may be processed several times, depending on which selection criteria the user specifies. The sequence in which these subroutines are processed is determined by the structure of the logical database.

Database access (SELECT statements) should be programmed with optimal performance in mind. When creating a logical database you generate the corresponding database program after first having determined its structure and selection attributes. You can find performance tips in the comment lines.

When a program that has been assigned a logical database is started, control is initially passed to the database program of the logical database. Each event has a corresponding subroutine in the database program - for example, subroutine init for event INITIALIZATION. During the interaction between the LDB and the associated program, the subroutine is always processed first, followed by the event (if there is one in the report).

Logical database programs read data from a database according to the structure declared for the logical database. They begin with the root node and then process the individual "branches" consecutively from top to bottom.

The logical database reads the data in the put_ subroutines. During event PUT, control is passed from the database program to the GET event of the associated report.

The data is made available in the corresponding work areas in the report. The processing block defined for the GET event is performed. Control then returns to the logical database.
PUT activates the next form subroutine found in the structure. This flow is continued until the report has collected all the available data.

The depth of data read in the structure depends upon a program's GET events. A logical database reads to the lowest GET event contained within the structure attributes. Only those GET events for which processing is supposed to take place are written into the report program. Logical databases read all data records found on the direct access path.

If you specify a logical database and declare additional selections in the program attributes that refer to the fields of a node not designated for dynamic selection, you must use the CHECK statement to see if the current data record fulfills the selection criteria.

If the data record does not fulfill these selection criteria, current event block processing ends.

RELATED POST

LESSON 29 SELECTION SCREENS IN ABAP REPORT

Closing Process Assets and Liabilities
Profit and Loss Closing Process

Subscribe to: Posts ( Atom )