Playing with Dates in Oracle!

In and around Oracle Application, as a developer you need to play with Dates while writing your code. Playing with dates is fun, although sometimes we faced some challenging tasks. Therefore it is always helpful to go through this feature of Oracle in detail. I did the same and here is what I have found.

Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-YY. Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999 A.D.

Unlike other datatypes, DATE datatypes are bit complex. However, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.

Get the current date and time:

Often it is required to retrieve the current date and time in our code and use them. Many developers go with SYSDATE function, but Oracle Database now offers several other functions as well.

SELECT SYSDATE FROM DUAL;
--27-FEB-12 (You will get the DATE from Oracle Database Server)
SELECT CURRENT_DATE FROM DUAL;
--27-FEB-12 (You will get the Current DATE from Oracle Session Time Zone)
SELECT LOCALTIMESTAMP FROM DUAL;
--27-FEB-12 01.16.42.486809000 AM (You will get the TIMESTAMP from Oracle Session Time Zone)
SELECT SYSTIMESTAMP FROM DUAL;
--27-FEB-12 01.19.41.059413000 AM -06:00 (You will get the TIMESTAMP WITH TIME ZONE from Oracle Database Server)
SELECT CURRENT_TIMESTAMP FROM DUAL;
--27-FEB-12 01.20.57.839733000 AM AMERICA/CHICAGO (You will get the TIMESTAMP WITH TIME ZONE from Oracle Session Time Zone)

DATE Format:

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle’s default format for DATE is “DD-MON-YY”. Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format.

Guidelines

• The format model must be enclosed in single quotation marks and is case sensitive.
• The format model can include any valid date format element. Be sure to separate the date value from the format model by a comma.
• The names of days and months in the output are automatically padded with blanks.
• To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

Examples:

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd') FROM DUAL; -- Returns 2012/02/27
SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM DUAL; -- Returns February  27, 2012
SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL; -- Returns February 27, 2012
SELECT TO_CHAR(SYSDATE, 'MON DDth, YYYY') FROM DUAL; -- Returns FEB 27TH, 2012
SELECT TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') FROM DUAL; -- Returns FEB 27TH, 2012
SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL; -- Returns February 27, 2012
SELECT TO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY') FROM DUAL; -- Returns Monday, 27TH February 2012
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- Returns 2012-02-27 02:05:59
SELECT TO_CHAR(SYSDATE, 'FMDdspth "of" Month YYYY FMHH:MI:SS AM') FROM DUAL; -- Returns Twenty-Seventh of February 2012 02:06:36 AM
SELECT TO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY','NLS_DATE_LANGUAGE=Spanish') FROM DUAL; -- Returns Lunes, 27TH Febrero 2012
SELECT TO_CHAR(SYSDATE, 'Day') TODAY_EN,
TO_CHAR(sysdate, 'Day', 'nls_date_language=Dutch') TODAY_DT
FROM DUAL; -- Returns Monday   	Maandag

TO_DATE & TO_TIMESTAMP Functions:

TO_DATE function converts a character string representing a date to a date value according to the fmt specified. If fmt is omitted, the format is DD-MON-YY. The nlsparams parameter has the same purpose in this function as in the TO_CHAR function for date conversion.

TO_DATE(char,[fmt],[nlsparams])

The DD-MON-YY format is usually used to insert a date value. If a date must be entered in a format other than the default format, for example, with another century, or a specific time, you must use the TO_DATE function.

Examples:

SELECT TO_DATE('2012/02/27', 'yyyy/mm/dd') FROM DUAL;
SELECT TO_DATE('022712', 'MMDDYY') FROM DUAL;
SELECT TO_DATE('20120227', 'yyyymmdd') FROM DUAL;
SELECT TO_DATE('February 27, 2012, 04:00 P.M.','Month dd, YYYY, HH:MI A.M.')FROM DUAL;

Similarly TO_TIMESTAMP used to convert char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

Examples:

