APPS.PAY_MX_ARCHIVE_DBI_PKG SQL Statements

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

Line: 18

   SELECT context_id
   INTO   l_assignment_action_context_id
   FROM   ff_contexts
   WHERE  context_name = 'ASSIGNMENT_ACTION_ID';

Line: 23

   SELECT context_id
   INTO   l_gre_context_id
   FROM   ff_contexts
   WHERE  context_name = 'TAX_UNIT_ID';

Line: 41

      SELECT 'Y'
      INTO   l_exists
      FROM   ff_routes
      WHERE  route_name = 'MX_BALANCES_ARCHIVE_ROUTE';

Line: 46

      UPDATE ff_routes
      SET    text       = l_text
      WHERE  route_name = 'MX_BALANCES_ARCHIVE_ROUTE';

Line: 52

      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,
         'MX_BALANCES_ARCHIVE_ROUTE',
         'N',
         'MX Year End Balances Archive Route',
         l_text,
         SYSDATE,
         0,
         0,
         0,
         SYSDATE
      );

Line: 80

      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: 96

      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: 107

      INSERT INTO ff_route_context_usages
      (
         route_id,
         context_id,
         sequence_no
      )
      SELECT ff_routes_s.CURRVAL,
             l_gre_context_id,
             2
      from   dual;

Line: 155

      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  = 'MX'
      AND    ue.business_group_id IS NULL;

Line: 181

         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: 206

/*   SELECT route_id
   INTO   l_number_archive_route_id
   FROM   ff_routes
   WHERE  route_name = 'MX_NUMBER_ARCHIVE_ROUTE';

Line: 211

   SELECT route_id
   INTO   l_date_archive_route_id
   FROM   ff_routes
   WHERE  route_name = 'MX_DATE_ARCHIVE_ROUTE';

Line: 216

   SELECT route_id
   INTO   l_character_archive_route_id
   FROM   ff_routes
   WHERE  route_name = 'MX_CHARACTER_ARCHIVE_ROUTE';

Line: 221

   SELECT route_id
   INTO   l_balances_route
   FROM   ff_routes
   WHERE  route_name = 'MX_BALANCES_ARCHIVE_ROUTE';

Line: 235

   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: 244

      SELECT user_entity_id
      INTO   l_user_entity_seq
      FROM   ff_user_entities
      WHERE  user_entity_name  = p_item_name
      AND    legislation_code  = 'MX'
      AND    business_group_id IS NULL;

Line: 264

         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: 270

         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: 277

         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: 302

      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: 314

      SELECT ff_user_entities_s.NEXTVAL
      INTO   l_user_entity_seq
      FROM   dual;

Line: 318

      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
         'MX',                                   -- legislation_code
         l_route_id,                             -- route_id
         'Y',                                    -- l_ue_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: 354

      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: 377

      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
      );