-- Point of Sale System Implementation
-- IS 252 Database Project
-- Step 1: Create user with appropriate privileges
CREATE USER pointOfSal IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE SEQUENCE TO pointOfSal;
GRANT UNLIMITED TABLESPACE TO pointOfSal;
-- Connect as the new user
CONNECT pointOfSal/password;
-- Step 2: Create tables with appropriate relationships
-- Create Category table
CREATE TABLE Category (
catNo NUMBER PRIMARY KEY,
catogeryName VARCHAR2(100) UNIQUE NOT NULL
);
-- Create sequence for category
CREATE SEQUENCE seq_category_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Create Employee table
CREATE TABLE Employee (
employeeNo NUMBER PRIMARY KEY,
employeeName VARCHAR2(100) NOT NULL,
job VARCHAR2(100)
);
-- Create sequence for employee
CREATE SEQUENCE seq_employee_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Create Branch table
CREATE TABLE Branch (
No NUMBER PRIMARY KEY,
Name VARCHAR2(100) UNIQUE NOT NULL
);
-- Create sequence for branch
CREATE SEQUENCE seq_branch_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Create Items table
CREATE TABLE Items (
itemNo NUMBER PRIMARY KEY,
ItemName VARCHAR2(100) UNIQUE NOT NULL,
Price NUMBER(10,2) NOT NULL,
total_quantity NUMBER DEFAULT 0,
catNo NUMBER,
CONSTRAINT fk_category FOREIGN KEY (catNo) REFERENCES Category(catNo)
);
-- Create sequence for items
CREATE SEQUENCE seq_item_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Create Items_stor_entry table
CREATE TABLE Items_stor_entry (
entry_no NUMBER PRIMARY KEY,
itemNo NUMBER,
entry_date DATE DEFAULT SYSDATE,
quantity NUMBER NOT NULL,
employeeNo NUMBER,
CONSTRAINT fk_item_entry FOREIGN KEY (itemNo) REFERENCES Items(itemNo),
CONSTRAINT fk_employee_entry FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);
-- Create sequence for items store entry
CREATE SEQUENCE seq_item_entry_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Create Customer table
CREATE TABLE Customer (
customerNo NUMBER PRIMARY KEY,
costomerName VARCHAR2(100) NOT NULL,
gender CHAR(1) CHECK (gender IN ('M', 'F'))
);
-- Create sequence for customer
CREATE SEQUENCE seq_customer_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Create Invoice table
CREATE TABLE Invoice (
invoiceNo NUMBER PRIMARY KEY,
employeeNo NUMBER,
customerNo NUMBER,
total_price NUMBER(10,2) DEFAULT 0,
InvoiceDate DATE DEFAULT SYSDATE,
branchNo NUMBER,
CONSTRAINT fk_employee_invoice FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT fk_customer_invoice FOREIGN KEY (customerNo) REFERENCES Customer(customerNo),
CONSTRAINT fk_branch_invoice FOREIGN KEY (branchNo) REFERENCES Branch(No)
);
-- Create sequence for invoice
CREATE SEQUENCE seq_invoice_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Create InvoiceDetail table
CREATE TABLE InvoiceDetail (
invoiceNo NUMBER,
ItemNo NUMBER,
paidPrice NUMBER(10,2) NOT NULL,
quantity NUMBER NOT NULL,
CONSTRAINT pk_invoice_detail PRIMARY KEY (invoiceNo, ItemNo),
CONSTRAINT fk_invoice FOREIGN KEY (invoiceNo) REFERENCES Invoice(invoiceNo),
CONSTRAINT fk_item_detail FOREIGN KEY (ItemNo) REFERENCES Items(itemNo)
);
-- Create table for deleted invoice details history
CREATE TABLE InvoiceDetail_deleted_History (
invoiceNo NUMBER,
ItemNo NUMBER,
paidPrice NUMBER(10,2),
quantity NUMBER,
deletion_date DATE DEFAULT SYSDATE,
CONSTRAINT fk_invoice_history FOREIGN KEY (invoiceNo) REFERENCES Invoice(invoiceNo),
CONSTRAINT fk_item_history FOREIGN KEY (ItemNo) REFERENCES Items(itemNo)
);
-- Step 3: Create getItemTotalPrice function
CREATE OR REPLACE FUNCTION getItemTotalPrice(p_quantity NUMBER, p_paidPrice NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_quantity * p_paidPrice;
END;
/
-- Step 4: Create triggers for automatic ID generation
-- Trigger for Category ID
CREATE OR REPLACE TRIGGER trg_category_id
BEFORE INSERT ON Category
FOR EACH ROW
BEGIN
IF :NEW.catNo IS NULL THEN
SELECT seq_category_id.NEXTVAL INTO :NEW.catNo FROM DUAL;
END IF;
END;
/
-- Trigger for Employee ID
CREATE OR REPLACE TRIGGER trg_employee_id
BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
IF :NEW.employeeNo IS NULL THEN
SELECT seq_employee_id.NEXTVAL INTO :NEW.employeeNo FROM DUAL;
END IF;
END;
/
-- Trigger for Branch ID
CREATE OR REPLACE TRIGGER trg_branch_id
BEFORE INSERT ON Branch
FOR EACH ROW
BEGIN
IF :NEW.No IS NULL THEN
SELECT seq_branch_id.NEXTVAL INTO :NEW.No FROM DUAL;
END IF;
END;
/
-- Trigger for Item ID
CREATE OR REPLACE TRIGGER trg_item_id
BEFORE INSERT ON Items
FOR EACH ROW
BEGIN
IF :NEW.itemNo IS NULL THEN
SELECT seq_item_id.NEXTVAL INTO :NEW.itemNo FROM DUAL;
END IF;
END;
/
-- Trigger for Item Entry ID
CREATE OR REPLACE TRIGGER trg_item_entry_id
BEFORE INSERT ON Items_stor_entry
FOR EACH ROW
BEGIN
IF :NEW.entry_no IS NULL THEN
SELECT seq_item_entry_id.NEXTVAL INTO :NEW.entry_no FROM DUAL;
END IF;
END;
/
-- Trigger for Customer ID
CREATE OR REPLACE TRIGGER trg_customer_id
BEFORE INSERT ON Customer
FOR EACH ROW
BEGIN
IF :NEW.customerNo IS NULL THEN
SELECT seq_customer_id.NEXTVAL INTO :NEW.customerNo FROM DUAL;
END IF;
END;
/
-- Trigger for Invoice ID
CREATE OR REPLACE TRIGGER trg_invoice_id
BEFORE INSERT ON Invoice
FOR EACH ROW
BEGIN
IF :NEW.invoiceNo IS NULL THEN
SELECT seq_invoice_id.NEXTVAL INTO :NEW.invoiceNo FROM DUAL;
END IF;
END;
/
-- Step 5: Create security trigger to prevent invoice modifications on weekends
CREATE OR REPLACE TRIGGER trg_invoice_weekend_security
BEFORE INSERT OR UPDATE OR DELETE ON Invoice
DECLARE
v_day VARCHAR2(20);
BEGIN
SELECT TO_CHAR(SYSDATE, 'DAY') INTO v_day FROM DUAL;
-- Check if it's Friday or Saturday
IF TRIM(v_day) IN ('FRIDAY', 'SATURDAY') THEN
RAISE_APPLICATION_ERROR(-20001, 'Invoice operations are not allowed on Friday and Saturday');
END IF;
END;
/
CREATE OR REPLACE TRIGGER trg_invoice_detail_weekend_security
BEFORE INSERT OR UPDATE OR DELETE ON InvoiceDetail
DECLARE
v_day VARCHAR2(20);
BEGIN
SELECT TO_CHAR(SYSDATE, 'DAY') INTO v_day FROM DUAL;
-- Check if it's Friday or Saturday
IF TRIM(v_day) IN ('FRIDAY', 'SATURDAY') THEN
RAISE_APPLICATION_ERROR(-20002, 'Invoice detail operations are not allowed on Friday and Saturday');
END IF;
END;
/
-- Step 6: Create views for simplified data access
-- Invoice Master View
CREATE OR REPLACE VIEW invoiceMaster AS
SELECT
i.invoiceNo,
i.InvoiceDate,
i.total_price,
e.employeeName,
c.costomerName AS customerName,
b.Name AS branchName,
c.customerNo,
e.employeeNo,
b.No AS branchNo
FROM
Invoice i
JOIN Customer c ON i.customerNo = c.customerNo
JOIN Employee e ON i.employeeNo = e.employeeNo
JOIN Branch b ON i.branchNo = b.No;
-- Invoice Detail View
CREATE OR REPLACE VIEW invoiceMasterDetail AS
SELECT
id.invoiceNo,
i.ItemName,
id.quantity,
id.paidPrice,
getItemTotalPrice(id.quantity, id.paidPrice) AS total_price,
id.ItemNo
FROM
InvoiceDetail id
JOIN Items i ON id.ItemNo = i.itemNo;
-- Step 7: Create the pointOfSal package
CREATE OR REPLACE PACKAGE pointOfSal AS
-- Check existing functions
FUNCTION checkExistingCustomer(p_customerName VARCHAR2) RETURN BOOLEAN;
FUNCTION checkExistingEmployee(p_employeeName VARCHAR2) RETURN BOOLEAN;
FUNCTION checkExistingCategory(p_categoryName VARCHAR2) RETURN BOOLEAN;
FUNCTION checkExistingItem(p_itemName VARCHAR2) RETURN BOOLEAN;
FUNCTION checkExistingBranch(p_branchName VARCHAR2) RETURN BOOLEAN;
FUNCTION checkExistingInvoice(p_invoiceNo NUMBER) RETURN BOOLEAN;
FUNCTION checkExistingInvoiceItem(p_invoiceNo NUMBER, p_itemNo NUMBER) RETURN VARCHAR2;
-- Get info functions
FUNCTION getCategoryNo(p_categoryName VARCHAR2) RETURN NUMBER;
FUNCTION getItemNo(p_itemName VARCHAR2) RETURN NUMBER;
FUNCTION getEmployeeNo(p_employeeName VARCHAR2) RETURN NUMBER;
FUNCTION getCustomerNo(p_customerName VARCHAR2) RETURN NUMBER;
FUNCTION getBranchNo(p_branchName VARCHAR2) RETURN NUMBER;
FUNCTION getItemPrice(p_itemNo NUMBER) RETURN NUMBER;
FUNCTION getItemInvoiceQuantity(p_invoiceNo NUMBER, p_itemNo NUMBER) RETURN NUMBER;
FUNCTION getItemInvoicePaidPrice(p_invoiceNo NUMBER, p_itemNo NUMBER) RETURN NUMBER;
FUNCTION getNextInvoiceNo RETURN NUMBER;
-- Main procedures
PROCEDURE addCustomer(p_customerName VARCHAR2, p_gender CHAR);
PROCEDURE addEmployee(p_employeeName VARCHAR2, p_job VARCHAR2);
PROCEDURE addCategory(p_categoryName VARCHAR2);
PROCEDURE addItem(p_itemName VARCHAR2, p_price NUMBER, p_total_quantity NUMBER, p_categoryName VARCHAR2);
PROCEDURE addBranch(p_branchName VARCHAR2);
PROCEDURE newItemQuantity(p_itemName VARCHAR2, p_newQuantity NUMBER);
PROCEDURE addInvoice(p_customerName VARCHAR2, p_employeeName VARCHAR2, p_branchName VARCHAR2, p_invoiceDate DATE DEFAULT SYSDATE);
PROCEDURE addInvoiceDetail(p_invoiceNo NUMBER, p_itemName VARCHAR2, p_quantity NUMBER);
PROCEDURE removeInvoiceItem(p_invoiceNo NUMBER, p_itemName VARCHAR2);
-- XML procedures
PROCEDURE generateInvoiceMasterXML;
PROCEDURE generateInvoiceDetailXML;
PROCEDURE addCustomerListXML(p_customerList XMLTYPE);
END pointOfSal;
/
CREATE OR REPLACE PACKAGE BODY pointOfSal AS
-- Check existing functions implementation
FUNCTION checkExistingCustomer(p_customerName VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM Customer WHERE UPPER(costomerName) = UPPER(p_customerName);
RETURN v_count > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END checkExistingCustomer;
FUNCTION checkExistingEmployee(p_employeeName VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM Employee WHERE UPPER(employeeName) = UPPER(p_employeeName);
RETURN v_count > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END checkExistingEmployee;
FUNCTION checkExistingCategory(p_categoryName VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM Category WHERE UPPER(catogeryName) = UPPER(p_categoryName);
RETURN v_count > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END checkExistingCategory;
FUNCTION checkExistingItem(p_itemName VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM Items WHERE UPPER(ItemName) = UPPER(p_itemName);
RETURN v_count > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END checkExistingItem;
FUNCTION checkExistingBranch(p_branchName VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM Branch WHERE UPPER(Name) = UPPER(p_branchName);
RETURN v_count > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END checkExistingBranch;
FUNCTION checkExistingInvoice(p_invoiceNo NUMBER) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM Invoice WHERE invoiceNo = p_invoiceNo;
RETURN v_count > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END checkExistingInvoice;
FUNCTION checkExistingInvoiceItem(p_invoiceNo NUMBER, p_itemNo NUMBER) RETURN VARCHAR2 IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM InvoiceDetail
WHERE invoiceNo = p_invoiceNo AND ItemNo = p_itemNo;
IF v_count > 0 THEN
RETURN 'exists';
ELSE
RETURN 'not exists';
END IF;
END checkExistingInvoiceItem;
-- Get info functions implementation
FUNCTION getCategoryNo(p_categoryName VARCHAR2) RETURN NUMBER IS
v_catNo NUMBER;
BEGIN
SELECT catNo INTO v_catNo FROM Category WHERE UPPER(catogeryName) = UPPER(p_categoryName);
RETURN v_catNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END getCategoryNo;
FUNCTION getItemNo(p_itemName VARCHAR2) RETURN NUMBER IS
v_itemNo NUMBER;
BEGIN
SELECT itemNo INTO v_itemNo FROM Items WHERE UPPER(ItemName) = UPPER(p_itemName);
RETURN v_itemNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END getItemNo;
FUNCTION getEmployeeNo(p_employeeName VARCHAR2) RETURN NUMBER IS
v_employeeNo NUMBER;
BEGIN
SELECT employeeNo INTO v_employeeNo FROM Employee WHERE UPPER(employeeName) = UPPER(p_employeeName);
RETURN v_employeeNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END getEmployeeNo;
FUNCTION getCustomerNo(p_customerName VARCHAR2) RETURN NUMBER IS
v_customerNo NUMBER;
BEGIN
SELECT customerNo INTO v_customerNo FROM Customer WHERE UPPER(costomerName) = UPPER(p_customerName);
RETURN v_customerNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END getCustomerNo;
FUNCTION getBranchNo(p_branchName VARCHAR2) RETURN NUMBER IS
v_branchNo NUMBER;
BEGIN
SELECT No INTO v_branchNo FROM Branch WHERE UPPER(Name) = UPPER(p_branchName);
RETURN v_branchNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END getBranchNo;
FUNCTION getItemPrice(p_itemNo NUMBER) RETURN NUMBER IS
v_price NUMBER;
BEGIN
SELECT Price INTO v_price FROM Items WHERE itemNo = p_itemNo;
RETURN v_price;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END getItemPrice;
FUNCTION getItemInvoiceQuantity(p_invoiceNo NUMBER, p_itemNo NUMBER) RETURN NUMBER IS
v_quantity NUMBER;
BEGIN
SELECT quantity INTO v_quantity FROM InvoiceDetail
WHERE invoiceNo = p_invoiceNo AND ItemNo = p_itemNo;
RETURN v_quantity;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END getItemInvoiceQuantity;
FUNCTION getItemInvoicePaidPrice(p_invoiceNo NUMBER, p_itemNo NUMBER) RETURN NUMBER IS
v_paidPrice NUMBER;
BEGIN
SELECT paidPrice INTO v_paidPrice FROM InvoiceDetail
WHERE invoiceNo = p_invoiceNo AND ItemNo = p_itemNo;
RETURN v_paidPrice;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END getItemInvoicePaidPrice;
FUNCTION getNextInvoiceNo RETURN NUMBER IS
v_max NUMBER;
BEGIN
SELECT NVL(MAX(invoiceNo), 0) + 1 INTO v_max FROM Invoice;
RETURN v_max;
END getNextInvoiceNo;
-- Main procedures implementation
PROCEDURE addCustomer(p_customerName VARCHAR2, p_gender CHAR) IS
BEGIN
IF NOT checkExistingCustomer(p_customerName) THEN
INSERT INTO Customer (customerNo, costomerName, gender)
VALUES (seq_customer_id.NEXTVAL, p_customerName, UPPER(p_gender));
COMMIT;
DBMS_OUTPUT.PUT_LINE('Customer ' || p_customerName || ' added successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Customer with name ' || p_customerName || ' already exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error adding customer: ' || SQLERRM);
END addCustomer;
PROCEDURE addEmployee(p_employeeName VARCHAR2, p_job VARCHAR2) IS
BEGIN
IF NOT checkExistingEmployee(p_employeeName) THEN
INSERT INTO Employee (employeeNo, employeeName, job)
VALUES (seq_employee_id.NEXTVAL, p_employeeName, p_job);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Employee ' || p_employeeName || ' added successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee with name ' || p_employeeName || ' already exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error adding employee: ' || SQLERRM);
END addEmployee;
PROCEDURE addCategory(p_categoryName VARCHAR2) IS
BEGIN
IF NOT checkExistingCategory(p_categoryName) THEN
INSERT INTO Category (catNo, catogeryName)
VALUES (seq_category_id.NEXTVAL, p_categoryName);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Category ' || p_categoryName || ' added successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Category with name ' || p_categoryName || ' already exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error adding category: ' || SQLERRM);
END addCategory;
PROCEDURE addItem(p_itemName VARCHAR2, p_price NUMBER, p_total_quantity NUMBER, p_categoryName VARCHAR2) IS
v_catNo NUMBER;
BEGIN
-- Check if item exists
IF NOT checkExistingItem(p_itemName) THEN
-- Get category number
v_catNo := getCategoryNo(p_categoryName);
-- Check if category exists
IF v_catNo != -1 THEN
INSERT INTO Items (itemNo, ItemName, Price, total_quantity, catNo)
VALUES (seq_item_id.NEXTVAL, p_itemName, p_price, p_total_quantity, v_catNo);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Item ' || p_itemName || ' added successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Category ' || p_categoryName || ' does not exist.');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Item with name ' || p_itemName || ' already exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error adding item: ' || SQLERRM);
END addItem;
PROCEDURE addBranch(p_branchName VARCHAR2) IS
BEGIN
IF NOT checkExistingBranch(p_branchName) THEN
INSERT INTO Branch (No, Name)
VALUES (seq_branch_id.NEXTVAL, p_branchName);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Branch ' || p_branchName || ' added successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Branch with name ' || p_branchName || ' already exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error adding branch: ' || SQLERRM);
END addBranch;
PROCEDURE newItemQuantity(p_itemName VARCHAR2, p_newQuantity NUMBER) IS
v_itemNo NUMBER;
v_employeeNo NUMBER := 1; -- Default employee for demo purposes
BEGIN
-- Get item number
v_itemNo := getItemNo(p_itemName);
-- Check if item exists
IF v_itemNo != -1 THEN
-- Add new entry to Items_stor_entry
INSERT INTO Items_stor_entry (entry_no, itemNo, quantity, employeeNo)
VALUES (seq_item_entry_id.NEXTVAL, v_itemNo, p_newQuantity, v_employeeNo);
-- Update total quantity in Items table
UPDATE Items
SET total_quantity = total_quantity + p_newQuantity
WHERE itemNo = v_itemNo;
COMMIT;
-- Get updated quantity
DECLARE
v_updated_quantity NUMBER;
BEGIN
SELECT total_quantity INTO v_updated_quantity
FROM Items WHERE itemNo = v_itemNo;
DBMS_OUTPUT.PUT_LINE('Added ' || p_newQuantity || ' units to item ' || p_itemName ||
'. New total quantity: ' || v_updated_quantity);
END;
ELSE
DBMS_OUTPUT.PUT_LINE('No item has this name: ' || p_itemName);
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error updating item quantity: ' || SQLERRM);
END newItemQuantity;
PROCEDURE addInvoice(p_customerName VARCHAR2, p_employeeName VARCHAR2, p_branchName VARCHAR2, p_invoiceDate DATE DEFAULT SYSDATE) IS
v_customerNo NUMBER;
v_employeeNo NUMBER;
v_branchNo NUMBER;
v_invoiceNo NUMBER;
BEGIN
-- Get customer, employee and branch numbers
v_customerNo := getCustomerNo(p_customerName);
v_employeeNo := getEmployeeNo(p_employeeName);
v_branchNo := getBranchNo(p_branchName);
-- Check if all exist
IF v_customerNo != -1 AND v_employeeNo != -1 AND v_branchNo != -1 THEN
-- Get next invoice number
v_invoiceNo := getNextInvoiceNo();
-- Create invoice with total price = 0
INSERT INTO Invoice (invoiceNo, employeeNo, customerNo, total_price, InvoiceDate, branchNo)
VALUES (v_invoiceNo, v_employeeNo, v_customerNo, 0, p_invoiceDate, v_branchNo);
COMMIT;
DBMS_OUTPUT.PUT_LINE('One record has been created with invoice No: ' || v_invoiceNo);
ELSE
IF v_customerNo = -1 THEN
DBMS_OUTPUT.PUT_LINE('Customer ' || p_customerName || ' does not exist.');
END IF;
IF v_employeeNo = -1 THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || p_employeeName || ' does not exist.');
END IF;
IF v_branchNo = -1 THEN
DBMS_OUTPUT.PUT_LINE('Branch ' || p_branchName || ' does not exist.');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error creating invoice: ' || SQLERRM);
END addInvoice;
PROCEDURE addInvoiceDetail(p_invoiceNo NUMBER, p_itemName VARCHAR2, p_quantity NUMBER) IS
v_itemNo NUMBER;
v_itemPrice NUMBER;
v_itemExists VARCHAR2(20);
v_totalPrice NUMBER;
BEGIN
-- Check if invoice exists
IF checkExistingInvoice(p_invoiceNo) THEN
-- Get item number
v_itemNo := getItemNo(p_itemName);
-- Check if item exists
IF v_itemNo != -1 THEN
-- Get item price
v_itemPrice := getItemPrice(v_itemNo);
-- Check if item exists in this invoice
v_itemExists := checkExistingInvoiceItem(p_invoiceNo, v_itemNo);
IF v_itemExists = 'not exists' THEN
-- Add new item to invoice
INSERT INTO InvoiceDetail (invoiceNo, ItemNo, paidPrice, quantity)
VALUES (p_invoiceNo, v_itemNo, v_itemPrice, p_quantity);
-- Calculate total price for this item
v_totalPrice := getItemTotalPrice(p_quantity, v_itemPrice);
-- Update invoice total price
UPDATE Invoice
SET total_price = total_price + v_totalPrice
WHERE invoiceNo = p_invoiceNo;
-- Update item quantity in inventory
UPDATE Items
SET total_quantity = total_quantity - p_quantity
WHERE itemNo = v_itemNo;
COMMIT;
DBMS_OUTPUT.PUT_LINE('One item: ' || p_itemName || ' has been added successfully.');
ELSE
-- Item already exists in invoice, update quantity
DECLARE
v_oldQuantity NUMBER;
v_oldTotalPrice NUMBER;
BEGIN
-- Get current quantity
v_oldQuantity := getItemInvoiceQuantity(p_invoiceNo, v_itemNo);
-- Calculate old total price
v_oldTotalPrice := getItemTotalPrice(v_oldQuantity, v_itemPrice);
-- Update quantity
UPDATE InvoiceDetail
SET quantity = quantity + p_quantity
WHERE invoiceNo = p_invoiceNo AND ItemNo = v_itemNo;
-- Calculate new total price for this item
v_totalPrice := getItemTotalPrice(v_oldQuantity + p_quantity, v_itemPrice);
-- Update invoice total price
UPDATE Invoice
SET total_price = total_price - v_oldTotalPrice + v_totalPrice
WHERE invoiceNo = p_invoiceNo;
-- Update item quantity in inventory
UPDATE Items
SET total_quantity = total_quantity - p_quantity
WHERE itemNo = v_itemNo;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Quantity updated for item: ' || p_itemName || ' in invoice ' || p_invoiceNo);
END;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('There is no item with this name: ' || p_itemName);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Invoice with number ' || p_invoiceNo || ' does not exist.');
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error adding invoice detail: ' || SQLERRM);
END addInvoiceDetail;
PROCEDURE removeInvoiceItem(p_invoiceNo NUMBER, p_itemName VARCHAR2) IS
v_itemNo NUMBER;
v_itemExists VARCHAR2(20);
v_quantity NUMBER;
v_paidPrice NUMBER;
v_totalPrice NUMBER;
BEGIN
-- Get item number
v_itemNo := getItemNo(p_itemName);
-- Check if item exists
IF v_itemNo != -1 THEN
-- Check if item exists in this invoice
v_itemExists := checkExistingInvoiceItem(p_invoiceNo, v_itemNo);
IF v_itemExists = 'exists' THEN
-- Get quantity and price
v_quantity := getItemInvoiceQuantity(p_invoiceNo, v_itemNo);
v_paidPrice := getItemInvoicePaidPrice(p_invoiceNo, v_itemNo);
-- Calculate total price for this item
v_totalPrice := getItemTotalPrice(v_quantity, v_paidPrice);
-- Increase item quantity in inventory
UPDATE Items
SET total_quantity = total_quantity + v_quantity
WHERE itemNo = v_itemNo;
-- Decrease invoice total price
UPDATE Invoice
SET total_price = total_price - v_totalPrice
WHERE invoiceNo = p_invoiceNo;
-- Insert record into deletion history
INSERT INTO InvoiceDetail_deleted_History (invoiceNo, ItemNo, paidPrice, quantity)
VALUES (p_invoiceNo, v_itemNo, v_paidPrice, v_quantity);
-- Delete item from invoice
DELETE FROM InvoiceDetail
WHERE invoiceNo = p_invoiceNo AND ItemNo = v_itemNo;
COMMIT;
DBMS_OUTPUT.PUT_LINE('One record deleted successfully');
ELSE
DBMS_OUTPUT.PUT_LINE('Item ' || p_itemName || ' does not exist in invoice ' || p_invoiceNo);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('There is no item with this name: ' || p_itemName);
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error removing invoice item: ' || SQLERRM);
END removeInvoiceItem;
-- XML procedures implementation
PROCEDURE generateInvoiceMasterXML IS
v_xml_clob CLOB;
v_file UTL_FILE.FILE_TYPE;
BEGIN
-- Generate XML from invoiceMaster view
SELECT XMLELEMENT(
"Invoices",
XMLAGG(
XMLELEMENT("Invoice",
XMLELEMENT("InvoiceNo", invoiceNo),
XMLELEMENT("InvoiceDate", InvoiceDate),
XMLELEMENT("TotalPrice", total_price),
XMLELEMENT("CustomerName", customerName),
XMLELEMENT("EmployeeName", employeeName),
XMLELEMENT("BranchName", branchName)
)
)
).getClobVal() INTO v_xml_clob
FROM invoiceMaster;
-- Write XML to file
v_file := UTL_FILE.FOPEN('XML_DIR', 'invoice_master.xml', 'W', 32767);
UTL_FILE.PUT_LINE(v_file, '<?xml version="1.0" encoding="UTF-8"?>');
UTL_FILE.PUT_LINE(v_file, v_xml_clob);
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('Invoice master XML file generated successfully.');
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(v_file) THEN
UTL_FILE.FCLOSE(v_file);
END IF;
DBMS_OUTPUT.PUT_LINE('Error generating XML file: ' || SQLERRM);
END generateInvoiceMasterXML;
PROCEDURE generateInvoiceDetailXML IS
v_xml_clob CLOB;
v_file UTL_FILE.FILE_TYPE;
-- Cursor for all invoices
CURSOR c_invoices IS
SELECT DISTINCT invoiceNo FROM invoiceMaster;
BEGIN
-- Create a CLOB to hold XML data
DBMS_LOB.CREATETEMPORARY(v_xml_clob, TRUE);
DBMS_LOB.APPEND(v_xml_clob, '<?xml version="1.0" encoding="UTF-8"?>');
DBMS_LOB.APPEND(v_xml_clob, '<InvoicesDetail>');
-- Loop through invoices
FOR inv_rec IN c_invoices LOOP
-- Add invoice master data
DECLARE
v_invoice_xml CLOB;
BEGIN
SELECT XMLELEMENT(
"Invoice",
XMLELEMENT("InvoiceNo", im.invoiceNo),
XMLELEMENT("InvoiceDate", im.InvoiceDate),
XMLELEMENT("TotalPrice", im.total_price),
XMLELEMENT("CustomerName", im.customerName),
XMLELEMENT("EmployeeName", im.employeeName),
XMLELEMENT("BranchName", im.branchName),
XMLELEMENT("Items",
(SELECT XMLAGG(
XMLELEMENT("Item",
XMLELEMENT("ItemName", imd.ItemName),
XMLELEMENT("Quantity", imd.quantity),
XMLELEMENT("PaidPrice", imd.paidPrice),
XMLELEMENT("TotalPrice", imd.total_price)
)
)
FROM invoiceMasterDetail imd
WHERE imd.invoiceNo = im.invoiceNo)
)
).getClobVal() INTO v_invoice_xml
FROM invoiceMaster im
WHERE im.invoiceNo = inv_rec.invoiceNo;
DBMS_LOB.APPEND(v_xml_clob, v_invoice_xml);
END;
END LOOP;
DBMS_LOB.APPEND(v_xml_clob, '</InvoicesDetail>');
-- Write XML to file
v_file := UTL_FILE.FOPEN('XML_DIR', 'invoice_detail.xml', 'W', 32767);
UTL_FILE.PUT_LINE(v_file, v_xml_clob);
UTL_FILE.FCLOSE(v_file);
-- Free temporary CLOB
DBMS_LOB.FREETEMPORARY(v_xml_clob);
DBMS_OUTPUT.PUT_LINE('Invoice detail XML file generated successfully.');
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(v_file) THEN
UTL_FILE.FCLOSE(v_file);
END IF;
IF DBMS_LOB.ISTEMPORARY(v_xml_clob) = 1 THEN
DBMS_LOB.FREETEMPORARY(v_xml_clob);
END IF;
DBMS_OUTPUT.PUT_LINE('Error generating XML file: ' || SQLERRM);
END generateInvoiceDetailXML;
PROCEDURE addCustomerListXML(p_customerList XMLTYPE) IS
v_customers XMLSequenceType;
v_customerName VARCHAR2(100);
v_gender CHAR(1);
BEGIN
-- Extract customers from XML
v_customers := p_customerList.extract('/customers/customer').getSequenceVal();
-- Process each customer
FOR i IN 1..v_customers.COUNT LOOP
-- Extract customer name and gender
v_customerName := v_customers(i).extract('//name/text()').getStringVal();
v_gender := v_customers(i).extract('//gender/text()').getStringVal();
-- Add customer
addCustomer(v_customerName, v_gender);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Customers from XML added successfully.');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error adding customers from XML: ' || SQLERRM);
END addCustomerListXML;
END pointOfSal;
/
Telegram: DB developers channel