Thursday 11 October 2012

Costing Creation/Allocation API in HRMS

DECLARE
l_combination_name VARCHAR2(100);
l_cost_allocation_id
NUMBER; l_effective_start_date
DATE; l_effective_end_date
DATE; l_cost_allocation_keyflex_id
NUMBER;l_cc_object_version_number NUMBER; BEGIN
pay_cost_allocation_api.create_cost_allocation
(p_validate => FALSE,
 p_effective_date => SYSDATE,
 p_assignment_id => 11972,
 p_proportion => 1,
 p_business_group_id => 81,
 p_segment1 => '600',
 p_segment2 => '310000',
 p_segment3 => 'AFA',
 p_combination_name => l_combination_name
 p_cost_allocation_id => l_cost_allocation_id
 p_effective_start_date => l_effective_start_date
 p_effective_end_date => l_effective_end_date
 p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
 p_object_version_number => l_object_version_number
);
EXCEPTIONWHEN OTHERSTHENapi_msg := SUBSTR(SQLERRM,1,2400);
Fnd_File.put_line(Fnd_File.log,'Error - '||api_msg);
END;

ORA-20001: PAY_50983_INVALID_PROPORTION:

PAY_COST_ALLOCATION_API failing with the error below when the parameter value for p_proportion is set to '100'?
ORA-20001: PAY_50983_INVALID_PROPORTION:
ORA-06512: at "APPS.PAY_COST_ALLOCATION_API", line 721
ORA-06512: at line 13

ANSWER:

The parameter value for p_proportion should be set to '1'.  This is the equivalent of 100% entered via the application screen.

Tuesday 9 October 2012

Query to Extract Employee Contact Information

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

Wednesday 3 October 2012

Organizations in HRMS

Technical Details

Tables

HR_ALL_ORGANIZATION_UNITS
The basic details of an organization are stored here.
There is also a translation table HR_ALL_ORGANIZATION_UNITS_TL for the organization name in each language installed.

HR_ORG_INFORMATION_TYPES
This holds the different types of organization information.
Each type is defined in the Org Developer DF as a context value. eg Work Day Information, Business Group Information, etc.
When using the Organizations screen you would see the information types relevant to the org classification.

HR_ORG_INFO_TYPES_BY_CLASS
This table holds the org information types that are available for each classification.
This data is seeded and there is no screen that displays these groupings.
When you select a classification on the Org screen and press the Other button, this table is referenced to display the relevant org information types.

HR_ORGANIZATION_INFORMATION
This table is more complicated as it stores two distinct sets of information.
To know which type of information is stored you need to check the value in column ORG_INFORMATION_CONTEXT.
When the value is 'CLASS' the row is used to link an organization to a classification. There will be one row for every classification used by an organization. The classification name is held in column ORG_INFORMATION1.
If you create an organization, a row will be added here for every classification you save against that org.
When the value is set to an information type (from HR_ORG_INFORMATION_TYPES) the columns ORG_INFORMATION1-20 are used to hold the values for that information type.
So when you are in the Define Organizations screen and you press the others button and select an information type, the values you see in the fields on the screen come from this table.
The information type is a dff context with some segments defined. Each segment is mapped to one of the ORG_INFORMATIONx columns in this table. When you open the information type field you see the individual segments and the value for each segment is held in the column in table HR_ORGANIZATION_INFORMATION that matches the column specified in the segment definition.
The lookup ORG_TYPE holds values for the 'Type' field on the Define Organization screen.

The lookup ORG_CLASS holds values for the Classifications Name field on the Define Organization screen.


This sql will prompt you for an organization name

SQL> select i.organization_id, o.name, l.meaning
from hr_all_organization_units o
, hr_organization_information i
, fnd_lookup_values l
where o.organization_id = i.organization_id
and o.name = '&Organization_Name'
and i.org_information1 = l.lookup_code
and l.lookup_type = 'ORG_CLASS'
and i.org_information_context = 'CLASS';


This sql will prompt you for an organization information values

SQL> select o.name
, i.org_information1 Normal_Start_Time
, i.org_information2 Normal_End_Time
, i.org_information3 Working_Hours
, i.org_information4 Frequency
from hr_organization_information i
, hr_all_organization_units o
where o.name = '&Organization_Name'
and o.organization_id = i.organization_id
and i.org_information_context = 'Work Day Information';

Profile Option Values Query in Oracle Apps

SELECT distinct
fpot.profile_option_name profile_short_name
,substr(fpot.user_profile_option_name,1,60) profile_name
,DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
,fr.responsibility_name,DECODE(fpov.level_id,10001,
null,
10002,
fa.application_short_name
,10003,
fr.responsibility_name,
10004,
fu.user_name
) level_value,
fpov.profile_option_value profile_value
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, fnd_responsibility_tl fr
, fnd_user fu
WHERE (fpo.profile_option_name like nvl(fpo.profile_option_name,fpo.profile_option_name)
or fpot.user_profile_option_name like nvl(fpot.user_profile_option_name,fpot.user_profile_option_name))
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value
and fu.end_date is null

Profile Option Values Load using API in Oracle Apps

BEGIN

FND_PROFILE_OPTION_VALUES_PKG.INSERT_ROW
(X_ROWID=>X_ROWID,
 X_APPLICATION_ID=>v_application_id,
 X_PROFILE_OPTION_ID=>v_profile_option_id,
 X_LEVEL_ID=>crec.level_id,
 X_LEVEL_VALUE =>L_LEVEL_VALUE,
 X_CREATION_DATE =>SYSDATE,
 X_CREATED_BY =>fnd_global.login_id,
 X_LAST_UPDATE_DATE =>SYSDATE,
 X_LAST_UPDATED_BY =>fnd_global.login_id,
 X_LAST_UPDATE_LOGIN=>fnd_global.login_id,
 X_PROFILE_OPTION_VALUE=>crec.profile_value,
 X_LEVEL_VALUE_APPLICATION_ID=>crec.level_value_application_id,
 X_LEVEL_VALUE2=>NULL
); 
END;
---
---
L_LEVEL_VALUE based on the Profile level.

IF profile_level ='site'

THENL_LEVEL_VALUE :=NULL;ELSIF
profile_level ='Appl'
THENL_LEVEL_VALUE :=l_application_id(from fnd_application);
ELSIF
profile_level ='Resp'THENL_LEVEL_VALUE :=v_responsibility_id(from FND_RESPONSIBILITY);
ELSIF
profile_level ='User'THENL_LEVEL_VALUE :=v_user_id;(from FND_USERS);
END IF;

Download/Uplaod Value Sets in Oracle Apps

Value Sets

FNDLOAD apps/hrapps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FUNCTIONS_LOV.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME='XX_FUNCTIONS_LOV'

Value Set Values

FNDLOAD apps/hrapps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FUNCTIONS_LOV_VAL.ldt VALUE_SET FLEX_VALUE_SET_NAME='XX_FUNCTIONS_LOV'

Uplaod

FNDLOAD apps/mig3apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_FUNCTIONS_LOV.ldt