[Home] [Help]
PACKAGE BODY: APPS.PQH_SEED_BEN_CRITERIA
Source
1 PACKAGE BODY pqh_seed_ben_criteria AS
2 /* $Header: pqcritld.pkb 120.1 2005/10/12 20:18 srajakum noship $ */
3 --
4 procedure load_criteria_seed_row(
5 p_owner in varchar2
6 ,p_short_code in varchar2
7 ,p_name in varchar2
8 ,p_description in varchar2
9 ,p_crit_col1_val_type_cd in varchar2
10 ,p_crit_col1_datatype in varchar2
11 ,p_col1_lookup_type in varchar2
12 ,p_col1_value_set_name in varchar2
13 ,p_access_table_name1 in varchar2
14 ,p_access_column_name1 in varchar2
15 ,p_crit_col2_val_type_cd in varchar2
16 ,p_crit_col2_datatype in varchar2
17 ,p_col2_lookup_type in varchar2
18 ,p_col2_value_set_name in varchar2
19 ,p_access_table_name2 in varchar2
20 ,p_access_column_name2 in varchar2
21 ,p_allow_range_validation_flag in varchar2
22 ,p_allow_range_validation_flag2 in varchar2
23 ,p_user_defined_flag in varchar2
24 ,p_business_group_id in varchar2
25 ,p_legislation_code in varchar2
26 ,p_last_update_date in varchar2
27 ) is
28
29 l_ovn number := 1;
30 l_created_by ben_eligy_criteria.created_by%type;
31 l_last_updated_by ben_eligy_criteria.last_updated_by%type;
32 l_creation_date ben_eligy_criteria.creation_date%type;
33 l_last_update_date ben_eligy_criteria.last_update_date%type;
34 l_last_update_login ben_eligy_criteria.last_update_login%type;
35
36 l_col1_value_set_id ben_eligy_criteria.col1_value_set_id%type;
37 l_col2_value_set_id ben_eligy_criteria.col2_value_set_id%type;
38
39 l_eligy_criteria_id ben_eligy_criteria.eligy_criteria_id%type;
40
41
42 cursor csr_fvs(p_valset_name in varchar2) is
43 select flex_value_set_id
44 from fnd_flex_value_sets
45 where flex_value_set_name = p_valset_name;
46 --
47 cursor csr_bec is
48 select eligy_criteria_id
49 from ben_eligy_criteria
50 where short_code = p_short_code and business_group_id is null and criteria_type='STD';
51 --
52 cursor csr_bg_bec is
53 select eligy_criteria_id
54 from ben_eligy_criteria
55 where short_code = p_short_code and business_group_id is not null and criteria_type='STD';
56 --
57 l_data_migrator_mode varchar2(10);
58 --
59 begin
60 --
61 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
62 hr_general.g_data_migrator_mode := 'Y';
63 --
64 l_last_updated_by := fnd_load_util.owner_id(p_owner);
65 l_created_by := fnd_load_util.owner_id(p_owner);
66 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
67 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
68 /**
69 l_creation_date := sysdate;
70 l_last_update_date := sysdate;
71 **/
72 l_last_update_login := 0;
73 --
74 open csr_bec;
75 fetch csr_bec into l_eligy_criteria_id;
76 close csr_bec;
77 --
78 open csr_fvs(p_col1_value_set_name);
79 fetch csr_fvs into l_col1_value_set_id;
80 close csr_fvs;
81 --
82 open csr_fvs(p_col2_value_set_name);
83 fetch csr_fvs into l_col2_value_set_id;
84 close csr_fvs;
85
86 if l_eligy_criteria_id is not null then
87 --
88 update ben_eligy_criteria set
89 name = p_name,
90 description = p_description,
91 crit_col1_val_type_cd = p_crit_col1_val_type_cd,
92 crit_col1_datatype = p_crit_col1_datatype,
93 col1_lookup_type = p_col1_lookup_type,
94 col1_value_set_id = l_col1_value_set_id,
95 access_table_name1 = p_access_table_name1 ,
96 access_column_name1 = p_access_column_name1,
97 crit_col2_val_type_cd = p_crit_col2_val_type_cd,
98 crit_col2_datatype = p_crit_col2_datatype,
99 col2_lookup_type = p_col2_lookup_type,
100 col2_value_set_id = l_col2_value_set_id,
101 access_table_name2 = p_access_table_name2,
102 access_column_name2 = p_access_column_name2,
103 allow_range_validation_flag = p_allow_range_validation_flag,
104 allow_range_validation_flag2 = p_allow_range_validation_flag2,
105 user_defined_flag = p_user_defined_flag,
106 business_group_id = to_number(p_business_group_id),
107 legislation_code = p_legislation_code,
108 criteria_type = 'STD',
109 last_updated_by = l_last_updated_by,
110 last_update_date = l_last_update_date,
111 last_update_login = l_last_update_login
112 where eligy_criteria_id = l_eligy_criteria_id;
113 --
114 -- Update any BG specific rows that were created.
115 --
116 For bg_crit_rec in csr_bg_bec loop
117 --
118 update ben_eligy_criteria set
119 name = p_name,
120 description = p_description,
121 crit_col1_val_type_cd = p_crit_col1_val_type_cd,
122 crit_col1_datatype = p_crit_col1_datatype,
123 col1_lookup_type = p_col1_lookup_type,
124 col1_value_set_id = l_col1_value_set_id,
125 access_table_name1 = p_access_table_name1 ,
126 access_column_name1 = p_access_column_name1,
127 crit_col2_val_type_cd = p_crit_col2_val_type_cd,
128 crit_col2_datatype = p_crit_col2_datatype,
129 col2_lookup_type = p_col2_lookup_type,
130 col2_value_set_id = l_col2_value_set_id,
131 access_table_name2 = p_access_table_name2,
132 access_column_name2 = p_access_column_name2,
133 allow_range_validation_flag = p_allow_range_validation_flag,
134 allow_range_validation_flag2 = p_allow_range_validation_flag2,
135 user_defined_flag = p_user_defined_flag,
136 legislation_code = p_legislation_code,
137 criteria_type = 'STD',
138 last_updated_by = l_last_updated_by,
139 last_update_date = l_last_update_date,
140 last_update_login = l_last_update_login
141 where eligy_criteria_id = bg_crit_rec.eligy_criteria_id;
142 --
143 End loop;
144
145 else
146 --
147 insert into ben_eligy_criteria
148 (
149 eligy_criteria_id,
150 short_code,
151 name,
152 description,
153 crit_col1_val_type_cd,
154 crit_col1_datatype,
155 col1_lookup_type,
156 col1_value_set_id,
157 access_table_name1,
158 access_column_name1,
159 crit_col2_val_type_cd,
160 crit_col2_datatype,
161 col2_lookup_type,
162 col2_value_set_id,
163 access_table_name2,
164 access_column_name2,
165 allow_range_validation_flag,
166 allow_range_validation_flag2,
167 user_defined_flag,
168 business_group_id,
169 legislation_code,
170 criteria_type,
171 created_by,
172 creation_date,
173 last_updated_by,
174 last_update_date,
175 last_update_login,
176 object_version_number
177 )
178 values
179 (
180 ben_eligy_criteria_s.nextval,
181 p_short_code,
182 p_name,
183 p_description,
184 p_crit_col1_val_type_cd,
185 p_crit_col1_datatype,
186 p_col1_lookup_type,
187 l_col1_value_set_id,
188 p_access_table_name1,
189 p_access_column_name1,
190 p_crit_col2_val_type_cd,
191 p_crit_col2_datatype,
192 p_col2_lookup_type,
193 l_col2_value_set_id,
194 p_access_table_name2,
195 p_access_column_name2,
196 p_allow_range_validation_flag,
197 p_allow_range_validation_flag2,
198 p_user_defined_flag,
199 null,
200 p_legislation_code,
201 'STD',
202 l_created_by,
203 l_creation_date,
204 l_last_updated_by,
205 l_last_update_date,
206 l_last_update_login,
207 l_ovn
208 );
209 --
210 end if;
211 --
212 hr_general.g_data_migrator_mode := l_data_migrator_mode;
213 end load_criteria_seed_row;
214 --
215 --
216 end pqh_seed_ben_criteria;