SELECT TO_TIMESTAMP('2012/FEB/27 04:12:34', 'YYYY/MON/DD HH:MI:SS') FROM DUAL;
SELECT TO_TIMESTAMP('February 27, 2012, 04:12:34 P.M.','Month dd, YYYY, HH:MI:SS A.M.')FROM DUAL;
SELECT TO_TIMESTAMP ('27-Feb-12 04:12:34.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

Arithmetic with Dates:

Here are 3 golden roles:

1. You can compare DATE values using the standard comparison operators such as =, !=, >, etc.
2. You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.
3. You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.

Date Functions:

Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

• MONTHS_BETWEEN (date1, date2):

This function returns the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

• ADD_MONTHS(date, n):

This function adds n number of calendar months to date. The value of n must be an integer and can be negative.

• NEXT_DAY(date, ‘char’):

This function finds the date of the next specified day of the week (‘char’) following date. The value of char may be a number representing a day or a character string.

• LAST_DAY(date):

This function finds the date of the last day of the month that contains date.

• ROUND(date[,’fmt’]):

This function returns date rounded to the unit specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.

• TRUNC(date[, ‘fmt’]):

This function returns date with the time portion of the day truncated to the unit specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

Examples:

SELECT SYSTIMESTAMP FROM DUAL;  --Returns 27-FEB-12 05.16.41.676947000 AM -06:00
SELECT MONTHS_BETWEEN(SYSDATE , TO_DATE('01-JAN-2012','DD-MON-YYYY')) FROM DUAL; --Returns months between Sysdate and '01-JAN-2012' (1.84580906511350059737156511350059737157)
SELECT ADD_MONTHS (SYSDATE, 1) FROM DUAL; -- Move ahead one month (27-MAR-12)
SELECT ADD_MONTHS (SYSDATE, -4) FROM DUAL; -- Move backward four months (27-OCT-11)
SELECT NEXT_DAY (SYSDATE, 'MONDAY') FROM DUAL;  -- Go to next Monday after today’s date (05-MAR-12)
SELECT LAST_DAY (SYSDATE) FROM DUAL;  -- Returns the last day of the month (29-FEB-12)
SELECT ROUND (SYSDATE, 'MONTH') FROM DUAL; --01-MAR-12
SELECT TRUNC (SYSDATE, 'MONTH') FROM DUAL; --01-FEB-12
SELECT ROUND (SYSDATE, 'YEAR') FROM DUAL;  --01-JAN-12
SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL;  --01-JAN-12
SELECT ROUND (SYSDATE, 'DAY') FROM DUAL;   --26-FEB-12
SELECT TRUNC (SYSDATE, 'DAY') FROM DUAL;   --26-FEB-12

EXTRACT Function:

An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation

The syntax of EXTRACT function is

EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

Example:

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; --Returns 2012
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --Returns 2
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --Returns 27
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM DUAL; --Returns 11
SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM DUAL; --Returns 34

Have  a nice Day!

Understanding the Workflow Definition (Set up) Tables

Oracle workflow is also a database application as many other application of Oracle, which means that it also utilizes database tables as the basis of its operation. Behind its very pleasant and user-friendly GUI, It’s the database tables which store every piece of information regarding the attributes, functions, process, messages you create while designing a workflow. If you really want to know Workflow and discover how it works, you have to understand its table structures.

In this article, I have covered the tables which got affected, when you create or modify a workflow process. However it doesn’t include the tables which capture information at run time when you run a workflow. I have taken the ‘PO Approval Workflow’ (POAPPRV) for example purpose.

1] WF_ITEM_TYPES:

The wf_item_types table contains one record for each item_type created. The eight character name of the item_type represents the “Internal Name” of the item. It also functions as the primary key for this table. Some key columns are:

• NAME: It is a mandatory field. It represents the internal name of the item type.
• PROTECT_LEVEL: Level at which the data is protected. A mandatory field.
• CUSTOM_LEVEL: Level of user who last updated the row. Again a mandatory field.
• WF_SELECTOR: It stores the name of the PL/SQL procedure which implements selector function. This is an optional field.
• PERSISTENCE_TYPE: Indicates whether item type is temporary or permanent.
• PERSISTENCE_DAYS: Number of days until purge if persistence is temporary.

