APPS.PAY_ZA_ARCHIVE_DBITEMS_PKG SQL Statements

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

Line: 20

   select context_id
   into   l_assignment_action_context_id
   from   ff_contexts
   where  context_name = 'ASSIGNMENT_ACTION_ID';

Line: 25

   select context_id
   into   l_source_context_id
   from   ff_contexts
   where  context_name = 'SOURCE_ID';

Line: 30

   select context_id
   into   l_source_text_context_id
   from   ff_contexts
   where  context_name = 'SOURCE_TEXT';

Line: 35

   select context_id
   into   l_source_number_context_id
   from   ff_contexts
   where  context_name = 'SOURCE_NUMBER';

Line: 50

      select 'y'
      into   l_exists
      from   ff_routes
      where  route_name = 'ZA_NUMBER_ARCHIVE_ROUTE';

Line: 55

      update ff_routes
      set    text       = l_text
      where  route_name = 'ZA_NUMBER_ARCHIVE_ROUTE';

Line: 61

      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.nextval,
         'ZA_NUMBER_ARCHIVE_ROUTE',
         'N',
         'Generic number archive route',
         l_text,
         sysdate,
         0,
         0,
         0,
         sysdate
      );

Line: 89

      insert into ff_route_parameters
      (
         route_parameter_id,
         route_id,
         data_type,
         parameter_name,
         sequence_no
      )
      select ff_route_parameters_s.nextval,
             ff_routes_s.currval,
             'N',
             'User Entity ID',
             1
      from   dual;

Line: 105

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

Line: 128

      select 'y'
      into   l_exists
      from   ff_routes
      where  route_name = 'ZA_DATE_ARCHIVE_ROUTE';

Line: 133

      update ff_routes
      set    text       = l_text
      where  route_name = 'ZA_DATE_ARCHIVE_ROUTE';

Line: 139

      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.nextval,
         'ZA_DATE_ARCHIVE_ROUTE',
         'N',
         'Generic date archive route',
         l_text,
         sysdate,
         0,
         0,
         0,
         sysdate
      );

Line: 167

      insert into ff_route_parameters
      (
         route_parameter_id,
         route_id,
         data_type,
         parameter_name,
         sequence_no
      )
      select ff_route_parameters_s.nextval,
             ff_routes_s.currval,
             'N',
             'User Entity ID',
             1
      from   dual;

Line: 183

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

Line: 206

      select 'y'
      into   l_exists
      from   ff_routes
      where  route_name = 'ZA_CHARACTER_ARCHIVE_ROUTE';

Line: 211

      update ff_routes
      set    text       = l_text
      where  route_name = 'ZA_CHARACTER_ARCHIVE_ROUTE';

Line: 217

      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.nextval,
         'ZA_CHARACTER_ARCHIVE_ROUTE',
         'N',
         'Generic character archive route',
         l_text,
         sysdate,
         0,
         0,
         0,
         sysdate
      );

Line: 245

      insert into ff_route_parameters
      (
         route_parameter_id,
         route_id,
         data_type,
         parameter_name,
         sequence_no
      )
      select ff_route_parameters_s.nextval,
             ff_routes_s.currval,
             'N',
             'User Entity ID',
             1
      from   dual;

Line: 261

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

Line: 290

      select 'y'
      into   l_exists
      from   ff_routes
      where  route_name = 'ZA_IRP5_DEDUCTIONS_ARCHIVE_ROUTE';

Line: 295

      update ff_routes
      set    text       = l_text
      where  route_name = 'ZA_IRP5_DEDUCTIONS_ARCHIVE_ROUTE';

Line: 301

      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.nextval,
         'ZA_IRP5_DEDUCTIONS_ARCHIVE_ROUTE',
         'N',
         'IRP5 Deductions archive route',
         l_text,
         sysdate,
         0,
         0,
         0,
         sysdate
      );

Line: 329

      insert into ff_route_parameters
      (
         route_parameter_id,
         route_id,
         data_type,
         parameter_name,
         sequence_no
      )
      select ff_route_parameters_s.nextval,
             ff_routes_s.currval,
             'N',
             'User Entity ID',
             1
      from   dual;

Line: 345

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

Line: 356

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

Line: 367

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

Line: 393

      select 'y'
      into   l_exists
      from   ff_routes
      where  route_name = 'ZA_IRP5_4001_4003_ARCHIVE_ROUTE';

Line: 398

      update ff_routes
      set    text       = l_text
      where  route_name = 'ZA_IRP5_4001_4003_ARCHIVE_ROUTE';

Line: 404

      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.nextval,
         'ZA_IRP5_4001_4003_ARCHIVE_ROUTE',
         'N',
         'IRP5 4001 to 4003 archive route',
         l_text,
         sysdate,
         0,
         0,
         0,
         sysdate
      );

