pls use oracle PL/sql command line

------

Q1) Create a complete project using SQL/PLSQL programing language to achieve the following requirement to develop point of sale system which allow the Employee to add customer invoice which contain his/her invoice information such as customer name, employee name who create this invoice which contain invoice date and total price with its related purchased items which contain quantity and price for each item in customer invoice.

Follow these requirements

1) Design database schema and create database user pointOfSal that contain database schema tables with itsrelated relationships and database constraints

2) Grant the pointOfSal user all privileges that needed by database designer and developer to create database schema

3) The system must contain tables such as category(catNo,catogeryName) ,Items(itemNo,ItemName,Price,total_quantity, catNo),Items_stor_entry(entry_no number primary key,itemNo,entry_date ,quantity),

Employee (employeeNo,employeeName,job) ,branch(No,Name), Customer(customerNo,costomerName,gender) ,

,Invoice (employeeNo,customerNo,total price ,invoiceNo,InvoiceDate,branchNo )

InvoiceDeatail contain List Of Purchase Items contain detail information related to the invoice (invoiceNo,ItemNo, paidPrice, quantity) . create getItemTotalPrice function which to return quantity*paidPrice .

4) Create database package pointOfSal which contain the following

A. procedure to add new customer which use validation method checkExistingCustomer using cursor to validate customer name if its not exists before, then add this new customer and generate the customer number automatically using database trigger.

B. Method to add new Employee which use validation method checkExistingEmployee using cursor to validate Employee name if its not exists before, then add this Employee and generate the Employee number automatically using database trigger.

C. procedure to add new Category which use validation method checkExistingCategory using exception to validate category name if its not exists before, then add this category and generate the category number automatically using database trigger.

D. procedure to add new item(ItemName,Price,total_quantity, category name) which use validation method checkExistingitem using exception to validate item name if its not exists before ,then add this category and generate the item number automatically using database trigger , Note create method getCatgeoryNo(category name) to return category number to be used when item is added , this method will return -1 if category name not exists , and the procedure addItem will display message that one item record has been added or this category not exists or this item is exists before .

E. procedure to add new branchy which use validation method checkExistingbranch using cursor to validate branch name if its not exists before ,then add this branchand generate the branch number automatically using database trigger .

F. procedure to add newItemQuantaty(itemName,newquantaty) this firstly check the existing of this item name and return item no if exists else it return -1 , then if its exists then it will add new entry to Items_stor_entry table to create new row with this itemname as new entry to store. Then increase the item total_quantity in the items table with new quantity then display message show the items new total quantity in the store else if this item not exists it will display the message no item has this name will be display .

G. procedure to add new customer invoice addInvoice(customername,employeeName,branchName,invoice_date)

then the procedure will check from the exists of customername,employeeName,BranchName by calling there check existsing function for each name and if all of them are exists then a new record will be created with total price equel to zero and the invoceno will be generated using method getNextInvoiceNo() which return the maximum +1 , then the method will display the message one record has been created with invoice No:show the generated invoiceNo her in this message else display a specific message that show the customer or employee or branch name doesnt exist please re-enter correct names .

H. procedure to add Invoice detail AddInvoiceDeatail(invoiceNo,itemName,quantity). This method start by validating the existing of the current invoiceNO by call function checkExistingInvoice(invoiceNo1) so if this invoice exists then we will start checking the existing of item name by call getItemNo(itemName1) which will return itemNo if the item name is exists else it will return -1 ifthis item not exists in items then if exists we call getItemPrice(itemNo1) function to return item price.

After that if invoiceNo and ItemNO valid and exists before then the procedure will check the existsing if itemno of this invoiceNO not exists before by call checkExistingInvoiceItem(invoiceNo1,itemNo1) so this itemNo will be added to this invoiceNO in table InvoiceDeatail and the transaction related to adding this record as follow :

New record contain invoiceNo with i itemNo,paidprice, and quantity it will be added to InvoiceDeatail table then The total price for this invoice it will be increased by using this formula (quantity* price ) related to current item then the total price of invoice it will be increase, after that the item total quantity in items table will be decrease for this itemNO related to this itemNO in InvoiceDeatail . but if this itemNo exists before in this InvoiceNo then instead of insert new recorde with the new quantity in InvoiceDeatail table then the quantity it will be updated in for that item added before and the rest of logic related to increase the invoice total price and decrease item quantity from items table its the same scenario of added new items . the system will display a message that one item :item name has been addedsuccessfully else the message will display there is no item has this name: parameter item name .

I. procedure to remove item from customer invoice removeInvoiceItem(invoiceNo, itemName) where this method will firstly call getItemNo(itemNAme) method which it will return item no if exists else it will return -1 if not exists . After that the procedure will call function

checkExistingInvoiceItem(invoiceNo,itemNo) which will return exists if this item was sell in this invoice or not exists if not was sell. After that the procedure will call getitemInvoiceQuantity(invoiceNo,itemNo) ,getitemInvoicepaidprice(invoiceNo,itemNo) for existingitem in this invoice , then this function it will return the quantity for this item was sell to customer in this invoicethen the procedure will increase the total_quantity for this item in items table ,then it will decrease the total price related to this invoice by this formula {total price =total price (quantity*price)} .finaly after make updates then the invoiceNo with its itemNo and its quantity and paid price it will be inserted in InvoiceDeatail_delted_History table with deletion transaction date , after that then the item invoice record it will be deleted from InvoiceDeatail table related to this invoice item and message one record deleted successfully will be display to the user .

5) create view invoiceMaster which contain InvoiceNo,Date,total Price,EmployeeName,,customerName,branch name,customerNo ,employeeNo,branch no

6) create view invoiceMasterDetail which contain InvoiceNo,item name,quantity,price,total price ,itemNo

7) create a procedure getCustomerInvoiceInfo(invoiceno) where this procedure will firstly call chechExistingInvoice( invoiceNo) function if its exists then it will display the main information of this invoice will it will display invoice no,invoice data,totalprice,customer name,employee name,branch name as master information from invoiceMaster view

then it will list all item information (item name,quantity, price ,total price) from invoiceDetail

view related to this invoice .

8) apply transaction commit on each Stord procedure

9) make the database more secure by un allowed for any user to modify(apply dml) invoice master and detail table on Friday and Saturday by using statement trigger

10) generate an xml file for all created invoices records read from invoice master view