Example SQL Script to Populate Mapping Table (Oracle HRMS)

Example SQL Script to Populate Mapping Table

This example script configures links between Oracle benefit plans and programs and Authoria HR pages. See Configuring Mappings Between Oracle and Authoria HR for information on how to configure this script for your own use.

The script assumes that there are no mappings stored in the table to begin with. The mappings are business group-specific. Each plan/program combination results in two mappings, one for open enrollment and the other for non-open enrollment. The open enrollment mapping is typically to a common open enrollment page.

The script shows the following four mappings:

A plan named 'ACME Dental NonFlex' exists in the program 'ACME Program NonFlex'. This is mapped to the Authoria page 'AnnEnroll' during open enrollment, and at other times to the page 'Dental'. This results in the procedure call for non-open enrollment:
```
create_page_map('ACME Dental NonFlex','ACME Program NonFlex',
                'N','Dental',l_business_group_id);
```

and for open enrollment:

create_page_map('ACME Dental NonFlex','ACME Program NonFlex',
               'Y','AnnEnroll',l_business_group_id);

A plan 'Saving Plan', that is not in a program, is mapped to the Authoria page 'AnnEnroll' during open enrollment, and at other times to the page 'Sav'. This results in the procedure call for non-open enrollment:
```
create_page_map('Saving Plan',NULL,
                'N','Sav',l_business_group_id);
```

and for open enrollment:

create_page_map('Saving Plan',NULL,
               'Y','AnnEnroll',l_business_group_id);

Example Script

REM +===============================================+
REM SQL Script File Name : create_page_map.sql
REM Description: Seed Authoria page mappings via APIs
REM +===============================================+
declare
l_business_group_id number;
procedure create_page_map
 (p_plan_name                     in     varchar2
 ,p_program_name                  in     varchar2
  ,p_open_enrollment_flag          in     varchar2
 ,p_target_page                   in     varchar2
  ,p_business_group_id             in     number
 ) is
--
l_authoria_mapping_id number;
l_object_version_number number;
l_plip_id number := null;
l_pl_id number;
l_pgm_id number := null;
-- value -924926578 represents a number unlikely to
-- exist in the database
cursor csr_exists is
  select authoria_mapping_id,
         object_version_number
  from  hr_authoria_mappings
  where pl_id            = l_pl_id
    and nvl(plip_id,-924926578) = nvl(l_plip_id,-924926578)
    and open_enrollment_flag = p_open_enrollment_flag;
begin
-- get pl_id
select pl_id
into l_pl_id
from ben_pl_f
where sysdate between effective_start_date
                and effective_end_date
and name = p_plan_name
and business_group_id = p_business_group_id;
if (p_program_name is not null) then
  -- get pgm_id
  select pgm_id
  into l_pgm_id
  from ben_pgm_f
 where sysdate between effective_start_date
                    and effective_end_date
    and name = p_program_name
    and business_group_id = p_business_group_id;
 select plip_id
  -- get plip_id
  into l_plip_id
 from ben_plip_f
 where sysdate between effective_start_date
                    and effective_end_date
    and pl_id = l_pl_id
    and pgm_id = l_pgm_id
   and business_group_id = p_business_group_id;
end if;
-- see if there is an existing record
open csr_exists;
fetch csr_exists into l_authoria_mapping_id
                     ,l_object_version_number;
if csr_exists%found then
 -- do update
 HR_AUTHORIA_MAPPING_API.update_authoria_mapping
    (
    p_authoria_mapping_id           => l_authoria_mapping_id
    ,p_pl_id                         => l_pl_id
    ,p_plip_id                       => l_plip_id
   ,p_open_enrollment_flag          => p_open_enrollment_flag
    ,p_target_page                   => p_target_page
   ,p_object_version_number         => l_object_version_number
    );
else
 -- do insert
  HR_AUTHORIA_MAPPING_API.create_authoria_mapping
    (
    p_pl_id                         => l_pl_id
   ,p_plip_id                       => l_plip_id
   ,p_open_enrollment_flag          => p_open_enrollment_flag
   ,p_target_page                   => p_target_page
   ,p_authoria_mapping_id           => l_authoria_mapping_id
   ,p_object_version_number         => l_object_version_number
end if;
    );
close csr_exists;
end;
--
BEGIN
--
select business_group_id
into l_business_group_id
from per_business_groups
where name = 'SC Benefits';
create_page_map('ACME Dental NonFlex','ACME Program NonFlex',
               'N','Dental',l_business_group_id);
create_page_map('ACME Dental NonFlex','ACME Program NonFlex',
                'Y','AnnEnroll',l_business_group_id);
create_page_map('Saving Plan',NULL,
               'N','Sav',l_business_group_id);
create_page_map('Saving Plan',NULL,
               'Y','AnnEnroll',l_business_group_id);
--
END;
/
COMMIT;
EXIT;