How to enable back space in putty?

Login into Putty

Don't login into sql plus

type following command

stty erase <press back space after space and hit enter>

How to get the Trace ID with Request ID

SELECT request_id, oracle_process_id trace_id
FROM fnd_concurrent_requests
WHERE request_id = &requestid; 

Check Whether a Patch is applied or not

 select *
from ad_bugs ab
where ab.bug_number = '&PatchNumber';

or

select bug_number from ad_bugs where bug_number='&PatchNumber';

How to find Trace file location when you are given Trace ID

select name,value from v$parameter where name='user_dump_dest';


SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = 65791130
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

Replace Trace ID with your trace ID and find the location of trace file and extract

How to set profile options to enable personalization?

System Administrator -->Profile-->System-


Ensure to set system profile 'Hide Diagnostics menu entry' to 'No' and 'Utilities:Diagnostics' to 'Yes' at the user level

Full Forms

EBS - E - Business Suite
ERP - Enterprise Resource Planning
P2P - Procure to Pay
TNS - Transparent Network Substrate
OM - Order Management
CRM - Customer Relationship Management
ASL - Approved Suppliers List
DFF - Descriptive Flex Fields
KFF- Key Flex Fields
PO - Purchase Order
SO - Sales Order
PLL - Program Link Library
COA - Chart of Accounts
MRP - Material Requirement Planning
SR - Service Request
WIP - Work in Progress
WMS - Warehouse Management Systems
RMA - Return Material Authorization
AFF - Accounting Flex Fields
PLL - PL/SQL Link Module Library
FMB - Form Module Binary
MMB - Menu Module Binary

How to Find UTL File Directory in Apps

Type the following command in SQLDeveloper or TOAD or SQL*Plus

show parameter UTL_FILE_DIR;

It displays UTL_FILE_DIR in apps

How to login to WinSCP and Putty?

The username and password of WinSCP and Putty are the same

Hostname can be the name of the host found in TNS or even you can use the IP Address as well and password is given by the DBA. Make sure the connection type is SSH and the port number usually gets defualted to 22. Save the credentials and login to the unix box.

To change the password using putty. You can use passwd command

New API's for Suppliers in R12

Name of Package: AP_VENDOR_PUB_PKG (apvndrs.pls and apvndrb.pls) 

AP_VENDOR_PUB_PKG.Create_Vendor

This is used to create new vendors in R12

AP_VENDOR_PUB_PKG.r_vendor_rec_type

If the TCA Party ID is provided in record type, the system will validate and create the new record in AP_SUPPLIERS else system calls TCA API to create API then create records in AP_Suppliers

AP_VENDOR_PUB_PKG.Update_Vendor_Public

Use to update vendor in R12

 AP_VENDOR_PUB_PKG.Create_Vendor_Site

 This is the new API to create new vendor sites in Release 12.

AP_VENDOR_PUB_PKG.Update_Vendor_Site_Public

        This is the new API to update Vendor Sites in Release 12.

AP_VENDOR_PUB_PKG.Create_Vendor_Contact

        This is the new API to create new vendor site contacts in Release 12. 

AP_VENDOR_PUB_PKG.Update_Vendor_Contact_Public

        This is the new API to update Vendor Contacts in Release 12.

 

CONCURRENT PROGRAMS IN R12 SUPPLIERS

       APXSUIMP - SUPPLIER OPEN INTERFACE IMPORT 

Supplier Open Interface Import program is extended to create Parties in TCA when creating the suppliers based on the information in AP_SUPPLIERS_INT. If records are populated in the IBY External Payee Accounts table, then the program will call the necessary APIs from Oracle Payments to create the Supplier Bank Accounts.


 APXSSIMP -- Supplier Sites Open Interface Import Supplier Sites Open Interface Import program is extended to create Locations, Party Sites, and Party Site Uses in TCA based on the information provided in AP_SUPPLIER_SITES_INT. If records are populated in the Payments External Payee Accounts table, then the program will call the necessary APIs from Oracle Payments to create the Supplier Bank Accounts for the site. 

