Link of WF_NOTIFICATION_OUT and WF_NOTIFICATIONS tables

Link WF_NOTIFICATIONS and WF_NOTIFICATION_OUT (queue table)

tables to find where the notification is pending

SELECT status, mail_status, notification_id FROM APPLSYS.WF_NOTIFICATIONS wfn

WHERE 1 = 1 AND wfn.mail_status = 'MAIL' and status in ('OPEN', 'CANCELED')

and exists

(select * from APPLSYS.AQ$WF_NOTIFICATION_OUT wfo

where wfn.notification_id = TO_NUMBER

((SELECT str_value FROM TABLE

(wfo.user_data.header.properties) WHERE

NAME ='NOTIFICATION_ID')

)

);

Also, you can always run $FND_TOP/sql/wfmlrdbg.sql by passing notification_id as parameter to this sql to get all the notification details

R12 Instance Home - INSTANCE_TOP

R12 architecure in brief was already introduced in my previous post, Now in this post more details on this new architecture.

In Applications release 12 top three directory structures are

db(database server),

apps(application tier servers) and

inst (instance home - new)

Figure explains these 3 directory strucures

Oracle Application release 12 introduces the concept of a top-level directory for an appication instance.

This directory referred to as Instance Home, and denoted by environment variable $INSTANCE_TOP.

This aims to get a clear distinction between the shared file system and the file system unique to an instance.

This TOP will now hold any configuration files (specific to that server), Certificates (If SSL is enabled SSL Certificates & any other certificates specific to that server) and related log files or PID files (for Apache or any other process) and INSTANCE_TOP will be specific to Server

Following are the Advantages of Instance Home

• Provides flexibility to share Applications Technology stack code (Apps, technology stack) among multiple instances/nodes and even instances (Dev,Test, Production)
• Support for read-only file systems and centralization of log files
• Enables split ownership of applications and technology stack file systems
• Improves code safety by making shared file system read-only while not patching
• Configuration and log files are co-located
• Easier collection of instance state
• Ability to use network or local storage for various file system portions
• Minimize disk usage and maximize performance

Use of Two Application Server ORACLE_HOMEs in Release 12

Two different OracleAS 10g releases, in separate ORACLE_HOMEs, are used in OracleApplications Release 12. This enables Applications to take advantage of the latest Oracle technologies.

R12 Architecture

Features of high-level architecture (more detials : coming soon)

• Java Home : IAS 10.1.3 Oracle home replaces 11i 1.0.2.2 9i AS Oracle Home
• C Home : Developer 10.1.2 Oracle home replaces 11i 8.0.6 home
• Database home : Database 10.2 Oracle home replaces 11i 9.2 home
• Appltop : Applications code staging area
• Common top : Runtime location for Java, Html
• Instance top : Configuration and runtime generated files

Applications Login Flow

Welcome to the Oracle Apps World!
Let me introduce with Apps Release 11i Login flow initially, though most of you know it

By now you might be aware of different Services/Component in Oracle apps I repeat them here these are ,
1. Oracle Web Server ( Apache, Jserv, plssql ) IAS_ORACLE_HOME 9iAS, Ver. 10222
2. Forms Server ( 8.0.6 ) ORACLE_HOME
3. Report Server ( in side 8.0.6 ORACLE_HOME)
4. Concurrent Manager for Batch or Individual Job Processing
5. Admin Server ( These are used for application dba job like patching , cloning & lot more )
6. Database Server

A) So when User makes a request from browser by typing URL like http:// <> : <> (port number is WebServer Port default is 8000) request hit to Oracle WebServer
B) Webserver checks that with http request there is no cookie attached (Cookie is message given by WebServer to Web Browser to identify client) so user is new & it passes login page to User .
C) User types his Username Password in login window & clicks submit button
D) It initially connects to public schema APPLSYSPUB through GWYUID defined APPLSYSPUB/PUB
E) Webserver checks that username and passwords need to authenticate against database FND_USER table , so it needs first some user to check client's username password in database . Guess which username password it uses ?? ( GUEST/ORACLE have you ever noticed why this user is there , Hope you know it now it Uses Guest User to autenticate your user in database )
F) Once user is authenticated its check against FND_RESPONSBILITY....for Authorization about users responsibilities & assigned responsibilities are presented back to user.
G) Now these responsibilities are of two type Self Services(served by Jserv/Servlets) or Core Applications Server by Forms Server .
H) If this is Core responsibility , Apache transfers request to Forms Listener (Listening on Forms port, I will cover how to check Forms Port in my coming posts).
From here request is on Socket (May be on forms servelets depending on forms config. ) which is persistense connection. From here onwards Client talks directly with Forms Server in forms session and if Client requested some data, forms server makes database connection ( How ?? using tnsnames.ora in 806 HOME or using dbc Database Connector file in FND_SECURE_TOP or wdbsvr.apps in Apache/modplsql/cfg ?)
I) If User selected Self Service Responsibility like iProcurement, iRecruitement.., Apache forwards request to mod_jserv and request is fullfilled by jserv_component.Have you ever noticed why webserver donot ask client to login again until session timeout ??( I know you know answer now its because of cookies as User have valid cookies , try deleting cookies from browser & see Oracle Apache asks you to relogin .
J) When your webserver tries to connect to database for plsql or any other type of connection it asks mod_pls to fulfill request but where is this connection information stored ? ( dbc file or wdbsvr.app file or somthing else ? )
K) Users submits his requests for batch processing or somthing else ( Gather Stats done by Sysadmins or Apps DBA's) these kind of requests are fullfilled by Concurrent Manager ( More about CM, including CM tuning still to come in future )
L) When user want to see reports of all these things request is being forwarded to Reports Server .You as Sysadmin or Oracle Apps DBA need not to worry how this request transmission happens from one service to another , this is transparent to you me or User . ( Let Oracle do this behind the scene ) You should know request flow as this is most crucial during Troubleshooting (I'll cover in future posts ) to pinpoint in which compnent issue is & where to look for .