#1
|
|||
|
|||
Consulta entre tabla VBAK y STPO
Buen día amigos, recurro a sus conocimientos para que me orienten en como implementar la siguiente consulta:
De la tabla vbak voy a usar la fecha preferente de entrega para mostrar algunos datos como su documento de venta, la posicion, el material, hasta alli todo bien, pero tambien se debe mostrar el componente de la lista de materiales y su texto corto, he buscado las tablas y en stpo hay un campo idnrk que es el de componente y en esta tabla makt-maktx trae su texto corto ahora como puedo hacer la union de estas tablas. Por favor soy novato en este mundo de abap pero con muchas ganas de aprender. Saludos expertos. |
#2
|
|||
|
|||
Saludos brujop,
Con el material (MATNR) y el centro (WERKS) vas a la tabla MAST, allí encontrarás el campo STLNR que es la lista de material, con esa lista de material vas a la tabla STPO y tomas los IDNRK (Componentes)... |
#3
|
|||
|
|||
Muchas gracias por responder, he tratado de implementar la consulta pero no lo he conseguio, tendras algun ejemplo de como tratar los select y los loops.
Gracias por tu tiempo. |
#4
|
|||
|
|||
La consulta incial debe hacerse a la tabla MAST, que es allí donde se definen las listas de material por centro.
SELECT matnr werks stlan stlnr INTO TABLE lt_mast FROM mast FOR ALL ENTRIES IN lt_conteo_f WHERE matnr EQ Materiales de VBAP AND werks EQ Centros de VBAP. Con el resultado, vas a la tabla STPO para consultar los componentes. * Consulta posiciones de lista de material SELECT stlnr idnrk meins menge INTO TABLE lt_stpo FROM stpo WHERE stlnr EQ lt_mast-stlnr. "STLNR es el ID de la lista de material Al final, en LT_STPO deben quedar los componentes por lista de material. Salu2. |
#5
|
|||
|
|||
Muchas gracias DUARCO por responder, te comento que aun no puedo implementar la consulta, por favor te envio el codigo que estoy utilizando para que me puedas indicar en donde colocar el codigo.
TABLES: vbak, vbap, mast, stpo. *---------------------------------------------* * S E L E C T I O N S C R E E N * *---------------------------------------------* SELECTION-SCREEN: BEGIN OF BLOCK block01 WITH FRAME TITLE title01. PARAMETERS: so_werks TYPE vbap-werks OBLIGATORY DEFAULT 'PLJA'. SELECT-OPTIONS: so_vdatu FOR vbak-vdatu. SELECTION-SCREEN: END OF BLOCK block01. TYPES: BEGIN OF gty_result , vdatu TYPE vbak-vdatu, vbeln TYPE vbap-vbeln, werks TYPE vbap-werks, matnr TYPE mast-matnr, maktx TYPE makt-maktx, posnr TYPE stpo-posnr, idnrk TYPE stpo-idnrk, END OF gty_result. *---------------------------------------------* * D A T A D E C L A R A T I O N * *---------------------------------------------* DATA: gt_result TYPE STANDARD TABLE OF gty_result, gs_result LIKE LINE OF gt_result, go_alv TYPE REF TO cl_gui_alv_grid, gs_fcat TYPE lvc_s_fcat, gt_fcat TYPE lvc_t_fcat. START-OF-SELECTION. BREAK-POINT. SELECT vbak~vdatu vbap~vbeln vbap~werks INTO TABLE gt_result FROM vbap INNER JOIN vbak ON vbap~vbeln = vbak~vbeln WHERE vdatu IN so_vdatu. *---------------------------------------------* * A L V D I S P L A Y * *---------------------------------------------* * main program PERFORM main_prog. *&---------------------------------------------------------------------* *& FORM MAIN_PROG *&---------------------------------------------------------------------* * The main program *----------------------------------------------------------------------* FORM main_prog. * build field catalog for the ALV grid PERFORM create_fcat. * display list CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC' EXPORTING it_fieldcat_lvc = gt_fcat TABLES t_outtab = gt_result EXCEPTIONS program_error = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. " MAIN_PROG. *&---------------------------------------------------------------------* *& FORM CREATE_FCAT *&---------------------------------------------------------------------* * Generate field catalog for the ALV *----------------------------------------------------------------------* FORM create_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 1 . gs_fcat-fieldname = 'VDATU'. gs_fcat-tabname = 1. gs_fcat-datatype = 'DATS'. gs_fcat-inttype = 'D'. gs_fcat-domname = 'DATUM'. gs_fcat-intlen = '000016'. gs_fcat-coltext = 'FePrefEnt.'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 2 . gs_fcat-key = 'X'. gs_fcat-fieldname = 'VBELN'. gs_fcat-tabname = 1. gs_fcat-datatype = 'CHAR'. gs_fcat-inttype = 'C'. gs_fcat-domname = 'VBELN'. gs_fcat-intlen = '000020'. gs_fcat-coltext = 'Doc.venta'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 3 . gs_fcat-fieldname = 'WERKS'. gs_fcat-tabname = 1. gs_fcat-datatype = 'CHAR'. gs_fcat-inttype = 'C'. gs_fcat-domname = 'WERKS'. gs_fcat-intlen = '000008'. gs_fcat-coltext = 'Ce.'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 4 . gs_fcat-key = 'MATNR'. gs_fcat-fieldname = 'MATNR'. gs_fcat-tabname = 1. gs_fcat-datatype = 'CHAR'. gs_fcat-inttype = 'C'. gs_fcat-domname = 'MATNR'. gs_fcat-intlen = '000036'. gs_fcat-coltext = 'Material'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. ENDFORM. " CREATE_FCAT. Gracias por tu valioso tiempo. |
#6
|
|||
|
|||
En esta consulta:
SELECT vbak~vdatu vbap~vbeln vbap~werks VBAP~MATNR "A estos materiales se les consultarán las listas de material INTO TABLE gt_result FROM vbap INNER JOIN vbak ON vbap~vbeln = vbak~vbeln WHERE vdatu IN so_vdatu. debes adicionar el material. Luego podrías consultar la tabla MAST: SELECT matnr werks stlan stlnr INTO TABLE lt_mast FROM mast FOR ALL ENTRIES IN gt_result WHERE matnr EQ gt_result-matnr AND werks EQ gt_result-werks. Finalmente vas a la STPO para obtener los componentes de los materiales de VBAP. SELECT stlnr idnrk meins menge INTO TABLE lt_stpo FROM stpo WHERE stlnr EQ lt_mast-stlnr. "STLNR es el ID de la lista de material. Luego podrías recorrer la tabla gt_result e ir consultando con cada material/centro sus respectivos componentes. LOOP AT GT_RESULT INTO LWA_RESULT. READ TABLE LT_MAST INTO LWA_MAST WITH KEY matnr = LWA_RESULT-matnr werks = LWA_RESULT-werks. IF sy-subrc EQ 0. LOOP AT LT_STPO INTO LWA_STPO WHERE stlnr EQ LWA_MAST-STLNR. MOVE-CORRESPONDING LWA_RESULT TO LWA_IMPRIME_REPORTE. LWA_IMPRIME_REPORTE-IDNRK = LWA_STPO-IDNRK. "Componente APPEND LWA_IMPRIME_REPORTE INTO GT_IMPRIME_REPORTE. ENDLOOP. ENDIF. ENDLOOP. Al final GT_IMPRIME_REPORTE queda con los datos de materiales y sus componentes. |
#7
|
|||
|
|||
Duarco gracias por responder nuevamente, has sido muy amable al ayudarme, pero solo me falta mostrar el campo idnrk, por favor me puedes orientar derrepente debo de declarar mas variables, envio el código para que me ayudes otra vez si no fuera mucha molestia.
REPORT ypedidos. * Transferencia de tablas TABLES: vbak, vbap, mast, stpo. *---------------------------------------------* * S E L E C T I O N S C R E E N * *---------------------------------------------* SELECTION-SCREEN: BEGIN OF BLOCK block01 WITH FRAME TITLE title01. SELECT-OPTIONS: so_vdatu FOR vbak-vdatu. SELECTION-SCREEN: END OF BLOCK block01. *---------------------------------------------* * I N I T I A L I Z A T I O N * *---------------------------------------------* INITIALIZATION. SELECT SINGLE ddtext FROM dd02t INTO title01 WHERE tabname = 'VBAK' AND ddlanguage = sy-langu. "#EC * *---------------------------------------------* * T Y P E D E F I N I T I O N S * *---------------------------------------------* TYPES: BEGIN OF gty_result , vbeln TYPE vbap-vbeln, werks TYPE vbap-werks, posnr TYPE vbap-posnr, matnr TYPE mast-matnr, stlnr TYPE stpo-stlnr, idnrk TYPE stpo-idnrk, END OF gty_result. *---------------------------------------------* * D A T A D E C L A R A T I O N * *---------------------------------------------* DATA: gt_result TYPE STANDARD TABLE OF gty_result, gs_result LIKE LINE OF gt_result, gt_result1 TYPE STANDARD TABLE OF gty_result1 WITH HEADER LINE, gs_result1 LIKE LINE OF gt_result1, lt_mast TYPE STANDARD TABLE OF mast WITH HEADER LINE, st_mast TYPE mast, lt_stpo TYPE STANDARD TABLE OF stpo WITH HEADER LINE, st_stpo TYPE stpo, go_alv TYPE REF TO cl_gui_alv_grid, gs_fcat TYPE lvc_s_fcat, gt_fcat TYPE lvc_t_fcat. *---------------------------------------------* * D A T A S E L E C T I O N * *---------------------------------------------* START-OF-SELECTION. SELECT vbak~vdatu vbap~vbeln vbap~werks vbap~posnr vbap~matnr INTO CORRESPONDING FIELDS OF TABLE gt_result FROM vbap INNER JOIN vbak ON vbap~vbeln = vbak~vbeln WHERE vdatu IN so_vdatu. SELECT matnr werks stlan stlnr INTO TABLE lt_mast FROM mast FOR ALL ENTRIES IN gt_result WHERE matnr EQ gt_result-matnr AND werks EQ gt_result-werks. SELECT stlnr idnrk INTO TABLE lt_stpo FROM stpo WHERE stlnr EQ st_mast-stlnr. "STLNR es el ID de la lista de material. BREAK-POINT. LOOP AT gt_result INTO gs_result. READ TABLE lt_mast INTO st_mast WITH KEY matnr = gs_result-matnr werks = gs_result-werks. IF sy-subrc = 0. LOOP AT lt_stpo INTO st_stpo. MOVE-CORRESPONDING gs_result TO gs_result1. gs_result1-idnrk = st_stpo-idnrk. APPEND gs_result1 TO gt_result1. ENDLOOP. ENDIF. ENDLOOP. *---------------------------------------------* * A L V D I S P L A Y * *---------------------------------------------* * main program PERFORM main_prog. *&---------------------------------------------------------------------* *& FORM MAIN_PROG *&---------------------------------------------------------------------* * The main program *----------------------------------------------------------------------* FORM main_prog. * build field catalog for the ALV grid PERFORM create_fcat. * display list CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC' EXPORTING it_fieldcat_lvc = gt_fcat TABLES t_outtab = gt_result EXCEPTIONS program_error = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. " MAIN_PROG. *&---------------------------------------------------------------------* *& FORM CREATE_FCAT *&---------------------------------------------------------------------* * Generate field catalog for the ALV *----------------------------------------------------------------------* FORM create_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 1 . gs_fcat-key = 'X'. gs_fcat-fieldname = 'VBELN'. gs_fcat-tabname = 1. gs_fcat-datatype = 'CHAR'. gs_fcat-inttype = 'C'. gs_fcat-domname = 'VBELN'. gs_fcat-intlen = '000020'. gs_fcat-coltext = 'Doc.venta'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 2 . gs_fcat-fieldname = 'POSNR'. gs_fcat-tabname = 1. gs_fcat-datatype = 'CHAR'. gs_fcat-inttype = 'C'. gs_fcat-domname = 'MATNR'. gs_fcat-intlen = '000012'. gs_fcat-coltext = 'Posición'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 3 . gs_fcat-fieldname = 'WERKS'. gs_fcat-tabname = 1. gs_fcat-datatype = 'CHAR'. gs_fcat-inttype = 'C'. gs_fcat-domname = 'WERKS'. gs_fcat-intlen = '000008'. gs_fcat-coltext = 'Ce.'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 4 . gs_fcat-fieldname = 'MATNR'. gs_fcat-tabname = 1. gs_fcat-datatype = 'CHAR'. gs_fcat-inttype = 'C'. gs_fcat-domname = 'MATNR'. gs_fcat-intlen = '000036'. gs_fcat-coltext = 'Material'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. CLEAR gs_fcat. gs_fcat-col_pos = 5 . gs_fcat-fieldname = 'IDNRK'. gs_fcat-tabname = 1. gs_fcat-datatype = 'CHAR'. gs_fcat-inttype = 'C'. gs_fcat-domname = 'MATNR'. gs_fcat-intlen = '000036'. gs_fcat-coltext = 'Componente'. gs_fcat-outputlen = gs_fcat-intlen. APPEND gs_fcat TO gt_fcat. ENDFORM. " CREATE_FCAT. Buen día. Úlima edición por brujop fecha: 26/10/13 a las 14:58:21. |
Herramientas | Buscar en Tema |
Desplegado | |
|
|