APXSCIMP -- Supplier Site Contacts Open Interface Import Supplier Sites Contacts Open Interface Import program is extended to create Person Parties, Contact Relationships, Org Contacts, Contact Party Sites, and Contact Points in TCA based on the information provided in AP_SUPPLIER_SITES_INT. 

New Tables in R12 Suppliers

AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS
AP_SUPPLIER_INT_REJECTIONS

Suppliers in R12

Supplier information in EBS is basically defined using three things

1. Suppliers
2. Supplier Locations/Sites
3. Supplier Contacts


R11 Vs. R12


R11

Suppliers is stored in PO_Vendors
Supplier Locations/Sites PO_Vendor_Sites_All
Supplier Contacts are stored in PO_Vendor_Contacts

R12

In R12,

PO_Vendors changed as AP_Suppliers
PO_Vendor_Sites_All is changed as AP_Supplier_Sites_All
PO_Vendor_Contacts is changed as AP_Supplier_Contacts


     The supplier sites table will store the Site account attributes per Operating Unit,
     which will default into transactions. Going forward, supplier site creation will involve
     either selecting an existing location for the supplier or creating a new location in HZ_LOCATIONS.
     The user will then have to select the Operating Unit based on the security profile,
     and enter the site attributes as they are entered today.




The existing Suppliers tables are obsoleted and renamed as: 
PO_Vendors_OBS
PO_Vendor_Sites_All_OBS and
PO_Vendor_Contacts_OBS


To minimize impact on other products, views are provided with names

PO_Vendors
PO_Vendor_Sites_All
PO_Vendor_Contacts

Important SQL Queries

select * from v$version;

-- Gives Oracle Database version + Procedural Language for SQL Release +CORE+Transparent Network Substrate+National Language Support Run Time Library Version


Select * from v$database;

-- Gives DBID, NAME, CREATED, RESETLOGS_CHANGE etc...

Select * from all_tables;

-- table which stores tables in db


Select * from all_tab_coulmns;
-- table which gives columns of all tables

Select * from all_source;
-- Gives OWNER, NAME, Type of Object, Line and text of every object in DB


select * from all_views;

--table which gives all views in DB


 select * from all_users;

-- table gives all users in DB

How to do Conversions in a Migration Project?

1. Download all the tables from legacy systems
2. Sit with Business to decide which fields map to Oracle Directly and remove unwanted columns/tables. Note down the fields which can't map to Oracle directly. Most of them go as Descriptive Flex fields.
3. Make sure you have clean up process script ready before you do conversion, depending on the logic of the business
4. Load the data in the intermediate staging tables
5. Run the clean up process using the script
6. Generate report to functional review
7. Run the validation process
8. Use CRP 1 CRP 2 to minimize process. Try to get zero validation errors
9. You can use the 2 programs for validation a. API b. Interface table
10. Interface table program will have more control. We can use batch method in Interface which we cannot use in API

What is power shell and How can we copy output?

Windows PowerShell is Microsoft's task automation and configuration management framework, consisting of a command-line shell and associated scripting language built on .NET Framework

How can we copy output from PowerShell?

Select the text which you want to copy from powershell with Mouse and Press Enter button. Paste it where ever you need using CTRL+V 

Debug file, UTL and Trace File Directories

The following paths gives the way to access debug and trace directories.

However, they may vary depending on their set ups

Trace /dev2_bin/pebbles/db/tech_st/11.2.0.2/admin/DEV2_scdoratst1/diag/rdbms/dev2/DEV2/trace

Debug /dev2_bin/pebbles/db/tech_st/11.2.0.2/appsutil/outbound/DEV2_scdoratst1

UTL  /usr/tmp, /dev2_bin/pebbles/db/tech_st/11.2.0.2/appsutil/outbound/DEV2_scdoratst1, /dev2_bin/pebbles

