[Home] [Help]
PACKAGE BODY: APPS.CZ_RULE_IMPORT
Source
1 PACKAGE BODY CZ_RULE_IMPORT AS
2 /* $Header: czruleib.pls 120.2 2007/11/26 15:18:46 kdande 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_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
8 TYPE table_of_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
9 TYPE table_of_clob IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
10 ---------------------------------------------------------------------------------------
11 PROCEDURE report(p_message IN VARCHAR2,
12 p_run_id IN NUMBER,
13 p_caller IN VARCHAR2,
14 p_statuscode IN NUMBER) IS
15 BEGIN
16 INSERT INTO cz_db_logs (logtime, urgency, caller, statuscode, message, run_id)
17 VALUES (SYSDATE, 1, p_caller, p_statuscode, p_message, p_run_id);
18 END;
19 ---------------------------------------------------------------------------------------
20 PROCEDURE cnd_rules(p_api_version IN NUMBER,
21 p_run_id IN NUMBER,
22 p_maximum_errors IN PLS_INTEGER,
23 p_commit_size IN PLS_INTEGER,
24 p_errors IN OUT NOCOPY PLS_INTEGER,
25 x_return_status IN OUT NOCOPY VARCHAR2,
26 x_msg_count IN OUT NOCOPY NUMBER,
27 x_msg_data IN OUT NOCOPY VARCHAR2) IS
28
29 v_debug NUMBER := 6000;
30
31 CURSOR c_rec IS
32 SELECT ROWID, devl_project_id, message, orig_sys_ref, rule_folder_id, rule_type,
33 name, seeded_flag, deleted_flag, mutable_flag, disabled_flag, invalid_flag,
34 presentation_flag, effective_usage_mask, seq_nbr, disposition,
35 component_id, model_ref_expl_id, fsk_component_id, fsk_model_ref_expl_id,
36 instantiation_scope
37 FROM cz_imp_rules
38 WHERE run_id = p_run_id
39 AND rec_status IS NULL
40 AND disposition IS NULL;
41
42 t_rowid table_of_rowid;
43 t_devl_project_id table_of_number;
44 t_message table_of_varchar;
45 t_orig_sys_ref table_of_varchar;
46 t_rule_folder_id table_of_number;
47 t_rule_type table_of_number;
48 t_name table_of_varchar;
49 t_seeded_flag table_of_varchar;
50 t_deleted_flag table_of_varchar;
51 t_mutable_flag table_of_varchar;
52 t_disabled_flag table_of_varchar;
53 t_invalid_flag table_of_varchar;
54 t_presentation_flag table_of_varchar;
55 t_effective_usage_mask table_of_varchar;
56 t_seq_nbr table_of_number;
57 t_disposition table_of_varchar;
58 t_component_id table_of_number;
59 t_model_ref_expl_id table_of_number;
60 t_fsk_component_id table_of_varchar;
61 t_fsk_model_ref_expl_id table_of_varchar;
62 t_instantiation_scope table_of_number;
63
64 validateModel VARCHAR2(4000) :=
65 'SELECT model_ref_expl_id ' ||
66 ' FROM cz_rp_entries r, cz_model_ref_expls e ' ||
67 ' WHERE r.deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
68 ' AND r.object_type = ''' || CZRI_REPOSITORY_PROJECT || ''' ' ||
69 ' AND r.object_id = :1 ' ||
70 ' AND e.deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
71 ' AND e.parent_expl_node_id IS NULL ' ||
72 ' AND e.model_id = r.object_id';
73
74 getRootFolderId VARCHAR2(4000) :=
75 'SELECT rule_folder_id ' ||
76 ' FROM cz_rule_folders ' ||
77 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
78 ' AND object_type = ''' || CZRI_TYPE_RULE_FOLDER || ''' ' ||
79 ' AND devl_project_id = :1 ' ||
80 ' AND parent_rule_folder_id IS NULL';
81
82 validateFolder VARCHAR2(4000) :=
83 'SELECT NULL ' ||
84 ' FROM cz_rule_folders ' ||
85 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
86 ' AND object_type = ''' || CZRI_TYPE_RULE_FOLDER || ''' ' ||
87 ' AND devl_project_id = :1 ' ||
88 ' AND rule_folder_id = :2';
89
90 h_ValidModel table_of_number_index_VC2;
91 h_InvalidModel table_of_number_index_VC2;
92 h_ValidFolder table_of_number;
93 h_RootFolder table_of_number_index_VC2;
94 h_NoSuchFolder table_of_number;
95 h_NoRootFolder table_of_number_index_VC2;
96 v_null NUMBER;
97 v_root_folder_id NUMBER;
98 v_root_expl_id NUMBER;
99 ---------------------------------------------------------------------------------------
100 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
101 BEGIN
102
103 FORALL i IN 1..p_upper_limit
104 UPDATE cz_imp_rules SET
105 message = t_message(i),
106 seeded_flag = t_seeded_flag(i),
107 deleted_flag = t_deleted_flag(i),
108 rec_status = CZRI_RECSTATUS_CND,
109 disposition = t_disposition(i),
110 mutable_flag = t_mutable_flag(i),
111 disabled_flag = t_disabled_flag(i),
112 invalid_flag = t_invalid_flag(i),
113 presentation_flag = t_presentation_flag(i),
114 effective_usage_mask = t_effective_usage_mask(i),
115 seq_nbr = t_seq_nbr(i),
116 model_ref_expl_id = t_model_ref_expl_id(i),
117 rule_folder_id = t_rule_folder_id(i),
118 component_id = t_component_id(i),
119 instantiation_scope = t_instantiation_scope(i)
120 WHERE ROWID = t_rowid(i);
121 END;
122 ---------------------------------------------------------------------------------------
123 BEGIN
124
125 OPEN c_rec;
126 LOOP
127
128 t_rowid.DELETE;
129 t_devl_project_id.DELETE;
130 t_message.DELETE;
131 t_orig_sys_ref.DELETE;
132 t_rule_folder_id.DELETE;
133 t_rule_type.DELETE;
134 t_name.DELETE;
135 t_seeded_flag.DELETE;
136 t_deleted_flag.DELETE;
137 t_mutable_flag.DELETE;
138 t_disabled_flag.DELETE;
139 t_invalid_flag.DELETE;
140 t_presentation_flag.DELETE;
141 t_effective_usage_mask.DELETE;
142 t_seq_nbr.DELETE;
143 t_disposition.DELETE;
144 t_component_id.DELETE;
145 t_model_ref_expl_id.DELETE;
146 t_fsk_component_id.DELETE;
147 t_fsk_model_ref_expl_id.DELETE;
148 t_instantiation_scope.DELETE;
149
150 FETCH c_rec BULK COLLECT INTO
151 t_rowid, t_devl_project_id, t_message, t_orig_sys_ref, t_rule_folder_id, t_rule_type, t_name,
152 t_seeded_flag, t_deleted_flag, t_mutable_flag, t_disabled_flag, t_invalid_flag, t_presentation_flag,
153 t_effective_usage_mask, t_seq_nbr, t_disposition, t_component_id, t_model_ref_expl_id, t_fsk_component_id,
154 t_fsk_model_ref_expl_id, t_instantiation_scope
155 LIMIT p_commit_size;
156 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
157
158 FOR i IN 1..t_rowid.COUNT LOOP
159
160 t_message(i) := NULL;
161 t_disposition(i) := CZRI_DISPOSITION_REJECT;
162
163 IF(t_devl_project_id(i) IS NULL)THEN
164
165 t_message(i) := cz_utils.get_text('CZRI_RLE_NULLMODELID');
166
167 ELSIF(h_InvalidModel.EXISTS(t_devl_project_id(i)))THEN
168
169 t_message(i) := cz_utils.get_text('CZRI_RLE_INVALIDMODEL');
170
171 ELSIF(h_NoRootFolder.EXISTS(t_devl_project_id(i)))THEN
172
173 t_message(i) := cz_utils.get_text('CZRI_RLE_NOROOTFOLDER');
174
175 ELSIF(t_rule_folder_id(i) IS NOT NULL AND h_NoSuchFolder.EXISTS(t_rule_folder_id(i)))THEN
176
177 t_message(i) := cz_utils.get_text('CZRI_RLE_NOSUCHFOLDER');
178
179 ELSIF(t_orig_sys_ref(i) IS NULL)THEN
180
181 t_message(i) := cz_utils.get_text('CZRI_RLE_NULLORIGSYSREF');
182
183 ELSIF(t_rule_type(i) IS NULL)THEN
184
185 t_message(i) := cz_utils.get_text('CZRI_RLE_NULLTYPE');
186
187 ELSIF(t_rule_type(i) NOT IN (CZRI_TYPE_EXPRESSION_RULE, CZRI_TYPE_COMPANION_RULE))THEN
188
189 t_message(i) := cz_utils.get_text('CZRI_RLE_INVALIDTYPE');
190
191 -- For the Phase I we do not implement resolution of component_id, model_ref_expl_id values for
192 -- CX rules using surrogate keys. Instead, we require direct population of the columns.
193 --
194 -- ELSIF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_fsk_component_id(i) IS NULL)THEN
195 --
196 -- t_message(i) := cz_utils.get_text('CZRI_RLE_NULLCOMPONENTID');
197 --
198 -- ELSIF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_fsk_model_ref_expl_id(i) IS NULL)THEN
199 --
200 -- t_message(i) := cz_utils.get_text('CZRI_RLE_NULLEXPLID');
201
202 -- If component_id is NULL, it will be populated automatically with the ps_node_id of the root
203 -- model node.
204 --
205 -- ELSIF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_component_id(i) IS NULL)THEN
206 --
207 -- t_message(i) := cz_utils.get_text('CZRI_RLE_NULLCOMPONENTID');
208
209 -- If model_ref_expl_id is NULL, it will be populated automatically with the model_ref_expl_id
210 -- of the root model node.
211 --
212 -- ELSIF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_model_ref_expl_id(i) IS NULL)THEN
213 --
214 -- t_message(i) := cz_utils.get_text('CZRI_RLE_NULLEXPLID');
215
216 ELSIF(t_name(i) IS NULL)THEN
217
218 t_message(i) := cz_utils.get_text('CZRI_RLE_NULLNAME');
219
220 ELSIF(t_presentation_flag(i) IS NOT NULL AND t_presentation_flag(i) <> CZRI_FLAG_STATEMENT_RULE)THEN
221
222 t_message(i) := cz_utils.get_text('CZRI_RLE_PRESENTFLAG');
223
224 ELSE
225
226 t_disposition(i) := CZRI_DISPOSITION_PASSED;
227 END IF;
228
229 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED AND (NOT h_ValidModel.EXISTS(t_devl_project_id(i))))THEN
230
231 BEGIN
232
233 EXECUTE IMMEDIATE validateModel INTO v_root_expl_id USING t_devl_project_id(i);
234 h_ValidModel(t_devl_project_id(i)) := v_root_expl_id;
235
236 EXCEPTION
237 WHEN NO_DATA_FOUND THEN
238 t_message(i) := cz_utils.get_text('CZRI_RLE_INVALIDMODEL');
239 t_disposition(i) := CZRI_DISPOSITION_REJECT;
240 h_InvalidModel(t_devl_project_id(i)) := 1;
241 END;
242 END IF;
243
244 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
245 IF(t_rule_folder_id(i) IS NULL)THEN
246 IF(h_RootFolder.EXISTS(t_devl_project_id(i)))THEN
247
248 t_rule_folder_id(i) := h_RootFolder(t_devl_project_id(i));
249
250 ELSE
251
252 BEGIN
253
254 EXECUTE IMMEDIATE getRootFolderId INTO v_root_folder_id USING t_devl_project_id(i);
255 t_rule_folder_id(i) := v_root_folder_id;
256 h_RootFolder(t_devl_project_id(i)) := v_root_folder_id;
257
258 EXCEPTION
259 WHEN NO_DATA_FOUND THEN
260 t_message(i) := cz_utils.get_text('CZRI_RLE_NOROOTFOLDER');
261 t_disposition(i) := CZRI_DISPOSITION_REJECT;
262 h_NoRootFolder(t_devl_project_id(i)) := 1;
263 END;
264 END IF;
265 ELSE
266 IF(NOT h_ValidFolder.EXISTS(t_rule_folder_id(i)))THEN
267
268 BEGIN
269
270 EXECUTE IMMEDIATE validateFolder INTO v_null USING t_devl_project_id(i), t_rule_folder_id(i);
271 h_ValidFolder(t_rule_folder_id(i)) := v_root_folder_id;
272
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 t_message(i) := cz_utils.get_text('CZRI_RLE_NOSUCHFOLDER');
276 t_disposition(i) := CZRI_DISPOSITION_REJECT;
277 h_NoSuchFolder(t_rule_folder_id(i)) := 1;
278 END;
279 END IF;
280 END IF;
281 END IF;
282
283 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
284
285 p_errors := p_errors + 1;
286
287 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
288
289 --Update the already processed records here.
290
291 update_table_data(i);
292 COMMIT;
293 RAISE CZRI_ERR_MAXIMUM_ERRORS;
294 END IF;
295 END IF;
296
297 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
298
299 --Validations passed, condition the record.
300 --The seeded flag is unconditionally set to '0'.
301
302 t_seeded_flag(i) := CZRI_FLAG_NOT_SEEDED;
303 IF(t_instantiation_scope(i) IS NULL)THEN t_instantiation_scope(i) := CZRI_RULE_SCOPE_INSTANCE; END IF;
304 IF(t_deleted_flag(i) IS NULL)THEN t_deleted_flag(i) := CZRI_FLAG_NOT_DELETED; END IF;
305 IF(t_mutable_flag(i) IS NULL)THEN t_mutable_flag(i) := CZRI_FLAG_NOT_MUTABLE; END IF;
306 IF(t_disabled_flag(i) IS NULL)THEN t_disabled_flag(i) := CZRI_FLAG_NOT_DISABLED; END IF;
307 IF(t_invalid_flag(i) IS NULL)THEN t_invalid_flag(i) := CZRI_FLAG_NOT_INVALID; END IF;
308 IF(t_presentation_flag(i) IS NULL)THEN t_presentation_flag(i) := CZRI_FLAG_STATEMENT_RULE; END IF;
309 IF(t_effective_usage_mask(i) IS NULL)THEN t_effective_usage_mask(i) := CZRI_EFFECTIVE_USAGE; END IF;
310 IF(t_seq_nbr(i) IS NULL)THEN t_seq_nbr(i) := CZRI_RULE_SEQ_NBR; END IF;
311 IF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_model_ref_expl_id(i) IS NULL)THEN
312 t_model_ref_expl_id(i) := h_ValidModel(t_devl_project_id(i));
313 END IF;
314 IF(t_rule_type(i) = CZRI_TYPE_COMPANION_RULE AND t_component_id(i) IS NULL)THEN
315 t_component_id(i) := t_devl_project_id(i);
316 END IF;
317 END IF;
318 END LOOP;
319
320 --Update all the records from memory here.
321
322 update_table_data(t_rowid.COUNT);
323 COMMIT;
324 END LOOP;
325
326 CLOSE c_rec;
327 EXCEPTION
328 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
329 CLOSE c_rec;
330 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
331 x_msg_count := 1;
332 report(x_msg_data, p_run_id, 'cnd_rules', v_debug);
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
334 RAISE CZRI_ERR_FATAL_ERROR;
335 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
336 CLOSE c_rec;
337 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
338 x_msg_count := 1;
339 report(x_msg_data, p_run_id, 'cnd_rules', v_debug);
340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
341 RAISE CZRI_ERR_FATAL_ERROR;
342 END;
343 ---------------------------------------------------------------------------------------
344 PROCEDURE krs_rules(p_api_version IN NUMBER,
345 p_run_id IN NUMBER,
346 p_maximum_errors IN PLS_INTEGER,
347 p_commit_size IN PLS_INTEGER,
348 p_errors IN OUT NOCOPY PLS_INTEGER,
349 x_return_status IN OUT NOCOPY VARCHAR2,
350 x_msg_count IN OUT NOCOPY NUMBER,
351 x_msg_data IN OUT NOCOPY VARCHAR2) IS
352
353 v_debug NUMBER := 7000;
354
355 CURSOR c_rec IS
356 SELECT ROWID, rule_id, devl_project_id, message, orig_sys_ref, rec_status, disposition,
360 AND rec_status = CZRI_RECSTATUS_CND
357 fsk_localized_text_1, fsk_component_id, fsk_model_ref_expl_id, rule_type, reason_id
358 FROM cz_imp_rules
359 WHERE run_id = p_run_id
361 AND disposition = CZRI_DISPOSITION_PASSED
362 ORDER BY devl_project_id, orig_sys_ref;
363
364 t_rowid table_of_rowid;
365 t_rule_id table_of_number;
366 t_devl_project_id table_of_number;
367 t_message table_of_varchar;
368 t_orig_sys_ref table_of_varchar;
369 t_rec_status table_of_varchar;
370 t_disposition table_of_varchar;
371 t_fsk_localized_text_1 table_of_varchar;
372 t_fsk_component_id table_of_varchar;
373 t_fsk_model_ref_expl_id table_of_varchar;
374 t_rule_type table_of_number;
375 t_reason_id table_of_number;
376
377 resolveRuleId VARCHAR2(4000) :=
378 'SELECT rule_id ' ||
379 ' FROM cz_rules ' ||
380 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
381 ' AND devl_project_id = :1 ' ||
382 ' AND orig_sys_ref = :2';
383
384 last_orig_sys_ref cz_imp_rules.orig_sys_ref%TYPE;
385 last_project_id cz_imp_rules.devl_project_id%TYPE;
386
387 last_id_allocated NUMBER := NULL;
388 next_id_to_use NUMBER := 0;
389 id_increment NUMBER := CZRI_RULES_INC;
390
391 t_intl_text_id table_of_number;
392 v_translations PLS_INTEGER;
393 ---------------------------------------------------------------------------------------
394 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
395 BEGIN
396
397 FORALL i IN 1..p_upper_limit
398 UPDATE cz_imp_rules SET
399 rule_id = t_rule_id(i),
400 reason_id = t_reason_id(i),
401 message = t_message(i),
402 rec_status = CZRI_RECSTATUS_KRS,
403 disposition = t_disposition(i)
404 WHERE ROWID = t_rowid(i);
405 END;
406 ---------------------------------------------------------------------------------------
407 FUNCTION next_rule_id RETURN NUMBER IS
408 id_to_return NUMBER;
409 BEGIN
410
411 IF((last_id_allocated IS NULL) OR
412 (next_id_to_use = (NVL(last_id_allocated, 0) + id_increment)))THEN
413
414 SELECT cz_rules_s.NEXTVAL INTO last_id_allocated FROM dual;
415 next_id_to_use := last_id_allocated;
416 END IF;
417
418 id_to_return := next_id_to_use;
419 next_id_to_use := next_id_to_use + 1;
420 RETURN id_to_return;
421 END;
422 ---------------------------------------------------------------------------------------
423 BEGIN
424
425 OPEN c_rec;
426 LOOP
427
428 t_rowid.DELETE;
429 t_rule_id.DELETE;
430 t_devl_project_id.DELETE;
431 t_message.DELETE;
432 t_orig_sys_ref.DELETE;
433 t_rec_status.DELETE;
434 t_disposition.DELETE;
435 t_fsk_localized_text_1.DELETE;
436 t_fsk_component_id.DELETE;
437 t_fsk_model_ref_expl_id.DELETE;
438 t_rule_type.DELETE;
439 t_reason_id.DELETE;
440
441 FETCH c_rec BULK COLLECT INTO
442 t_rowid, t_rule_id, t_devl_project_id, t_message, t_orig_sys_ref, t_rec_status, t_disposition,
443 t_fsk_localized_text_1, t_fsk_component_id, t_fsk_model_ref_expl_id, t_rule_type, t_reason_id
444 LIMIT p_commit_size;
445 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
446
447 FOR i IN 1..t_rowid.COUNT LOOP
448
449 t_message(i) := NULL;
450
451 IF(last_orig_sys_ref IS NOT NULL AND
452 last_orig_sys_ref = t_orig_sys_ref(i) AND
453 last_project_id = t_devl_project_id(i))THEN
454
455 --This is a duplicate record in the source data.
456
457 t_message(i) := cz_utils.get_text('CZRI_RLE_DUPLICATE', 'MODELID', last_project_id);
458 t_disposition(i) := CZRI_DISPOSITION_REJECT;
459 END IF;
460
461 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
462
463 --Resolve rule_id for existing record or generate a new one for a new record.
464
465 BEGIN
466 EXECUTE IMMEDIATE resolveRuleId INTO t_rule_id(i)
467 USING t_devl_project_id(i), t_orig_sys_ref(i);
468
469 t_disposition(i) := CZRI_DISPOSITION_MODIFY;
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 t_disposition(i) := CZRI_DISPOSITION_INSERT;
473 t_rule_id(i) := next_rule_id;
474 END;
475
476 --Resolve reason_id.
477
478 IF(t_fsk_localized_text_1(i) IS NOT NULL)THEN
479
480 SELECT intl_text_id BULK COLLECT INTO t_intl_text_id
481 FROM cz_localized_texts
482 WHERE deleted_flag = CZRI_FLAG_NOT_DELETED
483 AND model_id = t_devl_project_id(i)
484 AND orig_sys_ref = t_fsk_localized_text_1(i);
485
486 IF(t_intl_text_id.COUNT > 0)THEN
487
488 t_reason_id(i) := t_intl_text_id(1);
489
490 --All the records should have the same number of translations. Remember the number
491 --of translations for the first record and compare all other records to it.
492
493 IF(v_translations IS NULL)THEN
494
498
495 v_translations := t_intl_text_id.COUNT;
496
497 ELSIF(v_translations <> t_intl_text_id.COUNT)THEN
499 t_message(i) := cz_utils.get_text('CZRI_RLE_TRANSLATIONS', 'ACTUAL', t_intl_text_id.COUNT, 'EXPECTED', v_translations);
500 t_disposition(i) := CZRI_DISPOSITION_REJECT;
501 END IF;
502 ELSE
503
504 t_message(i) := cz_utils.get_text('CZRI_RLE_NOREASONID');
505 t_disposition(i) := CZRI_DISPOSITION_REJECT;
506 END IF;
507 END IF;
508 END IF;
509
510 IF(t_disposition(i) <> CZRI_DISPOSITION_REJECT)THEN
511
512 last_orig_sys_ref := t_orig_sys_ref(i);
513 last_project_id := t_devl_project_id(i);
514 END IF;
515
516 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
517
518 p_errors := p_errors + 1;
519
520 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
521
522 --Update the already processed records here.
523
524 update_table_data(i);
525 COMMIT;
526 RAISE CZRI_ERR_MAXIMUM_ERRORS;
527 END IF;
528 END IF;
529 END LOOP;
530
531 --Update all the records from memory here.
532
533 update_table_data(t_rowid.COUNT);
534 COMMIT;
535 END LOOP;
536
537 CLOSE c_rec;
538 EXCEPTION
539 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
540 CLOSE c_rec;
541 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
542 x_msg_count := 1;
543 report(x_msg_data, p_run_id, 'krs_rules', v_debug);
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 RAISE CZRI_ERR_FATAL_ERROR;
546 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
547 CLOSE c_rec;
548 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
549 x_msg_count := 1;
550 report(x_msg_data, p_run_id, 'krs_rules', v_debug);
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 RAISE CZRI_ERR_FATAL_ERROR;
553 END;
554 ---------------------------------------------------------------------------------------
555 PROCEDURE xfr_rules(p_api_version IN NUMBER,
556 p_run_id IN NUMBER,
557 p_maximum_errors IN PLS_INTEGER,
558 p_commit_size IN PLS_INTEGER,
559 p_errors IN OUT NOCOPY PLS_INTEGER,
560 x_return_status IN OUT NOCOPY VARCHAR2,
561 x_msg_count IN OUT NOCOPY NUMBER,
562 x_msg_data IN OUT NOCOPY VARCHAR2) IS
563
564 v_debug NUMBER := 8000;
565
566 --persistent_rule_id is taken care of by a trigger.
567
568 CURSOR c_rec IS
569 SELECT ROWID, rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
570 name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
571 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
572 effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
573 unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
574 presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
575 mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type,
576 disposition, message
577 FROM cz_imp_rules
578 WHERE run_id = p_run_id
579 AND rec_status = CZRI_RECSTATUS_KRS
580 AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY);
581
582 t_rowid table_of_rowid;
583 t_rule_id table_of_number;
584 t_reason_id table_of_number;
585 t_rule_folder_id table_of_number;
586 t_devl_project_id table_of_number;
587 t_invalid_flag table_of_varchar;
588 t_desc_text table_of_varchar;
589 t_name table_of_varchar;
590 t_rule_type table_of_number;
591 t_expr_rule_type table_of_number;
592 t_component_id table_of_number;
593 t_model_ref_expl_id table_of_number;
594 t_reason_type table_of_number;
595 t_disabled_flag table_of_varchar;
596 t_orig_sys_ref table_of_varchar;
597 t_deleted_flag table_of_varchar;
598 t_security_mask table_of_varchar;
599 t_checkout_user table_of_varchar;
600 t_last_update_login table_of_number;
601 t_effective_usage_mask table_of_varchar;
602 t_seq_nbr table_of_number;
603 t_effective_from table_of_date;
604 t_effective_until table_of_date;
605 t_effectivity_set_id table_of_number;
606 t_unsatisfied_msg_id table_of_number;
607 t_unsatisfied_msg_source table_of_varchar;
608 t_signature_id table_of_number;
609 t_template_primitive_flag table_of_varchar;
610 t_presentation_flag table_of_varchar;
611 t_template_token table_of_varchar;
612 t_rule_text table_of_clob;
613 t_notes table_of_clob;
614 t_class_name table_of_varchar;
615 t_instantiation_scope table_of_number;
619 t_ui_page_id table_of_number;
616 t_mutable_flag table_of_varchar;
617 t_seeded_flag table_of_varchar;
618 t_ui_def_id table_of_number;
620 t_ui_page_element_id table_of_number;
621 t_rule_folder_type table_of_number;
622 t_disposition table_of_varchar;
623 t_message table_of_varchar;
624 ---------------------------------------------------------------------------------------
625 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
626 BEGIN
627
628 --We updating rec_status to XFR, not OK, because the rules still have to be parsed.
629 --When parsed successfully, the status will be changed to OK.
630
631 FORALL i IN 1..p_upper_limit
632 UPDATE cz_imp_rules SET
633 message = t_message(i),
634 rec_status = CZRI_RECSTATUS_XFR,
635 disposition = t_disposition(i)
636 WHERE ROWID = t_rowid(i);
637 END;
638 ---------------------------------------------------------------------------------------
639 FUNCTION insert_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
640
641 t_rule_id table_of_number;
642 t_reason_id table_of_number;
643 t_rule_folder_id table_of_number;
644 t_devl_project_id table_of_number;
645 t_invalid_flag table_of_varchar;
646 t_desc_text table_of_varchar;
647 t_name table_of_varchar;
648 t_rule_type table_of_number;
649 t_expr_rule_type table_of_number;
650 t_component_id table_of_number;
651 t_model_ref_expl_id table_of_number;
652 t_reason_type table_of_number;
653 t_disabled_flag table_of_varchar;
654 t_orig_sys_ref table_of_varchar;
655 t_deleted_flag table_of_varchar;
656 t_security_mask table_of_varchar;
657 t_checkout_user table_of_varchar;
658 t_last_update_login table_of_number;
659 t_effective_usage_mask table_of_varchar;
660 t_seq_nbr table_of_number;
661 t_effective_from table_of_date;
662 t_effective_until table_of_date;
663 t_effectivity_set_id table_of_number;
664 t_unsatisfied_msg_id table_of_number;
665 t_unsatisfied_msg_source table_of_varchar;
666 t_signature_id table_of_number;
667 t_template_primitive_flag table_of_varchar;
668 t_presentation_flag table_of_varchar;
669 t_template_token table_of_varchar;
670 t_rule_text table_of_clob;
671 t_notes table_of_clob;
672 t_class_name table_of_varchar;
673 t_instantiation_scope table_of_number;
674 t_mutable_flag table_of_varchar;
675 t_seeded_flag table_of_varchar;
676 t_ui_def_id table_of_number;
677 t_ui_page_id table_of_number;
678 t_ui_page_element_id table_of_number;
679 t_rule_folder_type table_of_number;
680
681 v_index PLS_INTEGER := 1;
682 BEGIN
683
684 FOR i IN 1..p_upper_limit LOOP
685
686 IF(xfr_rules.t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
687
688 t_rule_id(v_index) := xfr_rules.t_rule_id(i);
689 t_reason_id(v_index) := xfr_rules.t_reason_id(i);
690 t_rule_folder_id(v_index) := xfr_rules.t_rule_folder_id(i);
691 t_devl_project_id(v_index) := xfr_rules.t_devl_project_id(i);
692 t_invalid_flag(v_index) := xfr_rules.t_invalid_flag(i);
693 t_desc_text(v_index) := xfr_rules.t_desc_text(i);
694 t_name(v_index) := xfr_rules.t_name(i);
695 t_rule_type(v_index) := xfr_rules.t_rule_type(i);
696 t_expr_rule_type(v_index) := xfr_rules.t_expr_rule_type(i);
697 t_component_id(v_index) := xfr_rules.t_component_id(i);
698 t_model_ref_expl_id(v_index) := xfr_rules.t_model_ref_expl_id(i);
699 t_reason_type(v_index) := xfr_rules.t_reason_type(i);
700 t_disabled_flag(v_index) := xfr_rules.t_disabled_flag(i);
701 t_orig_sys_ref(v_index) := xfr_rules.t_orig_sys_ref(i);
702 t_deleted_flag(v_index) := xfr_rules.t_deleted_flag(i);
703 t_security_mask(v_index) := xfr_rules.t_security_mask(i);
704 t_checkout_user(v_index) := xfr_rules.t_checkout_user(i);
705 t_last_update_login(v_index) := xfr_rules.t_last_update_login(i);
706 t_effective_usage_mask(v_index) := xfr_rules.t_effective_usage_mask(i);
707 t_seq_nbr(v_index) := xfr_rules.t_seq_nbr(i);
708 t_effective_from(v_index) := xfr_rules.t_effective_from(i);
709 t_effective_until(v_index) := xfr_rules.t_effective_until(i);
710 t_effectivity_set_id(v_index) := xfr_rules.t_effectivity_set_id(i);
711 t_unsatisfied_msg_id(v_index) := xfr_rules.t_unsatisfied_msg_id(i);
712 t_unsatisfied_msg_source(v_index) := xfr_rules.t_unsatisfied_msg_source(i);
713 t_signature_id(v_index) := xfr_rules.t_signature_id(i);
714 t_template_primitive_flag(v_index) := xfr_rules.t_template_primitive_flag(i);
715 t_presentation_flag(v_index) := xfr_rules.t_presentation_flag(i);
716 t_template_token(v_index) := xfr_rules.t_template_token(i);
717 t_rule_text(v_index) := xfr_rules.t_rule_text(i);
721 t_mutable_flag(v_index) := xfr_rules.t_mutable_flag(i);
718 t_notes(v_index) := xfr_rules.t_notes(i);
719 t_class_name(v_index) := xfr_rules.t_class_name(i);
720 t_instantiation_scope(v_index) := xfr_rules.t_instantiation_scope(i);
722 t_seeded_flag(v_index) := xfr_rules.t_seeded_flag(i);
723 t_ui_def_id(v_index) := xfr_rules.t_ui_def_id(i);
724 t_ui_page_id(v_index) := xfr_rules.t_ui_page_id(i);
725 t_ui_page_element_id(v_index) := xfr_rules.t_ui_page_element_id(i);
726 t_rule_folder_type(v_index) := xfr_rules.t_rule_folder_type(i);
727
728 v_index := v_index + 1;
729 END IF;
730 END LOOP;
731
732 --FORALL i IN 1..t_rule_id.COUNT does not work in 8i because rule_text and noted are CLOB columns.
733
734 FOR i IN 1..t_rule_id.COUNT LOOP
735
736 INSERT INTO cz_rules
737 (rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
738 name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
739 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
740 effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
741 unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
742 presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
743 mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type)
744 VALUES
745 (t_rule_id(i), t_reason_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_invalid_flag(i),
746 t_desc_text(i), t_name(i), t_rule_type(i), t_expr_rule_type(i), t_component_id(i),
747 t_model_ref_expl_id(i), t_reason_type(i), t_disabled_flag(i), t_orig_sys_ref(i), t_deleted_flag(i),
748 t_security_mask(i), t_checkout_user(i), t_last_update_login(i), t_effective_usage_mask(i),
749 t_seq_nbr(i), t_effective_from(i), t_effective_until(i), t_effectivity_set_id(i),
750 t_unsatisfied_msg_id(i), t_unsatisfied_msg_source(i), t_signature_id(i), t_template_primitive_flag(i),
751 t_presentation_flag(i), t_template_token(i), t_rule_text(i), t_notes(i), t_class_name(i),
752 t_instantiation_scope(i), t_mutable_flag(i), t_seeded_flag(i), t_ui_def_id(i), t_ui_page_id(i),
753 t_ui_page_element_id(i), t_rule_folder_type(i));
754 END LOOP;
755
756 FORALL i IN 1..t_rule_folder_id.COUNT
757 INSERT INTO cz_rule_folders
758 (rule_folder_id, parent_rule_folder_id, devl_project_id, desc_text, name, object_type, folder_type,
759 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
760 effective_usage_mask, tree_seq, effective_from, effective_until, effectivity_set_id)
761 VALUES
762 (t_rule_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_desc_text(i), t_name(i),
763 DECODE(t_rule_type(i), CZRI_TYPE_COMPANION_RULE, CZRI_FOLDER_TYPE_CX, CZRI_FOLDER_TYPE_RULE),
764 t_rule_folder_type(i), t_disabled_flag(i), t_orig_sys_ref(i), t_deleted_flag(i),
765 t_security_mask(i), t_checkout_user(i), t_last_update_login(i), t_effective_usage_mask(i),
766 t_seq_nbr(i), t_effective_from(i), t_effective_until(i), t_effectivity_set_id(i));
767
768 RETURN TRUE;
769
770 EXCEPTION
771 WHEN OTHERS THEN
772 RETURN FALSE;
773 END insert_online_data;
774 ---------------------------------------------------------------------------------------
775 FUNCTION update_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
776
777 t_rule_id table_of_number;
778 t_reason_id table_of_number;
779 t_rule_folder_id table_of_number;
780 t_devl_project_id table_of_number;
781 t_invalid_flag table_of_varchar;
782 t_desc_text table_of_varchar;
783 t_name table_of_varchar;
784 t_rule_type table_of_number;
785 t_expr_rule_type table_of_number;
786 t_component_id table_of_number;
787 t_model_ref_expl_id table_of_number;
788 t_reason_type table_of_number;
789 t_disabled_flag table_of_varchar;
790 t_orig_sys_ref table_of_varchar;
791 t_deleted_flag table_of_varchar;
792 t_security_mask table_of_varchar;
793 t_checkout_user table_of_varchar;
794 t_last_update_login table_of_number;
795 t_effective_usage_mask table_of_varchar;
796 t_seq_nbr table_of_number;
797 t_effective_from table_of_date;
798 t_effective_until table_of_date;
799 t_effectivity_set_id table_of_number;
800 t_unsatisfied_msg_id table_of_number;
801 t_unsatisfied_msg_source table_of_varchar;
802 t_signature_id table_of_number;
803 t_template_primitive_flag table_of_varchar;
804 t_presentation_flag table_of_varchar;
805 t_template_token table_of_varchar;
806 t_rule_text table_of_clob;
807 t_notes table_of_clob;
808 t_class_name table_of_varchar;
809 t_instantiation_scope table_of_number;
810 t_mutable_flag table_of_varchar;
811 t_seeded_flag table_of_varchar;
812 t_ui_def_id table_of_number;
813 t_ui_page_id table_of_number;
817 v_index PLS_INTEGER := 1;
814 t_ui_page_element_id table_of_number;
815 t_rule_folder_type table_of_number;
816
818 BEGIN
819
820 FOR i IN 1..p_upper_limit LOOP
821
822 IF(xfr_rules.t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
823
824 t_rule_id(v_index) := xfr_rules.t_rule_id(i);
825 t_reason_id(v_index) := xfr_rules.t_reason_id(i);
826 t_rule_folder_id(v_index) := xfr_rules.t_rule_folder_id(i);
827 t_devl_project_id(v_index) := xfr_rules.t_devl_project_id(i);
828 t_invalid_flag(v_index) := xfr_rules.t_invalid_flag(i);
829 t_desc_text(v_index) := xfr_rules.t_desc_text(i);
830 t_name(v_index) := xfr_rules.t_name(i);
831 t_rule_type(v_index) := xfr_rules.t_rule_type(i);
832 t_expr_rule_type(v_index) := xfr_rules.t_expr_rule_type(i);
833 t_component_id(v_index) := xfr_rules.t_component_id(i);
834 t_model_ref_expl_id(v_index) := xfr_rules.t_model_ref_expl_id(i);
835 t_reason_type(v_index) := xfr_rules.t_reason_type(i);
836 t_disabled_flag(v_index) := xfr_rules.t_disabled_flag(i);
837 t_orig_sys_ref(v_index) := xfr_rules.t_orig_sys_ref(i);
838 t_deleted_flag(v_index) := xfr_rules.t_deleted_flag(i);
839 t_security_mask(v_index) := xfr_rules.t_security_mask(i);
840 t_checkout_user(v_index) := xfr_rules.t_checkout_user(i);
841 t_last_update_login(v_index) := xfr_rules.t_last_update_login(i);
842 t_effective_usage_mask(v_index) := xfr_rules.t_effective_usage_mask(i);
843 t_seq_nbr(v_index) := xfr_rules.t_seq_nbr(i);
844 t_effective_from(v_index) := xfr_rules.t_effective_from(i);
845 t_effective_until(v_index) := xfr_rules.t_effective_until(i);
846 t_effectivity_set_id(v_index) := xfr_rules.t_effectivity_set_id(i);
847 t_unsatisfied_msg_id(v_index) := xfr_rules.t_unsatisfied_msg_id(i);
848 t_unsatisfied_msg_source(v_index) := xfr_rules.t_unsatisfied_msg_source(i);
849 t_signature_id(v_index) := xfr_rules.t_signature_id(i);
850 t_template_primitive_flag(v_index) := xfr_rules.t_template_primitive_flag(i);
851 t_presentation_flag(v_index) := xfr_rules.t_presentation_flag(i);
852 t_template_token(v_index) := xfr_rules.t_template_token(i);
853 t_rule_text(v_index) := xfr_rules.t_rule_text(i);
854 t_notes(v_index) := xfr_rules.t_notes(i);
855 t_class_name(v_index) := xfr_rules.t_class_name(i);
856 t_instantiation_scope(v_index) := xfr_rules.t_instantiation_scope(i);
857 t_mutable_flag(v_index) := xfr_rules.t_mutable_flag(i);
858 t_seeded_flag(v_index) := xfr_rules.t_seeded_flag(i);
859 t_ui_def_id(v_index) := xfr_rules.t_ui_def_id(i);
860 t_ui_page_id(v_index) := xfr_rules.t_ui_page_id(i);
861 t_ui_page_element_id(v_index) := xfr_rules.t_ui_page_element_id(i);
862 t_rule_folder_type(v_index) :=xfr_rules.t_rule_folder_type(i);
863
864 v_index := v_index + 1;
865 END IF;
866 END LOOP;
867
868 --FORALL i IN 1..t_rule_id.COUNT does not work in 8i because rule_text and noted are CLOB columns.
869
870 FOR i IN 1..t_rule_id.COUNT LOOP
871
872 UPDATE cz_rules SET
873 reason_id = t_reason_id(i),
874 rule_folder_id = t_rule_folder_id(i),
875 devl_project_id = t_devl_project_id(i),
876 invalid_flag = t_invalid_flag(i),
877 desc_text = t_desc_text(i),
878 name = t_name(i),
879 rule_type = t_rule_type(i),
880 expr_rule_type = t_expr_rule_type(i),
881 component_id = t_component_id(i),
882 model_ref_expl_id = t_model_ref_expl_id(i),
883 reason_type = t_reason_type(i),
884 disabled_flag = t_disabled_flag(i),
885 orig_sys_ref = t_orig_sys_ref(i),
886 deleted_flag = t_deleted_flag(i),
887 security_mask = t_security_mask(i),
888 checkout_user = t_checkout_user(i),
889 last_update_login = t_last_update_login(i),
890 effective_usage_mask = t_effective_usage_mask(i),
891 seq_nbr = t_seq_nbr(i),
892 effective_from = t_effective_from(i),
893 effective_until = t_effective_until(i),
894 effectivity_set_id = t_effectivity_set_id(i),
895 unsatisfied_msg_id = t_unsatisfied_msg_id(i),
896 unsatisfied_msg_source = t_unsatisfied_msg_source(i),
897 signature_id = t_signature_id(i),
898 template_primitive_flag = t_template_primitive_flag(i),
899 presentation_flag = t_presentation_flag(i),
900 template_token = t_template_token(i),
901 rule_text = t_rule_text(i),
902 notes = t_notes(i),
903 class_name = t_class_name(i),
904 instantiation_scope = t_instantiation_scope(i),
905 mutable_flag = t_mutable_flag(i),
906 seeded_flag = t_seeded_flag(i),
907 ui_def_id = t_ui_def_id(i),
908 ui_page_id = t_ui_page_id(i),
909 ui_page_element_id = t_ui_page_element_id(i),
910 rule_folder_type = t_rule_folder_type(i)
911 WHERE rule_id = t_rule_id(i);
912 END LOOP;
913
914 FORALL i IN 1..t_rule_id.COUNT
915 UPDATE cz_rule_folders SET
916 parent_rule_folder_id = t_rule_folder_id(i),
917 desc_text = t_desc_text(i),
918 name = t_name(i),
919 folder_type = t_rule_folder_type(i),
920 tree_seq = t_seq_nbr(i),
924 checkout_user = t_checkout_user(i),
921 disabled_flag = t_disabled_flag(i),
922 deleted_flag = t_deleted_flag(i),
923 security_mask = t_security_mask(i),
925 last_update_login = t_last_update_login(i),
926 effective_usage_mask = t_effective_usage_mask(i),
927 effective_from = t_effective_from(i),
928 effective_until = t_effective_until(i),
929 effectivity_set_id = t_effectivity_set_id(i)
930 WHERE rule_folder_id = t_rule_id(i)
931 AND object_type = CZRI_FOLDER_TYPE_RULE;
932
933 RETURN TRUE;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 RETURN FALSE;
938 END update_online_data;
939 ---------------------------------------------------------------------------------------
940 BEGIN
941 OPEN c_rec;
942 LOOP
943
944 t_rowid.DELETE;
945 t_rule_id.DELETE;
946 t_reason_id.DELETE;
947 t_rule_folder_id.DELETE;
948 t_devl_project_id.DELETE;
949 t_invalid_flag.DELETE;
950 t_desc_text.DELETE;
951 t_name.DELETE;
952 t_rule_type.DELETE;
953 t_expr_rule_type.DELETE;
954 t_component_id.DELETE;
955 t_model_ref_expl_id.DELETE;
956 t_reason_type.DELETE;
957 t_disabled_flag.DELETE;
958 t_orig_sys_ref.DELETE;
959 t_deleted_flag.DELETE;
960 t_security_mask.DELETE;
961 t_checkout_user.DELETE;
962 t_last_update_login.DELETE;
963 t_effective_usage_mask.DELETE;
964 t_seq_nbr.DELETE;
965 t_effective_from.DELETE;
966 t_effective_until.DELETE;
967 t_effectivity_set_id.DELETE;
968 t_unsatisfied_msg_id.DELETE;
969 t_unsatisfied_msg_source.DELETE;
970 t_signature_id.DELETE;
971 t_template_primitive_flag.DELETE;
972 t_presentation_flag.DELETE;
973 t_template_token.DELETE;
974 t_rule_text.DELETE;
975 t_notes.DELETE;
976 t_class_name.DELETE;
977 t_instantiation_scope.DELETE;
978 t_mutable_flag.DELETE;
979 t_seeded_flag.DELETE;
980 t_ui_def_id.DELETE;
981 t_ui_page_id.DELETE;
982 t_ui_page_element_id.DELETE;
983 t_rule_folder_type.DELETE;
984 t_disposition.DELETE;
985 t_message.DELETE;
986
987 FETCH c_rec BULK COLLECT INTO
988 t_rowid, t_rule_id, t_reason_id, t_rule_folder_id, t_devl_project_id, t_invalid_flag, t_desc_text, t_name,
989 t_rule_type, t_expr_rule_type, t_component_id, t_model_ref_expl_id, t_reason_type, t_disabled_flag,
990 t_orig_sys_ref, t_deleted_flag, t_security_mask, t_checkout_user, t_last_update_login, t_effective_usage_mask,
991 t_seq_nbr, t_effective_from, t_effective_until, t_effectivity_set_id, t_unsatisfied_msg_id,
992 t_unsatisfied_msg_source, t_signature_id, t_template_primitive_flag, t_presentation_flag, t_template_token,
993 t_rule_text, t_notes, t_class_name, t_instantiation_scope, t_mutable_flag, t_seeded_flag, t_ui_def_id,
994 t_ui_page_id, t_ui_page_element_id, t_rule_folder_type, t_disposition, t_message
995 LIMIT p_commit_size;
996 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
997
998 FOR i IN 1..t_rowid.COUNT LOOP
999
1000 t_message(i) := NULL;
1001 END LOOP;
1002
1003 IF(NOT insert_online_data(t_rowid.COUNT))THEN
1004
1005 FOR i IN 1..t_rowid.COUNT LOOP
1006
1007 IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
1008
1009 BEGIN
1010
1011 SAVEPOINT insert_rule_record;
1012
1013 INSERT INTO cz_rules
1014 (rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
1015 name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
1016 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
1017 effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
1018 unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
1019 presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
1020 mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type)
1021 VALUES
1022 (t_rule_id(i), t_reason_id(i), t_rule_folder_id(i), t_devl_project_id(i),
1023 t_invalid_flag(i), t_desc_text(i), t_name(i), t_rule_type(i), t_expr_rule_type(i),
1024 t_component_id(i), t_model_ref_expl_id(i), t_reason_type(i), t_disabled_flag(i),
1025 t_orig_sys_ref(i), t_deleted_flag(i), t_security_mask(i), t_checkout_user(i),
1026 t_last_update_login(i), t_effective_usage_mask(i), t_seq_nbr(i), t_effective_from(i),
1027 t_effective_until(i), t_effectivity_set_id(i), t_unsatisfied_msg_id(i),
1028 t_unsatisfied_msg_source(i), t_signature_id(i), t_template_primitive_flag(i),
1029 t_presentation_flag(i), t_template_token(i), t_rule_text(i), t_notes(i),
1030 t_class_name(i), t_instantiation_scope(i), t_mutable_flag(i), t_seeded_flag(i),
1031 t_ui_def_id(i), t_ui_page_id(i), t_ui_page_element_id(i), t_rule_folder_type(i));
1032
1033 INSERT INTO cz_rule_folders
1034 (rule_folder_id, parent_rule_folder_id, devl_project_id, desc_text, name, object_type, folder_type,
1035 disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
1039 DECODE(t_rule_type(i), CZRI_TYPE_COMPANION_RULE, CZRI_FOLDER_TYPE_CX, CZRI_FOLDER_TYPE_RULE),
1036 effective_usage_mask, tree_seq, effective_from, effective_until, effectivity_set_id)
1037 VALUES
1038 (t_rule_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_desc_text(i), t_name(i),
1040 t_rule_folder_type(i), t_disabled_flag(i), t_orig_sys_ref(i),
1041 t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_last_update_login(i),
1042 t_effective_usage_mask(i), t_seq_nbr(i), t_effective_from(i), t_effective_until(i),
1043 t_effectivity_set_id(i));
1044
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047 t_message(i) := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1048 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1049 ROLLBACK TO insert_rule_record;
1050 END;
1051 END IF;
1052
1053 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1054
1055 p_errors := p_errors + 1;
1056
1057 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1058
1059 --Update the already processed records here.
1060
1061 update_table_data(i);
1062 COMMIT;
1063 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1064 END IF;
1065 END IF;
1066 END LOOP;
1067 END IF;
1068
1069 IF(NOT update_online_data(t_rowid.COUNT))THEN
1070
1071 FOR i IN 1..t_rowid.COUNT LOOP
1072
1073 IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
1074
1075 BEGIN
1076
1077 SAVEPOINT update_rule_record;
1078
1079 UPDATE cz_rules SET
1080 reason_id = t_reason_id(i),
1081 rule_folder_id = t_rule_folder_id(i),
1082 devl_project_id = t_devl_project_id(i),
1083 invalid_flag = t_invalid_flag(i),
1084 desc_text = t_desc_text(i),
1085 name = t_name(i),
1086 rule_type = t_rule_type(i),
1087 expr_rule_type = t_expr_rule_type(i),
1088 component_id = t_component_id(i),
1089 model_ref_expl_id = t_model_ref_expl_id(i),
1090 reason_type = t_reason_type(i),
1091 disabled_flag = t_disabled_flag(i),
1092 orig_sys_ref = t_orig_sys_ref(i),
1093 deleted_flag = t_deleted_flag(i),
1094 security_mask = t_security_mask(i),
1095 checkout_user = t_checkout_user(i),
1096 last_update_login = t_last_update_login(i),
1097 effective_usage_mask = t_effective_usage_mask(i),
1098 seq_nbr = t_seq_nbr(i),
1099 effective_from = t_effective_from(i),
1100 effective_until = t_effective_until(i),
1101 effectivity_set_id = t_effectivity_set_id(i),
1102 unsatisfied_msg_id = t_unsatisfied_msg_id(i),
1103 unsatisfied_msg_source = t_unsatisfied_msg_source(i),
1104 signature_id = t_signature_id(i),
1105 template_primitive_flag = t_template_primitive_flag(i),
1106 presentation_flag = t_presentation_flag(i),
1107 template_token = t_template_token(i),
1108 rule_text = t_rule_text(i),
1109 notes = t_notes(i),
1110 class_name = t_class_name(i),
1111 instantiation_scope = t_instantiation_scope(i),
1112 mutable_flag = t_mutable_flag(i),
1113 seeded_flag = t_seeded_flag(i),
1114 ui_def_id = t_ui_def_id(i),
1115 ui_page_id = t_ui_page_id(i),
1116 ui_page_element_id = t_ui_page_element_id(i),
1117 rule_folder_type = t_rule_folder_type(i)
1118 WHERE rule_id = t_rule_id(i);
1119
1120 UPDATE cz_rule_folders SET
1121 parent_rule_folder_id = t_rule_folder_id(i),
1122 desc_text = t_desc_text(i),
1123 name = t_name(i),
1124 folder_type = t_rule_folder_type(i),
1125 tree_seq = t_seq_nbr(i),
1126 disabled_flag = t_disabled_flag(i),
1127 deleted_flag = t_deleted_flag(i),
1128 security_mask = t_security_mask(i),
1129 checkout_user = t_checkout_user(i),
1130 last_update_login = t_last_update_login(i),
1131 effective_usage_mask = t_effective_usage_mask(i),
1132 effective_from = t_effective_from(i),
1133 effective_until = t_effective_until(i),
1134 effectivity_set_id = t_effectivity_set_id(i)
1135 WHERE rule_folder_id = t_rule_id(i)
1136 AND object_type = CZRI_FOLDER_TYPE_RULE;
1137
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 t_message(i) := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1141 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1142 ROLLBACK TO update_rule_record;
1143 END;
1144 END IF;
1145
1146 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1147
1148 p_errors := p_errors + 1;
1149
1150 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1151
1152 --Update the already processed records here.
1153
1154 update_table_data(i);
1155 COMMIT;
1156 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1157 END IF;
1158 END IF;
1159 END LOOP;
1160 END IF;
1161
1165
1162 update_table_data(t_rowid.COUNT);
1163 COMMIT;
1164 END LOOP;
1166 CLOSE c_rec;
1167 EXCEPTION
1168 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
1169 CLOSE c_rec;
1170 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
1171 x_msg_count := 1;
1172 report(x_msg_data, p_run_id, 'xfr_rules', v_debug);
1173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1174 RAISE CZRI_ERR_FATAL_ERROR;
1175 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1176 CLOSE c_rec;
1177 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1178 x_msg_count := 1;
1179 report(x_msg_data, p_run_id, 'xfr_rules', v_debug);
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1181 RAISE CZRI_ERR_FATAL_ERROR;
1182 END;
1183 ---------------------------------------------------------------------------------------
1184 PROCEDURE rpt_rules(p_api_version IN NUMBER,
1185 p_run_id IN NUMBER,
1186 x_return_status IN OUT NOCOPY VARCHAR2,
1187 x_msg_count IN OUT NOCOPY NUMBER,
1188 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1189
1190 v_debug NUMBER := 9000;
1191 BEGIN
1192 FOR c_stat IN (SELECT disposition, rec_status, COUNT(*) as records
1193 FROM cz_imp_rules
1194 WHERE run_id = p_run_id
1195 AND rec_status IS NOT NULL
1196 AND disposition IS NOT NULL
1197 GROUP BY disposition, rec_status) LOOP
1198
1199 INSERT INTO cz_xfr_run_results (run_id, imp_table, disposition, rec_status, records)
1200 VALUES (p_run_id, 'CZ_IMP_RULES', c_stat.disposition, c_stat.rec_status, c_stat.records);
1201 END LOOP;
1202
1203 COMMIT;
1204 EXCEPTION
1205 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1206 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1207 x_msg_count := 1;
1208 report(x_msg_data, p_run_id, 'rpt_rules', v_debug);
1209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1210 RAISE CZRI_ERR_REPORT_ERROR;
1211 END;
1212 ---------------------------------------------------------------------------------------
1213 PROCEDURE restat_rules(p_api_version IN NUMBER,
1214 p_run_id IN NUMBER,
1215 x_return_status IN OUT NOCOPY VARCHAR2,
1216 x_msg_count IN OUT NOCOPY NUMBER,
1217 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1218
1219 v_debug NUMBER := 11000;
1220 BEGIN
1221
1222 DELETE FROM cz_xfr_run_results WHERE run_id = p_run_id;
1223 rpt_rules(p_api_version, p_run_id, x_return_status, x_msg_count, x_msg_data);
1224 END;
1225 ---------------------------------------------------------------------------------------
1226 PROCEDURE import_rules(p_api_version IN NUMBER,
1227 p_run_id IN NUMBER,
1228 p_maximum_errors IN PLS_INTEGER,
1229 p_commit_size IN PLS_INTEGER,
1230 p_errors IN OUT NOCOPY PLS_INTEGER,
1231 x_return_status IN OUT NOCOPY VARCHAR2,
1232 x_msg_count IN OUT NOCOPY NUMBER,
1233 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1234 BEGIN
1235 cnd_rules(p_api_version,
1236 p_run_id,
1237 p_maximum_errors,
1238 p_commit_size,
1239 p_errors,
1240 x_return_status,
1241 x_msg_count,
1242 x_msg_data);
1243
1244 krs_rules(p_api_version,
1245 p_run_id,
1246 p_maximum_errors,
1247 p_commit_size,
1248 p_errors,
1249 x_return_status,
1250 x_msg_count,
1251 x_msg_data);
1252
1253 xfr_rules(p_api_version,
1254 p_run_id,
1255 p_maximum_errors,
1256 p_commit_size,
1257 p_errors,
1258 x_return_status,
1259 x_msg_count,
1260 x_msg_data);
1261
1262 rpt_rules(p_api_version,
1263 p_run_id,
1264 x_return_status,
1265 x_msg_count,
1266 x_msg_data);
1267
1268 EXCEPTION
1269 WHEN CZRI_ERR_REPORT_ERROR THEN
1270 RAISE CZRI_ERR_FATAL_ERROR;
1271 WHEN OTHERS THEN
1272 rpt_rules(p_api_version,
1273 p_run_id,
1274 x_return_status,
1275 x_msg_count,
1276 x_msg_data);
1277 RAISE;
1278 END;
1279 ---------------------------------------------------------------------------------------
1280 PROCEDURE cnd_localized_texts(p_api_version IN NUMBER,
1281 p_run_id IN NUMBER,
1282 p_maximum_errors IN PLS_INTEGER,
1283 p_commit_size IN PLS_INTEGER,
1284 p_errors IN OUT NOCOPY PLS_INTEGER,
1285 x_return_status IN OUT NOCOPY VARCHAR2,
1286 x_msg_count IN OUT NOCOPY NUMBER,
1287 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1288
1289 v_debug NUMBER := 2000;
1290
1291 CURSOR c_rec IS
1292 SELECT ROWID, model_id, message, orig_sys_ref, language, source_lang,
1296 AND rec_status IS NULL
1293 seeded_flag, deleted_flag, disposition
1294 FROM cz_imp_localized_texts
1295 WHERE run_id = p_run_id
1297 AND disposition IS NULL;
1298
1299 t_rowid table_of_rowid;
1300 t_model_id table_of_number;
1301 t_message table_of_varchar;
1302 t_orig_sys_ref table_of_varchar;
1303 t_language table_of_varchar;
1304 t_source_lang table_of_varchar;
1305 t_seeded_flag table_of_varchar;
1306 t_deleted_flag table_of_varchar;
1307 t_disposition table_of_varchar;
1308
1309 validateModel VARCHAR2(4000) :=
1310 'SELECT NULL ' ||
1311 ' FROM cz_rp_entries ' ||
1312 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
1313 ' AND object_type = ''' || CZRI_REPOSITORY_PROJECT || ''' ' ||
1314 ' AND object_id = :1';
1315
1316 h_ValidModel table_of_number_index_VC2;
1317 h_InvalidModel table_of_number_index_VC2;
1318 v_null NUMBER;
1319
1320 ---------------------------------------------------------------------------------------
1321 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
1322 BEGIN
1323
1324 FORALL i IN 1..p_upper_limit
1325 UPDATE cz_imp_localized_texts SET
1326 message = t_message(i),
1327 seeded_flag = t_seeded_flag(i),
1328 deleted_flag = t_deleted_flag(i),
1329 rec_status = CZRI_RECSTATUS_CND,
1330 disposition = t_disposition(i)
1331 WHERE ROWID = t_rowid(i);
1332 END;
1333 ---------------------------------------------------------------------------------------
1334 BEGIN
1335
1336 OPEN c_rec;
1337 LOOP
1338
1339 t_rowid.DELETE;
1340 t_model_id.DELETE;
1341 t_message.DELETE;
1342 t_orig_sys_ref.DELETE;
1343 t_language.DELETE;
1344 t_source_lang.DELETE;
1345 t_seeded_flag.DELETE;
1346 t_deleted_flag.DELETE;
1347 t_disposition.DELETE;
1348
1349 FETCH c_rec BULK COLLECT INTO
1350 t_rowid, t_model_id, t_message, t_orig_sys_ref, t_language, t_source_lang, t_seeded_flag, t_deleted_flag,
1351 t_disposition
1352 LIMIT p_commit_size;
1353 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
1354
1355 FOR i IN 1..t_rowid.COUNT LOOP
1356
1357 t_message(i) := NULL;
1358 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1359
1360 IF(t_model_id(i) IS NULL)THEN
1361
1362 t_message(i) := cz_utils.get_text('CZRI_TXT_NULLMODELID');
1363
1364 ELSIF(h_InvalidModel.EXISTS(t_model_id(i)))THEN
1365
1366 t_message(i) := cz_utils.get_text('CZRI_TXT_INVALIDMODEL');
1367
1368 ELSIF(t_orig_sys_ref(i) IS NULL)THEN
1369
1370 t_message(i) := cz_utils.get_text('CZRI_TXT_NULLORIGSYSREF');
1371
1372 ELSIF(t_language(i) IS NULL)THEN
1373
1374 t_message(i) := cz_utils.get_text('CZRI_TXT_NULLLANGUAGE');
1375
1376 ELSIF(t_source_lang(i) IS NULL)THEN
1377
1378 t_message(i) := cz_utils.get_text('CZRI_TXT_NULLSOURCELANG');
1379
1380 ELSE
1381
1382 t_disposition(i) := CZRI_DISPOSITION_PASSED;
1383 END IF;
1384
1385 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED AND (NOT h_ValidModel.EXISTS(t_model_id(i))))THEN
1386 BEGIN
1387
1388 EXECUTE IMMEDIATE validateModel INTO v_null USING t_model_id(i);
1389 h_ValidModel(t_model_id(i)) := 1;
1390
1391 EXCEPTION
1392 WHEN NO_DATA_FOUND THEN
1393 t_message(i) := cz_utils.get_text('CZRI_TXT_INVALIDMODEL');
1394 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1395 h_InvalidModel(t_model_id(i)) := 1;
1396 END;
1397 END IF;
1398
1399 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1400
1401 p_errors := p_errors + 1;
1402
1403 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1404
1405 --Update the already processed records here.
1406
1407 update_table_data(i);
1408 COMMIT;
1409 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1410 END IF;
1411 END IF;
1412
1413 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
1414
1415 --All the validations passed, condition the record.
1416 --The seeded flag is unconditionally set to '0'.
1417
1418 t_seeded_flag(i) := CZRI_FLAG_NOT_SEEDED;
1419 IF(t_deleted_flag(i) IS NULL)THEN t_deleted_flag(i) := CZRI_FLAG_NOT_DELETED; END IF;
1420 END IF;
1421 END LOOP;
1422
1423 --Update all the records from memory here.
1424
1425 update_table_data(t_rowid.COUNT);
1426 COMMIT;
1427 END LOOP;
1428
1429 CLOSE c_rec;
1430 EXCEPTION
1431 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
1432 CLOSE c_rec;
1433 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
1434 x_msg_count := 1;
1435 report(x_msg_data, p_run_id, 'cnd_localized_texts', v_debug);
1436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1437 RAISE CZRI_ERR_FATAL_ERROR;
1438 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1439 CLOSE c_rec;
1440 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1441 x_msg_count := 1;
1442 report(x_msg_data, p_run_id, 'cnd_localized_texts', v_debug);
1443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1447 PROCEDURE krs_localized_texts(p_api_version IN NUMBER,
1444 RAISE CZRI_ERR_FATAL_ERROR;
1445 END;
1446 ---------------------------------------------------------------------------------------
1448 p_run_id IN NUMBER,
1449 p_maximum_errors IN PLS_INTEGER,
1450 p_commit_size IN PLS_INTEGER,
1451 p_errors IN OUT NOCOPY PLS_INTEGER,
1452 x_return_status IN OUT NOCOPY VARCHAR2,
1453 x_msg_count IN OUT NOCOPY NUMBER,
1454 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1455
1456 v_debug NUMBER := 3000;
1457
1458 CURSOR c_rec IS
1459 SELECT ROWID, intl_text_id, model_id, message, orig_sys_ref, language, rec_status, disposition
1460 FROM cz_imp_localized_texts
1461 WHERE run_id = p_run_id
1462 AND rec_status = CZRI_RECSTATUS_CND
1463 AND disposition = CZRI_DISPOSITION_PASSED
1464 ORDER BY model_id, orig_sys_ref, language;
1465
1466 t_rowid table_of_rowid;
1467 t_intl_text_id table_of_number;
1468 t_model_id table_of_number;
1469 t_message table_of_varchar;
1470 t_orig_sys_ref table_of_varchar;
1471 t_language table_of_varchar;
1472 t_rec_status table_of_varchar;
1473 t_disposition table_of_varchar;
1474
1475 resolveIntlTextId VARCHAR2(4000) :=
1476 'SELECT intl_text_id ' ||
1477 ' FROM cz_localized_texts ' ||
1478 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
1479 ' AND model_id = :1 ' ||
1480 ' AND language = :2 ' ||
1481 ' AND orig_sys_ref = :3';
1482
1483 getIntlTextId VARCHAR2(4000) :=
1484 'SELECT intl_text_id ' ||
1485 ' FROM cz_localized_texts ' ||
1486 ' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
1487 ' AND model_id = :1 ' ||
1488 ' AND orig_sys_ref = :2 ' ||
1489 ' AND ROWNUM = 1';
1490
1491 last_orig_sys_ref cz_imp_localized_texts.orig_sys_ref%TYPE;
1492 last_model_id cz_imp_localized_texts.model_id%TYPE;
1493 last_language cz_imp_localized_texts.language%TYPE;
1494 last_intl_text_id cz_imp_localized_texts.intl_text_id%TYPE;
1495
1496 last_id_allocated NUMBER := NULL;
1497 next_id_to_use NUMBER := 0;
1498 id_increment NUMBER := CZRI_LOCALIZED_TEXTS_INC;
1499 ---------------------------------------------------------------------------------------
1500 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
1501 BEGIN
1502
1503 FORALL i IN 1..p_upper_limit
1504 UPDATE cz_imp_localized_texts SET
1505 intl_text_id = t_intl_text_id(i),
1506 message = t_message(i),
1507 rec_status = CZRI_RECSTATUS_KRS,
1508 disposition = t_disposition(i)
1509 WHERE ROWID = t_rowid(i);
1510 END;
1511 ---------------------------------------------------------------------------------------
1512 FUNCTION next_intl_text_id RETURN NUMBER IS
1513 id_to_return NUMBER;
1514 BEGIN
1515
1516 IF((last_id_allocated IS NULL) OR
1517 (next_id_to_use = (NVL(last_id_allocated, 0) + id_increment)))THEN
1518
1519 SELECT cz_intl_texts_s.NEXTVAL INTO last_id_allocated FROM dual;
1520 next_id_to_use := last_id_allocated;
1521 END IF;
1522
1523 id_to_return := next_id_to_use;
1524 next_id_to_use := next_id_to_use + 1;
1525 RETURN id_to_return;
1526 END;
1527 ---------------------------------------------------------------------------------------
1528 BEGIN
1529
1530 OPEN c_rec;
1531 LOOP
1532
1533 t_rowid.DELETE;
1534 t_intl_text_id.DELETE;
1535 t_model_id.DELETE;
1536 t_message.DELETE;
1537 t_orig_sys_ref.DELETE;
1538 t_language.DELETE;
1539 t_rec_status.DELETE;
1540 t_disposition.DELETE;
1541
1542 FETCH c_rec BULK COLLECT INTO
1543 t_rowid, t_intl_text_id, t_model_id, t_message, t_orig_sys_ref, t_language, t_rec_status, t_disposition
1544 LIMIT p_commit_size;
1545 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
1546
1547 FOR i IN 1..t_rowid.COUNT LOOP
1548
1549 t_message(i) := NULL;
1550
1551 IF(last_orig_sys_ref IS NOT NULL AND
1552 last_orig_sys_ref = t_orig_sys_ref(i) AND
1553 last_model_id = t_model_id(i) AND
1554 last_language = t_language(i))THEN
1555
1556 --This is a duplicate record in the source data.
1557
1558 t_message(i) := cz_utils.get_text('CZRI_TXT_DUPLICATE', 'MODELID', last_model_id);
1559 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1560 END IF;
1561
1562 IF(t_disposition(i) = CZRI_DISPOSITION_PASSED)THEN
1563
1564 --Resolve intl_text_id for existing record or generate a new one for a new record.
1565
1566 BEGIN
1567 EXECUTE IMMEDIATE resolveIntlTextId INTO t_intl_text_id(i)
1568 USING t_model_id(i), t_language(i), t_orig_sys_ref(i);
1569
1570 t_disposition(i) := CZRI_DISPOSITION_MODIFY;
1571 EXCEPTION
1572 WHEN NO_DATA_FOUND THEN
1573 t_disposition(i) := CZRI_DISPOSITION_INSERT;
1574
1575 --Bug #4053091 - need to share intl_text_id between all the records with same orig_sys_ref.
1576
1577 BEGIN
1578 EXECUTE IMMEDIATE getIntlTextId INTO t_intl_text_id(i)
1579 USING t_model_id(i), t_orig_sys_ref(i);
1580 EXCEPTION
1584
1581 WHEN NO_DATA_FOUND THEN
1582 IF(last_orig_sys_ref IS NOT NULL AND last_orig_sys_ref = t_orig_sys_ref(i) AND
1583 last_model_id = t_model_id(i))THEN
1585 t_intl_text_id(i) := last_intl_text_id;
1586 ELSE
1587
1588 t_intl_text_id(i) := next_intl_text_id;
1589 END IF;
1590 END;
1591 END;
1592 END IF;
1593
1594 IF(t_disposition(i) <> CZRI_DISPOSITION_REJECT)THEN
1595
1596 last_orig_sys_ref := t_orig_sys_ref(i);
1597 last_model_id := t_model_id(i);
1598 last_language := t_language(i);
1599 last_intl_text_id := t_intl_text_id(i);
1600 END IF;
1601
1602 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1603
1604 p_errors := p_errors + 1;
1605
1606 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1607
1608 --Update the already processed records here.
1609
1610 update_table_data(i);
1611 COMMIT;
1612 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1613 END IF;
1614 END IF;
1615 END LOOP;
1616
1617 --Update all the records from memory here.
1618
1619 update_table_data(t_rowid.COUNT);
1620 COMMIT;
1621 END LOOP;
1622
1623 CLOSE c_rec;
1624 EXCEPTION
1625 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
1626 CLOSE c_rec;
1627 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
1628 x_msg_count := 1;
1629 report(x_msg_data, p_run_id, 'krs_localized_texts', v_debug);
1630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1631 RAISE CZRI_ERR_FATAL_ERROR;
1632 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1633 CLOSE c_rec;
1634 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1635 x_msg_count := 1;
1636 report(x_msg_data, p_run_id, 'krs_localized_texts', v_debug);
1637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1638 RAISE CZRI_ERR_FATAL_ERROR;
1639 END;
1640 ---------------------------------------------------------------------------------------
1641 PROCEDURE xfr_localized_texts(p_api_version IN NUMBER,
1642 p_run_id IN NUMBER,
1643 p_maximum_errors IN PLS_INTEGER,
1644 p_commit_size IN PLS_INTEGER,
1645 p_errors IN OUT NOCOPY PLS_INTEGER,
1646 x_return_status IN OUT NOCOPY VARCHAR2,
1647 x_msg_count IN OUT NOCOPY NUMBER,
1648 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1649
1650 v_debug NUMBER := 4000;
1651
1652 --persistent_intl_text_id is taken care of by a trigger.
1653
1654 CURSOR c_rec IS
1655 SELECT ROWID,last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
1656 checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag,
1657 disposition, message
1658 FROM cz_imp_localized_texts
1659 WHERE run_id = p_run_id
1660 AND rec_status = CZRI_RECSTATUS_KRS
1661 AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY);
1662
1663 t_rowid table_of_rowid;
1664 t_last_update_login table_of_number;
1665 t_locale_id table_of_number;
1666 t_localized_str table_of_varchar;
1667 t_intl_text_id table_of_number;
1668 t_deleted_flag table_of_varchar;
1669 t_security_mask table_of_varchar;
1670 t_checkout_user table_of_varchar;
1671 t_orig_sys_ref table_of_varchar;
1672 t_language table_of_varchar;
1673 t_source_lang table_of_varchar;
1674 t_model_id table_of_number;
1675 t_seeded_flag table_of_varchar;
1676 t_disposition table_of_varchar;
1677 t_message table_of_varchar;
1678 ---------------------------------------------------------------------------------------
1679 PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
1680 BEGIN
1681
1682 --We updating rec_status to OK, not XFR, because, unlike the rules which still have
1683 --to be parsed, import of localized texts is done.
1684
1685 FORALL i IN 1..p_upper_limit
1686 UPDATE cz_imp_localized_texts SET
1687 message = t_message(i),
1688 rec_status = CZRI_RECSTATUS_OK,
1689 disposition = t_disposition(i)
1690 WHERE ROWID = t_rowid(i);
1691 END;
1692 ---------------------------------------------------------------------------------------
1693 FUNCTION insert_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
1694
1695 t_last_update_login table_of_number;
1696 t_locale_id table_of_number;
1697 t_localized_str table_of_varchar;
1698 t_intl_text_id table_of_number;
1699 t_deleted_flag table_of_varchar;
1700 t_security_mask table_of_varchar;
1701 t_checkout_user table_of_varchar;
1702 t_orig_sys_ref table_of_varchar;
1703 t_language table_of_varchar;
1704 t_source_lang table_of_varchar;
1705 t_model_id table_of_number;
1706 t_seeded_flag table_of_varchar;
1707
1708 v_index PLS_INTEGER := 1;
1709 BEGIN
1710
1711 FOR i IN 1..p_upper_limit LOOP
1712
1713 IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
1717 t_localized_str(v_index) := xfr_localized_texts.t_localized_str(i);
1714
1715 t_last_update_login(v_index) := xfr_localized_texts.t_last_update_login(i);
1716 t_locale_id(v_index) := xfr_localized_texts.t_locale_id(i);
1718 t_intl_text_id(v_index) := xfr_localized_texts.t_intl_text_id(i);
1719 t_deleted_flag(v_index) := xfr_localized_texts.t_deleted_flag(i);
1720 t_security_mask(v_index) := xfr_localized_texts.t_security_mask(i);
1721 t_checkout_user(v_index) := xfr_localized_texts.t_checkout_user(i);
1722 t_orig_sys_ref(v_index) := xfr_localized_texts.t_orig_sys_ref(i);
1723 t_language(v_index) := xfr_localized_texts.t_language(i);
1724 t_source_lang(v_index) := xfr_localized_texts.t_source_lang(i);
1725 t_model_id(v_index) := xfr_localized_texts.t_model_id(i);
1726 t_seeded_flag(v_index) := xfr_localized_texts.t_seeded_flag(i);
1727
1728 v_index := v_index + 1;
1729 END IF;
1730 END LOOP;
1731
1732 FORALL i IN 1..t_intl_text_id.COUNT
1733 INSERT INTO cz_localized_texts
1734 (last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
1735 checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag)
1736 VALUES
1737 (t_last_update_login(i), t_locale_id(i), t_localized_str(i), t_intl_text_id(i),
1738 t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_orig_sys_ref(i),
1739 t_language(i), t_source_lang(i), t_model_id(i), t_seeded_flag(i));
1740
1741 RETURN TRUE;
1742
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 RETURN FALSE;
1746 END;
1747 ---------------------------------------------------------------------------------------
1748 FUNCTION update_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
1749
1750 t_last_update_login table_of_number;
1751 t_locale_id table_of_number;
1752 t_localized_str table_of_varchar;
1753 t_intl_text_id table_of_number;
1754 t_deleted_flag table_of_varchar;
1755 t_security_mask table_of_varchar;
1756 t_checkout_user table_of_varchar;
1757 t_orig_sys_ref table_of_varchar;
1758 t_language table_of_varchar;
1759 t_source_lang table_of_varchar;
1760 t_model_id table_of_number;
1761 t_seeded_flag table_of_varchar;
1762
1763 v_index PLS_INTEGER := 1;
1764 BEGIN
1765
1766 FOR i IN 1..p_upper_limit LOOP
1767
1768 IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
1769
1770 t_last_update_login(v_index) := xfr_localized_texts.t_last_update_login(i);
1771 t_locale_id(v_index) := xfr_localized_texts.t_locale_id(i);
1772 t_localized_str(v_index) := xfr_localized_texts.t_localized_str(i);
1773 t_intl_text_id(v_index) := xfr_localized_texts.t_intl_text_id(i);
1774 t_deleted_flag(v_index) := xfr_localized_texts.t_deleted_flag(i);
1775 t_security_mask(v_index) := xfr_localized_texts.t_security_mask(i);
1776 t_checkout_user(v_index) := xfr_localized_texts.t_checkout_user(i);
1777 t_orig_sys_ref(v_index) := xfr_localized_texts.t_orig_sys_ref(i);
1778 t_language(v_index) := xfr_localized_texts.t_language(i);
1779 t_source_lang(v_index) := xfr_localized_texts.t_source_lang(i);
1780 t_model_id(v_index) := xfr_localized_texts.t_model_id(i);
1781 t_seeded_flag(v_index) := xfr_localized_texts.t_seeded_flag(i);
1782
1783 v_index := v_index + 1;
1784 END IF;
1785 END LOOP;
1786
1787 FORALL i IN 1..t_intl_text_id.COUNT
1788 UPDATE cz_localized_texts SET
1789 last_update_login = t_last_update_login(i),
1790 locale_id = t_locale_id(i),
1791 localized_str = t_localized_str(i),
1792 deleted_flag = t_deleted_flag(i),
1793 security_mask = t_security_mask(i),
1794 checkout_user = t_checkout_user(i),
1795 orig_sys_ref = t_orig_sys_ref(i),
1796 source_lang = t_source_lang(i),
1797 model_id = t_model_id(i),
1798 seeded_flag = t_seeded_flag(i)
1799 WHERE intl_text_id = t_intl_text_id(i)
1800 AND language = t_language(i);
1801
1802 RETURN TRUE;
1803
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 RETURN FALSE;
1807 END;
1808 ---------------------------------------------------------------------------------------
1809 BEGIN
1810 OPEN c_rec;
1811 LOOP
1812
1813 t_rowid.DELETE;
1814 t_last_update_login.DELETE;
1815 t_locale_id.DELETE;
1816 t_localized_str.DELETE;
1817 t_intl_text_id.DELETE;
1818 t_deleted_flag.DELETE;
1819 t_security_mask.DELETE;
1820 t_checkout_user.DELETE;
1821 t_orig_sys_ref.DELETE;
1822 t_language.DELETE;
1823 t_source_lang.DELETE;
1824 t_model_id.DELETE;
1825 t_seeded_flag.DELETE;
1826 t_disposition.DELETE;
1827 t_message.DELETE;
1828
1829 FETCH c_rec BULK COLLECT INTO
1830 t_rowid, t_last_update_login, t_locale_id, t_localized_str, t_intl_text_id, t_deleted_flag,
1831 t_security_mask, t_checkout_user, t_orig_sys_ref, t_language, t_source_lang, t_model_id,
1832 t_seeded_flag, t_disposition, t_message
1833 LIMIT p_commit_size;
1834 EXIT WHEN c_rec%NOTFOUND AND t_rowid.COUNT = 0;
1835
1836 FOR i IN 1..t_rowid.COUNT LOOP
1837
1838 t_message(i) := NULL;
1839 END LOOP;
1840
1841 IF(NOT insert_online_data(t_rowid.COUNT))THEN
1842
1843 FOR i IN 1..t_rowid.COUNT LOOP
1844
1848 INSERT INTO cz_localized_texts
1845 IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
1846
1847 BEGIN
1849 (last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
1850 checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag)
1851 VALUES
1852 (t_last_update_login(i), t_locale_id(i), t_localized_str(i), t_intl_text_id(i),
1853 t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_orig_sys_ref(i),
1854 t_language(i), t_source_lang(i), t_model_id(i), t_seeded_flag(i));
1855
1856 EXCEPTION
1857 WHEN OTHERS THEN
1858 t_message(i) := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1859 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1860 END;
1861 END IF;
1862
1863 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1864
1865 p_errors := p_errors + 1;
1866
1867 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1868
1869 --Update the already processed records here.
1870
1871 update_table_data(i);
1872 COMMIT;
1873 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1874 END IF;
1875 END IF;
1876 END LOOP;
1877 END IF;
1878
1879 IF(NOT update_online_data(t_rowid.COUNT))THEN
1880
1881 FOR i IN 1..t_rowid.COUNT LOOP
1882
1883 IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
1884
1885 BEGIN
1886 UPDATE cz_localized_texts SET
1887 last_update_login = t_last_update_login(i),
1888 locale_id = t_locale_id(i),
1889 localized_str = t_localized_str(i),
1890 deleted_flag = t_deleted_flag(i),
1891 security_mask = t_security_mask(i),
1892 checkout_user = t_checkout_user(i),
1893 orig_sys_ref = t_orig_sys_ref(i),
1894 source_lang = t_source_lang(i),
1895 model_id = t_model_id(i),
1896 seeded_flag = t_seeded_flag(i)
1897 WHERE intl_text_id = t_intl_text_id(i)
1898 AND language = t_language(i);
1899
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 t_message(i) := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1903 t_disposition(i) := CZRI_DISPOSITION_REJECT;
1904 END;
1905 END IF;
1906
1907 IF(t_disposition(i) = CZRI_DISPOSITION_REJECT)THEN
1908
1909 p_errors := p_errors + 1;
1910
1911 IF(p_errors > CZRI_MAXIMUM_ERRORS)THEN
1912
1913 --Update the already processed records here.
1914
1915 update_table_data(i);
1916 COMMIT;
1917 RAISE CZRI_ERR_MAXIMUM_ERRORS;
1918 END IF;
1919 END IF;
1920 END LOOP;
1921 END IF;
1922
1923 update_table_data(t_rowid.COUNT);
1924 COMMIT;
1925 END LOOP;
1926
1927 CLOSE c_rec;
1928 EXCEPTION
1929 WHEN CZRI_ERR_MAXIMUM_ERRORS THEN --maximum errors number exceeded.
1930 CLOSE c_rec;
1931 x_msg_data := cz_utils.get_text('CZRI_IMP_MAXIMUMERRORS', 'RUNID', p_run_id);
1932 x_msg_count := 1;
1933 report(x_msg_data, p_run_id, 'xfr_localized_texts', v_debug);
1934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1935 RAISE CZRI_ERR_FATAL_ERROR;
1936 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1937 CLOSE c_rec;
1938 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1939 x_msg_count := 1;
1940 report(x_msg_data, p_run_id, 'xfr_localized_texts', v_debug);
1941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1942 RAISE CZRI_ERR_FATAL_ERROR;
1943 END;
1944 ---------------------------------------------------------------------------------------
1945 PROCEDURE rpt_localized_texts(p_api_version IN NUMBER,
1946 p_run_id IN NUMBER,
1947 x_return_status IN OUT NOCOPY VARCHAR2,
1948 x_msg_count IN OUT NOCOPY NUMBER,
1949 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1950
1951 v_debug NUMBER := 5000;
1952 BEGIN
1953
1954 FOR c_stat IN (SELECT disposition, rec_status, COUNT(*) as records
1955 FROM cz_imp_localized_texts
1956 WHERE run_id = p_run_id
1957 AND rec_status IS NOT NULL
1958 AND disposition IS NOT NULL
1959 GROUP BY disposition, rec_status) LOOP
1960
1961 INSERT INTO cz_xfr_run_results (run_id, imp_table, disposition, rec_status, records)
1962 VALUES (p_run_id, 'CZ_IMP_LOCALIZED_TEXTS', c_stat.disposition, c_stat.rec_status, c_stat.records);
1963 END LOOP;
1964
1965 COMMIT;
1966 EXCEPTION
1967 WHEN OTHERS THEN --unexpected errors occurred in the procedure.
1968 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
1969 x_msg_count := 1;
1970 report(x_msg_data, p_run_id, 'rpt_localized_texts', v_debug);
1971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1972 RAISE CZRI_ERR_REPORT_ERROR;
1973 END;
1974 ---------------------------------------------------------------------------------------
1978 x_msg_count IN OUT NOCOPY NUMBER,
1975 PROCEDURE restat_localized_texts(p_api_version IN NUMBER,
1976 p_run_id IN NUMBER,
1977 x_return_status IN OUT NOCOPY VARCHAR2,
1979 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1980
1981 v_debug NUMBER := 10000;
1982 BEGIN
1983
1984 DELETE FROM cz_xfr_run_results WHERE run_id = p_run_id;
1985 rpt_localized_texts(p_api_version, p_run_id, x_return_status, x_msg_count, x_msg_data);
1986 END;
1987 ---------------------------------------------------------------------------------------
1988 PROCEDURE import_localized_texts(p_api_version IN NUMBER,
1989 p_run_id IN NUMBER,
1990 p_maximum_errors IN PLS_INTEGER,
1991 p_commit_size IN PLS_INTEGER,
1992 p_errors IN OUT NOCOPY PLS_INTEGER,
1993 x_return_status IN OUT NOCOPY VARCHAR2,
1994 x_msg_count IN OUT NOCOPY NUMBER,
1995 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1996 BEGIN
1997
1998 cnd_localized_texts(p_api_version,
1999 p_run_id,
2000 p_maximum_errors,
2001 p_commit_size,
2002 p_errors,
2003 x_return_status,
2004 x_msg_count,
2005 x_msg_data);
2006
2007 krs_localized_texts(p_api_version,
2008 p_run_id,
2009 p_maximum_errors,
2010 p_commit_size,
2011 p_errors,
2012 x_return_status,
2013 x_msg_count,
2014 x_msg_data);
2015
2016 xfr_localized_texts(p_api_version,
2017 p_run_id,
2018 p_maximum_errors,
2019 p_commit_size,
2020 p_errors,
2021 x_return_status,
2022 x_msg_count,
2023 x_msg_data);
2024
2025 rpt_localized_texts(p_api_version,
2026 p_run_id,
2027 x_return_status,
2028 x_msg_count,
2029 x_msg_data);
2030
2031 EXCEPTION
2032 WHEN CZRI_ERR_REPORT_ERROR THEN
2033 RAISE CZRI_ERR_FATAL_ERROR;
2034 WHEN OTHERS THEN
2035 rpt_localized_texts(p_api_version,
2036 p_run_id,
2037 x_return_status,
2038 x_msg_count,
2039 x_msg_data);
2040 RAISE;
2041 END;
2042 ---------------------------------------------------------------------------------------
2043 PROCEDURE refresh_statistics(p_api_version IN NUMBER,
2044 p_run_id IN NUMBER,
2045 x_return_status IN OUT NOCOPY VARCHAR2,
2046 x_msg_count IN OUT NOCOPY NUMBER,
2047 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2048
2049 v_debug NUMBER := 12000;
2050 BEGIN
2051
2052 x_msg_count := 0;
2053 x_return_status := FND_API.G_RET_STS_SUCCESS;
2054
2055 restat_localized_texts(p_api_version, p_run_id, x_return_status, x_msg_count, x_msg_data);
2056 restat_rules(p_api_version, p_run_id, x_return_status, x_msg_count, x_msg_data);
2057
2058 EXCEPTION
2059 WHEN CZRI_ERR_REPORT_ERROR THEN
2060 --All the logging has already been done.
2061 NULL;
2062 WHEN OTHERS THEN
2063 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2064 x_msg_count := 1;
2065 report(x_msg_data, p_run_id, 'refresh_statistics', v_debug);
2066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2067 END;
2068 ---------------------------------------------------------------------------------------
2069 PROCEDURE rule_import(p_api_version IN NUMBER,
2070 p_run_id IN OUT NOCOPY NUMBER,
2071 p_maximum_errors IN PLS_INTEGER,
2072 p_commit_size IN PLS_INTEGER,
2073 x_return_status IN OUT NOCOPY VARCHAR2,
2074 x_msg_count IN OUT NOCOPY NUMBER,
2075 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2076
2077 v_errors PLS_INTEGER;
2078 v_error_flag PLS_INTEGER;
2079 v_null PLS_INTEGER;
2080 v_api_version NUMBER := p_api_version;
2081 v_maximum_errors PLS_INTEGER := p_maximum_errors;
2082 v_commit_size PLS_INTEGER := p_commit_size;
2083 v_debug NUMBER := 1000;
2084 BEGIN
2085
2086 --Initialize the FND message stack.
2087
2088 FND_MSG_PUB.INITIALIZE;
2089
2090 --Check for other active import sessions.
2091
2092 BEGIN
2093
2094 SELECT NULL INTO v_null FROM v$session WHERE module = CZRI_MODULE_NAME;
2095 RAISE CZRI_ERR_ACTIVE_SESSIONS;
2096
2097 EXCEPTION
2098 WHEN NO_DATA_FOUND THEN
2099
2100 --Set the application module name.
2101
2102 DBMS_APPLICATION_INFO.SET_MODULE(CZRI_MODULE_NAME,'');
2103 END;
2104
2105 --Default the parameters.
2106
2110
2107 IF(v_api_version IS NULL OR v_api_version <= 0)THEN v_api_version := CZRI_API_VERSION; END IF;
2108 IF(v_maximum_errors IS NULL OR v_maximum_errors <= 0)THEN v_maximum_errors := CZRI_MAXIMUM_ERRORS; END IF;
2109 IF(v_commit_size IS NULL OR v_commit_size <= 0)THEN v_commit_size := CZRI_COMMIT_SIZE; END IF;
2111 --Initialize error counter and output parameters.
2112
2113 v_errors := 0;
2114 x_msg_count := 0;
2115 x_return_status := FND_API.G_RET_STS_SUCCESS;
2116
2117 --If necessary, generate a run_id and update the source records with this value.
2118
2119 IF(p_run_id IS NULL)THEN
2120
2121 SELECT cz_xfr_run_infos_s.NEXTVAL INTO p_run_id FROM DUAL;
2122
2123 UPDATE cz_imp_rules SET run_id = p_run_id
2124 WHERE disposition IS NULL
2125 AND rec_status IS NULL
2126 AND run_id IS NULL;
2127
2128 UPDATE cz_imp_localized_texts SET run_id = p_run_id
2129 WHERE disposition IS NULL
2130 AND rec_status IS NULL
2131 AND run_id IS NULL;
2132
2133 COMMIT;
2134 END IF;
2135
2136 BEGIN
2137
2138 SELECT 1 INTO v_error_flag FROM DUAL WHERE EXISTS
2139 (SELECT NULL FROM cz_imp_rules WHERE run_id = p_run_id);
2140
2141 EXCEPTION
2142 WHEN NO_DATA_FOUND THEN
2143 v_error_flag := 0;
2144 END;
2145
2146 --Raise an error if there is no data for the specified run_id.
2147
2148 IF(v_error_flag = 0)THEN RAISE CZRI_ERR_RUNID_INCORRECT; END IF;
2149
2150 --Create a control record for the current session.
2151
2152 BEGIN
2153
2154 SELECT NULL INTO v_null FROM cz_xfr_run_infos WHERE run_id = p_run_id;
2155 RAISE CZRI_ERR_RUNID_EXISTS;
2156
2157 EXCEPTION
2158 WHEN NO_DATA_FOUND THEN
2159
2160 INSERT INTO cz_xfr_run_infos (run_id, started, last_activity, completed)
2161 VALUES (p_run_id, SYSDATE, SYSDATE, '0');
2162 END;
2163
2164 --Call the import procedures.
2165
2166 import_localized_texts(v_api_version,
2167 p_run_id,
2168 v_maximum_errors,
2169 v_commit_size,
2170 v_errors,
2171 x_return_status,
2172 x_msg_count,
2173 x_msg_data);
2174
2175 import_rules(v_api_version,
2176 p_run_id,
2177 v_maximum_errors,
2178 v_commit_size,
2179 v_errors,
2180 x_return_status,
2181 x_msg_count,
2182 x_msg_data);
2183
2184 --Update the control record for this session.
2185
2186 UPDATE cz_xfr_run_infos SET
2187 last_activity = SYSDATE,
2188 completed = '1'
2189 WHERE run_id = p_run_id;
2190
2191 --IF there were any errors, the return status will be 'Warning', not 'Success'.
2192
2193 IF(v_errors > 0)THEN x_return_status := FND_API.G_RET_STS_ERROR; END IF;
2194
2195 --If there were no successfully transferred rules, the return status will be 'Error'.
2196
2197 BEGIN
2198
2199 SELECT 1 INTO v_error_flag FROM DUAL WHERE EXISTS
2200 (SELECT NULL FROM cz_imp_rules
2201 WHERE run_id = p_run_id
2202 AND rec_status = CZRI_RECSTATUS_XFR
2203 AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY));
2204
2205 EXCEPTION
2206 WHEN NO_DATA_FOUND THEN
2207 v_error_flag := 0;
2208 END;
2209
2210 IF(v_error_flag = 0)THEN RAISE CZRI_ERR_DATA_INCORRECT; END IF;
2211
2212 --Reset the application module name.
2213
2214 DBMS_APPLICATION_INFO.SET_MODULE('','');
2215
2216 EXCEPTION
2217 WHEN CZRI_ERR_ACTIVE_SESSIONS THEN
2218 x_msg_data := cz_utils.get_text('CZRI_IMP_ACTIVESESSION', 'RUNID', p_run_id);
2219 x_msg_count := 1;
2220 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2222 DBMS_APPLICATION_INFO.SET_MODULE('','');
2223 WHEN CZRI_ERR_RUNID_EXISTS THEN
2224 x_msg_data := cz_utils.get_text('CZRI_IMP_RUNID_EXISTS', 'RUNID', p_run_id);
2225 x_msg_count := 1;
2226 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2228 DBMS_APPLICATION_INFO.SET_MODULE('','');
2229 WHEN CZRI_ERR_RUNID_INCORRECT THEN
2230 x_msg_data := cz_utils.get_text('CZRI_ERR_RUNID_INCORRECT', 'RUNID', p_run_id);
2231 x_msg_count := 1;
2232 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2234 DBMS_APPLICATION_INFO.SET_MODULE('','');
2235 WHEN CZRI_ERR_DATA_INCORRECT THEN
2236 x_msg_data := cz_utils.get_text('CZRI_ERR_DATA_INCORRECT', 'RUNID', p_run_id);
2237 x_msg_count := 1;
2238 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2240 DBMS_APPLICATION_INFO.SET_MODULE('','');
2241 WHEN CZRI_ERR_FATAL_ERROR THEN
2242 --hard errors occurred in underlying procedures, already logged.
2243 DBMS_APPLICATION_INFO.SET_MODULE('','');
2244 WHEN OTHERS THEN
2245 --unexpected errors occurred in the procedure.
2246 x_msg_data := cz_utils.get_text('CZRI_IMP_SQLERROR', 'ERRORTEXT', SQLERRM);
2247 x_msg_count := 1;
2248 report(x_msg_data, p_run_id, 'rule_import', v_debug);
2252
2249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2250 DBMS_APPLICATION_INFO.SET_MODULE('','');
2251 END;
2253 -----------------------
2254 --------------procedures for lock, unlock
2255 PROCEDURE lock_models (p_api_version IN NUMBER,
2256 p_run_id IN NUMBER,
2257 p_commit_flag IN VARCHAR2,
2258 x_locked_entities OUT NOCOPY SYSTEM.CZ_NUMBER_TBL_TYPE,
2259 x_return_status OUT NOCOPY VARCHAR2,
2260 x_msg_count OUT NOCOPY NUMBER,
2261 x_msg_data OUT NOCOPY VARCHAR2)
2262 IS
2263
2264 l_locked_entities cz_security_pvt.number_type_tbl;
2265 l_model_id_tbl table_of_number;
2266 rec_count NUMBER;
2267 MODEL_IS_LOCKED EXCEPTION;
2268
2269 BEGIN
2270 ----initialize FND stack
2271 FND_MSG_PUB.initialize;
2272 x_return_status := FND_API.G_RET_STS_SUCCESS;
2273 x_msg_data := NULL;
2274 x_msg_count := 0;
2275 x_locked_entities := SYSTEM.CZ_NUMBER_TBL_TYPE();
2276
2277 IF (p_run_id IS NULL) THEN
2278 SELECT distinct devl_project_id
2279 BULK
2280 COLLECT
2281 INTO l_model_id_tbl
2282 FROM cz_imp_rules
2283 WHERE rec_status IS NULL
2284 AND disposition IS NULL
2285 AND run_id IS NULL;
2286 ELSE
2287 SELECT distinct devl_project_id
2288 BULK
2289 COLLECT
2290 INTO l_model_id_tbl
2291 FROM cz_imp_rules
2292 WHERE rec_status IS NULL
2293 AND disposition IS NULL
2294 AND run_id = p_run_id;
2295 END IF;
2296
2297 IF (l_model_id_tbl.COUNT > 0) THEN
2298 FOR I IN l_model_id_tbl.FIRST..l_model_id_tbl.LAST
2299 LOOP
2300 cz_security_pvt.lock_model(1.0,
2301 l_model_id_tbl(i),
2302 FND_API.G_FALSE,
2303 FND_API.G_FALSE,
2304 FND_API.G_FALSE,
2305 l_locked_entities,
2306 x_return_status,
2307 x_msg_count,
2308 x_msg_data);
2309
2310 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2311 RAISE MODEL_IS_LOCKED;
2312 ELSE
2313 IF (l_locked_entities.COUNT > 0) THEN
2314 x_locked_entities.EXTEND(1);
2315 rec_count := x_locked_entities.COUNT;
2316 x_locked_entities(rec_count) := l_model_id_tbl(i);
2317 END IF;
2318 END IF;
2319 END LOOP;
2320 END IF;
2321 IF (p_commit_flag = FND_API.G_TRUE) THEN COMMIT; END IF;
2322 EXCEPTION
2323 WHEN MODEL_IS_LOCKED THEN
2324 fnd_msg_pub.count_and_get(FND_API.G_FALSE, x_msg_count, x_msg_data);
2325 WHEN NO_DATA_FOUND THEN
2326 NULL;
2327 WHEN OTHERS THEN
2328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2329 fnd_msg_pub.add_exc_msg('CZ_RULE_IMPORT', 'lock_models');
2330 fnd_msg_pub.count_and_get(FND_API.G_FALSE, x_msg_count, x_msg_data);
2331 END lock_models;
2332 ---------------------------------------------------------------------------------------
2333 END;