🌐 Phoenix Meal Attendance API

A RESTful API built with Oracle ORDS + PL/SQL, designed to manage daily employee meal attendance.

This repository contains:

• Table DDL (PHOENIX_DAILY_ATTENDANCE)
• Triggers for auto ID and month extraction
• ORDS REST API module export
• Postman Collection for API testing

⚠ Note: Employee master data (PHOENIX_HR_EMP_MASTER) is referenced in the GET API, but the table itself is not included here. You may substitute your own employee table.

---

🚀 Features

• Record daily lunch/dinner participation
• Auto-generated attendance IDs (ATD-xxxxxx)
• Month value is automatically extracted
• Bulk Insert, Bulk Update, Bulk Delete supported
• Filterable GET API (by date, ID, lunch/dinner flags)

---

🗄 Database Table Definition

CREATE TABLE PHOENIX_DAILY_ATTENDANCE (
  ATTENDANCE_ID      VARCHAR2(10) PRIMARY KEY,
  ATTENDANCE_DATE    DATE NOT NULL,
  EMP_CODE           VARCHAR2(15) NOT NULL,
  IS_TAKING_LUNCH    VARCHAR2(3) DEFAULT 'Y',
  IS_TAKING_DINNER   VARCHAR2(3) DEFAULT 'N',
  USER_ID            VARCHAR2(50),
  ENTRY_DATE         DATE DEFAULT SYSDATE,
  LAST_UPDATE        VARCHAR2(20),
  LAST_UPDATE_DATE   DATE,
  MONTH_VALUE        NUMBER(2,0)
);

🔁 Triggers

TRG_PHOENIX_ATTENDANCE_ID       → Generates ATD-000001 style IDs
TRG_PHOENIX_ATTENDANCE_MONTH_FILL → Keeps MONTH_VALUE synced to date

---

📡 API Endpoints

Method	Endpoint	Description
POST	/add	Bulk insert new attendance records
GET	/all	Fetch records with filters
PUT	/update	Bulk update records
DELETE	/delete	Bulk delete by attendance_id

Base URL is environment-specific.
Replace with your own ORDS server and schema:

{{base_url}}/{{schema}}/daily_attendance/add

Use Postman environment variable {{base_url}} instead of hardcoding private URLs.

---

🧪 Postman Collection

The file:

postman/phoenix_attendance.postman_collection.json

Can be imported directly into Postman to test all APIs.

You may set environment variable:

Variable	Example
base_url	https://yourserver:port/ords
schema	intern

---

📝 Demo JSON Payloads

POST (Bulk Insert)

[
  {
    "attendance_id": "ATD-001200",
    "emp_code": "EMP-000001",
    "attendance_date": "08/03/2025",
    "is_taking_lunch": "Y",
    "is_taking_dinner": "N",
    "user_id": "admin"
  }
]

PUT (Bulk Update)

[
  {
    "attendance_date": "08/03/2025",
    "emp_code": "EMP-000001",
    "is_taking_lunch": "N",
    "is_taking_dinner": "Y",
    "user_id": "manager01"
  }
]

DELETE (Bulk Delete)

[
  { "attendance_id": "ATD-001200" },
  { "attendance_id": "ATD-001201" }
]

---

🏗 Architecture

Client (APEX / React / Mobile / Postman)
       ↓ REST JSON
ORDS REST Module (this project)
       ↓ SQL / PL/SQL
Oracle Database (PHOENIX_DAILY_ATTENDANCE)

---

👨‍💻 Author

onlynayan / Meal Attendance Module
Oracle + API + Data Engineering Stack

---