Line: 432

      insert into ff_route_parameters
      (
         route_parameter_id,
         route_id,
         data_type,
         parameter_name,
         sequence_no
      )
      select ff_route_parameters_s.nextval,
             ff_routes_s.currval,
             'N',
             'User Entity ID',
             1
      from   dual;

Line: 448

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

Line: 459

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

Line: 488

      select 'y'
      into   l_exists
      from   ff_routes
      where  route_name = 'ZA_LMPSM_ARCHIVE_ROUTE';

Line: 493

      update ff_routes
      set    text       = l_text
      where  route_name = 'ZA_LMPSM_ARCHIVE_ROUTE';

Line: 499

      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.nextval,
         'ZA_LMPSM_ARCHIVE_ROUTE',
         'N',
         'Generic number archive route with SOURCE_TEXT context',
         l_text,
         sysdate,
         0,
         0,
         0,
         sysdate
      );

Line: 527

      insert into ff_route_parameters
      (
         route_parameter_id,
         route_id,
         data_type,
         parameter_name,
         sequence_no
      )
      select ff_route_parameters_s.nextval,
             ff_routes_s.currval,
             'N',
             'User Entity ID',
             1
      from   dual;

Line: 543

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

Line: 554

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

Line: 583

      select 'y'
      into   l_exists
      from   ff_routes
      where  route_name = 'ZA_CLRNO_ARCHIVE_ROUTE';

Line: 588

      update ff_routes
      set    text       = l_text
      where  route_name = 'ZA_CLRNO_ARCHIVE_ROUTE';

Line: 594

      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.nextval,
         'ZA_CLRNO_ARCHIVE_ROUTE',
         'N',
         'Generic number archive route with SOURCE_NUMBER context',
         l_text,
         sysdate,
         0,
         0,
         0,
         sysdate
      );

Line: 622

      insert into ff_route_parameters
      (
         route_parameter_id,
         route_id,
         data_type,
         parameter_name,
         sequence_no
      )
      select ff_route_parameters_s.nextval,
             ff_routes_s.currval,
             'N',
             'User Entity ID',
             1
      from   dual;

Line: 638

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

Line: 649

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

Line: 708

   SELECT formula_id
   BULK COLLECT INTO l_fast_formula_id_tbl
   FROM  ff_fdi_usages_f
   WHERE item_name = p_item_name
   AND   usage = 'D';

Line: 716

      DELETE
      FROM  ff_compiled_info_f
      WHERE formula_id = l_fast_formula_id_tbl(i);

Line: 721

   delete from ff_fdi_usages_f
   where item_name = p_item_name
   and   usage = 'D';

Line: 726

      select ue.notfound_allowed_flag,
             ue.creator_type,
             ue.entity_description,
             ue.route_id,
             dbi.null_allowed_flag,
             dbi.description ,
             dbi.data_type,
             dbi.user_name
      into   l_ue_notfound_allowed_flag,
             l_ue_creator_type,
             l_ue_entity_description,
             l_live_route_id,
             l_dbi_null_allowed_flag,
             l_dbi_description,
             l_dbi_data_type,
             l_dbi_user_name
      from   ff_database_items dbi,
             ff_user_entities  ue
      where  dbi.user_name        = substr(p_item_name, 3, length(p_item_name) - 2)
      and    dbi.user_entity_id   = ue.user_entity_id
      and    ue.legislation_code  = 'ZA'
      and    ue.business_group_id is null;

Line: 752

         select ue.notfound_allowed_flag,
                ue.creator_type,
                ue.entity_description,
                ue.route_id,
                dbi.null_allowed_flag,
                dbi.description,
                dbi.data_type,
                dbi.user_name
         into   l_ue_notfound_allowed_flag,
                l_ue_creator_type,
                l_ue_entity_description,
                l_live_route_id,
                l_dbi_null_allowed_flag,
                l_dbi_description,
                l_dbi_data_type,
                l_dbi_user_name
         from   ff_database_items dbi,
                ff_user_entities  ue
         where  dbi.user_name        = substr(p_item_name, 3, length(p_item_name) - 2)
         and    dbi.user_entity_id   = ue.user_entity_id
         and    ue.legislation_code  is null
         and    ue.business_group_id is null;

Line: 777

   select route_id
   into   l_number_archive_route_id
   from   ff_routes
   where  route_name = 'ZA_NUMBER_ARCHIVE_ROUTE';

Line: 782

   select route_id
   into   l_date_archive_route_id
   from   ff_routes
   where  route_name = 'ZA_DATE_ARCHIVE_ROUTE';

Line: 787

   select route_id
   into   l_character_archive_route_id
   from   ff_routes
   where  route_name = 'ZA_CHARACTER_ARCHIVE_ROUTE';

Line: 792

   select route_id
   into   l_irp5_deductions_route
   from   ff_routes
   where  route_name = 'ZA_IRP5_DEDUCTIONS_ARCHIVE_ROUTE';

