Being arjoon...: April 2011

How many key flex fields in HRMS?

Let me brief about HRMS first:

HRMS includes Payroll, but we have Payroll as a separate application when query KFFs. There are 10 KFFs in HRMS.

1. Job KFF
2. Position KFF
3. Grade KFF
4. Competence KFF
(The above four KFFs are part Human Resources application)

5. People Group KFF and

6. Cost allocation KFFs are part of Payroll Application.

Above six are mandatory KFFs which we need to create before creating BG.

The other KFFs are:

7. Personal Analysis KFF (based on the requirement)
8. CAGR KFF ( If there exists structured Unions and contingent workers ) this will not be used normally.
9. Soft Coded KFF.
10. Bank Details KFF ( if there is no localization for the country we need to create the structure)

What is date tracking, how it is used in HRMS ?

Well date tracking isa feature in Oracle HRMS esp with all tables whose name send with _F eg:per_all_people_f .Now what happens here is we cannont purge a record what we can do is endate a record and if we need to create an extension eg: of an assignment then we end-date the assignment and on a new record that is appended to the table we create a new record with starting date after the eend date and can restate the assignment used in cases of Re-hire after end of assignment or in the extension of assignment beyond the end date. It helps Oracle maintain historical data and hence the tables are data trackable.

Different Date Track mode

   Update,---Update Overwrite,Update change Insert.

  correction,purge

Next, if UPDATE was selected, DateTrack checks whether the record being updated has

already had future updates entered. If it has been updated in the future, the user is

further prompted for the type of update, as follows:

• 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 (End Date) - This is the DateTracked delete. The record that the user is

currently viewing has its effective end date set to today's date. The record

disappears from the form although the user can requery it.

• 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, including a future DateTracked delete, to be removed. The current

record has its effective end date set to 31-DEC-4712.

The record can again be displayed by requerying.

• DELETE NEXT CHANGE (Next Change) - This choice causes the next change to the

current DateTracked record to be removed.

Where another future dated DateTracked row exists for this record, it is removed

and the current row has its effective end date set to the effective end date of the

deleted row.

Update:
When a user first alters a field in a DateTracked block in the current Commit unit, he or
she sees a choice of Update prompts as follows:
• 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.
• CORRECTION - The updated values override the old record values and inherit the
same effective dates.
If the user selects UPDATE, DateTrack checks whether the record being updated starts
today. If it does, a message warns that the previous values will be lost (because
DateTrack can only store information on a day by day basis). DateTrack then changes
the mode for that record to CORRECTION.
Next, if UPDATE was selected, DateTrack checks whether the record being updated has
already had future updates entered. If it has been updated in the future, the user is
further prompted for the type of update, as follows:
• 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.
In most forms, users are prompted for the update mode for each record they update. In
some forms, they are asked for the update mode for only the first record they update.
Any other rows updated take the same update mode. Users are not prompted again,
until they have committed or cleared any outstanding changes.
Delete:
When deleting a record, the user is prompted for the type of delete. There are four
options, as follows:
• DELETE (End Date) - This is the DateTracked delete. The record that the user is
currently viewing has its effective end date set to today's date. The record
disappears from the form although the user can requery it.
• 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, including a future DateTracked delete, to be removed. The current
record has its effective end date set to 31-DEC-4712.
The record can again be displayed by requerying.
• DELETE NEXT CHANGE (Next Change) - This choice causes the next change to the
current DateTracked record to be removed.
Where another future dated DateTracked row exists for this record, it is removed
and the current row has its effective end date set to the effective end date of the
deleted row.
Where no future DateTracked row exists, but the current row has an end date other
than 31-DEC-4712, then this option causes the effective end date to be set to
31-DEC-4712. This means that a date effective end is considered to be a change.
Notice that this option again removes the current row from the form, though it can
be displayed again by requerying.
Insert:
The user is not prompted for any modes when inserting a record. The effective start
date is always set to today (Effective Date). The effective end date is set as late as
possible. Usually this is 31-DEC-4712, although it can be earlier especially when the
record has a parent DateTracked record.

What are the system administration tasks in Oracle...

Following are the tasks for system administrator
-- Set Profile Options
-- Create Menu and Request Group for Custom Responsibilities
-- Create Responsibilities
-- Create Users
-- Attached Responsibilities with User

How is employment information maintained in Oracle...

At table level employee records are stored with effective start date and end date. Whenever you change any record in the Employee record in UPDATE mode a new row is created at back-end. Hence history get stored in system.
At front end you have option to select effective date to get effective record on that date.

Enter and Maintain contains most of the required information related to employee. If you have some more information to cover you can use Extra Information or Special Information to store details.

Use of user_exit:

user_exit is the program,transfer the data from third
generation language to oracle report builder.

P_CONC_REQUEST_ID:This is default parameter.This is
optional one.
Use:generate the concurrent program Request_id.

we can develope without user_exit and p_conc_request_id

if any queries please let me know.

User Exit is a program, which stops the execution of
process and transfer the controls to other Third generation
Language. After that it executes the remaining excution
process.
    P_conc_request_id is the system parameter.It is used to
generate the concurrent request id.

   If we used user_exits in report builder we should use
p_conc_rquest_id, because it is mandatory.
i have 100 pages output. i want to print 10 records per page in Template?

set no of records 10 in property of repeating frame so
that it will display 10 records on eahc page.

You can also use for-each and page-break XML statement in
the template to acheive this.

<split-by-page-break>

What is the Lookup's and what is the use of lookup's?

Lookups are referred in more locations.
instead of storing the original value,we can store the
lookup codes.
this will reduce the space that is occupied in the database.
this is one reason.

the main reason is..

take an example...

in a table xx_emp there is one column gender.

possible values are
male m
female f
other o

define the above values in a lookup.

if u want to change the male to female ..u go and update
the lookups then automatically all the records referring to
that lookup will also change dynamically...

This saves time and complexity.

How You Will display the key flexifields in your report

Hear We have two ways
1) one is by using the segment1||segment2||-----segmetn5.
This is one way but

2) second one is by using USER_EXITS those are 'FND FLEXSQL'
AND
'FND FLEXDVAL'
by useing Thease we can get the flex field data into the
report

What are the requests groups?

request groups is a set of concurrent programs and request
set.
A request set is group of one or more concurrent programs
which run one after the other.

What is the interface?
Interface is the program used to transfer the data from one
system to the another system.i.e., it might be file(Legacy
System) to table or table to file(Legacy System).

File(Legacy System) to table is called Inbound Interface
Table to file(Legacy System) is called Outbound Interface.

what is lexical parameter?

Lexical references are placeholders for columns or
parameters that you embed in a SELECT statement. You can
use lexical references to replace the clauses appearing
after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING,
CONNECT BY, and START WITH. Use a lexical reference when
you want to substitute multiple values at runtime. You
cannot make lexical references in a PL/SQL statement. You
can, however, use a bind reference in PL/SQL to set the
value of a parameter that is then referenced lexically in
SQL, as shown in te example below. You create a lexical
reference by typing an ampersand (&) followed immediately
by the column or parameter name. A default definition is
not provided for lexical references.
Therefore, you must do the following:
Before you create your query, define a column or parameter
in the data model for each lexical reference in the query.
For columns, you must set the Value if Null property, and,
for parameters, you must set the Initial Value property.

Restrictions::::
You cannot make lexical references in a PL/SQL statement.

If a column or parameter is used as a lexical reference in
a query, its data type must be character.

If you want to use lexical references in your SELECT
clause, you should create a separate lexical reference for
each column you will substitute. In addition, you should
assign an alias to each lexical reference. This enables you
to use the same layout field and boilerplate label for
whatever value you enter for the lexical reference on the
Runtime Parameter Form.

If you use lexical references in your SELECT clause, you
must specify the same number of items at runtime as were
specified in the report's data model. Each value you
specify for your lexical references at runtime must have
the same datatype as its Initial Value.

A lexical reference cannot be used to create additional
bind variables after the After Form trigger fires. For
example, suppose you have a query like the following (note
that the WHERE clause is replaced by a lexical reference):
SELECT ENAME, SAL FROM EMP
&where_clauseIf the value of the where_clause parameter
contains a reference to a bind variable, you must specify
the value in the After Form trigger or earlier. You would
get an error if you supplied the following value for the
parameter in the Before Report trigger: WHERE SAL
= :new_bindIf you supplied this same value in the After
Form trigger, the report would run

Reports Builder uses these values to validate a query with
a lexical reference. Create your query containing lexical
references.

How to create user and how u attach with responsibility

Navigation: System Administrator--> Security-->User-->
Define,, Provide details like password(Mandatory), etc and
responsibilities which you need to attach with the user,
and save the record.

Explain the multi-organization structure.

Multi Organization :Using a single installation of any
oracle application product , to support any number of an
organization even it has different Set Of Books
Bussiness Level(It Secures Human TransactionInformation)
||
Set Of Books(It secures all Transaction Information In
Oracle Genral Ledger)
||
Leagel Entry(All leagal Information in the Organization)
||
OPerating Unit(It Uses Order management, Cash
managment,AR,AP...it may be Sales Office ,
Division,Department)
||
Inventry Organization(Inventry Details)

what is user exit and wat program do we write in user exit?

Answer
# 1

User exit is a program to pass control from Report Builder
to a program you have written, which performs some
function, and then returns controlto Report Builder.

We can write UE in before report trigger to populate
the value

User Exits are used to pass control from report builder to
outside program.

   Basically if we want to create any flex field reports.
we need to get the structure and code combination and
description of flexfileds at that time we use userexits.

srw.flex_sql,flexid_val,srw.init etc..

when we run payment batch what are the 3 concurrent programs?

AutoSelect Invoices,Build Payments,Format Payments

what is the difference between a Job and a Position

job is general one, whereas position is specific to its
role and responsibilities.

for Eg:   JOB: MANAGER (generic term)
          Positon: finance manager, hr manager,(this is
position which is specific to the role to be played.

what is work structure
Work Structure (WS) represent the different way in which
emp can work within the enterprise.It provide a framework
for defining the work assignment of employee.WS represent
the organisation unit of an enterprices which include dept
or division, payroll, Job, Position, grade, competence ans
so on.

what dif bett extra information type and special information type?

EIT and SIT Both are provided by Oracle to Capture Extra
Information. Basic differences would be EIT is similar like
an DFF and also defined using DFF Defination Screen.
SIT is KFF and defined using Personal Analysis KFF
Defination screen.
SIT generally used at Person Level, EIT can be defined at
person,assignment,contact,element,location and Job Level.
There are 2 columns date_to and date_from in SIT while no
such columns are there in EIT.
SIT IN CONTEXT SENSITIVE.
Key Flexfields have qualifiers whereas Desc Flexfields do not. Desc Flexfields can have context sensitive segments while Key flexfields cannot.

how many types of extra information is there?
The Majore Extra Information (EIT) Types in HRMS :

1. Location
2. Job
3. Position
4. People
5. Assignment

+ Organization define in Org Dev Dff

How to write the no data found in XML Publiser Report in apps?

Answer
# 2

We can do it in 2 ways.
   In rdf, we can define a summary column (say
CF_COUNT)which counts the number of rows of a group, and in
the rtf layout create a text form field and put the help
text as <?if:CF_COUNT=0?>No Data Found<?end if?>
   Other way, no need of summary column, in the rtf layout
create a text form field and put the help text as
<?if:
count(./LIST_G_SO_NUMBER/G_SO_NUMBER/LIST_G_SO_NUMBER1/G_SO_NUMBER1)=0?>No
Data Found<?end if?>

where
./LIST_G_SO_NUMBER/G_SO_NUMBER/LIST_G_SO_NUMBER1/G_SO_NUMBER1
is the expected path to the group, from the root of the xml,
that we define. This expression will count the number of
occurances of the group "G_SO_NUMBER1"

while running a interface if we get 100 records as erors. how to debug it.

We are having to two types the error records.....
1)Rejected by the SQL Loader.
These are stored in the '.BAD' file......
2)Rejected by the condition which have been mention in the
control file.
These are stored in the '.dis' file......

