SQL to Find the Rules that SQL contain a Given SQL Object

select TL_RULE_ID
 from PS_TL_RULE_STEPS
where SQL_ID = 'XXX'

Last User Exit To Brooklyn

T&L rules can include PeopleCode steps by making them User Exit rules. To create a user exit rule, do the following.

1. Open the TL_TA_RULES application engine and create a new section for your rule. For this section you must check the Access Pulblic check box.
2. Add your rule steps to this section.
3. Navigate to Home > Setup HRMS > Product Realted > Time and Labor > Validation Criteria > AE Section Definition and define your newly created section. The program name is TL_TA_RULES and theprocess type should be Rule (User Exit).
4. Navigate to Home > Setup HRMS >System Administration > Utilities > Build Time And Labor Rules > Rules and create a rule definition, checking the User Exit box and entering the name of the AE section you created earlier.
5. Add the new rule to any desired rule programs.
6. In the DMS script you use to migrate your rule you will need to include the table PS_TL_AE_SECTION which contains user exit section details. The migration project will need to contain your AE Section and any associated steps.
   

One Step Beyond

One of Time Admin's subprograms, TL_TA000800, calls TL_TA_RULES to apply rules in the order specified on the rule program for each batch. The sections in TL_TA_RULES correspond to each of the active time and labor rules.

When a rule is built the application engine definition tables underlying TL_TA_RULES are updated to reflect any changes made to the rule.

Unfortunately, for a particular rule, the section name and step of the app engine don't correspond to the rule name and step number of the rule itself. Which can make it a little tricky when debugging an abend. The following SQL solves this problem by giving you the rule ID and step for the app. engine section and step that the abend log will give you.

select *
  from ( select TL_RULE_ID,
                TL_RULE_STEP,
                rownum as AE_STEP,
                SQL_ID,
                DESCR100   
           from PS_TL_RULE_STEPS
          where TL_RULE_ID = (select TL_RULE_ID
                                from PS_TL_RULE_DEFN
                               where AE_SECTION = :AE_SECTION)
       )
 where AE_STEP = :STEP

SQL to find Rules and associated Application Engine Sections

Get the AE Section name from the Rule ID.

select AE_SECTION
from PS_TL_RULE_DEFN
where TL_RULE_ID = 'XXX'

Get the Rule ID from the AE Section name.

select TL_RULE_ID
from PS_TL_RULE_DEFN
where AE_SECTION = 'XXX'