Workflow Item Type Display Name and description can be found in WF_ITEM_TYPES _TL table. Also check the view WF_ITEM_TYPES_VL.

 

SELECT * FROM WF_ITEM_TYPES WHERE NAME='POAPPRV';
SELECT * FROM WF_ITEM_TYPES_TL WHERE NAME='POAPPRV';
SELECT * FROM WF_ITEM_TYPES_VL WHERE NAME='POAPPRV';

2] WF_ITEM_ATTRIBUTES:

This table stores definitions of attributes associated with a process. The entries in this table correspond to the “Attributes” subheading in the Workflow Builder. An item attribute works like a variable which can hold values that are specific to the process instance or which may change at run time. Some key columns are:

• ITEM_TYPE: Internal name for the item type that owns the attribute. A mandatory field.
• NAME: Internal name of the attribute. A mandatory field.
• SEQUENCE: Order of the attribute within the message
• TYPE: Each item attribute is assigned a datatype, such as “Character”, “Number”, or “Date”.

There are three fields to hold a default value, but only one of them will be populated for any item attribute, depending upon the datatype. For example, if you create an item attribute with a datatype of “Number”, and then supply a default value, that value would be stored in the “number_default” field.

The “format” field stores information about a format mask that should be applied to number or date values, and the “subtype” field contains “SEND” or “RECEIVE”. The Translation table is WF_ITEM_ATTRIBUTES_TL and the related view is WF_ITEM_ATTRIBUTES_VL.

 

SELECT * FROM WF_ITEM_ATTRIBUTES WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';
SELECT * FROM WF_ITEM_ATTRIBUTES_VL WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';

3] WF_ACTIVITIES:

This table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modeled workflow process, which can be included as an activity in other processes to represent a sub-process. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, but it provides a means of grouping activities. Some key columns are:

• ITEM_TYPE: Internal name for the Item Type that owns the message.
• NAME: Internal name for the activity.
• VERSION: It is used to support multiple versions of the same process running at the same time. The version number works in concert with the “begin_date” and “end_date” fields, to ensure that only one version of any activity is active at any given time. By versioning, the previously launched processes retain the process definition that was in force at the time they were launched.
• TYPE: The “type” field is the way that the individual types of activities can be distinguished. There are five valid values found in the “type” field: “FUNCTION”, “NOTICE”, “EVENT”, “PROCESS”, and “FOLDER”.
• RERUN: Determines if activity is rerun during looping.
• EXPAND_ROLE: Determines how many roles are required to respond to a notification activity.
• FUNCTION: For function activities only, the field is used to store the name of the PLSQL procedure that the Workflow Engine should call to implement the function.
• RESULT_TYPE: If you intend to model transitions in a process based upon values returned by an activity node, then the expected results must be predefined by supplying a lookup type, which is stored in this field.
• ICON_NAME: Name of activity icon used in process window.
• MESSAGE: For notification activities only, the field called “message” will be populated. In these cases, it will contain the internal name of the message that the notification will deliver.
• ERROR_PROCESS: Workflow process to run in case of an error.
• ERROR_ITEM_TYPE: Name of item type to execute in case of error.
• RUNNABLE_FLAG: Flag (Y or N) to indicate if activity is runnable.
• FUNCTION_TYPE: Indicates whether function type is pl/sql or internal.

The Translation table is WF_ACTIVITIES_TL and the related view is WF_ACTIVITIES_VL.

SELECT * FROM WF_ACTIVITIES WHERE ITEM_TYPE='POAPPRV'
AND NAME='FIND_APPROVER';

SELECT * FROM WF_ACTIVITIES_TL WHERE ITEM_TYPE='POAPPRV'
AND NAME='FIND_APPROVER';

SELECT * FROM WF_ACTIVITIES_VL WHERE ITEM_TYPE='POAPPRV' AND NAME='FIND_APPROVER';

4] WF_ACTIVITY_ATTRIBUTES:

This table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities. Each row includes the associated activity, type of attribute, and the format used by the activity. Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2. Notice that the table requires three fields just to identify to which activity the attribute is attached: the item_type, name, and version of the activity. To join this table to the wf_activities tables you must join all three of these fields to their corresponding fields in that table. Some key columns are:

• ACTIVITY_ITEM_TYPE: Item type the activity is associated with
• ACTIVITY_NAME: Internal name of the activity
• ACTIVITY_VERSION: Version of the activity
• NAME: Internal name of the attribute
• SEQUENCE: Order of the attribute within the message
• TYPE: This field refers to the datatype of the values that the attribute will contain.
• VALUE_TYPE: Defines if the default is a constant or a reference to an item attribute.

The Translation table is WF_ACTIVITY_ATTRIBUTES_TL and the related view is WF_ACTIVITY_ATTRIBUTES_VL.

SELECT *
FROM WF_ACTIVITY_ATTRIBUTES
WHERE ACTIVITY_ITEM_TYPE='POAPPRV'
AND ACTIVITY_NAME       ='GET_NOTIFICATION_ATTRIBUTE'
AND ACTIVITY_VERSION    =
  (SELECT VERSION
  FROM WF_ACTIVITIES
  WHERE ITEM_TYPE='POAPPRV'
  AND NAME       ='GET_NOTIFICATION_ATTRIBUTE'
  AND TRUNC(SYSDATE) BETWEEN TRUNC(BEGIN_DATE) AND TRUNC(NVL(END_DATE,SYSDATE))
  );

5] WF_ACTIVITY_ATTR_VALUES:

This table used to track values contained in activity attributes. This table is identical in purpose to wf_item_attribute_values except it holds values for activity attributes instead of item attributes. Each row includes the process activity id and the associated value for the attribute. The interesting thing about this table is that it uses the process_activity_id to identify the activity to which the attribute is attached. The same activity can be inserted into a process more than one time, so the only way to uniquely identify the node to which this attribute is attached is to use the process_activity_id.

SELECT * FROM WF_ACTIVITY_ATTR_VALUES WHERE NAME='NTF_USER_NAME';

6] WF_MESSAGES:

The messages that are associated with notifications are stored in this table. Each message, which is uniquely identified by the combination of item_type and message_name (stored in the fields “type” and “name”) receives a single record in the wf_messages table. The actual text of the message is stored only in its localization table (wf_messages_tl). They can found in the “body” and “html_body” fields.

SELECT * FROM WF_MESSAGES
WHERE TYPE='POAPPRV' AND NAME='NOTIFY_BUYER';

SELECT * FROM WF_MESSAGES_TL
WHERE TYPE='POAPPRV' AND NAME='NOTIFY_BUYER';

7] WF_MESSAGE_ATTRIBUTES:

This table contains message attribute definitions. Each message may have zero or more message attributes. Message attributes define additional information that is to be sent to, or received from the user. These attributes can be used as tokens in the subject or body of a message template to place variables values into the message at runtime.

SELECT * FROM WF_MESSAGE_ATTRIBUTES
WHERE MESSAGE_TYPE='POAPPRV'
AND MESSAGE_NAME  ='NOTIFY_BUYER'
AND NAME          ='BUYER_DISPLAY_NAME';

8] WF_PROCESS_ACTIVITIES:

A process is a sequence of activities performed in a pre-determined order. When you create a process definition in the Workflow Builder by dragging various notifications and functions into the process window, the records created by the Builder are stored into this table.

SELECT * FROM WF_PROCESS_ACTIVITIES
WHERE PROCESS_ITEM_TYPE='POAPPRV'
AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS';

9] WF_ACTIVITY_TRANSITIONS:

The flow of a process from node to node as indicated by the transition arrows is not saved in the wf_process_activities table. Instead this information is stored in this table.

A transition is defined by three discrete pieces of information: the node where the arrow begins, the node toward which the arrow points, and the result which, when returned by the beginning node, causes the transition to be followed. Not surprisingly, it is those three fields which are the most important fields in this table: “from_process_activity”, “to_process_activity”, and “result_code”. The values stored in “from_process_activity” and “to_process_activity” are numbers which represent the instance_id of the records from wf_process_activities from which and to which the transition is moving.

