Most Frequently Used SQLs for List of Values (LOVs)

  • Post published:March 4, 2019
  • Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 16, 2020
Most Frequently Used SQLs for List of Values (LOVs)
Most Frequently Used SQLs for List of Values (LOVs)

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 Reading Most Frequently Used SQLs for List of Values (LOVs)

OTBI Direct Database Query feature removal and fix – Obsolete

OTBI Direct Database Query feature removal and fix – Obsolete
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 Reading OTBI Direct Database Query feature removal and fix – Obsolete

SQL Query to get Fast Formula and related info

  • Post published:March 4, 2019
  • Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 26, 2020
SQL Query to get Fast Formula and related info
SQL Query to get Fast Formula and related info

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 Reading SQL Query to get Fast Formula and related info

Prevent Merging Columns in Excel Output using RTF template

  • Post published:March 4, 2019
  • Post category:BI Publisher
  • Post comments:1 Comment
  • Post last modified:June 12, 2020
Prevent Merging Columns in Excel Output using RTF template
Prevent Merging Columns in Excel Output using RTF template

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 Reading Prevent Merging Columns in Excel Output using RTF template