Create insights Tables
In order to use Reports Wand insights you need to create custom database tables in your Oracle E-Business Suite Database.
Execute the following SQL script to create the required tables:
You can modify the script to change the ownership of the table. If you choose to do so, ensure the APPS database user has SELECT, UPDATE and DELETE privileges on the table and create the required synonym to allow the APPS user to access the table without having to fully qualify the table name using the table owner.
CREATE TABLE "APPS"."XXE4A_REPORT_DEFINITIONS"
(
"REPORT_DEFINITION_ID" NUMBER(15,0) NOT NULL ENABLE,
"CONC_PROGRAM_APPLICATION_ID" NUMBER(15,0) NOT NULL ENABLE,
"CONCURRENT_PROGRAM_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"TYPE" VARCHAR2(8 BYTE) NOT NULL ENABLE,
"FEATURED_FLAG" VARCHAR2(1 BYTE) NOT NULL ENABLE,
CONSTRAINT "XXE4A_REPORT_DEFN_CK_1" CHECK (TYPE IN ('SEEDED', 'ADVANCED')) ENABLE,
CONSTRAINT "XXE4A_REPORT_DEFN_PK" PRIMARY KEY ("REPORT_DEFINITION_ID") ENABLE,
CONSTRAINT "XXE4A_REPORT_DEFN_UK1" UNIQUE ("CONC_PROGRAM_APPLICATION_ID", "CONCURRENT_PROGRAM_NAME") ENABLE
);
CREATE TABLE "APPS"."XXE4A_REPORT_DEFINITIONS_TL"
(
"REPORT_DEFINITION_ID" NUMBER(15,0) NOT NULL ENABLE,
"LANGUAGE" VARCHAR2(30 BYTE) DEFAULT userenv('LANG') NOT NULL ENABLE,
"BUSINESS_VALUE_DEFINITION" CLOB,
"BUSINESS_AREA_NAME" VARCHAR2(240 BYTE) NOT NULL ENABLE,
"APPLICATION_NAME" VARCHAR2(240 BYTE) NOT NULL ENABLE,
"SEARCH_TAGS" VARCHAR2(240 BYTE),
"SUBJECT_AREA" VARCHAR2(240 BYTE) NOT NULL ENABLE,
CONSTRAINT "XXE4A_REPORT_DEFN_TL_PK" PRIMARY KEY ("REPORT_DEFINITION_ID", "LANGUAGE") ENABLE,
CONSTRAINT "XXE4A_REPORT_DEFN_TL_FK1" FOREIGN KEY ("REPORT_DEFINITION_ID") REFERENCES "APPS"."XXE4A_REPORT_DEFINITIONS" ("REPORT_DEFINITION_ID") ENABLE
);
CREATE TABLE "APPS"."XXE4A_REPORT_DEFN_COLUMNS"
(
"COLUMN_ID" NUMBER(15,0),
"REPORT_DEFINITION_ID" NUMBER(15,0) NOT NULL ENABLE,
"COLUMN_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"SEQUENCE" NUMBER(4,0) NOT NULL ENABLE,
CONSTRAINT "XXE4A_REPORT_DEFN_COLUMNS_PK" PRIMARY KEY ("COLUMN_ID") ENABLE,
CONSTRAINT "XXE4A_REPORT_DEFN_COLUMNS_FK1" FOREIGN KEY ("REPORT_DEFINITION_ID") REFERENCES "APPS"."XXE4A_REPORT_DEFINITIONS" ("REPORT_DEFINITION_ID") ENABLE
);
CREATE TABLE "APPS"."XXE4A_REPORT_DEFN_COLUMNS_TL"
(
"COLUMN_ID" NUMBER(15,0),
"LANGUAGE" VARCHAR2(30 BYTE) DEFAULT userenv('LANG'),
"COLUMN_LABEL" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(240 BYTE),
CONSTRAINT "XXE4A_REPORT_DEFN_COLS_TL_PK" PRIMARY KEY ("COLUMN_ID", "LANGUAGE") ENABLE,
CONSTRAINT "XXE4A_REPORT_DEFN_COLS_TL_FK1" FOREIGN KEY ("COLUMN_ID") REFERENCES "APPS"."XXE4A_REPORT_DEFN_COLUMNS" ("COLUMN_ID") ENABLE
);
/
If you wish to use date tracked report definitions from the Oracle Human Resources module you will need to create the Report Wand Database Package containing the following procedure:
PROCEDURE XXE4A_HR_INITIALIZE (v_effective_date DATE)
AS
BEGIN
hr_integration_utils.add_or_update_session(nvl(v_effective_date,sysdate));
END XXE4A_HR_INITIALIZE;