Editing the Employees Report

In this exercise, you create a new column entitled Remuneration to display employee salaries in the Employees report.

Topics:

Create a Function
Add a Column
Previewing the Page
Changing the Column Format

Create a Function

First, create a function to calculate employees' salaries.

To create a function:

Click the Home link on the Developer toolbar.
Click SQL Workshop and then SQL Commands.

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

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;

When copying and pasting this SQL, make sure there is not an empty line before the first statement, CREATE OR REPLACE FUNCTION.

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:

Navigate to the Page Definition for page 3:
1. Click the Application Builder tab.
2. Select the AnyCo Corp application.
3. Click 3 - Employees.
  
  The Page Definition for page 3 appears.
Under Regions, locate Employees.

Description of the illustration bldap_emprjn.gif
Right-click Employees and select Edit.

The Region Definition appears.

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"

Click Apply Changes.
Click Apply Changes to confirm.

Previewing the Page

To preview the page, click the Run Page icon in the upper right corner. Notice the Remuneration column does not appear. You must add the column by using the Select Column action.

Description of the illustration bldap_emprpt.gif

To display the Remuneration column:

Click the Actions menu.
Select the Select Columns action.
Move the Remuneration column from the Do Not Display box to the Display in Report box.

Description of the illustration bldap_addrem.gif
Click Apply.

The Employees Report is displayed and includes the Remuneration column.

Description of the illustration bldap_empfrmrem.gif

Changing the Column Format

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

To change the column format:

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

The Page Definition appears.
Under Regions, right-click Employees and select the Edit Report Attributes.

The Report Attributes page appears.
Locate the Column Attributes section.

Next, change the format of the value in the Salary and Remuneration columns.
To edit the Salary format:
1. Click the Edit icon next to SALARY.
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.
Repeat the previous step for REMUNERATION.
Click the Run Page 3 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 and include commas and decimals.

Description of the illustration bldap_emprptnum2.gif