Thursday, June 25, 2015

Query To calculate request time



SELECT   pt.user_concurrent_program_name user_concurrent_program_name,
         DECODE (P.concurrent_program_name,'ALECDC', P.concurrent_program_name || '[' || f.description|| ']',P.concurrent_program_name) concurrent_program_name,
         f.request_id,A.requestor,f.argument_text,
         f.actual_start_date actual_start_date,
         f.actual_completion_date actual_completion_date,
            FLOOR (((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)/ 3600)|| ' HOURS '
         || FLOOR ((((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)
                      -   FLOOR ((( f.actual_completion_date - f.actual_start_date)* 24* 60* 60)/ 3600)* 3600)/ 60)|| ' MINUTES '
         || ROUND ((((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)
        -   FLOOR (((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)/ 3600)* 3600
        - (FLOOR ((((f.actual_completion_date - f.actual_start_date) * 24* 60* 60)
        - FLOOR (((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)/ 3600)* 3600)/ 60)* 60)))
         || ' SECS ' time_difference,
         DECODE (f.phase_code,'R', 'Running','C', 'Complete',f.phase_code) phase,
         f.status_code
    FROM apps.fnd_concurrent_programs P,
         apps.fnd_conc_req_summary_v a,
         apps.fnd_concurrent_programs_tl pt,
         apps.fnd_concurrent_requests f
   WHERE a.request_id = f.request_id
     AND f.concurrent_program_id = P.concurrent_program_id
     AND f.program_application_id = P.application_id
     AND f.concurrent_program_id = pt.concurrent_program_id
     AND f.program_application_id = pt.application_id
     AND pt.LANGUAGE = USERENV ('Lang')
     AND f.actual_start_date IS NOT NULL
--     AND pt.user_concurrent_program_name = '&Conc_prog_name'
     AND f.Request_Id  = :p_request_id
ORDER BY f.request_id DESC, f.actual_completion_date - f.actual_start_date DESC;      

No comments:

Post a Comment