Posted: Tue Nov 18, 2008 1:33 am Post subject: Show results of queries using Dynpro
Code:
*======================================================================*
* PROGRAMA...: ZZSQL_QUERY_DYNPRO *
*----------------------------------------------------------------------*
* AUTOR......: Luciano Rebuffi. *
*----------------------------------------------------------------------*
* OBJETIVO...: Realizar consultas de SQL y visualizar los datos en un *
* ALV. *
* Para realizar la consulta, ingresar la sentencia de la *
* siguiente forma: *
* - SINGLE: Defino el SELECT SINGLE. *
* - SEL: Defino los campos de los cuales se obtendran los *
* datos. *
* - DISTINCT: Defino el SELECT DISTINCT. *
* - S_TAB: Defino las tablas BD involucradas. *
* - JOIN: En caso que se quiera relacionar mediante un *
* JOIN varias tablas BD, se debe relacionar los *
* campos. *
* - DELJ: Borro el JOIN creado. *
* - VISJ: Visualizo el FROM creado. *
* - WHR: Ingresar los campos y los valores para realizar *
* la consulta a las tablas ingresadas. *
* - GRO: Selecciono los campos para el GROUP BY. *
* - HAV: Ingresar los campos y los valores para definir *
* el HAVING. *
* - ORD: Selecciono los campos y la clasificacion para *
* el ORDER BY. *
*----------------------------------------------------------------------*
* PARAMETROS.: ROWS: Ingresar la cantidad de registros para la *
* consulta. Por default, se toma la cantidad maxima de *
* entradas seleccionadas para el usuario. *
* CONV: Se setea en la visualizacion del ALV la *
* conversion del dominio definida para el campo. *
* TEC: Se setea en la visualizacion del ALV el nombre *
* tecnico o la descipcion de la columna. *
*----------------------------------------------------------------------*
* OPCIONES: *
* - VIS: Visualizo la sentencia completa del QUERY. *
* - EXC: Ejecuta el QUERY. (F8) *
*----------------------------------------------------------------------*
CONSTANTS:
c_inner(15) VALUE 'INNER JOIN',
c_left(15) VALUE 'LEFT OUTER JOIN',
c_max TYPE t606t-bezei VALUE 'MAX( col )',
c_min TYPE t606t-bezei VALUE 'MIN( col )',
c_avg TYPE t606t-bezei VALUE 'AVG( col )',
c_sum TYPE t606t-bezei VALUE 'SUM( col )',
c_ct1 TYPE t606t-bezei VALUE 'COUNT( DISTINCT col )',
c_ct2 TYPE t606t-bezei VALUE 'COUNT( * )',
c_asc TYPE t606t-bezei VALUE 'ASCENDING',
c_des TYPE t606t-bezei VALUE 'DESCENDING'.
DATA:
st_restr TYPE sscr_restrict_ds,
wa_layout TYPE slis_layout_alv,
wa_cat TYPE slis_fieldcat_alv. " WA catalogo
* Tabla para campos seleccionados en la visualizacion.
DATA: BEGIN OF st_select,
tabname LIKE dd03l-tabname,
fieldname LIKE dd03l-fieldname,
position LIKE dd03l-position,
keyflag LIKE dd03l-keyflag,
rollname LIKE dd03l-rollname,
domname LIKE dd03l-domname,
reftable LIKE dd03l-reftable,
reffield LIKE dd03l-reffield,
datatype TYPE dd03l-datatype,
scrtext_l LIKE dd04t-scrtext_l,
aggregate(30),
distinct(1),
name TYPE slis_fieldcat_alv-fieldname,
sel(1),
END OF st_select.
* Tabla para campos seleccionados.
DATA: BEGIN OF st_field,
tabname LIKE dd03l-tabname,
fieldname LIKE dd03l-fieldname,
position LIKE dd03l-position,
keyflag LIKE dd03l-keyflag,
rollname LIKE dd03l-rollname,
domname LIKE dd03l-domname,
reftable LIKE dd03l-reftable,
reffield LIKE dd03l-reffield,
datatype TYPE dd03l-datatype,
scrtext_l LIKE dd04t-scrtext_l,
clas(30),
sel(1),
END OF st_field.
* Datos realacionados al JOIN de las tablas.
DATA: BEGIN OF st_join,
tab1 LIKE dd03l-tabname,
but1 LIKE icon-id,
field1 LIKE dd03l-fieldname,
opt LIKE rsdsselopt-option,
tab2 LIKE dd03l-tabname,
but2 LIKE icon-id,
field2 LIKE dd03l-fieldname,
join(15) TYPE c,
END OF st_join.
* Auxiliary objects for filling RESTRICT
DATA opt_list TYPE sscr_opt_list.
DATA ass TYPE sscr_ass.
DATA:
ti_from TYPE TABLE OF rssource-line,
code TYPE TABLE OF rssource-line.
* Tabla para campos seleccionados en la visualizacion.
DATA:
ti_order LIKE st_field OCCURS 0 WITH HEADER LINE,
ti_group LIKE st_field OCCURS 0 WITH HEADER LINE,
ti_select LIKE st_select OCCURS 0 WITH HEADER LINE,
ti_join LIKE st_join OCCURS 0 WITH HEADER LINE.
DATA:
ti_filter TYPE slis_t_filter_alv,
ti_cat TYPE slis_t_fieldcat_alv. " TI catalogo
* Define the object to be passed to the RESTRICTION parameter
DATA restrict TYPE sscr_restrict.
* Tablas para la pantalla de seleccion dinamica.
DATA:
t_expr TYPE rsds_texpr,
t_where TYPE rsds_twhere,
t_fldranges TYPE rsds_trange,
t_tables TYPE rsdstabs OCCURS 0 WITH HEADER LINE,
t_fields TYPE rsdsfields OCCURS 0 WITH HEADER LINE,
t_rsdsevents TYPE rsdsevents OCCURS 0 WITH HEADER LINE,
t_rsdsevtfld TYPE rsdsevflds OCCURS 0 WITH HEADER LINE,
t_fcode TYPE rsdsfcode OCCURS 0 WITH HEADER LINE,
t_dfies TYPE TABLE OF dfies WITH HEADER LINE.
DATA:
prog(8) TYPE c,
msg(120) TYPE c,
lin(3) TYPE c,
wrd(10) TYPE c,
off(3) TYPE c,
lc_kind TYPE c VALUE 'T',
v_selid TYPE rsdynsel-selid,
v_noactflds TYPE sy-tfill,
l_tabname TYPE ddobjname,
functxt TYPE smp_dyntxt.
*----------------------------------------------------------------------*
* PARAMETROS
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK bl01 WITH FRAME TITLE tit .
* Select
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 1(7) selec,
COMMENT 9(8) singl,
POSITION 20.
PARAMETER: single AS CHECKBOX. "Single
SELECTION-SCREEN: PUSHBUTTON 24(45) sel USER-COMMAND sel
VISIBLE LENGTH 12.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 9(8) disti,
POSITION 20.
PARAMETER: distinct AS CHECKBOX. "DISTINCT
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: SKIP.
* From
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 1(10) from,
POSITION 21.
SELECT-OPTIONS s_tab FOR rsrd1-tbma_val NO INTERVALS.
SELECTION-SCREEN: PUSHBUTTON 50(45) join USER-COMMAND join
VISIBLE LENGTH 12.
* Where
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 1(10) whr1,
PUSHBUTTON 24(45) whr USER-COMMAND whr
VISIBLE LENGTH 12.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: SKIP.
* GROUP
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 1(10) gro1,
PUSHBUTTON 24(45) gro USER-COMMAND gro
VISIBLE LENGTH 12.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: SKIP.
* HAVING
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 1(10) hav1,
PUSHBUTTON 24(45) hav USER-COMMAND hav
VISIBLE LENGTH 12.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: SKIP.
* ORDER
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 1(10) ord1,
PUSHBUTTON 24(45) ord USER-COMMAND ord
VISIBLE LENGTH 12.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN END OF BLOCK bl01.
SELECTION-SCREEN: BEGIN OF LINE,
COMMENT 1(15) text1,
POSITION 20.
PARAMETER: rows TYPE rseumod-tbmaxsel. "Up To N rows
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE,
COMMENT 1(16) text3,
POSITION 20.
PARAMETER: conv AS CHECKBOX. "Conversion
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE,
COMMENT 1(16) text4,
POSITION 20.
PARAMETER: tec AS CHECKBOX DEFAULT 'X'. "Nombre tecnico
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE,
COMMENT 1(16) text5,
POSITION 20.
PARAMETER: deb AS CHECKBOX. "Debug
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: SKIP.
SELECTION-SCREEN: BEGIN OF LINE,
PUSHBUTTON 1(45) vis USER-COMMAND vis
VISIBLE LENGTH 12,
* Boton de Borrar todo en el STATUS GUI.
SELECTION-SCREEN: FUNCTION KEY 1.
*-----------------------------------------------------------------------
* DEFINICION DE MACROS
*-----------------------------------------------------------------------
DEFINE icon_create.
* Creo para el boton indicado, el icono y los textos correspondientes.
call function 'ICON_CREATE'
exporting
name = &2
text = &3
info = &4
importing
result = &1
exceptions
others = 0.
* Obtengo la cantidad maxima de entradas seleccionadas para el usuario.
SELECT SINGLE tbmaxsel
INTO rows
FROM rseumod
WHERE uname EQ sy-uname.
tit = 'Parametros:'.
text1 = 'Up To N rows'.
text3 = 'Sin conv. x dom.'.
text4 = 'Nombre tecnico'.
text5 = 'Debug'.
singl = 'SINGLE'.
selec = 'SELECT'.
disti = 'DISTINCT'.
from = 'FROM'.
whr1 = 'WHERE'.
gro1 = 'GROUP BY'.
hav1 = 'HAVING'.
ord1 = 'ORDER BY'.
* Agrego el boton de borrar todo en STATUS GUI.
functxt-icon_id = icon_delete.
functxt-quickinfo = 'Borrar todo'.
sscrfields-functxt_01 = functxt.
* icon_create: RESULT name text info.
icon_create:
* Creo la visualizacion del boton de SELECT en la DYNPRO.
sel icon_select_detail 'Campos' 'Visualizar campos',
* Creo la visualizacion del boton de JOIN en la dynpro.
join icon_filter 'JOIN' 'Crear JOIN',
* Creo la visualizacion del boton de Borrar JOIN en la dynpro.
delj icon_delete ' ' 'Borrar JOIN',
* Creo la visualizacion del boton de Visualizar FROM en la dynpro.
visj icon_display ' ' 'Visualizar FROM',
* Creo la visualizacion del boton de WHERE en la dynpro.
whr icon_filter 'Condicion' 'Crear WHERE',
* Creo la visualizacion del boton de SQL en la dynpro.
vis icon_display 'SQL' 'Visualizar QUERY',
* Creo la visualizacion del boton de SQL en la dynpro.
exc icon_execute_object 'Ejecutar' 'Ejecutar QUERY',
* Creo la visualizacion del boton de GROUP BY en la dynpro.
gro icon_target_group 'Crear' 'Crear GROUP BY',
* Creo la visualizacion del boton de HAVING en la dynpro.
hav icon_create 'Crear' 'Crear HAVING',
* Creo la visualizacion del boton de ORDER BY en la dynpro.
ord icon_sort_up 'Crear' 'Crear ORDER BY'.
PERFORM select_options_restrict.
*-----------------------------------------------------------------------
* AT SELECTION-SCREEN
*-----------------------------------------------------------------------
AT SELECTION-SCREEN.
CASE sy-ucomm.
WHEN 'FC01'.
CLEAR: s_tab, distinct, single.
REFRESH:
ti_select, ti_join, t_expr, t_where, t_fldranges, t_tables,
t_fields, t_rsdsevents, t_rsdsevtfld, t_fcode, t_dfies,
ti_from, code, ti_order, ti_group, s_tab.
PERFORM free.
WHEN 'SEL'.
PERFORM set_fields.
WHEN 'JOIN'.
PERFORM set_join.
WHEN 'DELJ'.
REFRESH: ti_from, ti_join.
FREE MEMORY ID 'ZJOIN'.
WHEN 'VISJ'.
PERFORM set_from.
PERFORM editor_table TABLES ti_from
USING 'FROM'.
WHEN 'WHR'.
PERFORM set_where USING 'WHERE'.
WHEN 'ORD'.
PERFORM set_order_by.
WHEN 'GRO'.
PERFORM set_group_by.
WHEN 'HAV'.
PERFORM set_where USING 'HAVING'.
WHEN 'VIS'.
REFRESH code.
PERFORM set_query TABLES code.
CHECK code[] IS NOT INITIAL.
PERFORM editor_table TABLES code
USING 'QUERY'.
WHEN 'EXC'.
PERFORM start-of-selection.
WHEN OTHERS.
* Do Nothing !!.
ENDCASE.
*----------------------------------------------------------------------*
* FORMS
*----------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form set_fields
*&---------------------------------------------------------------------*
* Defino los campos a visualizar.
*----------------------------------------------------------------------*
FORM set_fields .
FIELD-SYMBOLS <fs> LIKE LINE OF ti_select.
DATA lt_sel LIKE st_select OCCURS 0 WITH HEADER LINE.
PERFORM debug.
PERFORM check_s_tab.
IMPORT ti_select FROM MEMORY ID 'ZSELECT'.
lt_sel[] = ti_select[].
REFRESH ti_select.
PERFORM get_fields TABLES ti_select.
SORT ti_select BY tabname position.
IF lt_sel[] IS NOT INITIAL.
LOOP AT lt_sel WHERE sel EQ 'X'.
* Recorro las posiciones seleccionadas anteriormente.
READ TABLE ti_select ASSIGNING <fs>
WITH KEY tabname = lt_sel-tabname
fieldname = lt_sel-fieldname.
CHECK sy-subrc IS INITIAL AND <fs> IS ASSIGNED.
* Marco la linea.
MOVE lt_sel TO <fs>.
ENDLOOP.
ENDIF.
PERFORM armar_alv_fields.
EXPORT ti_select TO MEMORY ID 'ZSELECT'.
ENDFORM. " set_fields
*&---------------------------------------------------------------------*
*& Form get_fields
*&---------------------------------------------------------------------*
* Obtengo los campos de las tablas
*----------------------------------------------------------------------*
FORM get_fields TABLES pt_table TYPE table.
CHECK s_tab[] IS NOT INITIAL.
SELECT DISTINCT a~tabname a~fieldname a~position a~keyflag a~rollname
a~domname a~reftable a~reffield a~datatype b~scrtext_l
INTO CORRESPONDING FIELDS OF TABLE pt_table
FROM dd03l AS a INNER JOIN dd04t AS b
ON a~rollname EQ b~rollname
WHERE a~tabname IN s_tab
AND a~as4local EQ 'A'
AND a~datatype NE 'CLNT'
AND b~ddlanguage EQ sy-langu.
ENDFORM. " get_fields
*&---------------------------------------------------------------------*
*& Form armar_alv_fields
*&---------------------------------------------------------------------*
* Armo el ALV.
*----------------------------------------------------------------------*
FORM armar_alv_fields .
DATA:
l_repid LIKE sy-repid.
PERFORM set_fieldcat1.
PERFORM definir_layout USING 'TI_SELECT'
CHANGING wa_layout.
*&---------------------------------------------------------------------*
*& Form set_fieldcat1
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM set_fieldcat1 .
*&---------------------------------------------------------------------*
*& Form asignar_campo_a_catalogo
*&---------------------------------------------------------------------*
* Asignar campo al catalogo ALV
*----------------------------------------------------------------------*
* -->P_FIELDNAME Nombre de campo en el reporte
* -->P_KEY Indicador 'campo clave'
* -->P_ROW_POS N° fila
* -->P_COL_POS N° columna
*----------------------------------------------------------------------*
FORM asignar_campo_a_catalogo TABLES pt_cat STRUCTURE wa_cat
USING p_fieldname LIKE wa_cat-fieldname
p_key LIKE wa_cat-key
p_col_pos TYPE c
p_fix_column LIKE wa_cat-fix_column
p_no_out LIKE wa_cat-no_out
p_icon LIKE wa_cat-icon
p_hotspot LIKE wa_cat-hotspot
p_checkbox LIKE wa_cat-checkbox
p_input LIKE wa_cat-input
p_reptext_ddic LIKE wa_cat-reptext_ddic.
STATICS l_col_pos LIKE sy-cucol.
* Asignar atributos de campo en el catalogo de cabecera ALV
CLEAR wa_cat.
READ TABLE pt_cat INTO wa_cat
WITH KEY fieldname = p_fieldname.
CHECK sy-subrc EQ 0.
IF NOT p_reptext_ddic IS INITIAL.
wa_cat-seltext_l = wa_cat-seltext_m =
wa_cat-seltext_s = wa_cat-reptext_ddic = p_reptext_ddic.
ENDIF.
IF p_col_pos EQ 'X'.
* Inicializo el conteo para ordenar las columnas.
l_col_pos = 1.
ELSE.
ADD 1 TO l_col_pos.
ENDIF.
*&---------------------------------------------------------------------*
*& Form definir_layout
*&---------------------------------------------------------------------*
* Definir atributos del layout de reporte
*----------------------------------------------------------------------*
FORM definir_layout USING p_table TYPE any
CHANGING p_layout TYPE slis_layout_alv .
*&---------------------------------------------------------------------*
*& Form check_s_tab
*&---------------------------------------------------------------------*
* Verifico que se haya seleccionado alguna tabla BD.
*----------------------------------------------------------------------*
FORM check_s_tab .
CHECK s_tab[] IS INITIAL.
MESSAGE 'Ingrese una tabla.'(e01) TYPE 'I'.
STOP.
ENDFORM. " check_s_tab
*&---------------------------------------------------------------------*
*& Form validar_datos
*&---------------------------------------------------------------------*
* Validaciones.
*----------------------------------------------------------------------*
FORM validar_datos .
*&---------------------------------------------------------------------*
*& Form set_where
*&---------------------------------------------------------------------*
* Armo la condicion del WHERE.
*----------------------------------------------------------------------*
FORM set_where USING p_texto TYPE any.
* Defino las tablas involucradas, de las cuales se pueden tomar los
* campos para los parametros de seleccion.
CLEAR t_tables.
t_tables-prim_tab = s_tab-low.
APPEND t_tables.
* Cargo la tabla de la cual se toman los campos.
l_tabname = s_tab-low.
* Obtengo los campos de la tabla.
CALL FUNCTION 'DDIF_FIELDINFO_GET'
EXPORTING
tabname = l_tabname
TABLES
dfies_tab = t_dfies
EXCEPTIONS
not_found = 1
internal_error = 2
OTHERS = 3.
IF t_dfies[] IS NOT INITIAL.
* Recorro solo los campos clave de la tabla.
LOOP AT t_dfies WHERE keyflag EQ 'X' AND
datatype NE 'CLNT'.
* Agrego los campos que se visualizan como parametros de seleccion por
* DEFAULT en la DYNPRO.
t_fields-tablename = s_tab-low.
t_fields-fieldname = t_dfies-fieldname.
APPEND t_fields.
ENDLOOP.
ENDIF.
ENDLOOP.
break d4jp.
CONCATENATE 'Z' p_texto '2' INTO l_text.
IMPORT t_fldranges FROM MEMORY ID l_text.
CONCATENATE 'Z' p_texto '3' INTO l_text.
IMPORT t_expr FROM MEMORY ID l_text.
CONCATENATE 'Z' p_texto '1' INTO l_text.
EXPORT t_where TO MEMORY ID l_text.
CONCATENATE 'Z' p_texto '2' INTO l_text.
EXPORT t_fldranges TO MEMORY ID l_text.
CONCATENATE 'Z' p_texto '3' INTO l_text.
EXPORT t_expr TO MEMORY ID l_text.
ENDFORM. " set_where
*&---------------------------------------------------------------------*
*& Form SELECT_OPTIONS_RESTRICT
*&---------------------------------------------------------------------*
* Restringo la seleccion de tablas BD.
*----------------------------------------------------------------------*
FORM select_options_restrict .
* NOINTERVLS: BT y NB no permitidos.
CLEAR opt_list.
MOVE 'NOINTERVLS' TO opt_list-name.
MOVE 'X' TO: opt_list-options-cp,
opt_list-options-eq,
opt_list-options-ge,
opt_list-options-gt,
opt_list-options-le,
opt_list-options-lt,
opt_list-options-ne,
opt_list-options-np.
APPEND opt_list TO restrict-opt_list_tab.
* KIND = 'S': Aplica solo al SELECT-OPTION S_TAB.
CLEAR ass.
MOVE: 'S' TO ass-kind,
'S_TAB' TO ass-name,
'I' TO ass-sg_main,
' ' TO ass-sg_addy,
'NOINTERVLS' TO ass-op_main.
APPEND ass TO restrict-ass_tab.
*&---------------------------------------------------------------------*
*& Form check_s_tab_duplicates
*&---------------------------------------------------------------------*
* Verifico que no se hayan duplicado tablas BD.
*----------------------------------------------------------------------*
FORM check_s_tab_duplicates .
DATA: l_tfill1 TYPE sy-tfill,
l_tfill2 TYPE sy-tfill.
*&---------------------------------------------------------------------*
*& Form check_where
*&---------------------------------------------------------------------*
* Verifico si se selecciono alguna condicion para el WHERE.
*----------------------------------------------------------------------*
FORM check_where .
DATA l_tfill TYPE sy-tfill.
IMPORT t_where FROM MEMORY ID 'ZWHERE1'.
IMPORT t_fldranges FROM MEMORY ID 'ZWHERE2'.
DESCRIBE TABLE t_fldranges LINES l_tfill.
CHECK l_tfill IS INITIAL.
MESSAGE 'Ingrese una condicion de seleccion.'(e03) TYPE 'I'.
STOP.
ENDFORM. " check_where
*&---------------------------------------------------------------------*
*& Form check_fields
*&---------------------------------------------------------------------*
* Verifico si se seleccionaron campos para el SELECT.
*----------------------------------------------------------------------*
FORM check_fields .
IMPORT ti_select FROM MEMORY ID 'ZSELECT'.
READ TABLE ti_select WITH KEY sel = 'X'.
CHECK sy-subrc IS NOT INITIAL.
MESSAGE 'Seleccione los campos a visualizar.'(e04) TYPE 'I'.
STOP.
ENDFORM. " check_fields
*&---------------------------------------------------------------------*
*& Form set_join
*&---------------------------------------------------------------------*
* Defino el JOIN de las tablas.
*----------------------------------------------------------------------*
FORM set_join .
DATA:
l_tfill TYPE sy-tfill,
l_answer TYPE c.
PERFORM debug.
DESCRIBE TABLE s_tab LINES l_tfill.
CHECK l_tfill > 1.
IMPORT ti_join FROM MEMORY ID 'ZJOIN'.
DO.
PERFORM get_sel_table1 CHANGING ti_join-tab1.
PERFORM get_tipo_join CHANGING ti_join-join.
PERFORM get_sel_table2 USING ti_join-tab1
CHANGING ti_join-tab2.
PERFORM set_new_join USING ti_join-tab1
ti_join-tab2
ti_join-join.
PERFORM armar_alv_join.
CALL FUNCTION 'POPUP_TO_CONFIRM'
EXPORTING
text_question = 'Crear un nuevo JOIN ?'(t09)
IMPORTING
answer = l_answer
EXCEPTIONS
text_not_found = 1
OTHERS = 2.
CHECK l_answer NE '1'.
* Cancelo
EXIT.
ENDDO.
EXPORT ti_join TO MEMORY ID 'ZJOIN'.
ENDFORM. " set_join
*&---------------------------------------------------------------------*
*& Form set_code
*&---------------------------------------------------------------------*
* Genero el codigo fuente.
*----------------------------------------------------------------------*
FORM set_code .
DATA: rownum TYPE i,
mystring TYPE string,
crows(8) TYPE c,
l_tfill TYPE sy-tfill.
DATA:
ls_select LIKE st_select.
FIELD-SYMBOLS:
<fs> LIKE st_select.
* Nota: Se toma como linea para el codigo fuente, 72 caracteres.
REFRESH code.
MOVE 'ZSUBPOOL' TO prog.
APPEND 'REPORT ZSUBPOOL.' TO code.
APPEND '' TO code.
APPEND 'Form DoSQL USING rows TYPE RSEUMOD-TBMAXSEL.' TO code.
APPEND '' TO code.
* Creo la tabla interna que contiene los datos.
APPEND '* Creo la tabla interna.' TO code.
APPEND 'data : begin of TI_TAB occurs 0,' TO code.
CLEAR rownum.
LOOP AT ti_select ASSIGNING <fs>
WHERE sel EQ 'X'.
ADD 1 TO rownum.
WRITE rownum TO crows.
CONCATENATE 'WFLD' crows INTO mystring.
CONDENSE mystring NO-GAPS.
* Agrego el nombre del campo.
<fs>-name = mystring.
IF <fs>-aggregate NE c_ct2 AND <fs>-aggregate NE c_ct1.
CONCATENATE mystring 'LIKE' <fs>-tabname INTO mystring
SEPARATED BY space.
CONCATENATE mystring '-' <fs>-fieldname ',' INTO mystring.
ELSE.
* COUNT( * ).
CONCATENATE mystring 'TYPE i, " COUNT( * )' INTO mystring
SEPARATED BY space.
ENDIF.
APPEND mystring TO code.
ENDLOOP.
APPEND ' end of TI_TAB.' TO code.
APPEND '' TO code.
APPEND 'Type-Pools : Slis.' TO code.
APPEND '' TO code.
APPEND 'DATA: l_oref TYPE REF TO cx_root,' TO code.
APPEND ' text TYPE string. ' TO code.
APPEND 'DATA : l_repid LIKE sy-repid.' TO code.
APPEND 'DATA : fcat TYPE SLIS_T_FIELDCAT_ALV.' TO code.
APPEND 'DATA : wcat LIKE LINE OF FCAT.' TO code.
APPEND 'DATA : l_dbcnt(50) TYPE c.' TO code.
APPEND 'DATA : MyString type STRING.' TO code.
APPEND 'DATA : MyTitle type LVC_TITLE.' TO code.
APPEND '' TO code.
APPEND 'MOVE sy-repid TO l_repid.' TO code.
APPEND '' TO code.
APPEND '* Create Field Catalogue' TO code.
CLEAR rownum.
LOOP AT ti_select WHERE sel EQ 'X'.
ADD 1 TO rownum.
CONCATENATE ' wcat-fieldname = ' '''' ti_select-name '''' '.'
INTO mystring.
APPEND mystring TO code.
CONCATENATE ' wcat-tabname = ' '''TI_TAB''' '.'
INTO mystring.
APPEND mystring TO code.
CONCATENATE ' wcat-ref_fieldname = ' '''' ti_select-fieldname
'''.' INTO mystring.
APPEND mystring TO code.
CONCATENATE ' wcat-ref_tabname = ' '''' ti_select-tabname
'''.' INTO mystring.
APPEND mystring TO code.
IF conv IS NOT INITIAL.
* Se setea el catalogo para que no realice la conversion definida
* en el dominio del campo.
CONCATENATE ' wcat-no_convext = ' '''X''' '.'
INTO mystring.
ELSE.
CONCATENATE ' wcat-no_convext = ' ''' ''' '.'
INTO mystring.
ENDIF.
APPEND mystring TO code.
IF ti_select-aggregate EQ c_ct1.
* COUNT( DISTINCT col ).
MOVE ti_select-aggregate TO mystring.
REPLACE 'col' INTO mystring WITH ti_select-fieldname.
PERFORM col_head TABLES code
USING mystring.
ELSE.
IF tec IS NOT INITIAL.
* Se visualiza el nombre tecnico de la columna.
PERFORM col_head TABLES code
USING ti_select-fieldname.
ENDIF.
ENDIF.
IF ti_select-reftable IS NOT INITIAL AND
ti_select-reffield IS NOT INITIAL AND
( ti_select-datatype EQ 'QUAN' OR
ti_select-datatype EQ 'CURR' ).
* Campo de importe o cantidad referenciado a otro campo.
READ TABLE ti_select INTO ls_select
WITH KEY tabname = ti_select-reftable
fieldname = ti_select-reffield
sel = 'X'.
IF sy-subrc IS INITIAL.
* El campo al cual se hace referencia fue seleccionado. Por esto,
* asigno la referencia.
IF ti_select-datatype EQ 'QUAN'.
CONCATENATE ' wcat-qfieldname = ' '''' ls_select-name ''''
'.' INTO mystring.
ELSEIF ti_select-datatype EQ 'CURR'.
CONCATENATE ' wcat-cfieldname = ' '''' ls_select-name ''''
'.' INTO mystring.
ENDIF.
ENDIF.
ENDIF.
IF ti_select-datatype EQ 'DEC' OR
ti_select-datatype EQ 'INT1' OR
ti_select-datatype EQ 'INT2' OR
ti_select-datatype EQ 'INT4' OR
ti_select-datatype EQ 'NUMC' OR
ti_select-datatype EQ 'CURR' OR
ti_select-datatype EQ 'QUAN' .
CONCATENATE ' wcat-do_sum = ' '''X''' '.' INTO mystring.
ENDIF.
IF rownum > 99.
* El ALV solo puede tratar hasta 99 columnas, sino tira un DUMP.
APPEND ' wcat-no_out = ''X''.' TO code.
ENDIF.
APPEND ' Append Wcat to Fcat.' TO code.
APPEND '' TO code.
ENDLOOP.
APPEND '' TO code.
APPEND 'Try.' TO code.
APPEND '' TO code.
APPEND '* QUERY. ' TO code.
PERFORM set_query TABLES code.
IF NOT single IS INITIAL.
APPEND '' TO code.
APPEND 'APPEND TI_TAB.' TO code.
ENDIF.
* Visualizo la cantidad de registros.
APPEND '' TO code.
APPEND 'MOVE SY-DBCNT TO MyString.' TO code.
APPEND 'CONCATENATE ''Cantidad de registros:'' MyString' TO code.
APPEND 'INTO l_dbcnt SEPARATED BY space.' TO code.
APPEND 'MESSAGE l_dbcnt TYPE ''S''.' TO code.
* Activo el debug.
APPEND '' TO code.
CONCATENATE 'PERFORM debug IN PROGRAM' sy-repid ' IF FOUND.'
INTO mystring SEPARATED BY space.
APPEND mystring TO code.
APPEND '' TO code.
APPEND 'CALL FUNCTION ''REUSE_ALV_LIST_DISPLAY''' TO code.
APPEND ' EXPORTING' TO code.
APPEND ' IT_FIELDCAT = FCAT' TO code.
APPEND ' TABLES' TO code.
APPEND ' T_OUTTAB = TI_TAB.' TO code.
APPEND '' TO code.
APPEND '' TO code.
APPEND 'CATCH CX_ROOT INTO l_oref.' TO code.
APPEND '' TO code.
APPEND '* Manejo de excepciones.' TO code.
APPEND ' text = l_oref->get_text( ).' TO code.
APPEND ' MESSAGE text TYPE ''I''.' TO code.
APPEND '' TO code.
APPEND 'EndTry.' TO code.
APPEND '' TO code.
APPEND 'EndForm. "DoSQL.' TO code.
ENDFORM. " set_code
*&---------------------------------------------------------------------*
*& Form ejecutar
*&---------------------------------------------------------------------*
* Ejecuto el codigo generado para la consulta.
*----------------------------------------------------------------------*
FORM ejecutar .
PERFORM debug.
GENERATE SUBROUTINE POOL code NAME prog
MESSAGE msg
LINE lin
WORD wrd
OFFSET off.
IF sy-subrc IS NOT INITIAL.
MESSAGE msg TYPE 'I'.
STOP.
ELSE.
PERFORM dosql IN PROGRAM (prog)
USING rows IF FOUND.
IF sy-subrc IS NOT INITIAL.
MESSAGE 'Generate SUBROUTINE POOL Succeeded BUT Call failed'(e05)
TYPE 'I'.
STOP.
ENDIF.
ENDIF.
ENDFORM. " ejecutar
*&---------------------------------------------------------------------*
*& Form get_sel_table1
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM get_sel_table1 CHANGING p_tab1 TYPE dd03l-tabname.
DATA: BEGIN OF spoplist OCCURS 0.
INCLUDE STRUCTURE spopli.
DATA: END OF spoplist.
DATA: antwort TYPE c.
LOOP AT s_tab.
CLEAR spoplist.
AT FIRST.
spoplist-selflag = 'X'.
ENDAT.
spoplist-varoption = s_tab-low.
APPEND spoplist.
ENDLOOP.
IF sy-subrc IS INITIAL.
* Seleccionaron una opcion.
EXIT.
ENDIF.
ENDDO.
IF antwort = 'A'.
* Cancelaron.
STOP.
ELSE.
READ TABLE spoplist WITH KEY selflag = 'X'.
MOVE spoplist-varoption TO p_tab1.
ENDIF.
ENDFORM. " get_sel_table1
*&---------------------------------------------------------------------*
*& Form get_tipo_join
*&---------------------------------------------------------------------*
* Seleccione el tipo de JOIN.
*----------------------------------------------------------------------*
FORM get_tipo_join CHANGING p_join TYPE any.
DATA: BEGIN OF spoplist OCCURS 0.
INCLUDE STRUCTURE spopli.
DATA: END OF spoplist.
DATA: antwort TYPE c.
CALL FUNCTION 'POPUP_TO_DECIDE_LIST'
EXPORTING
mark_flag = 1
mark_max = 1
textline1 = 'Seleccione el tipo de'(t05)
textline2 = 'JOIN.'(t06)
titel = 'Seleccione tipo JOIN:'(t07)
IMPORTING
answer = antwort
TABLES
t_spopli = spoplist
EXCEPTIONS
not_enough_answers = 1
too_much_answers = 2
too_much_marks = 3
OTHERS = 4.
IF sy-subrc IS INITIAL.
* Seleccionaron una opcion.
EXIT.
ENDIF.
ENDDO.
IF antwort = 'A'.
* Cancelaron.
STOP.
ELSE.
READ TABLE spoplist WITH KEY selflag = 'X'.
MOVE spoplist-varoption TO p_join.
ENDIF.
ENDFORM. " get_tipo_join
*&---------------------------------------------------------------------*
*& Form get_sel_table2
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM get_sel_table2 USING p_tab1 TYPE dd03l-tabname
CHANGING p_tab2 TYPE dd03l-tabname.
DATA: BEGIN OF spoplist OCCURS 0.
INCLUDE STRUCTURE spopli.
DATA: END OF spoplist.
DATA:
l_tabix TYPE sy-tabix,
antwort TYPE c.
LOOP AT s_tab WHERE low NE p_tab1.
CLEAR spoplist.
IF l_tabix IS INITIAL.
spoplist-selflag = 'X'.
ENDIF.
ADD 1 TO l_tabix.
spoplist-varoption = s_tab-low.
APPEND spoplist.
ENDLOOP.
IF sy-subrc IS INITIAL.
* Seleccionaron una opcion.
EXIT.
ENDIF.
ENDDO.
IF antwort = 'A'.
* Cancelaron.
STOP.
ELSE.
READ TABLE spoplist WITH KEY selflag = 'X'.
MOVE spoplist-varoption TO p_tab2.
ENDIF.
ENDFORM. " get_sel_table2
*&---------------------------------------------------------------------*
*& Form set_new_join
*&---------------------------------------------------------------------*
* Agrego una linea a la tabla interna.
*----------------------------------------------------------------------*
FORM set_new_join USING p_tab1 TYPE dd03l-tabname
p_tab2 TYPE dd03l-tabname
p_join TYPE any.
CLEAR: ti_join-field1, ti_join-field2.
MOVE:
p_tab1 TO ti_join-tab1,
icon_select_block TO ti_join-but1,
'EQ' TO ti_join-opt,
p_tab2 TO ti_join-tab2,
icon_select_block TO ti_join-but2,
p_join TO ti_join-join.
APPEND ti_join.
SORT ti_join BY tab1 field1 tab2 field2.
DELETE ADJACENT DUPLICATES FROM ti_join.
ENDFORM. " set_new_join
*&---------------------------------------------------------------------*
*& Form armar_alv_join
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM armar_alv_join .
DATA:
l_repid LIKE sy-repid.
PERFORM set_fieldcat2.
PERFORM definir_layout USING 'TI_JOIN'
CHANGING wa_layout.
PERFORM filter2 USING ti_join-tab1
ti_join-tab2.
l_repid = sy-repid.
*&---------------------------------------------------------------------*
*& Form set_fieldcat2
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM set_fieldcat2 .
*&---------------------------------------------------------------------*
*& Form user_command
*&---------------------------------------------------------------------*
* Evaluar acciones del usuario
*----------------------------------------------------------------------*
FORM user_command USING ucomm TYPE sy-ucomm
sfields TYPE slis_selfield.
FIELD-SYMBOLS:
<fs> LIKE st_join,
<fs1> LIKE st_select,
<fs2> LIKE st_field.
* Seleccion de opcion.
CASE ucomm.
* DOUBLE CLICK.
WHEN '&IC1'.
* Rescatar el registro seleccionado.
IF sfields-tabname EQ 'ST_SELECT'.
READ TABLE ti_select ASSIGNING <fs1> INDEX sfields-tabindex.
ELSEIF sfields-tabname EQ 'ST_JOIN'.
READ TABLE ti_join ASSIGNING <fs> INDEX sfields-tabindex.
ELSEIF sfields-tabname EQ 'ST_FIELD'.
READ TABLE ti_order ASSIGNING <fs2> INDEX sfields-tabindex.
ELSE.
EXIT.
ENDIF.
CHECK sy-subrc EQ 0.
CASE sfields-fieldname.
* ########################################################
* ##### Boton #####
* ########################################################
WHEN 'BUT1' OR 'BUT2' .
PERFORM f4_field USING sfields-fieldname
CHANGING <fs>.
IF <fs>-field1 IS NOT INITIAL AND
<fs>-field2 IS NOT INITIAL.
PERFORM set_new_join USING <fs>-tab1
<fs>-tab2
<fs>-join.
ENDIF.
*&---------------------------------------------------------------------*
*& Form f4_field
*&---------------------------------------------------------------------*
* F4 para los campos de la tabla.
*----------------------------------------------------------------------*
FORM f4_field USING p_field TYPE slis_selfield-fieldname
CHANGING ps_join LIKE st_join.
DATA:
l_tabname TYPE dd03l-tabname,
lc_sel LIKE sy-tabix, "Posicion seleccionada.
lt_fld LIKE st_select OCCURS 0 WITH HEADER LINE.
DATA: BEGIN OF lt_helptab OCCURS 0,
feld(40),
END OF lt_helptab.
* Definiciones para ayudas de busqueda.
DATA: BEGIN OF lt_help_fields OCCURS 0.
INCLUDE STRUCTURE help_value.
DATA: END OF lt_help_fields.
IF p_field EQ 'BUT1'.
MOVE ps_join-tab1 TO l_tabname.
ELSEIF p_field EQ 'BUT2'.
MOVE ps_join-tab2 TO l_tabname.
ELSE.
EXIT.
ENDIF.
SELECT DISTINCT a~tabname a~fieldname a~position a~keyflag a~rollname
a~domname b~scrtext_l
INTO CORRESPONDING FIELDS OF TABLE lt_fld
FROM dd03l AS a INNER JOIN dd04t AS b
ON a~rollname EQ b~rollname
WHERE a~tabname EQ l_tabname
AND a~as4local EQ 'A'
AND a~datatype NE 'CLNT'
AND b~ddlanguage EQ sy-langu.
SORT lt_fld BY tabname position.
LOOP AT lt_fld.
APPEND lt_fld-fieldname TO lt_helptab.
APPEND lt_fld-keyflag TO lt_helptab.
APPEND lt_fld-scrtext_l TO lt_helptab.
ENDLOOP.
CALL FUNCTION 'F4TOOL_F4FUNCTION_BRIDGE'
EXPORTING
tabname = space
fieldname = space
display_only = space
IMPORTING
selected_index = lc_sel
TABLES
value_tab = lt_helptab
fields_tab = lt_help_fields.
READ TABLE lt_fld INDEX lc_sel.
CHECK sy-subrc EQ 0.
IF p_field EQ 'BUT1'.
MOVE lt_fld-fieldname TO ps_join-field1.
ELSEIF p_field EQ 'BUT2'.
MOVE lt_fld-fieldname TO ps_join-field2.
ENDIF.
ENDFORM. " f4_field
*&---------------------------------------------------------------------*
*& Form z_append_ihelp_fields
*&---------------------------------------------------------------------*
* Rutina auxiliar para ayuda de busqueda
*----------------------------------------------------------------------*
FORM z_append_ihelp_fields TABLES pt_help_fields STRUCTURE help_value
USING value(p_0110)
value(p_0111)
value(p_0112). "#EC *
*&---------------------------------------------------------------------*
*& Form check_JOIN
*&---------------------------------------------------------------------*
* Verifico si todas las tablas BD son utilizadas en el JOIN.
*----------------------------------------------------------------------*
FORM check_join .
DATA: l_tfill TYPE sy-tfill.
DESCRIBE TABLE s_tab LINES l_tfill.
CHECK l_tfill > 1.
IMPORT ti_join FROM MEMORY ID 'ZJOIN'.
LOOP AT s_tab.
LOOP AT ti_join WHERE tab1 EQ s_tab-low OR
tab2 EQ s_tab-low.
EXIT.
ENDLOOP.
CHECK sy-subrc IS NOT INITIAL.
MESSAGE 'JOIN Incompleto.'(e06) TYPE 'I'.
STOP.
ENDLOOP.
ENDFORM. " check_JOIN
*&---------------------------------------------------------------------*
*& Form filter2
*&---------------------------------------------------------------------*
* Filtro el ALV del JOIN para que se visualicen solo los datos
* de las tablas correspondientes.
*----------------------------------------------------------------------*
FORM filter2 USING p_tab1 TYPE dd03l-tabname
p_tab2 TYPE dd03l-tabname.
DATA ls_filter TYPE slis_filter_alv.
REFRESH ti_filter.
CHECK p_tab1 IS NOT INITIAL AND
p_tab2 IS NOT INITIAL.
READ TABLE ti_cat INTO wa_cat
WITH KEY fieldname = 'TAB1'.
*&---------------------------------------------------------------------*
*& Form set_from
*&---------------------------------------------------------------------*
* Armo la parte del FROM para el SELECT.
*----------------------------------------------------------------------*
FORM set_from .
DATA:
l_field1 TYPE rssource-line,
l_field2 TYPE rssource-line,
l_tfill TYPE sy-tfill,
st_from TYPE rssource-line.
DATA: BEGIN OF lt_tab OCCURS 0,
tabname TYPE dd03l-tabname,
END OF lt_tab.
PERFORM debug.
CHECK s_tab[] IS NOT INITIAL.
REFRESH ti_from.
DESCRIBE TABLE s_tab LINES l_tfill.
IF l_tfill EQ 1.
READ TABLE s_tab INDEX 1.
CONCATENATE 'FROM' s_tab-low INTO st_from SEPARATED BY space.
APPEND st_from TO ti_from.
ELSE.
LOOP AT ti_join WHERE field1 IS NOT INITIAL AND
field2 IS NOT INITIAL.
IF lt_tab[] IS INITIAL.
APPEND ti_join-tab1 TO lt_tab.
APPEND ti_join-tab2 TO lt_tab.
* Ej: scarr INNER JOIN spfli ON
CONCATENATE 'FROM' ti_join-tab1 ti_join-join ti_join-tab2 'ON'
INTO st_from SEPARATED BY space.
APPEND st_from TO ti_from.
* 1er campo.
CONCATENATE ti_join-tab1 '~' ti_join-field1
INTO l_field1.
* 2do campo.
CONCATENATE ti_join-tab2 '~' ti_join-field2
INTO l_field2.
CONCATENATE l_field1 '=' l_field2
INTO st_from SEPARATED BY space.
APPEND st_from TO ti_from.
CONTINUE.
ENDIF.
READ TABLE lt_tab WITH KEY tabname = ti_join-tab1.
IF sy-subrc IS NOT INITIAL.
* Tabla BD nueva para el JOIN.
* Ej: INNER JOIN sflight ON
CONCATENATE ti_join-join ti_join-tab1 'ON'
INTO st_from SEPARATED BY space.
APPEND st_from TO ti_from.
* 1er campo.
CONCATENATE ti_join-tab1 '~' ti_join-field1
INTO l_field1.
* 2do campo.
CONCATENATE ti_join-tab2 '~' ti_join-field2
INTO l_field2.
CONCATENATE l_field1 '=' l_field2
INTO st_from SEPARATED BY space.
APPEND st_from TO ti_from.
CONTINUE.
ENDIF.
READ TABLE lt_tab WITH KEY tabname = ti_join-tab2.
IF sy-subrc IS NOT INITIAL.
* Tabla BD nueva para el JOIN.
* Ej: INNER JOIN sflight ON
CONCATENATE ti_join-join ti_join-tab2 'ON'
INTO st_from SEPARATED BY space.
APPEND st_from TO ti_from.
* 1er campo.
CONCATENATE ti_join-tab1 '~' ti_join-field1
INTO l_field1.
* 2do campo.
CONCATENATE ti_join-tab2 '~' ti_join-field2
INTO l_field2.
CONCATENATE l_field1 '=' l_field2
INTO st_from SEPARATED BY space.
APPEND st_from TO ti_from.
CONTINUE.
ENDIF.
* 1er campo.
CONCATENATE ti_join-tab1 '~' ti_join-field1
INTO l_field1.
* 2do campo.
CONCATENATE ti_join-tab2 '~' ti_join-field2
INTO l_field2.
CONCATENATE 'AND' l_field1 '=' l_field2
INTO st_from SEPARATED BY space.
APPEND st_from TO ti_from.
ENDLOOP.
ENDIF.
ENDFORM. " set_from
*&---------------------------------------------------------------------*
*& Form editor_table
*&---------------------------------------------------------------------*
* Editor de texto.
*----------------------------------------------------------------------*
FORM editor_table TABLES pt_lines TYPE table
USING p_name TYPE any.
DATA:
l_changed LIKE s38e-buf_varied,
l_subrc LIKE sy-subrc.
*&---------------------------------------------------------------------*
*& Form set_query
*&---------------------------------------------------------------------*
* Armo el SELECT dela consulta.
*----------------------------------------------------------------------*
FORM set_query TABLES pt_code TYPE table.
DATA:
l_field TYPE string,
mystring TYPE string,
l_tfill TYPE sy-tfill.
PERFORM debug.
MOVE 'SELECT' TO mystring.
IF single EQ 'X'.
CONCATENATE mystring 'SINGLE' INTO mystring SEPARATED BY space.
ELSEIF distinct EQ 'X'.
CONCATENATE mystring 'DISTINCT' INTO mystring SEPARATED BY space.
ENDIF.
APPEND mystring TO pt_code.
DESCRIBE TABLE s_tab LINES l_tfill.
* Agrego los campos seleccionados al SELECT.
LOOP AT ti_select WHERE sel EQ 'X'.
IF l_tfill = 1.
* Una sola tabla.
MOVE ti_select-fieldname TO l_field.
ELSE.
* JOIN
CONCATENATE ti_select-tabname ti_select-fieldname INTO l_field
SEPARATED BY '~'.
ENDIF.
IF ti_select-distinct IS NOT INITIAL AND
( ti_select-aggregate NE c_ct1 OR
ti_select-aggregate NE c_ct2 ).
CONCATENATE 'DISTINCT' l_field INTO l_field
SEPARATED BY space.
ENDIF.
IF ti_select-aggregate IS INITIAL.
APPEND l_field TO pt_code.
ELSE.
MOVE ti_select-aggregate TO mystring.
REPLACE 'col' INTO mystring WITH l_field.
APPEND mystring TO pt_code.
ENDIF.
ENDLOOP.
IF single IS INITIAL.
APPEND 'INTO TABLE TI_TAB' TO pt_code.
ELSE.
APPEND 'INTO TI_TAB' TO pt_code.
ENDIF.
IF NOT rows IS INITIAL AND single IS INITIAL.
APPEND 'UP TO ROWS rows' TO pt_code.
ENDIF.
* FROM.
PERFORM set_from.
APPEND LINES OF ti_from TO pt_code.
* WHERE.
PERFORM get_where TABLES pt_code
USING 'WHERE'.
* GROUP BY.
PERFORM get_group_by TABLES pt_code.
* HAVING.
PERFORM get_where TABLES pt_code
USING 'HAVING'.
* ORDER BY.
PERFORM get_order_by TABLES pt_code.
APPEND '.' TO pt_code.
APPEND ' ' TO pt_code.
ENDFORM. " set_query
*&---------------------------------------------------------------------*
*& Form get_where
*&---------------------------------------------------------------------*
* Armo el WHERE.
*----------------------------------------------------------------------*
FORM get_where TABLES pt_code TYPE table
USING p_texto TYPE any.
FIELD-SYMBOLS:
<fst> TYPE rsds_where,
<fs> TYPE rsdswhere.
DATA:
l_text(30),
l_cant TYPE i,
l_flag TYPE c,
l_tfill TYPE sy-tfill,
l_field TYPE rsdswhere-line,
ls_fldranges TYPE rsds_range,
ls_frange TYPE rsds_frange,
ls_where TYPE rsds_where.
REFRESH: t_fldranges, t_where.
CONCATENATE 'Z' p_texto '1' INTO l_text.
IMPORT t_where FROM MEMORY ID l_text.
CONCATENATE 'Z' p_texto '2' INTO l_text.
IMPORT t_fldranges FROM MEMORY ID l_text.
DESCRIBE TABLE s_tab LINES l_tfill.
* Modifico el nombre de los campos.
LOOP AT t_fldranges INTO ls_fldranges.
* Verifico que se hayan cargados valores para una tabla.
CHECK ls_fldranges-frange_t[] IS NOT INITIAL.
LOOP AT ls_fldranges-frange_t INTO ls_frange.
CHECK ls_frange-selopt_t[] IS NOT INITIAL.
READ TABLE t_where ASSIGNING <fst>
WITH KEY tablename = ls_fldranges-tablename.
CHECK sy-subrc IS INITIAL.
MOVE 'X' TO l_flag.
IF l_tfill > 1.
* Seteo el campo con la tabla.
CONCATENATE ls_fldranges-tablename '~' ls_frange-fieldname
INTO l_field.
ENDIF.
LOOP AT <fst>-where_tab ASSIGNING <fs>.
CONDENSE <fs>.
IF l_tfill > 1.
REPLACE ls_frange-fieldname IN <fs> WITH l_field.
ENDIF.
ENDLOOP.
ENDLOOP.
ENDLOOP.
CHECK l_flag EQ 'X'.
* Por lo menos hay una condicion cargada.
APPEND p_texto TO pt_code.
* Recorro las tablas.
LOOP AT t_where INTO ls_where.
CHECK ls_where-where_tab[] IS NOT INITIAL.
* Verifico que se haya cargado una condicion para alguno de los campos
* de la tabla.
IF l_cant IS NOT INITIAL.
* Si hay mas de una tabla, agrego el AND por cada tabla nueva.
APPEND 'AND' TO pt_code.
ENDIF.
ADD 1 TO l_cant.
LOOP AT ls_where-where_tab ASSIGNING <fs>.
* Agrego la condicion a la consulta.
APPEND <fs> TO pt_code.
ENDLOOP.
ENDLOOP.
ENDFORM. " get_where
*&---------------------------------------------------------------------*
*& Form START-OF-SELECTION
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM start-of-selection .
*&---------------------------------------------------------------------*
*& Form f4_aggregate
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM f4_aggregate CHANGING ps_select LIKE st_select.
DATA:
lc_sel LIKE sy-tabix. "Posicion seleccionada.
DATA: BEGIN OF lt_text OCCURS 0,
line(40),
END OF lt_text.
DATA: BEGIN OF lt_helptab OCCURS 0,
feld(40),
END OF lt_helptab.
* Definiciones para ayudas de busqueda.
DATA: BEGIN OF lt_help_fields OCCURS 0.
INCLUDE STRUCTURE help_value.
DATA: END OF lt_help_fields.
APPEND c_max TO : lt_text, lt_helptab.
APPEND c_min TO : lt_text, lt_helptab.
APPEND c_avg TO : lt_text, lt_helptab.
APPEND c_sum TO : lt_text, lt_helptab.
APPEND c_ct1 TO : lt_text, lt_helptab.
APPEND c_ct2 TO : lt_text, lt_helptab.
PERFORM z_append_ihelp_fields TABLES lt_help_fields
USING :
'T606T' 'BEZEI' 'X'.
CALL FUNCTION 'F4TOOL_F4FUNCTION_BRIDGE'
EXPORTING
tabname = space
fieldname = space
display_only = space
IMPORTING
selected_index = lc_sel
TABLES
value_tab = lt_helptab
fields_tab = lt_help_fields.
READ TABLE lt_text INDEX lc_sel.
CHECK sy-subrc EQ 0.
MOVE lt_text-line TO ps_select-aggregate.
ENDFORM. " f4_aggregate
*&---------------------------------------------------------------------*
*& Form set_order_by
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM set_order_by .
FIELD-SYMBOLS <fs> LIKE st_field.
DATA lt_sel LIKE st_field OCCURS 0 WITH HEADER LINE.
PERFORM debug.
PERFORM check_s_tab.
IMPORT ti_order FROM MEMORY ID 'ZORDER'.
lt_sel[] = ti_order[].
REFRESH ti_order.
PERFORM get_fields TABLES ti_order.
SORT ti_order BY tabname position.
IF lt_sel[] IS NOT INITIAL.
LOOP AT lt_sel WHERE sel EQ 'X'.
* Recorro las posiciones seleccionadas anteriormente.
READ TABLE ti_order ASSIGNING <fs>
WITH KEY tabname = lt_sel-tabname
fieldname = lt_sel-fieldname.
CHECK sy-subrc IS INITIAL AND <fs> IS ASSIGNED.
* Marco la linea.
MOVE lt_sel TO <fs>.
ENDLOOP.
ENDIF.
PERFORM armar_alv_order.
EXPORT ti_order TO MEMORY ID 'ZORDER'.
ENDFORM. " set_order_by
*&---------------------------------------------------------------------*
*& Form armar_alv_order
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM armar_alv_order .
DATA:
l_repid LIKE sy-repid.
PERFORM set_fieldcat3.
PERFORM definir_layout USING 'TI_ORDER'
CHANGING wa_layout.
*&---------------------------------------------------------------------*
*& Form set_fieldcat3
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM set_fieldcat3 .
*&---------------------------------------------------------------------*
*& Form f4_clasif
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM f4_clasif CHANGING ps_order LIKE st_field.
DATA:
lc_sel LIKE sy-tabix. "Posicion seleccionada.
DATA: BEGIN OF lt_text OCCURS 0,
line(40),
END OF lt_text.
DATA: BEGIN OF lt_helptab OCCURS 0,
feld(40),
END OF lt_helptab.
* Definiciones para ayudas de busqueda.
DATA: BEGIN OF lt_help_fields OCCURS 0.
INCLUDE STRUCTURE help_value.
DATA: END OF lt_help_fields.
APPEND c_asc TO : lt_text, lt_helptab.
APPEND c_des TO : lt_text, lt_helptab.
PERFORM z_append_ihelp_fields TABLES lt_help_fields
USING :
'T606T' 'BEZEI' 'X'.
CALL FUNCTION 'F4TOOL_F4FUNCTION_BRIDGE'
EXPORTING
tabname = space
fieldname = space
display_only = space
IMPORTING
selected_index = lc_sel
TABLES
value_tab = lt_helptab
fields_tab = lt_help_fields.
READ TABLE lt_text INDEX lc_sel.
CHECK sy-subrc EQ 0.
MOVE lt_text-line TO ps_order-clas.
ENDFORM. " f4_clasif
*&---------------------------------------------------------------------*
*& Form get_order_by
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM get_order_by TABLES pt_code TYPE table.
DATA:
l_tfill TYPE sy-tfill,
l_field TYPE string.
IMPORT ti_order FROM MEMORY ID 'ZORDER'.
CHECK ti_order[] IS NOT INITIAL.
READ TABLE ti_order WITH KEY sel = 'X'.
CHECK sy-subrc IS INITIAL.
APPEND 'ORDER BY' TO pt_code.
DESCRIBE TABLE s_tab LINES l_tfill.
* Agrego los campos seleccionados al ORDER BY.
LOOP AT ti_order WHERE sel EQ 'X'.
IF l_tfill = 1.
* Una sola tabla.
MOVE ti_order-fieldname TO l_field.
ELSE.
* JOIN
CONCATENATE ti_order-tabname ti_order-fieldname INTO l_field
SEPARATED BY '~'.
ENDIF.
IF ti_order-clas IS NOT INITIAL.
CONCATENATE l_field ti_order-clas INTO l_field
SEPARATED BY space.
ENDIF.
APPEND l_field TO pt_code.
ENDLOOP.
ENDFORM. " get_order_by
*&---------------------------------------------------------------------*
*& Form set_GROUP_BY
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM set_group_by .
FIELD-SYMBOLS <fs> LIKE st_field.
DATA lt_sel LIKE st_field OCCURS 0 WITH HEADER LINE.
PERFORM debug.
PERFORM check_s_tab.
IMPORT ti_group FROM MEMORY ID 'ZGROUP'.
lt_sel[] = ti_group[].
REFRESH ti_group.
PERFORM get_fields TABLES ti_group.
SORT ti_group BY tabname position.
IF lt_sel[] IS NOT INITIAL.
LOOP AT lt_sel WHERE sel EQ 'X'.
* Recorro las posiciones seleccionadas anteriormente.
READ TABLE ti_group ASSIGNING <fs>
WITH KEY tabname = lt_sel-tabname
fieldname = lt_sel-fieldname.
CHECK sy-subrc IS INITIAL AND <fs> IS ASSIGNED.
* Marco la linea.
MOVE lt_sel TO <fs>.
ENDLOOP.
ENDIF.
PERFORM armar_alv_group.
EXPORT ti_group TO MEMORY ID 'ZGROUP'.
ENDFORM. " set_GROUP_BY
*&---------------------------------------------------------------------*
*& Form armar_alv_GROUP
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM armar_alv_group .
DATA:
l_repid LIKE sy-repid.
PERFORM set_fieldcat4.
PERFORM definir_layout USING 'TI_GROUP'
CHANGING wa_layout.
*&---------------------------------------------------------------------*
*& Form set_fieldcat4
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM set_fieldcat4 .
*&---------------------------------------------------------------------*
*& Form get_GROUP_BY
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM get_group_by TABLES pt_code TYPE table.
DATA:
l_tfill TYPE sy-tfill,
l_field TYPE string.
IMPORT ti_group FROM MEMORY ID 'GROUP'.
CHECK ti_group[] IS NOT INITIAL.
READ TABLE ti_group WITH KEY sel = 'X'.
CHECK sy-subrc IS INITIAL.
APPEND 'GROUP BY' TO pt_code.
DESCRIBE TABLE s_tab LINES l_tfill.
* Agrego los campos seleccionados al GROUP BY.
LOOP AT ti_group WHERE sel EQ 'X'.
IF l_tfill = 1.
* Una sola tabla.
MOVE ti_group-fieldname TO l_field.
ELSE.
* JOIN
CONCATENATE ti_group-tabname ti_group-fieldname INTO l_field
SEPARATED BY '~'.
ENDIF.
APPEND l_field TO pt_code.
ENDLOOP.
ENDFORM. " get_GROUP_BY
*&---------------------------------------------------------------------*
*& Form free
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM free .
*&---------------------------------------------------------------------*
*& Form col_head
*&---------------------------------------------------------------------*
* Encabezado de la columna del ALV.
*----------------------------------------------------------------------*
FORM col_head TABLES pt_code TYPE table
USING p_texto TYPE any.
DATA mystring TYPE string.
CONCATENATE ' wcat-seltext_s = ' 'wcat-seltext_m ='
INTO mystring SEPARATED BY space.
APPEND mystring TO pt_code.
CONCATENATE ' wcat-seltext_l = ' 'wcat-reptext_ddic = '
INTO mystring SEPARATED BY space.
APPEND mystring TO pt_code.
CONCATENATE ' ''' p_texto '''.'
INTO mystring.
APPEND mystring TO pt_code.
ENDFORM. " col_head
*&---------------------------------------------------------------------*
*& Form debug
*&---------------------------------------------------------------------*
* Debug.
*----------------------------------------------------------------------*
FORM debug .
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
