AMDP - Usando WHILE, leitura tipo READ TABLE, variável de sistema

CLASS zcl_szk_amdp1 DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .

  PUBLIC SECTION.

    TYPES: BEGIN OF tp_transp,

             nodeid type char05,

             hierarchy type char05,

             parentid type char05,

             tknum type tknum,

             vbeln type likp-vbeln,

             posnr type lips-posnr,

             matnr type lips-matnr,

             maktx type makt-maktx,

             lfimg type lips-lfimg,

             meins type lips-meins,

           END OF tp_transp.

    TYPES tp_vttk type table of vttk WITH DEFAULT KEY.

    types tp_transp_tab type TABLE OF tp_transp with DEFAULT KEY

    .

    INTERFACES if_amdp_marker_hdb .

    METHODS get_transport_detail IMPORTING VALUE(iv_mandt) type sy-mandt

                                           VALUE(iv_tknum) type vttk-tknum

                                           default ' ' "Indica parâmetro opcional

                                 EXPORTING value(et_transp) type tp_transp_tab

                                           value(e_text) type char10.

    class-METHODS get_transporte1 FOR TABLE FUNCTION zteste_szk2.

  PROTECTED SECTION.

  PRIVATE SECTION.

ENDCLASS.

CLASS zcl_szk_amdp1 IMPLEMENTATION.

  METHOD get_transport_detail BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT

                                OPTIONS READ-ONLY USING VTTK vttp lips makt.

   DECLARE lt_transp TABLE LIKE :et_transp;

   DECLARE lv_index INTEGER;

   DECLARE lv_nodeid INTEGER;

   DECLARE lv_rows INTEGER;

   DECLARE lv_tknum "$ABAP.type( TKNUM )";

   DECLARE lv_pos INTEGER;

   it_vttk = select tknum

                from vttk

               WHERE mandt = :IV_MANDT;

   lv_rows = record_count( :it_vttk ); "igual ao LINES no ABAP

   lv_index = 1;

   while lv_index <= lv_rows do

     lv_nodeid = lv_index;

     lt_transp.tknum[ :lv_nodeid ] = :it_vttk.tknum[ :lv_index ];

     lt_transp.nodeid[ :lv_nodeid ] = :lv_nodeid;

     lv_index = lv_index + 1;

   END WHILE;

   it_rem = select vttp.tknum,

                   vttp.vbeln,

                   posnr,

                   lips.matnr,

                   maktx,

                   lfimg,

                   meins

              from vttp

                   INNER join :it_vttk as vttk

                   on vttk.tknum = vttp.tknum

                   INNER join lips

                   on lips.vbeln = vttp.vbeln

                   "Mandante é importante, podem vir linhas duplicadas 

                   "caso não seja informado

                   and lips.mandt = vttp.mandt 

                   INNER join makt

                   on makt.matnr = lips.matnr

                   and makt.mandt = lips.mandt

           where vttp.mandt = session_context('CLIENT') "tipo SY-MANDT

             AND makt.spras = session_context('LOCALE_SAP'); "tipo SY-LANGU

    lv_index = 1;

    lv_rows = record_count( :it_rem );

    while lv_index <= lv_rows do

     lv_nodeid = lv_nodeid + 1;

     lt_transp.tknum[ :lv_nodeid ] = :it_rem.tknum[ :lv_index ];

     lv_tknum = :it_rem.tknum[ :lv_index ];

     lt_transp.nodeid[ :lv_nodeid ] = :lv_nodeid;

     lt_transp.vbeln[ :lv_nodeid ] = :it_rem.vbeln[ :lv_index ];

     lt_transp.posnr[ :lv_nodeid ] = :it_rem.posnr[ :lv_index ];

     lt_transp.matnr[ :lv_nodeid ] = :it_rem.matnr[ :lv_index ];

     lt_transp.maktx[ :lv_nodeid ] = :it_rem.maktx[ :lv_index ];

     lt_transp.lfimg[ :lv_nodeid ] = :it_rem.lfimg[ :lv_index ];

     lt_transp.meins[ :lv_nodeid ] = :it_rem.meins[ :lv_index ];

     "SEARCH - faz a busca igual read table

     lv_pos = :lt_transp.search( ( tknum ), :it_rem.tknum[ :lv_index ] );

     IF lv_pos <> 0 THEN

         lt_transp.hierarchy[ :lv_nodeid ] = :lt_transp.nodeid[ :lv_pos ];

         lv_index = lv_index + 1;

     END IF;

    END WHILE ;

   et_transp = SELECT *

                FROM :lt_transp;

  ENDMETHOD.

  method get_transporte1 BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT

                               OPTIONS READ-ONLY using vttk vttp lips.

    return select vttk.mandt,

                  vttk.tknum,

                  vttp.vbeln,

                  posnr,

                  lfimg,

                  meins

             from vttk

                  INNER join vttp

                  on vttp.tknum = vttk.tknum

                  inner join lips

                  on lips.vbeln = vttp.vbeln;

  endmethod.

ENDCLASS.

============================  OUTRA BUSCA ==========================

Busca utilizando SEARCH com 2 campos.

No caso o Pedido e Saida são campos da minha tabela

=================================

METHOD execute_db BY DATABASE FUNCTION FOR HDB LANGUAGE

