BB_SHOP_SALES

BB_PROD_SALES

BB_BASKET

Please help for PL/SQL

Create a package named SHOP_PROCESS_PKG that contains all the program units created previously as follows.

The Reporting and Analysis Department has a database containing tables that hold summarized data to make report generation simpler and more efficient. They want to schedule some jobs to run nightly to update summary tables. Create two procedures to update the following tables (assuming that existing data in the tables is deleted before these procedures run):

PROCEDURES:

Create the PROD_SALES_SUM_SP procedure to update the BB_PROD_SALES table, which holds total sales dollars and total sales quantity by product ID, month and year. The order date should be used for the month and year information.

Create the SHOP_SALES_SUM_SP procedure to update the BB_SHOP_SALES table, which holds total dollar sales by shopper ID. The total should include only product amountsno shipping or tax amounts.

The BB_SHOP_SALES and BB_PROD_SALES tables have already been created. Use the DESCRIBE command to review their table structures.

FUNCTIONS:

A number of functions created in this unit assume that the basket amounts, including shipping, tax, and total, are already posted to the BB_BASKET table. However, the program units for updating these columns when a shopper checks out have not been developed yet. A procedure is needed to update the following columns in the BB_BASKET table when an order is completed: ORDERPLACED, SUBTOTAL, SHIPPING, TAX and TOTAL.

Construct three functions to perform the following tasks: calculating the subtotal by using the BB_BASKETITEM table based on basket ID as input, calculating shipping costs based on basket ID as input, and calculating the tax based on basket ID and subtotal as input. Use these functions in a procedure.

A value of 1 entered in the ORDERPLACED column indicates that the shopper has completed the order. The subtotal is determined by totaling the item lines of the BB_BASKETITEM table for the applicable basket number. The shipping cost is based on the number of items in the basket: 1 to 4 = $5, 5 to 9 = $8, and more than 10 = $11.

The tax is based on the rate applied by referencing the SHIPSTATE column of the BB_BASKET table with the STATE column of the BB_TAX table. This rate should be multiplied by the basket subtotal, which should be an INPUT parameter to the tax calculation because the subtotal is being calculated in this same procedure. The total tallies all these amounts.

The only INPUT parameter for the procedure is a basket ID. The procedure needs to update the correct row in the BB_BASKET table with all these amounts. To test, first set all column values to NULL for basket 3 with the following UPDATE statement. Then call the procedure for basket 3 and check the INSERT results.

Modify the BASK_CALC_PP procedure so that the subtotal, tax, shipping and total amounts are placed in packaged variables rather than the database so that the application can display a purchase confirmation page for shoppers. Test this procedure with basket 3.

The lead programmer has requested that all package program units be in alphabetical order, to make them easy to locate. Use forward declarations, if needed, to allow alphabetizing program units.

COLUMN-NAME Et NULLABLE DATA-DEFAULT COLUMN-ID | :f COMMENTS 1 IDSHOPPER 2 TOTAL DATA_TYPE NUMBER (4,0) Yes NUMBER(6,2) Yes inull) (null) l null) 2 (null) COLUMN-NAME Et NULLABLE DATA-DEFAULT COLUMN-ID | :f COMMENTS 1 IDSHOPPER 2 TOTAL DATA_TYPE NUMBER (4,0) Yes NUMBER(6,2) Yes inull) (null) l null) 2 (null)