BI Publisher

We can create BI publisher report either through date template as data definition or oracle reports/plsql procedure as data definition .

Data Template -

This method involves, writing required queries in xml file and attaching the same to data definition as Data template , Later we can create a layout template as we wish to display on the screen .

Data template required to have following tags,

Parameters - > you can include parameter details here , if your reports accept input parameter from user before running the report . These parameter name should match token name mentioned in concurrent program parameter . ( concurrent program should be based on  XDOTEXE  executable )

DataQuery - > Data Query tag can have multiple sql statements tag . if requirement is to use one sql statement  then open one sqlstatement tag and mention your queries in that tag, Make sure to wrap them between <![CDATA[ and ]]> tags. This is to inform Parsing engine to not process detail between these tags and pass them to Oracle.

Data Structure - > if we want to group values fetched from data query before passing them to layout engine , we can do that in this tag . Create a new group in this data structure and create a element for each column that our query uses .

Below is the same data template for extracting vendor id and vendor name for the given vendor number .

<?xml version="1.0" encoding="UTF-8"?>
<dataTemplate name="VendorDT" description="Vendor Details" version="1.0">
   <parameters>
     <parameter name="p_vendor_no" datatype="number"/>
   </parameters>
  <dataQuery>
    <sqlStatement name="VenDet">
         <![CDATA[ SELECT vendor_name,vendor_id FROM po_vendors
                            WHERE segment1 = :p_vendor_no ]]>
    </sqlStatement>
  </dataQuery>
  <dataStructure>
    <group name="VenDet_GN" source="VenDet">
      <element name="vendor_id" value="vendor_id"/>
      <element name="vendor_name" value="vendor_name"/>
    </group>
  </dataStructure>
</dataTemplate>

once above step is completed , we can create layout template by using MS word (install desktop bi publisher addon)

common issues faced while developing BI report ->

Make sure to give same code to link data definition and layout template if you are using data template approach .

If you are using oracle report/plsql procedure based approach , given program short name as code while creating data definition .

while creating valueset that is dependent on table, make sure that schema name is not given in table name field and give field value large enough accommodate all values that you are selecting from the table  . This needs to followed on assigning that value set to concurrent program parameter .

if you face any trouble in displaying number in bi publisher report, use below to enforce number .

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?vendor_id?></fo:b idi-override> 

Oracle discoverer - Tables and Queries

Discoverer reports and to get list of those from backend is different from oracle reports . They have own set of tables and these tables are not listed in oracle ETRM .

Below queries can be used to get list of responsibilities that have access to particular workbook .

To get list of Oracle discoverer workbooks,  below query can be used 

select * from disco.EUL5_DOCUMENTS ;

once we get doc id from the above query,  we can run the below query to get list of responsibilities that can run our workbook .

Select * From Apps.Fnd_Responsibility_Tl Where '#'||Responsibility_Id||'#'|| Application_Id in(

Select Eu_Username From Disco.Eul5_Eul_Users Where Eu_Id In (

Select Ap_Eu_Id From Disco.Eul5_Access_Privs Where Gd_Doc_Id=&p_doc_id)) and language='US';

Query to fetch profile values

Below query can be used to fetch profile values being set at different levels,

 SELECT fpo.profile_option_name SHORT_NAME,

         fpot.user_profile_option_name NAME,

         DECODE (fpov.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Responsibility',

                 10004, 'User',

                 10005, 'Server',

                 'UnDef')

            LEVEL_SET,

         DECODE (TO_CHAR (fpov.level_id),

                 '10001', '',

                 '10002', fap.application_short_name,

                 '10003', frsp.responsibility_key,

                 '10005', fnod.node_name,

                 '10006', hou.name,

                 '10004', fu.user_name,

                 'UnDef')

            "CONTEXT",

         fpov.profile_option_value VALUE

    FROM fnd_profile_options fpo,

         fnd_profile_option_values fpov,

         fnd_profile_options_tl fpot,

         fnd_user fu,

         fnd_application fap,

         fnd_responsibility frsp,

         fnd_nodes fnod,

         hr_operating_units hou

   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)

         AND fpo.profile_option_name = fpot.profile_option_name

         AND fu.user_id(+) = fpov.level_value

         AND frsp.application_id(+) = fpov.level_value_application_id

         AND frsp.responsibility_id(+) = fpov.level_value

         AND fap.application_id(+) = fpov.level_value

         AND fnod.node_id(+) = fpov.level_value

         AND hou.organization_id(+) = fpov.level_value

         AND fpot.user_profile_option_name =&p_profile_option_name