Log Directory

/dev2_bin/pebbles/inst/apps/DEV2_scdoratst1/logs

How can I copy and compile forms?

Make sure while copying the pll files from AU_Top to local drive or vice versa from winscp make sure your Transfer Settings is changed from default to Binary.

Before compiling make sure you set environment settings in putty. Ask your DBA if it is the first time.

How to compile forms?
 For example if its custom.pll

frmcmp_batch module=CUSTOM.pll userid=apps/$pwd output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

FRM-10142

If you face this issue when you click on run form button in forms builder, Start OCJ4 server and go to edit click on preferences --> click on runtime and Type your application server URL or click on reset it to default and start running the form again

Forms Builder Settings

Forms Builder

Step 1

Open WinSCP and go to AU_TOP

/<Root> à Dev2_Bin à Pebbles à Apps à Apps_st à appl à auà12.0.0.0 à Resource

Copy all PLL files to your local drive to any folder say forms

1.       You can select PLL files by right clicking on WinSCP and sort by column options and copy and paste it to your local drive

2.       Copy APPSTAND.FMB and STANDARD.FMB file as well

Step 2

I assume you have Devsuite folder in your C Drive as you have devsuite installed

Copy tnsnames.ora file to your DevsuiteàNetworkàAdmin folder in devsuite and save it.

Step 3

Open Regedit and press CTRL+F and type FORMS_PATH. There add the path of the folder where you copied the pll files from AU_TOP to the existing path and save it

Step 4

Right click on forms builder icon, click on properties and give the path of the same folder in Start in checkbox and save it.

Now open APPSTAND.FMB file and Any one pll file. You should be good if you do not face any error.

PDE - PLI018 could not find library FNDSQF

If you face this kind of error in forms open WINSCP and go to AU_TOP and copy all the pll files from resource folder to local computer and try to load it again. If you face the same error you may need to check the path of FORMS_PATH in regedit and add this folder path over there

Forms Builder

Step 1

Open WinSCP and go to AU_TOP

/<Root> à Dev2_Bin à Pebbles à Apps à Apps_st à appl à auà12.0.0.0 à Resource

Copy all PLL files to your local drive to any folder say forms

1.       You can select PLL files by right clicking on WinSCP and sort by column options and copy and paste it to your local drive

2.       Copy APPSTAND.FMB and STANDARD.FMB file as well

Step 2

I assume you have Devsuite folder in your C Drive as you have devsuite installed

Copy tnsnames.ora file to your DevsuiteàNetworkàAdmin folder in devsuite and save it.

Step 3

Open Regedit and press CTRL+F and type FORMS_PATH. There add the path of the folder where you copied the pll files from AU_TOP to the existing path and save it

Step 4

Right click on forms builder icon, click on properties and give the path of the same folder in Start in checkbox and save it.

Now open APPSTAND.FMB file and Any one pll file. You should be good if you do not face any error.

Forms: Ora 12154 TNS: Could not resolve service name

When you face this kind of error check for your tnsnames.ora file. If it doesn't exist in devsuite-->Network-->admin folder, make sure that you keep tnsnames.ora file in the folder and check whether the tns do contain data about the database which you are about to connect

Max Vs Greatest SQL

select greatest(50,20) from dual;

Output

50

select max(50,20,30) from dual;

Invalid number of arguments

select max(empno) from emp;

7934

select greatest(empno) from emp;

List of Documents in Oracle

