Find Applied Patch Set Information Query

select
 fav.application_name app_name
, fav.application_short_name app_s_name
, decode(fpi.status, 'I', 'Yes',
                    'S', 'Shared',
                    'N', 'No', fpi.status) inst_status
, nvl(fpi.patch_level, 'Not Available') patchset
, fav.application_id app_id
from apps.fnd_application_vl fav, apps.fnd_product_installations fpi
where fav.application_id = fpi.application_id
--and fav.application_name like '%Secu%Enterprise%'
and fav.application_short_name = 'PER'
order by 3;

Remove Junk Characters Function

CREATE OR REPLACE FUNCTION xxx_ascii_only_vipul (p_txt IN VARCHAR2)
       RETURN VARCHAR2
    IS
       v_tmp     VARCHAR2 (32767);
       v_clean   VARCHAR2 (32767);
       v_char    VARCHAR2 (3 BYTE);
    BEGIN
       FOR i IN 1 .. LENGTH (p_txt) LOOP
          v_char := SUBSTR (p_txt, i, 1);

         IF    (ASCII (v_char) BETWEEN 32 AND 127)
            OR (ASCII (v_char) IN (9, 10, 13)) THEN
            v_clean := v_clean || v_char;
         END IF;
      END LOOP;

      IF LENGTH (v_clean) != LENGTH (p_txt) THEN
         DBMS_OUTPUT.put_line ('removed '||TO_CHAR(LENGTH(p_txt) - LENGTH(v_clean))||' characters');
      END IF;

      RETURN v_clean;
   END;
   /

Query for Ledger, OU, Legal Enity, balancing segment

SELECT DISTINCT hrl.country,
  hroutl_bg.NAME bg,
  hroutl_bg.organization_id,
  lep.legal_entity_id,
  lep.NAME legal_entity,
  hroutl_ou.NAME ou_name,
  hroutl_ou.organization_id org_id,
  hrl.location_id,
  hrl.location_code,
  glev.FLEX_SEGMENT_VALUE
FROM xle_entity_profiles lep,
  xle_registrations reg,
  hr_locations_all hrl,
  hz_parties hzp,
  fnd_territories_vl ter,
  hr_operating_units hro,
  hr_all_organization_units_tl hroutl_bg,
  hr_all_organization_units_tl hroutl_ou,
  hr_organization_units hou,
  gl_legal_entities_bsvs glev
WHERE lep.transacting_entity_flag      = 'Y'
AND lep.party_id                       = hzp.party_id
AND lep.legal_entity_id                = reg.source_id
AND reg.source_table                   = 'XLE_ENTITY_PROFILES'
AND hrl.location_id                    = reg.location_id
AND reg.identifying_flag               = 'Y'
AND ter.territory_code                 = hrl.country
AND lep.legal_entity_id                = hro.default_legal_context_id
AND hou.organization_id = hro.organization_id
AND hroutl_bg.organization_id          = hro.business_group_id
AND hroutl_ou.organization_id          = hro.organization_id
AND glev.legal_entity_id               = lep.legal_entity_id
AND hou.organization_id                =241

API to get DEPENDENT OBEJCTS

API to get DEPENDENT OBEJCTS:


DBMS_UTILITY.GET_DEPENDENCY
   type      IN     VARCHAR2,
   schema    IN     VARCHAR2,
   name      IN     VARCHAR2);

SELECT * FROM ALL_DEPENDENCIES

Using above method, we can find out the Dependent Objects.

Find Locks on Packages/Tables and Release them:

Find Locks on Packages/Tables and Release them:

SELECT l.session_id||','||v.serial# sid_serial,
       l.ORACLE_USERNAME ora_user,
       o.object_name,
       o.object_type,
       DECODE(l.locked_mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive',
          TO_CHAR(l.locked_mode)
       ) lock_mode,
       o.status,
       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
      and l.SESSION_ID=v.sid
order by 2,3;

SELECT * FROM DBA_DDL_LOCKS
WHERE NAME IN ('XXX_OBJECT_NAME' ,'XXX_OBJECT_NAME')

SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME IN ('XXX_OBJECT_NAME' ,'XXX_OBJECT_NAME')

SELECT * FROM SYSTEM.V$SESSION
WHERE SID = 2883 OR ROW_WAIT_OBJ# IN (374295,373803,382481,382490)

ALTER SESSION SET CURRENT_SCHEMA=SYS

ALTER SYSTEM KILL SESSION '1586,395'

ALTER SYSTEM FLUSH BUFFER_CACHE

ALTER SYSTEM FLUSH SHARED_POOL

ALTER SESSION SET CURRENT_SCHEMA=APPS

DFF Deletion API

DFF Deletion API:

DECLARE
CURSOR C1 IS
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE, DESCRIPTIVE_FLEXFIELD_NAME, APPLICATION_ID
FROM FND_DESCR_FLEX_CONTEXTS
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCR_FLEX_CONTEXTS_PKG.DELETE_ROW (
  V1.APPLICATION_ID,
  V1.DESCRIPTIVE_FLEXFIELD_NAME,
  V1.DESCRIPTIVE_FLEX_CONTEXT_CODE);
END LOOP;
END;

-----------

DECLARE
CURSOR C1 IS
SELECT APPLICATION_COLUMN_NAME,DESCRIPTIVE_FLEX_CONTEXT_CODE,DESCRIPTIVE_FLEXFIELD_NAME,APPLICATION_ID
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCR_FLEX_COL_USAGE_PKG.DELETE_ROW (
  V1.APPLICATION_ID,
  V1.DESCRIPTIVE_FLEXFIELD_NAME,
  V1.DESCRIPTIVE_FLEX_CONTEXT_CODE,
  V1.APPLICATION_COLUMN_NAME);
END LOOP;
END;

-----------

DECLARE
CURSOR C1 IS
SELECT DESCRIPTIVE_FLEXFIELD_NAME,APPLICATION_ID
FROM FND_DESCRIPTIVE_FLEXS
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCRIPTIVE_FLEXS_PKG.DELETE_ROW (
  V1.APPLICATION_ID,
  V1.DESCRIPTIVE_FLEXFIELD_NAME);
END LOOP;
END;

Oracle Alerts

Oracle Alert - Basics

Idea of this post is to create a simple alert and test if it is working or not.
Just for example, assume a requirement to send Happy birthday mail to employees in an organization.

As you might already know by now, there are 2 kinds of alerts in oracle apps.
Periodic Alerts and Event based Alerts.
You are correct, we have to choose Periodic alert for our requirement.

Employee Birthday can be any calendar day of the year. So we will tell oracle apps 
to check daily once every calender day and see if today is employee's birthday and send email if true.

You need Alert Manager Responsibility to define a new Alert.
Navigate through Alert Manager -> Alert -> Define
Fill the options as given in the screenshot below.

Note that we need to write SQL which satisfies our condition and also to fetch required details. Here is the SQL

select global_name, date_of_birth, email_address
into &emp_name, &dob, &emp_email
from per_all_people_f
where trunc(sysdate) between effective_start_date and effective_end_date
AND to_char(to_date(date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(date_of_birth),'mm') = to_char(to_date(sysdate),'mm');

You can check the SQL for syntax using "verify" button.

One point here, if any row that matches condition, it is called exception in Alert.
So when you click "run" button, it will display the number of exceptions occured (number of rows that satisfied the condition).

Next step is to define action if condition matches. Don't forget to select action level of type "Detail". This is because action should be performed once for every Alert Exception.

Then click on "Action Details" button and define the email message as shown below.

That's not enough, we also need "Action Sets" and attach the action which we just created. Just follow the screenshots to do that.

Now comes the question, how do we test this Alert?

Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time.
It will submit a concurrent program. [In this example CP Name is "Birthday Wishes E-mail Alert (Check Periodic Alert)"]

Once concurrent program is successfully ran, we can verify the number of exception from
Alert Manager -> History and query for alert.

Which means our Alert is working.
Note that you can do many more than just sending mails. Alert can execute SQL code/function/procedures and it can also run concurrent programs.
So why wait, go ahead and explore more. :-)

Alert in Oracle Forms for User Response

Here is how a alert can be used in Oracle Forms for user response.
   
     DECLARE
       al_id Alert;
       al_button NUMBER;
    BEGIN
         al_id := Find_Alert('');
          
         al_button := Show_Alert(al_id);
          
         IF al_button = ALERT_BUTTON1 THEN -- Alert_button1 value is OK
          --<<--Your Condition-->>
          null;
         ELSE
           --<<--Your Condition-->>
           RAISE Form_trigger_Failure;
         END IF;
    END;

Interview Questions : Oracle Alerts

1.  What are the different business uses of Alerts?

        Ans:

a)      Keep you informed of critical activity in your database

b)      Deliver key information from your applications, in the format you choose to provide you with regular reports on your database information

c)       Automate system maintenance and routine online tasks Information about exception conditions.

2.       What are the different types of alerts, Define it?

      Ans:

You can define one of two types of alerts: an event alert or a periodic alert.

Event alert: An event alert immediately notifies you of activity in your database      

as it occurs.

Periodic alert: A periodic alert, on the other hand, checks the database for information according to a schedule you define.

3.       What database events can cause what actions?

Ans: An insert and/or an update to a specific database table

4.       What actions can you perform in an alert?

Ans: An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

5.       What do you specify when creating a Periodic Alert?

Ans:

a.       A SQL Select statement that retrieves specific database information

b.      The frequency that you want the periodic alert to run the SQL statement

c.       Actions that you want Oracle Alert to perform once it runs the SQL statement.

