Call DFF segments e.g SIT in hrms

How to create a package to call the SIT which will store the flexvalues in different segments.

e,g Code:

1.Note down the flexstrcture name and segment.
2.Call the flex value in the Function.
3.pass the flex value in query


CREATE OR REPLACE function xxri_passport_num(p_id_flex_num in number,p_person_id in number) return varchar2

is
l_passport varchar2(240);
begin
select pac.segment1 into l_passport from per_person_analyses ppa,per_analysis_criteria pac
where ppa.ANALYSIS_CRITERIA_ID=pac.ANALYSIS_CRITERIA_ID and ppa.id_flex_num =p_id_flex_num and ppa.person_id=p_person_id;
return l_passport;
end xxri_passport_num;
/

select xxri_passport_num(50348,61) passport from dual;

WORKFLOW

Components:

Oracle Workflow is a series of tools designed to facilitate the creation and management of business process models. Oracle Workflow is comprised of:

• Workflow engine – implements the workflow process definitions at runtime monitoring the states and coordinating the routing of process activities
• The workflow engine is a database package containing procedures run on a schedule. The workflow engine recognizes activities meeting specific criteria and only operates on those activities.
• Through the use of standard APIs, background engines can be scheduled to target activities meeting other specified criteria, such as deferred and stuck status activities.
• Business Event System – utilizes Oracle Advanced Queuing infrastructure to communicate information between systems.Employs agents watching for subscriptions to user-defined events.Event information propagated across systems via queues using specified payload (defined data structure).
• Workflow Monitor – allows monitoring and administration of defined workflows via standard web browser capable of supporting Java
• Workflow Builder - graphical tool that facilitates the modeling of business processes with simple drag-and-drop operations

Oracle Workflow Builder:

Oracle Workflow Builder displays a navigator window with a hierarchy tree containing the following tree nodes:

Item Type – classification of the components that comprise the workflow process definition
Attributes – property associated with a given item type that acts as a global variable that can be referenced or updated by any activity within a process or externally through the use of standard APIs
Processes – a collection of activities in a specific relationship
Notifications – allow for definition of how a message will be used within the process model as an activity.
Functions – defined by the PL/SQL or external program it calls, usually performing fully automated process steps

Events – a business event from the Business Event System
Messages – defines the information conveyed in a notification
Lookup Types –a static list of values that can be used by messages and activity attributes

OUTBOUND-UTL PACKAGE

UTL_FILE PACKAGE



The UTL_FILE package provides text file I/O from within PL/SQL. The init.ora file, the initialization parameter UTL_FILE_DIR is set to the accessible directories desired.
UTL_FILE_DIR = directory_name

Function & Procedure Description

• FOPEN A function that opens a file for input or output and returns a file handle used in subsequent I/O operations
• IS_OPEN A function that returns a Boolean value whenever a file handle refers to an open file
• GET_LINE A procedure that reads a line of text from the opened file and places the text in the Output buffer parameter (the maximum size of an input record is 1,023 bytes unlessyou specify a larger size in the overloaded version of FOPEN)
• PUT, PUT_LINE A procedure that writes a text string stored in the buffer parameter to the opened file (no line terminator is appended by put; use new_line to terminate the line, or use PUT_LINE to write a complete line with a terminator)
• PUTF A formatted put procedure with two format specifiers: %s and \n (use %s to substitute a value into the output string. \n is a new line character)
• NEW_LINE Procedure that terminates a line in an output file
• FFLUSH Procedure that writes all data buffered in memory to a file
• FCLOSE Procedure that closes an opened file
• FCLOSE_ALL Procedure that closes all opened file handles for the session
• Exceptions to the UTL_FILE Package Exception Name Description
• INVALID_PATH The file location or filename was invalid.
• INVALID_MODE The OPEN_MODE parameter in FOPEN was invalid.
• INVALID_FILEHANDLE The file handle was invalid.
• INVALID_OPERATION The file could not be opened or operated on as requested.
• READ_ERROR An operating system error occurred during the read operation.
• WRITE_ERROR An operating system error occurred during the write operation.
• INTERNAL_ERROR An unspecified error occurred in PL/SQL.

UTL_FILE procedures can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or
VALUE_ERROR.


SQL * Loader


D:\oracle\prodora\8.0.6\BIN>SQLLDR scott/tiger@prod control='e:\vbbk\myin.ctl'

New Form Quick Review

New Form Development
Edit PRE‐FORM trigger at form level
FND_STANDARD.FORM_INFO('$Revision: 115.12 $', 'JP Media Form', 'JP','$Date: 2003/12/19 11:02 $', '$Author: appldev $');
app_standard.event('PRE-FORM');
app_window.set_window_position('MYWINDOW', 'FIRST_WINDOW');

