Education & Training
Services & Support
to create and rate content, and to follow, bookmark, and share content with other members.
Looking for Stored Procedure/Job for notifying Delinquent Timesheet Approv
Question asked by
on Mar 25, 2008
on Apr 30, 2008 by Superman
Show 0 Likes
We are looking for a job that we can schedule that would notify the timesheet approvers that they are delinquent. Does anyone have one they would like to share?[left] [left] Thanks -[left] [left] Ruthann[left]
No one else has this question
Mark as assumed answered
This content has been marked as final.
Show 2 comments
(Required, will not be published)
Apr 17, 2008 8:31 AM
[left] This is one that I wrote a while back. You will need to create a table (mine is Z_TEMP_SUBMIT_TIMESHEET ). You will then need to make this stored procedure as a job that Clarity can kick off. Then make a process to run at the designated time to run this procedure, followed by a gel script to email everyone on this table. [left] [left] CREATE OR REPLACE PROCEDURE niku . sh_unsubmitted_timesheets ( [left] p_job_run_id IN NUMBER , p_job_user_id IN NUMBER ) IS CURSOR new_record IS SELECT temp1 . resl staff , temp1 . dte dat , temp1 . email eml , CASE temp2 . stat WHEN 2 THEN 'has been returned by your manager' ELSE 'has not been submitted' END status FROM srm_resources man , (SELECT CONCAT ( res . resource_id , tp . prid ) ID, res . resource_id resid , tp . prstart dte , res2 . first_name resl , res . obs1_unit_id obs , res2 . email email , res2 . manager_id manid FROM prtimeperiod tp , nbi_resource_current_facts res , srm_resources res2 , prj_resources res3 WHERE res . is_active = 1 AND res2 .ID = res3 . prid AND res3 . prisopen = 1 AND res . is_role = 0 AND res . last_name NOT LIKE 'Admin%' AND res . resource_id > 5000000 AND tp . prstart BETWEEN SYSDATE - 10 AND SYSDATE - 3 AND res2 .ID = res . resource_id AND tp . prstart > res2 . date_of_hire AND tp . prstart NVL ( res2 . date_of_termination , SYSDATE)) temp1 , (SELECT CONCAT ( prresourceid , prtimeperiodid ) ID, prstatus stat FROM prtimesheet ) temp2 WHERE temp1 .ID = temp2 .ID(+) AND ( temp2 . stat = 0 OR temp2 . stat = 2 OR temp2 . stat IS NULL) AND man . user_id = temp1 . manid ; cnt NUMBER := 1 ; p_staff srm_resources . first_name% TYPE; p_date prtimeperiod . prstart% TYPE; p_mail srm_resources . email% TYPE; p_status globalpkg .STRING; BEGIN DELETE FROM z_temp_submit_timesheet ; OPEN new_record ; LOOP FETCH new_record INTO p_staff , p_date , p_mail , p_status ; EXIT WHEN new_record% NOTFOUND; INSERT INTO z_temp_submit_timesheet (ID, staff_first , email , timesheet , status ) VALUES ( cnt , p_staff , p_mail , p_date , p_status ); cnt := cnt + 1 ; END LOOP; CLOSE new_record ; END sh_unsubmitted_timesheets ; / [left]
Show 0 Likes
Apr 30, 2008 11:30 AM
I am trying to understand the GEL scripting. I have the manuals, are there any other places i can find more info on the subject? Besides this
. I am trying to understand it from a Clarity standpoint. Does anyone have some examples i could look at? Thanks Message Edited by Superman on 04-30-2008 11:28 AM Message Edited by Superman on 04-30-2008 11:29 AM
Message Edited by Superman on 04-30-2008 11:30 AM
Show 0 Likes
Retrieving data ...
[WEBCAST] CA IDMS Database Maintenance: Recover the Database (2 of 2) – November 16, 2018 @ 11 a.m. ET
Benefit plan replacement by XOG
WS-Security and Live Invocation
Agile Central Database Connector (Postgres, MS SQL Server) 1.1.0
How do I query for a list of User Stories that have attachments?