Friday, December 16, 2011

Oracle Learning Management Tables (Oracle Appraisal Tables)

When you setup the appraisal templates in Core HR, that will be stored in "PER_APPRAISAL_TEMPLATES_V" table.

There are 3 temporary tables that hold data for entries made in SSHR until the approval cycle is complete

1) HR_API_TRANSACTIONS
2) HR_API_TRANSACTIONS_STEPS
3) HR_API_TRANSACTIONS_VALUES
Once the request is approved, data will be updated in "PER_PAY_PROPOSALS and PER_PERFORMANCE_REVIEWS_V" tables.

Below tables are interlinked:
PER_ALL_ASSIGNMENTS_F, PER_PAY_PROPOSALS and PER_PERFORMANCE_REVIEWS_V TABLES.

PER_ALL_ASSIGNMENTS_F
PER_ALL_PEOPLE_F
PER_JOBS
PER_GRADES
PER_APPRAISALS
PER_APPRAISAL_TEMPLATES
PER_ASSESSMENTS
PER_ASSESSMENT_GROUPS
PER_ASSESSMENT_TYPES
PER_COMPETENCES
PER_COMPETENCES_TL
PER_COMPETENCE_DEFINITIONS
PER_COMPETENCE_ELEMENTS
PER_PARTICIPANTS
PER_PERFORMANCE_RATINGS
PER_PERFORMANCE_REVIEWS
PER_RATING_LEVELS
PER_RATING_LEVELS_TL
PER_RATING_SCALES
PER_RATING_SCALES_TL
HR_QUESTIONNAIRES
HR_QUEST_ANSWERS
HR_QUEST_ANSWER_VALUES
HR_QUEST_FIELD
HR_ORGANIZATION_UNITS
PER_POSITIONS
---------------------------------------------------------------------------------------------------------------------------------------------
SELECT APPRAISAL_TYPE_M
,A.APPRAISEE
,C.EMPLOYEE_NUMBER
,A.MAIN_APPRAISER
,A.DEPARTMENT
,A.APPRAISAL_STATUS_M APPRAISAL_STATUS
, DECODE (B.APPRAISAL_SYSTEM_STATUS ,'ONGOING','Appraisal Pending With - '||A.MAIN_APPRAISER
,'APPRFEEDBACK','Appraisal Pending With - '||a.APPRAISEE
,'SAVED',' Appraisal Pending With - '||a.APPRAISEE
,'TRANSFER','Appraisal Pending With - '||a.APPRAISEE
,'COMPLETED', 'Appraisal Completed'
,'PENDINGAPPR','Appraisal Pending With - '||A.MAIN_APPRAISER
,B.APPRAISAL_SYSTEM_STATUS ) ownership
FROM APPS.HRFV_APPRAISAL_DETAILS A
,PER_APPRAISALS B
,PER_ALL_PEOPLE_F C
WHERE C.PERSON_ID = A.APPRAISEE_PERSON_ID
AND A.APPRAISAL_ID = B.APPRAISAL_ID
AND A.appraisal_id in ( select max(e.appraisal_id) from per_appraisals e
where e.plan_id is null
group by e.appraisee_person_id
having count(*) >= 1)
AND TRUNC (SYSDATE) BETWEEN TRUNC (C.effective_start_date)
AND TRUNC (C.effective_end_date)
order by A.APPRAISEE



SELECT bgrt.NAME business_group_name, orgt.NAME department,
apse.full_name appraisee, appr.full_name appraiser,
mapr.full_name main_appraiser, apr.appraisal_date appraisal_date,
apr.appraisal_period_start_date appraisal_start_date,
apr.appraisal_period_end_date appraisal_end_date,
apr.next_appraisal_date next_appraisal_date,
apr.comments appraisal_comments, rtt.NAME rating_level_name,
rtl.step_value step_value,
hr_bis.bis_decode_lookup ('APPRAISAL_TYPE',
apr.TYPE) appraisal_type,
hr_bis.bis_decode_lookup
('APPRAISAL_SYSTEM_STATUS',
apr.appraisal_system_status
) appraisal_status,
hr_bis.bis_decode_lookup ('APPRAISEE_ACCESS',
apr.appraisee_access
) appraisee_access,
apr.TYPE appraisal_type_code,
apr.appraisal_system_status appraisal_status_code,
apr.appraisee_access appraisee_access_code,
apr.creation_date creation_date,
apr.last_update_date last_update_date, '_DF:PER:PER_APPRAISALS:APR',
apr.appraisal_id appraisal_id,
apr.business_group_id business_group_id,
apr.appraisal_template_id appraisal_template_id,
apr.appraisee_person_id appraisee_person_id,
apr.appraiser_person_id appraiser_person_id,
apr.group_initiator_id group_initiator_id,
apr.overall_performance_level_id overall_performance_level_id,
apr.main_appraiser_id main_appraiser_id,
apr.assignment_id assignment_id, apr.event_id event_id,
rtl.rating_scale_id rating_scale_id,
rtl.competence_id competence_id,
orgt.organization_id organization_id
FROM per_appraisals apr,
hr_all_organization_units_tl bgrt,
hr_all_organization_units_tl orgt,
per_people_x apse,
per_people_x appr,
per_people_x mapr,
per_rating_levels rtl,
per_rating_levels_tl rtt
WHERE apr.business_group_id = bgrt.organization_id
AND bgrt.LANGUAGE = USERENV ('LANG')
AND apr.assignment_organization_id = orgt.organization_id
AND orgt.LANGUAGE = USERENV ('LANG')
AND apr.appraisee_person_id = apse.person_id
AND apr.appraiser_person_id = appr.person_id
AND apr.main_appraiser_id = mapr.person_id(+)
AND apr.overall_performance_level_id = rtl.rating_level_id(+)
AND rtl.rating_level_id = rtt.rating_level_id(+)
AND rtt.LANGUAGE(+) = USERENV ('LANG')
AND apr.business_group_id =
NVL (hr_bis.get_sec_profile_bg_id, apr.business_group_id)
WITH READ ONLY;

