Most Frequently Used SQLs for List of Values (LOVs)

  • Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 16, 2020
  • Reading time:7 mins read

Below are the list of SQLs that are most frequently used to get the list of values for business objects. Legislative Data Group: Select name from per_legislative_data_groups_vl Secured Persons List: SELECT DISTINCT PERSON_NUMBER FROM PER_PERSON_SECURED_LIST_V Secured Departments List: SELECT DISTINCT SUBSTR(DEPT.NAME,1,INSTR(DEPT.NAME,’-‘,1)-1) DEPCODE FROM PER_DEPARTMENT_SECURED_LIST_V SEC_DEPT, PER_DEPARTMENTS DEPT WHERE DEPT.ORGANIZATION_ID=SEC_DEPT.ORGANIZATION_ID Payroll Name: select distinct payroll_name from pay_all_payrolls_f where TRUNC(SYSDATE) BETWEEN effective_start_date…

Continue ReadingMost Frequently Used SQLs for List of Values (LOVs)

OTBI Direct Database Query feature removal and fix Obsolete

In one of the earlier posts Direct Database Query, we saw how to use the Direct Database Query feature of OTBI analytics. This feature has been removed from 19A, so if you have upgraded or planning to upgrade to 19A, remember that the reports created using this feature will not be supported post upgrade. Oracle has suggested moving these analytics…

Continue ReadingOTBI Direct Database Query feature removal and fix Obsolete

SQL Query to get Fast Formula and related info

  • Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 26, 2020
  • Reading time:5 mins read

Below are the SQL's to extract Fast Formula related data from underlying tables:Fast Formula Functions and their underlying Packages: SELECT f.BASE_FUNCTION_NAME ,f.description ,f.DEFINITION || '(' || listagg(fp.name || ' ' || decode(fp.class, 'I', 'IN', 'O', 'OUT') || ' ' || decode(fp.data_type, 'N', 'NUMBER', 'T', 'VARCHAR2', 'D', 'DATE'), ',') WITHIN GROUP (ORDER BY fp.sequence_number) || ') RETURN ' || decode(f.data_type,'N','NUMBER','T','VARCHAR2','D','DATE') AS…

Continue ReadingSQL Query to get Fast Formula and related info

Prevent Merging Columns in Excel Output using RTF template

  • Post category:BI Publisher
  • Post comments:2 Comments
  • Post last modified:June 12, 2020
  • Reading time:3 mins read

While creating a BI Publisher Report, we develop templates using RTF template as it provides multiple output formats like Interactive Mode, PDF, XLSX, XML, CSV etc which are not possible with any other templates like XLS, E-TEXT, etc. One problem most of the people face is when they try to download the excel output using RTF template, the excel output contains merged…

Continue ReadingPrevent Merging Columns in Excel Output using RTF template