SQLSCRIPT OPTIONS READ-ONLY USING mslb matdoc nsdm_e_marc t156 ekpo.

declare lv_index, lv_rows, lv_pos integer;

it_mat = select mslb.mandt as client,

mslb.matnr as Material,

mslb.werks as Centro,

'0' as QuantTransf,

mslb.charg as Lote,

mslb.lifnr as Fornecedor,

'S' as tipo

FROM mslb

WHERE mandt = session_context('CLIENT')

and sobkz = 'O'

-- AND matnr = '000000000001157206'

UNION ALL

SELECT marc.mandt as Client,

marc.matnr as Material,

marc.werks as Centro,

marc.umlmc as QuantTransf,

'' as Lote,

'' as Fornecedor,

'T' as tipo

FROM nsdm_e_marc as Marc

where marc.mandt = session_context('CLIENT')

and marc.umlmc > 0;

it_mov = select mandt as Client,

bwart

FROM t156;

IF :p_sql_sub IS NOT null then

it_mov_sub = apply_filter (:it_mov, :p_sql_sub);

END if;

IF :p_sql_trc IS NOT NULL then

it_mov_trc = apply_filter (:it_mov, :p_sql_trc);

END if;

-- Documentos subcontratacao

it_doc = SELECT Doc.mandt as Client,

Doc.ebeln as Pedido,

Doc.ebelp as ItemPedido,

Doc.matnr as Material,

Doc.werks as Centro,

Doc.lifnr as Fornecedor,

Doc.budat as DtLancamento,

Doc.charg as Lote,

Doc.vfdat as DtVencLote,

Doc.ebeln as PedCKD,

Doc.lgort as Deposito,

Mat.tipo as Tipo,

CASE when Doc.blart = 'WE' then Doc.menge * -1

else Doc.menge end as Quantidade,

CASE when Doc.blart = 'WE' then ''

ELSE 'X' END as Saida

from matdoc as Doc

inner join :it_mat as Mat

ON Mat.Material = Doc.matnr

and Mat.Centro = Doc.werks

and Mat.Fornecedor = Doc.lifnr

and Mat.tipo = 'S'

inner join :it_mov_sub as mov

ON mov.bwart = Doc.bwart

where Doc.xauto = ''

AND Doc.cancelled = ''

AND Doc.reversal_movement = ''

union all

-- Documento de transferencia

SELECT Doc.mandt as Client,

Doc.ebeln as Pedido,

Doc.ebelp as ItemPedido,

Doc.matnr as Material,

Doc.werks as Centro,

Doc.lifnr as Fornecedor,

Doc.budat as DtLancamento,

Doc.charg as Lote,

Doc.vfdat as DtVencLote,

Doc.ebeln as PedCKD,

Doc.lgort as Deposito,

Mat.tipo as Tipo,

CASE when Doc.blart = 'WE' then Doc.menge * -1

else Doc.menge end as Quantidade,

CASE when Doc.blart = 'WE' then ''

ELSE 'X' END as Saida

from matdoc as Doc

inner join :it_mat as Mat

ON Mat.Material = Doc.matnr

and Mat.Centro = Doc.werks

and Mat.tipo = 'T'

inner join :it_mov_trc as mov

ON mov.bwart = Doc.bwart

where Doc.xauto = ''

AND Doc.cancelled = ''

AND Doc.reversal_movement = '';

it_doc = APPLY_FILTER( :it_doc, :p_sql_werks );

-- Documento ckd

it_ckd = SELECT Doc.Material as Material,

Doc.Lote as Lote,

ItPedido.zzpaickd as PedCKD

from :it_doc as Doc

inner join matdoc as MatDoc

on MatDoc.matnr = Doc.Material

and MatDoc.charg = Doc.Lote

and MatDoc.lifnr = Doc.Fornecedor

and MatDoc.bwart = '101'

INNER JOIN ekpo as ItPedido

ON ItPedido.ebeln = MatDoc.ebeln

and ItPedido.ebelp = MatDoc.ebelp

and ItPedido.zzpaickd <> ''

where Doc.Lote <> '';

lv_index = 1;

lv_rows = record_count( :it_doc );

-- Coloco a DATA de lancamento da saída igual aos documentos de entrada para agrupar os valores

WHILE lv_index <= lv_rows DO

if :it_doc.Saida[ :lv_index ] = '' then

lv_pos = :it_doc.search( ( Pedido, Saida ), ( :it_doc.Pedido[ :lv_index ], 'X' ) );

IF lv_pos <> 0 then

it_doc.DtLancamento[ :lv_index ] = :it_doc.DtLancamento[ :lv_pos ] ;

it_doc.Deposito[ :lv_index ] = :it_doc.Deposito[ :lv_pos ] ;

END if;

END if;

lv_pos = :it_ckd.search( ( Material, Lote ),

( :it_doc.Pedido[ :lv_index ], :it_doc.Lote[ :lv_index ] ) );

IF lv_pos > 0 then

it_doc.PedCKD[ :lv_index ] = :it_ckd.PedCKD[ :lv_pos ];

ELSE it_doc.PedCKD[ :lv_index ] = '';

END if;

lv_index = lv_index + 1 ;

END WHILE ;