Hi all,
We have a Function Module for the Custom Extractor to get the data from VBRK VBFA, FPLT & FPLA tables and the performance is VERY VERY BAD. Could you please help me to improve the performance.
Thanks in advance.
Regards,
Venkat.
FUNCTION zbi_ccs_bp01 .
*"----
types
TYPES:BEGIN OF t_vbeln_range,
sign TYPE c LENGTH 1,
option TYPE c LENGTH 2,
low TYPE vbuk-vbeln,
high TYPE vbuk-vbeln,
END OF t_vbeln_range.
TYPES:BEGIN OF t_vbeln_vf_range,
sign TYPE c LENGTH 1,
option TYPE c LENGTH 2,
low TYPE vbrk-vbeln,
high TYPE vbrk-vbeln,
END OF t_vbeln_vf_range.
Declaration of internal table and work areas
DATA:l_r_vbeln TYPE STANDARD TABLE OF t_vbeln_range,
l_r_vbeln_vf TYPE STANDARD TABLE OF t_vbeln_vf_range,
l_wa_vbeln_vf TYPE t_vbeln_vf_range,
l_wa_vbeln TYPE t_vbeln_range,
l_whereclause TYPE string.
CONSTANTS: l_c_pack TYPE sy-tabix VALUE '1000'.
Maximum number of lines for DB table
STATICS: s_s_if TYPE srsc_s_if_simple,
s_counter_datapakid LIKE sy-tabix,
v_nodata TYPE c. " No Data available Flag
Initialization mode (first call by SAPI) or data transfer mode
(following calls) ?
IF i_initflag = sbiwa_c_flag_on.
Check DataSource validity
CASE i_dsource.
WHEN 'ZBWP_CCS_BP01'.
WHEN OTHERS.
IF 1 = 2. MESSAGE e009(r3). ENDIF.
this is a typical log call. Please write every error message like this
log_write 'E' "message type
'R3' "message class
'009' "message number
i_dsource "message variable 1
' '. "message variable 2
RAISE error_passed_to_mess_handler.
ENDCASE.
APPEND LINES OF i_t_select TO s_s_if-t_select.
Fill parameter buffer for data extraction calls
s_s_if-requnr = i_requnr.
s_s_if-dsource = i_dsource.
s_s_if-maxsize = i_maxsize.
APPEND LINES OF i_t_fields TO s_s_if-t_fields.
ELSE. "Initialization mode or data extraction ?
************************************************************************
Data transfer: First Call OPEN CURSOR + FETCH
Following Calls FETCH only
************************************************************************
First data package -> OPEN CURSOR
IF s_counter_datapakid = 0.
Determine number of database records to be read per FETCH statement
from input parameter I_MAXSIZE. If there is a one to one relation
between DataSource table lines and database entries, this is trivial.
In other cases, it may be impossible and some estimated value has to
be determined.
Read ranges table for Billing Doc no.
LOOP AT s_s_if-t_select INTO wa_select
WHERE fieldnm = 'VBELN_VF'.
l_wa_vbeln_vf-sign = wa_select-sign.
l_wa_vbeln_vf-option = wa_select-option.
l_wa_vbeln_vf-low = wa_select-low.
l_wa_vbeln_vf-high = wa_select-high.
APPEND l_wa_vbeln_vf TO l_r_vbeln_vf.
CLEAR l_wa_vbeln_vf.
ENDLOOP.
Read ranges table for Billing Doc no.
LOOP AT s_s_if-t_select INTO wa_select
WHERE fieldnm = 'VBELN'.
l_wa_vbeln-sign = wa_select-sign.
l_wa_vbeln-option = wa_select-option.
l_wa_vbeln-low = wa_select-low.
l_wa_vbeln-high = wa_select-high.
APPEND l_wa_vbeln TO l_r_vbeln.
CLEAR l_wa_vbeln.
ENDLOOP.
Delta process
SELECT SINGLE logsys
INTO t000-logsys
FROM t000
WHERE mandt = sy-mandt.
IF sy-subrc = 0.
CLEAR : roosgendlm, l_whereclause.
SELECT SINGLE *
FROM roosgendlm
INTO roosgendlm
WHERE oltpsource = i_dsource
AND slogsys = t000-logsys.
IF sy-subrc = 0 AND i_requnr NE 'TEST'..
v_date = sy-datum.
v_date1 = roosgendlm-deltaid+0(8).
v_time1 = roosgendlm-deltaid+8(6).
v_time1 = v_time1 - 1. "Safety limit of 30 min
v_date2 = v_date1 + 1.
CONCATENATE 'vbeln IN l_r_vbeln_vf' "Billing
'AND ( ( erdat = v_date1 AND erzet >= v_time1 ) OR erdat GE v_date2 ).'
INTO l_whereclause
SEPARATED BY space.
ELSE. " Init load
CONCATENATE 'vbeln IN l_r_vbeln_vf' "Billing
INTO l_whereclause SEPARATED BY space.
ENDIF.
ENDIF.
Get billing document header and item details
REFRESH i_billing.
SELECT vbeln
waerk
netwr
kunag
vkont
FROM vbrk
PACKAGE SIZE l_c_pack
APPENDING TABLE i_billing
WHERE (l_whereclause).
IF sy-subrc EQ 0.
SORT i_billing.
ENDIF.
Get Document Flow
REFRESH i_vbfa.
SELECT vbelv
posnv
vbeln
posnn
vbtyp_n
vbtyp_v
fplnr
fpltr
FROM vbfa
APPENDING TABLE i_vbfa
FOR ALL ENTRIES IN i_billing
WHERE vbelv IN l_r_vbeln
AND vbeln EQ i_billing-vbeln
AND vbtyp_n EQ 'M'
AND vbtyp_v EQ 'G'.
IF sy-subrc EQ 0.
SORT i_vbfa.
ENDIF.
Get the billing plan details
REFRESH i_plan.
SELECT a~fplnr
a~fpltr
b~fptyp
b~bedat
b~endat
b~vbeln
APPENDING TABLE i_plan
FROM fplt AS a INNER JOIN fpla AS b
ON afplnr = bfplnr
FOR ALL ENTRIES IN i_vbfa
WHERE a~fplnr EQ i_vbfa-fplnr
AND b~vbeln EQ i_vbfa-vbelv.
IF sy-subrc EQ 0 .
SORT i_plan BY fplnr fpltr.
ENDIF.
LOOP AT i_billing INTO wa_billing.
APPEND wa_billing to i_billing_final.
ENDLOOP.
REFRESH i_billing .
ENDSELECT.
ENDIF. "First data package ?
Fetch records into interface table.
named E_T_'Name of extract structure'.
************************************************
FETCH NEXT CURSOR S_CURSOR
APPENDING CORRESPONDING FIELDS
OF TABLE E_T_DATA
PACKAGE SIZE S_S_IF-MAXSIZE.
************************************************
v_currsize = '000000'.
IF v_nodata = 'Y'.
RAISE no_more_data.
ENDIF.
v_maxrec_bp01 = 1.
SORT i_plan BY fplnr fpltr.
SORT i_vbfa.
SORT i_billing_final.
DO.
r_tabix = r_tabix + v_maxrec_bp01.
Read plan details
READ TABLE i_plan INTO wa_plan INDEX r_tabix.
IF sy-subrc NE 0.
v_nodata = 'Y'.
EXIT.
ELSE.
wa_zbwp_ccs_bp01-fplnr = wa_plan-fplnr.
wa_zbwp_ccs_bp01-fpltr = wa_plan-fpltr.
wa_zbwp_ccs_bp01-bedat = wa_plan-bedat.
wa_zbwp_ccs_bp01-endat = wa_plan-endat.
wa_zbwp_ccs_bp01-waers = wa_plan-waers.
wa_zbwp_ccs_bp01-vbeln = wa_plan-vbeln.
Read billing details by billing plan and billing plan item
READ TABLE i_vbfa INTO wa_vbfa
WITH KEY fplnr = wa_plan-fplnr
fpltr = wa_plan-fpltr
vbelv = wa_plan-vbeln binary search.
IF sy-subrc EQ 0.
READ TABLE i_billing_final INTO wa_billing
WITH KEY vbeln = wa_vbfa-vbeln.
IF sy-subrc EQ 0.
wa_zbwp_ccs_bp01-vbeln_vf = wa_vbfa-vbeln.
wa_zbwp_ccs_bp01-fkart = wa_billing-fkart.
wa_zbwp_ccs_bp01-erdat = wa_billing-erdat.
wa_zbwp_ccs_bp01-kunag = wa_billing-kunag.
ENDIF.
ENDIF.
The details of document currency,company code,sales org,distribution
channel,division needs to be displayed for all the sales release,
partially released and open
IF wa_plan-vbeln IS NOT INITIAL.
wa_zbwp_ccs_bp01-waerk = wa_billing-waerk.
wa_zbwp_ccs_bp01-vkorg = wa_billing-vkorg.
wa_zbwp_ccs_bp01-vtweg = wa_billing-vtweg.
wa_zbwp_ccs_bp01-spart = wa_billing-spart.
ENDIF.
Read contract number by billing plan
READ TABLE i_vbfa INTO wa_vbfa
WITH KEY fplnr = wa_plan-fplnr
vbelv = wa_plan-vbeln binary search.
IF sy-subrc EQ 0.
READ TABLE i_billing_final INTO wa_billing
WITH KEY vbeln = wa_vbfa-vbeln.
IF sy-subrc EQ 0.
wa_zbwp_ccs_bp01-vkont = wa_billing-vkont.
ENDIF.
ENDIF.
APPEND wa_zbwp_ccs_bp01 TO e_t_data.
CLEAR wa_zbwp_ccs_bp01.
v_currsize = v_currsize + 1.
IF s_s_if-maxsize LE v_currsize.
CLEAR v_currsize.
EXIT.
ENDIF.
ENDIF.
ENDDO.
v_maxrec_bp01 = s_s_if-maxsize.
s_counter_datapakid = s_counter_datapakid + 1.
ENDIF. "Initialization mode or data extraction