ORDER BY short_name;

Tables and queries used for oracle workflow

Below tables are most commonly used in oracle workflow,


select * from wf_item_types;

select * from wf_item_types_tl;

select * from wf_items;

select * from wf_item_attributes;

select * from wf_item_attribute_values;

select * from wf_activities;

select * from wf_activity_attributes;

SELECT * FROM WF_NOTIFICATIONS;

SELECT * FROM WF_NOTIFICATIONS_out;

select * from WF_NOTIFICATION_attributes;

select * from wf_messages;

select * from wf_process_Activities;

select * from wf_item_activity_statuses;

select * from WF_LOOKUP_TYPES_TL;


SELECT A.*, b.*

FROM APPS.WF_ITEM_ACTIVITY_STATUSES A,

apps.WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'CREATEPO'

AND A.ITEM_KEY = &p_item_key 

order by instance_id desc;

SELECT A.*, b.*

FROM APPS.WF_ITEM_ACTIVITY_STATUSES A,

apps.WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)

And B.Process_Item_Type = A.Item_Type

AND A.ITEM_TYPE = 'POAPPRV'

And A.Item_Key = &p_item_key

AND INSTANCE_LABEL=&p_label_name

order by instance_id desc;

WF TABLES

WF_ITEM_TYPES

The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE

WF_ITEM_ATTRIBUTES

The WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITIES

WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG

WF_ACTIVITY_ATTRIBUTES

The WF_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.

ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGES

WF_MESSAGES contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGE_ATTRIBUTES

WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

WF_NOTIFICATIONS

WF_NOTIFICATIONS holds the runtime information about a specific instance of a sent message. A new row is created in the table each time a message is sent.

WF_NOTIFICATION_ATTRIBUTES

WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.

WF_ITEMS

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE

WF_ITEM_ACTIVITY_STATUSES

The WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK)

WF_ITEM_ACTIVITY_STATUSES_H

The WF_ITEM_ACTIVITY_STATUSES_H table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY

WF_PROCESS_ACTIVITIES

WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITY_TRANSITIONS

The WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL

WF_ACTIVITY_ATTR_VALUES

The WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

select * from apps.wf_activities where name ='AP_EXPENSE_REPORT_PROCESS';

SELECT * FROM APPS.WF_ACTIVITY_ATTRIBUTES WHERE ACTIVITY_ITEM_TYPE='APEXP';

most commonly used api's in workflow,


To initiate workflow (creating and starting a process)



WF_ENGINE.CREATEPROCESS (ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,PROCESS IN VARCHAR2 DEFAULT );

WF_ENGINE.STARTPROCESS (ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2);

To set values

WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2,AVALUE IN VARCHAR2);

WF_ENGINE.SETITEMATTRNUMBER(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2,AVALUE IN NUMBER);

WF_ENGINE.SETITEMATTRDATE (ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2,AVALUE IN DATE);
 
To get values


WF_ENGINE.GETITEMATTRTEXT(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2) RETURN VARCHAR2;

WF_ENGINE.GETITEMATTRNUMBER(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2) RETURN NUMBER;

WF_ENGINE.GETITEMATTRDATE(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2) RETURN DATE;

To synchronize workflow tables - below program needs to run

Synchronize Workflow LOCAL tables

Meaning of status -


FUNCMODE : For functions, it can be RUN or CANCEL
For notifications, it can be either of RESPOND, FORWARD, TRANSFER or TIMEOUT

RESULTOUT : COMPLETE  - Means Activity is completed successfully.
WAITING : Pending for some other activity
DEFERRED: : Activity deferred till
NOTIFIED::: Activity notified to with a . Externally, must be completed using WF_ENGINE.CompleteActivity.
ERROR: :ACTIVITY ENCOUNTERS AN ERROR

 

