“REP-0118 unable to create a temporary file”

“REP-0118 unable to create a temporary file” error pops up when we install Oracle Report and open it for first time.

Cause – Issue with Temp file path and you don’t have access to this folder.

Fix – Navigate to Registry entry (Run -> Regedit -> and search (Ctrl+f) with “REPORTS_TMP”. Modify the path to your path or get the write access for this folder.

Advertisements

Add PQH Roles and add users to it

Complete procedure to add PQH Roles and add users to it

PROCEDURE mpl_cse_create_roles(
errbuf OUT NOCOPY  VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2)
IS
/*************************************************************************************
**  Name       : MPL_CSE_CREATE_ROLES
**  DESCRIPTION: This procedure used to define roles for AME
**************************************************************************************/
l_num_role_id      pqh_roles.role_id%TYPE;
l_num_object_version_number   pqh_roles.object_version_number%type;
l_num_person_extra_info_id   per_people_extra_info.person_extra_info_id%type;
l_num_ovn_pie      per_people_extra_info.object_version_number%type;
l_num_person_id      per_people_extra_info.person_id%type;
l_num_loop_count        NUMBER;
l_var_user              VARCHAR2(1000);
l_var_err               VARCHAR2(1000);
l_num_addusrrole_id     NUMBER;
l_var_process           VARCHAR2(10);
CURSOR cur_role
IS
SELECT *
FROM mpl_cse_role_conv_stg
WHERE process_flag IS NULL;
BEGIN
l_num_object_version_number := NULL;
l_num_role_id               := NULL;
l_num_person_id             := NULL;
l_num_person_extra_info_id  := NULL;
l_num_ovn_pie               := NULL;
FOR cur_role_rec IN cur_role
LOOP
BEGIN
pqh_roles_api.create_role
( p_validate          => FALSE ,
p_role_id           => l_num_role_id ,
p_role_name         => cur_role_rec.role_name ,
p_business_group_id   => 0 ,
p_role_type_cd    => ‘EMP’ ,
p_enable_flag    => ‘Y’ ,
p_object_version_number  => l_num_object_version_number ,
p_effective_date    => SYSDATE ,
p_information_category  => cur_role_rec.location
);
COMMIT;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
l_var_err     := SQLERRM;
l_var_process :=’N’;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = ‘Role Could not be  created ‘
|| l_var_err
WHERE role_name = cur_role_rec.role_name;
FND_FILE.PUT_LINE(FND_FILE.LOG,’Role Could not be  created’);
END;
/*adding users to role*******/
/*checking number of user in a role*/
BEGIN
SELECT DECODE(cur_role_rec.member_1, NULL, 0, 1) + DECODE(cur_role_rec.member_2, NULL, 0, 1) + DECODE(cur_role_rec.member_3, NULL, 0, 1)
INTO l_num_loop_count
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_num_loop_count := 0;
l_var_process    :=’N’;
END;
IF cur_role_rec.member_1 IS NOT NULL OR cur_role_rec.member_2 IS NOT NULL OR cur_role_rec.member_3 IS NOT NULL THEN
FOR i IN 1..l_num_loop_count
LOOP
l_var_user          := NULL;
l_num_person_id     := NULL;
l_num_addusrrole_id := NULL;
l_var_process       := NULL;
IF i                 =1 THEN
l_var_user        := cur_role_rec.member_1_emp_num;
ELSIF i              =2 THEN
l_var_user        := cur_role_rec.member_2_emp_num;
ELSIF i              =3 THEN
l_var_user        := cur_role_rec.member_3_emp_num;
END IF;
BEGIN
SELECT person_id
INTO l_num_person_id
FROM per_all_people_f
WHERE employee_number =l_var_user
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_var_process :=’N’;
l_var_user    := NULL;
dbms_output.put_line(‘person_id:’||SQLERRM);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ User could not be added to Role. Employee Number: ‘
||cur_role_rec.member_1_emp_num
WHERE role_name = cur_role_rec.role_name;
END;
BEGIN
SELECT role_id
INTO l_num_addusrrole_id
FROM pqh_roles
WHERE role_name = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘role_id:’||SQLERRM);
l_var_process       :=’N’;
l_num_addusrrole_id := NULL;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ Role not valid’
WHERE role_name = cur_role_rec.role_name;
END;
IF NVL(l_var_process,’Y’) !=’N’ THEN
BEGIN
hr_person_extra_info_api.create_person_extra_info
( p_validate       => FALSE ,
p_person_id      => l_num_person_id ,
p_information_type     => ‘PQH_ROLE_USERS’ ,
p_pei_information_category   => ‘PQH_ROLE_USERS’ ,
p_pei_information3     => l_num_addusrrole_id ,
p_pei_information4     => ‘N’ ,
p_pei_information5     => ‘Y’ ,
p_pei_information9     => ‘N’ ,
p_person_extra_info_id    =>l_num_person_extra_info_id ,
p_object_version_number   =>l_num_ovn_pie
);
IF l_num_person_extra_info_id IS NOT NULL THEN
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
ELSE
DBMS_OUTPUT.PUT_LINE(‘USER NOT LOADED :’||l_num_person_id);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘Seeded api ERROR hr_person_extra_info_api.create_person_extra_info ‘
WHERE role_name = cur_role_rec.role_name;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,’user Could not be  be added to role’);
dbms_output.put_line(‘user Could not be  be added to role’||SQLERRM);
l_var_err := SUBSTR(SQLERRM,1000);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘user could not be adde for perosn_id’
||l_num_person_id
WHERE role_name = cur_role_rec.role_name;
END;
END IF;
END LOOP;
END IF ;
COMMIT;
END LOOP;
END mpl_cse_create_roles;