atfer getting the error records in the specified file then
we will correct the errors and proceed them once again...

How many phases of implementation and what are those.
AIM methodlogy,,it has got six phases
definition,
Operation Analysis
Design
Build
Transition
Production

what do you mean by value sets

A set of predifined and validated values assigned to a
field,that resticts the user from entering junk or
invalidate data
Types of value sets : dependent, independent, none, table,
pair: The data input is checked against two flex-field specified range of values, special: The data input is checked against a flex-field. Special and pair value sets provide a mechanism to allow a "flexfield-within-a-flexfield". These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal flexfield segments. Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another flexfield as a value set for a single segment or to provide a range flexfield as a value set for a pair of segments.
translatable dependent, translatable independent: A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used. Flexfield Value Security cannot be used with Translatable Independent or Translatable Dependent value sets

FND

FND means Foundation. It means, this is the schema where all the foundation/"apps tech stack" tables are created. For example, following entities can be classified as Foundation to apps.1 1. Apps Security related tables, to name a few FND_USER, FND_SECURITY_GROUPS etc... 2. Apps validation related tables, like FND_FLEX_VALUES, FND_FLEX_VALIDATION_EVENTS 3. All the workflow related tables... 4. Concurrent manager related tables....

As you would know, reports are submitted as concurrent programs in Oracle Apps. For debugging things like Report Triggers or Fast Formulas, you can induce messages using srw.message, and those messages then appear in the log file of the concurrent request.

Qns7: What is the difference between per_people_f and per_all_people_f Ans7: PER_PEOPLE_F is a secured view on top of PER_ALL_PEOPLE_F. The secure view uses an API hr_security.show_person. This API internally checks for an entry in table PER_PERSON_LIST for the logged in person's security profile. A concurrent program named "Security List Maintainence program" will insert records in PER_PERSON_LIST

Difference between per_all_people_f.person_type_id and per_person_type_usages_f.person_type_id

When you need to identify the person_type_id of a person, which one do you use - per_all_people_f.person_type_id or per_person_type_usages_f.person_type_id? It is one of the frequently asked question in Oracle Application HRMS Interview. Let me explain you the difference between the two in detail- The answer is you should always use the per_person_type_usages_f.person_type_id. Though on the surface the two fields seem to be the same but they are different and that can mislead you. Here are the reasons below -

1) The per_all_people_f.person_type_id holds the default user_person_type for a given system_person_type. It is not maintained to reflect the true user person type. Confusing??? Please read on the example below-

For each system_person_type in each business group, there can only be one USER_PERSON_TYPE with a default_flag = 'Y'. In the above example, the default user_person_type is Employee with Person Type ID = 20.

However, an employee can have a user_person_type = Contractor, in this case is person_type_id 40. What will show in person_type_id of the two tables with a CONTRACTOR user_person_type will be as follows:

PER_ALL_PEOPLE_F: 20

PER_PERSON_TYPE_USAGES_F: 40

So, only the person_type_usages_f table will give us a true picture of user_person_type and should be used in ALL HR transactions. The per_all_people_f.person_type_id is for use by non HR applications, such as Purchasing which doesn't care about the various user flavors but the system_person_type of 'EMP'. When HR Development made the change to use per_person_type_usages_f table, they retain the person_type_id column in per_all_people_f table for compatibility with other applications. This scheme will enable other non-HR applications still work.

NOTE: When accessing per_person_type_usages_f table for a given person at a given time, there can be multiple rows returned because per_person_type_usages_f table will have a row for each person_type. When an employee is an EMP_APL, there'll be two rows returned: one for a system_person_type = 'EMP' with user_person_type = whatever, and another row for system_person_type = 'APL' and user_person_type = whatever. Any SQL statement or cursor needs to handle the multiple rows returned condition. Otherwise, one will get an error something like "Exact fetch returns more than 1 row".

select papf.full_name "Employee Name"
      ,papf.employee_number "Employee Number"

      ,per_utility_functions.get_Net_Accrual
(
          P_Assignment_ID           => paaf.assignment_id
          ,P_Payroll_ID             => 162
          ,P_Business_Group_ID      => 2008
          ,P_Assignment_Action_ID   => NULL
          ,P_Calculation_Date       => to_date('01-Apr-2011')
          ,P_Plan_ID                => 4063
          ,P_Accrual_Start_Date     => null
          ,P_Accrual_Latest_Balance => null
          ) "Casual Balance"
      ,to_date('01-Apr-2011') "As On"

from per_all_people_f papf
    ,per_all_assignments_f paaf
where papf.person_id = paaf.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and papf.current_employee_flag = 'Y'
and paaf.primary_flag = 'Y'
and paaf.assignment_type = 'E'
and papf.business_group_id = 2008
and (papf.employee_number like 'P%' or papf.employee_number like 'C%');

What is the difference between key flexfield and Descriptive flexfield?
Key Flexfield is used to describe unique identifiers that will have a better meaning than using number IDs. e.g a part number a cost centre etc Desc Flex is used to just capture extra information. Key Flexfields have qualifiers whereas Desc Flexfields do not. Desc Flexfields can have context sensitive segments while Key flexfields cannot.

Grade Rate
In Oracle HRMS, you can set up a table of values related to each grade. These are called grade rates. You can enter a fixed value or a range of valid values for each grade.
For example, you might define a salary rate and an overtime rate of pay for every grade, with minimum, maximum, and midpoint values. Both rates would be in monetary units. However, you can also define rates with non-monetary units, such as days, hours, or numbers.
Per_people_x --- Current record
Per_people_f --- Secured View

Interface error
HR_API_BATCH_MESSAGE_LINES
hr_batch_message_line_api.create_message_line
hr_batch_message_line_api.delete_message_line
hr_batch_message_line_api.delete_message_lines
Eligibility crieteriya

Interface table for balance initialization
pay_balance_batch_headers
pay_balance_batch_lines
pay_batch_headers
pay_batch_lines

An eligibility rule for an element is defined as a link between the element and the components of the employee assignment. Each link defines a group of employees who are eligible to receive the element.
For example, you might want to give a production bonus only to those employees who work full time in Production and are on the weekly payroll. To do this you would define a link between the element 'Production Bonus' and the combination of the 'Production' organization, the 'Full-Time' employment category and the 'Weekly' payroll.
While this link is effective, you cannot pay the bonus to anyone who is not eligible. Similarly, element linking on deductions ensures that you do not take deductions from employees not liable for them.
An employee is eligible for an element when his or her assignment exactly matches the link definition. You can link an element to the following components of an employee assignment:
Organization

Job

Position

Grade

Location

Payroll

Group Segments

Employment Category

Salary Basis

Multiple Rules of Eligibility
You can define more than one link for each element but there must be no overlap between the eligibility criteria for the links. For example, you could create one link to the combination of grade 'A' and the job 'Accountant'. However, you could not create one link to grade 'A' and a second link to the job 'Accountant'. This would imply that an accountant on grade A is eligible for the same element twice.
Oracle HRMS does not permit you to define links that would make an employee eligible for an element more than once.
API Used in Inbund interface

1>    TNA to payroll Interface -- pay_element_entry_api
2>    Employee reimbursement Interface -- pay_element_entry_api,HR_PERSON_EXTRA_INFO_API

3>    EIT to PPM Interface -- HR_PERSONAL_PAY_METHOD_API

4>    Salary proposal -- hr_maintain_proposal_api

5>    CTC Structure creation PROG.

6>    CTC structure update prog.

7>    HCM CSD EMPLOYEE DATA INTERFACE

8 > HCM Update FND USER END DATE

Conversion

1.    Update Statutory Info(HR_ASSIGNMENT_API.update_emp_asg)
2.    Salaryproposal(hr_maintain_proposal_api)
3.      to attach salary proposal element(HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
4.    )
5.    Attendance element entry(pay_element_entry_api)
6.    Employee Address(HR_PERSON_ADDRESS_API.CRE_OR_UPD_PERSON_ADDRESS)
7.    prev employer(hr_previous_employment_api)
8.    hr_ex_employee_api.actual_termination_emp

What is the difference between quick-pay and payroll run?
Quickpay is a functionality(available from assignment screen), by which you can run the payroll for single person assignment. It uses the same executable as that of actual Payroll run.

Which Assignment Records were included in Payroll Actions :- PAY_ASSIGNMENT_ACTIONS pay_assignment_actions contains an entry for each Assignment_id that was eligible during Payroll Action. For example if you run a quickpay, an entry for that specific assignment_id will be created in pay_assignment_actions. Obviously this table has a column named assignment_id. You can drill down from Assignment Action screen to view Payroll Run Results and Payroll Balances, for the specific assignment. Hence both PAY_RUN_BALANCES and PAY_RUN_RESULTS reference ASSIGNMENT_ACTION_ID. NOTE: ASSIGNMENT_ACTION_ID is the primary key of PAY_ASSIGNMENT_ACTIONS. Also note that entries in this table are created by Concurrent Processes, hence this table is never updated by end user from screens. Hence there is no date-tracking on this table. What is payroll run results? As you would know, when payroll process runs, it reads the element entries for the assignment in pay_assignment_actions. For those element entries, payroll engine either uses the entry value in "pay value" or it kicks off a fast formula if the element has a ff attached to that. The end result is that each eligible element gets a result. These results are stored in pay run result tables. What are the payroll run results tables? Pay_run_results Pay_run_result_values Obviously the values are stored per element in Pay_run_results. The input value used/derived by payroll engine is stored in Pay_run_result_values/

PAY_PAYROLL_ACTIONS - What is a payroll action? Well, just about anything you make the Oracle Payroll engine do, it records an entry in PAY_PAYROLL_ACTIONS. What are the possible actions? To name a few:-      Costing      Quickpay      Payroll Run      Magnetic Transfer [synonym to EOY-End of Year run]      Transfer to GL......etc A column named ACTION_TYPE [validated by lookup type ACTION_TYPE] is used to store the type of action.

SIT - PER_ANALYSYS_CIETERIA,PER_PERSON_ANALYSYS
EIT-   PER_PEOPLE_EXTRA_INFO

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE. A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Define Interfaces.
What are the different types of Interfaces? Interfaces are used to integrate external systems and data conversion in Oracle Applications. These can be used to either transfer data from Oracle Applications to a Flat File or Data from Legacy System to Oracle Applications. There are two types of Interfaces. Inbound and Outbound Interfaces. Inbound Interfaces are used to transfer data from external system to Oracle Applications. Outbound Interfaces are used to transfer data from Oracle Applications to External System. Open Interface is the interface whose interface logic is provided by Oracle. Custom Interface is an Interface whose logic is developed by implementation team.