Queries to fetch Bank account details in 11i and R12

We can use below queries to fetch bank details in 11i and R12.

for 11i,

Select Abb.Global_Attribute19,Aba.Global_Attribute19,Abu.Start_Date,Abu.End_Date,Pov.Vendor_Id,

Pov.Vendor_Name,Pov.Segment1,Povs.Vendor_Site_Id,Aba.Bank_Account_Num,Aba.Bank_Account_Name

 ,Abb.Bank_Name,abb.BANK_NUMBER,abb.BANK_BRANCH_NAME,abb.BANK_BRANCH_ID

From Apps.Po_Vendors Pov, Apps.Po_Vendor_Sites_All Povs

, apps.Ap_Bank_Account_Uses_All Abu, Apps.Ap_Bank_Branches Abb

, apps.ap_bank_accounts_all aba

where pov.vendor_id = povs.vendor_id

and povs.vendor_site_id = abu.vendor_site_id

And Abu.External_Bank_Account_Id = Aba.Bank_Account_Id

And Aba.Bank_Branch_Id = Abb.Bank_Branch_Id

And Pov.Vendor_Name =&p_vendor_name;

for R12,

 SELECT party_supp.party_name supplier_name

, aps.segment1 supplier_number

, ass.vendor_site_code supplier_site

, ieb.bank_account_num

, ieb.bank_account_name

, party_bank.party_name bank_name

, branch_prof.bank_or_branch_number bank_number

, party_branch.party_name branch_name

, Branch_Prof.Bank_Or_Branch_Number Branch_Number

FROM apps.hz_parties party_supp

, Apps.Ap_Suppliers Aps

, Apps.Hz_Party_Sites Site_Supp

, Apps.Ap_Supplier_Sites_All Ass

, iby.iby_external_payees_all iep

, Iby.Iby_Pmt_Instr_Uses_All Ipi

, Iby.Iby_Ext_Bank_Accounts Ieb

, apps.hz_parties party_bank

, Apps.Hz_Parties Party_Branch

, Apps.Hz_Organization_Profiles Bank_Prof

, Apps.Hz_Organization_Profiles Branch_Prof

WHERE party_supp.party_id = aps.party_id

AND party_supp.party_id = site_supp.party_id

AND site_supp.party_site_id = ass.party_site_id

AND ass.vendor_id = aps.vendor_id

AND iep.payee_party_id = party_supp.party_id

AND iep.party_site_id = site_supp.party_site_id

AND iep.supplier_site_id = ass.vendor_site_id

AND iep.ext_payee_id = ipi.ext_pmt_party_id

AND ipi.instrument_id = ieb.ext_bank_account_id

AND ieb.bank_id = party_bank.party_id

AND ieb.bank_id = party_branch.party_id

AND party_branch.party_id = branch_prof.party_id

And Party_Bank.Party_Id = Bank_Prof.Party_Id

and aps.vendor_name=&p_vendor_name

Order By Party_Supp.Party_Name

, ass.vendor_site_code;

SQL statement to get DDL statement of Table

We can get DDL statement used to create any able by using dbms_metadata package.

For example , we can get table creation DDL statement for ap_invoices_all table by running below query.

select dbms_metadata.get_ddl('TABLE','AP_INVOICES_ALL','AP') from dual;

SQL statements to initialize org id in session

In order to retrieve data from organization specific views like AP_INVOICES, PO_LINE_LOCATIONS . We need to initialize organization id in session , we can do that by executing below statements .

if oracle apps version is R12 or more .

Begin

mo_global.set_policy_context('S', &org_id);

End;

oracle apps version is 11i,

Begin

apps.Fnd_Client_Info.Set_Org_Context(&org_id);

End;

Swap values of two variables in Oracle SQL

Swapping values of two variables is bit different in oracle SQL then other traditional programming languages .

Here you don't need  to use temporary variable to swap two variables .

Let's say, we want to swap values of two variable called attribute7 and attribute8 of po_vendors table for vendor_id=1000.

We can accomplish above requirement by using below DML statement .

update po_vendors set attribute7=attribute8,attribute8=attribute7 where vendor_id=1000;