Insert an Image File into Oracle Database

Photographs and pictures and Oracle BLOB data are easy to add to a Oracle table. There are two ways to load BLOBs and CLOBs into the database. The first method uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures. The second uses the Oracle Call Interface (OCI) to perform the same function. Here, we will use the first method . For inserting an image, we follow the folowing steps :


Step 1 : First, we  need to create a directory on the database (which is mapped to a directory in the server's filesystem). The user must be granted the create any directory  privilege.
SQL>create directory photo_dir as 'c:\photo_dir' ;
Directory created.

Step 2 : Then we need to create a table which is used by procedure to insert the image in our table . Here we have to use a BLOB to insert the image .
SQL> create table temp_photo
 (
 ID    NUMBER(3)     NOT NULL,
 PHOTO_NAME      VARCHAR2(50),
 PHOTO    BLOB
 );
Table created.

Step 3 : Now let's write the procedure to insert the image in the table above.

SQL> create or replace PROCEDURE load_file (
 p_id number,
 p_photo_name in varchar2) IS
 src_file BFILE;
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
 BEGIN
  src_file := bfilename('PHOTO_DIR', p_photo_name);
  -- insert a NULL record to lock
  INSERT INTO temp_photo
  (id, photo_name, photo)
  VALUES
  (p_id , p_photo_name ,EMPTY_BLOB())
  RETURNING photo INTO dst_file;
  -- lock record
  SELECT photo
  INTO dst_file
  FROM temp_photo
  WHERE id = p_id
  AND photo_name = p_photo_name
  FOR UPDATE;
  -- open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
  -- determine length
  lgh_file := dbms_lob.getlength(src_file);
  -- read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
  -- update the blob field
  UPDATE temp_photo
  SET photo = dst_file
  WHERE id = p_id
  AND photo_name = p_photo_name;
  -- close file
  dbms_lob.fileclose(src_file);
 END load_file;
/


Step 4 :  We can test it from SQL*Plus 
SQL> execute load_file(1,'kartheek.jpg') ;


Note : Remember that the file kartheek.jpg should exist in the server's 'c:\photo_dir' directory .

Understanding Organization Hierarchy in Apps

Objective: The objective of this document is to understand Organization Hierarchy in Oracle E-Business Suite. This involves understanding entities such as Business Groups, Set Of Books, GRE/Legal Entities, Operating Units and Inventory Organization. This document will attempt to clearly define these terms and help representing any given company using the following model.

Business Group: This is the top-most entity with in a given company's organization hierarchy. A business group serves the sole purpose of being an employer operating in a specific business area. This is the first organization you must setup. All the other organizations will belong to this organization. A corporation may choose to setup more than one Business Group.

Set of Books: The purpose of Set of Books is to facilitate recording financial information for any number of entities within a company with complex hierarchy. Each Set of Books belongs to a single Business Group, but a Business Group may have multiple Sets of Books. The criterion for creating a new Set of Books is based on Currency, Chart of Accounts and Calendar.

1.        Currency:  USD, GBP, etc.

2.        Chart of Accounts (CoA):  This is the basic information structure for all financial systems within the E-Business Suite.  The purpose of CoA is to easily identify the nature of transaction and facilitate reporting on specific kind of transactions. A given organization within the company will setup the CoA in an intuitive manner to capture the following information:

a.        Who is the owner of the transaction? E.g. Purchasing within Procurement Organization

b.        What type of transaction is it? E.g. Office Supplies, Amenities

c.        What is the source of funds? E.g. Generic, Training, Fun

d.        What is the fund being used for? E.g. Recreation, Education, Sports

For Eg:  If a Procurement Org Member would like to request for a recreation equipment. The chart of accounts could potentially look like:

APPS

PRC

AME

FUN

REC

1OP579

Such flexibility in setting up CoA structure is essential to facilitate grouping and reporting financial transactions. In E-Biz Suite this is facilitated by Accounting flexfield in GL.

3.       Calendar: You define a Calendar to capture your company’s financial year. You can split this Calendar into different periods. The company's business cycle will determine the calendar and the type/number of periods within the calendar. It offers flexibility to choose a specific period type such as Month, Day, etc

GRE/Legal Entity:  This is the organization that is recognized by the Government to represent the company in the country of operation. All necessary information that a Government requires such as EEO statistics, etc will be addressed by this organization. Each Legal Entity is associated with a single Set of Books, and a Set of Books may have multiple Legal Entities.

Operating Unit: This can be defined as a business unit that performs one or more business activities and in the process creates transactions with financial impact. In E-Business Suite, the actions that can be performed by a given user is based on the chosen responsibility. This responsibility is exclusively tied to a specific Operating Unit by setting a profile option named “MO:Operating Unit”. The MO stands for Multi-Org. This is how all the transactions created by a given user with a specific responsibility tie to a specific Operating Unit. An Operating Unit belongs to a particular Legal Entity, and a Legal Entity may own multiple Operating Units.

Inventory Organization: The sole purpose of this organization is to facilitate stocking goods and act as a source for all the items (say raw material) required by the Operating Units for the purpose of manufacturing and also items (say manufactured finished goods) for distribution to the company's clientele. An Operating Unit may own multiple Inventory Organizations, and each Inventory Organization belongs to a particular Operating Unit. However, an Inventory Organization can serve multiple Operating Units within the same Set of Books.

How a corporation is represented within E-Business Suite:

The sole purpose of E-Business suite is to capture transactions generated by the company as information, facilitate sharing of this information between the entities within a company and provide a security blanket to control access to the companys information. To do all this there are certain steps that need to happen:

1.        Represent the Company and its hierarchy as an entity in E-Business Suite using the organization model defined above.

2.        Associate Employees that are responsible for all the actions a company does to the above entity

3.        Capture transactions the employees generate by virtue of their actions in conducting business. 

Figure 1: A portion of GE's Organization Hierarchy.

Figure 2: A portion of Vision's Organization Hierarchy.

   

Defining Organizations

Purchasing: Setup -> Organizations -> Organizations

Business Group, Legal Entity, Operating Unit, and Inventory Organization are simply different roles (classifications) assigned to an organization. Because of this, a single organization can be both an Operating Unit and an Inventory Organization (ex. Vision Operations). Such an organization can create transactions with financial impact as well as manage an inventory of goods. In fact, small companies may choose to define a single organization as the Business Group, Legal Entity, Operating Unit, and Inventory Organization, so that it can serve all of these roles. This flexibility allows you to define the organization hierarchy in the way that best suits your business.

All organizations are defined in the Organization form. In the Organization Classifications region, you can assign one or more classifications to each organization. For example, Figure 3 shows that Vision Services R&D is classified as an HR Organization, an Inventory Organization, and an Operating Unit.

 

Figure 3: Defining the Vision Services R&D organization.

Select an organization classification and click the Others button to configure the options for that classification. For example, here you can attach a legal entity to a set of books (Legal Entity Accounting), an operating unit to a legal entity and a set of books (Operating Unit Information), and an inventory organization to an operating unit, a legal entity, and a set of books (Accounting Information). In Figure 4, I selected Operating Unit, clicked the Others button, and then selected Operating Unit Information from the Additional Information LOV. In the Operating Unit Information window, you can assign the OU to a Legal Entity and Set of Books. Here we see that Vision Services R&D is assocaited with the Vision Services Legal Entity and the Vision Services (USA) Set of Books.

 

Figure 4: Configuring the Operating Unit options for Vision Services R&D.

Organizations Data Model

Tables

All organizations and classifications are defined in HR_ALL_ORGANIZATION_UNITS and HR_ORGANIZATION_INFORMATION. Sets of books are defined in GL_SETS_OF_BOOKS.

HR_ALL_ORGANIZATION_UNITS

Columns: ORGANIZATION_ID, BUSINESS_GROUP_ID, NAME, LOCATION_ID...

This table contains basic organization information. It corresponds to the Organization window of the Organization form. It has one record for each organization defined in the system.

HR_ORGANIZATION_INFORMATION

Columns: ORGANIZATION_ID, ORG_INFORMATION_CONTEXT, ORG_INFORMATION1...20

This table contains the classifications for each organization as well as the options settings for each classification. It corresponds to the Additional Organization Information window of the Organization form. Its columns provide storage for the developer descriptive flexfield Org Developer DF.

The table has 20 flexfield segments (ORG_INFORMATION1, ORG_INFORMATION2, ORG_INFORMATION3 ... ORG_INFORMATION20). The meaning of these segments depends on the value of ORG_INFORMATION_CONTEXT, which is called the structure defining column. For example, for records with ORG_INFORMATION_CONTEXT = 'CLASS', segment 1 is the classification name, and segment 2 indicates whether the classification is enabled.

ORG_INFORMATION_CONTEXT	Segment	Meaning
'CLASS' (Used for all organizations)	1	Organization classification name
	2	Enabled
'Legal Entity Accounting' (Used for legal entities only)	1	Set of Books
'Operating Unit Info' (Used for operating units only)	2	Legal Entity
	3	Set of Books
'Accounting Information' (Used for inventory organizations only)	1	Set of Books
	2	Legal Entity
	3	Operating Unit

GL_SETS_OF_BOOKS

Columns: SET_OF_BOOKS_ID, NAME, CHART_OF_ACCOUNTS_ID, CURRENCY_CODE, PERIOD_SET_NAME...

This table contains all the sets of books defined in General Ledger.

Convenience Views

When querying for information about specific types of organizations, you can use several convenience views that already include joins between the relevant tables:

Business Groups - PER_BUSINESS_GROUPS

Columns: ORGANIZATION_ID, BUSINESS_GROUP_ID, NAME, LOCATION_ID...

Legal Entities - HR_LEGAL_ENTITIES

Columns: ORGANIZATION_ID, BUSINESS_GROUP_ID, NAME, SET_OF_BOOKS_ID...

Operating Units - HR_OPERATING_UNITS

Columns: ORGANIZATION_ID, BUSINESS_GROUP_ID, NAME, LEGAL_ENTITY_ID, SET_OF_BOOKS_ID...

Inventory Organizations - ORG_ORGANIZATION_DEFINITIONS

Columns: ORGANIZATION_ID, BUSINESS_GROUP_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SET_OF_BOOKS_ID, LEGAL_ENTITY, OPERATING_UNIT...

The ORGANIZATION_CODE here is the 3-letter abbreviation for the inventory org, such as V1, M1, or VSD.

Using Organizations in Oracle Applications

Assigning Organizations to a Responsibility

Each responsibility is tied to a particular business group, set of books, and operating unit. This is configured using several system profile options.

Entity	Profile Option
Business Group	HR: Business Group
Set of Books	GL: Set of Books
Operating Unit	MO: Operating Unit

By convention, responsibilities are usually named for their business function and operating unit. For example, Purchasing, Vision Operations (US) provides purchasing functions for the Vision Operations operating unit.

When you log in as a particular responsibility, all of your actions take place in the assigned business group, set of books, and operating unit.

Using Operating Units

The Operating Unit is an autonomous business unit that creates transactions with financial impact. In the Oracle Applications that use Multi-Org functionality (ex. Purchasing, Order Management, Projects, Payables, Receivables), transactional data and some setup data are partitioned by OU. When using one of these applications, you will only be able to see the transactions that belong to the OU configured for your responsibility. In addition, setup data such as the purchasing, receiving, and financial options can be configured independently for each OU. For example, you can set the minimum release amount to $1 for Vision Operations and $50 for Vision Services.

Org Context

At runtime, the current Operating Unit is stored in the first 10 bytes of the CLIENT_INFO database variable. This is called the org context. It can be accessed from SQL using the following expression:

TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10))

