How to check the Approval Rule status using BI Report?

0 Shares

In this article, we will try to get the approval rule status (Active/Bypassed) using SQL Query.

SQL Query to pull Approval Rule status information:

SELECT 
      hacv.composite_name
      ,hacv.name
      ,hacv.description
      ,HRPB.TASK_FILE_NAME TaskName
      ,HRPB.TXN_SUB_MODULE_IDENTIFIER SubTask
      ,hrpb.family
      ,hrpb.category_code
      ,hrpb.subcategory_code
      ,hrpb.active
      ,hrpb.rule_configure_mode
      ,hrpb.bypass_supported
      ,DECODE(HAAO.APPROVAL_DISABLED,'true','Bypassed','false','Enabled') "Enabled Status"
      ,NVL((select full_name from per_person_names_f ppnf, per_users PU
	     where ppnf.name_type = 'GLOBAL'
		   and sysdate between ppnf.effective_start_date and ppnf.effective_end_date
		   and ppnf.person_id = pu.person_id 
		   and pu.username = HAAO.last_updated_by),HAAO.last_updated_by) last_updated_by
      ,HAAO.last_update_date
      ,fat.module_name
      ,haao.approval_disabled
	  ,haao.alert_enabled
	  ,hr_general.decode_lookup ('ORA_HRC_TXN_CON_TXN_DATE_RANGE', haao.archive_period) Archive_Period
	  ,hr_general.decode_lookup ('ORA_HRC_TXN_PURGE_PERIOD_TYPES', haao.Purge_Period) Purge_Period
  FROM HRC_ARM_PROCESS_B HRPB
      ,HRC_ARM_APPROVAL_OPTIONS HAAO
      ,HRC_ARM_COMPOSITES_VL hacv
      ,Fnd_appl_taxonomy_vl fat
 WHERE HRPB.PROCESS_ID=HAAO.PROCESS_ID
 and hacv.composite_id = hrpb.composite_id
 and fat.module_id = hrpb.module_id
 and hrpb.family = 'HCM'
  --and hacv.composite_name = 'HcmEmploymentPwkComposite'
 order by hacv.composite_name

Now, Let’s check the sample for Add Pending Worker