Wednesday, December 7, 2011

Confirm Receipts Workflow

Confirm Receipts workflow

Using Confirm Receipts, employees can view and receive orders in an extremely simple self service interface through Oracle Web Employees or by responding to workflow notifications. Confirm Receipts has the following major components:

A Confirm Receipts workflow process initiated after the due date of open orders
A series of web pages that enable employees to confirm receipt of orders and navigate to relevant documents
The Receiving Transaction Manager which fully validates the receipt information and completes the transaction in Purchasing
Confirm Receipts Workflow Process
The Confirm Receipt workflow process periodically polls for orders that are past due and sends workflow notifications to employees. The workflow engine drives a transaction through a process performing automated steps and invoking the appropriate agents when external processing is required. The engine monitors workflow states and coordinates routing of activities.

Confirm Receipts Notifications
The workflow engine polls for overdue orders and sends workflow notifications to the different users - typically the requester or the buyer. These notifications can be:

workflow generated notification (e.g. email)
viewed through a web browser in Oracle Web Employees
With Web-enabled notifications (email mail attachments or the inbox in Oracle Self-Service Web Applications), the user can drill down from the attached notification Web Page to the specific Receive Orders web page to view orders which are due and confirm receipt of these orders.
Confirm Receipts workflow generates notifications for an entire document if all items on that order have the same due date. If the document includes different due dates, the Confirm Receipts workflow generates a single notification per purchase order per requester per due date.


Review and Respond to Confirm Receipts Notifications
Oracle Web Employees automatically notifies employees of past due orders that have not been received. Valid responses to the notification include:
Fully Received You have fully received the items on the order.
Partially / Over Received You have received less than or more than the quantity of items you requested. In this case, you also drill down from the notification into the Receive Orders web page to record the receipt details. Oracle Web Employees automatically displays the open line items for this order. You can enter the quantity you have received.
Not Received You have not received the items included in the notification.
If the user fails to respond, it will be treated as a time out and a separate exception sub-process will be initiated.


Confirm Receipts Using "Receive Orders" Employee Self Service Web Pages
The user can confirm receipt of goods by using the Oracle Web Employees "Receive Orders" function or by responding to the workflow notification. Using the Receive Orders function in Oracle Web Employees, you can review all open orders that are past due.
However, if the page is accessed from a Confirm Receipts workflow notification, only order information associated with the notification will display on the page. Oracle Purchasing displays the unit of measure from the original purchase order or requisition.

Once you select the order lines you want to receive, press the "Submit" button. You can also identify which items you have partially or over-received, and then update the displayed quantity to reflect the quantity you have actually received.


Update Receiving in Oracle Purchasing
Once you confirm receipt of an order, either through the self-service Receive Orders web page, or by responding directly to the workflow notification, Oracle Web Employees automatically processes your receipt in Oracle Purchasing through the Receiving Transaction Processor.
The receiving transaction processor validates receipt transactions and updates the receiving history and purchase order tables in Oracle Purchasing. If the system encounters an error while processing the receiving transaction, notifications are sent to the requester and the buyer indicating the likely cause for failure.


Exception Handling
Oracle Web Employees handles the following exceptions as part of the Confirm Receipts workflow process:
Time Out Once a notification is sent, the process will be in a wait state to get a response from the user. If there is no response from the user, the system will treat it as a timeout and resend the notification after one week as a first reminder. If there is no response for another week, the notification is resent as a second reminder. If the user does not respond to the second reminder within twenty-four hours, a notification is sent to the user's manager.
Receiving Controls The receipt confirmation process enforces the receiving controls supported in Oracle Purchasing. For example, the system rejects received quantities that are greater than the tolerance level.


Configure the Receive Orders Web Page
Oracle Web Employees allows you to use the Web Applications Dictionary technology (a.k.a. Object Navigator) to tailor the layout and content of the Receive Orders self service web page. Use the Web Applications Manager to select the fields to display, the field, prompts, length, formatting characteristics, and include certain input fields as part of the receipt confirmation.

Setting Up Web Users
Each employee that will confirm receipts through Oracle Web Employees must be defined as a web user. To setup the Receive Orders Web Page for an employee you must:

Define a web user for employees who will receive orders through the web
Assign the Receive Orders functions to that user's list of responsibilities/functions
Respond to Receipt Confirmation Notifications
To respond to workflow generated receipt confirmation requests:

Login to Oracle Web Employees and select the "View Notifications" function.
Select a response to the receipt confirmation request:
Fully Received.
Over/Partially Received.
Not Received.
For partially/over received orders, drill down from the notification into the Receive Orders web page. Record your receipt details.
Select the "Submit Response" button to complete the response and initiate the next workflow activity.