Basics of Oracle BI Discoverer Reports

Oracle BI Discoverer comprises the following components:

  • Oracle Business Intelligence Discoverer Plus This component runs over the internet or intranet and enables you to:
    • create new worksheets and graphs to get the data you want
    • analyze the data
    • share worksheets and graphs with other people
  • Oracle Business Intelligence Discoverer Viewer

This Oracle BI Discoverer component runs over the internet or intranet inside a web browser. Use Discoverer Viewer to analyze the data in worksheets created in Discoverer Plus, and Discoverer Desktop. You can personalize worksheets (for example, by repositioning items) and save your changes. For more information about Discoverer Viewer, see the Oracle Fusion Middleware User’s Guide for Oracle Business Intelligence Discoverer Viewer

  • Oracle Business Intelligence Discoverer Portlet Provider and Discoverer portlets:

These Oracle BI Discoverer components run over the internet or intranet inside a web browser. Use Discoverer Portlet Provider to include Discoverer worksheets as areas (or portlets) within existing Oracle Portal pages. Discoverer Portlet Provider enables you to publish the following types of Discoverer portlet:

  • a List of Worksheets portlet
  • a Worksheet portlet
  • a Gauges portlet

You can personalize worksheets (for example, by adding stoplight formatting) and save your changes.

  • Oracle BI Discoverer Desktop

This Oracle BI Discoverer component is a Windows-only application that enables you to build new worksheets to analyze data from relational data sources. The worksheets you create in Discoverer Desktop can be used in Discoverer Plus, Discoverer Viewer, and Discoverer portals.

  • Oracle BI Discoverer Administrator

This Oracle BI Discoverer component is a Windows-only application used by the Discoverer manager to create and maintain a business oriented view of relational data. Discoverer Administrator provides wizards and dialogs to:

  • control access to data
  • manage summary data
  • administer batch scheduling
  • Oracle BI Discoverer End User Layer (EUL)

The Discoverer End User Layer component is a repository for storing and retrieving definitions of objects used when querying relational data sources.

  • Oracle BI Discoverer Catalog

The Discoverer Catalog component is a repository for storing and retrieving definitions of user objects when querying multidimensional data sources.

Simple and easy steps to Oracle Business Intelligence Discoverer

