เทมเพลตข้อมูลสำหรับการจัดการสินค้าคงคลัง

WITH EventLog_BASE AS (
-- 1. Goods Receipt Posted
SELECT
  MSEG.MATNR || '_' || MSEG.CHARG AS "InventoryBatchLot",
  'Goods Receipt Posted' AS "ActivityName",
  MKPF.CPUDT AS EVENT_DATE,
  MKPF.CPUTM AS EVENT_TIME,
  MSEG.MATNR AS "MaterialNumber",
  MSEG.WERKS AS "Plant",
  MSEG.LGORT AS "StorageLocation",
  MSEG.BWART AS "MovementType",
  CASE WHEN MSEG.SHKZG = 'H' THEN -1 * MSEG.MENGE ELSE MSEG.MENGE END AS "QuantityInBaseUnit"
FROM [Your SAP Schema].MSEG AS MSEG
JOIN [Your SAP Schema].MKPF AS MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.CHARG IS NOT NULL AND MSEG.CHARG <> ''
  AND MKPF.BUDAT BETWEEN '{StartDate}' AND '{EndDate}'
  AND MSEG.BWART IN ('101', '103', '105', '122', '161')

UNION ALL

-- 2. Quality Inspection Lot Created
SELECT
  QALS.MATNR || '_' || QALS.CHARG AS "InventoryBatchLot",
  'Quality Inspection Lot Created' AS "ActivityName",
  QALS.ENTSTEHDAT AS EVENT_DATE,
  QALS.ENTSTEHZEIT AS EVENT_TIME,
  QALS.MATNR AS "MaterialNumber",
  QALS.WERK AS "Plant",
  QALS.LAGORT AS "StorageLocation",
  NULL AS "MovementType",
  QALS.LOSMENGE AS "QuantityInBaseUnit"
FROM [Your SAP Schema].QALS AS QALS
WHERE QALS.CHARG IS NOT NULL AND QALS.CHARG <> ''
  AND QALS.ENTSTEHDAT BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

-- 3. Inspection Usage Decision Made
SELECT
  QALS.MATNR || '_' || QALS.CHARG AS "InventoryBatchLot",
  'Inspection Usage Decision Made' AS "ActivityName",
  QAVE.VDATUM AS EVENT_DATE,
  QAVE.VZEIT AS EVENT_TIME,
  QALS.MATNR AS "MaterialNumber",
  QALS.WERK AS "Plant",
  QALS.LAGORT AS "StorageLocation",
  NULL AS "MovementType",
  QALS.LOSMENGE AS "QuantityInBaseUnit"
FROM [Your SAP Schema].QAVE AS QAVE
JOIN [Your SAP Schema].QALS AS QALS ON QAVE.PRUEFLOS = QALS.PRUEFLOS
WHERE QALS.CHARG IS NOT NULL AND QALS.CHARG <> ''
  AND QAVE.VDATUM BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

-- 4. Put-away Transfer Order Created
SELECT
  LTAP.MATNR || '_' || LTAP.CHARG AS "InventoryBatchLot",
  'Put-away Transfer Order Created' AS "ActivityName",
  LTBK.BDATU AS EVENT_DATE,
  LTBK.BZEIT AS EVENT_TIME,
  LTAP.MATNR AS "MaterialNumber",
  LTAP.WERKS AS "Plant",
  LTAP.NLORT AS "StorageLocation",
  LTBK.BWLVS AS "MovementType",
  LTAP.NSOLM AS "QuantityInBaseUnit"
FROM [Your SAP Schema].LTAP AS LTAP
JOIN [Your SAP Schema].LTBK AS LTBK ON LTAP.TANUM = LTBK.TANUM
WHERE LTAP.CHARG IS NOT NULL AND LTAP.CHARG <> ''
  AND LTBK.BDATU BETWEEN '{StartDate}' AND '{EndDate}'
  AND LTBK.BETYP = 'E' -- 'E' for Putaway

UNION ALL

-- 5. Put-away Completed
SELECT
  LTAP.MATNR || '_' || LTAP.CHARG AS "InventoryBatchLot",
  'Put-away Completed' AS "ActivityName",
  LTAP.QDATU AS EVENT_DATE,
  LTAP.QZEIT AS EVENT_TIME,
  LTAP.MATNR AS "MaterialNumber",
  LTAP.WERKS AS "Plant",
  LTAP.NLORT AS "StorageLocation",
  LTBK.BWLVS AS "MovementType",
  LTAP.NISTM AS "QuantityInBaseUnit"
FROM [Your SAP Schema].LTAP AS LTAP
JOIN [Your SAP Schema].LTBK AS LTBK ON LTAP.TANUM = LTBK.TANUM
WHERE LTAP.CHARG IS NOT NULL AND LTAP.CHARG <> ''
  AND LTAP.QDATU BETWEEN '{StartDate}' AND '{EndDate}'
  AND LTBK.BETYP = 'E' AND LTAP.PQUIT = 'X'

UNION ALL

-- 6. Stock Moved Internally
SELECT
  MSEG.MATNR || '_' || MSEG.CHARG AS "InventoryBatchLot",
  'Stock Moved Internally' AS "ActivityName",
  MKPF.CPUDT AS EVENT_DATE,
  MKPF.CPUTM AS EVENT_TIME,
  MSEG.MATNR AS "MaterialNumber",
  MSEG.WERKS AS "Plant",
  MSEG.LGORT AS "StorageLocation",
  MSEG.BWART AS "MovementType",
  MSEG.MENGE AS "QuantityInBaseUnit"
FROM [Your SAP Schema].MSEG AS MSEG
JOIN [Your SAP Schema].MKPF AS MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.CHARG IS NOT NULL AND MSEG.CHARG <> ''
  AND MKPF.BUDAT BETWEEN '{StartDate}' AND '{EndDate}'
  AND MSEG.BWART IN ('301', '311', '321', '343')

UNION ALL

-- 7. Inventory Count Initiated
SELECT
  ISEG.MATNR || '_' || ISEG.CHARG AS "InventoryBatchLot",
  'Inventory Count Initiated' AS "ActivityName",
  IKPF.CDATE AS EVENT_DATE,
  IKPF.CTIME AS EVENT_TIME,
  ISEG.MATNR AS "MaterialNumber",
  ISEG.WERKS AS "Plant",
  ISEG.LGORT AS "StorageLocation",
  NULL AS "MovementType",
  ISEG.BUCHM AS "QuantityInBaseUnit"
FROM [Your SAP Schema].IKPF AS IKPF
JOIN [Your SAP Schema].ISEG AS ISEG ON IKPF.IBLNR = ISEG.IBLNR
WHERE ISEG.CHARG IS NOT NULL AND ISEG.CHARG <> ''
  AND IKPF.CDATE BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

-- 8. Inventory Count Performed
SELECT
  ISEG.MATNR || '_' || ISEG.CHARG AS "InventoryBatchLot",
  'Inventory Count Performed' AS "ActivityName",
  ISEG.ZLDAT AS EVENT_DATE,
  ISEG.ZLZEIT AS EVENT_TIME,
  ISEG.MATNR AS "MaterialNumber",
  ISEG.WERKS AS "Plant",
  ISEG.LGORT AS "StorageLocation",
  NULL AS "MovementType",
  ISEG.ERFMG AS "QuantityInBaseUnit"
FROM [Your SAP Schema].ISEG AS ISEG
WHERE ISEG.CHARG IS NOT NULL AND ISEG.CHARG <> ''
  AND ISEG.ZLDAT BETWEEN '{StartDate}' AND '{EndDate}'
  AND ISEG.XZAEL = 'X'

UNION ALL

-- 9. Inventory Discrepancy Adjusted
SELECT
  MSEG.MATNR || '_' || MSEG.CHARG AS "InventoryBatchLot",
  'Inventory Discrepancy Adjusted' AS "ActivityName",
  MKPF.CPUDT AS EVENT_DATE,
  MKPF.CPUTM AS EVENT_TIME,
  MSEG.MATNR AS "MaterialNumber",
  MSEG.WERKS AS "Plant",
  MSEG.LGORT AS "StorageLocation",
  MSEG.BWART AS "MovementType",
  CASE WHEN MSEG.SHKZG = 'H' THEN -1 * MSEG.MENGE ELSE MSEG.MENGE END AS "QuantityInBaseUnit"
FROM [Your SAP Schema].MSEG AS MSEG
JOIN [Your SAP Schema].MKPF AS MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.CHARG IS NOT NULL AND MSEG.CHARG <> ''
  AND MKPF.BUDAT BETWEEN '{StartDate}' AND '{EndDate}'
  AND MSEG.BWART IN ('701', '702', '711', '712')

UNION ALL

-- 10. Picking Initiated
SELECT
  LTAP.MATNR || '_' || LTAP.CHARG AS "InventoryBatchLot",
  'Picking Initiated' AS "ActivityName",
  LTBK.BDATU AS EVENT_DATE,
  LTBK.BZEIT AS EVENT_TIME,
  LTAP.MATNR AS "MaterialNumber",
  LTAP.WERKS AS "Plant",
  LTAP.VLORT AS "StorageLocation",
  LTBK.BWLVS AS "MovementType",
  LTAP.VSOLM AS "QuantityInBaseUnit"
FROM [Your SAP Schema].LTAP AS LTAP
JOIN [Your SAP Schema].LTBK AS LTBK ON LTAP.TANUM = LTBK.TANUM
WHERE LTAP.CHARG IS NOT NULL AND LTAP.CHARG <> ''
  AND LTBK.BDATU BETWEEN '{StartDate}' AND '{EndDate}'
  AND LTBK.BETYP = 'A' -- 'A' for Picking

UNION ALL

-- 11. Picking Completed
SELECT
  LTAP.MATNR || '_' || LTAP.CHARG AS "InventoryBatchLot",
  'Picking Completed' AS "ActivityName",
  LTAP.QDATU AS EVENT_DATE,
  LTAP.QZEIT AS EVENT_TIME,
  LTAP.MATNR AS "MaterialNumber",
  LTAP.WERKS AS "Plant",
  LTAP.VLORT AS "StorageLocation",
  LTBK.BWLVS AS "MovementType",
  LTAP.NISTM AS "QuantityInBaseUnit"
FROM [Your SAP Schema].LTAP AS LTAP
JOIN [Your SAP Schema].LTBK AS LTBK ON LTAP.TANUM = LTBK.TANUM
WHERE LTAP.CHARG IS NOT NULL AND LTAP.CHARG <> ''
  AND LTAP.QDATU BETWEEN '{StartDate}' AND '{EndDate}'
  AND LTBK.BETYP = 'A' AND LTAP.PQUIT = 'X'

UNION ALL

-- 12. Packing Completed
SELECT
  VEPO.MATNR || '_' || VEPO.CHARG AS "InventoryBatchLot",
  'Packing Completed' AS "ActivityName",
  VEKP.ERDAT AS EVENT_DATE,
  VEKP.ERZET AS EVENT_TIME,
  VEPO.MATNR AS "MaterialNumber",
  VEPO.WERKS AS "Plant",
  NULL AS "StorageLocation",
  NULL AS "MovementType",
  VEPO.VEMNG AS "QuantityInBaseUnit"
FROM [Your SAP Schema].VEPO AS VEPO
JOIN [Your SAP Schema].VEKP AS VEKP ON VEPO.VENUM = VEKP.VENUM
WHERE VEPO.CHARG IS NOT NULL AND VEPO.CHARG <> ''
  AND VEKP.ERDAT BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

-- 13. Goods Issue Posted
SELECT
  MSEG.MATNR || '_' || MSEG.CHARG AS "InventoryBatchLot",
  'Goods Issue Posted' AS "ActivityName",
  MKPF.CPUDT AS EVENT_DATE,
  MKPF.CPUTM AS EVENT_TIME,
  MSEG.MATNR AS "MaterialNumber",
  MSEG.WERKS AS "Plant",
  MSEG.LGORT AS "StorageLocation",
  MSEG.BWART AS "MovementType",
  CASE WHEN MSEG.SHKZG = 'H' THEN -1 * MSEG.MENGE ELSE MSEG.MENGE END AS "QuantityInBaseUnit"
FROM [Your SAP Schema].MSEG AS MSEG
JOIN [Your SAP Schema].MKPF AS MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.CHARG IS NOT NULL AND MSEG.CHARG <> ''
  AND MKPF.BUDAT BETWEEN '{StartDate}' AND '{EndDate}'
  AND MSEG.BWART IN ('601', '641', '643', '201', '261')

UNION ALL

-- 14. Return Goods Received
SELECT
  MSEG.MATNR || '_' || MSEG.CHARG AS "InventoryBatchLot",
  'Return Goods Received' AS "ActivityName",
  MKPF.CPUDT AS EVENT_DATE,
  MKPF.CPUTM AS EVENT_TIME,
  MSEG.MATNR AS "MaterialNumber",
  MSEG.WERKS AS "Plant",
  MSEG.LGORT AS "StorageLocation",
  MSEG.BWART AS "MovementType",
  MSEG.MENGE AS "QuantityInBaseUnit"
FROM [Your SAP Schema].MSEG AS MSEG
JOIN [Your SAP Schema].MKPF AS MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.CHARG IS NOT NULL AND MSEG.CHARG <> ''
  AND MKPF.BUDAT BETWEEN '{StartDate}' AND '{EndDate}'
  AND MSEG.BWART IN ('651', '653', '655')

UNION ALL

-- 15. Stock Scrapped
SELECT
  MSEG.MATNR || '_' || MSEG.CHARG AS "InventoryBatchLot",
  'Stock Scrapped' AS "ActivityName",
  MKPF.CPUDT AS EVENT_DATE,
  MKPF.CPUTM AS EVENT_TIME,
  MSEG.MATNR AS "MaterialNumber",
  MSEG.WERKS AS "Plant",
  MSEG.LGORT AS "StorageLocation",
  MSEG.BWART AS "MovementType",
  CASE WHEN MSEG.SHKZG = 'H' THEN -1 * MSEG.MENGE ELSE MSEG.MENGE END AS "QuantityInBaseUnit"
FROM [Your SAP Schema].MSEG AS MSEG
JOIN [Your SAP Schema].MKPF AS MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.CHARG IS NOT NULL AND MSEG.CHARG <> ''
  AND MKPF.BUDAT BETWEEN '{StartDate}' AND '{EndDate}'
  AND MSEG.BWART IN ('551', '553', '555')
)
SELECT
  e."InventoryBatchLot",
  e."ActivityName",
  -- The following syntax for creating a timestamp may need to be adjusted
  -- for your specific database (e.g., use `+` for SQL Server, TO_TIMESTAMP for Oracle).
  CAST(CONCAT(e.EVENT_DATE, ' ', e.EVENT_TIME) AS TIMESTAMP) AS "EventTime",
  e."MaterialNumber",
  e."Plant",
  e."StorageLocation",
  e."MovementType",
  e."QuantityInBaseUnit"
