[Home] [Help]
PACKAGE BODY: APPS.CZ_RULE_IMPORT
Source
1 PACKAGE BODY CZ_RULE_IMPORT AS
2 /* $Header: czruleib.pls 120.12.12020000.2 2012/08/17 15:29:21 smanna ship $ */
3 ---------------------------------------------------------------------------------------
4 TYPE table_of_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE table_of_number_index_VC2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
6 TYPE table_of_varchar IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
7 TYPE table_of_varchar_index_VC2 IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(300); --Bug8580853
8 TYPE table_of_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
9 TYPE table_of_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
10 TYPE table_of_clob IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
11 ---------------------------------------------------------------------------------------
12
13 -- Specification for Process default localized texts
14 PROCEDURE process_dflt_localized_texts(p_api_version IN NUMBER,
15 p_run_id IN NUMBER,
16 p_maximum_errors IN PLS_INTEGER,
17 p_commit_size IN PLS_INTEGER,
18 p_errors IN OUT NOCOPY PLS_INTEGER,
19 x_return_status IN OUT NOCOPY VARCHAR2,
20 x_msg_count IN OUT NOCOPY NUMBER,
21 x_msg_data IN OUT NOCOPY VARCHAR2);
22
23 PROCEDURE report(p_message IN VARCHAR2,
24 p_run_id IN NUMBER,
25 p_caller IN VARCHAR2,
26 p_statuscode IN NUMBER) IS
27 BEGIN
28 INSERT INTO cz_db_logs (logtime, urgency, caller, statuscode, message, run_id)
29 VALUES (SYSDATE, 1, p_caller, p_statuscode, p_message, p_run_id);
30 END;
31 ---------------------------------------------------------------------------------------
32 PROCEDURE cnd_rules(p_api_version IN NUMBER,
33 p_run_id IN NUMBER,
34 p_maximum_errors IN PLS_INTEGER,
35 p_commit_size IN PLS_INTEGER,
36 p_errors IN OUT NOCOPY PLS_INTEGER,
37 x_return_status IN OUT NOCOPY VARCHAR2,
38 x_msg_count IN OUT NOCOPY NUMBER,
39 x_msg_data IN OUT NOCOPY VARCHAR2) IS
40
41 v_debug NUMBER := 6000;
42
43 CURSOR c_rec IS
44 SELECT ROWID, devl_project_id, message, orig_sys_ref, rule_folder_id, rule_type,
48 instantiation_scope,
45 name, seeded_flag, deleted_flag, mutable_flag, disabled_flag, invalid_flag,
46 presentation_flag, effective_usage_mask, seq_nbr, disposition,
47 component_id, model_ref_expl_id, fsk_component_id, fsk_model_ref_expl_id,
49 rule_class, class_seq,config_engine_type,accumulator_flag,top_level_constraint_flag -- Bug9467066
50 FROM cz_imp_rules
51 WHERE run_id = p_run_id
52 AND rec_status IS NULL
53 AND disposition IS NULL;
54
55 t_rowid table_of_rowid;
56 t_devl_project_id table_of_number;
57 t_message table_of_varchar;
58 t_orig_sys_ref table_of_varchar;
59 t_rule_folder_id table_of_number;
60 t_rule_type table_of_number;
61 t_name table_of_varchar;
62 t_seeded_flag table_of_varchar;
63 t_deleted_flag table_of_varchar;
64 t_mutable_flag table_of_varchar;
65 t_disabled_flag table_of_varchar;
66 t_invalid_flag table_of_varchar;
67 t_presentation_flag table_of_varchar;
68 t_effective_usage_mask table_of_varchar;
69 t_seq_nbr table_of_number;
70 t_disposition table_of_varchar;
71 t_component_id table_of_number;
72 t_model_ref_expl_id table_of_number;
73 t_fsk_component_id table_of_varchar;
74 t_fsk_model_ref_expl_id table_of_varchar;
75 t_instantiation_scope table_of_number;
76 t_rule_class table_of_number; -- Bug9467066
77 t_class_seq table_of_number; -- Bug9467066
78 t_config_engine_type table_of_varchar; -- Bug9467066
79 t_accumulator_flag table_of_varchar; -- Bug9467066
80 t_top_level_constraint_flag table_of_varchar; -- Bug9467066
81
82 validateModel VARCHAR2(4000) :=
83 'SELECT model_ref_expl_id ' ||
84 ' FROM cz_rp_entries r, cz_model_ref_expls e ' ||
85 ' WHERE r.deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
86 ' AND r.object_type = ''' || CZRI_REPOSITORY_PROJECT || ''' ' ||
87 ' AND r.object_id = :1 ' ||
88 ' AND e.deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
89 ' AND e.parent_expl_node_id IS NULL ' ||
90 ' AND e.model_id = r.object_id';
91
92 getRootFolderId VARCHAR2(4000) :=
93 'SELECT rule_folder_id ' ||
94 ' FROM cz_rule_folders ' ||
95 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
96 ' AND object_type = ''' || CZRI_TYPE_RULE_FOLDER || ''' ' ||
97 ' AND devl_project_id = :1 ' ||
98 ' AND parent_rule_folder_id IS NULL';
99
100 validateFolder VARCHAR2(4000) :=
101 'SELECT NULL ' ||
102 ' FROM cz_rule_folders ' ||
103 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
104 ' AND object_type = ''' || CZRI_TYPE_RULE_FOLDER || ''' ' ||
105 ' AND devl_project_id = :1 ' ||
106 ' AND rule_folder_id = :2';
107
108 h_ValidModel table_of_number_index_VC2;
109 h_InvalidModel table_of_number_index_VC2;
110 h_ValidFolder table_of_number;
111 h_RootFolder table_of_number_index_VC2;
112 h_NoSuchFolder table_of_number;
113 h_NoRootFolder table_of_number_index_VC2;
114 h_NameRootFolder table_of_varchar_index_VC2; --Bug8580853
115 v_null NUMBER;
116 v_root_folder_id NUMBER;
117 v_root_expl_id NUMBER;
118 v_check_dup_name VARCHAR2(4000); --Bug8580853
119 ---------------------------------------------------------------------------------------
120 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
121 BEGIN
122
123 FORALL i IN 1..p_upper_limit
124 UPDATE cz_imp_rules SET
125 message = t_message(i),
126 seeded_flag = t_seeded_flag(i),
127 deleted_flag = t_deleted_flag(i),
128 rec_status = CZRI_RECSTATUS_CND,
129 disposition = t_disposition(i),
130 mutable_flag = t_mutable_flag(i),
131 disabled_flag = t_disabled_flag(i),
132 invalid_flag = t_invalid_flag(i),
133 presentation_flag = t_presentation_flag(i),
134 effective_usage_mask = t_effective_usage_mask(i),
135 seq_nbr = t_seq_nbr(i),
136 model_ref_expl_id = t_model_ref_expl_id(i),
137 rule_folder_id = t_rule_folder_id(i),
138 component_id = t_component_id(i),
139 instantiation_scope = t_instantiation_scope(i),
140 rule_class = t_rule_class(i), --Bug9467066
141 class_seq = t_class_seq(i), --Bug9467066
142 config_engine_type = t_config_engine_type(i), --Bug9467066
143 accumulator_flag = t_accumulator_flag(i), --Bug9467066
144 top_level_constraint_flag = t_top_level_constraint_flag(i) --Bug9467066
145 WHERE ROWID = t_rowid(i);
146 END;
147 ---------------------------------------------------------------------------------------
148 BEGIN
149
150 OPEN c_rec;
151 LOOP
152
153 t_rowid.DELETE;
154 t_devl_project_id.DELETE;
155 t_message.DELETE;
156 t_orig_sys_ref.DELETE;
157 t_rule_folder_id.DELETE;
158 t_rule_type.DELETE;
159 t_name.DELETE;
160 t_seeded_flag.DELETE;
161 t_deleted_flag.DELETE;
162 t_mutable_flag.DELETE;
163 t_disabled_flag.DELETE;
164 t_invalid_flag.DELETE;
165 t_presentation_flag.DELETE;
166 t_effective_usage_mask.DELETE;
167 t_seq_nbr.DELETE;
168 t_disposition.DELETE;
169 t_component_id.DELETE;
170 t_model_ref_expl_id.DELETE;
171 t_fsk_component_id.DELETE;
172 t_fsk_model_ref_expl_id.DELETE;
173 t_instantiation_scope.DELETE;
174 t_rule_class.DELETE; --Bug9467066
178 t_top_level_constraint_flag.DELETE; --Bug9467066
175 t_class_seq.DELETE; --Bug9467066
176 t_config_engine_type.DELETE; --Bug9467066
177 t_accumulator_flag.DELETE; --Bug9467066
179 FETCH c_rec BULK COLLECT INTO
180 t_rowid, t_devl_project_id, t_message, t_orig_sys_ref, t_rule_folder_id, t_rule_type, t_name,
181 t_seeded_flag, t_deleted_flag, t_mutable_flag, t_disabled_flag, t_invalid_flag, t_presentation_flag,
182 t_effective_usage_mask, t_seq_nbr, t_disposition, t_component_id, t_model_ref_expl_id, t_fsk_component_id,
183 t_fsk_model_ref_expl_id, t_instantiation_scope,t_rule_class, t_class_seq,t_config_engine_type,t_accumulator_flag,t_top_level_constraint_flag --Bug9467066
184 LIMIT p_commit_size;
185 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
186
187 FOR i IN 1..t_rowid.COUNT LOOP
188
189 t_message(i) := NULL;
190 t_disposition(i) := CZRI_DISPOSITION_REJECT;
191
192 IF(t_devl_project_id(i) IS NULL)THEN
193
194 t_message(i) := cz_utils.get_text('CZRI_RLE_NULLMODELID');
195
196 ELSIF(h_InvalidModel.EXISTS(t_devl_project_id(i)))THEN
197
198 t_message(i) := cz_utils.get_text('CZRI_RLE_INVALIDMODEL');
199
200 ELSIF(h_NoRootFolder.EXISTS(t_devl_project_id(i)))THEN
201
202 t_message(i) := cz_utils.get_text('CZRI_RLE_NOROOTFOLDER');
203
204 ELSIF(t_rule_folder_id(i) IS NOT NULL AND h_NoSuchFolder.EXISTS(t_rule_folder_id(i)))THEN
205
206 t_message(i) := cz_utils.get_text('CZRI_RLE_NOSUCHFOLDER');
207
208 ELSIF(t_orig_sys_ref(i) IS NULL)THEN
209
210 t_message(i) := cz_utils.get_text('CZRI_RLE_NULLORIGSYSREF');
211
212 ELSIF(t_rule_type(i) IS NULL)THEN
213
214 t_message(i) := cz_utils.get_text('CZRI_RLE_NULLTYPE');
215
216 ELSIF(t_rule_type(i) NOT IN (CZRI_TYPE_EXPRESSION_RULE, CZRI_TYPE_COMPANION_RULE))THEN
217
218 t_message(i) := cz_utils.get_text('CZRI_RLE_INVALIDTYPE');
219
220 -- For the Phase I we do not implement resolution of component_id, model_ref_expl_id values for
221 -- CX rules using surrogate keys. Instead, we require direct population of the columns.
222 --
223 -- ELSIF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_fsk_component_id(i) IS NULL)THEN
224 --
225 -- t_message(i) := cz_utils.get_text('CZRI_RLE_NULLCOMPONENTID');
226 --
227 -- ELSIF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_fsk_model_ref_expl_id(i) IS NULL)THEN
228 --
229 -- t_message(i) := cz_utils.get_text('CZRI_RLE_NULLEXPLID');
230
231 -- If component_id is NULL, it will be populated automatically with the ps_node_id of the root
232 -- model node.
233 --
234 -- ELSIF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_component_id(i) IS NULL)THEN
235 --
236 -- t_message(i) := cz_utils.get_text('CZRI_RLE_NULLCOMPONENTID');
237
238 -- If model_ref_expl_id is NULL, it will be populated automatically with the model_ref_expl_id
239 -- of the root model node.
240 --
241 -- ELSIF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_model_ref_expl_id(i) IS NULL)THEN
242 --
243 -- t_message(i) := cz_utils.get_text('CZRI_RLE_NULLEXPLID');
244
245 ELSIF(t_name(i) IS NULL)THEN
246
247 t_message(i) := cz_utils.get_text('CZRI_RLE_NULLNAME');
248
249 ELSIF(t_presentation_flag(i) IS NOT NULL AND t_presentation_flag(i) <> CZRI_FLAG_STATEMENT_RULE)THEN
250
251 t_message(i) := cz_utils.get_text('CZRI_RLE_PRESENTFLAG');
252
253 ELSE
254
255 t_disposition(i) := CZRI_DISPOSITION_PASSED;
256 END IF;
257
258 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED AND (NOT h_ValidModel.EXISTS(t_devl_project_id(i))))THEN
259
260 BEGIN
261
262 EXECUTE IMMEDIATE validateModel INTO v_root_expl_id USING t_devl_project_id(i);
263 h_ValidModel(t_devl_project_id(i)) := v_root_expl_id;
264
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 t_message(i) := cz_utils.get_text('CZRI_RLE_INVALIDMODEL');
268 t_disposition(i) := CZRI_DISPOSITION_REJECT;
269 h_InvalidModel(t_devl_project_id(i)) := 1;
270 END;
271 END IF;
272
273 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
274 IF(t_rule_folder_id(i) IS NULL)THEN
275 IF(h_RootFolder.EXISTS(t_devl_project_id(i)))THEN
276
277 t_rule_folder_id(i) := h_RootFolder(t_devl_project_id(i));
278
279 ELSE
280
281 BEGIN
282
283 EXECUTE IMMEDIATE getRootFolderId INTO v_root_folder_id USING t_devl_project_id(i);
284 t_rule_folder_id(i) := v_root_folder_id;
285 h_RootFolder(t_devl_project_id(i)) := v_root_folder_id;
286
287 EXCEPTION
288 WHEN NO_DATA_FOUND THEN
289 t_message(i) := cz_utils.get_text('CZRI_RLE_NOROOTFOLDER');
290 t_disposition(i) := CZRI_DISPOSITION_REJECT;
291 h_NoRootFolder(t_devl_project_id(i)) := 1;
292 END;
293 END IF;
294 ELSE
295 IF(NOT h_ValidFolder.EXISTS(t_rule_folder_id(i)))THEN
296
297 BEGIN
298
299 EXECUTE IMMEDIATE validateFolder INTO v_null USING t_devl_project_id(i), t_rule_folder_id(i);
300 h_ValidFolder(t_rule_folder_id(i)) := v_root_folder_id;
301
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304 t_message(i) := cz_utils.get_text('CZRI_RLE_NOSUCHFOLDER');
305 t_disposition(i) := CZRI_DISPOSITION_REJECT;
306 h_NoSuchFolder(t_rule_folder_id(i)) := 1;
307 END;
308 END IF;
309 END IF;
310 END IF;
314 IF(h_NameRootFolder.EXISTS(v_check_dup_name))THEN --Bug8580853
311 --Check for Duplicate names of rules Bug8580853
312 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
313 v_check_dup_name := t_devl_project_id(i) || '-' || t_rule_folder_id(i) ||'-' || t_name(i); --Bug8580853
315 t_message(i) := cz_utils.get_text('CZRI_RLE_DUPNAME','DEVL_PROJ_ID',t_devl_project_id(i),'RULE_FOLDER_ID',t_rule_folder_id(i),'RULE_NAME',t_name(i)); --Bug8580853
316 t_disposition(i) := CZRI_DISPOSITION_REJECT;
317 FND_FILE.PUT_LINE(FND_FILE.LOG, t_message(i));
318 ELSE
319 h_NameRootFolder(v_check_dup_name):=1;
320 END IF;
321 END IF;
322
323 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
324
325 p_errors := p_errors + 1;
326
327 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
328
329 --Update the already processed records here.
330
331 update_table_data(i);
332 COMMIT;
333 RAISE CZRI_ERR_MAXIMUM_ERRORS;
334 END IF;
335 END IF;
336
337 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
338
339 --Validations passed, condition the record.
340 --The seeded flag is unconditionally set to '0'.
341
342 t_seeded_flag(i) := CZRI_FLAG_NOT_SEEDED;
343 IF(t_instantiation_scope(i) IS NULL)THEN t_instantiation_scope(i) := CZRI_RULE_SCOPE_INSTANCE; END IF;
344 IF(t_deleted_flag(i) IS NULL)THEN t_deleted_flag(i) := CZRI_FLAG_NOT_DELETED; END IF;
345 IF(t_mutable_flag(i) IS NULL)THEN t_mutable_flag(i) := CZRI_FLAG_NOT_MUTABLE; END IF;
346 IF(t_disabled_flag(i) IS NULL)THEN t_disabled_flag(i) := CZRI_FLAG_NOT_DISABLED; END IF;
347 IF(t_invalid_flag(i) IS NULL)THEN t_invalid_flag(i) := CZRI_FLAG_NOT_INVALID; END IF;
348 IF(t_presentation_flag(i) IS NULL)THEN t_presentation_flag(i) := CZRI_FLAG_STATEMENT_RULE; END IF;
349 IF(t_effective_usage_mask(i) IS NULL)THEN t_effective_usage_mask(i) := CZRI_EFFECTIVE_USAGE; END IF;
350 IF(t_seq_nbr(i) IS NULL)THEN t_seq_nbr(i) := CZRI_RULE_SEQ_NBR; END IF;
351 IF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_model_ref_expl_id(i) IS NULL)THEN
352 t_model_ref_expl_id(i) := h_ValidModel(t_devl_project_id(i));
353 END IF;
354 IF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_component_id(i) IS NULL)THEN
355 t_component_id(i) := t_devl_project_id(i);
356 END IF;
357 END IF;
358 END LOOP;
359
360 --Update all the records from memory here.
361
362 update_table_data(t_rowid.COUNT);
363 COMMIT;
364 END LOOP;
365
366 CLOSE c_rec;
367 EXCEPTION
368 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
369 CLOSE c_rec;
370 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
371 x_msg_count := 1;
372 report(x_msg_data, p_run_id, 'cnd_rules', v_debug);
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 RAISE CZRI_ERR_FATAL_ERROR;
375 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
376 CLOSE c_rec;
377 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
378 x_msg_count := 1;
379 report(x_msg_data, p_run_id, 'cnd_rules', v_debug);
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 RAISE CZRI_ERR_FATAL_ERROR;
382 END;
383 ---------------------------------------------------------------------------------------
384 PROCEDURE krs_rules(p_api_version IN NUMBER,
385 p_run_id IN NUMBER,
386 p_maximum_errors IN PLS_INTEGER,
387 p_commit_size IN PLS_INTEGER,
388 p_errors IN OUT NOCOPY PLS_INTEGER,
389 x_return_status IN OUT NOCOPY VARCHAR2,
390 x_msg_count IN OUT NOCOPY NUMBER,
391 x_msg_data IN OUT NOCOPY VARCHAR2) IS
392
393 v_debug NUMBER := 7000;
394 v_error_flag PLS_INTEGER;
395 nOnlRuleId CZ_IMP_RULES.RULE_ID%TYPE;
396 nOnlDevlProjId CZ_IMP_RULES.DEVL_PROJECT_ID%TYPE;
397 nOnlDescText CZ_IMP_RULES.DESC_TEXT%TYPE;
398 nOnlOrigSysRef CZ_IMP_RULES.ORIG_SYS_REF%TYPE;
399 nOnlRulefolderId CZ_IMP_RULES.RULE_FOLDER_ID%TYPE;
400 nOnlRuleType CZ_IMP_RULES.RULE_TYPE%TYPE;
401 nOnlName CZ_IMP_RULES.NAME%TYPE;
402 nOnlDelFlag CZ_IMP_RULES.DELETED_FLAG%TYPE;
403 nOnlMutFlag CZ_IMP_RULES.MUTABLE_FLAG%TYPE;
404 nOnlDisFlag CZ_IMP_RULES.DISABLED_FLAG%TYPE;
405 nOnlInvFlag CZ_IMP_RULES.INVALID_FLAG%TYPE;
406 nOnlPreFlag CZ_IMP_RULES.PRESENTATION_FLAG%TYPE;
407 nOnlInstScope CZ_IMP_RULES.INSTANTIATION_SCOPE%TYPE;
408 nOnlRuleText CZ_IMP_RULES.RULE_TEXT%TYPE;
409
410 CURSOR c_rec IS
411 SELECT ROWID, rule_id, name,rule_folder_id,devl_project_id, message, orig_sys_ref, rec_status, disposition,
412 fsk_localized_text_1,fsk_localized_text_2, fsk_component_id, fsk_model_ref_expl_id, rule_type, reason_id, unsatisfied_msg_id,
413 rule_class, class_seq,config_engine_type,accumulator_flag,top_level_constraint_flag,rule_text --Bug9467066
414 FROM cz_imp_rules
415 WHERE run_id = p_run_id
416 AND rec_status = CZRI_RECSTATUS_CND
417 AND disposition = CZRI_DISPOSITION_PASSED
418 ORDER BY devl_project_id, orig_sys_ref;
419
420
421 CURSOR c_get_engine_type (cp_devl_project_id NUMBER) IS
422 SELECT config_engine_type
423 FROM cz_devl_projects
424 WHERE devl_project_id = cp_devl_project_id;
425
426
430 t_rule_folder_id table_of_number; --Bug8580853
427 t_rowid table_of_rowid;
428 t_rule_id table_of_number;
429 t_name table_of_varchar; --Bug8580853
431 t_devl_project_id table_of_number;
432 t_message table_of_varchar;
433 t_orig_sys_ref table_of_varchar;
434 t_rec_status table_of_varchar;
435 t_disposition table_of_varchar;
436 t_fsk_localized_text_1 table_of_varchar;
437 t_fsk_localized_text_2 table_of_varchar; --Bug9068095
438 t_fsk_component_id table_of_varchar;
439 t_fsk_model_ref_expl_id table_of_varchar;
440 t_rule_type table_of_number;
441 t_reason_id table_of_number;
442 t_unsatisfied_msg_id table_of_number; --Bug9068095
443 t_rule_class table_of_number; --Bug9467066
444 t_class_seq table_of_number; --Bug9467066
445 t_config_engine_type table_of_varchar; --Bug9467066
446 t_accumulator_flag table_of_varchar; --Bug9467066
447 t_top_level_constraint_flag table_of_varchar; --Bug9467066
448 t_rule_text table_of_varchar;
449
450
451
452 resolveRuleId VARCHAR2(4000) :=
453 'SELECT rule_id, rule_text, devl_project_id,orig_sys_ref, rule_folder_id, rule_type,name ' ||
454 ' FROM cz_rules ' ||
455 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
456 ' AND devl_project_id = :1 ' ||
457 ' AND orig_sys_ref = :2';
458
459
460
461
462
463 last_orig_sys_ref cz_imp_rules.orig_sys_ref%TYPE;
464 last_project_id cz_imp_rules.devl_project_id%TYPE;
465
466 last_id_allocated NUMBER := NULL;
467 next_id_to_use NUMBER := 0;
468 id_increment NUMBER := CZRI_RULES_INC;
469
470 t_intl_text_id table_of_number;
471 l_intl_text_id table_of_number;
472 v_translations PLS_INTEGER;
473
474 v_rule_class_chk NUMBER :=0; --Bug9467066
475 t_data_value VARCHAR2(100) := ''; --Bug9467066
476
477
478 ---------------------------------------------------------------------------------------
479 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
480 BEGIN
481
482 FORALL i IN 1..p_upper_limit
483 UPDATE cz_imp_rules SET
484 rule_id = t_rule_id(i),
485 reason_id = t_reason_id(i),
486 unsatisfied_msg_id = t_unsatisfied_msg_id(i),
487 message = t_message(i),
488 rec_status = CZRI_RECSTATUS_KRS,
489 disposition = t_disposition(i),
490 config_engine_type = t_config_engine_type(i)
491 WHERE ROWID = t_rowid(i);
492 END;
493 --------------------------------------------------------------------------------------
494 FUNCTION next_rule_id RETURN NUMBER IS
495 id_to_return NUMBER;
496 BEGIN
497
498 IF((last_id_allocated IS NULL) OR
499 (next_id_to_use = (NVL(last_id_allocated, 0) + id_increment)))THEN
500
501 SELECT cz_rules_s.NEXTVAL INTO last_id_allocated FROM dual;
502 next_id_to_use := last_id_allocated;
503 END IF;
504
505 id_to_return := next_id_to_use;
506 next_id_to_use := next_id_to_use + 1;
507 RETURN id_to_return;
508 END;
509 ---------------------------------------------------------------------------------------
510 BEGIN
511
512 OPEN c_rec;
513 LOOP
514
515 t_rowid.DELETE;
516 t_rule_id.DELETE;
517 t_name.DELETE; --Bug8580853
518 t_rule_folder_id.DELETE; --Bug8580853
519 t_devl_project_id.DELETE;
520 t_message.DELETE;
521 t_orig_sys_ref.DELETE;
522 t_rec_status.DELETE;
523 t_disposition.DELETE;
524 t_fsk_localized_text_1.DELETE;
525 t_fsk_localized_text_2.DELETE; --Bug9068095
526 t_fsk_component_id.DELETE;
527 t_fsk_model_ref_expl_id.DELETE;
528 t_rule_type.DELETE;
529 t_reason_id.DELETE;
530 t_unsatisfied_msg_id.DELETE; --Bug9068095
531 t_rule_class.DELETE; --Bug9467066
532 t_class_seq.DELETE; --Bug9467066
533 t_config_engine_type.DELETE; --Bug9467066
534 t_accumulator_flag.DELETE; --Bug9467066
535 t_top_level_constraint_flag.DELETE; --Bug9467066
536 t_rule_text.DELETE;
537
538 FETCH c_rec BULK COLLECT INTO
539 t_rowid, t_rule_id,t_name,t_rule_folder_id, t_devl_project_id, t_message, t_orig_sys_ref, t_rec_status, t_disposition,
540 t_fsk_localized_text_1, t_fsk_localized_text_2, t_fsk_component_id, t_fsk_model_ref_expl_id, t_rule_type, t_reason_id, t_unsatisfied_msg_id,
541 t_rule_class, t_class_seq, t_config_engine_type,t_accumulator_flag,t_top_level_constraint_flag,t_rule_text --Bug9467066
542 LIMIT p_commit_size;
543 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
544
545 FOR i IN 1..t_rowid.COUNT LOOP
546
547 t_message(i) := NULL;
548
549 -- Bug9467066 Validating Config EngineType, Rule Class and Class Sequence combinations for FCE Rule Import
550
551 IF (t_config_engine_type(i) IS NULL) THEN
552 OPEN c_get_engine_type(t_devl_project_id(i));
553 FETCH c_get_engine_type INTO t_config_engine_type(i);
554 CLOSE c_get_engine_type;
555 END IF;
556
557 IF (t_config_engine_type(i) NOT IN ('F', 'L')) THEN
558 t_message(i) := cz_utils.get_text('CZRI_CONFIG_ENGINE_INCORRECT');
559 t_disposition(i) := CZRI_DISPOSITION_REJECT;
560
561 ELSIF((t_config_engine_type(i)= 'L') AND ((t_rule_class(i) IS NOT NULL) OR (t_class_seq(i) IS NOT NULL))) THEN
562 t_message(i) := cz_utils.get_text('CZRI_ENGTYP_RULCLSSSEQ_INVALD');
563 t_disposition(i) := CZRI_DISPOSITION_REJECT;
564
568
565 ELSIF (t_config_engine_type(i)='F' AND t_rule_class(i) IS NULL) THEN
566 t_message(i) := cz_utils.get_text('CZRI_RULE_CLASS_INCORRECT');
567 t_disposition(i) := CZRI_DISPOSITION_REJECT;
569 ELSIF (t_config_engine_type(i)='F' AND t_rule_class(i) IS NULL AND t_class_seq(i) IS NOT NULL) THEN
570 t_message(i) := cz_utils.get_text('CZRI_CLASS_SEQ_INCORRECT');
571 t_disposition(i) := CZRI_DISPOSITION_REJECT;
572 END IF;
573
574
575 -- Bug9467066 Validating Rule Class against Master data and Class Sequence combinations for FCE Rule Import
576 IF (t_config_engine_type(i)='F' AND t_rule_class(i) IS NOT NULL)THEN
577 BEGIN
578
579 BEGIN
580 SELECT cz_rule_class_lkv.data_value
581 INTO t_data_value
582 FROM cz_rule_class_lkv
583 WHERE cz_rule_class_lkv.data_value=t_rule_class(i);
584 EXCEPTION
585 WHEN NO_DATA_FOUND THEN
586 t_message(i) := cz_utils.get_text('CZRI_RULE_CLASS_INCORRECT');
587 t_disposition(i) := CZRI_DISPOSITION_REJECT;
588 END;
589
590
591 IF((t_data_value='1')OR (t_data_value='2')) THEN
592 IF(t_class_seq(i) IS NULL) THEN
593 t_message(i) := cz_utils.get_text('CZRI_CLASS_SEQ_INCORRECT');
594 t_disposition(i) := CZRI_DISPOSITION_REJECT;
595 ELSE
596 BEGIN
597 SELECT class_seq
598 INTO v_rule_class_chk
599 FROM cz_rules
600 WHERE devl_project_id = t_devl_project_id(i)
601 AND rule_class= t_rule_class(i)
602 AND class_seq = t_class_seq(i)
603 AND deleted_flag = CZRI_FLAG_NOT_DELETED;
604
605 IF(v_rule_class_chk IS NOT NULL) then
606 t_message(i) := cz_utils.get_text('CZRI_CLASS_SEQ_INCORRECT');
607 t_disposition(i) := CZRI_DISPOSITION_REJECT;
608 END IF;
609
610 EXCEPTION
611 WHEN NO_DATA_FOUND THEN
612 -- Valid Case, No record found with same model id, rule class and class sequence. do nothing.
613 null;
614 WHEN TOO_MANY_ROWS THEN
615 t_message(i) := cz_utils.get_text('CZRI_CLASS_SEQ_INCORRECT', 'MODELID', t_devl_project_id(i));
616 t_disposition(i) := CZRI_DISPOSITION_REJECT;
617 END;
618 END IF;
619
620 ELSIF(t_data_value='0' AND t_class_seq(i) IS NOT NULL ) THEN
621 t_message(i) := cz_utils.get_text('CZRI_CLASS_SEQ_INCORRECT', 'MODELID', t_devl_project_id(i));
622 t_disposition(i) := CZRI_DISPOSITION_REJECT;
623 END IF;
624 EXCEPTION
625 WHEN NO_DATA_FOUND THEN
626 t_message(i) := cz_utils.get_text('CZRI_CLASS_SEQ_INCORRECT', 'MODELID', t_devl_project_id(i));
627 t_disposition(i) := CZRI_DISPOSITION_REJECT;
628
629 WHEN TOO_MANY_ROWS THEN
630 t_message(i) := cz_utils.get_text('CZRI_CLASS_SEQ_INCORRECT', 'MODELID', t_devl_project_id(i));
631 t_disposition(i) := CZRI_DISPOSITION_REJECT;
632
633 END;
634 END IF;
635
636 IF(last_orig_sys_ref IS NOT NULL AND
637 last_orig_sys_ref = t_orig_sys_ref(i) AND
638 last_project_id = t_devl_project_id(i))THEN
639
640 --This is a duplicate record in the source data.
641
642 t_message(i) := cz_utils.get_text('CZRI_RLE_DUPLICATE', 'MODELID', last_project_id);
643 t_disposition(i) := CZRI_DISPOSITION_REJECT;
644 END IF;
645
646 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
647
648 --Resolve rule_id for existing record or generate a new one for a new record.
649
650 BEGIN
651 EXECUTE IMMEDIATE resolveRuleId INTO t_rule_id(i),nOnlRuleText,nOnlDevlProjId,nOnlOrigSysRef,nOnlRulefolderId,nOnlRuleType,nOnlName
652 USING t_devl_project_id(i), t_orig_sys_ref(i);
653
654 t_disposition(i) := CZRI_DISPOSITION_MODIFY;
655 EXCEPTION
656 WHEN NO_DATA_FOUND THEN
657 t_disposition(i) := CZRI_DISPOSITION_INSERT;
658 t_rule_id(i) := next_rule_id;
659 END;
660 BEGIN
661 SELECT 1
662 INTO v_error_flag
663 FROM cz_rule_folders
664 WHERE deleted_flag = CZRI_FLAG_NOT_DELETED
665 AND devl_project_id = t_devl_project_id(i)
666 AND name = t_name(i)
667 AND rule_folder_id <> t_rule_id(i)
668 AND parent_rule_folder_id = t_rule_folder_id(i);
669 EXCEPTION
670 WHEN NO_DATA_FOUND THEN
671 v_error_flag := 0;
672 WHEN TOO_MANY_ROWS THEN
673 v_error_flag := 1;
674 END;
675 -- Raise an error if similar rule name already exists in the project under a rule folder
676 IF(v_error_flag = 1)THEN
677 t_disposition(i) := CZRI_DISPOSITION_REJECT;
678 t_message(i) := cz_utils.get_text('CZRI_DUPL_RULE_IN_FOLDER','DEVL_PROJ_ID',t_devl_project_id(i),'RULE_FOLDER_ID',t_rule_folder_id(i),'RULE_NAME',t_name(i)); --Bug8580853
679 FND_FILE.PUT_LINE(FND_FILE.LOG, t_message(i));
680 END IF;
681 --Resolve unsatisfied_msg_id. --Bug9068095
682
683 IF(t_fsk_localized_text_2(i) IS NOT NULL)THEN
684
685 SELECT intl_text_id BULK COLLECT INTO l_intl_text_id
686 FROM cz_localized_texts
687 WHERE deleted_flag = CZRI_FLAG_NOT_DELETED
688 AND model_id = t_devl_project_id(i)
689 AND orig_sys_ref = t_fsk_localized_text_2(i);
690
691 IF(l_intl_text_id.COUNT > 0)THEN
692
693 t_unsatisfied_msg_id(i) := l_intl_text_id(1);
694
695 --All the records should have the same number of translations. Remember the number
696 --of translations for the first record and compare all other records to it.
697
701
698 IF(v_translations IS NULL)THEN
699
700 v_translations := l_intl_text_id.COUNT;
702 ELSIF(v_translations <> l_intl_text_id.COUNT)THEN
703
704 t_message(i) := cz_utils.get_text('CZRI_RLE_TRANSLATIONS', 'ACTUAL', l_intl_text_id.COUNT, 'EXPECTED', v_translations);
705 t_disposition(i) := CZRI_DISPOSITION_REJECT;
706 END IF;
707 ELSE
708 t_message(i) := cz_utils.get_text('CZRI_RLE_NOUNSATISFIED');
709 t_disposition(i) := CZRI_DISPOSITION_REJECT;
710 END IF;
711 END IF;
712
713 --Resolve reason_id.
714
715 IF(t_disposition(i) <> CZRI_DISPOSITION_REJECT)THEN
716
717 IF(t_fsk_localized_text_1(i) IS NOT NULL)THEN
718
719 SELECT intl_text_id BULK COLLECT INTO t_intl_text_id
720 FROM cz_localized_texts
721 WHERE deleted_flag = CZRI_FLAG_NOT_DELETED
722 AND model_id = t_devl_project_id(i)
723 AND orig_sys_ref = t_fsk_localized_text_1(i);
724
725 IF(t_intl_text_id.COUNT > 0)THEN
726
727 t_reason_id(i) := t_intl_text_id(1);
728
729 --All the records should have the same number of translations. Remember the number
730 --of translations for the first record and compare all other records to it.
731
732 IF(v_translations IS NULL)THEN
733
734 v_translations := t_intl_text_id.COUNT;
735
736 ELSIF(v_translations <> t_intl_text_id.COUNT)THEN
737
738 t_message(i) := cz_utils.get_text('CZRI_RLE_TRANSLATIONS', 'ACTUAL', t_intl_text_id.COUNT, 'EXPECTED', v_translations);
739 t_disposition(i) := CZRI_DISPOSITION_REJECT;
740 END IF;
741 ELSE
742
743 t_message(i) := cz_utils.get_text('CZRI_RLE_NOREASONID');
744 t_disposition(i) := CZRI_DISPOSITION_REJECT;
745 END IF;
746 END IF;
747 END IF;
748 END IF;
749
750 IF(t_disposition(i) <> CZRI_DISPOSITION_REJECT)THEN
751
752 last_orig_sys_ref := t_orig_sys_ref(i);
753 last_project_id := t_devl_project_id(i);
754 END IF;
755
756 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
757
758 p_errors := p_errors + 1;
759
760 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
761
762 --Update the already processed records here.
763
764
765 update_table_data(i);
766 COMMIT;
767 RAISE CZRI_ERR_MAXIMUM_ERRORS;
768 END IF;
769 END IF;
770
771
772 IF (t_disposition(i)=CZRI_DISPOSITION_MODIFY) THEN
773 If ((nOnlRuleText=t_rule_text(i)) AND (nOnlDevlProjId=t_devl_project_id(i))
774 AND (nOnlOrigSysRef=t_orig_sys_ref(i)) AND (nOnlRulefolderId=t_rule_folder_id(i))
775 AND (nOnlRuleType=t_rule_type(i)) AND (nOnlName=t_name(i)))THEN
776
777 t_disposition(i):=CZRI_DISPOSITION_NOCHANGE;
778 END IF ;
779 END IF;
780 END LOOP;
781
782 --Update all the records from memory here.
783
784
785 update_table_data(t_rowid.COUNT);
786 COMMIT;
787 END LOOP;
788
789 CLOSE c_rec;
790 EXCEPTION
791 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
792 CLOSE c_rec;
793 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
794 x_msg_count := 1;
795 report(x_msg_data, p_run_id, 'krs_rules', v_debug);
796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
797 RAISE CZRI_ERR_FATAL_ERROR;
798 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
799 CLOSE c_rec;
800 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
801 x_msg_count := 1;
802 report(x_msg_data, p_run_id, 'krs_rules', v_debug);
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804 RAISE CZRI_ERR_FATAL_ERROR;
805 END;
806 ---------------------------------------------------------------------------------------
807 PROCEDURE xfr_rules(p_api_version IN NUMBER,
808 p_run_id IN NUMBER,
809 p_maximum_errors IN PLS_INTEGER,
810 p_commit_size IN PLS_INTEGER,
811 p_errors IN OUT NOCOPY PLS_INTEGER,
812 x_return_status IN OUT NOCOPY VARCHAR2,
813 x_msg_count IN OUT NOCOPY NUMBER,
814 x_msg_data IN OUT NOCOPY VARCHAR2) IS
815
816 v_debug NUMBER := 8000;
817
818 --persistent_rule_id is taken care of by a trigger.
819
820 CURSOR c_rec IS
821 SELECT ROWID, rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
822 name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
823 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
824 effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
825 unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
826 presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
827 mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type,
828 disposition, message,
829 rule_class, class_seq,config_engine_type,accumulator_flag,top_level_constraint_flag --Bug9467066
830 FROM cz_imp_rules
831 WHERE run_id = p_run_id
832 AND rec_status = CZRI_RECSTATUS_KRS
833 AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY);
834
835 CURSOR c_rec_nochange IS
839 AND disposition = CZRI_DISPOSITION_NOCHANGE;
836 SELECT ROWID FROM cz_imp_rules
837 WHERE run_id = p_run_id
838 AND rec_status = CZRI_RECSTATUS_KRS
840
841
842 t_rowid table_of_rowid;
843 t_rule_id table_of_number;
844 t_reason_id table_of_number;
845 t_rule_folder_id table_of_number;
846 t_devl_project_id table_of_number;
847 t_invalid_flag table_of_varchar;
848 t_desc_text table_of_varchar;
849 t_name table_of_varchar;
850 t_rule_type table_of_number;
851 t_expr_rule_type table_of_number;
852 t_component_id table_of_number;
853 t_model_ref_expl_id table_of_number;
854 t_reason_type table_of_number;
855 t_disabled_flag table_of_varchar;
856 t_orig_sys_ref table_of_varchar;
857 t_deleted_flag table_of_varchar;
858 t_security_mask table_of_varchar;
859 t_checkout_user table_of_varchar;
860 t_last_update_login table_of_number;
861 t_effective_usage_mask table_of_varchar;
862 t_seq_nbr table_of_number;
863 t_effective_from table_of_date;
864 t_effective_until table_of_date;
865 t_effectivity_set_id table_of_number;
866 t_unsatisfied_msg_id table_of_number;
867 t_unsatisfied_msg_source table_of_varchar;
868 t_signature_id table_of_number;
869 t_template_primitive_flag table_of_varchar;
870 t_presentation_flag table_of_varchar;
871 t_template_token table_of_varchar;
872 t_rule_text table_of_clob;
873 t_notes table_of_clob;
874 t_class_name table_of_varchar;
875 t_instantiation_scope table_of_number;
876 t_mutable_flag table_of_varchar;
877 t_seeded_flag table_of_varchar;
878 t_ui_def_id table_of_number;
879 t_ui_page_id table_of_number;
880 t_ui_page_element_id table_of_number;
881 t_rule_folder_type table_of_number;
882 t_disposition table_of_varchar;
883 t_message table_of_varchar;
884 t_rule_class table_of_number; --Bug9467066
885 t_class_seq table_of_number; --Bug9467066
886 t_config_engine_type table_of_varchar; --Bug9467066
887 t_accumulator_flag table_of_varchar; --Bug9467066
888 t_top_level_constraint_flag table_of_varchar; --Bug9467066
889
890 ---------------------------------------------------------------------------------------
891 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
892 BEGIN
893
894 --We updating rec_status to XFR, not OK, because the rules still have to be parsed.
895 --When parsed successfully, the status will be changed to OK.
896
897 FORALL i IN 1..p_upper_limit
898 UPDATE cz_imp_rules SET
899 message = t_message(i),
900 rec_status = CZRI_RECSTATUS_XFR,
901 disposition = t_disposition(i)
902 WHERE ROWID = t_rowid(i);
903 END;
904 ---------------------------------------------------------------------------------------
905
906 PROCEDURE update_table_data_nochange(p_upper_limit IN PLS_INTEGER) IS -- Meant to change the rec_status of no change records to 'OK'
907 BEGIN
908
909 FORALL i IN 1..p_upper_limit
910 UPDATE cz_imp_rules SET
911 rec_status = CZRI_RECSTATUS_OK
912 WHERE ROWID = t_rowid(i) ;
913 END;
914 ---------------------------------------------------------------------------------------
915 FUNCTION insert_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
916
917 t_rule_id table_of_number;
918 t_reason_id table_of_number;
919 t_rule_folder_id table_of_number;
920 t_devl_project_id table_of_number;
921 t_invalid_flag table_of_varchar;
922 t_desc_text table_of_varchar;
923 t_name table_of_varchar;
924 t_rule_type table_of_number;
925 t_expr_rule_type table_of_number;
926 t_component_id table_of_number;
927 t_model_ref_expl_id table_of_number;
928 t_reason_type table_of_number;
929 t_disabled_flag table_of_varchar;
930 t_orig_sys_ref table_of_varchar;
931 t_deleted_flag table_of_varchar;
932 t_security_mask table_of_varchar;
933 t_checkout_user table_of_varchar;
934 t_last_update_login table_of_number;
935 t_effective_usage_mask table_of_varchar;
936 t_seq_nbr table_of_number;
937 t_effective_from table_of_date;
938 t_effective_until table_of_date;
939 t_effectivity_set_id table_of_number;
940 t_unsatisfied_msg_id table_of_number;
941 t_unsatisfied_msg_source table_of_varchar;
942 t_signature_id table_of_number;
943 t_template_primitive_flag table_of_varchar;
944 t_presentation_flag table_of_varchar;
945 t_template_token table_of_varchar;
946 t_rule_text table_of_clob;
947 t_notes table_of_clob;
948 t_class_name table_of_varchar;
949 t_instantiation_scope table_of_number;
950 t_mutable_flag table_of_varchar;
951 t_seeded_flag table_of_varchar;
952 t_ui_def_id table_of_number;
953 t_ui_page_id table_of_number;
954 t_ui_page_element_id table_of_number;
955 t_rule_folder_type table_of_number;
959 t_accumulator_flag table_of_varchar; --Bug9467066
956 t_rule_class table_of_number; --Bug9467066
957 t_class_seq table_of_number; --Bug9467066
958 t_config_engine_type table_of_varchar; --Bug9467066
960 t_top_level_constraint_flag table_of_varchar; --Bug9467066
961
962 v_index PLS_INTEGER := 1;
963 BEGIN
964
965 FOR i IN 1..p_upper_limit LOOP
966
967 IF(xfr_rules.t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
968
969 t_rule_id(v_index) := xfr_rules.t_rule_id(i);
970 t_reason_id(v_index) := xfr_rules.t_reason_id(i);
971 t_rule_folder_id(v_index) := xfr_rules.t_rule_folder_id(i);
972 t_devl_project_id(v_index) := xfr_rules.t_devl_project_id(i);
973 t_invalid_flag(v_index) := xfr_rules.t_invalid_flag(i);
974 t_desc_text(v_index) := xfr_rules.t_desc_text(i);
975 t_name(v_index) := xfr_rules.t_name(i);
976 t_rule_type(v_index) := xfr_rules.t_rule_type(i);
977 t_expr_rule_type(v_index) := xfr_rules.t_expr_rule_type(i);
978 t_component_id(v_index) := xfr_rules.t_component_id(i);
979 t_model_ref_expl_id(v_index) := xfr_rules.t_model_ref_expl_id(i);
980 t_reason_type(v_index) := xfr_rules.t_reason_type(i);
981 t_disabled_flag(v_index) := xfr_rules.t_disabled_flag(i);
982 t_orig_sys_ref(v_index) := xfr_rules.t_orig_sys_ref(i);
983 t_deleted_flag(v_index) := xfr_rules.t_deleted_flag(i);
984 t_security_mask(v_index) := xfr_rules.t_security_mask(i);
985 t_checkout_user(v_index) := xfr_rules.t_checkout_user(i);
986 t_last_update_login(v_index) := xfr_rules.t_last_update_login(i);
987 t_effective_usage_mask(v_index) := xfr_rules.t_effective_usage_mask(i);
988 t_seq_nbr(v_index) := xfr_rules.t_seq_nbr(i);
989 t_effective_from(v_index) := xfr_rules.t_effective_from(i);
990 t_effective_until(v_index) := xfr_rules.t_effective_until(i);
991 t_effectivity_set_id(v_index) := xfr_rules.t_effectivity_set_id(i);
992 t_unsatisfied_msg_id(v_index) := xfr_rules.t_unsatisfied_msg_id(i);
993 t_unsatisfied_msg_source(v_index) := xfr_rules.t_unsatisfied_msg_source(i);
994 t_signature_id(v_index) := xfr_rules.t_signature_id(i);
995 t_template_primitive_flag(v_index) := xfr_rules.t_template_primitive_flag(i);
996 t_presentation_flag(v_index) := xfr_rules.t_presentation_flag(i);
997 t_template_token(v_index) := xfr_rules.t_template_token(i);
998 t_rule_text(v_index) := xfr_rules.t_rule_text(i);
999 t_notes(v_index) := xfr_rules.t_notes(i);
1000 t_class_name(v_index) := xfr_rules.t_class_name(i);
1001 t_instantiation_scope(v_index) := xfr_rules.t_instantiation_scope(i);
1002 t_mutable_flag(v_index) := xfr_rules.t_mutable_flag(i);
1003 t_seeded_flag(v_index) := xfr_rules.t_seeded_flag(i);
1004 t_ui_def_id(v_index) := xfr_rules.t_ui_def_id(i);
1005 t_ui_page_id(v_index) := xfr_rules.t_ui_page_id(i);
1006 t_ui_page_element_id(v_index) := xfr_rules.t_ui_page_element_id(i);
1007
1008 t_rule_class(v_index) := xfr_rules.t_rule_class(i); -- Bug9467066
1009 t_class_seq(v_index) := xfr_rules.t_class_seq(i); --Bug9467066
1010 t_config_engine_type(v_index) := xfr_rules.t_config_engine_type(i); --Bug9467066
1011 t_accumulator_flag(v_index) := xfr_rules.t_accumulator_flag(i); --Bug9467066
1012 t_top_level_constraint_flag(v_index) := xfr_rules.t_top_level_constraint_flag(i); --Bug9467066
1013
1014 v_index := v_index + 1;
1015 END IF;
1016 END LOOP;
1017
1018 --FORALL i IN 1..t_rule_id.COUNT does not work in 8i because rule_text and noted are CLOB columns.
1019
1020 FOR i IN 1..t_rule_id.COUNT LOOP
1021
1022 INSERT INTO cz_rules
1023 (rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
1024 name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
1025 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
1026 effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
1027 unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
1028 presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
1029 mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type,
1030 rule_class, class_seq,config_engine_type,accumulator_flag,top_level_constraint_flag) --Bug9467066
1031 VALUES
1032 (t_rule_id(i), t_reason_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_invalid_flag(i),
1033 t_desc_text(i), t_name(i), t_rule_type(i), t_expr_rule_type(i), t_component_id(i),
1034 t_model_ref_expl_id(i), t_reason_type(i), t_disabled_flag(i), t_orig_sys_ref(i), t_deleted_flag(i),
1035 t_security_mask(i), t_checkout_user(i), t_last_update_login(i), t_effective_usage_mask(i),
1036 t_seq_nbr(i), t_effective_from(i), t_effective_until(i), t_effectivity_set_id(i),
1037 t_unsatisfied_msg_id(i), t_unsatisfied_msg_source(i), t_signature_id(i), t_template_primitive_flag(i),
1038 t_presentation_flag(i), t_template_token(i), t_rule_text(i), t_notes(i), t_class_name(i),
1039 t_instantiation_scope(i), t_mutable_flag(i), t_seeded_flag(i), t_ui_def_id(i), t_ui_page_id(i),
1040 t_ui_page_element_id(i), t_rule_folder_type(i),
1041 t_rule_class(i), t_class_seq(i),t_config_engine_type(i),t_accumulator_flag(i),t_top_level_constraint_flag(i)); --Bug9467066
1042 END LOOP;
1043
1044 FORALL i IN 1..t_rule_folder_id.COUNT
1045 INSERT INTO cz_rule_folders
1046 (rule_folder_id, parent_rule_folder_id, devl_project_id, desc_text, name, object_type, folder_type,
1047 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
1051 DECODE(t_rule_type(i), CZRI_TYPE_COMPANION_RULE, CZRI_FOLDER_TYPE_CX, CZRI_FOLDER_TYPE_RULE),
1048 effective_usage_mask, tree_seq, effective_from, effective_until, effectivity_set_id)
1049 VALUES
1050 (t_rule_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_desc_text(i), t_name(i),
1052 t_rule_folder_type(i), t_disabled_flag(i), t_orig_sys_ref(i), t_deleted_flag(i),
1053 t_security_mask(i), t_checkout_user(i), t_last_update_login(i), t_effective_usage_mask(i),
1054 t_seq_nbr(i), t_effective_from(i), t_effective_until(i), t_effectivity_set_id(i));
1055
1056 RETURN TRUE;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 RETURN FALSE;
1061 END insert_online_data;
1062 ---------------------------------------------------------------------------------------
1063 FUNCTION update_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
1064
1065 t_rule_id table_of_number;
1066 t_reason_id table_of_number;
1067 t_rule_folder_id table_of_number;
1068 t_devl_project_id table_of_number;
1069 t_invalid_flag table_of_varchar;
1070 t_desc_text table_of_varchar;
1071 t_name table_of_varchar;
1072 t_rule_type table_of_number;
1073 t_expr_rule_type table_of_number;
1074 t_component_id table_of_number;
1075 t_model_ref_expl_id table_of_number;
1076 t_reason_type table_of_number;
1077 t_disabled_flag table_of_varchar;
1078 t_orig_sys_ref table_of_varchar;
1079 t_deleted_flag table_of_varchar;
1080 t_security_mask table_of_varchar;
1081 t_checkout_user table_of_varchar;
1082 t_last_update_login table_of_number;
1083 t_effective_usage_mask table_of_varchar;
1084 t_seq_nbr table_of_number;
1085 t_effective_from table_of_date;
1086 t_effective_until table_of_date;
1087 t_effectivity_set_id table_of_number;
1088 t_unsatisfied_msg_id table_of_number;
1089 t_unsatisfied_msg_source table_of_varchar;
1090 t_signature_id table_of_number;
1091 t_template_primitive_flag table_of_varchar;
1092 t_presentation_flag table_of_varchar;
1093 t_template_token table_of_varchar;
1094 t_rule_text table_of_clob;
1095 t_notes table_of_clob;
1096 t_class_name table_of_varchar;
1097 t_instantiation_scope table_of_number;
1098 t_mutable_flag table_of_varchar;
1099 t_seeded_flag table_of_varchar;
1100 t_ui_def_id table_of_number;
1101 t_ui_page_id table_of_number;
1102 t_ui_page_element_id table_of_number;
1103 t_rule_folder_type table_of_number;
1104
1105 v_index PLS_INTEGER := 1;
1106 t_rule_class table_of_number; --Bug9467066
1107 t_class_seq table_of_number; --Bug9467066
1108 t_config_engine_type table_of_varchar; --Bug9467066
1109 t_accumulator_flag table_of_varchar; --Bug9467066
1110 t_top_level_constraint_flag table_of_varchar; --Bug9467066
1111 BEGIN
1112
1113 FOR i IN 1..p_upper_limit LOOP
1114
1115 IF(xfr_rules.t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
1116
1117 t_rule_id(v_index) := xfr_rules.t_rule_id(i);
1118 t_reason_id(v_index) := xfr_rules.t_reason_id(i);
1119 t_rule_folder_id(v_index) := xfr_rules.t_rule_folder_id(i);
1120 t_devl_project_id(v_index) := xfr_rules.t_devl_project_id(i);
1121 t_invalid_flag(v_index) := xfr_rules.t_invalid_flag(i);
1122 t_desc_text(v_index) := xfr_rules.t_desc_text(i);
1123 t_name(v_index) := xfr_rules.t_name(i);
1124 t_rule_type(v_index) := xfr_rules.t_rule_type(i);
1125 t_expr_rule_type(v_index) := xfr_rules.t_expr_rule_type(i);
1126 t_component_id(v_index) := xfr_rules.t_component_id(i);
1127 t_model_ref_expl_id(v_index) := xfr_rules.t_model_ref_expl_id(i);
1128 t_reason_type(v_index) := xfr_rules.t_reason_type(i);
1129 t_disabled_flag(v_index) := xfr_rules.t_disabled_flag(i);
1130 t_orig_sys_ref(v_index) := xfr_rules.t_orig_sys_ref(i);
1131 t_deleted_flag(v_index) := xfr_rules.t_deleted_flag(i);
1132 t_security_mask(v_index) := xfr_rules.t_security_mask(i);
1133 t_checkout_user(v_index) := xfr_rules.t_checkout_user(i);
1134 t_last_update_login(v_index) := xfr_rules.t_last_update_login(i);
1135 t_effective_usage_mask(v_index) := xfr_rules.t_effective_usage_mask(i);
1136 t_seq_nbr(v_index) := xfr_rules.t_seq_nbr(i);
1137 t_effective_from(v_index) := xfr_rules.t_effective_from(i);
1138 t_effective_until(v_index) := xfr_rules.t_effective_until(i);
1139 t_effectivity_set_id(v_index) := xfr_rules.t_effectivity_set_id(i);
1140 t_unsatisfied_msg_id(v_index) := xfr_rules.t_unsatisfied_msg_id(i);
1141 t_unsatisfied_msg_source(v_index) := xfr_rules.t_unsatisfied_msg_source(i);
1142 t_signature_id(v_index) := xfr_rules.t_signature_id(i);
1143 t_template_primitive_flag(v_index) := xfr_rules.t_template_primitive_flag(i);
1144 t_presentation_flag(v_index) := xfr_rules.t_presentation_flag(i);
1145 t_template_token(v_index) := xfr_rules.t_template_token(i);
1146 t_rule_text(v_index) := xfr_rules.t_rule_text(i);
1147 t_notes(v_index) := xfr_rules.t_notes(i);
1148 t_class_name(v_index) := xfr_rules.t_class_name(i);
1149 t_instantiation_scope(v_index) := xfr_rules.t_instantiation_scope(i);
1150 t_mutable_flag(v_index) := xfr_rules.t_mutable_flag(i);
1151 t_seeded_flag(v_index) := xfr_rules.t_seeded_flag(i);
1152 t_ui_def_id(v_index) := xfr_rules.t_ui_def_id(i);
1153 t_ui_page_id(v_index) := xfr_rules.t_ui_page_id(i);
1157 t_class_seq(v_index) := xfr_rules.t_class_seq(i); --Bug9467066
1154 t_ui_page_element_id(v_index) := xfr_rules.t_ui_page_element_id(i);
1155 t_rule_folder_type(v_index) :=xfr_rules.t_rule_folder_type(i);
1156 t_rule_class(v_index) := xfr_rules.t_rule_class(i); --Bug9467066
1158 t_config_engine_type(v_index) := xfr_rules.t_config_engine_type(i); --Bug9467066
1159 t_accumulator_flag(v_index) := xfr_rules.t_accumulator_flag(i); --Bug9467066
1160 t_top_level_constraint_flag(v_index) := xfr_rules.t_top_level_constraint_flag(i); --Bug9467066
1161
1162 v_index := v_index + 1;
1163 END IF;
1164 END LOOP;
1165
1166 --FORALL i IN 1..t_rule_id.COUNT does not work in 8i because rule_text and noted are CLOB columns.
1167
1168 FOR i IN 1..t_rule_id.COUNT LOOP
1169
1170 -- Bug 9793987 , Need to retain the original Reason id /Intl text id, if no localized messages are imported in this run.
1171
1172 UPDATE cz_rules SET
1173 reason_id = NVL(t_reason_id(i), reason_id) ,
1174 rule_folder_id = t_rule_folder_id(i),
1175 devl_project_id = t_devl_project_id(i),
1176 invalid_flag = t_invalid_flag(i),
1177 desc_text = t_desc_text(i),
1178 name = t_name(i),
1179 rule_type = t_rule_type(i),
1180 expr_rule_type = t_expr_rule_type(i),
1181 component_id = t_component_id(i),
1182 model_ref_expl_id = t_model_ref_expl_id(i),
1183 reason_type = t_reason_type(i),
1184 disabled_flag = t_disabled_flag(i),
1185 orig_sys_ref = t_orig_sys_ref(i),
1186 deleted_flag = t_deleted_flag(i),
1187 security_mask = t_security_mask(i),
1188 checkout_user = t_checkout_user(i),
1189 last_update_login = t_last_update_login(i),
1190 effective_usage_mask = t_effective_usage_mask(i),
1191 seq_nbr = t_seq_nbr(i),
1192 effective_from = t_effective_from(i),
1193 effective_until = t_effective_until(i),
1194 effectivity_set_id = t_effectivity_set_id(i),
1195 unsatisfied_msg_id = t_unsatisfied_msg_id(i),
1196 unsatisfied_msg_source = t_unsatisfied_msg_source(i),
1197 signature_id = t_signature_id(i),
1198 template_primitive_flag = t_template_primitive_flag(i),
1199 presentation_flag = t_presentation_flag(i),
1200 template_token = t_template_token(i),
1201 rule_text = t_rule_text(i),
1202 notes = t_notes(i),
1203 class_name = t_class_name(i),
1204 instantiation_scope = t_instantiation_scope(i),
1205 mutable_flag = t_mutable_flag(i),
1206 seeded_flag = t_seeded_flag(i),
1207 ui_def_id = t_ui_def_id(i),
1208 ui_page_id = t_ui_page_id(i),
1209 ui_page_element_id = t_ui_page_element_id(i),
1210 rule_folder_type = t_rule_folder_type(i),
1211 rule_class = t_rule_class(i), --Bug9467066
1212 class_seq = t_class_seq(i), --Bug9467066
1213 config_engine_type = t_config_engine_type(i), --Bug9467066
1214 accumulator_flag = t_accumulator_flag(i), --Bug9467066
1215 top_level_constraint_flag = t_top_level_constraint_flag(i) --Bug9467066
1216 WHERE rule_id = t_rule_id(i);
1217 END LOOP;
1218
1219 FORALL i IN 1..t_rule_id.COUNT
1220 UPDATE cz_rule_folders SET
1221 parent_rule_folder_id = t_rule_folder_id(i),
1222 desc_text = t_desc_text(i),
1223 name = t_name(i),
1224 folder_type = t_rule_folder_type(i),
1225 tree_seq = t_seq_nbr(i),
1226 disabled_flag = t_disabled_flag(i),
1227 deleted_flag = t_deleted_flag(i),
1228 security_mask = t_security_mask(i),
1229 checkout_user = t_checkout_user(i),
1230 last_update_login = t_last_update_login(i),
1231 effective_usage_mask = t_effective_usage_mask(i),
1232 effective_from = t_effective_from(i),
1233 effective_until = t_effective_until(i),
1234 effectivity_set_id = t_effectivity_set_id(i)
1235 WHERE rule_folder_id = t_rule_id(i)
1236 AND object_type = CZRI_FOLDER_TYPE_RULE;
1237
1238 RETURN TRUE;
1239
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 RETURN FALSE;
1243 END update_online_data;
1244 ---------------------------------------------------------------------------------------
1245 BEGIN
1246 OPEN c_rec;
1247
1248
1249
1250 LOOP
1251
1252 t_rowid.DELETE;
1253 t_rule_id.DELETE;
1254 t_reason_id.DELETE;
1255 t_rule_folder_id.DELETE;
1256 t_devl_project_id.DELETE;
1257 t_invalid_flag.DELETE;
1258 t_desc_text.DELETE;
1259 t_name.DELETE;
1260 t_rule_type.DELETE;
1261 t_expr_rule_type.DELETE;
1262 t_component_id.DELETE;
1263 t_model_ref_expl_id.DELETE;
1264 t_reason_type.DELETE;
1265 t_disabled_flag.DELETE;
1266 t_orig_sys_ref.DELETE;
1267 t_deleted_flag.DELETE;
1268 t_security_mask.DELETE;
1269 t_checkout_user.DELETE;
1270 t_last_update_login.DELETE;
1271 t_effective_usage_mask.DELETE;
1272 t_seq_nbr.DELETE;
1273 t_effective_from.DELETE;
1274 t_effective_until.DELETE;
1275 t_effectivity_set_id.DELETE;
1276 t_unsatisfied_msg_id.DELETE;
1277 t_unsatisfied_msg_source.DELETE;
1278 t_signature_id.DELETE;
1279 t_template_primitive_flag.DELETE;
1280 t_presentation_flag.DELETE;
1281 t_template_token.DELETE;
1282 t_rule_text.DELETE;
1283 t_notes.DELETE;
1284 t_class_name.DELETE;
1288 t_ui_def_id.DELETE;
1285 t_instantiation_scope.DELETE;
1286 t_mutable_flag.DELETE;
1287 t_seeded_flag.DELETE;
1289 t_ui_page_id.DELETE;
1290 t_ui_page_element_id.DELETE;
1291 t_rule_folder_type.DELETE;
1292 t_disposition.DELETE;
1293 t_message.DELETE;
1294 t_rule_class.DELETE; --Bug9467066
1295 t_class_seq.DELETE; --Bug9467066
1296 t_accumulator_flag.DELETE; --Bug9467066
1297 t_top_level_constraint_flag.DELETE; --Bug9467066
1298 t_config_engine_type.DELETE; --Bug9467066
1299 FETCH c_rec BULK COLLECT INTO
1300 t_rowid, t_rule_id, t_reason_id, t_rule_folder_id, t_devl_project_id, t_invalid_flag, t_desc_text, t_name,
1301 t_rule_type, t_expr_rule_type, t_component_id, t_model_ref_expl_id, t_reason_type, t_disabled_flag,
1302 t_orig_sys_ref, t_deleted_flag, t_security_mask, t_checkout_user, t_last_update_login, t_effective_usage_mask,
1303 t_seq_nbr, t_effective_from, t_effective_until, t_effectivity_set_id, t_unsatisfied_msg_id,
1304 t_unsatisfied_msg_source, t_signature_id, t_template_primitive_flag, t_presentation_flag, t_template_token,
1305 t_rule_text, t_notes, t_class_name, t_instantiation_scope, t_mutable_flag, t_seeded_flag, t_ui_def_id,
1306 t_ui_page_id, t_ui_page_element_id, t_rule_folder_type, t_disposition, t_message,
1307 t_rule_class, t_class_seq,t_config_engine_type, t_accumulator_flag, t_top_level_constraint_flag --Bug9467066
1308 LIMIT p_commit_size;
1309 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
1310
1311 FOR i IN 1..t_rowid.COUNT LOOP
1312
1313 t_message(i) := NULL;
1314 END LOOP;
1315
1316 IF(NOT insert_online_data(t_rowid.COUNT))THEN
1317 --Bug9467066
1318 -- SELECT cz_devl_projects.config_engine_type
1319 -- INTO t_config_engine_type
1320 -- FROM cz_devl_projects,cz_imp_rules
1321 -- WHERE cz_imp_rules.devl_project_id = cz_devl_projects.devl_project_id
1322 -- AND cz_imp_rules.run_id = p_run_id;
1323 --Bug9467066
1324 FOR i IN 1..t_rowid.COUNT LOOP
1325
1326 IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
1327
1328 BEGIN
1329
1330 SAVEPOINT insert_rule_record;
1331 INSERT INTO cz_rules
1332 (rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
1333 name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
1334 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
1335 effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
1336 unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
1337 presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
1338 mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type,
1339 rule_class, class_seq,config_engine_type,accumulator_flag, top_level_constraint_flag) --Bug9467066
1340 VALUES
1341 (t_rule_id(i), t_reason_id(i), t_rule_folder_id(i), t_devl_project_id(i),
1342 t_invalid_flag(i), t_desc_text(i), t_name(i), t_rule_type(i), t_expr_rule_type(i),
1343 t_component_id(i), t_model_ref_expl_id(i), t_reason_type(i), t_disabled_flag(i),
1344 t_orig_sys_ref(i), t_deleted_flag(i), t_security_mask(i), t_checkout_user(i),
1345 t_last_update_login(i), t_effective_usage_mask(i), t_seq_nbr(i), t_effective_from(i),
1346 t_effective_until(i), t_effectivity_set_id(i), t_unsatisfied_msg_id(i),
1347 t_unsatisfied_msg_source(i), t_signature_id(i), t_template_primitive_flag(i),
1348 t_presentation_flag(i), t_template_token(i), t_rule_text(i), t_notes(i),
1349 t_class_name(i), t_instantiation_scope(i), t_mutable_flag(i), t_seeded_flag(i),
1350 t_ui_def_id(i), t_ui_page_id(i), t_ui_page_element_id(i), t_rule_folder_type(i),
1351 t_rule_class(i), t_class_seq(i),t_config_engine_type(i),t_accumulator_flag(i), t_top_level_constraint_flag(i)); --Bug9467066
1352 INSERT INTO cz_rule_folders
1353 (rule_folder_id, parent_rule_folder_id, devl_project_id, desc_text, name, object_type, folder_type,
1354 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
1355 effective_usage_mask, tree_seq, effective_from, effective_until, effectivity_set_id)
1356 VALUES
1357 (t_rule_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_desc_text(i), t_name(i),
1358 DECODE(t_rule_type(i), CZRI_TYPE_COMPANION_RULE, CZRI_FOLDER_TYPE_CX, CZRI_FOLDER_TYPE_RULE),
1359 t_rule_folder_type(i), t_disabled_flag(i), t_orig_sys_ref(i),
1360 t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_last_update_login(i),
1361 t_effective_usage_mask(i), t_seq_nbr(i), t_effective_from(i), t_effective_until(i),
1362 t_effectivity_set_id(i));
1363
1364 EXCEPTION
1365 WHEN OTHERS THEN
1366 t_message(i) := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1367 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1368 ROLLBACK TO insert_rule_record;
1369 END;
1370 END IF;
1371
1372 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1373
1374 p_errors := p_errors + 1;
1375
1376 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1377
1378 --Update the already processed records here.
1379
1380 update_table_data(i);
1381 COMMIT;
1382 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1383 END IF;
1384 END IF;
1385 END LOOP;
1386 END IF;
1387
1388 IF(NOT update_online_data(t_rowid.COUNT))THEN
1389
1390 FOR i IN 1..t_rowid.COUNT LOOP
1394 BEGIN
1391
1392 IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
1393
1395
1396 SAVEPOINT update_rule_record;
1397
1398 -- Bug 9793987 , Need to retain the original Reason id /Intl text id, if no localized messages are imported in this run.
1399 UPDATE cz_rules SET
1400 reason_id = NVL(t_reason_id(i), reason_id) ,
1401 rule_folder_id = t_rule_folder_id(i),
1402 devl_project_id = t_devl_project_id(i),
1403 invalid_flag = t_invalid_flag(i),
1404 desc_text = t_desc_text(i),
1405 name = t_name(i),
1406 rule_type = t_rule_type(i),
1407 expr_rule_type = t_expr_rule_type(i),
1408 component_id = t_component_id(i),
1409 model_ref_expl_id = t_model_ref_expl_id(i),
1410 reason_type = t_reason_type(i),
1411 disabled_flag = t_disabled_flag(i),
1412 orig_sys_ref = t_orig_sys_ref(i),
1413 deleted_flag = t_deleted_flag(i),
1414 security_mask = t_security_mask(i),
1415 checkout_user = t_checkout_user(i),
1416 last_update_login = t_last_update_login(i),
1417 effective_usage_mask = t_effective_usage_mask(i),
1418 seq_nbr = t_seq_nbr(i),
1419 effective_from = t_effective_from(i),
1420 effective_until = t_effective_until(i),
1421 effectivity_set_id = t_effectivity_set_id(i),
1422 unsatisfied_msg_id = t_unsatisfied_msg_id(i),
1423 unsatisfied_msg_source = t_unsatisfied_msg_source(i),
1424 signature_id = t_signature_id(i),
1425 template_primitive_flag = t_template_primitive_flag(i),
1426 presentation_flag = t_presentation_flag(i),
1427 template_token = t_template_token(i),
1428 rule_text = t_rule_text(i),
1429 notes = t_notes(i),
1430 class_name = t_class_name(i),
1431 instantiation_scope = t_instantiation_scope(i),
1432 mutable_flag = t_mutable_flag(i),
1433 seeded_flag = t_seeded_flag(i),
1434 ui_def_id = t_ui_def_id(i),
1435 ui_page_id = t_ui_page_id(i),
1436 ui_page_element_id = t_ui_page_element_id(i),
1437 rule_folder_type = t_rule_folder_type(i),
1438 rule_class = t_rule_class(i), --Bug9467066
1439 class_seq = t_class_seq(i), --Bug9467066
1440 config_engine_type = t_config_engine_type(i), --Bug9467066
1441 accumulator_flag = t_accumulator_flag(i), --Bug9467066
1442 top_level_constraint_flag = t_top_level_constraint_flag(i) --Bug9467066
1443 WHERE rule_id = t_rule_id(i);
1444
1445 UPDATE cz_rule_folders SET
1446 parent_rule_folder_id = t_rule_folder_id(i),
1447 desc_text = t_desc_text(i),
1448 name = t_name(i),
1449 folder_type = t_rule_folder_type(i),
1450 tree_seq = t_seq_nbr(i),
1451 disabled_flag = t_disabled_flag(i),
1452 deleted_flag = t_deleted_flag(i),
1453 security_mask = t_security_mask(i),
1454 checkout_user = t_checkout_user(i),
1455 last_update_login = t_last_update_login(i),
1456 effective_usage_mask = t_effective_usage_mask(i),
1457 effective_from = t_effective_from(i),
1458 effective_until = t_effective_until(i),
1459 effectivity_set_id = t_effectivity_set_id(i)
1460 WHERE rule_folder_id = t_rule_id(i)
1461 AND object_type = CZRI_FOLDER_TYPE_RULE;
1462
1463 EXCEPTION
1464 WHEN OTHERS THEN
1465 t_message(i) := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1466 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1467 ROLLBACK TO update_rule_record;
1468 END;
1469 END IF;
1470
1471 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1472
1473 p_errors := p_errors + 1;
1474
1475 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1476
1477 --Update the already processed records here.
1478
1479 update_table_data(i);
1480 COMMIT;
1481 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1482 END IF;
1483 END IF;
1484 END LOOP;
1485 END IF;
1486
1487
1488
1489
1490
1491 update_table_data(t_rowid.COUNT);
1492 COMMIT;
1493 END LOOP;
1494 OPEN c_rec_nochange;
1495 LOOP /*vigramak */
1496 t_rowid.DELETE;
1497
1498
1499 FETCH c_rec_nochange BULK COLLECT INTO t_rowid
1500 LIMIT p_commit_size;
1501 EXIT WHEN c_rec_nochange%NOTFOUND AND t_rowid.COUNT = 0;
1502
1503 FOR i IN 1..t_rowid.COUNT LOOP
1504 update_table_data_nochange(i);
1505 COMMIT;
1506
1507 END LOOP;
1508 END LOOP;
1509 CLOSE c_rec_nochange;
1510 CLOSE c_rec;
1511
1512 EXCEPTION
1513 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
1514 CLOSE c_rec;
1515 CLOSE c_rec_nochange;
1516 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
1517 x_msg_count := 1;
1518 report(x_msg_data, p_run_id, 'xfr_rules', v_debug);
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 RAISE CZRI_ERR_FATAL_ERROR;
1521 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1522 CLOSE c_rec;
1523 CLOSE c_rec_nochange;
1524 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1525 x_msg_count := 1;
1529 END;
1526 report(x_msg_data, p_run_id, 'xfr_rules', v_debug);
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1528 RAISE CZRI_ERR_FATAL_ERROR;
1530 ---------------------------------------------------------------------------------------
1531 PROCEDURE rpt_rules(p_api_version IN NUMBER,
1532 p_run_id IN NUMBER,
1533 x_return_status IN OUT NOCOPY VARCHAR2,
1534 x_msg_count IN OUT NOCOPY NUMBER,
1535 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1536
1537 v_debug NUMBER := 9000;
1538 BEGIN
1539 FOR c_stat IN (SELECT disposition, rec_status, COUNT(*) as records
1540 FROM cz_imp_rules
1541 WHERE run_id = p_run_id
1542 AND rec_status IS NOT NULL
1543 AND disposition IS NOT NULL
1544 GROUP BY disposition, rec_status) LOOP
1545
1546 INSERT INTO cz_xfr_run_results (run_id, imp_table, disposition, rec_status, records)
1547 VALUES (p_run_id, 'CZ_IMP_RULES', c_stat.disposition, c_stat.rec_status, c_stat.records);
1548 END LOOP;
1549
1550 COMMIT;
1551 EXCEPTION
1552 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1553 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1554 x_msg_count := 1;
1555 report(x_msg_data, p_run_id, 'rpt_rules', v_debug);
1556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 RAISE CZRI_ERR_REPORT_ERROR;
1558 END;
1559 ---------------------------------------------------------------------------------------
1560 PROCEDURE restat_rules(p_api_version IN NUMBER,
1561 p_run_id IN NUMBER,
1562 x_return_status IN OUT NOCOPY VARCHAR2,
1563 x_msg_count IN OUT NOCOPY NUMBER,
1564 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1565
1566 v_debug NUMBER := 11000;
1567 BEGIN
1568
1569 DELETE FROM cz_xfr_run_results WHERE run_id = p_run_id;
1570 rpt_rules(p_api_version, p_run_id, x_return_status, x_msg_count, x_msg_data);
1571 END;
1572 ---------------------------------------------------------------------------------------
1573 PROCEDURE import_rules(p_api_version IN NUMBER,
1574 p_run_id IN NUMBER,
1575 p_maximum_errors IN PLS_INTEGER,
1576 p_commit_size IN PLS_INTEGER,
1577 p_errors IN OUT NOCOPY PLS_INTEGER,
1578 x_return_status IN OUT NOCOPY VARCHAR2,
1579 x_msg_count IN OUT NOCOPY NUMBER,
1580 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1581 BEGIN
1582 cnd_rules(p_api_version,
1583 p_run_id,
1584 p_maximum_errors,
1585 p_commit_size,
1586 p_errors,
1587 x_return_status,
1588 x_msg_count,
1589 x_msg_data);
1590
1591 krs_rules(p_api_version,
1592 p_run_id,
1593 p_maximum_errors,
1594 p_commit_size,
1595 p_errors,
1596 x_return_status,
1597 x_msg_count,
1598 x_msg_data);
1599
1600 xfr_rules(p_api_version,
1601 p_run_id,
1602 p_maximum_errors,
1603 p_commit_size,
1604 p_errors,
1605 x_return_status,
1606 x_msg_count,
1607 x_msg_data);
1608
1609 process_dflt_localized_texts(p_api_version,
1610 p_run_id,
1611 p_maximum_errors,
1612 p_commit_size,
1613 p_errors,
1614 x_return_status,
1615 x_msg_count,
1616 x_msg_data);
1617
1618 rpt_rules(p_api_version,
1619 p_run_id,
1620 x_return_status,
1621 x_msg_count,
1622 x_msg_data);
1623
1624 EXCEPTION
1625 WHEN CZRI_ERR_REPORT_ERROR THEN
1626 RAISE CZRI_ERR_FATAL_ERROR;
1627 WHEN OTHERS THEN
1628 rpt_rules(p_api_version,
1629 p_run_id,
1630 x_return_status,
1631 x_msg_count,
1632 x_msg_data);
1633 RAISE;
1634 END;
1635 ---------------------------------------------------------------------------------------
1636 PROCEDURE cnd_localized_texts(p_api_version IN NUMBER,
1637 p_run_id IN NUMBER,
1638 p_maximum_errors IN PLS_INTEGER,
1639 p_commit_size IN PLS_INTEGER,
1640 p_errors IN OUT NOCOPY PLS_INTEGER,
1641 x_return_status IN OUT NOCOPY VARCHAR2,
1642 x_msg_count IN OUT NOCOPY NUMBER,
1643 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1644
1645 v_debug NUMBER := 2000;
1646
1647 CURSOR c_rec IS
1648 SELECT ROWID, model_id, message, orig_sys_ref, language, source_lang,
1649 seeded_flag, deleted_flag, disposition
1650 FROM cz_imp_localized_texts
1651 WHERE run_id = p_run_id
1652 AND rec_status IS NULL
1653 AND disposition IS NULL;
1654
1655 t_rowid table_of_rowid;
1656 t_model_id table_of_number;
1657 t_message table_of_varchar;
1658 t_orig_sys_ref table_of_varchar;
1659 t_language table_of_varchar;
1660 t_source_lang table_of_varchar;
1661 t_seeded_flag table_of_varchar;
1662 t_deleted_flag table_of_varchar;
1663 t_disposition table_of_varchar;
1664
1665 validateModel VARCHAR2(4000) :=
1666 'SELECT NULL ' ||
1667 ' FROM cz_rp_entries ' ||
1668 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
1672 h_ValidModel table_of_number_index_VC2;
1669 ' AND object_type = ''' || CZRI_REPOSITORY_PROJECT || ''' ' ||
1670 ' AND object_id = :1';
1671
1673 h_InvalidModel table_of_number_index_VC2;
1674 v_null NUMBER;
1675
1676 ---------------------------------------------------------------------------------------
1677 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
1678 BEGIN
1679
1680 FORALL i IN 1..p_upper_limit
1681 UPDATE cz_imp_localized_texts SET
1682 message = t_message(i),
1683 seeded_flag = t_seeded_flag(i),
1684 deleted_flag = t_deleted_flag(i),
1685 rec_status = CZRI_RECSTATUS_CND,
1686 disposition = t_disposition(i)
1687 WHERE ROWID = t_rowid(i);
1688 END;
1689 ---------------------------------------------------------------------------------------
1690 BEGIN
1691
1692 OPEN c_rec;
1693 LOOP
1694
1695 t_rowid.DELETE;
1696 t_model_id.DELETE;
1697 t_message.DELETE;
1698 t_orig_sys_ref.DELETE;
1699 t_language.DELETE;
1700 t_source_lang.DELETE;
1701 t_seeded_flag.DELETE;
1702 t_deleted_flag.DELETE;
1703 t_disposition.DELETE;
1704
1705 FETCH c_rec BULK COLLECT INTO
1706 t_rowid, t_model_id, t_message, t_orig_sys_ref, t_language, t_source_lang, t_seeded_flag, t_deleted_flag,
1707 t_disposition
1708 LIMIT p_commit_size;
1709 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
1710
1711 FOR i IN 1..t_rowid.COUNT LOOP
1712
1713 t_message(i) := NULL;
1714 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1715
1716 IF(t_model_id(i) IS NULL)THEN
1717
1718 t_message(i) := cz_utils.get_text('CZRI_TXT_NULLMODELID');
1719
1720 ELSIF(h_InvalidModel.EXISTS(t_model_id(i)))THEN
1721
1722 t_message(i) := cz_utils.get_text('CZRI_TXT_INVALIDMODEL');
1723
1724 ELSIF(t_orig_sys_ref(i) IS NULL)THEN
1725
1726 t_message(i) := cz_utils.get_text('CZRI_TXT_NULLORIGSYSREF');
1727
1728 ELSIF(t_language(i) IS NULL)THEN
1729
1730 t_message(i) := cz_utils.get_text('CZRI_TXT_NULLLANGUAGE');
1731
1732 ELSIF(t_source_lang(i) IS NULL)THEN
1733
1734 t_message(i) := cz_utils.get_text('CZRI_TXT_NULLSOURCELANG');
1735
1736 ELSE
1737
1738 t_disposition(i) := CZRI_DISPOSITION_PASSED;
1739 END IF;
1740
1741 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED AND (NOT h_ValidModel.EXISTS(t_model_id(i))))THEN
1742 BEGIN
1743
1744 EXECUTE IMMEDIATE validateModel INTO v_null USING t_model_id(i);
1745 h_ValidModel(t_model_id(i)) := 1;
1746
1747 EXCEPTION
1748 WHEN NO_DATA_FOUND THEN
1749 t_message(i) := cz_utils.get_text('CZRI_TXT_INVALIDMODEL');
1750 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1751 h_InvalidModel(t_model_id(i)) := 1;
1752 END;
1753 END IF;
1754
1755 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1756
1757 p_errors := p_errors + 1;
1758
1759 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1760
1761 --Update the already processed records here.
1762
1763 update_table_data(i);
1764 COMMIT;
1765 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1766 END IF;
1767 END IF;
1768
1769 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
1770
1771 --All the validations passed, condition the record.
1772 --The seeded flag is unconditionally set to '0'.
1773
1774 t_seeded_flag(i) := CZRI_FLAG_NOT_SEEDED;
1775 IF(t_deleted_flag(i) IS NULL)THEN t_deleted_flag(i) := CZRI_FLAG_NOT_DELETED; END IF;
1776 END IF;
1777 END LOOP;
1778
1779 --Update all the records from memory here.
1780
1781 update_table_data(t_rowid.COUNT);
1782 COMMIT;
1783 END LOOP;
1784
1785 CLOSE c_rec;
1786 EXCEPTION
1787 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
1788 CLOSE c_rec;
1789 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
1790 x_msg_count := 1;
1791 report(x_msg_data, p_run_id, 'cnd_localized_texts', v_debug);
1792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1793 RAISE CZRI_ERR_FATAL_ERROR;
1794 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1795 CLOSE c_rec;
1796 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1797 x_msg_count := 1;
1798 report(x_msg_data, p_run_id, 'cnd_localized_texts', v_debug);
1799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1800 RAISE CZRI_ERR_FATAL_ERROR;
1801 END;
1802 ---------------------------------------------------------------------------------------
1803 PROCEDURE krs_localized_texts(p_api_version IN NUMBER,
1804 p_run_id IN NUMBER,
1805 p_maximum_errors IN PLS_INTEGER,
1806 p_commit_size IN PLS_INTEGER,
1807 p_errors IN OUT NOCOPY PLS_INTEGER,
1808 x_return_status IN OUT NOCOPY VARCHAR2,
1809 x_msg_count IN OUT NOCOPY NUMBER,
1810 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1811
1812 v_debug NUMBER := 3000;
1813 nOnlIntlTextId CZ_IMP_LOCALIZED_TEXTS.INTL_TEXT_ID%TYPE;
1814 nOnlModelId CZ_IMP_LOCALIZED_TEXTS.MODEL_ID%TYPE;
1815 nOnlOrigSysRef CZ_IMP_LOCALIZED_TEXTS.ORIG_SYS_REF%TYPE;
1816 nOnlLang CZ_IMP_LOCALIZED_TEXTS.LANGUAGE%TYPE;
1817 nOnlLastUpdate CZ_IMP_LOCALIZED_TEXTS.LAST_UPDATE_LOGIN%TYPE;
1821
1818 nOnlDeletedFlag CZ_IMP_LOCALIZED_TEXTS.DELETED_FLAG%TYPE;
1819 nOnlSourceLang CZ_IMP_LOCALIZED_TEXTS.SOURCE_LANG%TYPE;
1820 nOnlLocalString CZ_IMP_LOCALIZED_TEXTS.LOCALIZED_STR%TYPE;
1822 CURSOR c_rec IS
1823 SELECT ROWID, intl_text_id, model_id, message, orig_sys_ref, language, rec_status, disposition,source_lang,localized_str
1824 FROM cz_imp_localized_texts
1825 WHERE run_id = p_run_id
1826 AND rec_status = CZRI_RECSTATUS_CND
1827 AND disposition = CZRI_DISPOSITION_PASSED
1828 ORDER BY model_id, orig_sys_ref, language;
1829
1830 t_rowid table_of_rowid;
1831 t_intl_text_id table_of_number;
1832 t_model_id table_of_number;
1833 t_message table_of_varchar;
1834 t_orig_sys_ref table_of_varchar;
1835 t_language table_of_varchar;
1836 t_rec_status table_of_varchar;
1837 t_disposition table_of_varchar;
1838 n_last_update_login table_of_number;
1839 n_deleted_flag table_of_varchar;
1840 n_source_lang table_of_varchar;
1841 n_local_string table_of_varchar;
1842
1843 resolveIntlTextId VARCHAR2(4000) :=
1844 'SELECT intl_text_id, model_id, orig_sys_ref, language,source_lang,localized_str ' ||
1845 ' FROM cz_localized_texts ' ||
1846 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
1847 ' AND model_id = :1 ' ||
1848 ' AND language = :2 ' ||
1849 ' AND orig_sys_ref = :3';
1850
1851 getIntlTextId VARCHAR2(4000) :=
1852 'SELECT intl_text_id ' ||
1853 ' FROM cz_localized_texts ' ||
1854 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
1855 ' AND model_id = :1 ' ||
1856 ' AND orig_sys_ref = :2 ' ||
1857 ' AND ROWNUM = 1';
1858
1859 last_orig_sys_ref cz_imp_localized_texts.orig_sys_ref%TYPE;
1860 last_model_id cz_imp_localized_texts.model_id%TYPE;
1861 last_language cz_imp_localized_texts.language%TYPE;
1862 last_intl_text_id cz_imp_localized_texts.intl_text_id%TYPE;
1863
1864 last_id_allocated NUMBER := NULL;
1865 next_id_to_use NUMBER := 0;
1866 id_increment NUMBER := CZRI_LOCALIZED_TEXTS_INC;
1867 ---------------------------------------------------------------------------------------
1868 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
1869 BEGIN
1870
1871 FORALL i IN 1..p_upper_limit
1872 UPDATE cz_imp_localized_texts SET
1873 intl_text_id = t_intl_text_id(i),
1874 message = t_message(i),
1875 rec_status = CZRI_RECSTATUS_KRS,
1876 disposition = t_disposition(i)
1877 WHERE ROWID = t_rowid(i);
1878 END;
1879
1880 FUNCTION next_intl_text_id RETURN NUMBER IS
1881 id_to_return NUMBER;
1882 BEGIN
1883
1884 IF((last_id_allocated IS NULL) OR
1885 (next_id_to_use = (NVL(last_id_allocated, 0) + id_increment)))THEN
1886
1887 SELECT cz_intl_texts_s.NEXTVAL INTO last_id_allocated FROM dual;
1888 next_id_to_use := last_id_allocated;
1889 END IF;
1890
1891 id_to_return := next_id_to_use;
1892 next_id_to_use := next_id_to_use + 1;
1893 RETURN id_to_return;
1894 END;
1895 ---------------------------------------------------------------------------------------
1896 BEGIN
1897
1898 OPEN c_rec;
1899 LOOP
1900
1901 t_rowid.DELETE;
1902 t_intl_text_id.DELETE;
1903 t_model_id.DELETE;
1904 t_message.DELETE;
1905 t_orig_sys_ref.DELETE;
1906 t_language.DELETE;
1907 t_rec_status.DELETE;
1908 t_disposition.DELETE;
1909 n_last_update_login.DELETE;
1910 n_deleted_flag.DELETE;
1911 n_source_lang.DELETE;
1912 n_local_string.DELETE;
1913
1914 FETCH c_rec BULK COLLECT INTO
1915 t_rowid, t_intl_text_id, t_model_id, t_message, t_orig_sys_ref, t_language, t_rec_status, t_disposition,n_source_lang,n_local_string
1916
1917 LIMIT p_commit_size;
1918 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
1919
1920 FOR i IN 1..t_rowid.COUNT LOOP
1921
1922 t_message(i) := NULL;
1923
1924 IF(last_orig_sys_ref IS NOT NULL AND
1925 last_orig_sys_ref = t_orig_sys_ref(i) AND
1926 last_model_id = t_model_id(i) AND
1927 last_language = t_language(i))THEN
1928
1929 --This is a duplicate record in the source data.
1930
1931 t_message(i) := cz_utils.get_text('CZRI_TXT_DUPLICATE', 'MODELID', last_model_id);
1932 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1933 END IF;
1934
1935 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
1936
1937 --Resolve intl_text_id for existing record or generate a new one for a new record.
1938
1939 BEGIN
1940 EXECUTE IMMEDIATE resolveIntlTextId INTO t_intl_text_id(i),nOnlModelId,nOnlOrigSysRef,nOnlLang,nOnlSourceLang,nOnlLocalString
1941 USING t_model_id(i), t_language(i), t_orig_sys_ref(i);
1942
1943 t_disposition(i) := CZRI_DISPOSITION_MODIFY;
1944 EXCEPTION
1945 WHEN NO_DATA_FOUND THEN
1946 t_disposition(i) := CZRI_DISPOSITION_INSERT;
1947
1948 --Bug #4053091 - need to share intl_text_id between all the records with same orig_sys_ref.
1949
1950 BEGIN
1951 EXECUTE IMMEDIATE getIntlTextId INTO t_intl_text_id(i)
1952 USING t_model_id(i), t_orig_sys_ref(i);
1953 EXCEPTION
1954 WHEN NO_DATA_FOUND THEN
1955 IF(last_orig_sys_ref IS NOT NULL AND last_orig_sys_ref = t_orig_sys_ref(i) AND
1956 last_model_id = t_model_id(i))THEN
1957
1958 t_intl_text_id(i) := last_intl_text_id;
1959 ELSE
1960
1964 END;
1961 t_intl_text_id(i) := next_intl_text_id;
1962 END IF;
1963 END;
1965 END IF;
1966
1967 IF(t_disposition(i) <> CZRI_DISPOSITION_REJECT)THEN
1968
1969 last_orig_sys_ref := t_orig_sys_ref(i);
1970 last_model_id := t_model_id(i);
1971 last_language := t_language(i);
1972 last_intl_text_id := t_intl_text_id(i);
1973 END IF;
1974
1975 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1976
1977 p_errors := p_errors + 1;
1978
1979 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1980
1981 --Update the already processed records here.
1982
1983
1984
1985 update_table_data(i);
1986 COMMIT;
1987 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1988 END IF;
1989 END IF;
1990
1991 IF(t_disposition(i)=CZRI_DISPOSITION_MODIFY) THEN
1992 If ((nOnlModelId=t_model_id(i)) AND (nOnlOrigSysRef=t_orig_sys_ref(i)) AND (nOnlLang=t_language(i))AND (nOnlSourceLang=n_source_lang(i))AND (nOnlLocalString=n_local_string(i))) THEN
1993 t_disposition(i):=CZRI_DISPOSITION_NOCHANGE;
1994 END IF;
1995 END IF;
1996
1997 END LOOP;
1998
1999 --Update all the records from memory here.
2000
2001
2002 update_table_data(t_rowid.COUNT);
2003 COMMIT;
2004 END LOOP;
2005
2006 CLOSE c_rec;
2007 EXCEPTION
2008 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
2009 CLOSE c_rec;
2010 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
2011 x_msg_count := 1;
2012 report(x_msg_data, p_run_id, 'krs_localized_texts', v_debug);
2013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2014 RAISE CZRI_ERR_FATAL_ERROR;
2015 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
2016 CLOSE c_rec;
2017 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2018 x_msg_count := 1;
2019 report(x_msg_data, p_run_id, 'krs_localized_texts', v_debug);
2020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2021 RAISE CZRI_ERR_FATAL_ERROR;
2022 END;
2023 ---------------------------------------------------------------------------------------
2024 PROCEDURE xfr_localized_texts(p_api_version IN NUMBER,
2025 p_run_id IN NUMBER,
2026 p_maximum_errors IN PLS_INTEGER,
2027 p_commit_size IN PLS_INTEGER,
2028 p_errors IN OUT NOCOPY PLS_INTEGER,
2029 x_return_status IN OUT NOCOPY VARCHAR2,
2030 x_msg_count IN OUT NOCOPY NUMBER,
2031 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2032
2033 v_debug NUMBER := 4000;
2034
2035 --persistent_intl_text_id is taken care of by a trigger.
2036
2037 CURSOR c_rec IS
2038 SELECT ROWID,last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
2039 checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag,
2040 disposition, message
2041 FROM cz_imp_localized_texts
2042 WHERE run_id = p_run_id
2043 AND rec_status = CZRI_RECSTATUS_KRS
2044 AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY);
2045
2046 CURSOR c_rec_nochange IS
2047 SELECT ROWID FROM cz_imp_localized_texts
2048 WHERE run_id = p_run_id
2049 AND rec_status = CZRI_RECSTATUS_KRS
2050 AND disposition =CZRI_DISPOSITION_NOCHANGE;
2051
2052 t_rowid table_of_rowid;
2053 t_last_update_login table_of_number;
2054 t_locale_id table_of_number;
2055 t_localized_str table_of_varchar;
2056 t_intl_text_id table_of_number;
2057 t_deleted_flag table_of_varchar;
2058 t_security_mask table_of_varchar;
2059 t_checkout_user table_of_varchar;
2060 t_orig_sys_ref table_of_varchar;
2061 t_language table_of_varchar;
2062 t_source_lang table_of_varchar;
2063 t_model_id table_of_number;
2064 t_seeded_flag table_of_varchar;
2065 t_disposition table_of_varchar;
2066 t_message table_of_varchar;
2067 ---------------------------------------------------------------------------------------
2068 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
2069 BEGIN
2070
2071 --We updating rec_status to OK, not XFR, because, unlike the rules which still have
2072 --to be parsed, import of localized texts is done.
2073
2074 FORALL i IN 1..p_upper_limit
2075 UPDATE cz_imp_localized_texts SET
2076 message = t_message(i),
2077 rec_status = CZRI_RECSTATUS_OK,
2078 disposition = t_disposition(i)
2079 WHERE ROWID = t_rowid(i);
2080 END;
2081 ---------------------------------------------------------------------------------------
2082
2083 PROCEDURE update_table_data_nochange(p_upper_limit IN PLS_INTEGER) IS
2084 BEGIN
2085
2086 FORALL i IN 1..p_upper_limit
2087 UPDATE cz_imp_localized_texts SET
2088 rec_status = CZRI_RECSTATUS_OK
2089 WHERE ROWID = t_rowid(i);
2090 END;
2091 ---------------------------------------------------------------------------------------
2092 FUNCTION insert_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
2093
2094 t_last_update_login table_of_number;
2095 t_locale_id table_of_number;
2096 t_localized_str table_of_varchar;
2097 t_intl_text_id table_of_number;
2098 t_deleted_flag table_of_varchar;
2099 t_security_mask table_of_varchar;
2100 t_checkout_user table_of_varchar;
2101 t_orig_sys_ref table_of_varchar;
2105 t_seeded_flag table_of_varchar;
2102 t_language table_of_varchar;
2103 t_source_lang table_of_varchar;
2104 t_model_id table_of_number;
2106
2107 v_index PLS_INTEGER := 1;
2108 BEGIN
2109
2110 FOR i IN 1..p_upper_limit LOOP
2111
2112 IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
2113
2114 t_last_update_login(v_index) := xfr_localized_texts.t_last_update_login(i);
2115 t_locale_id(v_index) := xfr_localized_texts.t_locale_id(i);
2116 t_localized_str(v_index) := xfr_localized_texts.t_localized_str(i);
2117 t_intl_text_id(v_index) := xfr_localized_texts.t_intl_text_id(i);
2118 t_deleted_flag(v_index) := xfr_localized_texts.t_deleted_flag(i);
2119 t_security_mask(v_index) := xfr_localized_texts.t_security_mask(i);
2120 t_checkout_user(v_index) := xfr_localized_texts.t_checkout_user(i);
2121 t_orig_sys_ref(v_index) := xfr_localized_texts.t_orig_sys_ref(i);
2122 t_language(v_index) := xfr_localized_texts.t_language(i);
2123 t_source_lang(v_index) := xfr_localized_texts.t_source_lang(i);
2124 t_model_id(v_index) := xfr_localized_texts.t_model_id(i);
2125 t_seeded_flag(v_index) := xfr_localized_texts.t_seeded_flag(i);
2126
2127 v_index := v_index + 1;
2128 END IF;
2129 END LOOP;
2130
2131 FORALL i IN 1..t_intl_text_id.COUNT
2132 INSERT INTO cz_localized_texts
2133 (last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
2134 checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag)
2135 VALUES
2136 (t_last_update_login(i), t_locale_id(i), t_localized_str(i), t_intl_text_id(i),
2137 t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_orig_sys_ref(i),
2138 t_language(i), t_source_lang(i), t_model_id(i), t_seeded_flag(i));
2139
2140 RETURN TRUE;
2141
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 RETURN FALSE;
2145 END;
2146 ---------------------------------------------------------------------------------------
2147 FUNCTION update_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
2148
2149 t_last_update_login table_of_number;
2150 t_locale_id table_of_number;
2151 t_localized_str table_of_varchar;
2152 t_intl_text_id table_of_number;
2153 t_deleted_flag table_of_varchar;
2154 t_security_mask table_of_varchar;
2155 t_checkout_user table_of_varchar;
2156 t_orig_sys_ref table_of_varchar;
2157 t_language table_of_varchar;
2158 t_source_lang table_of_varchar;
2159 t_model_id table_of_number;
2160 t_seeded_flag table_of_varchar;
2161
2162 v_index PLS_INTEGER := 1;
2163 BEGIN
2164
2165 FOR i IN 1..p_upper_limit LOOP
2166
2167 IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
2168
2169 t_last_update_login(v_index) := xfr_localized_texts.t_last_update_login(i);
2170 t_locale_id(v_index) := xfr_localized_texts.t_locale_id(i);
2171 t_localized_str(v_index) := xfr_localized_texts.t_localized_str(i);
2172 t_intl_text_id(v_index) := xfr_localized_texts.t_intl_text_id(i);
2173 t_deleted_flag(v_index) := xfr_localized_texts.t_deleted_flag(i);
2174 t_security_mask(v_index) := xfr_localized_texts.t_security_mask(i);
2175 t_checkout_user(v_index) := xfr_localized_texts.t_checkout_user(i);
2176 t_orig_sys_ref(v_index) := xfr_localized_texts.t_orig_sys_ref(i);
2177 t_language(v_index) := xfr_localized_texts.t_language(i);
2178 t_source_lang(v_index) := xfr_localized_texts.t_source_lang(i);
2179 t_model_id(v_index) := xfr_localized_texts.t_model_id(i);
2180 t_seeded_flag(v_index) := xfr_localized_texts.t_seeded_flag(i);
2181
2182 v_index := v_index + 1;
2183 END IF;
2184 END LOOP;
2185
2186 FORALL i IN 1..t_intl_text_id.COUNT
2187 UPDATE cz_localized_texts SET
2188 last_update_login = t_last_update_login(i),
2189 locale_id = t_locale_id(i),
2190 localized_str = t_localized_str(i),
2191 deleted_flag = t_deleted_flag(i),
2192 security_mask = t_security_mask(i),
2193 checkout_user = t_checkout_user(i),
2194 orig_sys_ref = t_orig_sys_ref(i),
2195 source_lang = t_source_lang(i),
2196 model_id = t_model_id(i),
2197 seeded_flag = t_seeded_flag(i)
2198 WHERE intl_text_id = t_intl_text_id(i)
2199 AND language = t_language(i);
2200
2201 RETURN TRUE;
2202
2203 EXCEPTION
2204 WHEN OTHERS THEN
2205 RETURN FALSE;
2206 END;
2207 ---------------------------------------------------------------------------------------
2208 BEGIN
2209 OPEN c_rec;
2210
2211 LOOP
2212
2213 t_rowid.DELETE;
2214 t_last_update_login.DELETE;
2215 t_locale_id.DELETE;
2216 t_localized_str.DELETE;
2217 t_intl_text_id.DELETE;
2218 t_deleted_flag.DELETE;
2219 t_security_mask.DELETE;
2220 t_checkout_user.DELETE;
2221 t_orig_sys_ref.DELETE;
2222 t_language.DELETE;
2223 t_source_lang.DELETE;
2224 t_model_id.DELETE;
2225 t_seeded_flag.DELETE;
2226 t_disposition.DELETE;
2227 t_message.DELETE;
2228
2229 FETCH c_rec BULK COLLECT INTO
2230 t_rowid, t_last_update_login, t_locale_id, t_localized_str, t_intl_text_id, t_deleted_flag,
2231 t_security_mask, t_checkout_user, t_orig_sys_ref, t_language, t_source_lang, t_model_id,
2232 t_seeded_flag, t_disposition, t_message
2233 LIMIT p_commit_size;
2237
2234 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
2235
2236 FOR i IN 1..t_rowid.COUNT LOOP
2238 t_message(i) := NULL;
2239 END LOOP;
2240
2241 IF(NOT insert_online_data(t_rowid.COUNT))THEN
2242
2243 FOR i IN 1..t_rowid.COUNT LOOP
2244
2245 IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
2246
2247 BEGIN
2248 INSERT INTO cz_localized_texts
2249 (last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
2250 checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag)
2251 VALUES
2252 (t_last_update_login(i), t_locale_id(i), t_localized_str(i), t_intl_text_id(i),
2253 t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_orig_sys_ref(i),
2254 t_language(i), t_source_lang(i), t_model_id(i), t_seeded_flag(i));
2255
2256 EXCEPTION
2257 WHEN OTHERS THEN
2258 t_message(i) := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2259 t_disposition(i) := CZRI_DISPOSITION_REJECT;
2260 END;
2261 END IF;
2262
2263 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
2264
2265 p_errors := p_errors + 1;
2266
2267 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
2268
2269 --Update the already processed records here.
2270
2271 update_table_data(i);
2272 COMMIT;
2273 RAISE CZRI_ERR_MAXIMUM_ERRORS;
2274 END IF;
2275 END IF;
2276 END LOOP;
2277 END IF;
2278
2279 IF(NOT update_online_data(t_rowid.COUNT))THEN
2280
2281 FOR i IN 1..t_rowid.COUNT LOOP
2282
2283 IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
2284
2285 BEGIN
2286 UPDATE cz_localized_texts SET
2287 last_update_login = t_last_update_login(i),
2288 locale_id = t_locale_id(i),
2289 localized_str = t_localized_str(i),
2290 deleted_flag = t_deleted_flag(i),
2291 security_mask = t_security_mask(i),
2292 checkout_user = t_checkout_user(i),
2293 orig_sys_ref = t_orig_sys_ref(i),
2294 source_lang = t_source_lang(i),
2295 model_id = t_model_id(i),
2296 seeded_flag = t_seeded_flag(i)
2297 WHERE intl_text_id = t_intl_text_id(i)
2298 AND language = t_language(i);
2299
2300 EXCEPTION
2301 WHEN OTHERS THEN
2302 t_message(i) := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2303 t_disposition(i) := CZRI_DISPOSITION_REJECT;
2304 END;
2305 END IF;
2306
2307 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
2308
2309 p_errors := p_errors + 1;
2310
2311 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
2312
2313 --Update the already processed records here.
2314
2315 update_table_data(i);
2316 COMMIT;
2317 RAISE CZRI_ERR_MAXIMUM_ERRORS;
2318 END IF;
2319 END IF;
2320 END LOOP;
2321 END IF;
2322
2323 update_table_data(t_rowid.COUNT);
2324 COMMIT;
2325 END LOOP;
2326 OPEN c_rec_nochange;
2327 LOOP /*vigramak */
2328 t_rowid.DELETE;
2329
2330
2331 FETCH c_rec_nochange BULK COLLECT INTO t_rowid
2332 LIMIT p_commit_size;
2333 EXIT WHEN c_rec_nochange%NOTFOUND AND t_rowid.COUNT = 0;
2334
2335 FOR i IN 1..t_rowid.COUNT LOOP
2336 update_table_data_nochange(i);
2337 COMMIT;
2338
2339 END LOOP;
2340 END LOOP;
2341 CLOSE c_rec_nochange;
2342
2343
2344
2345 CLOSE c_rec;
2346 EXCEPTION
2347 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
2348 CLOSE c_rec;
2349 CLOSE c_rec_nochange;
2350 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
2351 x_msg_count := 1;
2352 report(x_msg_data, p_run_id, 'xfr_localized_texts', v_debug);
2353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2354 RAISE CZRI_ERR_FATAL_ERROR;
2355 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
2356 CLOSE c_rec;
2357 CLOSE c_rec_nochange;
2358 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2359 x_msg_count := 1;
2360 report(x_msg_data, p_run_id, 'xfr_localized_texts', v_debug);
2361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2362 RAISE CZRI_ERR_FATAL_ERROR;
2363 END;
2364 ---------------------------------------------------------------------------------------
2365 PROCEDURE rpt_localized_texts(p_api_version IN NUMBER,
2366 p_run_id IN NUMBER,
2367 x_return_status IN OUT NOCOPY VARCHAR2,
2368 x_msg_count IN OUT NOCOPY NUMBER,
2369 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2370
2371 v_debug NUMBER := 5000;
2372 BEGIN
2373
2374 FOR c_stat IN (SELECT disposition, rec_status, COUNT(*) as records
2375 FROM cz_imp_localized_texts
2376 WHERE run_id = p_run_id
2377 AND rec_status IS NOT NULL
2378 AND disposition IS NOT NULL
2379 GROUP BY disposition, rec_status) LOOP
2380
2381 INSERT INTO cz_xfr_run_results (run_id, imp_table, disposition, rec_status, records)
2382 VALUES (p_run_id, 'CZ_IMP_LOCALIZED_TEXTS', c_stat.disposition, c_stat.rec_status, c_stat.records);
2383 END LOOP;
2384
2385 COMMIT;
2389 x_msg_count := 1;
2386 EXCEPTION
2387 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
2388 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2390 report(x_msg_data, p_run_id, 'rpt_localized_texts', v_debug);
2391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2392 RAISE CZRI_ERR_REPORT_ERROR;
2393 END;
2394 ---------------------------------------------------------------------------------------
2395 PROCEDURE restat_localized_texts(p_api_version IN NUMBER,
2396 p_run_id IN NUMBER,
2397 x_return_status IN OUT NOCOPY VARCHAR2,
2398 x_msg_count IN OUT NOCOPY NUMBER,
2399 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2400
2401 v_debug NUMBER := 10000;
2402 BEGIN
2403
2404 DELETE FROM cz_xfr_run_results WHERE run_id = p_run_id;
2405 rpt_localized_texts(p_api_version, p_run_id, x_return_status, x_msg_count, x_msg_data);
2406 END;
2407 ---------------------------------------------------------------------------------------
2408 PROCEDURE import_localized_texts(p_api_version IN NUMBER,
2409 p_run_id IN NUMBER,
2410 p_maximum_errors IN PLS_INTEGER,
2411 p_commit_size IN PLS_INTEGER,
2412 p_errors IN OUT NOCOPY PLS_INTEGER,
2413 x_return_status IN OUT NOCOPY VARCHAR2,
2414 x_msg_count IN OUT NOCOPY NUMBER,
2415 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2416 BEGIN
2417
2418 cnd_localized_texts(p_api_version,
2419 p_run_id,
2420 p_maximum_errors,
2421 p_commit_size,
2422 p_errors,
2423 x_return_status,
2424 x_msg_count,
2425 x_msg_data);
2426
2427 krs_localized_texts(p_api_version,
2428 p_run_id,
2429 p_maximum_errors,
2430 p_commit_size,
2431 p_errors,
2432 x_return_status,
2433 x_msg_count,
2434 x_msg_data);
2435
2436 xfr_localized_texts(p_api_version,
2437 p_run_id,
2438 p_maximum_errors,
2439 p_commit_size,
2440 p_errors,
2441 x_return_status,
2442 x_msg_count,
2443 x_msg_data);
2444
2445 rpt_localized_texts(p_api_version,
2446 p_run_id,
2447 x_return_status,
2448 x_msg_count,
2449 x_msg_data);
2450
2451 EXCEPTION
2452 WHEN CZRI_ERR_REPORT_ERROR THEN
2453 RAISE CZRI_ERR_FATAL_ERROR;
2454 WHEN OTHERS THEN
2455 rpt_localized_texts(p_api_version,
2456 p_run_id,
2457 x_return_status,
2458 x_msg_count,
2459 x_msg_data);
2460 RAISE;
2461 END;
2462 ---------------------------------------------------------------------------------------
2463 PROCEDURE refresh_statistics(p_api_version IN NUMBER,
2464 p_run_id IN NUMBER,
2465 x_return_status IN OUT NOCOPY VARCHAR2,
2466 x_msg_count IN OUT NOCOPY NUMBER,
2467 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2468
2469 v_debug NUMBER := 12000;
2470 BEGIN
2471
2472 x_msg_count := 0;
2473 x_return_status := FND_API.G_RET_STS_SUCCESS;
2474
2475 restat_localized_texts(p_api_version, p_run_id, x_return_status, x_msg_count, x_msg_data);
2476 restat_rules(p_api_version, p_run_id, x_return_status, x_msg_count, x_msg_data);
2477
2478 EXCEPTION
2479 WHEN CZRI_ERR_REPORT_ERROR THEN
2480 --All the logging has already been done.
2481 NULL;
2482 WHEN OTHERS THEN
2483 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2484 x_msg_count := 1;
2485 report(x_msg_data, p_run_id, 'refresh_statistics', v_debug);
2486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2487 END;
2488 ---------------------------------------------------------------------------------------
2489 PROCEDURE rule_import(p_api_version IN NUMBER,
2490 p_run_id IN OUT NOCOPY NUMBER,
2491 p_maximum_errors IN PLS_INTEGER,
2492 p_commit_size IN PLS_INTEGER,
2493 x_return_status IN OUT NOCOPY VARCHAR2,
2494 x_msg_count IN OUT NOCOPY NUMBER,
2495 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2496
2497 v_errors PLS_INTEGER;
2498 v_error_flag PLS_INTEGER;
2499 v_null PLS_INTEGER;
2500 v_api_version NUMBER := p_api_version;
2501 v_maximum_errors PLS_INTEGER := p_maximum_errors;
2502 v_commit_size PLS_INTEGER := p_commit_size;
2503 v_debug NUMBER := 1000;
2504 BEGIN
2505
2506 --Initialize the FND message stack.
2507
2508 FND_MSG_PUB.INITIALIZE;
2509
2510 --Check for other active import sessions.
2511
2512 BEGIN
2513
2514 SELECT NULL INTO v_null FROM v$session WHERE module = CZRI_MODULE_NAME;
2515 RAISE CZRI_ERR_ACTIVE_SESSIONS;
2516
2517 EXCEPTION
2518 WHEN NO_DATA_FOUND THEN
2519
2520 --Set the application module name.
2521
2522 DBMS_APPLICATION_INFO.SET_MODULE(CZRI_MODULE_NAME,'');
2523 END;
2524
2528 IF(v_maximum_errors IS NULL OR v_maximum_errors <= 0)THEN v_maximum_errors := CZRI_MAXIMUM_ERRORS; END IF;
2525 --Default the parameters.
2526
2527 IF(v_api_version IS NULL OR v_api_version <= 0)THEN v_api_version := CZRI_API_VERSION; END IF;
2529 IF(v_commit_size IS NULL OR v_commit_size <= 0)THEN v_commit_size := CZRI_COMMIT_SIZE; END IF;
2530
2531 --Initialize error counter and output parameters.
2532
2533 v_errors := 0;
2534 x_msg_count := 0;
2535 x_return_status := FND_API.G_RET_STS_SUCCESS;
2536
2537 --If necessary, generate a run_id and update the source records with this value.
2538
2539 IF(p_run_id IS NULL)THEN
2540
2541 SELECT cz_xfr_run_infos_s.NEXTVAL INTO p_run_id FROM DUAL;
2542
2543 UPDATE cz_imp_rules SET run_id = p_run_id
2544 WHERE disposition IS NULL
2545 AND rec_status IS NULL
2546 AND run_id IS NULL;
2547
2548 UPDATE cz_imp_localized_texts SET run_id = p_run_id
2549 WHERE disposition IS NULL
2550 AND rec_status IS NULL
2551 AND run_id IS NULL;
2552
2553 COMMIT;
2554 END IF;
2555
2556 BEGIN
2557
2558 SELECT 1 INTO v_error_flag FROM DUAL WHERE EXISTS
2559 (SELECT NULL FROM cz_imp_rules WHERE run_id = p_run_id);
2560
2561 EXCEPTION
2562 WHEN NO_DATA_FOUND THEN
2563 v_error_flag := 0;
2564 END;
2565
2566 --Raise an error if there is no data for the specified run_id.
2567
2568 IF(v_error_flag = 0)THEN RAISE CZRI_ERR_RUNID_INCORRECT; END IF;
2569
2570 --Create a control record for the current session.
2571
2572 BEGIN
2573
2574 SELECT NULL INTO v_null FROM cz_xfr_run_infos WHERE run_id = p_run_id;
2575 RAISE CZRI_ERR_RUNID_EXISTS;
2576
2577 EXCEPTION
2578 WHEN NO_DATA_FOUND THEN
2579
2580 INSERT INTO cz_xfr_run_infos (run_id, started, last_activity, completed)
2581 VALUES (p_run_id, SYSDATE, SYSDATE, '0');
2582 END;
2583
2584 --Call the import procedures.
2585
2586 import_localized_texts(v_api_version,
2587 p_run_id,
2588 v_maximum_errors,
2589 v_commit_size,
2590 v_errors,
2591 x_return_status,
2592 x_msg_count,
2593 x_msg_data);
2594
2595 import_rules(v_api_version,
2596 p_run_id,
2597 v_maximum_errors,
2598 v_commit_size,
2599 v_errors,
2600 x_return_status,
2601 x_msg_count,
2602 x_msg_data);
2603
2604 --Update the control record for this session.
2605
2606 UPDATE cz_xfr_run_infos SET
2607 last_activity = SYSDATE,
2608 completed = '1'
2609 WHERE run_id = p_run_id;
2610
2611 --IF there were any errors, the return status will be 'Warning', not 'Success'.
2612
2613 IF(v_errors > 0)THEN x_return_status := FND_API.G_RET_STS_ERROR; END IF;
2614
2615 --If there were no successfully transferred rules, the return status will be 'Error'.
2616
2617 BEGIN
2618
2619 SELECT 1 INTO v_error_flag FROM DUAL WHERE EXISTS
2620 (SELECT NULL FROM cz_imp_rules
2621 WHERE run_id = p_run_id
2622 AND rec_status = CZRI_RECSTATUS_XFR
2623 AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY));
2624
2625 EXCEPTION
2626 WHEN NO_DATA_FOUND THEN
2627 v_error_flag := 0;
2628 END;
2629
2630 IF(v_error_flag = 0)THEN RAISE CZRI_ERR_DATA_INCORRECT; END IF;
2631
2632 --Reset the application module name.
2633
2634 DBMS_APPLICATION_INFO.SET_MODULE('','');
2635
2636 EXCEPTION
2637 WHEN CZRI_ERR_ACTIVE_SESSIONS THEN
2638 x_msg_data := cz_utils.get_text('CZRI_IMP_ACTIVESESSION', 'RUNID', p_run_id);
2639 x_msg_count := 1;
2640 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2642 DBMS_APPLICATION_INFO.SET_MODULE('','');
2643 WHEN CZRI_ERR_RUNID_EXISTS THEN
2644 x_msg_data := cz_utils.get_text('CZRI_IMP_RUNID_EXISTS', 'RUNID', p_run_id);
2645 x_msg_count := 1;
2646 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2648 DBMS_APPLICATION_INFO.SET_MODULE('','');
2649 WHEN CZRI_ERR_RUNID_INCORRECT THEN
2650 x_msg_data := cz_utils.get_text('CZRI_ERR_RUNID_INCORRECT', 'RUNID', p_run_id);
2651 x_msg_count := 1;
2652 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2654 DBMS_APPLICATION_INFO.SET_MODULE('','');
2655 WHEN CZRI_ERR_DATA_INCORRECT THEN
2656 x_msg_data := cz_utils.get_text('CZRI_ERR_DATA_INCORRECT', 'RUNID', p_run_id);
2657 x_msg_count := 1;
2658 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2660 DBMS_APPLICATION_INFO.SET_MODULE('','');
2661 WHEN CZRI_ERR_FATAL_ERROR THEN
2662 --hard errors occurred in underlying procedures, already logged.
2663 DBMS_APPLICATION_INFO.SET_MODULE('','');
2664 WHEN OTHERS THEN
2665 --unexpected errors occurred in the procedure.
2666 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2667 x_msg_count := 1;
2668 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2670 DBMS_APPLICATION_INFO.SET_MODULE('','');
2671 END;
2672
2673 -----------------------
2674 --------------procedures for lock, unlock
2675 PROCEDURE lock_models (p_api_version IN NUMBER,
2676 p_run_id IN NUMBER,
2677 p_commit_flag IN VARCHAR2,
2681 x_msg_data OUT NOCOPY VARCHAR2)
2678 x_locked_entities OUT NOCOPY SYSTEM.CZ_NUMBER_TBL_TYPE,
2679 x_return_status OUT NOCOPY VARCHAR2,
2680 x_msg_count OUT NOCOPY NUMBER,
2682 IS
2683
2684 l_locked_entities cz_security_pvt.number_type_tbl;
2685 l_model_id_tbl table_of_number;
2686 rec_count NUMBER;
2687 MODEL_IS_LOCKED EXCEPTION;
2688
2689 BEGIN
2690 ----initialize FND stack
2691 FND_MSG_PUB.initialize;
2692 x_return_status := FND_API.G_RET_STS_SUCCESS;
2693 x_msg_data := NULL;
2694 x_msg_count := 0;
2695 x_locked_entities := SYSTEM.CZ_NUMBER_TBL_TYPE();
2696
2697 IF (p_run_id IS NULL) THEN
2698 SELECT distinct devl_project_id
2699 BULK
2700 COLLECT
2701 INTO l_model_id_tbl
2702 FROM cz_imp_rules
2703 WHERE rec_status IS NULL
2704 AND disposition IS NULL
2705 AND run_id IS NULL;
2706 ELSE
2707 SELECT distinct devl_project_id
2708 BULK
2709 COLLECT
2710 INTO l_model_id_tbl
2711 FROM cz_imp_rules
2712 WHERE rec_status IS NULL
2713 AND disposition IS NULL
2714 AND run_id = p_run_id;
2715 END IF;
2716
2717 IF (l_model_id_tbl.COUNT > 0) THEN
2718 FOR I IN l_model_id_tbl.FIRST..l_model_id_tbl.LAST
2719 LOOP
2720 cz_security_pvt.lock_model(1.0,
2721 l_model_id_tbl(i),
2722 FND_API.G_FALSE,
2723 FND_API.G_FALSE,
2724 FND_API.G_FALSE,
2725 l_locked_entities,
2726 x_return_status,
2727 x_msg_count,
2728 x_msg_data);
2729
2730 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2731 RAISE MODEL_IS_LOCKED;
2732 ELSE
2733 IF (l_locked_entities.COUNT > 0) THEN
2734 x_locked_entities.EXTEND(1);
2735 rec_count := x_locked_entities.COUNT;
2736 x_locked_entities(rec_count) := l_model_id_tbl(i);
2737 END IF;
2738 END IF;
2739 END LOOP;
2740 END IF;
2741 IF (p_commit_flag = FND_API.G_TRUE) THEN COMMIT; END IF;
2742 EXCEPTION
2743 WHEN MODEL_IS_LOCKED THEN
2744 fnd_msg_pub.count_and_get(FND_API.G_FALSE, x_msg_count, x_msg_data);
2745 WHEN NO_DATA_FOUND THEN
2746 NULL;
2747 WHEN OTHERS THEN
2748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2749 fnd_msg_pub.add_exc_msg('CZ_RULE_IMPORT', 'lock_models');
2750 fnd_msg_pub.count_and_get(FND_API.G_FALSE, x_msg_count, x_msg_data);
2751 END lock_models;
2752
2753 -------------------------------------------------------------------
2754 --This procedure creates/updates the localized text records for
2755 --rules with reason type as "Name or Description"
2756 -------------------------------------------------------------------
2757 PROCEDURE process_dflt_localized_texts(p_api_version IN NUMBER,
2758 p_run_id IN NUMBER,
2759 p_maximum_errors IN PLS_INTEGER,
2760 p_commit_size IN PLS_INTEGER,
2761 p_errors IN OUT NOCOPY PLS_INTEGER,
2762 x_return_status IN OUT NOCOPY VARCHAR2,
2763 x_msg_count IN OUT NOCOPY NUMBER,
2764 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2765
2766
2767 -- Select rules with reason type as "Name" or "Description" and no corresponding localized texts.
2768
2769 CURSOR c_rec IS
2770 SELECT ROWID,devl_project_id,last_update_login, reason_type, name, desc_text,
2771 decode(reason_type, 0, name, 2, desc_text) text_str, disposition, reason_id,rule_id
2772 FROM cz_imp_rules
2773 WHERE run_id = p_run_id
2774 AND rec_status = CZRI_RECSTATUS_XFR
2775 AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY)
2776 AND reason_type IN (0,2)
2777 AND fsk_localized_text_1 IS NULL;
2778
2779 t_rowid table_of_varchar;
2780 t_model_id table_of_number;
2781 t_last_update_login table_of_varchar;
2782 t_reason_type table_of_number;
2783 t_rule_name table_of_varchar;
2784 t_rule_desc_text table_of_varchar;
2785 t_text_str table_of_varchar;
2786 t_disposition table_of_varchar;
2787 t_reason_id table_of_number;
2788 t_rules_id table_of_number;
2789 l_reason_id NUMBER;
2790
2791
2792
2793 FUNCTION update_dflt_localized_texts(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
2794
2795 t_rowid table_of_varchar;
2796 t_last_update_login table_of_varchar;
2797 t_text_str table_of_varchar;
2798 t_disposition table_of_varchar;
2799 t_rules_id table_of_number;
2800
2801 v_index PLS_INTEGER := 1;
2802
2803 BEGIN
2804
2805 FOR i IN 1..p_upper_limit LOOP
2806 t_disposition(i) := process_dflt_localized_texts.t_disposition(i);
2807
2808 IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
2809 t_rowid(v_index) := process_dflt_localized_texts.t_rowid(v_index);
2810 t_last_update_login(v_index) := process_dflt_localized_texts.t_last_update_login(v_index) ;
2811 t_text_str(v_index) := process_dflt_localized_texts.t_text_str(v_index) ;
2812 t_disposition(v_index) := process_dflt_localized_texts.t_disposition(v_index) ;
2813 t_rules_id(v_index) := process_dflt_localized_texts.t_rules_id(v_index) ;
2814
2815
2816 v_index := v_index + 1;
2817
2818 END IF;
2819 END LOOP;
2820
2821 FORALL i IN 1..t_rowid.COUNT
2822 UPDATE cz_localized_texts
2823 SET localized_str = t_text_str(i)
2824 WHERE intl_text_id = (SELECT reason_id from cz_rules where rule_id = t_rules_id(i));
2825 RETURN TRUE;
2826
2827 EXCEPTION
2828 WHEN OTHERS THEN
2829 RETURN FALSE;
2830 END ;
2831 ---------------------------------------------------------------------------------------
2832 BEGIN
2833
2834 OPEN c_rec;
2835 LOOP
2836
2837 FETCH c_rec BULK COLLECT INTO
2838 t_rowid,t_model_id,t_last_update_login ,t_reason_type, t_rule_name ,
2839 t_rule_desc_text, t_text_str , t_disposition, t_reason_id , t_rules_id
2840 LIMIT p_commit_size;
2841
2842 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
2843
2844 FOR i IN 1..t_rowid.COUNT LOOP
2845
2846 IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
2847
2848 BEGIN
2849 SAVEPOINT insert_msg_record;
2850 INSERT INTO cz_localized_texts_vl
2851 (last_update_login, text_str, intl_text_id, deleted_flag, model_id)
2852 VALUES
2853 (t_last_update_login(i), t_text_str(i), cz_intl_texts_s.NEXTVAL, 0, t_model_id(i)) ;
2854
2855 UPDATE CZ_RULES
2856 SET reason_id = cz_intl_texts_s.currval
2857 WHERE devl_project_id = t_model_id(i)
2858 AND rule_id = t_rules_id(i);
2859
2860 EXCEPTION
2861 WHEN OTHERS THEN
2862 ROLLBACK TO insert_msg_record;
2863 END;
2864
2865 END IF;
2866 END LOOP;
2867
2868
2869 IF (NOT update_dflt_localized_texts(t_rowid.COUNT)) THEN
2870 FOR i IN 1..t_rowid.COUNT LOOP
2871 IF (t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
2872 BEGIN
2873 SAVEPOINT update_msg_record;
2874
2875 UPDATE cz_localized_texts
2876 SET localized_str = t_text_str(i)
2877 WHERE intl_text_id = (SELECT reason_id from cz_rules where rule_id = t_rules_id(i));
2878
2879 EXCEPTION
2880 WHEN OTHERS THEN
2881 ROLLBACK TO update_msg_record;
2882 END;
2883 END IF;
2884 END LOOP;
2885 END IF;
2886
2887 COMMIT;
2888 END LOOP;
2889
2890
2891 END process_dflt_localized_texts;
2892
2893 END;