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 life cycle. 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

Order Management Integration With Oracle Inventory Module

Order Management integrates with Oracle Inventory Management in the following areas:

Managing reservations

Order Management calls Inventory’s reservation APIs to manage reservations. You can create reservations to on-hand quantities from the Sales Orders form. You can also go to Inventory reservation form the Sales Orders form and create a reservation to any level of inventory

(Sub-inventory, locator, lot).When supply is created for an ATO configuration (for a configuration item); it is reserved to a Work Order. This reservation gets transferred to on-hand

When the work order is completed, thus reserves the Order line to on-hand. For every Order created, Order Management creates a record in MTL_SALES_ORDERS, an entity that Inventory uses to manage demand from various sources. Reservation information is stored in MTL_RESERVATIONS. Reservation records for order Lines point to both MTL_SALES_ORDERS and OE_ORDER_LINES_ALL.

Customer-Item Cross reference

You can use Oracle Inventory to set-up Customer Items and Customer Item Cross reference information. You can then place orders using those pre-defined customer item identifiers. Order Management calls the Inventory API INV_CUSTOMER_ITEM_GRP to derive the internal item based on the specified customer item, ship-to site and the warehouse on the Order Line.

The Item Identifier Type on the Order Line indicates the Cross-reference Type that was used for placing the order. The Ordered Item tracks identifier that was used to place the order.

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).

How to change Message Displayed on invalid login Information

We are going to change the message displayed in e business suite on invalid login information

Login by Sys Admin Login and Navigate to Application Developer Responsibility

Menu Navigation

Application —-> Messages

Query for “FND_APPL_LOGIN_FAILED” Change the Message Display in Current Message Text

After Saving Record Reboot application tear services.

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 to Implement Function Security for Orders and Returns in Order Management 11.5.10.2

Use function security to control user access to functions in the Order Organizer and Sales Orders window. Your system administrator customizes a responsibility at your site by including or excluding functions and menus in the Responsibilities window.

The form functions listed below are available by default, but may be excluded from menus tied to restricted responsibilities:

• Sales Orders

• Order Organizer

• Order Organizer View

Note: The functions Returns: Enter and Returns: View are available for backward compatibility with older releases of Oracle Order Entry. Do not use these for new installations. If you exclude Sales Orders, Order Organizer, and Order Organizer View from a responsibility, that responsibility’s users can neither access the Sales Orders window by selecting from the Navigator menu nor query orders from the Orders Organizer. If you exclude all three functions, you should also remove the Orders, Returns menu item from the Navigator.

• Sales Orders and Order Organizer gives you the right to view, enter or modify orders and returns.

• Order Organizer View only enables you to view the orders in the Sales Orders window from the Order Organizer.

Additionally, utilizing standard form functional security, you can restrict actions that a user can perform in the Sales Order window by adding or removing these functions from the menu associated with a responsibility. Below lists the following actions that can be restricted using this feature.

Apply Holds

Authorize Payment

Book Order

Calculate Tax

Cancel Orders

Charges

Configurations (Link, Delink, Match & Reserve)

Copy Orders

Gross Margin

Mass Change

Send Notifications

Price Orders

Progress Order

Release Holds

Sales Credits

Schedule Orders

Function Security Example

Your company employs some individuals whose tasks include viewing orders and returns. They do not enter orders or returns.

Before Applying Any Rule for user Ahmad Bilal

Now we will process function security rule for User Ahmadbilal

1. Navigate to the Responsibilities window. Query an existing responsibility “Order Management Super User, Vision Operations (USA)” whose functionality you want to limit, or define a new one.

In the Function and Menu Exclusions block, choose Function as the type of exclusion Rule to apply against the responsibility. Here we have restricted two function within this responsibility

a. Book Order

b. Price Order

And save work

Again switch back to Order Management Super User, Vision Operations (USA) Responsibility and query and existing order with status entered

Look in below picture there is no book Order Button and price order also disappear from action list

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 determine XML Publisher and Business Intelligence Publisher Version in oracle eBusiness Suite

SQL to get the version of XML Publisher / BI Publisher that you are running in the Oracle eBusiness Suite:

SELECT DECODE (bug_number,

‘3554613′, ‘4.5.0′,

‘3263588′, ‘XDO.H’,

‘3822219′, ‘5.0.0′,

‘4236958′, ‘5.0.1′,

‘4206181′, ‘5.5.0′,

‘4561451′, ‘5.6.0′,

‘4905678′, ‘5.6.1′,

‘5097966′, ‘5.6.2′,

‘5472959′, ‘5.6.3′

) patch,

bug_number

FROM ad_bugs

WHERE bug_number IN