AP010	Executive Project Strategy
AP010	Invitation Memorandum
AP010	Session Planning Checklist
AP020	Application Overview Customization
AP020	Invitation Memorandum
AP020	Project Team Orientation Plan
AP020	Session Planning Checklist
AP030	Project Team Learning Plan
AP040	Project Team Learning Environment
AP050	Project Team Learning Events Administration
AP060	Business Unit Managers Readiness Plan
AP060	High Level Systems Overview Customization
AP060	Invitation Memorandum
AP060	Session Planning Checklist
AP070	Invitation Memorandum
AP070	Project Readiness Roadmap
AP070	Session Planning Checklist
AP080	Communication Campaign
AP090	Application to Process Overview Customization
AP090	Invitation Memorandum
AP090	Managers Readiness Plan
AP090	Session Planning Checklist
AP100	Business Process Organizational Impact
AP110	Human Performance Support Systems
AP120	Information Technology Groups Alignment
AP130	User Learning Needs Analysis
AP140	Learning Plan
AP150	User Learningware
AP160	User Learning Environment
AP170	User Learning Events Administration
AP180	Effectiveness Assessment
AP180	Invitation Memorandum
AP180	Session Planning Checklist
BP010	Business and Process Strategy
BP020	Change Catalog
BP030	Data Gathering Requirements
BP040	Current Process Model
BP050	Leading Practice Review
BP060	High Level Process Vision
BP070	High Level Process Designs
BP080	Future Process Model
BP090	Business Procedure Documentation
BR010	High Level Gap Analysis
BR020	Configured Mapping Environment Architecture and Configuration
BR030	Business Requirements Mapping Form
BR050	Integration Fit Analysis
BR060	Information Model
BR080	Business Mapping Test Results
BR100Aap	Accounts Payable Application Setup
BR100Aar	Accounts Receivable Application Setup
BR100Aas	Sales and Marketing Application Setup
BR100Abom	Bill Of Materials Application Setup
BR100Aca	Common Applications Application Setup
BR100Acas	Common Applications System Admin Application Setup
BR100Ace	Cash Management Applications Setup
BR100Acf	Common Financials Application Setup
BR100Acm	Common Manufacturing Application Setup
BR100Acn	Sales Compensation Application Setup
BR100Acrp	Capacity Application Setup
BR100Acs	Service Application Setup
BR100Acst	Cost Management Application Setup
BR100Aeng	Engineering Application Setup
BR100Afa	Fixed Assets Application Setup
BR100Afi	OPM Financials Application Setup
BR100Agl	General Ledger Application Setup
BR100Ahr	Human Resources Application Setup
BR100Aic	OPM Inventory Application Setup
BR100Ainv	Inventory Application Setup
BR100Alo	OPM Logistics Application Setup
BR100Amrp	Master Scheduling MRP Application Setup
BR100Aoe	Order Entry Application Setup
BR100Apa	Project Accounting Application Setup
BR100Apd	OPM Product Development Application Setup
BR100Apo	Purchasing Application Setup
BR100App	OPM Process Planning Application Setup
BR100Aqa	Quality Application Setup
BR100Ascp	Supply Chain Planning Application Setup
BR100Ass	Supplier Scheduling Application Setup
BR100Asy	OPM Systems Application Setup
BR100Awip	Work in Process Application Setup Document
CM010	Configuration Management Procedures
CM020	Document Index
CM020	Document Update Notice
CM030	Configuration Item Index
CM030	Configuration Item Status Record
CM040	Release Note
CR010	Project Management Plan
CR010	Project Start Up Checklist
CR020	Control and Reporting Procedures
CR040	Risk and Issue Form
CR040	Risk and Issue Log
CR050	Problem Report Form
CR050	Problem Report Log
CR060	Change Request Form
CR060	Change Request Log
CR070	Meeting Minutes
CR070	Project Progress Report
CR080	Acceptance Certificate
CR080	Client Satisfaction Report
CR080	End Report
CV010	Data Conversion Requirements and Strategy
CV020	Conversion Standards
CV030	Conversion Environment
CV040	Conversion Data Mapping
CV050	Manual Conversion Procedures
CV060	Conversion Program Designs
CV070	Conversion Test Plans
CV120	Installed Conversion Programs
CV130	Converted and Verified Data
DO010	Documentation Requirements and Strategy
DO020	Documentation Standards and Procedures
DO030	Glossary
DO040	Document Environment
DO060	User Reference Manual
DO070	User Guide
DO080	Technical Reference Manual
DO090	System Management Guide
MD010	Application Extension Strategy
MD020	Application Extension Definition And Estimates
MD030	Design Standards
MD040	Build Standards
MD050	Application Extensions Functional Design
MD060	Database Extensions Design
MD070	Application Extensions Technical Design
MD090	Development Environment
MD120	Installation Instructions
PJM01	Engagement Summary
PJM02	Project Memo
PJM03	Meeting Agenda
PJM04	Rejection Note
PJM05	Supplier Assessment Record
PJM06	Project Document Generic
PJM07	Phase End Report
PJM08	Site Visit Report
PJM09	Project Management Checklist
PJM10	Quality Management Checklist
PM010	Transition Strategy
PM020	Production Support Infrastructure Design
PM030	Transition and Contingency Plan
PM040	Installation QA Checklist
PM090	System Performance Assessment
PM130	Business Direction Recommendations
PM140	Technical Direction Recommendations
PT010	Performance Testing Strategy
PT020	Performance Test Scenarios
PT030	Performance Test Transaction Models
PT040	Performance Test Scripts
PT050	Performance Test Transaction Program Designs
PT060	Performance Test Data Design
PT070	Performance Test Database Load Program Designs
PT110	Performance Test Environment
PT120	Performance Test Results
PT130	Performance Test Report
QM010	Quality Management Procedures
QM020	Review Comments List
QM020	Review Leader Form
QM030	Audit Action Form
QM030	Audit Report
QM040	Metrics Report
QM045	Project Healthcheck Checklist
QM050	Quality Report
RD010	Current Financial and Operating Structure
RD020	Current Business Baseline
RD030	Process and Mapping Summary
RD040	Business Volumes and Metrics
RD050	Business Requirements Scenarios
RD060	Audit and Control Requirements
RD070	Business Availability Requirements
RM010	Resource Management Procedures
RM020	Assignment Request
RM020	Staffing and Organization Plan
RM025	Project Orientation Guide
RM030	Staff Training Plan
RM040	Physical Resource Plan
RM040	Service Level Agreement
RM050	Incoming Item Record
RM050	Installation Plan and Record
RM060	Assignment Terms of Reference
RM070	Equipment Fault Record
TA010	Architecture Requirements and Strategy
TA020	Current Technical Architecture Baseline
TA030	Preliminary Conceptual Architecture
TA040	Application Architecture
TA050	System Availability Strategy
TA060	Reporting and Information Access
TA080	Application Security Architecture
TA090	Application and Database Server Architecture
TA100	Architecture Subsystems Proposal
TA110	System Capacity Plan
TA120	Platform and Network Architecture
TA130	Application Deployment Plan
TA140	Performance Risk Assessment
TA150	System Management Procedures
TE010	Testing Requirements and Strategy
TE020	Unit Test Script
TE030	Link Test Script
TE040	System Test Script
TE050	Systems Integration Test Script
TE060	Testing Environment Setup Log
TE110	Test Report for System Test
TE120	Test Report for Systems Integration Test
TE130	Test Report for Acceptance Test
WM010	Work Management Procedures
KDD	Key Decision Document

