Administrating Oracle application, its important to understand the core components.In oracle application, lets take some of them start with some of the

• Responsibilities
• Menus
• Profile Options
• Concurrent Manager
• Security Concepts

• Responsibility

A responsibility defines and address these:

• • What functions a user can use
  • What data a user can access
  • What “Set of Books” a user can access
    • Normally in oracle application a “Set of Books” describes as significant piece of the enterprise that has a different currency, calendar, or chart of accounts
    • Is the top level in the hierarchy that a responsibility is assigned to
  • What “Organization” a user belongs to
    • An organization is a sub-set of the “Set of Books”, which is used to further restrict the data a user can access Multi-org ,which allows a subsidiary ledger, such as AP, to process invoices that will reside in multiple sets of books.
    • Users normally are assigned to multiple responsibilities.
  • The R12 there is concept called MOAC which describe the access over different OU level. Will discuss this in details some other day.
• Menus
  • A menu is a hierarchical arrangement of functions and menus of functions. Each responsibility has a menu assig ned to it.
    You should take a note functionality of a responsibility can be customized by modifying the menu associated with the responsibility.
  • The same menu can be used by many different Responsibilities.
  • In oracle application,menus can not be copied. New menus have to be built from scratch.
  • While creating a new menu hierarchy, create the lowest-level menus first. A menu must be defined before it can be selected as an entry on another menu.
  • When a menu’s name is changed, the menu entries are not affected. Other menus calling the menu by its old name, automatically call the same menu by its new name.
  • Menu structure can be documented by printing a Menu Report.
  • Use the Define Menu Form in the System Administration responsibility to create new Menus
  • You can create and define Menu Form in the System Administration responsibility to create new Menus.
• Profile Options

• These are a set of changeable options that affect the way your application runs.
• These can be set at different level, from 11.5.10 there are 6 different level where you can make take the options.
• Further control security by assigning a set of books to a responsibility,Application,or Site.
• Set of books is a company or group of companies that uses Oracle Applications that share a common Chart of Accounts,Currency,and Calendar.

Profiles are established at six different levels:

1. Site Site level profile values are used when the profile option can have only one value for the entire instance of Oracle. (example- EDI Input file path lets say if the entire server is using one directory to accept inbound files from EDI,then you would just set the profile option at the site level, then it flows down to all levels, and there is no need to set this at other levels).
2. Application
3. Server
4. Organization
5. Responsibility
6. User

• • If you are very new to oracle application , it should be noted that system Profile Options are set by the System Administrator and affect the behavior of the Application and its modules for everyone.
  • Personal Profile Options are set by the individual user and cover that individual’s interaction with the system.
• Concurrent Manager
  • In Oracle Applications, concurrent processing simultaneously executes programs running in the background with online operations. with system Administrator, you can manage when programs are run and how many operating system processes Oracle Applications devotes to running programs in the background.
  • When a user runs a report, a request to run the report is generated. The command to run the report is a concurrent request. The program that generates the report is a concurrent program. Concurrent programs are started by a concurrent manager.
  • Every time your users request a concurrent program to be run, their request is inserted into a database table, and is uniquely identified by a request ID. Concurrent managers read requests from this table.
  • Part of a manager’s definition is how many operating system processes it can devote to running requests. This number is referred to as the manager’s number of target processes.

A concurrent program actually starts running based on:

• • • When it is scheduled to start
    • Whether it is placed on hold,
    • Whether it is incompatible with other programs
    • Its request priority
  • The priority of a concurrent request is determined by application user name, and is set by the System Administrator using the Concurrent:Priority user profile option.
  • The first available concurrent manager compares the request’s priority to other requests it is eligible to process, and runs the request with the highest priority.
  • When choosing between requests of equal priority, the concurrent manager runs the oldest request first.
  • Often, several programs may be grouped together, as in a request set. Submitting the request set as a whole generates a request ID, and as each member of the set is submitted it receives its own request ID. The set’s request ID identifies the Parent request, and each of the individual programs’ request ID identifies a Child request.

• Security Concept

