To find the concurrent program submission interval

SELECT req.request_id
, decode (prg.user_concurrent_program_name, 'Report Set', 'Report Set:' || req.description, prg.user_concurrent_program_name) AS name
, argument_text as parameters
, req.resubmit_interval
, nvl2 (req.resubmit_interval, 'Periodically', nvl2 (req.release_class_id, 'On specific days', 'Once')) AS schedule_type
, decode (nvl2 (req.resubmit_interval, 'Periodically', nvl2 (req.release_class_id, 'On specific days', 'Once')),
'Periodically', 'Every ' || req.resubmit_interval || ' ' || lower(req.resubmit_interval_unit_code) || ' from ' || lower(req.resubmit_interval_type_code) || ' of previous run',
'Once', 'At :' || to_char (req.requested_start_date, 'DD-MON-RR HH24:MI'), 'Every: ' || crc.class_info) as schedule
, fus.user_name as owner
, to_char(requested_start_date,'DD-MON-YYYY HH24:MI:SS') as next_submission
FROM
apps.fnd_concurrent_programs_tl prg
, apps.fnd_concurrent_requests req
, apps.fnd_user fus
, apps.fnd_conc_release_classes crc
WHERE 1=1
AND prg.application_id = req.program_application_id
AND prg.concurrent_program_id = req.concurrent_program_id
AND req.requested_by = fus.user_id
AND req.phase_code = 'P'
AND req.requested_start_date > sysdate 
AND prg.language = 'US'
AND crc.release_class_id(+) = req.release_class_id
AND crc.application_id(+) = req.release_class_app_id
ORDER BY name

To find out profile option set at which level

select substr(ot.user_profile_option_name,1,35),
substr(o.profile_option_name,1,30),
decode(level_id,10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User') lev,
decode(level_id,10001, null, 10002, a.application_name, 10003, r.responsibility_name, 10004, u.user_name) lev_value,
r.RESPONSIBILITY_ID,
v.profile_option_value opt_value
from fnd_profile_option_values v,
fnd_profile_options o,
fnd_profile_options_tl ot,
fnd_application_tl a,
fnd_responsibility_tl r,
fnd_user u
where LEVEL_VALUE = decode(level_id, 10004, u.user_id, 10003, r.responsibility_id, 10002, a.application_id,10001,0)
and v.profile_option_id = o.profile_option_id
and v.application_id = o.application_id
and a.application_id (+) = v.level_value
and r.responsibility_id (+) = v.level_value
--and o.application_id = 275
and u.user_id (+) = v.level_value
and ot.profile_option_name = o.profile_option_name
and nvl(ot.language,'US') = 'US'
and sysdate between nvl(o.start_date_active, sysdate)
and nvl(o.end_date_active,sysdate)
and v.profile_option_value is not null
and ot.user_profile_option_name like 'PJI_%Debug%' order by 1

Query to find the concurrent program status which ran today

SELECT DISTINCT fcp.user_concurrent_program_name,
                fcp.concurrent_program_name,
                fcr.request_id,
                fcr.request_date,
                flv.meaning status,
                fcr.status_code,
                fcr.completion_text,
                fcr.logfile_name,
                fcr.outfile_name,
                fcr.argument_text
  FROM apps.fnd_concurrent_programs_vl fcp,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_lookup_values          flv
 WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
   AND trunc(fcr.last_update_date) = trunc(SYSDATE)
   AND flv.lookup_code = fcr.status_code
   and fcp.user_concurrent_program_name = 'Program name'
   AND flv.lookup_type = 'CP_STATUS_CODE'
   AND flv.language = 'US'
 ORDER BY fcr.request_date,
          fcr.request_id DESC;
     

When responsibility added to user and it is not showing in the web based application then run the below query to see the responsibility added or not.

Newly added responsibilities not being shown is another symptom of this delay in Java Cache processing

Run the following script to verify that the data is correct:

set linesize 155;
set pagesize 200;
set verify off;

column USER_ID format 9999999999;
column RESP_ID format 9999999999;
column RESP_NAME format a60;
column RESP_APP_ID format 9999999999;
column SEC_GRP_ID format 9999999999;

select fu.user_id, fo.RESPONSIBILITY_ID resp_id, RESPONSIBILITY_NAME resp_name, fo.RESPONSIBILITY_APPLICATION_ID resp_app_id, fo.SECURITY_GROUP_ID sec_grp_id
from fnd_user_resp_groups fo, fnd_responsibility_vl fv, fnd_user fu
where fu.user_id=fo.user_id
and fu.user_name='<affected_user>'
and fo.RESPONSIBILITY_ID=fv.RESPONSIBILITY_ID
and fo.RESPONSIBILITY_APPLICATION_ID=fv.APPLICATION_ID; 

API to delete Template in OTL

begin
hxc_self_service_time_deposit.delete_timecard(37275916, sysdate, 'DELETE','OTL Deposit Process',null);
end;

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;

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

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

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;