(‘3554613′,

‘3263588′,

‘3822219′,

‘4236958′,

‘4206181′,

‘4905678′,

‘5097966′,

‘5472959′,

‘4561451′

);

Best Pictorial Representation For SCM

Setup Steps and Considerations Common to Oracle Purchasing and Payables

Some setup steps are common to both Oracle Purchasing and Oracle Payables. First, we will discuss the common setup steps and considerations. The common setup steps are as follows:

· System administrator

· Other modules flex fields

· Accounting calendars

· Workday calendar

· Currencies

· Conversion rates

· Set of Books

· Opening and closing accounting periods

· Organizations

· Human resources

· Tax names

· Locations

· Suppliers

System Administrator

Setting up system administrator functions is a required task, and involves setting up system security, defining user names, and defining system printers. User IDs must be defined for all users, and additional responsibilities may also need to be defined. The Oracle System Administrator responsibility will need to be set up to perform this task.

Considerations involved in this step are security and establishing naming conventions to be used throughout Oracle Applications. You must determine which responsibilities should be tied to which users and whether to exclude forms and functions from the standard menu. You must also establish a naming convention for printers and set them up. This lets users know where the printer will be located by simply reading the printer name. You must also determine whether a dedicated printer will be necessary for checks and purchase orders, which usually have special preprinted forms.

Other Module Key Flexfields

This step involves setting up other modules’ Key flexfields related to Oracle Purchasing and Oracle Payables. These Key flexfields include accounting, human resources, and inventory. The accounting Key flexfield must be defined through the General Ledger setup and configuration. Next, set up the four human resources Key flexfields: job, position, grade, and people group. You needn’t set up these flexfields if you already have installed Oracle Human Resources, or if you’re installing in a common application setup (in which case, it will be done during the HR setup). The following inventory Key flexfields should be defined as part of the inventory setup: item catalog, item category and system items. Once you have defined the system items flex field, you must run the Item Flex field view.

Considerations involved in this step involve constructing Key flex fields that have room to grow, that can be used across modules, and that can be validated according to everyone’s need. You must also determine how to maintain valid values, whether to allow dynamic insertions, and how to enforce security.

Accounting Calendars

If you have not defined your accounting calendars in Oracle General Ledger, then you must define them for Oracle Purchasing and Oracle Payables. If you are performing a multi-org implementation of Purchasing and Payables, you may also create more than one calendar.

Whether you need an adjusting period and how to name your accounting period are the two considerations for this setup step. Your accounting calendar is dictated by your business needs, and all Oracle Applications modules share the same accounting calendar, yet they maintain a separate status for each module. For example, an open period in Payables can be a closed period in Receivables.

Workday Calendar

If you have not defined your workday calendars in Oracle Inventory, you must define them for Oracle Purchasing and Oracle Payables. If you are performing a multi-org implementation of Purchasing and Payables, you may also create more than one calendar.

How to name your inventory period and how to create an exception template to filter out nonworking days are the two considerations for this setup step. Your workday calendar is dictated by your business needs, and all Oracle Applications modules share the same workday calendar.

Currencies

If you are planning to use the multi currency features of Oracle, you must also enable the currencies you need. This step is done in Oracle General Ledger.

Determining which currencies you need is the only consideration for this setup step. Oracle Applications has all ISO currencies, but if you have a currency that is not defined, you must define it in Oracle General Ledger.

Conversion Rates

If you plan to use Oracle’s multi currency features, you must also define conversion rate types, daily rates, period rates, and historical rates in Oracle General Ledger.

Determining what kind of exchange rates to use and who will maintain the exchange rates for your company are the two considerations for this setup step.

Set of Books

At least one Set of Books must be defined in Oracle General Ledger in order to use the other modules. Once you have created the Sets of Books, assign responsibilities of Oracle Purchasing and Oracle Payables to the defined Sets of Books.

Considerations for this step, which include whether you want to perform suspense accounting, intercompany accounting, and budgetary control, among others, are covered in the first part of this book.

Opening and Closing Accounting Periods

Next, you must open the appropriate accounting period in Oracle General Ledger. Whether you should open and close the accounting periods is the only consideration for this step. If you open the accounting period, accounting entries and transactions can be entered for the opened accounting period. Open the appropriate inventory and purchasing periods, and open purchasing periods for which you have purchasing activity. Oracle Purchasing will only create journal entries for transactions in an open purchasing period, including creating the correct receipt accrual journal entry. Use the Control Purchasing Periods form to open/close purchasing periods.

Organizations

If you use expense reporting in Oracle Payables, you must define one or more organizations before using Oracle Purchasing and Oracle Payables. Organizations describe and define unique entities within your company, such as manufacturing plants, warehouses, distribution centers, and sales offices. Oracle Purchasing fully supports both centralized and decentralized purchasing for multiple receiving organizations. You can change your current receiving organization at any time with the Change Organization form.

