Northwind Orders Form SQL Converted Into an Oracle Package

The following package was generated by Scriptoria for Oracle from the
Northwind Traders Orders and Order Subform Forms. The procedures
implement all database opreations required by the Forms.

CREATE OR REPLACE PACKAGE BODY NORTHWIND.PKG_NORTHWIND
AS

/* When a CustomerID is selected from the CustomerID ComboBox on the Orders form, the data used to populate the customer address fields are retrieved by passing the Customer ID to ORDERSCUSTOMERID_AUTOLKUP.
*/
PROCEDURE ORDERSCUSTOMERID_AUTOLKUP
(
crsrResultSet IN OUT refCursor,
P_CUSTOMERID VARCHAR2
)
AS
BEGIN

OPEN crsrResultSet FOR
  SELECT ADDRESS,
  CITY,
  REGION,
  POSTALCODE,
  COUNTRY
  FROM NORTHWIND.CUSTOMERS
  WHERE (
  CUSTOMERID = P_CUSTOMERID
  )
;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_CUSTOMERID retrieves a result set for populating the CustomerID ComboBox.
*/
PROCEDURE ORDERS_CUSTOMERID
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
  OPEN crsrResultSet FOR
  SELECT DISTINCT CUSTOMERS.CUSTOMERID,
  CUSTOMERS.COMPANYNAME
  FROM NORTHWIND.CUSTOMERS
  ORDER BY CUSTOMERS.COMPANYNAME
;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_DELETE deletes a record from the Orders table for the supplied OrderID unless the record has been updated by another user during the time the current user has retrieved the record. This is termed 'Optimistic Concurrency' and requires the original values of all fields to be passed to the procedure. The p__ROWS_RETURNED signals the status of the operation to the calling program.
*/
PROCEDURE ORDERS_DELETE
(
p_OrderID NUMBER,
p_OrigCustomerID VARCHAR2,
p_OrigEmployeeID NUMBER,
p_OrigOrderDate DATE,
p_OrigRequiredDate DATE,
p_OrigShippedDate DATE,
p_OrigFreight NUMBER,
p_OrigShipName VARCHAR2,
p_OrigShipAddress VARCHAR2,
p_OrigShipCity VARCHAR2,
p_OrigShipRegion VARCHAR2,
p_OrigShipPostalCode VARCHAR2,
p_OrigShipCountry VARCHAR2,
p_OrigShipVia NUMBER,
p__ROWS_RETURNED OUT NUMBER
)
AS
BEGIN
  SELECT NVL(COUNT(*), 0)
  INTO p__ROWS_RETURNED
  FROM ORDERS
  WHERE (ORDERID =   p_OrderID)
  AND ((p_OrigCustomerID IS NULL AND CUSTOMERID IS NULL)
  OR CUSTOMERID = p_OrigCustomerID
  AND (p_OrigEmployeeID IS NULL AND EMPLOYEEID IS NULL)
  OR EMPLOYEEID = p_OrigEmployeeID
  AND (p_OrigOrderDate IS NULL AND ORDERDATE IS NULL)
  OR ORDERDATE = p_OrigOrderDate
  AND (p_OrigRequiredDate IS NULL AND REQUIREDDATE IS NULL)
  OR REQUIREDDATE = p_OrigRequiredDate
  AND (p_OrigShippedDate IS NULL AND SHIPPEDDATE IS NULL)
  OR SHIPPEDDATE = p_OrigShippedDate
  AND (p_OrigFreight IS NULL AND FREIGHT IS NULL)
  OR FREIGHT = p_OrigFreight
  AND (p_OrigShipName IS NULL AND SHIPNAME IS NULL)
  OR SHIPNAME = p_OrigShipName
  AND (p_OrigShipAddress IS NULL AND SHIPADDRESS IS NULL)
  OR SHIPADDRESS = p_OrigShipAddress
  AND (p_OrigShipCity IS NULL AND SHIPCITY IS NULL)
  OR SHIPCITY = p_OrigShipCity
  AND (p_OrigShipRegion IS NULL AND SHIPREGION IS NULL)
  OR SHIPREGION = p_OrigShipRegion
  AND (p_OrigShipPostalCode IS NULL AND SHIPPOSTALCODE IS NULL)
  OR SHIPPOSTALCODE = p_OrigShipPostalCode
  AND (p_OrigShipCountry IS NULL AND SHIPCOUNTRY IS NULL)
  OR SHIPCOUNTRY = p_OrigShipCountry
  AND (p_OrigShipVia IS NULL AND SHIPVIA IS NULL)
  OR SHIPVIA = p_OrigShipVia);

  IF p__ROWS_RETURNED = 1 THEN
    DELETE ORDERS
    WHERE ORDERID = p_OrderID;
    COMMIT;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_EMPLOYEEID retrieves a result set for populating the EmployeeID ComboBox.