FROM EventLog_BASE e
ORDER BY "InventoryBatchLot", "EventTime";

import pandas as pd
import pyrfc
from pyrfc import Connection
from datetime import datetime

class=class="hl-string">"hl-comment"># --- 1. SAP Connection Details (Use a secure method for credentials) ---
sap_params = {
    class="hl-string">"user": class="hl-string">"[Your_SAP_User]",
    class="hl-string">"passwd": class="hl-string">"[Your_SAP_Password]",
    class="hl-string">"ashost": class="hl-string">"[Your_SAP_App_Server_IP_or_Hostname]",
    class="hl-string">"sysnr": class="hl-string">"[Your_System_Number]",
    class="hl-string">"client": class="hl-string">"[Your_Client_ID]",
    class="hl-string">"lang": class="hl-string">"EN"
}

class=class="hl-string">"hl-comment"># --- 2. Extraction Parameters ---
class=class="hl-string">"hl-comment"># It is recommended to run the extraction for smaller, manageable date ranges.
start_date = class="hl-string">"20230101"  class=class="hl-string">"hl-comment"># YYYYMMDD
end_date = class="hl-string">"20230331"    class=class="hl-string">"hl-comment"># YYYYMMDD

class=class="hl-string">"hl-comment"># Optional filters (leave blank class="hl-string">'' to ignore)
plant_filter = class="hl-string">"[Your_Plant_Code]"

