Implement the following stored procedures. Ensure that each stored procedure is tested with appropriate sample data and appropriate error messages raised when required. Test cases should be saved in a separate test script.
usp_addCustomerOrder
customer – customer who places the order
products – a list of products and quantity of each product (as a table valued parameter)
discount – discount amount (default is zero)
Add an order complete with all the products ordered, amount due, etc.
CREATE PROCEDURE "createDB"."dbo"." create_usp_addCustomerOrder"( cn IN "createDB"."dbo"."CustomerRecord".companyName%TYPE, cp IN "createDB"."dbo"."CustomerRecord".contactPerson%TYPE, e IN "createDB"."dbo"."CustomerRecord".email%TYPE, g IN "createDB"."dbo"."CustomerRecord".gender%TYPE, ca IN "createDB"."dbo"."CustomerRecord".custaddress%TYPE, p IN "createDB"."dbo"."CustomerRecord".phone%TYPE, ec IN "createDB"."dbo"."CustomerRecord".emaxCredit%TYPE co IN "createDB"."dbo"."CustomerOrder".CustomerOrderID%TYPE ) AS counted NUMBER; BEGIN SELECT count(*) into counted FROM "createDB"."dbo"."CustomerRecord"; IF(counted=0) then INSERT INTO "createDB"."dbo"."CustomerRecord" VALUES (companyName(cn), contactPerson(cp),e,g,ca,p,ec,co, 'N'); else insert into "createDB"."dbo"."CustomerRecord" VALUES (companyName(cn), contactPerson(cp),e,g,ca,p,ec,co, (select max(CustomerOrderID) +1 FROM "createDB"."dbo"."CustomerRecord"), "N'); END IF; END;
EXEC "createDB"."dbo"." create_usp_addCustomerOrder";
This stored procedure can be executed only by sales staff. The sales employee who executes this stored procedure is the employee making the order. Ensure that you create appropriate roles; users etc. and grant appropriate permissions.
usp_printOutstandingCustomers
None
Print the customer id, name and total outstanding amount for all customers who owe funds to OfficeWizard
CREATE PROCEDURE "createDB"."dbo"."create_usp_printOutstandingCustomers" ( do IN "createDB"."dbo"."CustomerSale".dateOfOrder%TYPE, d IN "createDB"."dbo"."CustomerSale".discount%TYPE, of IN "createDB"."dbo"."CustomerSale".overdueFee%TYPE, cf IN "createDB"."dbo"."CustomerSale".cancellationFee%TYPE, bd IN "createDB"."dbo"."CustomerSale".billingDate%TYPE, dd IN "createDB"."dbo"."CustomerSale".dueDate%TYPE, cs IN "createDB"."dbo"."CustomerSale".custstatus%TYPE cd IN "createDB"."dbo"."CustomerSale".custdescription%TYPE ) AS counted NUMBER; BEGIN SELECT count(*) into counted FROM "createDB"."dbo"."CustomerSale"; IF(counted=0) then INSERT INTO "createDB"."dbo"."CustomerSale" VALUES ( do,d,of.cf,bd,dd,cs,cd,'N'); else insert into"createDB"."dbo"."CustomerSale" VALUES (do,d,of.cf,bd,dd,cs,cd, (select max(CustomerRecordID) +1 FROM "createDB"."dbo"."CustomerSale"), "N'); END IF; END;
EXEC "createDB"."dbo"."CustomerSale";
Any employee can execute this stored procedure
usp_ProductsToOrder
Print the products and their respective
None
Print all products (product IDs) and suppliers who supply these products for products whose re-order level >= to available quantity
CREATE PROCEDURE "createDB"."dbo"."create_usp_ProductsToOrder" ( n IN "createDB"."dbo"."Product".name%TYPE, m IN "createDB"."dbo"."Product".manufacturer%TYPE, c IN "createDB"."dbo"."Product".category%TYPE, pd IN "createDB"."dbo"."Product".prodescription%TYPE, qd IN "createDB"."dbo"."Product".quantityDescription%TYPE, up IN "createDB"."dbo"."Product".unitPrice%TYPE, ps IN "createDB"."dbo"."Product".prostatus%TYPE, qa IN "createDB"."dbo"."Product".quantityAvailable%TYPE, el IN "createDB"."dbo"."Product".reorderLevel%TYPE, md IN "createDB"."dbo"."Product".maxDiscount%TYPE ) AS BEGIN SELECT * FROM "createDB"."dbo"."Product"; END;
EXEC "createDB"."dbo"."Product";
Any employee can execute this stored procedure
usp_addQuotation
Supplier id Date (default is current date) Validity period for quotation (in months) Products, quantity and their respective prices (as a table valued parameter) Description (if any) Employee managing the quotation Quotation ID of the newly created quotation (output parameter)
Add the complete quotation to the database
CREATE PROCEDURE "createDB"."dbo"."create_usp_ProductsToOrder" ( qd IN "createDB"."dbo"."Quotation".qtDate%TYPE, vp IN "createDB"."dbo"."Quotation".validityPeriod%TYPE, qd IN "createDB"."dbo"."Quotation".QuotationDescription%TYPE ) AS BEGIN SELECT * FROM "createDB"."dbo"."Quotation"; IF(counted=0) then INSERT INTO "createDB"."dbo"."Quotation" VALUES ( qd, vp, qd, 'N'); else insert into "createDB"."dbo"."Quotation" VALUES (qd, vp, qd, (select max(qtNumber) +1 FROM "createDB"."dbo"."Quotation"), "N'); END IF; END;
EXEC "createDB"."dbo"."Quotation";
This stored procedure can be executed only by administration staff. The admin employee who executes this stored procedure is the employee requesting the quotation. Ensure that you create appropriate roles; users etc. and grant appropriate permissions.
Also, the quotation id of the newly created quotation needs to be returned as an output parameter.
SELECT cr.companyName from "createDB"."dbo"."CustomerSale" as cs , "createDB"."dbo"."CustomerRecord" as cr, "createDB"."dbo"."CustomerOrder" as co where cs.CustomerRecordID = cr.CustomerRecordID and cr.CustomerOrderID = co.CustomerOrderID and cs.overdueFee >=5000;
SELECT cr.companyName from "createDB"."dbo"."CustomerSale" as cs , "createDB"."dbo"."CustomerRecord" as cr, "createDB"."dbo"."CustomerOrder" as co, "createDB"."dbo"."Employee" as e where cs.CustomerRecordID = cr.CustomerRecordID and cr.CustomerOrderID = co.CustomerOrderID and e.EmployeeID = co.EmployeeID;
Urgenthomework helped me with finance homework problems and taught math portion of my course as well. Initially, I used a tutor that taught me math course I felt that as if I was not getting the help I needed. With the help of Urgenthomework, I got precisely where I was weak:
Read More
Follow Us