Thursday, November 29, 2018

Query to find Concurrent Requests Ran count by Template/Layout Name for Each Month

SELECT user_concurrent_program_name program_name,
         xtt.template_name,
         hou.name operating_unit,
         TO_CHAR (actual_start_date, 'MON-YYYY') program_ran_month,
         --fu.user_name,
         --frt.responsibility_name,
         COUNT (1) COUNT
    FROM apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_concurrent_programs fcp,
         apps.fnd_concurrent_requests fcr,
         apps.fnd_user fu,
         apps.fnd_responsibility_tl frt,
         apps.hr_operating_units hou,
         apps.fnd_conc_pp_actions fcpa,
         apps.xdo_templates_b xtb,
         apps.xdo_templates_tl xtt
   WHERE     1 = 1
         AND fcp.concurrent_Program_id = fcpt.concurrent_program_id
         AND fcp.concurrent_program_id = fcr.concurrent_program_id(+)
         AND fu.user_id = fcr.requested_by
         AND frt.responsibility_id = fcr.RESPONSIBILITY_ID
         AND fcr.org_id = hou.organization_id
         AND fcr.request_id = fcpa.concurrent_request_id
         AND xtb.template_code = fcpa.ARGUMENT2
         AND xtb.template_code = xtt.template_code
         AND user_concurrent_program_name LIKE 'XX Conc Program Name%'
GROUP BY user_concurrent_program_name,
         hou.name,
         TO_CHAR (actual_start_date, 'MON-YYYY'),
         xtt.template_name
ORDER BY TO_DATE (TO_CHAR (actual_start_date, 'MON-YYYY'), 'MON-YYYY') DESC

Unix commands to get the count of files

 ls -l *.rdf | wc -l