Implementing ERP – Selection to Rolling out


When implementing new a ERP system, implementing on time and on budget is not by chance. As with any project plan, a detailed and methodical process is required for success. A plan for selecting and implementing new ERP software is no different than any major project being undertaken by a corporation. It requires a significant investment of time and resources, requires the involvement of virtually the entire organization, as well as a considerable amount of research, planning, and reevaluation along the way.

The best project is well thought out and fully researched. It is not limited to a budget and time line, but focuses on tasks, owners, goals, and milestones. It begins at the time of software selection and goes well beyond go-live. And although most projects will stumble along the way, successful implementations that actually end on time and on budget are quite possible if managed properly.

we preach the importance of a solid selection and implementation plan. So much so, that we will voluntarily chose not to participate in the evaluation project if we feel the prospect’s selection process lacks structure. Why would we ever do that? Because the company will never be happy. If they do not know how to evaluate their own requirements or communicate those needs to others, they’ll never be able to successfully evaluate if a vendor’s ERP solution is a good fit for their unique requirements. And unfortunately if we can’t review their requirements with them, we cannot help evaluate if our package is a good fit either.

A solid evaluation project for new ERP software is broken out into some fairly set project goals and milestones. Regardless of the company, a proper project plan should include:

– A Review of Key Business Objects & Long-term Goals
– A Formation of a Selection Team & Project Leader
– An Ongoing Project Plan
– Research & Budget Preparation
– Clearly Defined Requirements & Project Goals
– Vendor Demonstrations & an RFP
– Reference Checks
– Data Migration
– Testing, Testing, & More Testing
– Training, Training, & More Training

In reality, you can easily execute on time, on task, and on budget by controlling the entire project through conception and go live. Led by in house or through an outside professional, flawless executions of software implementations are possible. They do not happen by chance, on their own, or without effort. But they are certainly a reality if given the right team and project plan.

Basic Unix Vi editor command


Unix Vi editor command keys:

ZZ

Q

<ESC>

:<cmd>

:!<cmd>

^g

^f

^b

^d

^u

<x>G

/<x>

?<x>

n

N

]]

