Webi

Creating Webi Report

0FI_AP_50

Base Table : PAYR

This DataSource provides subledger accounting payments based on checks (table PAYR) and the corresponding vendor line items.

Technical Data

Application Component	Financial Accounting: Vendors (FI-AP)
Available as of Release	SAP enhancement package 3 for SAP ERP 6.0, business function Reporting Financials (FIN_REP_SIMPL_1)
Shipment	SAP NetWeaver 7.0 BI Content Add-On 3 Support Package 08
Content Versions	603
RemoteCube-Capable	Yes
Delta-Capable	No
Extraction from Archives	No
Verifiable	No

Data Modeling

Fields of Origin for the Extract Structure

Fields in the Extract Structure	Description of the Field in the Extract Structure	Table of Origin	Field in the Table of Origin
MANDT	Client
ZBUKR	Paying company code	PAYR	ZBUKR
HBKID	Short key for a house bank	PAYR	HBKID
HKTID	Short key for account details	PAYR	HKTID
RZAWE	Payment method	PAYR	RZAWE
CHECT	Check number	PAYR	CHECT
CHECF	Check number of	PAYR	CHECF
LIFNR	Account number of vendor	PAYR	LIFNR
KUNNR	Customer number 1	PAYR	KUNNR
UBHKT	G/L account number for our bank account/bank subaccount	PAYR	UBHKT
VBLNR	Payment document number	PAYR	VBLNR
FISCVAR	Fiscal year variant	T001	PERIV
GJAHR	Fiscal year	PAYR	GJAHR
ZALDT	Expected payment date (cash discount-1 due)	PAYR	ZALDT
WAERS	Currency key	PAYR	WAERS
RWBTR	Regulated amount in currency of payment	PAYR	RWBTR
BANCD	Date of check encashment	PAYR	BANCD
ZANRE	Form of address for payee	PAYR	ZANRE
ZNME1	Name of the payee	PAYR	ZNME1
ZPSTL	ZIP code of payee	PAYR	ZPSTL
ZORT1	Location of payee	PAYR	ZORT1
ZSTRA	Street and house number of payee	PAYR	ZSTRA
ZPFAC	Payee's PO box	PAYR	ZPFAC
ZLAND	Country key	PAYR	ZLAND
ZREGI	Regional code of payee	PAYR	ZREGI
ZBNKS	Country key	PAYR	ZBNKS
ZBNKN	Bank account number of the payee	PAYR	ZBNKN
ZBNKL	Bank number of the payee's bank	PAYR	ZBNKL
ZBKON	Bank control key of payee	PAYR	ZBKON
ZSWIF	SWIFT code for international payments	PAYR	ZSWIF
ZIBAN	Payee's IBAN	PAYR	ZIBAN
VOIDR	Reason for invalid check	PAYR	VOIDR
VOIDD	Date of invalid check	PAYR	VOIDD
VOIDU	User of invalid check	PAYR	VOIDU
ZPST2	ZIP code of payee's PO box	PAYR	ZPST2
PERNR	Personnel number	TPAYR	PERNR
REC_BELNR	Accounting document number	PAYR	REC_BELNR
REC_GJAHR	Fiscal year	PAYR	REC_GJAHR
ZPFOR	Location of payee's PO box	PAYR	ZPFOR
RWSKT	Overall account of payment transaction in currency of payment	PAYR	RWSKT
XAUSC	List of outstanding checks		*1
CHECM	Check number intervals		*2
BUKRS	Company code	BSIK, BSAK	BUKRS
BELNR	Accounting document number	BSIK, BSAK	BELNR
XBLNR	Reference document number	BSIK, BSAK	XBLNR
BUZEI	Number of the line item within the accounting document	BSIK, BSAK	BUZEI
BLDAT	Document date in document	BSIK, BSAK	BLDAT
BUDAT	Posting date in document	BSIK, BSAK	BUDAT
WAERS_I	Currency key	T001	WAERS
WRSHB	Foreign currency amount (+/-)	BSIK, BSAK	WRSHB
ABZUG	Cash discount amount in document currency with +/- Signs	BSIK, BSAK	*3
NETTO	Net amount in foreign currency	BSIK, BSAK	*3
SGTXT	Item text	BSIK, BSAK	SGTXT
ZUONR	Assignment number	BSIK, BSAK	ZUONR

