Concept of Rownum

ROWNUM is a pseudocolumn that is available in a query . A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation.

Look at the cases below where rownum fetches record and where it does'nt

select * from emp
where
rownum =1 will work
rownum <=5 will work

but the following where clauses wld'nt work

rownum > 5
rownum = 2

This is the algorithm that oracle uses
****************
rownum=1
for x in ( select * from emp )
loop
if ( Predicate) then -- Predicate is nothing but the Where Clause in your select Query
OUTPUT the row
rownum = rownum + 1
end if;
end loop;
****************

For Rownum=1,<=1, why it works

rownum = 1
for x in ( select * from emp )
loop
if ( rownum = 1 ) then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;

For Rownum>=5, why it doesn't work

rownum = 1
for x in ( select * from emp )
loop
if ( rownum >=5 ) --This will not work for the first row
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;

Think of it as being processed in this order:
1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.

Multi Org Access Control In R12

If you are not familiar with Multi Org Structure read my previous article on that.

In R12 One responsibilty can be tied to multiple operating Units, which is not availabe in the prior releases...

In 11.5.X,

• Profile 'MO: Operating Unit' will restrict data access for a single Operating Unit.
• To transact data in operating units, for each operating unit we need to have one Responsibility with profile 'MO: Operating Unit' defined.
• Multi-Org views are used to filter records for a single Operating Unit.
  

From R12 onwards,

• Profile 'MO: Security Profile' will ensure access to multiple operating units from single responsibility.
• End-Users will select a Operating Unit and start entering the transaction.
• List of Operating units accessible to End-user from a single responsibility is determined by 'Security Profile' defined at responsibility level/User level using Profile
  option 'MO: Security Profile'. This profile option will default in Operating Unit field of a transaction form.

• 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.
• Also, if the Profile Option 'MO: Default Operating Unit' is not set, then there is no default Operating Unit.
• 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 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.
  
  Metalink Note 414003.1 gives a detailed description on this new feature

• Multi Org Concept In Oracle Apps
  ------------------------------------
  Oracle's Multi Org Structure has the following top-to-bottom hierarchy
  Business Groups
  Legal Entity
  Operating Unit
  Inventory Org
  
  The concept can be understood with the below example
  
• Lets think of a Global Service company ABC having offices in India and China.
• ABC has implemented Oracle Apps in a single instance (which means one database for both India and China operations).
• Lets say the company has the following requirement
• All the Service Requests created in India should not be visible for an agent who is in China office and the Service Requests created in China should not be visible for an agent who is in India.Basically there is one table(cs_incidents_all_b) which holds the Service requests created in both china and India.
  
  Lets say that there are two SRs resords in the table
• SR 1- India
• SR 2- China
• Also there are say two responsibilities
  India Resp - Users in India are assigned this responsibility
  China resp - Users in China are assigned this responsibility
• To the Indian users, In the Servcie Request screen they should be able to see only Service Requests created for India
• This could very well be establised without any hard coding in the screen
  Define two organizations
  "India Operations"
  and "China Operations"
• Then map the responsibility

"India Resp" to "India Operations"and

"China resp" to "China Operations"

• Set profile option MO : Operating unit to "India Operations" for "India Resp"
  while creating a SR ,system will do the following
  Insert into cs_incidents_all_b (.........,fnd_profile.value('org_id'))
  So in a Multi Org Environment each muti_or table ends with _all and has a coumn called org_idFor each _all table , Oracle provides a correspondong view without _all.
  
  To summarise think of these as below:-
  1. Inventory:- Each of your physical warehouse
  2. Operating Unit :- Think of this units within different taxation rules, accounting rules etc
  3. Business Group :- Think of it, Mittal Steel and Arcelor have merged. If you were to integrate their systems,
  you will have two different business groups.
  4. Mittal Steel UK and Arcelor steel UK
  will both belong to same Legal Entity
  
  Some Queries
  --------------------
  To get the Complete list of classifications run below SQL
  -------------------------------------------------------------------------------
  
  SELECT hl.meaning,hl.lookup_code
  FROM hr_lookups hl
  WHERE hl.lookup_type = 'ORG_CLASS'
  AND hl.enabled_flag = 'Y'
  AND trunc(SYSDATE) BETWEEN nvl(hl.start_date_active,trunc(SYSDATE))
  AND nvl(hl.end_date_active,trunc(SYSDATE))
  ORDER BY meaning

Stmt to get all the inv orgs attached to a ou
-----------------------------------------------------------
select organization_code,operating_unit from org_organization_definitions

Stmt to get all the operating units
---------------------------------------------
select organization_id, namefrom hr_operating_units order by organization_id;

What is TCA

Oracle's Customer Relationship management categorizes its products Sales, Marketing, Service, Interaction Center and eCommerce.The Service component supports activities related to customer management and support, field sales, spare parts management, and depot repair functions
The Oracle E Business Suite is designed and built as an integrated suite of applications that share Foundation components.One of the most important components of the Oracle Foundation is the customer data model known as the Trading Community Architecture (TCA).The Trading Community Architecture (TCA) is a customer model designed to support complex trading communities.
The main entities of TCA are the Party, Account ,Locations and party sites.The ‘party’ entity refers to any person, establishment or a relation between persons and/or establishments; that the implementing organization has business interests in.Locations are essentially the physical address.The connection between a location and a party that indicates that a particular location is valid for that party.The party site entity is basically to solve the Many-Many Relationship.Whenever there is a financial transaction with the customer, we crate a account .Please note that an account cannot exist without a prty but the viceversa is possible, that is a party can exist without accounts.

To explain the concepts clearly consider the following example.
You have taken a landline,mobile and a Broad Band Connection in Airtel.In ORACLE apps,you will be created as a Party(say Party 1) and three customer accounts will be created for landline,mobile and broadband connections(say A1,A2 amd A3).suppose say for the landline and Mobile accounts(A1 and A2) your bill-to address is your residential address and for the broad band(A3) its your official address.Then in this case there will be two addresses created for Party 1 in hz_locations table.Accounts A1 and A2 will be mapped to the residential address via the party site.

The main tables involved here are
HZ_PARTIES
HZ_LOCATIONS
HZ_PARTY_SITES
HZ_PARTY_SITE_USES
HZ_CUST_ACCOUNTS
HZ_CUST_ACCT_SITES
HZ_CUST_SITE_USES
HZ_RELATIONSHIPS
HZ_CONTACT_POINTS

TCA Modelling - Some Examples
-----------------------------
1. How do you view your customer who has operations in ‘n’ different regions/offices around the world? Do you consider it as one customer or ‘n’ customers?
The implications are straightforward; either there would be one party with multiple locations or many parties each with a location. This can still be crystallized to one requirement by the next question.

2. Assume that your customer has 2 offices; one in London and another in Tokyo. If your sales rep in Tokyo discovers that there could be a potential opportunity for a product for the London office; do you wish to have this sales rep assist his peer at London on the opportunity?
If the answer is ‘Yes’, well, it is one customer with multiple locations.