class=class="hl-string">"hl-comment"># Name of the custom RFC function module in SAP
rfc_function_name = class="hl-string">"Z_PM_GET_INVENTORY_LOG"

class=class="hl-string">"hl-comment"># --- 3. Establish Connection and Call RFC ---
try:
    print(class="hl-string">"Connecting to SAP...")
    conn = Connection(**sap_params)
    print(fclass="hl-string">"Successfully connected to SAP. Calling RFC: {rfc_function_name}")

    class=class="hl-string">"hl-comment"># Call the remote function module with the specified parameters
    result = conn.call(rfc_function_name, 
                         I_DATE_FROM=start_date, 
                         I_DATE_TO=end_date,
                         I_PLANT=plant_filter)

    print(class="hl-string">"Data extraction successful. Processing results...")

    class=class="hl-string">"hl-comment"># The export table in the ABAP function is named class="hl-string">'E_T_EVENT_LOG'
    event_log_data = result[class="hl-string">'E_T_EVENT_LOG']

    conn.close()
    print(class="hl-string">"SAP connection closed.")

except pyrfc.RFCError as e:
    print(fclass="hl-string">"An RFC error occurred: {e}")
    exit()
except Exception as e:
    print(fclass="hl-string">"An unexpected error occurred: {e}")
    exit()