Edit the program unit’s APP_CUSTOM body


if (wnd = 'MYWINDOW') then
app_window.close_first_window;
elsif (wnd = '') then
null;
elsif (wnd = '') then
null;
end if;


Application Application -> Register
Form Application-> Form
Function Application-> Function
Menu Application-> Menu
Responsibility Security->Responsibility ->Define
User Security-> User -> Define

WHO Columns

CREATED_BY  :NUMBER(15) NOT NULL Keeps track of which user created each row
Value:TO_NUMBER(FND_PROFILE.VALUE(’USER_ID’))
CREATION_DATE :DATE NOT NULL:Stores the date on which each row was created
LAST_UPDATED_BY :NUMBER(15) NOT NULL Keeps track of who last updated each row
Value:TO_NUMBER(FND_PROFILE.VALUE(’USER_ID’))
LAST_UPDATE_DATE: DATE NOT NULL:Stores the date on which each row was last updated
LAST_UPDATED_LOGIN :NUMBER(15):Provides access to Information about the operating system login of the user who last updated each row Value: TO_NUMBER(FND_PROFILE.VALUE(’LOGIN_ID’))

HRMS Tables

HRMS BASE TABLES:(Table _F are date tracked tables)

• PER_ALL_PEOPLE_F: DateTracked table holding personal information for employees, applicants and other people. Primary Key:PERSON_ID .The columns START_DATE,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE are all maintained by DateTrack. The START_DATE is the date when the first record for this person was created.

(Object Version Number: Keeps track of Date change.The triggers maintains the object_version_number column when a record is modified via an interface which does not support the object_version_number explicitly. This ensures the object_version_number is maintained consistently.)
                    Database Triggers:
                 Trigger Name : SSP_PER_T1
                 Trigger Time : AFTER
                 Trigger Level : ROW
                 Trigger Event : UPDATE

                Trigger Name : PER_ALL_PEOPLE_F_OVN
                Trigger Time : BEFORE
                Trigger Level : ROW
                Trigger Event : INSERT, UPDATE

• PER_ALL_ASSIGNMENTS_F:Employee and applicant assignment details.And is the DateTracked table.The ASSIGNMENT_TYPE is E for an employee assignment, and A for an applicant assignment.EX_EMP for an X-Employee.Primary Key:ASSIGNMENT_ID.Employee can have Multiple Assignments But need to hold one primary Assignments.

           Database Triggers
           Trigger Name : PER_ALL_ASSIGNMENTS_F_OVN
           Trigger Time : BEFORE
           Trigger Level : ROW
           Trigger Event : INSERT, UPDATE

• PER_ASSIGNMENT_INFO_TYPES:The definitions of extra information types that may be held against an assignment.Primary_key:INFORMATION_TYPE
• PER_ASS_STATUS_TYPE_AMENDS:Amendments to predefined assignment status types.Primary Key:ASSIGNMENT_STATUS_TYPE_ID.The HRMS system Always checks the PER_ASS_STATUS_TYPE_AMENDS table for the existence of status types before checking the PER_ASSIGNMENT_STATUS_TYPES table.
• PER_GRADES Grade definitions for a business group.Primary Key:GRADE_ID
• PER_JOBS Jobs defined for a Business Group
• PER_PEOPLE_INFO_TYPES The table holds the definitions of extra information types that may be held against a person.
• PER_PERIODS_OF_SERVICE:Period of service details for an employee.Primary_id:PERIOD_OF_SERVICE_ID.

            Database Triggers
           Trigger Name : SSP_PDS_T2
           Trigger Time : AFTER
           Trigger Level : STATEMENT
           Trigger Event : UPDATE

• PER_PERSON_TYPES:holds the list of names that may be used when entering or changing person types.This determines the employee is ex-employee or employee in USER_PERSON_TYPE Column.
• PER_SPINAL_POINTS:stores a list of progression points from a pay scale and their incremental order number .

PayRoll Tables:
PAY_ALL_PAYROLLS_F:is the DateTracked table.Payroll group definitions.DateTracked table holding personal information for employees, applicants and other people.Primary key:PAYROLL_ID.

The definition of the payroll includes the default processing information for consolidation sets,
payment types, cost allocation, and offset dates.
 
PAY_ELEMENT_ENTRIES_F:is the DateTracked table that holds the list of element entries for each assignment
 Primary Key Column:COST_ALLOCATION_KEYFLEX_ID.