[[

%

^l

^r

z<CR>

z-

^e

^y

H

L

M

+

hjkl

0

$

f<x>

F<x>

Exit, saving changes

Enter ex mode

End of insert

Execute ex command

Shell command

Show filename/size

Forward one screen

Back one screen

Forward half screen

Backward half screen

Go to line <x>

Search forward for <x>

Search backward for <x>

Repeat last search

Reverse last search

Next section/function

Previous section/function

Find matching () { or }

Redraw screen

Refresh screen

Current line at top

Current line at bottom

Scroll down one line

Scroll up one line

revious context

Home window line

Last window line

Middle window line

Next line

Cursor movement:

left/down/up/right

Beginning of line

End of line

Find <x> forward

Find <x> backward

t<x>

T<x>

<x>|

w,W

b,B

e,E

^h

^w

^?

~

a

i,I

A

o

O

r

R

d

dd

c

y

C

D

s

S

J

x

X

Y

p

P

<<

>>

u

U

Up to <x> forward

Back up to <x>

Go to column <x>

Forward one word

Back one word

End of word

Erase last character

Erase last word

Interrupt

Toggle character case

Append after

Insert before

Append at end of line

Open line below

Open line above

Replace character

Replace characters

Delete

Delete line

Change

Yank lines to buffer

Change rest of line

Delete rest of line

Substitute character

Substitute lines

Join lines

Delete after

Delete before

Yank current line

Put back lines

Put before

Shift line left

Shift line right

Undo last change

Restore current line

File Options:

sh      Invoke shell

vi      Vi mode

wq      Write and quit

w <f>   Write file <f>

w! <f>  Overwrite file <f>

Ex mode commands:

q

q!

r <f>

set <x>

set no<v>

set all

Quit

Quit, discard changes

Read in file <f>

Enable option

Disable option

Show all options

      
      

Oracle 10g Cost Based Optimizer


Hello Friends, here i like to give some brief on Cost based optimizer in Oracle, i am not covering Rule based optimizer as it is not much in use as well not recommended. plz share ur feedback or if u like to contribute more on this topic,

Oracle 10g Cost Based Optimizer

Whenever you execute a SQL statement, a component of the database known as the optimizer must decide how best to access the data operated on by that statement. To figure out the optimal execution path for a statement, the optimizers consider the following:

  • The syntax you’ve specified for the statement
  • Any conditions that the data must satisfy (the WHERE clauses)
  • The database tables your statement will need to access
  • All possible indexes that can be used in retrieving data from the table
  • The Oracle RDBMS version
  • The current optimizer mode
  • SQL statement hints
  • All available object statistics (generated via the ANALYZE command)
  • The physical table location (distributed SQL)
  • INIT.ORA settings (parallel query, async I/O, etc.)

Understanding the Cost-Based Optimizer

The information required by the cost-based optimizer is available once a table has been analyzed via the ANALYZE command, or via the DBMS_STATS facility. Plz note The ANALYZE command and the DBMS_STATS functions collect statistics about tables, clusters, and indexes, and store those statistics in the data dictionary.

The RDBMS kernel defaults to using the cost-based optimizer under a number of situations, including the following:

  • OPTIMIZER_MODE = CHOOSE has been specified in the INIT.ORA file, and statistics exist for at least one table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = CHOOSE command has been executed, and statistics exist for at least one table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (or ALL_ROWS) command has been executed, and statistics exist for at least one table involved in the statement
  • A statement uses the FIRST_ROWS or ALL_ROWS hint (e.g., SELECT /*+ FIRST_ROWS */. . .)

ANALYZE command

The way that you analyze your tables can have a dramatic effect on your SQL performance. If your DBA forgets to analyze tables or indexes after a table re-build, the impact on performance can be devastating. If your DBA analyzes each weekend, a new threshold may be reached and Oracle may change its execution plan. The new plan will more often than not be an improvement, but will occasionally be worse.

If you do want to analyze frequently, use DBMS_STATS.EXPORT_SCHEMA_STATS to back up the existing statistics prior to re-analyzing. This gives you the ability to revert back to the previous statistics if things screw up. When you analyze, you can have Oracle look at all rows in a table (ANALYZE COMPUTE) or at a sampling of rows (ANALYZE ESTIMATE). Typically, When a table is analyzed using ANALYZE, all associated indexes are analyzed as well. If an index is subsequently dropped and recreated, it must be re-analyzed. Be aware that the procedures DBMS_STATS.GATHER_SCHEMA_STATS and GATHER_TABLE_STATS analyze only tables by default, not their indexes. When using those procedures, you must specify the CASCADE=>TRUE option for indexes to be analyzed as well.

Following are some sample ANALYZE statements:

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS; ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

If you analyze a table by mistake, you can delete the statistics. For example:

ANALYZE TABLE EMP DELETE STATISTICS;

Analyzing can take an excessive amount of time if you use the COMPUTE option on large objects. We find that on almost every occasion, ANALYZE ESTIMATE 5 PERCENT on a large table forces the optimizer make the same decision as ANALYZE COMPUTE.

Inner workings of the cost-based optimizer

The cost-based optimizer is flexible and can adapt to its environment. This adaptation is possible only once the necessary underlying object statistics have been refreshed (re-analyzed). What is constant is the method by which the cost-based optimizer calculates each possible execution plan and evaluates its cost (efficiency).

The cost-based optimizer’s functionality can be (loosely) broken into the following steps:

1.       Parse the SQL (check syntax, object privileges, etc.).

2.       Generate a list of all potential execution plans.

3.       Calculate (estimate) the cost of each execution plan using all available object statistics.

4.       Select the execution plan with the lowest cost.

The cost-based optimizer will be used only if at least one table within a SQL statement has statistics (table statistics for unanalyzed tables are estimated). If no statistics are available for any table involved in the SQL, the RDBMS will resort to the rule-based optimizer, unless the cost-based optimizer is forced via statement-level HINTS or by an optimizer goal of ALL_ROWS or FIRST_ROWS.

To understand how the cost-based optimizer works and, ultimately, how to exploit it, we need to understand how it thinks.

Primary key and/or UNIQUE index equality —A UNIQUE index’s selectivity is recognized as 100%. No other indexed access method is more precise. For this reason, a unique index is always used when available.

Non-UNIQUE index equality —For non-UNIQUE indexes, index selectivity is calculated. The cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option of the ANALYZE. That option will make the cost-based optimizer aware of how the data in the indexed columns is skewed.

Range evaluation —For index range execution plans, selectivity is evaluated. This evaluation is based on a column’s most recent high-value and low-value statistics. Again, the cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option when analyzing the table.

Range evaluation over bind variables—For index range execution plans, selectivity is guessed. Prior to Oracle9i, because bind variable values are not available at parse time (values are passed to the cursor after the execution plan has been decided), the optimizer cannot make decisions based on bind variable values. The optimizer assumes a rule of thumb of 25% selectivity for unbounded bind variable ranges (e.g., WHERE dept_no = :b1) and 50% selectivity for bounded ranges (WHERE dept_no > :b1 AND dept_no < :b2). Beginning with Oracle9i, the cost-based optimizer obtains bind variable values prior to determining an execution plan.

System resource usage —By default, the cost-based optimizer assumes that you are the only person accessing the database. Oracle9i gives you the ability to store information about system resource usage, and can make much better informed decisions based on workload (read up on the DBMS_STATS.GATHER_SYSTEM_STATS package).

Current statistics are important —The cost-based optimizer can make poor execution plan choices when a table has been analyzed but its indexes have not been, or when indexes have been analyzed but not the tables.

Distinction between configuration, personalization, and extensibility.


Distinction between configuration, personalization, and extensibility.

Configuration provides setup and administrative choices using the native features of the product. Some configuration examples include:

  • Profile Options
  • User-defined fields (Flexfield)
  • Function Security Setup
  • Data Security Setup
  • and a lot more….

Personalization enables you to declaratively tailor the UI look-and-feel, layout or visibility of page content to suit a business need or a user preference. Some personalization examples include:

  • Tailor the order in which table columns are displayed.
  • Tailor a query result.
  • Tailor the color scheme of the UI.
  • Folder Forms
  • Forms Personalization
  • Oracle Application Framework (OAF)

Extensibility is about extending the functionality of an application beyond what can be done through personalization. Some extensibility examples include:

  • Add new functional flows
  • Extend or override existing business logic
  • Using Oracle Forms Developer, Oracle JDeveloper and Oracle Workflow

Oracle Applications – Technical Interview Questions


Hello Friends, Upon requests on some of my friends, i am posting few more generic oracle applications’ questions. hope this will give you overview of generic features. do share your feedback if these are good n useful.

what is a flexfield qualifier ?

Oracle Applications products use flexfield qualifiers to identify certain segments used for specific purposes. eg: segment named ‘Account’ is qualified as ‘Natural Account’so that it identifies Accounts, similarly a segment named ‘Company’ can be qualified as ‘Balancing Segment’, i.e, for these segment values B/S can be brought out in Accounting Flexfield in GL.

A flexfield is made of segments and oracle identifies if particular segment in the accounting flexfield is an account segment or a company segment, based on some predefined values called as flexfield qualifiers which are assigned to the segments to look meaningful for defining the accounting flexfield. Eg: Account segment uses Natural Account segment and the company segment uses Balancing segment are mandatory for defining the accounting flexfield. Other values are Cost center segment, Inter company segment, secondary tracking segment

What is the difference between configuration, conversion, and customization?

Conversion ,customization and configuration are part of an oracle implementation project they come in this order

1. configuration/set up

2. Customization

3. Conversion

Configuration is setting up customer specific requirement which generally exist in oracle apps customization is filling the gap between oracle apps and Customer requirement by developing some extension and solution design

Customization is relatively different thing, something that is not a part of oracle apps core product but the client requirement demands it we go for Customization. If the functionality demanded by the client exists in the application, it is to be configured according to his needs by taking some data, this is called configuration

Conversion is structural change of customer data in a form which is compatible with oracle database best of luck. Conversion is the process of mapping the tables from a legacy system to apps system. Sometimes conversions can be from one version to other

Where we can check the status of PO.

In po_headers_all authorization_status coloumn is their we can find through this coloumn

What is back order in OM

There are few reasons that a scheduled order could be Backordered during pick release process.

1. In-sufficient onhand

2. Order might be put as ‘Pick release hold’

3. If it’s a lot controlled item being involved then some of the factors will be checking at picking rule setup (like Lot- expiration date, etc) and tured to ‘Backordered’ incase the criteria does not meet.

Where we find the status of order information.

Order header status is in oe_order_headers_all table reference with flow_status_code column and order line status would be oe_order_lines_all table reference with flow_status_code column.

What are the tables of auto invoice?

RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL ARE THE TWO INTERFACE TABLES MAINLY USED BT AUTOINVOICE.  FURTHER DATA READ FROM THESE TABLES WILL BE INSERTED INTO RA_CUSTOMER_TRX_ALL , RA_CUSTOMER_TRX_LINES_ALL , RA_CUST_TRX_LINE_GL_DIST_ALL , AR_PAYMENT_SCHEDULES_ALL AFTER VALIDATING.

For report i have to parameters those are from_date and to_date ,so to_date should be greater when compare to from_date ,if we are giving to_date is less then it must shows some error how we will make it.

While defining Parameters you can set range option(in concurrent prog parameters window). Select low for from_Date and high for to_Date.Then it will not allow for from_date > to_date.

How to find the custom directory in front end.

From Application Developer responsibility, navigate to Application –> Register. Query for the custom application name. The value in the field Basepath, is the OS system variable that stores the actual directory info.

What are the execution methods in oracle apps?

1. Host

2. Immediate

3. Java Stored Procedure

4. Java Concurrent Program

5. Multi Language Function

6. Oracle Reports

7. Request Set Stage Function

8. Pl/Sql Stored Procedure

9. Spawned

10. Sql*Lodar

11. Sql*Plus

12. Perl Concurrent Program

What are APIs

API stands for application program interface. Oracle has its own api facility to transfer data from a external source to oracle base table so api is an standard oracle tool to transfer data to oracle database. It is a very simple method and a person with no technical background also can use api. Example-uploading journal and budget data through API.

Thanks – plz share questions if you have more in this area. also share your comment/feedback. Shivmohan Purohit

Oracle – Technical FAQ – Part 4


 Q18.) What are the OOPS concepts in Oracle?

A18.) Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorise the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects.

 