*/
PROCEDURE ORDERS_EMPLOYEEID
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
  OPEN crsrResultSet FOR
  SELECT DISTINCT EMPLOYEES.EMPLOYEEID,
  LASTNAME || ', ' || FIRSTNAME AS SALESPERSON
  FROM NORTHWIND.EMPLOYEES
  ORDER BY LASTNAME || ', ' || FIRSTNAME
;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_INSERT inserts a record into the Orders table. The value of the generated sequence is returned to the caller.
*/
PROCEDURE ORDERS_INSERT
(
p_CustomerID VARCHAR2,
p_EmployeeID NUMBER,
p_OrderDate DATE,
p_RequiredDate DATE,
p_ShippedDate DATE,
p_ShipVia NUMBER,
p_Freight NUMBER,
p_ShipName VARCHAR2,
p_ShipAddress VARCHAR2,
p_ShipCity VARCHAR2,
p_ShipRegion VARCHAR2,
p_ShipPostalCode VARCHAR2,
p_ShipCountry VARCHAR2,
p_OrderID OUT NUMBER
)
AS
BEGIN
  INSERT INTO ORDERS
   (CUSTOMERID, EMPLOYEEID, ORDERDATE, REQUIREDDATE, 
      SHIPPEDDATE, SHIPVIA, FREIGHT, SHIPNAME, SHIPADDRESS, 
     SHIPCITY, SHIPREGION, SHIPPOSTALCODE, SHIPCOUNTRY)
  VALUES
   (p_CustomerID, p_EmployeeID, p_OrderDate, p_RequiredDate,
     p_ShippedDate, p_ShipVia, p_Freight, p_ShipName, p_ShipAddress,
     p_ShipCity, p_ShipRegion, p_ShipPostalCode, p_ShipCountry);
  COMMIT;
  SELECT ORDERS_SQ.CurrVal INTO p_OrderID FROM DUAL;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_QRY retrieves the result set to populate the Orders form as specified by the RecordSource property.
*/
PROCEDURE ORDERS_QRY
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
  OPEN crsrResultSet FOR
  SELECT DISTINCT ORDERS.ORDERID,
    ORDERS.CUSTOMERID,
    ORDERS.EMPLOYEEID,
    ORDERS.ORDERDATE,
    ORDERS.REQUIREDDATE,
    ORDERS.SHIPPEDDATE,
    ORDERS.SHIPVIA,
    ORDERS.FREIGHT,
    ORDERS.SHIPNAME,
    ORDERS.SHIPADDRESS,
    ORDERS.SHIPCITY,
    ORDERS.SHIPREGION,
    ORDERS.SHIPPOSTALCODE,
    ORDERS.SHIPCOUNTRY,
    CUSTOMERS.COMPANYNAME,
    CUSTOMERS.ADDRESS,
    CUSTOMERS.CITY,
    CUSTOMERS.REGION,
    CUSTOMERS.POSTALCODE,
    CUSTOMERS.COUNTRY
  FROM NORTHWIND.CUSTOMERS,
    NORTHWIND.ORDERS
  WHERE (CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID)
;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_SUBFORM_DELETE deletes records from the Order Details table for the given OrderID and ProductID unless there is a concurrency violation.
*/
PROCEDURE ORDERS_SUBFORM_DELETE
(
p_OrderID NUMBER,
p_ProductID NUMBER,
p_OrigUnitPrice NUMBER,
p_OrigQuantity NUMBER,
p_OrigDiscount NUMBER,
p_OrigProductID NUMBER,
p__ROWS_RETURNED OUT NUMBER
)
AS
BEGIN
  SELECT NVL(COUNT(*), 0)
  INTO p__ROWS_RETURNED
  FROM ORDER_DETAILS
  WHERE (ORDERID = p_OrderID AND PRODUCTID = p_ProductID)
  AND (UNITPRICE =  p_OrigUnitPrice
  AND QUANTITY = p_OrigQuantity AND DISCOUNT = p_OrigDiscount
  AND PRODUCTID = p_OrigProductID);

  IF p__ROWS_RETURNED = 1 THEN
    DELETE ORDER_DETAILS
    WHERE ORDERID = p_OrderID AND PRODUCTID = p_ProductID;
    COMMIT;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_SUBFORM_INSERT inserts a record into the Order_Details table.
