, , , ,

Query to extract Employee Contact Information

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = ‘CONTACT’
AND hl.lookup_code(+) = pcr.contact_type
/by
, , , ,

Workflow Tables Information in R12

WF_ITEM_TYPES – It defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE

WF_ITEM_ATTRIBUTES – stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITIES -table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG

WF_ACTIVITY_ATTRIBUTES – table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGES – contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

WF_NOTIFICATIONS holds the runtime information about a specific instance of a sent message. A new row is created in the table each time a message is sent.

WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE

WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK)

WF_ITEM_ACTIVITY_STATUSES_H – table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY

WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL

WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

SELECT * FROM WF_USER_ROLE_ASSIGNMENTS
SELECT * FROM WF_USER_ROLES
SELECT * FROM WF_ROLES
SELECT * FROM WF_ITEMS
SELECT * FROM WF_ITEM_ATTRIBUTES
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES
SELECT * FROM WF_ITEM_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITIES
SELECT * FROM WF_ACTIVITIES_TL
SELECT * FROM WF_ACTIVITY_ATTRIBUTES
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITY_TRANSITIONS
SELECT * FROM WF_DEFERRED
/by
, , , ,

A comparative analysis between SQL*LOADER and UTL_FILE utility

In implementing new systems we come across problems of importing “alien” data. This may be coming from a legacy system or an on-going system. This data is transported via extract files from the legacy system to the Oracle system. The gateway to Oracle for this data is SQL*Loader and data is loaded into tables via a control script into tables.

Typically, the older systems do not have very normalized data, nor have they been operating with fully implemented database constraints. The lack of constraints over the years in legacy system can lead to bad data that has crept in. Therefore, while bringing external data into oracle system we need a refined set of checks and balances to ensure that we get good data. This requires a lot of programmatic control in the process of data-loading.

The approach applied in case of SQL* Loader is as follows :
1. Load the data into temporary tables via SQL*Loader via control file and make the data native to ORACLE.
2. Write a PL/SQL program to do the processing.
3. Load the data into live tables.

This approach has a lot of dependencies as well as a strong lack of integration of steps and programmatic control. To overcome this, we have analyzed another facility in that has been release Oracle 7.3.x onwards. It is called the UTL_FILE package. With some creative use of this package we can achieve whatever  SQL*LOADER offers and in addition to that do some high level validation and complex data loading. In the following discussion a study of two tools is done.

A BRIEF OVERVIEW OF SQL*Loader:
SQL*Loader is a server utility for loading data from external data files into Oracle database. The basic advantage of using SQL*Loader is for simple loads and fast loading of data. It can load data into myriad data formats, perform elementary filtering, load data into multiple tables, and create one logical record from one or more physical records.

It creates a detailed log file, a bad file that contains rejected records and a discard file to hold the records that are selectively not loaded. The tool is executed from a command line and a username and password and the control file name and location are required to run it.

A BRIEF OVERVIEW OF UTL_FILE:
PL/SQL does not have text file input output capabilities but acquires it via UTL_FILE package. It provides rudimentary utility for reading ( as well as writing) files from within a PL/SQL program. The lines in the file are read sequentially and hence it effects the performance of the program.

The UTL_FILE package can be wrapped around with a PL/SQL program and since this package is integrated with PL/SQL it provides us the tremendous ability for flexing our “programming muscles.” Some procedures and functions can be added to this wrapper program that serve as a handy “tool” for doing normal file reading operations. With this approach we can achieve whatever SQL*Loader can do and much more. The security mechanism for UTL_FILE is achieved by defining a parameter in INIT.ora file called utl_file_dir parameter. The directories that UTL_FILE can read from and write to need to have permissions of Oracle
instance owner and the user running the package.

CONCLUSIONS:
The comparative analysis of SQL*Loader and UTL_FILE reveals that these tools are suitable to our environment subject to the conditions of our needs.

If the data load is complex (as is the case in relational databases) UTL_FILE seems to be the tool of choice. This tool does require programmatic effort in terms of writing a wrapper package but the subsequent effort in this direction is greatly reduced once the initial tool kit is built for your environment.

The UTL_FILE tightly integrates the file input with the programmatic control and the data manipulation inside a single PL/SQL unit. There are disadvantages of speed in loading in case of UTL_FILE but these are totally offset by the programmatic control it offers and the integration it brings in.

Thus we find that UTL_FILE tool bridges the gap left by SQL*Loader for complex data loads.

/by
, , , , ,

Script to display status of all the Concurrent Managers

SELECT DISTINCT concurrent_process_id “Concurrent Process ID”,
       pid “System Process ID”, os_process_id “Oracle Process ID”,
       q.concurrent_queue_name “Concurrent Manager Name”,
       p.process_status_code “Status of Concurrent Manager”,
       TO_CHAR(p.process_start_date,’MM-DD-YYYY HH:MI:SSAM’) “Concurrent Manager Started at”
  FROM fnd_concurrent_processes p,
       fnd_concurrent_queues q,
       fnd_v$process
 WHERE q.application_id = queue_application_id
   AND q.concurrent_queue_id = p.concurrent_queue_id
   AND spid = os_process_id
   AND process_status_code NOT IN (‘K’, ‘S’)
ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name
/by
, , , ,

How to get the create statement of sql view

SELECT owner “view owner name”, view_name “view name”
FROM all_views
WHERE view_name = :view_name

SELECT DBMS_METADATA.get_ddl (‘VIEW’, ‘view name’,
‘view owner name’)
FROM DUAL

/by