In oracle application security can be classified in two broad area viz:

• Database Security
  • Each Oracle application module database objects ( tables, views etc.,) are owned by a different Oracle ID. For example, all General Ledger objects are owned by the Oracle ID called GL, all Accounts Payables objects are owned by AP and so on.
  • All programs under a responsibility are executed using these Oracle IDs. For example, the General Ledger responsibilities use the GL database user-id.
  • Custom tables created to interface with Oracle Applications should be placed in a separate Oracle id to facilitate upgrading the application when necessary.
• Function Security
  • Function security is the mechanism by which user access to application functionality is controlled. Function is a part of an application’s functionality that is registered under a unique name for the purpose of assigning it to, or excluding it from, a responsibility.
  • Two types of functions: Form function ( known as a form) and non-form function ( known as a sub-function).
    A form function invokes an Oracle Forms form. Forms appear in the Navigate Window and can be navigated to. Forms can exist on their own.
  • A non-form function ( sub-function) is a function executed from within a form. These are frequently associated with buttons or other graphical elements in the form. Sub-functions do not appear on the Navigate window.

AOL Integration

In AOL , Forms, Menus, Responsibilities, Profiles, Users are inter related. Here are diagram which best describe the dependency among each other.

 

 

 

 

 

 

 

 

 

 

Testing for forum (Beta)

The key types of concurrent managers in EBS are:

The Internal and the Standard concurrent managers

Conflict Resolution Manager (CRM)

• CRM enforces any incompatibility rules.

Product Specific Managers

• It includes inventory, MRP, PA and any user defined managers

Transaction Managers

• It supports synchronous request processing
• A small pool of server-side transaction manager processes service a large number of clients
• Ex. of this manager is approval of an order

We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.

These are the user exits available in Oracle Reports that makes AOL integration.

• FND SRWINIT
• FND SRWEXIT
• FND FORMAT_CURRENCY
• FND FLEXIDVAL
• FND FLEXSQL

• FND SRWINIT
  • This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.
  • FND SRWINIT also allows your report to use the correct organization automatically.
  • Can be used in BEFORE-REPORT Trigger.
• FND_SRWEXIT
  • This user exit ensures that all the memory allocated for AOL user exits has been freed up properly.
  • Can be used in AFTER- REPORT Trigger
• USER EXITS
  • are used in Oracle APPS to access user profile values and perform proper calculation.
  • Ex. of Other AOL User exits available in Reports are given below.
• FND FORMAT_CURRENCY
  • To format the currency amount dynamically depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.
  • You obtain the currency value from the database into an Oracle Reports column. Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value. A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.

Syntax
FND FORMAT_CURRENCY
CODE=”:column containing currency code”
DISPLAY_WIDTH=”field width for display”
AMOUNT=”:source column name”
DISPLAY=”:display column name”
[MINIMUM_PRECISION=”:P_MIN_PRECISION”]
[PRECISION=”{STANDARD|EXTENDED}”]
[DISPLAY_SCALING_FACTOR=””:P_SCALING_FACTOR”]

• FND FLEXSQL
  • This user exits allows you to use Flex fields in Reports.

Syntax:
FND FLEXSQL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
OUTPUT=”:output lexical parameter name”
MODE=”{ SELECT | WHERE | HAVING | ORDER BY}”
[DISPLAY="{ALL | flexfield qualifier | segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[NUM=":structure defining lexical" |
MULTINUM="{Y | N}"]
[TABLEALIAS="code combination table alias"]
[OPERATOR="{ = | < | > | <= | >= | != | "||" |
BETWEEN | QBE}"]
[OPERAND1=":input parameter or value"]
[OPERAND2=":input parameter or value"]

• FND FLEXIDVAL

