[Home] [Help]
PACKAGE BODY: APPS.GCS_VALUE_SET_PKG
Source
1 PACKAGE BODY GCS_VALUE_SET_PKG AS
2 /* $Header: gcsvsetb.pls 120.2 2006/01/06 06:57:58 mikeward noship $ */
3 new_line VARCHAR2(4) := '
4 ';
5 g_api VARCHAR2(80) := 'gcs.plsql.GCS_VALUE_SET_PKG';
6
7 g_entity_type_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').attribute_id;
8 g_entity_type_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').version_id;
9
10 PROCEDURE create_entity_value_set(x_errbuf OUT NOCOPY VARCHAR2,
11 x_retcode OUT NOCOPY VARCHAR2)
12
13 IS
14
15 l_flex_value_set_id NUMBER(10);
16
17 BEGIN
18
19 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
20 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.create_entity_value_set.begin', '<<Enter>>');
21 END IF;
22
23 fnd_file.put_line(fnd_file.log, 'Creating Entity Value Set for ICM Integration.');
24
25 SELECT flex_value_set_id
26 INTO l_flex_value_set_id
27 FROM fnd_flex_value_sets
28 WHERE flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';
29
30 MERGE INTO fnd_flex_values ffv
31 USING (SELECT feb.entity_id,
32 feb.entity_display_code,
33 decode(fea.dim_attribute_varchar_member,'C','Y','N') summary_flag
34 FROM fem_entities_b feb,
35 fem_entities_attr fea
36 WHERE feb.value_set_id = 18
37 AND fea.entity_id = feb.entity_id
38 AND fea.attribute_id = g_entity_type_attr_id
39 AND fea.version_id = g_entity_type_version_id
40 ) entity
41 ON (ffv.flex_value_id = entity.entity_id)
42 WHEN MATCHED THEN
43 UPDATE SET flex_value = entity.entity_display_code,
44 last_update_date = sysdate,
45 last_updated_by = fnd_global.user_id,
46 last_update_login = fnd_global.login_id
47 WHEN NOT MATCHED THEN
48 INSERT(flex_value_set_id,
49 flex_value_id,
50 flex_value,
51 enabled_flag,
52 summary_flag,
53 creation_date,
54 created_by,
55 last_update_date,
56 last_updated_by,
57 last_update_login)
58 VALUES(l_flex_value_set_id,
59 entity.entity_id,
60 entity.entity_display_code,
61 'Y',
62 entity.summary_flag,
63 sysdate,
64 fnd_global.user_id,
65 sysdate,
66 fnd_global.user_id,
67 fnd_global.login_id);
68
69 MERGE INTO fnd_flex_values_tl ffvt
70 USING (SELECT fet.entity_id,
71 fet.entity_name,
72 fet.description,
73 fet.language,
74 fet.source_lang
75 FROM fem_entities_tl fet
76 WHERE fet.value_set_id = 18
77 ) entity
78 ON (ffvt.flex_value_id = entity.entity_id AND
79 ffvt.language = entity.language)
80 WHEN MATCHED THEN
81 UPDATE SET flex_value_meaning = entity.entity_name,
82 description = entity.entity_name,
83 last_update_date = sysdate,
84 last_updated_by = fnd_global.user_id,
85 last_update_login = fnd_global.login_id
86 WHEN NOT MATCHED THEN
87 INSERT(flex_value_id,
88 language,
89 source_lang,
90 flex_value_meaning,
91 description,
92 creation_date,
93 created_by,
94 last_update_date,
95 last_updated_by,
96 last_update_login)
97 VALUES(entity.entity_id,
98 entity.language,
99 entity.source_lang,
100 entity.entity_name,
101 entity.entity_name,
102 sysdate,
103 fnd_global.user_id,
104 sysdate,
105 fnd_global.user_id,
106 fnd_global.login_id);
107
108 COMMIT;
109
110 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
111 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.create_entity_value_set.end', '<<Exit>>');
112 END IF;
113 fnd_file.put_line(fnd_file.log, 'End of Entity Value Set creation for ICM Integration.');
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
118 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, g_api || '.create_entity_value_set.error', SQLERRM);
119 END IF;
120 fnd_file.put_line(fnd_file.log, SUBSTR(SQLERRM,1,200));
121
122 END create_entity_value_set;
123
124 PROCEDURE recurse_hierarchy( p_hierarchy_id IN NUMBER,
125 p_entity_id IN NUMBER,
126 p_eff_date IN VARCHAR2,
127 p_entity_display_code IN VARCHAR2,
128 p_flex_value_set_id IN NUMBER )
129 IS
130
131 CURSOR c_child_entity ( p_hierarchy_id NUMBER,
132 p_entity_id NUMBER,
133 p_eff_date VARCHAR2)
134 IS
135 SELECT gcr.child_entity_id,
136 feb.entity_display_code child_entity_display_code,
137 fea.dim_attribute_varchar_member child_entity_type_code
138 FROM gcs_cons_relationships gcr,
139 fem_entities_b feb,
140 fem_entities_attr fea
141 WHERE gcr.hierarchy_id = p_hierarchy_id
142 AND gcr.parent_entity_id = p_entity_id
143 AND TO_DATE(p_eff_date,'DD-MM-YYYY') BETWEEN gcr.start_date AND NVL(gcr.end_date, TO_DATE(p_eff_date,'DD-MM-YYYY'))
144 AND gcr.dominant_parent_flag = 'Y'
145 AND feb.entity_id = gcr.child_entity_id
146 AND fea.entity_id = gcr.child_entity_id
147 AND fea.attribute_id = g_entity_type_attr_id
148 AND fea.version_id = g_entity_type_version_id;
149 BEGIN
150
151 FOR v_temp IN c_child_entity ( p_hierarchy_id,
152 p_entity_id,
153 p_eff_date)
154 LOOP
155
156 INSERT INTO fnd_flex_value_norm_hierarchy ( flex_value_set_id,
157 parent_flex_value,
158 range_attribute,
159 child_flex_value_low,
160 child_flex_value_high,
161 creation_date,
162 created_by,
163 last_update_date,
164 last_updated_by,
165 last_update_login,
166 start_date_active,
167 end_date_active)
168 VALUES ( p_flex_value_set_id,
169 p_entity_display_code,
170 decode(v_temp.child_entity_type_code,
171 'C', 'P',
172 'C'),
173 v_temp.child_entity_display_code,
174 v_temp.child_entity_display_code,
175 sysdate,
176 fnd_global.user_id,
177 sysdate,
178 fnd_global.user_id,
179 fnd_global.login_id,
180 null,
181 null);
182
183 IF (v_temp.child_entity_type_code = 'C') THEN
184
185 recurse_hierarchy( p_hierarchy_id,
186 v_temp.child_entity_id,
187 p_eff_date,
188 v_temp.child_entity_display_code,
189 p_flex_value_set_id
190 );
191
192 END IF;
193
194 END LOOP;
195 EXCEPTION
196 WHEN OTHERS THEN
197 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
198 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, g_api || '.create_value_set_hierarchy.recurse_error', 'Value Set Id:'||p_flex_value_set_id||' Hierarchy Id:'||p_hierarchy_id ||' Entity Id:'|| p_entity_id ||'-'||SQLERRM);
199 END IF;
200 fnd_file.put_line(fnd_file.log, 'Recurse error '||SUBSTR(SQLERRM,1,200));
201 END recurse_hierarchy;
202
203 PROCEDURE create_entity_value_set_hier( x_errbuf OUT NOCOPY VARCHAR2,
204 x_retcode OUT NOCOPY VARCHAR2,
205 p_eff_date IN VARCHAR2 )
206
207 IS
208
209 l_hierarchy_id NUMBER(15);
210 l_top_entity_id NUMBER;
211 l_entity_display_code VARCHAR2(150);
212 l_flex_value_set_id NUMBER(10);
213 l_request_id NUMBER(15);
214 BEGIN
215
216 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
217 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.create_entity_value_set_hier.begin', '<<Enter>>');
218 END IF;
219
220 fnd_file.put_line(fnd_file.log, 'Creating Entity Value Set Hierarchy for ICM Integration.');
221
222 SELECT ghb.hierarchy_id,
223 ghb.top_entity_id,
224 feb.entity_display_code
225 INTO l_hierarchy_id,
226 l_top_entity_id,
227 l_entity_display_code
228 FROM gcs_hierarchies_b ghb,
229 fem_entities_b feb
230 WHERE ghb.certification_flag = 'Y'
231 AND feb.entity_id = ghb.top_entity_id;
232
233 SELECT flex_value_set_id
234 INTO l_flex_value_set_id
235 FROM fnd_flex_value_sets
236 WHERE flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';
237
238 DELETE fnd_flex_value_norm_hierarchy
239 WHERE flex_value_set_id = l_flex_value_set_id;
240
241 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
242 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.create_entity_value_set_hier.recurse_hierarchy', '<<Exit>>');
243 END IF;
244 fnd_file.put_line(fnd_file.log, 'Start Recursive Entity Value Set Hierarchy creation for ICM Integration.');
245
246 recurse_hierarchy( l_hierarchy_id,
247 l_top_entity_id,
248 p_eff_date,
249 l_entity_display_code,
250 l_flex_value_set_id
251 );
252
253 COMMIT;
254
255 -- Compile value set hierarchies
256 l_request_id := fnd_request.submit_request( application => 'FND',
257 program => 'FDFCHY',
258 sub_request => FALSE,
259 argument1 => l_flex_value_set_id);
260
261
262 GCS_ICM_INTG_PROF_PKG.launch_fin_stmt_import;
263
264 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
265 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.create_entity_value_set_hier.end', '<<Exit>>');
266 END IF;
267 fnd_file.put_line(fnd_file.log, 'End of Entity Value Set Hierarchy creation for ICM Integration.');
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
272 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, g_api || '.create_entity_value_set_hier.error', SQLERRM);
273 END IF;
274 fnd_file.put_line(fnd_file.log, SUBSTR(SQLERRM,1,200));
275
276 END create_entity_value_set_hier;
277
278 END GCS_VALUE_SET_PKG;