PACKAGE BODY: APPS.BEN_SEED_COMMUNICATION_TYPES

Source

1 package body ben_seed_communication_types as
2 /* $Header: bencmtse.pkb 120.0 2005/05/28 03:50:33 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 |			Copyright (c) 1997 Oracle Corporation		       |
7 |			   Redwood Shores, California, USA		       |
8 |			        All rights reserved.			       |
9 +==============================================================================+
10 --
11 Name
12 	Seed Communication Types
13 Purpose
14         This package is used to seed communication types on a business group basis.
15 History
16         Date             Who        Version    What?
17         ----             ---        -------    -----
18         30 Oct 98        S Tee      115.0      Created.
19         01 Jul 99        Tmathers   115.1      Changed to use startup table.
20         11 May 00        S Tee      115.0      Insert into MLS table.
21         16 Dec 04        rpgupta    115.4      Insert the to_be_sent_cd which is
22                                                seeded.
23 */
24 --------------------------------------------------------------------------------
25 --
26 g_package varchar2(80) := 'ben_seed_communication_types';
27 --
28 --
29 procedure seed_communication_types(p_business_group_id in number) is
30   --
31   l_package               varchar2(80) := g_package||'.seed_communication_types';
32   --
33 begin
34   --
35   hr_utility.set_location ('Entering '||l_package,10);
36     --
37     insert into ben_cm_typ_f
38     (cm_typ_id,
39      effective_start_date,
40      effective_end_date,
41      name,
42      whnvr_trgrd_flag,
43      shrt_name,
44      pc_kit_cd,
45      trk_mlg_flag,
46      to_be_sent_dt_cd,
47      inspn_rqd_flag,
48      business_group_id,
49      object_version_number)
50     select
51     ben_cm_typ_s.nextval,
52      to_date('01-01-1950','DD-MM-YYYY'),
53      to_date('31-12-4712','DD-MM-YYYY'),
54      name,
55      'N',
56      shrt_name,
57      'PC',
58      'N',
59      nvl(to_be_sent_dt_cd, 'OED'),--4056466
60      'N',
61      p_business_group_id,
62      1
63      from ben_startup_cm_typ s_ct
64      where not exists (select 1
65                        from ben_cm_typ_f ct
66                        where s_ct.name          = ct.name
67                        and   s_ct.shrt_name     = ct.shrt_name
68                        and ct.business_group_id = p_business_group_id);
69     --
70     -- Insert into MLS table.
71     --
72     insert into ben_cm_typ_f_tl (
73       cm_typ_id,
74       effective_start_date,
75       effective_end_date,
76       shrt_name,
77       name,
78       language,
79       source_lang,
80       last_update_date,
81       last_updated_by,
82       last_update_login,
83       created_by,
84       creation_date
85     ) select
86       b.cm_typ_id,
87       b.effective_start_date,
88       b.effective_end_date,
89       tl.shrt_name,
90       tl.name,
91       tl.language,
92       tl.source_lang,
93       b.last_update_date,
94       b.last_updated_by,
95       b.last_update_login,
96       b.created_by,
97       b.creation_date
98     from ben_cm_typ_f b
99         ,ben_startup_cm_typ_tl tl
100     where b.business_group_id = p_business_group_id
101     and b.shrt_name = tl.shrt_name
102     and not exists
103             (select 'Y'
104              from ben_cm_typ_f_tl t
105              where t.cm_typ_id = b.cm_typ_id
106              and t.effective_start_date = b.effective_start_date
107              and   t.source_lang = userenv('LANG'));
108   --
109   hr_utility.set_location ('Leaving '||l_package,10);
110   --
111 end seed_communication_types;
112 --
113 end ben_seed_communication_types;