Following are the steps to develop report in Oracle discoverer. Query is developed in Discoverer Administrator and Layout is developed in Discoverer Desktop or Discoverer Plus. Example is of Purchase Order report.

Discoverer Administrator

  1. Login into Discoverer Administrator.

1

Create a New Business Area

1

 

1

 

Select the table or View on which you would like to place you Report

1

To provide access for this report to any User or Responsibility, you need to set its Security. Security can been set for the entire Business Area

Tools–>Security

1

 

 

2) You can also use a pre defined Business Area. Select Business Area.  For e.g. The Common Reports

 

2

3) Create New Custom Folder and enter SQL Query and give name to that folder as per the standards. If you want to develop using View then select New Folder from Database.

3

4) Validate the SQL. Click OK.

4

 

Here are the screen shots for creating the workbooks in the Discoverer Desktop:-

1) Login into Discoverer Desktop.

6

2) Create New Workbook and select display results(Table, Crosstab, Page-Detail Table and Page-Detail Crosstab) as per requirement.

8

3) Select the folder or required fields of folder for which you are creating Workbook.

9

4) Make the layout as per requirement by rearranging the fields and adding fields to Page Items. Hide duplicate rows by clicking the check box (if necessary).

10

5) Finally the report is developed and it looks like this

 

11

We need to set the security for the Desktop also.

File–>Manage Workspace –> Sharing

Permission needs to be set for each Work Book. Here responsibilities are  also treated as a User.

Discoverer Plus

Step-by-step to create a report in Discoverer Plus

1) log into discoverer Plus using the URL

2) Click on ‘Create a new workbook’ and select only ‘Title’; leave others unchecked. Click Next.

12

3) select your Common Report Folder. And select the columns that you would like to display in the Report

13

4) The report would look like the following

14

5) Save your report by going to File -> Save. Save frequently to avoid losing changes.

You can add parameters to the report using the add Parameter option on the tool bar. Ypu can dynamically select the columns that you would like to display on the report by selecting the columns from the Selected Items in below the page.

15

 

 
Discoverer Viewer

Log in using the URL.

It is similar to the Plus. Only difference is that the user will not be able to chose his own parameters. The parameters will need to be defined by the developer at the Administrator. Depending n the client requirement the access of the Viewer or the Plus would be given

Adding Users to PQH_ROLES

For adding user to PQH_ROLES

Table : per_people_extra_info  — Role ID would be stored in an attribute as per setup

begin
hr_person_extra_info_api.create_person_extra_info
(p_validate                   => false
,p_person_id                  => 25915
,p_information_type           => ‘PQH_ROLE_USERS’
,p_pei_information_category   => ‘PQH_ROLE_USERS’
,p_pei_information3           => 4001–l_num_role_id
,p_pei_information4           => ‘N’
,p_pei_information5           => ‘Y’
,p_pei_information9           => ‘N’
,p_person_extra_info_id       =>l_num_person_extra_info_id
,p_object_version_number      =>l_num_ovn_pie
);

Create Position Control roles(pqh_roles)

The following script can be used for creating Position Control roles used for AME.

Table : PQH_ROLES

Navigation : Global HRMS Manger –> Ctrl + L –> Maintain Roles

 

Capture

 

DECLARE
L_NUM_ROLE_ID  pqh_roles.role_id%TYPE;
l_num_object_version_number  pqh_roles.object_version_number%TYPE;
BEGIN
pqh_roles_api.create_role
(
P_VALIDATE                      => FALSE
,P_ROLE_ID                       => L_NUM_ROLE_ID
,P_ROLE_NAME                    => ‘DEMO_ROLE’
,P_ROLE_TYPE_CD                  =>’EMP’
,P_ENABLE_FLAG                   => ‘Y’
,P_OBJECT_VERSION_NUMBER         =>L_NUM_OBJECT_VERSION_NUMBER
— ,p_business_group_id              in  number    default null
,P_EFFECTIVE_DATE                =>SYSDATE
, P_INFORMATION_CATEGORY        =>NULL
,p_information4=> ‘IN-BAN19’
);
COMMIT;
END;

/***********************END********************************/