You can programmatically set the org context by calling the following API: FND_CLIENT_INFO.set_org_context()

When using Apps, you can see the org context by using Help menu -> Diagnostics -> Examine on the following field:

Block: $PROFILES$

Field: ORG_ID

Org-Striped Views

For every kind of transactional data that needs to be partitioned by OU, there is an _ALL table with all the transactions and a corresponding view with only the transactions that belong to the current org context. For example, PO_HEADERS_ALL contains all the PO headers, while PO_HEADERS contains only the PO headers that belong to the current org context.

Setup data is partitioned in a similar way. The _ALL table contains records with the setup data for every operating unit, and the view has a single record with the setup data for the current org context. For example, FINANCIALS_SYSTEM_PARAMS_ALL contains the financial options for all the operating units, while FINANCIALS_SYSTEM_PARAMETERS has a single record with the financial options for the current org context.

Using Inventory Organizations

The Inventory Organization is an organization used to track inventory transactions and balances. It is used to secure information in applications such as Purchasing (receiving), Order Management (shipping), Inventory, Master Scheduling, and Work in Progress. For example, the ship-to organization that you specify on a Purchase Order is an Inventory Org. You can choose any Inventory Org that has the same set of books as your responsibility. When you receive against that PO, you should choose the same Inventory Org.

