hxc_timestore_deposit.DELETE_TIMECARD ( p_time_building_block_id => l_timecard_to_delete,);
p_time_building_block_id The id of the Time Building Block
Tuesday, February 14, 2012
Tuesday, February 7, 2012
Query to Get OTL Template information for particular user from Database
SELECT ProjectNumber,
TaskNumber,
attribute1,
attribute2,
attribute3,
MAX (monday) AS monday,
MAX (tuesday) AS tuesday,
MAX (wednesday) AS wednesday,
MAX (thursday) AS thursday,
MAX (friday) AS friday,
MAX (saturday) AS saturday,
MAX (sunday) AS sunday
FROM ( SELECT ProjectNumber,
start_time,
TaskNumber,
attribute1,
attribute2,
attribute3,
DECODE (TRIM (day_of_week), 'Monday', measure, NULL) AS monday,
DECODE (TRIM (day_of_week), 'Tuesday', measure, NULL)
AS tuesday,
DECODE (TRIM (day_of_week), 'Wednesday', measure, NULL)
AS wednesday,
DECODE (TRIM (day_of_week), 'Thursday', measure, NULL)
AS thursday,
DECODE (TRIM (day_of_week), 'Friday', measure, NULL) AS friday,
DECODE (TRIM (day_of_week), 'Saturday', measure, NULL)
AS saturday,
DECODE (TRIM (day_of_week), 'Sunday', measure, NULL) AS sunday
FROM (SELECT day_week.day_of_week,
Project_Id,
ProjectNumber,
measure,
start_time,
TaskNumber,
attribute1,
attribute2,
attribute3
FROM ( SELECT TRIM (TO_CHAR (SYSDATE + rown, 'Day'))
AS day_of_week
FROM (SELECT ROWNUM rown
FROM all_objects
WHERE ROWNUM < 8)
ORDER BY DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Monday', 1),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Tuesday', 2),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Wednesday', 3),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Thursday', 4),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Friday', 5),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Saturday', 6),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Sunday', 7)) day_week,
(SELECT DISTINCT pop.project_id,
pop.project_number ProjectNumber,
details.measure,
days.start_time,
pot.task_number TaskNumber,
ta.attribute1,
ta.attribute2,
ta.attribute3
FROM hxc_time_attributes ta,
hxc_time_attribute_usages tau,
hxc_time_building_blocks days,
hxc_time_building_blocks details,
pa_online_projects_v pop,
pa_online_tasks_v pot,
pa_projects_all p,
pa_tasks t
WHERE details.resource_id = :1 --Resource_id(Person_id from per_all_people_f table)
-- AND ABS (days.start_time - TO_DATE (:2, 'YYYY/MM/DD')) <= 21
AND days.scope = 'DAY'
AND DAYS.PARENT_building_block_id =:2 --33983243 --Template_id from hxc_template_summary---37272687
AND days.date_to = hr_general.end_of_time
AND details.scope = 'DETAIL'
AND details.date_to = hr_general.end_of_time
AND details.parent_building_block_id =
days.time_building_block_id
AND details.parent_building_block_ovn =
days.object_version_number
AND tau.time_building_block_id =
details.time_building_block_id
AND tau.time_building_block_ovn =
details.object_version_number
AND tau.time_attribute_id =
ta.time_attribute_id
AND ta.attribute_category = 'PROJECTS'
AND TO_NUMBER (ta.attribute1) = pop.project_id
AND TO_NUMBER (ta.attribute2) = pot.task_id
AND pop.project_id = p.project_id
AND pot.task_id = t.task_id) QRSLT
WHERE TRIM (TO_CHAR (QRSLT.start_time(+), 'Day')) =
day_week.day_of_week)
ORDER BY projectnumber, start_time)
GROUP BY ProjectNumber,
TaskNumber,
attribute1,
attribute2,
attribute3
TaskNumber,
attribute1,
attribute2,
attribute3,
MAX (monday) AS monday,
MAX (tuesday) AS tuesday,
MAX (wednesday) AS wednesday,
MAX (thursday) AS thursday,
MAX (friday) AS friday,
MAX (saturday) AS saturday,
MAX (sunday) AS sunday
FROM ( SELECT ProjectNumber,
start_time,
TaskNumber,
attribute1,
attribute2,
attribute3,
DECODE (TRIM (day_of_week), 'Monday', measure, NULL) AS monday,
DECODE (TRIM (day_of_week), 'Tuesday', measure, NULL)
AS tuesday,
DECODE (TRIM (day_of_week), 'Wednesday', measure, NULL)
AS wednesday,
DECODE (TRIM (day_of_week), 'Thursday', measure, NULL)
AS thursday,
DECODE (TRIM (day_of_week), 'Friday', measure, NULL) AS friday,
DECODE (TRIM (day_of_week), 'Saturday', measure, NULL)
AS saturday,
DECODE (TRIM (day_of_week), 'Sunday', measure, NULL) AS sunday
FROM (SELECT day_week.day_of_week,
Project_Id,
ProjectNumber,
measure,
start_time,
TaskNumber,
attribute1,
attribute2,
attribute3
FROM ( SELECT TRIM (TO_CHAR (SYSDATE + rown, 'Day'))
AS day_of_week
FROM (SELECT ROWNUM rown
FROM all_objects
WHERE ROWNUM < 8)
ORDER BY DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Monday', 1),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Tuesday', 2),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Wednesday', 3),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Thursday', 4),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Friday', 5),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Saturday', 6),
DECODE (
TRIM (TO_CHAR (SYSDATE + rown, 'Day')),
'Sunday', 7)) day_week,
(SELECT DISTINCT pop.project_id,
pop.project_number ProjectNumber,
details.measure,
days.start_time,
pot.task_number TaskNumber,
ta.attribute1,
ta.attribute2,
ta.attribute3
FROM hxc_time_attributes ta,
hxc_time_attribute_usages tau,
hxc_time_building_blocks days,
hxc_time_building_blocks details,
pa_online_projects_v pop,
pa_online_tasks_v pot,
pa_projects_all p,
pa_tasks t
WHERE details.resource_id = :1 --Resource_id(Person_id from per_all_people_f table)
-- AND ABS (days.start_time - TO_DATE (:2, 'YYYY/MM/DD')) <= 21
AND days.scope = 'DAY'
AND DAYS.PARENT_building_block_id =:2 --33983243 --Template_id from hxc_template_summary---37272687
AND days.date_to = hr_general.end_of_time
AND details.scope = 'DETAIL'
AND details.date_to = hr_general.end_of_time
AND details.parent_building_block_id =
days.time_building_block_id
AND details.parent_building_block_ovn =
days.object_version_number
AND tau.time_building_block_id =
details.time_building_block_id
AND tau.time_building_block_ovn =
details.object_version_number
AND tau.time_attribute_id =
ta.time_attribute_id
AND ta.attribute_category = 'PROJECTS'
AND TO_NUMBER (ta.attribute1) = pop.project_id
AND TO_NUMBER (ta.attribute2) = pot.task_id
AND pop.project_id = p.project_id
AND pot.task_id = t.task_id) QRSLT
WHERE TRIM (TO_CHAR (QRSLT.start_time(+), 'Day')) =
day_week.day_of_week)
ORDER BY projectnumber, start_time)
GROUP BY ProjectNumber,
TaskNumber,
attribute1,
attribute2,
attribute3
Tuesday, January 31, 2012
OTL submit and Save time card through API's
The timecards are first saved using p_mode = 'SAVE' (hxc_timestore_deposit.execute_deposit_process) and when the user is ready he submits his timecard using p_mode = 'SUBMIT'.
Sample code for submit and save the time cards.
Sample code for submit and save the time cards.
DECLARE
v_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
v_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
v_tbv_messages hxc_self_service_time_deposit.message_table;
v_new_timecard_id NUMBER;
v_new_timecard_ovn NUMBER;
i PLS_INTEGER;
v_message fnd_new_messages.message_text%TYPE;
BEGIN
FND_GLOBAL.APPS_INITIALIZE( user_id => 75375
,resp_id => 65477
,resp_appl_id => 809 );
hxc_timestore_deposit.update_building_block(p_building_block_id => 23717233,
p_app_blocks => v_tbl_timecard_info,
p_app_attributes => v_tbl_attributes_info);
hxc_timestore_deposit.execute_deposit_process (
p_validate => FALSE,
p_app_blocks => v_tbl_timecard_info,
p_app_attributes => v_tbl_attributes_info,
p_messages => v_tbv_messages,
p_mode => 'SUBMIT',
p_deposit_process => 'OTL Deposit Process',
p_timecard_id => v_new_timecard_id,
p_timecard_ovn => v_new_timecard_ovn);
-- messages have been returned
IF v_tbv_messages.COUNT != 0 THEN
i := v_tbv_messages.FIRST;
LOOP
EXIT WHEN (NOT v_tbv_messages.EXISTS (i));
-- First translate the message as the messagetable returned does not give the actual
-- message, only the message_name which doesn't mean anything to the user.
v_message := fnd_message.get_string (appin => v_tbv_messages (i).application_short_name,
namein => v_tbv_messages (i).message_name);
-- returns 4: 4
dbms_output.put_line(v_tbv_messages(i).message_name||': '||v_message);
i := v_tbv_messages.NEXT (i);
END LOOP;
END IF;
END;
Tuesday, December 6, 2011
OTL Metalink Imp Notes
OIT to OTL Migration (Metalink, Note: 289674.1)
Oracle OTL HXC Time Store Deposit API (Metalink Note: 223987.1)
Oracle Time & Labor Implementation and User Guide (Metalink Note: 207333.1)
Oracle Time & Labor Mini-Pack About Documents
• HXT.G (Metalink Note: 258309.1)
• HXT.H (Metalink Note: 282087.1)
• HXT.I (Metalink Note: 303755.1)
• HXT.J (Metalink Note: 344022.1)
Oracle Time & Labor Timecard Configuration 11.5.10+ (Metalink Note: 304340.1; customers whose systems are not yet at the 11.5.10
Oracle OTL HXC Time Store Deposit API (Metalink Note: 223987.1)
Oracle Time & Labor Implementation and User Guide (Metalink Note: 207333.1)
Oracle Time & Labor Mini-Pack About Documents
• HXT.G (Metalink Note: 258309.1)
• HXT.H (Metalink Note: 282087.1)
• HXT.I (Metalink Note: 303755.1)
• HXT.J (Metalink Note: 344022.1)
Oracle Time & Labor Timecard Configuration 11.5.10+ (Metalink Note: 304340.1; customers whose systems are not yet at the 11.5.10
Tuesday, November 29, 2011
OTL queries
Query to check if for a person a Timecard has been submitted or not for the start and stop time
SELECT time_building_block_id, object_version_number, start_time,
comment_text, created_by, creation_date, last_updated_by,
last_update_date, last_update_login
FROM hxc_time_building_blocks
WHERE resource_id = <employee id>
AND SCOPE = 'TIMECARD'
AND start_time = <Start Time>
AND stop_time = <stop Time>
AND date_to = hr_general.end_of_time;
Query to get the Complete Timecard Data:
SELECT *
FROM HXC_TIME_BUILDING_BLOCKS
WHERE date_to=hr_general.end_of_time
CONNECT BY prior time_building_block_id=parent_building_block_id
AND object_version_number=parent_building_block_ovn
START WITH time_building_block_id=< Timecard Id>
ORDER BY 1
Query to get all Attributes Information for a complete Timecard
SELECT htb.resource_id Person_id,
htb.start_time Start_time, htb.stop_time Stop_time,
htb2.time_building_block_id, hta.time_attribute_id,
htb2.measure, htb2.start_time, htb2.stop_time,
hta.Attribute_category
FROM hxc_time_building_blocks htb,
hxc_time_building_blocks htb1,
hxc_time_building_blocks htb2,
hxc_time_attribute_usages htau,
hxc_time_attributes hta
WHERE htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND htb.date_to = hr_general.end_of_time
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
AND htb1.date_to = hr_general.end_of_time
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number
AND htb2.SCOPE = 'DETAIL'
AND htb2.date_to = hr_general.end_of_time
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND TRUNC(htb.start_time) = <start Date>
AND TRUNC(htb.stop_time) = <End Date>
AND htb.resource_id = <resource_id>
Use the preference to get the setup for a particular resource id
hxc_preference_evaluation.resource_preferences
(<.Resource_id>,'TC_W_TCRD_LAYOUT',1,SYSDATE);
Table : hxc_pref_definitions
l_pref_table hxc_preference_evaluation.t_pref_table;
hxc_preference_evaluation.resource_preferences(<resource_id>,l_pref_table,sysdate);
l_index := l_pref_table.first;
LOOP
EXIT WHEN
(NOT l_pref_table.exists(l_index));
IF (l_pref_table(l_index).preference_code = 'TC_W_TCRD_LAYOUT') THEN ....
l_index := l_pref_table.next(l_index);
END LOOP;
SELECT time_building_block_id, object_version_number, start_time,
comment_text, created_by, creation_date, last_updated_by,
last_update_date, last_update_login
FROM hxc_time_building_blocks
WHERE resource_id = <employee id>
AND SCOPE = 'TIMECARD'
AND start_time = <Start Time>
AND stop_time = <stop Time>
AND date_to = hr_general.end_of_time;
Query to get the Complete Timecard Data:
SELECT *
FROM HXC_TIME_BUILDING_BLOCKS
WHERE date_to=hr_general.end_of_time
CONNECT BY prior time_building_block_id=parent_building_block_id
AND object_version_number=parent_building_block_ovn
START WITH time_building_block_id=< Timecard Id>
ORDER BY 1
Query to get all Attributes Information for a complete Timecard
SELECT htb.resource_id Person_id,
htb.start_time Start_time, htb.stop_time Stop_time,
htb2.time_building_block_id, hta.time_attribute_id,
htb2.measure, htb2.start_time, htb2.stop_time,
hta.Attribute_category
FROM hxc_time_building_blocks htb,
hxc_time_building_blocks htb1,
hxc_time_building_blocks htb2,
hxc_time_attribute_usages htau,
hxc_time_attributes hta
WHERE htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND htb.date_to = hr_general.end_of_time
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
AND htb1.date_to = hr_general.end_of_time
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number
AND htb2.SCOPE = 'DETAIL'
AND htb2.date_to = hr_general.end_of_time
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND TRUNC(htb.start_time) = <start Date>
AND TRUNC(htb.stop_time) = <End Date>
AND htb.resource_id = <resource_id>
Use the preference to get the setup for a particular resource id
hxc_preference_evaluation.resource_preferences
(<.Resource_id>,'TC_W_TCRD_LAYOUT',1,SYSDATE);
Table : hxc_pref_definitions
l_pref_table hxc_preference_evaluation.t_pref_table;
hxc_preference_evaluation.resource_preferences(<resource_id>,l_pref_table,sysdate);
l_index := l_pref_table.first;
LOOP
EXIT WHEN
(NOT l_pref_table.exists(l_index));
IF (l_pref_table(l_index).preference_code = 'TC_W_TCRD_LAYOUT') THEN ....
l_index := l_pref_table.next(l_index);
END LOOP;
Saturday, October 15, 2011
To Find the executable name and package name and application name of a concurrent program
fcp.concurrent_program_id,fcpt.user_concurrent_program_name,fcp.concurrent_program_name
from fnd_executables fe,fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpt, fnd_application_tl fa
where fe.executable_id = fcp.executable_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcp.concurrent_program_name in ( Concurrent program short name)
and fcp.application_id = fa.application_id
Subscribe to:
Posts (Atom)