Chapter 6: Forms, Reports and Applications All Powder Board and Ski

1. Designing & Building Business Applications Oracle 9i Jerry Post Chapter 6: Forms, Reports and Applications All Powder Board and Ski

2. Form Types Ski Board Style Customer Grid Last Name First Name Phone Address City Sale Customer Salesperson Main Main and Subform

3. Customer Main Form Record navigation Text box Label

4. Action Action Start the form builder Select Tools/Data Block Wizard Choose the Customer table Select all columns Stick with default options Run the Layout Wizard Choose all columns Clean up the prompts Enter smaller values for the widths Select the option to show the scrollbar Save the form design Run program: Start OC4J Instance Run the form to test it Click the Execute Query button

5. Main Form Wizard Tools/Data block wizard Select table Select/transfer fields

6. Layout Wizard Clean up prompts Set column widths—default values are too wide

7. Form Design View Toolbox to add controls Object navigator Right click to add trigger events Right click to open properties Properties to control objects

8. Oracle Form Structure Form Data Block 1 Customer Canvas 1 Item 1 Item 2 Database Canvas 2 Data Block 2 Order Item 1 Item 2

9. Data Source Properties Data Source = Customer table Column Name = LastName Data block properties Control properties

10. Action Action Right click the Trigger option for the Customer form Select Smart Trigger Choose WHEN-NEW-FORM-INSTANCE Enter code line: Execute_query; Close and save everything Run the form

11. Form Triggers Compile button Add the one line: Execute_query; Right click and select Smart triggers, New form instance

12. Action Action Right click the record group and create a new one Select a static group and add the three values (Female, Male, and Unidentified) Save and rename the group Choose Tools/LOV Wizard Select the new (existing) record group Choose the Gender column Click the look up button and select Gender as the return value column In the Gender box, add the WHEN-NEW-ITEM-INSTANCE trigger with the code List_Values; Save everything and run the form

13. List of Values (LOV): Record Group Column name Values Edit/Create Static group

14. List of Values Select the return value

15. Initial Grid Form

16. Action Action Start a new form with Tools/Data Block Wizard Base it on the table: SkiBoardStyle Select all three columns Choose defaults and run the Layout Wizard Choose all three columns again Set the column widths (144, 216, 72) Choose Tabular (not Form) Enter a frame title, number of records (10), and space between records (2) Save the form as SkiBoardStyle.fmb Run it

17. Layout Wizard for Grids Number of rows to display in grid Spacing between rows

18. Action Action Start the Tools/LOV Wizard In a new record group, build an SQL query using the ProductCategory table Select both columns, close the designer Check the SQL syntax Put both columns into the LOV and set their widths (72 and 144) Click the return item column in the Category row and click the Return Item button Select the SkiBoardStyle.Category column Finish the wizard by accepting the default values Save the form and run it

19. Add a List of Values (LOV) Add a list of values (LOV) for Category

20. LOV Role Display ProductCategory Board Boots Clothes Electronic Glasses Ski SkiBoardStyle Select Style Desc Category Downhill Ski

21. LOV Runtime Click Ctrl-L Chosen value is transferred to form Select the item

22. Main/Subform by Sale Begin with the Sale main form

23. Action Action Create the Sale form with the wizard Set property ORDER BY to SaleDate Format the SalesTax to $990.00 Rearrange the items to the top of the form Use the Data Block Wizard to add the SaleItem table with all of its columns Create the relationship to Sale In the Layout Wizard, choose tabular and do not include the SaleID column Choose 5 rows and display the scroll bar Save and run the form

24. Data Block Relationship Create a relationship to the Sale block Add the SaleItem table

25. Initial Form Design Sale block SaleItem block

26. Initial Sale Form LOV button for CustomerID, code: Go_Item(‘CustomerID’); List_Values; Need LOV box Multiply price by quantity Calculate subtotal

27. Action Action Use the LOV Wizard to create lists for the employee, customer, payment method, and SKU The SKU will use columns from the Inventory and Item model tables Add a button for the CustomerID that opens the LOV box

28. Subform Value Column To maintain a consistent format, copy and paste the SalePrice column Set properties of Value column: Name: Value Enabled: No Justification: End Format Mask: $99,990.00 Calculation Mode: Formula Formula: :QuantitySold*:SalePrice Database Item: No Column Name: (blank/delete) Query Allowed: No Insert Allowed: No Update Allowed: No Prompt: Value You must include the leading colon in variable names

29. Action Action Copy and paste the SalePrice column in the subform to create a Value column Assign properties so that it multiplies SalePrice by QuantitySold Copy and paste this new column to create a subtotal column Assign properties to make it a Summary calculation for Sum of the Value field Add subtotal and total fields to the Sale data block to display the subtotal and add the sales tax