What are alerts?
What are the different types of alerts? Explain. Alerts Immediately inform you of the database activity as it happens. Can Periodically trigger off events as and when required Can take predefined actions Allow you to define distribution list Can keep history of the exceptions and actions taken against them. There are two types of alerts. Event Alerts Periodic Alerts

What is the Flex field? What are the types of Flex field?
Flex Field is “Flexible Field” A Flexfield is made up of Segments. Each segment has a name that can be assigned and has set of valid values. There are two types of Flex field Key Flex Field and Descriptive Flex Fields
What is a security profile?
Security profile provides more security within BG.It helps in determining what users have access to what data. The levels where you can have security profile are: Organization Position Payroll Supervisor User specific: you can restrict / give permission to any user you can also have Custom security

Technically speaking, how do you know from tables that an Employee has been terminated?

The Person_Type_ID in PER_PERSON_TYPE_USAGES_F will correspond to System Person Type EX_EMP (b) A record gets created in table PER_PERIODS_OF_SERVICE with Actual_Termination_date being not null

Jobs- required, generic within BG, independent of any single organization, jobs can occur in many organizations, holds fair labour standards act (FLSA) code, holds equal employment opportunity (EEO) work category, associted with worker's compensation codes
Positions- optional, specific occurence of one job, must be unique within an organization, linked to an organization, job and location, shared with other applications (i.e. purchasing), position hierarchies control access to information (security)
Similarity between jobs and positions- valid grades, evaluations, competencies, skills, work choices, not date tracked
advantages of using positions- define job more specifically, reporting, position hierarchies for PO approvals, position hierarchies for security, standard conditions and working hours, successor, competencies, requirements, recuitments, career management, career paths
you may not want to use positions in case of- re-organizations, concerned about increased maintainance

Q> Is it possible to call a function which content a DML statement within a select statement?
A> Generally it is not possible,but if autonomous transaction is used into the function then it can be used in select statement.

Q> what is ref cursor?

Q> what is balance and dimension?

Q> what is feeds in balance?

Why do we need a Balance?
Lets take an example. You work at a company for stipulated 37.5 hours a week. Lets assume that you work extra hours during weekdays. At any given time of the year you would like to know how many extra hours have you worked. To work out your running total, we accumulate this in something called as a balance in Oracle Payroll.

Q> what is kff and dff?
Q> what is eit and sit?
Q> what is the next step you follow after running the payroll?
Q> how functions are called into ff?
Q> what is formula function,contex in formula function?
Q>

How do you Debug a Fast Formula? Ans 3: You can create a message variable in Fast Formula. This message variable must also be registered as a Formula Result( In “Formula Result Rule” window).

Can you call pl/sql package functions from within a Fast Formula? Ans 1. Yes you can -->How do we do this? -->There is a Define Function screen in HR. In this screen you will register the pl/sql as “External Function”. -->If we want to pass a parameter PAYROLL_ID to this external pl/sql Function, how do we do it ? -->The “Define Function” screen has a button named “Context Usage”. This button opens up a window in which you can register the parameters.

Define Quick Paint reports:
a)A Quick Paint formula can return a text string to display in the Assignment field on the Maintenance window and in the Data Organizer.
b)A Quick Paint formula can return message tokens that you can use in a notification message issued from template forms.
The main adv. of using PRAGMA AUTONOMOUS_TRANSATION is that weather the transaction made by the parent may be rolled back due to some error the autonomous transaction has no effect on it. Suppose if there is any error in autonomous transaction … then >>>> what happen? ? ? don’t worry***** It will save all the transactions just before the error occurred. Only the last transaction that has error will be rolled back only if there is no error handler.
Note: A pragma is a compiler directive that tells Oracle to use rules, other than the default rules, for the object.

The salary basis

The salary basis establishes the duration for which a salary is quoted, for example, hourly, monthly or annually. Notice that an employee's salary basis is not necessarily the same as the pay periods of his or her payroll. For example, an employee with an hourly pay rate has the salary basis Hourly Salary, but can have an assignment to a weekly payroll.
You can associate an element with one salary basis. When you associate an element with a salary basis, you cannot create or maintain entries for the element on the Element Entries window. You must use the Salary Administration window to enter and maintain employee salaries.
You define a salary basis in the Salary Basis window.
In Oracle HRMS, you can set up a table of values related to each grade. These are called grade rates. You can enter a fixed value or a range of valid values for each grade... These grade rates serve only as a guideline to validate that the salary you propose for an employee on a certain grade is appropriate for that grade."

Rehire and inter BG Transfer
In rehire the person_id remains same,but in inter BG Transfer the person_id will be diff.
HR_PERSON_DEPLOYMENTS table is used to store the changes for interBG transfer.

When you rehire an employee, person_id does not change. -- Only assignment_id has to be changed whenever there is a termination and rehiring. Also assignment_id changes whenever person_type (employment_type) changes.
types of value sets- dependent, independent, none, table,
pair: The data input is checked against two flex-field specified range of values, special: The data input is checked against a flex-field. Special and pair value sets provide a mechanism to allow a "flexfield-within-a-flexfield". These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal flexfield segments. Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another flexfield as a value set for a single segment or to provide a range flexfield as a value set for a pair of segments.
translatable dependent, translatable independent: A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used. Flexfield Value Security cannot be used with Translatable Independent or Translatable Dependent value sets
While registering procedure as concurent program there we need to declare two mandatary OUT parameters in procedure. 1.Errbuf 2.Retcode
Errbuf : Errbuf is one the paramter which will define in pl/sql to store procedure to get error messges into log file.
Retcode: this parameter will be used to get the status of the concurrent program. It will give the values
0 for sucess
1 for warning
2 for errors

apps_initialize

fnd_global.apps_initialize(user_id in number, resp_id in number, resp_appl_id in number, security_group_id in number default 0, server_id in number default -1):

Sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session.This routine should only be used when a session must be established outside of a normal form or concurrent program connection.

Where to store errore message for interface
HR_API_BATCH_MESSAGE_LINES
hr_batch_message_line_api.create_message_line
hr_batch_message_line_api.delete_message_line
hr_batch_message_line_api.delete_message_lines

How to initialize balances?
1.    We need to populate the data in the temp table by SQL loader.
2.    Then need to run the prog to upload the data in pay_balance_bathch_lines and pay_balance_batch_headers.
3.    need to run the seeded prog intial balance element entry india to attach element in initial feed.
4.    need to run another prog to upload data from lines table to Oracle system.
            pay_balance_types
          , pay_balance_dimensions
          , pay_defined_balances

Interface table for balance initialization
pay_balance_batch_headers
pay_balance_batch_lines

If an error occurs during the processing of the batch, the error message
is written to the PAY_MESSAGE_LINES table with a source_type of H
(header) or L (line).

Balance Initialization Steps

5.    We need to populate the data in the temp table by SQL loader.
6.    Then need to run the prog to upload the data in pay_balance_bathch_lines and pay_balance_batch_headers.
7.    need to run the seeded prog intial balance element entry india to attach element in initial feed.
8.    need to run another prog to upload data from lines table to Oracle system.
            pay_balance_types
          , pay_balance_dimensions
          , pay_defined_balances

Interface table for balance initialization
pay_balance_batch_headers
pay_balance_batch_lines

If an error occurs during the processing of the batch, the error message
is written to the PAY_MESSAGE_LINES table with a source_type of H
(header) or L (line).

Balance Initialization Steps
Here’s a simple check list on how to set up the data:
1. Create payrolls in Oracle Payroll with periods going back to the
start of the year. Enter all employees into Oracle HRMS and give
them assignments to these payrolls.
Attention: The next step applies to US users only. Users in
other legislations need only define links for the predefined
balance loading elements.

2. From the Submit Requests window, run the Initial Balance Structure
Creation process, selecting a batch name as the parameter. For each
batch, this process creates:
• An input value to hold the amount of each balance and of any
context, and enough elements with the special classification
Balance Initialization to hold all the input values created
• The necessary links and balance feeds for these elements

3. Create any other elements you need to initialize balances for your
own earnings and deductions.
• Follow the requirements listed above. See: Setting Up an Element
to Feed Initial Balances: page 11.
• Use multiple input values to reduce the number of elements
• Define one balance feed for each input value
Note: Each balance must have one initial balance feed only.
Multiple input values for one element must feed balances that
have the same ’upload date’.

4. Group employees into batches for managing initialization of their
balances. Enter an identifying header for each batch (these headers

go into the PAY_BALANCE_BATCH_HEADERS table). Each
header contains the following information:
• Business Group name and payroll name
• Batch name and ID number
• Upload date: the date on which the balances in the current system
will be correct and ready for transfer
For example:
Batch Name Business Group Payroll Name Upload Date
Upload 1 BG name Full Time 1 13–AUG–1995

5. Create a batch line for each balance to be transferred (these lines go
into the PAY_BALANCE_BATCH_LINES table). A batch line
includes the following information:
• Employee assignment number
• Balance name and dimension, such as quarter to date or year to
date
• Balance value
• Balance context where appropriate. For US users the context may
include a GRE and a jurisdiction (federal, state or local).
Note: The process uses your balance feed definitions to
determine which element input value to use.
For example:
Asg. Number Balance Dimension Value
60001 Salary PTD 700
60001 Salary QTD 1400
60001 Salary YTD 2400
60001 Tax Paid PTD 2200
60001 Tax Paid QTD 2400
60001 Tax Paid YTD 2400
Attention: The Tax Paid YTD value is not required because it
has the same value as the QTD. However, this balance is
included to create a value for the latest balance, and improve the
performance of the first payroll run.

6. From the Submit Requests window, run the Initial Balance Upload
process. Select the mode in which to run this process as a
parameter. Available modes are:
• Validate
– Validate batch lines but do not transfer
–         Send error messages to PAY_MESSAGE_LINES

• Transfer
– Validate and transfer batch lines
– If any line for an assignment is in error, none of the lines for
the assignment are transferred
• Undo
Removes balance initialization entries from the database and
marks the lines as U in the batch lines table.
• Purge
Purges all lines in the batch lines table, regardless of how they are
marked.
Note: Use Purge mode only when you are sure that the
balances for all assignments in a batch have been successfully
entered into the HRMS database.

The PL/SQL code resides in one package.
pay_balance_pkg

conc prog seeded :      Initial Balance Upload, (Validate,transfer,undo,purge)
                                      Initial Balance StructureCreation process

Concurrent prog properties

Incompatible : If its checked for any conc prog then it will give us option that which progs will not run when the specific prog is submitted.
SRS : By default its checked.If its not checked then u can’t submit the conc prog from view request.

Ivoker rights in Oracle to submit conc prog from view req from any user apart from apps.
Dated and Date Tracked
Work Structure related data are dated, Dates can be attached to work structure to maintain the versions and structure changes.The prev data is maintain as historical and new data can be created with future date.In HRMS the data related to organizations,job,grade are dated.It have a date form and date to colm.