class=class="hl-string">"hl-comment"># --- 4. Convert to Pandas DataFrame and Save to CSV ---
if event_log_data:
    df = pd.DataFrame(event_log_data)

    class=class="hl-string">"hl-comment"># Optional: Data type conversions and cleaning
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'], errors=class="hl-string">'coerce')
    df[class="hl-string">'QuantityInBaseUnit'] = pd.to_numeric(df[class="hl-string">'QuantityInBaseUnit'], errors=class="hl-string">'coerce')

    class=class="hl-string">"hl-comment"># Reorder columns to match ProcessMind expectations if necessary
    column_order = [
        class="hl-string">'InventoryBatchLot',
        class="hl-string">'ActivityName',
        class="hl-string">'EventTime',
        class="hl-string">'MaterialNumber',
        class="hl-string">'Plant',
        class="hl-string">'StorageLocation',
        class="hl-string">'MovementType',
        class="hl-string">'QuantityInBaseUnit'
    ]
    class=class="hl-string">"hl-comment"># Ensure all expected columns exist before reordering
    existing_columns = [col for col in column_order if col in df.columns]
    df = df[existing_columns]

    print(fclass="hl-string">"\nSuccessfully created DataFrame with {len(df)} rows.")
    print(class="hl-string">"\nDataFrame Head:")
    print(df.head())

    class=class="hl-string">"hl-comment"># Save to CSV
    output_filename = fclass="hl-string">"inventory_event_log_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f')
    print(fclass="hl-string">"\nEvent log saved to {output_filename}")
else:
    print(class="hl-string">"No data returned from SAP for the specified criteria.")