There are multiple considerations for this step. You must determine your company structure, and what kind of security precautions to enforce. In other words, define how you want to segregate data and how you want personnel from one organization to interact with data from another organization. You also need to determine whether you want to centralize or decentralize each Oracle Applications module.

Human Resources

You will need to set up personnel, or human resources information if Human Resources is not installed. This step involves defining employee lookup codes, supplier and employee numbering, positions, position hierarchies, and employees. You must define employees when you define buyers, which is a required step for Oracle Purchasing. You must also define positions, position hierarchies, and employees if you will be using hierarchies for approvals and workflow.

How you want to number your employees and approve purchasing documents are the two considerations for this step. Approving purchase orders can be very complicated, and you can use a purchasing hierarchy for this task. A purchasing hierarchy is a position hierarchy that you can use to determine the relationship between employees when Oracle Purchasing tries to get the next level of approval.

Tax Names

Until now, all common setups have been performed in Oracle Applications modules, with the exception of the procurement modules. The remaining common setup steps are specific to Oracle Purchasing and Oracle Payables. To get to the Setup form, you must sign on as the Operation user and select the Purchasing, Vision Operations (USA) responsibility. The tax name, which you can use when entering tax amounts in payables invoices, defines a specific tax rate and tax account. You must define tax names for each operating unit. To set up tax names, you follow the navigation path Setup : Financials : Accounting: Taxes.

The only consideration for this step is that the tax name you choose be sufficiently descriptive when your users select it within the Payables invoices.

Locations

For purchasing, you must set up locations, which can be defined as ship-to, bill-to, office, internal, or receiving sites. You can follow the navigation path Setup : Organizations: Locations to set up locations.

Suppliers

Suppliers can be created and maintained in Oracle Purchasing or Oracle Payables. You define the suppliers, the supplier sites, and the supplier contacts. Deciding who should maintains suppliers, since you do not want two departments within your organization to duplicate maintenance of the supplier master, is the primary consideration for this step. You must also establish and maintain a naming convention so that your users know how to select existing suppliers.

Oracle Purchasing with Other Oracle Applications Modules

Oracle Purchasing integrates with a number of other Oracle Applications modules, including:

· Oracle Order Entry

· Oracle Inventory

· Oracle Work flow

· Oracle Material Resource Planing

· Oracle Projects

· Oracle Human Resources

· Oracle Alert

· Oracle Quality

· Oracle EDI Gateway

· Oracle General Ledger

Oracle Order Entry

Once the internal requisition has been approved and interfaced to Order Entry, the sales order must proceed through the order cycle. This includes pick release, ship confirm, and inventory interface processing (not Supplier), which can automatically create internal sales orders in Oracle Order Entry. Internal sales orders are those that are ordered from another organization within your own company. The organization that fulfills the order is the selling organization, while the organization with the purchase requisition is the buying organization.

Oracle Inventory

Purchasing increases Inventory’s on-hand balance when POs are received and delivered. Internal requisitions, mentioned above, also affect demand once the item has been reserved and depletes the shipping organization’s on-hand balance once it has been shipped.

Oracle Work flow

There are two areas where Oracle Purchasing integrates with Oracle Work flow. The first is Account Generator, with a work flow process that defines the business rules in which account combinations are generated. The second area consists of some seeded approval work flow processes. These work flow processes read the approval business rules and hierarchies you set up in Oracle Purchasing, and they drive purchasing documents such as purchase requisitions and purchase orders through defined approval business rules and hierarchies.

Oracle Material Resource Planing

The material planning process in Oracle Material resource planing automatically generates purchase requisitions for raw materials (goods) or outside processing (services).

Oracle Projects

When you buy goods and/or services for a particular project, Oracle Purchasing, which allows you to capture project information on purchasing documents, integrates with Oracle Projects.

Oracle Human Resources

Oracle Purchasing shares employees and positions information with Oracle Human Resources. The two also share position hierarchies if approvals in Oracle Purchasing and Human Resources are set up to use the same position hierarchies.

Oracle Alert

Oracle Purchasing has some seeded alerts to help monitor exceptions and buyer performances, get notified with releases and expirations against planned and/or blanket purchase orders, and identify potential forecast overconsumption.

Oracle Quality

You can capture quality data, which will be automatically stored in Oracle Quality, during receiving. You can also make the capturing of quality data mandatory in Oracle Purchasing.

Oracle EDI Gateway

Oracle EDI gateways offers EDI capability to Oracle Purchasing. Supported purchasing EDI transactions in Oracle EDI gateways are Inbound Price/Sales Catalog (832), Inbound Response to Request for Quote (843), Inbound Ship Notice/Manifest (856), Inbound Shipping and Billing Notice (857), Outbound Planning Schedule (830), Outbound Shipping Schedule (862), Outbound Purchase Order (850), and Outbound Purchase Order Change request (860).