The dynamic information related to emp assignments compensations benefits is date tracked.position is date tracked

which API to return the parameters & valuesets etc
FND_FLEX_VALUES_PKG

Tell me some thing about SQL-LOADER.
Sql * loader is a bulk loader utility used for moving data
from external files into the oracle database.
Sql * loader supports various load formats, selective
loading, and multi-tables loads.
        1) conventional
       --The conventional path loader essentially loads the
data by using standard ?insert? statement.
        2) direct
                 -- the direct path loader (direct = true)
by possess of logic involved with that, and loads directly
in to the oracle data files.
EX:-
My data.csv file
                 1001, ?scott tiger?,1000,40
                 1002,?gvreddy?,2345,50
Load data
Infile ?c:\data\mydata.csv?
Into table emp
Fields terminated by ?,? optionally enclosed by ???
(empno, empname,sal,deptno)
>sqlldr scott/tiger@vis
control=loader.ctl log= gvlog.log bad=gvbad.bad
discard=gvdis.dsc .

Sql * loader used to transfer Huge data from legacy to
oracel database. It's supports .dat,.xls etc.. files.

5 types of files are there.

Flat file     -- .dat file
Control file -- Control file program
Bad file     -- Stores the all error records
Discard file -- stores all the discard filed (WHEN Condi)
Log file     -- Shows the status of the Control file

what is difference between API and Interface?
An API (Application Programming Interface) is inbuilt
program through which data’s can be transferred to Oracle
base tables directly without writing the program for
validating or inserting in Interface tables. But through
User Interface, we have to write codes for validation and
insertion of data’s in Interface tables and then in Oracle
base tables

How to insert data into two tables using single control file?
Using "When"

we use "When" condition to load data into multiple tables.

Syntax:

Load Data infile '&1'
Insert into table table1
(
.
.
.
.
)
Insert into table table2
when <condiation>
(
.
.
.
);
How To call Conc prog from back End including APPS Initialize

DECLARE   L_RQST_ID NUMBER;   BEGIN      SELECT * FROM FND_USER;      SELECT * FROM FND_RESPONSIBILITY ;      SELECT * FROM FND_APPLICATION;      SELECT * FROM FND_SECURITY_GROUPS;                /*1st parameter is Userid,      2nd parameter responsibility id,      3rd parameter responsibility application id,      4th parameter security profile id     */     fnd_global.APPS_INITIALIZE(V_USER_ID,V_RESP_ID,V_APP_ID,V_SEQ_GR_ID);                     L_RQST_ID := FND_REQUEST.SUBMIT_REQUEST (                          application => 'XXHCM',                                              -- Application Short Name                          program     => 'XXHCM_APPRAISAL_TEST',  -- Program Name                          description => NULL,                          start_time => TRUNC(sysdate),                          sub_request => FALSE,                          argument1   => P_PLAN_NAME,                    -- Parameter 1 for Concurrent Prog                          argument2   => P_PLAN_ID                             -- Parameter 2 for Concurrent Prog        -------                         );     COMMIT;      IF L_RQST_ID=0 THEN      FND_FILE.PUT_LINE(FND_FILE.LOG,'Standard Program is Not Submitted');      ELSE      FND_FILE.PUT_LINE(FND_FILE.LOG,'Request ID :'||L_RQST_ID);      END IF; END; ============================================================== Note: V_USER_ID   :=FND_PROFILE.VALUE('USER_ID');           V_RESP_ID   :=FND_PROFILE.VALUE('RESP_ID');           V_APP_ID    :=FND_PROFILE.VALUE('APPLICATION_ID');           V_SEQ_GR_ID :=FND_PROFILE.VALUE('SECURITY_GROUP_ID');
+++++++++++++++++++++++++++++++++++++++++++++++++++
hr_competence_element_api.create_competence_element
hr_competences_api.create_competence
hr_contingent_worker_api.create_cwk
hr_employee_api.create_employee
hr_employee_api.create_applicant
hr_maintain_proposal_api.insert_salary_proposal
hr_grade_api.create_grade
hr_job_api.create_job
hr_position_api.create_position
hr_person_address_api.create_person_address
+++++++++++++++++++++++++++++++++++++++++++++++++++
other definitions -> table structure
other definitions -> table values
total compensation -> basic -> write formulas
total compensation -> basic -> global values
+++++++++++++++++++++++++++++++++++++++++++++++++++
common tables in fast formula
FF_FUNCTIONS
FF_FUNCTION_PARAMETERS
FF_FORMULAS_F
FF_FORMULA_TYPES
FF_DATABASE_ITEMS
FF_GLOBALS_F
+++++++++++++++++++++++++++++++++++++++++++++++++++
Fast Formulas are used for validation, to perform calculations and to specify rules
types of input in FF: input, DBI, global values
static DBI shipped with system, cannot be modified
dynamic DBI created by oracle hrms processes whenever u define new elements or other related entities
the database items window includes a checkbox labeled default required. this checkbox is checked for database items that can be empty
to compile many formulas at the same time, run CP "Bulk Compile Formula Process" in Submit Requests window
formula errors: verify-time, run-time
common run time errors: uninitialized variables, divide by zero, no data found, too many rows, value exceeded allowable range, invalid number, null data found (use default statement)
+++++++++++++++++++++++++++++++++++++++++++++++++++
PER_ALL_PEOPLE_F.person_type_id holds the default user_person_type for a given system_person_type. it is not maintained to reflect the true user person type
+++++++++++++++++++++++++++++++++++++++++++++++++++
the soft coded legislation flexfield holds legislation specific information only. segments can be seen at BG, payroll and assignment level
HR_SOFT_CODING_KEYFLEX
oracle supplies 2 KFF and 4 DFF (predefined and protected)
2 KFF: soft coded legislation kff and bank details kff
+++++++++++++++++++++++++++++++++++++++++++++++++++
PER_ALL_PEOPLE_F also has party_id because oracle creates a party in TCA as soon as a record in PER_ALL_PEOPLE_F gets created
PER_PERSON_TYPES person_type_id(pk)
PER_PERSON_TYPE_USAGES_F
PAY_ELEMENT_TYPES_F element_type_id(pk) elements get created in this table
PAY_ELEMENT_LINKS_F element_link_id(pk) to make payroll elements to a group of people, create element links.
PAY_ELEMENT_ENTRIES_F
PAY_ELEMENT_ENTRY_VALUES_F input_value_id(pk)
+++++++++++++++++++++++++++++++++++++++++++++++++++
the date selected by user for date_track is stored in FND_SESSIONS with their session_id
from backend nothing is returned when select * from per_people_v?
almost all HRMS views on which screens are HRMS based, they have a join to FND_SESSIONS table
when user changes their date-track value, it gets reflected in FND_SESSIONS table.
below SQL creates a default record for SQL*Plus session
INSERT INTO FND_SESSIONS(session_id, effective date)
     (SELECT userenv('session_id'), SYSDATE from DUAL
     WHERE NOT EXISTS(SELECT 'c' FROM FND_SESSIONS s1
                                   WHERE userenv('session_id') = s1.session_id));
