All Powder Board and Ski Oracle 9 i
All Powder Board and Ski Oracle 9 i Workbook Chapter 6: Forms, Reports, and Applications Jerry Post Copyright © 2003 1
Form Types Ski Board Style Description Customer Category Grid Last Name First Name Phone Address City Sale Customer Main Salesperson Item. ID Description Price Quantity Value Main and Subform 2
Customer Main Form Record navigation Label Text box 3
Main Form Wizard Tools/Data block wizard Select table Select/transfer fields 4
Layout Wizard Clean up prompts Set column widths—default values are too wide 5
Form Design View Object navigator Toolbox to add controls Right click to add trigger events Right click to open properties Properties to control objects 6
Oracle Form Structure Form Data Block 1 Customer Canvas 1 Item 2 Database Canvas 2 Data Block 2 Item 1 Item 2 Order 7
Data Source Properties Data Source = Customer table Data block properties Column Name = Last. Name Control properties 8
Form Triggers Compile button Add the one line: Execute_query; Right click and select Smart triggers, New form instance 9
List of Values (LOV): Record Group Column name Values Edit/Create Static group 10
List of Values Select the return value 11
Initial Grid Form 12
Layout Wizard for Grids Number of rows to display in grid Spacing between rows 13
Add a List of Values (LOV) Add a list of values (LOV) for Category 14
LOV Role Display Product. Category Board Boots Clothes Electronic Glasses Ski. Board. Style Desc Category Downhill Ski Select 15
LOV Runtime Click Ctrl-L Chosen value is transferred to form Select the item 16
Main/Subform by Sale Begin with the Sale main form 17
Data Block Relationship Add the Sale. Item table Create a relationship to the Sale block 18
Initial Form Design Sale block Sale. Item block 19
Initial Sale Form LOV button for Customer. ID, code: Go_Item(‘Customer. ID’); List_Values; Need LOV box Multiply price by quantity Calculate subtotal 20
Subform Value Column To maintain a consistent format, copy and paste the Sale. Price column Set properties of Value column: Name: Value Enabled: No Justification: End Format Mask: $99, 990. 00 Calculation Mode: Formula: : Quantity. Sold*: Sale. Price 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 21
Subform Subtotal Column Properties: Name: Sub. Total Enabled: No Justification: Right Keyboard Navigable: No Data Type: Number Format Mask: $999, 990. 00 Calculation Mode: Summary Function: 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 Sale. Item data block: Query All Records: Yes 22
Sale Form: Subtotal and Total Click Sale data block Add text box to main form to display the subtotal value Properties: Name: Subtotal Enabled: No Justification: Right Data Type: Number Format Mask: $999, 990. 00 Calculation Mode: Formula: : Sale. Item. Subtotal Database Item: No Prompt: Subtotal Copy it to create a Total Due Properties: Name: Sale. Total Formula: : Sale. Subtotal + : Sales. Tax Prompt: Total Due 23
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 Sale. ID, Employee. ID, Sale. Date, Sale. Customer. ID, Ship. Address, Ship. City, Ship. State, Ship. ZIP, Sales. Tax, Payment. Method, Customer. Last. Name as c. Last. Name, Customer. First. Name as c. First. Name, Customer. Phone as c. Phone FROM Sale INNER JOIN Customer ON Sale. Customer. ID=Customer. ID) Properties for the Sale data block 24
Sale Form: Customer Name (2) Query Columns/Fields 25
Sale Form: Customer Name (3) On the Sale form, set the property for Sale. ID: Primary Key: Yes Then add textboxes or copy existing boxes for the new data. Properties: Name: c. First. Name Enabled: No Database Item: Yes Column Name: c. First. Name Query Only: Yes Insert Allowed: No Update Allowed: No Prompt: First Name 26
Sale Form: Edit Customer Add a button (btn. Edit. Customer) below the Customer. ID Code: WHEN-BUTTON-PRESSED : global. Customer. ID : = : Sale. Customer. ID; Call_Form('D: StudentsAll. PowderCustomer'); 27
Customer Form WHEN-NEW-FORM-INSTANCE trigger: DECLARE s. Where VARCHAR 2(200); BEGIN -- You should assign a null value to this in the startup form IF (: global. Customer. ID IS NOT NULL) THEN s. Where : = ' WHERE Customer. ID=' || : global. Customer. ID; set_block_property('Customer', DEFAULT_WHERE, s. Where); END IF; go_block('Customer'); execute_query; END; 28
Final Sale Form Design 29
Final Sale Form 30
Customer Sales Report: Query Select columns from the Customer, Sale, and Sale. Item tables Join tables by dragging a column to the next table 31
Query: Calculated Value Column SELECT ALL CUSTOMERID, CUSTOMER. LASTNAME, CUSTOMER. FIRSTNAME, CUSTOMER. PHONE, CUSTOMER. EMAIL, CUSTOMER. CITY, CUSTOMER. STATE, SALEID, SALEDATE, SALE. SHIPSTATE, SALE. PAYMENTMETHOD, SALEITEM. SKU, SALEITEM. QUANTITYSOLD, SALEITEM. SALEPRICE, SALEITEM. QUANTITYSOLD*SALEITEM. SALEPRICE AS VALUE FROM CUSTOMER, SALEITEM WHERE ((CUSTOMERID = SALE. CUSTOMERID) AND (SALEID = SALEITEM. SALEID)) 32
Report Groups Move Customer. ID first, followed by columns from the Customer table Click Level 1 Move Sale. ID, followed by columns from the Sale table 33
Report Display Columns 34
Select Totals 35
Initial Labels and Column Widths 36
Initial Report Customer break Sale. Item detail 37
Initial Report Design Customer header Sale. Item detail Sale footer Customer footer Report footer 38
Report Design Customer break Sale. Item detail 39
Report: Object Navigator Customer footer Record group for customer header Customer labels Customer fields Sale group within Customer 40
Final Design 41
Final Report (One Page) 42
Customer Totals Without Items SELECT Customer. ID, Customer. Last. Name, Customer. First. Name, Customer. Phone, Customer. EMail, Sale. ID, Sale. Date, Sale. Ship. City, Sale. Ship. State, Sum(Sale. Price*Quantity. Sold) AS Value FROM (Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID) INNER JOIN Sale. Item ON Sale. ID = Sale. Item. Sale. ID GROUP BY Customer. ID, Customer. Last. Name, Customer. First. Name, Customer. Phone, Customer. EMail, Sale. ID, Sale. Date, Sale. Ship. City, Sale. Ship. State ORDER BY Customer. Last. Name, Customer. First. Name; 43
Sales Total Design 44
Creating a Switchboard Form Add images with Edit/Import Add report links in navigator Open forms or reports Add command buttons 45
Open Oracle Reports from Forms declare report_id report_job_id Report_Object; VARCHAR 2(100); You really need Oracle 9 i/AS to link the reports server to the forms server begin -- Note: You must define the report file in the Reports node report_id : = find_report_object('Customer. Sales'); SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_EXECUTION_MODE, RUNTIME); SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_COMM_MODE, ASYNCHRONOUS); SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_DESTYPE, PREVIEW); SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_SERVER, ' '); report_job_id : = run_report_object(report_id); end; http: //otn. oracle. com/products/forms/pdf/277282. pdf 46
Creating Menus Add menu module Add menus Sub menu Add menu items Set item properties Write action code 47
Custom Menu Save and compile the menu first Place the new file name in the Menu Module property 48
HTML Help File Helpset: All. Powder. Help. hs Map: map. xml Index: index. xml TOC: toc. xml Search: search. idx Form Help menu: 1 Topic A 2 Topic B 3 Topic C 4 Topic D 5 Topic E 6 Topic F HTML Topic HTML Topic Web. Show_Document (‘http: //company. com: 8888/ohw/ help/? topic=Customer. html’) http: //otn. oracle. com/software/tech/java/help/htdocs/utilsoft. htm 49
HTML Help Files <TITLE>All Powder Board and Ski Shop</TITLE> <LINK rel="stylesheet" type="text/css" href="Styles. css"> </HEAD><BODY> <H 1>Introduction to the All Powder Board and Ski Shop</H 1> <TABLE><TR> <TD><IMG SRC="Board. Logo 1. gif" border="0"></TD> <TD>All Powder Board and Ski Shop sells and rents snowboards and skis for all levels of riders and skiers. </TD> </TR></TABLE> <H 2>The Board and Ski Shop</H 2> <UL><LI><A HREF="Customers. html">Customers</A></LI> <LI><A HREF="Sales. html">Sales </A></LI> </UL></BODY></HTML> Warning: You must use double quotes not single quotes or the links will fail. 50
Topics Map File <? xml version='1. 0' ? > <map version="1. 0"> <map. ID target="All. Powder_html" url="All. Powder. html" /> <map. ID target="Customers_html" url="Customers. html" /> <map. ID target="Sales_html" url="Sales. html" /> </map> 51
Table of Contents (TOC) file <? xml version='1. 0' ? > <toc version="1. 0"> <tocitem text="Introduction to All Powder Board and Ski Shop"> <tocitem target="All. Powder_html" text="The Board and Ski Shop" /> <tocitem text="Sales Options" target="Sales_html" /> </tocitem> <tocitem text="Customer Options"> <tocitem target="Customers_html" text="Adding New Customers" /> <tocitem target="Sales_html" text="Sales Options" /> </tocitem> </toc> 52
Index File <? xml version='1. 0' ? > <index version="1. 0"> <indexitem target="All. Powder" text="All Powder" /> <indexitem target="All. Powder" text="Management" /> <indexitem target="All. Powder" text="Start" /> <indexitem target="Customers" text="Client" /> <indexitem target="Customers" text="Customers" /> <indexitem target="Sales" text="Sales" /> <indexitem text="Introduction"> <indexentry target="All. Powder" text="The Company" /> <indexentry target="Customers" text="Customers" /> <indexentry target="Sales" text="Sales" /> </indexitem> 53
Search File Is a proprietary binary file that must be generated. Commercial systems can create the file. Or, install Oracle Java Help and use the help-4 -indexer Use a command line statement to create the binary index file set CLASSPATH=%CLASSPATH%; c: program filesohelphelp 4 indexer. jar java -mx 64 m oracle. help. tools. index. Indexer -l=en_US =e=8859_1 D: Oracleohwoc 4 jj 2 eehomeapplicationsohweappohwhelpsetsAll. Powder search. idx The classpath depends on where you installed the Java Help system The path on the java line is the folder holding your HTML files 54
Help. System File (1) <? xml version='1. 0' ? > <helpset version="1. 1"> <title>All Powder Board and Ski Shop</title> <maps> <mapref location="map. xml" /> </maps> <linkref location="link. xml"/> </links> 55
Help. System File (2) <view> <label>Contents</label> <type>oracle. help. navigator. toc. Navigator. TOCNavigator</type> <data engine="oracle. help. engine. XMLTOCEngine">toc. xml</data> </view> <label>Index</label> <type>oracle. help. navigator. keyword. Navigator. Keyword. Navigator</type> <title>All Powder Board and Ski Shop</title> <data engine="oracle. help. engine. XMLIndex. Engine">index. xml</data> </view> <label>Search</label> <title>All Powder Board and Ski Shop</title> <type>oracle. help. navigator. search. Navigator. Search. Navigator</type> <data engine="oracle. help. engine. Search. Engine">search. idx</data> </view> </helpset> 56
ohwconfig. xml <books combine. Books="true" use. Label. Info="true"> <help. Set location=“All. Powder/All. Powder. Help. hs" /> … other help sets </books> 57
Initial Help File 58
Context-Sensitive Help (sort of) Form property: Help Book Title Control property: Help Book Topic But these will probably not work with the Help. System and Oracle 9. 0. 2 Instead, create a custom menu with an entry for Help Use PL/SQL Code to open a specific topic: Web. Show_Document (‘http: //yourserver. com: 8888/ohw/help/? topic=Customers_html’); 59
- Slides: 59