Order Management Links Oracle Order Management Track

Visit following to find some useful information of order Management


Order Management Links Oracle Order Management Track

Oracle Order to Cash Basics

If you want a high-level overview of Oracle Order to Cash then please visit the following link


http://www.bryanthompsononline.com/oracle/wp-content/uploads/2006/07/Oracle%20OTC.ppt

Oracle’s Application Implementation Methodology

Oracle’s Application Implementation Methodology is their methodology for the implementation of its e-Business Suite (ebs). According to Oracle: -

AIM Advantage is a time-tested implementation approach and toolkit for planning, executing and controlling the implementation of your Oracle E-Business Suite. It is the only implementation method specifically built for Oracle Applications, and has been used in thousands of successful implementations by Oracle Consulting, Oracle’s select implementation partners, and customers.

I’ve used this methodology on all of the Oracle Applications implementations I have been involved in and have found it to be a very useful tool. The methodology helps you plan and document an implementation at all stages of the lifecycle. Information on the pricing can be found by performing a search for "AIM" at the Oracle Store. Here you will find two options: -

1. AIM advantage without Supplement Option - Packaged Method Named User (US$2,200)
2. AIM Advantage with Supplement Option - Packaged Method Named User (US$2,530)

Both of these options provide you with the AIM CD-ROM and documentation whilst according to the Store, with the supplement option:

Oracle will provide access to AIM Advantage 3.0 supplements made generally available to commercial customers for a period of twelve months from the effective date of purchase. Supplements may include new deliverable templates and point releases of AIM Advantage (e.g. Version 3.0 to version 3.1), but will not include new major releases (e.g. Version 3.0 to version 4.0); major releases are licensed separately.

As the pricing is based on a named user, an organizations implementation cost would be directly related to the number of users that would make use of the software. To continue receiving annual updates via the supplementary option the cost would be 15% of the current list price of AIM.

Source

Import of an Excel or any other file (External data) into Oracle

For this purpose we can use the following three methods

1. Oracle Application Express

One way which is very easy is to install Oracle Application Express (Formerly HTML DB). Within Application Express we can actually just copy and paste our Excel rows and it will import them to a table. Other then that Application Express is a very useful tool for other things too.

2. SQL Loader SQLLDR

One solution is to use the sqlloader to load any external data into the Oracle database. The problem with this is that we need to run the sqlloader (sqlldr) script every time we need an update.

3. External Table

The more elegant and faster method is to use external tables.

Now, with an external table we can initialize our table once and never have to worry about it anymore. Plus we can use the external table just like any other table in database and issue SQL commands to join the table (may be processing is bit slowly).

Steps required

a. Export your excel sheet to a tab. Delimited format, we call our file "data_2_import.txt".

b. If you don't have a directory alias set up within Oracle then create one now. The directory allows Oracle to read files from this directory on your hard drive.

You create a directory with the following commands:

Create or replace directory importdir as 'C:\data_to_import';

c. Now we only need to create the external table.

CREATE TABLE EXT_MEMBERS

(

ID VARCHAR2(20 CHAR),

NAME VARCHAR2(100 CHAR)

)

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

DEFAULT DIRECTORY importdir

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY X'9' (

ID char(20),

Name CHAR(100)

)

)

LOCATION (importdir:'data_2_import.txt')

)

REJECT LIMIT 0

PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT)

NOMONITORING;

As we can see in the code above, we are creating a table with the filenames "id" and "name" which represents the order from our Excel file. We also tell the external table that every record is on a newline and that the fields are separated with tabs (X'9') 

In case we are using Comma delimited we use fields terminated by ',' in case we are using fixed field length then we use fields (

       field_1 position(1: 4) char( 4),

       field_2 position(5:30) char(30)

    )

Now when we issue a select command on the external table Oracle will read in our data_2_import.txt file. Whenever there is an update of our data_2_import files we only need to replace the file with same formatting on the hard drive and the table is automatically updated within the database.

Note: If external tables are created with NOLOG then granting READ on the DIRECTORY object is sufficient. If an external table is created without the NOLOG syntax then both READ and WRITE must be granted to SELECT from it.

External tables are READ ONLY. Insert, update, and delete can not be performed

How to recover Control file in oracle

/* I have already backup control file to trace */

SQL> ALTER DATABASE BACKUP CONTROLFILE TO trace;

/* modify and run your trace file and your control file is up to date */

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE OPEN;

Up the database

Change the font and font size in SQLPLUS

You can change the font in SQL*Plus for Windows NT/2000.

In regedit, go to
HKEY_LOCAL_MACHINE
-> SOFTWARE
-> ORACLE
-> HOME0