+++++++++++++++++++++++++++++++++++++++++++++++++++
while setting up BG, following needs to be set up:
emp number generation for employees and applicants (automatic/manual)
national identifier generation only for employees (automatic/manual)
KFF structures that would be used in the BG
legislation code and default currency
organizations can be internal or external
the location needs to be set up before and organization can be defined. this is the exact location of the organization along with address and phone number
the organization must be classified as an HR Organization to assign employees
job is independent of organization
a job group stores jobs of similar types. all jobs defined in HRMS for employees must be a part of the default HR job group
PER_JOB_DEFINITIONS stores the segment of the KFF. this is linked to FND tables that stores the KFF structure details
PER_JOB_GROUPS.job_group_id linked with PER_JOBS.job_group_id
PER_JOBS.job_definition_id linked with PER_JOB_DEFINITIONS.job_definition_id
a position is defined for an organization and a job. positions data is date tracked
valid grades could be defined for a job or for a position
a grade could be valid grade either for a job or for a position
a single grade cannot be a valid one for both a job and a position
work structures -> organization -> description
work structures -> location
work structures -> job -> description
work structures -> job -> job group
work structures -> position -> description
work structures -> grade -> description
+++++++++++++++++++++++++++++++++++++++++++++++++++
people -> enter and maintain (employee and applicant data)
people -> enter and maintain -> address (address)
people -> enter and maintain -> others -> phones (work phone, home phone)
people -> enter and maintain -> others -> end employment (periods of service)
people -> enter and maintain -> others -> contact
PER_CONTACT_RELATIONSHIPS
People Group navigation: people -> enter and maintain -> assignment (Group field on the screen)
this is a kff used for grouping the people together based on certain criteria. to access the assignment screen, atleast one segment of the people group kff needs to be enabled
people -> enter and maintain -> assignment -> salary
+++++++++++++++++++++++++++++++++++++++++++++++++++
payroll -> description
PAY_ALL_PAYROLLS_F.payroll_id linked with PER_ALL_ASSIGNMENTS_F.payroll_id
total compensation -> basic -> element description (element)
total compensation -> basic -> element description -> input values (input value)
total compensation -> basic -> link (element link)
element links are used to determine which group of people is eligible for which elements
the element, for which link has to be created, needs to be selected in the window. in the eligibility criteria, the element can be linked to an organization, job, grade, people group, location, position, payroll, employment category and salary basis
the checkbox "link to all payrolls" will link the element to all the employees
PAY_ELEMENT_TYPES_F
PAY_ELEMENT_ENTRIES_F
PAY_INPUT_VALUES_F
PAY_ELEMENT_LINKS_F
+++++++++++++++++++++++++++++++++++++++++++++++++++
in APIs, optional parameters are followed by a default value, e.g. P_EMAIL_ADDRESS_IN VARCHAR2 DEFAULT NULL
Control parametes are mandatory parameters common across all the APIs
P_OBJECT_VERSION_NUMBER for insert APIs P_OBJECT_VERSION_NUMBER is an OUT parameter. for update APIs P_OBJECT_VERSION_NUMBER is an IN OUT parameter
P_VALIDATE
P_EFFECTIVE_DATE mandatory IN parameter for insert/update/delete APIs for date tracked data
P_DATETRACK_UPDATE_MODE APIs used to update date tracked data. valid values for this parameter: UPDATE, CORRECTION, UPDATE_OVERRIDE and UPDATE_CHANGE_INSERT
P_DATETRACK_DELETE_MODE APIs used to update date tracked data. valid values for this parameter: ZAP, DELETE, FUTURE_CHANGE, DELETE_NEXT_CHANGE
APIs in work structures: HR_ORGANIZATION_API, HR_JOB_API, HR_POSITION_API, HR_GRADE_API, HR_VALID_GRADES_API, HR_LOCATION_API
APIs in people: HR_EMPLOYEE_API, HR_PERSON_API, HR_ASSIGNMENT_API, HR_PERSON_ADDRESS_API, HR_PHONE_API, HR_UPLOAD_PROPOSAL_API, HR_APPLICANT_API, HR_CONTACT_API, HR_CONTACT_REL_API, HR_PERIODS_OF_SERVICE_API
+++++++++++++++++++++++++++++++++++++++++++++++++++
SIT is nothing but personal analysis kff. SIT is a mechanism by which you can create new fields, to capture additional information against an HRMS record
when a new segment combination is created, the system will first check to see whether that combination already exists in the table before creating a row. if the combination does exist, the system only retrieves the ID of the row it found. the system therefore has to scan through the whole SIT combinations table
SITs are primarily attached to people. they can also be used with jobs, positions
EITs are dff
when a new segment combination is created, the row is just inserted into the appropriate table(without checking whether the combination already exists). there is no full scan of the underlying table and so performance should not be affected by the size of the table
EITs can be attached to people, assignment, job, position, location and organization
+++++++++++++++++++++++++++++++++++++++++++++++++++
Difference between jobs and positions:
Jobs- required, generic within BG, independent of any single organization, jobs can occur in many organizations, holds fair labour standards act (FLSA) code, holds equal employment opportunity (EEO) work category, associted with worker's compensation codes
Positions- optional, specific occurence of one job, must be unique within an organization, linked to an organization, job and location, shared with other applications (i.e. purchasing), position hierarchies control access to information (security)
Similarity between jobs and positions- valid grades, evaluations, competencies, skills, work choices, not date tracked
advantages of using positions- define job more specifically, reporting, position hierarchies for PO approvals, position hierarchies for security, standard conditions and working hours, successor, competencies, requirements, recuitments, career management, career paths
you may not want to use positions in case of- re-organizations, concerned about increased maintainance
+++++++++++++++++++++++++++++++++++++++++++++++++++
8 digit password for FND_USER record: lower(dbms_random.STRING('X',8));
fnd_user_pkg.createuser(x_user_name=>p_user_name, x_owner=>'', x_encrypted_password=>v_password, x_description=>p_person_description, x_password_lifespan_days=>180, x_employee_id=>p_person_id, x_email_address=>p_email_address);
the random password that is generated might have repeating characters, which will error if profile "Signon Password Hard To Guess" is set to YES. you can temporarily set the profile to N in the session during which concurrent program runs. fnd_profile.put(NAME=>'SIGNON_PASSWORD_HARD_TO_GUESS', VAL=>'N');
+++++++++++++++++++++++++++++++++++++++++++++++++++
select instance_name from v$instance;
+++++++++++++++++++++++++++++++++++++++++++++++++++
technically how to know from tables that an employee has been terminated: PPTUF.person_type_id will correspond to system person type EX_EMP. a record gets created in PER_PERIODS_OF_SERVICE with actual_termination_date being NOT NULL
to make employee number generation automated- use fast formula
+++++++++++++++++++++++++++++++++++++++++++++++++++
PER_PEOPLE_F is a secured view on top of PER_ALL_PEOPLE_F. the secure view uses an api HR_SECURITY.SHOW_PERSON. this api internally checks for an entry in the table PER_PERSON_LIST for the logged in person's security profile. a concurrent program named "Security List Maintainence Program" will insert records in PER_PERSON_LIST
if personalization is done at both function level and responsibility level, which personalization has higher precedence: responsibility level
how to migrate personalizations from DEV to PROD: use "Functional Administrator" responsibility, click on personalization tab, then click on Import/Export
+++++++++++++++++++++++++++++++++++++++++++++++++++
what do u need to do to allow different user to see your output file: user must log on with same responsibility and the profile option concurrent report access level value must be set to responsibility
when will a request group become a request security group: when the request group is assigned to a responsibility
+++++++++++++++++++++++++++++++++++++++++++++++++++
steps to register shell script as concurrent program:
create a shell script xyz and move it to BIN directory
the parameters in shell scripts should start with $5, $6 onwards
rename the shell script to xyz.prog
change permissions to 755 for xyz.prog file
create a link to the shell script (ln -s $FND_TOP/bin/fndcpesr/xyz.prog) to create xyz file
register this executable in the application as a host executable (using sysadmin resp)
first four parameters are allocated to user_id, request_id, resp_id, resp_appl_id
+++++++++++++++++++++++++++++++++++++++++++++++++++
Difference between KFF and DFF:
KFF: unique identifier, stored in segment column
DFF: used to capture additional information, stored in attribute column
structure of KFF in accounting flexfield: company, cost center, account, product, future use (max 30 segments, min 2)
flexfield qualifiers are used to identify the segments. balancing segment qualifier, cost center segment qualifier, natural account segment qualifier, intercompany segment qualifier
dynamic insertion: code combinations can be created at run time
flex values are stored in FND_FLEX_VALUES and FND_FLEX_VALUES_TL
set of books is a financial reporting entity which consists of chart of accounts, currency and calendar. set of books is stored in GL_SETS_OF_BOOKS
currency is stored in FND_CURRENCIES and period is stored in GL_PERIOD_STATUSES
segment values are stored in GL_CODE_COMBINATIONS and concatenated values are stored in GL_CODE_COMBINATIONS_KFV
different types of currency: functional currency and foreign currency
different types of calendar: fiscal and accounting
profile is a changeable option that affects the way your application runs. there are two types of profiles- system defined and user defined
profile levels- site(lowest level), application, responsibility, user
name of some profile options- GL sets of books name, GL sets of books id, MO:operating unit(multi-org), HR:user type
cycle of GL- open the period, create journal entries, post the journals
journal entries are created in GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES
after posting data goes in GL_BALANCES (columns- period_net_cr, period_net_dr)
important tables in GL- GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES, GL_BALANCES, GL_SETS_OF_BOOKS, GL_CODE_COMBINATIONS, GL_PERIOD_STATUSES, GL_INTERFACE
supplier information is stored in PO_VENDORS, PO_VENDOR_SITES_ALL, PO_VENDOR_CONTACTS
org_id is for operating unit and organization_id is for inventory organization
+++++++++++++++++++++++++++++++++++++++++++++++++++
oracle alerts can only be triggered from an application that has been registered in oracle applications
response processing is a component of alerts which allows the recipients of an alert to reply with a message and have application take some action based on the response. response processing only works with the oracle mail products
oracle alert is designed to work with oracle office, oracle interoffice, UNIX sendmail and VMS mail
to use event alert on a custom application, we need to register the application, the application's ORACLE ID, the event table and the form with AOL
+++++++++++++++++++++++++++++++++++++++++++++++++++
how to find locked objects and kill the session:
step1- select aob.object_name, aob.object_id, vob.process, vob.session_id from all_objects aob, v$locked_object vob where aob.object_id = vob.object_id
drill down the list till you find the locked object and note down its session_id
step2- select * from v$session where SID = <session_id>
note SID and SERIAL#
step3- alter system kill session '<SID>,<SERIAL#>'
+++++++++++++++++++++++++++++++++++++++++++++++++++
p_validate FALSE- if the procedure is successful, rows/values are inserted/updated/deleted
p_validate TRUE- if the procedure is successful, without raising any validation errors, then non-warning OUT parameters are set to NULL, warning OUT parameters are set to a specific value, and IN OUT parameters are reset to their IN values
+++++++++++++++++++++++++++++++++++++++++++++++++++
KFFs in HRMS- job, position, grade, people group, cost allocation, personal analysis. all KFFs are required for setup
no DFFs are required for setup
after completion of definition of a KFF, you need to run the Create Key Flexfield Database Items process concurrent process to generate database items for the individual segments of the flexfield. this applies to job, position, grade, people group KFFs only
how to know which DFFs are available on any       form- Help->Dignostics->Examine. for the block field, click the LOV and select the value $DESCRIPTIVE_FLEXFIELD$. now click on the LOV for the field. in this LOV u will see all the DFFs available on the form
can the same flexfield structure have a different set of segments- yes, these are context sensitive segments
tables that hold the data for KFF structures: job- PER_JOB_DEFINITIONS, position- PER_POSITION_DEFINITIONS, grade- PER_GRADE_DEFINITIONS, people group- PAY_PEOPLE_GROUPS, cost allocation- PAY_COST_ALLOCATION_KEYFLEX, personal analysis- PER_ANALYSIS_CRITERIA
why aren't any fo the KFF or DFF windows opening when u click on them- make sure that the following profile options in the system administrator are set to YES at site level- flexfields: open descr window, flexfields: open key window
there is no profile option or functionality to temporarily turn off flexfield validation
you have defined job KFF structure and segments. when u navigate to assignment form, the job field has no list of values- u need to create job flexfield data on the job form. work structure->job->description. after this has been defined, u should now see data from job field list of values on the assignment form
the same is applicable for position and grade. out of all flexfields on assignment form, the people group flexfield you can create your records without having to go to a separate form
+++++++++++++++++++++++++++++++++++++++++++++++++++
assignment_status_type_id: 1-Active Assignment, 2-Suspend Assignment, 3-Terminate Assignment, 4-Active Application, 5-Offer, 6-Accepted, 7-Terminate Application, 8-End, 9-First Interview, 10-Second Interview
+++++++++++++++++++++++++++++++++++++++++++++++++++
difference between lookups and value sets:
value sets can be attached to parameters of concurrent program, whereas lookups can't
Certain types of Lookups are maintainable by the users too, for example HR Users will maintain "Ethnic Minority" lookups. Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators
Value sets can contain values that are a result of an SQL Statement. Hence it is possible to make Value Set list of values dynamic. On the contrary, Lookup Codes are Static list of values
lookups are stored in tables, value sets are not
+++++++++++++++++++++++++++++++++++++++++++++++++++
user defined tables are stored in: pay_user_tables, pay_user_rows_f, pay_user_columns, pay_user_column_instances_f, pay_user_column_instance_api.update_user_column_instance
+++++++++++++++++++++++++++++++++++++++++++++++++++
types of value sets- dependent, independent, none, table,
pair: The data input is checked against two flex-field specified range of values, special: The data input is checked against a flex-field. Special and pair value sets provide a mechanism to allow a "flexfield-within-a-flexfield". These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal flexfield segments. Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another flexfield as a value set for a single segment or to provide a range flexfield as a value set for a pair of segments.
translatable dependent, translatable independent: A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used. Flexfield Value Security cannot be used with Translatable Independent or Translatable Dependent value sets
+++++++++++++++++++++++++++++++++++++++++++++++++++
paf.organization_id = :$FLEX$.XXPLG_HR_ORGANIZATION_NAME and ppf.original_date_of_hire between NVL(fnd_date.canonical_to_date(:$FLEX$.XXPLG_FROM_DATE:NULL),TO_DATE('01-JAN-1951','DD-MON-RRRR')) and fnd_date.canonical_to_date(:$FLEX$.XXPLG_TO_DATE)
+++++++++++++++++++++++++++++++++++++++++++++++++++
Lookups- Access Level: System - No changes to the lookup codes are allowed. Extensible - New lookup codes can be added. However, you cannot modify seeded lookup codes. User - You can change any lookup code
The tag can be used to categorize lookup values
+++++++++++++++++++++++++++++++++++++++++++++++++++
Workflow Administrator Web->Oracle Application Manager->Workflow Manager
+++++++++++++++++++++++++++++++++++++++++++++++++++
What are the various levels where you can Set up Payroll Costing Accounts- Element Entry => Highest, Assignment => Second Highest, Organization => Third Highest, Element Link => Fourth Highest, Payroll => Fifth Highest
+++++++++++++++++++++++++++++++++++++++++++++++++++
Why use an SIT when we can enable descriptive flexfields against the person record- A. Data in Descriptive Flexfield against an employee record will be visible to all the users that have access to the Employee creation/query screen. On the contrary, using HR Workflow security, we can make SIT to become visible for the responsibility that we desire. B. There is limited number of descriptive flex field columns available. C. SIT let you logically group similar information together. For example, you may wish to capture "Medical illness history/details" and also Citizenship/Country Residency History" of your employees. In this case, you will create two different SIT.
+++++++++++++++++++++++++++++++++++++++++++++++++++
flexfield register screen- application: human resources, code: PEA, title: Personal Analysis Flexfield, table name: PER_ANALYSIS_CRITERIA, unique ID column: ANALYSIS_CRITERIA_ID, structure column: ID_FLEX_NUM
application developer->flexfield->key->segments. query flexfield title. add a context/structure to this flexfield. add segments/fields. freeze the structure. ensure that the allow dynamic insert is checked. in HRMS responsibility, go to define special information types and create an entry for the context
hr_sit_api.create_sit
+++++++++++++++++++++++++++++++++++++++++++++++++++
UDT- For the table's match type, select Match or Range. Select Match if each row is for one item, such as one job or one code. Select Range if each row is for a range of numeric values, such as an age range or a pay range. If the match type is Match, select Date, Number or Text as the Key Units of Measure, depending on whether entries in the rows are dates, numbers, or alphanumeric text. If the table's match type is Range, the Key Units of Measure automatically becomes Number.
+++++++++++++++++++++++++++++++++++++++++++++++++++
PAAF.soft_coding_keyflex_id
+++++++++++++++++++++++++++++++++++++++++++++++++++
A mutating table is a table that is currently being modified by an update, delete, or insert statement. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering off of.
Mutating means that some one is trying to access the table currently being held by some other non-committing transaction(i.e. in Locked state)
In general a table mutation error will result if you create a trigger on a table that attempts to examine the current table. Also, the error is given when an attempt is made to examine a parent table in a cascading update/delete.
The trigger is trying to change or examine something that’s already being changed. This confuses Oracle.
You can actually make your trigger an autonomous transaction. Remember that an autonomous transaction is an embedded block of code that executes from within a parent transaction, but it is treated by Oracle as a completely independent transaction. This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist, for Oracle does not perceive that there is any choice between an unchanged table and an uncommitted but changed one.
To make your trigger an autonomous transaction, add the statement PRAGMA AUTONOMOUS TRANSACTION and do a COMMIT within the trigger.
But you're still not out of danger! If you are using an autonomous transaction as a trigger, you are sidestepping the mutating table error by setting Oracle up to see your initial table in a pre-update state, even if uncommitted changes exist. You can then read from the table as it was before any changes occurred. So there is no potential conflict from Oracle’s point of view. Remember that the trigger you are using causes the autonomous transaction to see the original values, not the updated ones, so what you’ll end up with is the updated quantities you intended in your line items and a completely wrong average value, calculated from the pre-update quantities. And Oracle won’t tell you that you’ve implemented a flawed design.
General guidelines to keep your tables safe
If you keep the following guidelines in mind, you’ll know you aren’t building any update conflicts into your table-handling design: 1. Always remember that a row-level trigger can’t read or write the table it’s fired from. A statement-level trigger can, however. 2. Are you reading or writing? If you need to query the firing table but don’t need to modify it, you can do so with a row-level trigger by making it an autonomous transaction, which circumvents the above restriction.
autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.
Begin
      (block of code in an order processing transaction)