SELECT *
FROM WF_ACTIVITY_TRANSITIONS
WHERE FROM_PROCESS_ACTIVITY =
  (SELECT INSTANCE_ID
  FROM WF_PROCESS_ACTIVITIES
  WHERE PROCESS_ITEM_TYPE='POAPPRV'
  AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
  AND INSTANCE_LABEL     ='START'
  AND PROCESS_VERSION    =
    (SELECT MAX(PROCESS_VERSION)
    FROM WF_PROCESS_ACTIVITIES
    WHERE PROCESS_ITEM_TYPE='POAPPRV'
    AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
    AND INSTANCE_LABEL     ='START'
    )
  )
AND TO_PROCESS_ACTIVITY =
  (SELECT INSTANCE_ID
  FROM WF_PROCESS_ACTIVITIES
  WHERE PROCESS_ITEM_TYPE='POAPPRV'
  AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
  AND INSTANCE_LABEL     ='IS_DOCUMENT_APPROVED'
  AND PROCESS_VERSION    =
    (SELECT MAX(PROCESS_VERSION)
    FROM WF_PROCESS_ACTIVITIES
    WHERE PROCESS_ITEM_TYPE='POAPPRV'
    AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
    AND INSTANCE_LABEL     ='IS_DOCUMENT_APPROVED'
    )
  );

10] WF_LOOKUP_TYPES_TL & WF_LOOKUPS_TL:

Wf_lookup_types_tl is the table used to set up the types of results expected from Workflow activities like functions and notifications. This table does not contain the actual result values, it holds the groupings of the result_codes – the names you see in the Workflow Builder as the names of the Lookups. Wf_lookups_tl is the table that stores the component values that comprise a lookup_type.

SELECT *
FROM WF_LOOKUP_TYPES_TL
WHERE ITEM_TYPE='POAPPRV'
AND LOOKUP_TYPE='PO_POAPPRV_APPROVE_ACTION';

SELECT * FROM WF_LOOKUPS_TL
WHERE LOOKUP_TYPE='PO_POAPPRV_APPROVE_ACTION';

API to add Classification to an Oracle Project

The below API can be used to add a Classification to an Oracle Project.

DECLARE
  l_project_id pa_projects_all.project_id%type            :=NULL;
  l_class_category pa_project_classes.class_category%type :=NULL;
  l_class_code pa_project_classes.class_code%type         :=NULL;
  l_return_status VARCHAR2(20);
  l_msg_count     NUMBER;
  l_msg_data      VARCHAR2(240);
BEGIN
  L_PROJECT_ID     := &P_PROJECT_ID;
  L_CLASS_CATEGORY := &P_CLASS_CATEGORY;
  l_class_code     := &p_class_code;
pa_projects_maint_pub.create_classifications
            (p_api_version                  => 1.0                   ,
            p_init_msg_list                 => fnd_api.g_true        ,
            p_commit                        => fnd_api.g_false       ,
            p_validate_only                 => fnd_api.g_false        ,
            p_validation_level              => fnd_api.g_valid_level_full,
            p_calling_module                => 'SELF_SERVICE'        ,
            p_debug_mode                    => 'N',
            p_max_msg_count                 => fnd_api.g_miss_num    ,
            p_object_id                     => l_project_id,
            p_object_type                   => 'PA_PROJECTS',
            p_class_category                => l_class_category       ,
            p_class_code                    => l_class_code           ,
            p_code_percentage               => fnd_api.g_miss_num    ,
            p_attribute_category            => fnd_api.g_miss_char   ,
            p_attribute1                    => fnd_api.g_miss_char   ,
            p_attribute2                    => fnd_api.g_miss_char   ,
            p_attribute3                    => fnd_api.g_miss_char   ,
            p_attribute4                    => fnd_api.g_miss_char   ,
            p_attribute5                    => fnd_api.g_miss_char   ,
            p_attribute6                    => fnd_api.g_miss_char   ,
            p_attribute7                    => fnd_api.g_miss_char   ,
            p_attribute8                    => fnd_api.g_miss_char   ,
            p_attribute9                    => fnd_api.g_miss_char   ,
            p_attribute10                   => fnd_api.g_miss_char   ,
            p_attribute11                   => fnd_api.g_miss_char   ,
            p_attribute12                   => fnd_api.g_miss_char   ,
            p_attribute13                   => fnd_api.g_miss_char   ,
            p_attribute14                   => fnd_api.g_miss_char   ,
            p_attribute15                   => fnd_api.g_miss_char   ,
            x_return_status                 => l_return_status  ,
            x_msg_count                     => l_msg_count    ,
            x_msg_data                      => l_msg_data);
            COMMIT;

  dbms_output.put_line('Status:'||l_return_status);
  dbms_output.put_line('Message:'||l_msg_data);
