Monthly Archives: January 2011


The captioned profile option has been obsoleted in Oracle R12 version. All the customized report used this profile option “MO: Top Reporting Level” and value set “XLA_MO_REPORTING_LEVEL” should be re-written.

If customer used this profile option in his 11i custom reports , Customer might have been using this profile option to set the reporting level at SoB,Legal Entity or Org. However, in Release 12 the highest level is always a Ledger. Thus, these profiles are no longer needed in Release 12 and you will need to update the custom reports by basing them ledger and Mo profiles instead of SoB and MO: Top Reporting Level.

Differences between HR:Security Profile & MO:Security Profile

HR:Security Profile and MO:Security Profile are used for MOAC (Multi-Org-Access-Control).
HR:Security Profile is used to restrict the data in Human Resources according to the Business Group or whatever criteria you define in this security profile. Thus, for HR, it uses this secuity profile in its data exposure to the user.
MO:Security Profile acts the same but is used for Financials and Manufacturing applications. It restricts the access (site level or can be set at responsibility level) to certain operating unit (or whatever criteria defined in the security profile).
If there is no value at MO:Security Profile, then Financials and Manufacturing use the HR:Security Profile option for their data exposure to users.

Multi-Org features in Oracle EBS 11i and R12

Generally, Oracle 11i fulfills multi-org feature by the views in apps schema; while Oracle R12 fulfills the feature by Virtual Private Database feature.

In 11i,  the base table are created by product schema, e.g. oe_order_headers_all in ont db schema. In apps schema, there’s a view called oe_order_headers, which has a where clause like “org_id = SUBSTRB (USERENV (‘CLIENT_INFO’), 1, 10)” . So every user accessed application has his own userenv, then every user can access his own org’s data.

In R12, the base table are also created by product schema, e.g. oe_order_haders_all in ont schema. In apps schema, there’s a Synonym oe_order_headers with VPD feature to fulfill the multi-org feature.

In 11i, we set MO: Operating Unit profile on the responsibility level, then all of user logged from this responsibility has this OU(org_id) client info, so this user can only access this operating unit data from this responsibility.

In R12, we set up a security profile, which can include several operating units, then we assign this security profile to the profile option “MO: Security Profile” on the responsibility level.  The user logged from this responsibility can access all of OUs’ data  in the security profile.

Adding a New Operating Unit


The following steps are required to add new operating units to your organizational structure:
1.1 Revise the Organization Structure.
1.2 Define Sets of Books (optional).
1.3 Define Locations.
1.4. Define Organizations and Relationships.

2.1 Define Responsibilities.
2.2 Set Profile Options for Each Responsibility linked to the new Operating Unit.
1.1. HR: Business Group
1.2  HR: Security Option
1.3: HR: User Type (FOR accessing HRMS functions)
1.4  HR: Cross Business Group
2.1. GL: Set of Books(11i)
2.1  GL:%Ledger%  (R12)
2.3  GL: Data Access Set. This profile option to control the ledgers that can be used by Oracle General Ledger.
3.1. MO: Operating Unit
3.2. MO: Security Profile (R12)
3.3. MO: Default Operating Unit
4.1 Tax: Allow Override of Tax Code
4.2 Tax: Invoice Freight as Revenue
4.3 Tax: Inventory Item for Freight
5.1 Sequential Numbering
5.2 INV: Intercompany Currency Conversion
2.3 Run the Replicate Seed Data concurrent program.

3.1 Define Inventory Organization Security.
3.2 Define Intercompany Relations (optional).
3.3 Implement the Application Products.

4.1 Secure Balancing Segment Values (optional).
4.2 Run the Setup Validation Report (recommended).
4.3 Implement Document Sequencing (optional).
4.4 Set the top reporting level (optional).
4.5 Set up conflict domains (optional).

Replacement of “Application.filesearch” in Excel 2007

In Excel 2003, we have an excel with VBA script to integrate all of the runtime folder’s spreadsheets to one spreadsheet. In that file, we used “Application.filesearch” method to find out all of the spreadsheets  in the current folder.