declare
             pragma autonomous_transaction;
begin
             (block of code in the autonomous transaction,
which is an inventory update routine)
            commit;
      end;
      (block of code in the order processing transaction
      commit;
end;
+++++++++++++++++++++++++++++++++++++++++++++++++++
A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-4091 error if you have a row trigger that reads or modifies the mutating table. A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress.
If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table.
An after statement trigger fires before any changes are saved to the database. If the trigger fails/raises an error, all changes of that transaction are rolled back.
When processing so-called bulk inserts/updates, the system might need PL/SQL tables to contain the variables. This is due to the fact that a row level trigger fires for each row (i.e. possibly more times in a bulk operation) and a statement trigger fires only once per statement (hence the names). In the before row trigger one should insert a record in the PL/SQL table. In the after statement trigger, loop through the records of the PL/SQL table and do the necessary processing.
+++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT OSE.organization_id_child
FROM PER_ORG_STRUCTURE_ELEMENTS_V OSE
WHERE p_rollup = 'Y'
and org_structure_version_id = XXHCM_HR_HOST.get_hierarchy_id()
START WITH OSE.organization_id_Parent = p_org_id
CONNECT BY OSE.organization_id_Parent = PRIOR OSE.organization_id_child
+++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT TRUNC(SYSDATE+1)-LEVEL lvl FROM DUAL CONNECT BY LEVEL <= 31
+++++++++++++++++++++++++++++++++++++++++++++++++++
v_file_test := UTL_FILE.fopen (v_dir, v_filename, 'W');
UTL_FILE.put_line (v_file_test, v_string);
UTL_FILE.fclose (v_file_test);
WHEN UTL_FILE.INVALID_PATH THEN FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Path');
+++++++++++++++++++++++++++++++++++++++++++++++++++
create elements (element screen or wizard)
defin input values for each element
define element links
element entry in assignment
payroll run (run/quick pay)
PAY_ELEMENT_TYPES_F
PAY_INPUT_VALUES_F
PAY_ELEMENT_LINKS_F
PAY_ELEMENT_ENTRIES_F
PAY_ELEMENT_ENTRY_VALUES_F
PAY_ALL_PAYROLLS_F
PAY_ASSIGNMENT_ACTIONS
PAY_RUN_RESULTS
PAY_RUN_RESULT_VALUES
PAY_PAYROLL_ACTIONS
+++++++++++++++++++++++++++++++++++++++++++++++++++
ref cursor is a data structure which points to an object which in turn points to the memory location. strong ref cursor has a return type defined. weak ref cursor doesnt have a return type
+++++++++++++++++++++++++++++++++++++++++++++++++++
select * from FND_ID_FLEX_STRUCTURES_VL where id_flex_structure_name = 'Transportation Details';
select * from PER_ANALYSIS_CRITERIA where id_flex_num = 50357 and analysis_criteria_id = 164307;
select * from PER_PERSON_ANALYSES where id_flex_num = 50357 and person_id = 9374
per_people_extra_info
+++++++++++++++++++++++++++++++++++++++++++++++++++
Update:
When a user first alters a field in a DateTracked block in the current Commit unit, he or
she sees a choice of Update prompts as follows:
• 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.
• CORRECTION - The updated values override the old record values and inherit the
same effective dates.
If the user selects UPDATE, DateTrack checks whether the record being updated starts
today. If it does, a message warns that the previous values will be lost (because
DateTrack can only store information on a day by day basis). DateTrack then changes
the mode for that record to CORRECTION.
Next, if UPDATE was selected, DateTrack checks whether the record being updated has
already had future updates entered. If it has been updated in the future, the user is
further prompted for the type of update, as follows:
• 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.
In most forms, users are prompted for the update mode for each record they update. In
some forms, they are asked for the update mode for only the first record they update.
Any other rows updated take the same update mode. Users are not prompted again,
until they have committed or cleared any outstanding changes.
Delete:
When deleting a record, the user is prompted for the type of delete. There are four
options, as follows:
• DELETE (End Date) - This is the DateTracked delete. The record that the user is
currently viewing has its effective end date set to today's date. The record
disappears from the form although the user can requery it.
• 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, including a future DateTracked delete, to be removed. The current
record has its effective end date set to 31-DEC-4712.
The record can again be displayed by requerying.
• DELETE NEXT CHANGE (Next Change) - This choice causes the next change to the
current DateTracked record to be removed.
Where another future dated DateTracked row exists for this record, it is removed
and the current row has its effective end date set to the effective end date of the
deleted row.
Where no future DateTracked row exists, but the current row has an end date other
than 31-DEC-4712, then this option causes the effective end date to be set to
31-DEC-4712. This means that a date effective end is considered to be a change.
Notice that this option again removes the current row from the form, though it can
be displayed again by requerying.
Insert:
The user is not prompted for any modes when inserting a record. The effective start
date is always set to today (Effective Date). The effective end date is set as late as
possible. Usually this is 31-DEC-4712, although it can be earlier especially when the
record has a parent DateTracked record.
+++++++++++++++++++++++++++++++++++++++++++++++++++
application developer -> application -> messages
+++++++++++++++++++++++++++++++++++++++++++++++++++
The application specific lookups views[like hr_lookups] are database views created on top of fnd_lookup_values itself
+++++++++++++++++++++++++++++++++++++++++++++++++++

Difference between functions and procedures:
A Function must return a value while procedure may or may not and even may return multiple values. Function can take only input aurguments, but procedure may take both input and out put parameters.
Only functions return a value. Procedures can only have OUT parameters. Therefore, we can use functions in a SELECT statement but we cannot use procedures similarly.
There is one more difference between stored procedure and functions that stored procedures compiled only once and can be called again and again without being compiled each time, this improves performance and saves time, on the other hand functions compile each time they get called.
Functions are normally used for computations where as procedures are normally used for executing business logic.
You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
Function returns 1 value only. Procedure can return multiple values (max 1024).
Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function won't support deferred name resolution.
Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
Stored procedure is precompiled execution plan where as functions are not.
A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
+++++++++++++++++++++++++++++++++++++++++++++++++++
Difference between rowid and rownum:
rowid is the unique index number of every row of table maintaining by database automatically. rownum is the sequential number of rows in the resultset object.
Every row is identified by a rowid. ROWID is pseudo column in every table. The physical address of the rows is use to for the ROWID.IN HEXADECIMAL representation, ROWID is shown as 18 character string of the following format
BBBBBBBBB.RRRR.FFFF (block,row,file)
FFFF is the FileID of the datafile that contains the row.
BBBBBBBBB is the address of the datablock within the datafile that contains the row.
RRRR is the ROW NUMBER with the data block that contains the row.
ROWID is the fastest means of accessing data.
They are unique identifiers for the any row in a table.
They are internally used in the construction of indexes.
Rownum is just the serial No of your output while Rowid is automatically generated unique id of a row an it is generated at the time of insertion of row.
Rownum is numeric and rowid is 16 bit hexadecimal no.
rownum is generated for the data retrieved and stored in an implicit cursor so depending on the outcum it may varry where as rowid is generated automatically for creation of every row hence forth if we want retrive a particular row in a table we can abosolutelt do that if we can give rowid.
rownum is a pseudo column which is generated for query data set at runtime. while rowid is the physical address of the row and hence definition suggest rowid for a row will never change but row num will always change.
rownum is the temp num assigned for the return rows per statement query
It will change as per the query statement.
It can be used with =1 or < 10 id number .
Where as rowid is the id assigned by the
Oracle while creating record in database. It is permanent & we can query with = sign & it will not change
or depend on query.
+++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++
A job is a generic role within a business group, which is independent of any single organization. For example, the jobs Manager and Consultant can occur in many organizations.
A position is a specific occurrence of one job, fixed within one organization. For example, the position Finance Manager is an instance of the job of Manager in the Finance organization. The position belongs to the organization.
Grades are normally used to record the relative status of employee assignments and to determine compensation and benefits, such as salary, overtime rates, and company car.
+++++++++++++++++++++++++++++++++++++++++++++++++++
Before you can define a BG in oracle HRMS, you must define six key flexfield structures:
Job, Position, Grade, People Group, Cost Allocation, Competence.
The specification must include the following details for each key flexfields:
The structure name and the number of segments, The flexfield segment names,order,validation options,and qualifiers, The flexfield value sets and any lists of values.
After you have completed the definition of a key flexfield, you need to run the Create Key Flexfield Database Items process concurrent process to generate Database Items for the individual segments of the Flexfield. This applies to your Job, Position, Grade, Competence, and People Group Key Flexfields only.
If you want to make use of the individual segments of the flexfield as separate Database Items you can run this concurrent process- Run Create Key Flexfield Database Items Process. The only parameter associated with this process is the Key Flexfield Name.
+++++++++++++++++++++++++++++++++++++++++++++++++++
Differences between 11i and R12:
You can assign a node of organization hierarchy or a list of operating units to your responsibility. Effectively, you are now able to assign multiple operating units to a single responsibility.
+++++++++++++++++++++++++++++++++++++++++++++++++++
v_file_test := UTL_FILE.fopen (v_dir, v_filename, 'W');
UTL_FILE.put_line (v_file_test, v_string);
UTL_FILE.fclose (v_file_test);
WHEN UTL_FILE.INVALID_PATH THEN FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Path');

PL /SQL
Nth Highest salary

SELECT DISTINCT (a.sal) FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Delete,Drop & Truncate
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

Duplicate row Delete from table
delete from employee
   where (rowid, empssn)
    not in
     (select min(rowid), empssn from employee group by empssn);

Write an SQL Query to Delete Duplicate records from a table using ROWID.
Delete from table_name where ROWID not in ( select
max(rowid) from table group by duplicate_values_field_name);

DELETE FROM <table_name> T1
WHERE T1.ROWID < (SELECT MAX(T2.ROWID)
                   FROM <table_name> T2
                 WHERE T2.<common column name> = T1.<common
column name>)

What are the cursor attributes used in PL/SQL ?
Ans. %ISOPEN – to check whether cursor is open or not % ROWCOUNT – number of rows fetched/updated/deleted. % FOUND – to check whether cursor has fetched any row. True if rows are fetched. % NOT FOUND – to check whether cursor has fetched any row. True if no rows are fetched. These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

What is a database trigger ? Name some usages of database trigger ? Ans. Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.
How many types of database triggers can be specified on a table ? What are they ? Ans. Insert Update Delete Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k. Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k. If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement. If WHEN clause is specified, the trigger fires according to the returned boolean value.

What is an Exception ? What are types of Exception ? Ans. Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined exceptions are. CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR INVALID_NUMBER LOGON_DENIED NOT_LOGGED_ON PROGRAM-ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE VALUE_ERROR ZERO_DIVIDE OTHERS. 22. What is Pragma EXECPTION_INIT ? Explain the usage ? Ans. The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Pragma Exception_Init(Object_Locked, -0054); 23. What is Raise_application_error ? Ans. Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger. 24. What are the return values of functions SQLCODE and SQLERRM ? Ans. SQLCODE returns the latest code of the error that has occurred. SQLERRM returns the relevant error message of the SQLCODE.
25. Where the Pre_defined_exceptions are stored ? Ans. In the standard package.
26. What is a stored procedure ? Ans. A stored procedure is a sequence of statements that perform specific function.

What is Overloading of procedures ? Ans. The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line

Can one read/write files from PL/SQL?

Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

What is mutatinig trigger? How do you avoid mutating trigger?
the row level trigger based on a table the trigger body can't read thedata from same table if you are trying to this it gives mutating error to avoid that you must use statement level trigger
what is diff between strong and weak ref cursors

strong REF CURSOR type definition specifies a return type but a weak definition does not. DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp ROWTYPE; -- strong TYPE GenericCurTyp IS REF CURSOR; -- weak

Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause is used for updating or deleting rows selected by the cursor with FOR UPDATE cluase.It explicitily locks the row for updating or deleting.

State the difference between implict and explict cursor's

Implicit cursors are faster and result in much neater code so there are very few cases where you need to resort to explicit cursors.
Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL SELECT statements including queries that return only one row. Explicit cursors for queries that return more than one row explicit cursors are declared and named by the programmer and manipulated through specific statements in the block’s executable actions.
DDL statements:
Create, Alter, Drop, Truncate, Grant. DDL statements perform implicit commit.
DML statements:
Select, Insert, Update, Delete
TCL statements:
Commit, Savepoint, Rollback, Set Transaction

Why are triggers required?

If we want to perform certain actions whenever an insert/update/delete happens on a table, we could achieve this by using a trigger. Remember, only these 3 DMLs - Insert/Update/delete could fire a trigger.
Truncate or drop or create or any other commands could not fire a trigger on a particular table.

Different types of Classification for Triggers:

First type of classification ::

a. Row Level Trigger:

For each row affected by the Insert/Update/Delete operation, the trigger is fired and thus the action associate with it.

Delete from emp where deptno=30;                  -- deletes 10 records

For eg: If you want to log all the Employee Ids if they gets deleted, you could create an After Delete Row Level Trigger such that for each ID getting deleted they could be inserted into an Audit Table. So, for the above delete operation the trigger is fired 10 times.

b. Statement Level Trigger:

                                    The trigger will be fired only once for the Insert/Update/Delete operation happened on a table.
For eg:
Update emp set sal=sal+1050 where deptno=10;

This update statement updates 30 records in a single update operation. But if the Trigger on table Emp is statement level, the update action would fire the trigger only once!

Based on the 2 types of classifications, we could have 12 types of triggers.

·        *        Before Insert row level
·        *        After Insert row level
·        *        Before Delete row level
·        *        After Delete row level
·        *        Before Update row level
·        *        After Update row level

·        *        Before Insert Statement Level
·        *        After Insert Statement Level
·        *        Before Delete Statement Level
·        *        After Delete Statement Level
·        *        Before Update Statement Level
·        *        After Update Statement Level

What is a Mutating table error (MTE)?

Oracle manages read consistent view of data. The error occurs when a row-level trigger accesses the same tables on which it is based while executing.

Maximum Trigger size in Oracle      :           32k (but functions, procedures etc could be called inside)

4. Can we create trigger on views?

            No.                  But we can use INSTEAD OF Triggers

Eg:                   create or replace trigger trg_logon
Instead of update on view_v1
For each row
Begin
<processing>                -- can call a procedure or a function too.
End;
/

5. Can we do commit or rollback in a trigger?
No

            Exception         : If the trigger is an AUTONOMOUS TRANSACTION we can commit or rollback

What is an INSTEAD OF Trigger in Oracle?

            Views cannot have triggers. So, when someone tries to update a view, you can re-direct the data to the underlying table using an INSTEAD OF trigger.

Eg:                   create or replace trigger ins_trigger
                        INSTEAD OF update on emp_view
                        For each row
                        Begin
                                    if :new.sal<:old.sal then
                                                update emp set sal=:old.sal where empno=:new.empno;
                                    else
                                                update emp set sal=:new.sal where empno=:new.empno;
                                    end if;
End;

*When INSTEAD OF trigger is written, even if the view is directly updateable, it updates using the trigger only.

What is set function in sql .E.g.What is difference between union and union all

set operators are used to combine the out put of more than
one query.

union: used to combine the output Query1 and query2
       and display them uniquely AND OUTPUT IS SORTED
ACCORDING TO FIRST COLUMN OF QUERY1

union all : it would combine the two query and displays then
          as its duplicates AND HERE NO SORTING IS DONE.

what are pl/sql collections?
A collection is an ordered group of elements, all of the
same type. It is a general concept that encompasses lists,
arrays, and other familiar datatypes. Each element has a
unique subscript that determines its position in the collection.

PL/SQL offers these collection types:

    * Index-by tables,
    * Nested tables
    * Varrays

Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Nested table is a table that is stored in database as the data of a column of the table. Nested
table is like an Index-By table, but the main difference is that a nested table can be stored in
the database and an Index-by table cannot.

There is a upper limit in Varrray and which is not in nested table.

2.     Choosing Between Nested Tables and Varrays
Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence. When stored in the database, varrays retain their ordering and subscripts.
Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.
Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end. Nested table data is stored out-of-line in a store table, a system-generated database table associated with the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection. You cannot rely on the order and subscripts of a nested table remaining stable as the table is stored and retrieved, because the order and subscripts are not preserved when a nested table is stored in the database.
The difference between VARRAY and nested table is we can have numerous value single table but in case of VARRAY we can not have more than the defined limit .

Table with in a table is called as nested table NESTED TABLE: stored outside the table supports unlimited rows DMLallowed VARRAYS: stored with in the table supports limited rows DML not allowed

Differences Between Nested Tables and Varrays
---------------------------------------------
Nested tables are unbounded, whereas varrays have a maximum size.

Individual elements can be deleted from a nested table, but not from a varray. Therefore, nested tables can be sparse, whereas varrays are always dense.

Varrays are stored by Oracle in-line (in the same tablespace), whereas nested table data is stored out-of-line in a store table, which is a system-generated database table associated with the nested table.

When stored in the database, nested tables do not retain their ordering and subscripts, whereas varrays do.

Nested tables support indexes while varrays do not.

Definition:
Profiles are the changeable options which affect the way Oracle Application runs. Moreover, Moreover, The profile option acts like a Global Variable in Oracle. It is needed to provide the flexibility to Oracle Applications.

Types:
The profiles are of two types those are given below.
1. System Profile and
2. User Profile.
Site: This field displays the current value, if set, for all users at the installation site.
Application: This field displays the current value, if set, for all users working under responsibilities owned by the application identified in the Find Profile Values block.
Responsibility: This field displays the current value, if set, for all users working under the responsibility identified in the Find Profile Values block.
User: This field displays the current value, if set, for the application user identified in the Find Profile Values block.
Profile: Enter the name of the profile option whose values you wish to display.
FND_PROFILE.GET(‘Name of the Profile’, variable name);
Example
SELECT fnd_profile.value('PROFILEOPTION')
      ,fnd_profile.value('MFG_ORGANIZATION_ID')
      ,fnd_profile.value('ORG_ID')
      ,fnd_profile.value('LOGIN_ID')
      ,fnd_profile.value('USER_ID')
      ,fnd_profile.value('USERNAME')
      ,fnd_profile.value('CONCURRENT_REQUEST_ID')
      ,fnd_profile.value('GL_SET_OF_BKS_ID')
      ,fnd_profile.value('SO_ORGANIZATION_ID')
      ,fnd_profile.value('APPL_SHRT_NAME')
      ,fnd_profile.value('RESP_NAME')
      ,fnd_profile.value('RESP_ID')
  FROM DUAL;

DECLARE
   v_conc_login_id      NUMBER;
BEGIN
   FND_PROFILE.put ('CONC_LOGIN_ID',1425);
   fnd_profile.get ('CONC_LOGIN_ID', v_conc_login_id);
   DBMS_OUTPUT.put_line (v_conc_login_id);
END;

2. variable name := FND_PROFILE.VALUE(‘Name of the profile’);
3. FND_PROFILE.PUT(‘Name of the profile’, value of the profile);

Qns: What are different Profiles Level available in oracle apps.
Ans: Below are the Profiles Level available in oracle apps
1. Site(Lowest level)
2. Application
3. Responsibility
4. User.
Qns: In which table flex values are stored.
Ans: 1. fnd_ flex_Values
2. fnd_ flex_Values_tl
Qns: What are flexfield Qualifiers.
Ans: Flexfield Qualifiers is used to identify the segments. Various types of flexfield qualifiers are listed below:
a) Balancing Segment Qualifier.
b) Cost Center segment Qualifier.
c) Natural Account Segment Qualifier.
d) Intercompany Segment Qualifier.
Qns: How many segments are in DFF.
Ans: max 30 segments and min two.