END;

Query to find Project Manager Info of an Oracle Project

The below query can be useful when you are working in Oracle Projects and you need to find out the Project Manager Name and Email Address for a particular project.

 SELECT PE.FULL_NAME,
  PE.EMAIL_ADDRESS
FROM PA_PROJECT_PARTIES PPP,
  PA_PROJECTS_ALL PPA,
  PA_PROJECT_ROLE_TYPES PPRT,
  PER_ALL_PEOPLE_F PE,
  PA_PROJECT_ASSIGNMENTS PA,
  FND_USER U,
  (SELECT PJ.NAME JOB_NAME,
    HAOU.ORGANIZATION_ID ORG_ID,
    HAOU.NAME ORG_NAME,
    PAF.PERSON_ID,
    PAF.ASSIGNMENT_TYPE
  FROM PER_ALL_ASSIGNMENTS_F PAF,
    PER_JOBS PJ,
    HR_ALL_ORGANIZATION_UNITS HAOU
  WHERE TRUNC(SYSDATE) BETWEEN TRUNC(PAF.EFFECTIVE_START_DATE) AND TRUNC(PAF.EFFECTIVE_END_DATE)
  AND PAF.PRIMARY_FLAG     = 'Y'
  AND PAF.ORGANIZATION_ID  = HAOU.ORGANIZATION_ID
  AND NVL(PAF.JOB_ID, -99) = PJ.JOB_ID(+)
  ) PRD
WHERE PPA.PROJECT_ID                                              = :P_PROJECT_ID
AND DECODE(PA.ASSIGNMENT_ID,NULL,PPRT.MEANING,PA.ASSIGNMENT_NAME) = 'Project Manager'
AND PPP.PROJECT_ID                                                = PPA.PROJECT_ID
AND PPP.PROJECT_ROLE_ID                                           = PPRT.PROJECT_ROLE_ID
AND PPP.RESOURCE_SOURCE_ID                                        = PE.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPP.START_DATE_ACTIVE) AND NVL(TRUNC(PPP.END_DATE_ACTIVE),SYSDATE)
AND PE.EFFECTIVE_START_DATE =
  (SELECT MIN(PAPF.EFFECTIVE_START_DATE)
  FROM PER_ALL_PEOPLE_F PAPF
  WHERE PAPF.PERSON_ID         =PE.PERSON_ID
  AND PAPF.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
  )
AND PE.EFFECTIVE_END_DATE          >=TRUNC(SYSDATE)
AND PPP.PROJECT_PARTY_ID            = PA.PROJECT_PARTY_ID(+)
AND NVL(PRD.ASSIGNMENT_TYPE,'-99') IN ('C',DECODE(DECODE(PE.CURRENT_EMPLOYEE_FLAG,'Y','Y', DECODE(PE.CURRENT_NPW_FLAG,'Y','Y','N')),'Y','E', 'B'),'E', '-99')
AND PPP.RESOURCE_SOURCE_ID          = PRD.PERSON_ID(+)
AND U.EMPLOYEE_ID (+)               = PPP.RESOURCE_SOURCE_ID
AND PPP.OBJECT_TYPE                 = 'PA_PROJECTS'
AND ppp.object_id                   = ppa.project_id;

Oracle also has provided a seeded package called PA_PROJECT_PARTIES_UTILS that have couple of good functions that can be used to get Project Parties information.