06-21-2010 8:31 AM
Need code to Retrieve data from database view through select statement
REPORT zfi_v1.
tables : bkpf,bseg.
TYPES : BEGIN OF ty_vbsegs,
belnr TYPE belnr_d,
gjahr TYPE gjahr,
bukrs TYPE bukrs,
buzei TYPE buzei,
bschl TYPE bschl,
mwskz TYPE mwskz,
wrbtr TYPE wrbtr,
sgtxt TYPE sgtxt,
saknr TYPE saknr,
blart TYPE blart,
budat TYPE budat,
xblnr TYPE xblnr1,
END OF ty_vbsegs.
DATA : it_vbsegs TYPE TABLE OF ty_vbsegs,
wa_vbsegs TYPE ty_vbsegs.
SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS : p_bukrs TYPE bseg-bukrs.
PARAMETERS : p_hkont TYPE bseg-hkont.
SELECT-OPTIONS : s_budat FOR bkpf-budat OBLIGATORY.
SELECTION-SCREEN : END OF BLOCK b1 .
START-OF-SELECTION.
BREAK-POINT.
SELECT belnr
gjahr
bukrs
buzei
bschl
mwskz
wrbtr
sgtxt
saknr
blart
budat
xblnr
FROM v_vbsegs
INTO TABLE it_vbsegs
WHERE bukrs = p_bukrs
AND saknr = p_hkont
AND budat IN s_budat.
END-OF-SELECTION.
LOOP AT it_vbsegs INTO wa_vbsegs.
WRITE:/ wa_vbsegs-belnr,
wa_vbsegs-gjahr,
wa_vbsegs-bukrs,
wa_vbsegs-buzei,
wa_vbsegs-bschl,
wa_vbsegs-mwskz,
wa_vbsegs-wrbtr,
wa_vbsegs-sgtxt,
wa_vbsegs-saknr,
wa_vbsegs-blart,
wa_vbsegs-budat,
wa_vbsegs-xblnr.
CLEAR : wa_vbsegs.
ENDLOOP.
At run time we are not getting data into the internal table( created on database view)
06-21-2010 8:38 AM
Check if the data exists in the view for the WHERE condition you have mentioned.
06-21-2010 9:16 AM
Hi ,
You can treat the dat base view as table only. So the way we retrieve datya from tables using select query , same way you can retrieve data from data bsea view. Just see whether ther data ixists with tye conditions you have mentioned.
Hope this is helpful.
Thanks & Regards,
Uma Dave
06-23-2010 8:45 AM
Can u plz suggest some standard database views for bseg and bkpf?
06-23-2010 9:28 AM
See these
ARIX_BKPF FI_DOCUMNT: Header-Oriented Document Index
BKPF Accounting Document Header
BKPF_ADD Accounting Document Header
EBKPF Fin.Accntng Doc.Header (of Docs from Exter
LRBKPF Documents as Result of Logical Document Re
TXW_S_BKPF Retrieve BKPF from archive
VBKPF Document Header for Document Parking
VIAKBKPF Document Header from Service Charge Settle
VBSEGA Document Segment for Document Parking - As
VBSEGD Document Segment for Customer Document Par
VBSEGK Document Segment for Vendor Document Parki
VBSEGS Document Segment for Document Parking - G/
VIAKBSEG Document Line Item from SCS for Interface
Regards,
Uma Dave
06-23-2010 8:54 AM
Check via SE11, v_vbsegs is not a database view on BKPF and BSEG (but only refers to Document Parking).
BSEG is a cluster table (it does not exist on the database as a separate table, but is a part of table RFBLG), so it is not possible to create a database view. As BSEG is a cluster table, SELECT can only be optimized when specifying some of the actual database key (RFBLG : MANDT, BUKRS, BELNR, GJAHR)
You can try to use one of the "secondary indexes" maintained by SAP
- BSAD Accounting: Secondary Index for Customers (Cleared Items)
- BSAK Accounting: Secondary Index for Vendors (Cleared Items)
- BSAS Accounting: Secondary Index for G/L Accounts (Cleared Items)
- BSID Accounting: Secondary Index for Customers
- BSIK Accounting: Secondary Index for Vendors
- BSIM Secondary Index, Documents for Material
- BSIP Index for Vendor Validation of Double Documents
- BSIS Accounting: Secondary Index for G/L Accounts
Regards,
Raymond
06-23-2010 9:15 AM
While retrieving data from Bseg its taking time to gte data from bseg can u suggest another table instead of BSEG?
While using this select statement for bseg we are getting performance issue can u suggest anyway to improve performance?
SELECT bukrs "Company Code
belnr "Accounting Document Number
gjahr "Fiscal Year
buzei "Number of Line Item Within Accounting Document
bschl "Posting Key
mwskz "Sales Tax Code
wrbtr "Amount in document currency
sgtxt "Item Text
hkont "General Ledger Account
lifnr "Account Number of Vendor or Creditor
FROM bseg "Document Segment for Document Parking - G/L Account Database
INTO TABLE it_bseg_1
WHERE bukrs = p_bukrs
AND hkont = p_hkont.
06-23-2010 2:57 PM
Try to firsy select data from tables BSIS and BSAS as your criteria is General ledger account (only BSIS if account is not clearable)
If some fields are missing, you can then SELECT from BSEG with the full key (BUKRS, BELNR GJAHR and BUZEI) in a SELECT FOR ALL ENTRIES
Regards,
Raymond
06-23-2010 6:50 PM
Moderator message - Please search before asking - post locked Rob