FRM-10102: Cannot attach PL/SQL library APPCORE

FRM-10102: Cannot attach PL/SQL library APPCORE


Problem :: FRM-10102: Cannot attach PL/SQL library APPCORE. This library attachment will be lost if the module is saved

Solution :: Need to add FORMS_PATH  in registry of windows.

Press Win R to open windows Run and put regedit to edit the windows registry.
and search ofr FORMS_PATH
and custom Forms and plls path in this variable

====================================

How to do RMA?

Sales Order Screen-> Select Order type as a kind of return defined by cleint-<Mandatory field called return reason
-> select a value from LOV and Book

Do inventory receipt
Book the order status will be awaiting return

Go to Inventory Super user
Transactions -_ Receipts Query  with your order Number
Select the return line and save it-> order gets closed

Check onhand quantities table 

How to copy a Workflow Process in Oracle Workflow Builder

Open the Workflow builder and select the process which you want to copy.
Click on CTRL+C and Press CTRL+V
Change the internal name, display name and description and add required functions.
After working there save to Database

TIPS

We can't add a new field using forms Personalization. If you have to add a new field which already exists in table, the customization should be easy. It takes few days to complete the issue 

APP-WIP-25023 You must define parameters for the Work in Process Module

When you go to WIP Module and expand discrete and click on discrete jobs, you may face this issue. It means the organization is not set up. Press CTRL + L and select an organization. Then you should be goof and the form opens when you set the organization. CTRL+L usually opens Form Functions

