XML Publisher Reports

XML Publisher Report is a light weight reporting method using XML Templates. This is an alternate to RDF Reports as this is easy to develop and maintain. Any user with a basic knowledge of XML tags and RTF can develop / Modify the Report.

XML Reports supports a wide variety of output formats including charts and graphic components which is very flexible.

XML can handle small or medium data load. XML cant be used when the data volume is enormous. This may overload the XML Reporting engine and slow down the report generation. In all other situations XML publisher is good reporting choice.

All the Steps involved in the development process is attached here. Its free to download and keep for your future references

ORACLE XML Publisher Report- I
ORACLE XML Publisher Report - II

Original Reference

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/index.html#o

Multiple Organization Access Control (MOAC)

Introduction

11i Concept

In ORACLE database, data is organized to contain multiple Operating Units. The data is classified or extracted by having a column named ORG_ID in the ORG based tables.  And in the 11i systems we have the extraction criteria as “WHERE ORG_ID = <P_org_id>”, this means we will pass the ORG_ID as a parameter or alternatively pick the ORG_ID from profile fnd_profile.value ('ORG_ID') and pull the records. So here there is a restriction of viewing one org records at a time.  In short data was restricted to a single operating unit view which partition the base table data on the OU setting and which is controlled by the profile.

11i concept work on

• The Base tables , with _ALL which contains the data for all OUs
• Each org_based tables contains a column called ORG_ID indicating the Operating Unit to which the data belongs to
• The current OU was stored in the first 10 characters of the database Application Context variable CLIENT_INFO
• When logging into the applications, the CLIENT_INFO value was set to the appropriate operating unit organization ID for the session based on the profile option setting for MO: Operating Unit
• SQL plus can set this using 
  Execute dbms_application_info.set_client_info(‘<ORG_ID>’);

R12 Concept

With the R12 release ORACLE came up with a new concept called Multiple Organization Access Control (MOAC), in this concept ORACLE allows more flexibility in accessing the data across multiple OUs without having the ORG_ID parameter. This is a generic concept that can be applied to various objects such as Forms, Reports, and OAF etc. This means you can access the data across different OUs from a single responsibility.

Advantages

• Users can access and perform multiple tasks across different OUs without changing OUs
• This can improve on the efficiency of transactions for companies that have centralized business functions
• Speed up the whole transaction processing
• Reduce the overhead of setup, maintenance and better control.
• Better decision making on Suppliers, Sites across different OUs and Customer Records.
• Allows a global consolidated view across different OUs and Legal Entities.

MOAC - How this is done?

Create Security Profiles (using form function ‘Define Global Security Profile’)

Security Profiles are the driving key for MOAC. Each security profile can be setup to contain multiple OUs.

     

• Enter a unique name for the security profile.
• To restrict access by discrete list of organizations, select ‘Secure organizations by organization hierarchy and/or organization list for the Security Type’.
• Check the Exclude Business Group check box to remove the business group in the list of organizations.
• Use the Classification field to limit the list of values (LOV) in the Organization Name field. For example, if you select the classification to Operating Unit, only operating units will display in the LOV.
• In the organization name field, select the Operating Unit for which you want access.
• Repeat until you have included all organizations to which you need access.

      

Here we can see that the Security Profile XXMOAC Test is setup and it contains two operating units named Vision Operations and Vision USD

Run the concurrent program “Security List Maintenance Program” from the standard request submission form. 

The “Security List Maintenance Program” can be run for a single named security profile to prevent impact to other security profiles.

Assign appropriate security to the profile option “MO: Security Profile” for your users and responsibilities

Navigate to the “System Administrator” responsibility > System Profile Options

Assign the security profiles to MO: Security Profile for your responsibilities and/or users.

MO: Operating Unit shall be blank; otherwise it’ll work just like 11i works

Assign a value for profile option “MO: Default Operating Unit” (Optional)

Navigate to System Administrator Responsibility > System Profile Options

Assign a default operating unit to “MO: Default Operating Unit” profile option for your responsibilities and/or user.

Assign MO: Operating Unit (Mandatory for only Single Org or if MO: Security Profile is not defined)

Note that if you already set the security profile as per the above screen-shot then skip this step. This is applicable when you have only one OU data.