*/
PROCEDURE ORDERS_SUBFORM_INSERT
(
p_OrderID NUMBER,
p_ProductID NUMBER,
p_UnitPrice NUMBER,
p_Quantity NUMBER,
p_Discount NUMBER
)
AS
BEGIN
  INSERT INTO ORDER_DETAILS(ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT)
  VALUES(p_OrderID, p_ProductID, p_UnitPrice, p_Quantity, p_Discount);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_SUBFORM_PRODUCTID retrieves a result set for populating the ProductID ComboBox on the Order Details Sub Form.
*/
PROCEDURE ORDERS_SUBFORM_PRODUCTID
(
crsrResultSet IN OUT refCursor
)
AS
BEGIN
  OPEN crsrResultSet FOR
  SELECT DISTINCT PRODUCTID,
    PRODUCTNAME,
    DISCONTINUED
  FROM NORTHWIND.PRODUCTS
  ORDER BY PRODUCTNAME
;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_SUBFORM_SUBFRM retrieves Order Details records for the given OrderID as specified by the Record Source of the Order Details Sub Form.
*/
PROCEDURE ORDERS_SUBFORM_SUBFRM
(
crsrResultSet IN OUT refCursor,
P_ORDERID NUMBER
)
AS
BEGIN
  OPEN crsrResultSet FOR
    SELECT DISTINCT ORDER_DETAILS.ORDERID,
      ORDER_DETAILS.PRODUCTID,
      PRODUCTS.PRODUCTNAME,
      ORDER_DETAILS.UNITPRICE,
      ORDER_DETAILS.QUANTITY,
      ORDER_DETAILS.DISCOUNT,
      TO_NUMBER(ORDER_DETAILS.UNITPRICE*QUANTITY*(1-DISCOUNT)/100)*100
      AS EXTENDEDPRICE
    FROM NORTHWIND.PRODUCTS,
      NORTHWIND.ORDER_DETAILS
    WHERE (PRODUCTS.PRODUCTID=ORDER_DETAILS.PRODUCTID)
    AND ( (P_ORDERID = ORDERID) )
    ORDER BY ORDER_DETAILS.ORDERID
;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END;

/* ORDERS_SUBFORM_UPDATE updates the Order_Details record for the given OrderID and ProductID unless there is a concurrency violation.
*/
PROCEDURE ORDERS_SUBFORM_UPDATE
(
p_OrderID NUMBER,
p_ProductID NUMBER,
p_OrigUnitPrice NUMBER,
p_NewUnitPrice NUMBER,
p_OrigQuantity NUMBER,
p_NewQuantity NUMBER,
p_OrigDiscount NUMBER,
p_NewDiscount NUMBER,
p_OrigProductID NUMBER,
p_NewProductID NUMBER,
p__ROWS_RETURNED OUT NUMBER
)
AS
BEGIN
  SELECT NVL(COUNT(*), 0) INTO p__ROWS_RETURNED FROM ORDER_DETAILS
  WHERE  (ORDERID = p_OrderID AND PRODUCTID = p_ProductID)
  AND (UNITPRICE = p_OrigUnitPrice AND QUANTITY = p_OrigQuantity
  AND DISCOUNT = p_OrigDiscount AND PRODUCTID = p_OrigProductID);
  IF p__ROWS_RETURNED = 1 THEN
    UPDATE ORDER_DETAILS
    SET UNITPRICE = p_NewUnitPrice, 
       QUANTITY = p_NewQuantity,
      DISCOUNT = p_NewDiscount,
      PRODUCTID = p_NewProductID
    WHERE ORDERID = p_OrderID AND PRODUCTID = p_ProductID;
    COMMIT;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END ORDERS_SUBFORM_UPDATE;