Error in Workflow ORA-04061: existing state of has been invalidated

Working with workflow gets tough when we get errors which are hard to debug. I am working on a workflow yielded error as mentioned above. Everything is right about package.

First Thing to Check
1. Whether the package is Valid or not and it is in Valid state

Reason for the Error
As I am modifying a workflow, it works well for certain instances. When I modify the package and recompile it, I am facing similar issue. Recompilation of package spec/body called through workflow is the main reason for this error. This happens because workflow fails to look at the new status of the package after compilation and it looks at the old data

Solution

1. Get your DBA to bounce the instance
or
2. Restart the notification mailer and Service Components
 or
3. Kill Active Sessions of workflow and drop the package and recompile it then force reload your workflow in the database

How to connect to Oracle Workflow Builder?

After installing Oracle Workflow Builder, you can click on file and open. You will see a similar screen

1. Click on Database and user would be apps
2. Password would be same as password for database
3. Connect would be instance such as Dev2
4. You can leave the Effective as blank
5. Click on ok and select the require workflow and start using it

Unable to drag anything in Workflow Builder

When you allow modifications on customized objects, some times it doesn't allow you to customize objects in workflow Builder. In such cases, click on help and then click on About and then decrease the access level to zero and you should be good to customize objects, add functions and whatever

SQL Query to find Legal Entity, Org ID

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;

Conversion Documents

List of Conversion Documents 
CV40 - Funcational Document (Prepared by Funcational Consultant)
CV60 - Technical Document (Preapred by Technical Consultant)
CV70 - Test Plan Document (Preapred by Technical Consultant)
CV120 - Installed Conversion Programs(Preapred by Technical Consultant)

Label Tables in Oracle

Label tables in Oracle

WMS_LABELS
WMS_BFLOW_LABEL_TYPE
WMS_LABEL_FORMATS
WMS_LABEL_FIELD_VARIABLES
WMS_LABEL_FIELDS_B
WMS_LABEL_FIELDS_TL
WMS_LABEL_REQUESTS_HIST
WMS_LABEL_SET_FORMATS
WMS_LABEL_REQUESTS

Pricing Tables

qp_pricing_attributes
qp_list_headers
ap_list_headers_b
qp_qualifiers
qp_list_lines

List of Installed Modules in your Company

SELECT a.application_name,a.product_code,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status, 
patch_level 
FROM apps.fnd_application_vl a, 
apps.fnd_product_installations b 
WHERE a.application_id = b.application_id
and b.status='I'
order by product_code asc;

Java Installation Errors

Error 1723. There is a problem with this Windows Installer package. A DLL required for this install to complete could not be run. Contact your support personnel or package vendor.

If you ever face this issue while uninstalling Java. Download Microsoft fix it and then run it. Using fix it, uninstall the program.

http://support.microsoft.com/mats/program_install_and_uninstall/

How to Remove Oracle Manually Without Installing New OS?

Removing Oracle from computer completely is always a pain. Before going to the steps, I will provide some clarity what to remove why to remove?

1. If you have SQLDeveloper and if you don't have Oracle client and if you are connecting server database you don't have anything related to Oracle installed in your PC
2. If you have any Oracle client installed in your laptop and if you are connecting the TOAD through that oracle client to your local database then Oracle client is installed and you have to follow the steps below