Navigate to System Administrator Responsibility > System Profile Options

Assign the Operating unit to MO: Operating Unit profile option for your responsibility or user.

MOAC is initialized when you open a Form, OA page or a Report. The first MOAC call checks if the profile “MO: Security Profile” has a value. If yes, then the list of operations units to which access is allowed is fetched and the list of values (LOV) is populated. Then default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”. This is how MOAC works in Oracle Release 12 when the value of “MO: Security Profile” is set.

When the profile “MO: Security Profile” does not have a value MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

Developer Guidelines and Approach

To increase the flexibility and performance in a multiple organizations environment and provide the same level of data security, the DBMS Virtual Private Database (VPD) feature replaces the CLIENT_INFO function.

The Virtual Private Database (VPD) 

This is a feature that allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user’s SQL statement to include conditions set by security policy that are visible to the user.

View Changes

• Drop the single organization view
• Create a synonym with the same name as the obsolete single organization view
• Attach a policy function to the synonym

Table Changes

• Create the Custom Base tables as DEMO_TABLE_ALL
• Add the ORG_ID column if it does not exist
• Create SYNONYM on the base table as DEMO_TABLE
• Apply Security Policy to DEMO_TABLE

Code Changes

• Remove the Views and Base Tables from the code
• Use SYNONYMS
• Remove the Org_id parameter if any.

MOAC Setup for Concurrent Program

• System Administrator (responsibility):
• System Administration: Concurrent: Programs
• Query by your program name
• Click on the edit icon
• Click on the Requests tab
• System Administrator (responsibility):

     

• Multiple – you can’t select the operating unit that you want to run for that report – it’ll run for all OU depending on the Security Profile attached to the responsibility
• Single – you have to enter which operating unit you want to run this report against – it’ll be a required field to be enter to run the report
  
  
  This is the screen-print when the operating mode is set to SINGLE and the same will be disabled when we set it as MULIPLE and this will fetch the data across all the OUs attached to the Security Profile that we explained above.

      

API Handling

• Do not use the multiple organizations temporary table directly in the SQL query.
• Rewrite the SQL joins with two or more views to use just one secured synonym depending on the driving table for the query and replace the remaining views by _ALL tables.
• Add the ORG_ID to the WHERE clause of the SQL to avoid Cartesian joins for tables that include ORG_ID the composite or driving key.
• Use MO_GLOBAL.Set_Policy_Context.

This API has 2 parameters –

1. Operating unit 
2. Context

Context has 2 values 

1. M  
2. S

When policy context is set to ‘M’, data from all accessible Operating Units will be returned.

When policy context is set to ‘S’, then only data from the specified Org_Id will be returned.

• Products must call the MO_GLOBAL.init() API to execute the multiple organizations initialization.

Workflow Handling

With multiple organizations access control, you must set the current organization ID and not the CLIENT_INFO org context. You must derive the current organization ID from item keys. Do not rely on MO: Security Profile, MO: Default Operating Unit and MO: Operating Unit profile options when setting the organization context because the operating unit must be validated before initiating the workflow.

MOAC Defaulting Rule

If the profile option “MO: Security Profile” is not set, then “MO:Operating Unit” value is used as the default Operating Unit even if “MO: Default Operating Unit” profile is set to a different value.

If the profile option “MO: Security Profile” is set and gives access to one Operating Unit, the default Operating Unit will return this value even if “MO: Default Operating Unit” is set to a different value.

If the profile option “MO: Security Profile” is set and gives access to multiple Operating Units, then the profile value “MO: Default Operating Unit” if set is validated against the list of Operating Units in “MO: Security Profile”. If the Operating Unit is included in the security profile then it is returned as the default value. Otherwise there is no Operating Unit default. Moreover, if the Profile Option “MO: Default Operating Unit” is not set, then there is no default Operating Unit.

Pre R12 Multi-Org Features 

Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).

A view in the APPS schema provides the Multi-Org filtering based on the following statement in the where clause.   SUBSTRB (USERENV (’CLIENT_INFO’), 1, 10)

R12 Multi-Org Features 

Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).

A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private Database feature of the Oracle 10G DB Server.

Multi-Org Access Control (MOAC) Profile Options

MO: Security Profile