Q19) What is the difference between candidate key, unique key and primary key?

A19) Candidate keys are the columns in the table that could be primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.

 

Q20) What is concurrency?

A20) Concurrency is allowing simultaneous access of same data by different users. Locks useful for accessing the database are

a) Exclusive — The exclusive lock is useful for locking the row when an insert , update or delete is being done. This lock should not be applied when we do only select from the row.

b) Share lock — We can do the table as Share_Lock as many share_locks can be put on the same resource.

 

Q21) What are the Previleges and Grants?

A21) Privileges are the right to execute a particular type of SQL statements. The grant has to be given by the owner to object.

 

Q22) What are Table Space, Data files, parameter file, control files?

A22) Table space: The table space is useful for storing the data in the database. When a database is created two table spaces are created.

a) System Table space : This data file stores all the tables related to the system and dba tables.

b) User Table Space: This data file stores all the user related tables.

We should have separate table spaces for storing the tables and indexes so that the access is fast.

Data files : Every oracle Data base has one or more physical data files. They store the data for the database. Every datafile is associated with only one database. Once the data file is created the size can not change. To increase the size of the database to store more data we have to add data file.

Parameter file:Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g. db_block_buffers = 500

db_name = ORA7

db_domain = u.s.acme lang

Control files : Control files record the physical structure of the data files and redo log files . They contain the DB name, name and locations of dbs , data files, redo log files and time stamp.

 

