, , , ,

SQL*Loader Basics

SQL*Loader Basics

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. It has a powerful data parsing engine that supports data present in any format in the data files. It can load data from multiple datafiles during the same load session as well as can load data into multiple tables during the same load session. SQL*Loader can manipulate the data before loading it, using SQL functions.

SQL*Loader’s Capabilities:

SQL*Loader can read from multiple input files in a single load session.
SQL*Loader can handle files with fixed-length records, variable-length records, and stream-oriented data.
SQL*Loader supports a number of different datatypes, including text, numeric, zoned decimal, packed decimal, and various machine-specific binary types.
Not only can SQL*Loader read from multiple input files, but it can load that data into several different database tables, all in the same load session.
SQL*Loader allows you to use Oracle’s built-in SQL functions to manipulate the data being read from the input file.
SQL*Loader includes functionality for dealing with whitespace, delimiters, and null data.
In addition to standard relational tables, SQL*Loader can load data into object tables, varying arrays (VARRAYs), and nested tables.
SQL*Loader can load data into large object (LOB) columns.
SQL*Loader can handle character set translation between the input data file and the database.

/by
, , ,

A.I.M. Methodology Summary

Application Implementation Method is a proven approach for all the activities required to implement oracle applications.
The scope of the AIM is focused on an enterprise as a whole.
There are eleven processes of implementation.
1. Business Process Architecture [BP]
This phase outlines:

  • Existing business practices
  • Catalog change practices
  • Leading practices
  • Future practices

2. Business Requirement Definition[RD]
This phase explains about the initial baseline questionnaire and gathering of requirements.
3. Business Requirement Mapping[BR]
In this phase the requirements of business are matched with the standard functionality of the oracle applications.
4. Application and Technical Architecture [TA]
This outlines the infrastructure requirements to implement oracle applications.
5. Build and Module Design [MD]
This phase emphasizes the development of new functionality (customization) required by the client. It mainly details how to design the required forms, database and reports.
6. Data Conversion [CV]
Data Conversion is the process of converting or transferring the data from legacy system to oracle applications.
Ex. Transferring customer records from the legacy to the Customer Master.
7. Documentation [DO]
Documentation prepared per module that includes user guides and implementation manuals.
8. Business System Testing [TE]
A process of validating the setup’s and functionality by QA(functional consultant) to certify status.
9. Performance Testing [PT]
Performance testing is the evaluation of transactions saving time, transaction retrieval times, workflow background process, database performance, etc…
10; Adoption and Learning [AP]
This phase explains the removal of the legacy system and oracle application roll out enterprise wide.
11. Production Migration [PM]
The process of “decommissioning” of legacy system and the usage(adoption) of oracle application system.

/by
, , ,

Oracle Apps Fundamentals

Three environments, each with a different URL and different database instance. These are

  • Development environment; where developers usually have System Administrator responsibility and also the apps password. If not System Administrator then at least they should have access to “Application Developer” responsibility.

  • Testing environment; Developers usually do not and must not have apps password to this environment . This is where users sign-off customizations or even setup changes.
  • Production environment; This is where the business runs

When you logs in the user is authenticated against a table named fnd_user for the username and password.
Oracle internally uses a login named GUEST, prior to invoking validation of actual username. Oracle uses a DB User account named applsyspub to which it first connects during validation of LOGIN. This user account has very restricted privileges and has access to below objects (primarily for authentication purposes):-
  • FND_APPLICATION

  • FND_UNSUCCESSFUL_LOGINS
  • FND_SESSIONS
  • FND_PRODUCT_INSTALLATIONS
  • FND_PRODUCT_GROUPS
  • FND_MESSAGES
  • FND_LANGUAGES_TL
  • FND_APPLICATION_TL
  • FND_APPLICATION_VL
  • FND_LANGUAGES_VL
  • FND_SIGNONFND_PUB_MESSAGE
  • FND_WEBFILEPUB
  • FND_DISCONNECTED
  • FND_MESSAGE
  • FND_SECURITY_PKG
  • FND_LOOKUPS

  1. All the pl/sql packages will be created in APPS Schema

  2. All the views will be created in APPS Schema
  3. For each table in individual schema, there will exist one synonym in APPS schema
  4. Tables are not created in APPS schema, every implementation has at least 1 custom schema, where custom tables are created.
  5. For each custom table created by you, you will need to create a Synonym in APPS schema

Custom tables are generally required in Oracle ERP because:
1. You wish to create a custom screens ( your own screen to capture some info) for a functionality that is not delivered by Oracle.
2. Pre-Interface tables
3. Temp processing
4. Staging of data for third party extract interfaces

To create a new table named, the steps are
Step 1. Create table in custom schema .
Step2. Grant all on schema.table_name to apps
Step 3. connect to apps/apps, and
Create or replace synonym for schema.table_name