Create a new registry value called SQLPLUS_FONT of type REG_EXPAND_SZ and set it to your favourite fixed-width font, Eg. Courier New
Create a new registry value called SQLPLUS_FONT_SIZE of type REG_EXPAND_SZ and set it to the size you want (13 is a good size).

Formated Query For Auto Month Addition of Given period with all tabs etc for Data Loader

By Using this query you can generate your sequence information in data Loader Format

SELECT SUBSTR (sa.method_code, 1, 1) ty, fap.application_name, '\{TAB}' tb1,

dsc.NAME, '\{TAB}' tb2, sob.NAME, '\{TAB}' tb3,

DECODE (SUBSTR (sa.method_code, 1, 1), 'A', '\{LEFT}') lf1,

'\{LEFT}' lf2, '\{TAB}' tb4,

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'DD-MON-YYYY') start_dt,

'\{TAB}' tb5,

TO_CHAR (ADD_MONTHS (sa.end_date, 1), 'DD-MON-YYYY') date_ed,

'\{TAB}' tb6,

REPLACE (REPLACE (UPPER (ds.NAME),

TO_CHAR (sa.start_date, 'MON'),

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'MON')

),

TO_CHAR (sa.start_date, 'YY'),

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'YY')

) seq_na,

'\{TAB}' tb7

FROM fnd_doc_sequence_assignments sa,

fnd_application_vl fap,

gl_sets_of_books sob,

fnd_document_sequences ds,

fnd_doc_sequence_categories dsc

WHERE (sa.start_date >= TO_DATE ('01-01-2007', 'DD-MM-YYYY'))

AND (sa.end_date <= TO_DATE ('31-01-2007', 'DD-MM-YYYY'))

AND sa.application_id = fap.application_id

AND sa.set_of_books_id = sob.set_of_books_id

AND sa.doc_sequence_id = ds.doc_sequence_id

AND sa.category_code = dsc.code

ORDER BY sob.NAME, sa.category_code, sa.method_code, sa.application_id

Just need to Input Start_date and end_date in my case this is '01-01-2007 and 31-01-2007'

Oracle Identity Management and Oracle AS Single Sign-On

Oracle Application Server provides a security framework that incorporates the different key components here I discuss oracle identity management

Oracle Identity Management supports a variety of complex password policies. These fall into two categories:

• Value-based policies (including minimum lengths and the presence of a minimum number of special characters)
• State-based policies (e.g., expiration and maximum number of retries)

Many users face a proliferation of passwords as they gain access to more applications and systems. Because it is so easy for users to forget passwords when they have so many to remember, users may end up writing them down in public places, thus creating a security risk. Oracle Identity Management can help lift this burden on users by enabling deployment of single sign-on, allowing a single user and password combination across these applications and systems.

Follow these steps to set up a basic single sign-on system:

1. Install the identity management infrastructure database, database server, and single sign-on servers using the Oracle Universal Installer.
2. Configure the HTTP servers in the single sign-on middle tier.
3. Configure the HTTP hardware load balancer or Oracle AS Web Cache.
4. Configure the identity management infrastructure database single sign-on server to accept authentication requests from an externally published address of the Oracle AS Single Sign-On server.
5. Re Register the mod_osso (Oracle AS Single Sign-On extension) to the Oracle AS Single Sign-On middle tier.

How Get Trace of a database user activity

Connect to your Sys Schema by using SQL or Toad create following trigger

Create or replace trigger APPSTRACE
after logon on apps.schema
begin
execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/

This will generate Trace for that specific session.

This is some sort of database customization that’s y I never suggest to do it with your production environment hmmm u can do it with your vision / test environment

How to remove this check from your APPS schema

alter trigger appstrace disable;

How to Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)

If we are working with Oracle Applications, here how we can initialize our session in whichever tool we are using to take off the login process and pick up profile option values.

The key profile option is usually org_id (organization id in Multi-Org Environment) so we can select from organization aware views, but it applied equally to other profile options,

For Example.

We can then use FND_PROFILE.VALUE('PROFILE_OPTION_NAME') to get values from profile options.
We need to be logged into the database as the APPS user. The examples set up the session for SYSADMIN user, System Administrator responsibility.
E.g. SQL*Plus

Exec fnd_global.apps_initialize(0,20420,1);

E.g. for PL/SQL, TOAD, SQLDeveloper, SQL Navigator etc.:

Begin fnd_global.apps_initialize(0,20420,1); end;

The parameters used in above example are:

1. User_ID

SELECT user_id, user_name, description FROM applsys.fnd_user

2. Responsibility_ID

