Normally when we burst the BI Reports we either do the html bursting so that the content will appear in the email body or burst with an attachment so that the end user can open the pdf and view it. However if there is a requirement to combine both of them, then we can use the below bursting SQL: select…
Do you know the most frequently used Date, Number and String functions in SQL query? Read on to know more details
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…
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…
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…
One of the missing piece in BI Publisher earlier was the password protection for Excel 2007 Output and it has been introduced in 18c update. Earlier password protection was possible for PDF output only and there have been a number of bugs and enhancements pending around the feasibility for password protection for Excel Output. And finally Oracle has worked on…
In this article we will look into getting the Country, State, County, City and Zipcode information from backend tables. These queries will be handy when there is a requirement to get the Country Name, State Names etc apart from the Country Code and State Code which are present in most of the address tables. Countries:SELECT TERRITORY_CODE ,TERRITORY_SHORT_NAME ,CURRENCY_CODE ,DESCRIPTIONFROM FND_TERRITORIES_VLWHERE…
For most of the BI Reports, we have the input parameter as "AsOfDate" alias sysdate or current date... Sometimes we also have a date range "From Date" and "To Date" to fetch transactions between those dates. And these dates need to have a default value when we open the report/schedule the report. If the scheduled report includes date parameters, when…
Most of the customers want to know the list of users who logged in to Fusion Applications and want to encourage other employees too to use the Fusion system. For this, the customer needs to generate a report with the list of users who have logged in within a period of time like last 30days or on specific date or…
Normally when we load the data using HCM Data Loader aka HDL, the File Delete Option would be defaulted to 'Y' and the input zip file would be deleted post processing of that file. However out of my enthusiasm, I searched for options to get that HDL file from staging tables and finally figured out a way to get it…