/* ORDERS_UPDATE updates the Orders table for the given OrderID unless there is a concurrency violation.
*/
PROCEDURE ORDERS_UPDATE
(
p_OrderID NUMBER,
p_OrigCustomerID VARCHAR2,
p_NewCustomerID VARCHAR2,
p_OrigEmployeeID NUMBER,
p_NewEmployeeID NUMBER,
p_OrigOrderDate DATE,
p_NewOrderDate DATE,
p_OrigRequiredDate DATE,
p_NewRequiredDate DATE,
p_OrigShippedDate DATE,
p_NewShippedDate DATE,
p_OrigFreight NUMBER,
p_NewFreight NUMBER,
p_OrigShipName VARCHAR2,
p_NewShipName VARCHAR2,
p_OrigShipAddress VARCHAR2,
p_NewShipAddress VARCHAR2,
p_OrigShipCity VARCHAR2,
p_NewShipCity VARCHAR2,
p_OrigShipRegion VARCHAR2,
p_NewShipRegion VARCHAR2,
p_OrigShipPostalCode VARCHAR2,
p_NewShipPostalCode VARCHAR2,
p_OrigShipCountry VARCHAR2,
p_NewShipCountry VARCHAR2,
p_OrigShipVia NUMBER,
p_NewShipVia NUMBER,
p__ROWS_RETURNED OUT NUMBER
)
AS
BEGIN
  SELECT NVL(COUNT(*), 0)
  INTO p__ROWS_RETURNED
  FROM ORDERS
  WHERE (ORDERID = p_OrderID)
  AND ((p_OrigCustomerID IS NULL AND CUSTOMERID IS NULL)
  OR CUSTOMERID = p_OrigCustomerID
  AND (p_OrigEmployeeID IS NULL AND EMPLOYEEID IS NULL)
  OR EMPLOYEEID = p_OrigEmployeeID AND (p_OrigOrderDate IS NULL
  AND ORDERDATE IS NULL) OR ORDERDATE = p_OrigOrderDate
  AND (p_OrigRequiredDate IS NULL AND REQUIREDDATE IS NULL)
  OR REQUIREDDATE = p_OrigRequiredDate
  AND (p_OrigShippedDate IS NULL AND SHIPPEDDATE IS NULL)
  OR SHIPPEDDATE = p_OrigShippedDate
  AND (p_OrigFreight IS NULL AND FREIGHT IS NULL)
  OR FREIGHT = p_OrigFreight
  AND (p_OrigShipName IS NULL AND SHIPNAME IS NULL)
  OR SHIPNAME = p_OrigShipName
  AND (p_OrigShipAddress IS NULL AND SHIPADDRESS IS NULL)
  OR SHIPADDRESS = p_OrigShipAddress
  AND (p_OrigShipCity IS NULL AND SHIPCITY IS NULL)
  OR SHIPCITY = p_OrigShipCity
  AND (p_OrigShipRegion IS NULL AND SHIPREGION IS NULL)
  OR SHIPREGION = p_OrigShipRegion
  AND (p_OrigShipPostalCode IS NULL AND SHIPPOSTALCODE IS NULL)
  OR SHIPPOSTALCODE = p_OrigShipPostalCode
  AND (p_OrigShipCountry IS NULL AND SHIPCOUNTRY IS NULL)
  OR SHIPCOUNTRY = p_OrigShipCountry
  AND (p_OrigShipVia IS NULL AND SHIPVIA IS NULL)
  OR SHIPVIA = p_OrigShipVia);
  IF p__ROWS_RETURNED = 1 THEN
    UPDATE ORDERS
    SET
      CUSTOMERID = p_NewCustomerID,
      EMPLOYEEID = p_NewEmployeeID,
      ORDERDATE = p_NewOrderDate,
      REQUIREDDATE = p_NewRequiredDate,
      SHIPPEDDATE = p_NewShippedDate,
      FREIGHT = p_NewFreight,
      SHIPNAME = p_NewShipName,
      SHIPADDRESS = p_NewShipAddress,
      SHIPCITY = p_NewShipCity,
      SHIPREGION = p_NewShipRegion,
      SHIPPOSTALCODE = p_NewShipPostalCode,
      SHIPCOUNTRY = p_NewShipCountry, SHIPVIA = p_NewShipVia
    WHERE ORDERID = p_OrderID;
    COMMIT;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
END ORDERS_UPDATE;
END PKG_NORTHWIND;