[Automation] SAP Pending Approval Notification

[Requirements]

- A customer want to check if there is any pending approval in SAP system and receive a notification

** SAP Business One has an email notification funtion but not activated in the customer environment

 

[OS] 

Windows 10

 

[Development Language]

Python / SQL

 

[IDE]

SAP HANA DB / PYCharm

 

[Setting]

- Figure out table structure in terms of approval document and create a procedure printing out pending documents

 

CREATE PROCEDURE "BE1S_AUTO_PENDINGAPPROVALLIST"
( 
)
LANGUAGE SQLSCRIPT
AS
BEGIN
	SELECT 
	CASE 
		WHEN T0."ObjType"= '13' THEN 'A/R Invoice'
		WHEN T0."ObjType"= '18' THEN 'A/P Invoice'
		WHEN T0."ObjType"= '20' THEN 'Goods Receipt PO'
		WHEN T0."ObjType"= '22' THEN 'Purchase Order'
		END AS "Document Type" 
		, T2."DocNum" AS "Document Number"
		, T4."U_NAME" AS "Originator"
		, T3."U_NAME" AS "Authorizer"
		, T3."E_Mail" AS "Authorizer Email"
		, T0."Remarks"
		, T0."DraftEntry"
		, CASE
		WHEN T1."Status" = 'W' THEN 'Pending'
		ELSE T1."Status"
		END AS "Status"
		, T2."Comments"
		
		FROM OWDD T0 
		INNER JOIN WDD1 T1 ON T0."WddCode" = T1."WddCode"
		INNER JOIN ODRF T2 ON T2."DocEntry" = T0."DraftEntry"
		INNER JOIN OUSR T3 ON T3."USERID" = T1."UserID"
		INNER JOIN OUSR T4 ON T4."USERID" = T0."UserSign"
		
		WHERE T1."Status" = 'W' AND T0."Status" = 'W'
		ORDER by T0."DraftEntry";
END;

 

- SAP HANA DB connection

# HANA DB 접속
conn = dbapi.connect(
    address=[HANA SERVER ADDRESS],  # HANA 서버 주소
    port=[HANA PORT],               # HANA 포트 (기본값: 30015)
    user=[USER ID],
    password=[USER PASSWORD]
)
cursor = conn.cursor()

 

- Set "Task Scheduler" to run the script every week days at 9:00 am

 

 

[Code]

from datetime import datetime
from hdbcli import dbapi
from datetime import date
import smtplib
from email.mime.text import MIMEText
from email.utils import formataddr

# HANA DB 접속
conn = dbapi.connect(
    address=[HANA SERVER ADDRESS],  # HANA 서버 주소
    port=[HANA PORT],               # HANA 포트 (기본값: 30015)
    user=[USER ID],
    password=[USER PASSWORD]
)
cursor = conn.cursor()

# ──────────────────────────────────────────────
# 2. 프로시저 호출 (결과: 여러 행일 수도 있음)
# ──────────────────────────────────────────────
cursor.callproc('STK_PROD.BE1S_AUTO_PENDINGAPPROVALLIST')
rows = cursor.fetchall()

cursor.close()
conn.close()

# ──────────────────────────────────────────────
# 3. 메일 서버 설정 (예: Outlook / Gmail / 내부 SMTP)
# ──────────────────────────────────────────────
SMTP_SERVER = "smtp.office365.com"  # Outlook 예시
SMTP_PORT = 587
SMTP_USER = "jaehee.yoon@beonesolutions.com"
SMTP_PASS = "Whdkqkq12508"

today = date.today().strftime("%Y-%m-%d")

# ──────────────────────────────────────────────
# 4. 각 행에 대해 메일 전송
# ──────────────────────────────────────────────
for row in rows:
    # row = ('A/R Invoice', 180004070, 'Interface', 'Jihyun Ma', 'jaehee.yoon@beonesolutions.com', None, 3, 'Pending', '')

    doc_type = row[0]
    doc_num = row[1]
    requester = row[2]
    recipient = row[3]  # 메일 수신자
    recipient_m = row[4]  # 메일 수신자

    # 메일 제목 / 본문 구성
    subject = f"[SAP PENDING APPROVAL] {today} - {doc_type} / {doc_num} / {requester}"
    body = f"""Good morning! {recipient}

Hope you are well.
    
Please find below SAP pending approval request.

- Document type: {doc_type}
- Document number: {doc_num}
- Requester: {requester}

Thank you.
Daily Pending Approval Notification by BE1S 
"""

    msg = MIMEText(body, "plain", "utf-8")
    msg["Subject"] = subject
    msg["From"] = formataddr(("SAP Notification", SMTP_USER))
    msg["To"] = recipient_m
    msg["Cc"] = "jaehee.yoon@beonesolutions.com"

    # 메일 전송
    try:
        with smtplib.SMTP(SMTP_SERVER, SMTP_PORT) as server:
            server.starttls()
            server.login(SMTP_USER, SMTP_PASS)
            server.send_message(msg)
            print(f"✅ 메일 전송 완료 → {recipient}")
    except Exception as e:
        print(f"❌ 메일 전송 실패 ({recipient}): {e}")


with open(r"C:\Scripts\log.txt", "a", encoding="utf-8") as f:
    f.write(f"[{datetime.now()}] Script executed\n")

 

[Test]

 

[Lesson Learned]

- Python is so powerful

- Follow the stage, set up - connection - test - apply