Seu Template de Dados para Gestão de Inventário

SELECT
    mat_doc.Batch AS "InventoryBatchLot",
    CASE
        WHEN mat_doc.MovementType = '101' AND mat_doc.InventoryStockType = '2' THEN 'Stock Posted to Quality Inspection'
        WHEN mat_doc.MovementType = '101' THEN 'Goods Receipt Posted'
        WHEN mat_doc.MovementType = '321' THEN 'Quality Inspection Stock Released'
        WHEN mat_doc.MovementType = '311' THEN 'Internal Stock Transfer Posted'
        WHEN mat_doc.MovementType = '344' THEN 'Stock Status Changed to Blocked'
        WHEN mat_doc.MovementType IN ('343', '322') THEN 'Stock Status Changed to Unrestricted'
        WHEN mat_doc.MovementType IN ('701', '702') THEN 'Inventory Adjustment Posted'
        WHEN mat_doc.MovementType = '601' THEN 'Goods Issue for Delivery Posted'
        WHEN mat_doc.MovementType = '261' THEN 'Goods Issue for Production Posted'
        WHEN mat_doc.MovementType IN ('651', '653') THEN 'Sales Return Received'
        WHEN mat_doc.MovementType = '551' THEN 'Stock Scrapped'
        WHEN mat_doc.MovementType = '313' THEN 'Put-Away Transfer Posted' -- Example for two-step transfers
        ELSE 'Unknown Material Movement'
    END AS "ActivityName",
    TO_TIMESTAMP(mat_doc.PostingDate || LPAD(mat_doc.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    mat_doc.Material AS "MaterialNumber",
    mat_doc.CreatedByUser AS "UserName",
    mat_doc.MovementType AS "MovementType",
    mat_doc.Plant AS "Plant",
    mat_doc.StorageLocation AS "StorageLocation",
    mat_doc.QuantityInEntryUnit AS "Quantity",
    mat_doc.ReasonForMovement AS "MovementReasonCode"
FROM
    I_MaterialDocumentItem AS mat_doc
WHERE
    mat_doc.Batch IS NOT NULL AND mat_doc.Batch <> ''
    AND mat_doc.PostingDate BETWEEN '20230101' AND '20231231' -- Placeholder: Set your date range
    -- AND mat_doc.Plant IN ('Plant1', 'Plant2') -- Placeholder: Add filters for relevant plants
UNION ALL
SELECT
    SPLIT_PART(change_doc.ChangeableObjectDescription, '/', 3) AS "InventoryBatchLot",
    'Batch Status Changed' AS "ActivityName",
    change_doc.ChangeDocumentCreationDateTime AS "EventTime",
    SPLIT_PART(change_doc.ChangeableObjectDescription, '/', 1) AS "MaterialNumber",
    change_doc.ChangedByUser AS "UserName",
    NULL AS "MovementType",
    SPLIT_PART(change_doc.ChangeableObjectDescription, '/', 2) AS "Plant",
    NULL AS "StorageLocation",
    NULL AS "Quantity",
    NULL AS "MovementReasonCode"
FROM
    I_BatchChangeDocument AS change_doc
WHERE
    change_doc.ChangeDocumentTable = 'MCHA' AND change_doc.ChangeDocumentTableFieldName = 'ZUSTD'
    AND TO_VARCHAR(change_doc.ChangeDocumentCreationDate) BETWEEN '20230101' AND '20231231' -- Placeholder: Set your date range
UNION ALL
SELECT
    wh_task.Batch AS "InventoryBatchLot",
    'Picking Initiated' AS "ActivityName",
    wh_task.WarehouseTaskCreationDateTime AS "EventTime",
    wh_task.Product AS "MaterialNumber",
    wh_task.CreatedByUser AS "UserName",
    NULL AS "MovementType",
    wh_task.Plant AS "Plant",
    wh_task.SourceStorageLocation AS "StorageLocation",
    wh_task.TargetQuantity AS "Quantity",
    NULL AS "MovementReasonCode"
FROM
    I_WarehouseTask AS wh_task
WHERE
    wh_task.Batch IS NOT NULL AND wh_task.Batch <> ''
    AND wh_task.WarehouseProcessType IN ('P210', 'P220') -- Placeholder: Adjust process types based on your picking configuration
    AND TO_VARCHAR(wh_task.WarehouseTaskCreationDate) BETWEEN '20230101' AND '20231231'; -- Placeholder: Set your date range

SELECT
    mat_doc.Batch AS "InventoryBatchLot",
    CASE
        WHEN mat_doc.MovementType = '101' AND mat_doc.InventoryStockType = '2' THEN 'Stock Posted to Quality Inspection'
        WHEN mat_doc.MovementType = '101' THEN 'Goods Receipt Posted'
        WHEN mat_doc.MovementType = '321' THEN 'Quality Inspection Stock Released'
        WHEN mat_doc.MovementType = '311' THEN 'Internal Stock Transfer Posted'
        WHEN mat_doc.MovementType = '344' THEN 'Stock Status Changed to Blocked'
        WHEN mat_doc.MovementType IN ('343', '322') THEN 'Stock Status Changed to Unrestricted'
        WHEN mat_doc.MovementType IN ('701', '702') THEN 'Inventory Adjustment Posted'
        WHEN mat_doc.MovementType = '601' THEN 'Goods Issue for Delivery Posted'
        WHEN mat_doc.MovementType = '261' THEN 'Goods Issue for Production Posted'
        WHEN mat_doc.MovementType IN ('651', '653') THEN 'Sales Return Received'
        WHEN mat_doc.MovementType = '551' THEN 'Stock Scrapped'
        WHEN mat_doc.MovementType = '313' THEN 'Put-Away Transfer Posted' -- Example for two-step transfers
        ELSE 'Unknown Material Movement'
    END AS "ActivityName",
    TO_TIMESTAMP(mat_doc.PostingDate || LPAD(mat_doc.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    mat_doc.Material AS "MaterialNumber",
    mat_doc.CreatedByUser AS "UserName",
    mat_doc.MovementType AS "MovementType",
    mat_doc.Plant AS "Plant",
    mat_doc.StorageLocation AS "StorageLocation",
    mat_doc.QuantityInEntryUnit AS "Quantity",
    mat_doc.ReasonForMovement AS "MovementReasonCode"
FROM
    I_MaterialDocumentItem AS mat_doc
WHERE
    mat_doc.Batch IS NOT NULL AND mat_doc.Batch <> ''
    AND mat_doc.PostingDate BETWEEN '20230101' AND '20231231' -- Placeholder: Set your date range
    -- AND mat_doc.Plant IN ('Plant1', 'Plant2') -- Placeholder: Add filters for relevant plants
UNION ALL
SELECT
    SPLIT_PART(change_doc.ChangeableObjectDescription, '/', 3) AS "InventoryBatchLot",
    'Batch Status Changed' AS "ActivityName",
    change_doc.ChangeDocumentCreationDateTime AS "EventTime",
    SPLIT_PART(change_doc.ChangeableObjectDescription, '/', 1) AS "MaterialNumber",
    change_doc.ChangedByUser AS "UserName",
    NULL AS "MovementType",
    SPLIT_PART(change_doc.ChangeableObjectDescription, '/', 2) AS "Plant",
    NULL AS "StorageLocation",
    NULL AS "Quantity",
    NULL AS "MovementReasonCode"
FROM
    I_BatchChangeDocument AS change_doc
WHERE
    change_doc.ChangeDocumentTable = 'MCHA' AND change_doc.ChangeDocumentTableFieldName = 'ZUSTD'
    AND TO_VARCHAR(change_doc.ChangeDocumentCreationDate) BETWEEN '20230101' AND '20231231' -- Placeholder: Set your date range
UNION ALL
SELECT
    wh_task.Batch AS "InventoryBatchLot",
    'Picking Initiated' AS "ActivityName",
    wh_task.WarehouseTaskCreationDateTime AS "EventTime",
    wh_task.Product AS "MaterialNumber",
    wh_task.CreatedByUser AS "UserName",
    NULL AS "MovementType",
    wh_task.Plant AS "Plant",
    wh_task.SourceStorageLocation AS "StorageLocation",
    wh_task.TargetQuantity AS "Quantity",
    NULL AS "MovementReasonCode"
FROM
    I_WarehouseTask AS wh_task
WHERE
    wh_task.Batch IS NOT NULL AND wh_task.Batch <> ''
    AND wh_task.WarehouseProcessType IN ('P210', 'P220') -- Placeholder: Adjust process types based on your picking configuration
    AND TO_VARCHAR(wh_task.WarehouseTaskCreationDate) BETWEEN '20230101' AND '20231231'; -- Placeholder: Set your date range

REPORT Z_PM_INVENTORY_EXTRACT.

" ====================================================================
" SELECTION SCREEN
" ====================================================================
SELECT-OPTIONS: s_budat FOR sy-datum OBLIGATORY.
SELECT-OPTIONS: s_werks FOR mseg-werks.

PARAMETERS: p_fpath TYPE string DEFAULT '/usr/sap/trans/tmp/inventory_log.csv' OBLIGATORY.

" ====================================================================
" DATA STRUCTURES
" ====================================================================
TYPES: BEGIN OF ty_event_log,
         InventoryBatchLot  TYPE charg,
         ActivityName       TYPE string,
         EventTime          TYPE string,
         MaterialNumber     TYPE matnr,
         UserName           TYPE xubname,
         MovementType       TYPE bwart,
         Plant              TYPE werks_d,
         StorageLocation    TYPE lgort_d,
         Quantity           TYPE menge_d,
         MovementReasonCode TYPE grund,
       END OF ty_event_log.

DATA: lt_event_log TYPE TABLE OF ty_event_log.

" ====================================================================
" DATA SELECTION
" ====================================================================
START-OF-SELECTION.

  SELECT
      mseg~charg         AS InventoryBatchLot,
      'Goods Receipt Posted' AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart IN ('101', '103', '105', '501', '521', '561')
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      CASE mseg~shkzg
        WHEN 'H' THEN 'Stock Posted to Quality Inspection'
        WHEN 'S' THEN 'Quality Inspection Stock Released'
      END                AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart = '321' " For QI to Unrestricted
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      'Put-Away Transfer Posted' AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart = '311' AND mseg~shkzg = 'H' " Assume put-away is the credit side
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      'Internal Stock Transfer Posted' AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart IN ('301', '311', '313', '315')
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      CASE mseg~bwart
        WHEN '343' THEN 'Stock Status Changed to Blocked'
        WHEN '344' THEN 'Stock Status Changed to Unrestricted'
      END                AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart IN ('343', '344')
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      'Inventory Adjustment Posted' AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart IN ('701', '702', '711', '712')
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      'Goods Issue for Delivery Posted' AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart = '601'
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      'Goods Issue for Production Posted' AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart = '261'
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      'Sales Return Received' AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart IN ('651', '653')
  UNION ALL
  SELECT
      mseg~charg         AS InventoryBatchLot,
      'Stock Scrapped'   AS ActivityName,
      CONCAT( mkpf~cpudt, mkpf~cputm ) AS EventTime,
      mseg~matnr         AS MaterialNumber,
      mkpf~usnam         AS UserName,
      mseg~bwart         AS MovementType,
      mseg~werks         AS Plant,
      mseg~lgort         AS StorageLocation,
      mseg~menge         AS Quantity,
      mseg~grund         AS MovementReasonCode
    FROM mseg
    JOIN mkpf ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
    WHERE mkpf~budat IN s_budat
      AND mseg~werks IN s_werks
      AND mseg~charg IS NOT NULL AND mseg~charg <> ''
      AND mseg~bwart = '551'
  UNION ALL
  SELECT
      ltap~charg         AS InventoryBatchLot,
      'Picking Initiated' AS ActivityName,
      CONCAT( ltak~bdatu, ltak~bzeit ) AS EventTime,
      ltap~matnr         AS MaterialNumber,
      ltak~bname         AS UserName,
      ltak~bwart         AS MovementType,
      ltap~werks         AS Plant,
      ltap~lgort         AS StorageLocation,
      ltap~nista         AS Quantity,
      ''                 AS MovementReasonCode
    FROM ltap
    JOIN ltak ON ltak~tanum = ltap~tanum
    WHERE ltak~bdatu IN s_budat
      AND ltap~werks IN s_werks
      AND ltap~charg IS NOT NULL AND ltap~charg <> ''
  UNION ALL
  SELECT
      SUBSTRING( cdhdr~objectid, 5, 18 ) AS InventoryBatchLot, " Object ID for BATCH is MATNR+WERKS+CHARG
      'Batch Status Changed'              AS ActivityName,
      CONCAT( cdhdr~udate, cdhdr~utime )  AS EventTime,
      SUBSTRING( cdhdr~objectid, 1, 4 )   AS MaterialNumber,
      cdhdr~username                      AS UserName,
      ''                                  AS MovementType,
      ''                                  AS Plant,
      ''                                  AS StorageLocation,
      0                                   AS Quantity,
      ''                                  AS MovementReasonCode
    FROM cdhdr
    JOIN cdpos ON cdpos~objectclas = cdhdr~objectclas
               AND cdpos~objectid   = cdhdr~objectid
               AND cdpos~changenr   = cdhdr~changenr
    WHERE cdhdr~udate IN s_budat
      AND cdhdr~objectclas = 'BATCH'
      AND cdpos~tabname = 'MCH1'
      AND cdpos~fname = 'ZUSTD'
    INTO TABLE @lt_event_log.

" ====================================================================
" WRITE OUTPUT FILE
" ====================================================================
  DATA: lv_string TYPE string.
  DATA: lo_conv TYPE REF TO cl_abap_conv_out_ce.

  lo_conv = cl_abap_conv_out_ce=>create( encoding = 'UTF-8' ).

  OPEN DATASET p_fpath FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc <> 0.
    MESSAGE 'Error opening file.' TYPE 'E'.
    RETURN.
  ENDIF.

  " Write Header
  lv_string = 'InventoryBatchLot,ActivityName,EventTime,MaterialNumber,UserName,MovementType,Plant,StorageLocation,Quantity,MovementReasonCode'.
  TRANSFER lv_string TO p_fpath.

  " Write Data
  LOOP AT lt_event_log ASSIGNING FIELD-SYMBOL(<fs_log>).
    CONCATENATE
      <fs_log>-InventoryBatchLot
      <fs_log>-ActivityName
      <fs_log>-EventTime
      <fs_log>-MaterialNumber
      <fs_log>-UserName
      <fs_log>-MovementType
      <fs_log>-Plant
      <fs_log>-StorageLocation
      <fs_log>-Quantity
      <fs_log>-MovementReasonCode
      INTO lv_string
      SEPARATED BY ','.
    TRANSFER lv_string TO p_fpath.
  ENDLOOP.

  CLOSE DATASET p_fpath.

  WRITE: 'Extraction complete. File written to:', p_fpath.