The MO Security Profile controls the list of operating units that a responsibility or user can access. If you set the security profile at the responsibility level, then all users using that responsibility will have access to only the operating units available in the security profile. If you set the security profile at the user level, then the user will have access to only those operating units, irrespective of application responsibility that they log into.

MO: Default Operating Unit

The MO: Default Operating Unit is optional and allows you to specify a default operating unit that defaults when you open different sub ledger application pages. Because you can access multiple operating units, you may want to set up a default one instead of forcing users to constantly have to choose one. User Preferences allows you to specify a default operating unit at the user level. Use the MO: Default Operating Unit profile option to set the operating unit context or default operating unit when accessing an applications.

MO: Operating Unit

This profile option is for backwards compatibility and to support products that do not use Multiple Organizations. The release 11i setting was for this is preserved during upgrade. The Release 11i MO: Operating Unit profile option is also supported in Release 12.

SQL Session Initialization

Pre-R12 

You could set your SQL session context for multi-org with the following:

BEGIN

dbms_application_info.set_client_info(101);

END;

In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.

R12 

You can set your SQL session context for a single OU with the following:

BEGINExecute mo_global.set_policy_context(’S',101);END;

‘S’ -> means Single Org Context

101 -> is the ORG_ID you want set

Example of Setting of Single Org Context

BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT('S', <orgid>);
END;

Then you need to set the MO: Security Profile so that SQLplus will have same org context as given responsibility

By SQL you can do setup as:

Execute fnd_global.apps_initialize(&user_id
                                  ,&responsibility_id
                                  ,&responsibility_application_id
                                  );

Execute mo_global.init('&product_short_name');

You can set your SQL session context for multiple OUs with the following:

BEGIN
Execute mo_global.set_org_access(NULL,111,‘ONT’);
END;

111   -> is the Security Profile you want to use

‘ONT’ -> is the application short name associated with the responsibility you will be using to find the security profiles:

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them:

SELECT psp.SECURITY_PROFILE_NAME,

       psp.SECURITY_PROFILE_ID,

       hou.NAME,

       hou.ORGANIZATION_ID

  FROM PER_SECURITY_PROFILES psp,

       PER_SECURITY_ORGANIZATIONS pso,

       HR_OPERATING_UNITS hou

 WHERE     pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID

       AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

Complete Metalink Doc

How To Setup and Verify eBS MO/MOAC, Defaulting Rules and MO: Security Profile with Channel Revenue (Trade) Management Example (Doc ID 745420.1)

Form / Menu Personalization

How to Enable / Disable a menu item using Form Personalization

There might be occasions where you would like to hide / show menu list to a single / group of users or to a responsibility. There are few ways in which we can do this

Option 1: By using the Menu Exclusion Feature

This is a tab on the Responsibility Setup Screen

Steps

1. Find the Menu’s Function

2. Search for the Responsibility

3. Exclude the Function under Responsibility

Ex: Disable the Create Accounting Menu Option from AR Transaction Form.

The actions: Create Accounting and View Accounting currently come bundled, and can be enabled or disable together through menu customization by adding / removing the function: XLA_LINESINQ_SUBLEDGER - SLA: View Accounting - Lines Inquiries.

Currently, to enable or disable either Create Accounting or View Accounting separately you need to use a workaround via the form personalization and customization of the request group to achieve this.

Navigation: 

Responsibility: Receivables Super User (Process Operations) -> Transactions 

The below screen-shot shows the option that we are going to disable

Step1:  

Find the Menu’s Function

Here the Function for this Menu is SLA: View Accounting - Lines Inquiries. This can be checked in Oracle Metalink (ID: 802645.1) / Documentation available.

Step2: 

Search for the Responsibility

Navigation:

Responsibility:  System Administrator -> Security -> Responsibility -> Define

Searching for the Responsibility: Receivables Super User (Process Operations)

We can see that after excluding the above Function the below 2 menu Items are Disabled.

Option 2: By Overriding Menu’s Functionality

If you are not able to find the Function for a particular menu item, we can use the Form Personalization to override the Menu’s Functionality

Ex: Overriding the “About AR” menu to prevent the users from viewing this information.

Go to Personalize option on Transactions Form. 

Recheck the Menu Item.

Option 3:

1. Disable Create Accounting Concurrent Request

      1. Create a new Responsibility with a custom Request Group. 

     

      2. In the Request Group remove the concurrent request Create Accounting. 

     

          Save your work and exit.

         This will prevent the users with this responsibility from submitting the Create Accounting as a              Concurrent Process.

2. Disable the Create Accounting Online Menu from the Workbenches

        a. Transaction Workbench:

               i. Go to Forms Personalization Menu 

             

              ii. Create a form personalization with the following details:

         Trigger event        : Special3 
         Processing Mode      : Both 
         Level                : Responsibility 

             

             iii. Create the first action with the following values:

         Type           : Property
         Object Type    : View
         Property Name  : VISIBLE
         Value          : False

             

                  This will disable the Special Menu to submit the Create Accounting Online from the                             Actions menu item.

             iv. Create another action to display a message.

         Type           : Message

         Message Type   : Show

         Message Text   : <Some message you want to display when 

         someone tries to use the create accounting function> 

             

                  This will display the message when the Create Accounting is selected from tools.

               v. Save your work and exit.

        b. Receipts Workbench:

               i. Go to Forms Personalization Menu 

             

              ii. Create a form personalization with the following values

         Trigger event     : Special3 

         Processing Mode   : Both 

         Level             : Responsibility

             

              iii. Create the first action with the following values

         Type           : Property 

         Object Type    : View 

         Property Name  : VISIBLE 

         Value          : False

             

             iv. Create another action to display a message.

         Type           : Message 

         Message Type   : Show 

         Message Text   : < Some message you want to display when 

         someone tries to use the create accounting function> 

             

              v. Save your work and exit.

      c. Bills Receivable Workbench:

               i. Go to Forms Personalization Menu 

             

              ii. Create a Form Personalization with the following values.

         Trigger event     : Special32

         Processing Mode   : Both 

         Level             : Responsibility 

              

              iii. Create the first action with the following values

         Type           : Property 

         Object Type    : View 

         Property Name  : VISIBLE 

         Value          : False

             

             iv. Create another action to display a message. 

         Type           : Message 

         Message Type   : Show 

         Message Text   : < Some message you want to display 

         when someone tries to use the create accounting function>  

             

              v. Save your work and exit.

3. Review Settings

    If the create accounting is clicked after the above personalization you would get the message that
    was set.

    

Sequence

A sequence is a database object that generates a series of integers. We create a sequence with the keyword CREATE SEQUENCE

Syntax:

CREATE SEQUENCE <sequence_name>

[START WITH <start_value>]
[INCREMENT BY <increment_value>]
[MINVALUE <min_value> | NOMINVALUE]
[MAXVALUE <max_value> | NOMAXVALUE]
[CYCLE | NOCYCLE]
[ORDER | NOORDER];




Ex:
Create a sequence




CREATE SEQUENCE myseq
 START WITH 1
 INCREMENT BY 5
 MINVALUE 0
 MAXVALUE 100
 CYCLE




Ex:
CURRVAL and NEXTVAL

SELECT myseq.CURRVAL
   FROM dual

*
Error at line 0
ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session




Ex:

SELECT myseq.NEXTVAL
  FROM dual

   NEXTVAL
----------
         1
1 row selected.




Ex: 
CREATE SEQUENCE myseq
 START WITH 3
 INCREMENT BY 2
 MINVALUE 0
 MAXVALUE 40
 CYCLE



SELECT myseq.CURRVAL
  FROM dual

*
Error at line 0
ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session

The above Sequence will generate the below series

3, 5, 7. . . . 39, 0, 2, 4 . . . . 40, 0, 2 . . . .




Ex:
Without CYCLE Clause




CREATE SEQUENCE myseq
 START WITH 3
 INCREMENT BY 2
 MINVALUE 0
 MAXVALUE 40

The above Sequence will generate the below series

3, 5, 7. . . . 39
After this it throws the below error as the sequence reached its Max Value.


SELECT myseq.NEXTVAL
   FROM dual
*
Error at line 0
ORA-08004: sequence MYSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated




Ex: 
Dropping a view

DROP SEQUENCE myseq;

Views

A view is basically a query built upon one or more tables. Retrieving data from view is done in the same manner as it is done on Tables.

Syntax:

CREATE OR REPLACE VIEW <view_name>
(  Alias1
,  Alias2
.
.
,  AliasN
)
AS
(
   Subquery
)
[WITH {CHECK OPTION | READ ONLY}
 CONSTRAINT <constraint_name>
]

Ex: CREATE VIEW keyword
Creating a simple view

CREATE OR REPLACE VIEW myview
AS
(
SELECT empno
     , ename
     , job
  FROM emp
)

Inserting values using View

You can perform DML operations on VIEW.

Ex: INSERT keyword on VIEW
Insert a record into EMP table using the view

INSERT INTO myview
     VALUES ( 6661
            , 'Obama'
            , 'President'
            )

Ex: UPDATE keyword on VIEW
Update a record in EMP table using the view

UPDATE myview
   SET ename = 'Osama'
 WHERE empno = 6661

Ex: SELECT keyword on VIEW

SELECT *
  FROM myview
 WHERE empno = 6661

     EMPNO ENAME      JOB    
---------- ---------- ---------
      6661 Osama      President
1 row selected.

Note: The REPLACE keyword modifies the view and recreates if it already exists.

Dropping a View

The view is dropped using the keyword DROP. Only the view NOT the table will be dropped
    
Syntax
DROP VIEW <view_name>;

Ex: DROP keyword on VIEW

Removes the VIEW from database

DROP VIEW myview;

Tables

Table is a database structure that holds the data organized into rows and columns. Each column has a data type and optionally constraints associated with it. We can perform the following operations with respect to Tables.

• Creating Table – Tables are created with CREATE Table clause
  
  Syntax
CREATE TABLE <table_name>
( <column1> <datatype> [CONSTRAINT] <constraint_def> DEFAULT <Default_Expr>
, <column2> <datatype> [CONSTRAINT] <constraint_def> DEFAULT <Default_Expr>
.
.
.
, <columnn> <datatype> [CONSTRAINT] <constraint_def> DEFAULT <Default_Expr>
)
[ON COMMIT {DELETE | PRESERVE} ROWS]
TABLESPACE <tab_space>;

Note : [ON COMMIT {DELETE | PRESERVE} ROWS] – This is used with Global Temporary Tables.

• ​​Altering Table – Tables are modified with ALTER Table clause. ALTER TABLE statement can perform one of the following
  Add, Modify or Drop a Column
  Add or Drop a Constraint
  Enable or Disable a Constraint
  
  Syntax
ALTER TABLE <table_name>
ADD         <column_name> | [CONSTRAINT] <constraint_def>
MODIFY      <column_name> <datatype>

• Renaming Table – Tables are renamed with RENAME Table clause.
  
  Syntax
  RENAME TABLE <current_table_name>
    TO <new_table_name>;
• Dropping Table – Tables are dropped (i.e., remove the whole structure along with the data in it) using the DROP TABLE statement.
  
  
  Syntax
  DROP TABLE <table_name>;
• Truncating Table – The tables are truncated (i.e. whole data inside the table is deleted) using the TRUNCATE TABLE statement.
  
  
  Syntax
  TRUNCATE TABLE <table_name>;

Tables - Examples


Ex: CREATE keyword
Creating a table and primary key constraint



CREATE TABLE mytable
( id                NUMBER      
, name              VARCHAR2(100)
, CONSTRAINT PK_ID PRIMARY KEY(id,name)
);


Ex: CREATE Keyword
Creating a simple table



CREATE TABLE mytable
( id      NUMBER        CONSTRAINT PK_ID PRIMARY KEY
, name    VARCHAR2(100)
);


Ex: ALTER Keyword
Add a primary key



ALTER TABLE mytable
  ADD CONSTRAINT PK_ID PRIMARY KEY(id,name);


Ex: ALTER Keyword
Modify a column



 ALTER TABLE mytable
MODIFY address varchar2(300);


Ex: ALTER Keyword
DROP a column



ALTER TABLE mytable
 DROP COLUMN address;


Ex: RENAME Keyword
Rename a table to a new name



RENAME mytable
    TO mytable_new;


Ex: TRUNCATE Keyword
Delete all the records from mytable_new



TRUNCATE TABLE mytable_new;


Ex: DROP Keyword
DROP the table



DROP TABLE mytable_new;