Oracle General Ledger

Oracle Purchasing posts encumbrance entries and receipt accrual entries to Oracle General Ledger.

Fulfilling and Invoicing Full and Partial Drop-Ship Orders

Once proof of shipment for drop-shipped orders has been received from the vendor, use Oracle Purchasing to enter a receipt. Proof of shipment can be manual, such as a fax or phone call, or by ASN (advance shipment notice) through EDI Gateway. Items are received into the receiving location of the receiving organization against the PO related to the drop-ship. Upon receipt, the drop-ship sales order line status will be updated to either Partial or Confirmed, depending on whether the received quantity is the same as the ordered quantity. If it is under received, the status will be Partial. If it is completely received, the status will be Confirmed. Oracle Order Entry does not handle over shipment, so if more product is shipped than ordered, the situation must be handled manually. Manual invoices can be created to bill the surplus quantity—or, you can ask the customer to return the surplus.

When the status of the purchase release becomes Partial or Confirmed, your drop-shipment order lines are ready for invoicing and the cycle action Receivables interface—which pushes the order/return information towards Oracle Receivables—has an Eligible result. The Receivables interface program can be run manually from Order Entry, or you can set it to run at particular intervals or at a certain time of the day. Follow the navigation path Orders, Returns : Receivables Interface. This program should be run at least once prior to running auto invoice, which is run in Oracle Receivables to create the actual invoices. If the Receivables interface is not run in Oracle Order Entry, then no invoices of source Order Entry will be picked up when auto invoice is run in Oracle Receivables. The Receivables interface works the exact same way for drop-ship orders as it does for regular orders.

Once the Receivables interface is completed, your drop-shipment order information can be closed. It should have reached the following order cycle action and result: Complete Order and Eligible at the order level, and Complete Line and Eligible at the line level. All order cycles must be set up correctly, and all lines must be completed and closed out or the order will not complete or close.

You can run the Close Orders program manually from Oracle Order Entry, or it can be set to run at particular intervals or at a certain time of the day. Follow the function patch Orders, Returns : Close Orders. The Close Orders program speeds up the various forms and greatly reduces printed lines on some standard Oracle reports by closing the ready to be closed open orders/lines. However, if all lines and the order/return are closed, then you cannot add items or make any changes.

How and When COGS account hit for transactions

Inventory Interface generates the cost of goods sold account for transactions when passing the transactions to Oracle Inventory. Receivables Interface generates a receivable account, a revenue account, a tax account, a freight account, and others, but not the cost of goods sold account. Ship confirm and pick release do not generate any accounts.

The explain plan command

The explain plan command

For select, update, delete, and insert statements, the explain plan command generates and stores information about the execution plan chosen by the query optimizer in a table called PLAN TABLE. This table is created when you run the SQL script $ORACLE HOME/rdbms/admin/utlxplan.sql in SQL*Plus. The PLAN TABLE then can be queried using a select statement.

The syntax of the explain plan command is as follows:

explain plan set statement id = ’’ for ;

Example: We want to generate the execution plan for the query “List the name, job, salary and department of all employees whose

salary is not within the job’s salary range.”

explain plan set statement id = ’MYPLAN’ for select ENAME, JOB, SAL, DNAME from EMP, DEPT where EMP.DEPTNO = DEPT.DEPTNO and not exists (select _ from SALGRADE where EMP.SAL between LOSAL and HISAL);

Note that every execution plan gets a statement id, which is just some string. The rest is just a “normal” select statement.

The above explain plan statement causes the query optimizer to insert data about the execution plan for the query into PLAN TABLE. The following query can be used to view to content of the plan table in an indented format, which reflects the tree structure of the plan (the right-most entries correspond to the leafs of the query tree).

Note that before creating a new plan with the same id, you have to delete all entries from PLAN TABLE (delete from PLAN TABLE

select substr(lpad(’ ’,2*(level-1)),1,8)|| substr(operation,1,1 “OPERAsubstr(options,1,12) “OPTIONS”, substr(object_name,1,16) object_name, id, parent_id, cost, cardinality, bytes, filter_predicates

from plan_table

start with id=0 and statement_id = ’MYPLAN’

connect by prior id = parent_id and statement_id = ’MYPLAN’;

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#

Period End Processing In Oracle Order Management Release 11.5.10.2

Make sure that following transactions have been entered for the period being closed:

1. Enter all Orders For that Particular period
2. Schedule Orders (If you are using Reservation On Basis of Scheduling)
3. Enter Return Material Authorizations (RMA)