, , ,

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
, , , ,

List of AIM Documents

Oracle A.I.M. Methodology encompasses a project management methodology with documentation templates that support the life cycle of an implementation. The life cycle methodology and documentation templates allows A.I.M. to be a very useful tool for managing implementation projects successfully.
This is a depiction of the A.I.M. methodology life cycle:
Application Implementation Method is a proven approach for all the activities required to implement oracle applications. there are eleven processes of implementation.
1. Business Process Architecture [BP] – This phase outlines:
  • Existing Business Practices
  • Catalog change practices
  • Leading practices
  • Future practices
BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure
2. Business Requirement Definition [RD] – This phase explains about the initial baseline questionnaire and gathering of requirements.
RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements
3. Business Requirement Mapping [BR] – In this phase the requirements of business are matched with the standard functionality of the oracle applications.
BR.010 Analyze High-Level GapsBR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles
4. Application and Technical Architecture [TA] – This outlines the infrastructure requirements to implement oracle applications.
TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Architecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures
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.
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines
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.
CV.010 Define data conversion requirements and strategyCV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data
7. Documentation [DO] – Documentation prepared per module that includes user guides and implementation manuals.
DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide
8. Business System Testing [TE] – A process of validating the setup’s and functionality by QA(functional consultant) to certify status.
TE.010 Define testing requirements and strategyTE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test
9. Performance Testing [PT] – Performance testing is the evaluation of transactions saving time, transaction retrieval times, workflow background process, database performance, etc
PT.010 – Define Performance Testing StrategyPT.020 – Identify Performance Test Scenarios
PT.030 – Identify Performance Test Transaction
PT.040 – Create Performance Test Scripts
PT.050 – Design Performance Test Transaction Programs
PT.060 – Design Performance Test Data
PT.070 – Design Test Database Load Programs
PT.080 – Create Performance Test TransactionPrograms
PT.090 – Create Test Database Load Programs
PT.100 – Construct Performance Test Database
PT.110 – Prepare Performance Test Environment
PT.120 – Execute Performance Test
10. Adoption and Learning [AP] – This phase explains the removal of the legacy system and oracle application roll out enterprise wide.
AP.010 – Define Executive Project StrategyAP.020 – Conduct Initial Project Team Orientation
AP.030 – Develop Project Team Learning Plan
AP.040 – Prepare Project Team Learning Environment
AP.050 – Conduct Project Team Learning Events
AP.060 – Develop Business Unit Managers’Readiness Plan
AP.070 – Develop Project Readiness Roadmap
AP.080 – Develop and Execute CommunicationCampaign
AP.090 – Develop Managers’ Readiness Plan
AP.100 – Identify Business Process Impact onOrganization
AP.110 – Align Human Performance SupportSystems
AP.120 – Align Information Technology Groups
AP.130 – Conduct User Learning Needs Analysis
AP.140 – Develop User Learning Plan
AP.150 – Develop User Learningware
AP.160 – Prepare User Learning Environment
AP.170 – Conduct User Learning Events
AP.180 – Conduct Effectiveness Assessment
11. Production Migration [PM] – The process of “decommissioning” of legacy system and the usage(adoption) of oracle application system.
PM.010 – Define Transition Strategy
PM.020 – Design Production Support Infrastructure
PM.030 – Develop Transition and Contingency Plan
PM.040 – Prepare Production Environment
PM.050 – Set Up Applications
PM.060 – Implement Production Support Infrastructure
PM.070 – Verify Production Readiness
PM.080 – Begin Production
PM.090 – Measure System Performance
PM.100 – Maintain System
PM.110 – Refine Production System
PM.120 – Decommission Former Systems
PM.130 – Propose Future Business Direction
PM.140 – Propose Future Technical Direction

Aim Documentation Exe Download
or
Aim Documentation Docs Download
/by
, ,

To Retriew the records from Org dependent Views

To Retriew the records from Org dependent Views on Sql Developer/Toad/Sql * Plus

begin
fnd_client_info.set_org_context(Org_id);
end;

/by
, , , , , ,

Query : PO, Supplier and Inventory Item

Select pha.segment1 “PO_NUMBER”,pla.line_num,plla.shipment_num,
pv.segment1 “Oracle_Supplier_number”, msib.segment1 “item_number”,pla.item_revision, pla.item_description,
NULL as “Delivery_date”, NULL as “Blank Column”, pla.unit_price, pha.rate,plla.Quantity_received,plla.Quantity_rejected,
(pla.unit_price*pla.Quantity) as “Line_Amount”, NULL as “ITEM_COST”, NULL as “Amount”, pla.VENDOR_PRODUCT_NUM
from apps. PO_HEADERS_ALL pha,
apps. PO_VENDORS pv,
apps. PO_VENDOR_SITES_ALL pvsa,
apps. PO_LINES_ALL pla,
apps. MTL_SYSTEM_ITEMS_B msib,
apps. PO_LINE_LOCATIONS_ALL plla
where pha.org_id=’xxxx’
AND pha.org_id=pvsa.org_id
AND pv.vendor_id=pha.vendor_id
AND pvsa.vendor_id=pv.vendor_id
AND pha.po_header_id=pla.po_header_id(+)
–AND pha.po_header_id=plla.po_header_id
AND plla.po_line_id(+)=pla.po_line_id
–AND msib.organization_id=’xxxx’
AND pla.item_id=msib.inventory_item_id(+)
Order by pha.segment1
/by
, , , , , ,

Inventory Organization and corresponding Operating Unit, Ledger and Legal Entity

Select a.organization_id, a.organization_code, a.organization_name,
a.operating_unit, b.name OU, a.set_of_books_id,d.name LEDGER,
a.legal_entity,c.name LE_NAME
From  apps. ORG_ORGANIZATION_DEFINITIONS a,
apps. HR_OPERATING_UNITS b,
apps. xle_entity_profiles c,
apps. gl_ledgers d
Where a.operating_unit=b.organization_id
AND c.legal_entity_id=a.legal_entity
AND d.ledger_id=a.set_of_books_id
/by