Line: 797

   select route_id
   into   l_4001_4003_archive_route_id
   from   ff_routes
   where  route_name = 'ZA_IRP5_4001_4003_ARCHIVE_ROUTE';

Line: 802

   select route_id
   into   l_lmpsm_archive_route_id
   from   ff_routes
   where  route_name = 'ZA_LMPSM_ARCHIVE_ROUTE';

Line: 807

   select route_id
   into   l_clrno_archive_route_id
   from   ff_routes
   where  route_name = 'ZA_CLRNO_ARCHIVE_ROUTE';

Line: 861

   select route_parameter_id
   into   l_route_parameter_id
   from   ff_route_parameters
   where  parameter_name = 'User Entity ID'
   and    route_id       = l_route_id;

Line: 870

      select user_entity_id, route_id
      into   l_user_entity_seq, l_old_route_id
      from   ff_user_entities
      where  user_entity_name  = p_item_name
      and    legislation_code  = 'ZA'
      and    business_group_id is null;

Line: 878

          DELETE FROM ff_route_parameter_values
            where user_entity_id     = l_user_entity_seq
            AND route_parameter_id <> l_route_parameter_id;

Line: 884

      update ff_user_entities
      set    route_id              = l_route_id,
             notfound_allowed_flag = 'Y',   -- l_ue_notfound_allowed_flag,
             entity_description    = substr('Archive of ' || l_ue_entity_description, 1, 240)
      where  user_entity_name      = p_item_name
      and    legislation_code      = 'ZA'
      and    business_group_id     is null;

Line: 894

         select route_parameter_id
         into   l_dummy_id
         from   ff_route_parameter_values
         where  route_parameter_id = l_route_parameter_id
         and    user_entity_id     = l_user_entity_seq;

Line: 900

         update ff_route_parameter_values
         set    value              = l_user_entity_seq
         where  route_parameter_id = l_route_parameter_id
         and    user_entity_id     = l_user_entity_seq;

Line: 907

         insert into ff_route_parameter_values
         (
            route_parameter_id,
            user_entity_id,
            value,
            last_update_date,
            last_updated_by,
            last_update_login,
            created_by,
            creation_date
         )
         values
         (
            l_route_parameter_id,
            l_user_entity_seq,
            l_user_entity_seq,
            sysdate,
            0,
            0,
            0,
            sysdate
         );

Line: 932

      update ff_database_items
      set    user_entity_id    = l_user_entity_seq,
             data_type         = l_dbi_data_type,
             definition_text   = l_definition_text,
             null_allowed_flag = 'Y',   -- l_dbi_null_allowed_flag,
             description       = substr('Archive of item ' || l_dbi_description, 1, 240)
      where  user_name         = p_item_name;

Line: 943

      select ff_user_entities_s.nextval
      into   l_user_entity_seq
      from   dual;

Line: 947

      insert into ff_user_entities
      (
         user_entity_id,
         business_group_id,
         legislation_code,
         route_id,
         notfound_allowed_flag,
         user_entity_name,
         creator_id,
         creator_type,
         entity_description,
         last_update_date,
         last_updated_by,
         last_update_login,
         created_by,
         creation_date
      )
      values
      (
         l_user_entity_seq,                                          -- user_entity_id
         null,                                                       -- business_group_id
         'ZA',                                                       -- legislation_code
         l_route_id,                                                 -- route_id
         'Y',   -- l_ue_notfound_allowed_flag,                       -- notfound_allowed_flag
         p_item_name,                                                -- user_entity_name
         0,                                                          -- creator_id
         'X',                                                        -- archive extract creator_type
         substr('Archive of ' || l_ue_entity_description, 1, 240),   -- entity_description
         sysdate,                                                    -- last_update_date
         0,                                                          -- last_updated_by
         0,                                                          -- last_update_login
         0,                                                          -- created_by
         sysdate                                                     -- creation_date
      );

Line: 982

      insert into ff_route_parameter_values
      (
         route_parameter_id,
         user_entity_id,
         value,
         last_update_date,
         last_updated_by,
         last_update_login,
         created_by,
         creation_date
      )
      values
      (
         l_route_parameter_id,
         l_user_entity_seq,
         l_user_entity_seq,
         sysdate,
         0,
         0,
         0,
         sysdate
      );

Line: 1005

      insert into ff_database_items
      (
         user_name,
         user_entity_id,
         data_type,
         definition_text,
         null_allowed_flag,
         description,
         last_update_date,
         last_updated_by,
         last_update_login,
         created_by,
         creation_date
      )
      values
      (
         p_item_name,
         l_user_entity_seq,
         l_dbi_data_type,
         l_definition_text,
         'Y',   -- l_dbi_null_allowed_flag,
         substr('Archive of item ' || l_dbi_description, 1, 240),
         sysdate,
         0,
         0,
         0,
         sysdate
      );