WeChat: cj_william / Mail: william.chen.cn@outlook.com
分类： 其他平台
2013-03-07 22:18:04
由于bseg表很大，而且表的索引字段是：
BUKRS - Company Code
BELNR - Accounting Document Number
GJAHR - Fiscal Year
BUZEI - Line Item Number
访问此表最佳的是包含所有的索引字段，但在实际应用是很少能达到的。但要有好的性能根据经验必须要有公司和凭证号作为查询条件。为了达到这一点，我可以根据条件不同而使用不同ｓａｐ的其他表，先查出公司和凭证号，再去读取ＢＳＥＧ表。
代码如下：
注意：该程序包含了各种条件的查询，程序员可以根据自己应用需求选择对应一个的子程序来读取公司和凭证号。
REPORT ztest_select.
* Tables***************************************************************
TABLES: bkpf, bseg,
covp, csks,
glpca,
bsis, bsas, bsid, bsad,bsik, bsak,
ekbe, aufk,
vbfa, vbak,
vapma,
fmifiit,
payr.
* Global Data**********************************************************
TYPES: BEGIN OF doc,
bukrs TYPE bseg-bukrs,
belnr TYPE bseg-belnr,
gjahr TYPE bseg-gjahr,
buzei TYPE bseg-buzei,
END OF doc.
DATA: doc_int TYPE TABLE OF doc,
doc_wa TYPE doc,
w_repid TYPE sy-repid VALUE sy-repid,
no_lines TYPE sy-tabix.
* Selection Screen*****************************************************
PARAMETERS: p_gjahr TYPE covp-refgj OBLIGATORY.
SELECTION-SCREEN SKIP.
PARAMETERS: p_kokrs TYPE csks-kokrs OBLIGATORY,
p_kostl TYPE csks-kostl,
p_prctr TYPE glpca-rprctr,
p_aufnr TYPE aufk-aufnr.
SELECTION-SCREEN SKIP.
PARAMETERS: p_bukrs TYPE bsis-bukrs OBLIGATORY,
p_budat TYPE bkpf-budat,
p_ebeln TYPE ekko-ebeln,
p_hkont TYPE bsis-hkont,
p_lifnr TYPE bsik-lifnr,
p_kunnr TYPE bsid-kunnr.
SELECTION-SCREEN SKIP.
PARAMETERS: p_vbeln TYPE vbak-vbeln.
SELECTION-SCREEN SKIP.
PARAMETERS: p_matnr TYPE vapma-matnr.
SELECTION-SCREEN SKIP.
PARAMETERS: p_fikrs TYPE fmifiit-fikrs,
p_fistl TYPE fmifiit-fistl,
p_fonds TYPE fmifiit-fonds.
SELECTION-SCREEN ULINE.
PARAMETERS: p_hbkid TYPE payr-hbkid,
p_hktid TYPE payr-hktid,
p_rzawe TYPE payr-rzawe,
p_chect TYPE payr-chect.
START-OF-SELECTION.
* Retrieve document numbers based on different requirements
* Posting Date (用日期做查询条件)
PERFORM posting_date_actuals
USING p_bukrs
p_budat.
* Cost Center
PERFORM cost_center_actuals
USING p_kokrs
p_kostl
p_gjahr.
* GL Account
PERFORM gl_actuals
USING p_bukrs
p_hkont
p_gjahr.
* Vendor
PERFORM vendor_actuals
USING p_bukrs
p_lifnr
p_gjahr.
* Customer
PERFORM customer_actuals
USING p_bukrs
p_kunnr
p_gjahr.
* Purchase Order
PERFORM po_actuals
USING p_ebeln.
* Sales Order
PERFORM so_actuals
USING p_vbeln.
* Order
PERFORM order_actuals
USING p_aufnr
p_gjahr.
* Fund/Fund Center
PERFORM fm_actuals
USING p_fikrs
p_gjahr
p_fistl
p_fonds.
* Profit Center
PERFORM profit_center_actuals
USING p_kokrs
p_prctr
p_gjahr.
* Material
PERFORM material_actuals
USING p_matnr
p_gjahr.
* Cheque number
PERFORM cheque_actuals
USING p_hbkid
p_hktid
p_chect.
*&---------------------------------------------------------------------*
*& Form posting_date_actuals
*&---------------------------------------------------------------------*
* Use one of the secondary indices of BKPF toretrieve the
* document number
*----------------------------------------------------------------------*
FORM posting_date_actuals
USING bukrs
budat.
DATA: disp_date(10).
CHECK NOT budat IS INITIAL.
* Uses index BKPF~2 (4.7)
SELECT bukrs belnr gjahr
INTO TABLE doc_int
UP TO 100 ROWS
FROM bkpf
WHERE bukrs = bukrs AND
* Normally, you would probably only want normal documents, thatis
* documents with BSTAT = ' '. So you would change the nextline.
* On the other hand, you might want documents for all values ofBSTAT,
* but not want to hardcode the values. In that case, you canretrieve
* values from the domain of BSTAT and put them in a range tableand
* use the range table in the next line.
bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V','W', 'Z') AND
budat = budat.
CHECK sy-subrc = 0.
WRITE budat TO disp_date.
PERFORM display_documents
TABLES doc_int
USING 'Posting date'
disp_date
space
space.
ENDFORM. " posting_date_actuals
*&---------------------------------------------------------------------*
*& Form cost_center_actuals
*&---------------------------------------------------------------------*
* Retrieve documents for a cost center
*----------------------------------------------------------------------*
FORM cost_center_actuals
USING kokrs
kostl
gjahr.
DATA: covp_int TYPE TABLE OF covp,
disp_cc(10).
CHECK NOT kostl IS INITIAL.
* Uses primary index (4.7)
SELECT SINGLE objnr
FROM csks
INTO csks-objnr
WHERE kokrs = kokrs
AND kostl = kostl.
CHECK sy-subrc = 0.
* COVP is a view. This uses index COEP~1 (4.7)
SELECT refbk refbn refgj refbz
FROM covp
INTO TABLE doc_int
UP TO 100 ROWS
WHERE lednr = '00'
AND objnr = csks-objnr
AND gjahr = gjahr
AND wrttp IN ('04', '11')
AND versn = '000'.
CHECK sy-subrc = 0.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = kostl
IMPORTING
output = disp_cc.
PERFORM display_documents
TABLES doc_int
USING 'Cost Center'
disp_cc
space
space.
ENDFORM. " cost_center_actuals
*&---------------------------------------------------------------------*
*& Form gl_actuals
*&---------------------------------------------------------------------*
* BKPF and BSEG have a number of secondaryindex tables. These are
* tables that are indexed by GL customer orvendor number and have
* data that is in both BKPF and BSEG. Thesesecondary index tables
* that have an 'i' in the third character ofthe name contain open
* items. Those with an 'a' contain cleareditems. In practice, you
* may only one or the other. In this program Iam retrieving both.
*
* Here we get documents related to a GL.
*----------------------------------------------------------------------*
FORM gl_actuals
USING bukrs
hkont
gjahr.
DATA: disp_gl(10).
CHECK NOT hkont IS INITIAL.
* Uses primary index (4.7)
SELECT bukrs belnr gjahr buzei
FROM bsis
INTO TABLE doc_int
UP TO 100 ROWS
WHERE bukrs = bukrs
AND hkont = hkont
AND gjahr = gjahr.
* Uses primary index (4.7)
SELECT bukrs belnr gjahr buzei
FROM bsas
APPENDING TABLE doc_int
UP TO 100 ROWS
WHERE bukrs = bukrs
AND hkont = hkont
AND gjahr = gjahr.
DESCRIBE TABLE doc_int LINES no_lines.
CHECK no_lines > 0.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = hkont
IMPORTING
output = disp_gl.
PERFORM display_documents
TABLES doc_int
USING 'GL Account'
disp_gl
space
space.
ENDFORM. " gl_actuals
*&---------------------------------------------------------------------*
*& Form vendor_actuals
*&---------------------------------------------------------------------*
* Here we get documents related to avendor.
*----------------------------------------------------------------------*
FORM vendor_actuals
USING bukrs
lifnr
gjahr.
DATA: disp_vendor(10).
CHECK NOT lifnr IS INITIAL.
* Uses primary index (4.7)
SELECT bukrs belnr gjahr buzei
FROM bsik
INTO TABLE doc_int
UP TO 100 ROWS
WHERE bukrs = bukrs
AND lifnr = lifnr
AND gjahr = gjahr.
* Uses primary index (4.7)
SELECT bukrs belnr gjahr buzei
FROM bsak
APPENDING TABLE doc_int
UP TO 100 ROWS
WHERE bukrs = bukrs
AND lifnr = lifnr
AND gjahr = gjahr.
DESCRIBE TABLE doc_int LINES no_lines.
CHECK no_lines > 0.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = lifnr
IMPORTING
output = disp_vendor.
PERFORM display_documents
TABLES doc_int
USING 'Vendor'
disp_vendor
space
space.
ENDFORM. " vendor_actuals
*&---------------------------------------------------------------------*
*& Form customer_actuals
*&---------------------------------------------------------------------*
* Here we get documents related to acustomer.
*----------------------------------------------------------------------*
FORM customer_actuals
USING bukrs
kunnr
gjahr.
DATA: disp_customer(10).
CHECK NOT kunnr IS INITIAL.
* Uses primary index (4.7)
SELECT bukrs belnr gjahr buzei
FROM bsid
INTO TABLE doc_int
UP TO 100 ROWS
WHERE bukrs = bukrs
AND kunnr = kunnr
AND gjahr = gjahr.
* Uses primary index (4.7)
SELECT bukrs belnr gjahr buzei
FROM bsad
APPENDING TABLE doc_int
UP TO 100 ROWS
WHERE bukrs = bukrs
AND kunnr = kunnr
AND gjahr = gjahr.
DESCRIBE TABLE doc_int LINES no_lines.
CHECK no_lines > 0.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = kunnr
IMPORTING
output = disp_customer.
PERFORM display_documents
TABLES doc_int
USING 'Customer'
disp_customer
space
space.
ENDFORM. " customer_actuals
*&---------------------------------------------------------------------*
*& Form po_actuals
*&---------------------------------------------------------------------*
* Table BKPF has a useful index on AWTYP andAWKEY. Here, we use
* this to retrieve documents for purchaseorders.
*----------------------------------------------------------------------*
FORM po_actuals
USING ebeln.
TYPES: BEGIN OF ekbe_type,
belnr TYPE ekbe-belnr,
gjahr TYPE ekbe-gjahr,
vgabe TYPE ekbe-vgabe,
END OF ekbe_type.
DATA: ekbe_int TYPE TABLE OF ekbe_type,
ekbe_wa TYPE ekbe_type.
DATA: v_reference TYPE bkpf-awtyp, "Referenceprocedure
v_objectkey TYPEbkpf-awkey. "Object key
DATA:disp_po(10).
CHECK NOT ebeln IS INITIAL.
* Uses primary index (4.7)
SELECT belnr gjahr
FROM ekbe
INTO TABLE ekbe_int
UP TO 100 ROWS
WHERE ebeln = ebeln
AND vgabe IN ('1', '2'). "1 - GR, 2 - IR
CHECK sy-subrc = 0.
SORT ekbe_int.
DELETE ADJACENT DUPLICATES FROM ekbe_int.
LOOP AT ekbe_int INTO ekbe_wa.
v_objectkey+00(10) = ekbe_wa-belnr.
v_objectkey+10(10) =ekbe_wa-gjahr. "BELNR+YEAR
IF ekbe_wa-vgabe = '1'.
v_reference = 'MKPF'.
ELSE.
v_reference = 'RMRP'.
ENDIF.
* Uses index BKPF~4 (4.7)
SELECT SINGLE bukrs belnr gjahr "AccountingDoc Header
FROM bkpf
INTO doc_wa
WHERE awtyp = v_reference
AND awkey= v_objectkey.
IF sy-subrc = 0.
APPEND doc_wa TO doc_int.
ENDIF.
ENDLOOP.
CHECK no_lines > 0.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = ebeln
IMPORTING
output = disp_po.
PERFORM display_documents
TABLES doc_int
USING 'Purchase Order'
disp_po
space
space.
ENDFORM. " po_actuals
*&---------------------------------------------------------------------*
*& Form so_actuals
*&---------------------------------------------------------------------*
* Use AWTYP and AWKEY to retrieve documentsrelated to sales
*----------------------------------------------------------------------*
FORM so_actuals USING vbeln.
TYPES: BEGIN OF vbfa_type,
vbeln TYPE vbfa-vbeln,
END OF vbfa_type.
DATA: vbfa_int TYPE TABLE OF vbfa_type,
vbfa_wa TYPE vbfa_type.
DATA: v_reference TYPE bkpf-awtyp, "Referenceprocedure
v_objectkey TYPEbkpf-awkey. "Object key
DATA:disp_so(10).
CHECK NOT vbeln IS INITIAL.
* Uses primary index (4.7)
SELECT vbeln
FROM vbfa
INTOTABLE vbfa_int
UP TO 100 ROWS
WHERE vbelv = vbeln
AND vbtyp_n = 'P'. "Debit memo
CHECK sy-subrc = 0.
SORT vbfa_int.
DELETE ADJACENT DUPLICATES FROM vbfa_int.
LOOP AT vbfa_int INTO vbfa_wa.
v_objectkey+00(10) = vbfa_wa-vbeln. "BELNR
v_reference = 'VBRK'.
* Uses index BKPF~4 (4.7)
SELECT SINGLE bukrs belnr gjahr "AccountingDoc Header
FROM bkpf
INTO doc_wa
WHERE awtyp = v_reference
AND awkey= v_objectkey.
IF sy-subrc = 0.
APPEND doc_wa TO doc_int.
ENDIF.
ENDLOOP.
DESCRIBE TABLE doc_int LINES no_lines.
CHECK no_lines > 0.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = vbeln
IMPORTING
output = disp_so.
PERFORM display_documents
TABLES doc_int
USING 'Sales Document'
disp_so
space
space.
ENDFORM. " so_actuals
*&---------------------------------------------------------------------*
*& Form order_actuals
*&---------------------------------------------------------------------*
* Retrieve documents related to an order
*----------------------------------------------------------------------*
FORM order_actuals
USING aufnr
gjahr.
DATA: disp_order(10).
CHECK NOT aufnr IS INITIAL.
* Uses primary index (4.7)
SELECT SINGLE objnr
FROM aufk
INTO aufk-objnr
WHERE aufnr = aufnr.
CHECK sy-subrc = 0.
* COVP is a view. This uses index COEP~1 (4.7)
SELECT refbk refbn refgj refbz
FROM covp
INTO TABLE doc_int
UP TO 100 ROWS
WHERE lednr = '00'
AND objnr = aufk-objnr
AND gjahr = gjahr
AND wrttp IN ('04', '11')
AND versn = '000'.
CHECK sy-subrc = 0.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = aufnr
IMPORTING
output = disp_order.
PERFORM display_documents
TABLES doc_int
USING 'Order'
disp_order
space
space.
ENDFORM. " order_actuals
*&---------------------------------------------------------------------*
*& Form FM_actuals
*&---------------------------------------------------------------------*
* Not many institutions use Funds Management,but if you do, this
* is how to relate funds management documentsto FI documents.
*----------------------------------------------------------------------*
FORM fm_actuals
USING fikrs
gjahr
fistl
fonds.
DATA: disp_cfc(10),
disp_fund(10).
CHECK NOT fikrs IS INITIAL AND
NOT fistl IS INITIAL.
* Uses index FMIFIIT~3 (4.7)
SELECT bukrs knbelnr kngjahr knbuzei
FROM fmifiit
INTO TABLE doc_int
UP TO 100 ROWS
WHERE fistl = fistl
AND fonds = fonds.
CHECK sy-subrc = 0.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = fistl
IMPORTING
output = disp_cfc.
IF NOT fonds IS INITIAL.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input =fonds
IMPORTING
output = disp_fund.
ENDIF.
PERFORM display_documents
TABLES doc_int
USING 'Fund Center'
disp_cfc
'Fund'
disp_fund.
ENDFORM. " FM_actuals