Q23) What is physical storage of the Data?

A23) The finest level of granularity of the data base are the blocks.

Extent : Extent is the number of specific number of contigious data blocks.

Segments : Set of Extents allocated for Extents. There are three types of segments

a) Data Segment : Non clustered tables has data segment data of every table is stored in cluster data segment.

b) Index Segment : Each index has index segment that stores data.

c) Rollback Segment : Temporarily store ‘undo’ information.

 

Q24) What is Normalisation ? Explain 1NF, 2NF, 3NF.

Normalization is a process of eliminating data redudancy.

1NF- No repeating groups.

2NF- Eliminates the functional dependency on the partial key field.

3NF- Eliminates the functional dependency on the non-key field.

Oracle – Technical FAQ – Part 2


Q6) What are the database triggers and stored procedures?
A6) Database triggers are procedures that are automatically executed as a result of insert in, update to or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which can not be enforced using the integrity rules. We can have the trigger as before trigger or after trigger and at statement or row level.
e.g. operations insert, update, delete — 3
before, after 3 * 2 — a total of 6 combinations

At statement level (once for the trigger) or row level (for every execution) 6 * 2 — a total of 12.

Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 onwards

Stored Procedures : Stored procedures are procedures that are stored in complied form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

 

Q7) How many integrity rules are there and what are they?