Syntax:
FND FLEXIDVAL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
DATA=”:source column name”
[NUM=":structure defining source column/lexical"]
[DISPLAY="{ALL|flexfield qualifier|segment number}"]
[IDISPLAY="{ALL|flexfield qualifier|segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[VALUE=":output column name"]
[DESCRIPTION=":output column name"]
[APROMPT=":output column name"]
[LPROMPT=":output column name"]
[PADDED_VALUE=":output column name"]
[SECURITY=":column name"]

This is an AOL user exit available to populate key flexfields for display

CODE means Key flexfield code(GL# is for Accounting Flex field, for all other check the table FND_ID_FLEXS)
NUM is the structure of the key flex field(Chart of Accounts Number)
DATA is where you store the retrieved data (your sql output).

Example

SRW.USER_EXIT(’FND FLEXSQL CODE=”GL#”
NUM=”:P_STRUCT_NUM”
APPL_SHORT_NAME=”SQLGL”
OUTPUT=”:P_SEC_SEG”
MODE=”SELECT”
DISPLAY=”:P_SEC_SEG_VAL” ‘)

The userexit call FND FLEXIDVAL :

SRW.REFERENCE(:SEC_SEG);
SRW.USER_EXIT(’FND FLEXIDVAL CODE=”GL#”
DATA=”:SEC_SEG”
APPL_SHORT_NAME=”SQLGL”
VALUE=”:SEC_SEG_DISP”
DISPLAY=”ALL”
NUM=”:P_STRUCT_NUM” ‘);
return(:sec_seg_disp);

If you are new to oracle or looking to get start with oracle new technology,here is the resource link from oracle.

What is FND_FILE

An API that contains procedures to write text to log and output files which can be viewed once the Concurrent program get completed in output or log . These procedures can only be used within PL/SQL Procedures which, are concurrent programs.

Internally its uses the oracle supplied database package UTL_FILE

• utl_file_dir parameter must be set to include the log and out directories for the applications that will run concurrent requests using FND_FILE
• Database owner must have write privileges on those directories
• Take a note that FND_FILE supports a maximum buffer line size of 32K for both log and output files.

Here are the list of Options available within API.

• FND_FILE.PUT

Useful for writing text to a file without a new line character. Multiple calls will concatenate the text .
Does not include new line character

FND_FILE.PUT (which IN NUMBER, buff IN VARCHAR2)
Which - Means LOG file or OUTPUT file.

• FND_FILE.LOG
• FND_FILE.OUTPUT

Buff - Means text to write.

Usage
FND_File.Put(FND_File.Log, ‘This text written to Log file’);

FND_File.Put(FND_File.Output, ‘This text written to Log file’);

• FND_FILE.PUT_LINE

This is used to write a line of text to a file followed by a new line character

FND_FILE.PUT_LINE (which IN NUMBER, buff IN VARCHAR2)

Usage
FND_File.Put_Line(FND_File.Out, ‘This text written to Out file’);

• FND_FILE.NEW_LINE

FND_FILE.NEW_LINE( which IN NUMBER, LINES IN NATURAL:= 1)
Writes line terminators (new line characters) to a file.
Eg. FND_FILE.NEW_LINE(FND_FILE.LOG,2)

• FND_File.PUT_NAMES

This is used to sets temporary log and out file names and directory, which normally used during test and debug phase.

FND_FILE.PUT_NAMES(p_log in varchar2,
p_out in varchar2,
p_dir in varchar2)
Usage
FND_File.Put_Names(‘Order123.log’, ‘Order123.out’, ‘/usr/temp/outfiles’);

Example
BEGIN
fnd_file.put_names(’test.log’, ‘test.out’,'‘/usr/temp/outfiles’’);
fnd_file.put_line(fnd_file.output,’Called stored procedure’);
/* add logic, etc… */

fnd_file.put_line(fnd_file.output, ‘transaction locked at point A’);
/* More logic, etc… */

• FND_FILE.CLOSE
  This will closes open files.

This is normally used only during test and debug phase

Usage:
FND_File.Close;

You can enter a value directly to a date field or use the calendar facility in APPS. Calendar lets you the following needs.

• Represent date and time graphically so that you can make a quick selection without typing.
• Select only those dates or date ranges that are suitable for the current field.

How to invoke a calendar in a form

To invoke a calendar in a custom form you need to do the following.

• Create a text item with data type DATE in the form.
• Set the LOV of the text item to ENABLE_LIST_LAMP in the property sheet.
• Set the LOV for Validation property of the text item to FALSE.
• Create an item level trigger KEY-LISTVAL as CALENDAR.SHOW

Many times, we need to submit concurrent requests and request sets from non SRS window like operating system or PL/sql . In Oracle Applications there are API’s to take these options. Here are some of the API’s function and usage..

• FND_REQUEST
  • This API is used Submit concurrent requests from backend. As we know there are couple of options avaible in oracle application to submit a concurrent requset like SRS, CONSCUB and FND’s API. This is API offers some great functionality to use via PL/sql or any other technology like , Host Program or java program.
• FND_CONCURRENT
  • Retrieve information about concurrent requests
  • Set information about concurrent requests
    
• Set_Options ·
  • This is a function of boolen type
  • Set request options
  • This is Call before submitting the request

usage:

FND_REQUEST.SET_OPTIONS(IMPLICIT = > ‘ERROR’);

• Set_Repeat_Options ·
  • Function, returns TRUE or FALSE
  • Set repeat options
  • Call before submitting the request
    
• Set_Print_Options ·
  • Function, returns TRUE or FALSE
  • Set print options (note, some options can not be over-ridden)
  • Call before submitting the request

Called before submitting request if the printing of output has to be controlled with specific printer/style/copies etc., Optionally call for each program in the request set.

Usage :

fnd_submit.set_print_options(NULL,NULL,NULL,TRUE,’N');

or

• fnd_request.set_print_options (printer =>’<printer name>’,
  style =>’LANDSCAPE’,
  copies=>1,
  save_output => TRUE,print_together => ‘N’)

• Add_printer
  • Called after set print options to add a printer to the print list.Optionally call for each program in the request set.
  • Returns TRUE on successful completion, and FALSE otherwise

Usage

Function FND_SUBMIT.ADD_PRINTER (printer =>’Printer Name’,
copies => null) ;

• Submit_Request
  • Function, returns Request Id or 0·
  • If submitting from a form, all Arguments must be specified
    
• Set_Mode (server only)
  • Function, returns TRUE or FALSE
  • Must use if request is submitted from a database trigger.
  • Call before submitting the request
  • Failure in the database trigger call of FND_SUBMIT.SUBMIT_SET does not rollback changes
    
• Get_Request_Status
  • Function returns TRUE or FALSE
  • Returns status information in function output parameters
    
• Wait_For_Request

When submitting concurrent requests using PL/SQL, it is often desired to have the parent process wait until all the child processes have completed before completing itself.

This function Wait for the request completion, then return the request phase/status and completion message to the caller. Also call sleep between database checks.

FUNCTION WAIT_FOR_REQUEST (request_id IN number default NULL,
interval IN number default 60,
max_wait IN number default 0,
phase OUT varchar2,
status OUT varchar2,
dev_phase OUT varchar2,
dev_status OUT varchar2,
message OUT varchar2) return Boolean;

This API Wait for the request completion, then return the request phase/status and completion message to the caller. Also call sleep between database checks.

• Arguments (input)
  • request_id :The request ID of the program to wait on.
  • interval :Time to wait between checks. This is the number of seconds to sleep. The default is 60 seconds.
  • max_wait :The maximum time in seconds to wait for the requests completion.
• Arguments (output) :
  • phase :The user friendly request phase from FND_LOOKUPS.
  • status :The user friendly request status from FND_LOOKUPS.
  • dev_phase :The request phase as a constant string that can be used for program logic comparisons.
  • dev_status :The request status as a constant string that can be used for program logic comparisons.
  • message :The completion message supplied if the request has completed.

Usage

IF request_id = 0 THEN
fnd_file.put_line(fnd_file.log, ‘Request Not Submitted.’);
ELSE

call_status := fnd_concurrent.wait_for_request(request_id, v_interval, v_max_wait, request_phase, request_status, dev_request_phase, dev_request_status, request_status_mesg);

END IF;

IF call_status = TRUE THEN
IF dev_request_phase!= ‘Completed’ OR
dev_request_status IN (’Cancelled’,'Error’,'Terminated’) THEN
DBMS_OUTPUT.PUT_LINE(’STATUS=JOB FAILED’);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(’WAIT FOR REQUEST FAILED - STATUS UNKNOWN’);
DBMS_OUTPUT.PUT_LINE(’STATUS=JOB FAILED’);
END IF;

• Set_Complete_Status (server only)
  • Function, returns TRUE or FALSE
  • Called from a concurrent program to set its own completion status
  • These have three status
    • NORMAL
    • WARNING
    • ERROR
• Add_notification
  • Called before submission to add a user to the notify list.
  • Optionally call for each program in the reques set.
  • ReturnsTRUE on sucessful completion, and FALSE otherwise.

Usage
FND_SUBMIT.ADD_NOTIFICATION (user ==>’ANAND’) ;

Make sure the name should be from fnd_user table

• SET_NLS_OPTIONS
  • Called before submitting request to set request attributes.
  • Optionally call for each program in the request set.
  • Returns TRUE on successful completion, and FALSE otherwise.

Usage
FND_SUBMIT.SET_NLS_OPTIONS (language =>’US’,
territory => NULL);

The process flow for Internal Orders

Internal orders normally start with creation of requisitions in Oracle Purchasing (which is created as Internal Sales Orders) .

Once the requisitions created from the MRP and Inventory modules, the Requisition Import program must be run in order to move records from the requisition interface tables to the historical PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL.

Now next is to create internal requisitions from Purchasing module.When each requisition is approved, data will be inserted into the MTL_SUPPLY table. When the items on the requisition are stocked in inventory, the supply data can be viewed from the inventory form called Item Supply/Demand.

Once the Internal Requisition is approved, a user should run Create Internal Sales Orders (from a Purchasing responsibility) to load the internal sales orders interface tables called in Order Entry.

After the Creation of Internal Sales Orders has completed, a Import Orders concurrent program need to run from an Order Management.

at this stage, Oracle order Workflow will process the internal sales order using the workflow definition listed for the Transaction Type definition in Order Management. When the workflow completes the Shipping Network is checked to see if the Transfer Type between the source and destination organization is In transit or Direct.

You can use Oracle Order Entry/Shipping to define order cycles, approvals and holds.

Internal Requisitions use the Account Generator, which automatically builds account distributions. You can specify only one accounting distribution per inventory sourced line.

This internal requisition process provides the features needed to define your inter- organization shipping network. For transfers between two organizations, you can specify whether to use in transit or direct shipments. You can also require internal requisitions for transfers between specific organization.

The Data Flow for Internal orders

Last week, got a mail from one of reader who requested for providing data flow of standard orders and Return Orders. So,here are the information for data flow. Normally standard sales order can be split into nine sub steps , which jointly carried out by some module like INV,OM, Pricing, Shipping and AR. Lets take each sub steps with data flow conditions. These are based out of the flow which is available in 11.5.10.2.

1. Order Entry

This is first stage when Order in enter in system.When the order is entered it basically create a record in order headers and Order Lines table.

2.Order Booking

This is next stage , when Order which is entered in step 1 is booked and Flow status changed from Entered to Booked.At this stage , these table get affected.

Same time, Demand interface program runs in background And insert into inventory tables mtl_demand

3. Reservation

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.

4. Pick Release

Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.

Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:

5.Pick Confirm

Items are transferred from saleble to staging Subinventory.

• mtl_material_transactions
• mtl_transaction_accounts
• wsh_delivery_details (released_status ‘Y’‘Released’ )
• wsh_delivery_assignments

6.Ship Confirm

Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries

7.Enter Invoice

This is also called Receivables interface, that mean information moved to accounting area for invoicing details.

8.Complete Line

In this stage order line leval table get updated with Flow status and open flag.

9.Close Order

This is last step of Order Processing . In this stage only oe_order_lines_all table get updated.

These are the table get affected in this step.

These are the typically data flow of a order to cash model for a standard order.