The database version of your Server may be different from the database version of your local database. You can know the version of the database using the SQL Query
select * from v$database;

Step 1: Stop all Services

Go to Control Panel --> System and Security --> Administrative Tools --> Services and stop all the services starting with Ora or Oracle. To stop all you need to do is to right click on service and stop the service.

Step 2:  If you have Oracle already installed in your PC. Don't delete any folder manually.

Click on Windows button and in the search bar type Oracle Universal Installer. There pops up oracle Universal Installer and there will be a button called Deinstall products. Click on that and select all the Oracle components to uninstall. Uninstall them completely and also uninstall Oracle home

Step 3: Manually Remove Folders

In the drive where you installed Oracle remove following folders
1. App folder
2. Oracle folder in program files
3. Oracle folder in program files 86
4.  If OCI.dll is not getting deleted follow my previous post on how to delete OCI.dll

Step 4: Removing Patches from Regedit

Click on Windows + R and type regedit and registry will be opened
Press Control + F and type Oracle and click on find and delete every oracle field in registry
Press Control + F and type Ora and click on find and delete every oracle field in registry

Step 5: Remove everything from Recycle bin
Remove every thing from Temp folder
Remove every thing from %temp% folder
Remove every thing from recent folder

Delete all shortcuts and clear recycle bin as well.

Reboot the machine and you are good to install new copy of Oracle

If you still face issues after doing all the issues try downloading Oracle Developer new version...

Invalid Object in Instance

How to check for Invalid Objects in an Instance?

select object_name, object_type from dba_objects where status = 'INVALID' order by object_type, object_name;

What should I do?

Forward the issue to the DBA if you are a developer. Subject of the E-Mail would be Recompile all the forms. In most of the cases invalid objects may occur due to no apparent reason. DBA should recompile all forms for every 3 days or so

What DBA Should do?

using adadmin,
- 1. Generate Applications Files menu
- 2. Generate form files
- Enter the number of workers [4] :
- Do you want to generate Oracle Forms objects using this character set [Yes] ?
- Do you want to regenerate Oracle Forms PL/SQL library files [Yes] ?
- Do you want to regenerate Oracle Forms menu files [Yes] ?
- Do you want to regenerate Oracle Forms executable files [Yes] ?
- Enter list of products ('all' for all products) [all] : ont
- Generate specific forms objects for each selected product [No] ?
- Enter list of languages ('all' for all of the above) [all] :
- You selected the following languages: US
Is this the correct set of languages [Yes] ? 

How to delete OCI.DLL file?

When you have Oracle Client Home 11 and you already deleted Oracle Universal installer you cannot delete client home using Oracle 10G Oracle Universal Uninstaller. This is the error which you will face:

"some of the components you selected cannot be deinstalled because they were installed using oracle universal installer 11.2.0.0.0. please use oracle universal installer version 11.2.0.0.0 or higher to deinstall these products"

Then you have to delete the client home manually. Check for Oracle Folders in Program Files and Program Files (86) and delete Oracle folders. Some times OCI.DLL cannot be deleted. Then you cut the OCI.DLL file and paste it on desktop. Remove the app folder and then reboot your computer and then delete the file.

What is Party?

As we discussed in earlier post, Party in CRM is different from Party in ERP. When CRM refers to customer they are referring to Party layer. When ERP about customer they are referring to Account Layer.

What is Party ID?

Each party has unique id called a Party ID.

What is an identifying address?
You can designate one address per party as an Identifying Address. A party can have multiple party sites, but each party has one identifying address. 

What is Site Number?
The number which uniquely identifies a location.

What is Party Layer?

Party layer consists of party and party’s contacts and addresses. A party is defined as an entity that can enter into a business relationship. Party can be either a single person or an Organization.

Examples:

University of Houston Clear Lake (Organization)

Mr.XYZ (Person)