SELECT application_id, responsibility_id, LANGUAGE, responsibility_name, created_by, creation_date, last_updated_by, last_update_date, last_update_login, description, source_lang, security_group_id FROM applsys.fnd_responsibility_tl

3. Responsibility_Application_ID

SELECT application_id, responsibility_id, LANGUAGE, responsibility_name, created_by, creation_date, last_updated_by, last_update_date, last_update_login, description, source_lang, security_group_id FROM applsys.fnd_responsibility_tl

To get these we have a couple of choices

1. SQL - Replace SYSADMIN and System Administrator with your user and responsibility:

2. In Oracle Applications forms session. Login as your user and navigate to the required responsibility.
Open a function that uses Oracle forms. Go to Help > Diagnostics > Examine. In the Block enter $PROFILES$. In the field enter the appropriate field name for the parameter:

User_ID = USER_ID

Responsibility_ID = RESP_ID

Responsibility_Application_ID = RESP_APPL_ID

How to move a datafile from one Disk To Other

Connect to SQL as Sysdba then Follow the following steps

1. alter tablespace users offline;


2. copy c:\Oracle\users01.dbf e:\Oracle\users01.dbf


3. alter database rename file 'c:\oracle\users01.dbf' to 'e:\oracle\users01.dbf';


4. alter tablespace users online;

Trace the Password Change History of Database Accounts

Oracle only tracks the date that the password will expire based on when it was latest changed. So by looking at the DBA_USERS.EXPIRY_DATE and subtracting PASSWORD_LIFE_TIME you can determine when password was last changed. The last password change time can also directly be seen from the PTIME column in dictionary table USER$ (on which DBA_USERS view is based).

If you have PASSWORD_REUSE_TIME and/or PASSWORD_REUSE_MAX set in a profile assigned to a user account then you can reference dictionary table USER_HISTORY$ for when the password was changed for this account. This will maintain any password which still falls with in the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX limits.

Must Run this Query after connecting by Sys user

SELECT user$.NAME, user$.PASSWORD, user$.ptime, user_history$.password_date
FROM SYS.user_history$, SYS.user$
WHERE user_history$.user# = user$.user#

Oracle Fusion Development Tools

Oracle Fusion Development Tools

Written by Anil Passi

Monday, 20 November 2006

The roadmap for Oracle Fusion strategy is becoming clearer, thanks to most recent article by Steven Chan. So the question is, what will be the development skills required by Oracle Fusion Developer? In order to answer this, lets have a look at current skillsets required for Oracle Apps, and then map those to Oracle Fusion.

http://oracle.anilpassi.com/oracle-fusion-development-tools.html

Responsiblity of Technical Consultant

Developing simple reports using D2k report writer
Developing simple forms using D2k
Developing views that can be used for example in discoverer reports
Developing code for custom.pll but now in 11.5.10.2 using forms personalizations even a functional consultant can do this
Registering concurrent programs, menus, form functions etc
Simple forms/self service personalizations
Developing interfaces for Data Uploading
Developing scripts/programs for data migration into Oracle Apps
Designing and building custom extensions and customizations with series of screens
possibly with Close integration with other Oracle Apps screens/interfaces/API
Developing and customizing oracle work flows.
Customize Work Flows
Developing self service extensions j developer.
Troubleshooting critical issues like Material Transactions.

oracle.anilpassi.com

How to Kill Oracle Session

Get All Lock Objects by Using Following Query

SELECT a.object_id, a.session_id, b.object_name
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id


Get Session id From below Query by Passing session _id (get From Above query)

For example i got 67 from above query

SELECT SID, serial#,SID||','||serial# "session id", username, command, schemaname, osuser, machine, terminal
FROM v$session
WHERE SID = 67;

I got 93,10383 from above query
Pass Session Id In below query

ALTER system kill session '93,10383'

The most common reporting tools used in Oracle Applications

• Oracle Reports: Fixed format reports delivered with the 11i release were built on this tool. This is the most used tool for reporting on Oracle Applications. Most of reports customizations are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.

• Oracle Discoverer: is an intuitive tool for creating reports and performing on-line analysis. Discoverer uses the EUL (End User Layer), a meta data definition, which hides the complexity of the database from the end user and provides easy to use wizards for creating reports to suit individual needs. The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel.

• Oracle XML Publisher: is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report. At runtime, XML Publisher merges the custom templates with the concurrent request extracts data to generate output in RTF, PDF, HTML and EXCEL.

• RXi Report: (Variable reports) – variable format reports delivered with the E-Business 11i. With this tool a user has the ability to print the same report with multiple layouts. The user can also choose which columns he requires on a particular report. This tool is most used on Oracle Financials Applications