While, after upgrading to Excel 2007, the “Application.filesearch” is obsoleted by Microsoft. How to replace it in our tools?

Against internet researching, we added two classes to the Excel and use these two classes to replace the old “Application.filesearch”.

First calss, named with “FileSearch”

Dim pLookIn As String
Dim pSearchSubFolders As Boolean
Dim pFileName As String
Public FoundFiles As New Collection
Public Property Get LookIn() As String
LookIn = pLookIn
End Property
Public Property Let LookIn(value As String)
pLookIn = value
End Property
Public Property Get SearchSubFolders() As Boolean
LookIn = pSearchSubFolders
End Property
Public Property Let SearchSubFolders(value As Boolean)
pSearchSubFolders = value
End Property
Public Property Get fileName() As String
fileName = pFileName
End Property
Public Property Let fileName(value As String)
pFileName = value
End Property
Public Function Execute() As Long
Dim ex As Long
Dim sLookIn As String
Dim sDirName As String
Dim sSubDir As String
Dim sFileName As String
Dim ff As FilesFound
Set ff = New FilesFound
sLookIn = LookIn
sDirName = Dir(sLookIn, vbDirectory)
sFileName = Dir(sLookIn & “\”, vbNormal)
Do Until Len(sFileName) = 0
If sFileName Like fileName Then
ff.AddFile sLookIn, sFileName
FoundFiles.Add (ff.FoundFileFullName)
End If
sFileName = Dir
If SearchSubFolders Then
Do Until Len(sDirName) = 0
If GetAttr(sLookIn & sDirName) = vbDirectory Then
sSubDir = sDirName
Do Until Len(sFileName) = 0
If GetAttr(sDirName) = vbNormal Then
sFileName = sDirName
ff.AddFile sDirName, sFileName
FoundFiles.Add (ff)
End If
End If
sDirName = Dir
End If
Execute = FoundFiles.Count
End Function

Dim pLookIn As StringDim pSearchSubFolders As BooleanDim pFileName As StringPublic FoundFiles As New Collection
Public Property Get LookIn() As StringLookIn = pLookInEnd PropertyPublic Property Let LookIn(value As String)pLookIn = valueEnd PropertyPublic Property Get SearchSubFolders() As BooleanLookIn = pSearchSubFoldersEnd PropertyPublic Property Let SearchSubFolders(value As Boolean)pSearchSubFolders = valueEnd PropertyPublic Property Get fileName() As StringfileName = pFileNameEnd PropertyPublic Property Let fileName(value As String)pFileName = valueEnd PropertyPublic Function Execute() As Long
Dim ex As LongDim sLookIn As StringDim sDirName As StringDim sSubDir As StringDim sFileName As StringDim ff As FilesFound
Set ff = New FilesFoundsLookIn = LookInsDirName = Dir(sLookIn, vbDirectory)sFileName = Dir(sLookIn & “\”, vbNormal)Do Until Len(sFileName) = 0If sFileName Like fileName Thenff.AddFile sLookIn, sFileNameFoundFiles.Add (ff.FoundFileFullName)End IfsFileName = DirLoopIf SearchSubFolders ThenDo Until Len(sDirName) = 0If GetAttr(sLookIn & sDirName) = vbDirectory ThensSubDir = sDirNameDo Until Len(sFileName) = 0If GetAttr(sDirName) = vbNormal ThensFileName = sDirNameff.AddFile sDirName, sFileNameFoundFiles.Add (ff)End IfLoopEnd IfsDirName = DirLoopEnd If
Execute = FoundFiles.Count
End Function

Second class, named by “FilesFound”:

Public FoundFileFullName As String

Public Function AddFile(path As String, fileName As String)

FoundFileFullName = path & “\” & fileName

End Function

How to use it?

Dim sFile As String

Dim fs As New FileSearh

With fs

.LookIn = sPath

.SearchSubFolders = True

.fileName = “*”

If .Execute > 0 Then

For i = 1 To .FoundFiles.Count

sFile = .FoundFiles(i)

‘ your code here


End If

End With