What are the types of Concurrent Managers?
Can we delete a Concurrent Manager?
There are three types of Concurrent manager, list Below

1-Standard Concurrent Manager

2-Conflict Concurrent Manager

3-Not Recalling later on

there are mainly three types of concurrent managers are there they are as follows :

1 : internal concurrent manager
2 : standard concurrent manager
3 : conflict resolution manager

to delete a concurrent manager :

You can disable the manager by checking the 'Enabled' checkbox You can simply Terminate the manager and it will not run
again unless you reactivate it. Or, if you really, really want to, you can query the manager in the 'Define Manager' form, and
delete the row. (But DONT do this)

Shell script reg in conc prog
step 1:
=======
Place the <name>.prog script under the bin directory for your
applications top directory.

For example, call the script ERPS_DEMO.prog and place it under
$CUSTOM_TOP/bin

step 2:
=======
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called ERPS_DEMO.prog use this:

ln -s $FND_TOP/bin/fndcpesr ERPS_DEMO

This link should be named the same as your script without the
.prog extension.

Put the link for your script in the same directory where the
script is located.

step 3:
=======
Register the concurrent program, using an execution method of
‘Host’. Use the name of your script without the .prog extension
as the name of the executable.

For the example above:
Use ERPS_DEMO

step 4:
=======
Your script will be passed at least 4 parameters, from $1 to $4.
$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id

Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.