• FSG Reports (Financial Statement Generator): is a powerful report building tool for Oracle General Ledger. Some of benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only drawback of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.

• Business Intelligence System (BI): is a set of tools to provide high level information for the managers (decision makers) to run their business such as the profitability of a particular business unit. The information this tool provides helps managers to take the right decision with the daily data that is uploaded on their systems.

With Thanks Form http://blog.oraclecontractors.com/

Multi-Organization in Oracle e business Suite

Oracle Applications 11i (Oracle e business Suite) has a feature called Multi-Organization, what brought a lot of benefits for an Organization which has multiple business units. Using the Multi-Organization enhancement, this kind of company will need only one single instance to support its business, keeping transactions data separate and secure.

This model allows you to support any number of business units on a single instance of any Oracle Applications product, even if those business units use different Set Of Books. Now we are able to define different structures to customize Oracle Applications according to our business need.

The basics business needs are:

• Sell products from a legal entity which uses one set of books and, ship them from another legal entity using a different set of books.
• Purchase products through one legal entity and receive them in another legal entity.

• Use a single installation of Oracle Applications product to support any number of organization
• Support any number of legal entities within a single installation of Oracle Applications.
• Secure access to data, so users can access only the information that is relevant to Them

The Oracle Applications multi-org structure is defined base on the picture Above

This structure is the first definition that has to be done when implementing Oracle e-Business suite. It has to be done carefully, since it is not possible to change after it is defined in the system. After the structure has already been defined, the Multi-Organization Company will be ready to use the single instance of Oracle Applications.

Oracle Base Tables Detail

General Ledger

Ø Base Tables

· gl_je_batches

· gl_je_headers

· gl_je_lines

· gl_je_sources

· gl_je_categories_tl

· gl_set_of_books

· gl_daily_rates

· gl_balances

· gl_periods

· gl_period_sets

· gl_code_combinations

Ø Interface Tables

· gl_interface

· gl_budget_interface

Inventory

Ø Base Tables

· mtl_system_items_b

· mtl_system_items_tl

· mtl_item_locations

· mtl_item_categories

· mtl_item_revisions_b

· mtl_parameters

· hr_all_organization_units

· cst_item_costs

Ø Interface Tables

· mtl_system_items_interface

· mtl_item_categories_interface

· mtl_item_revisions_interface

· mtl_interface_errors

Order Management

Ø Base Tables

· oe_order_headers_all

· oe_order_lines_all

· oe_order_sources

· oe_transaction_types_all

· oe_transaction_types_tl

Ø Interface Tables

· oe_headers_iface_all

· oe_lines_iface_all

· oe_actions_iface_all

Submit Concurrent Requests at the OS level

CONCSUB is a utility for allowing the Sysadmin user name and password to have the ability to submit concurrent requests at the OS level. This utility, unlike many of the Applications utilities, is not menu driven. It runs from the command line, submits a concurrent request, and returns you to the command prompt once the concurrent request completes. You can check the status of your concurrent request via the Concurrent Request form

Syntax

CONCSUB applsys/pwd 'responsibility application short name' 'responsibility name' 'username' [wait=] CONCURRENT 'Program application short name' PROGRAM

CONCSUB Parameters and Their Meanings

Applsys/pwd

Oracle application user name and password that connects to Applications Object Library.

Responsibility Application Short Name

Application short name of the responsibility you want to run the request for.

Responsibility Name

Name of the responsibility for which you want to run the request.

Username

User name of the person who is submitting the request.

Wait

Do you want CONCSUB to wait before returning the OS command prompt?

N (default value) waits until the job completes.

Y returns you to the command prompt.

"n" is the number of seconds to wait before it exits.

If this parameter is used, it has to come before concurrent.

Program Application Short Name

Short name of the program (for deactivate, abort, and verify, the program application short name is FND).

PROGRAM

The program to submit (e.g., DEACTIVATES, VERIFY, ABORT).

Tables Used by Concurrent Request Concurrent Program

FND_CONCURRENT_REQUESTS

Contains a complete history of all concurrent requests (both past history and those scheduled to run in the future).

FND_RUN_REQUESTS

Stores information about the reports in a report set that a user submits including the report set's parameter values.

FND_CONC_REQUEST_ARGUMENTS

Records all arguments passed by Concurrent Managers to concurrent requests as those requests are running.

FND_DUAL

Records when a request does not update any database tables.

FND_CONCURRENT_PROCESSES

Records information about Oracle Applications processes and OS processes.

FND_CONC_STAT_LIST

Collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY

Contains Concurrent Program performance statistics generated by the Purge Concurrent Request program or the manager data program. These programs use the data in FND_CONC_STAT_LIST to compute these statistics।