A7) There are three integrity rules and they are as follows

a)Entity integrity Rule : The entity integrity rule enforces that the primary key can not be Null

b) Foreign key integrity rule : The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in child tables the master tables can not be deleted.

c) Business integrity rules : The third integrity rule is about the complex business processes which can not be implemented by the above 2 rules.

 

Q.8.) What are the various Master and Detail relationships?

A.8.) The various Master and Detail relationship are

a) Non Isolated : The Master can not be deleted when a child is existing

b) Isolated : The master can be deleted when the child is existing

c) Cascading : The child gets deleted when the master is deleted.

 

Q9) What are the various Block Coordination properties ?.

A9) The various block coordination properties are

a) Immediate – Default setting. The detail records are shown when the Master Record are shown.

b) Differed with AutoQuery — Oracle forms defer fetching the detail records until the operator navigates to the detail block.

C) Deffered with no auto query — The operator must navigate to the detail block and explicitly executes a query.

 

Q10) What are the different Optimisation techniques ?

A10) The various optimisation techniques are

a) Execute plan : We can see the plan of the query and change it accordingly based on the indexes

b) Optimizer_hint : Set_item_property (‘DeptBlock’,OPTIMIZER_HINT,’FIRST_ROWS’);

Select /* + First Rows */ Deptno, Dname, Loc, Rowid from dept

where (Deptno > 25)

c) Optimize_sql : By setting the optimize_sql = No, Oracle Forms assign a single cursor for all SQL statements. This slow downs the processing because for everytime the SQL must be parsed whenever they are executed. f45run module = my_firstform useid = scott/tiger optimize_Tp = No

 

  more questions are here : https://knoworacle.wordpress.com/2008/06/08/oracle-technical-faq-part-2/

Part 3 is here :   https://knoworacle.wordpress.com/2008/06/09/oracle-technical-faq-part-3/

Part 1 is here : https://knoworacle.wordpress.com/2008/08/11/oracle-technical-faq-part-1