Creating DataModel and Report in BI Publisher

This post provides the steps required and to be followed for creating sample Report in the BI Publisher.

Part 1 :

1. Login to BI Publisher.
   

    Click on create Datamodel:

 

2. Select The Datasource, connection to Database:

 

3.Create a Dataset:

4. Paste the SQL with parameters and the parameters will get created automatically:

5. Parameter creation:

 

6.Parameter Display Name, Datatype and order of placement:

 

7.Save the Datamodel:

 

Generating the XML for Developing RTF:

Click on XML Icon on right top of the screen:

Pass the parameter , select All in the drop down number of rows to return and click run:

 

Save the XML generated as Sample XML.

 

 

Export the generated XML for RTF Development:

   

 

Save the XML and Start the development of RTF

To be continued in Part2 of this post.

 

Connect SQL* Plus using the TNS entry

This post is about connecting SQL Plus using the TNS entry details from the command prompt.

This are the command to use.


1. Using SID :

sqlplus <username>/<password>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=<sid>)))


2. Using Service Name :

sqlplus <username>/<password>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<service_name>)))

Example for VARRAYS and RECORD types in PL/SQL

Here is small code snippet which illustrates the VARRAYS and RECORD types in PL/SQL.

This is just for reference purpose :

----------------------------------------
--   Test Script
----------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF

SPOOL C:\Results.txt

DECLARE

    TYPE users IS RECORD(
            p_invoice_id          NUMBER,
            p_approver           VARCHAR2(250),
            p_status               VARCHAR2(250)
            );
           
    TYPE number_of_users IS VARRAY(50) OF users;
   
    p_inputs    number_of_users  := number_of_users();
   
BEGIN
     p_inputs.extend(3);
   
     p_inputs(1).p_invoice_id :=  1111;
     p_inputs(1).p_approver  := 'AAAAAA';
     p_inputs(1).p_status := 'Active';

     p_inputs(2).p_invoice_id :=  2222;
     p_inputs(2).p_approver  := 'BBBB';
     p_inputs(2).p_status := 'Active';

     p_inputs(3).p_invoice_id :=  3333;
     p_inputs(3).p_approver  := 'CCCC';
     p_inputs(3).p_status := 'Active';

     FOR i in 1..p_inputs.count
     LOOP
    
        DBMS_OUTPUT.PUT_LINE('Test Condition : '|| i );
        DBMS_OUTPUT.PUT_LINE(CHR(13)||CHR(10));
        DBMS_OUTPUT.PUT_LINE('Input Parameters : ');
       
        DBMS_OUTPUT.PUT_LINE('Invoice ID :'||p_inputs(i).p_invoice_id);
        DBMS_OUTPUT.PUT_LINE('Approver :'||p_inputs(i).p_approver);
        DBMS_OUTPUT.PUT_LINE('Status :'||p_inputs(i).p_status);      
      
     END LOOP;  

END;
/
SPOOL OFF

Pass Multiple values to PL/SQL concurrent program

Here is small piece of code snippet which can be used in the select query to fetch the records for the multiple input parameter values entered for the concurrent program : 

Say for an Example : 

We have a field, 'Employee Number' as a parameter to PL/SQL concurrent program. Say, an user enters multiple values for this particular field( Employee Number : 100, 200, 300,400,500) and then concurrent program needs to process records only for the input values. 

Following is sample query to achieve the same : 

 SELECT * FROM employee xx
  WHERE status = 'A'
   AND INSTR(','||NVL(REPLACE(p_employee_number,' ',''),xx.employee_number)||',',',' ||TRIM(xx.employee_number)||',')>0;

This can be modified according to the requirements.

iExpense - Code to fetch Start and End Date field value in iexpense

Here is small code snippet to fetch the Start and End Date field values entered in the front end. This will be useful if there is need to validate the expense lines and check the expense start and end dates. There is standard hook package AP_WEB_CUST_DFLEX_PKG which provides custom procedure CustomValidateLine to validate the expense lines.


Declare
     l_expense_start_date   DATE;
     l_expense_end_date     DATE;

Begin

      l_expense_start_date :=
         fnd_date.canonical_to_date
                 (AP_WEB_DFLEX_PKG.GetCustomFieldValue('Start Date',
                                                        p_custom_fields_array
                                                       )
                 );

      l_expense_end_date :=
          fnd_date.canonical_to_date
                  (AP_WEB_DFLEX_PKG.GetCustomFieldValue('End Date',
                                                         p_custom_fields_array
                                                        )
                  );

End;
/

Querying on a non database field in Oracle Forms

Here is small piece of code for Querying on a non database field in the Custom Oracle Form.

Displaying Header information in each of the Page in BI Publisher

Last week, i had issue where I wanted to display the Header column information in all the pages in BI publisher. The output was displaying the header information only in first page and not in all the pages.

Mandatory columns needed to insert data into BOM Interface tables.

Here is a snippet of code which tells about the mandatory columns needed to insert the data into BOM Interface tables.

Project template creation in PA using standard API.

Here is a sample script which uses standard Oracle API pa_proj_template_setup_pub.create_project_template to create Project template in Project Accounts

Sample example of Create Task API in CRM

Here is a sample example of Create Task API used in R11i.
This example shows how to create a Open task and assign it to a employee.

Count characters in a value in XML Tag in XML(BI Publisher)

I was looking for one of the funtion in XML for my requirement in RTF in order to display based on the length of the string.

Here goes that function, which is used to count the number of characters in a string in XML Publisher RTF.

Error in Price Adjustment screen : Invalid value for the field ADJUSTMENT_NAME

While doing one of the forms personalization in Service contracts in Price Adjustment screen, I have encountered this strange behavior of the form. The form personalization is related to defaulting the value of the Modifier Number field, whenever the user navigates to the Price Adjustment screen in R12. Following which, I have raised a SR with Oracle and fix was provided for the issue.

This topic is related to that behavior in Price Adjustment screen.

End dating the Price List line in Service Contracts using the API qp_price_list_pub.process_price_list

Here is sample method of ending dating the Price List line for a Item in Service Contracts using API : qp_price_list_pub.process_price_list

Script to reset Oracle Apps frontend User ID Password

Here is a  sample anonymous PL/SQL block which will reset the Oracle Apps frontend password for a given user from backend

if - else condition in XML Publisher

Here is a small snippet of XML tags which tells you how to add  if - else condition in XML Publisher RTF template.

Setting Discount to Service Contract Lines

The following example will show you, how to set the Discount for the Service Contract Lines and how to price the Service contract lines.

Sample example for DataLoad - Part 3

This is last part of the DataLoad example.

Sample example for DataLoad - Part 2

In my last previous post, we have successfully created a DataLoader script file. This post will resume loading data from that script to the actual destination ie. Oracle Applications.