kumar whiz

Tuesday, March 6, 2012

API To Delete whole time card for the week in OTL

DECLARE
-- Constant declarations
l_otl_appl_id CONSTANT NUMBER (3) := 809; --OTL application id
l_resp_appl_id NUMBER (10) := 51102; --OTL Responsibility id
l_user_id VARCHAR2 (20) := 120345; -- id from fnd_user table
BEGIN
-- First initialize your session, this needs to be done for internal reasons so
-- the TimeStore knows who is trying to deposit the information. When you log
-- into SS, the same is done for you by the framework, here however we have to do
-- it manually.
FND_GLOBAL.
APPS_INITIALIZE (user_id => l_user_id,
resp_id => l_resp_appl_id,
resp_appl_id => l_otl_appl_id); -- This is the appl_id for OTL, do not change

--
--Delte time card API calling
--
hxc_timestore_deposit.DELETE_TIMECARD (p_building_block_id => 37275996); --p_building_block_id is the timecard id with scope 'Timecard' and endate should be ''12/31/4712

COMMIT;
END;

API To Delete timecard Detail(row wise) in OTL

DECLARE
-- Constant declarations
-- This is the appl_id for OTL, do not change
c_otl_appl_id CONSTANT NUMBER (3) := 809;
-- Variable declarations
-- declare the PL/SQL Table that will hold the complete timecard (all the BBs)
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;

-- declare the PL/SQL Table that will hold all the attributes

l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;

-- declare the PL/SQL Table that will hold the messages returned by the API
l_tbl_messages hxc_self_service_time_deposit.message_table;
l_out_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_out_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
BEGIN
-- First initialize your session, this needs to be done for internal reasons so
-- the TimeStore knows who is trying to deposit the information. When you log
-- into SS, the same is done for you by the framework, here however we have to do
-- it manually.
FND_GLOBAL.APPS_INITIALIZE( user_id => 120345
,resp_id => 51102
,resp_appl_id => 809 );-- This is the appl_id for OTL, do not change
--xc_self_service_time_deposit.delete_timecard(37276080, sysdate, 'DELETE','OTL Deposit Process',null);
hxc_timestore_deposit.delete_detail_bb (
p_building_block_id =>37276080,
p_app_blocks=> l_tbl_timecard_info,
p_app_attributes=> l_tbl_attributes_info
);
hxc_timestore_deposit.execute_deposit_process (
p_validate=> FALSE,
p_app_blocks=> l_tbl_timecard_info,
p_app_attributes=> l_tbl_attributes_info,
p_messages=> l_tbl_messages,
p_mode=> 'SUBMIT',
p_deposit_process=> 'OTL Deposit Process',
p_timecard_id=> l_out_timecard_id,
p_timecard_ovn=> l_out_timecard_ovn
);
COMMIT;
END;

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