How to take care of null values in SQL Loader ?

By using the following statement in Control file.

TRAILING NULLCOLS

What are the diiferent types of Value sets and expalin them briefly which value set will use when?

There are eight(8) diffrent types of Value sets available in Oracle Apps.

1)Independent value set : If user set of values need to be populated to a perticular parameter then we can use Independent value set.

2)Dependent value set : It always dependent upon independent value set only.

3)Table value set : It will get the values from the database objects (such as table or view).

4)Special value set : It is used for Flex Fields.

5)Pair value set : To give maximum value and minimum value to the value set.

6)None Value set: If parameter value has to enter by end user.

7)Translated Independent value Set : It translate values into different languages for an independent value set.

8)Translated Dependent Value Set : It Translate values into different languages for an dependent value set.

What is the purpose of a flexfield qualifier? Why is it needed when you can have a unique name for a segment?

Flexfield qualifiers are basically used to identify the nature of the segment at the flexfield level. Qualifiers like Balancing,Cost Center, InterEntity,Natural Account etc..indicates the type of segment. For Ex--> Normally company segment is qualified as Balancing, which will intimate that the debits should balance the credits for a particular account for a specified company. i.e for a specific GL code combinations. Hence having unique names to the segments/flexfield has no connection with this as the purpose of the above is totally different.

What is the difference between flexfield qualifier and segment qualifier?

flexfield qualifier identifies a particular segment of a key flexfield. You can think of a flexfield qualifier as an ”identification tag” for asegment.
Ex. balancing segment, natural accounting segment, cost center and intercompany segment qualifiers
A segment qualifier identifies a particular type of value in a single segment of a key flexfield. In the Oracle Applications, only the Accounting Flexfield uses segment qualifiers. You can think of a segment qualifier as an ”identification tag” for a value.
Allow Budgeting, Allow Posting, and Account Type fields are segment qualifiers for the Accounting Flexfield

What are context sensitive segments in descriptive flexfields?
CONTEXT SENSITIVE SEGMENTS are displayed based on the context value. for eg, in a application where we enter the employee information, certain segments should be displayed with respect to designation and some should not. This is where CONTEXT SENSITIVE SEGMENTS come into picture

Context Sensitive Segments are conditional DFFs. Only when a condition is met a particular field 'appears' and we are able to capture details. So for that first DFF is made context sesitive and then second one if made reference.

What is the difference between data conversion and data migration?

Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

Data Conversion refers to the movement of data from a legacy system or application, to a replacement application or sub-system.
Data migration refers to movement of data from one database to another database, but not neccesarily to a working application or subsystem tables.

Prior to Oracle 8.0, you cannot read or write a line of text with more than 1023 bytes. In Oracle 8.0 and above, you can specify a maximum line size of up to 32767 when you open a file..

Concurrent prog upload through fnd_load
Parameter INOUT nocopy

The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value.
When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables, which are then used by the subprogram being called. On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the original parameter values being left unchanged. The process of copying large parameters, such as records, collections, and objects requires both time and memory which affects performance.
With the NOCOPY hint the parameters are passed by reference and on successful completion the outcome is the same, but unhandled exceptions may leave the parameters in an altered state, so programs must handle errors or cope with the suspect values.
The nocopy.sql script compares the performance of both methods by passing a populated collection as a parameter

Terminate emp api

Log file store where? FND_FILE

XML Publisher :
<split-by-width> to generate cpls dynamically
<spli-by-page-break> page break

Terminate emp

PROCEDURE delete_employee ( p_person_id IN NUMBER ,p_leaving_reasons IN VARCHAR2 ,p_last_working_date IN DATE ,p_actual_notice_period_date IN DATE ) IS -- Purpose :Call the Terminate Employee API -- Parameter :p_person_id,p_Leaving_Reason, -- p_Last_Working_Date,p_Actual_Notice_Period_Date l_business_group_id NUMBER := fnd_profile.VALUE ('PER_BUSINESS_GROUP_ID'); l_person_id per_all_people_f.person_id%TYPE; l_start_date DATE; l_end_date DATE; l_object_version_number NUMBER; l_period_of_service_id NUMBER; l_validate BOOLEAN := FALSE; l_supervisor_warning BOOLEAN; l_event_warning BOOLEAN; l_interview_warning BOOLEAN; l_review_warning BOOLEAN; l_recruiter_warning BOOLEAN; l_asg_future_changes_warning BOOLEAN; l_entries_changed_warning VARCHAR2 (30); l_pay_proposal_warning BOOLEAN; l_dod_warning BOOLEAN; l_last_working_date DATE; BEGIN SELECT pps.period_of_service_id ,pps.object_version_number INTO l_period_of_service_id ,l_object_version_number FROM per_periods_of_service pps WHERE person_id = p_person_id; l_last_working_date := p_last_working_date; hr_ex_employee_api.actual_termination_emp (p_validate => l_validate ,p_effective_date => TRUNC (SYSDATE) ,p_period_of_service_id => l_period_of_service_id ,p_object_version_number => l_object_version_number ,p_actual_termination_date => p_actual_notice_period_date ,p_last_standard_process_date => l_last_working_date ,p_leaving_reason => p_leaving_reasons ,p_supervisor_warning => l_supervisor_warning ,p_event_warning => l_event_warning ,p_interview_warning => l_interview_warning ,p_review_warning => l_review_warning ,p_recruiter_warning => l_recruiter_warning ,p_asg_future_changes_warning => l_asg_future_changes_warning ,p_entries_changed_warning => l_entries_changed_warning ,p_pay_proposal_warning => l_pay_proposal_warning ,p_dod_warning => l_dod_warning ); END delete_employee;

Multi-Org is an important feature of Oracle Applications for several reasons:

Allows multiple sets of books and multiple legal entities to be configured and to operate in the same instance

Provides support for data security between business units within a single applications installation

Permits users to sell and ship products from different legal entities (in different sets of books) with automatic intercompany accounting

Supports internal requisitions and purchasing/receiving products from different inventory organizations (within the same set of books)

Enables an enterprise to be housed in one database instance of Oracle, spanning multiple countries, currencies, and legal entities without a reduction in response times (architecture-related)

Multiple Organizations Reporting enhances the reporting capabilities of Oracle Applications products by allowing you to report at the:Set of Books level, Legal entity level or Operating unit level

Multiple Organization in Oracle Applications depends primarily on defining your organizational structure in the multi-level hierarchy used by Oracle Applications. The levels are:
Business groups

Accounting sets of books

Legal entities

Operating units]

Inventory organizations

Business Groups: Oracle Applications secures human resources information, including organization definition, by business group. At least one Business Group will be required for every country since the employee legislation is specific for each country and employee profile is set up at the Business Group level. Security access to the sensitive Human Resources data is secured at the highest level at the Business Group. In short Business Group partitions Human Resources information in a multi-organization structure. Organization Structures and Organization Hierarchy is defined within the Business Group.
Multiple sets of books can share the same business group if they share the same business group attributes, including HR flex-field structures (Grade, positions flex-fields etc).
Accounting sets of books: A General Ledger concept for having separate financial reporting entities for which chart of accounts, calendar, or functional currency differs. In addition for scalability and ensuring independent numbering system for all the accounting transactions as well as the ability to open and close each of the legal entities period independently a separate set of books will be configured for each legal entity.
Legal entities: An organization that represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other relevant legal company information to this entity.
Operating units: An organization that partitions and uses data for Payables, Purchasing, Order Management, Cash Management, Fixed Assets and Receivables. Operating Units allow for configuration of the Oracle Applications across Multiple Business Groups using a single installation of the software.
Inventory organizations: An organization that tracks inventory transactions and balances, and/or that manufactures or distributes products or components. Segregate Item data objects for Inventory, Purchasing, Order Entry, and the Manufacturing Applications (Organization_Id).
Examples could be manufacturing plants, warehouses, distribution centers, and sales offices
The following applications secure information by inventory organization: Oracle Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and Purchasing receiving functions. To run any of these applications, you must choose an organization that has been classified as an inventory organization.
HR Organization Internal Departments to which Employees are Assigned.
Asset Organizations An asset organization is an organization that allows you to perform assetâ€“related activities for a specific Oracle Assets corporate depreciation book. Oracle Assets uses only organizations designated as asset organizations

Being arjoon...

Tuesday 26 April 2011

SQL Interview Questions

Thursday 14 April 2011

HRMS

What is mutatinig trigger? How do you avoid mutating trigger?

what is diff between strong and weak ref cursors

Explain the usage of WHERE CURRENT OF clause in cursors ?

State the difference between implict and explict cursor's

a. Row Level Trigger:

b. Statement Level Trigger:

2. Choosing Between Nested Tables and Varrays

Definition:

Types:

What is the difference between flexfield qualifier and segment qualifier?

What is the difference between data conversion and data migration?