APPS.PAY_US_CONTR_DBI SQL Statements

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

Line: 31

			 for extra parameter to hrdyndbi.insert_user_entity.
			 New Version: 110.1
rfine      24-NOV-1994   Suppressed index on business_group_id
rfine      05-OCT-1994   Prepended package name with 'PAY_' as per naming
                         standards.
mwcallag   09-DEC-1993   G334 : Benefit DB item names changed to be
                         _BEN_EE_CONTR_VALUE and
                         _BEN_ER_CONTR_VALUE.  Legislation code
                         derived from from per_business_groups if the
                         Legislation code is null on the input value table.
mwcallag   30-NOV-1993   G259 : Routine modified to use externalised database
                         item creation procedures in package hrdyndbi.
JRhodes    05-Nov-1993   Added "and nvl(BC.contributions_used,'Y') = 'Y'"
                         to cater for Payroll Deductions generation of
			 DB items
mwcallag   02-NOV-1993   exception of no_data_found added to procedure
                         create_contr_items.
JRhodes    20-OCT-1993   Created.
SDoshi     22-MAR-1999   Flexible Dates Conversion
irgonzal   24-SEP-2001   Bug fix 2004226. Enabled index  on per_business_groups
                         table by removing "+ 0" from the WHERE clause.
                         Modified create_contr_items procedure.
emunisek   17-NOV-2011   For Bug#13375560,replaced LONG datatype by ff_routes.text%TYPE

*/
--
PROCEDURE create_usdbi_startup is
l_text                       ff_routes.text%TYPE;

Line: 70

procedure insert_route_parameters
(
    p_parameter_name  in  varchar2,
    p_data_type       in  varchar2,
    p_sequence_no     in  number
) is
begin
    hr_utility.set_location('pay_us_contr_dbi.insert_route_parameters', 1);

Line: 78

    insert into ff_route_parameters
          (route_id,
           sequence_no,
           parameter_name,
           data_type,
           route_parameter_id)
   select  ff_routes_s.currval,
           p_sequence_no,
           p_parameter_name,
           p_data_type,
           ff_route_parameters_s.nextval
   from    dual;

Line: 90

end insert_route_parameters;

Line: 94

procedure insert_route_context_usages
(
    p_context_id    in  number,
    p_sequence_no   in  number
) is
begin
    hr_utility.set_location('pay_us_contr_dbi.insert_route_context_usages', 1);

Line: 101

    insert into ff_route_context_usages
           (route_id,
            context_id,
            sequence_no)
    select  ff_routes_s.currval,
            p_context_id,
            p_sequence_no
    from    dual;

Line: 109

end insert_route_context_usages;

Line: 118

    select context_id
    into   l_assign_id_context_id
    from   ff_contexts
    where  context_name = 'ASSIGNMENT_ID';

Line: 124

    select context_id
    into   l_date_earned_context_id
    from   ff_contexts
    where  context_name = 'DATE_EARNED';

Line: 176

    select ff_routes_s.nextval
    into   l_temp
    from   dual;

Line: 183

    insert into ff_routes
           (route_id,
            route_name,
            user_defined_flag,
            description,
            text,
            last_update_date,
            last_updated_by,
            last_update_login,
            created_by,
            creation_date)
    values (ff_routes_s.currval,
            'US_CONTRIBUTION_VALUES',
            'N',
            'route for contribution values using benefit contributions',
            l_text,
            sysdate,
            0,
            0,
            0,
            sysdate);

Line: 205

    insert_route_parameters ('Element Type ID', 'N', 1);

Line: 206

    insert_route_parameters ('Input value ID', 'N', 2);

Line: 207

    insert_route_context_usages (l_date_earned_context_id, 1);

Line: 208

    insert_route_context_usages (l_assign_id_context_id,   2);

Line: 247

   SELECT route_id
   INTO   l_route_id
   FROM   ff_routes
   WHERE  route_name         = p_route_name;

Line: 275

l_record_inserted BOOLEAN;

Line: 281

   hrdyndbi.insert_user_entity (p_route_name,
                                p_entity_name,
                                p_entity_description,
                                p_not_found_flag,
                                'I',
                                p_creator_id,
                                p_business_group_id,
                                p_legislation_code,
                                p_created_by,
                                p_last_login,
				l_record_inserted);

Line: 295

   IF l_record_inserted THEN
       hrdyndbi.insert_parameter_value (p_element_type_id, 1);

Line: 297

       hrdyndbi.insert_parameter_value (p_input_value_id,  2);

Line: 301

       hrdyndbi.insert_database_item (p_entity_name,
                                  'VALUE',
                                  p_data_type,
                                  p_text,
                                  'Y',
                                  p_description);

Line: 319

        select ET.element_type_id,
	       replace (ltrim (rtrim (upper (ET.element_name))), ' ', '_'),
	       replace (ltrim (rtrim (upper (IV.name))), ' ', '_'),
	       IV.business_group_id,
               nvl (ltrim(rtrim(IV.legislation_code)),
                    ltrim(rtrim(BUSGP.legislation_code))),
               IV.created_by,
               IV.last_update_login
        into   l_element_type_id,
	       l_element_name,
               l_input_value_name,
	       l_business_group_id,
               l_legislation_code,
               l_created_by,
               l_last_login
        from   pay_input_values_f       IV,
               pay_element_types_f      ET,
               per_business_groups      BUSGP,
	       ben_benefit_classifications BC
        where  IV.input_value_id      = p_input_value_id
        and    p_effective_date between IV.effective_start_date
                                and     IV.effective_end_date
        and    IV.element_type_id     = ET.element_type_id
        and    p_effective_date between ET.effective_start_date
                                and     ET.effective_end_date
        and    ET.benefit_classification_id = BC.benefit_classification_id(+)
	and    nvl(BC.contributions_used,'Y')     = 'Y'
        and    BUSGP.business_group_id (+)  = IV.business_group_id + 0; --#2004226