Choosing the Inventory Org

When you navigate to Choose Organization or open a form that requires an Inventory Org, an LOV opens with a list of inventory orgs. The org that you choose is stored in the global variables GLOBAL.FND_ORG_ID, GLOBAL.FND_ORG_NAME, and GLOBAL.FND_ORG_CODE. In the PRE-FORM trigger of the Receiving forms, this org is then copied to PO_STARTUP_VALUES.ORG_ID and PO_STARTUP_VALUES.ORG_NAME.

Inventory Org Security

You can restrict Inventory Org access to specific responsibilities. In Inventory, navigate to Setup -> Organizations -> Organization Access. Here you can add entries specifying that only certain responsibilities can access an Inventory Org. If you do not specify any entries for a particular Inventory Org, then all responsibilities can access that Inventory Org. In Figure 5, only Shop Floor Manager and Shop Floor Manager, Vision Operations can access Denver Manufacturing (M5), but any responsibility can access Seattle Manufacturing (M1).

Figure 5: Defining access to Inventory Organizations.

Inventory Orgs and Items

Each item is defined in a particular organization, called the Master Inventory Organization. Once the item is defined, it can be assigned to other inventory organizations (Master Item form, Tools menu -> Organization Assignment). For example, CM13139 is defined in Vision Operations (V1) and assigned to other orgs like M1, M2, M3, W1, W2, and W3, so you can use the item in any of these inventory orgs. When you create a PO, you can choose any item that is assigned to the ship-to organization.

Certain attributes of an item (Master level) are the same across all organizations, while other attributes (Organization level) may be configured differently for each organization. You configure Master-level attributes using the Master Items form and configure Organization-level attributes using the Organization Items form.