Oracle Apps Deployment
  • Any executable that has intense database operations is stored at database tier e.g. oracle reports, sql files, sql*loader.

  • Any executable that has intense UI operations is deployed at forms tier. Examples are Oracle Forms fmx files, jsp files, pll/plx etc.
  • A form that has intense database processing, must handle most of the database processing within pl/sql packages. The api’s that you build in pl/sql must have well defined parameters.
  • Multiple mid-tiers distributes the user load. The user requests are first sent to a load balancer switch, this switch the decides which middle tier to use.
  • You will need to deploy your forms file to each middle tier machine (unles shared APPL_TOP) has been implemented.
  • FMB files delivered by oracle are picked from $AU_TOP/forms/us.
  • All of the pl/sql packages are installed in apps schema.This includes your custom packages and also oracle delivered packages.
  • The forms are attached to form functions and it is the form function that is attached to an application
  • To generate CUSTOM.pll: cd $AU_TOP/resourcef60gen module=$AU_TOP/resource/CUSTOM.pll userid=apps/apps output_file=./CUSTOM.plx module_type=LIBRARY

Optional environments:-
  • CRP environment: Conference room pilot environment is where usually implementation team gets buying to their product offering from user community, during implementation. This environment is usually used for sign off during new implementations.

  • Patching environment: This is where all new Oracle Patches are sanity tested.
  • Support environment: This environment is exclusive to support staff. This environment is usually the most frequently cloned environment in Oracle Apps site that has gone live. Frequent cloning helps the Oracle Applications support staff to reproduce production issues
  • Migration Environment: This is where repeated data migration can take place before migration code gets frozen and ready for UAT. 

/by
, , , ,

Overview of Standard Request Submission

Standard Request Submission is a feature that works with concurrent processing to provide a common interface for running your Oracle Applications reports and programs.
Standard Request Submission provides you with a set of windows for running reports and programs and a set of windows for creating groups of reports and programs to run together. These windows give you control over the submission and output of your reports and programs.

Attention: Some Oracle Applications products provide, in addition to the Standard Request Submission windows, product-specific windows to submit certain reports and programs. Such a window automatically runs a report or a program as a concurrent process when you choose a specific button or save your work in the form.

The Post Journals window in the Oracle General Ledger application is an example of a window that submits a concurrent program to post journal entries when you choose the Post button. You should refer to the user’s guide for your Oracle Applications product to learn if your product uses any product-specific windows to submit reports or programs.

  • Provides standard interface for running and monitoring reports.
  • Single form to submit any request, concurrent program. Another form to see report’s progress and to review report online.
  • Submit Request form: easy-to-use interface.
  • Automatic Resubmission.
  • Request Sets: define sets of reports, Submit an entire set at same time.
  • Request Set options: Run order, printer, no. of copies etc.
  • Request Set Log File:Single log file containing the completion status of all reports in set.
  • Viewing Requests: Monitor report’s progress using View Request form.
  • Child Request: or a sub-request submitted by any other concurrent request(Parent Request)
  • Parameter: A value you specify when you run a request.

* A request set is parent request and all reports in set are child requests
Value Set: A set of values against which AOL validates values, the end user enters when running a concurrent program.
Controlling Access to Reports:

  • Create related group of reports and request sets.
  • Define responsibility and assign a report security group to that responsibility.

Request Sets:

  • A collection of reports/programs that are grouped together. Allow to submit reports or programs in a request set all at once using a single transaction.
  • Request set can be defined to submit requests depending on the completion status of previously submitted requests in the set.

Stage:

  • Component of a request set used to group requests within a set. All requests in a stage are executed in parallel. Request set executes one stage at a time., following links from stage to stage.
  • Links that are followed depend on the completion status of the individual stages. A stage completes when all the requests contained in the stage have completed.
  • PL/SQL functions calculate the completion status of a stage
  • Three status of a stage:
  1. Success
  2. Warning
  3. Error

Stage Evaluation function:

  • Provided by AOL, computes the stage completion status from the completion statuses of the specified requests in set.
  • Function returns a
  1. Success: If all requests completed with a status of “Success”.
  2. Warning: If one or more requests in set completed with warning and no request completed with error.
  3. Error: If any request in set completed with error.
/by
, , , ,

Frequently Asked Questions in Assets

1. What are the different ways of adding assets in FA?
2. How do we depreciate Assets in Oracle Applications?
3. What is the significance of asset books in FA? Types?
4.What is ment by retire asset? How do we retire assets in Oracle applications?
5. What are the various Journal Entries generated through fixed assets
6.At what level FA is implemented?
7.What is the profile used to secure asset register?
8.What are the asset types in FA Module?
9.What are the different calendars used in FA Module?
10.Is FA Supports Multi _org?

11.What is ment by Roll back depreciation?
12.What are the mandatory flexfiels used in FA?
13.What are the depreciation methods used in FA module?
14.What is ment by prorate convention?
15.What is the use of allow amortized changes check box?
16.What is the difference between Quick addition and detail addition?
17.What is ment by projection?
18.What is ment by what-if analysis?
19.What is ment by leased asset?
20.What is ment by depreciation override? Can we override depreciation?
21.What is ment by physical inventory reconciliation?
22.Tell me something about asset insurance?
23.What is ment by asset revaluation?
24.In prepare mass additions window what are available Q names?
25.what is the difference between initial mass copy and periodic mass copy?
26.what is internal retairment?
27.What experience do you have in FA Module Implementation?
28.What do you know about FA to GL cycle?
/by