APPS.PAY_US_BALANCE_FEEDS_HOOK SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 23

    Description : This package is called from the AFTER INSERT/UPDATE
                  User Hooks. The following are the functionalities present
                  in User Hook

                  1. Puts time_definition_type as 'G' in pay_element_types_f
		     whenever that element feeds a balance for which defined
		     balance exists with dimensions:
		     _ASG_GRE_TD_RUN
		     _ASG_GRE_TD_BD_RUN

    Change List
    -----------
    Name           Date          Version Bug      Text
    -------------- -----------   ------- -------  -----------------------------
    rdhingra       06-MAR-2006   115.0   5073515  Created

  *****************************************************************************/

-----------------------------INSERT SECTION STARTS HERE--------------------------
/******************************************************************************
   Name        : INSERT_TIMEDEF_TYPE
   Scope       : LOCAL
   Description : This procedure is called by AFTER INSERT Row Level handler
                 User Hook.
******************************************************************************/
PROCEDURE INSERT_TIMEDEF_TYPE(
                    p_effective_date    IN DATE
                   ,p_balance_type_id   IN NUMBER
                   ,p_input_value_id	IN NUMBER
                   ,p_business_group_id IN NUMBER
                   ,p_legislation_code  IN VARCHAR2
                   ) IS

-- Check if defined balance exist for given dimension names
CURSOR get_defined_bal_status( cp_balance_type_id   NUMBER
                              ,cp_business_group_id NUMBER
			     ) IS
   select 'Y'
    from dual
   where exists
               ( select 1
                   from pay_defined_balances pdb
                  where pdb.balance_type_id = cp_balance_type_id
                    and ((pdb.business_group_id IS NULL AND pdb.legislation_code = 'US') OR              -- For Seeded
                         (pdb.business_group_id = cp_business_group_id AND pdb.legislation_code IS NULL) -- For Custom
                        )
                    and exists
                             (
                              select 1
                                from pay_balance_dimensions pbd
                               where pbd.balance_dimension_id = pdb.balance_dimension_id
                                 /*Dimension names are used in place of database_item_suffix to utilize
                                   PAY_BALANCE_DIMENSIONS_UK2 index*/
				 and pbd.dimension_name in ('Assignment Within GRE Time Definition Run'
                                                           ,'Assignment Within GRE Time Definition BD Run')
                                 and pbd.legislation_code = 'US' -- Seeded Dimensions
                                 and pbd.business_group_id IS NULL
                             )
               );

Line: 89

   select element_type_id
     from pay_input_values_f
    where input_value_id = cp_input_value_id
      and ((business_group_id IS NULL AND legislation_code = 'US') OR              -- For Seeded
           (business_group_id = cp_business_group_id AND legislation_code IS NULL) -- For Custom
          )
      and cp_effective_date between effective_start_date and effective_end_date;

Line: 102

   hr_utility.trace('Entering PAY_US_BALANCE_FEEDS_HOOK.INSERT_TIMEDEF_TYPE');

Line: 116

      /*Get the element_type_id to update*/
      OPEN get_element_type_id(p_input_value_id
                              ,p_business_group_id
			      ,p_effective_date
                              );

Line: 125

         /*Update the time_definition_type to G for the element found above*/
	 UPDATE pay_element_types_f
            SET time_definition_type = 'G'
          WHERE element_type_id = ln_element_type_id
            AND ((business_group_id IS NULL AND legislation_code = 'US') OR               -- For Seeded
                 (business_group_id = p_business_group_id AND legislation_code IS NULL)   -- For Custom
                )
	    AND time_definition_type IS NULL;

Line: 140

   hr_utility.trace('Leaving PAY_US_BALANCE_FEEDS_HOOK.INSERT_TIMEDEF_TYPE');

Line: 141

END INSERT_TIMEDEF_TYPE;

Line: 145

   Name        : INSERT_USER_HOOK
   Scope       : GLOBAL
   Description : This procedure is called by AFTER INSERT Row Level handler
                 User Hook.
******************************************************************************/
PROCEDURE INSERT_USER_HOOK(
   p_effective_date     IN  DATE
  ,p_balance_type_id	IN  NUMBER
  ,p_input_value_id	IN  NUMBER
  ,p_scale		IN  NUMBER    DEFAULT NULL
  ,p_business_group_id  IN  NUMBER    DEFAULT NULL
  ,p_legislation_code	IN  VARCHAR2  DEFAULT NULL
  ) IS
BEGIN

   hr_utility.trace('Entering PAY_US_BALANCE_FEEDS_HOOK.INSERT_USER_HOOK');

Line: 167

   INSERT_TIMEDEF_TYPE(
                       p_effective_date    => p_effective_date
                      ,p_balance_type_id   => p_balance_type_id
                      ,p_input_value_id	   => p_input_value_id
                      ,p_business_group_id => p_business_group_id
                      ,p_legislation_code  => p_legislation_code
                      );

Line: 176

   hr_utility.trace('Leaving PAY_US_BALANCE_FEEDS_HOOK.INSERT_USER_HOOK');

Line: 177

END INSERT_USER_HOOK;