Extractor Logic

Information on the “Field in the Table of Origin”:

*1: only selection field for direct access

*2: Calculated from the fields CHECF and CHECT

*3: calculated

The extractor uses the extraction logic of the ABAP report RFCHKN10.

Table Types

Database Tables

Transparent Table
One to One
One table in data dictionary equal to one table in data base.



Cluster Table
Many to One
Many tables in ABAP dictionary equal to one table cluster in database. Basically used to hold application data. One table cluster contains few number of cluster tables having data of around large quantities.

They have different field name at the table and database level ( as they are coming from different tables ?)

• Cannot be read from other systems
• BSEG / CDPOS are cluster tables and can not be read from IDT.

Pool Table




Reference Links
https://archive.sap.com/discussions/thread/940285


Internal Tables

Standard Table

Hash Table


Sorted Table

Create Universe in IDT

Step 1 : Create Project
File - New - Project : Project gets created. There will be folder with the name of the project created.

Step 2: Establish Connections
Go to the Repository Resources ( LHS Downpane ) - Connections. Look for the connection needed and right click on it - Create Relational Connection Shortcut - It will give a pop up for the available projects and select the project you want to add the connection. Once this is done you will be able to read data from the tables from the connection. You can add multiple connections to a single project.

Step 3: Data Foundation
Right click on the project folder and create Data Foundation. Give a name to the data foundation and in the next screen select if it a Single Source or Multiple Source Enabled.
Select the connections you need and in the next screens you can design ( different colors, descriptions) how you want the tables to look like from different connections.

In the data foundation , you can add all the tables you want to use in the Universe. There is a search option available to look for tables.
Once the tables are added you can either use all the fields in the table or only specific fields can be selected from the table ( Double click the table and you can select the fields ).

If you right click on the table, it will give list of various operations you can do on the table. In the 'Insert' option, you can insert Alias Table Or Derived Table Or View From Selection.

Step 4 : Business Layer
Right click on Project and create the Business Layer. Select the type of datasource for the layer ( Relational Data Foundation Or OLAP Connection ).

The name of the Business Layer becomes the name of your universe.
Select the Data Foundation to be used in the business layer.

Here you can also create sample queries to play around the data to see if the right results are coming from the dataset.

IDT Errors

Error
Refresh Failed
You cannot run this query because it will produce a Cartesian product.  (IES 00012)
Cause / Solution
When 2 or more tables are being used in the data foundation with out being mapped to one another. ( I had same data from 2 different sources and the tables are not mapped)

IDT Terms

Alias Table

Derived Table
This is created based on the database table. Important reason this is created is that we can manipulate the SQL to suit our needs.
For example , MARA is being in IDT we can add filters on the table in IDT directly but if you create a derived table, we can join it with other tables in the SQL and make if more effective.
However, derived table are slower in performance compared to database tables.
More information can be found below

Creating Derived Table
Right click on the database table and it will give you an option to create Derived table.
[[ However, when you right click on the empty space and create Derived tables, it seems create it but cannot be seen in the screen.. not sure why? ]]

https://dwbicastle.com/2014/06/05/what-is-derived-table-in-bo-universe/

View From Selection

Using Formula in IDT

curdate()
Returns the value in format Jun 13,2017

ToDate

toDate('2003-02-12') = February 12, 2003 

Date in IDT is saved in this format by default. When we need to use date for calculations, we need to use ToDate to convert the date to 'YYYY-MM'DD' format.

Eg : Use concatenation to first convert 06/13/2017 to get it to format 2017-06-13 and then use ToDate which converts to IDT format.

ABAP Errors

•  Access using a 'ZERO' object reference is not possible.
• This happens when the class is not initiated(?) . Use 'CREATE OBJECT lcl_obj' and then call the method