Parties have one or more addresses. Just like your amazon account where you have bill to address to your home and ship to address to someone’s place. Each address may have one or more uses. Party layer can exist independently without an Account Layer.

What is Account Layer? 

Account Layer consists of Account, Account addresses and Account contacts. An account is defined as an financial roll up point for a party that defines customer relationship between a party and implementing organization.

Party layer can exist without account layer

Account Layer cannot exist without party layer. Account must have only one owner which must be person party or an organization party. Account owner is responsible for the account.

Oracle says “If the entire E-Business Suite is used, the Party layer is usually created first in CRM, and then the Account layer is added when it becomes necessary. In many cases, the Account layer is added when a Party wishes to place an order.  When an Account is created, it must be linked to one and only one Party. This Party is the Account owner.

Once an Account is created, Addresses can be defined for the Account. Account Address must have a corresponding Party Address in order to exist. The corresponding Party address must be associated to the Party that owns the Account. Account Addresses are a subset of Party Addresses. Similarly, an Account Contact must first be defined as a Party Contact for the Party
that owns the Account. Party Contacts are defined through Relationships.”

CRM Vs. ERP

CRM	ERP
CUSTOMER RELATIONSHIP MANAGEMENT	ENTERPRISE RESOURCE PLANNING
It is a system used to store and record all the information about customer interactions	ERP focuses on business.
Used by executives to create sales projections	ERP is used to improve efficiency of business.
Goal: Provide a comprehensive store of customer data than can be used to increase sales, customer relation and make customer relations more efficient	ERP provides a method for stream lining business process across the board
Salesforce, Job Diva, Microsoft Dynamics CRM are popular CRM	SAP, Oracle and Microsoft Dynamics are popular ERP

Various Naming Conventions of Oracle Tables

Various Naming Conventions of oracle Tables

_ALL

These tables hold information of different operating units

Examples: HR_LOCATIONS_ALL, OE_ORDER_HEADERS_ALL, AP_AE_HEADERS_ALL, AP_AE_LINES_ALL etc.

_B

These are the base tables in Oracle. They are important because they contain data in table with all validations. If any issue occurs on these tables, then we can see a data corruption issue.

Example: MTL_SYSTEM_ITEMS_B, MTL_ITEM_CATEGORIES_B, MTL_CATEGORY_SETS_B, MTL_ITEM_REVISIONS_B, MTL_ITEM_TEMPLATES_B, QP_SEGMENTS_B, PA_PROJECT_SETS_B etc.

_A

The tables ending with _A are Audit Shadow Tables

Example: PO_VENDORS_A, FND_AUDIT_TABLES_A etc.

All Audit Trail shadow tables contain certain special auditing columns. These columns include:

• AUDIT_USER_NAME (the Application User ID, except when changes are applied using SQL*Plus, in which case it is the Oracle ID)

• AUDIT_TIMESTAMP (the date/time when the insertion occurred)

• AUDIT_TRANSACTION_TYPE (I for Insert, U for Update, D for Delete, L for Last, and C for Current)

• AUDIT_TRUE_NULLS (VARCHAR2(250) column containing a delimited list of column names that have changed from NULL)

_AVN and _ACN are Audit Shadow Views 

-V

_V tables are the views created for the base tables

Examples: MTL_ITEM_CATEGORIES_V is view for MTL_ITEM_CATEGORIES

_S

Table ending with _S are sequence tables

_TL

These are the tables corresponding to same table minus _TL, they provide multi language support

Example: MTL_ITEM_REVISIONS_TL resembles MTL_ITEM_REVISIONS but you will find language column, source_lang column in addition.

_F

These are date tracked tables. They can be found mostly in HR and Payroll Modules. EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE are two date columns which together with PK define a row uniquely.

Example: HR_S_GLOBALS_F, HR_S_FORMULAS_F etc.

_VL

_VL are the views for multi-language tables which combines row of base tables with corresponding row of _TL tables