6.       Can you define Alert on Oracle Applications Tables?

Ans: Yes

7.       How alert is different from database triggers?

Ans:

a)      Code can be modified and viewed in a screen

b)      Periodic alert is not possible through Database trigger

c)       Oracle Alert will also transfer your entire alert definition across databases. You can instantly leverage the work done in one area to all your systems.

d)      Customizable Alert Frequency with Oracle Alert, you can choose the frequencyof each periodic alert. You may want to check some alerts every day, some only once a month, still others only when you explicitly request them.

8.       Can you define detailed or summary actions in alert?

Ans: Yes, Detail or Summary Actions you can choose to have Oracle Alert perform actions based on a single exception or a combination of exceptions found in your database.

9.       Can you perform actions when NO exceptions are found?

Ans: No Exception Actions : Oracle Alert can perform actions if it finds no exceptions in your database, same as alert actions.

10.   Can you specify History Maintenance?

Ans: Alert History Oracle Alert can keep a record of the actions it takes and the exceptions it finds in your database, for as many days as you specify.

Alerts in Oracle Application

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alertsto monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

Event Alert:

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.

• A SQL Select statement that retrieves specific database information as a result of the database event.

• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

Periodic Alert:

A periodic alert, on the other hand, checks the database for information according to a schedule you define. In a periodic alert specify the following:

• A SQL Select statement that retrieves specific database information.

• The frequency that you want the periodic alert to run the SQL statement.

• Actions that  Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. We include all the actions we want Oracle Alert to perform, in an action set.

Navigation in Oracle Apps to define an alert:

Go to “Alert Manager” Responsibility
Alert >> Define

Transfer Alert from one instance/database to other:

Go to “Alert Manager” Responsibility
Alert >> Define
Go to “Tools” Menu on top
Click on “Transfer Alert”
Enter source and destination fields and click Transfer.

How to define an periodic alert:

1. Go to Alert Manager > Alert > Define.
2. Select the ‘Periodic’ Tab.
3. Enter the name of the application that owns the alert in the Application field.
4. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
5. Check Enabled to enable your periodic alert.
6. Set the frequency for the periodic alert to any of the following:

• On Demand
• On Day of the Month
• On Day of the Week
• Every N Calendar Days
• Every Day
• Every Other Day
• Every N Business Days
• Every Business Day
• Every Other Business Day

Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define. Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.

Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in :THRESHOLD_DAYS.:

SELECT user_name,
password_date,
:THRESHOLD_DAYS
INTO &USER,
&LASTDATE,
&NUMDAYS
FROM fnd_user
WHERE sysdate = NVL(password_date,
sysdate) + :THRESHOLD_DAYS
ORDER BY user_name

Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function.

You can verify the accuracy and effectiveness of your Select statement. Choose Verify to parse your Select statement and display the result in a Note window.

Choose Run to execute the Select statement in one of your application’s Oracle IDs, and display the number of rows returned in a Note window.

Once you are satisfied with the SQL statement, save your work.

Specifying Alert Details:

Once you define an event or periodic alert in the Alerts window, you need to display to the Alert Details window to complete the alert definition. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

Creating Alert Actions:

After you define your alert you need to create the actions you want your alert to perform. There are four types of actions you can create:

• message actions

• concurrent program actions

• operating script actions

• SQL statement script actions

Choose Actions

Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.

Select a level for your action: Detail, Summary, or No Exception.

Choose Action Details to display the Action Details window.

Select the type of action you want to create in the Action Type field

Creating an Event Alert:

Specify the name of the application and the database table that you want Oracle Alert to monitor.

Note: You cannot use a view as the event table for your alert.

Check After Insert and/or After Update if you want to run your event alert when an application user inserts and/or updates a row in the database table.

Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.

Specify a value in the End Date field if you want to disable your alert by a certain date.

Important Alert Tables:

• ALR_ALERTS
• ALR_ACTIONS
• ALR_ACTION_SETS
• ALR_ACTION_SET_INPUTS
• ALR_ACTION_SET_OUTPUTS
• ALR_ACTION_SET_MEMBERS
• ALR_ALERT_CHECKS
• ALR_ALERT_INPUTS
• ALR_ALERT_OUTPUTS
• ALR_ACTION_SET_CHECKS
• ALR_RESPONSE_SETS
• ALR_RESPONSE_ACTIONS
• ALR_VALID_RESONSES

Oracle Alert uses the following internal views:

•  ALR_ALERT_ACTIONS_VIEW
•  ALR_ALERT_HISTORY_VIEW
•  ALR_CHECK_ACTION_HISTORY_VIEW
•  ALR_INSTALLATIONS_VIEW
•  ALR_PERIODIC_ALERTS_VIEW
•  ALR_RESPONSE_ACTIONS_VIEW
•  ALR_SCHEDULED_PROGRAMS
•  ALR_VARIABLES_AND_OUTPUTS