Editing the Employees Report

Create a Function

First, create a function to calculate employees' salaries.

To create a function:

1. Click the Application link on the Developer toolbar (at the bottom of the page).

   The Application home page appears.

2. Click the Home breadcrumb link in the title bar.

   
   Description of the illustration home_brdcrmb.gif
   
   

3. Click SQL Workshop and then SQL Commands.

   SQL Commands provides a window where you can run PL/SQL against the database.

4. In the SQL editor, enter the following script:

   CREATE OR REPLACE FUNCTION calc_remuneration(
       salary IN number, commission_pct IN number)
   RETURN NUMBER IS
   BEGIN
       RETURN ((salary*12) + (salary * 12 * nvl(commission_pct,0)));
   END;
   
   

5. Click Run.

   The Results section displays this message:

   Function created.
   

Add a Column

Next, add a column to display the results of the employees' salary calculations.

To add a column:

1. Navigate to the Page Definition for page 3:

   1. On the SQL Commands page, click the Home breadcrumb link.

   2. On the Workspace home page, click Application Builder.

   3. Select the AnyCo Corp application.

   4. Click 3 - Employees.

      The Page Definition for page 3, Employees, appears.

2. Under Regions, click the Employees link (next to Report).

   
   Description of the illustration reg_emplrpt.gif
   
   

   The Region Definition appears.

3. Scroll down to Source, and replace the existing code with the following:

   SELECT "EMPLOYEE_ID", 
       "FIRST_NAME",
       "LAST_NAME",
       "HIRE_DATE",
       "SALARY",
       "COMMISSION_PCT",
       calc_remuneration(salary, commission_pct) REMUNERATION
   FROM "#OWNER#"."OEHR_EMPLOYEES"
   

4. Click Apply Changes.

Previewing the Page

To preview the page, click the Run Page icon in the upper right corner. Notice the addition of the Remuneration column.

Changing the Column Format

Next, change the format of the columns that contain numeric values.

To change the column format:

1. Click Edit Page 3 on the Developer toolbar (at the bottom of the page).

   The Page Definition appears.

2. Under Regions, click Report.

   The Report Attributes page appears.

3. Locate the Column Attributes section.

   Next, change the column and heading alignment for the appropriate columns.

4. For Column Alignment, select right for SALARY, COMMISSION_PCT, and REMUNERATION.

5. For Heading Alignment, select center for SALARY, COMMISSION_PCT, and REMUNERATION.

   Next, edit the format of the value in the column.

6. To edit the format of SALARY:

   1. Under Column Attributes, click the Edit icon next to SALARY.

      
      Description of the illustration edit_icon_col.gif
      
      

      The Column Attributes page appears.

   2. For Number/Date Format, select $5,234.10 from the list.

      Notice that the appropriate format mask appears in the field.

   3. Click Apply Changes.

7. Repeat the previous step for REMUNERATION.

8. Click the Run Page icon in the upper right corner to preview the page.

   Notice that the numbers in the Salary and Remuneration columns now appear with the dollar sign, include commas and decimals, and are correctly aligned.

   
   Description of the illustration bldap_emprptnum2.gif