30. Subform Subtotal Column Properties: Name: SubTotal Enabled: No Justification: Right Keyboard Navigable: No Data Type: Number Format Mask: $999,990.00 Calculation Mode: Summary Summary Function: Sum Summarized Block: SALEITEM Summarized Item: VALUE Database Item: No Height: 1 Width: 5 Visible: No Prompt: Subtotal Very important! You must also set one property for the SaleItem data block: Query All Records: Yes

31. Action Action Edit the Sale data block properties and create a query as the data source to load the customer name and phone Add textboxes to the Sale form to display the name and phone Set the SaleID property to Primary Key Set properties of the customer items so the database cannot change them

32. Sale Form: Subtotal and Total Click Sale data block Add text box to main form to display the subtotal value Properties: Copy it to create a Total Due Properties: Name: Subtotal Enabled: No Justification: Right Data Type: Number Format Mask: $999,990.00 Calculation Mode: Formula Formula: :SaleItem.Subtotal Database Item: No Prompt: Subtotal Name: SaleTotal Formula: :Sale.Subtotal + :Sale.SalesTax Prompt: Total Due

33. Sale Form: Customer Name (1) Sale Data Block Properties: DML Data Target Type: Table DML Data Target Name: Sale Query Data Source Type: Table Query Data Source Name: (SELECT SaleID, EmployeeID, SaleDate, Sale.CustomerID, ShipAddress, ShipCity, ShipState, ShipZIP, SalesTax, PaymentMethod, Customer.LastName as cLastName, Customer.FirstName as cFirstName, Customer.Phone as cPhone FROM Sale INNER JOIN Customer ON Sale.CustomerID=Customer.CustomerID) Properties for the Sale data block

34. Sale Form: Customer Name (2) Query Columns/Fields

35. Sale Form: Customer Name (3) On the Sale form, set the property for SaleID: Primary Key: Yes Then add textboxes or copy existing boxes for the new data. Properties: Name: cFirstName Enabled: No Database Item: Yes Column Name: cFirstName Query Only: Yes Insert Allowed: No Update Allowed: No Prompt: First Name

36. Action Action Add a button to the Sale form to edit customer data Add the two lines of code Add more code to the Customer startup trigger Save, compile, and test all forms

37. Sale Form: Edit Customer Add a button (btnEditCustomer) below the CustomerID Code: WHEN-BUTTON-PRESSED :global.CustomerID := :Sale.CustomerID; Call_Form('D:\Students\AllPowder\Customer');

38. Customer Form WHEN-NEW-FORM-INSTANCE trigger: DECLARE sWhere VARCHAR2(200); BEGIN -- You should assign a null value to this in the startup form IF (:global.CustomerID IS NOT NULL) THEN sWhere := ' WHERE CustomerID=' || :global.CustomerID; set_block_property('Customer', DEFAULT_WHERE, sWhere); END IF; go_block('Customer'); execute_query; END;

39. Final Sale Form Design

40. Final Sale Form

41. Action Action Create a new report with the wizard Set the title and choose Group Above Build the SQL query using the Customer, Sale, and SaleItem tables Add the column Value AS QuantitySold*SalePrice to the SQL Set the group fields so Customer data is at Level 1 and Sale data is at Level 2 Display all fields For totals, compute Sum(Value) Choose a template Test the report and save it

42. Customer Sales Report: Query Select columns from the Customer, Sale, and SaleItem tables Join tables by dragging a column to the next table

43. Query: Calculated Value Column SELECT ALL CUSTOMER.CUSTOMERID, CUSTOMER.LASTNAME, CUSTOMER.FIRSTNAME, CUSTOMER.PHONE, CUSTOMER.EMAIL, CUSTOMER.CITY, CUSTOMER.STATE, SALE.SALEID, SALE.SALEDATE, SALE.SHIPSTATE, SALE.PAYMENTMETHOD, SALEITEM.SKU, SALEITEM.QUANTITYSOLD, SALEITEM.SALEPRICE, SALEITEM.QUANTITYSOLD*SALEITEM.SALEPRICE AS VALUE FROM CUSTOMER, SALE, SALEITEM WHERE ((CUSTOMER.CUSTOMERID = SALE.CUSTOMERID) AND (SALE.SALEID = SALEITEM.SALEID))

44. Report Groups Move CustomerID first, followed by columns from the Customer table Click Level 1 Move SaleID, followed by columns from the Sale table

45. Report Display Columns

46. Select Totals

47. Initial Labels and Column Widths

48. Initial Report Customer break Sale break SaleItem detail

49. Initial Report Design Customer header Sale header SaleItem detail Sale footer Customer footer Report footer

50. Report Design Customer break Sale break SaleItem detail