Tuesday, February 14, 2012

Delete time card API

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 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

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.
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

OTL Imp Metalink note which will have all the setup details and API details of OTL

223987.1

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;

Saturday, October 15, 2011

To Find the executable name and package name and application name of a concurrent program

 select fa.application_name, fe.executable_name,fe.execution_file_name package_name,
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