PAY_ELEMENT_ENTRY_VALUES_F:is the DateTracked table that holds the actual values entered for a specific element entry.
PAY_ELEMENT_LINKS_F:is the DateTracked table that holds the eligibility rules that link elements to groups of employees.

• Database Triggers

           Trigger Name : PAY_ELEMENT_LINKS_T1
           Trigger Time : AFTER
           Trigger Level : ROW
           Trigger Event : UPDATE
PAY_ELEMENT_SETS:Has the list of selected or rejected elements for a particular payroll.With the category of its definition like tax,allowance.
PAY_INPUT_VALUES_F:is the DateTracked table that holds the definitions of the input values associated with a specific element.Primary Key:INPUT_VALUE_ID
PAY_ORG_PAYMENT_METHODS_F:Payment methods used by a Business Group.is the DateTracked table.It includes details of the payment type (cash, check/cheque, magnetic tape) and the source bank account.
PAY_ORG_PAY_METHOD_USAGES_F:Details of personal payment methods for individual employees are held in.
PAY_PAYMENT_TYPES:Types of payment that can be processed by the system.
PAY_PAYROLL_ACTIONS:Holds information about a payroll process., including their type and all the parameters passed to them.
PAY_PEOPLE_GROUPS:contains the people group flexfield segment values for individual employee assignments.
PAY_RUN_RESULTS:the run results from processing each element entry. There is always a single run result for each entry.
PAY_RUN_RESULT_VALUES:Result values from processing a single element entry.
   
Other Important tables:
PER_ABSENCE_ATTENDANCES:Records of absence, or attendance for an employee.
PER_ABSENCE_ATTENDANCE_TYPES User defined absence types.

PER_ABS_ATTENDANCE_REASONS Valid absence reasons for an absence type.
PAY_GL_INTERFACE Costed details to be passed to the General Ledger.
PAY_COST_ALLOCATIONS_F Cost allocation details for an assignment.
PAY_COST_ALLOCATION_KEYFLEX Cost Allocation key flexfield combinations table. allocation details at the assignment level.
PAY_COSTS:Cost details and values for run results.

HRMS BASICS

Functional setups Needed:



❑ Administration:  Define Key Flexfields,Descriptive Flexfields,Extra Information Types(EITs), currencies, “View All” HRMS User, lookups and Application Data Exchange (ADE).

❑ Work Structures: Define Key Flexfields with organizations, jobs, positions, grades and payrolls

❑ Compensation and Benefits (Optional):Payroll elements descriptions are included.compensation elements, input value validation, balances,formulas, salary administration, absence management/accruals of paid time off and element sets.

❑ Total Compensation: (Optional)Includes online benefits services, benefits eligibility, eligibility factors, life events, program setup and flex credits calculations.

❑ People and Assignments: (Required)Includes person types, assignment statuses and special personal information.

❑ Specific Business Functions: (Optional)Includes human resource budgets, evaluation systems and requirements matching.Also includes Workers’ Compensation,provincial medical, and vacation banking.

❑ Career and Succession Management: (Optional)Includes recruitment, career management, evaluation and appraisals and succession planning.

Few Concepts To be Noted:-
There are six Key Flexfield Structures you must define before you can


Define a business group in Oracle HRMS. These are:
• Job
• Position
• Grade
• People Group
• Cost Allocation
• Competence


DateTrack:

Keeping History for change in records.i.e The details of a employee needs to be chaged at a point of time,the change can be made with the history of date and time.
This change can be activated form the date that we want and can ahev the previous records also.
The date tracked columns are Effective_Start_date and Effective_end_date.
The types available are:


1.UPDATE – Updated values are written to the database as a new row, effective from today until 31–DEC–4712. The old values remain effective up to and including yesterday.
2.CORRECTION – The updated values override the old record values and inherit the same effective dates.
if UPDATE was selected, DateTrack checks whether the record being updated has already had future updates entered.

• UPDATE_CHANGE_INSERT (Insert) – The changes that the user makes remain in effect until the effective end date of the current record. At that point the future scheduled changes take
effect.
• UPDATE_OVERRIDE (Replace) – The user’s changes take effect from now until the end date of the last record in the future. All future dated changes are deleted.

Delete The record

When deleting a record, the user is prompted for the type of delete.
There are four options, as follows:
• DELETE (End Date) – The record has its effective end date set to today’s date.
• ZAP (Purge) – This is the total delete. All records matching the key value, whatever their date stamps, are deleted.
• FUTURE CHANGE (All) – This choice causes any future dated changes to the current record,to be removed. The current record has its effective end date set to 31–DEC–4712.
• DELETE NEXT CHANGE (Next Change) – This choice causes the next change to the current DateTracked record to be removed.